Microsoft Word - Excel函數10-11.doc

Size: px

Start display at page:

Download "Microsoft Word - Excel函數10-11.doc"

向臣事权
5 years ago
Views:

1 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 =INT( ) =TRUNC( ,1) 位小數 (0.1) =TRUNC( ,2) 位小數 (0.01) =TRUNC( , 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( ,0) 準確至 1 ( 個位 ) =ROUND( ,1) 位小數 (0.1) =ROUND( ,2) 位小數 (0.01) =ROUND( , 1) 十位 10 =ROUND( , 2) 百位平方根 x =SQRT(16) 4 =SQRT( 16) #NUM! Error 7 次方 (xⁿ) ~pow() =POWER(2,3) 8 =2³ 三次方 =POWER(3,2) 9 =3² 二次方 =POWER(2, 3) =2 ³ 8 隨機數 =RAND() 0 x random number ( ) =RAND()*100 0 x =INT(RAND()*100) 0 99 C: n=rand()%100; =INT(RAND()*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

2 E 1 3 TRUE 總和 =SUM(A1,B1,2) 5 =3+TRUE( 文字 )+2 =SUM(A2:B2, 15) 30 = =SUM(3, 2) 5 =SUM("3", 2, TRUE) 6 =3+2+TRUE(1) E Sales /8/ 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 =( )/5 =AVERAGE(Scores) 11 Scores = B1:B5 =AVERAGE(B1:B5,5) 10 =( )/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) =IF(C3<50,0,C3) 15 數一數 ( 範圍, 條件 ) =COUNTIF(A1:D1,"apples") 2 如果等如 "apples" =COUNTIF(A2:D2,">=55") 2 如果大於或等如 55 =COUNTIF(A2:D2,">"&B2) 2 如果大於如果.., 則.., 否則.. =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

3 1 房屋價值銷售佣金 ,000 7, ,000 14, ,000 21, ,000 28, 加總 B2:B5, 如果 A2:A5 大於 =SUMIF(A2:A5,">160000",B2:B5) 63, 最大最小 =MAX(D1:D5) 27 最大 =MAX(D1:D5,30) 30 =MIN(D1:D5) 2 最小 =MIN(D1:D5, 0) 0 1 Frequency Color 顏色 red 紅 orange 橙 yellow 黃 green 綠 blue 藍已排序 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 汽車零件存貨 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

4 1 在 1 atm 壓力下的空氣 2 密度黏度溫度 3 (kg/m 3 ) (kg/m*s)*10 5 ( C) VLOOKUP( 目標數值, 尋找範圍, 回傳範圍, 近似 ) 設 Range = A4:C12 =VLOOKUP(1,Range,1,TRUE) 最接近 =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 先乘, 然後再加總 =SUMPRODUCT( A1:A3,D1:D3) 11 =1*2 + 2*3 + 3*1 =SUMPRODUCT( A2:B2,A3:B3) 8 =SUMPRODUCT( A1:B3,D1:E3) 日期時間小時分鐘 (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

5 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 TD Copper Coils # Variable Resistors # 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") days since 1/1/1900 =VALUE("12:00:00") hrs = 0.5 days =VALUE("16:48:00") VALUE("12:00:00") hrs 48 min = 0.2 days Mr. CY Szeto page 5

6 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 =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

7 1 4 小於或等於 Frequency =FREQUENCY(A$1:A$50,B2) =FREQUENCY(A$1:A$50,B3) {=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) 準確至 0.1 =CEILING(169.45,0.5) 準確至下捨入 FLOOR( 數值, 準確至最接近的 ) =FLOOR(169.65,1) 169 準確至 1 =FLOOR(169.65,2) 168 準確至 2 =FLOOR(169.65,0.1) 準確至 0.1 =FLOOR(169.45,0.5) 169 準確至 =PRODUCT(A1:A5) A1*A2*A3*A4*A5 =ODD(10.6) 11 最接近的單數 =EVEN(10.6) 12 最接近的雙數 =ROMAN(2010) MMX 羅馬數字 =PI() =FACT(4) 24 4! = LOG(number,base) =LOG(5,10) =LOG(81,3) 4 =LOG10(5) =EXP(3) e³ =LN( ) 3 Mr. CY Szeto page 7

8 1 Date Value /01/ /12/ /12/ /30/ /28/ SUMIF(range1,criteria,range2) =SUMIF(A2:A6,2000,C2:C6) = =SUMIF(A:A,D2,C:C) =SUMIF(A2:A6, ">=2001", C2:C6) A1=Acer =SUBSTITUTE(A1,"er","or",1) Acor 把 A1 第 1 個內 "er" 取代為 "or" =FIXED(1234,1,TRUE) 位小數, 沒有 (,) =FIXED(1234,2,FALSE) 1, 位小數, 有 (,) 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 合併文字 " " =DOLLAR(A1+A2,2) "$1,345.00" 55 SUBTOTAL( 方法, 範圍 ) 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

9 =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_ ;_ * " "_ _ * #,##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.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.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

Excel 2010

Excel 2010 1. 絕對值 ( x ) =ABS(1 4) =ABS ( 2) 3 #NAME? Error - no blank 2. 整數 ( 較小 ) =INT(18/5) =INT(3.65) =INT( 4.3) 3. 去除尾數 ( 小數 ) 3 3 5 =TRUNC(3.65) =TRUNC( 4.3) =TRUNC(123.456) =TRUNC(123.456,1) =TRUNC(123.456,2)