2014年9月30日 星期二

Excel-取出清單中非空白儲存格內容重新排列(SMALL,IFERROR)

有網友問到:在一個 Excel 工作表中有一個數列清單,其中有些儲存格有資料,有些儲存格沒有任何內容,如何將有內容的儲存格集合在一起(重新排列)?

參考下圖,分就水平資料和垂直資料來處理。

(1)

儲存格J2:=IFERROR(SMALL($A$2:$H$2,ROW(1:1)),"")

複製儲存格J2,貼至儲存格J2:J9。依序取出最小到最大的值重新排列。

儲存格J12:=IFERROR(SMALL($G$12:$G$19,ROW(1:1)),"")

複製儲存格J12,貼至儲存格J12:J19。依序取出最小到最大的值重新排列。

(2)

儲存格B6:=IFERROR(SMALL($B$2:$I$2,COLUMN(A:A)),"")

複製儲存格B6,貼至儲存格B6:I6。依序取出最小到最大的值重新排列。

儲存格B18:=IFERROR(SMALL($B$9:$B$16,COLUMN(A:A)),"")

複製儲存格B18,貼至儲存格B18:I18。依序取出最小到最大的值重新排列。

2014年9月29日 星期一

Excel-列出清單中各個項目的數量及排序(SUMPRODUCST,COUNTIF)

有網友問到:在 Excel 的工作表中有一個商品的清單(參考下圖左),如何找出各個商品的出現的次數,並且做出排行?(參考下圖右)

【準備工作】

選取B欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:名稱。

 

【輸入公式】

儲存格E2:=SUMPRODUCT(--(名稱=D2))

公式中 SUMPRODUCT 函數使用的「--」運算,會將 (名稱=D2) 所得的判斷結果(一個 TRUE/FALSE 的陣列),轉換為 1/0 的陣列。

儲存格F2:=RANK(E2,$E$2:$E$8)

複製儲存格E2:F2,貼至儲存格E2:F8。

 

【延伸學習】

可以試試使用 COUNTIF 函數來完成以上兩個式子的運算:

儲存格E2:=COUNTIF(名稱,D2)

儲存格F2:=COUNTIF($E$2:$E$8,">"&E2)+1

2014年9月28日 星期日

Excel-在LOOKUP函數中使用雙條件查詢

有網友問到在 Excel 的資料表中有一個資料清單(參考下圖右),如何根據這個資料清單給予二個條件查表得到結果(參考下圖左)?

【備準工作】

選取儲存格E1:G18,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、勞點時數、勞點點數。

【輸入公式】

儲存格C2:=LOOKUP(1,1/((A2=項目)*(B2=勞點時數)),勞點點數)

(A2=項目)*(B2=勞點時數):條件一/判斷儲存格A2和項目陣列中的內容是否相符,傳回 TRUE/FALSE 的陣列;判斷儲存格B2和勞點時數陣列中的內容是否相符,傳回 TRUE/FALSE 的陣列。其中「*」,乃執行邏輯 AND 的運算,在運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

1/((A2=項目)*(B2=勞點時數)):當 1/1 時,傳回 1;當 1/0 時,傳回 #DIV/0! 訊息。最後得到含有一個「1」,其餘項目皆為「#DIV/0!」的陣列。

最後在 LOOKUP 函數中查詢上述陣列中「1」對應到勞點點數陣列中的內容,即為所求。

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

2014年9月27日 星期六

Excel-將日期分隔為年月日(INDEX,MID,TEXT)

在 Excel 中能接受的日期格式,例如:「西元年/月/日」或是「西元年-月-月」,輸入後都會轉換為:西元年/月/日。常見國人會輸入日期格式:西元年‧月‧日,無法被 Excel 接受。如果要將日期分開年、月、日來顯示該如何處理呢?

參考下圖(上)的「日期格式一」為例,你可能最常使用 MID 函數來處理:

儲存格B2:=MID(A2,1,4),儲存格C2:=MID(A2,6,2),儲存格D2:=MID(A2,9,2)

在網路上看到有人用以下的公式,覺得可以用在陣列觀念的練習:

儲存格B2:=INDEX(MID($A2,{1,6,9},{4,2,2}),COLUMN(A2))

藉助查表公式 INDEX 函數,將上述的三個 MID 函數,併入一個公式中。其中 COLUMN(A2)=1,當往右欄位複製/貼上時,會產生COLUMN(B2)=2、COLUMN(C2)=3。

複製儲存格B2,貼至儲存格B2:D2。複製儲存格B2:D2,貼至儲存格B2:D8。

參考上圖(下),如果以「日期格式二」為例,每個日期的長度並不一致,如果要使用 MID 函數,也會很辛苦,因為每個公式都要修改。

所以使用 INDEX 函數是一個好的選擇,但日期參數要先稍加變化:

儲存格B11:==INDEX(MID(TEXT($A11,"yyyy/mm/dd"),{1,6,9},{4,2,2}),COLUMN(A11))

TEXT($A11,"yyyy/mm/dd"):使用 TEXT 函數配合參數:yyyy/mm/dd,將日期格式轉換為年四碼、月二碼、日二碼的格式。

複製儲存格B11,貼至儲存格B11:D11。複製儲存格B11:D11,貼至儲存格B11:D18。

2014年9月26日 星期五

Excel-在日期區間數列中小計分年分月的人數3(SUMPRODUCT)

根據前二篇文章:

Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT)
Excel-在日期區間數列中小計分年分月的人數2(SUMPRODUCT)

本篇要延伸列出每個月通過認證的名單,本例僅以 2014 年1 ~ 12 月為例,參考下圖。

【準備工作】

選取欄A和欄B中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:成員、期間。

 

【輸入公式】

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(MID(期間,1,4),
MID(期間,6,2),1)<=DATE(2014,COLUMN(A:A)+1,0))*(DATE(MID(期間,9,4),
MID(期間,14,2),1)>=DATE(2014,COLUMN(A:A),1)),ROW(期間),FALSE),
ROW(1:1))-1,,,),"")}

這是陣列公式,輸入完畢要按 Ctrl+Shfit+Enter 鍵,Excel 自動產生「{  }」。

複製儲存格D2,貼至儲存格D2:O27。

(1)

IF((DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(2014,COLUMN(A:A)+1,0))*
(DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(2014,COLUMN(A:A),1)),
ROW(期間),FALSE)

該公式乃要判斷期間陣列中頭尾月份中是否包含1月(COLUMN(A:A)=1),若是則傳回儲存格在期間中的第幾個,否則傳回 False

本例傳回陣列:{FALSE,FALSE,FALSE,FALSE,FALSE,...,20,FALSE,...},其中有19個FALSE,接著20,接著6個FASLE。

(2)

SMALL(上述(1)公式,ROW(1:1)):在陣列公式中利用 SMALL 函數找出符合條件的第一個最小值位置。本例傳回:20。

(3)

OFFSET($A$1,SMALL(上述(1)公式,ROW(期間),FALSE),ROW(1:1))-1,,,)

將上述 (2) 公式所得的數值(符合條件的第一個最小值位置)代入 OFFSET 函數,傳回以儲存格A1為起點的相對位置。本例傳回:儲存格A20。

(4)

IFERROR(OFFSET($A$1,SMALL(上述(1)公式,ROW(期間),FALSE),ROW(1:1))-1,,,),"")

藉由 IFERROR 函數將傳回錯誤值的結果以空白顯示。

Excel-週期性取出儲存格資料重新排列(INT,MOD,OFFSET)

有網友問到:在 Excel 的工作表中有一連續排列的資料,若要取出儲存格重新排列,例如,不要取出第1~18筆資料、要取出第19~64筆資料(有46筆)、不要取出65~82筆資料(18筆)、取出第83~128筆資料(46筆)、...,該如何處理?

為了方便理解,先簡化內容。參考下圖為例來說明,其中項次 1-4 不取用(4個)、5-10 要取用(6個)、11-14 不取用(4個)、15-20 要取用(6個)、...。

依題意,每10個為一個週期,前4個不顯示,後6個要顯示。

【解法一】藉助輔助欄位(D欄)

