2017年12月24日 星期日

Excel-排列二組編號(OFFSET,COUNTA,TEXT)

(網友提問)在 Excel 的工作表中(參考下圖),如何將下圖中的員工編號配給每個產品代碼?
員工編號的顯示數量是依產品代碼的數量而定,如何產生員工編號和產品代碼的清單?
Excel-排列二組編號(OFFSET,COUNTA,TEXT)

【公式設計與解析】
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(產品代號))
利用 MOD 函數再藉由 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 函數找出對應的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東