2016年2月29日 星期一

Excel-依據打卡時間判斷上班的班別(SUMPRODUCT)

網友問到:如果根據一個上下班打卡的資料表,如何在 Excel 中計算各個班別的數量?
如下圖,共有三種班別,起迄時間都不相同,如何根據打卡時間的清單,自動判斷各個班別(早班、晚班、全天)的數量?
Excel-依據打卡時間判斷上班的班別(SUMPRODUCT)

【公式設計與解析】
選取B欄和C欄有資料的儲存格,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:上班打卡、下班打卡。
由於實際狀況多樣,所以,以下的範例不見得實用。
因為上班打卡不見得會準時,可能提早打卡或是延後打卡,因此假設上班前後一個小時內的打卡都接受。
1. 計算全天班
儲存格H3:=SUMPRODUCT(1*((下班打卡-上班打卡)>=12/24))
由於在 Excel 中一天24小時被定義為『1』,因此一小時以 1/24 計。而 12/24 表示 12 小時。
(下班打卡-上班打卡)>=12/24:當下班打卡和上班打卡時間相減時,必須大於 12 小時。在 SUMPRODUCT 函數,判斷會傳回是否大於 12/24 的邏輯運算結果(TRUE/FALSE)。
而公式中的『1*』,用意在於將 (下班打卡-上班打卡)>=12/24 傳回的 TRUE/FALSE陣列轉換為 1/0 陣列。
最後透過 SUMPRODUCT 函數計算乘績和,即為所求。

2. 計算晚班
儲存格H4:=SUMPRODUCT(1*(ABS(14/24-上班打卡)<=1/24))
ABS(14/24-上班打卡)<=1/24:其由 14/24 表示一天的 14 時。14/24-上班打卡為計算上班打卡時與 14 時的差距,該式用以判斷該差距是否小於 1/24(1小時)。如果『是』,則為 14 時附近打卡,如果『否』,則不是在 14 時左右來打卡。
ABS 函數用以取數值的絶對值,而公式中的『1*』,用意在於將 ABS(14/24-上班打卡)<=1/24 傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後透過 SUMPRODUCT 函數計算乘績和,即為所求。

3. 計算早班
儲存格H2:=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-(上式計算全天班)
因為早班和全日班的上班時間重覆,所以當計算上班時間是否在 9 時左右時,必須扣掉全天班的數量。
儲存格H2:=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-SUMPRODUCT(1*
((下班打卡-上班打卡)>=12/24))

【延伸練習】
儲存格D2顯示的是每個儲存格判斷的各日班別。
儲存格D2:=IF(C2-B2>=12/24,"全天",IF(ABS(9/24-B2)<=1/24,"早班","晚班"))

2016年2月26日 星期五

在Word中編輯PowerPoint簡報的投影片當作講義

