1. 絕對值 (|x|) =ABS(1–4) =ABS (–2) 3 #NAME? Error - no blank 2. 整數

Presentation on theme: "1. 絕對值 (|x|) =ABS(1–4) =ABS (–2) 3 #NAME? Error - no blank 2. 整數"— Presentation transcript:

1 1. 絕對值 (|x|) =ABS(1–4) =ABS (–2) 3 #NAME? Error - no blank 2. 整數
=INT(18/5) =INT(3.65) =INT(–4.3) 3 –5 3. 去除尾數(小數) =TRUNC(3.65) =TRUNC(–4.3) =TRUNC( ) =TRUNC( ,1) =TRUNC( ,2) =TRUNC( ,–1) 3 –4 123 123.4 123.45 120 truncate截尾 =INT( ) 準確至1位小數 (0.1) 準確至2位小數 (0.01) 準確至十位 (10) 函數 Functions

2 ROUND, ROUNDUP, ROUNDDOWN
4. 餘數 (a%b) =MOD(19,4) =MOD(–15,4) =MOD(15,–4) =MOD(–15,–4) 3 1 –1 –3 餘數remainder of 19/4 5. 四捨五入 ROUND, ROUNDUP, ROUNDDOWN =ROUND( ,0) =ROUND( ,1) =ROUND( ,2) =ROUND( ,–1) =ROUND( ,–2) 126 125.7 125.68 130 100 126 125.7 125.68 130 200 125 125.6 125.67 120 100 準確至1 (個位) 1位小數 (0.1) 2位小數 (0.01) 十位 10 百位 100 函數 Functions

3 6. 平方根 =SQRT(16) =SQRT(–16) 4 #NUM! Error 7. 次方 =POWER(2,3)
8 =2³ 三次方 9 =3² 二次方 0.125 =2–3 8. 隨機數 =RAND() =RAND()*100 =INT(RAND()*100) =INT(RAND()*6)+1 0 – 99 1 – 6 n=rand()%100; n=rand()%6+1; 函數 Functions

4 9. 平方和 (a²+b²) =SUMSQ(3, 4) =SUMSQ(10) =SUMSQ(A1:A3) 25 =3²+4² 100
=10² 35 =3²+5²+1² A B C D E 1 3 TRUE 2 5 10 10. 總和  =SUM(A1,B1,2) =SUM(A2:B2, 15) =SUM(3, 2) =SUM("3", 2, TRUE) 5 =3+TRUE(文字)+2 30 = 6 =3+2+TRUE(1) 函數 Functions

5 11. 排名次 =RANK(數值Num,範圍Ref,次序Order) =RANK(A1, A1:A5, 0)
B C D 1 7 10 4 2 3.5 18 3 9 27 5 11. 排名次 =RANK(數值Num,範圍Ref,次序Order) =RANK(A1, A1:A5, 0) =RANK(A1, A1:A5) =RANK(A1, A1:A5, 1) 1 =0 由大至小descending 同上 5 0 由小至大ascending 12. 平均分 Scores = B1:B5 =AVERAGE(B1:B5) =AVERAGE(Scores) =AVERAGE(B1:B5,5) 11 =( )/5 10 =( )/6 函數 Functions

6 E 1 Sales 2 25/12/1999 3 4 19 5 22.24 6 TRUE 7 #DIV/0! 13. 數一數 COUNT (數字、日期) =COUNT(E1:E7) =COUNT(E1:E3) =COUNT(E1:E7, 2) 3 數字+日期 1 date 4 14. 數一數 COUNTA (非空格，空格) =COUNTA(E1:E7) =COUNTA(E1:E7, 2) =COUNTBLANK(E1:E7) 6 非空白non–blank 7 1 空白blank cells 函數 Functions

7 A B C D E 1 apples oranges peaches =IF(C3<50,"",C3) 2 32 54 75 86
15. 數一數 (範圍，條件) =COUNTIF(A1:D1,"apples") =COUNTIF(A2:D2,">=55") =COUNTIF(A2:D2,">"&B2) 2 如果等如"apples" 如果大於或等如55 如果大於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") ) ) ) 函數 Functions

