2011年7月27日 星期三

Excel-比較各組數字的個數(SUMPRODUCT)

在 Excel 中有一個三組(A,B,C)的數字,現在要來找出互相比較大小的個數,只要使用SUMPRODUCT函數即可達到。

為了簡化公式,所以先定義名稱:選取儲存格A1:C21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,定義A組、B組、C組三個名稱。

(1) 計算 A>B 的個數

儲存格J2:=SUMPRODUCT(--(A組>B組))

其中「--」可以將 True/False 的陣列轉換為 1/0 的陣列。

(2) 計算 A>C 的個數

儲存格J3:=SUMPRODUCT(--(A組>C組))

(3) 計算 B>C 的個數

儲存格J4:=SUMPRODUCT(--(B組>C組))

(4) 計算 A>B且B>C 的個數

儲存格J5:=SUMPRODUCT(--(A組>B組),--(B組>C組))

(5) 計算 A>C且B>C 的個數

儲存格J6:=SUMPRODUCT(--(A組>C組),--(B組>C組))

如果你想要試試陣列的方式,可以參考以下的做法:

(以下均為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。)

(1) 計算 A>B 的個數

儲存格J2:{=SUM(IF(A組>B組,1,0))}

(2) 計算 A>C 的個數

儲存格J3:{=SUM(IF(A組>C組,1,0))}

(3) 計算 B>C 的個數

儲存格J4:{=SUM(IF(B組>C組,1,0))}

(4) 計算 A>B且B>C 的個數

儲存格J5:{=SUM(IF(A組>B組,IF(B組>C組,1,0),FALSE))}

(5) 計算 A>C且B>C 的個數

儲存格J6:{=SUM(IF(A組>C組,IF(B組>C組,1,0),FALSE))}

2011年7月25日 星期一

Excel-計算年休假日數(VLOOKUP)

一般在學校中兼行政的老師可以比照公務人員,每年可以有休假日數,如下所示:

服務滿一年(第二年起):7天
服務滿三年(第四年起):14天
服務滿六年(第七年起):21天
服務滿九年(第十年起):28天
服務滿十四年(第十五年起):30天

根據以上的資料如何快速求得每個行政人員的年休假日數呢?(參考下圖左)

首先要建立一個年資和年休假日數的對照表(如下圖右)

儲存格E2:=VLOOKUP(D2,$G$1:$H$7,2,TRUE)

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

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

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

Excel-用公式轉換全型字和半型字(ASC和BIG5)

因為英文字和數字在顯示時可以設定為全型字或半型字,當你在 Excel 中取得文件要資料處理,試著練習將全型文數字轉為半型字,或是將半型文數字轉為全型字。

儲存格B2:=ASC(A2)

複製儲存格B2,往下貼上。複製B欄,貼上時選取「值」,可以將公式消除。

儲存格C2:=BIG5(B2)

複製儲存格C2,往下貼上。複製C欄,貼上時選取「值」,可以將公式消除。

Excel-轉換日期和時間的格式

在 Excel 的工作表中,可能為了輸入方便或是由其他資料來源取得,日期輸入為:20110101格式,來表示2011年1月1日;時間輸入為:0102格示,來表示1時2分。如何轉換為 Excel 可以運算的日期格式或是正確的表示方式呢?(參考下圖)

因為「20110101」和「0102」被 Excel 認為只是一個數字或是一串文字,所以要以TEXT函數來轉換:

儲存格B2:=TEXT(A2,"0000-00-00")

將原始日期轉換為「0000-00-00」格式,此轉換結果視日期為文字字串。

如果要拿日期來運算,則應修正為:

儲存格C2:=--TEXT(A2,"0000-00-00")

其中的「--」運算可將數字組成的文字轉換為可運算的數字。

同理,來處理時間部分。

儲存格E2:=TEXT(D2,"00\:00")

公式中的「\:」,乃用於強制顯示「:」,否則公式結果將會出錯。也可以使用「!:」,結果是相同的。

儲存格F2:=--TEXT(D2,"00\:00")

如果,你要反動作將B欄的日期轉換為A欄的表示法,而E欄的時間轉換為D欄的表示法,該如何處理?

儲存格A2:=SUBSTITUTE(B2,"-","")

儲存格D2:=SUBSTITUTE(E2,":","")

利用SUBSTITUTE函數將「-、:」去除(以空的字串取代)。

2011年7月21日 星期四

Excel-列出排名前10名

有網友閱讀另一篇文章:

Excel-關於數列的排名(http://isvincent.blogspot.com/2011/06/excel_5404.html)

問到:如果根據下圖的左表要查出前10名(參考下圖右表),該如何做呢?

當然你可以透過排序等操作,可以得到結果,但是網友可能想要以公式自動產生。

首先,定義名稱:

選取儲存格A1:D25,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,設定:編號、姓名、分數、名次等名稱。再次選取儲存格A1:D25,將其名稱定義為:資料。

接著,輸入公式:

儲存格H2:=LARGE(分數,F2)

儲存格G2:{=VLOOKUP(SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)),資料,2)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

