1. 絕對值 (|x|)
30
函函 Functions 1 1. 函函函 (|x|) 2. 函函 ( 函函 ) =INT(18/5) =INT(3.65) =INT(–4.3) 3 integer 3 –5 3. 函函函函 ( 函函 ) =TRUNC(3.65) =TRUNC(–4.3) =TRUNC(123.456) =TRUNC(123.456,1) =TRUNC(123.456,2) =TRUNC(129.456,–1) 3 –4 123 123.4 123.45 120 truncate 函函 =INT(123.456) 函函函 1 函函函 (0.1) 函函函 2 函函函 (0.01) 函函函函函 (10) =ABS(1–4) =ABS (–2) 3 #NAME? Error - no blank |–3| 3.6
description
1. 絕對值 (|x|) . | – 3|. 2. 整數 ( 較小 ). 3.6. 3. 去除尾數 ( 小數 ). 4. 餘數 (a%b). 餘數 remainder of 19/4 . 5. 四捨五入. ROUND, ROUND UP , ROUND DOWN ( 下捨入 ). 6. 平方根. Error . 7. 次方. 8. 隨機數. 9. 平方和 (a²+b²). 10. 總和 . 11. 排名次 =RANK( 數值 Num, 範圍 Ref , 次序 Order). 12. 平均分. - PowerPoint PPT Presentation
Transcript of 1. 絕對值 (|x|)
1函數 Functions
1. 絕對值 (|x|)
2. 整數 ( 較小 )
=INT(18/5)=INT(3.65)=INT(–4.3)
3 integer
3–5
3. 去除尾數 ( 小數 )
=TRUNC(3.65)=TRUNC(–4.3)=TRUNC(123.456)=TRUNC(123.456,1)=TRUNC(123.456,2)=TRUNC(129.456,–1)
3–4123123.4123.45120
truncate 截尾=INT(123.456)準確至 1 位小數 (0.1)準確至 2 位小數 (0.01)準確至十位 (10)
=ABS(1–4)
=ABS (–2)3#NAME? Error - no blank
|–3|
3.6
2函數 Functions
4. 餘數 (a%b)
5. 四捨五入
=MOD(19,4)=MOD(–15,4)=MOD(15,–4)=MOD(–15,–4)
31–1–3
餘數 remainder of 19/4
=ROUND(125.678,0)=ROUND(125.678,1)=ROUND(125.678,2)=ROUND(125.678,–1)=ROUND(125.678,–2)
126125.7125.68130100
準確至 1 ( 個位 )1 位小數 (0.1)2 位小數 (0.01)十位 10百位 100
126125.7125.68130200
125125.6125.67120100
ROUND, ROUNDUP, ROUNDDOWN( 下捨入 )
3函數 Functions
6. 平方根 x
=SQRT(16)=SQRT(–16)
4#NUM! Error
7. 次方=POWER(2,3)=POWER(3,2)=POWER(2,–3)
8 =2³ 3 次方9 =3² 2 次方0.125 =2–3
8. 隨機數=RAND()=RAND()*100=INT(RAND()*100)=INT(RAND()*6)+1
0 – 0.9990 – 99.9990 – 991 – 6
nx
n=rand()%100;n=rand()%6+1;
1616
4函數 Functions
9. 平方和 (a²+b²)
=SUMSQ(3, 4)=SUMSQ(10)=SUMSQ(A1:A3)
25 =3²+4²100 =10²35 =3²+5²+1²
A B C D E1 3 TRUE2 5 103 110. 總和
=SUM(A1,B1,2)=SUM(A2:B2, 15)=SUM(3, 2)=SUM("3", 2, TRUE)
5 =3+TRUE( 文字 )+230 =5+10+1556 =3+2+TRUE(1)
5函數 Functions
A B C D1 7 10 42 3.5 7 183 3.5 9 74 1 275 2 2
11. 排名次 =RANK( 數值 Num, 範圍 Ref, 次序 Order)
=RANK(A1, A1:A5, 0)=RANK(A1, A1:A5)=RANK(A1, A1:A5, 1)
1 =0 由大至小 descending 1 同上5 0 由小至大 ascending
12. 平均分=AVERAGE(B1:B5)=AVERAGE(Scores)=AVERAGE(B1:B5,5)
Scores = B1:B5
11 =(10+7+9+27+2)/51110 =(10+7+9+27+2+5)/6
6函數 Functions
E1 Sales2 25/12/199934 195 22.246 TRUE7 #DIV/0!
13. 數一數 COUNT ( 數字、日期 )
3 數字 + 日期1 date4
14. 數一數 COUNTA ( 非空白儲存格 ) ， COUNTBLANK( 空白儲存格 )
6 非空白 non–blank71 空白 blank cells
=COUNT(E1:E7)=COUNT(E1:E3)=COUNT(E1:E7, 2)
=COUNTA(E1:E7)=COUNTA(E1:E7, 2)=COUNTBLANK(E1:E7)
7函數 Functions
A B C D E1 apples oranges peaches apples =IF(C3<50,"",C3)2 32 54 75 86 =IF(C3<50,0,C3)
15. 數一數 ( 範圍，條件 )
=COUNTIF(A1:D1,"apples")=COUNTIF(A2:D2,">=55")=COUNTIF(A2:D2,">"&B2)
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") ) ) )
2 如果等如 "apples"2 如果大於或等如 552 如果大於 54
8函數 Functions
A B C D1 房屋價值 銷售佣金 102 100,000 7,000 73 200,000 14,000 94 300,000 21,000 275 400,000 28,000 2
=SUMIF(A2:A5,">160000",B2:B5)17. 加總 B2:B5 ，如果 A2:A5 大於 160000
18. 最大 max 、最小 min
=MAX(D1:D5)=MAX(D1:D5,30)=MIN(D1:D5)=MIN(D1:D5, 0)
63,000
273020
9函數 Functions
A B C D1 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)
B2:B7 is unsorted 未排序
orangeorangeviolet#N/A橙紅#N/A
4.91
7.66
10函數 Functions
20 Lookup=LOOKUP("C",{"a","b","c","d";1,2,3,4})=LOOKUP("bump",{"a",1;"b",2;"c",3})
A B C D1 Axles Bearings Bolts 汽車零件存貨2 4 4 93 5 7 104 6 8 11
21. HLOOKUP( 目標數值，尋找範圍，回傳範圍，近似 )
=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)
32
4 近似7 準確7 近似11 準確8 近似#N/A 準確
default=FALSE
橫向 Horizontal Table Lookup
11函數 Functions
A B C D1 在 1 atm 壓力下的空
氣2 密度 黏度 溫度3 (kg/m3) (kg/m*s)*105 (C)4 0.457 3.55 5005 0.525 3.25 4006 0.616 2.93 3007 0.675 2.75 2508 0.764 2.57 2009 0.835 2.38 15010 0.946 2.17 10011 1.090 1.95 5012 1.290 1.71 0設 Range = A4:C12
22. VLOOKUP( 目標數值，尋找範圍，回傳範圍，近似 )
Key (A4:A12)
12函數 Functions
22. VLOOKUP( 目標數值，尋找範圍，回傳範圍，近似 )
=VLOOKUP(1, A4:C12,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(...))
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 最大
最接近 =TRUEdefault = TRUE
準確 =FALSE0.1 not found
偵錯
設 Range = A4:C120.9462.17100200#N/A1.71
緃向 Vertical Table Lookup
13函數 Functions
A B C D E1 1 3 2 32 2 1 3 23 3 2 1 1
25. 先乘，然後再加總=SUMPRODUCT(A1:A3,D1:D3)=SUMPRODUCT(A2:B2,A3:B3)=SUMPRODUCT(A1:B3,D1:E3)
26. 日期、時間、小時、分鐘 (0.2 days = 240.2 = 4 hours 48 minutes)
=NOW()=DATE(2007,12,25)=HOUR(0.2)=MINUTE(0.2)=WEEKDAY(TODAY(),2)
=1*2 + 2*3 + 3*1=2*3 + 1*2
date and timeyyyy,mm,dd小時部分 hours分鐘部分 minutes
(sunday) mon(1)–sun(7)
11824
2007/9/1 23:452007/12/254487
星期幾
14函數 Functions
27. 字符串 String
=CHAR(65)=CODE("ABCDE")=CONCATENATE("abc","xyz")=ISBLANK(A2)
28. 字符串長度 String length
=LEN("")=LEN("abcde")
29. 從左面取字串的部分=LEFT("abcde")=LEFT("abcde",3)=LEFT("abcde",LEN("abcde")–1)=LEFT(“ 陳大文 ",2)
子字串 sub–string
ASCII CharChar ASCII="abc"&"xyz"
true or false 是否空白
05
aabcabcd陳大
A65abcxyz
15函數 Functions
30. 從中間取字串的部分 =MID( 字串 , 開始 , 字數 )
=MID("abcde",2,3)=MID("abcde",9,2) empty string
31. 從右面取字串的部分=RIGHT("abcde",3)=RIGHT("abcde")
32. 轉小寫、一般、大寫、去除多餘空白=LOWER("chAN tai MAN")=PROPER("chAN tai MAN")=UPPER("chAN tai MAN ")=TRIM(" abc def ")
去除多餘空白 spaces
bcd
cdee
小寫一般大寫
chan tai manChan Tai ManCHAN TAI MANabc def
16函數 Functions
33. 找尋 ( 目標，搜索範圍，開始位置 )
=FIND("M","Miriam McGovern")=FIND("m","Miriam McGovern")=FIND("M","Miriam McGovern",3)
A1 零件及代號2 Ceramic Insulators #124–TD45–873 Copper Coils #12–671–67724 Variable Resistors #116010
34. MID+FIND
=MID(A2,1,FIND(" #",A2,1)–1)=MID(A3,1,FIND(" #",A3,1)–1)=MID(A4,FIND(" #",A4,1),10)
168
Ceramic InsulatorsCopper Coils #116010
case sensitivewithout wildcards
191
17函數 Functions
35. 比較字符串=EXACT("abc","abc")=EXACT("abc","ABC")
36. 格式輸出=TEXT(2.715, "$0.00")=TEXT("1991/4/15","mmmm dd, yyyy")=TEXT(9,"00")&":"&TEXT(5,"00")
True 真 = 相同False 假 = 不同
$2.72April 15, 199109:05
37. 字符串轉數值=VALUE("$1,000")=VALUE("1900/2/1")=VALUE("12:00:00")=VALUE("16:48:00")–VALUE("12:00:00")
100032 1/1/1900+320.5 0.5 days0.2 0.2 days
=4 hrs 48 min
18函數 Functions
A1 Margin2 Profit Margin
38. 找尋 ( 目標，搜索範圍，開始位置 )=SEARCH("e","Statements",6)=SEARCH("E","Statements",6)=SEARCH(A1,A2)=SEARCH("ch*n", "Cheong")
7781
38. SEARCHnon-case sensitivewith wildcards(?*)
=REPLACE("Profit Margin", 8, 6, "Amount")=REPLACE(A2, 8, 4, "Ga")=REPLACE(A2, SEARCH(A1,A2), LEN(A1), "Loss")
Profit AmountProfit GainProfit Loss
39. 找尋取代 (old-text ，開始位置，長度 n ， new-text)
=CHOOSE(3,A3,A4,A5,A6)=SUM(A2:CHOOSE(2,A3,A4,A5))=SUM(CHOOSE(2,A1:A2,A3:A4,A5:A6))
40. 選擇 (n, 選項 1, 選項 2,…)A5=SUM(A2:A4)=SUM(A3:A4)
33. FINDcase sensitive
without wildcards
19函數 Functions
41. 邏輯表 Truth table
AND true falsetruefalse
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))
(1<10) and (2<10)
(1>10) or (2<10)
negation
NOTtruefalse
OR true falsetruefalse
true falsefalse false
true truetrue false
falsetrue
truefalsefalsetruetruefalsefalsetruetrue
20函數 Functions
43. 潤年測試 Leap Year Test
A B C1 1980 Leap Year ?
23
潤年二月=IF(OR(B1,AND(B2,B3)),29,28)
44. TRUE / FALSE
=TRUE() TRUE=FALSE() FALSE
=MOD(A1,400)=0=MOD(A1,4)=0=MOD(A1,100)<>0
=OR(B1,AND(B2,B3))
29 =B1 or (B2 and B3)
=OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0))
21函數 Functions
A B C1 4 小於或等於 Frequency2 73 114 165 166 177 208 219 2110 2411 27
45. 統計資料=MEDIAN(A1:A50) 中位數=MODE(A1:A50) 出現次數最多=FREQUENCY(A1:E10,10) 數值≤ 10 出現次數
=FREQUENCY(A$1:A$50,B2)
53.55312
累積頻數9192939495969798999
26
1215212936444850
另外還有Array Formula
{= FREQUENCY(A1:A50,B2:B11)}
http://www.youtube.com/watch?v=CGLbs7wtZQw
22函數 Functions
46. 上捨入 CEILING( 數值，準確至最接近的 )
=CEILING(169.65,1) 準確至 1=CEILING(169.65,2) 準確至 2=CEILING(169.65,0.1) 準確至 0.1=CEILING(169.45,0.5) 準確至 0.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
170170169.7169.5
169168169.6169
23函數 Functions
49. LOG(number,base 基數 )
=LOG(5,10) 10x=5, x=?=LOG(81,3) 3x=81=LOG10(5) 10x=5=EXP(3) e³=LN(20.08553692) ex=20.08553692
48=PRODUCT(A1:A5) =A1*A2*A3*A4*A5=ODD(10.6) 最接近的單數=EVEN(10.6) 最接近的雙數=ROMAN(2010) 羅馬數字=PI()
=FACT(4) 4! = 1234
1112MMX3.14...24
0.6989740.6989720.085536923
24函數 Functions
A B C D1 Date Value2 2000 08/01/2000 10.5 20003 2003 05/12/2003 7.24 2000 03/12/2000 1005 2001 07/30/2001 5.46 2000 02/28/2000 8.1
50. SUMIF (range 範圍 1,criteria 條件 ,range 範圍 2)
=SUMIF(A2:A6,2000,C2:C6) =10.5+100+8.1=SUMIF(A:A,D2,C:C) ditto 同上=SUMIF(A2:A6, ">=2001", C2:C6) =7.2+5.4
118.6118.612.6
25函數 Functions
51=SUBSTITUTE(A1,"er","or",1) 把 A1 內第 1 個 "er" 取代
為 "or"=FIXED(1234,1,TRUE) 1 位小數，沒有 (,)=FIXED(1234,2,FALSE) 2 位小數，有 (,)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")
53. T 只顯示文字 =T(56)=T("56") 56=T("56 ABC") 56 ABC
"Acer"Acor1234.01,234.00
$7,678.87767905123.71,2772003/12/31Dec 31, 2003
26函數 Functions
54=CLEAN(A10)=REPT("ab",3)=CONCATENATE(A1,"/",B1,"/",C1)=C1&"–"&B1&"–"&A1=DOLLAR(A2+A3,2)
55. SUBTOTAL( 方法 1-11 ，範圍 )1 2 3 4 5 6
average count counta max min product7 8 9 10 11
stdev stdevp sum var varp
A B C1 25 12 20052 10003 345
清除 A10 內所有非列印字符重複 3 次 "ababab"合併文字 "25/12/2005"合併文字 "2005-12-25""$1,345.00"
=SUBTOTAL(1,A1:A10)
27函數 Functions
56=PERCENTILE(A1:A10,0.5) median (50%)=PERCENTILE(A1:A10,0.75) 75%=QUARTILE(A1:A10,n) n=04=STDEV(A1:A10) 標準差 standard deviation=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)
28函數 Functions
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.000% ##0.0E+00.00% # ?/?0.00E+00 # ??/??
29函數 Functions
"US$"#,##0_);("US$"#,##0) "US$"#,##0_);[ 紅色 ]("US$"#,##0)"US$"#,##0.00_);("US$"#,##0.00)
"US$"#,##0.00_);[ 紅色 ]("US$"#,##0.00)
d–mmm d–mmmm–yymmm–yy m/d/yye" 年 "m" 月 "d" 日 " e/m/dyyyy" 年 "m" 月 "d" 日 " yyyy/m/dhh" 時 "mm" 分 " hh:mmhh" 時 "mm" 分 "ss" 秒 " hh:mm:ss上午 / 下午 hh" 時 "mm" 分 " hh:mm AM/PM上午 / 下午 hh" 時 "mm" 分 "ss" 秒 "
hh:mm:ss AM/PM
yyyy/m/d hh:mmmm:ss @mm:ss.0 [h]:mm:ss
60. Custom Format: ( 自訂格式 )
30函數 Functions
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
