2015年1月31日 星期六

Excel-分日期計算不重覆項目數(SUMPRODUCT,COUNTIF,OFFSET)

在下圖中是一個 Excel 的資料表,如果要計算各個日期中不含空白的不重覆資料個數,該如何處理?

 

【設計公式】

選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

思考之後,發現得設計一個輔助欄位才能順利設計公式。

儲存格C2:=IF(ISBLANK(B2),"???",B2)

若B欄的儲存格內容為空白,則顯示「???」,這個內容不可以和B欄中的項目內容有所重覆。

儲存格F2:
=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(
日期,E2),),OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),)))-IF(COUNTIF(
OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),),"???")>=1,1,0)

公式這麼長是要嚇死人嗎?稍微來分解一下:

OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),):找出合於儲存格E2的日期儲存格範圍。(別名:日期E2)

其中 MATCH 函數找出符合儲存格E2的第一個儲存格位置,COUNTIF 函數則找出和儲存格E2內容相同的儲存格有幾個。

原公式中變化為二個部分:

(1) SUMPRODUCT(1/COUNTIF(日期E2,日期E2))

求出符合儲存格E2內容的儲存格範圍中包含「???」的不重覆個數。

(若要進一步了解可參考:Excel-計算不重覆的數值個數

(2) IF(COUNTIF(日期E2,"???")>=1,1,0)

求出符合儲存格E2內容的儲存格範圍中是否包含「???」,若是傳回 1,若否傳回 0。

將公式 (1)-(2),即為所求。

冬天的雙溪公園

台北市的雙溪公園在士林的泰北高中旁,是往故宮的至善路和往陽明山的起點的福林路交會處,一個三角地段形成的公園:

公園裡除了樹木之外,還有小湖、小橋、小山,還有鳥兒飛來停靠。今天是陰霾之日,而且溫度稍低,趁著中午吃完飯出來走動一下。

公園裡的花卉有些盛開,有些已在凋謝,看見白色和粉紅的茶花非常的漂亮。

2015年1月30日 星期五

Excel-依條件調整分鐘數進位到小時(INT,MOD,HOUR,MINUTE)

在 Excel 的一個資料表中有一個時間構成的數列,如果其分鐘數:

若為00~19者要捨去,若為20~29要變為30,若為30~49者要變為30,若為50~50者要進位至小時。基於這些條件來調整時間,該如何處理呢?(參考下圖)

 

【公式設計】

儲存格D2:

=TIME(HOUR(A2),(MOD(MINUTE(A2),30)>20)*(INT(MINUTE(A2)/30)+1)*30,0)

HOUR(A2):取得儲存格A2時間數值中「時」的部分。

MINUTE(A2):取得儲存格A2時間數值中「分」的部分。

MOD(MINUTE(A2),30)>20):判斷儲存格A2時間中分的數值是否為20~29或50~59,結果會傳回 TRUE/FALSE

(INT(MINUTE(A2)/30)+1)*30:若儲存格A2時間中分的數值為20~29,則結果傳回30,如果數值為50~59,則結果傳回60。

將以上二式代入 TIME 函數的時和分參數中,若分代入60,則會自動進位到時。

2015年1月29日 星期四

Excel-給予水平和垂直的條件查詢表格陣列中的值(INDEX,MATCH,OFFSET)

下圖中是 Excel 的一般資料表,給予二個條件,其實就是水平和垂直的項目,二項都符合者所得到(十字交叉處)的結果,該如何用公式自動求得?這其實就是查表的概念。

【公式設計】

以下使用三種方式來執行查表的工作。

(1) 使用 INDEX 函數

儲存格F8:=INDEX(A1:F6,MATCH(B8,A2:A6,0)+1,MATCH(D8,B1:F1,0)+1)

先使用 MATCH 函數來取得儲存格B8在儲存格A2:A6中為第幾個,也用來取得儲存格D8在儲存格B1:F1中為第幾個。

再以 INDEX 函數來執行以水平和垂直交叉處取得結果。

(2) 使用 OFFSET 函數

儲存格F8:=OFFSET(A1,MATCH(B8,A2:A6,0),MATCH(D8,B1:F1,0))

(3) 使用 INDIRECT+ADDRESS 函數

儲存格F8:
=INDIRECT(ADDRESS(MATCH(B8,A2:A6,0)+1,MATCH(D8,B1:F1,0)+1))

試著比較(1)、(2)、(3)式,其中都是運用 MATCH 函數,都是將傳回值用來取得位於第幾欄/第幾列。其中的巧妙變化,留給你來練習。