因為資料表中可能會有名次重覆的問題,所以:

COUNTIF($H$2:H2,H2):可以算出在H欄中的儲存格之前有幾個和自己重覆。

IF(H2=分數,編號,FALSE):利用陣列公式找出某數和數列相符所對應的編號,其結果類似:Fasle,False,3,False, …, Fasle的陣列,利用SMALL可以取出這個唯一的數值。

SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)):可以找出名次所對應的編號。

接著根據編號查表得到姓名:

VLOOKUP(SMALL(IF(H2=分數,編號,FALSE),COUNTIF($H$2:H2,H2)),資料,2)

 

關於函數說明,請參考微軟網站:

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

2011年7月18日 星期一

Word-在尋找與取代中使用萬用字元

在執行 Word 的尋找與取代功能時,可以使用萬用字元「*、?」,來擴大找尋範圍。其中「*」可以代表0到多個字元,而「?」代表1個字元。

你必須在[尋找與取代]對話框中:

1. 按一下[較多]按鈕。

2. 勾選[使用萬用字元]選項。

3. 在[尋找目標]文字方塊中輸入含有萬用字元的搜尋文字。

4. 按一下[尋找下一筆]按鈕。

Word-更換文件中指定文字的格式

最近同事問到,如果要將文件中的某些文件設定同一格式,那種方式較方便?

有的人會先在指定文字上設定好格式,再以複製格式的方式套用到每個相同文字上,但這樣的手動處理,總是速度較慢、較費工,且容易出錯。

建議如下的做法:

1. 按一下 Ctrl+H 鍵,開啟[尋找與取代]對話框。

2. 在[尋找目標]文字方塊中輸入想要被設定格式的文字,本例為:「中華電信」。

3. 將滑鼠游標移至[取代為]文字方塊中按一下。(不需輸入任何文字)

4. 按一下[較多]按鈕。

5. 按一下[格式]按鈕, 選取[字型]。

6. 設定想要的格式,例如:字型設為標楷體、字型樣式為粗體、字型色彩為紅色、設定單線底線。

7. 按一下[確定]按鈕。

8. 按一下[全部取代]按鈕。

其結果如下:

如果你按一下[尋找與取代]對話框中的[不限定格式]按鈕,則可以將[尋找]或[取代]文字方塊中的格式清除。

2011年7月16日 星期六

Google街景已可全台走透透了

一段時日沒有特別留意 Google 的街景檢視功能,最近試了一下,全台各縣市已都被涵蓋了。(參考下圖)

縣市合併的行政區名也都有更新了:

