close

883

驚喜!一個EXCEL函數解決困擾二年的問題

示範檔

範例檔

883.XLSX

結果檔

883F.XLSX

在正式系統學習Excel之前,錦子老師是有一些Lotus基礎的,也曾因為會Lotus而娶到一位仙女美嬌娘,但進入到Excel世界後每次遇到難題要麼問同事要麼酷狗,使盡洪荒之力依舊有很多問題解決不了,再後來就不了了之……

現在,再回頭看,有很多問題其實很簡單,只是當時太懶,不願意去深入探究。

案例:這是一位上海松江福佑斯電器公司學生的問題?

A欄為中國各地區的編碼,1-34分別代表中國23個省、5個自治區、4個直轄市、2個特別行政區。B欄為三級分公司的編碼,1-3分別代表一級分公司、二級分公司、三級分公司。

現需要設置各地區各級分公司唯一IDID=地區編碼+分公司級別編碼+同一地區同一分公司第幾次出現,如下圖所示。

2年前,能夠實現的結果是這樣的:

使用&連接子,在C2儲存格輸入公式「=A2&B2&1」,並將公式複製到C3:C12,如下圖所示。

這樣無法解決同一地區同一分公司第幾次出現的問題,放棄!

1年前,能夠實現的結果是這樣的:

使用COUNTIF函數判斷該級別分公司是第幾次出現,在C2儲存格輸入公式「=A2&B2&COUNTIF(B$1:B2,B2)」,並將公式複製到C3:C12,如下圖所示。

這樣只能解決同一級別分公司第幾次出現,不能解決同一地區同一分公司第幾次出現。

例如C11儲存格結果應該是211C12儲存格結果應該是221,是判斷前兩位數21第一次出現,21第二次出現,而不是中間的1~3第幾次出現。所以這個問題又擱置了。

現在,能夠實現的結果是這樣的:

使用SUMPRODUCT函數將地區與分公司級別合併考慮,是否是第一次出現,在C2儲存格輸入公式「=A2&B2&SUMPRODUCT(1*(A2&B2=A$2:A2&B$2:B2))」,並將公式複製到C3:C12,如下圖所示。

SUMPRODUCT(1*(A2&B2=A$2:A2&B$2:B2))

統計A$2:A2&B$2:B2這個範圍中符合條件A2&B2的個數,SUMPRODUCT函數,各個參數必須為數值型資料,A2&B2=A$2:A2&B$2:B2判斷的結果為邏輯值,所以需要乘1轉換為數位。

完全是最開始計畫想要得到的結果,利用一個函數完美的解決了困擾了2年的問題。

2018年,錦子老師將繼續走在精進Excel的路上,您,是否願意與我同行呢?一起加油!


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()