(1) 在儲存格D2輸入 5。

(2) 輸入公式:儲存格D3:=IF(MOD(D2,10)<4,D2+5,D2+1)

MOD(D2,10):使用 MOD 函數判斷儲存格D2除以10的餘數。

IF(MOD(D2,10)<4,D2+5,D2+1) :如果上式的餘數小於 4,則輸出儲存格D2+5,否則輸出儲存格D2+1。結果為:5,6,8,9,10,1516,17,18,19,20, ... 。

複製儲存格D3,往下各列貼上公式。

儲存格E2:=OFFSET($B$1,D2,,,)

透過 OFFSET 函數,利用儲存格D2來取用根據儲存格B1的相對位址。

複製儲存格E2,往下各列貼上公式。

 

【解法二】不需藉助輔助欄位(D欄)

儲存格E2:=OFFSET($B$1,MOD(ROW(1:1)-1,6)+INT((ROW(1:1)-1)/6)*10+5,,,)

MOD(ROW(1:1)-1,6):產生 0,1,2,3,4,5,0,1,2,3,4,5,0,1,2,3,4,5, ... 數列。

其中參數 6 為要顯示的個數。

INT((ROW(1:1)-1)/6)*10+5:產生 5,5,5,5,5,5,15,15,15,15,15,15,25,25,25,25,25,25, ... 數列。

其中參數 6 為要顯示的個數,參數 10 為一個週期的個數,參數 5 為一個週期的起始值。

複製儲存格E2,往下各列貼上公式。

 

【本題解答】

儲存格E2:=OFFSET($B$1,MOD(ROW(1:1)-1,46)+INT((ROW(1:1)-1)/46)*64+19,,,)

其中參數 46 為要顯示的個數,參數 64 為一個週期的個數,參數 19 為一個週期的起始值。

複製儲存格E2,往下各列貼上公式。

2014年9月25日 星期四

Dropbox-使用進階搜尋

當你在使用 Dropbox 時要找尋某些檔案,除了使用瀏覽的方式之外,使用「搜尋」工具應該是較方便省時省事的。有同事問到,如何使用除了「關鍵字」搜尋之外的進階搜尋呢?

當你在搜尋框輸入關鍵字時:

當下即會在搜尋框的左側顯示「進階搜尋」字樣:

按一下「進階搜尋」,即可使用「包含其中一個單字」、「不包含其中任何一個單字」、「精確對應字詞」、等搜尋條件,也可調整搜尋範圍:檔案、資料夾、已刪除的項目:

例如:下圖中搜尋「xmind」,找到了二個檔案:

使用「包含其中一個單字」,輸入「計畫」,只找到了一個檔案:

使用「不包含其中任何一個單字」,輸入「悅讀」,只找到了一個檔案:

使用「精確對應字詞」,輸入「快樂」,只找到了一個檔案:

2014年9月24日 星期三

Excel-在日期區間數列中小計分年分月的人數2(SUMPRODUCT)

根據上一篇文章:Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT)

要來修改統計方式。參考下圖,要在日期區間數列中小計分年分月的人數,但是要在期間中的每一月份分開統計,例如:A 成員在 5, 6, 7, 8, 9, 10 月都要被加計 1,B 成員在 10, 11, 12, 1, 2 月被加計 1,依此類推。

【準備工作】

選取欄B中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:期間。

 

【輸入公式】

儲存格F2:=SUMPRODUCT((DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1))*
(DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1)))

(1) DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1)

MID(期間,1,4):取期間陣列中的日期第 1 ~ 4 個字元,以儲存格B2為例,結果為 2014。

MID(期間,6,2),1):取期間陣列中的日期第 6 ~ 7 個字元,以儲存格B2為例,結果為 05。

DATE(D2,E2,1):以儲存格D2和儲存格E2代入日期,以儲存格F2為例,結果為 2014/1/1。

DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(D2,E2,1):由儲存格B2取得一個年/月,判斷是否「小於或等於」由儲存格D2(年)和儲存格E2(月)和1(日)組成的日期。

(2) DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1)

