2010年6月30日 星期三

Excel-自動重新排序+只顯示第一個

在Excel中輸入一個資料表,如下圖左半部,班級是不規則順序輸入的。如果要自動轉換成右半部,變成依班級順序排列,而且只有班級名稱的第一筆才顯示,其餘班級名稱不顯示。

1.

儲存格G2:=SMALL($B$2:$B$24,ROW(1:1))

觀察因為班級全部由數字組成,所以利用SMALL函數,將其由小到大取出。

2.

儲存格H2:{=OFFSET($A$1,SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2)),,)}

這是陣列公式,輸入請按Ctrl+Shift+Enter鍵。目的是將SMALL函數所對應的班級來找出對應的編號。

其中「ROW(INDIRECT("$A$2:$A$24"))-1」,在陣列公式中會產生{1,2,3,…,23}的陣列內容。

其中「COUNTIF($G$2:G2,G2))」,找出某個班級名稱在「相同的」班級名稱上位於第幾個位置。

其中「SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2))」,在陣列公式中為找出指定的班級在第幾列。

透過「OFFSET($A$1,第幾列,,)」來找出編號號。

3.

儲存格I2:=VLOOKUP($H2,$A$1:$E$24,COLUMN(C2),FALSE)

複製儲存格I2到儲存格I2:K2。

使用VLOOKUP函數來根據編號找到姓名、檢定和級別欄位內容。

4.

然後將儲存格G2:K2複製到儲存格G2:K24。

5.

如何只顯示第一次出現的班級名稱,其餘則不顯示呢?可以利用設定格式化條件的方式來處理。

選取儲存格G3,設定公式的內容為:=COUNTIF($G$2:$G3,$G3)>1,並設定符合條件時,顯示和底色相同的文字色彩(當文字色彩和底色一樣時,將看不到文字。),這樣就可以把第一個以外的文字隱藏了。

Excel-隨機重新排序資料

在Excel中,如果要將一組依序排好的資料(例如:依序的班級編號)隨機重新排序,該如何處理呢?

因為要隨機排序,所以需要產生一組亂數:

儲存格A2:=INT(RAND()*100)+ROW()/1000

複製儲存格A2到儲存格A2:A17。

公式:INT(RAND()*100)產生1~99的整數亂數。

公式中加上ROW()/1000的用意,是因為之前的整數亂數可能重覆,而ROW()在每列中會產生不一樣的數字。

儲存格D2:=VLOOKUP(LARGE(A$2:A$17,ROW(1:1)),A$1:B$17,2,FALSE)

複製儲存格D2到儲存格DA2:D17。

此公式利用LARGE函數依序找出A欄中第1大、第2大、第3大…的亂數,再藉由VLOOKUP函數於儲存格A$1:B$17中,找尋對應的班級。

每按一次F9鍵,班級就會重新排列。

2010年6月29日 星期二

Excel-限制同一欄中輸入唯一值

在Excel中如果要輸入一個報名表,希望同一欄中的資料是唯一值(不可重覆),該如何處理?

例如在運動會一百公尺報名表(每班一名),希望班級欄位不要重覆,可以藉助資料驗證來把關。

儲存格A3:=IF(B4<>"",A3+1,"")

將儲存格A3往下複製到多個儲存格。

該公式的作用是,如果B欄有輸入資料時,序號會自動加1。

 

接著,選取儲存格B3,設定其資料驗證:

儲存格內允許:自訂

公式:=COUNTIF($B:$B,B3)=1

注意:B3要使用相對位址,因為之後要複製到B欄的其他儲存格。

將錯誤提醒也一併設定,如下:

如果在B欄中輸入了同一欄重覆的內容,則會顯示以下的對話框。

使用這個方式,有助於減少輸入相同值。

Excel-身分證號碼驗證

我國的身分證號碼是基於某一種規則之下產生,如何使用Excel來驗證身份證號碼是否為有效的號碼呢?

身分證號碼的第一碼是英文字母,代表的出生後入籍的縣市的代號,如下表所示:

