The EPR function in Excel allows data from one table to rearrange the second cells. Her English name is VLookup.

Very comfortable and frequently used. Because Match manually ranges with tens of thousands of items is problematic.

How to use the FPR function in Excel

Suppose, the enterprise for the production of containers and packaging was received by materials in a certain amount.

The cost of materials is in the price list. This is a separate table.

It is necessary to find out the cost of materials enrolled in the warehouse. For this you need to substitute the price from the second table in the first. And through ordinary multiplication, we will find the desired.

Algorithm of actions:

Click OK. And then "spread" the function throughout the column: cling the right lower corner and pull down. We get the desired result.

Now finding the cost of materials will not be difficult: quantity * price.

The ERP function tied two tables. If the price is changed, the cost of the materials received to the warehouse (received today) will change. To avoid this, use the "special insert".

1. We highlight a column with inserted prices.
2. Right mouse button - "Copy".
3. Without removing the selection, the right mouse button is "Special Insert".
4. Put a tick opposite the "values". OK.

The formula in the cells will disappear. Only values \u200b\u200bwill remain.



Quick comparison of two tables using PRD

The function helps to compare the values \u200b\u200bin huge tables. Suppose the price has changed. We need to compare old prices with new prices.

The data presented in this way can be compared. Find a numerical and percentage difference.

PRD function in Excel with multiple conditions

Until now, we have proposed only one condition for analysis - the name of the material. In practice, it is often necessary to compare several ranges with data and select a value of 2, 3rd, etc. criteria.

Table for example:

Suppose we need to find at what price corrugated cardboard from OAO East was brought. You need to set two conditions for searching for the name of the material and by the supplier.

The case is complicated by the fact that several items come from one supplier.

Consider the formula in detail:

1. What we are looking for.
2. Where we are looking for.
3. What data take.

PRD and drop-down function

Suppose some data from us are made in the form of a drop-down list. In our example - "Materials". You must configure the function so that the price appears when choosing the name.

First, we will make the drop-down list:

Now you need to make it so that when choosing a certain material in the column, the price appeared the corresponding figure. We put the cursor in the E9 cell (where the price should appear).

1. Open the "Master of Functions" and choose the PRD.
2. The first argument is "the desired value" - a cell with a drop-down list. Table - range with materials titles and prices. Column, respectively, 2. The function acquired the following form: .
3. Press the input and enjoy the result.

We change the material - the price changes:

So the drop-down list in Excel works with the PRD function. Everything happens automatically. Within a few seconds. Everything works quickly and efficiently. You just need to deal with this feature.

EXEL editor is a very powerful program for working with tables. Sometimes it happens that you have to work with a large amount of data. In such cases, various information search tools are used. The "EPR" function in Excel is one of the most sought-after for this purpose. Consider it more carefully.

Most users do not know that the abbreviation "PRD" is decrypted as "vertical viewing". In English, the function is called "VLOOKUP", which means "Vertical Look Up"

How to use the function

In order to understand how this tool works, the following steps must be performed.

1. Create a table at which you can make any search for information.

1. Add a few fields that will be used to demonstrate formulas.

1. In the field "The desired surname" we introduce some choice of those that are in the table.
2. Then go to the next cell and call the "Function Insert" window.
3. Choose the category "Full Alphabetical List".
4. We find the function of the "PRD" function. To continue, click on the "OK" button.

1. Then we will be asked to indicate "function arguments":
   • In the "Software Expression" field, we specify a link to the cell in which we wrote the surname we need.
   • In order to fill the "Table" field, it is enough just to highlight all of our data using a mouse. The link will set automatically.
   • In the column number, you specify number 2, since in our case the name is in the second column.
   • The latter field can take the values \u200b\u200bof "0" or "1" ("lie" and "truth"). If you specify "0", the editor will look for an exact coincidence of specified criteria. If "1", then during the search will not take into account complete coincidences.
