2015年10月31日 星期六

Excel-在月曆型式中顯示排班結果(個人)(SUMPRODUCT,OFFSET,DATE,ROW)

延續前一篇文章:
如果想要給每一位員工一張個人的值班表,該如何處理?

【公式設計與解析】
作法一:標示[班別]姓名
Excel-在月曆型式中顯示排班結果(個人)(SUMPRODUCT,OFFSET,DATE,ROW)
先將日期、早班、中班、晚班各自的範圍內的儲存格定義名稱為:日期、早班、中班、晚班。(選取排班資料清單的所有儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」。)
儲存格A8:=IFERROR("[早]"&OFFSET($J$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(早班=$D$1)*ROW(日期))-2,,,),"")
若該日期沒有排到班,則公式會產生錯誤訊息,所以藉助 IFFERROR 函數來顯示空白。
儲存格A9:=IFERROR("[中]"&OFFSET($K$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(中班=$D$1)*ROW(日期))-2,,,),"")
儲存格A10:=IFERROR("[晚]"&OFFSET($L$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(晚班=$D$1)*ROW(日期))-2,,,),"")
複製儲存格A8:A10,貼至所有日期的儲存格中。

作法二:標示[班別]
Excel-在月曆型式中顯示排班結果(個人)(SUMPRODUCT,OFFSET,DATE,ROW)
儲存格A8:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(早班=
$D$1)*ROW(日期)),"早班",""),"")
儲存格A9:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(中班=
$D$1)*ROW(日期)),"中班",""),"")
儲存格A10:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(晚班=
$D$1)*ROW(日期)),"晚班",""),"")
複製儲存格A8:A10,貼至所有日期的儲存格中。

Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

有網友想要將一個 Excel 的排班清單(下圖右)直接在一個月曆中顯示(下圖右),該如何處理?
Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