MID(期間,9,4):取期間陣列中的日期第 9 ~ 12 個字元,以儲存格B2為例,結果為 2014。

MID(期間,14,2),1):取期間陣列中的日期第 14 ~ 15 個字元,以儲存格B2為例,結果為 10。

DATE(D2,E2,1):以儲存格D2和儲存格E2代入日期,以儲存格F2為例,結果為 2014/1/1。

MID(期間,9,4),MID(期間,14,2),1)>=DATE(D2,E2,1):由儲存格B2取得一個年/月,判斷是否「大於或等於」由儲存格D2(年)和儲存格E2(月)和1(日)組成的日期。

若符合 (1) 和 (2) 條件者以 SUMPRODUCT 函數計算總和。公式中的「*」運算,相當於執行邏輯 AND 運算。所以在運算過程中,TRUE/FALSE 陣列會轉換為 1/0 陣列,SUMPRODUCT 函數會執行「乘積和」,因此計算最後 1 的個數,即為分年分月的個數。

2014年9月23日 星期二

Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT,LEFT,RIGHT)

有網友問到:在 Excel 的工作表中有一個認證期間的日期數列,而這個日期數列是由兩個日期組成的文字(參考下圖右),現在希望能在這個數列中分年分月的計數人數,該如何處理?(參考下圖左)(認證完成日以期間最後的年月份為準)

 

【準備工作】

選取欄B中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:期間。

 

【輸入公式】

(1)

儲存格F2:=SUMPRODUCT((VALUE(LEFT(期間,4))=D2)*(VALUE(RIGHT(期間,2))=E2))

LEFT(期間,4):取出「期間」陣列的每個期間文字最左邊四個字元。(此為年份文字)

VALUE(LEFT(期間,4)):將上式中取得的四個字元文字,轉換為數字。(此為年份數字)

VALUE(LEFT(期間,4))=D2:判斷上式中的數字是否等於儲存格D2的內容,傳回 TRUE/FALSE 的陣列。

(2)

RIGHT(期間,2):取出「期間」陣列的每個期間文字最右邊二個字元。(此為月份文字)

VALUE(RIGHT(期間,2)):將上式中取得的二個字元文字,轉換為數字。(此為月份數字)

VALUE(RIGHT(期間,2))=E2:判斷上式中的數字是否等於儲存格E2的內容,傳回 TRUE/FALSE 的陣列。

(3)

最後透過 SUMPRODUCT 函數將 (1) 和 (2) 相乘,公式中的「*」運算,相當於執行邏輯 AND 運算。所以在運算過程中,TRUE/FALSE 陣列會轉換為 1/0 陣列,SUMPRODUCT 函數會執行「乘積和」,因此計算最後 1 的個數,即為分年分月的個數。

2014年9月22日 星期一

Excel-計算不連續儲存格中數字的差值

有網友想要在 Excel 的資料表中,在某些儲存格中輸入數值(參考下圖),並且想要自動計算出某個儲存格和上一個有數值的儲存格的差,該如何處理呢?

【題目分析】

若以上圖儲存格B14(=18)為例,應該在儲存格C14中顯示5(=儲存格B14-儲存格B11)。

先來觀察:

儲存格C11:=B11-B9,儲存格C9:=B9-B7,

儲存格C7:=B7-B5,儲存格C5:=B5-B2,

C5+C7+C9+C11 = (B5-B2)+(B7-B5)+(B9-B7)+(B11-B9)=B11-B2

B11=C5+C7+C9+C11+B2

根據上式,儲存格C14:=B14-B11=B14-(C5+C7+C9+C11)-B2

 

【輸入公式】

依據上述的題目分析:

儲存格C14:=IF(B14="","",B14-SUM($C$2:C13)-$B$2)

令:儲存格C2保持空白。

儲存格C3:=IF(B3="","",B3-SUM($C$2:C2)-$B$2)

複製儲存格C3,往下各列貼上。只要在B欄輸入一個數值,即可在C欄自動計算和上一個儲存格的差值。

2014年9月21日 星期日

Windows-開啟由搜尋找到的檔案所在的資料夾

