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

2018年1月7日 星期日

Google在時間軸中新增對齊道路和播放動態縮時影片功能

你知道Google地圖中有個「時間軸」功能嗎?在時間軸裡會記錄你每天的停留點和行徑路線(前提是你的手機中GPS必須是開啟的)。最近Google在時間軸中新增了「對齊道路」和「播放動態縮時影片」功能,(參考下圖的動畫)在地圖中可以用街景檢視的街景來重現某一段行徑路線。
Google在時間軸中新增對齊道路和播放動態縮時影片功能
Google地圖在預設的時間軸中所記錄的行經路線不一定是對齊道路的,現在Google可以自動幫你對齊道路,如果發現有誤差,還可以讓你手動來調整路徑。
做法如下:例如,在某個開車的路徑上點選「對齊道路」圖示。
Google在時間軸中新增對齊道路和播放動態縮時影片功能
根據已經對齊道路的狀況,自行手動調整(拖曳調整控制點),完成後按下儲存。
Google在時間軸中新增對齊道路和播放動態縮時影片功能
根據這個儲存後的路徑,Google會自動產生該行經路徑的街景檢視動畫。只要點選播放圖示:
Google在時間軸中新增對齊道路和播放動態縮時影片功能
再點選「觀看動態縮時影片」:
Google在時間軸中新增對齊道路和播放動態縮時影片功能
在Google地圖上,會將你開車所行經路線的街景檢視以動畫方式播放出來:
Google在時間軸中新增對齊道路和播放動態縮時影片功能
這真是個很有趣的功能!你也有玩一玩!

Google地圖-如何產生超過10個地點的規劃路線

使用Google地圖在生活和工作上都十分有用,尤其是「規劃路線」在旅遊計畫中更是不可或缺。但是,使用Google地圖來規劃路線時,在網站上最多只能規劃新增最多10個地點的路線,如果我們要的規劃路線不只10個地點時,該如何處理?
寒假就要來臨,很多家庭一出去玩就好幾天,旅遊路線可能會有超過20個以上的地點。參考下圖,如何產生約20個地點的規劃路線?
Google地圖-如何產生超過10個地點的規劃路線
當你在Google地圖上規劃路線時,新增到了第10個地點,就不再出現「新增」地點了。
Google地圖-如何產生超過10個地點的規劃路線
你可能產生另一組的路線規劃,把另外10個地點放在另一個路線上。
Google地圖-如何產生超過10個地點的規劃路線
你可以試試以下的做法:
https://www.google.com.tw/maps/dir/網址之後,依序填入想要規劃新增的地點,並且以『/』加以隔開。
Google地圖會產生如下的規劃路線:
Google地圖-如何產生超過10個地點的規劃路線
檢視詳細路線也是正確:
Google地圖-如何產生超過10個地點的規劃路線
分享地圖的連結及嵌入地圖都會正確的產生:
Google地圖-如何產生超過10個地點的規劃路線
試著將此路線傳送至手機,結果也是正確無誤!
Google地圖-如何產生超過10個地點的規劃路線 Google地圖-如何產生超過10個地點的規劃路線

2018年1月6日 星期六

Excel-根據清單中列出含有指定字元的資料(OFFSET,SUBSUTITUTE)

(網友提問)在 Excel 的工作表中有一組資料清單(參考下圖),如何列出清單中含有指定字元的資料?
例如:要找出資料中含有 T、N、R 的清單。
Excel-根據清單中列出含有指定字元的資料(OFFSET,SUBSUTITUTE)

【公式設計與解析】
選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。
儲存格C2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(資料,C$1,"")<>
資料,ROW(資料),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格C2,貼至儲存格C2:E18。
(1) SUBSTITUTE(資料,C$1,"")
利用 SUBSTITUTE 函數將資料陣列中每個儲存格都以儲存格C1的內容用空白取代。
(2) IF(第(1)式<>資料,ROW(資料),"")
對資料陣列中的每個儲存格判斷第(1)式的傳回結果,如果取代後的結果和原來的內容不一樣,表示含有儲存格C1內容,則傳該儲存格的列號;否則傳回空字串(空白)。(ROW 函數可傳儲存格的列號)
(3) SMALL(第(2)式,ROW(1:1))
當公式向下各列複製時,將第(2)式傳回的列號利用 SMALL 函數由小至大取出。
(4) OFFSET($A$1,第(3)式-1,0)
將第(3)式傳回的列號代入 OFFSET 函數取得由儲存格A1起始所對應的儲存格內容。
(5) IFERROR(第(4)式,"")
利用 IFERROR 函數將第(3)可能傳回的錯誤訊轉換為空字串(空白)。

2018年1月5日 星期五

使用Google文件的對話功能進行團隊共筆

當多人編輯一份文件進行協作時,除了輸入資料這個基本工作,過程中的對話機制是很重要的。如何能快速而迶效的傳遞訊息,讓編輯工作不會陷於反覆不斷的修正,而且能正確讓團隊成員知悉各自的看法,相對更是重要的歷程。
以下用Google文件的編輯為例,來稍微描述Google在共享文件編輯時的對話機制。本例假設這個文件有二個使用者共用:A使用者:文件原始擁有者/B使用者:文件共用者。
1. 使用建議操作
B使用者在編輯文件時啟用「建議操作」,並輸入一些建議。
使用Google文件的對話功能進行團隊共筆
B使用者所輸入的文字會以不同色彩標示之外,視窗右側會顯示編輯的內容和編輯的時間。
使用Google文件的對話功能進行團隊共筆
A使用者會看到B使用者的操作建議,並且可以針對B使用者的建議文字加以回覆。(二個使用者不一定都要在線,何時回覆都可以。)
使用Google文件的對話功能進行團隊共筆
A使用者可以可以選取「工具/查看修訂建議」:
使用Google文件的對話功能進行團隊共筆
可以選取要接受或是拒絶相關的建議,除了全部一次處理之外,也可以使用視窗右側的每一個編輯建議,逐一給予接受建議(V)或是拒絶建議(X)。
使用Google文件的對話功能進行團隊共筆

2. 使用註解功能
先前所提的「建議操作」也是一種註解,使用者也可以以註解方式來對和共用的使用者對話。先將插入點移至要加上註解的位置,點選右上角的「註解」,再點選「+註解」以新增註解。
使用Google文件的對話功能進行團隊共筆
例如:A使用者輸入註解內容後,點選「留言」。
使用Google文件的對話功能進行團隊共筆
B使用者看到這個註解後,可以加以回覆或是點選「解決」,若點選了「解決」,則會將討論標示為已解決並予以隱藏。
使用Google文件的對話功能進行團隊共筆
B使用者也可以使用插入註解的方式,和A使用者對話。
使用Google文件的對話功能進行團隊共筆
共用文件的每位使用者也可以將一個註解取得超連結,並傳送給其他使用者,指定該使用者來處理這個註解。以分派工作的方式讓團隊運作。
使用Google文件的對話功能進行團隊共筆
每個使用者可以在編輯註解時,使用「@」再加上使用者的ID來指定給某使用者,輸入註解後勾選「指派給…」,再點選「指派」,該使用者會收到一個通訊訊息。
使用Google文件的對話功能進行團隊共筆
使用Google文件的對話功能進行團隊共筆

3. 使用即時通訊
如果共用文件的使用者同時已在線上(看是否出線頭貼來判斷),可以透過「訊息」功能來即時傳遞即時訊息,互相對話討論。但是所有的討論過程訊息並不會儲存在文件中,一旦離線或是關閉文件,訊息也會隨之消失。
使用Google文件的對話功能進行團隊共筆

檢視其他文章

好康東東