2015年9月30日 星期三

Excel-每筆分二列摘要資料(陣列公式,OFFSET,SMALL,ROW,INT)

有網友問到:如下圖的 Excel 資料表(右圖),如何將「報名」欄為「Yes」者取出摘要成左圖的結果,其中每個學員的 Email 和電話分成二列呈現。
為了說明方便,選取F欄中所有「報名」欄的內容,定義名稱:報名。

【公式設計與解析】
1. 列出學員姓名
儲存格A2:{=IFERROR(OFFSET($E$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
此為陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
(1) SMALL(IF(報名="Yes",ROW(報名),FALSE)
在陣列公式中,當「報名」欄位為「Yes」時傳回「報名」儲存格陣列的列號,否則傳回「FALSE」。
(2) INT(ROW(2:2)/2))
當公式向下複製時會產生 0, 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, ...。
(3) OFFSET($E$1, 第(1)式, 第(2)式, 0,1,1)
將第(1)式和第(2)式代入 OFFSET 函式,取得對應的儲存格內容。
(4) IFERROR(OFFSET($E$1, 第(1)式, 第(2)式 , 0,1,1),"")
將第 (3) 式傳回值為錯誤訊息者,以空白顯示。
2. 列出學員Email
儲存格B2:{=IFERROR(OFFSET($G$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
和儲存格A2公式的說明相仿。
3. 列出學員電話
儲存格B3:{=IFERROR(OFFSET($H$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
和儲存格A2公式的說明相仿。
複製儲存格A2:B3,往下各列貼上。

Word-在表格中插入圖片時自動調整大小以符合欄寬

以前的一位老同事最近問到一個常見的問題:如何在 Word 中插入圖片時,能自動調整圖片大小以符合表格的欄位寬度?
以 Word 2010 為例,下圖是一個 2X2 的表格,想要在這個表格中插入四張圖,可是四張圖片的大小並不相同,希望能插入圖片時,不要影響表格的欄位寬度,該如何處理呢?
在預設狀態下插入圖片時,會改變儲存格的大小。
要解決這樣的問題,可以先調整好你要的表格、欄位的大小,然後選取表格,再選取[表格工具/版面配置]功能表中的[儲存格大小/自動調整]選項中的「固定欄寬」。
當插入大小大一或是寬度、高度不相同的各式圖片時,都會自動的縮小圖片以符合儲存格的大小。經過這樣的設定後,工作就省時、省事多了。

2015年9月29日 星期二

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)

有位老師問到:
如果在 Excel 中每一個月有不同內容的多個工作表,其內容是某些課程的時間、地點和攜帶物品的明細表。如何能在輸入某個月份時,即能列出該月份的各個課程之時間、地點和攜帶物品,以方便提醒每位小朋友呢?

【公式設計與解析】
關於這個問題,因為老師想要將各個月的課程內容放在不同的工作表,所以依老師的想法設計公式。
1. 安排工作表
以 10月、11月、12月為例:
(1) 將每不同月份都以相同格式安排,只是其中的內容不同而已。
(2) 依序將工作表名稱命名為:10月、11月、12月

2. 設計查詢公式
(1) 在儲存格B1輸入月份
因為儲存格B1輸入的內容要與工作表名稱一致,建議使用「資料驗證」方式來建立下拉式清單以方便選取。
(2) 輸入公式
儲存格C1:
=VLOOKUP($B3,INDIRECT($B$1&"!$A$2:$D$4"),COLUMN(B:B),FALSE)
複製儲存格C1,貼至儲存格C1:E10。
INDIRECT($B$1&"!$A$2:$D$4"):透過 INDIRECT 函數將儲存格B1的內容轉換為工作表名稱,而參照不同工作表的格式為:「'工作表名稱'!儲存格範圍」。
COLUMN(B:B):當公式向右複製時,COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4→...。用以在 VLOOKUP 函數中顯示儲存格A2:D4的第 2, 3, 4 欄內容(時間、地點和攜帶物品)。

Excel-查表應用(OFFSET,MATCH,COUNTIF,ROW)

網友問到在 Excel 中有一個如下圖(上)的一個基本資料表,如何藉由輸入發票號碼,來篩選出同一發票的所有客戶資料?(參考下圖(下))

【公式設計與解析】
1. 查詢客戶編號
儲存格C19:=OFFSET(B1,MATCH(A20,A2:A17,0),,,)
MATCH(A20,A2:A17,0):求儲存格A20的發票號碼在儲存格A2:A17範圍中的第幾列。
OFFSET(B1,MATCH(A20,A2:A17,0),,,):將上式傳回的第幾列,將值代入 OFFSET 函數取得儲存格的內容。
2. 查詢客戶名稱
儲存格E19:=OFFSET(C1,MATCH(A20,A2:A17,0),,,)
做法同「1. 查詢客戶編號」公式。
3. 查詢:產品編號、產品名稱、單價、數量
(1) MATCH($A$20,$A$2:$A$17,0)
做法同「1. 查詢客戶編號」公式。
(2) OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,)
MATCH($A$20,$A$2:$A$17,0)+ROW(1:1):用於向下複製公式時,每增加一個列號,其傳回值也會相對加 1。
因為相同發票號已排列在一起,所以將上式代入 OFFSET 函數取得對應的儲存格內容,當向下複製公式時,即可依序取得相同的發票的不同記錄之內容。
儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20), 第(2)式 ,"")
COUNTIF($A$2:$A$17,$A$20):計算同一個發票號碼共有幾筆記錄。
透過 IF 函數,將超過相同發票的記錄數者,予以顯示空白。
最後,複製儲存格B21,貼至儲存格B21:E27。

【補充說明】
完整公式/儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20),
OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,),"")

2015年9月28日 星期一

Excel-以合計不超過某一數值為一組來分組(OFFSET,MATCH,COLUMN)

網友在 Excel 中有一個資料表如下圖中的A欄和B欄,想要根據項次和數值的內容,將合計不超過某一數值者(本例為:300)分成一組,並且依項次由小至大分組。其次,再將各組的項次列出。以上兩個問題,該如何處理?
觀察下圖,項次 A01~A06 的合計為 285,若再加上 A07,會超過 300,所以將A01~A06 分在第1組。而 A07 為第 2 組的第 1 個,A07~A11 合計為 222,若再加上A12 的 96,會超過 300,所以將 A07~A11 分為第 2 組,依此類推。

【公式設計與解析】
本例以累不超過 300 者為一組。
1. 計算分組累計
儲存格C2:=B1
儲存格C3:=IF((C2+B3)>300,B3,C2+B3)
複製儲存格C3,往下各列貼上。
2. 找出組別
儲存格D2:=1
儲存格D3:=IF((C2+B3)>300,D2+1,D2)
複製儲存格D3,往下各列貼上。
3. 列出每組分項內容
先將D欄中有資料的範圍,定義名稱:組別。
儲存格G2:=IF(COLUMN(A:A)<=COUNTIF(組別,$F2),OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,),"")
(1) MATCH($F2,組別,0)
找出各組的第一個項目所在的列號。
(2) OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,)
根據各組第一個列號,依序列出各組的項目。其中,COLUMN(A:A)=1 在向右複製時,會產生 COLUMN(A:A)=1→COLUMN(B:BA)=2→COLUMN(C:CA)=3→...。
透過 OFFSET 函數將直式(由上而下)的資料轉換為橫式(由左而右)顯示。
(3) IF(COLUMN(A:A)<=COUNTIF(組別,$F2), 第(2)式 ,"")
如果項目所在欄號(A欄=1、B欄=2、C欄=3、...)大於該組的數量,則以空白顯示。
最後,複製儲存格G2,貼至儲存格G2:L16。

