合并單元格后 如何高效處理已有數(shù)據(jù)

俞木發(fā)

為了美觀或出于其他各種需要，很多朋友喜歡在Excel中添加合并單元格。但Excel中合并單元格作為一個(gè)整體，在進(jìn)行日常求和、填充序號等操作時(shí)，會(huì)帶來極大的不便。那么，如何才能在合并單元格的同時(shí)，對數(shù)據(jù)進(jìn)行高效的處理呢？

1. 求和不走尋常路

我們經(jīng)常在Excel中使用SUM函數(shù)對一些數(shù)據(jù)進(jìn)行求和。但是，如果求和的數(shù)據(jù)位于合并單元格中，需要對SUM函數(shù)進(jìn)行變通才能實(shí)現(xiàn)正確求和。比如對于如圖的銷售額求和，由于每個(gè)銷售部銷售的產(chǎn)品種類不同，傳統(tǒng)SUM函數(shù)就無法直接填充完成統(tǒng)計(jì)（圖1）。

對于這類數(shù)據(jù)的統(tǒng)計(jì)，我們可以使用SUM函數(shù)相減的方法實(shí)現(xiàn)。選中D2：D11填充區(qū)域，接著定位到D2單元格，輸入公式“=SUM（C2：C11）-SUM（D3：D11）”，然后按下Ctrl+Enter鍵填充，即可完成這類數(shù)據(jù)的統(tǒng)計(jì)（圖2）。

公式解釋：

因?yàn)楹喜卧駥?shí)際上保留的只是第一個(gè)單元格的數(shù)據(jù)，因此對于第一個(gè)合并單元格的數(shù)據(jù)實(shí)際上是C2：C11總和-D3：D11的和。這里公式中對計(jì)算區(qū)域的引用是相對引用，這樣當(dāng)按下Ctrl+Enter鍵復(fù)制填充后，引用的數(shù)據(jù)區(qū)域會(huì)根據(jù)合并單元格左上角第一個(gè)單元格的地址進(jìn)行相應(yīng)移動(dòng)，從而實(shí)現(xiàn)對合并單元格數(shù)據(jù)的準(zhǔn)確統(tǒng)計(jì)。

2. 序號添加不求人

為了數(shù)據(jù)的排序更為直觀，很多時(shí)候我們需要對合并單元格添加序號。比如上例，需要將1至4填充到對應(yīng)的銷售部中，但是合并單元格是一個(gè)整體，如果直接填充序號并不能將各銷售部實(shí)現(xiàn)精確的排序（圖3）。

因?yàn)樾蛱柺且来卧黾拥?，并且每次都是增?，所以可以用上一個(gè)序號加上1來得到合并單元格的序號。這里可以使用MAX函數(shù)填充。同上先選中A2：A11區(qū)域，接著在A2輸入公式“=MAX（A$1：A1）+1”，同上按下Ctrl+Enter鍵填充即可（圖4）。

公式解釋：

這里A$1表示絕對引用，即始終以A1作為起始引用位置，后一個(gè)A1則為相對引用，這樣在使用Ctrl+Enter鍵向下填充的時(shí)候，公式會(huì)自動(dòng)引用下方的合并單元格，并且根據(jù)序號的特點(diǎn)依次加1。

3. 數(shù)據(jù)填充不鬧心

很多時(shí)候我們需要將數(shù)據(jù)填充到合并單元格中，比如某學(xué)校理科班已經(jīng)有學(xué)生姓名數(shù)據(jù)，為了方便對學(xué)生的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，現(xiàn)在需要將學(xué)生姓名依次填充到A列合并單元格中（圖5）。對于這類數(shù)據(jù)填充到合并單元格的操作，可以借助OFFSET嵌套COUNT函數(shù)完成。

操作同上類似，先選中A2：A19區(qū)域，在A2輸入公式“=OFFSET（$E$1，COUNTA（$A$1：A1），）”，然后向下填充即可。當(dāng)然，如果這里合并單元格的數(shù)目不同（比如科目數(shù)量不同），那就需要先選中填充區(qū)域，再使用Ctrl+Enter鍵填充（圖6）。

公式解釋：

這里先使用COUNTA函數(shù)對A列不為空的單元格進(jìn)行計(jì)數(shù)，然后OFFSET函數(shù)根據(jù)COUNTA函數(shù)的統(tǒng)計(jì)結(jié)果，由$E$1往下偏移取數(shù)。這里$A$1使用絕對引用將其作為固定的起點(diǎn)，A1作為相對終點(diǎn)（使用相對引用），公式下拉后，相對終點(diǎn)的位置就不斷向上一單元格延伸作為引用對象，即不斷引用從E2開始的姓名數(shù)據(jù)填充到合并單元格中。

4. 恢復(fù)真身 數(shù)據(jù)統(tǒng)計(jì)更高效

從上述介紹可以看到，在Excel的統(tǒng)計(jì)中如果包含合并單元格，會(huì)給我們的日常操作帶來極大的不便，導(dǎo)致很多日常函數(shù)無法直接使用。因此要讓數(shù)據(jù)統(tǒng)計(jì)更高效，我們就可以取消單元格的合并，讓其恢復(fù)真身。比如對于下面的數(shù)據(jù)，現(xiàn)在需要將合并單元格的“單價(jià)”數(shù)據(jù)取消合并并自動(dòng)填充到原來的單元格（圖7）。

選中E2：E11，點(diǎn)擊“開始→合并后居中”，取消原來所有的合并單元格，但是這樣會(huì)留下很多空白的單元格。按下F5鍵，在打開的窗口點(diǎn)擊“定位條件→空值”。這樣原來取消合并后空值的單元格會(huì)被全部選中，定位到E3輸入公式“=E2”，使用Ctrl+Enter鍵填充，這樣單價(jià)內(nèi)容會(huì)自動(dòng)填充到對應(yīng)的單元格中?，F(xiàn)在，銷售額的統(tǒng)計(jì)就可以直接利用D2*E2公式進(jìn)行準(zhǔn)確計(jì)算了（圖8）。