2018年1月31日 星期三

Excel-列出指定項目的清單(MATCH,COUNTIF,OFFSET)

(網友提問)在 Excel 中常會用到要執行查詢的工作,或許你會用 VLOOKUP 函數或是 INDEX 函數,以欄、列交會之處查詢。但是如果要列出相同者的清單,就無法直接使用這兩個函數。這次來試試 OFFSET 函數。
Excel-列出指定項目的清單(MATCH,COUNTIF,OFFSET)

【公式設計與解析】
本題已假設同機種的物料是連續排列。
儲存格E2:=IF(ROW(1:1)<=COUNTIF($A$2:$A$24,$D$2),OFFSET($B$1,
MATCH($D$2,$A$2:$A$24,0)+ROW(1:1)-1,0),"")
複製儲存格E2,貼至儲存格E2:E9。
(1) COUNTIF($A$2:$A$24,$D$2)
計算儲存格D2(機種)在儲存格A2:A24中共有幾個(傳回數值,本例傳回 7)。
(2) MATCH($D$2,$A$2:$A$24,0)
找出儲存格D2(機種)在儲存格A2:A24中第一個的位置(傳回數值,本例傳回 11)。
(3) OFFSET($B$1,第(2)式+ROW(1:1)-1,0)
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
第(2)式+ROW(1:1)-1:本例依序傳回 11、12、13、...。
將上式代入 OFFSET 函數求得對應的儲存格內容。
(4) IF(ROW(1:1)<=第(1)式,第(3)式,"")
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
本例中若 ROW 函數的傳回值小於或等於 7,則執行第(3)式,否則顯示空字串。

2018年1月30日 星期二

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

許多網友對於 Excel 中的 VLOOKUP 函數或許不陌生,但如果要在公式中使用雙條件或多條件,可能就會產生一些困擾,甚至不知如何下手。
因為 VLOOKUP 函數是藉由將要查詢的內容和資料陣列的第一欄比對,查到第幾列時,再由公式中指定的欄數,以欄列交會查出對應的儲存格內容。在此,若要使用雙條件,的確很不方便。該如何解決?
Excel-在VLOOUP函數中使用雙條件查詢

【公式設計與解析】
我的部落格上有許多應用雙條件的查詢公式可參考:
但是,這些公式對於很多讀者是很大的門檻,不容易一看就懂。所以,試著將程序稍微複雜化,或許公式的理解和使用也會變簡單。
本例中,如果要使用 VLOOKUP 函數來查詢雙條件的結果,使用者可以先建立一個輔助欄位(A欄),其內容是 AA 和 BB 的串接,例如:儲存格A2:=B2&C2,複製儲存格A2,貼至儲存格A2:A17。
再使用公式:
儲存格F6:=VLOOKUP(F3&G3,A2:D17,4,FALSE)
如此,便可以由雙條件查詢對應的結果。

Excel-關於WEEKDAY函數

網友問到關於 Excel 中 WEEKDAY 函數的使用:
在公式中使用 WEEKDAY 函數,可以依傳回的數值來判斷為星期幾。
WEEKDAY的參數與傳回值:
1:適用星期日為每週第一天者(傳回值以 1 為起始)
2:適用星期六為每週第一天者(傳回值以 2 為起始)
3:適用星期一為每週第一天者(傳回值以 0 為起始)
11~17:分別對應以星期一至星期日為第一天者(對應傳回值以 1~7 為起始)
其中參數 1 和 17 為相同結果;參數 2 和 11 為相同結果。
Excel-關於WEEKDAY函數
參數 11~17 方便在公式中使用連續的數列產生傳回值。
例如公式:=WEEKDAY(A2,10+ROW(1:1))
Excel-關於WEEKDAY函數

2018年1月28日 星期日

Excel-由上而下排序和由左至右排序

在Excel中執行排序動作時,通常習慣使用由上而下的排序方式,如果想要由左至右排序時,該如何處理?而中文字的排序預設是依筆劃多寡來排序(還可以調整為依注音排序),如果想要依自己定義的順序排序,該如何處理?
image

1. 由上而下遞減自訂清單排序(癸→壬→辛→...→丙→乙→甲)
Excel 預設為「由上而下」排序,如果要自訂依癸→壬→辛→...→丙→乙→甲,必須要先在排序的順序中選取「自訂清單」。
image
再選取「子,丑,寅,卯,辰,巳,午,未,申,酉,戌,亥」。(此為預設的自訂清單,所以如果你有自己的清單,應先在此自訂。)
image
然後在順序中選取:亥,戌,酉,申,未,午,巳,辰,卯,寅,丑,子。(遞減排序)
image
結果如下:
image