2015年9月27日 星期日

Excel-在折線圖中分二區顯示不同色彩

在網路上看到一篇 Excel 的文章,覺得可以分享給有需要的人。
觀察下圖,這是一個由每個月最後一天所集合而成的報表所繪製出來的折線圖。然而,9/30、10/31、11/30、12/31 因為是推測值(時間是未來時間),所以想要用不同色彩來呈現,以凸顯這是推測的區域,該如何處理呢?
image
參考步驟:
下圖是一個依資料表做成的折線圖,其中數列構成的折線圖是單一色彩,無法顯示二種不同色彩。
因此,可以在想要顯示不同色彩的區域新增一個矩形,蓋住這個區域的折線圖。
這個矩形有一個預設的色彩,先修改這個色彩為:其他填滿色彩。
在[色彩]對話框中設定:紅色、綠色、藍色的數值均為:255,透明設定數值為:30%。外框設定為:無外框。
也就是 9/30~12/31 之間顯示的是一個 30% 透明的白色,視覺上淡化了這個區域的色彩。讓使用者注意到這是個不一樣的折線圖區域。

Excel-在輸入公式時使用函數的各種方式

你知道在 Excel 中要輸入函數有那些方法嗎?對於初學者,可能要由瀏覽函數開始慢慢找到想要的函數,漸漸的可以把函數名稱背下來直接輸入。有時候會面對少用而陌生的函數,該如何取得提示和說明呢?

(1) 瀏覽函數來輸入
使用[插入函數]按鈕,當你選取一個儲存格後,按一下 fx,可以開啟[插入函數]對話框,透過瀏覽函數的方式來找到並且輸入函數。你也可以按下 Shift+F3 鍵,來開啟[插入函數]對話框。

