Excel Functions

Size: px

Start display at page:

Download "Excel Functions"

总郁
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) 百位平方根 =SQRT(16) 4 =SQRT( 16) #NUM! Error 7 次方 (xⁿ) ~pow() x =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 10 總和 A B C D 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) A B C D 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 A B C D 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 A B C D 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 A B C D 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 A B C D 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 A B C D 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 大 A B C D 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) =MID("abcde",9,3) 31 從右面取字串的部分 bcd empty string =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 A B C D 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 找尋 ( 目標, 搜索範圍, 開始位置 ) wildcards *? =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 45 統計資料 A B C D 1 4 小於或等於 Frequency =FREQUENCY(A$1:A$50,B2) =FREQUENCY(A$1:A$50,B3) {=FREQUENCY(A1:A50,B2:B11)} =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 A B C D 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") 54 =CLEAN(A1) =REPT("ab",3) "56 ABC" 清除 A1 內所有非列印字符 "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%) =COMBIN(n,r) =PERCENTILE(A1:A10,0.75) 75% Combination ncr =QUARTILE(A1:A10,n) n=0..4 =PERMUT(n,r) =STDEV(A1:A10) 標準差 Permutation npr Mr. CY Szeto page 8

9 57-70 =REPT("*-", 3) 等於 "*-*-*-" =A1-INT(A1) 小數部分 =RANDBETWEEN(bottom,top) =ASC("EXCEL") 等於 "EXCEL" =SUBSTITUTE("Sales Data", "Sales", "Cost") 等於 "Cost Data" =SUBSTITUTE("Quarter 1, 1991", "1", "2", 1) 等於 "Quarter 2, 1991" =SUBSTITUTE("Quarter 1, 1991", "1", "2", 3) 等於 "Quarter 1, 1992" =ROWS(A1:C4) 等於 4 =COLUMNS(A1:C4) 等於 3 =ROWS(A:A) 等於 (= ) =COLUMNS(1:1) 等於 (= ) =ROW(A3) 等於 3 =COLUMN(A3) 等於 1 =ROW(C5) 等於 5 =COLUMN(C5) 等於 3 =TEXT("1998/4/16", "dddd") 等於 Thursday =TEXT(TODAY(), "dddd") =TEXT("1998/4/16", "aaaa") 等於星期四 =TEXT("1991/4/15", "mmmm dd, yyyy") 等於 "April 15, 1991" =TEXT(TODAY(), "mmmm dd, yyyy") =TEXT(2.715, "$0.00") 等於 "$2.72" =TEXT(TIME(23,18,14),"h:mm:ss AM/PM") 等於 "11:18:14 PM" =TEXT( ,"#,##0.00") 等於 "12,345.68" =WEEKDAY("1998/2/14") dow Sun=1 =WEEKDAY("1998/2/14",2) dow Mon=1 =EOMONTH(start_date,months) =EOMONTH(DATEVALUE("1998/01/01"), 1) 等於 1998 年 02 月 28 日 =EOMONTH("1998/01/01",-1) 等於 1997 年 12 月 31 日 =EOMONTH("2000/01/30",3) 等於 2000 年 04 月 30 日 =WORKDAY(start_date,days,holidays) 工作天 =COMBIN(8,2) 等於 8C2=28 =PERMUT(8,2) 等於 8P2=56 =CONVERT(1.0, "lbm", "kg") 等於 =CONVERT(68, "F", "C") 等於 20 =CONVERT(2.5, "ft", "sec") 等於 #N/A =GCD(5, 2) 等於 1 =FIXED(number, decimals,no_commas) =GCD(24, 36) 等於 12 =FIXED( , 1) 等於 "1234.6" =GCD(7, 1) 等於 1 =FIXED( , -1) 等於 "1230" =GCD(5, 0) 等於 5 =FIXED( , -1) 等於 "-1230" =LCM(5, 2) 等於 10 =FIXED(44.332) 等於 "44.33" =LCM(24, 36) 等於 72 =DATE(year, month, day) Mr. CY Szeto page 9