2. 由左而右遞減自訂清單排序(亥→戌→酉→...→寅→丑→子)
如果在Excel中要由左而右排序,則要先選取「選項」,然後選取「循列排序」。
image
在排序的順序中選取「自訂清單」,再選取「甲,乙,丙,丁,戊,己,庚,辛,壬,癸」。
然後在順序中選取:癸,壬,辛,庚,己,戊,丁,丙,乙,甲。(遞減排序)
image
結果如下:
image

2018年1月27日 星期六

Excel-VLOOKUP查詢和HLOOKUP查詢

在 Excel 中使用 VLOOKUP 函數和 HLOOKUP 函數都可以用來做為查詢的工具,這兩者的查詢在概念上有何差別?
Excel-VLOOKUP查詢和HLOOKUP查詢

【公式設計與解析】
1. 使用VLOOKUP函數
儲存格N4:=VLOOKUP(N3,B3:K22,MATCH(N2,B2:K2,0),FALSE)
(1) 找出儲存格N3在資料陣列儲存格B3:K22中的第一欄(B欄)的位置(第n列)。
(2) 以MATCH(N2,B2:K2,0)找出儲存格N2在儲存格B2:K2中的位置(第m欄)。
(3) 第(1)式和第(2)式欄(m)、列(n)交會之處,即為查詢結果。
Excel-VLOOKUP查詢和HLOOKUP查詢

2. 使用HLOOKUP函數
儲存格N4:=HLOOKUP(N2,C2:K22,MATCH(N3,B2:B22,0),FALSE)
(1) 找出儲存格N2在資料陣列儲存格C2:K22中的第一列(2列)的位置(第m欄)。
(2) 以MATCH(N3,B2:B22,0)找出儲存格N3在儲存格B2:B22中的位置(第n列)。
(3) 第(1)式和第(2)式欄(m)、列(n)交會之處,即為查詢結果。
Excel-VLOOKUP查詢和HLOOKUP查詢

2018年1月23日 星期二

Excel-設定小數轉換為指定分母的分數

當在 Excel 中的公式運算結果出現小數,如果想要轉換為特定分母的分數,該如何處理?以下圖例,將1~24 除以 24,會得到以小數顯示的數值。如何轉換為以 24 為分母的分數?
Excel-設定小數轉換為指定分母的分數
選取儲存格A15:D20,設定數值格式,自訂:# ??/24。
即 # 後接空格,再接 ??,再接 /,再接 24(指定的分母)。
Excel-設定小數轉換為指定分母的分數
這是設定數值格式:# ??/48 的結果:
Excel-設定小數轉換為指定分母的分數
這是設定數值格式:# ??/12 的結果:
Excel-設定小數轉換為指定分母的分數

2018年1月22日 星期一

Excel-比較「年/月/日」和「數值/數值/數值」

在 Excel 中,日期是以數值來儲存,其格式為「年/月/日」和「數值/數值/數值」很相像,學生常會搞不清楚。試著比較在儲存格中輸入以下的內容顯示的結果:
(1) =2016/2/4:顯示252,其執行公式運算2016除以2、再除以4的結果。
(2) 2016/2/4:顯示2016/2/4,其執行輸入日期2016年2月4日。
(3) '2016/2/4:顯示2016/2/4,其執行輸入2016/2/4字串。
(4) ="2016/2/4":顯示2016/2/4,其執行公式運算顯示2016/2/4字串。
Excel-比較「年/月/日」和「數值/數值/數值」

Excel-根據條件計算次數(SUMPRODUCT,ISBLANK,NOT)

(回答網友提問)網友根據下圖,想要利用 Excel 來根據指定條件來計算次數,該如何處理?
例如:根據「編號」來計算「遲到、未帶卡、傳遞物品」等的次數。
Excel-根據條件計算次數(SUMPRODUCT,ISBLANK,NOT)

【公式設計與解析】
選取儲存格A1:G8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、日期、排班、姓名、遲到、未帶卡、傳遞物品。
1. 計算遲到的次數。
儲存格D11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(遲到)))
複製儲存格D11:F11,貼至儲存格D11:F12。
 SUMPRODUCT 函數,根據:
條件一:編號=A11,編號儲存格範圍中和儲存格A11相同者。
條件二:NOT(ISBLANK(遲到)),遲到儲存格範圍中不是空白者。
ISBLANK 函數:判斷儲存格是否為空白,傳回 TRUE/FALSE 。
NOT 函數: 將 TRUE/FALSE 轉換為 FALSE/TRUE
2. 計算未帶卡的次數。
儲存格E11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(未帶卡)))
3. 計算傳遞物品的次數。
儲存格F11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(傳遞物品)))

2018年1月20日 星期六

Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)

