Microsoft Word - Excel函數10-11.doc

1 絕對值 ( x ) FUNCTIONS 函數 RESULTS 結果 MEANING 意義 =ABS(1 4) 3 absolute value =ABS ( 2) #NAME? no blank 2 整數 =INT(18/5), =INT(3.65) 3 integer part =INT( 4.3) 5 3 去除小數 =TRUNC(3.65) 3 Truncate 截尾 =TRUNC( 4.3) 4 =TRUNC(123.456) 123 =INT(123.456) =TRUNC(123.456,1) 123.4 1 位小數 (0.1) =TRUNC(123.456,2) 123.45 2 位小數 (0.01) =TRUNC(129.456, 1) 120 十位 4 餘數 (a%b) =MOD(19,4) 3 餘數 remainder of 19/4 =MOD( 15,4) 1 意義不大 =MOD(15, 4) 1 =MOD( 15, 4) 3 5 四捨五入 ROUND, ROUNDUP, ROUNDDOWN =ROUND(125.678,0) 126 126 125 準確至 1 ( 個位 ) =ROUND(125.678,1) 125.7 125.7 125.6 1 位小數 (0.1) =ROUND(125.678,2) 125.68 125.68 125.67 2 位小數 (0.01) =ROUND(125.678, 1) 130 130 120 十位 10 =ROUND(125.678, 2) 100 200 100 百位 100 6 平方根 x =SQRT(16) 4 =SQRT( 16) #NUM! Error 7 次方 (xⁿ) ~pow() =POWER(2,3) 8 =2³ 三次方 =POWER(3,2) 9 =3² 二次方 =POWER(2, 3) 0.125 =2 ³ 8 隨機數 =RAND() 0 x 0.999 random number (0 0.999) =RAND()*100 0 x 99.999 =INT(RAND()*100) 0 99 C: n=rand()%100; =INT(RAND()*6)+1 1 6 C: n=rand()%6+1; 9 平方和 (a²+b²) =SUMSQ(3, 4) 25 =3²+4² =SUMSQ(10) 100 =10² =SUMSQ(A1:A3) 14 A1=1,A2=2,A3=3 Mr. CY Szeto page 1

E 1 3 TRUE 2 5 10 10 總和 =SUM(A1,B1,2) 5 =3+TRUE( 文字 )+2 =SUM(A2:B2, 15) 30 =5+10+15 =SUM(3, 2) 5 =SUM("3", 2, TRUE) 6 =3+2+TRUE(1) E 1 7 10 4 Sales 2 3.5 7 18 12/8/90 3 3.5 9 7 4 1 27 19 5 2 2 22.24 6 TRUE 7 #DIV/0! 11 排名次 =RANK( 數值 Num, 範圍 Ref, 次序 Order) =RANK(A1, A1:A5, 0) 1 =0 由大至小 descending =RANK(A1, A1:A5) 1 同上 =RANK(A1, A1:A5, 1) 5 0 由小至大 ascending 12 平均分 =AVERAGE(B1:B5) 11 =(10+7+9+27+2)/5 =AVERAGE(Scores) 11 Scores = B1:B5 =AVERAGE(B1:B5,5) 10 =(10+7+9+27+2+5)/6 13 數一數 ( 數字 日期 ) =COUNT(E1:E7) 3 數字儲存格 =COUNT(E1:E3) 1 包括 date =COUNT(E1:E7, 2) 4 14 數一數 ( 非空格, 空格 ) =COUNTA(E1:E7) 6 非空白 non blank =COUNTA(E1:E7, 2) 7 =COUNTBLANK(E1:E7) 1 空白 blank cells E 1 apples oranges peaches apples =IF(C3<50,"",C3) 2 32 54 75 86 =IF(C3<50,0,C3) 15 數一數 ( 範圍, 條件 ) =COUNTIF(A1:D1,"apples") 2 如果等如 "apples" =COUNTIF(A2:D2,">=55") 2 如果大於或等如 55 =COUNTIF(A2:D2,">"&B2) 2 如果大於 54 16 如果.., 則.., 否則.. =IF(A10>=50, "Pass", "Fail") 如果 A10>=50, 則 "Pass", 否則 "Fail" =IF(A10=100,SUM(B5:B15),"") 如果 A10=100, 則 SUM(B5:B15), 否則 "" =IF(A2>89, "A", IF(A2>79, "B", IF(A2>69, "C", IF(A2>59, "D", "F")))) Mr. CY Szeto page 2