8 A B C D 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 17. 加總B2:B5，如果A2:A5大於160000 =SUMIF(A2:A5,">160000",B2:B5) 63,000 18. 最大max、最小min =MAX(D1:D5) =MAX(D1:D5,30) =MIN(D1:D5) =MIN(D1:D5, 0) 27 30 2 函數 Functions

9 19. LOOKUP(目標數值，尋找範圍，回傳範圍) =LOOKUP(4.91, A2:A7, B2:B7)
C D 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(目標數值，尋找範圍，回傳範圍) =LOOKUP(4.91, A2:A7, B2:B7) =LOOKUP(5.00, A2:A7, B2:B7) =LOOKUP(7.66, A2:A7, B2:B7) =LOOKUP(1.66, A2:A7, B2:B7) =LOOKUP(4.91, A2:A7, C2:C7) =LOOKUP("RED", B2:B7, C2:C7) =LOOKUP("blue", B2:B7, C2:C7) orange violet #N/A 橙 紅 函數 Functions B2:B7 is unsorted

10 =LOOKUP("bump",{"a",1;"b",2;"c",3}) 3
=LOOKUP("C",{"a","b","c","d";1,2,3,4}) =LOOKUP("bump",{"a",1;"b",2;"c",3}) 3 2 A B C D 1 Axles Bearings Bolts 汽車零件存貨 2 4 9 3 5 7 10 6 8 11 21. HLOOKUP(目標數值，尋找範圍，回傳範圍，近似) default=FALSE =HLOOKUP("Axles", A1:C4,2,TRUE) =HLOOKUP("Bearings",A1:C4,3,FALSE) =HLOOKUP("Bearings",A1:C4,3,TRUE) =HLOOKUP("Bolts",A1:C4,4) =HLOOKUP("Bolt",A1:C4,4,TRUE) =HLOOKUP("Bolt",A1:C4,4,FALSE) 4 近似 7 準確 11 8 #N/A 函數 Functions