學校開學了,老師們早在寒假期間就陸續備課。很多老師製作了自己的簡報當為上課教材,但是投影片不一定適合直接印給學生當講義,而如果使用 PowerPoint 所提供的講義模式來列印,也無法滿足老師的需求,該如何來調整變化?
在Word中編輯PowerPoint簡報的投影片當作講義
說到要編製講義,使用 Word 或許還是比較方便實用的工具。而在 PowerPoint 中其實就有匯出投影片到 Word 的功能。
當你選取[檔案]功能表,再選取[匯出]選項。在[匯出]功能區中點選「建立講義」,PowerPiont 會執行:
1. 將投影片和備忘稿於 Word 文件
2. 在 Word 中編輯並格式化內容
3. 當簡報變更時,自動更新講義中的投影片
按一下「建立講義」按鈕。
在Word中編輯PowerPoint簡報的投影片當作講義
在[傳送至 Microsoft Word]對話框中,有五種版面配置可以選擇。當你點選一個格式,按下[確定]按鈕後,投影片會陸續被傳送到 Word 文件中。
在Word中編輯PowerPoint簡報的投影片當作講義
本例在將投影片新增至Microsoft Word 文件中,選取「貼上」。一張投影會依你選取的格式轉換至 Word 中,而且一張投影片對應一個 Word 頁面。在 Word 中環境中你可以善用排版功能來編製講義。
在Word中編輯PowerPoint簡報的投影片當作講義
當你在 Word 中的投影片上按右鍵,可以對投影片物件,進行剪裁、編輯、開啟和轉換。
在Word中編輯PowerPoint簡報的投影片當作講義
選取:開啟,Word 會幫你開啟這個簡報,讓你在 PowerPoint 環境下編輯。(本例會產生一個新的簡報檔)
選取:編輯,Word 功能表區顯示的是 PowerPoint 的功能表,可以直接進行各種編輯操作。
你可能會問:在將投影片新增至Microsoft Word 文件時選取「貼上」,如果在 Word 中修改投影片,會不會讓原始的投影片也跟著修改?答案是:不會。反之,如果你在原始簡報中修改了投影片,並不會改變 Word 中的投影片,要特別注意喔!
在Word中編輯PowerPoint簡報的投影片當作講義
但是,如果在將投影片新增至Microsoft Word 文件時選取「貼上連結」,則在 Word 文件中的每一張投影片會和原始簡報檔產生連結。也就是說,在 Word 文件中點選投影片來編輯時,其實都是在編輯原始的簡報檔。因此,當你修改了原始簡報檔的投影片內容,Word 中的投影片也會跟著被修改。
在Word中編輯PowerPoint簡報的投影片當作講義
以上二種方法各有特點,視你的用途,各取所需吧!

Word-如何快速移除文件中的所有圖片(保留文字和格式)

有同仁問到:一份文件200頁,其中有很多大大小小的圖片,如何才能快速移除文件中的所有圖片,並且保留原來的文字和格式?
參考下圖,文件中的許多地方都有一些各種格式的圖片,可能高達數十、數百個,要移除文件中的所有圖片,並且保留原來的文字和格式,如果一個一個圖片點選再刪除,實在不是太容易的事。如果複製文件後貼至記事本中,只會留下文字,但格式也會全跑掉,該如何處理呢?
Word-如何快速移除文件中的所有圖片(保留文字和格式)
方法其實很簡單:
請先開啟[尋找與取代]對話框,於[尋找目標]文字方塊中輸入「^g」,然後[取代為]文字方塊中要留空。當你按下[全部取代]按鈕,所有圖片瞬間秒殺!
Word-如何快速移除文件中的所有圖片(保留文字和格式)
你可以在[取代]區中按下[指定方式]按鈕,再選取「圖形」,Word 會自動幫你輸入『^g』。
Word-如何快速移除文件中的所有圖片(保留文字和格式)
Word-如何快速移除文件中的所有圖片(保留文字和格式)
不過,也要特別注意,文件中的圖片、美工圖案等都可以使用此方法來刪除。但是,如果你的圖案是浮動的,而非和文字排列在一起時(例如下圖的兩個圖案),無法以這個方法來刪除。
Word-如何快速移除文件中的所有圖片(保留文字和格式)

2016年2月25日 星期四

Excel-一欄分多欄資料重組(OFFSET,INT,MOD)

有網友問到一個在 Excel 工作表中資料重組的問題:如下圖工作表中資料清單,甲、乙、丙、丁四欄各有24列資料,如何將其重組為二個甲、乙、丙、丁四欄各有12列資料?
下圖中的公式中的參數 12 和 4 對應到圖示中的 12 和 4。
Excel-一欄分多欄資料重組(OFFSET,INT,MOD)

【公式設計與解析】
儲存格F:=OFFSET($A$2,MOD(ROW(1:1)-1,12)+INT((COLUMN(A:A)-1)/4)*12,
MOD(COLUMN(A:A)-1,4))
複製儲存格F2,貼至儲存格F2:M13。
MOD(ROW(1:1)-1,12):當公式向下複製時,可以產生『0,1,2,3,4,5,6,7,8,9,10,11,
0,1,2,3,…』
INT((COLUMN(A:A)-1)/4)*12:當公式向右複製時,可以產生『0,0,0,0,12,12,12,12,…』
MOD(COLUMN(A:A)-1,4):當公式向右複製時,可以產生『0,1,2,3,0,1,2,3,…』
Excel-一欄分多欄資料重組(OFFSET,INT,MOD)
將以上三式代入 OFFSET 函數,可取得對應的儲存格內容。

