Creating interactive tests in MS Excel. How to create tests in Microsoft Excel How to make tests in Excel examples
1. Open Microsoft Excel
2. In the right place in the window that opens, make a title, “Test program in mathematics.” To do this, you can prepare a cell in advance by merging cells: select the cells to be merged, and execute the following commands: Format – cells – alignment, check the box next to merging cells and click OK.
In the merged cells, write down the title of the test:
https://pandia.ru/text/78/496/images/image003_1.jpg" width="540" height="210">
4. Let’s type the questions and post the answer options:
5. Make a choice: in cell B4 (opposite the first question), select this cell, click the Data-Check menu
From the “Data type” options, you need to select “list”, after which the “Source” input window will appear. Click on the button and then select the cells with the answers (in our case this is)
We will see that there is a select button near cell B4. By clicking it, we get the options.
6. Place a check function in the adjacent cell C4
=IF(B4=" ";" ";IF(B4=K4,"yes","no"))
Where K4– this is the address of the cell with the correct answer to the first question.
The meaning of the function is as follows:
· If the cell being tested is empty, then the cell containing the function should also be empty (no response – no reaction).
· Otherwise, if it is not empty, then if the answer matches the correct one, then put “yes”, otherwise “no”.
7. We construct questions and answers in the same way, copy the function and do not forget to change the address of the cell with the correct answer in the function.
= COUNTIF(C4:C8, “yes”)
The function will give as many points as the number of times the word “yes” appears in cells C4 to C8.
Save the program.
The role of such a test is reinforcing and training, rather than controlling. However, it is possible to give the test a semblance of rigor. To do this you need:
- using the menu Format deprotect those cells in which the answer and reaction are entered, as well as scoring: select the headings of the columns in the cells of which there are answers and click Format-Hide. You can also hide formulas. Then (Service) protect the sheet.
You can evaluate the test. For this purpose, you need to select a cell in which to create a formula to obtain the estimate.
For example, for our example, place the formula in cell C11
=IF(C9=" ";" ";IF(C9=5,"5";IF(C9=4,"4";IF(C9=3,"3","2"))))
Save the program.
Now check how the program works.
If you answered all the test questions correctly, the window of completed actions looks like this:
When closing the Excel window, do not save the result.
The use of psychological tests and questionnaires in the work of an HR manager always causes heated debate. This toolkit has many supporters and opponents.
In a dispute, both sides present compelling arguments, such as the following:
It is possible if: it is justified and necessary; if there is a clear understanding of the purposes of use; if the data is interpreted by a high-level specialist.
You can't, because it's a waste of time. both on the part of the applicant and on the part of the employer; there is a danger of replacing an interview with a test, etc.
So, this article is for those HR managers who use (or want to learn how to use) special techniques in their work. My experience in automating data processing will be useful to those company specialists who:
decided to use tests when selecting (training, evaluating) personnel for certain vacancies;
for various reasons, they are not yet able to purchase standardized packages that include automated processing of data obtained from the use of various methods and tests;
They do not have professional diagnosticians on staff with experience in conducting research.
More precisely, we will talk about the possibilities of automating diagnostic procedures using the program Excel. The choice of this program is due to its availability. it is included by default in the standard Microsoft Office package, as well as the wide range of capabilities for working with data sets that it provides.
From my own experience, I know that novice HR managers invariably ask the question: Why automate work with tests? Here are typical answers from experienced HR people:
1. I want to reduce the costs of testing personnel, namely:
eliminate the need to print forms (save paper). I’ll add on my own: I don’t like piles of papers. it makes my desk and office look like trash, and I'm the same guy. for technical progress, and therefore for automation;
reduce the working time spent on processing results (saving man-hours), it can be spent on more important matters;
optimize activities by reducing repetitive, routine, unproductive operations: once you spend some time on developing tools, you can save effort in the future;
minimize the time of the subjects;
reduce the likelihood of errors by leveling the influence of the human factor, both on the part of the respondent and on the part of HR;
increase your own mobility: data in electronic form can be easily transferred to removable media, sent by mail, etc.
increase the controllability of the diagnostic process: the automated process can be flexibly changed, adjusted, configured taking into account changed circumstances (and simply for yourself).
And in general, I want to live with the times, build the most advanced system, maybe even remote. Automation of tests. one of the examples of the use of new technologies, new approaches to work, which speaks of development and self-improvement, the desire to move forward. All this, in turn, works for a positive image of the company and attracts candidates seeking development.
2. I want to be able to manage the testing process:
modify non-working questions (with appropriate preparation), add necessary ones;
understand how testing is carried out, monitor the progress of the process;
modify, develop new versions of reports, charts, tables, etc.
take a flexible approach to data analysis. for each subject, for individual groups, for the company as a whole;
do not waste time answering traditional questions from beginners: How to process the results? . It is best if they are processed themselves.
In fact, such work is not that difficult; any experienced computer user can master it. You don't have to be a programmer. Often it is enough to provide the HR manager with a little methodological assistance and show him. what and how, and he will be able to independently use and even develop his own automated solutions.
Let's look at automation of data processing using the example of working with a test Interpersonal Relationships Questionnaire. It is better if you download both files before considering the algorithm.
Test automation algorithm: development of a computer version
Step 1. Preparation of the questionnaire.
1. Open a new file in the program Excel. At the same time, open the source Word file, which contains a description of the methodology, the questionnaire itself, and the keys to it.
2. Transfer the questionnaire form from the Word file to Excel (rice. 1). To do this, select, then copy the text and transfer it to Excel-file. In order for the text to save the parameters of the paragraph, the insertion must be done in the formula bar (it is located at the bottom of the top menu and is indicated by the symbol f x . rice. 1).
Rice. 1
3. Immediately save the file to a previously created folder (naming it, for example, mejlichnostn.xls).
4. We edit the instructions for the computer version. The subject will not have to select an answer option, but will have to enter 1 in the selected answer cell.
5. Change the cell format for ease of reading. (Select several cells, merge them, then use the right mouse button to bring up the Formatting menu.)
6. Align the width and height of the lines.
8. You can add the Last Name field at the very beginning. Name. Surname ( rice. 2). Sometimes it is useful to add some socio-demographic characteristics (for example, gender, age, position, length of service in the company, etc.).
9. The cells in which the respondent will enter answers can be highlighted in a different color ( rice. 2).
10. We select fonts and sizes of characters (for example, you can use fonts Arial And Arial Narrov . rice. 2).
11. Rename the sheet. you can call it Questionnaire ( rice. 2).
Click image for a larger view
Rice. 2
Step 2. We use the option Automatically check that cells are filled in correctly.
1. When filling out the questionnaire, the respondent may make mistakes:
skip one of the questions (that is, do not mark any answer);
mark more answers than possible (in this case, two or more).
Therefore, it is necessary to automate verification of correct filling of response fields. For example, if the answer field is filled in incorrectly. display a warning (Answer not selected or Extra answers), but if filled in correctly. remove the error signal.
2. As an answer, we agreed to put the number 1 in the cell. This means that to check that the fields are filled in correctly, we need to analyze the sum of the answers for each question in the cells.
3. Next to the answer options, enter formulas to check the rules we have established ( rice. 3), in this case it will be the formula:
=IF(SUM(C5:H5)=0,"no answer selected";IF(SUM(C5:H5)=1,"","extra answers"))
Now we control whether the cells with answers are filled in correctly.
This way, even if the respondent makes a mistake, the HR manager will not have to manually review all the answer cells. You can only pay attention to the signals no answer selected or extra answers to invite the respondent to correct the identified errors.
Click image for a larger view
Rice. 3
Step 3. We set formulas for calculating the result.
1. Let's turn to the source Word file with a description of the test and the keys to it. Our task. repeat the key table in Excel and automate calculations. The calculation of results can be organized either on the same sheet or on another ( rice. 4).
2. Create a key table. transfer it from the Word file to Excel (rice. 4). It is useful to immediately highlight the keys in a different color.
Click image for a larger view
Rice. 4
3. In accordance with the keys, we write down formulas for counting answers ( rice. 5). In this case, if the answer matches one of the answer options of the key, it is scored 1 point, if it does not match. 0 points (Fig. 5). For example, the formula for counting answers to the first question will look like this ( rice. 5):
=IF((SUM(O7:R7))>=1,1,0)
Click image for a larger view
Rice. 5
4. The same formulas must be written for each of the keys. As a result, we will receive a summary table of results ( rice. 6).
5. To prevent the respondent from seeing these calculations, the key table must be hidden in the future.
Click image for a larger view
Rice. 6
Step 4. Selective protection of cells from changes.
1. When filling out a questionnaire, a person may accidentally delete or change information in the cells with questions. Therefore, our next task. selectively allow the respondent to change information only in those cells in which answers are entered. All other sheet cells must be protected from changes.
2. Before protecting the entire sheet, you need to mark the cells/ranges that can be changed in the future. In our case, these are cells with answer options. they are marked in a different color ( rice. 7).
Click image for a larger view
Rice. 7
3. Install protection on the entire sheet. In the Tools menu, select the commands: Protection. Protect the sheet. After this, you can change the contents only in those cells where we previously allowed ( rice. 8).
4. Let's check the protection: let's try to change the cells with answers and other cells. To remove protection, in the Tools menu you need to select the commands: Protection. Remove protection. Now let's try again to change the cells with answers and other cells.
5. By the way, if necessary, you can enter a password to remove the protection, then when you try to change protected cells (for example, cells with questions, formulas or keys), the computer will display a message that it is prohibited to change the contents of these cells (Fig. 8).
Click image for a larger view
Rice. 8
Step 5. Presentation of results.
1. Let's turn again to the source Word file, which contains the form for outputting the result. In this case, for clarity, you can use a table-diagram ( rice. 9). We will calculate the results themselves on another sheet. Let's call it, for example, Result ( rice. 9).
2. Enter the formula for displaying results from the Questionnaire sheet to the Result sheet. In this case it will be the formula:
=questionnaire!B4
Now on the Result sheet, in the Test taker field, the name of the respondent will be displayed, which was entered on the Questionnaire sheet in cell B4 ( rice. 9).
Click image for a larger view
Rice. 9
3. In the same way, we enter formulas that establish the connection between cells with scores on the Questionnaire sheet with similar cells on the Result sheet ( rice. 10). As a result, all scores on all scales will be displayed on the next sheet automatically.
4. For clarity, we introduce formulas for displaying results in scale lines. Let's type the formula ( rice. 10):
=IF($D$4>=E3,"X","")
And then we replicate it for all cells of all scales. As a result, we will have a visual display of behavioral manifestations in three areas of interpersonal relationships.
5. You can enhance clarity using formulas conditional formatting. Cells that summarize results across scales can be analyzed.
Let's highlight in a different color those cells that are marked with the X symbol. For example, if the score is 5, then highlight in a different color the cells with a score from 1 to 5. To do this, use the commands: Format. Conditional formatting. We enter the condition formula: if the cell value is equal to X, highlight this cell with a different color (for example, light yellow. rice. 10).
6. We replicate the conditional formatting across all result cells. To do this, use the command: Format by sample ( rice. 10). As a result, we get a table-diagram in which all the test results are clearly presented (in this case, a profile of behavioral manifestations in three areas of interpersonal relationships).
Click image for a larger view
Rice. 10
7. Add descriptions of each scale to separate fields. To do this, let's turn to the source Word file and transfer all the descriptions to the file Excel per sheet Result ( rice. eleven).
In the same way, you can add a hint-note to the name of each scale ( rice. eleven). Now, when you hover the cursor over a cell containing the name of the scale, a note will pop up with a brief description of it ( rice. eleven).
Click image for a larger view
Rice. eleven
interaction volume indices ( e + w) . within specific areas of interpersonal needs;
indices of inconsistency of interpersonal behavior ( e . w) . between distinct areas of interpersonal needs.
To do this, in the cells displaying the indices for each area, we will enter formulas for calculating the sum and difference of points. For example, in a cell that displays the interaction volume index ( e + w
Accordingly, in the cell that displays the index of inconsistency of interpersonal behavior ( e . w) for the Inclusion area, you need to insert the formula:
Similarly, we introduce formulas for the remaining indices ( rice. 12).
Click image for a larger view
Rice. 12
13. In addition to the tabular form of displaying data, you can use diagrams for clarity, since the program Excel is known precisely for its developed tools for their construction. Let's look at the capabilities of diagrams using the example of working with test results using our questionnaire.
Select the area of cells containing data to construct the chart. in this case, these are assessments of behavioral manifestations ( rice. 13). Then we use the Chart Wizard option. From the proposed types of charts for this questionnaire, we will choose a radar chart ( rice. 13). Next, following the instructions, fill in the necessary parameters in the Diagram Wizard dialog box ( rice. 13). As a result, we get a visual, informative diagram in which the profile of behavioral manifestations is immediately visible.
Click image for a larger view
Rice. 13
14. Now that all the calculation formulas and diagrams are ready, we will try to hide some technical details from the respondent. For example, a table of keys on the Questionnaire sheet.
To do this, select the range of columns to be hidden, use the right mouse button to call up the context menu, in which select the Hide option ( rice. 14). This area is now invisible. If necessary, you can display it by doing the opposite operation: selecting the range in which the hidden cells are located and selecting the Show option from the menu ( rice. 14).
Click image for a larger view
Rice. 14
So, Excel starts working for HR! Using the algorithm proposed by the author as a sample, HR managers will be able to independently automate many very useful tools in their work. This will allow them to save their time and energy so that they can use it to solve more creative problems.
Electronic tests are currently widely used both in educational institutions when testing the knowledge of students, and in enterprises when testing the competence of employees. An electronic test is convenient because the computer immediately provides a grade or test result. There are various software tools for creating electronic tests.
One of the simplest options is to create a test using Excel spreadsheets. To create a test, you must have the test itself and a system for evaluating test results.
Let's look at creating a test using the example of creating a test for schoolchildren studying hypertext markup language. The figure below shows a fragment of a test already completed on an Excel sheet; you need to configure it to automatically display results.
1) Opposite each question there is a cell for entering an answer option; for these cells we set the following properties:
- set external boundaries: Home - Font - Borders - External boundaries ;
- remove cell protection - right-click on the selected cell and select the item from the pop-up menu Cell format... , in the dialog box Lists select a tab Protection , uncheck the box before the option Protected cell .
We carry out these points for all cells in which data is supposed to be entered - these are cells for entering personal data and cells for entering the selected answer to a question.
2) Set the sign of the correct answer. In the next cell next to the answer option entry cell (in the example, the cell F4) enter a formula that displays “1” for a correct answer and “0” for an incorrect answer. To enter a formula, you can use the function wizard or enter the formula manually in a cell; in this example, we use the function =IF(E4=2;1; 0):
In the logical expression E4 = 2, we compare with “2”, since number 2 is the correct answer option and if 2 is entered in cell E4, then in cell F4 the result will be “1”, otherwise - “0”. We copy this function opposite each cell for entering an answer option, changing only the number of the correct answer.
3) We count the number of points scored. At the end of the test, we summarize the values in the column indicating the correct answer. At the end of the test, under all the answers we count the number of ones (correct answers) and enter the function SUM. For the example given in the cell F37 enter =SUM(F4:F36) .
4) We evaluate the test result in accordance with the number of points scored. In this example, the following correspondence scheme is adopted:
10 points – rating “5”,
9-8 points – score “4”,
7-4 points – score “3”,
less than 4 points – score “2”.
For the accepted assessment, you should enter the formula: =IF(F37=10,5,IF(F37>=8,4,IF(F37>=4,3,2))) in a cell E37.
Instead of the accepted school assessment, you can use the text equivalent - “excellent”, “good”, etc., then the formula will look like this: =IF(F37=10;"excellent";IF(F37>=8,"good";IF(F37>=4,"satisfactory","unsatisfactory"))).
5) We hide the signs of correct answers. Column F, which contains functions that indicate the correct answer, we hide it so that it is not visible to the person taking the test. To do this, do the following - right-click on the column header, select the item in the pop-up menu Hide .
Imagine a situation: you have been assigned to test students. There are more than 20 students in the class, there are 10 questions in the test. You have very little time to summarize. And here you apply your knowledge in Excel 2010 and create a table in which the student enters answers to the test questions, and the program you created in the form of a table gives him a grade depending on the number of errors. Now let's describe the process of creating such a table.
Prepare the table (Fig. 10.5). The first line contains the numbers of 10 questions. The texts of the questions can be given below, with notes, etc., as you prefer.
In the second line, which is called “Survey on Topic No. 1,” students will enter their answer options. The third line contains formulas that contain the correct test answers. Let's say that in our test, students choose from three answer options. Don't worry, we will hide this line later and it won't be visible. Pay attention to the formula in cell B3 in Fig. 10.5. I used a Boolean function from the Excel library here. In general, it looks like this: IF(log_expression; value_if_true; value_if_false). The IF function returns one value if the specified condition evaluates to TRUE, and another value if the specified condition evaluates to FALSE.
In simple words: the first argument of a function (logical_expression) is a logical expression that the function checks. If the expression is true, then the function outputs the second argument (value_if_true), if the expression is false, then the function outputs the third argument (value_if_false). The log_expression argument is required. For example, B2=1 is a logical expression; If the value in cell B2 is 1, this expression evaluates to TRUE; otherwise, it evaluates to FALSE. This argument can use any comparison operator.
Comparison operators in Excel:
- if there are 9 or 10 correct answers, then the score is 5;
- if there are 8 or 7 correct answers, then the score is 4;
- if there are from 6 to 4 correct answers, then the score is 3;
- if there are 3 or more correct answers, then the score is 2;
- if there are 2 or 1 correct answers, then the score is 1.
I give this example to show that any logical function can be used as an argument to the IF function. And there can be no more than 64 such nested functions. But I can hardly imagine how you can build a logical structure from 64 nested functions and not get confused... Let's try to figure it out with at least three.
Look at the formula in Fig. 10.7. The analysis of such “matryoshka dolls” should always begin with the smallest, that is, with the innermost function, in our case it looks like this: =IF(L3>2;2;1) . This function checks the number of correct answers (line 3 is hidden, it can be seen in Fig. 10.5). If there are more than 2 correct answers, then the output score is 2, if not more than 2 (and not more than 2, then less than or equal to 2),
then 1.
The following “matryoshka”: =IF(L3>3,3,IF(L3>2,2,1)) . If the number of correct answers is more than 3, then the score is 3, if not more than 3 (that is, less than or equal to 3), then the first condition is checked and, depending on the result, 2 or 1 is assigned.
Third level: =IF(L3>6,4,IF(L3>3,3,IF(L3>2,2,1))) . If the number of correct answers is more than 6, then the score is 4. If it is less, then the second and first conditions are checked sequentially and scores of 3, 2 or 1 are given.
And the last level. =IF(L3>8,5,IF(L3>6,4,IF(L3>3,3,IF(L3>2,2,1)))) For excellent students - those who give more than 8 correct answers. They are given a score of 5. If there are less than 8 correct answers, then the program checks all IFs sequentially and assigns 4, 3, 2 or 1.
Excel has a whole section of Boolean functions. They can work both as separate functions and as arguments to each other. With their help, you can build the most complex logical chains. Let's look at some logical functions.
AND(logical_value1,logical_value2,...)- returns the value TRUE if the result of evaluating all arguments is TRUE; returns FALSE if the result of evaluating at least one of the arguments is FALSE. The boolean_value1 argument is required. This is the first condition to be tested that evaluates to TRUE or FALSE. Arguments boolean_value2 and onwards are optional. There can be no more than 255 of these conditions.
The AND function checks all the logical values that are written in its arguments. If all of them are true, then the function outputs the value TRUE; if at least one expression is false, then the output is FALSE. Look at the example in Fig. 10.8.
The AND function can be used as an argument to the IF function. For example: =IF(AND(1
I(1 If FALSE (the value in cell A1 does not fall within the interval from 1 to 100), then the output is the text “Value outside the interval.” Look at fig. 10.9. Here, column A contains the initial values, and column B contains the result of executing the described function. OR(logical_value1,logical_value2,...)- returns TRUE if at least one of the arguments is TRUE. Returns FALSE if all arguments are FALSE. The boolean_value1 argument is required. Argument boolean value2 and further are optional. The function works in the same way as AND, only it evaluates to TRUE much more often. For this, it is enough for her that at least one of the set conditions is met. I am often asked to do a test in Excel. This is a test for company employees or for friends and acquaintances. As a rule, the request includes several conditions: 1) You need to test your colleague, friend or relative's knowledge of Excel 2) The test should not be easy or too difficult 4) You need to cover all the main Excel topics 5) It should be possible to quickly change the macro or test calculation mechanism, add or change questions, etc. The article presents a file with a test, as well as a short description of this test. Download the test
Open the file and enable macros first, because... calculation is done using VBA. Read how to do this. Then open the file and delete the correct answers on each of the sheets Question1 - Question10. To pass the test correctly, follow the instructions: 1. “Enter the correct answer” - you need to enter a number or text in the field 2. “Choose the correct answer” - put 1 or another character in the cell next to it After passing the test, on the Calculation of totals sheet, click the Calculate button, see the picture at the beginning. The score will be calculated on line 17. The Excel test has a difficulty level of 4 out of 5, so a test result of 3.5 (that is, 7 answers out of 10) indicates good knowledge of MS Excel, and a response of 4 to 6 indicates satisfactory knowledge of the program. P.S. For those who want to change a VBA macro, it is specially written in quite simple language. Anyone who has an idea of programming, I think, will easily understand what is written. The main function of the created macro is to calculate the correctness of the answer, count the number of correct and incorrect answers and give a grade. Good luck taking the test! ;) You can see my other works.
Working with the test using Excel
If the cell says:
