Test out of five questions on Excel. How to create a test in the Excel program

Often they are asked to make an Excel test. This is a test for company employees or for friends and acquaintances.

As a rule, please adds to several conditions:

1) need to check a colleague, friend or relative for knowledge Excel

2) the test should not be simple or too complicated

4) need to affect all the main topics of Excel

5) There must be able to quickly change the macro or the test mechanism, add or change the questions, etc.

The article presents a file with a test, as well as a small description of this test.

Work with Excel test

Download Test

Open the file and first turn on the macros, because Calculation occurs with VBA. How to do it.

Then open the file and delete the correct answers to each of the sheets Question1 - a question 10.

To properly go through the test follow the instructions:
If the cell indicates:

1. "Enter the correct answer" - you need to enter the number or text in the field

2. "Select the correct answer option" - Slip 1 or another symbol in the cell nearby

After passing the test on the sheet count, click the Calculate button, see the picture at first.

The assessment is calculated in line 17.

EXCEL quiz 4 of 5 complexity, so the result of the test is 3.5 (that is, 7 replies out of 10) speaks of good knowledge of MS Excel, from 4 to 6 the answer speaks of a satisfactory knowledge of the program.

P.S. For those who want to change the VBA macro - it is specifically written enough simple language. Who has an idea of \u200b\u200bprogramming, I think easily will figure out in the written. The main function of the macro made is to calculate the correctness of the answer, calculate the number of faithful and wrong answers and raise the assessment.

Good luck in the passage of the test! ;)

With other works you can find.

Multimedia Interactive Resource " Microsoft Excel. 2013. Evaluation test "Designed to test the level of knowledge of employees by microsoft program Excel 2013. The total number of evaluation test questions is equal to one hundred, of which, during testing, 56 questions are chosen randomly, which should be answered. At the end of the test, the number of scores on each topic (from 0 to 100) and the test as a whole (from 0 to 100), as well as the final score, appears on the monitor screen.

The evaluation test includes questions on the following topics:

Formulas in Excel 2013

Advanced input and editing formulas. Working with features. Fittings on cells, absolute and relative addressing. Work with reference to tabular data. Names, named ranges

Working with tables

Working with tables. Sorting data. Sorting data on multiple criteria. Sort by color cell, font color or icon. Sort by customizable lists. Data filtering. Autofilters. Work with an extended filter. Combination of conditions when working with an extended filter. The use of formulas and templates in an extended filter.

Data visualization

Instant conditional formatting. Setting conditional formatting. Conditional formatting. Conditional formatting rules based on formulas. Creating infocal and working with them.

Working with diagrams

Fast creation of charts. Acquaintance with some kinds of diagrams. Setting the elements of the chart. Work with rows of data. Processing missed data. Planks of errors. Trend lines. Combined diagrams.

Numeric formats and data verification

Numeric formats and simple formatting. Cell format dialog box. Creating custom numerical formats. Examples of numeric formats. Criteria for data verification. Creating lists. Formulas for data verification. Creating dependent lists.

Consolidated tables

Creating a pivot table. Formatting. Cuts. Creating a timeline. Setting up a consolidated table. Calculations in the pivot table. Grouping data. Panel "Fields of the Pivot Table". Sorting data in a pivot table. Filtering summary tables. Field calculated and calculated items. Consolidated charts. Conditional formatting of summary tables.

Data analysis

Working with the selection of the parameter. Solving mathematical equations. Work with mortgage loan. Table of data with one input. Table of data with two inputs. Creating and removing scripts. Report on scenarios. Combining scripts. An example of using the analysis "What, if". A simple example of finding a solution. Solving a system of linear algebraic equations. Transport task. Production task.

Create an Excel test You can simply. A function in Excel "If" will help us. To understand the "If" function, read the article "The" If "feature in Excel.
How to create an Excel test.
Answers to test questions will choose from the drop-down list. How to make a drop-down list, see the "Excel drop-down list" article. An even simpler version of the drop-down list will be suitable for the test. Read "How to make a drop-down list in Excel without a list."
So, creating test B.Excel.
We have a table with questions in column A and drop-down lists with answers in column V. In the column with writing an estimate of the selected response. In the C2 cell we write such a formula. \u003d If (B2 \u003d 60; 5; 0)
Explanation of the formula: If in the B2 cell there is a number "60", then this is the correct answer, for it we put "5 points". If the answer is not correct, then deliver "0".
The B3 cell wrote such a formula. \u003d If (B3 \u003d 30; 5; 0) it turned out such a table. We consider the amount of scored points. In the last cell of the column with establish the formula "Avosumn". We in the C4 cell put the formula of autosumum. \u003d Sums (C2: C3)
Now you need to evaluate the result of the test depending on the number of points received. For example, we will evaluate on such a scale.
10 points - rating "5",
5 points - rating "3",
0 points - rating "0".
In the B4 cell we write such a formula. \u003d If (C4 \u003d 10; 5; if (C4\u003e \u003d 5; 3; if (C4\u003e \u003d 0; 0)))
It turned out such a table.
Now we place a questionnaire, test. Insert cells for the name, other data. We hide the column of S. highlight the font, color, the boundary of the cells, etc., sections of the test, the result of the dough, etc. It turned out such a test.
If we deliver all the answers correctly, then the test result will be.
It turned out 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 embodiment, there are words in the drop-down list.
Then in the G16 cell we will write such a formula. \u003d If (F16 \u003d "Yes"; 5; if (F16 \u003d "No"; 0)) If the tests put the answers not in words, and signs, the result is totable, too, you can.
What ways to do it, look in the article "