11 A B C D 1 在1 atm壓力下的空氣 2 密度 黏度 溫度 3 (kg/m3) (kg/m*s)*105 (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 設Range = A4:C12 函數 Functions

12 22. VLOOKUP(目標數值，尋找範圍，回傳範圍，近似)
設Range = A4:C12 =VLOOKUP(1,Range,1,TRUE) =VLOOKUP(1,Range,2) =VLOOKUP(1,Range,3,TRUE) =VLOOKUP(.746,Range,3,FALSE) =VLOOKUP(0.1,Range,2,TRUE) =VLOOKUP(2,Range,2,TRUE) =IFERROR(VLOOKUP(...),"",VLOOKUP(...)) 最接近=TRUE default = TRUE 準確=FALSE 0.1 not found 偵錯 0.946 2.17 100 200 #N/A 1.71 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最大 函數 Functions

13 26. 日期、時間、小時、分鐘 (0.2 days = 4 hours 48 minutes) =NOW()
B C D E 1 3 2 25. 先乘，然後再加總 =SUMPRODUCT(A1:A3,D1:D3) =SUMPRODUCT(A2:B2,A3:B3) =SUMPRODUCT(A1:B3,D1:E3) =1*2 + 2*3 + 3*1 =2*3 + 1*2 11 8 24 26. 日期、時間、小時、分鐘 (0.2 days = 4 hours 48 minutes) =NOW() =DATE(2007,12,25) =HOUR(0.2) =MINUTE(0.2) =WEEKDAY(TODAY(),2) date and time yyyy,mm,dd 小時hours 分鐘minutes (sunday) mon(1)–sun(7) 2007/9/1 23:45 2007/12/25 4 48 7 函數 Functions

14 =CONCATENATE("abc","xyz")
27. 字符串String =CHAR(65) =CODE("ABC") =CONCATENATE("abc","xyz") =ISBLANK(A2) ASCII  Char Char  ASCII ="abc"&"xyz" true or false 是否空白 A 65 abcxyz 28. 字符串長度 String length =LEN("") =LEN("abcde") 5 29. 從左面取字串的部分 子字串sub–string =LEFT("abcde") =LEFT("abcde",3) =LEFT("abcde",LEN("abcde")–1) =LEFT(“陳大文",2) a abc Abcd 陳大 函數 Functions

15 30. 從中間取字串的部分 =MID(字串,開始,字數) =MID("abcde",2,3)
empty string 31. 從右面取字串的部分 =RIGHT("abcde",3) =RIGHT("abcde") cde e 32. 轉小寫、一般、大寫、去除多餘空白 小寫 一般 大寫 =LOWER("chAN tai MAN") =PROPER("chAN tai MAN") =UPPER("chAN tai MAN ") =TRIM(" abc def ") chan tai man Chan Tai Man CHAN TAI MAN abc def 去除多餘空白spaces 函數 Functions

16 case sensitive without wildcards
33. 找尋(目標，搜索範圍，開始位置) =FIND("M","Miriam McGovern") =FIND("m","Miriam McGovern") =FIND("M","Miriam McGovern",3) 1 6 8 A 1 零件及代號 2 Ceramic Insulators #124–TD45–87 3 Copper Coils #12–671–6772 4 Variable Resistors #116010 34. MID+FIND =MID(A2,1,FIND(" #",A2,1)–1) =MID(A3,1,FIND(" #",A3,1)–1) =MID(A4,1,FIND(" #",A4,1)–1) Ceramic Insulators Copper Coils Variable Resistors 函數 Functions

17 =VALUE("16:48:00")–VALUE("12:00:00") 1000
35. 比較字符串 =EXACT("abc","abc") =EXACT("abc","ABC") True 真 相同 False 假 不同 36. 格式輸出 =TEXT(2.715, "$0.00") =TEXT("1991/4/15","mmmm dd, yyyy") =TEXT(9,"00")&":"&TEXT(5,"00") $2.72 April 15, 1991 09:05 37. 字符串轉數值 =VALUE("$1,000") =VALUE("1900/2/1") =VALUE("12:00:00") =VALUE("16:48:00")–VALUE("12:00:00") 1000 32 1/1/ 0.5 0.5 days 0.2 0.2 days =4 hrs 48 min 函數 Functions

18 non-case sensitive with wildcards
1 Margin 2 Profit Margin non-case sensitive with wildcards 38. 找尋(目標，搜索範圍，開始位置) =SEARCH("e","Statements",6) =SEARCH("E","Statements",6) =SEARCH(A1,A2) 7 8 39. 找尋(目標，搜索範圍，開始位置) =REPLACE("Profit Margin", 8, 6, "Amount") =REPLACE(A2, 8, 6, "Amount") =REPLACE(A2, SEARCH(A1,A2), LEN(A2), "Amount") =SEARCH("ch*n", "Cheong") Profit Amount 1 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)) A5 =SUM(A2:A4) =SUM(A3:A4) 函數 Functions

19 42. 邏輯運算子Logical operators =AND(1<10,2<10)
41. 邏輯表Truth table AND true false OR true false NOT true false true false true false false true 42. 邏輯運算子Logical operators =AND(1<10,2<10) =AND(1>10,2<10,3>10) =AND(1>10,2>10) =OR(1>10,"ABC"="abc") =OR(1<10,2>10) =OR(1>10,2>10) =NOT(1=1) =NOT(1<>1) =AND(5=5,OR(1<10,2<10)) true false (1<10) and (2<10) (1>10) or (2<10) false true negation 函數 Functions

20 AND(MOD(A1,4)=0,MOD(A1,100)<>0))
43. 潤年測試Leap Year Test A B C 1 1980 Leap Year ? 2 3 =MOD(A1,400)=0 =MOD(A1,4)=0 =MOD(A1,100)<>0 =OR(B1,AND(B2,B3)) 潤年二月 =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 函數 Functions

21 A B C 1 4 小於或等於 Frequency 2 7 9 3 11 19 6 16 29 12 5 39 15 17 49 21 20 59 8 69 36 79 44 10 24 89 48 27 99 50 =FREQUENCY(A$1:A$50,B2) 累積頻數 45. 統計資料 =MEDIAN(A1:A50) 中位數 =MODE(A1:A50) 出現次數最多 =FREQUENCY(A1:E10,10) 數值≤10出現次數 53.5 53 12 函數 Functions

