Excel Functions

Save this PDF as:
 WORD  PNG  TXT  JPG

Size: px
Start display at page:

Download "Excel Functions"

Transcription

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

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

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

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

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

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

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

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