10 =DATEDIF(start_date,end_date,unit) =DATEDIF("2001/1/1","2003/1/1","Y") 等於 2, 即兩個整年 =DATEDIF("2001/6/1","2002/8/15","D") 等於 440 天 Unit Return value Unit 傳回 "Y" 週期中的整年數 "MD" 天數的差忽略日期中的月和年 "M" 週期中的整月數 "YM" 月數的差忽略日期中的日和年 "D" 週期中的天數 "YD" 天數的差忽略日期中的年 A B C D 1 房屋價值銷售佣金 2 100,000 7,000 Apples Lemons 3 200,000 14,000 Bananas Pears 4 300,000 21, ,000 28,000 B2 =SUMIF(A2:A5, >160000,B2:B5) 等於 $63,000 =SUMIF(A2:A5, <220000,B2:B5) 等於 $21,000 =INDEX(A1:B5,2,2) 等於 7,000 =INDEX(C2:D3,2,2) 等於 Pears =INDEX(A1:B5,3,1) 等於 200,000 =INDEX(C2:D3,2,1) 等於 Bananas =INDIRECT(D5) 等於 7,000 =MEDIAN(1, 2, 3, 4, 5) 等於 3 =INDIRECT("A5") 等於 400,000 =MEDIAN(1, 2, 3, 4, 5, 6) 等於 3.5 =POWER(5,2) 等於 25 =POWER(98.6,3.2) 等於 =POWER(4,5/4) 等於 HYPERLINK(" report.xls", "Click for report") HYPERLINK("'[ report.xls]annual'!f10", D1) HYPERLINK("'[ Quarter'!DeptTotal", " 按一下以查看第一季的部門總和 ") HYPERLINK("'[ "Quarterly Profit Report") HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5) HYPERLINK("D:\FINANCE\1stqtr.xls", H10) HYPERLINK("[C:\My Documents\Mybook.xls]Totals") HYPERLINK("Macintosh HD:Budget Reports:First Quarter","Click here") HYPERLINK("[Budget]June!E56",E56) Mr. CY Szeto page 10

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

11 規劃求解 Linear Programming: 資料 ( 右 ) 規劃求解一個測驗分為甲乙兩部, 各有 10 題甲部 : 答對 1 題得 7 分, 每題需時 6 分鐘 ; 乙部 : 答對 1 題得 8 分, 每題需時 7 分鐘 ; 考生在甲乙兩部, 必須各答 4 題或以上, 測驗時限為 90 分鐘求最高分數多少? 答題數目每題分數每題需時 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 目標儲存格 D7 ( 最大值 ) 變數儲存格 C5:C6 限制式 : C5:C6 = integer C5:C6 <= G5:G6 C5:C6 >= F5:F6 E7 <= E8 選項 : 誤差 0% Mr. CY Szeto page 11

12 Q1 某團體利用飛機把衣服藥物送到地震災區 (1) 衣服放箱藥物放盒 (2) 每個箱和盒皆佔 0.9 立方米 (3) 一箱衣服重 27kg 一盒藥物重 54kg 飛機可載重 21,600 kg, 容量 540 立方米一箱衣服可幫助 12 家庭一盒藥物可幫助 18 家庭求最多可幫助多少個家庭 Q4 (1) 造 1kg 蛋糕 : 成本 $60; 造 1kg 餅乾 : 成本 $30 製餅師傅最多花 $1,200 (2) 製成的蛋糕餅乾不可超過 30kg (3) 利潤為 : 蛋糕 $15/kg 餅乾 $7/kg (4) 最少要造 5kg 蛋糕及 5kg 餅乾求最大利潤 Q2 售價 : 單層巴士 $20; 雙層巴士 $45 用最多 $400 買不多於 10 輛巴士求各買巴士多少? 最多支出多少? Q3 游泳池可容納最多 250 成人及 250 小童, 且不可多於 450 人入場費 : 成人 $20 小童 $15 每兩個小童必須有一個成人陪同求入場費的最大值 Q6 醫院為每個病人提供的食物須符合以下要求 : 碳水化合物 : 至少 120 單位蛋白質 : 至少 40 單位纖維 : 至少 30 單位食物碳水化合物蛋白質纖維成本 $/kg P 120 units 20 units 10 units $60 Q 40 units 20 units 30 units $30 求最低成本多少? Q5 把 350 個蘋果放進 x 個盒及 y 個籃內一個盒可放 25 個蘋果而一個籃可放 10 個蘋果盒的數目不可多於籃的數目成本 : 盒 $12; 籃 $6 求 x 和 y ( 最低成本多少?) Answers: Q Q Q Q Q Q Q Q Q Q Q Q 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, 求最大利潤 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 求單人房及雙人房各佔多少間, 每日最多可收取租金多少? Mr. CY Szeto page 12

$1. 打開 Y:\F4-ICT\Class-List.xlsx 工作表 :school school-2016.txt 2. 文字檔 Y:\F4-ICT\school2016.$

13 1. 打開 Y:\F4-ICT\Class-List.xlsx 工作表 :school school-2016.txt 2. 文字檔 Y:\F4-ICT\school2016.txt 班號英文姓名中文姓名性別學社 1A01 Chan Tai Man 陳大文 M S 1A02 Chan Siu Ming 陳小明 M L 工作表 :namelist 建立學生名單該班人數 G40: =vlookup( ) 例如 : 32 人 Mr. CY Szeto page 13

=vlookup(b3,school,3,false) =if(a3>g$40,

14 =vlookup(b3,school,3,false) =if(a3>g$40, "",G$1&text(A3,"00")) =vlookup(g1,teacher,2,false) 4. 工作表 :namelist ( 自動格式化 B2:G39) 常用 =row() 顯示行號 =mod(10,2) 計算 10%2 資料移除重複 ( 班別 1A,1B, ) 範圍 :school (B2:F756) 範圍 :teacher (H2:J28) Mr. CY Szeto page 14

15 G1 資料驗證清單 (1A,1B,1C,1D,,6E) 資料來源 :=school!$h$2:$h$28 午膳訂購 :school(raw_data!a2:w730) RegNo 班別學號姓名 A 1 陳政 A A X X C A C X X A B X X A D A X X B A 2 鄭祺 A D B D B A A B A A B A B B D B B B A A 3 張天 A A A B B A B C A C B B B B C A A A B A 4 蔡樂 A B B C A D C B A B B D C B X B X X D A 5 徐安 A A B C B A C B A B C B A B A B B C A A 6 鍾丞 A B D B A C A C A A B B B B A B C A A A 7 樊彥 A B B C A B C C A D D B B B A B A C B A 8 符泓 A A X X A A C D X A X C A D C A X A C A 9 何曉 A B C B A B A C B Ag2 B C B A B A C A B =MATCH(DAY(TODAY()),raw_data!A1:AB1,0) A B C D E F G H RegNo Count Class No Name Meal 6/10/ A 23 包懿 A =today() A 1 陳政 A A 23 包懿 A A 29 楊芷 B B 19 鍾嘉 B B 23 林思 B =COUNTIF(A:A,A2) =VLOOKUP($A2,school,4,FALSE) Mr. CY Szeto page 15

16 String Q1 Q2 Q3. Mr. CY Szeto page 16

17 Q4 Q5 Q6 Mr. CY Szeto page 17

18 Q1 Q2 Mr. CY Szeto page 18

19 Q3 Q4 Q5 Mr. CY Szeto page 19

20 Q6 Q7 Q8 Mr. CY Szeto page 20

21 Q9 Q10 Q11a Mr. CY Szeto page 21

Q11b Q11c A B C D E F G H I 1 班別班號姓名性別學社 L 2 1A 1A01 陳元 M

6 7 1A 1A06 鍾謙 M 8 1A 1A07 樊廷 M 8 9 1A 1A08 符曜 M [G2]

22 Q11b Q11c A B C D E F G H I 1 班別班號姓名性別學社 L 2 1A 1A01 陳元 M 2 =small(g$2:g$730,1) 1A01 3 1A 1A02 鄭祺 M =small(g$2:g$730,2) 1A05 4 1A 1A03 張賜 M =small(g$2:g$730,3) 1A07 5 1A 1A04 蔡樂 M : 6 1A 1A05 徐安 M 6 7 1A 1A06 鍾謙 M 8 1A 1A07 樊廷 M 8 9 1A 1A08 符曜 M [G2] =IF(E2=G$1,row(),"") [I2] =INDEX(B:B,H2) [H2] =small(g$2:g$730,row()-1) Mr. CY Szeto page 22

Data Table 運算列表 1 本金 principal 1000000 利率 interest rate 6% =PMT(r%/12,120 期,1000000) 年期 years 10 期數 months 120 每月供款 repayment -$11,102 5% 6% 7% 8% 2-43871 -44321-44773 -45227 4-23029 -23485-23946

23 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 $ Mr. CY Szeto page 23

24 Pivot Table 插入樞紐分析表 Mr. CY Szeto page 24

25 57 =INTERCEPT(y-values,x-values) =INTERCEPT(A1:A5,B1:B5) =INDIRECT(A1) 60 Custom Format: ( 自訂格式 ) y-intercept (when x=0) 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 25

$1. 打開 Y:\F4-ICT\Seating-Plan-F4.xlsx 工作表 :school 2. 文字檔 Y:\F4-ICT\school-s.$

26 1. 打開 Y:\F4-ICT\Seating-Plan-F4.xlsx 工作表 :school 2. 文字檔 Y:\F4-ICT\school-s.txt 班號 # 英文姓名 # 中文姓名 # 性別 # 學社 1A01#Cheng Chun Ho# 鄭駿浩 #M#M 1A02#Choy Chung Yin# 蔡仲賢 #M#L 工作表 :namelist 4. 工作表 :seat Mr. CY Szeto page 26

27 5. 額外練習 : 利用 MS Word 把文字檔內 # 號轉為 (tab), 再於 Excel 貼上 6. 額外練習 : 製作班分紙 class-list.xlsx 重溫 : =VLOOKUP(,,, FALSE) =IF(A1, "Fail", "Pass") Mr. CY Szeto page 27

數據庫 (SQL 指令建基於 SQL-92 標準 ) 常數運算符 SQL 保留字 TRUE, FALSE +, -, *, /, >, <, =, >=,

AT, CHAR_LENGTH (LEN), LOWER, TRIM, SPACE, SUBSTRING (SUBSTR/MID), UPPER, AS,

SELECT, WHERE 電子試算表常數 TRUE, FALSE 運算符 +, -, *, /, <, >, =, <>, <=, >= 函數 ABS,

LEN, LOWER, MID, PROPER, RIGHT, TEXT, TRIM, UPPER, VALUE, AVERAGE, COUNT,

28 數據庫 (SQL 指令建基於 SQL-92 標準 ) 常數運算符 SQL 保留字 TRUE, FALSE +, -, *, /, >, <, =, >=, <=, <>, %, _, ', AND, NOT, OR ABSOLUTE (ABS), AVG, INT, MAX, MIN, SUM, COUNT, AT, CHAR_LENGTH (LEN), LOWER, TRIM, SPACE, SUBSTRING (SUBSTR/MID), UPPER, AS, BETWEEN, BY, ASC, DESC, DISTINCT, FROM, GROUP, HAVING, LIKE, NULL, ORDER, SELECT, WHERE 電子試算表常數 TRUE, FALSE 運算符 +, -, *, /, <, >, =, <>, <=, >= 函數 ABS, INT, RAND, SQRT, ROUND, AND, NOT, OR, CHAR, CONCATENATE (&), ISBLANK, LEFT, LEN, LOWER, MID, PROPER, RIGHT, TEXT, TRIM, UPPER, VALUE, AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF, MAX, MIN, RANK, SUM, SUMIF, FIND, VLOOKUP, IF Mr. CY Szeto page 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)