學不完．教不停．用不盡

網友問到：參考下圖，在 Excel 中依據日期和類別自動計算數量加以編號？

日期欄位是由「月份/年份」組成，而類別有二個：車(C)、船(S)。

代碼中必須依同年、同月、同類別自動加總，並以 3 碼呈現。

【公式設計與解析】

1. 計算累積數量代碼

儲存格E3：=TEXT(SUMPRODUCT((RIGHT($A$4:A4,4)=RIGHT(A4,4))*
(LEFT($A$4:A4,2)=LEFT(A4,2))*($B$4:B4=B4)),"000")

(1) 條件一：RIGHT($A$4:A4,4)=RIGHT(A4,4)

利用 RIGHT 函數取出儲存格最右 4 碼以得到年份。

用以在 SUMPRODUCT 函數中計算從第一個儲存格至目前儲存格裡，所有年份和儲存格A4相同者的數量。

(2) 條件二：LEFT($A$4:A4,2)=LEFT(A4,2)

利用 LEFT 函數取出儲存格最左 2 碼以得到月份。

用以在 SUMPRODUCT 函數中計算從第一個儲存格至目前儲存格裡，所有月份和儲存格A4相同者的數量。

(3) 條件三：$B$4:B4=B4

用以在 SUMPRODUCT 函數中計算從第一個儲存格至目前儲存格裡，所有類別和儲存格B4同者的數量。

(4) SUMPRODUCT(條件一*條件二*條件三)

「條件一*條件二*條件三」中的「*」相當於執行邏輯 AND 運算，會將傳回的 TRUE／FALSE 陣列轉換為 1／0 陣列，再經由 SUMPRODUCT 函數予以加總。

(5) TEXT(SUMPRODUCT(條件一*條件二*條件三),"000")

最後，將第(4)式傳回的結果以 TEXT 函數利用參數「000」編成 3 碼。

2. 編輯代碼

儲存格C3：=RIGHT(A4,4)&LEFT(A4,2)&E4&IF(B4="車","C","S")

(1) RIGHT(A4,4)

利用 RIGHT 函數取出儲存格最右 4 碼以得到年份。

(2) LEFT(A4,2)

利用 LEFT 函數取出儲存格最左 2 碼以得到月份。

(3) IF(B4="車","C","S")

判斷儲存格B4如果為「車」，則傳回「C」，否則，傳回「S」(船)。

最後，將第(1)式、第(2)式、第(3)式及數量代碼，以「&」串接，即為所求。