如何利用 Excel 來計算多重選擇的總得分?如下圖,假設每個題目的答案由 A, B, C, D 所組成,答案可能是其中的 1 ~ 4 個所組成。
Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)
其得分的標準:
●該選的選項也有選:+1分
●該選的選項沒有選:+0分
●不該選的選項沒有選:+1分
●不該選的選項卻有選:+0分

【公式設計與解析】
1. 計算每個題目題分
儲存格D2:=SUMPRODUCT(((SUBSTITUTE(B2,{"A","B","C","D"},"")=B2)=
(SUBSTITUTE(C2,{"A","B","C","D"},"")=C2))*1)
複製儲存格D2,貼至儲存格D2:D26。
在 SUMPRODUCT 函數中,公式可以陣列形式來運算。{"A","B","C","D"} 表示由 A、B、C、D 字元組成的陣列。
(1) SUBSTITUTE(B2,{"A","B","C","D"},"")=B2
利用 SUBSTITUTE 函數將儲存格B2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格B2相同。例如,置換 A 後若傳回相同,代表儲存格B2中沒有 A 字元。

(2) SUBSTITUTE(C2,{"A","B","C","D"},"")=C2
利用 SUBSTITUTE 函數將儲存格C2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格C2相同。

(3) (第(1)式)=(第(2)式))*1
在 SUMPRODUCT 函數中,判斷第(1)式和第(2)式的結果是否相等,傳回 TRUE/FALSE 陣列。因為 TURE 代表得分(+1),FALSE 代表沒有得分(+0),所以利用公式中的『*1』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後經由 SUMPRODUCT 函數予以加總,即為該題得分。

2. 計算所有題目總得分
儲存格G2:=SUMPRODUCT(((SUBSTITUTE(B2:B26,{"A","B","C","D"},"")=B2:B26)
=(SUBSTITUTE(C2:C26,{"A","B","C","D"},"")=C2:C26))*1)
有了「1. 計算每個題目題分」的運算經驗,只要將公式中的儲存格B2置換成儲存格B2:B26,和將儲存格C2置換成儲存格C2:C26,即為所求。
由本例來看,善用 SUMPRODUCT 函數來處理陣列形式的運算,可以縮短公式的總量。或許對很多人而言,其實看不懂這樣的公式,建議由其他較簡單的 SUMPRODUCT 函數運算先理解。

2018年1月14日 星期日

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

在 Excel 的工作表中如果能好好利用設定格式化的條件來處理多儲存格的格式,可以不怕儲存格新增/刪除所帶來又要重設的困擾。
例如:(參考下圖)如何在一個資料表中,自動為間隔欄、間隔列、間隔欄列交會產生不同的背景色彩。
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
這是原始表格:
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
1. 產生間隔欄不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(COLUMN(B2),2)=1
格式:設定淺綠色的背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
利用 COLUMN 函數取得儲存格的欄數,利用 MOD 函數判斷欄數是否奇數欄。

2. 產生間隔列不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(ROW(B2),2)=1
格式:設定淺綠色的背景色彩
利用 ROW 函數取得儲存格的列數,利用 MOD 函數判斷列數是否奇數列。

3. 產生間隔欄列不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(MOD(COLUMN(B2),2)=1)+(MOD(ROW(B2),2)=1)
格式:設定淺綠色的背景色彩
公式中的『+』運算子,相當於執行邏輯 OR 運算。
4. 產生間隔欄列交會不同背景色彩
Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)
選取儲存格B2:K13,設定格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(MOD(COLUMN(B2),2)=1)*(MOD(ROW(B2),2)=1)
格式:設定淺綠色的背景色彩
公式中的『*』運算子,相當於執行邏輯 AND 運算。

2018年1月13日 星期六

Excel-由大至小列出數值清單中出現次數最多者(MODE)

在 Excel 的工作表中有一個數值構成的資料清單,如何由些數值中找出出現次數最多者,並依由大至小排列?
在下圖中,出現最多的數字是 81,而其出數次數是 9。排列順序依次數由大至小排列。
Excel-由大至小列出數值清單中出現次數最多者(MODE)

【公式設計與解析】
選取儲存格A2:J18,按 Ctrl+F3 鍵,在名稱管理員中定義名稱:DATA。
儲存格L2:{=MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))}
儲存格M2:=COUNTIF(DATA,L2)
複製儲存格L2:M2,貼至儲存格L2:M18。
在公式中使用 MODE 函數,乃是因為 MODE 函數會傳回陣列或資料範圍中最常出現,或重複的值。
(1) COUNTIF(L$1:L1,DATA)
在陣列公式中計算在DATA儲存格範圍中和儲存格L1內容相符者的個數。
(2) IF(COUNTIF(L$1:L1,DATA),"",DATA)
COUNTIF(L$1:L1,DATA)如果傳回大於 0 的數,表示儲存格L1裡的數已計算過,所以傳回空字串;否則傳回 DATA 儲存格範圍的內容 .
(3) MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))
將第(2)式的結果代入 MODE 函數,就會找出個數最多者。

