The customers responsible for each employee of the business department of a factory are manually registered on a table, the format is Table 1 in Figure 1 below, if an employee corresponds to multiple customers, then use the ton number to open, the advantage of such a registration method is that it looks more intuitive, and the disadvantages are also very obvious, the data is concentrated in a cell, and it is very inconvenient to analyze and reference the customer if you need to do it in the future.
The needs of the statistician in this factory are to design a function formula to separate the corresponding customers of each employee and count the number of total customers (customers have duplicate data), and the final result is shown in the red box in Figure 1 below
Figure 1 now requires that the customers in column C be separated into independent elements and converted into a one-dimensional table in the row (vertical) direction, and the customer's needs are counted according to this one-dimensional table.
To accomplish this, first observe the source data and find a key symbol, "number", which can be used to create a relay virtual memory array. First Text Concatenation Alternate Text Merge Text Columnized Text Conditional Statistics;This requirement can basically be accomplished;
The logical tree is shown in Figure 2 below
Figure 2 Text concatenation is a technique that is often used in **, no need to enter a function, only need to enter the "&" sign, this symbol can be called a connection symbol or an AND sign, its function is to connect two elements and become a new element (within a cell);
Enter = at cell E3"@"&b3:b6&"、",This is an auxiliary column,Wait for it to be used as an inner array in the replacement function,If you don't understand this auxiliary column,Don't worry,Just enter it;
Figure 3 just looks at the pattern of customer data in column C, and it is obvious that different customers are separated by commas. So we need to replace this ton number with the auxiliary column above, what is the purpose of the replacement?It is equivalent to associating the information of the employees in column B and marking it with the symbol "@". Entry Function:
f3 =substitute(c3:c6,"、","@"&b3:b6&"、")
g3 =b3:b6&"、"&substitute(c3:c6,"、","@"&b3:b6&"、")
Function definition: By replacing the comma, connect the data of column B with column C, and connect the data of column B with the symbol "&" again, so that you can find that the data of column B and column C are regular, that is, the customer is separated by the symbol "@".
After completion, it is shown in Figure 4 below
Figure 4 uses three auxiliary columns above, the purpose is to have a rule when merging, and it is good to divide the column again with a rule, merge the auxiliary column data entered by G3 into a cell again, and use the symbol "@" to merge, enter the function:
e3 =textjoin("@",,b3:b6&"、"&substitute(c3:c6,"、","@"&b3:b6&"、"))
After the merger is completed, it is shown in Figure 5 below
Figure 5 divides the data in a cell again, and the function used is textsplit, and the function is entered into the function:
e3=textsplit(textjoin("@",,b3:b6&"、"&substitute(c3:c6,"、","@"&b3:b6&"、")),"、","@")
Function Interpretation: The merged data is divided into row symbols "@" and column symbols ","That is, the column and column are separated at the same time, and the effect is shown in Figure 6 below
Figure 6 has converted a standard 2D merged data into a standard 1D data through the above steps. It's not difficult to count the number of customers. Entry Function:
e3=rows(unique(take(e3#
Function definition: first use the take function to reserve the last 1st column for e3, the parameter is -1, that is, the customer column, and then delete the duplicates in this column, and the result is judged by rows, and the number of rows is the number of customers;
The finished effect is shown in Figure 7 below
Figure 7