2016年2月24日 星期三

Excel-每欄取定量重排並去除空白(OFFSET,MOD,INT)

網友問到:在 Excel 的工作表中(如下圖左),如果想要在每欄取定量來重排,並且去除空白儲存格(如下圖右),該如何處理?
在下圖中,在甲、乙、丙三種不同且重覆的欄位,如果根據取樣中的數量(本例為5),將甲的多欄資料重組在一欄(每欄取5個),並且希望去除空格。
Excel-每欄取定量重排並去除空白(OFFSET,MOD,INT)

【公式設計與解析】
(1) OFFSET(A$2,MOD(ROW(1:1)-1,$H$2),INT((ROW(1:1)-1)/$H$2)*3)
MOD(ROW(1:1)-1,$H$2):依儲存格H2的數值(=5),當公式向下複製時傳回 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, ...。
INT((ROW(1:1)-1)/$H$2)*3:依儲存格H2的數值(=5),當公式向下複製時傳回 0, 0, 0, 0, 0, 3, 3, 3, 3, 3, 3, ...。

(2) 取出定量重排
儲存格I2:=IF(ISBLANK(第(1)式,"",第(1)式)
當 OFFSET 函數取得的儲存格內容為空白儲存格時,改以空字串顯示。
複製儲存格I2,往下各列貼上。

(3) 去除空白儲存格
儲存格M2:{=IFERROR(OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17), 
FALSE),ROW(1:1))-2,,,),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+F3 鍵,Excel 會自動產生「{}」。
IF(I$2:I$17<>"",ROW(I$2:I$17):在陣列公式中判斷儲存格I2:I17裡不是空白儲存格者,傳回其列號(ROW(I2:I17)。
SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE):將上式傳回的列號中,由小到大依序取出其最小者。
OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE),ROW(1:1))-2,,,):將
上式代入 OFFSET 函數傳回對應儲存格的內容。
最後利用 IFERROR 函數,將因查詢不到資料而傳回錯誤訊息者,以空白顯示。
複製儲存格M2,往下各列貼上。

2016年2月22日 星期一

Excel-在日期清單中每日只統計當日數量(TODAY,SUMPRODUCT)

有網友問到:在 Excel 的工作表中有一個日期清單記錄每天的數值,如何每天自動只顯示當天的小計資料(參考下圖),該如何處理?
如下圖,在日期和數量清單中,資料會一直輸入,如何才能只顯示今天的小計而已。
Excel-在日期清單中每日只統計當日數量(TODAY,SUMPRODUCT,OFFSET)

【公式設計與解析】
儲存格E2:=SUMPRODUCT((A2:A1000=TODAY())*B2:B1000)
假設你的資料不會超過 1000 筆,而 TODAY 函數可以取得今天的日期,透過SUMPRODUCT 函數計算符合 A2:A1000=TODAY() 者和其對應的數量(B2:B1000) 的乘積和。
如果你的清單資料是不斷的增加,你可能會使用這樣的公式:(不建議)
(X) 儲存格E2:=SUMPRODUCT((A:A=TODAY())*B:B)
因為上式中使用A欄整欄來運算乘積和,可能容易產生當機現象。稍微修改一下:
儲存格E2:=SUMPRODUCT((OFFSET(A2,,,COUNT(A:A),)=TODAY())*(OFFSET
(B2,,,COUNT(B:B),)))
先利用 OFFSET(A2,,,COUNT(A:A),) 和 OFFSET(B2,,,COUNT(B:B),) 來找出有資料的儲存格範圍,再讓 SUMPRODUCT 函數計算乘積和。
當每天開啟這個 Excel 檔時,就會以當天的日期來抓取資料計算。

