贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前分類:講義資料 (1883)

瀏覽方式: 標題列表 簡短摘要

網友根據這篇: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 次以上,若是,則顯示儲存格位址,若不是,則顯示空字串。

, ,

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到 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)式的傳回值(二進制數)轉換為十六進制數。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友提問:在 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"))))

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:在 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)式傳回的錯誤訊息以空白(空字串)顯示。

, ,

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

網友提問:

如下圖,在 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)。

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友根據這篇: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試算表中執行向下填滿空白儲存格

,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在實務面上會遇到在 Word 文件中要合併列印時,為了節省紙張會希望能一張紙印多份。以每頁印二份為例,在版面設定中,如果你使用單面雙頁的設計,你會發現:

版面向方為直向時,單面雙頁會將一張紙切割為上下二頁:

image

版面向方為橫向時,單面雙頁會將一張紙切割為左右二頁:

image

但是,如果你想在紙張方向為橫向時,切割紙張為上下二頁,該如何處理?

實例:以一張A4紙張列印二份的學生繳費收據(含學校存根聯和學生收執聯),再將其切割成二份(不同編號)。

1. A4紙張上下分為二個不同編號,左右各有一張學校存根和學生收執(如下圖)

image

方法:利用表格來定位資料。

當使用2X2的表格時,第一列要印第一份,第二列要印第二份。因為合併列印會由左而右、由上而下列印。所以在第二列最前面使用 Next Record 功能變數。

image

合併列印如果如下:

image


2. A4紙張左右分為二個不同編號,上下各有一張學校存根和學生收執(如下圖)

image

方法:將文件版面設定為二欄。

Word 的合併列印會先處理左欄,再處理右欄。所以 Next Record 功能變數放在右欄的最前面。

image

合併列印結果如下:

image

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到 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)*數值)

, , ,

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到的 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.。

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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)

,

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到 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)<TIME(1,30,0)

因為加班超過30分鐘才起算加班。

TIME(1,30,0):找出1.5小時代表的數值。

D2-TIME(17,30,0)<TIME(1,30,0) 用以判斷儲存格D2的時間是否已加班超過30分鐘,並且在二小時以下。

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。

, ,

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 的工作表中(如下圖),如果想要根據日期清單列出不重覆的日期,該如何處理?

手動方式可以參考以下二篇文章:

Excel-手動產生不重覆項目(樞紐分析表)

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 函數將錯誤訊息轉換為空字串(顯示為空白)。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到 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)

,

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()

網友問到:在 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

......

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()

網友問到:在 Excel 中有一個資料表(如下圖左),由日期和數值欄位組成。當繪製其統計圖表時,卻出現了原本日期清單中沒有的日期(星期六日),該如何取消顯示這些日期?

在新增圖表時,Excel 判斷了A欄是日期時,即自動以連續的日期來顯示圖表,所以必須要在圖表的座標軸格式設定中處理。

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

選取座標軸後,進入座標軸格式設定,在座標軸選項中的「座標軸類型」選取「文字座標軸」,即可隱藏這些原本日期清單中沒有的日期。

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

結果如下:

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中的「篩選」功能是很好用的資料處理工具。當你篩選資料後,如果想要得知篩選出來的數量,並不需要再做任何處理即可得知,只是這些資訊常被忽略而已。

當你執行了篩選的動作:

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

當顯示篩選結果時,狀態列上即可得知本次篩選出來的資料數量。

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

不管你執行幾層篩選,都會顯示篩選出的數量。

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友問到:如下圖,在 Excel 中有一個成績表,如果想要將有一次以上缺考者的成績不列入排名,該如何處理?

如果你的成績表中有出現文字(例如:缺考),則利用 SUM 函數運算時,會將文字視為 0,所以仍可得一個成績。

Excel-缺考者的總分不列入排名(COUNTIF,IFERROR,RANK)


【公式設計與解析】

1. 將一次以上缺考者的總分列為「缺考」

儲存格I2:=IF(COUNTIF(B2:G2,"缺考")>0,"缺考",SUM(B2:G2))

利用 COUNTIF 函數來計算資料範圍內含有「缺考」者的數量,結果若超過 0,則顯示缺考,否則列出總分。

2. 根據新的總分來排名

儲存格J2:=IFERROR(RANK(I2,$I$2:$I$21),"")

利用 IFERROR 函數將第(1)式中傳回「缺考」者在 RANK 函數中產生的錯誤訊息轉換為空字串。(其結果有缺考者不會列入排名)

, , ,

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

最近有些網友不約而同的問到在 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)。

, , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:如何將一個數字的『個位數』轉換為 0 和 5?其規則為「 0~4:0」和「5~9:5」。

Excel-依規則轉換數字的個位數(ROUNDDOWN,MOD)


【公式設計與解析】

儲存格C2:=ROUNDDOWN(A2,-1)+(MOD(A2,10)>4)*5

複製儲存格C2,貼至儲存格C2:C16。

(1) ROUNDDOWN(A2,-1)

利用 ROUNDDOWN 函數將儲存格A2的數字予以無條件捨去至個位數(個位數為 0)。(注意其中使用參數『-1』)

(2) (MOD(A2,10)>4)*5

利用 MOD(A2,10) 求得儲存各A2的個位數,則判斷是否大於 4,傳回 TRUE/FALSE。透過『*5』運算,可以將 TRUE/FALSE 轉換為 1/0,再將結果乘以 5。


【延伸學習】

關於 Excel 的進位問題,可參考:Excel-各種進位方式

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友想要根據 Excel 工作表中的基本資料(如下圖),列出含有指定字串的資料清單,該如何處理?

例如下圖中,要列出每一項目中含有「BBB」字串者,其中有可能某一項中有一個以上符合。為了方便說明,並且簡化公式,特別使用「輔助欄位」。

Excel-列出含有特字元的清單(SMALL,ROW,COLUMN,陣列公式)


【公式設計與解析】

1. 輔助欄位

儲存格I2:{=SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格I2,貼至儲存格I2:I13。

(1) IF(FIND($B$16,B2:H2)>1,1,0)

在陣列公式中利用 FIND 函數,找出是否同一列中有一個(含)以上包含指定字串的內容。若是(TRUE),則傳回 1,若否(FALSE),則傳回 0。(注意:FIND 函數若是儲存格中找不到含有指定字串,則會傳回錯誤訊息。)

(2) IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),"")

利用 IFERROR 函數將傳回值是錯誤訊者,轉換為空字串。

(3) SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))

在陣列公式中,以 SUM 函數將傳回的 1/0 予以相加。


2. 列出清單

你可以使用「篩選」功能將含有特定字串的項目列出,以下要以公式方式來產生。

儲存格A17:{=IFERROR(OFFSET($A$1,SMALL(IF($I$2:$I$13>0,
ROW($A$2:$A$13),""),ROW(1:1))-1,COLUMN(A:A)-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格A17,貼至儲存格A17:H27。

(1) IF($I$2:$I$13>0,ROW($A$2:$A$13),"")

判斷儲存格I2:I13中是否大於0(表示含有指定字串),若是,則傳回其列號;若否,則傳回空字串。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數由小至大找出符合者。(ROW(1:1)向下複製時,會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。)

(3) OFFSET($A$1,第(2)式-1,COLUMN(A:A)-1)

將第(2)式的結果代入 OFFSET 函數,找出以儲存格A1起始的對應儲存格內容。

(4) IFERROR(第(3)式,"")

因為 SMALL 函數可能傳回錯誤訊息,所以使用 IFERROR 函數將其顯示為空字串。

, , , ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