2015年1月27日 星期二

Excel-串接連續的儲存格內容(PHONETIC,OFFSET,MATCH,COUNTIF)

在下圖左裡有一個 Excel 的資料表,有網友想要轉換成下圖右的形式,即將相同負責人的內容集合在一個儲存格裡,該如何處理呢?

 

【公式設計與解析】

觀察原始資料中,已經將相同負責人的資料集合在一起了,這樣就簡化了這個問題。

儲存格F2:=PHONETIC(OFFSET($B$1,MATCH(E2,$A$2:$A$22,0),,
COUNTIF($A$2:$A$22,E2),2))

MATCH(E2,$A$2:$A$22,0):找出儲存格E2的內容在儲存格A2:A22中第一個的位置。

COUNTIF($A$2:$A$22,E2),2)):找出儲存格E2的內容在儲存格A2:A22中共有幾個。

OFFSET($B$1,MATCH(E2,$A$2:$A$22,0),,COUNTIF($A$2:$A$22,E2),2)):
將以上二式的結果代入 OFFSET 函數中,找出符合儲存格E2的所有儲存格,本例為儲存格B2:C4。

最後透過 PHONETIC 函數將上式代入作為參數,其結果即可將多個儲存格內容串接在一起了。

註:這個公式並不適用於負責人是分散不連續的狀況。

2015年1月26日 星期一

Excel-找出一群數列中包含某些字的個數(SUBSTITUE,SUMPRODUCT,LEN)

網友問到:在 Excel 的工作表中有一個數列(參考下圖),如何計算數列中包含幾個特定字元(例如:05),或是數列中全部有幾個 0,該如何處理呢?

(1) 含「05」的個數

儲存格B2:=(LEN(A2)-LEN(SUBSTITUTE(A2,"05","")))/2

利用 SUBSTITUTE 函數將某個字串(本例為:05)轉換為空白,再使用 LEN 函數將轉換前後的字數相減、再除以2(因為05為兩個字),即為 05 的數量。

複製儲存格B2,貼至儲存格B2:B15。

(2) A2~A15有幾個0

儲存格C2:=SUMPRODUCT(LEN(A2:A15)*1)-SUMPRODUCT(LEN(
SUBSTITUTE(A2:A15,"0","")))

對照(1)的解法,使用 SUMPRODUCT 函數將A2:A15的字串長度予以加總,即為所求。

2015年1月24日 星期六

Excel-快速取得連續編號工作表的相同儲存格內容(INDIRECT,ROW)

網友問到:在 Excel 中有一個由多個名稱為連續編號命名的工作表,如何快速取得每一個工作表的相同儲存各內容?(參考下圖)

通常,要連結某一個工作表的某一個儲存格,其格式為:='工作表'!儲存格

例如:='1'!A1,此為取得名為「1」的工作表之儲存格A1。

但是如果需要連結很多個連續編號的工作表,公式將需要一個一個的編寫,很不方便。

透過 INDIRECT 函數和 ROW 函數,即可快速完成。

儲存格C2:=INDIRECT("'" & ROW(1:1) & "'!A1")

使用 ROW(1:1)=1,當向下複製公式時會產生 ROW(2:2)=2、ROW(3:3)=3、...。

使用 INDIRECT 函數將符合「'工作表'!儲存格」格式的字串轉換為真實位址。

複製儲存格C2,貼至儲存格C2:C31。

Excel-為電話號碼加上+886

網友問到:在 Excel 的工作表中輸入一些數字(例如電話號碼),可能會遇到的一些問題,例如:

●第一個字若為0,可能會被 Excel 自動消去,輸入0930123456變為930123456。

●想要自動為電話加上「-」,輸入0930123456變為0930-123456

●改變電話格式為電話加上「()」,輸入0227481234變為(02)27481234

●想要自動為電話加上「+886」,輸入0930123456變為+886930123456。

●...

以為電話加上「+886」當例子,通常你需要使用設定儲存格的數值格式,透過自訂格式輸入「"+886"@」,即可在輸入一個電話數字後,自動轉換為+886起始的電話,而原來要儲存的電話內容並不會改變,因為只是顯示的格式改變而已。

這個結果會讓你的電話號碼被視為「文字」,若是改成格式:"+886"##########,則同樣的電話號碼輸入後會被視為「數字」。

若是你想要在輸入10碼的手機電話:0930123456,想要顯示+886930123456

則可以自訂格式:"+886"#########

