2017年6月30日 星期五

Excel-解決無法使用快速鍵產生今天的日期和現在的時間的問題

在 Excel 中如果要讓系統自動產生今天的日期和現在的時間,可以使用快速鍵:
輸入今天的日期/Ctrl+; 【例:2017/6/30】
輸入現在的時間/Ctrl+: 【例:09:43 PM】
Excel-解決無法使用快速鍵產生今天的日期和現在的時間的問題
但是,學生常會反應這些快速鍵失效,經觀察發現可能的原因,是系統處在中文輸入法的狀態下,這將無法使用這個快速鍵來產生日期和時間。只要切回英數模式,即可回復正常使用了。

Word-只列印文件中的部分內容

有時,在處理 Word 文件時,可能只想列印文件中的部分內容。例如在下圖中是一個版面為二欄的文件,假設只想要列印左欄下方區域,該如何處理?
或許你可能將要列印區或複製到另一份文件中列印,但是得重設版面配置。也可能你會把不要列印的區域加以刪除後,再執行列印。
Word-只列印文件中的部分內容
但是,其實你要做的工作只是「選取」即可。當你選取了想要列印的區域後,在[列印]標籤下,並且在[設定]區中選取「列印選取範圍」。
Word-只列印文件中的部分內容
列印後的結果就是你要的。(注意:紙張大小版面的配置都會維持原文件的設定)
Word-只列印文件中的部分內容
注意喔!當你選取了不連續的區域:
Word-只列印文件中的部分內容
就無法使用「列印選取範圍」的功能了!
Word-只列印文件中的部分內容

2017年6月27日 星期二

Excel-清單比對後傳回重覆者的位址(MATCH,ADDRESS)

問到如果要比對的資料位在同一欄中(如下圖),即重覆重現第2次以上者要標示第1次出現的儲存格位置,該如何處理?
Excel-清單比對後傳回重覆者的位址(MATCH,ADDRESS)

【公式設計與解析】
儲存格B2:
=IF(COUNTIF($A$2:A2,A2)>1,ADDRESS(MATCH(A2,$A$2:A2,0)+1,1,4),"")
複製儲存格B2,貼至儲存格B2:B27。
(1) MATCH(A2,$A$2:A2,0)
利用 MATCH 函數比對儲存格A2的內容,在以儲存格A2起始的儲存格範圍,傳回其位置(一個數字)。若是比對後,是不存在的內容,則會傳回錯誤訊息。
(2) ADDRESS(MATCH(A2,$A$2:A2,0)+1,1,4)
將第(1)式傳回值代入 ADDRESS 函數傳回其對應的儲存格名稱。
(3) IF(COUNTIF($A$2:A2,A2)>1,ADDRESS(MATCH(A2,$A$2:A2,0)+1,1,4),"")
利用 COUNTIF($A$2:A2,A2)>1 判斷儲存格A2的內容是否為出現 1 次以上,若是,則顯示儲存格位址,若不是,則顯示空字串。

2017年6月24日 星期六

Excel-將十六進制數右移1位元(BIN2HEX,HEX2BIN,LEN)

網友問到 Excel 的問題:參考下圖,如何將十六進制數右移1位?
Excel-將十六進制數右移1位元(BIN2HEX,HEX2BIN,LEN)

【公式設計與解析】
儲存格D2:=BIN2HEX(LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1))
複製儲存格D2,貼至儲存格D2:D15。
(1) HEX2BIN(A2)
利用 HEX2BIN 函數將儲存格A2內的十六進制數轉換為二進制數。
(2) LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1)
將第(1)式轉換後的二進制數右移 1 位元。
LEN(HEX2BIN(A2)):利用 LEN 函數計算HEX2BIN(A2)的位元數。
(3) BIN2HEX(LEFT(HEX2BIN(A2),LEN(HEX2BIN(A2))-1))
利用 BIN2HEX 函數將第(2)式的傳回值(二進制數)轉換為十六進制數。

2017年6月23日 星期五

Excel-比對二組資料在指定起始和終止欄位內計算相同個數(SUMPRODUCT,INDIRECT)