22 46. 上捨入CEILING(數值，準確至最接近的) =CEILING(169.65,1) 準確至1
準確至2 =CEILING(169.65,0.1) 準確至0.1 =CEILING(169.45,0.5) 準確至0.5 170 169.7 169.5 47. 下捨入FLOOR(數值，準確至最接近的) =FLOOR(169.65,1) 準確至1 =FLOOR(169.65,2) 準確至2 =FLOOR(169.65,0.1) 準確至0.1 =FLOOR(169.45,0.5) 準確至0.5 169 168 169.6 函數 Functions

23 48 =PRODUCT(A1:A5) =A1*A2*A3*A4*A5 =ODD(10.6) 最接近的單數 =EVEN(10.6)
最接近的雙數 =ROMAN(2010) 羅馬數字 =PI()  =FACT(4) 4! = 1234 11 12 MMX 24 49. LOG(number,base基數) =LOG(5,10) 10x=5 =LOG(81,3) 3x=81 =LOG10(5) =EXP(3) e³ =LN( ) ex= 4 3 函數 Functions

24 50. SUMIF (range範圍1,criteria條件,range範圍2) =SUMIF(A2:A6,2000,C2:C6)
B C D 1 Date Value 2 2000 08/01/2000 10.5 3 2003 05/12/2003 7.2 4 03/12/2000 100 5 2001 07/30/2001 5.4 6 02/28/2000 8.1 50. SUMIF (range範圍1,criteria條件,range範圍2) =SUMIF(A2:A6,2000,C2:C6) = =SUMIF(A:A,D2,C:C) ditto =SUMIF(A2:A6, ">=2001", C2:C6) = 118.6 12.6 函數 Functions

25 51 "Acer" =SUBSTITUTE(A1,"er","or",1) 把A1第1個內"er"取代為"or"
=FIXED(1234,1,TRUE) 1位小數，沒有(,) =FIXED(1234,2,FALSE) 2位小數，有(,) Acor 1234.0 1,234.00 52. TEXT =TEXT(A1, "$#,##0.00") =TEXT(A1, "0") =TEXT(5, "00") =TEXT(A2, "0.0") =TEXT(1277, "#,##0") =TEXT(C1, "yyyy/mm/dd") =TEXT(C1, "mmm dd, yyyy") $7,678.87 7679 05 123.7 1,277 2003/12/31 Dec 31, 2003 53. T 只顯示文字 =T(56) =T("56") 56 =T("56 ABC") 56 ABC 函數 Functions

26 A B C 1 25 12 2005 2 1000 3 345 54 =CLEAN(A10) =REPT("ab",3)
=CONCATENATE(A1,"/",B1,"/",C1) =C1&"–"&B1&"–"&A1 =DOLLAR(A2+A3,2) 清除A10內所有非列印字符 重複3次 "ababab" 合併文字 "25/12/2005" 合併文字 " " "$1,345.00" 55. SUBTOTAL(方法1-11，範圍) 1 2 3 4 5 6 average count counta max min product 7 8 9 10 11 stdev stdevp sum var varp 函數 Functions

27 57. INTERCEPT y-intercept (when x=0) =INTERCEPT(y-values,x-values)
56 =PERCENTILE(A1:A10,0.5) median (50%) =PERCENTILE(A1:A10,0.75) 75% =QUARTILE(A1:A10,n) n=0..4 =STDEV(A1:A10) 標準差 =COMBIN(n,r) Combination nCr =PERMUT(n,r) Permutation nPr 57. INTERCEPT y-intercept (when x=0) =INTERCEPT(y-values,x-values) =INTERCEPT(A1:A5,B1:B5) =INDIRECT(A1) 函數 Functions

28 _–* #,##0_–;–* #,##0_–;_–* "–"_–;_–@_–
60. Custom Format: (自訂格式) G/通用格式 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% ##0.0E+0 0.00% # ?/? 0.00E+00 # ??/?? 函數 Functions

29 "US$"#,##0_);[紅色]("US$"#,##0)
60. Custom Format: (自訂格式) "US$"#,##0_);("US$"#,##0) "US$"#,##0_);[紅色]("US$"#,##0) "US$"#,##0.00_); ("US$"#,##0.00) [紅色]("US$"#,##0.00) d–mmm d–mmmm–yy mmm–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 函數 Functions

30 水瓶,雙魚,白羊,金牛,雙子,巨蟹,獅子,處女,天秤,天蠍,人馬,山羊座
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 函數 Functions