2016年2月21日 星期日

Excel-以星期幾為主顯示各月日期的萬年曆(DATE,WEEKDAY)

在下圖中,是一個以星期幾為主,來顯示各月日期的萬年曆,如何在 Excel 的工作表中建立這個萬年曆?
有了這個萬年曆,只要改變儲存格A1(年份),即可顯示當年的月份和星期幾的對照。因此,例如:可以快速找到各月週六的日期。要如何來設計公式?
image

【公式設計與解析】
儲存格B2:=VALUE(TEXT(DATE($A$1,COLUMN(A:A),1)-WEEKDAY(DATE($A$1,
COLUMN(A:A),1),2)+ROW(1:1),"dd"))
(1) DATE($A$1,COLUMN(A:A),1)
藉由 DATE 函數取得各年各月的第一天日期,Excel 會傳回一個數值。
(2) WEEKDAY(DATE($A$1,COLUMN(A:A),1),2)
先藉由 DATE 函數取得各年各月的第一天日期,再利用 WEEKDAY 函數來找出每個月的第一天的傳回值。本例是選取參數 2,代表星期一到星期日,傳回數字 1 到 7。
image
公式中的 COLUMN(A:A) 乃用於向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(M:M)=12。
(3) 完整公式
儲存格B2:=VALUE(TEXT(第(1)式-第(2)式+ROW(1:1),"dd"))
第(1)式-第(2)式+ROW(1:1):取得每個月的第一個儲存格(儲存格B2)應該顯示的日期,而 ROW(1:1) 向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ ... →ROW(31:31)=31。
TEXT(第(1)式-第(2)式+ROW(1:1),"dd"):透過 TEXT 函數將上式的日期取出日期中的『日期數值』,並以二碼顯示("dd")。當向下複製公式時,即可產生連續的日期數值。
最後再以 VALUE 函數將上式 TEXT 取得的結果(文字)轉換為數字。
複製儲存格B2,貼至儲存格B2:M43。

2016年2月20日 星期六

Word-將文件中的頁碼設定為不連續

學校同仁問到:在使用 Word 編輯文件時,由於列印出來的文件還要插入其他資料,所以頁碼無法連續,若在其中插入空白頁,列印時又會浪費紙張,該如何處理?
可以在 Word 中設定不連續頁碼!如下圖,第 4 頁的頁碼要修正為第 6 頁,其後的頁碼要跟著依序順延,應該如何操作?
image
參考以下的步驟:
1. 先在一個 Word 文件中的頁尾插立頁碼。(本練習要將第 4 頁改為第 6 頁)
image
2. 在第 4 頁的最前面設定其後新增『一節』。
例如:可以選取[版面配置]功能表的[分隔設定]選單下的「分節符號/下一頁」選項。
image
例如:也可以在[版面設定]對話框中的[邊界]標籤下,選取『套用至插入點之後』,來新增一節。
image
3. 設定不同的頁碼。
你可以看到目前第 1, 2, 3 頁在第 1 節,而第 4, 5, 6 頁在第 2 節。
image
請你選取第 4 節的頁碼,在其上按右鍵,選取「頁碼格式」選項。
image
在[頁碼格式]對話框中修改為:起始頁碼 6。
image
原來的頁碼『第4頁』已改為『第6頁』:
image
同理:你也可以練習插入第 3 節,並將原第 7 頁的頁碼改為第 12 頁,其後各頁的頁碼依序順延。
image

Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)

開學之初而已,就有同仁問到在 Excel 中,關於名次計算的問題。參考下圖,一般在計算名次時,可能會有這樣的狀況:
1. 名次重覆跳過:例如有 2 個第 4 名,所以沒有第 5 名。
2. 名次重覆不跳過:例如有 2 個第 4 名,原來的第 6 名成為第 5 名。
3. 名次不重覆:例如有 2 個第 4 名,會隨機產生一個第 4 名,另一個為第 5 名。
Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)