而第2到第10個碼為阿拉伯數字,其中第2碼代表性別:1為男性、2為女性,第3碼至第9碼為流水號。最後一碼為檢查碼,而它的檢查方法透過以下的公式換算而來:

image02

(1) 驗算是否正確(正確:V,不正確:X)

儲存格B2:=IF(VALUE(RIGHT(A2,1))=10-MOD(SUM(K6:T6),10),"V","X")

(2) 判定縣市別

儲存格C2:=VLOOKUP(LEFT(A2,1),$F$2:$H$25,2)

(3) 判定性別

儲存格D2:=IF(MID(A2,2,1)="1","男","女")

image03

試著使用以下的公式產生一個亂數的號碼:

=CHAR(INT(RAND()*26+65))&INT(RAND()*2+1)&INT(RAND()*8999999+1000000)&INT(RAND()*10)

如果不想透過上述表格換算,而想直接在儲存格中判定,試試以下的公式:(以儲存格B3為例)

=IF(VALUE(RIGHT(A3,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),2,1)*9+MID(A3,2,1)*8+MID(A3,3,1)*7+MID(A3,4,1)*6++MID(A3,5,1)*5+MID(A3,6,1)*4+MID(A3,7,1)*3+MID(A3,8,1)*2+MID(A3,9,1)*1,10),10),"V","X")

如果想要縮短一點公式的長度,可以試試陣列公式:(以儲存格B4為例)(輸入要按Ctrl+Shift+Enter)

{=IF(VALUE(RIGHT(A4,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),2,1)*9+SUM(MID(A4,ROW(INDIRECT("2:9")),1)*(10-ROW(INDIRECT("2:9")))),10),10),"V","X")}

2010年6月28日 星期一

微軟的bing工具列

微軟的 Bing 搜尋,似乎愈來愈多人使用,所以安裝 Bing 工具列,看看有什麼特色?

下載網址:http://toolbar.msn.com/?loc=zh-tw

安裝好的bing工具列如下圖:

工具列上的新聞、影劇、生活、流行、影片、3C等,都是連結到MSN網站,當你點選其中一項時,會列出細部類別,再點選有興趣的內容之[讀取更多資訊],即會連結到MSN網站。

當你在輸入關鍵字時,其工具列中會延伸出網頁、圖片和新聞等圖示。

當你點選其中一項時,例如:圖示,即可展開搜尋。

當你點選工具列上的[安全中心]按鈕,其會列出目前IE的安全狀態。

如果你按一下工具列最右側的工具符號,則會顯[工具列選項]對話框。在這裡,你可以設定工具列要顯示的按鈕,可以勾選是否要顯示搜尋歷程記錄,還可以設定工具列的色彩等。

如果你登入Windows Live,則可以使用Hotmail信件的預覽功能,結合Messenger等功能。

2010年6月27日 星期日

Excel-限制不可輸入未來日期

在Excel中,如果要建立一個請假時數的資料表,如何來輸入最少資料而且避免輸入錯誤呢?練習以下的做法:

 

如果你在日期的儲存格欄位中使用資料驗證(如下圖),設定日期要小於「=TODAY()」,則可以避免輸入未來日期,但是必須準確的輸入日期格式。

如果你的工作是週期性的,例如每七天輸入一次,則可以利用一個工作表建立以下的資料:

儲存格A7:=TODAY()

儲存格A6:=A7-1,複製儲存格A6到儲存格A1:A6。(建立最近七天的日期清單)

然後選取儲存格A1:A7,定義名稱為:dayList。

在日期欄位中的驗證準則設定為儲存格允許:清單,來源:「=dayList」。

如此輸入日期時,不僅不會輸入未來日期,也可以由選取最近的日期來輸入。

假別欄位的驗證準則也設定為儲存格允許:清單,來源:「事假,病假,公假」。

Excel-對儲存格的公式除錯

在Excel的工作表中使用公式,難免會有錯誤運算訊息,所以提供了許多對公式除錯的工具。

