The new WPS function shared today is ChooseCols, which is the most frequent function used in Mr. Gu's personal dynamic array, and can be used in many scenarios. Especially when you have mastered and been able to use other dynamic array functions, such as the filter function and the sort function, you need to specify the column return.
The same series of functions of this function is chooserows, and the function is basically the same as choosecols, except that it is in the column direction (vertical) and the other is in the row direction (horizontal). So as long as you understand ChooseCols, ChooseRows basically understands.
Function name: choosecols
Function description: Returns an array or a referenced column.
Function parameters: epxand(array, ordinal 1, ordinal 1....))
Function abbreviation: =choosec, press tab to complete automatically after the entry is completed, this method is not as good as typing =cho, and the direction down button twice tabs is fast, so generally remember the cho two keys on it.
Returns a single column: i3 = choosecols(b3:g7,2).
Function description: The second column (number 2) of b3:g7 (selected array region) is returned, and the key understanding is the column number of the selected region, and it is counted from the left;
Returns multiple columns: =choosecols(b3:g7,2,5,6).
Function description: In the case of multiple columns, the function is separated by a comma starting from the second parameter, and the corresponding numbers are entered separately. For example, 2, 5 represents the return of columns 2 and 5, 2, 5, 6, and columns 2, 5, and 6;
Scenario: If there are multiple columns of data in Table 1 and Table 2, the two columns cannot be merged because the headers of the two columns are inconsistent. In the following figure, just for the convenience of screenshots, in actual work, the number of columns is more.
To quickly make the data in two tables consistent, you can use the following methods:
Step 1: Merge Table 1 and use the mtach function to search for the title of Table 2 in Table 1 and return the corresponding figures. Enter the function: =match(b6:j6,b2:j2,0), you can find that the quantity is in column 4 in table 2, but in column 5 in table 1;
Step 2: Merge two tables based on Table 2, copy Table 2 as the base, and then enter the function =choosecols(b3:j4,match(b6:j6,b2:j2,0)) to merge them.
If you don't know the match function, you can change it to: =choosecols(b7:j8,1,2,3,5,9,4,8,6,7).
Manually entered, column numbered numbers;
Work scenario: Table 1 shows all the work tasks of the whole factory, and at this time, employee Liu Bei needs to filter out his own orders and calculate the outstanding amount. The data is shown in the figure below:
Step 1: Enter the filter function: =filter(b3:f6,g3:g6=.)"Liu Bei"At this time, it is found that the result of filtering is a large dynamic array area, which cannot be directly calculated (i3:m4), and if you need to use dynamic arrays to participate in the reference, you can use choosecols to achieve it;
Step 2: Enter the function: =choosecols(i3,4)-choosecols(i3
Function description: i3 is the result returned by the filter function (i3:m4).
Use choosecols to select columns 4 and 5 respectively.
The operation is then performed to obtain the unfinished dynamic array writing.
Working scenario: Under the new dynamic array writing method, many dynamic array functions can return a region as the function result, and this result cannot be directly referenced in most cases, so use choosecols to convert it into a dynamic array that can be referenced by a single column
Again, take scenario 2 to illustrate. i3 is a large dynamic array area, and only the function :
choosecols(filter($b$3:$f$6,$g$3:$g$6="Liu Bei"),column(a1))
The back-to-right padding converts a region's dynamic array into a single-column dynamic array;
column(a1) is the column number that returns cell A1, because there is no lock, the right padding is b1, and the column number of b1 is 2, so it is to select the first column and the second column.
After the conversion is completed, you can use a dynamic array function for a single column to find the unfinished quantity
Input: =l3 -m3
is the reference identifier of the dynamic array, representing the full range of the return of the cell in which the dynamic function is located.
List of high-quality authors