一般人在找尋檔案時,如果在檔案總管中,透過「搜尋」關鍵字的方式,可以較快速的找到想要或類似的檔案,但是在使用習慣上,往往你也會想要看看該檔案所在資料夾的其他檔案。如何能快速開啟某檔案所在的資料夾呢?參考以下做法:

例如:在檔案總管裡,以某一關鍵字來搜尋檔案。某一檔案恰好是我所想要,觀察其資料夾路徑是好幾層資料夾組合的結構,如果使用視窗左側的樹狀結構一一開啟資料夾,直到想要的資料夾,可能會浪費太多時間成本。

而其實你只要在想要的檔案上按一下右鍵,選取「開啟檔案位置」:

Windows 檔案總管會自動切換並顯示這個資料夾的內容:

這樣就可以比較省力的來切換資料夾了!

Excel-在增加的日期數列中計算最近和過去7天的小計(OFFSET,COUNT)

有網友問到:如何在一個 Excel 工作表的日期/金額數列中,要計算最近 7 天和過去 7 天的小計來比較,而日期會每天不斷的增加。

參考下圖,日期每天會增加一筆(不固定),而要取出數列中最下的 7 筆資料,和最後數來第 8 到第 14 筆資料來做比較。

【準備工作】

選取所有預定會增資料的範圍,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、金額。

【輸入公式】

(1) 最近 7 天金額小計

儲存格E2:=SUM(OFFSET(A2,COUNT(日期)-1,1,-7,1))

COUNT(日期):在日期陣列中計算已輸入日期的數量。

OFFSET(A2,COUNT(日期)-1,1,-7,1):利用 OFFSET 函數,找出最近 7 天(第 1 至 7 天)的位址。

(2) 過去 7 天金額小計

儲存格E3:=SUM(OFFSET(A2,COUNT(日期)-8,1,-7,1))

OFFSET(A2,COUNT(日期)-8,1,-7,1):利用 OFFSET 函數,找出最近 7 天(第 8 至 14 天)的位址。

(3) 設定上升/下降圖示

選取儲存格E2,設定其格式化的條件,如下圖所示:

當儲存格E2>儲存格E3時,就顯示綠色上升圖示,否則顯示紅色下降圖示。

而在儲存格E3中,為了配合儲存格E2的圖示位置,所以在格式化的條件中設定了「無儲存格圖示」:

 

【自行練習】

依據上列公式的描述,你可以自行設計最近 10天 和過去 10 天的金額小計:

儲存格E6:=SUM(OFFSET(A2,COUNT(日期)-1,1,-10,1))

儲存格E7:=SUM(OFFSET(A2,COUNT(日期)-11,1,-10,1))

YouTube-指定影片開始播放位置

有老師在備課時踫到一個問題,問到:在課程中會播放 YouTube 上的影片給學生看,或是指定一個 YouTube 影片讓學生自行觀賞,是否可以指定起始播放的位置?這樣課程才受到的干擾最小,也可以節省一些找尋播放位置的時間,減少學生的不耐煩。

觀察很多影片,的確在影片最前方的許多片段都不是影片的主要內容,幾乎都是漸漸導入重點的。老師上課時,很需要一次到位的播放和課程銜接的影片,所以需要這個功能。

還好,目前是很容就可以做到的,參考以下的步驟:

先在 YouTube 上找到要播放的影片後,點選影片下方的「分享」,立即會在下方的文字方塊中得到一個短網址,例如:http://youtu.be/WTcodZDH-Oc

