Excel 2010

Size: px

Start display at page:

Download "Excel 2010"

扬钮
5 years ago
Views:

1 1. 絕對值 ( x ) =ABS(1 4) =ABS ( 2) 3 #NAME? Error - no blank 2. 整數 ( 較小 ) =INT(18/5) =INT(3.65) =INT( 4.3) 3. 去除尾數 ( 小數 ) =TRUNC(3.65) =TRUNC( 4.3) =TRUNC( ) =TRUNC( ,1) =TRUNC( ,2) =TRUNC( , 1) truncate 截尾 =INT( ) 準確至 1 位小數 (0.1) 準確至 2 位小數 (0.01) 準確至十位 (10) 1 4. 餘數 (a%b) =MOD(19,4) =MOD( 15,4) =MOD(15, 4) =MOD( 15, 4) 餘數 remainder of 19/4 5. 四捨五入 ROUND, ROUNDUP, ROUNDDOWN =ROUND( ,0) 準確至 1 ( 個位 ) =ROUND( ,1) 位小數 (0.1) =ROUND( ,2) 位小數 (0.01) =ROUND( , 1) 十位 10 =ROUND( , 2) 百位 100 2

2 6. 平方根 x =SQRT(16) =SQRT( 16) 4 #NUM! Error 7. 次方 n x =POWER(2,3) =POWER(3,2) =POWER(2, 3) 8 =2³ 三次方 9 =3² 二次方 = 隨機數 =RAND() =RAND()*100 =INT(RAND()*100) =INT(RAND()*6) n=rand()%100; n=rand()%6+1; 3 9. 平方和 (a²+b²) =SUMSQ(3, 4) =SUMSQ(10) =SUMSQ(A1:A3) 25 =3²+4² 100 =10² 35 =3²+5²+1² 10. 總和 A B C D E 1 3 TRUE =SUM(A1,B1,2) =SUM(A2:B2, 15) =SUM(3, 2) =SUM("3", 2, TRUE) 5 =3+TRUE( 文字 )+2 30 = =3+2+TRUE(1) 4

3 A B C D 排名次 =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. 平均分 Scores = B1:B5 =AVERAGE(B1:B5) =AVERAGE(Scores) =AVERAGE(B1:B5,5) 11 =( )/ =( )/ 數一數 COUNT ( 數字日期 ) =COUNT(E1:E7) 3 數字 + 日期 =COUNT(E1:E3) 1 date =COUNT(E1:E7, 2) 4 E 1 Sales 2 25/12/ TRUE 7 #DIV/0! 14. 數一數 COUNTA ( 非空白儲存格 ),COUNTBLANK( 空白儲存格 ) =COUNTA(E1:E7) =COUNTA(E1:E7, 2) =COUNTBLANK(E1:E7) 6 非空白 non blank 7 1 空白 blank cells 6

4 A B C D E 1 apples oranges peaches apples =IF(C3<50,"",C3) =IF(C3<50,0,C3) 15. ( ) =COUNTIF(A1:D1,"apples") =COUNTIF(A2:D2,">=55") =COUNTIF(A2:D2,">"&B2) 2 "apples" =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 7 A B C D ,000 7, ,000 14, ,000 21, ,000 28, B2:B5 A2:A =SUMIF(A2:A5,">160000",B2:B5) 63, max min =MAX(D1:D5) =MAX(D1:D5,30) =MIN(D1:D5) =MIN(D1:D5, 0) Functions 8

5 A B C D 1 Frequency Color 顏色 red 紅 orange 橙 yellow 黃 green 綠 blue 藍 ** 已排序 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 orange violet #N/A 橙紅 #N/A B2:B7 is unsorted 9 20 Lookup =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 汽車零件存貨 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) #N/A 近似準確近似準確近似準確 10

6 A B C D 1 在 1 atm 壓力下的空氣 2 密度黏度溫度 3 (kg/m 3 ) (kg/m*s)*10 5 ( C) 設 Range = A4:C 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(...)) #N/A 1.71 最接近 =TRUE default = TRUE 準確 =FALSE 0.1 not found 偵錯 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 最大 12