手機電話有10碼,但上式的「#」只有9個,這樣第1碼的0就不會顯示了。

其他自訂格式,例如:

0###-######:將電話號碼輸入的第一個0被Excel省略,再以顯示格式方式重現。而第4和第5碼的中間會自動加上「-」。

(0#)####-####:將電話加上「(、)、-」。

Excel-如何記憶表格中垂直、水平是欄(column)還是列(row)?

在使用電腦建立文件,不管是 Word、Excel、PowerPoint 軟體,表格是高頻率使用的物件。在教學歷程中發現,常有人搞不清楚在表格中垂直方向、水平方向是欄(column)還是列(row)?

最近有人提及十幾年前我是如何教他們的:參考下圖,看中文字凸顯的一筆劃就知誰是垂直、誰是水平了!這樣你也記得下來嗎?

在 Excel 中位址以「欄名列號」來表示,所以稱A欄、B欄、...,稱第1列、第2列、...。

2015年1月23日 星期五

Excel-萬年月曆(WEEKDAY,DAY,DATE)

有網友問到想要在 Excel 中製如下圖的萬年月曆,可以使用微調按鈕來調整西元年和月份,即可產生各年各月的月曆表,該如何處理呢?

因為要使用「微調按鈕」,所以你的 Excel 必須先啟動「開發人員」功能表。

選取[開發人員/控制項]中的[插入]選單中的「微調按鈕」。

在工作表中建立一個微調按鈕來調整年的數字,並在[控制項格式]對話框中的[控制]標籤下設定:

最小值/最大值:設定要調整的年份,本例為1968/2068。

遞增值:1,即每按一下會增/減 1 年。

儲存格連結:選取儲存格A1。

相同做法來設定月份的微調按鈕,並設定:

最小值/最大值:設定要調整的月份,本例為1/12068。

遞增值:1,即每按一下會增/減 1月。

儲存格連結:選取儲存格G1。

儲存格A3:=DAY(DATE($A$1,$G$1,1)-(WEEKDAY(DATE($A$1,$G$1,1),1)-1)+
COLUMN(A:A)-1+(ROW(1:1)-1)*7)

DATE($A$1,$G$1,1):找出指定年和月之當月第1天的代表數值。(本例為42005)

WEEKDAY(DATE($A$1,$G$1,1),1)-1:指定WEEKDAY傳回值為1~7代表星期日到星期六。該式的傳回值代表指定年和月之當月第1天在一週7天的那個位置。(本例為4,表示2015/1/1在第一週的第五個)

DATE($A$1,$G$1,1)-(WEEKDAY(DATE($A$1,$G$1,1),1)-1):計算指定年和月之第1週的第1天的日期。

COLUMN(A:A)-1+(ROW(1:1)-1)*7):用於調整公式向右/向下複製時日期的增加。(往右增加1天,往下增加7天)

最後,再利用 DAY 函數取出第一個日期的日數值。

複製儲存格A3,貼至儲存格A3:G8。

Excel-將A~Z對應到1~26(MATCH,FIND,SEARCH,VLOOKUP)

有網友問到:在 Excel 中常會用到要將英文字母 A~Z 對應到數字 1~26,該如何設計公式呢?這的確是個常用到的狀況,例如:身份證的第一碼是英文字,常在信用卡繳費時需要做這樣的轉換。(參考下圖)

 

【公式設計】

以下提供四種方式來討論:

(1) 使用VLOOKUP函數

儲存格B2:=VLOOKUP(A2,$D$2:$E$27,2,FALSE)

如果你已經建立了英文字母和數字的對照表,則透過VLOOKUP函數可以利用查表方式來求對字母和數字的對照。使用參數 FALSE,表示查詢的字必須在表中完全相符才可以。

(2) 使用MATCH函數