網友提問:在 Excel 的工作表中有二組資料(如下圖),如何指定起始和終止欄位,並計算在這個區間中二組相同和不相同的個數?
Excel-比對二組資料在指定起始和終止欄位內計算相同個數(SUMPRODUCT,INDIRECT)

【公式設計與解析】
儲存格B6:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))=
(INDIRECT(B4&"2:"&B5&"2"))))
(1) INDIRECT(B4&"1:"&B5&"1")
利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D1:H1)。
(2) INDIRECT(B4&"2:"&B5&"2"):
利用儲存格B4和儲存格B5的內容,轉換取得儲存格範圍(本例為儲存格D2:H2)。
(3) (INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))
在 SUMPRODUCT 函數中判斷第(1)式和第(2)式的儲存格陣列是否相同,傳回 TRUE/FALSE
(4) 1*(INDIRECT(B4&"1:"&B5&"1"))=(INDIRECT(B4&"2:"&B5&"2"))
將第(3)式傳回的 TRUE/FALSE,轉換為 1/0
第(4)式所有 1 的個數和,即為第1列和第2列相同的個數。

同理:
儲存格B7:=SUMPRODUCT(1*((INDIRECT(B4&"1:"&B5&"1"))<>
(INDIRECT(B4&"2:"&B5&"2"))))

Excel-兩個清單比對後傳回重覆者的位址(MATCH,ADDRESS)

網友問到:在 Excel 中的工作表有一個原始資料(如下圖左),有一個比對欄位(如下圖右),經比對之後,要列出重覆者的位置(儲存格位址),該如何處理?
Excel-兩個清單比對後傳回重覆者的位址(MATCH,ADDRESS)

【公式設計與解析】
儲存格D2:=IFERROR(ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4),"")
複製儲存格D2,貼至儲存格D2:D27。
(1) MATCH(C2,$A$2:$A$20,0)
利用 MATCH 函數比對儲存格C2的內容,在儲存格A2:A20範圍的位址。若是比對後,是不存在的內容,則會傳回錯誤訊息。
(2) ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4)
根據第(1)式傳回的位址,利用 ADDRESS 函數傳回其對應的儲存格名稱。
(3) IFERROR(ADDRESS(MATCH(C2,$A$2:$A$20,0)+1,1,4),"")
利用 IFERROR 函數將第(1)式傳回的錯誤訊息以空白(空字串)顯示。

Excel-根據日期、人員摘要對應星期幾各天小計(SUMPRODUCT,INDIRECT)

網友提問:
如下圖,在 Excel 的工作表中如何將圖上方的資料表,摘要成圖下方的結果?
Excel-根據日期、人員摘要對應星期幾各天小計(SUMPRODUCT,INDIRECT)

【公式設計與解析】
選取儲存格A2:O8,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:日期、甲、乙、丙、丁、戊、己。
儲存格B13:
=SUMPRODUCT((WEEKDAY(日期,2)=COLUMN(A:A))*INDIRECT($A13))
複製儲存格B13,貼至儲存格B13:H18。
條件:WEEKDAY(日期,2)=COLUMN(A:A),其中 WEEKDAY 函數的參數『2』,表示傳回的數字「1~7」對應「星期一~星期日」。
Excel-根據日期、人員摘要對應星期幾各天小計(SUMPRODUCT,INDIRECT)
COLUMN(A:A)向右複製公式時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。
INDIRECT($A13):將儲存格A12的文字內容透過 INDIRECT 函數轉換為儲存格位址,而該儲存格內容(「甲」),已經在先前定義好名稱(範圍是儲存格B3:O3)。

Excel-在Google試算表中執行向下填滿空白儲存格