【公式設計與解析】
先將日期範圍內的儲存格定義名稱為:日期。
以下以 2015/10/4 為例:
儲存格A8:="[早]"&OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
DATE($A$1,$G$1,A7):取得每個儲存格所代表的日期。
SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期)):取得儲存格A8所代表日期在資料清單中的『列號』。
OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期))-2,,,):依據上式的『列號』,代入 OFFSET 函數查得J欄中對應的儲存格內容。
同理:
儲存格A9:="[中]"&OFFSET($K$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
儲存格A10:="[晚]"&OFFSET($L$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)
複製儲存格A8:A10,貼至所有日期的儲存格中。

【延伸處理】
如果在排班表中不是每個日期都有排班,則可以修改公式:
儲存格A8:=IFERROR(原公式,"")
即:
儲存格A8:=IFFERROR("[早]"&OFFSET($J$2,SUMPRODUCT((日期=
DATE($A$1,$G$1,A7))*ROW(日期))-2,,,),"")

2015年10月30日 星期五

Excel-報名資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

當在 Excel 中取得一個運動會的報名表(如下圖),除了各班的所有學生基本資料之後,還有各個比賽項目,其中的值為 TRUE 者代表有報名,FALSE 代表沒有報名。
如何能快速計算各個比賽項目的各班男生/女生的參數人數?
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

1. 使用樞紐分析表和交叉分析篩選器
以跳高項目為例,當你插入一個樞紐分析表,設定如下:
將欄設為:『性別』欄位;列設為:『班級編碼』欄位;值設定:『跳高』欄位。
你看到的只是各班男生人數和女生人數,並無法呈現 TRUE(報名) 的數量。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
所以你要選取[分析/篩選]功能區中的「插入交叉分析篩選器」。
接著,勾選「跳高」:
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
當你按一下「TRUE」,此時顯示的數值即為各班男生/女生的報名人數。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

2. 設計公式來計算
以比賽項目:跳高為例,先將『班級編碼』、『性別』、『跳高』三個欄位的所有資料,定義名稱:班級編碼、性別、跳高。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
輸入公式:
儲存格O2:=SUMPRODUCT((班級編碼=$N2)*(性別=O$1)*(跳高=TRUE))
(班級編碼=$N2)*(性別=O$1)*(跳高=TRUE):「*」運算可以將三個條件執行邏輯AND 運算,即三個條件皆符合者,才會取出來計算乘積和。而在「*」運算時,會將傳回值 TRUE/FALSE 結果轉換為 1/0
提醒:『跳高=TRUE』不可寫出『跳高="TRUE"』,TRUE 在此是一個布林代數值,而非文字 TRUE。
然後,複製儲存格O2,貼至儲存格O2:P48。

【進階處理】
如果想要一次就取得所有比賽項目的各班男生/女生的參賽人數,該何處理?(參考下圖)
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
設計公式前要注意:運算人數的資料表欄位稱必須要和原始資料的欄位名稱完全相同。
接,除了已經定義的名稱:班級編碼、性別之外,再將本例中的『一百、跳高、跳遠、一仟五、聖火隊、助理裁判』每個欄位的所有資料,定義名稱:一百、跳高、跳遠、一仟五、聖火隊、助理裁判。
設計公式,儲存格P2:
=SUMPRODUCT((班級編碼=$N2)*(性別=$O2)*(INDIRECT(P$1)=TRUE))
INDIRECT(P$1)=TRUE:其中 INDIRECT(P$1),巧妙的將儲存格P1中的欄位名稱轉換為實際儲存格範圍(其中每個欄位中的儲存格範圍都已先定義好名稱了)。
然後,複製儲存格P2,貼至儲存格P2:U4。

【後記】
你有發現嗎?只要一個公式即能求出各班的男生/女生參加各個比賽的人數,其實是有一些巧妙的安排的,例如:
(1) O欄中『男』和『女』是分二列安排。
(2) 運算人數的資料表欄位稱和原始資料的欄位名稱完全相同。

2015年10月29日 星期四

Excel-依日期判斷給予警示色彩(設定格式化的條件)

在 Excel 中有一個日期是取貨的期限,網友想要在期限前三天內予以警示,但是如果在D欄中已有『提件日期』,則取消警示,該如何處理?
參考下圖,例如今天的日期是2015/10/29,則項目:007~012都要警示,但是009和011因為已有『提件日期』,則取消警示。大部分的人做法都是使用「設定格式化的條件」來處理。
image

【參考設計做法】
1. 選取儲存格C1:C18。
2. 新增格式化的設定,設定如下:
規則類型:使用公式來決定要格式化哪些儲存格
編輯規則:=(B2-TODAY()<=3)*(B2-TODAY()>=0)*ISBLANK(D2)
格式:設定儲存格底色
image
公式:=(B2-TODAY()<=3)*(B2-TODAY()>=0)*ISBLANK(D2)
(B2-TODAY()<=3)*(B2-TODAY()>=0):判斷日期是否介於今日到前三日。
ISBLANK(D2):判斷儲存格B2是否為空白。

換個做法 
如果要呈現如下的標示,省略一個警示欄位,直接將整列顯示不同色彩。
image
1. 選取儲存格A1:C18。
2. 新增格式化的設定,設定如下:
規則類型:使用公式來決定要格式化哪些儲存格
編輯規則:=($B2-TODAY()<=3)*($B2-TODAY()>=0)*ISBLANK($C2)
格式:設定儲存格底色

思考一下 
比較以上二個公式,做法一致,但是呈現效果不一樣。特別要注意儲存格的位址參照方式。

2015年10月28日 星期三

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

延續上篇文章:Excel-在統計圖中更改時間標籤的間距,參考下圖,如果想要將多個工作表中的資料共用一個圖表(折線圖),該如何處理?
在下圖中,原來的表1~表7是放在多個工作表中,現在把它集合在一起,想要透過儲存格I2中的下拉式選單來選取表的名稱,而折線圖自動會顯示該表的數值內容。
image

【操作與解析】
在儲存格I2中要使用下拉式選單,可以透過「資料驗證」,設定其準則:
儲存格內允許:清單
來源:表1,表2,表3,表4,表5,表6,表7
image
接著,你藉由表1來產生一個折線圖(相關步驟在此省略),然後點選折線圖,觀察資料編輯列中的公式。如下:
=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)
其中『圖表!$B$15』和『圖表!$B$16:$B$27』必須改為動態。
image
因此,要定義二個名稱:
DATA1:=OFFSET(圖表!$H$2,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,1,1)
用以取代:『圖表!$B$15』
DATA2:=OFFSET(圖表!$H$3,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,12,1)
用以取代:『圖表!$B$16:$B$27』
注意:公式中使用的位址要設定為絶對參照,領域要設為「活頁簿」。
image
接著,選取折線圖,將公式:
=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)
改為:
=SERIES(折線圖.xlsx!DATA1,圖表!$A$16:$A$27,折線圖.xlsx!DATA2,1)
如此,便能利用儲存格I5的下拉式選單,來動態顯示想要的資料表的折線圖了。
image

