In data processing and analysis, minimize the use of merged cells whenever possible, but they are often unavoidable in practice. Therefore, mastering common techniques for handling merged cells is very helpful for everyday office work. This article explains several common merged-cell problems, solutions, and interpretations — hope this helps everyone!
- Function:Counts the non-empty cells in a specified range.
- Syntax:=COUNTA(value or cell range).
- Method:
- Select A2:A7
- Enter =COUNTA(A$1:A1)
- Ctrl + Enter
- Explanation:
- The COUNTA function's argument starts at the cell above the current cell and uses a mixed reference. If the cell above is blank, it will start from the first non-blank cell.
- Here, merged cells refer mainly to irregular merged cells.
- Function:Returns the maximum value in a specified range.
- Syntax:=MAX(value or cell range).
- Method:
- Select A2:A7
- Enter =MAX(A$2:A2)+1
- Ctrl + Enter
- Explanation:
- MAX's argument is numeric; if non-numeric, the result is 0. Because A2 contains text, =MAX(A$2:A2) returns 0, and adding +1 yields 1. When filling the second merged-cell range the reference becomes A$2:A3, whose maximum is 1; +1 then returns 2, and so on.
- You can also control the starting serial number by using X as the adjustment value.
- Method:
- Select D2:D7
- Enter =SUM(C2:C7)-SUM(D3:D7)
- Ctrl + Enter
- Explanation:
- The value of a merged cell is stored in the top-left cell of the merged range.
- This formula is divided into two parts: the first part calculates the sum of F3:F9; the second part calculates the sum of G4:G9. In other words, the total sum of all ranges minus the sums of the other ranges leaves the value of the first merged range.
- Function:Returns the value at the intersection of a specified row and column within a range.
- Syntax:=INDEX(cell range, row number).
- Method:
- Select D2:D7
- Enter =INDEX(E$2:E$4,COUNTA(D$1:D1))
- Ctrl + Enter
- Explanation:
- The purpose of this task is to copy values from the Notes column into the total cells. Pasting from a non-merged range into a merged range (or into merged ranges with different structures) will cause errors.
- In the formula, the COUNTA function counts the non-empty cells starting from the cell above the current cell and serves as INDEX's second argument.