網友根據這篇:Excel-向下填滿空白儲存格,詢問如何在 Google 試算表中完成相同工作。參考下圖,要如何能自動填空白儲存格為上一個儲存格的內容?
Excel-在Google試算表中執行向下填滿空白儲存格
我能想到的做法如下:(圖中的標題AAA是有做用的)
1. 選取儲存格A1:A17。
Excel-在Google試算表中執行向下填滿空白儲存格
2. 選取功能表中的「篩選」。
Excel-在Google試算表中執行向下填滿空白儲存格
3. 點選儲存格A1中的篩選圖示,再點選「清除」,再勾選『空白』。
(結果是只選取了空白儲存格)
Excel-在Google試算表中執行向下填滿空白儲存格
4. 在儲存格A3中輸入公式:=A2。
Excel-在Google試算表中執行向下填滿空白儲存格
5. 拖曳儲存格A3右下角的控制點(滑鼠形狀會改變)至儲存格A17。
Excel-在Google試算表中執行向下填滿空白儲存格
結果如下:
Excel-在Google試算表中執行向下填滿空白儲存格
6. 接著取消篩選,或是點選「全部選取」。
Excel-在Google試算表中執行向下填滿空白儲存格
結果如下,已將空白儲存格填入上一個儲存格的內容了。
Excel-在Google試算表中執行向下填滿空白儲存格

2017年6月22日 星期四

Word-A4紙張合併列印時分割二部分

在實務面上會遇到在 Word 文件中要合併列印時,為了節省紙張會希望能一張紙印多份。以每頁印二份為例,在版面設定中,如果你使用單面雙頁的設計,你會發現:
版面向方為直向時,單面雙頁會將一張紙切割為上下二頁:
image
版面向方為橫向時,單面雙頁會將一張紙切割為左右二頁:
image
但是,如果你想在紙張方向為橫向時,切割紙張為上下二頁,該如何處理?
實例:以一張A4紙張列印二份的學生繳費收據(含學校存根聯和學生收執聯),再將其切割成二份(不同編號)。
1. A4紙張上下分為二個不同編號,左右各有一張學校存根和學生收執(如下圖)
image
方法:利用表格來定位資料。
當使用2X2的表格時,第一列要印第一份,第二列要印第二份。因為合併列印會由左而右、由上而下列印。所以在第二列最前面使用 Next Record 功能變數。
image
合併列印如果如下:
image

2. A4紙張左右分為二個不同編號,上下各有一張學校存根和學生收執(如下圖)
image
方法:將文件版面設定為二欄。
Word 的合併列印會先處理左欄,再處理右欄。所以 Next Record 功能變數放在右欄的最前面。
image
合併列印結果如下:
image

2017年6月21日 星期三

Excel-計算間隔列的和(SUPRODUCT,MOD,ROW)

網友問到 Excel 的問題,參考下圖,如何計算間隔列的和(加總)?
Excel-計算間隔列的和(SUPRODUCT,MOD,ROW).
【公式設計與解析】
選取儲存格B1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。
儲存格E2:=SUMPRODUCT((MOD(ROW(數值)-1,2)=1)*數值)
公式中的參數『2』:此乃要間隔多少個儲存格要取出值。
MOD(ROW(數值)-1,2)=1:其中的「-1」,乃因數值陣列是由第2列開始,即項次1在第2列,項次2在第3列,...,都是差 1。利用 MOD 函數來求得列號除以2所得的結果,並判斷是否結果為 1,產生 1,0,1,0,1,0, ...。
將上式結果置入 SUMPRODUCT 函數,利用「數值」陣列執行「乘積和」
同理:
儲存格E3:=SUMPRODUCT((MOD(ROW(數值)-1,3)=1)*數值)
儲存格E4:=SUMPRODUCT((MOD(ROW(數值)-1,4)=1)*數值)
儲存格E5:=SUMPRODUCT((MOD(ROW(數值)-1,5)=1)*數值)
儲存格E6:=SUMPRODUCT((MOD(ROW(數值)-1,6)=1)*數值)
儲存格E7:=SUMPRODUCT((MOD(ROW(數值)-1,7)=1)*數值)
儲存格E8:=SUMPRODUCT((MOD(ROW(數值)-1,8)=1)*數值)

2017年6月20日 星期二

Excel-計算時間清單中某個區間的個數(SUMPRODUCT,TIME)

網友問到的 Excel 問題:如何在一個時間的資料清單中如何計算合於某個範圍內的數量?(如下圖)
Excel-計算時間清單中某個區間的個數(SUMPRODUCT,TIME)

