贊助廠商

目前分類:講義資料 (3225)

瀏覽方式: 標題列表 簡短摘要

在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。

vincent 發表在 痞客邦 留言(1) 人氣()

在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。

vincent 發表在 痞客邦 留言(3) 人氣()

資料來源:http://office.microsoft.com

摘錄微軟網站提供的 Excel 2010 函數說明,超連結會連到 Microsoft 網站。 如果需要用到時,可以方便查詢。

數學與三角函數  

ABS 函數

會傳回數字的絕對值
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 函數 會將數值捨位為整數

 

文字函數  

ASC 函數

將字串中的全形 (雙位原組) 英文字母或片假名變更為半形 (單位元組) 字元
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 函數 將文字引數轉換成數值

 

vincent 發表在 痞客邦 留言(6) 人氣()

在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。

其中:

vincent 發表在 痞客邦 留言(0) 人氣()

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

1.

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

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

2.

vincent 發表在 痞客邦 留言(1) 人氣()

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

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

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

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

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

vincent 發表在 痞客邦 留言(2) 人氣()

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

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

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

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

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

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

image02

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

vincent 發表在 痞客邦 留言(1) 人氣()

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

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

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

儲存格A7:=TODAY()

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

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

vincent 發表在 痞客邦 留言(2) 人氣()

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

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

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

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

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

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

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

vincent 發表在 痞客邦 留言(1) 人氣()

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

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

(1) 前半部

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

vincent 發表在 痞客邦 留言(11) 人氣()

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

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

1. 先將圖表建構好。

2. 點選一個圖形。

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

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

vincent 發表在 痞客邦 留言(1) 人氣()

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

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

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

各年級資料置於list工作表

 

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

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

vincent 發表在 痞客邦 留言(7) 人氣()

在Excel中輸入資料,如果完全以Key In方式輸入,除了耗費較多時間之外,還可能打錯字。所以如果能以選取方式來輸入資料,應該可以改善這些問題。解決這個問題,利用資料驗證、INDIRECT函數和定義名稱,可以很簡單的解決。

假設要有三個年級,每個年級的班級名稱都不相同,規則上也有所差異。(參考下圖)

 

假設各年級資料置於list工作表中,作法如下:

1. 定義名稱

(1) 一年級:=list!$A$2:$A$15

vincent 發表在 痞客邦 留言(4) 人氣()

在 Excel 中也可以做到選取儲存格內容後,即對應顯示一個圖片。(以Excel 2010為例)

以下就以製作「各地區的天氣預報的報表」來練習,Show工作表為顯示結果,List工作表為基本資料。先將網路上找到天氣相關的圖片,複製到List工作表中,並調整為相同大小,置放於Show工作表的B欄中。


Show工作表

List工作表

 

首先,為了達到各地區天氣狀況以選取方式產生(免於輸入)(如下圖),所以要藉助資料驗證的功能。

vincent 發表在 痞客邦 留言(9) 人氣()

這次來玩玩在Excel中產生10組偶數和10組奇數,比較其大小後,以設定格式化條件表示結果(如下圖)。如果偶數>奇數,則以紅色圓球表示;如果奇數>偶數,則以黑色圓球表示。

(1) 產生0~1000的偶數

儲存格B2:=INT(RAND()*500)*2

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

(2) 產生0~1000的偶數

vincent 發表在 痞客邦 留言(2) 人氣()

在 Excel 中,SUMPRODUCT函數對於製作數字統計相關的摘要表非常容易,再來練習以下的報表製作:

(1) 在B欄要計算每個人的請假次別(不管何種假別):

儲存格B1:輸入「第1次」

儲存格B2:="第"&COUNTIF(A$2:A3,A3)&"次"

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

vincent 發表在 痞客邦 留言(3) 人氣()

在 Excel 的一個大工作表中,如果要選其中某些類型的儲存格,不管使用那一種人工點選的方式,多將是費時費力。你可以讓Excel為你代勞!

選取資料範圍,按一下 F5 鍵,顯示[到]對話框,按一下[特殊]按鈕,顯示[特殊目標]對話框。

 

例如:選取[空格]選項,則可以快速選取所有的空格,這比起一格一格的選取要方便多了!

vincent 發表在 痞客邦 留言(0) 人氣()

Close

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