學不完．教不停．用不盡

（網友提問）在 Excel 的工作表中（參考下圖），如何將下圖中的員工編號配給每個產品代碼？

員工編號的顯示數量是依產品代碼的數量而定，如何產生員工編號和產品代碼的清單？

【公式設計與解析】

1. 定義名稱

選取儲存格B1:B11，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：產品代號。

2. 產生新的員工編號清單

儲存格D2：="A"&TEXT(INT((ROW(1:1)-1)/COUNTA(產品代號))+1,"000")

複製儲存格D2，往下各列貼上。

(1) COUNTA(產品代號)

利用 COUNTA 函數來計算產品代碼共有幾個。

(2) INT((ROW(1:1)-1)/COUNTA(產品代號))

利用 INT 函數再藉由 ROW 函數變化來產生代號。ROW(1:1) 向下複製時，會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

本例會產生：1,1,1,…(產品代號個數),2,2,2,…(產品代號個數),3,3,3,…(產品代號個數)。

(3) TEXT(INT((ROW(1:1)-1)/COUNTA(產品代號))+1,"000")

將第(2)式代入 TEXT 函數，再格式為「000」（將數字取三碼補 0 顯示）。

3. 產生新的產品代碼清單

儲存格E2：=OFFSET($B$2,MOD(ROW(1:1)-1,COUNTA(產品代號)),0)

複製儲存格E2，往下各列貼上。

(1) COUNTA(產品代號)

利用 COUNTA 函數來計算產品代碼共有幾個。

(2) MOD(ROW(1:1)-1,COUNTA(產品代號))

利用 ＭＯＤ 函數再藉由 ROW 函數變化來產生代號。ROW(1:1) 向下複製時，會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

本例會產生：1,2,3,…(產品代號個數),1,2,3,…(產品代號個數),1,2,3,…(產品代號個數)。

(3) OFFSET($B$2,MOD(ROW(1:1)-1,COUNTA(產品代號)),0)

將第(2)式代入 OFFSET 函數找出對應的儲存格內容。