Electronic tests are currently widely used as in educational institutions When checking knowledge of students and enterprises when checking the competence of employees. The electronic test is convenient because the computer immediately displays the assessment or test result. There are different software To create electronic tests.

One of the most simple options is to create a test using electronic excel tables. To create a test, it is necessary to have a test and testing system for testing results.

Consider creating a test on the example of creating a test for schoolchildren studying a hypertext markup language. Below in the figure shows a fragment of the test of the already decorated on excel sheet, It is necessary to configure the automatic issuance of results for it.

1) Opposite each question there is a cell to enter a response option, for these cells we specify the following properties:

• install the external borders: The main - Font - borders - External borders ;
• remove the Cell protection - click right-click on the selected cell and select the item in the pop-up menu Format cells ... , in the dialog box Lists Select the tab Protection , remove a tick before the option Protected cell. .

These items perform for all cells in which data is assumed - these are cells for entering personal data and cells to enter the selected answer to the question.

2) Install the sign of the correct answer. In the next cell next to the input cell input option (in the example of the cell F4.) We enter a formula that displays "1" with the correct answer and "0" with an incorrect response. To enter the formula, you can use the wizard of the functions or enter the formula manually in the cell, in this example Use the function \u003d If (E4 \u003d 2; 1; 0):

IN logic expression E4 \u003d 2, comparing with "2", since the correct version of the answer is the correct answer and if 2 will be administered in the E4 cell, then in the F4 cell the result will be "1", otherwise - "0". This feature is copied opposite each cell entering an answer option, change only the number of the correct answer.

3) We count the scored number of points. At the end of the test, we summarize the values \u200b\u200bon the column with a sign of the correct answer. At the end of the test, under all the answers, we count the number of units (correct answers) We enter a function Sum. For the example above in the cell F37we enter \u003d Amounts (F4: F36) .

4) We estimate the test result in accordance with the scored scores. In this example, the following conformity scheme was adopted:
10 points - rating "5",
9-8 points - rating "4",
7-4 points - rating "3",
Less than 4 points - rating "2".
For accepted estimation, introduce the formula: \u003d If (F37 \u003d 10; 5; if (F37\u003e \u003d 8; 4; if (F37\u003e \u003d 4; 3; 2))) In cell. E37.

Instead of accepted school estimation, you can use text equivalent - "excellent", "good", etc., then the formula will look like this: \u003d If (F37 \u003d 10; "Excellent"; if (F37\u003e \u003d 8; "Good"; if (F37\u003e \u003d 4; "Sat."; "Nobood."))).

5) Hiding signs of correct answers. Column F.In which the functions issuing a sign of the right answer are listed that he is not visible to someone who passes the test. To do this, perform the following - click right-click on the column header, select the item in the pop-up menu Sweat .

Tests on the interview when admission to work are primarily necessary in order for, with a large influx of those who want to settle on some position, to select the most worthy candidates, with which an individual interview can already be carried out.

After all, if such an interview spend with each candidate, then many companies, especially those who do not have a huge amount of money for the personnel department, simply do not have enough resources. Therefore, the tests on it are allowed.

First of all, these tests should show human intelligence, his psychological state, as well as the basic knowledge of what he will have to work with.

First of all, the most needed, first of all, the test for some of the programs with which the applicant will have to work in case of successful completion of tests and interviews.

Of course, tests cannot be the only sources of information about the candidate for the position. It should also be taken into account biography, experience with previous places of work and education, if there is.

Of course, with decent applicants it will be necessary to hold separate individual interviews. However, in this article we will focus, first of all, on tests.

Types of tests

Now you should describe some categories of tests that are most useful and distributed.

IQ tests

