Hello everyone, I'm excel body lightspeed efficiency guy off-duty, little flower.
Some time ago, an Excel expert complained to me:
Teacher Xiaohua, I used to claim to be proficient in Excel functions until I was punched in the face by an Excel written test question from a well-known company.
Curiously, I opened the question he sent:
Also startled:
Is the Excel circle so tough now? Is the little written test question so cruel?
This inhumane, ghost crying wolf howl group summing problem, must not let it be a disaster for the human world, let the little flower to destroy it.
Function masters can try their hands first and then watch the tutorial!
Problem-solving formulas
Topic: What is the maximum number of orders in a single city this week? (without auxiliary columns)
Topic Analysis:
It is not difficult to see that the amount of completed orders is calculated according to 0-23 hours, ticks, in order to calculate the highest number of single-day completions, it is necessary to first calculate the sum of the daily completed orders in each city, and then judge the maximum value.
This problem requires step-by-step calculations, usually done using auxiliary columns, which better match normal thinking and computational thinking.
This is where this problem is so tragic that it explicitly forbids this routine of perfect operation!
As a result, the respondent was forced to go to Liangshan and had to find a way to complete the two-step operation of "calculating the group sum of the single amount of a single day" and "judging the maximum value" in a formula.
This requires the use of function nesting and array formulas.
Grouping sum to take the maximum value formula:
▲ Swipe left and right to view
Formula parsing
Step 1: OFFSET+ROW, split the table into a single-day data table of 24 rows.
❶ OFFSET(D2:D25,24*(ROW(1:50)-1),)
Because the datasheet is filled in from 0-23 hours, every 24 rows represent the number of completed orders for the whole day.
That is, we need to add the singular number of column D every 24 ticks in the formula to form an ordered array representing the amount of daily completed orders.
This step presupposes that every 24 rows of column D is split into a group for further summation.
Equation fragment (1) is designed to accomplish this.
Equation Fragment (1):
Let's first use ROW(1:50)-1 to return an ordered array A of 0-49, where 50 is not a fixed number, it can be an arbitrary number large enough to completely split the valid data region.
Multiply the ordered array of 0-49 by 24 to get {0; 24; 48; ......; 1176} such an equal difference series B with a tolerance of 24.
Then we use the OFFSET function, which starts with the initial region, shifts down the specified number of rows, and then shifts the specified number of columns to the right.
Then, starting from the position after the offset, returns the offset function that specifies how many rows and how many columns the cell range.
Offset {0) using the offset function, STARTING with the D2:D25 cell range, and then offset {0; 24; 48; ......; 1176} cells;
Get 50 different ranges with 24 cells each:
RFS{D2:D25,D26:D49,D50:D73,,,}
These areas represent exactly the number of orders completed in each city for 24 consecutive hours per day.
Step 2: MAX+SUMIF, sum the amount of a single day and take the maximum value.
② {=MAX(SUMIF(RFS,">0"))}
Here, we use the SUMIF function for grouping summation.
SUMIF is a conditional summation function that sums the sum range cells that satisfy the condition by comparing the condition range to the condition value.
Obviously, every data in the regional RFS is greater than 0, so the second parameter of SUMIF is meaningless in itself, it only serves as summing.
But we can't use the SUM function directly, or we can't form 50 separate summations.
It is then necessary to have a meaningless summation condition , >0 " , which makes this judgment individually for each region and calculates the sum of the regions.
That is, the array of single-day orders for each city we need C{478519; 458663; ......;0;0;0}。
Among them, 478519 is the amount of orders completed in Fuzhou on Monday, and so on.
Finally, the MAX function takes the maximum value.
The outermost curly braces here indicate that the entire formula is an array formula.
After we enter the formula, we need to press Ctrl+Shift+Enter at the same time to perform the array operation.
Refine your ideas
Briefly summarize the operation logic of the formula for grouping summation to take the maximum value:
❶ First use the ROW function to generate an ordered array.
❷ Grouping is then achieved by offsetting the offset function.
❸ SUMIF grouping is summed and then max is used to take the maximum value.
Question Appreciation
This question not only assesses the ability to use function nesting and array formulas, but also tests the candidate's Excel skill reserve level.
For example, the ROW function and offset function used in solving the problem, ROW is tried and tested in numbering and sorting, and OFFSET is in dynamic charting.
Job seekers who are not familiar with these two functions may not be able to show high efficiency when dealing with some basic problems. A question to distinguish between high and low, wonderful!
The above is the analysis of the Excel written test question of a well-known company - group sum to get the maximum value.
Friends must chew carefully and thoroughly, don't smash your own proficient Excel golden signboard!
Of the 4 functions mentioned in this article, ROW, OFFSET, SUMIF, MAX functions, which one do you most want to learn? Maybe there will be tutorials later?
Leave a message to talk about watching~