Menu
homeMicrosoft

Complex Excel tests when applying for a job. Creating tests in Microsoft Excel. how to write a logical command in Excel

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 input cell for the answer option (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 matching 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 .

Create a test in Excel it can be simple. The “IF” function in Excel will help us. To understand the IF function, read the article “IF Function in Excel”.
How to create a test in Excel.
Answers to the test questions will be selected from a drop-down list. How to make a drop-down list, see the article “Drop-down list in Excel”. For the test, an even simpler version of the drop-down list is suitable. Read “How to make a drop-down list in Excel without a list.”
So, creating a test inExcel.
We have a table with questions in column A and drop-down lists with answer options in column B. In column C we write the rating of the selected answer. In cell C2 we write the following formula. =IF(B2=60,5,0)
Explanation of the formula: if the number “60” is in cell B2, then this is the correct answer, we give “5 points” for it. If the answer is not correct, then put “0”.
The following formula was written in cell B3. =IF(B3=30;5;0) The result is a table like this. We count the amount of points scored. In the last cell of column C, set the formula “AutoSum”. We put the autosum formula in cell C4. =SUM(C2:C3)
Now you need to evaluate the test result depending on the number of points received. For example, we will evaluate on this scale.
10 points – rating “5”,
5 points – rating “3”,
0 points - score “0”.
In cell B4 we write the following formula. =IF(C4=10,5,IF(C4>=5,3,IF(C4>=0,0)))
The result is a table like this.
Now let's fill out the questionnaire and test. Let's insert cells for full name and other data. Let's hide column C. Highlight with font, color, cell border, etc., sections of the test, test result, etc. The result was a test like this.
If we put all the answers correctly, we will get the following test result.
The result was the highest score - “5”.
In the drop-down list you can put not only numbers, but also words. For example - “Yes” and “No”. Or – “excellent”, “good”, “satisfactory”, unsatisfactory.” In the next option, there are words in the drop-down list.
Then in cell G16 we will write the following formula. =IF(F16="Yes";5;IF(F16="No";0)) If in the test the answers are given not in words, but in signs, you can also summarize the result.
For ways to do this, see the article "

Job interview tests are, first of all, necessary in order to, with a large influx of people wanting to apply for a position, select the most worthy candidates with whom you can conduct an individual interview.

After all, if such an interview is conducted with every candidate, then many companies, especially those that do not have a huge amount of money for the HR department, simply will not have enough resources. Therefore, they also use tests on it.

First of all, these tests must show a person’s intelligence, his psychological state, as well as basic knowledge of what he will have to work with.

The most necessary thing will be, first of all, a test of knowledge of some programs that the applicant will have to work with if he successfully completes the tests and interview.

Of course, tests cannot be the only sources of information about a candidate for a position. You should also take into account your biography, experience from previous jobs and education, if any.

Of course, separate individual interviews will need to be conducted with deserving applicants. However, in this article we will focus primarily on tests.

Types of tests

Now we should describe some of the categories of tests that are the most useful and common.

IQ tests

Interview IQ tests, as the name suggests, are needed to measure the intelligence level of the applicant.

They are especially popular when hiring for those positions that primarily require a person’s intellectual abilities.

In general, you don’t need to come up with any tricky ones, but just use a book with tests authored by one of the most important popularizers of IQ tests - G. Eysenck.

The only downside to this classic test is that if the applicant has already taken it, then some of the questions may be the same, which will give him some advantage over other candidates.

But if you are really afraid of such a situation, then you can conduct a test using the Rudolf Amthauer technique, which will not only determine the candidate’s intelligence, but also establish his aptitude for technical or human sciences, and also indicate whether the person is suitable for your position.

Read about what logical questions and tests are in an interview.

Psychological

Luscher test involves choosing the most pleasant color and usually helps to measure a person’s resistance to stress, conflict, and communication abilities.

Mathematical

For specialists such as accountants or economists, the ability to count is simply necessary.
This is where math interview tests come to the rescue.

This test is one of the easiest for the person conducting the interview.. He, in essence, requires almost nothing.

You just need to find several dozen tasks on working with graphs, as well as on arithmetic operations (including fractions and percentages), then distribute tests to applicants and check the results.

By the way, the test will also not be particularly difficult, because you will only be required to compare the applicants’ answers with the correct options, which is not difficult enough compared to psychological tests, where you need to have a certain amount of insight.

But, despite their simplicity, these tests are extremely accurate and indicative tests of attentiveness. In general, interview tests for attentiveness are especially popular when selecting for many professions where this character trait is necessary.

Math interview test example.

We talked in detail about what mathematical, logical and analytical tasks are asked at an interview and how to answer them in.

Computer

Almost all professions today are somehow related to working on a computer. The main skill under such conditions is the ability to navigate the programs necessary for the profession, as well as type at a decent speed.

Actually, this test will also be simple. To begin with, ask the applicant what programs he uses, mainly for work, and also what his experience as a PC user is.

After which you should give the candidate a task to measure typing speed. This can be done visually, if exact indicators are not so important to you, or using special programs for measuring typing speed. In any case, if a person barely knows how to navigate the keyboard, you will definitely notice it.

The most commonly used test in this area is the Excel knowledge test during an interview. To prepare for it, you can take the test online on specialized websites or download it from ours.

Professional

Aptitude tests are some of the most challenging for both parties involved.

The applicant, especially if he is not particularly confident in himself, is worried about almost any test of his skills.

The employer’s anxiety is primarily due to the need to create an accurate test that can show all the problem areas in the candidate’s knowledge for the position.

It doesn’t even make sense to list examples of questions, because each profession will have its own specific tasks and tests during a job interview.

The only advice I can give is to make a multiple-choice test and add a field in which the applicant can write why he chose a particular answer option.

When checking such tests, you will first find several applicants who have the most similar answer options to the key, and then read their explanations, which will help you choose the most worthy candidate.

You can find out more about how a sales manager is tested during an interview and how to answer correctly.

For quick wits

Various tasks and intelligence tests when applying for a job are an excellent chance to understand whether a person has the ability to think quickly and creatively.

However, you should not get carried away with this, because for a person who is applying for a position that does not require innovative thinking or some incredible creativity, these tests will not really show anything worth your attention.

Another problem lies in the use of well-known questions from the Internet, which people can prepare for and, accordingly, interfere with the assessment of their ingenuity and intelligence.

Examples of employment tests for a variety of specialties can be found.

A meeting between a candidate and an HR employee will be successful if both parties are prepared before it. On our website you will find detailed explanations on how to answer questions such as: or why you chose our company; from the previous place of work.

Do you need to prepare for them in advance?

If you're just going into an interview expecting there might be a test, you might want to do a little bit of preparation for them.

At a minimum, you must be prepared emotionally so that in the event of a particularly difficult question you will not get confused and be ready to think about it until the end of the time allotted to you.

You should also find some that can be found extremely often, and then answer them a little, checking your answers (you can look at the answers to questions at a job interview).

This will help you become more confident in interviews using tests. In addition, you may well come across a question that you have already “passed”.

How to behave during testing?

If you are a job seeker, then you will need to behave with restraint and calm. Try not to be distracted, focus on the questions or tasks for which you will need to find a solution. If there are difficult ones, skip them and come back to them at the end.

Sometimes it may be that there are too many questions for the time allotted to you. Do not panic! This could be a ploy to test your stress tolerance.

If you are conducting an interview, try not to distract or intimidate applicants.

Be reserved and calm, but friendly; Never express emotions regarding the test results of a person, as this may make him nervous.

Evaluation of results

When grading tests, try to be as careful as possible. In fact, with most tests, you don't have to do anything other than compare the answer options on the applicant's form with the correct answers in the key.

Don't forget that this is not the final stage and you will also need to conduct an interview, but with people who have already performed well during testing. Which ones there are, and are also presented in detail in our materials.

A wide variety of interview tests with answers can be found on the Internet, including ours.

Municipal educational institution "Secondary school with. Lipovka"

Master Class

“Creating Physics Tests

V Microsoft Excel »

Prepared

physics and computer science teacher

Municipal educational institution "Secondary school with. Lipovka"

A.Yu. Fedulov

2010-2011

    Open Microsoft Excel

    In the right place in the window that opens, make the title “Test program in physics.” To do this, you can prepare a cell in advance by merging cells: select the cells to be merged, execute the following commands: Format – cells – alignment, check the box next to merging cells and click OK.

In the merged cells write the title of the test:

3. Rename “sheet1” to “Test”, and “sheet2” to “Result”.

4. On the “Test” sheet, in the table cells, we will make column headings where questions, answers, and verification will be placed.

Excel allows you to create tests with a free answer (when the student is not given an answer option) and with a selective answer (when the student is offered answer options from which he chooses the correct one).

    When you create a free-response test, a group of cells is created for you to enter your answer.

    When you create a multiple response or matching test, follow these steps:

5. Let’s type the questions and post the answer options:

    Let's 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.

    Let's place a check function in the adjacent cell C4

=IF(B4=" ";" ";IF(B4=F4,"YES"," "))

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 answer in the cell being tested is not correct, then place a blank in the cell containing the function.

    Otherwise, if it is not empty, then if the answer matches the correct one, put “yes”.

    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.

To do this you need:

    using the menu Format deprive protection of those cells in which the answer is entered, as well as scoring:

    Format-Hide. You can also hide formulas.

= 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. Then (Service) protect the sheet.

To do this you need:

    using the menu Format deprive protection of those cells in which the answer and reaction are entered, as well as scoring:

    select the column headers whose cells contain 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";IF(C9=2,"2" ;" ")))))

Save the program.

The function of such a test is reinforcing - training, rather than controlling.

However, it is possible to give the test a semblance of rigor.

    Let's go to the "Result" sheet.

    In the appropriate place in the window that opens, make the heading “Test results”. To do this, you can prepare a cell in advance by merging cells: select the cells to be merged, execute the following commands: Format – cells – alignment, check the box next to merging cells and click OK.

    Place “Number of points” in cell A3 and “Score” in A4.

    Let’s make a choice: in cell B3 we type “=SHEET1C9”, and in B4 we type “=SHEET1C11”.

To do this you need:

    select the column headers whose cells contain a check and click Format-Hide. You can also hide formulas.

    select the cells of SHEET 1 “Your rating” and “Number of points” and click Format-Hide.

    Then (Service) protect SHEET2.

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.


I am often asked to do a test on 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.

Working with the test using Excel

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:
If the cell says:

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.