(原來網址:https://www.youtube.com/watch?v=WTcodZDH-Oc)

接著勾選「開始時間」,再設定起始的時間即可。例如,輸入時間為 1:40。

短網址變為:http://youtu.be/WTcodZDH-Oc?t=1m40s

你可以設定一個秒數,例如:100秒。

短網址變為:http://youtu.be/WTcodZDH-Oc?t=100

取用這兩個網址都可以讓使用者直接跳到指定位置再開始播放,讓老師的教學更精準到位。

2014年9月20日 星期六

Gmail-撰寫郵件時使用復原和取消復原快速鍵

最在無意中看到同事在 Gmail 中撰寫時,把「打錯/設錯/貼錯」等動作後,所採取的動作,發現可能有人不知道有更快的回復動作。

大家在使用 Word 軟體時,如果做錯都很習慣使用復原和取消復原的動作,其對應的快速鍵為 Ctrl + ZCtrl + Y 鍵。

而在 Gmail 撰寫文件時,其實也是可以使用復原和取消復原的操作,只是看不到按鈕/功能表等選項,所以只能靠快速鍵:Ctrl + ZCtrl + Y 鍵。

例如,在一個網址上設定了一個超連結:

按一下 Ctrl + Z 鍵,可以取消(移除)超連結設定:(本例亦可直接按一下上圖中的「移除」)

這個復原和取消復原的動作,不限一次,而是可以執行許多次的步驟。這個「復原和取消復原」可能會被忽略的動作,善用它可以提升一些效能。

順帶一提,在 Gmail 中如果按一下「?」(即 Shift + / 鍵),可以查詢相關的快速鍵。不過可能是我眼花,還是找不到復原和取消復原的快速鍵。

2014年9月18日 星期四

Excel-將「分鐘數」轉換為表示為「時:分」(INT,MOD,TEXT)

有讀者想要將在 Excel 中的一個分鐘數清單,轉換為以「時:分」表示的結果,該如何處理?(參考下圖)

【輸入公式】

儲存格B2:=TEXT(INT(A2/60),"00") & ":" & TEXT(MOD(A2,60),"00")

INT(A2/60):利用 INT 函數(轉換為不大於的最大整數),求取分鐘數轉換為「小時」數值。

MOD(A2,60):利用 MOD 函數(求兩數相除的餘數),求取分鐘數轉換為「分鐘」數值。

TEXT(INT(A2/60),"00"):使用 TEXT 函數將數值格式定為顯示 2 碼,僅 1 碼者前面補 0。

TEXT(MOD(A2,60),"00"):說明同上。

複製儲存格B2,往下各列貼上。

Excel-限定儲存格輸入的格式(資料驗證)

有網友想要在 Excel 工作表的儲存格中輸入資料時,希望只能接受特定格式的內容,其餘顯示錯誤訊息,該如何處理呢?。例如:(參考下圖)

編號的編碼規則
(1) 全部 5 碼
(2) 第 1 碼:B(必須為大寫)
(3) 第 2 碼:英文字(大小寫皆可)
(4) 第 3, 4, 5 碼:數字

通常這類的問題,大多使用「資料驗證」的手法來完成。

【參考做法】

(1) 選取想要輸入資料的儲存格。

(2) 選取[資料/資料驗證]功能表,再選取「資料驗證」選項。

(3) 在[設定]標籤下設定如下:

儲存格內允許:自訂。

公式:=AND(LEN(A4)=5,LEFT(A4,1)="B",CODE(UPPER(MID(A4,2,1)))<=90,CODE(
UPPER(MID(A4,2,1)))>=65,ISNUMBER(VALUE(MID(A4,3,3))))

LEN(A2)=5:設定「條件一/全部 5 碼」。

LEFT(A2,1)="B":設定「條件二/第 1 碼:B(必須為大寫)」

CODE(UPPER(MID(A4,2,1)))<=90:設定「條件三/第 2 碼:英文字(大小寫皆可)」

利用 MID 函數取得儲存格中的第 2 個字元,利用 UPPER 函數將該字轉換為大寫,利用 CODE 函數將該字元轉換為 ASCII 碼。

因為大寫英文字母的 ASCII 碼是介於 65 到 90 之間。所以,設定CODE(UPPER(MID(A4,2,1)))<=90 和 CODE(UPPER(MID(A4,2,1)))>=65 這兩個條件。

(關於 ASCII 碼可參考:http://isvincent.pixnet.net/blog/post/30226102)

ISNUMBER(VALUE(MID(A4,3,3))):設定「條件四/第 3, 4, 5 碼:數字」。因為 MID 函數取得儲存格A4中的第 3 碼起始的 3 碼,這三個字為文字,所以再透過 VALUE 函數將其轉換為數字,並且利用 ISNUMBER 來判斷三個連起來的文字是組合為一個數字。

最後,透過 AND 函數將以上的判斷式做邏輯 AND 的運算。

2014年9月17日 星期三

Excel-儲存格內容連結特定路徑中的活頁簿檔案

如果在 Excel 的儲存格中想要連結的儲存格內容是位於某一個活頁簿檔案中,其格式為:

儲存格D2:='路徑\[活頁簿]工作表'!儲存格

依上述公式配合 INDIRECT 函數,將儲存格內容轉換為實際位址:

儲存格D2:=INDIRECT("'"&B1&"["&B2&".xlsx]"&B3&"'!"&B4)

本例儲存格D2公式:='E:\Google Downloads\0\[11112.xlsx]工作表1'!A1

(該公式中使用「&」來串接字串,也可以使用 CONCATENATE 函數來串接字串。)

其結果顯示為 123456(檔案中儲存格A1的內容),但是這是前提在這個活頁簿已經被開啟的狀態下才可以。如果你關閉這個檔案,原公式將會得到錯誤訊息:#REF!。

這個結果對於實務上的應用是個很大的困擾,但似乎現在還沒有更好的解決方案。(網友一起來找看看是否有更好的答案)

Excel-調整日期顯示格式(TEXT,RIGHT)

有網友問到:在 Excel 中有一個日期清單(如下圖左),想要顯示成不同格式(如下圖右),該如何處理?

【輸入公式】

儲存格C2:=TEXT(A2,"mm/dd")&"("&RIGHT(TEXT(A2,"[$-404]aaa;@"),1)&")"

複製儲存格C2,往下各列貼上。

TEXT(A2,"mm/dd"):使用 TEXT 函數將儲存格A2內容顯示為月2碼/日2碼的格式。此舉相當於設定數值格式。

TEXT(A2,"[$-404]aaa;@"):使用 TEXT 函數將儲存格A2內容顯示為「週三」格式。

如果你不知道「[$-404]aaa;@」格式如何得到,可以試試先把儲存格數值格式設定在日期/週三:

接著切換至「自訂」,觀察[類型]文字方塊,複製這個格式文字即可。

RIGHT(TEXT(A2,"[$-404]aaa;@"),1):利用 RIGHT 函數將取得的星期幾文字取最右邊一個字元。

透過串接文字時加上「(」和「)」,即為所求。例如:2014/01/01轉換為01/01(三)。

2014年9月16日 星期二

Excel-數列中重覆出現的數字給予編號(COUNTIF)

有網友問到在以下的資料清單中(如下圖左),其數字雖然由小到大排列,但是有幾個重覆,要如何給予重覆的數字加以編號呢?(如下圖右)

【輸入公式】

儲存格B2:=IF(COUNTIF($A$2:A2,A2)>1,A2&"-"&COUNTIF($A$2:A2,A2)-1,A2)

COUNTIF($A$2:A2,A2)>1:判斷由第一個儲存格(A2)至目前儲存格中,和本身(儲存格A2)相同的數量是否大於 1。若成立代表數字已重覆出現,若不成立代表數字尚未出現過。

A2&"-"&COUNTIF($A$2:A2,A2)-1:給予新的編號「-號碼」,該號碼即為儲存格(A2)內容出次數再減 1。(此排序法為題目要求)

複製儲存格B2,貼至儲存格B2:B23。

這個公式對於原始的數列中,即使沒有經過排序也是適用!

Excel-在資料清單中的每一列插入多列

有網友問到:在 Excel 中,想要在某一資料清單中(下圖左),插入多列空白列(下圖右),該如何處理?

image image

假設我們要在 10 列資料中,每列下方插入 4 列空白列。

1. 首先,新增一欄,如下圖的欄A。

2. 在欄A中為已有資料的各列,依序填入流水號,本例為 1 ~ 10。(參考下圖)

3. 在儲存格A11中輸入公式:=1+(ROW(A11)-10)/4-0.001。

其中 ROW(A11)-10 是因為公式從儲存格A11起始,而「/4」乃為要插入 4 列,若要插入 n 列,則為「/n」。而「-0.001」則適用於插入 1000 列以下的動作。

4. 複製儲存格A11,往下多列貼上。(參考下圖)

5. 選取欄A。

6. 按一下[排序與篩選]功能表中的「從最小到最大排序」。

image

系統如果出現[排序警告]對話框,則選取「將選取範圍擴大」選項。

image

得到如下圖的結果,最後再將欄A刪除即可。

image

2014年9月15日 星期一

Excel-取用不連續工作表中相同位置儲存格的計算(COUNTIF,INDIRECT,ADDRESS)

我們常在一個 Excel 活頁簿中的多個工作表,分別放置了相同格式但不同內容的資料,並且希望取用這些工作表內容加以計算。

例如:老師們將不同班級同學的成績記錄在多個格式一樣的工作表中,而要取出各個工作表的資料來計算各班的不及格人數,或是計算多班的不及格人數總和。該如何處理這類的問題呢?(參考下圖)

過去網友也問到:COUNTIF 函數無法跨工作表使用的相關問題,在此也會遇到,所以要用不同的方式來計算。

 

(1) 計算各班不及格人數

儲存格E2:=COUNTIF(INDIRECT(D2&"!"&"B2:B21"),"<60")

複製儲存格E2,貼至儲存格E2:E11。

透過 INDIRECT 函數將「D2&"!"&"B2:B21"」字串轉換成「位址」(本例為:301!B2:B21)。再透過 COUNTIF 函數來計算小於 60 的個數。

 

(2) 計算全部班級不及格人數總和

儲存格H2:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(D2:D11))))<60)*1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。Excel 會自動在頭尾產生「{  }