2. To save, click on the "OK" button.

1. As a result, we got the name of Tomara. That is, everything is correct.

Now you need to use the same formula for other fields. Simple copying of the cell using Ctrl + C and Ctrl + V will not suit, since we use relative links and each time the column number will change.

In order for everything to work correctly, you need to do the following:

1. Click on the cell with the first function.
2. Go to the formula input string.
3. Copy the text using Ctrl + C.

1. Make an active next field.
2. Go back to the formula input string.
3. Click on the hot keys Ctrl + V.

Only in this way the editor will not change the references in the function arguments.

1. Then change the number of the column to the desired one. In our case, it is 3. Press the ENTER key.

1. Thanks to this, we see that the data from the Year of Birth column was determined correctly.

1. After that, repeat the same actions for the last field, but with the adjustment of the number of the desired column.

The principle of operation of this function is that Excel is looking for a fragment in the area you specified and then can return any value from this row. You only need to specify the sequence number of the column in the specified range.

That is, the numbering begins not from the beginning of the sheet, but from the beginning of the specified area of \u200b\u200bthe cells.

How to use the "PRD" function to compare the data

We will demonstrate this feature using two tables. Imagine that we have updated information about employees on our second sheet. Our task is to find out what exactly has changed. To do this, you will need to do the following.

1. Add a second sheet with exactly the same table (copied using hot keys Ctrl + C and Ctrl + V).
2. We will increase the interns to the "junior employee." This information will be the starting point for comparison.

1. Add another column to our old table.

1. Go to the first cell of the new column and enter the following formula there.

\u003d Arm ($ B $ 3: $ B $ 11; List2! $ B $ 3: $ E $ 11; 4; false)

It means:

• $ B $ 3: $ B $ 11 - All values \u200b\u200bof the first column are used to search (absolute links are used);
• Leaf2! - These values \u200b\u200bneed to be signed on a sheet with the specified name;
• $ B $ 3: $ E $ 11 - a table in which you want to search (cell range);
• 4 - the column number in the specified data area;
• Lies - look for accurate coincidences.

1. New information will be displayed in the place where we indicated the formula.
2. The result will be the following.

1. Now duplicate this formula into the rest of the cells. To do this, you need to pull in the mouse over the right lower angle of the original cell.

1. As a result, we will see that the formula written by us works correctly, since all new posts were copied as expected.

Now we can easily determine if there were changes in the records of which employee.

The only minus of this feature is that "PRD" cannot work with several conditions.

In such cases, you have to use various additional columns that combine information from several columns. And it looks ugly and not quite comfortable.

Function "PRD" and drop-down lists

Consider examples of using these two tools at the same time. To do this, you need to perform the following steps.

1. Navigate to the cell in which the surname is selected.
2. Open the Data tab.
3. Click on the specified tool and select "Data Check".

IN Microsoft Excel. Very many different functions that greatly facilitate the work of the user, and in this article we will talk about one of these. It is called HDP, and if in English, then vlookup.

The PRD function transfers the values \u200b\u200bfrom one table to certain cells of the other. Let's explain more - in the first table you choose the value you want to find in the left column of the second. If there are coincidences, the PRD transfers the value from the column you specified in the first table. Although the definition is a bit confusing, it is not so difficult to use the function. Consider several examples.

Since the function is most often used to fill the column with the price, which is indicated in separate prices, we will take next example. For example, there is a table with fruit (orange), where for everyone it is indicated how many kilograms we want to order. Accordingly, each fruit needs to record the price from the price (green), which gave the supplier, and then calculate how much the purchase costs. View each name and to transfer the data is difficult, especially if there are thousands with thousands of goods, therefore we use an arrangement.

Press the upper cell in the first table in the price column, and then the "FX" button in the formula row to open the functions wizard window.

The next thing we do is register the arguments in the proposed fields.