【公式設計與解析】
依上圖,選取儲存格A1:A24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。使用相同方法,將E欄中有資料的儲存格,定義名稱:輔助欄。

1. 名次重覆跳過
儲存格B2:=RANK(A2,數值)
使用 RANK 函數是最通用的計算排名工具。再提供以下三種方式,都能達到相同的排名結果。
(1) 儲存格B2:=COUNTIF(數值,">"&A2)+1
使用 COUNTIF 函數計算在數值陣列中,大於儲存格A2的有幾個,然後再加 1。
(2) 儲存格B2:=SUMPRODUCT(--(數值>A2))+1
在 SUMPRODUCT 函數中找出數值是否大於儲存格A2的 TRUE/FALSE 陣列,利用『--』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列,再由 SUMPRODUCT 函數執行『乘積和』運算。最後再加 1,即為所求。
(3) 儲存格B2:{=SUM(IF(數值>A2,1,0))+1}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
SUM(IF(數值>A2,1,0)) 的計算原理同 SUMPRODUCT(--(數值>A2))。

2. 名次重覆不跳過
儲存格C2:=SUMPRODUCT((數值>A2)*(1/COUNTIF(數值,數值)))+1
1/COUNTIF(數值,數值):藉由 COUNTIF 函數,找出每個名次出現的次數,1/COUNTIF 的用意是將計算後的次數加以倒數,例如:某一名次有 3 人,則 1/COUNTIF 為 0.333333,某一名次有 4 人,則 1/COUNTIF 為 0.25。透過SUMPRODUCT 函數加總運算後,結果均會為 1。
(數值>A2)*(1/COUNTIF(數值,數值)):找出大於儲存格A2者,再運算 1/COUNTIF 的部分。其中『*』運算,相當於執行邏輯 AND 運算。

3. 名次不重覆
儲存格E2:=A2+RAND()
儲存格D2:=RANK(E2,輔助欄)
藉由輔助欄位將A欄中的每個儲存格加上一個亂數(介於0和1之間的數),然後再置入 RANK 函數中加以排序,如此得到的結果,會是隨機讓同名次者,分出大小。

2016年2月19日 星期五

Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

網友問到 Excel 的問題:如何在一個日期區間中計算工作天數、放假天數?
參考下圖,因為每個人遇到的狀況不同,當要計算工作天數時,你可能要考量到星期六、星期日和放假日,也要考量到補班日,該如何處理?
Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

【公式設計與解析】
1. 不含六日的工作天數
儲存格C2:=NETWORKDAYS(A2,B2)
利用 NETWORKDAYS 函數,只要給予起始和終止的日期,即可計算日期區間中扣除星期六日的天數。
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
儲存格C2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 p="">
"A" & N(A2) & ":A" & N(B2):將二個日期的數值組成一個儲存格範圍,例如:A42370:A42552。其中 N 函數可以將一個日期傳回其代表的數值。
INDIRECT("A" & N(A2) & ":A" & N(B2):利用 INDIRECT 函數將上式轉換為真實的儲存格參照位址。
ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))):將上式置入 ROW 函數,轉換為列號範圍,例如:A42370:A42552→ROW(A42370:A42552),在 SUMPRODUCT 函數中可以表示為 42370, 42371, 42372, ..., 42551, 42552 組成的陣列。
WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 nbsp="" strong="">WEEKDAY
函數中利用參數『2』,得到傳回值小於 6 者(表示星期一至星期五)的 TRUE/FALSE 陣列。
Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)
1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6 nbsp="" strong="">TRUE/FALSE
 陣列轉換為 1/0 陣列。
最後,透過 SUMPRODUCT 函數加總,即為所求。

2. 不含六日、不含假日的工作天數
儲存格D2:=NETWORKDAYS(A2,B2,$G$3:$G$16)
在 NETWORKDAYS 函數置入第 3 個參數,其為放假日的儲存格範圍。
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2)):
求在儲存格G3:G16的放假日中,介於儲存格B2和儲存格A2兩個日期之間的個數。
儲存格D2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" 
& N(B2))),2)<6>=A2))