在陣列公式中,ROW(A$2:A$21) 代表第2, 3, 4, ..., 21。使用 TRANSPOSE 函數將班級名稱轉置(列的排列轉為欄的排列),在 ADDRESS 函數中即可取得表列文字所代表的每一個工作表相同位置的內容。

利用 INDIRECT 函數將 ADDRESS 函數取得的字串轉換成「位址」,而 N 函數將儲存格內容轉換為數字。公式 N(公式)<60)*1,目的為找出小於 60 的 TRUE/FALSE 陣列,「*1」的作用為將TRUE/FALSE 陣列轉換為 1/0 的陣列。再送至 SUM 函數計算總和,即為所求。

 

(3) 計算部分班級不及格人數總和

儲存格E5:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(G5:G9))))<60)*1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。Excel 會自動在頭尾產生「{  }

原理同 (2)。藉助班級清單,便可找出任意「不連續」工作表的不及格人數總和。

2014年9月13日 星期六

解決Chrome+Clearly將網頁輸出成PDF檔的亂碼問題

有幾個老師問過類似的問題:例如使用 Google Chrome 在一個網站上透過 Clearly 擴充功能來簡化網頁內容,並且要再利用 Chrome 的「列印」功能,要將網頁內容輸出成一個 PDF 檔案。這是一個不錯的組合,可以取得一個乾淨的網頁 PDF 檔。

關於 Clearly 擴充套件可以參考以前文章:

使用Clearly套件列印乾淨網頁
在Google Chrome中使用Clearly儲存右鍵被封鎖網頁中的圖片
使用Google Chrome的Clearly擴充功能朗讀網頁內容

在網頁中按一下「Clearly」擴充功能按鈕:

(範例網頁:http://www.shs.edu.tw/display_pages.php?pageid=2008080704)

網頁內容被整理的很乾淨,符合老師們的需求:

大家已知道利用 Google Chrome 的列印功能,就可以將網頁輸出成 PDF 檔。按一下 Ctrl+P 鍵,即可進入列印狀態,結果跟想要的結果不一樣,網頁內容又變亂了:

(如果你的 Google Chrome 不是在「另存為 PDF」狀態,可以按一下[變更]按鈕來調整。)

你可以改變做法,回到上一個步驟,改按 Clearly 工具列上的[列印]按鈕:

也是會到[列印]狀態,此時看到的就是乾淨的畫面了,現在可以按下「另存為 PDF」:

輸出的 PDF 檔符合想要的結果:

好康東東