將Google表單問卷列印成紙本問卷

Google表單當然是要在線上填答才不會浪費紙張,也才會不限於時間和空間都能回答。但是,有時就需要留下問卷的書面資料,或是有人無法於線上回答時,都需要有書面的紙本問卷。(如下圖)
將Google表單問卷列印成紙本問卷
當你使用Google表單設計好的問卷,就可以直接輸出成紙本。在表單的選項中選取「列印」:
將Google表單問卷列印成紙本問卷
觀察「列印」對話框,可以發現Google已經在列印時自動調整了表單的輸出,讓你列印出來的結果較符合紙本的特性。
將Google表單問卷列印成紙本問卷
除了Google表單之外,像是在日曆中的列印,也都有自動調整為列印格式:
將Google表單問卷列印成紙本問卷
Google地圖的列印功能也有自動調整:
將Google表單問卷列印成紙本問卷

2018年1月9日 星期二

Excel-依多條件取出清單中符合的項目加總(SUMPRODUCT)

(網友提問)根據下圖左的 Excel 工作表中的資料清單,來找出物料碼以 EIM 為首者的各月訂單數量總和,該如何處理?
Excel-依多條件取出清單中符合的項目加總(SUMPRODUCT)

【公式設計與解析】
假設資料來源位於儲存格A1:C115。
選取儲存格A1:C115,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:交貨日期、物料、訂單數量。
儲存格G2:
=SUMPRODUCT((MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量)
(1) 條件一:MONTH(交貨日期)=F3
在 SUMPRODUCT 函數中判斷「交貨日期」儲存格陣列中的月份(利用 MONTH 函數)是否和儲存格F3相同,傳回 TRUE/FALSE 陣列。
(2) 條件二:(LEFT(物料,3)="EIM"
在 SUMPRODUCT 函數中判斷「物料」儲存格陣列中的前 3 碼(利用 LEFT 函數)是否為「EIM」,傳回 TRUE/FALSE 陣列。
(3) (MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量
在 SUMPRODUCT 函數中執行乘積和的運算,其中『*』運算子,相當於執行邏輯 AND 運算,運算過程會將 TRUE/FALSE 陣列轉換為 1/0陣列。

2018年1月8日 星期一

Google相簿裡的智慧搜尋

我在Google相簿中放了幾萬張的相片,Google在處理我的相片也愈來愈有智慧了!
使用者不用為相片命名,只要利用關鍵字搜尋即可找到類以的相片,例如預設的搜尋:影片、截圖、電影、動畫、美術拼貼、作品、動態影片、360度相片、PhotoScan相片、最近使用等。
Google相簿裡的智慧搜尋
Google提供智慧搜尋功能,除了可以搜尋「蝴碟、桌子、火車、飛機、海灘、山丘、建築、自行車、運動、電腦、手機、花園、吊橋、書籍、筷子、湖、彩虹、熱氣球、瀑布、氣球、鉛筆、剪刀、燈塔、拱形、心形、機器、…」等等特特定主題之外,Google也會自動掃描你的照片,並且給予建議要封存。(封存只是將相片收納起來,不再出現在相片區裡,可以到封存區中檢視。)
例如,含有標籤的相片:
Google相簿裡的智慧搜尋
含有菜單的相片:
Google相簿裡的智慧搜尋
含有文件的相片:
Google相簿裡的智慧搜尋
含有螢幕擷圖的相片:
Google相簿裡的智慧搜尋
含有素描的相片:(顯然是誤判)
Google相簿裡的智慧搜尋
含有收據的相片:(有點誤判)
Google相簿裡的智慧搜尋
所以,你可以直接搜尋:文件
Google相簿裡的智慧搜尋
你可以直接搜尋:素描
Google相簿裡的智慧搜尋
你可以直接搜尋:收據
Google相簿裡的智慧搜尋
你可以直接搜尋:菜單
Google相簿裡的智慧搜尋
如果你想參與Google來辨識這些影像,可以加入Image Labeler,
網址:https://crowdsource.google.com/,選取一個類別。
Google相簿裡的智慧搜尋
Google會顯示圖片並且問你是否符合說明所述:
Google相簿裡的智慧搜尋
也可以下載 Crowdsource App,針對圖片來識別,也可以幫忙識別手寫文字。
Google相簿裡的智慧搜尋
識別圖片:
Google相簿裡的智慧搜尋 Google相簿裡的智慧搜尋
識別手寫文字:
Google相簿裡的智慧搜尋 Screenshot_20180108-223848

檢視其他文章

好康東東