2015年10月27日 星期二

Excel-依據不同時段收費標準計算停車費(SUMPRODUCT,ROW)

網友想要依據進場和出場的停車時間來計算停車費,規則如下(參考下圖):
(1) 停車未滿30分鐘收費0元
(2) 超過30分鐘未滿60分鐘收費30元
(3) 三小時以內每30分鐘收費15元
(4) 第四小時開始每30分鐘收費20元
Excel-依據不同時段收費標準計算停車費(SUMPRODUCT,ROW)
參考範例:
Excel-依據不同時段收費標準計算停車費(SUMPRODUCT,ROW)

【公式設計與解析】
為了方便說明,設計了輔助欄位:C欄和D欄。
1. 計算停車的分鐘數
儲存格C2:=INT((B2-A2)*60*24)
2. 分鐘數轉換為第幾個30分鐘
儲存格D2:=INT(C2/30)+1
3. 計算停車費
儲存格E2:
=(D2>1)*30+SUMPRODUCT((D2>=ROW($3:$6))*15)+(D2>6)*INT(D2-6)*20
該公式由三個部分組成:
(1) 30分鐘至60分鐘之間停車費:(D2>1)*30
(2) 60分鐘至180分鐘之間停車費:SUMPRODUCT((D2>=ROW($3:$6))*15)
(3) 180分鐘以上停車費:(D2>6)*INT(D2-6)*20

2015年10月26日 星期一

Excel-在統計圖中更改時間標籤的間距

有網友問到:在 Excel 中根據下圖中的資料表繪出一個統計圖,觀察座標軸上的刻度,是以每5分鐘為單位標記。如何讓座標軸上是以每15分鐘為單位標記?
Excel-在統計圖中更改時間標籤的間距

【參考做法】
在類別座標軸上按右鍵,選取[座標軸格式]選項。
Excel-在統計圖中更改時間標籤的間距
觀察標籤區中的「標籤與標籤之間的間距」,目前是設定為:自動。
Excel-在統計圖中更改時間標籤的間距
請選取:指定間隔的刻度間距,並將數值改為:3。
如此,便可以讓刻度以 15 分鐘為單位標記了。
Excel-在統計圖中更改時間標籤的間距

2015年10月24日 星期六

Dropbox-編輯文件時就可以同時分享和留言

雲端應用通常是透過在電腦使用瀏覽器或是在行動裝置使用 App 連網後,對檔案或資料夾進行共用、分享等操作。
而當你在電腦中,進入 Dropbox 雲端資料夾,並且在登入的狀態下,在某個檔案中按右鍵,可以發現有四個 Dropbox 的功能選項,讓你可以很方便的操作:分享 Dropbox 連結、在 Dropbox.com 上檢視、檢視過去版本、檢視留言。也就是在檔案總管即可完成在瀏覽器的部分動作。
Dropbox-編輯文件時就可以同時分享和留言
現在,如果你在 Dropbox 資料夾中開啟一個可以編輯的檔案,例如開啟一個 Word 檔,你會發現新增了一個 Dropbox 圖示(如下圖),在此你可以執行:分享連結、留言、檢視檔案版本紀錄、共享資料夾設定。你可以不用切換到檔案總管,再執行這些功能。
Dropbox-編輯文件時就可以同時分享和留言
【例如】
你可以按一下「分享連結」,即可取得該檔案被分享的網址。你不需連到網站或是開啟檔案總管來設定。
你可以在編輯時也同時輸入你的留言(輸入完成要按 Post)。也就是說,在編輯時也同時可以和其他使用者互動、溝通。
Dropbox-編輯文件時就可以同時分享和留言
另一個共享的使用者,可以同時和你互動留言。互動的過程,可以同步在網頁上看到。只要共享的使用者要編輯這個檔案時,都可以看到先前互動的結果。
Dropbox-編輯文件時就可以同時分享和留言
Dropbox 除了讓你分享、共用檔案之外,也一步一步讓你更方便來操作這些動作,讓你在一個工作狀態中就可以同時完成雲端上的操作。