Put it in the field "Fiction" And allocate the value in the first table that we will look for. I have this apple.

In the "Table" string, you must select the one from which the data will be taken - do not select the cap. Note that the left left column should consist of the values \u200b\u200bwe are looking for. That is, I need an apple and other fruits, it means that their list should be in the left column of the area allocated.

To after we write a formula and grow it throughout the column, the selected range did not displaced down, you need to make links absolute: Select the data in the field and click "F4". Now the address on the cells became absolute, the "$" sign was added to them, and the range will not shift.

Where the column number, put the digit corresponding to the second table to the column, the data from where you want to transfer. My price consists of fruits and prices, I need the second, so I put the digit "2".

IN "Interval_Prising" We write "Lie" - if you need to search for accurate coincidences, or "truth" - if the values \u200b\u200bcan be approximate. For our example, choose the first. If you do not specify anything in this field, the default is the second. Then press OK.

Here, pay attention to the following, if you work with numbers and specify "Truth", then the second table (this is our price) must be sorted by increasing. For example, when searching 5,25 there is 5.27 and data will be taken from this line, although there may be a number 5,2599 below - but the formula will not look next, because it thinks that below the number is only more.

How does an agency work? It takes the desired value (apple) and is looking for it in the extreme left column of the specified range (a list of fruits). When coincidence, the value is taken from the same line, only the column, which is specified in the arguments (2), and is transferred to the cell we need (C2). The formula looks like this:

PRD (A2; $ G $ 2: $ H $ 12; 2; lies)

Now you can stretch it on the required number of lines down by pulling over the right lower angle.

All prices are transferred from the price list to the purchasing table in accordance with the names of fruit.

If you have in the first table there are no product names that are not in the price list, I have vegetables, then in front of these items of the PRD formula will give an error # n / d.

When adding columns to the sheet, the data for the "Table" argument function will automatically change. In the example, the price is shifted on 2 columns to the right. We highlight any cell with the formula and see that instead of $ G $ 2: $ H $ 12 now $ I $ 2: $ J $ 14.

Now let's figure it out how to work with the function of an HDP in Excel and with drop-down lists. First you need to make the drop-down list. Select the cell, at the top open the "data" and click on the button "Data checking".

In the "Data Type" window that opens, there will be a "list" below, below indicate the source area - this is the names of the fruit, that is, the column that is in the first and in the second table. Press "OK".

Select F2 and insert the PRD function. The first argument is the list of (F1).

The second is the supplier table with the specified prices. Remember that here the left column must coincide with those data from which the drop-down list consists.

It turned out something like search: choose a fruit and an arrangement finds its price in the price list.

If you had some prices, and then suppliers gave a new list, then you need to somehow see what has changed. It is manually to do this for a long time, but with the help of the function in question, everything can be done very quickly. You will need to add one column and transfer new values \u200b\u200bto it, and then simply compare the data.

Click on any cell in the D column and insert one new one.

I called his new price - here will be new prices, and in the left, the old one is indicated. The new table is on my other sheet, so that you become clear how to use an HDP, if the data is located on different sheets. In the added column, we highlight the first empty cell.

Insert the function and indicate the arguments. First, what we will look for, in the example of an apple (A2). To select a range from the new price list, put the cursor in the "Table" field and go to the desired sheet, I have "list1".

We highlight the necessary columns and rows, without headers.

Further make absolute links to cells: "List1! $ A $ 2: $ B $ 12". Highlight the line and click "F4" to add a dollar sign to the cell addresses. We specify the column (2) and write "false".

In the end, click the "OK" button.

Now two columns with a new and old price are located nearby and you can make a visual comparison, or applying certain formulas, or conditional formatting.

I hope I got step-by-step instruction On the use and use of the FPR function in Excel, and now everything is clear to you.

Rate article:

(14 estimates, average: 5,00 out of 5)

Webmaster. Higher education in the specialty "Information Protection" .. The author of most articles and computer literacy lessons

