無(wú)懼商品代碼Excel一步到位識(shí)別代碼和統(tǒng)計(jì)數(shù)據(jù)

平淡

通過(guò)分析上述代碼可以知道，統(tǒng)計(jì)銷(xiāo)售額就是將代碼轉(zhuǎn)換為“對(duì)應(yīng)數(shù)量*商品單價(jià)”的形式，最后再依次相加。比如員工張三周一的銷(xiāo)售額A6B5C，那他的實(shí)際銷(xiāo)售額就是3×9.9+6×14.8+5×12.8=182.5。

首先獲取員工張三的周銷(xiāo)量數(shù)據(jù)，插入一個(gè)J 列，在J 2 單元格中輸入公式“="="&B2&C2&D2&E2&F2&G2&H2”，可以得到“=3A6B5C6A3B10C3A6B6D6A3C11D3A6B7D6A3B12D6A9B8C”數(shù)據(jù)組合，下拉公式后可以得到其他員工的周銷(xiāo)量數(shù)據(jù)組合（圖2）。

接著將上述數(shù)據(jù)轉(zhuǎn)換為類似“=數(shù)量*商品代碼+下一個(gè)數(shù)量*商品代碼”的形式，通過(guò)觀察可以發(fā)現(xiàn)，這個(gè)轉(zhuǎn)換實(shí)際上只需將代碼中的字母替換為“*字母+”，比如3A轉(zhuǎn)換為“3*A+”的形式即可。轉(zhuǎn)換工作可以在Word中實(shí)現(xiàn)，依次將J2到J4單元格中的數(shù)據(jù)粘貼到Word里，接著點(diǎn)擊“查找和替換”，點(diǎn)擊“更多”，在“查找內(nèi)容”處輸入“^$”（不含外雙引號(hào)，下同，表示查找任意字母），“替換為”處輸入“*^&+”（^&表示查找內(nèi)容，可以點(diǎn)擊“特殊格式→查找內(nèi)容”直接輸入）。然后點(diǎn)擊“全部替換”，即可將粘貼的內(nèi)容全部轉(zhuǎn)換為類似“3*A+6*B+5*C”的格式，在每行的末尾會(huì)多出一個(gè)“+”，需要手動(dòng)刪除（圖3）。

返回Excel，依次將上述轉(zhuǎn)換后的內(nèi)容粘貼到K 2：K4區(qū)域，接下來(lái)只需將其中的代碼替換為對(duì)應(yīng)的單價(jià)就可以求和了。替換過(guò)程可以通過(guò)自定義公式實(shí)現(xiàn)，不過(guò)自定義公式不支持直接使用字母C和字母R（C、R是Excel默認(rèn)保留的字符），因此我們還需要使用“查找和替換”功能將商品代碼中的字母C先全部替換為CC（在設(shè)置商品代碼時(shí)可以不使用C和R，這樣就無(wú)需再次轉(zhuǎn)換）（圖4）。

現(xiàn)在使用自定義公式將商品代碼轉(zhuǎn)換為單價(jià)。依次點(diǎn)擊“公式→定義名稱”，建立一個(gè)名為“A”的新名稱，“ 引用位置”設(shè)置為“=Sheet1！$N$2”，即A商品的單價(jià)，因?yàn)檫@里通過(guò)公式進(jìn)行引用，所以更改單價(jià)后會(huì)同步更新（圖5）。

操作同上，依次新建B、C C（不要用C）、D名稱，完成后返回工作表，這時(shí)可以看到在K2：K4區(qū)域已經(jīng)自動(dòng)完成銷(xiāo)售額的計(jì)算。定位到I2單元格，輸入公式“=K 2”并下拉，最后將J列、K列隱藏即可。我們可以將上述文件保存為模板備用，以后統(tǒng)計(jì)周銷(xiāo)售額時(shí)，只要復(fù)制J列的數(shù)據(jù)到Wo r d里，完成替換后再將其復(fù)制到K列就可以自動(dòng)完成銷(xiāo)售額的統(tǒng)計(jì)了（圖6）。

上述操作需要借助Word的“查找和替換”功能，而且還需要替換字母C，操作步驟比較多。其實(shí)對(duì)VBA比較熟悉的朋友還可以通過(guò)自制腳本來(lái)實(shí)現(xiàn)自動(dòng)統(tǒng)計(jì)，原理如下：

1.由于我們需要在J 2 ： J 4 區(qū)域通過(guò)連接符獲取周銷(xiāo)售數(shù)據(jù)組合，因此先要在J 2 單元格中輸入公式“= B 2 & C 2 & D 2 & E 2 & F 2 &G2&H2”并下拉填充到J4單元格。

2.復(fù)制J2：J4區(qū)域中的內(nèi)容，依次點(diǎn)擊“開(kāi)始→粘貼→選擇性粘貼→數(shù)值”，將其粘貼到K 2：K4區(qū)域，接著使用Replace函數(shù)將K 2：K4區(qū)域中的A替換為“*9.9+”（B、C、D的替換方法類似），這樣可以一步實(shí)現(xiàn)替換，這步通過(guò)VBA代碼實(shí)現(xiàn)。

3.在L2單元格中輸入公式“="="& K 2&" 0 "”，這里使用連接符將“=”、“K 2”、“0”連接起來(lái)，變?yōu)轭愃啤?3* 9.9+8*12.8+0”的標(biāo)準(zhǔn)公式形式。因?yàn)榻?jīng)過(guò)上述替換會(huì)在每行數(shù)據(jù)的結(jié)尾多出一個(gè)“+”，所以這里把它和“=”及“0”連接起來(lái)，使之成為公式。

4.選擇L 2：L4區(qū)域中的數(shù)據(jù)并復(fù)制，依次點(diǎn)擊“開(kāi)始→粘貼→選擇性粘貼→數(shù)值”，將其粘貼到M2：M4區(qū)域，此時(shí)在M2：M4區(qū)域就會(huì)自動(dòng)根據(jù)公式完成銷(xiāo)售額的統(tǒng)計(jì)，這個(gè)也是通過(guò)VBA代碼來(lái)完成（圖7）。

明白了原理后登錄“http：//dwz.date/ctSt”（提取碼： sqbs）下載所需的腳本，然后返回Excel窗口并按下“Alt+F11”組合鍵，將下載到的代碼粘貼到代碼編輯窗口。代碼含義請(qǐng)參考文件中的注釋，具體的單元格區(qū)域和代碼（A、B、C、D）替換請(qǐng)根據(jù)實(shí)際情況進(jìn)行修改。這樣以后在統(tǒng)計(jì)銷(xiāo)售數(shù)據(jù)時(shí)，只需運(yùn)行該腳本就會(huì)在M2：M4區(qū)域自動(dòng)計(jì)算銷(xiāo)售額（圖8）。