1 房屋價值 銷售佣金 10 2 100,000 7,000 7 3 200,000 14,000 9 4 300,000 21,000 27 5 400,000 28,000 2 17 加總 B2:B5, 如果 A2:A5 大於 160000 =SUMIF(A2:A5,">160000",B2:B5) 63,000 18 最大 最小 =MAX(D1:D5) 27 最大 =MAX(D1:D5,30) 30 =MIN(D1:D5) 2 最小 =MIN(D1:D5, 0) 0 1 Frequency Color 顏色 2 4.1423 red 紅 3 4.1934 orange 橙 4 5.1723 yellow 黃 5 5.7734 green 綠 6 6.3898 blue 藍 已排序 7 7.3134 violet 紫 ** Sorted List 19 LOOKUP( 目標數值, 尋找範圍, 回傳範圍 ) sorted list required =LOOKUP(4.91, A2:A7, B2:B7) orange =LOOKUP(5.00, A2:A7, B2:B7) orange =LOOKUP(7.66, A2:A7, B2:B7) violet =LOOKUP(1.66, A2:A7, B2:B7) #N/A =LOOKUP(4.91, A2:A7, C2:C7) 橙 =LOOKUP("RED", B2:B7, C2:C7) 紅 not case sensitive =LOOKUP("blue", B2:B7, C2:C7) #N/A B2:B7 is unsorted 20 =LOOKUP("C",{"a","b","c","d";1,2,3,4}) 3 =LOOKUP("bump",{"a",1;"b",2;"c",3}) 2 1 Axles Bearings Bolts 汽車零件存貨 2 4 4 9 3 5 7 10 4 6 8 11 21 HLOOKUP( 目標數值, 尋找範圍, 回傳範圍, 近似 ) =HLOOKUP("Axles", A1:C4,2,TRUE) 4 =HLOOKUP("Bearings",A1:C4,3,FALSE) 7 =HLOOKUP("Bearings",A1:C4,3,TRUE) 7 =HLOOKUP("Bolts",A1:C4,4) 11 =HLOOKUP("Bolt",A1:C4,4,TRUE) 8 Mr. CY Szeto page 3

1 在 1 atm 壓力下的空氣 2 密度 黏度 溫度 3 (kg/m 3 ) (kg/m*s)*10 5 ( C) 4 0.457 3.55 500 5 0.525 3.25 400 6 0.616 2.93 300 7 0.675 2.75 250 8 0.764 2.57 200 9 0.835 2.38 150 10 0.946 2.17 100 11 1.09 1.95 50 12 1.29 1.71 0 22 VLOOKUP( 目標數值, 尋找範圍, 回傳範圍, 近似 ) 設 Range = A4:C12 =VLOOKUP(1,Range,1,TRUE) 0.946 最接近 =TRUE =VLOOKUP(1,Range,2) 2.17 default = TRUE =VLOOKUP(1,Range,3,TRUE) 100 =VLOOKUP(.746,Range,3,FALSE) 200 =VLOOKUP(0.1,Range,2,TRUE) #N/A value 0.1 not found =VLOOKUP(2,Range,2,TRUE) 1.71 =IFERROR(VLOOKUP(...),"",VLOOKUP(...)) 偵錯 23 第 n 個最小 =SMALL(B4:B12,4) 2.38 第 4 小 =SMALL(B4:B12,2) 1.95 第 2 小 24 第 n 個最大 =LARGE(B4:B12,3) 2.93 第 3 大 =LARGE(B4:B12,7) 2.17 第 7 大 E 1 1 3 2 3 2 2 1 3 2 3 3 2 1 1 25 先乘, 然後再加總 =SUMPRODUCT( A1:A3,D1:D3) 11 =1*2 + 2*3 + 3*1 =SUMPRODUCT( A2:B2,A3:B3) 8 =SUMPRODUCT( A1:B3,D1:E3) 24 26 日期 時間 小時 分鐘 (0.2 days = 4 hours 48 minutes) =NOW() 2007/9/1 23:45 date and time =DATE(2007,12,25) 2007/12/25 yyyy,mm,dd =HOUR(0.2) 4 hours 小時 =MINUTE(0.2) 48 minutes 分鐘 =WEEKDAY(TODAY(),2) 7 (sunday) mon(1) sun(7) Mr. CY Szeto page 4