All-Russian check works as a form of testing of knowledge of school students were first conducted in 2015 - then they were written by fourth graders. These test works are aimed at the development of a single educational space in the country and the formation of uniform learning standards: evaluation criteria are also united for the whole Russian Federation. The criteria are sent to each school that are the same for the entire territory of the state, and in accordance with them, the work of schoolchildren is inspected.

ARP is not an analogue of the exam, since they are created with a completely different goal. That is, the assessment received by the schoolboy is not a final assessment for the entire period elementary school and does not play a decisive role in further fate and educational activities Child.

On the transition of the student in the following class results also do not affect. But if the student received 4 or 5, the teacher can focus on it as an indicator of knowledge and in the case of a controversial assessment in a quarter / trimester put the score above.

Where is carried out and checked?

Arms are held at school and are checked here - unlike the USE, the work is not borrowed outside educational organization To verify. However, the results can be used to set a summary assessment on the subject, if such a decision is made by the management of the school. Works are checked on the same day - the school is obliged to convey their results into a single federal form of a maximum of a day after their conduct.

What can be used on the UPR?

Use the phone during the exam is not allowed, but you can have it with you. The state does not provide for the general standard about how work is written with the help of written accessories - unlike the final attestation work of OGE and OGE, where you can only use black handles. But, since the All-Russian checks are conducted by the school, the decision on this issue takes its leadership - they can say to write a pen, and can allow the pencil to use.

Nevertheless, one general restriction still has: during the implementation of the Armediary, it is prohibited to use dictionaries, textbooks, notebooks, reference materials. You can have drafts - records made in them are not checked and not evaluated.

How long does the UPR last and when is it carried out?

PRD are performed for one lesson - for forty five minutes. In essence, this is an ordinary test work, the same as those that are always regularly held in schools. The only difference from traditional test work is that the tasks and criteria for their verification are all-Russian. They can be carried out both in spring and autumn. If the child sick and missed the writing of the work, then you do not need to write work at another time.

Who makes it up?

Options are drawn up by qualified specialists and reflect those aspects curriculumwho have the greatest importance for general educational training.

In 2015, Russian students of the fourth grades first wrote a PRD (All-Russian inspection work) in the Russian language and mathematics, in 2016 it was added to them by the Armed Mire.

What is a PRD?

What do they differ from the exam and why are they needed?

What is a PRD?

PRD applies to assess the quality of school education.

VDR in its essence - this is the final test papers According to the most important items of primary school. Invented them in order to soberly assess the level of training of graduates of the elementary school of all educational institutions Russia. Check whether the knowledge of schoolchildren meets the requirements of GEF. Find out which regions and schools give good education, and which are "for a tick".

Indpor fundamentally changes the attitude towards study. It is important to understand and assimilate each topic from grade 1. Previously, the school was only repetition and reproduction, now the system changes to the other side: its own reasoning, reflection, searching for the method of action.

Many compare the EPR and EGE. Of course, they are similar, because both are the test of schoolchildren's knowledge. But they still differ fundamentally. The exam affects the further fate of the graduate of the school, on his chances to enter a particular educational institution. ARP is not a state final certification and on the fate of the schoolchildren does not affect anybody, all graduates of graders grades are moving to high school, regardless of the results of the enterprise.

In 2019, as an experiment, it is proposed not to record the specific days of the All-Russian inspection work in grade 4, and to enable schools to carry out these works at a time convenient for them during the week.

As the leader explained Rosobrnadzor S.S. Kravtsov: "Such an approach will be implemented through the use of the formation technology of options from the banks of tasks." Individual sets of tasks will be formed for each school, which will eliminate the possibility of abuse.

In 2019, participation in the PRD 4, 5 and 6 classes will be mandatory, in 7 and 8 classes - voluntary. In 2020, the Arms will become compulsory and for 7-8 classes.