7 A B C D E 先乘, 然後再加總 =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 26. 日期時間小時分鐘 (0.2 days = 4 hours 48 minutes) =NOW() =DATE(2007,12,25) =HOUR(0.2) =MINUTE(0.2) 2007/9/1 23: /12/ date and time yyyy,mm,dd 小時 hours 分鐘 minutes =WEEKDAY(TODAY(),2) 7 (sunday) mon(1) sun(7) 字符串 String =CHAR(65) =CODE("ABC") =CONCATENATE("abc","xyz") =ISBLANK(A2) 28. 字符串長度 String length =LEN("") =LEN("abcde") A 65 abcxyz true or false 0 5 ASCII Char Char ASCII ="abc"&"xyz" 是否空白 29. 從左面取字串的部分子字串 sub string =LEFT("abcde") =LEFT("abcde",3) =LEFT("abcde",LEN("abcde") 1) =LEFT( 陳大文 ",2) a abc Abcd 陳大 14

8 30. 從中間取字串的部分 =MID( 字串, 開始, 字數 ) =MID("abcde",2,3) =MID("abcde",9,2) bcd 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 ") 去除多餘空白 spaces chan tai man Chan Tai Man CHAN TAI MAN abc def 找尋 ( 目標, 搜索範圍, 開始位置 ) =FIND("M","Miriam McGovern") =FIND("m","Miriam McGovern") =FIND("M","Miriam McGovern",3) case sensitive without wildcards A 1 零件及代號 2 Ceramic Insulators #124 TD Copper Coils # Variable Resistors # 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 16

9 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, : 字符串轉數值 =VALUE("$1,000") =VALUE("1900/2/1") =VALUE("12:00:00") =VALUE("16:48:00") VALUE("12:00:00") /1/ days days =4 hrs 48 min 17 non-case sensitive with wildcards 38. 找尋 ( 目標, 搜索範圍, 開始位置 ) =SEARCH("e","Statements",6) =SEARCH("E","Statements",6) =SEARCH(A1,A2) 39. 找尋 ( 目標, 搜索範圍, 開始位置 ) =REPLACE("Profit Margin", 8, 6, "Amount") =REPLACE(A2, 8, 6, "Amount") =REPLACE(A2, SEARCH(A1,A2), LEN(A2), "Amount") =SEARCH("ch*n", "Cheong") 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)) A 1 Margin 2 Profit Margin Profit Amount Profit Amount Profit Amount 1 A5 =SUM(A2:A4) =SUM(A3:A4) 18

10 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) =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 false true true false false true true (1<10) and (2<10) (1>10) or (2<10) negation 潤年測試 Leap Year Test A B C =MOD(A1,400)=0 Leap Year? 2 3 =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() =FALSE() TRUE FALSE 20

11 A B C 1 4 小於或等於 Frequency =FREQUENCY(A$1:A$50,B2) 累積頻數 45. 統計資料 =MEDIAN(A1:A50) =MODE(A1:A50) =FREQUENCY(A1:E10,10) 中位數出現次數最多數值 10 出現次數上捨入 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 準確至

12 48 =PRODUCT(A1:A5) =ODD(10.6) =EVEN(10.6) =ROMAN(2010) =PI() =FACT(4) 49. LOG(number,base 基數 ) MMX =A1*A2*A3*A4*A5 最接近的單數最接近的雙數羅馬數字 4! = =LOG(5,10) x =5 =LOG(81,3) 4 3 x =81 =LOG10(5) x =5 =EXP(3) e³ =LN( ) 3 e x = A B C D 1 Date Value /01/ /12/ /12/ /30/ /28/ 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) 12.6 =

13 51 "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") =TEXT(A1, "0") =TEXT(5, "00") =TEXT(A2, "0.0") =TEXT(1277, "#,##0") =TEXT(C1, "yyyy/mm/dd") =TEXT(C1, "mmm dd, yyyy") $7, , /12/31 Dec 31, T 只顯示文字 =T(56) =T("56") 56 =T("56 ABC") 56 ABC A B C =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, 範圍 ) average count counta max min product stdev stdevp sum var varp 26

14 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) 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 #??/?? 28

15 60. Custom Format: ( 自訂格式 ) "US$"#,##0_);("US$"#,##0) "US$"#,##0.00_); ("US$"#,##0.00) d mmm mmm yy "US$"#,##0_);[ 紅色 ]("US$"#,##0) "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 自定清單 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 30

16 condition ex.docx Q1 Q2 Q3.

17 condition ex.docx Q4 Q5 Q6

18 string ex.docx Q1 Q2 Q3 Q4

19 string ex.docx Q5 Q6 Q7

20 string ex.docx Q8 Q9 Q10 Q11

21 string ex.docx Q12 Q13 Q14

22 string ex.docx Q15 Q16 22

sections, A and B. There are 10 questions in each section.

23 資料 ( 右 ) 規劃求解答題數目每題分數每題需時 no.of Qn attempted Marks/Qn Time/Qn min max 甲部 section A 1 7 points 6 min 4 10 乙部 section B 2 8 points 7 min 4 10 Total 23 points 20 min Max 90 min You are about to take a test which contains two sections, A and B. There are 10 questions in each section. The marks and time required for each question in the two sections are given in the following table: You must attempt at least 4 questions from each section and the maximum time available for the test is 90 minutes. In order to maximize your score, how many questions in each section should you do? What is the maximum score? 一個測驗分為甲乙兩部, 各有 10 題甲部 : 答對 1 題得 7 分, 每題需時 6 分鐘 ; 乙部 : 答對 1 題得 8 分, 每題需時 7 分鐘 ; 考生在甲乙兩部, 必須各答 4 題或以上, 測驗時限為 90 分鐘求最高分數多少? 目標儲存格 D7 ( 最大值 ) 變數儲存格 C5:C6 限制式 : C5:C6 <= G5:G6 C5:C6 >= F5:F6 C5:C6 = integer E7 <= E8 選項 : 誤差 0% 23