27 字符串 String =CHAR(65) A ASCII Character =CODE("ABC") 65 Character ASCII =CONCATENATE("abc","xyz") abcxyz ="abc"&"xyz" =ISBLANK(A2) true or false 是否空白 28 字符串長度 =LEN("") 0 length of a string =LEN("abcde") 5 29 從左面取字串的部分 =LEFT("abcde") a 子字串 sub string =LEFT("abcde",3) abc =LEFT("abcde",LEN("abcde") 1) abcd 30 從中間取字串的部分 =MID("abcde",2,3) bcd =MID("abcde",9,3) empty string 31 從右面取字串的部分 =RIGHT("abcde",3) cde =RIGHT("abcde") e 32 轉小寫 一般 大寫 去除多餘空白 =LOWER("chAN tai MAN") chan tai man 小寫 =PROPER("chAN tai MAN") Chan Tai Man 一般 =UPPER("chAN tai MAN ") CHAN TAI MAN 大寫 =TRIM(" abc def ") abc def 去除多餘空白 spaces 1 零件及代號 2 Ceramic Insulators #124 TD45 87 3 Copper Coils #12 671 6772 4 Variable Resistors #116010 33 MID+FIND =MID(A2,1,FIND(" #",A2,1) 1) Ceramic Insulators =MID(A3,1,FIND(" #",A3,1) 1) Copper Coils =MID(A4,1,FIND(" #",A4,1) 1) Variable Resistors 34 比較字符串 ~strcmp =EXACT("abc","abc") true 真 相同 =EXACT("abc","ABC") false 假 不同 35 格式輸出 =TEXT(2.715, "$0.00") $2.72 =TEXT("1991/4/15","mmmm dd, yyyy") April 15, 1991 =TEXT(9,"00")&":"&TEXT(5,"00") 09:05 36 字符串 ( 文字 ) 轉數值 =VALUE("$1,000") 1000 =VALUE("1900/2/1") 32 32 days since 1/1/1900 =VALUE("12:00:00") 0.5 12 hrs = 0.5 days =VALUE("16:48:00") VALUE("12:00:00") 0.2 4 hrs 48 min = 0.2 days Mr. CY Szeto page 5

