在Excel中的一個資料表,試著來練習將其摘要出相關統計報表。(如下圖)
(1) 學歷分析
使用SMUPRODUCT函數來計算人數(參考微軟網站:SUMPRODUCT 函數說明)
儲存格H4:=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$H$2)*($B$2:$B$25=H$3))
複製儲存格H4到儲存格H4:I6。
在Excel中的一個資料表,試著來練習將其摘要出相關統計報表。(如下圖)
(1) 學歷分析
使用SMUPRODUCT函數來計算人數(參考微軟網站:SUMPRODUCT 函數說明)
儲存格H4:=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$H$2)*($B$2:$B$25=H$3))
複製儲存格H4到儲存格H4:I6。
在Excel中建立一個成績表,如果只要顯示前三名,並且以不同色彩標示,該如何處理呢?
儲存格F2:=IF(E2=LARGE($E$2:$E$26,1),1,IF(E2=LARGE($E$2:$E$26,2),2,IF(E2=LARGE($E$2:$E$26,3),3,"")))
或是
儲存格F2:{=IF(RANK(E2,$E$2:$E$26)<=3,RANK(E2,$E$2:$E$26),"")}
這是陣列公式,輸入請按Ctrl+Shift+Enter。
資料來源:http://office.microsoft.com
摘錄微軟網站提供的 Excel 2010 函數說明,超連結會連到 Microsoft 網站。 如果需要用到時,可以方便查詢。
數學與三角函數 | |
會傳回數字的絕對值 | |
ACOS 函數 | 會傳回數字的反餘弦值 |
ACOSH 函數 | 會傳回數字的反雙曲線餘弦值 |
AGGREGATE 函數 | 傳回清單或資料庫中的彙總 |
ASIN 函數 | 會傳回數字的反正弦值 |
ASINH 函數 | 會傳回數字的反雙曲線正弦值 |
ATAN 函數 | 會傳回數字的反正切值 |
ATAN2 函數 | 會從 X 與 Y 座標傳回反正切值 |
ATANH 函數 | 會傳回數字的反雙曲線正切值 |
CEILING 函數 | 會將數字四捨五入至最接近的整數或最接近的比較基數 |
CEILING.PRECISE 函數 | 會將數字四捨五入至最接近的整數或最接近的比較基數。無論數字的正負號,都會將數字進位。 |
COMBIN 函數 | 會傳回指定物件數的組合數 |
COS 函數 | 會傳回數字的餘弦值 |
COSH 函數 | 會傳回數字的雙曲線餘弦值 |
DEGREES 函數 | 會將弧度轉換成度數 |
EVEN 函數 | 會將數字無條件進位至最接近的偶數整數 |
EXP 函數 | 會傳回提升至指定數字之乘冪的 e |
FACT 函數 | 會傳回數字的階乘 |
FACTDOUBLE 函數 | 會傳回數字的雙階乘 |
FLOOR 函數 | 將數值以趨近於零方式捨位 |
FLOOR.PRECISE 函數 | 會將數字捨位至最接近的整數值或最接近的比較基數。無論數字的正負號,都會將數字捨位。 |
GCD 函數 | 會傳回最大公因數 |
INT 函數 | 會將數字捨位至最接近的整數值 |
LCM 函數 | 會傳回最小公倍數 |
LN 函數 | 會傳回數字的自然對數 |
LOG 函數 | 會傳回指定底數之數字的對數 |
LOG10 函數 | 會傳回數字的以 10 為底對數 |
MDETERM 函數 | 會傳回陣列的矩陣行列式 |
MINVERSE 函數 | 會傳回陣列的反矩陣 |
MMULT 函數 | 會傳回兩個陣列的矩陣乘積 |
MOD 函數 | 會傳回相除後的餘數 |
MROUND 函數 | 傳回四捨五入為所需倍數的數字 |
MULTINOMIAL 函數 | 會傳回一組數字的多項式 |
ODD 函數 | 會將數字無條件進位至最接近的奇數整數 |
PI 函數 | 會傳回 Pi 的值 |
POWER 函數 | 會傳回提升至某個乘冪之數字的結果 |
PRODUCT 函數 | 會將引數相乘 |
QUOTIENT 函數 | 會傳回相除後的整數部分 |
RADIANS 函數 | 會將度數轉換成弧度 |
RAND 函數 | 會傳回 0 和 1 之間的亂數 |
RANDBETWEEN 函數 | 會傳回所指定數字之間的亂數 |
ROMAN 函數 | 會將阿拉伯數字轉換成文字格式的羅馬字 |
ROUND 函數 | 會將數字四捨五入至指定的位數 |
ROUNDDOWN 函數 | 將數值以趨近於零方式捨位 |
ROUNDUP 函數 | 會將數字四捨五入至指定的位數 |
SERIESSUM 函數 | 會傳回依據公式的冪級數總和 |
SIGN 函數 | 會傳回數字的正負號 |
SIN 函數 | 會傳回指定角度的正弦值 |
SINH 函數 | 會傳回數字的雙曲線正弦值 |
SQRT 函數 | 會傳回正平方根 |
SQRTPI 函數 | 會傳回 (number * pi) 的平方根 |
SUBTOTAL 函數 | 會傳回清單或資料庫的小計 |
SUM 函數 | 會將引數相加 |
SUMIF 函數 | 會將特定準則所指定的儲存格相加 |
SUMIFS 函數 | 加入範圍中符合多個準則的儲存格 |
SUMPRODUCT 函數 | 會傳回對應陣列元件之乘積的總和 |
SUMSQ 函數 | 會傳回引數之平方的總和 |
SUMX2MY2 函數 | 會傳回兩個陣列中對應值之平方差的總和 |
SUMX2PY2 函數 | 會傳回兩個陣列中對應值之平方和的總和 |
SUMXMY2 函數 | 會傳回兩個陣列中對應值之差的平方和 |
TAN 函數 | 會傳回數字的正切值 |
TANH 函數 | 會傳回數字的雙曲線正切值 |
TRUNC 函數 | 會將數值捨位為整數 |
文字函數 | |
將字串中的全形 (雙位原組) 英文字母或片假名變更為半形 (單位元組) 字元 | |
BAHTTEXT 函數 | 依照 ß (泰銖) 貨幣格式,將數值轉換成文字 |
CHAR 函數 | 傳回代碼所對應的字元 |
CLEAN 函數 | 移除文字中所有無法列印的字元 |
CODE 函數 | 文字字串中第一個字元的字碼 |
CONCATENATE 函數 | 將數個文字項目組合成一個文字項目 |
DOLLAR 函數 | 依照 $ (錢幣) 貨幣格式,將數值轉換成文字 |
EXACT 函數 | 檢查兩個文字值是否相同 |
FIND、FINDB 函數 | 在其他文字值中搜尋文字值 (大小寫視為相異) |
FIXED 函數 | 以固定的小數位數將數值格式化為文字 |
JIS 函數 | 將字串中的半形 (單位元組) 英文字母或片假名變更為全形 (雙位原組) 字元 |
LEFT、LEFTB 函數 | 傳回文字值中最左邊的字元 |
LEN、LENB 函數 | 傳回文字字串中的字元個數 |
LOWER 函數 | 將文字轉換成小寫 |
MID、MIDB 函數 | 從文字字串中的指定位置開始傳回指定數目的字元 |
PHONETIC 函數 | 從文字字串中抽選注音 (假名註解) 字元 |
PROPER 函數 | 將文字值中每個單字的第一個字母設為大寫 |
REPLACE、REPLACEB 函數 | 取代文字中的字元 |
REPT 函數 | 依指定的次數重複顯示文字 |
RIGHT、RIGHTB 函數 | 傳回文字值中最右邊的字元 |
SEARCH 與 SEARCHB 函數 | 在其他文字值中搜尋文字值 (大小寫視為相異) |
SUBSTITUTE 函數 | 使用新文字取代文字字串中的舊文字 |
T 函數 | 將引數轉換成文字 |
TEXT 函數 | 設定數字的格式並將其轉換成文字 |
TRIM 函數 | 移除文字中的空格 |
UPPER 函數 | 將文字轉換成大寫 |
VALUE 函數 | 將文字引數轉換成數值 |
在Excel中建立一組隨機亂數資料,如何自動配置到另一個資料表中呢?例如依每間隔20為單位來配置。
儲存格G2:{=SMALL(IF($A$2:$E$11>=--LEFT(G$1,FIND("-",G$1,1)-1),IF($A$2:$E$11<=--RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1)),$A$2:$E$11)),ROW(1:1))}
此公式為陣列公式,輸入時要按Ctrl+Shift+Enter。
將儲存格G2複製到儲存格G2:K20。
其中:
在Excel中輸入一個資料表,如下圖左半部,班級是不規則順序輸入的。如果要自動轉換成右半部,變成依班級順序排列,而且只有班級名稱的第一筆才顯示,其餘班級名稱不顯示。
1.
儲存格G2:=SMALL($B$2:$B$24,ROW(1:1))
觀察因為班級全部由數字組成,所以利用SMALL函數,將其由小到大取出。
2.
在Excel中,如果要將一組依序排好的資料(例如:依序的班級編號)隨機重新排序,該如何處理呢?
因為要隨機排序,所以需要產生一組亂數:
儲存格A2:=INT(RAND()*100)+ROW()/1000
複製儲存格A2到儲存格A2:A17。
公式:INT(RAND()*100)產生1~99的整數亂數。
在Excel中如果要輸入一個報名表,希望同一欄中的資料是唯一值(不可重覆),該如何處理?
例如在運動會一百公尺報名表(每班一名),希望班級欄位不要重覆,可以藉助資料驗證來把關。
儲存格A3:=IF(B4<>"",A3+1,"")
將儲存格A3往下複製到多個儲存格。
該公式的作用是,如果B欄有輸入資料時,序號會自動加1。
我國的身分證號碼是基於某一種規則之下產生,如何使用Excel來驗證身份證號碼是否為有效的號碼呢?
身分證號碼的第一碼是英文字母,代表的出生後入籍的縣市的代號,如下表所示:
而第2到第10個碼為阿拉伯數字,其中第2碼代表性別:1為男性、2為女性,第3碼至第9碼為流水號。最後一碼為檢查碼,而它的檢查方法透過以下的公式換算而來:
(1) 驗算是否正確(正確:V,不正確:X)
在Excel中,如果要建立一個請假時數的資料表,如何來輸入最少資料而且避免輸入錯誤呢?練習以下的做法:
如果你在日期的儲存格欄位中使用資料驗證(如下圖),設定日期要小於「=TODAY()」,則可以避免輸入未來日期,但是必須準確的輸入日期格式。
如果你的工作是週期性的,例如每七天輸入一次,則可以利用一個工作表建立以下的資料:
儲存格A7:=TODAY()
在Excel的工作表中使用公式,難免會有錯誤運算訊息,所以提供了許多對公式除錯的工具。
在 E2 儲存格中出現了 #DIV/0! 的錯誤,只要按一下 Ctrl + ] ,就會顯示該儲存格中的公式引用了那些儲存格,並選取這些儲存格。而如果你選取一個儲存格,按一下 Ctrl + [ ,會顯示那些儲存格中的公式引用了這個儲存格,並選取那些儲存格。
如果你想知道公式中來源的儲存格,可以使用[追蹤前導參照]按鈕,即可以標示該儲存格公式中引用了那些儲存格。如果你想知道某個儲存格被其他儲存格引用,則按一下[追蹤從屬參照]按鈕。只要按一下[移除箭號]按鈕,即可以隱藏這些藍色的指標。
選取錯誤的儲存格,按一下[評估值公式]按鈕,可以逐步的檢驗問題在那裡。
在Excel中的資料編輯列中,當你正在輸入函數名稱時,可以即時看到該函數的說明(如下圖),這個說明區是可以自行拖曳至其他位置的。如果尚未輸入完函數名稱,而在其列出的函數名稱上按二下滑鼠左鍵,Excel會代為輸入這個函數名稱。
當你輸入函數名稱為,再輸入第一個右括號時,則會顯示完整的引數提示。其中以 「[ ]」表示者為選用引數,即可以視需要輸入。
如果點選函數名稱(顯示藍色的超連結),則會顯示該函數的相關說明。
面對一個已輸入完成的函數,在其引數上(藍色超連結)按一下,則資料編輯列上會將該函數的引數內容,以反白顯示。
在Excel的圖表中,如果只想凸顯資料中的最大值和最小值(如下圖),該如何處理呢?
必須藉助一列輔助列,輸入以下的公式:
儲存格A3:=IF(OR(A2=MIN($A$2:$J$2),A2=MAX($A$2:$J$2)),A2,NA())
複製儲存格A3到儲存格A3:J3。
只有最大值和最小值會被標示出來,其他以#N/A錯誤訊息表示。
在Excel中的表格,如果儲存格中輸入了含文字及數字的字串,想要將其分離來應用,該如何處理呢?
假設資料輸入在A欄,資料的最大長度為20個字。
(1) 前半部
儲存格B2:{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),
ROW(INDIRECT("1:20")),99))-1)}
在Excel中,把成績輸入至一個成績記錄表,同時在對應的圖表顯示儲存格中的內容,該如何處理呢?
做法非常簡單,參考以下做法:
1. 先將圖表建構好。
2. 點選一個圖形。
3. 在資料編輯列上輸入「=」,然後點選想要顯示的儲存格。
只要在儲存格輸入的任何資料,都會完全的顯示在圖片中。(注意:公式中的內容,必須為個有效位址參照或是已定義的名稱。)
前一篇文章提到:使用多層下拉式清單結構輸入資料(基礎)
參考網址:http://isvincent.blogspot.com/2010/06/excel_25.html
這次要來練習:不需要每個年級都定義一個名稱,而是要以整個基本資料表為單位來查詢。
各年級資料置於list工作表 |
1. 將A欄的儲存格設定資料驗證的準則為:
(1) 儲存格內允許:清單
在Excel中輸入資料,如果完全以Key In方式輸入,除了耗費較多時間之外,還可能打錯字。所以如果能以選取方式來輸入資料,應該可以改善這些問題。解決這個問題,利用資料驗證、INDIRECT函數和定義名稱,可以很簡單的解決。
假設要有三個年級,每個年級的班級名稱都不相同,規則上也有所差異。(參考下圖)
假設各年級資料置於list工作表中,作法如下:
1. 定義名稱
(1) 一年級:=list!$A$2:$A$15
在 Excel 中也可以做到選取儲存格內容後,即對應顯示一個圖片。(以Excel 2010為例)
以下就以製作「各地區的天氣預報的報表」來練習,Show工作表為顯示結果,List工作表為基本資料。先將網路上找到天氣相關的圖片,複製到List工作表中,並調整為相同大小,置放於Show工作表的B欄中。
Show工作表 |
List工作表 |
首先,為了達到各地區天氣狀況以選取方式產生(免於輸入)(如下圖),所以要藉助資料驗證的功能。
這次來玩玩在Excel中產生10組偶數和10組奇數,比較其大小後,以設定格式化條件表示結果(如下圖)。如果偶數>奇數,則以紅色圓球表示;如果奇數>偶數,則以黑色圓球表示。
(1) 產生0~1000的偶數
儲存格B2:=INT(RAND()*500)*2
複製儲存格B2到儲存格B2:K2。
(2) 產生0~1000的偶數
在 Excel 中,SUMPRODUCT函數對於製作數字統計相關的摘要表非常容易,再來練習以下的報表製作:
(1) 在B欄要計算每個人的請假次別(不管何種假別):
儲存格B1:輸入「第1次」
儲存格B2:="第"&COUNTIF(A$2:A3,A3)&"次"
複製儲存格B2到儲存格B2:B21。
在 Excel 的一個大工作表中,如果要選其中某些類型的儲存格,不管使用那一種人工點選的方式,多將是費時費力。你可以讓Excel為你代勞!
選取資料範圍,按一下 F5 鍵,顯示[到]對話框,按一下[特殊]按鈕,顯示[特殊目標]對話框。
例如:選取[空格]選項,則可以快速選取所有的空格,這比起一格一格的選取要方便多了!