3. 不含六日、不含假日、含補班日的工作天數
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2)):
求在儲存格G19:G21的補班日中,介於儲存格B2和儲存格A2兩個日期之間的個數。
儲存格E2:=NETWORKDAYS(A2,B2,$G$3:$G$16)+SUMPRODUCT
(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))
如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:
儲存格E2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" &
N(B2))),2)<6>=A2))+
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))

2016年2月17日 星期三

Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN)

有網友問到:在 Excel 中有一個資料清單,如下圖,如果想要在每種相同商品中找出最高/最低報價及其廠商,該如何處理?
在下圖中,有甲、乙、丙、丁、戊等不同廠商,分別有A、B、C、D、E等不同商品,現在要來找出各種商品的最高/最低報價和所屬廠商。
Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN,陣列公式)

【公式設計與解析】
選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:廠商、商品、報價。
1. 最高報價的報價/儲存格H2:
{=MAX(IF(商品=E2,報價,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
在陣列公式中,判斷商品陣列和儲存格E2相同者,傳回其報價。再透過 MAX 函數傳回最大值。例如:儲存格E2為『A』,則會傳回 A 中報價的最大值。

2. 最高報價的廠商/儲存格G2:
{=OFFSET($A$1,MAX(IF((商品=E2)*(報價=H2),ROW(廠商),FALSE))-1,)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
IF((商品=E2)*(報價=H2),ROW(廠商),FALSE):符合雙條件『商品=E2』和『報價=H2』者,傳回廠商所在列號。
再透過 OFFSET 函數取出列號對應的儲存格內容。

同理:
3. 最低報價的報價/儲存格H3:
{=MIN(IF(商品=E3,報價,FALSE))}
4. 最低報價的廠商/儲存格G3:
{=OFFSET($A$1,MIN(IF((商品=E3)*(報價=H3),ROW(廠商),FALSE))-1,)}

最後,複製儲存格H2:G3,貼至儲存格H2:H11。

2016年2月16日 星期二

Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)

網友問到:在下圖中的 Excel 資料表中,如果要計算三個條件都成立的個數有幾個,該如何處理,為何下圖中的公式會『錯誤』。其中的重點是要滿足日期中符合所要月份的條件。
選取儲存格A1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、項目。
Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)

【公式說明】
儲存格F4:=SUMPRODUCT((人員=F1)*(MONTH(日期)=F2)*(項目=F3))
MONTH(日期)=F2:取出日期陣列中的『月份』數值。
(人員=F1)*(MONTH(日期)=F2)*(項目=F3):『*』相當於執行邏輯 AND 運算。三個條件都成立者傳回 TRUE,不成立者傳回 FALSE,在 SUMPRODUCT 函數的乘積和運算時,會將 TRUE/FALSE 轉換為 1/0

為何以下的公式會錯誤?
(X)儲存格F4:=SUMPRODUCT((人員=F1)*(VALUE(LEFT(日期,2))=F2)*(項目=F3))
以圖中的儲存格B25為例,雖然顯示04月23日,但是內部儲存為42483,當你執行:
VALUE(LEFT(日期,2))會傳回『42』(42483的前2碼),並非你所需的月份『04』。
如果你想取得儲存格B25的數值,可以使用公式:=N(B25)。

2016年2月15日 星期一

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID)

本篇文章純粹是要練習幾個 Excel 函數的應用,是否有實用性沒有考量到。參考下圖,有兩個儲存格想要比較其中內容,每一個位元的字元是否相同。
一、儲存格中的內容全為數字
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

(1) 計算各位元相同者
儲存格C5:
=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))=VALUE(MID($B2,
COLUMN(A:H),1))))
COLUMN(A:H):在 SUMPRODUCT 函數中代表數字 1 ~ 8。
MID($A2,COLUMN(A:H),1):在 SUMPRODUCT 函數中以陣列方式取出儲存格A2的第1個字元至第8個字。
VALUE(MID($A2,COLUMN(A:H),1)):將上式的結果(文字型態的數字)轉換為數值。
VALUE(MID($B2,COLUMN(A:H),1)):原理同上式。
SUMPRDUCT 函數中的『1*』,其作用為透過『乘以1』的運算動作,將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(2) 計算各位元不同者
儲存格C6:
=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))<>VALUE(MID($B2,
COLUMN(A:H),1))))
此公式和是將儲存格C5公式中的『=』改為『<>』,原理相同。

