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

WBOY
Release: 2024-01-24 11:18:05
forward
4342 people have browsed it

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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

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!

Related labels:
source:docexcel.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!