2010年12月29日 星期三

Excel-陣列的應用

在 Excel 中取得一個資料表,其中有多個天數的跑步記錄,如何可以快速求得累計的時間總和呢?並且要能分別呈現時、分、秒。

觀察E欄時發現,其儲存格內容的格式固定,即時間中的分和秒都出現在固定位置。

如果以公式:「VALUE(MID(B2,8,2))」,可以求得儲存格B2中分的數量。

如果以公式:「VALUE(MID(B2,11,2))」,可以求得儲存格B2中秒的數量。

因為MID函數求得的是文字(例如"17”),所以透過VALUE函數轉換成真正的數字(例如17)。

但是有這麼多個儲存格如何一次計算呢?公式改為:

如果以陣列公式:「{SUM(VALUE(MID(B2:B26,8,2)))}」,可以求得所有儲存格中分的數量總和。

如果以陣列公式:「{SUM(VALUE(MID(B2:B26,11,2)))}」,可以求得所有儲存格中秒的數量總和。

接著要如何轉換成時、分、秒的數量呢?

你大概會想要將時間全部換算成秒,再設計公式來求時、分、秒吧?

以下有個巧妙的方式:

儲存格E2:{=HOUR(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

儲存格E3:{=MINUTE(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

儲存格E4:{=SECOND(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

因為TIME函數可以將大於59的分或秒數,自動調整在60以下,所以公式:

TIME(0,分的總和,秒的總和)

其中時的部分以0填入,Excel會自動將分和秒調整成60秒以下。

再透過HOUR、MINUTE和SECOND函數分別取得時、分、秒。

如此,完全不用再寫公式來計算時、分和秒的數量。

很有趣又有效率吧!

2010年12月26日 星期日

Excel-標記三科以上不及格者

在 Excel 中處理成績表時,如果要在多個成績中標記數科不及格者,該如何處理?(如下圖)

例如:在五個成績中要標記「三個以上」不及格者。提供以下數種方式來練習:

(1)在IF函數中使用OR觀念

儲存格G2:=IF((B2<60)+(C2<60)+(D2<60)+(E2<60)+(F2<60)>=3,"V","")

其中的「+」為OR運算,並且會將True/Fasle轉換成1/0。

(2)使用COUNTIF函數

儲存格G2:=IF(COUNTIF(B2:F2,"<60")>=3,"V","")

COUNTIF函數即為篩選符合條件的個數。

(3)使用SUMPRODUCT函數

儲存格G2:=IF(SUMPRODUCT(--(B2:F2<60))>=3,"V","")

公式中的「--」也是將True/Fasle轉換成1/0來運算。

(4)使用陣列公式

儲存格G2:{=IF(SUM(IF(B2:F2<60,1,0))>=3,"V","")}

輸入完成時,要按Ctrl+Shift+Enter鍵。

其觀念為將合於條件者設定1,否則設定0,再判斷加總後的結果。

最後,複製儲存格G2,往下貼至G欄各個儲存格。

2010年12月25日 星期六

Excel-解決從外部貼上資料時,前幾個0消失的問題(二)

上一篇文章(http://isvincent.blogspot.com/2010/12/excel-0.html),提到如何解決從外部貼上資料時,前幾個0消失的問題。如果已經將資料複製到資料表中發現儲存格中前幾個0消失了(如下圖B欄),該如何解決呢?

image02

提供以下二個方法:(假設B欄中的數字共有六碼)

(一)設定數值格式

將B欄的數值格式,自訂為:「000000」。

(二)使用公式

新增一欄,輸入公式:「=RIGHT("000000"&B1,6)」

意義為將B欄內容之前加上6個0的字串,再由右邊取出6碼,便可將原先不足6碼的數字,補上不夠的0。

Excel-解決從外部貼上資料時,前幾個0消失的問題(一)

在使用 Excel 時,常常發生來源資料中有些欄位的前幾個數字是「0」,當複製後貼到 Excel 的工作表時,前幾個0會消失的問題,那是因為 Excel 將它們視為數字,所以在儲存格中自動消失了。

例如:來源資料

複製後貼至 Excel 工作表,儲存格中前幾個0都不見了:

如果想要克服這個問題,可以使用另一種方式,或許比較麻煩,但是不會出錯。

例如:在 Excel 中開啟來源資料檔案時,選取這個TXT文字檔,Excel 會自動開啟「匯入字串精靈」。

第一個步驟是選取要剖析資料的方式,本例可以選「分隔符號」(因為來源檔案是以Tab鍵分隔)。

第二個步驟要選取分隔符號,例如:「Tab鍵」。

第三步驟最重要,先選取數字的欄位,接著在[欄位的資料格式]中選取「文字」,也就是說要將數字視為文字。(如果你選取「一般」選項,則可能該欄位會被視為數字。)

按下[完成]按鈕後,可以發現數字欄位上的0,都被保留了。

如果你不想這個欄位上的三角形綠色提示顯示出來,則選取[忽略錯誤]選項即可消除。

提醒你,別忘了要將檔案另存新檔為 Excel 的格式,因為此時該檔案仍是TXT格式。(存檔時,Excel會出現提示訊息。)

2010年12月19日 星期日

Google搜尋新增外文網頁翻譯版

Google搜尋最近新增了「外文網頁翻譯版」功能,當你輸入一個關鍵字後,在視窗左側的功能區中會顯示「外文網頁翻譯版」。

點選這個功能後,在每個搜尋到的標題下會顯示原文的語言。

當你點選這個標題後,會切換至「Google 翻譯」,並且已經轉換成你正在使用的語言。這種貼心的安排,可以快速看到世界各國的內容。所以你可以透過點選「新增語言」來加入你想要看的國家語言,例如:日文、韓文、阿拉伯文…,這樣你在上網時可以更有國際觀了。

Excel-交叉比對資料的最大值

在 Excel 中取得一個資料表,若要摘要該資料表的各種代碼之個數和最大值該如何處理?

image01

(1) 計算個數

儲存格I2:{=COUNT(IF($A$2:$A$20=$H2,B$2:B$20))}

此為陣列公式,輸入完成後要按 Ctrl+Shift+Enter 鍵。複製儲存格I2到儲存格I2:M6。

此公式是利用判斷合於代碼的陣列所對應的甲、乙、丙、丁、戊各項的陣列,再從陣列中計算個數。

(2) 計算最大值

儲存格I9:{=MAX(IF($A$2:$A$20=$H9,B$2:B$20))}

此為陣列公式,輸入完成後要按 Ctrl+Shift+Enter 鍵。複製儲存格I9到儲存格I9:M13。

此公式在當各個代碼中若個數為0時,其最大值會顯示0,顯然不合理。

修改如下:

儲存格I16:{=IF(COUNT(IF($A$2:$A$20=$H2,B$2:B$20))<>0,MAX(IF($A$2:$A$20=$H9,B$2:B$20)),"")}

此為陣列公式,輸入完成後要按 Ctrl+Shift+Enter 鍵。複製儲存格I16到儲存格I16:M20。

此公式判斷若各個代碼的個數為0時,顯示空白內容,否則再顯示最大值。

2010年12月18日 星期六

Excel-為何公式沒有自動更新運算結果

最近有朋友問到,他的 Excel 好像「壞掉了!」,因為建立好的一堆公式運算,只要來源儲存格的資料改變,公式都應自動顯示新的運算結果,但是最近不會重新運算,都還是顯示原來結果。

經過一番檢視其 Excel 視窗環境,發現在其 Excel 2010 的[公式]功能表下之[重算選項]功能,其中原本勾選「自動」選項,被修改為「手動」,難怪不會自動運算了。只要重新選取[自動]選項,即可解決這個問題。

image01

因為這個切換功能原本是在 Excel 的選項中,在[公式]項下設定。現在為了方便將其在功能表中可以快選切換,但不知情的人不小心點選了,就容易出現和自己預期不一樣的運算結果。

Excel-繪製方便改變內容的流程圖

在 Office 的各種軟體中,都可以方便的使用流程圖繪圖工具來製作流程圖,但是如果要修改內容就必須點選物件後再進行修改,這次要以不同方式來設計。

當你將流程圖的物件放好之後,還不用輸入內容文字:

1. 選取一個物件,例如起始的的第一個物件。

2. 在資料編輯列中輸入「=」,再點選儲存格B2,資料編輯列中會顯示「=$B$2」。

3. 依相同方式將各個流程圖物件都對應到一個儲存格。

之後如果要改變流程圖物件內文字,只要由修改對應的儲存格內容即可。即使在流程圖中新增物件,也是很方便就可以增加儲存格中的文字。

如果你遇到流程圖中的文字要分二行顯示時,你可以有二種選擇,例如:

(1) 在儲存格中,輸入第一行文字,按一下 Alt+Enter 鍵,輸入第二行文字。

(2) 在儲存格中輸入文字串接公式,例如:「="第一行文字" & CHAR(10) & "第二行文字"」。

其中CHAR(10)為斷行之用。

Excel-公式中使用R1C1 位址參照樣式

在 Excel 中大家習慣以欄名列號(例如:A1、$A$1)來表示位址,而 Excel 也可以使用R1C1 位址參照樣式表示法。

Excel 在 R1C1 樣式中使用 R 加上列號碼和 C 加上欄號碼,來表示儲存格的位置。如果使用[]表示要使用相對參照,否則使用絶對參照。

參照

意義

R[-2]C

相對參照至同一欄上二列的儲存格

R[2]C[2]

相對參照下二列右二欄的儲存格

R2C2

絕對儲存格參照至第二欄中第二列的儲存格

R[-1]

相對參照使用中儲存格上方的整列

R

絕對參照目前列

 

參考以下的範例:

公式中使用INDIRECT函數中必須使用FALSE參數,才可使用R1C1表示法。

而儲存格A12中的 R5C:R[-8]C 位址,其-8的意義為以儲存格A12往上8列(儲存格A4)。

如果要在每一欄的第一列中顯示該欄的所有數字總和,運用R1C1表示法來計算:

儲存格J1:=SUM(INDIRECT("R2C:R[-1]C",FALSE))

複製儲存格J1到儲存格J1:N1。

以儲存格J1為例,其公式內容相當於「=SUM($J$2:$J$1048576)」 (以Excel 2010為例)

儲存格J1中的公式使用R[-1]為相對位址,而儲存格J1已位於第1列,所以R[-1]為該欄的最後一列。

所以此公式可以計算整欄的總和。

如果複製儲存格J1到其他儲存格,公式內容都是一樣的。

2010年12月16日 星期四

Excel-選取儲存格範圍

在 Excel 的工作表中建立了一個資料表,如何可以選取儲存格範圍呢?使用滑鼠來選取,對於範圍較大的資料表較不方便,可以使用快速鍵等方式,例如:

(A)如果工作表中只有一個資料表,則將滑鼠移到表格中任一個儲存格位置,使用快速鍵Ctrl+A。

但是如果工作表中超過一個資料表,則Ctrl+A鍵會選取整個工作表。

(B)將滑鼠移到資料表中任一個儲存格位置,使用快速鍵Ctrl+*。

(C)如果想要選取某個儲存格範圍(尤其是範圍較大時)

1. 按一下Ctrl+G鍵,顯示[到]對話框。

2. 在[參照位址]中輸入想要的儲存格範圍。

3. 按一下[確定]按鈕,即可選取該範圍。

(D)如果想要選取某個儲存格範圍(尤其是範圍較大時)

1. 在[名稱方塊]中輸入儲存格範圍

2. 按一下Enter鍵,即可選取該範圍。

2010年12月15日 星期三

Google搜尋新增依閱讀難易顯示結果

Google推出新的搜尋功能可以依閱讀難易顯示結果,不過現在只能在英文版中使用。

請連到Google英文版(http://www.google.com/ncr),按一下[Advanced search](進階搜尋)。

輸入搜尋的關鍵字後,選取[Reading level]中的一項難易程度。

然後按一下[Advanced search]按鈕,可以進行搜尋。

其顯示結果會標示閱讀難易程度,並且告訴你各類難易度佔的百分比。以 Advanced 來看,提供的搜尋結果,果然比較多和學術性有關。你可以直接點選 Basic 或 Intermediate 執行不同難易度的搜尋。

比較一下輸入中文和英文關鍵字的搜尋結果,其不同難易度的文章比例並不一樣。

image03

這個功能應該是難在誰來定義難易度,而且同一內容的難易度本來就因人而異,且看Google將來如何處理這些問題。

Excel-挑出單月的資料

如果在 Excel 中取得一個資料表,其中含有日期的資料(下圖左),假設要將不同月份的資料取出(下圖右),該如何處理?假設月份輸入在儲存格F1。

儲存格G2:{=IF(ISERROR(VLOOKUP(SMALL(IF(MONTH($B$2:$B$78)=$F$1,$A$2:$A$78),ROW(1:1)),$A$2:$E$78,COLUMN(B:B))),"",VLOOKUP(SMALL(IF(MONTH($B$2:$B$78)=$F$1,$A$2:$A$78),ROW(1:1)),$A$2:$E$78,COLUMN(B:B)))}

這是陣列公式,輸入完成要按 Ctrl+Alt+Enter 鍵。複製儲存格G2到儲存格G2:J17。

公式的意義如下:

SMALL(IF(MONTH($B$2:$B$78)=$F$1,$A$2:$A$78),ROW(1:1))

取出B欄中的日期之月份和F1儲存格相同的A欄陣列(編號),利用ROW(1:1)=1,藉助SMALL函數求出編號最小者。往下複製時ROW(1:1)->ROW(2:2)->ROW(3:3),表示依序取出第1小者、第2小者、第3小者…。

VLOOKUP(SMALL(IF(MONTH($B$2:$B$78)=$F$1,$A$2:$A$78),ROW(1:1)),$A$2:$E$78,COLUMN(B:B)

利用求得的編號再由VLOOKUP函數查表得到日期、姓名、部門、職務等各欄。

最後使用ISERROR函數來避免查不得資料時不顯示錯誤訊息。

只要改變儲存格F1即可顯示不同月份的資料。

關於各個函數的詳細說明,請參閱微軟網站:

SMALL函數:http://office.microsoft.com/zh-tw/excel-help/HP010342904.aspx

VLOOKUP函數:http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

ISERROR函數:http://office.microsoft.com/zh-tw/excel-help/HP010342632.aspx

2010年12月14日 星期二

Excel-在統計圖表上使用自訂的圖片

在 Excel 中有個資料表已製成統計圖,如果想要將圖表上的長條圖以自訂的圖片表示,該如何做呢?

假設你已經將圖示置於工作表中了:

1.複製一張圖片(按Ctrl+C鍵)。

2.選取圖表中的一個數列(該數列的多個項目會一併被選取)。

3.按一下Ctrl+V(貼上),即可以改變該數列的長條圖為該圖片(下圖共有三個)。

你也可以只改變數列中的單一個項目:

1.複製一張圖片。

2.選取圖表中的一個數列,再點選其中一個項目。

3.按一下Ctrl+V(貼上),即可以改變長條圖為該圖片。

2010年12月12日 星期日

Excel-不受資料增加影響的排序公式

在 Excel 的工作表中如果要執行排序工作,通常都使用RANK函數。例如:

儲存格E2:=RANK(D2,$D$2:$D$14)

再將儲存格E2複製到儲存格E2:E14。

因為公式中的儲存格範圍$D$2:$D$14使用絶對位址,所以如果新增資料列時,整個排序欄位的公式全都要更改,否則會出現E欄中的錯誤(參考下圖)。

如何能做到即使增加多列的資料,也可以複製相同公式而不用更改全部的排序公式呢?

如果你將公式中的儲存格範圍改用OFFSET函數來定址,而使用COUNT函數來統計D欄中有數值料的全部列數。

儲存格F2:=RANK(D2,OFFSET($D$2,,,COUNT(D:D),))

在F欄中,往下複製儲存格F2。即使新增多列資料,也可只要將F欄中的公式往下複製即可,完全不用再修改公式。

關於OFFSET函數的詳細說明,請參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

Excel-核對三個欄位是否相同

在 Excel 的工作表中,如果要核對三個欄位中的數字是否完全相同,該如何做呢?

試著列舉以下數種計算方式來練習:

儲存格D2:=IF(A2=B2,IF(B2=C2,"V","X"),"X")

儲存格D3:=IF((A3=B3)*(B3=C3),"V","X")

儲存格D4:=IF(AND(A4=B4,B4=C4),"V","X")

儲存格D5:=IF(COUNTIF(A5:C5,A5)=3,"V","X")

儲存格D6:=IF(EXACT(A6,B6)*EXACT(A6,C6),"V","X")

儲存格D7:=IF(A7=AVERAGE(A7:C7),"V","X")

儲存格D8:=IF(A8=SUM(A8:C8)/COUNT(A8:C8),"V","X")

2010年12月10日 星期五

Excel-陣列公式的AND和OR運算

在 Excel 中的有一個資料表(如下圖),若要執行以下的運算,該如何處理?

(1) F欄要計算當A欄大於50 B欄大於50時,計算C欄、D欄、E欄的和。

(2) G欄要計算當A欄大於50 B欄大於50時,計算C欄、D欄、E欄的和。

透過陣列公式加入AND和OR運算即可完成。

儲存格F2:{=SUM(IF((A2>50)*(B2>50),C2:E2))}

儲存格G2:{=SUM(IF((A2>50)+(B2>50),C2:E2))}

以上為陣列公式,輸入完成必須按Ctrl+Shift+Enter鍵。

其中公式中的「*」執行AND運算,「+」執行OR運算。

複製儲存格F2:G2到儲存格F2:G19。

關於AND運算,還有另外的陣式公式算法:

儲存格F2:{=SUM(IF(A2>50,IF(B2>50,C2:E2)))}

儲存格F2:{=SUM(IF(AND(A2>50,B2>50),C2:E2))}

關於OR運算,還有另一種陣式公式的算法:

儲存格G2:{=SUM(IF(OR(A2>50,B2>50),C2:E2))}

2010年12月7日 星期二

Excel-使用TEXT函數自訂顯示格式

在 Excel 中若要在一個儲存格中顯示文字+數字的結果,則必須藉助 & 符號。例如:

儲存格E2:="共" & SUM(B2:D2) & "人"

會顯示「共1819人」,但是如果要對數字部分做顯示格式的設定該如何做呢?如何做到以下的效果呢?

只要藉助TEXT函數即可,詳細的函數說明請參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010342952.aspx

說明:

儲存格E2:="共" & TEXT(SUM(B2:D2),"#,###") & "人"

顯示千分號符號,但數字不會對齊。

儲存格E4:="共" & TEXT(SUM(B4:D4),"?,???") & "人"

顯示千分號符號,不足1000的數字會保留千位數。

儲存格E6:="共" & TEXT(SUM(B6:D6),"0,000") & "人"

顯示千分號符號,且數字會補0。

儲存格E10:="共" & TEXT(SUM(B12:D12),"#,###") & "元"

儲存格E12:="共" & TEXT(SUM(B12:D12),"?,???.0") & "元"

小數點以下1位會自動顯示一個0。

儲存格E14:="約" & TEXT(SUM(B14:D14),"#,") & "仟元"

取1000的近似值,數值會四捨五入至千位數。

2010年12月6日 星期一

台中+豐原二日遊

藉著小孩的校慶運動會之補假,大人也請假帶領他們離開台北,到中部二日遊。星期日早上八點,準備就緒後立即出發。車子感覺沒有很多,大概是上週六很多人返鄉投票,所以這週在高速公路上的車才會少吧!

到了泰安休息站,上個廁所休息5分鐘,順道帶小孩參觀在販賣部二樓參觀了獨角仙和鍬形蟲的標本。

 

今天的第一站是要讓小朋友騎腳踏車,所以選定了石岡大壩附近為起點,這條是東豐自行車綠廊。這條東豐鐵路綠色走廊是由廢棄鐵道改建為自行車道的腳踏車專用道,緊鄰大甲溪,全長約十二公里。車道兩旁綠化林,使自行車道儼然成為「綠色的大甲溪」。

圖片來源:http://tt.taichung.gov.tw/county/cht/item_bicy.jsp

找了一個可以免費停車的租借腳踏車店,租了四部腳踏車,立刻上路。腳踏車道上的車可說是密密麻麻,大人、小孩和老人都有,有的靠兩雙腳騎,有的靠電動馬達前進,在沒有什麼交通規則的狀況下,忽快忽慢、忽左忽右,在亂七八糟中一直往前進,只因為人實在太多了。

不過車道上被綠蔭覆蓋,有上坡也有下坡,兩邊的風景倒也不錯。你會經過旅客服務中心,可以下車休息和搜集些資料。

  DSC08697_調整大小

DSC08695_調整大小  DSC08698_調整大小 

騎到了「情人木橋」時,我們岔出了車道,欣賞了木橋後,由此折返。

   

往回騎到石岡大壩時又切去看大壩。一旁的小販賣著炸杏鮑菇,這是小孩的最愛,又買了米腸包香腸,口味也是不錯。小孩又有了動力可以往下騎了。

 

東豐自行車綠廊騎到底會接到「后豐鐵馬道」。

 

后豐鐵馬道以后里馬場為起點,貫穿原舊山線 9 號隧道,再經花樑鋼橋,全長共四點五公里,最後在國道四號橋下,與東豐自行車綠廊交接,形成總長約18 公里的鐵馬道。 后豐鐵馬道中有跨越大甲溪的花樑鋼橋,並且有改建鐵道的鐵馬隧道,感覺非常特殊。

圖片來源:http://tt.taichung.gov.tw/county/cht/item_bicy.jsp

image02

后豐鐵馬道和東豐鐵路綠色走廊的地面材質並不一樣,后豐鐵馬道是一般的柏油路面,所以車子的震動比較大,當然也比較痛囉!

花樑鋼橋和九號隧道很長,小孩說怎麼都騎不到盡頭呢?隧道中很涼爽,但是路面很不好,振動較大。當你看到出口的亮光時會很興奮哦!

DSC08722_調整大小   

   

騎過九號隧道,稍事休息後,由此折返,回到租車起點。回家後用Google量測了距離,大約騎了9公里X2,也就是來回共18公里,不知是否準確?不過卻是讓小孩興奮的度過運動的二小時。

接著走台三線要到豐原市區的廟東小吃。這個廟指的是慈濟宮,附近有許多停車場,但都要稍等一下才有車位。

要吃「清水排骨湯」,但是得抽號碼牌,要等十個號碼,所以先吃了「一口悶三角圓」,這二種食物連小孩都是贊不絕口、喜愛有加。因為人實在太多了,很多又要抽號等,所以沒辦法盡興享用。星期日的廟東小吃街,真是人潮爆滿。建議要挑非假日來才能好好享用。

DSC08754_調整大小 

DSC08751_調整大小  DSC08752_調整大小 

離開了豐原,直接要到今天住宿的飯店-裕元花園酒店(http://www.windsortaiwan.com/),位於中港交流道下來不遠。

圖片來源:http://www.windsortaiwan.com/zhtw/html/about.htm

先前在網路上要預訂時沒有房間,所以改以電話預定,很容易就訂到了,但可能有貴一點點。

我們被安排在十八樓,它的這層樓只有九個房間,所以視野相當的好。這個飯店還算新,所以看起來很舒服呢!因為浴室有另一個電視,所以小孩喜歡在浴室中看卡通。

四點半左右,帶小孩到游泳池游泳,大人則在溫水的SPA池,讓水波好好的沖打肌肉,今天可是有認真的運動了。溫水的SPA池在戶外,可以遠眺台中市區,傍晚的涼風吹來,很舒服哦!因為今天的台中有28度吔!

DSC08761-2_調整大小  DSC08756_調整大小    DSC08774_調整大小

晚餐要到「逢甲夜市」。和前幾次的經驗一樣,人這麼多怎麼走動啊?這次的策略是買多一點不同種類的食物,四個人分食,每一種吃一點,很有趣哦!大家都吃的很飽!攤位很多,胃很小,就只能品嚐那幾樣了,況且人太多移動是寸步難行啊。吃的很飽,回到飯店,在一旁的麥當勞,買了口樂,解一下渴,實在滿足。

 

一早醒來,太陽很大,望望遠處,因為我們的房間是在角落,所以視野很大。吃完早餐,帶小孩在一樓的花園閒晃一下。

 

因為今天中午還得趕回學校上研習的課,所以十一點不到就回程囉!

檢視其他文章

好康東東