【公式設計與解析】
選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。
1. 計算 13:15~16:15 的數量
儲存格D3:=SUMPRODUCT((資料>=TIME(13,15,0))*(資料<=TIME(16,15,0)))
TIME(13,15,0):表示時間13時15分。TIME(16,15,0):表示時間16時15分。
利用雙條件:((資料>=TIME(13,15,0))*(資料<=TIME(16,15,0)),在 SUMPRODUCT函數中計算「乘積和」。其中的『*』相當於執行邏輯 AND 運算,會將傳回值 TRUE/FALSE 函數轉換為 1/0,在 SUMPRODUCT 函數中加總。
2. 計算 08:35~11:45 的數量
儲存格D6:=SUMPRODUCT((資料>=TIME(80,35,0))*(資料<=TIME(11,45,0)))
原理同1.。

2017年6月17日 星期六

Excel-讓儲存格隨每週不同星期幾而變色(WEEKDAY)

在 Excel 中有一個資料表(如下圖),如果想要讓資料的標題欄能依每天不同星期幾而顯示不同色彩,該如何處理?
Excel-讓儲存格隨每週不同星期幾而變色(WEEKDAY)
選取儲存格A2:A17,設定格式化的條件:
規則公式:=WEEKDAY(TODAY(),2)=1;格式:儲存格底色為星期一的色彩。
其中 WEEKDAY 函數的參數『2』乃指定傳回「1~7」對應「星期一~星期日」。
Excel-讓儲存格隨每週不同星期幾而變色(WEEKDAY)
規則公式:=WEEKDAY(TODAY(),2)=2;格式:儲存格底色為星期二的色彩。
規則公式:=WEEKDAY(TODAY(),2)=3;格式:儲存格底色為星期三的色彩。
規則公式:=WEEKDAY(TODAY(),2)=4;格式:儲存格底色為星期四的色彩。
規則公式:=WEEKDAY(TODAY(),2)=5;格式:儲存格底色為星期五的色彩。
規則公式:=WEEKDAY(TODAY(),2)=6;格式:儲存格底色為星期六的色彩。
規則公式:=WEEKDAY(TODAY(),2)=7;格式:儲存格底色為星期日的色彩。
結果如下:
Excel-讓儲存格隨每週不同星期幾而變色(WEEKDAY)

2017年6月16日 星期五

Excel-判斷加班時數(TIME,WEEKDAY)

網友問到 Excel 的問題:公司上班時間為08:00-17:00,下班超過30分鐘開始算加班(如下圖),加班前二小時算加班1,超過二個小時算加班2,如何標記假日加班,該如何處理?
Excel-判斷加班時數(TIME,WEEKDAY)

【公式設計與解析】
1. 加班1
儲存格E2:=IF((D2<>"")*(D2>=TIME(17,30,0)),IF(D2-TIME(17,30,0)<
TIME(1,30,0),"V",""),"")
TIME(17,30,0):利用 TIME 函數找出17:30代表的數值。
(1) (D2<>"")*(D2>=TIME(17,30,0)
用以判斷二個條件是否同時成立。
條件一:儲存格D2為空白。
條件二:儲存格D2的時間大於17:30。
(2) D2-TIME(17,30,0)
因為加班超過30分鐘才起算加班。
TIME(1,30,0):找出1.5小時代表的數值。
D2-TIME(17,30,0)
2. 加班2
儲存格F2:=IF((D2<>"")*(D2>=TIME(17,30,0)),IF(D2-TIME(17,30,0)>=
TIME(1,30,0),"V",""),"")
D2-TIME(17,30,0)>=TIME(1,30,0)用以判斷儲存格D2的時間是否已加班超過30分鐘,並且在二小時以上。
3. 假日加班
儲存格G2:=IF((COUNTIF(E2:F2,"V")>0)*WEEKDAY(A2,2)>5,"V","")
使用雙條件來判斷是否為假日並且有加班。
條件一:COUNTIF(E2:F2,"V")>0,判斷在儲存格E2和儲存格F2中的『V』數量是否大於0。(若是,表示有一個『V』)
條件二:WEEKDAY(A2,2)>5,其中 WEEKDAY 函數中使用參數『2』,代表數字1~7對應星期一~星期日。當傳回值大於5時表示為星期六、日,即為假日。
image
複製儲存格E2:G2,貼至儲存格E2:G31。

Excel-列出不重覆的清單(OFFSET,SMALL,ROW)

在 Excel 的工作表中(如下圖),如果想要根據日期清單列出不重覆的日期,該如何處理?
手動方式可以參考以下二篇文章:
如果你想要使用公式,則參考以下的做法。
Excel-列出不重覆的清單(OFFSET,SMALL,ROW)

【公式設計與解析】
本例使用一個輔助欄位C欄。
1. 計算同一個日期由第一個儲存格起算累計有幾個。
儲存格C2:=COUNTIF($A$2:A2,A2)
儲存格C2,貼至儲存格C2:C25。
2. )
儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF($C$2:$C$25=1,
ROW($C$2:$C$25),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) IF($C$2:$C$25=1,ROW($C$2:$C$25),"")
在陣列公式中由C欄中找出標示為『1』的列號。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數,根據第(1)式中傳回的列號由小到大,找出第 1,2,3,… 最小者。ROW(1:1)函數,向下複製公式後,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式的傳回值代入 OFFSET 函數,得到對儲存格內容(一個日期)。
(4) IFERROR(第(3)式,"")
由於公式向下複製後,可能因為查詢不到對應的儲存格而產生錯誤訊息,利用 IFERROR函數將錯誤訊息轉換為空字串(顯示為空白)。

Excel-位於日期範圍者加總(SUMPRODUCT)

網友問到 Excel 的問題:在工作表中的一個日期和金額清單,網友想要將合於某個日期區間中的金額予以加總,該如何處理?
如下圖,以「01月03日」為例,觀察起日和迄日的日期區間,有 7 個區間中含有「01月03日」,要將這些的金額予以加總。
Excel-位於日期範圍者加總(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:C21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:起日、迄日、金額。
儲存格F2:=SUMPRODUCT((起日<=E2)*(迄日>=E2)*金額)
在 SUMPRODUCT 函數中,利用二個條件來判斷E欄的日期是否位於某個日期區間中,其中 (起日<=E2)*(迄日>=E2) 的『*』,相當於執行邏輯 AND 運算,會將條件判斷的傳回值 TRUE/FALSE 轉換為 1/0,再和金額陣列執行「乘積和」運算。

【延伸學習】
如何將金額欄位中含有指定日期者顯示為紅色?(以儲存格E4為例)
1. 選取儲存格C1:C21。
2. 進入設定格式化的條件的對話框,並設定:
類型:使用公式來決定要格式化哪些儲存格。
規則:=(A2<=$E$4)*(B2>=$E$4)
Excel-位於日期範圍者加總(SUMPRODUCT)

2017年6月11日 星期日

Excel-調整小數點後特定位數為偶數/奇數(INT,MOD)

網友問到:在 Excel 中,如果想要將數字的小數點後第3位,調整為不大於該位數的最大偶數,該如何處理?
(參考下圖)例:7.46545→7.464,7.55870→7.558
Excel-調整小數點後特定位數為偶數/奇數(INT,MOD)
【公式設計與解析】
儲存格C2:=INT(A2*1000)/1000-(MOD(INT(A2*1000),2)=1)*0.001
(1) INT(A2*1000)/1000
將儲存格A2內容,截取至小數點後第 3 位。
(2) MOD(INT(A2*1000),2)=1
判斷小數點後第3位是否為奇數,若是,傳回 TRUE;若否,傳回 FALSE
(3) INT(A2*1000)/1000-(MOD(INT(A2*1000),2)=1)*0.001
若小數點後第3位為奇數,則將截取至小數點後第 3 位的數,再減 0.001。
如果你想要改變調整第 n 位數,則只要變動公式中的 1000 和 0.001 即可。
2→100 和 0.01,即 10^2 和 10^-2
3→1000 和 0.001,即 10^3 和 10^-3
4→10000 和 0.0001,即 10^4 和 10^-4
......

Excel-在圖表中不顯示日期清單沒有的日期

網友問到:在 Excel 中有一個資料表(如下圖左),由日期和數值欄位組成。當繪製其統計圖表時,卻出現了原本日期清單中沒有的日期(星期六日),該如何取消顯示這些日期?
在新增圖表時,Excel 判斷了A欄是日期時,即自動以連續的日期來顯示圖表,所以必須要在圖表的座標軸格式設定中處理。
Excel-在圖表中不顯示日期清單沒有的日期
選取座標軸後,進入座標軸格式設定,在座標軸選項中的「座標軸類型」選取「文字座標軸」,即可隱藏這些原本日期清單中沒有的日期。
Excel-在圖表中不顯示日期清單沒有的日期
結果如下:
Excel-在圖表中不顯示日期清單沒有的日期

2017年6月10日 星期六

Excel-使用篩選功能時得知篩選結果有幾筆資料

在 Excel 中的「篩選」功能是很好用的資料處理工具。當你篩選資料後,如果想要得知篩選出來的數量,並不需要再做任何處理即可得知,只是這些資訊常被忽略而已。
當你執行了篩選的動作:
Excel-使用篩選功能時得知篩選結果有幾筆資料
當顯示篩選結果時,狀態列上即可得知本次篩選出來的資料數量。
Excel-使用篩選功能時得知篩選結果有幾筆資料
不管你執行幾層篩選,都會顯示篩選出的數量。
Excel-使用篩選功能時得知篩選結果有幾筆資料

Windows-查詢多個資料夾中包含的檔案數量

有同事問到:如何在檔案總管中,查詢多個資料夾中共包含有多個檔案?
當你在檔案總管中選取多個資料夾時,其只會顯示資料夾的數量。如果要查詢其檔案的數量,則可以在被選取的資料夾上按右鍵,選取「內容」。(本例共有6個資料夾)
Windows-查詢多個資料夾中包含的檔案數量
在[內容]對話框中即可以看到檔案的數目(本例為1146個檔案)。觀察一下資料夾的數目,和選取的資料夾數量不一致,表示資料夾中還包含了另外2個子資料夾。
image

2017年6月2日 星期五

Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

最近有些網友不約而同的問到在 Excel 中使用下拉式清單的問題。如下圖,在一個進貨商的資料清單A1~A7中,每一個包含的資料清單數量並不相同。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

【方法一:清單內容不變動】
如果清單項目的內容不會變動,則可以使用名稱定義+INDIRECT函數來處理。
1. 選取儲存格A2:H8。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)
2. 在[特殊目標]對話框中選取「常數」,結果只會選取有資料的儲存格。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)
3. 在儲存格選取狀態下,按 Ctrl+Shift+F3 鍵,勾選「最左欄」項目。
如此可以進貨商的項目名稱定義為名稱。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)
名稱定義結果如下:(因為名稱A1和儲存格的位址相同,所以會自動加上「_」。)
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)
4. 選取儲存格B12,進入[資料驗證]對話框。在[資料驗證準則]區中設定:
儲存格內允許:清單;來源:=INDIRECT(A12&"_")。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)

【方法二:清單內容會變動】
如果清單項目的內容會變動,則可以在資料驗證中使用 OFFSET 處理。
選取儲存格A1:A8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:進貨商。
Excel-產生動態的下拉式清單內容(資料驗證,INDIRECT,OFFSET,COUNTA)
接著,選取儲存格B12,進入[資料驗證]對話框。在[資料驗證準則]區中設定:
儲存格內允許:清單;來源:=OFFSET($A$1,MATCH(A12,進貨商,0),1,1,
COUNTA(OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7)))
其中:
(1) MATCH(A12,進貨商,0)
找出儲存格A12內容在進貨商中的位置。(傳回一個數字)
(2) OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7)
根據第(1)式的傳回值代入 OFFSET 函數,找出儲存格A12所對應的資料區間(本例為:儲存格B6:H6)
(3) COUNTA(OFFSET($A$1,MATCH(A12,進貨商,0),1,1,7))
計算在儲存格A12所對應的資料區間有幾個有文字的儲存格。(本例傳回5)
(4) OFFSET($A$1,第(1)式,1,1,第(3)式)
找出儲存格A12所對應的資料區間(本例為:儲存格B6:H6)中有內容的儲存格(本例為:儲存格B6:F6)。

好康東東