37 找尋 ( 目標, 搜索範圍, 開始位置 ) =FIND("M","Miriam McGovern") 1 =FIND("m","Miriam McGovern") 6 M m =FIND("M","Miriam McGovern",3) 8 A 1 Margin 2 Profit Margin 38 找尋 ( 目標, 搜索範圍, 開始位置 ) =SEARCH("e","Statements",6) 7 =SEARCH("E","Statements",6) 7 E=e =SEARCH(A1,A2) 8 39 取代 ( 原文, 開始, 字數, 代替文字 ) =REPLACE("Profit Margin", 8, 6, "Amount") =REPLACE(A2, 8, 6, "Amount") =REPLACE(A2, SEARCH(A1,A2), LEN(A2), "Amount") 40 選擇 (n, 選項 1, 選項 2, ) =CHOOSE(3,A3,A4,A5,A6) =SUM(A2:CHOOSE(2,A3,A4,A5)) =SUM(CHOOSE(2,A1:A2,A3:A4,A5:A6)) B Profit Amount Profit Amount Profit Amount A5 =SUM(A2:A4) =SUM(A3:A4) 41 邏輯表 Truth table AND true false OR true false NOT true true false true true true true false false false false false true false false true 42 邏輯運算子 Logical operators =AND(1<10,2<10) true (1<10) and (2<10) =AND(1>10,2<10,3>10) false =AND(1>10,2>10) false =OR(1>10,"ABC"="abc") true (1>10) or (2<10) =OR(1<10,2>10) true =OR(1>10,2>10) false =NOT(1=1) false negation =NOT(1<>1) true =AND(5=5,OR(1<10,2<10)) true 43 潤年測試 Leap Year Test A B C 1 1980 =MOD(A1,400)=0 =OR(B1,AND(B2,B3)) 2 =MOD(A1, 4)=0 3 =MOD(A1,100)<>0 潤年二月 =IF(OR(B1,AND(B2,B3)),29,28) 29 =B1 or (B2 and B3) =OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)) 44 TRUE / FALSE =TRUE() TRUE =FALSE() FALSE Mr. CY Szeto page 6

1 4 小於或等於 Frequency 2 7 9 2 =FREQUENCY(A$1:A$50,B2) 3 11 19 6 =FREQUENCY(A$1:A$50,B3) 4 16 29 12 5 16 39 15 6 17 49 21 7 20 59 29 8 21 69 36 9 21 79 44 10 24 89 48 11 27 99 50 {=FREQUENCY(A1:A50,B2:B11)} 45 統計資料 =MEDIAN(A1:A50) 53.5 中位數 =MODE(A1:A50) 53 出現次數最多 =FREQUENCY(A1:A11,10) 2 數值 10 出現次數 46 上捨入 CEILING( 數值, 準確至最接近的 ) =CEILING(169.65,1) 170 準確至 1 =CEILING(169.65,2) 170 準確至 2 =CEILING(169.65,0.1) 169.7 準確至 0.1 =CEILING(169.45,0.5) 169.5 準確至 0.5 47 下捨入 FLOOR( 數值, 準確至最接近的 ) =FLOOR(169.65,1) 169 準確至 1 =FLOOR(169.65,2) 168 準確至 2 =FLOOR(169.65,0.1) 169.6 準確至 0.1 =FLOOR(169.45,0.5) 169 準確至 0.5 48 =PRODUCT(A1:A5) A1*A2*A3*A4*A5 =ODD(10.6) 11 最接近的單數 =EVEN(10.6) 12 最接近的雙數 =ROMAN(2010) MMX 羅馬數字 =PI() 3.14... =FACT(4) 24 4! = 1 2 3 4 49 LOG(number,base) =LOG(5,10) 0.69897 =LOG(81,3) 4 =LOG10(5) 0.69897 =EXP(3) 20.08553692 e³ =LN(20.08553692) 3 Mr. CY Szeto page 7