(3) 在對應儲存格中顯示各個字元是否相同,傳回『TRUE/FALSE』
儲存格C2:
=XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),1)))
複製儲存格C2,貼至儲存格C2:J2。
XOR 函數可以判斷兩個數是否相同,相同傳回 TRUE,不同傳回 FALSE

(4) 在對應儲存格中顯示各個字元是否相同,傳回『相同/不同』
儲存格C3:
=IF(XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),
1))),"不同","相同")
複製儲存格C3,貼至儲存格C3:J3。

二、儲存格中的內容全為文字
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)
儲存格C5:
=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)=MID($B2,COLUMN(A:H),1)))
儲存格C6:
=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)<>MID($B2,COLUMN(A:H),1)))
儲存格C2:
=MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1)
儲存格C3:
=IF(MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1),"不同","相同")
Excel在判斷文字是否相同時,會將同一字母大寫和小寫視為相同。而相同公式在儲存格內容全改為數字時仍可使用,也就是可以取代一、中的公式。這個公式比較簡短,也沒有用到XOR函數。其中數字可以被視為文字來處理。
Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

2016年2月14日 星期日

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

當教師們在備課時,手邊會有許多自行搜集或是各家書商提供的多種光碟資料,由於多片光碟在管理和使用上並不方便。尤其要帶到課堂上,如果漏帶了某些資料,當天就要開天窗,如果你有習慣把某些資料備份在雲端,可以解決部分的問題。如果你想選擇將整片光碟複製到硬碟上,或許也是可行,但是在管理檔案上顯得較為不方便。
你應該用過壓縮檔(例如:ZIP、WinRAR檔等),如果將多個資料夾和檔案縮壓成一個檔案,透過壓縮/解壓縮軟體,可以直接取用壓縮檔內的檔案,所以在管理和使用上較為方便。
相同概念,如果能把光碟內容儲存成一個 ISO 檔(若有廠商願意提供,那就更棒了!),就可以縮小這些問題了。你可以試著使用燒錄軟體將整片光碟內容或是光碟中的部分檔案/資料夾燒錄成 ISO 檔。
Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用
要如何使用這個 ISO 檔呢?在 Winrdows 10 裡使用 ISO 檔非常簡單。打開檔案總管,在這個 ISO 檔上按右鍵,選取『掛接』選項,或是直接在 ISO 檔上按二下。
Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用
你的本機磁碟中會多了一個『虛擬光碟』:
Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用
點選這個光碟機,即可取用其中的檔案/資料夾。
Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用
如果想要卸載這個虛擬光碟,則在光碟機上按右鍵,選取「退出」即可。你還可以同時掛載多個虛擬光碟。
Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用
不過,使用上也要先提醒,當光碟內的資料量較大時,要製作成 ISO 檔會花較多的時間。現在流行共同備課,不妨大家互相分擔一點工作,再一起分享使用吧!將 ISO 檔備份在雲端硬碟(私有雲或公有雲)中,即可讓多人下載使用。

整合Google地圖、你的時間軸、在地嚮導貢獻

