2017年5月30日 星期二

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

網友問到:如何將一個數字的『個位數』轉換為 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-列出含有特字元的清單(SMALL,ROW,COLUMN,陣列公式)

網友想要根據 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 函數將其顯示為空字串。

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

每隔一段時間就會有網友問到關於在 Excel 中如何製作多層的下拉式清單的問題。參考下圖,當選取了「類別」中的一個項目後,在項目1中顯示該項目所屬的清單。在項目2中跟著顯示項目所選的項目來顯示所選取的清單,該如何處理?
Excel-建立多層下拉式清單(資料驗證,INDIRECT) Excel-建立多層下拉式清單(資料驗證,INDIRECT) Excel-建立多層下拉式清單(資料驗證,INDIRECT)
本例先以三層的下拉式清單為例,讀者再自行擴充成你要的層數。本例的作法需要轉換表格,雖然比較費工,卻是對大多人較容易接受。參考以下的操作步驟:
1. 將表一轉換至表二、表三、表四。
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
2. 選取儲存格H1:K14。
3. 按 Ctrl+G 鍵,選取[特殊]按鈕。
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
3. 選取[常數]選項,按一下[確定]按鈕。(目的:選取有資料的儲存格)
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
結果如下:
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
4. 按 Ctrl+Shift+F3 鍵,勾選「最左欄」。(目的:以最左欄的儲存格內容定義名稱)
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
結果如下:
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
5. 選取儲存格B21,進入[資料驗證]對話框中,設定資料驗證準則:
儲存格內允許:清單;來源:=類別。
6. 選取儲存格C21,進入[資料驗證]對話框中,設定資料驗證準則:
儲存格內允許:清單;來源:=INDIRECT(B21)。
Excel-建立多層下拉式清單(資料驗證,INDIRECT)
7. 選取儲存格D21,進入[資料驗證]對話框中,設定資料驗證準則:
儲存格內允許:清單;來源:=INDIRECT(C21)。

【延伸練習】
依照以上的作法,如果要更多層相關的下拉式清單,則必須建立更多的表格,建立更多以資料驗證設定清單的儲存格。

【注意事項】
在本例中下拉式清單的內容含有中文字,如果你使用類似A1、C20等當為名稱會造成果擾。相同作法之下,定義的名稱會轉換如下的結果:
Excel-建立多層下拉式清單(資料驗證,INDIRECT)

Excel-計算休假天數

網友提問:如果工作滿1年有7天假期,工作不滿3個月0天假期,工作滿3個月可以按比率得到7天的假期,該如何設計公式(參考下圖)? (比例計算公式=受僱天數/365天 * 7=得到的假期 (進位至整數))
Excel-計算休假天數

