不走尋常路同一單元格的求和有高招

俞木發(fā)

數(shù)據(jù)分離——分列法

上面示例中的數(shù)據(jù)都是使用“+”號(hào)隔開(kāi)的，因此要計(jì)算單元格的和，我們可以將數(shù)據(jù)先使用分列的方式分離。因?yàn)锽列最多有三個(gè)數(shù)據(jù)，所以需要在B列之后插入新的C列和D列。接著選中B列數(shù)據(jù)，點(diǎn)擊菜單欄中的“數(shù)據(jù)一分列”，在“分隔符號(hào)”里勾選“其他”，輸入“+”，按照向?qū)崾就瓿蓴?shù)據(jù)分列（圖2）。

可以看到完成分列后，原來(lái)B列單元格的數(shù)據(jù)會(huì)自動(dòng)分離到C列、D列中?，F(xiàn)在在F2單元格中輸入公式“=SUM（B2+C2+D2）*E2”（不合外側(cè)引號(hào)，下同），即可完成數(shù)據(jù)統(tǒng)計(jì)了（圖3）。

數(shù)據(jù)轉(zhuǎn)換——替換法

數(shù)據(jù)分列需要數(shù)據(jù)都使用統(tǒng)一的符號(hào)進(jìn)行隔離，但如果數(shù)據(jù)中有不同的分隔符號(hào)，比如上述數(shù)據(jù)中買了8公斤蘋果后又由于質(zhì)量問(wèn)題退了3公斤，數(shù)據(jù)是類似“2+6-3”的形式，對(duì)于類似的數(shù)據(jù)就無(wú)法分列了，此時(shí)可以使用數(shù)據(jù)轉(zhuǎn)換的方法實(shí)現(xiàn)求和。

在原始工作表中定位到E2單元格，輸入公式“=”=”&B2”（使用連字符將“=”和B2的數(shù)據(jù)連接在一起），下拉后在E2單元格中就可以將原來(lái)的數(shù)據(jù)轉(zhuǎn)換為“=3+6+9”的形式（圖4）。

現(xiàn)在選中E列的數(shù)據(jù)復(fù)制，啟動(dòng)記事本，將復(fù)制的數(shù)據(jù)粘貼到記事本中，然后再將記事本的數(shù)據(jù)重新粘貼到F列就可以自動(dòng)完成求和了。這里通過(guò)記事本將原來(lái)公式的數(shù)據(jù)轉(zhuǎn)換為類似“=2+6-3”的表達(dá)式，粘貼到Excel中后就可以自動(dòng)完成運(yùn)算（圖5）。

自定義公——直接求和

通過(guò)對(duì)原始數(shù)據(jù)分析可以看到，單元格的數(shù)據(jù)實(shí)際上就是一個(gè)表達(dá)式，上述方法需要多次復(fù)制粘貼才能完成數(shù)據(jù)的轉(zhuǎn)換。實(shí)際上對(duì)于類似表達(dá)式的計(jì)算，我們還可以借助Excel的自定義公式直接完成運(yùn)算。

點(diǎn)擊菜單欄中的“公式一定義名稱”，在彈出的窗口中將名稱更改為“單元格求和”，引用位置輸入“=EVALUATEfSheetl！B2_）”，這里通過(guò)自定義名稱，使用EVALUATE函數(shù)對(duì)表達(dá)式返回計(jì)算結(jié)果（圖6）。

這樣我們就在Excel中定義了一個(gè)名為“單元格求和”的自定義公式，這個(gè)公式實(shí)際上是將原來(lái)的格式轉(zhuǎn)換為“=1+2+3”的形式來(lái)實(shí)現(xiàn)單元格的求和?，F(xiàn)在定位到E2單元格，點(diǎn)擊“用于公式→單元格求和”，此時(shí)在E2單元格中將會(huì)出現(xiàn)“=單元格求和”，按下回車鍵后完成求和，接著將公式下拉即可完成所有B列單元格的求和（圖7）。

從上述操作可以看到，自定義公式可以方便我們對(duì)單元格里的表達(dá)式進(jìn)行操作。利用這個(gè)方法還可以完成很多類似的操作，比如在Excel中收集到了小學(xué)數(shù)學(xué)四則運(yùn)算題，現(xiàn)在需要求其答案。由于Excel中乘除符號(hào)是“*”和“/”，因此需要先將原來(lái)題目中的“×”、“÷”使用查找替換法進(jìn)行替換，復(fù)制A列數(shù)據(jù)到B列，接著定位到B列，使用查找替換工具替換其中的乘和除符號(hào)（圖8）。

同上點(diǎn)擊“公式→定義名稱”，設(shè)置一個(gè)名為“四則運(yùn)算”的自定義函數(shù)（引用位置為“=EVALUATE（Sheet2！82）”），定位到C2單元格，點(diǎn)擊“用于公式→四則運(yùn)算”，然后下拉填充即可快速獲得答案（圖9）。

最后，為了方便小學(xué)生識(shí)別原來(lái)的乘除運(yùn)算符號(hào)并做成等式模樣，可以選中B列，右擊選擇“隱藏”，繼續(xù)在B列后插入一列并填充“=”號(hào)，最后將其全選復(fù)制并粘貼到記事本中（再使用替換方法將“Tab”鍵替換為無(wú)），這樣既可以保留原來(lái)的運(yùn)算符號(hào)又可以完成自動(dòng)運(yùn)算（圖10）。