How to use formulas in Excel to reference data from another worksheet

Excel refers to data in another worksheet in one worksheet

For the purpose of explanation, the editor specially created the data table as shown in the figure:

In order to calculate the total sales volume in the first quarter, we need to accumulate the data at the corresponding positions in the other three tables. The steps are as follows:

Enter "=sum("" in cell "C3" of the "First Quarter Sales" worksheet, and then click the "January Sales" worksheet.

Then click on the corresponding C3 cell, then enter a "comma" and click on the "February Sales" worksheet.

Click the "C3" cell in this worksheet, enter another "comma", and click the "March Sales" worksheet.

Click the "C3" cell in the "March Sales Volume" worksheet, then enter ")" and press Enter to complete the reference to the corresponding location data of the first three worksheets.

Then you can see the calculation results in the "First Quarter Sales Volume" worksheet.

Copy this formula to other cells to complete the reference and calculation operations on the worksheet data.

How to directly reference data from another table in Excel

excel references other table data

Product table 1, see the product code

Product list 2, see product code

Copy the "Title" columns of the other two categories in product table 2 to product table 1.

Then use the formula below to quote.

Select the cell in product table 1, then click fx above, select all, find vlookup. Click OK

About Vlookup function content analysis:

Lookup_value "unique value" is the relational value that needs to be found in the first column of the array for reference. It can be a numerical value or a text string. For example: the product code column in the example is usually the value in the first column, which is used as the only data reference. (in this form)

Table_array "area": ​​numerical area, such as "A:D", is all the data you want to quote (the table you want to quote data)

Col_index_unm "Column number": refers to the first column in the area value. For example, there are 4 columns from A to D. If I want to quote the value of column B, then select 2, because column B is in the second column of the area. The same is true for others

Range_lookup "logical value": generally fill in TRUE or FALSE. Refers to whether the function VLOOKUP returns an exact match or an approximate match. Usually it is written false.

After the first cell is successfully referenced, pull it down (when the lower right corner of the cell becomes solid, pull it down again, the meaning is the same as above.)

For the brand in the other column, follow the above method. Repeat steps 4 and 5. You can also directly insert and copy the paste operation of the first column. You just need to change the third value of the second column to 2. The third column 3.. complete and then pull down the formula.

7

The displayed effect is as shown below, which combines the data from two tables. The data in the example is not very large, but when the data is huge, this method is very suitable.

How to use excel to reference data from other tables

How to use excel to reference data from other tables

Product table 1, see the product code

Product list 2, see product code

Copy the "Title" columns of the other two categories in product table 2 to product table 1.

Then use the formula below to quote.

Select the cell in product table 1, then click fx above, select all, find vlookup. Click OK

About Vlookup function content analysis:

Lookup_value "unique value" is the relational value that needs to be found in the first column of the array for reference. It can be a numerical value or a text string. For example: the product code column in the example is usually the value in the first column, which is used as the only data reference. (in this form)

Table_array "area": ​​numerical area, such as "A:D", is all the data you want to quote (the table you want to quote data)

Col_index_unm "Column number": refers to the first column in the area value. For example, there are 4 columns from A to D. If I want to quote the value of column B, then select 2, because column B is in the second column of the area. The same is true for others

Range_lookup "logical value": generally fill in TRUE or FALSE. Refers to whether the function VLOOKUP returns an exact match or an approximate match. Usually it is written false.

After the first cell is successfully referenced, pull it down (when the lower right corner of the cell becomes solid, pull it down again, the meaning is the same as above.)

For the brand in the other column, follow the above method. Repeat steps 4 and 5. You can also directly insert and copy the paste operation of the first column. You just need to change the third value of the second column to 2. The third column 3.. complete and then pull down the formula.

The displayed effect is as shown below, which combines the data from two tables. The data in the example is not very large, but when the data is huge, this method is very suitable.

How to reference data from one worksheet to another sheet in Excel

1. First, we need to add two worksheets to the same Excel sheet. In particular, the methods described below must be available in both worksheets in the same sheet. I named the first table: the referenced table, and the second table: the referenced table.

2. Enter "= the name of the reference table", as long as the data appears in the second table after the reference position! You need to reference the location of the data. For example, if you need to reference the numbers in the first table, you would enter: = Reference table! A2.

3. After completing the above steps, the returns will be numbered after the reference appears.

4. Similarly, enter the appropriate location: = Reference table! B2.

5. Click "Enter" and the referenced name will be displayed.

6. Similarly, you can also refer to the date of birth in the first table.

7. Follow the above method, and finally you can complete the referenced table

The above is the detailed content of How to use formulas in Excel to reference data from another worksheet. For more information, please follow other related articles on the PHP Chinese website!