【公式設計與解析】
儲存格C2:=ROUND((B2>=90)*(IF(B2>365,365,B2)/365)*7,0)
複製儲存格C2,貼至儲存格C2:C11。
(B2>=90):條件一,判斷儲存格B2是否大於 90,傳回 TRUE/FALSE(經過『*』運算後,會轉換為 1/0。)
(IF(B2>365,365,B2):條件二,判斷儲存格B2是否大於 365,若是,則傳 365,若否,則傳回儲存格B2。
儲存格C2:=ROUND((B2>=90)*((B2>365)*365+(B2<365 p="">

2017年5月28日 星期日

Google試算表-抓取網頁資料(IMPORTHTML)

如果你要使用 Excel 來抓取網頁上的表格資料,可以參考以下的文章:
相同的動作,如果要在Google 試算表中執行,則可以 IMPROTHTML函數。
Google試算表-抓取網頁資料(IMPORTHTML)
例如,要抓取本部落格(http://isvincent.pixnet.net)的文章清單第 28頁:
Google試算表-抓取網頁資料(IMPORTHTML)
在儲存格A1輸入:
此處的參數 table 是指網頁中資料結構為表格者,參數 3 為第 3 個。
當你輸入完公式,按下 Enter 鍵後,速度很快的就會顯示匯入的結果:
(匯入的資料是靜態結果,並不會跟著網頁內容更新而變動。)
Google試算表-抓取網頁資料(IMPORTHTML)
如果你將公式中的參數 table 改成 list,則會得到以下的結果:
Google試算表-抓取網頁資料(IMPORTHTML)
對應至網頁中的:
Google試算表-抓取網頁資料(IMPORTHTML)
其他相關的函數還有:
IMPORTXML:匯入多種結構化資料類型的資料,包括 XML、HTML、CSV、TSV 和 RSS 以及 ATOM XML 資訊提供。
IMPORTRANGE:匯入指定試算表中特定儲存格的範圍。
IMPORTFEED:匯入 RSS 或 ATOM 資訊提供。
IMPORTDATA:將指定網址的資料匯入成 .csv (逗號分隔值) 或 .tsv (Tab 分隔值) 格式。

2017年5月27日 星期六

Excel-天、時、分的運算(INT,MOD)

網友問到 Excel 的問題:在下圖中有二個清單包含了天和時的資料,如何將兩個數據予以加總?其中 1min = 0.1H(1分鐘=0.1小時)
以下圖中的第一列為例:1天14.41時+2天21.21時=4天12.02時。
Excel-天、時、分的運算(INT,MOD)

【公式設計與解析】
1. 計算天數
儲存格G2:=(A2+D2)+INT((B2+E2)/24)
複製儲存格G2,貼至儲存格G2:G15。
INT((B2+E2)/24):計算兩個儲存格中時的和是否大於24小時,若是,則進位 1 天。

2. 計算時數
儲存格H2:=MOD(INT(B2)+INT(E2),24)+INT((B2-INT(B2)+E2-INT(E2))/0.6)+
MOD(B2-INT(B2)+E2-INT(E2),0.6)
複製儲存格H2,貼至儲存格H2:H15。
(1) MOD(INT(B2)+INT(E2),24)
計算小時數是否有超過24小時,若有則取其進位後的小時數。
(2) INT((B2-INT(B2)+E2-INT(E2))/0.6)
計算小時數是否有超過0.6時,若有則進位 1 小時。
(3) MOD(B2-INT(B2)+E2-INT(E2),0.6)
計算小時數是否有超過0.6時,若有,則取其進位後的小時數。

Excel-將英文字元轉換為對應的數字(VLOOKUP,MID)

網友問到 Excel 的問題:在工作表中的資料表有 9 個字元,分別對應數字 1~9(參考下圖右),如何將數個字元的字串轉換為數字?
在原始的欄位中的儲存格裡有多個不同數量的字元要轉換為數字,例如:在下圖左中 FCCG 要轉換為數字 6377,該如何處理?
Excel-將英文字元轉換為對應的數字(VLOOKUP,MID)

【公式設計與解析】
儲存格B2:
=IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,2,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,3,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,4,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,5,1),$F$2:$G$10,2,TRUE),"")

複製儲存格B2,貼至儲存格B2:B20。
以 IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"") 來說明:
(1) MID(A2,1,1)
利用 MID 函數,取出儲存格A2中的第 1 個字元。(改變第 2 個參數即可調整取出第幾個字元,但是如果要取出的字元超出儲存格的字元數,則會傳回錯誤訊息。)
(2) VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE)
利用 VLOOKUP 函數將取出第(1)式取出的字元在對應表中查詢到對應的數字。
(3) IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"")
由於第(1)式可能會傳回錯誤訊息,則以 IFERROR 函數將錯誤訊息轉換為空字串。

2017年5月26日 星期五

Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

網友問到:在 Excel 的工作表中(如下圖),如何在D欄、E欄、F欄中,只能輸入同一列的A欄、B欄、C欄的內容?(共有1000列要套用同一規則)
參考下圖,例如在儲存格D1:F25範圍,利用下拉式清單,讓每儲存格只能輸入A1:C25中對應同一列中內容。
Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)
【設計與解析】
基本上這是要利用「資料驗證」的方式來產生下拉式清單。如下操作:
1. 選取儲存格D1。
2, 設定儲存格內允許:清單;來源:=$A1:$C1。
注意:欄的部分要使用絶對參照,列的部分要使用相對參照。
Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)
3. 複製儲存格D1,貼至儲存格D1:F25。
Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)
以上無法防制使用者自行輸入清單以外的內容,如果你要限制只能輸入A欄、B欄、C欄中的其中一個,則必須將資料驗證改設定為:
設定儲存格內允許:自訂;來源:=COUNTIF($A1:$C1,D1)=1。
複製儲存格D1,貼至儲存格D1:F25。
Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