2. 輸入函數名稱
若你記得函數名稱,直接在儲存格中輸入函數名稱,Excel 會列出與該函數文字相關的函數。
在你要的函數名稱上按二下,即可帶入該函數,並且會顯函數參數的提示。
點選函數名稱,可以取得該函數的說明:
若此時,按一下 Ctrl+Shift+A 鍵,則函數的參數提示會顯示在資料編輯列中。
若在已輸入函數名稱時,按一下 Ctrl+A 鍵或是 Shift+F3 鍵。
image
此時,會開啟對話框讓你輸入函數的參數,並且可以看到預覽結果和輔助說明。
如果你已定義一些名稱,該名稱也會出現在輸入公式的選項中。

Excel-依向左而右、由上而下的順序在儲存格中輸入資料

有學校同仁問到:如何在下圖中 Excel 的儲存格輸入資料,能由左而右、由上而下的順序輸入資料?
在 Excel 中的儲存格輸入資料,按下 Enter 鍵,預設跳到「下一列同一欄」的儲存格,而按下 Tab 鍵,預設跳到「同一列下一欄」的儲存格。由於在工作中,常常是一張紙填寫一筆資料(Record),所以輸入時會同一列的內容輸完才會換到下一列,因此依向左而右、由上而下的順序在儲存格中輸入資料是有其必要性。
image
如果你選取[檔案/選項]功能表,在「進階」標籤下的[編輯選項]區中,可以設定按Enter 鍵後,移動選取範圍的方向,預設值為:下。
如果你在一個儲存格中輸入資料後習慣按 Enter 鍵,則你可以在此更改為方向:右,並且在輸入前先選取要輸入資料的儲存格範圍。如此,便可以依你的習慣來輸入資料。
即輸入儲存格A2後,按 Enter 鍵,會自動選取右邊的儲存格B2,...,當儲存格F2輸入完成後,按 Enter 鍵,會自動跳到下一列的儲存格A3,如此,便能依向左而右、由上而下的順序在儲存格中輸入資料。
不過特別要注意,當你更改了方向設定,將會影響其他工作表輸入資料時,也會由左至右輸入。
如果你在一個儲存格中輸入資料後習慣按 Tab 鍵,則不須執行上述更改方向的動作。只要在輸入前先選取要輸入資料的儲存格範圍,當輸入儲存格A2後,按 Tab 鍵,會自動選取右邊的儲存格B2,...,當儲存格F2輸入完成後,按 Tab 鍵,會自動跳到下一列的儲存格A3。如此,也能依向左而右、由上而下的順序在儲存格中輸入資料。

2015年9月26日 星期六

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

這是回應學校老師的一個練習範例。參考下圖,這是一個常見的成績表,如果想要計算每個區間中的各科平均該如何處理?如果計算各科平均時,想要依某個加權來計算總平均,又該如何處理?
先觀察上圖,每個區間的次數並不相同,在此要自動產生各個科目在這個區間的「平均」,並且計算各科的「加權平均」。(如下圖,還能以群組概念檢視資料)
參考以下的做法:
1. 選取儲存格A1:G20。
2. 選取[資料/大網]功能表中的「小計」。
3. 在[小計]對話框中設定:
(1) 分組小計欄位:區間(指第一次期中考、第二次期中考、期末考)
(2) 使用函數:平均值(相當於使用 AVERAGE 函數)
(3) 新增小計位置:國文、英文、數學、社會、自然(指這個個科目都要計算平均)
(4) 按下[確定]按鈕後,即可看到自動建立群組且依區間計算每個科目的平均值:
(5) 按一下第2層的「-」按鈕(此為摺疊,按下後會轉為「+」,此為展開按鈕)。
(6) 按著 Ctrl 鍵,分別選取各個平均值的各科平均分數。
(7) 設定各科平均的小數點位數為1位,並設定一個色彩。
(8) 計算加權平均
假設國文、英文、數學、社會、自然的加權分別為4、4、4、3、3。
儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3})
複製儲存格H2,貼至儲存格H2:H24。
最後,稍微調整一下格式設定,美化之後即可使用群組功能來檢視資料了。

【延伸學習】
(1)
公式儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3}),其中 {4,4,4,3,3} 是一種陣列的表示法。
(2)
注意喔!自動產生的小計(平均值),並不是使用你熟悉的 AVERAGE 函數,而是使用SUBTOTAL 函數。值得你進一步研究。

2015年9月25日 星期五

找到每個手機App的隱私存取權限(以Android為例)

和同事聊到手機上裝了很多的 App,卻不知道這些 App 會取用那些自己的隱私?我開玩笑說,即使知道了,你能採取那些有效的應變措施?會堅決放棄不用有過度侵犯隱私疑慮的 App 嗎?
不過,至少應該會查到這個 App 宣告要使用個人隱私存取的權限有那些。以 Android 手機為例,如果你使用電腦,在 Google Play 網站先找到你要的 App,畫面往下拉,在「其他資訊」區域中,點選「權限」。(本文以 Evernote App 為例)
在此,即會顯示該 App 需要被授權存取那些個人隱私項目:
如果是在手機中操作,進入 Google Play App 中,找到 Evernote App,點選「權限詳細資訊」,就會出現這個 App 的版本資訊和要求授權的存取項目。(通常這些存取項目會根據你使用的 App 版本而有所不同)
 
