There are many functions in the Excel function library. Learning to use them correctly can greatly improve work efficiency. If you learn VBA programming knowledge and do some simple function control, you can simplify a lot of complicated work. Therefore, according to the practical problems in teaching, this paper introduces how to use simple VBA programming and VLOOKUP function to complete the topic selection of graduation thesis.
Keywords: educational administration; Excel function; VBA
With the popularization of office software, Excel spreadsheet making software has been widely used in daily teaching management. However, many people are limited to simple functions, such as sorting, summing, and finding the maximum and minimum values of input data. After a little research, you will understand that there are many powerful and practical functions in the Excel function library. Teaching management is a service for teachers and students, which is complicated and needs patience and meticulousness. Manual operation will inevitably lead to negligence and mistakes. In order to reduce the burden of teaching administrators, this paper takes the topic selection of graduation thesis as an example to introduce how to use Excel to reduce the workload.
First, demand analysis.
Because the school has strict regulations on the topic of the thesis, the topic must conform to the professional research direction, ensuring that one person has one topic, and there can be no duplicate or similar topics. The college requires each instructor to provide the same number of topics as the number of students for students to draw lots to choose. In the past, the paper label was made in advance, and the serial number of the thesis topic was written on the paper label for students to draw. After the students draw the serial number of the topic, fill in their student number, name and drawn number in another form. Finally, the teaching secretary sorts out and enters the handwritten list. This kind of work is not only time-consuming and laborious, but also prone to mistakes.
Second, the introduction of the lottery function. Let's introduce the lottery function of paper topics made by Excel.
First, copy the student ID and name from the student list file into the table, and then add a draw button to each row. After specifying the same macro, edit the macro. After obtaining the total number of students, every time you press the lottery key, a random number will be generated between 1 and the number of students, and the corresponding paper topic number will be filled in (the number of candidate topics is equal to the number of students). In VBA programming, only simple judgment and loop statements are used, which can be understood with a little understanding. This is a candidate topic in another worksheet. Next, use the VLOOKUP function to search the "lottery paper title number" in figure 1 in the "lottery number" column of figure 3. After the search, extract the corresponding candidate topics and teachers' names and fill in the figure 1 The functions actually used are as follows: IF(ISERROR(VLOOKUP(D2, candidate topic! A: d, 4, FALSE)), "",VLOOKUP(D2, candidate topic! One: D, 4, false). When there is no lottery serial number, "#N/A" will be displayed because the corresponding topic cannot be retrieved. For the sake of beauty, if you use the judgment function to fill in the blanks when you don't search.
Three. Concluding remarks
As long as you gently press the lottery button, the finishing work after the lottery can be completed at the same time. Using the common functions of Excel and simple VBA programming, the complicated work in the past is simplified, the efficiency is improved and the error probability is reduced.
References:
[1] Zhang Ping, Liang Jinghua. Some Applications of Excel in Teaching Management [J]. Computer Knowledge and Technology, 20 13
[2] Tian. On the application of Excel in school education and teaching management [J]. Intelligence, 20 14
[3] Zheng Weimin. Fundamentals of computer application -Excel 2003 spreadsheet system [M]. Beijing: China Central Radio and TV University Press, 2004.
;