2017年5月25日 星期四

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

同事常會問到 Excel 真的那麼有用?很多公式我也不懂,學 Excel 要做什麼?我用以下的例子做了一部分的回應,重點是資料的處理。
例如:在圖書館的流通系統中匯出了一個200頁的文件,其中是學生的圖書借用資料。現在高三要畢業了,必須找出那些學生沒有還清圖書。如果系統有這個功能,那就不用傷腦筋了,問題是沒有。所以打算根據學生的借用資料來得到想要的結果,然而由系統匯出的資料並不是可以直接處理的資料(如下圖)。
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
其中的文字沒有排的很整齊,更不用說進一步的處理了。
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
這個時候,Excel 就派上用場了!
先把文字檔中的資料複製到 Excel 的工作表中:
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
接著,執行「資料剖析」:
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
資料變的整齊多了:
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
觀察上圖中的資料,其中A,B,C欄內的資料並不完整。先來補足其中的資料:
1. 選取A欄至C欄。
2. 按 Ctrl+G 鍵,開啟[到]對話框。
3. 按[特殊]按鈕。
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
4. 選取[空格]選項,按下[確定]按鈕。
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
5. 輸入公式「=A1」,按 Ctrl+Enter 鍵。
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
6. 結果如下:(空白的資料已經自動補上)
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
7. 複製A欄至C欄,在貼上時選取[選擇性貼上/值]選項,結果會把公式去除。
接著,對這個資料表執行「排序」:(利用「還書日期」欄位遞增排序)
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
排序結果的前面幾列就是我要的未還書清單:(還書日期空白者)
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
再看一次原始沒有規則性的資料排列:
Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)
在使用 Excel 的操作(資料剖析、選取空白儲存格、大量輸入公式、排序)之後,就可以得到你想要的資料。其中並沒有使用複雜的公式計算,都是常用的操作技巧。輕易的就成功的資料處理了!

2017年5月24日 星期三

Excel-錯誤訊息的處理(IFERROR,ISERROR)

在 Excel 中當我們在執行查詢時,有時會有錯誤的訊息產生,該如何避免出現這些訊息,而改以其他適當的方式來呈現?
在下圖中,當你使用 VLOOKUP 函數查詢時,若是查詢到空的儲存格,則會傳回 0,但是應該是顯示空白比較恰當。或是使用錯誤內容來查詢,正常會傳回 #N/A 這類的訊息,該如何以其他文字來回應呢?
Excel-錯誤訊息的處理(IFERROR,ISERROR)
【公式設計與解析】
(1) 儲存格E4:=VLOOKUP(D4,A2:B17,2,FALSE)
當使用 VLOOKUP 函數查詢,由於儲存格B7為空白,所以應該傳回空白,而非傳回 0。
(2) 儲存格E5:=VLOOKUP(D5,A2:B17,2,FALSE)&""
公式修正為將原公式,再串接一個空字串『""』即可解決。
(3) 儲存格E9:=VLOOKUP(D9,A2:B17,2,FALSE)
這是要查詢「王」這個項目所對應的數值。
(4) 儲存格E10:=VLOOKUP(D10,A2:B17,2,FALSE)
如果不小心以「壬」來查詢,則會傳回 #N/A 錯誤訊。
(5) 儲存格E11:=IFERROR(VLOOKUP(D10,A2:B17,2,FALSE),"查無此項")
利用 IFERROR 函數,將 #N/A 訊息,改以『查無此項』文字來顯示。
(6) 儲存格E11:=IF(ISERROR(VLOOKUP(D10,A2:B11,2,FALSE)),"查無此項",
VLOOKUP(D10,A2:B11,2,FALSE))
如果你使用的 Excel 試算表沒有 IFERROR 函數,則可以使用 ISERROR 來處理。