或是你已經安裝了這個 App,也可以到「設定/應用程程式」中,找到並點選這個 App,在應用程式資訊中也會顯示這個 App 的版本資訊,將畫面往上滑動,即可看到不太一樣的 App 存取權限資訊。
 

2015年9月24日 星期四

Google日曆輸出為PDF檔以方便書面使用

有同仁問到常用的 Google 日曆,可否輸出成一個檔案以方便書面上使用?
Google日曆在電腦和手機上檢視,操作上已是十分方便,也有提供列印的功能。
如果你想要將日曆內容轉成檔案,Google 日曆已預設可以輸出 PDF 檔。使用時可以開啟 Google 日曆後,按 Ctrl+P 鍵,會進入日曆列印預覽。最下方有一個「另存新檔」的功能。
這個存檔功能即會儲存日曆為 PDF 檔:

Excel-在VLOOKUP函數中使用雙條件(INDIRECT)

在 Excel 中,如果想要依據一個包含 4 個區域、5 種費率的對照表(如下圖左),依不同重量來自動計算金額(如下圖右),該如何處理?
本例想要依區域和重量,自動對應出費率,才能試算金額。
【準備工作】
定義以下四個名稱:
北:=工作表1!$B$2:$C$6
中:=工作表1!$B$7:$C$11
南:=工作表1!$B$12:$C$16
東:=工作表1!$B$17:$C$21

【公式設計與解析】
儲存格G2:=VLOOKUP(F2,INDIRECT(E2),2)
INDIRECT(E2):將儲存格E2的內容(北、中、南、東)轉換為真實的儲存格位址(已先定義名稱)
透過 VLOOKUP 函數利用查表的概念找到對應的費率。
本例中使用的雙條件為:儲存格F2(重量)、INDIRECT(E2)(區域)。利用定義好的名稱,可以讓使用很短的公式,即可完成查詢的工作。

2015年9月23日 星期三

Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)

前幾天的二篇 Excel 文章,都是使用「陣列公式」來處理:
這次不要使用陣列公式,而是使用 SUMPRODUCT 函數來運算。在下圖右的上下二個摘要表中,上半部是依據一個條件來篩選資料,下半部是依據二個條件來篩選資料。請自行對照以上二篇文章比較其差異。

【公式設計與解析】
為了方便說明,先定義名稱。選取儲存格A1:E25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、客編、品名、規格、數量。
使用 SUMPRODUCT 函數可以不用使用「陣列公式」,有些人對於陣列公式望而卻步或是一之半解無法活用。

(1) 依據一個條件來篩選資料
為方便說明使用一個輔助欄位(K欄)。
儲存格K3:=SUMPRODUCT(LARGE((客編=$H$1)*ROW(日期),ROW(1:1)))-1
客編=$H$1:在 SUMPRODUCT 函數中的「客編」陣列中,判斷是和儲存格H1相同,傳回 TRUE/FALSE 陣列。
(客編=$H$1)*ROW(日期):傳回符合「客編」陣列中是和儲存格H1相同者的列號(以日期陣列來取得列號)所組成的陣列。
LARGE((客編=$H$1)*ROW(日期),ROW(1:1)):利用ROW(1:1)來依序「由大到小」取得上式中列號陣列的數值。ROW(1:1)=1向下複製時會產生ROW(2:2)=2→ROW(3:3)=3→...。
儲存格G3:=IFERROR(OFFSET($A$1,K3,,,),"")
儲存格H3:=IFERROR(OFFSET($C$1,K3,,,),"")
儲存格I3:=IFERROR(OFFSET($D$1,K3,,,),"")
儲存格J3:=IFERROR(OFFSET($E$1,K3,,,),"")
複製儲存格G3:K3,貼至儲存格G3:K13。

(2) 依據二個條件來篩選資料
儲存格K17:
=SUMPRODUCT(LARGE((客編=$H$15)*(品名=$J$15)*ROW(日期),ROW(1:1)))-1
觀察上式,使用 (客編=$H$15)*(品名=$J$15) 來篩選雙條件的結果。
儲存格G17:=IFERROR(OFFSET($A$1,K17,,,),"")
儲存格H17:=IFERROR(OFFSET($D$1,K17,,,),"")
儲存格I17:=IFERROR(OFFSET($E$1,K17,,,),"")
複製儲存格G17:K17,貼至儲存格G17:K25。

【思考一下】
如何依據三個條件、四個條件來篩選資料?

檢視其他文章

好康東東