EXCEL, HOW TO RE ENTER DATA BASED ON RAW DATA AND CODING TABLES 50

1. Open Excel** first.

in the raw data**.

2. Then make a coding table that needs to extract the corresponding data from the original data**.

3. Click on the cell that needs to extract the data and insert the vlookup function, enter the parameter data as shown below, and convert it into the formula: =vlookup(g2,$a$2:$e$7,2,0).

4. Click Enter and pull down the formula to generate the data of the gender column of the corresponding name.

5. Click on the other cell to be extracted, insert the vlookup function as well, enter the parameter data as shown below, and convert it into the formula: =vlookup(g2,$a$2:$e$7,4,0).

Enter a product number in one cell, and automatically fill in the other cell with the ** I originally set** can be matched using the vlookup function.

Software tool: Excel 2013

1. First of all, you need to establish a set ** database. To facilitate management, create a worksheet named "Raw Data". And enter the set **. The preset data number corresponds to the row corresponding to the physique.

2. Return to Table 1, assuming that A2 is the input and B2 is the output display. Enter =vlookup(a2, raw data!) in b2. a2:

b17,2,false). ps: The snapshot is for the convenience of answering, comparing and intercepting snapshots in the original data.

3. Enter the preset number in A2. B2 will automatically display the preset ** in the original data table.

Formula Explanation: 1. Function Formula: vlookup(A2, raw data!) a2:b17,2,false)

2. [A2] indicates the value to be found. That is, the cell where the user enters the number.

3. [Raw data!] a2:b17] indicates the region in which you want to find the value. To do so in the "Raw Data! a2:b17" looks for the value of "a2" here.

4. Indicates the column number in the region that contains the return value. Indicates that you want to match the "raw data!" A2:B17".

5. [false] indicates an exact match, and the number in the original data is exactly the same as the number entered by the user.

What to enter? Recorded**?

Upload a screenshot with a data example with row and row numbers, and understand what the known conditions and results are, so that we can give you the answer.

To be on the safe side, I usually save another copy. If you press CTRL+S easily, it will be a big problem. After all, I'm used to ctrl+s

Don't save, close and exit!

Reopen it and it's the same data.

First, manually enter the corresponding relationship table in columns e to f, and then enter the formula in b1 to drop it down.

B1 formula: if(a1="","",vlookup(a1,e:f,2,))

Use the vlookup function.

Now establish a correspondence in a table, for example, the number is 1234 **10;No. 1235 **20

Then in another table corresponds to ** vlookup(a1,sheet2!a:b,2)

Method 1: Use "Pivot Table".

Steps: Select data.

Click on the "Insert" tab.

Select Pivot Table

In the Row tab, select the first column.

In Numeric Value, select another column (the one that needs to be summed) and set it to "Sum Item".

Method 2: Use Formulas.

Steps: Select the first column of data.

Copy to a new 1 column (e.g. column c).

Use "Remove Duplicates" to deduplicate (click on the "Data" tab, click on "Remove Duplicates").

In cell D1 in column d, use the formula: =sumif(a:a,c1,b:b) formula to pull down to get the sum.