之前介紹過的關廟麵-長安製麵廠(http://isvincent.blogspot.com/2011/02/blog-post.html),輸入新的地址時已可清楚找到,並且可以街景檢視了。

2011年7月15日 星期五

畫面精美的測速網站

最近測速的話題又熱絡了,因為中華電信升速又降價。這次來介紹一個畫面精美的測速網站:

http://speedtest.net/

你可以測試PING、DWONLOAD SPEED、UPLOAD SPEED,單位是bit(bits per seocnd)。

DWONLOAD SPEED:

UPLOAD SPEED:

你還可以選取地圖中的一個位置(伺服器)來測試:

這個網站還連結至另一個測試PING速度的網站:http://pingtest.net/

你也是可以選取一個位置(伺服器)來測試:

Windows 7-在檔案總管中使用篩選功能

在 Windows 7 的檔案總管中檢視檔案,如果檔案很多時,就必須透過篩選功能,可以縮小檢視的檔案數量,進而快速找到想要的檔案。

首先要在「詳細資料」的模式下,在每個欄位名稱上會顯示一個下拉式清單的三角型符號。點選這個符號即可顯示篩選條件,勾選這些條件即可即時看到篩選結果。

例如:在檔案名稱欄位上即可依數字、英文字、中文筆劃等條件來篩選:

篩選結果只會顯示符合條件的檔案和資料夾,欄位名稱上也會顯示一個打勾符號。在一個欄位中設定篩選條時,篩選條件可以複選,如果在另一個欄位再設定篩選條件,則會以現在的篩選結果,再進一步篩選。如果要回復全部內容(未篩選),則取消已勾選的篩選條件即可。

依日期來篩選:

依檔案類型篩選:

依檔案大小來篩選:

當你新增一個欄位時,也可以使用篩選功能:

2011年7月14日 星期四

拖曳檔案至桌面的操作

通常在檔案總管中拖曳檔案至桌面,可以有以下三種操作:

1. Ctrl+拖曳:將檔案「複製」至桌面

2. Shift+拖曳:將檔案「搬移」至桌面

3. Alt+拖曳:在桌面建立檔案捷徑

Excel-將儲存格範圍轉換為表格

在 Excel 中,資料是放在一般的儲存格範圍中,你可以將這些儲存格範圍,轉換成表格,可以方便進行樣式設定及計算。

1. 選取表格中的一個儲存格。

2. 選取[插入/表格]按鈕,或是按一下 Ctrl+T 鍵。

3. 確認表格資料來源。

此時,該表格即多了「設計」功能表標籤,並且可以選取顯示/隱藏:標題列、首欄、合計列、末欄、帶狀列、帶狀欄等。也有多種表樣式可以選取。

當你勾選了[合計列],則會自動加入合計列並顯示加總結果。你可以在「合計」數值的儲存格中使用下拉式清單挑選不同的計算結果。

選取這個儲存格的右下角,拖曳至其他儲存格,即可以複製合計公式。而每個合計儲存格都可以修改合計的公式。

如果想要將表格轉換為一般儲存格,則按一下[設計/轉換為範圍]按鈕。

Excel-讓舊版Excel文件使用新版功能

當你在 Excel 2010 中開啟舊版 Excel 文件時,會發生無法使用新版 Excel 功能的狀況,例如:

2003格式:

image

2010格式:

image

你可以透過選取[檔案/資訊],再按一下[轉換]按鈕。

系統會提示你轉換檔案格式,可以使用 Excel 的所有新功能,並減少檔案大小。

當你按一下[確定]按鈕,該檔案會被轉成新版的格式,並且修改副檔名。

如果你將 Excel 2010 文件儲存成 Excel 2003 版,則新版的功能也會無法使用。

2011年7月12日 星期二

使用Gmail寄送其他郵件帳戶的郵件

在學校中大家習慣使用 Outlook 來收發或其他Webmail來收發信,但是如果想要:

(1) 使用 Gmail 寄信時,對方能回信到學校的郵件地址。

(2) 使用 Outlook 軟體時,也能使用 Gmail 寄信時,對方能回信到學校的郵件地址。
     (例如學校的 Email 伺服器故障了時可用)

(3) 寄信時能保留一份副本在 Gmail 帳戶中。

該如何處理?

一、在Gmail中的設定

1. 在 Gamil 的郵件設定中選取[帳戶和匯入]標籤。

2. 按一下[透過其他地址傳送郵件]按鈕。

3. 輸入你要對方回信的址址(例如你在學校的Email)。

4. Gmail 會要你認證這個郵件帳戶,按一下[傳送驗證]按鈕。

5. 到你指定的郵件地址收信,並輸入驗證碼。

6. 將該地址設為「預設」。

此後,只要你登入 Gmail 所發的信,都會回信到你新指定的地址。

7. 在 Gmail 帳戶中啟用 POP,設定完成時要按一下 [儲存變更]。

 

二、在Outlook中的設定

以下 Outlook 2010 來測試:

1. 在 Outlook 中新增一個電子郵件帳號。

2. 輸入你的名稱和 Gmail 電子郵件地址,再輸入這個 Gamil 郵件的密碼。

3. 按一下[下一步]按鈕。

4. Outlook 會開測試並寄發測試郵件訊息。(可以至Gmail信箱看看是否收到測試信)

5. 勾選[手動設定伺服器設定]選項。

6. 按一下[下一步]按鈕。

image

7. 在電子郵件地址輸入你要對方回覆的地址。

8. 在[帳戶類型]中選取[POP3]。

9. 在內送郵件伺服器中輸入:「pop.gmail.com」。

10. 在外寄郵件伺服器中輸入:「smtp.gmail.com」。

11. 在[傳送新郵件到]中選取[現有 Outlook 資料檔]選項,並且按一下[ 瀏覽]按鈕,選取一個郵件資料檔(已存在的資料檔)。
( ※如果你不指定一個資料檔,Outlook 會設定一個新的資料檔,你必須管理較多的資料檔。)

12. 按一下[其他設定]按鈕。

※請不要勾選:此選項需要使用安全密碼驗證 (SPA) 登入。

13. 在[一般]標籤下輸入一個可供辨識的名稱,本例為「TEST」。


14. 在 [外寄伺服器] 標籤下,勾選 [我的外寄伺服器 (SMTP) 需要驗證],並選取 [使用與內送郵件伺服器相同的設定]。


15. 按一下 [進階] 標籤下的 [內送伺服器 (POP3)]方塊中輸入995,並勾選 [此伺服器需要加密連線 (SSL) ] 。

16. 在 [外送伺服器 (SMTP)] 方塊中輸入 587,並在 [使用下列加密連線類型]下拉式選單中選取 [TLS]。

17. 勾選[在伺服器上保留一份郵件伺服器複本],並取消勾選[超過下列列天數就從伺服器移除]選項。

18. 按一下 [確定]按鈕。進行再一次的測試。

如果你寄一封給對方,對方的回覆地址會以你指定的地址來寄送。而如果對方以 Outlook 軟體來收發信,則會顯示如下圖的資訊,並且會以你指定的地址來寄送回信。

當你以 Outlook 寄出一封信時,在 Gmail 帳戶中也會保留一份副本。以後,你可以在家裡或其他地點以 Gmail 或 Outlook 寄信,都可以由學校的郵件伺服器來收信了。

檢視其他文章

好康東東