儲存格B2:=MATCH(A2,{"A","B","C","D","E","F","G","H","I","J","K","L","M","N",
"O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)

將所有英文字母「依順序」將每個字元字串編成常數陣列,放在MATCH函數中來查詢。

(3) 使用FIND函數

儲存格B2:=FIND(A2,"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

將所有英文字母「依順序」將每個字元字串編成一個字串,放在FIND函數中來查詢,傳回的數值即為對照的數字。

(4) 使用SEARCH函數

儲存格B2:=SEARCH(A2,"ABCDEFGHIJKLMNOPQRSTUVWXYZ")

將所有英文字母「依順序」將每個字元字串編成一個字串,放在SEARCH函數中來查詢,傳回的數值即為對照的數字。

2015年1月22日 星期四

Excel-多條件判斷小數是否進位(INT,MOD)

有網友問到一個 Excel 計算上的問題:如何滿足特定雙條件之四捨五入設計,當條件滿足資料個位數是偶數時,且小數點等於0.500,則無條件捨去至個位數,如果不滿足以上二個條件,則四捨五入到個位數。參考下圖。

(1) 判斷個位數是否為偶數

儲存格C2:=IF(MOD(INT(A2),2)=0,"V","")

INT(A2):將儲存格A2的小數部分去除。

MOD(INT(A2),2)=0:判斷去除小數部分的數值是否為偶數,將儲存格A2除以2,判斷餘數是否為0,為0者即為偶數。

(2) 判斷小數是否為0.5

儲存格D2:=IF(INT(A2)+0.5=A2,"V","")

INT(A2)+0.5=A2:利用INT(A2)將小數部分去除,若儲存格A2整數部分+0.5和儲存格A2內容相等,則表示儲存格A2的小數部分為0.5。

(3)雙條件成立則無條件捨去;否則四捨五入

儲存格G2:=IF((MOD(INT(A2),2)=0)*(INT(A2)+0.5=A2),INT(A2),ROUND(A2,0))

(MOD(INT(A2),2)=0)*(INT(A2)+0.5=A2):使用「*」運算子,相當於執行邏輯 AND 的運算。

INT(A2):儲存格A2無條件捨去至個位數。

ROUND(A2,0):儲存格A2四捨五入至個位數。

WhatsApp網頁版可以使用手機QR Code掃描使用了

我有在手機中使用 WhatsApp 這個 App,使用時個人最大的問題是在於自己在手機上的輸入文字速度太慢(嚴格講應該是寫太慢了),常常讓對方等的不耐煩,我都還沒回完,對方就又傳送了新的訊息,實在有些尷尬!

而在最新的 Whatsapp App 的更新中有一個重大的新功能-WhatsApp 網頁版可以使用手機掃描 QR Code 來登入使用了。這個功能至少讓我有在使用電腦時,就可以避免上述的煩腦,解決一部分的問題。

使用 WhatsApp 的網頁版要注意的資安問題和 Line 的電腦版一樣,都是帳號/密碼的控管問題,不過二者都不約而同使用變動的 QR Code 配合手機來配對使用,可以避免帳密被盜錄的問題。不過還是提醒一下,任何使用方式都存在資安的風險,網路沒有絶對的安全,或是說絶對的不安全。以下使用 Android 手機來示範。

網頁版網址:https://web.whatsapp.com/

在瀏覽器的主畫面中有一個會不斷變換的 QR Code:

要登入使用必須開啟手機 WhatsApp 程式,在選單中選取「WhatsApp 網頁版」,然後將其提供的 QR Code 掃描器對著電腦螢幕的 QR Code 掃描。

 

正確掃描後會自動登入:

其他使用方式大致和手機相同,所有內容也會和手機同步。並且可以同時使用網頁版和手機版,二者會即時顯示相同內容:

提醒:使用完記得要登出。

2015年1月20日 星期二

Excel-轉換學號為Email位址(MID,LEN)

在 Excel 中的一個資料表,含有類似學號的數字列表,有網友想要將其第一碼置換為「s」,最後一碼去除後,再串接「@upup.edu.tw」,看起來像是一個 Email 位址的字串,該如何處理呢?(參考下圖)

儲存格C2:="s"&MID(A2,2,LEN(A2)-2)&"@upup.edu.tw"

LEN(A2):計算儲存格A2的內容共有幾個字元。

MID(A2,2,LEN(A2)-2):使用 MID 函數由第2個字起取全部字元再減2個字。

串接字串使用「&」運算元。

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

在 Excel 中有一個二欄構成的資料表(如下圖左),有網友想要將其轉換為下圖右的二種呈現方式,該如何處理?

在 Excel 中其實有提供「轉置」的功能,也就是複製原始資料後,在貼上資料時選擇「轉置」即可。但是手動處理,對於資料筆數多時,形成很大的困擾,所以來試試如何建構公式來自動化處理。

 

【公式設計】

(1) 二欄直接轉置

儲存格D1:=OFFSET($A$1,(ROW(1:1)-1)/2*6+COLUMN(A:A)-1,,)

(ROW(1:1)-1)/2*6:因為A欄的資料會每間隔二列出現,所以在向下複製公式後得到以下結果。

第 1 列時公式為 ROW(1:1)-1/2*6,時傳回 0;

第 3 列時公式為 ROW(3:3)-1/2*6,時傳回 6;

第 5 列時公式為 ROW(5:5)-1/2*6,時傳回 12;

依此類推,取得儲存格B1、儲存格B7、儲存格B13、… 的資料。

COLUMN(A:A)-1:當向右複製公式時,結果如下。

在D欄公式為COLUMN(A:A)-1,傳回 0;

在E欄公式為COLUMN(B:B)-1,傳回 1;

在F欄公式為COLUMN(C:C)-1,傳回 2;

依此類推,取得儲存格B1:B6的資料。

透過 OFFSET 函數置入以上的參數值,即可取得對應的儲存格內容。

同理:

儲存格D2:=OFFSET($B$1,(ROW(1:1)-1)/2*6+COLUMN(A:A)-1,,)

複製儲存格D1:I2,貼至儲存格D1:I8。

 

(2)取出資料變項轉置

也可以簡化(1)的資料呈現方式,將標題固定於一列。

儲存格D12:=OFFSET($B$1,(ROW(1:1)-1)*6+COLUMN(A:A)-1,,)

公式原理和(1)雷同。

複製儲存格D12:I12,貼至儲存格D12:I15。

2015年1月19日 星期一

Excel-複製圖表格式免去重做的麻煩

在 Excel 中製作圖表是屬於操作方便的,但是如果重覆要製作多個類似的圖表,似乎在人性上一般人較無法接受,有沒有快一點的方法在類以的資料來源中,建立相似的圖表呢?

例如,在下圖中有一個取自於資料表製成的直條圖圖表:

經由多個步驟的操作之後,終於完成了以下的圖表格式:

image

要如何快速將另一個資料表建構的圖表也設定成和上圖一樣的圖表格式呢?

在 Excel 2013 中,方法其實很簡單:

1. 選取已經設定好格式的圖表。

2. 按一下 Ctrl + C 鍵。(即複製這個圖表)

3. 再選取另一個未設定格式的圖表。

4. 選取[常用/剪貼簿/貼上]選單中的「選擇性貼上」。

5. 選取「格式」選項,按一下[確定]按鈕。

這個未設定格式的圖表,隨即變化成和第一個圖表完全相同的格式(內容未被變動)。

image

2015年1月18日 星期日

Excel-只挑最佳的幾個成績計算排名(LARGE,陣列公式)

網友又問到之前時常被問到的類似問題:如何在 Excel 的資料表中將每個人有 12 個成績中挑出最佳幾個來平均?

以下圖為例:每個人有 12 個成績,如果要挑出最佳的前 8 個成績來平均,該如何處理?

【設計公式】

儲存格N1:{=AVERAGE(LARGE(B2:M2,ROW($1:$8)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格N1,往下各列貼上。

ROW($1:$8):在陣列公式中代表陣列 { 1, 2, 3, 4, 5, 6, 7, 8 }。如果你要取幾個來平均,只要改變 8 的數值即可。

LARGE(B2:M2,ROW($1:$8)):在陣列公式中代表取出儲存格B2:M2中的前 8 大的數值。

最後再以 AVERAGE 函數加以平均,即為所求。

如果你不想使用陣列公式,而改用 SUMPRODUCT 函數,則公式如下:

儲存格N2:=SUMPRODUCT(((B2:M2)>=LARGE(B2:M2,8))*B2:M2)/
SUMPRODUCT(((B2:M2)>=LARGE(B2:M2,8))*1)

注意到這些成績中,有幾筆是第 8 大的成績有重覆的現象。如果使用這個公式,則會取到 9 筆成績來計算平均。而使用陣列公式時,不會有這個現象,請讀者自行斟酌來使用。

桃園也有落羽松大道

去年到今年的冬季,「落羽松」似乎流行起來了,整理手機的照片時,發現去年12月中的幾張桃園落羽松大道的照片。回憶一個月前,已是過了最茂密的時期,但是還是在日正當中時,吸引了一些人在午間前來拍照。

落羽松大道的位置就在台3乙線往石門水庫方向,在往台灣菸酒公司的岔路上彎進去。遊客都直接把車子停在兩旁的白線上,盡量不要妨礙車輛通行。

 

就在這接近500公尺的小徑上,路的兩旁還有幾戶人家和工廠,雖說是落羽松大道,但是看來像是隱藏版的秘境,用我那效果不佳的手機相機隨手拍來,無法凸顯它的美,但是留做紀念,提醒自己明年早一點,用好一點的相機再來拍吧!

好康東東