2017年5月23日 星期二

Excel-計算符合條件者的不重覆數量(SUMPRODUCT,COUNTIF)3

網友問到:在 Excel 中有一個資料表,如何計算出每個日期的配編數有幾類?
(請參考下圖)
image

【公式設計與解析】
為了簡化公式,特別利用二個輔助欄位。
1. 搜尋指定日期的起始列號
儲存格F2:{=MIN(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),999))}
這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
2. 搜尋指定日期的結束列號
儲存格E2:{=MAX(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),0))}
這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
3. 完整公式
儲存格G2:=SUMPRODUCT((1/COUNTIF(OFFSET($B$1,F2-1,0,G2-F2+1,1),
OFFSET($B$1,F2-1,0,G2-F2+1,1))))
請參考其他說明:

Excel-資料交錯排列組合(OFFSET,INT,MOD)

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?
在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?
Excel-資料交錯排列組合(OFFSET,INT,MOD)

【公式設計與解析】
(1) 處理數字部分
儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)
利用 INT 函數將除法結果再取不大於的最大整數。
公式:INT((ROW(1:1)-1)/4)
當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。
儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。
(2)處理文字部分
儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)
當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。
儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。
(3) 整合文字和數字
儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))
MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。
公式判斷,每隔四列時,會顯示數字,其餘顯示文字。
當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

2017年5月21日 星期日

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

網友問到:在 Excel 的活頁簿中有 6 個工作表(A單位、B單位、C單位、D單位、E單位、F單位),如何計算這個 6 個工作表中相同位置儲存格的和?
這 6 個工作表的格式一致,其中「數值」欄位的內容不各有不同。
Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)
在下圖中,分別計算A單位~F單位的甲乙丙、丁戊己庚、辛壬癸之小計,即根據工作表名稱,來摘要各工作表的小計,該如何處理?
Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

【公式設計與解析】
儲存格B2:=SUM(INDIRECT(A2&"!B2:B4"))
先將工作表名稱(儲存格A2)和字串『!B2:B4』加以串接,再利用 INDIRECT 函數將其轉換為儲存格位址。
儲存格C2:=SUM(INDIRECT(A2&"!B5:B8"))
儲存格D2:=SUM(INDIRECT(A2&"!B9:B11"))
複製儲存格B2:D2,貼至儲存格B2:D7。

2017年5月20日 星期六

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?
如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。
Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)
【公式設計與解析】
選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。
儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))
在 SUMPRODUCT 函數中利用二個陣列來運算:
LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。
MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)
公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

2017年5月18日 星期四

如何將PDF檔列印在比A4還大的紙張

以前的文章提過,如果在 Excel 的工作表中置入一張相片,如果放大到幾個A4版面時,列印出來的紙張就可以拼貼成一張大海報了。(例如下圖的相片跨兩張 A4 紙張)
如何將PDF檔列印在比A4還大的紙張
如果你將放有照片的工作表版面,直接放大到你要輸出的紙張(沒有要拼貼了)。下圖已將工作表調整為 A3 版面。
如何將PDF檔列印在比A4還大的紙張
當你把 Excel 另存新檔時,可以直接轉換為 PDF 檔。
如何將PDF檔列印在比A4還大的紙張
當你要列印這個 PDF 檔時,預設會遇到其自動切割成 A4 大小,該如何印在 A3 的紙上呢?
如何將PDF檔列印在比A4還大的紙張
如何將PDF檔列印在比A4還大的紙張
請你在列印時,在「大小」中勾選「依PDF頁面大小選擇紙張來源」。PDF 就不會縮放,也不會切割紙張了。
如何將PDF檔列印在比A4還大的紙張
或是在版面設定中先行改變紙張大小(例如:A3),就可以實際大小來列印了。
如何將PDF檔列印在比A4還大的紙張

檢視其他文章

好康東東