最近學校放寒假,有比較多的時間到處旅遊,在台灣各地移動。每天都使用手機和電腦在 Google 地圖上把玩、檢視,不免引起同事和親人的好奇,常有人問我為何這麼有興趣在 Google 地圖上,本文就來回答他們的問題。
因為我個人的使用習慣,在旅遊的前、中、後,Google 地圖是非常吃重的助手,除了查詢地點位置、地點的相片、地點的評分和評論之外,通常也用來規劃路線,有時也會用來導航,這些本文就不再贅述了。然而,因為它已整合了『您的時間軸』,而我也加入了 Google 的在地嚮導,所以 Google 地圖上的『您的貢獻』,也是變成一個有趣的地方。
在 Google 地圖中如果你登入 Google 帳號,而且這個帳號也是你在手機上登入的帳號,並且 GPS 定位功能也開啟了。在 Google 地圖的選單中,請你先注意到:『您的時間軸』和『您的貢獻』這兩個項目。
整合Google地圖、你的時間軸、在地嚮導貢獻
在時間軸中,除了記錄你的重要停留點(以紅色點顯示)之外:(先注意到下圖中『時間軸』一旁的鎖頭圖示,表示該記錄只有你看的到)
整合Google地圖、你的時間軸、在地嚮導貢獻
還可以在每一天中,使用時間軸方式標示你所停留點的名稱和時間,如果你使用手機或電腦上傳相片至 Google 相簿中,Google 地圖也會將大約時間時拍的相片顯示在該地點中,很妙吧!
整合Google地圖、你的時間軸、在地嚮導貢獻
如果地點或時間不對,你也可以自行修正:(不想顯示該停留點,也可以刪除喔)
整合Google地圖、你的時間軸、在地嚮導貢獻
整合Google地圖、你的時間軸、在地嚮導貢獻
整合Google地圖、你的時間軸、在地嚮導貢獻
在某些地點你還可以自訂『暱稱』,Google 會幫你放置圖釘。例如,2016年2月6日大地震那天,我正好人在台南老家,歷經了劫後餘生的感受,地震後開車四處繞一下,隨手拍下房子傾斜的照片,日後再回顧這段往事時,相片和時間等元素都會一併出現。
整合Google地圖、你的時間軸、在地嚮導貢獻
當你在手機的 Google 地圖 App 中選取「時間軸」,也可以看到每一天的停留點的資訊:
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
你可以編輯每個停留點:
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
如果你的時間軸功能有問題,可以檢視選單中的「時間軸設定」,其中『Google 相簿』的狀態是啟動的,而位置回報功能要開啟,定位紀錄也要開啟。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
再來,如果你已加入『Google 在地嚮導』,則你在 Google 地圖中可以檢視「我的貢獻」,你可以看到曾經對某個地點給過的評分和評論,及在該地點上傳的相片:
整合Google地圖、你的時間軸、在地嚮導貢獻
你在每個 Google 地圖上的地點可以新增相片和編輯評論:
整合Google地圖、你的時間軸、在地嚮導貢獻
如果你在手機的 Google 地圖 App,則可以在「我的貢獻」中看到(在地嚮導)自己的評分和評論記綠及上傳的相片。而 Google 也很貼心的將上傳的照片和評論整合在一起,所以別人可以看你的資料,你也可以看到別人的資料。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
其中,評論、評分和相片是可以隨時修改和刪除的。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
如果你對某個地點給予評分或評論,或是你剛好在這個地點而你也在地點上點選該地點時,Google 會問你『熟悉這個地方嗎?協助別人作決定』,只要點一下「確定」,即可開始回答一些問題。問題可能高達好幾十個,Google 一次只會問你幾個問題,如果你主動再點選相同地點,它會不斷的問你各種問題。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
眾人所回答的問題形成大數據,會顯示在該地點的資訊中,點選後可得詳細資料,而 Google 提問的問題不斷的在增加,眾人的答案傾向也會改變相關顯示的資訊內容。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
回答完問題,會顯示你的作答記錄,你也可以看到在地嚮導的積分分佈。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
因為大家的資訊都是公開的,所以你看到別人的評論,點選某個人的姓名或頭貼,可以看到這個人的其他評論和投放的照片。
整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻
由於手機隨身攜帶,所以資訊整合的愈多愈方便,除了可以看別人的提供的資訊,也可以將個人的資訊提供給網友,這是個分享的時代,愈多人加入愈能提升品質。

好康東東