【這樣也可以】
即使你沒有要和他人共享檔案,你也是可以使用留言功能來提醒自己關於這個檔案要注意的地方。

2015年10月23日 星期五

Excel-點選含有Email郵件地址儲存格時,自動開啟Outlook新增郵件

網友問到一個問題:如何在 Excel 檔案含有人員和郵件地址的資料表中查到的結果(如下圖),可以直接點選打開 Outlook 新增郵件?
Excel-點選含有Email郵件地址儲存格時,自動開啟Outlook新增郵件

【公式設計與解析】
通常你在 Excel 的儲存格中輸入一個網址或是Email郵件地址時,當按下 Enter 鍵,即會產生這個超連結。
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件
當你在這個超連結中,按一下右鍵,選取[編輯超連結]選項。
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件
可以看到你可以指定這個超連結是連結到指定的網址,當然也可以指定某個郵件地址。
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件
如果你在儲存格中輸入:test01@ms11.hinet.net,
在超連結中會看到:mailto:test01@ms11.hinet.net
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件
所以得知:如果要連結郵件地址,可以使用『mailto:』協定。因此,套用到公式中,
儲存格E2:=HYPERLINK("mailto:" & VLOOKUP(D2,A2:B17,2,FALSE),
VLOOKUP(D2,A2:B17,2,FALSE))
VLOOKUP(D2,A2:B17,2,FALSE):在儲存格A2:B17中查詢儲存格D2的內容(人員),對應第2欄,傳回一個郵件地址。
再利用 HYPERLINK 函數建立超連結。
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件
當點選儲存格E2的郵件地址時,即會開啟 Outlook 新增郵件。
Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

Google Chrome-快速關閉分頁的音量

你有試著在 Google Chrome 所開啟的分頁標籤上按右鍵?以 Google Chrome 46 版為例,將會看到:新增分頁、重新載入、複製、固定分頁、關閉分頁音訊、關閉分頁、關閉其他分頁、關閉右邊的分頁、重新開啟已關閉的分頁、將所有分頁加入書籤。
以下特別介紹其中三種:
固定分頁:該分頁會被移至最左側,並且將分頁縮到最小,看不到分頁名稱,並且你無法使用關閉圖示。若要還原,則在固定分頁上按右鍵,選取[取消固定分頁]選項。
image
關閉分頁音訊:以 YouTube 影片分頁為例,當你選取關閉分頁音訊時,該分頁不會發出任何聲音,標籤上的符號可以看出沒有聲音。觀察 YouTube 網站時,影片仍在播放,而聲音圖示也沒有被關到最小。這個功能可以在不切換分頁的狀況下,快速關閉分頁中發出的聲音。如果要重新播放出聲音,則在標籤上按右鍵,再選取「開啟分頁音量」。

重新開啟已關分頁:讓你快速開啟先前關掉的分頁,而且你每按一次,會『倒著順序』再次開啟原關閉的檔案,多按幾次來試試吧!(你也可以使用快速鍵:Ctrl+Shift+T)
image

2015年10月22日 星期四

Excel-根據日期區間自動標示色彩(設定格式化的條件)