Which classes give apr in 2018-2019 academic year?

4th grade (required for all)
Russian language - April 15-19, 2019 (on any days of the week)
Mathematics - April 22-26, 2019 (on any day of the week.
The world around April 22-26, 2019 (on any day of the week)

Note. For 4 classes in the schedule of Arm for 2019, the experiment provides that the school itself chooses a convenient date and time for the designated week.

Grade 5.
History - April 16, 2019
Biology - April 18, 2019
Mathematics - April 23, 2019
Russian language - April 25, 2019

6th grade
Geography - April 9, 2019
History - April 11, 2019
Biology - April 16, 2019
Soclastology - April 18, 2019.
Russian language - April 23, 2019
Mathematics - April 25, 2019

Grade 7 (in approbation mode)
English - April 2, 2019
Social Studies - April 4, 2019
Russian language - April 9, 2019
Biology - April 11, 2019
Geography - April 16, 2019
Mathematics - April 18, 2019
Physics - April 23, 2019
History - April 25, 2019

Grade 11
Foreign languages \u200b\u200b- March 5, 2019
Geography - March 12, 2019
History - March 13, 2019
Chemistry - March 14, 2019
Physics - March 19, 2019
Biology - March 21, 2019

Note. For students of the 4th, 5th and 6th grades, the writing of the Arbitration is obligatory, in the 7th and 11th grades of the Arbitration rate are held by school.

Form of HDD.

ARP is carried out in the form of test work. By performing the tasks of the PRD, schoolchildren cannot use any auxiliary materials. MPD tasks are developed on federal levelTherefore, uniform materials are used for testing and unified evaluation criteria for academic achievements throughout the country.

Where are the UPR?

All HDPs are held in the same school who visits the student.

What are the results of the RMD?

On the one hand, an arbitrariness does not affect anything. These are simply test work necessary to assess the level of preparing a particular student and eliminate existing gaps in knowledge. Neither further teaching the child, nor on his final certification at the end of the year, nor on the receipt of it in the university, the results of the ERP do not affect. Even Rosobrnadzor does not recommend using the results of the PRD to place the annual stupid marks. ARP in grades - this is not an "EGE for elementary school" because, according to the results of industrial complex, no compulsory decisions are made, important to determine the further fate or the educational trajectory of the schoolboy.

On the other hand, the results of HDP in 4 and in other classes can be part of the student portfolio. Of course, these results can only be taken into account at the request of the child and his parents. For example, when moving to another school.

Why do I need an arrangement?

Annual testing of students will allow:

• identify the shortcomings of the curriculum in different subjects;
• check the quality and level of knowledge given by a specific school, in comparison with the results of other schools in Russia;
• develop uniform educational standards for all schools;
• to give students the opportunity to prepare psychologically to the EEG (because Methods of Methods are similar to the exam, which means that the child will feel calmer, performing a similar work not for the first time);
• make schoolchildren do through each school year, not just in the graduation class.

In addition, the results of verification works are needed:

• schoolchildren and their parents, because they can appreciate how high-quality education gives a school, as well as identify gaps in knowledge;
• educators, as they will receive an independent assessment of the quality of their work;
• school directors. They, in turn, can also objectively assess the work of primary school teachers;
• departments of Education, because they will see weak schools. Special support measures are being developed for such schools.

How to prepare a child to the UPR?

Especially cooking a child to an ART is not needed. The most important thing is to tune in to a positive way, not to create a nervous situation, calm the child and explain that this is just a part of the educational process. Let him write what he knows.


What guides to choose to prepare for the EPR?

Workers' notebooks I. methodical manuals To prepare for an arrangement in the 4th grade now produce a lot. Mostly, this is a publication representing template tasks for demorality with a minimum of training moments. Therefore, we recommend to get acquainted with. Their peculiarity is that they are aimed at conscious generalization and systematization of knowledge learned by the child, skills, ways of action.