在 E2 儲存格中出現了 #DIV/0! 的錯誤,只要按一下 Ctrl + ] ,就會顯示該儲存格中的公式引用了那些儲存格,並選取這些儲存格。而如果你選取一個儲存格,按一下 Ctrl + [ ,會顯示那些儲存格中的公式引用了這個儲存格,並選取那些儲存格。

如果你想知道公式中來源的儲存格,可以使用[追蹤前導參照]按鈕,即可以標示該儲存格公式中引用了那些儲存格。如果你想知道某個儲存格被其他儲存格引用,則按一下[追蹤從屬參照]按鈕。只要按一下[移除箭號]按鈕,即可以隱藏這些藍色的指標。

選取錯誤的儲存格,按一下[評估值公式]按鈕,可以逐步的檢驗問題在那裡。

每按一下[評估值]按鈕,即會進行一個運算動作。如果按一下[逐步執行]按鈕,可以進行更細部的運算做觀察。(如下圖,顯示D2+D3的結果為#VALUE!,即D2+D3出現錯誤。)

你還可以透過[監看視窗]來觀察各個儲存格中運算公式和內容的變化,只要按一下[新增監看式],再選取想要監看的儲存格即可。

Excel-在資料編輯列中使用函數提示工具

在Excel中的資料編輯列中,當你正在輸入函數名稱時,可以即時看到該函數的說明(如下圖),這個說明區是可以自行拖曳至其他位置的。如果尚未輸入完函數名稱,而在其列出的函數名稱上按二下滑鼠左鍵,Excel會代為輸入這個函數名稱。

當你輸入函數名稱為,再輸入第一個右括號時,則會顯示完整的引數提示。其中以 「[ ]」表示者為選用引數,即可以視需要輸入。

如果點選函數名稱(顯示藍色的超連結),則會顯示該函數的相關說明。

面對一個已輸入完成的函數,在其引數上(藍色超連結)按一下,則資料編輯列上會將該函數的引數內容,以反白顯示。

在資料編輯列中所用到的儲存格會以不同色彩表示,並會和儲存格上的框線色彩互相對照。

如果你拖曳儲存格上的色彩框線,則資料編輯列上的儲存格名稱也會隨之調整。

如果按一下資料編輯列上的「fx」符號,則會提供該函數的對話框,可以針對個別引數輸入,並可以看到相關的指引文字。 每個引數的右邊也會提供運算結果供參考。

2010年6月26日 星期六

在Google地圖上顯示中英文地名、路名

一般在台灣地區進入 Google 地圖網站(http://maps.google.com.tw/),地圖上顯示的完全是正體字的地名和路名。如果在台灣的老外要看 Google 地圖,或是想要知道地名、路名的英文,該如何由 Google 地圖上獲取呢?

image01

如果你試試這個網址:http://maps.google.com.tw/maps?hl=en

Google 地圖上將可以同時顯示正體字和英文字的地名、路名,可以中英文對照。

只是這些地名和路名不知是否準確?和實際道路上的標示是否一致?

如果要以簡體字看台灣的地名和路名,則試試:http://ditu.google.cn/

Excel-在圖表中凸顯最大值和最小值

在Excel的圖表中,如果只想凸顯資料中的最大值和最小值(如下圖),該如何處理呢?

必須藉助一列輔助列,輸入以下的公式:

儲存格A3:=IF(OR(A2=MIN($A$2:$J$2),A2=MAX($A$2:$J$2)),A2,NA())

複製儲存格A3到儲存格A3:J3。

只有最大值和最小值會被標示出來,其他以#N/A錯誤訊息表示。

選取儲存格A1:J3,插入平面折線圖中[含有資料標記的折線圖]的圖表。

將第3列的折線圖,按一下右鍵,選取[變更數列圖表類型]選項。

再選取[XY 散佈圖]中的[帶有資料標記的 XY 散佈圖]。

將第3列的折線圖,按一下右鍵,選取[變更數列圖表類型]選項。

再選[新增資料標籤]選項。

修改一下圖表的格式,即可在圖表中凸顯最大值和最小值。

Excel-分離儲存格中的文字和數字

在Excel中的表格,如果儲存格中輸入了含文字及數字的字串,想要將其分離來應用,該如何處理呢?

假設資料輸入在A欄,資料的最大長度為20個字。

(1) 前半部

儲存格B2:{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))-1)}