1 Date Value 2 2000 08/01/2000 10.5 2000 3 2003 05/12/2003 7.2 4 2000 03/12/2000 100 5 2001 07/30/2001 5.4 6 2000 02/28/2000 8.1 50 SUMIF(range1,criteria,range2) =SUMIF(A2:A6,2000,C2:C6) 118.6 =10.5+100+8.1 =SUMIF(A:A,D2,C:C) 118.6 =SUMIF(A2:A6, ">=2001", C2:C6) 12.6 51 A1=Acer =SUBSTITUTE(A1,"er","or",1) Acor 把 A1 第 1 個內 "er" 取代為 "or" =FIXED(1234,1,TRUE) 1234.0 1 位小數, 沒有 (,) =FIXED(1234,2,FALSE) 1,234.00 2 位小數, 有 (,) 52 TEXT =TEXT(A1, "$#,##0.00") "$7,678.87" =TEXT(A1, "0") "7679" =TEXT(5, "00") "05" =TEXT(A2, "0.0") "123.7" =TEXT(1277, "#,##0") "1,277" =TEXT(C1, "yyyy/mm/dd") "2003/12/31" =TEXT(C1, "mmm dd, yyyy") "Dec 31, 2003" 53 只顯示文字 =T(56) "" =T("56") "56" =T("56 ABC") "56 ABC" 54 =CLEAN(A1) 清除 A1 內所有非列印字符 =REPT("ab",3) "ababab" =CONCATENATE(A1,"/",B1,"/",C1) 合併文字 "25/12/2005" = C1&" "&B1&" "&A1 合併文字 "2005-12-25" =DOLLAR(A1+A2,2) "$1,345.00" 55 SUBTOTAL( 方法, 範圍 ) 1 2 3 4 5 6 7 8 9 10 11 average count counta max min product stdev stdevp sum var varp 56 =PERCENTILE(A1:A10,0.5) median (50%) =PERCENTILE(A1:A10,0.75) 75% =QUARTILE(A1:A10,n) n=0..4 =STDEV(A1:A10) 標準差 Mr. CY Szeto page 8

=COMBIN(n,r) =PERMUT(n,r) Combination ncr Permutation npr 57 =INTERCEPT(y-values,x-values) y-intercept (when x=0) =INTERCEPT(A1:A5,B1:B5) =INDIRECT(A1) 60 Custom Format: ( 自訂格式 ) G/ 通用格式 0 0.00 #,##0 #,##0.00 _ * #,##0_ ; * #,##0_ ;_ * " "_ ;_ @_ _ * #,##0.00_ ; * #,##0.00_ ;_ * " "??_ ;_ @_ _ $* #,##0_ ; $* #,##0_ ;_ $* " "_ ;_ @_ _ $* #,##0.00_ ; $* #,##0.00_ ;_ $* " "??_ ;_ @_ #,##0; #,##0 $#,##0; $#,##0 #,##0;[ 紅色 ] #,##0 $#,##0;[ 紅色 ] $#,##0 #,##0.00; #,##0.00 $#,##0.00; $#,##0.00 #,##0.00;[ 紅色 ] #,##0.00 $#,##0.00; $#,##0.00 0% ##0.0E+0 0.00% #?/? 0.00E+00 #??/?? "US$"#,##0_);("US$"#,##0) "US$"#,##0_);[ 紅色 ]("US$"#,##0) "US$"#,##0.00_); ("US$"#,##0.00) d mmm mmm yy "US$"#,##0.00_); [ 紅色 ]("US$"#,##0.00) d mmmm yy m/d/yy e" 年 "m" 月 "d" 日 " e/m/d yyyy" 年 "m" 月 "d" 日 " yyyy/m/d hh" 時 "mm" 分 " hh:mm hh" 時 "mm" 分 "ss" 秒 " hh:mm:ss 上午 / 下午 hh" 時 "mm" 分 " hh:mm AM/PM 上午 / 下午 hh" 時 "mm" 分 "ss" 秒 " hh:mm:ss AM/PM yyyy/m/d hh:mm mm:ss @ mm:ss.0 [h]:mm:ss 61 自定清單 Auto Fill Horoscope? Aquarius, Pisces, Aries, Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio, Sagittarius, Capricorn 星座? 水瓶, 雙魚, 白羊, 金牛, 雙子, 巨蟹, 獅子, 處女, 天秤, 天蠍, 人馬, 山羊座十二生肖? 猴, 雞, 狗, 豬, 鼠, 牛, 虎, 兔, 龍, 蛇, 馬, 羊顏色? 紅, 橙, 黃, 綠, 青, 藍, 紫 one, two, three, four, five, six, seven, eight, nine, ten, eleven, twelve, thirteen, fourteen, fifteen, sixteen, seventeen, eighteen, nineteen,twenty, thirty, forty, fifty, sixty, seventy, eighty, ninety 一二三四五六七八九十 I, II, III, IV, V, VI, VII VIII, XI, X,..., L, D, M Mr. CY Szeto page 9