網友想要取用 Excel 中的一個含有樓層、房號、入住時間、退房時間的住宿資料表,依據不同樓層,將有住宿的時間內的日期,在該房號中自動標示出來,該如何處理?
Excel-根據日期區間自動標示色彩(設定格式化的條件)
就像下圖這樣,當住宿的總表填入資料後,該樓層、房號對應的日期會被標示出來。
Excel-根據日期區間自動標示色彩(設定格式化的條件)
參考以下的做法:
1. 定義儲存格名稱
選取總表的儲存格A1:E23,按 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:樓層、房號、入住時間、退房時間。
特別注意:每個名稱的領域都要設定為「活頁簿」。
Excel-根據日期區間自動標示色彩(設定格式化的條件)
2. 設定格式化的條件(以4F工作表為例)
(1) 選取儲存格B2:P13。
(2) 選取[常用/樣式]功能中的[設定格式化的條件/新增規則]選項。
(3) 編輯格式化規則
選取規則類型:使用公式來決定要格式化哪些儲存格
編輯規則:=SUMPRODUCT((房號=$A2)*(入住時間<=B$1)*(退房時間>=B$1))
格式:在儲存格填滿中設定想要的色彩
Excel-根據日期區間自動標示色彩(設定格式化的條件)
(4) 複製格式到其他工作表
選取「4F工作」中的選取儲存格B2:P13,按一下「複製格式」按鈕,分別在「2F工作表」和「3F工作表」的儲存格B2上按一下,即完成複製格式工作。
大功已經告成。

使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

在校內教職員研習後的二篇文章繼續發酵著:
有同仁問到如果要用手機唸一唸就可以在電腦中取得這些文字,該如何處理?我想的到的,還是要藉助『Google 文件』。參考以下的步驟:
1. 你先在電腦中,連線至 Google 硬碟,再新增一個 Google 文件。
使用手機當成Google文件的文字輸入工具(用唸的輸入文字)
2. 接著在你的手機中,進入 Google 雲端硬碟 App,點選這個新增的文件。
你可以看到有另一人(在電腦)在檢視這個文件,在文件中點一下,進入編輯狀態。
3. 按一下輸入法中的麥克風,進入語音輸入狀態。
使用手機當成Google文件的文字輸入工具(用唸的輸入文字) 使用手機當成Google文件的文字輸入工具(用唸的輸入文字)
5. 此時,你可以開始唸出想要的文字。
使用手機當成Google文件的文字輸入工具(用唸的輸入文字) 使用手機當成Google文件的文字輸入工具(用唸的輸入文字)
6. 同一時間看著你的螢幕,那些字幾乎是同步的出現在電腦上。(文字旁會標示誰在輸入)
既然電腦上已有這些文字,即可隨你後續的操作了。
使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

2015年10月21日 星期三

在Google文件中使用語音輸入來建立文字內容

先前的文章(Google-使用翻譯和拍照功能幫你輸入文字)中提到在教職員研習時,介紹了使用語音和拍照方式來輸入文字,獲得同仁的迴響。也有同事問到如果在 Google 翻譯中輸入語音之後,還要複製/貼上到 Word 檔中,整個過程如果是比較長時,會有些不方便之處,還有更方便的方法嗎?
若想要更直接的方式,我想還是藉助 Google文件 的語音輸入功能是不錯的選擇。
當你使用 Google 翻譯的介面來使用語音輸入時,可以不用輸入帳號即可使用,但是內容無法即時儲存,如果你使用 Google 文件的語音輸入,則可以將內容即時儲存,並且下載成 Word 檔。 
使用方法也很簡單:
1. 先在 Google 雲端硬碟中新增一個Google的文件。
2. 點選[工具]功能表,再點選「語音輸入」選項。
在Google文件中使用語音輸入來建立文字內容
3. 在出現的麥克風圖示上按一下,你就可以用念方式的來輸入文字。
在Google文件中使用語音輸入來建立文字內容
4. 等到你暢所欲『唸』之後,文字也依依跑出來了。
在Google文件中使用語音輸入來建立文字內容
5. 輸入完成之後,點選[檔案]功能表的[下載格式]功能,再點選「Microsoft Word」。即可將這些內容下載為一個 Word 檔。
在Google文件中使用語音輸入來建立文字內容
所以,你只要有網路,並且登入 Google 雲端硬碟,在 Google 文件中輸入資料應該是一件很方便的事。而且輸入完成之後直接可以下載成 Word檔,便可以和你現在正在編輯的文件接軌,使用上應該算是便利的。

檢視其他文章

好康東東