24 規劃求解 Linear Programming: Q1 某團體利用飛機把衣服藥物送到地震災區 (1) 衣服放箱藥物放盒 (2) 每個箱和盒皆佔 0.9 立方米 (3) 一箱衣服重 27kg 一盒藥物重 54kg 飛機可載重 21,600 kg, 容量 540 立方米一箱衣服可幫助 12 家庭一盒藥物可幫助 18 家庭求最多可幫助多少個家庭 Q2 售價 : 單層巴士 $20; 雙層巴士 $45 用最多 $400 買不多於 10 輛巴士求各買巴士多少? 最多支出多少? Q3 游泳池可容納最多 250 成人及 250 小童, 且不可多於 450 人入場費 : 成人 $20 小童 $15 每兩個小童必須有一個成人陪同求入場費的最大值 Q4 (1) 造 1kg 蛋糕 : 成本 $60; 造 1kg 餅乾 : 成本 $30 製餅師傅最多花 $1,200 (2) 製成的蛋糕餅乾不可超過 30kg (3) 利潤為 : 蛋糕 $15/kg 餅乾 $7/kg (4) 最少要造 5kg 蛋糕及 5kg 餅乾求最大利潤 Q5 把 350 個蘋果放進 x 個盒及 y 個籃內一個盒可放 25 個蘋果而一個籃可放 10 個蘋果盒的數目不可多於籃的數目成本 : 盒 $12; 籃 $6 求 x 和 y ( 最低成本多少?) 24

25 Q6 醫院為每個病人提供的食物須符合以下要求 : 碳水化合物 : 至少 120 單位蛋白質 : 纖維 : 至少 40 單位至少 30 單位食物碳水化合物蛋白質纖維成本 $/kg P 120 units 20 units 10 units $60 Q 40 units 20 units 30 units $30 求最低成本多少? Q7 生產商每天生產 x 個雪櫃及 y 座電視 (1) 8<=x<=14 (2) 5<=y<=10 (3) x+y 不超過 20 利潤為 : 雪櫃 $1600 及電視 $2400 求最大利潤 Q8 產品 A,B,C 是由成份 M 和成份 N 製成 (1) 每噸 M 可造 : 4000 件 A, 件 B, 6000 件 C (2) 每噸 N 可造 : 6000 件 A, 5000 件 B, 3000 件 C 工廠接獲訂單 : 24,000 件 A, 60,000 件 B, 24,000 件 C 成本 : M( 每噸 $4,000); N( 每噸 $3,000) 要完成訂單, 最低成本多少? Q9 農夫想租田種生果及稻米租金 : 生果田 ( 每平方米 $300) 及稻米田 ( 每平方米 $100) 地主要求 : 每租一平方米生果田, 必須至少租三平方米稻米田利潤 : 種生果 $600/ 平方米 ; 種稻米 $150/ 平方米若租金不超過 $9000, 求最大利潤 25

26 Q10 超級市場售賣橙汁及蘋果汁超級市場經理訂了橙汁的數量, 至少是蘋果汁數量的兩倍超級市場只可容納 450 瓶果汁利潤 : 橙汁 $2.5; 蘋果汁 $3.0 求最大利潤 Q11 機器 A: 每小時可生產 120 nuts 及 100 bolts 運作成本 : 每小時 $150 機器 B: 每小時可生產 60 nuts 及 150 bolts 運作成本 : 每小時 $180 若要完成至少 1200 nuts 及 1500 bolts 機器 A 及 B 各需要運作多少小時? 最低成本多少? Q12 計劃中的新酒店有單人房及雙人房若干 (1) 每 4 間雙人房, 必須有 1 間單人房 (2) 房間總數最多 200 間 (3) 單人房至少 20 間 ; 雙人房最多 120 間每日租金 : 單人房 $800; 雙人房 $1000 求單人房及雙人房各佔多少間, 每日最多可收取租金多少? Answers: Q Q Q Q Q Q Q Q Q Q Q Q

27 Data Pivot Table.docx Data Table 運算列表 1 本金 principal 利率 interest rate 6% =PMT(r%/12,120 期, ) 年期 years 10 期數 months 120 每月供款 repayment -$11,102 5% 6% 7% 8% 原價 price $80 數量 quantity 10 折扣 discount 10% 資料模擬分析運算列表應付 Net Pay 720 $

28 Data Pivot Table.docx Pivot Table 插入樞紐分析表 28

Microsoft Word - Excel函數10-11.doc

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)