The indirect function is a versatile but often underrated function, and the magic lies in thatIt allows Excel to be instructed in one cell to get the values of other cells, which means you can passVariable parametersto flexibly reference cells, which is equivalent to adding to the function formulaSecondary columnsto add geometry questionsGuides。This kind of powerfulIndirect referencesThe functionality makes the indirect function easy and flexible to implementCross-table and dynamic referencesfor efficient data processing and computation.
But because of its indirect referencing characteristics, it also makes students who have just started learning indirect functions feel a little brain-burning, just like learning geometry and probability in middle school, which is a bit winding.
This article will analyze the direct function in detail by sharing many examples, its usage and skills, I hope it will be helpful for you to understand and use the direct function.
The basic syntax of the indirect function: =indirect (a reference to a cell, specifying the style of the reference to the cell).
The first argument is a reference to the cell to be referenced;
The second parameter is to specify the style of the cell reference, A1 style or R1C1 style, the default is A1 style.
1. Basic usage
The indirect function supports both constant and variable parametersConstant parametersDirect reference to the content, whileVariable parametersthen passedIndirect referencesAddress.
As shown in the figure below, indirect(a2), indirect("a1") and indirect("a"&1) The result of all three formulas is the value of cell A1"Name"。
indirect(a2), point to the value of cell A2 through the value of cell A1;
indirect("a1") and indirect("a"&1)，"a1"，"a"&1 is a constant parameter that directly references the value of cell A1.
2. One column to multiple columns
Direct can convert the data of a single column into multiple rows and columns by combining the row and column functions.
As shown in the figure below, convert the 6 data in column A into data in 3 columns and 2 rows, and enter =indirect( in cell B2"a"& row() 1) *3 + column()-1), and then fill in the formula according to the number of rows and columns you want to convert, and the number 3 can be modified to the number of columns you want to convert.
row() 1) *3 + column()-1 means the numbers 1, 2, 3, 4, 5, 6.
3. Cross-table query
Because Indirect's indirect referencing feature makes it very convenient to query across tables, beginners may not realize the importance of this feature.
As shown in the following figure, you need to query it"Zhang Guo"For the salary of 3 worksheets, enter the formula in cell C2 and fill it in:
vlookup($a$2,indirect(b2&"!a:b"),2,0)。
The convenience of this lookup formula is up to youYou don't need to go across tables to select a lookup region, the indirect function canReferences are implemented through the variables in column b
indirect(b2&"!a:b"The formula is equivalent to:'January'!a:b；
If there are spaces or other special characters in the worksheet name, you need to add two more"'", i.e.: indirect("'"&b2&"'!a:b"This formula is a bit more complex, but more intuitive;
The indirect function can also be referenced across workbooks, but the referenced workbook needs to be open;
For ease of presentation, put the worksheet"January"、"February"、"March"Take a screenshot of the main table together.
Fourth, the sum is found across tables
The combination of the direct and sum functions makes it easy to sum across tables.
As shown in the figure below, you need to find the total salary of each month, enter the formula in cell b2 and fill it down: =sum(indirect(a2&"!b:b"))。
You can also use the r1c1 pattern: c2=sum(indirect(a2&"!c[-1]",)) The second parameter is:"0"Can be omitted, though","It cannot be omitted.
5. Merge data from multiple tables
The indirect and row functions can quickly merge multiple ** data, as shown in the following figure, you need to merge the data of each month into the summary table, enter the formula in cell b2 and fill it in from right to bottom: =indirect(b$1&"!b"&row())
Because to populate the formula down, b$1 means that the first row needs to be locked.
6. Dynamic summation
As shown in the figure below, you need to find the cumulative salary amount of the current year according to the number of months in d2, and enter the formula =sum(indirect() in cell e2"b2:b"&match(d2,a:a,0)))
match(d2,a:a,0) finds the number of rows in column A according to the number of months in cell D2.
7. Dynamic matching data
Sometimes when making accounting entries, the numbers of the borrower and borrower need to match, as shown in the figure below, the numbers in column B are matched according to the misalignment of the numbers in column A, enter the formula in cell B1 and fill in the formula: =if(a2<>"","",if(row()>2*counta(a:a)-1,"",indirect("a"&row()-counta(a:a)+1)))
if(a2<>"","", formula), when column A data is not equal to empty, column B corresponds to cell data is empty;
if(row()>2*counta(a:a)-1,"", formula), when the number of rows corresponding to column B cells is greater than 2 times the data of column A, the data of column B cells is empty;
indirect("a"&row()-counta(a:a)+1), when the number of rows corresponding to the cells in column b is greater than 1 times and less than 2 times the data in column a, the number in column a is taken;
The data in column B is dynamically matched according to the amount of data in column A;
Achieving completely redundant and error-free data through formulas is one of the most important steps in making automated reports and templates.
8. Make a drop-down menu
1.To create a name for the source data region, select data region D1:E7, then select Formula - Create from Selected Content, and select First Row in the pop-up dialog box to set name management for the source data in the drop-down menu.
2.Set the drop-down menu, select cell B2, select "Data" - Data Validation, in the pop-up dialog box, select "Sequence", and enter the formula =indirect(A2) in the ** column.
9. Dynamic selection of charts
As shown in the image below, you can choose the type of chart you want to render according to the C2 cell.
1.Define the name, set the chart name for the chart area through the [Name Manager] function under the [Formula] tab, select the data area of the 4 charts in turn, and set the names as column chart, line chart, pie chart and chart.
2.Copy the region, select the area of any chart and copy and paste the link selectively to the position you want to render;
3.Define the name of the selected chart type cell, and create a new name through the Name Manager with the name as"Chart selection", reference position: =indirect($c$2), c2 corresponds to the cell you want to select **.
4.Set the drop-down menu, set the drop-down menu in cell C2, and the sequence is the name defined in step 1: column chart, line chart, pie chart, and chart.
5.Set the chart formula, as shown in the figure below, click the chart, and modify the formula in the edit bar to: = chart selection. Then you can select the type you want to render through the C2 cell.
In practice, it is recommended to store the presented report and the source chart in different **.
The power of the indirect function makes it an indispensable part of Excel, which allows us to refer to other cells in a dynamic way and even reference data across different worksheets. The indirect function is also one of my favorites, especially itsCross-table and dynamic referencesThe feature has played an unexpected role in making automated reports and templates, and I hope that the sharing of this article will bring more possibilities for you to use the indirect function.
I hope this article is helpful to you, welcome to follow and leave a message, this number will continue to share more high-quality excel knowledge.