此公式為陣列公式,輸入要按 Ctrl + Alt + Enter。

複製儲存格B2到儲存格B2:B12。

原理解析:

ROW(INDIRECT("1:20"))

會產生陣列值:{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}

MID(A2,ROW(INDIRECT("1:20")),1)

將儲存格A2的內容從第1個字到第20個字,逐一取出。

ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1)

因為MID函數取出的任一個字都視為「文字」,所以將其乘以1,如果是文字則結果為#VALUE,如果是數字則結果為該數字。再利用ISNUMBER函數測試1~20的位置所取出的字是否為數字(True, False)。

IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))

判斷1~20的位置如果為數字,則會藉由ROW(INDIRECT("1:20"),給予對應的數字(1~20),否則給予99,例如「OK232」會得到「{99,99,3,4,5}」(表示第1,2個字為文字,第3,4,5個字為數字。)

=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))-1)

利用LEFT函數,由左邊取出第n個數字開始的位置再-1個數,即是字串的前半部。

 

(2) 後半部

儲存格C2:=RIGHT(A2,LEN(A2)-LEN(B2))

複製儲存格C2到儲存格C2:C12。

和用RIGHT和LEN函數,取出字串的後半部。

Excel-在圖片中顯示儲存格內容

在Excel中,把成績輸入至一個成績記錄表,同時在對應的圖表顯示儲存格中的內容,該如何處理呢?

做法非常簡單,參考以下做法:

1. 先將圖表建構好。

2. 點選一個圖形。

3. 在資料編輯列上輸入「=」,然後點選想要顯示的儲存格。

只要在儲存格輸入的任何資料,都會完全的顯示在圖片中。(注意:公式中的內容,必須為個有效位址參照或是已定義的名稱。)

4. 重覆步驟3。

2010年6月25日 星期五

Excel-使用多層下拉式清單結構輸入資料(進階)

前一篇文章提到:使用多層下拉式清單結構輸入資料(基礎)

參考網址:http://isvincent.blogspot.com/2010/06/excel_25.html

這次要來練習:不需要每個年級都定義一個名稱,而是要以整個基本資料表為單位來查詢。

各年級資料置於list工作表

 

1. 將A欄的儲存格設定資料驗證的準則為:

(1) 儲存格內允許:清單

(2) 來源=list!$A$1:$C$1

2. 定義名稱

年級:=list!$A$1:$C$1

3.將B欄的儲存格設定資料驗證的準則為:(以儲存格B2為例)

(1) 儲存格內允許:清單

(2) 來源=OFFSET(list!A2,0,MATCH(A2,年級,0)-1,15,1)

此處假設每個年級的最多班級數為14班,所以公式中:OFFSET(list!A2,0,MATCH(A2,年級,0)-1,15,1)使用參數15。此公式保留了彈性,如果年級數(欄數)有變動時,只要修改「年級」名稱定義的位址即可。

如此,只要於A欄的儲存格中選取一個年級,即可以在B欄中選取一個對應的相關班級。 

參考資料:

OFFSET:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:必要參數。這是用以計算位移的起始參照位址。

Rows:必要參數。這是左上角儲存格要往上或往下參照的列數。

Cols:必要參數。這是結果的左上角儲存格要往左或往右參照的欄數。

Height:選用參數。這是所傳回參照位址的高度 (以列數為單位)。Height 必須是正數。

Width:選用參數。這是所傳回參照位址的寬度 (以欄數為單位)。Width 必須是正數。

 

MATCH 函數會搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:必要項。這是您要在 lookup_array 中尋找比對的值。

lookup_array:必要參數。要搜尋儲存格範圍。

match_type:選用參數。這是一個數字,其值有三種可能:-1、0 或 1。

檢視其他文章

好康東東