無懼商品代碼Excel一步到位識別代碼和統計數據

平淡

通過分析上述代碼可以知道，統計銷售額就是將代碼轉換為“對應數量*商品單價”的形式，最后再依次相加。比如員工張三周一的銷售額A6B5C，那他的實際銷售額就是3×9.9+6×14.8+5×12.8=182.5。

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

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

返回Excel，依次將上述轉換后的內容粘貼到K 2：K4區域，接下來只需將其中的代碼替換為對應的單價就可以求和了。替換過程可以通過自定義公式實現，不過自定義公式不支持直接使用字母C和字母R（C、R是Excel默認保留的字符），因此我們還需要使用“查找和替換”功能將商品代碼中的字母C先全部替換為CC（在設置商品代碼時可以不使用C和R，這樣就無需再次轉換）（圖4）。

現在使用自定義公式將商品代碼轉換為單價。依次點擊“公式→定義名稱”，建立一個名為“A”的新名稱，“ 引用位置”設置為“=Sheet1！$N$2”，即A商品的單價，因為這里通過公式進行引用，所以更改單價后會同步更新（圖5）。

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

上述操作需要借助Word的“查找和替換”功能，而且還需要替換字母C，操作步驟比較多。其實對VBA比較熟悉的朋友還可以通過自制腳本來實現自動統計，原理如下：

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

2.復制J2：J4區域中的內容，依次點擊“開始→粘貼→選擇性粘貼→數值”，將其粘貼到K 2：K4區域，接著使用Replace函數將K 2：K4區域中的A替換為“*9.9+”（B、C、D的替換方法類似），這樣可以一步實現替換，這步通過VBA代碼實現。

3.在L2單元格中輸入公式“="="& K 2&" 0 "”，這里使用連接符將“=”、“K 2”、“0”連接起來，變為類似“=3* 9.9+8*12.8+0”的標準公式形式。因為經過上述替換會在每行數據的結尾多出一個“+”，所以這里把它和“=”及“0”連接起來，使之成為公式。

4.選擇L 2：L4區域中的數據并復制，依次點擊“開始→粘貼→選擇性粘貼→數值”，將其粘貼到M2：M4區域，此時在M2：M4區域就會自動根據公式完成銷售額的統計，這個也是通過VBA代碼來完成（圖7）。

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