Tests on IQ on an interview, as well understood from the title, are needed to measure the level of intelligence of the applicant.

They are particularly popular when taking the posts on which first of all the intellectual abilities of a person are required.

In general, you do not need to invent some sly, but just just take advantage of the book with tests for the authorship of one of the most important popularizers of IQ tests - Aizenka.

The only drawback of this classic test will be that if the applicant has already passed it, then some questions may coincide that it will give him some advantage over other candidates.

But if you really have been afraid of this situation, you can spend a test on the technique of Rudolf Amthauer, who will not only determine the intelligence of the candidate, but also establishes his tendency to technical or humanitarian sciences, and also indicates whether a person is suitable for your position.

What are the logical questions and tests on the interview, read.

Psychological

Test Luchera It assumes the choice of the most pleasant color and usually helps measure stress resistance, conflict, as well as human communicative abilities.

Mathematical

For specialists such as accountants or economists, the ability to count is simply necessary.
Here, mathematical tests come to the residue.

This test is one of the simplest for a person conducting an interview.. He, in essence, it does not require almost nothing.

It is enough just to find several dozen tasks for work with charts, as well as on arithmetic actions (including with fractions and interest), after which it is possible to distribute the tests to applicants and check the result.

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

But despite its simplicity, these tests are extremely accurate and indicative tests for attentiveness. In general, tests at the interview for attentiveness are particularly popular when selecting many professions, where this character is necessary.

Mathematical test for interview, example.

In detail about what mathematical, logical and analytical tasks are asked for an interview and how to answer them, we told in.

Computer

Almost all professions today are somehow connected with the work on the computer. The main skill under such conditions is becoming possible to navigate the programs necessary for the profession, as well as print at a decent speed.

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

After that, you should give a candidate task for measuring the print speed. Make it can be visually, if you are not so important accurate indicators or using special programs To measure the velocity of the text set. In any case, if a person is almost no oriented in the keyboard, you will definitely notice.

The most frequently used test from this sphere is a test for Excel's knowledge at the interview. To prepare it, you can go online on specialized sites or download on our.

Professional

Professional fitness tests are one of the most difficult for both parties, which are involved in them.

The applicant, especially if he is not particularly confident, is worried almost with any check of his skills.

The excitement of the employer is also due, first of all, the need to make an accurate test that can show all the problematic places in the knowledge of the candidate for the position.

Examples of questions even makes no sense to list, because for each profession there will be their specific tasks and tests when interviewing work.

The only advice that can be given: Make a test with an answer options and add a field in which the applicant will be able to write, why he chose one or another answer.

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

In detail how on the interview is testing sales manager and how to answer, you can find out.

For sponderfulness

Various tasks and test tests when admitting to work are an excellent chance to understand whether a person has the ability to think quickly and creatively.

However, it is not necessary to get involved in this, because for a person who is arranged for a position that does not require non-standard thinking or some incredible creativity, these tests will not clear anything worthwhile.

Another problem lies in the use of famous questions from the Internet to which people can prepare and, accordingly, prevent the assessment of their seamless and intelligence.

Examples of tests when receiving work for a wide variety of specialties can be found.

The meeting of the candidate with an employee of the personnel department will be successful if the preparation of both parties will be held before it. On our site you will find detailed explanations on how, to answer and questions such as: or why you chose our company; from the previous job.

Do I need to prepare for them in advance?

If you just go to the interview, expecting that there may be a test, you should have a little prepared for them.

At a minimum, you should be ready emotionally so that in the event of a particularly difficult question is not to be confused and be ready to think about it to the end of the time allocated to you.

You should also find some who may be extremely often found, after which you will conceive a little on them by checking your answers (you can see the answers to questions on the interview when receiving a job you can).

This will help you become more confident at interview using tests. In addition, the question you already "passed" may well be asked.

How to behave when testing?

If you are a applicant, then you will need to behave restrained and calmly. Try not to be distracted, focus on questions or tasks to which you will need to find a solution. If there are difficult, skip them and return to them at the end.

Sometimes it may be that questions too much for the time allocated to you. Do not panic! It may be a trick to check your stress resistance.

In the event that you interview, then try not to distract and not scare applicants.

Be restrained and calm, but friendly; Never express emotions regarding the results of testing a person or another, as it can unwind it.

Assessment of results

When evaluating tests, try to be as careful as possible. In fact, in the case of most tests, you do not need to do something, except for comparing the answer options in the applicant's blank with the correct answers in the key.

Do not forget that this is not the final stage and you will also need to have an interview, but with people who have already shown themselves when testing. What are there, and also in detail are presented in our materials.

And the most diverse tests on the interview with the answers can be found on the Internet, including our.