24407《校车安全技术条件》(征求意见

1. 絕對值 (|x|) 2. 整數=INT(18/5)=INT(3.65)=INT(4.3)3353. 去除尾數(小數)=TRUNC(3.65)=TRUNC(4.3)=TRUNC(123.456)=TRUNC(123.456,1)=TRUNC(123.456,2)=TRUNC(129.456,1)34123123.4123.45120truncate截尾=INT(123.456)準確至1位小數 (0.1)準確至2位小數 (0.01)準確至十位 (10)=ABS(14)=ABS (2)3#NAME?Error - no blank函數 Functions14. 餘數 (a%b)5. 四捨五入=MOD(19,4)=MOD(15,4)=MOD(15,4)=MOD(15,4)3113餘數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百位 100126125.7125.68130200125125.6125.67120100ROUND, ROUNDUP, ROUNDDOWN函數 Functions26. 平方根x=SQRT(16)=SQRT(16)4#NUM!Error 7. 次方=POWER(2,3)=POWER(3,2)=POWER(2,3)8=2三次方9=3二次方0.125=238. 隨機數=RAND()=RAND()*100=INT(RAND()*100)=INT(RAND()*6)+10 . 0.9990 . 99.9990 991 6nxn=rand()%100;n=rand()%6+1;函數 Functions39. 平方和 (a+b)=SUMSQ(3, 4)=SUMSQ(10)=SUMSQ(A1:A3)25=3+4100=1035=3+5+1ABCDE13TRUE25103110. 總和 =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)函數 Functions4ABCD1710423.571833.597412752211. 排名次 =RANK(數值Num,範圍Ref,次序Order)=RANK(A1, A1:A5, 0)=RANK(A1, A1:A5)=RANK(A1, A1:A5, 1)1=0 由大至小descending 1同上50 由小至大ascending12. 平均分=AVERAGE(B1:B5)=AVERAGE(Scores)=AVERAGE(B1:B5,5)Scores = B1:B511=(10+7+9+27+2)/51110=(10+7+9+27+2+5)/6函數 Functions5E1Sales225/12/19993419522.246TRUE7#DIV/0!13. 數一數 COUNT (數字、日期)3數字+日期1date414. 數一數 COUNTA (非空格，空格)6非空白nonblank71空白blank cells=COUNT(E1:E7)=COUNT(E1:E3)=COUNT(E1:E7, 2)=COUNTA(E1:E7)=COUNTA(E1:E7, 2)=COUNTBLANK(E1:E7)函數 Functions6ABCDE1 applesorangespeachesapples=IF(C350,C3)232547586=IF(C3=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(A289, A,IF(A279, B,IF(A269, C,IF(A259, D, F) ) ) )2如果等如apples2如果大於或等如552如果大於54函數 Functions7ABCD1房屋價值銷售佣金102100,0007,00073200,00014,00094300,00021,000275400,00028,0002=SUMIF(A2:A5,160000,B2:B5)17. 加總B2:B5，如果A2:A5大於16000018. 最大max、最小min=MAX(D1:D5)=MAX(D1:D5,30)=MIN(D1:D5)=MIN(D1:D5, 0)63,000273020函數 Functions8ABCD1FrequencyColor顏色24.1423red紅34.1934orange橙45.1723yellow黃55.7734green綠66.3898blue藍* 已排序77.3134violet紫* Sorted List19. 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函數 Functions920 Lookup=LOOKUP(C,a,b,c,d;1,2,3,4)=LOOKUP(bump,a,1;b,2;c,3)ABCD1AxlesBearingsBolts汽車零件存貨2449357104681121. 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)324近似7準確7近似11準確8近似#N/A準確default=FALSE函數 Functions10ABCD1在1 atm壓力下的空氣2密度黏度溫度3(kg/m3)(kg/m*s)*105(C)40.4573.5550050.5253.2540060.6162.9330070.6752.7525080.7642.5720090.8352.38150100.9462.17100111.091.9550121.291.710設Range = A4:C12函數 Functions1122. VLOOKUP(目標數值，尋找範圍，回傳範圍，近似)=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(.)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函數 Functions12ABCDE11323221323321125. 先乘，然後再加總=SUMPRODUCT(A1:A3,D1:D3)=SUMPRODUCT(A2:B2,A3:B3)=SUMPRODUCT(A1:B3,D1:E3)26. 日期、時間、小時、分鐘 (0.2 days = 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*2date and timeyyyy,mm,dd小時hours分鐘minutes(sunday) mon(1)sun(7)118242007/9/1 23:452007/12/254487函數 Functions1327. 字符串String=CHAR(65)=CODE(ABC)=CONCATENATE(abc,xyz)=ISBLANK(A2)28. 字符串長度 String length=LEN()=LEN(abcde)29. 從左面取字串的部分=LEFT(abcde)=LEFT(abcde,3)=LEFT(abcde,LEN(abcde)1)=LEFT(“陳大文,2)子字串substring ASCII CharChar ASCII=abc&xyztrue or false是否空白05aabcAbcd陳大A65abcxyz函數 Functions1430. 從中間取字串的部分 =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 )去除多餘空白spacesbcdcdee小寫一般大寫chan tai manChan Tai ManCHAN TAI MANabc def函數 Functions1533. 找尋(目標，搜索範圍，開始位置)=FIND(M,Miriam McGovern)=FIND(m,Miriam McGovern)=FIND(M,Miriam McGovern,3)A1零件及代號2Ceramic Insulators #124TD45873Copper Coils #1267167724Variable Resistors #11601034. MID+FIND=MID(A2,1,FIND( #,A2,1)1)=MID(A3,1,FIND( #,A3,1)1)=MID(A4,1,FIND( #,A4,1)1)168Ceramic InsulatorsCopper CoilsVariable Resistorscase sensitive without wildcards函數 Functions1635. 比較字符串=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:0537. 字符串轉數值=VALUE($1,000)=VALUE(1900/2/1)=VALUE(12:00:00)=VALUE(16:48:00)VALUE(12:00:00)1000321/1/1900+320.50.5 days0.20.2 days=4 hrs 48 min函數 Functions17A1Margin2Profit Margin38. 找尋(目標，搜索範圍，開始位置)=SEARCH(e,Statements,6)=SEARCH(E,Statements,6)=SEARCH(A1,A2)778non-case sensitive with wildcards=REPLACE(Profit Margin, 8, 6, Amount)=REPLACE(A2, 8, 6, Amount)=REPLACE(A2, SEARCH(A1,A2), LEN(A2), Amount)=SEARCH(ch*n, Cheong)Profit AmountProfit AmountProfit Amount139. 找尋(目標，搜索範圍，開始位置)=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)函數 Functions1841. 邏輯表Truth tableANDtruefalsetruefalse42. 邏輯運算子Logical operators=AND(110,210,210)=AND(110,210)=OR(110,ABC=abc)=OR(110)=OR(110,210)=NOT(1=1)=NOT(11)=AND(5=5,OR(110,210)(110) and (210) or (210) negation NOTtruefalseORtruefalsetruefalsetruefalsefalsefalsetruetruetruefalsefalsetruetruefalsefalsetruetruefalsefalsetruetrue函數 Functions1943. 潤年測試Leap Year TestABC11980Leap 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)函數 Functions20ABC14小於或等於Frequency2792311196416291251639156174921720592982169369217944102489481127995045. 統計資料=MEDIAN(A1:A50)中位數=MODE(A1:A50)出現次數最多=FREQUENCY(A1:E10,10)數值10出現次數=FREQUENCY(A$1:A$50,B2)53.55312累積頻數函數 Functions2146. 上捨入CEILING(數值，準確至最接近的)=CEILING(169.65,1)準確至1=CEILING(169.65,2)準確至2=CEILING(169.65,0.1)準確至0.1=CEILING(169.45,0.5)準確至0.547. 下捨入FLOOR(數值，準確至最接近的)=FLOOR(169.65,1)準確至1=FLOOR(169.65,2)準確至2=FLOOR(169.65,0.1)準確至0.1=FLOOR(169.45,0.5)準確至0.5170170169.7169.5169168169.6169函數 Functions2249. LOG(number,base基數)=LOG(5,10)10 x=5=LOG(81,3)3x=81=LOG10(5)10 x=5=EXP(3)e=LN(20.08553692)ex=20.0855369248=PRODUCT(A1:A5)=A1*A2*A3*A4*A5=ODD(10.6)最接近的單數=EVEN(10.6)最接近的雙數=ROMAN(2010)羅馬數字=PI()=FACT(4)4! = 12341112MMX3.14.240.6989740.6989720.085536923函數 Functions23ABCD1DateValue2200008/01/200010.520003200305/12/20037.24200003/12/20001005200107/30/20015.46200002/28/20008.150. 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.4118.6118.612.6函數 Functions2451=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 ABCAcerAcor1234.01,234.00$7,678.87767905123.71,2772003/12/31Dec 31, 2003函數 Functions2554=CLEAN(A10)=REPT(ab,3)=CONCATENATE(A1,/,B1,/,C1)=C1&B1&A1=DOLLAR(A2+A3,2)55. SUBTOTAL(方法1-11，範圍)123456averagecountcountamaxminproduct7891011stdevstdevpsumvarvarpABC125122005210003345清除A10內所有非列印字符重複3次 ababab合併文字 25/12/2005合併文字 2005-12-25$1,345.00函數 Functions2656=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 nPr57. INTERCEPT y-intercept (when x=0)=INTERCEPT(y-values,x-values)=INTERCEPT(A1:A5,B1:B5)=INDIRECT(A1)函數 Functions2760. Custom Format: (自訂格式)G/通用格式00.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# ?/?函數 Functions28US$#,#0_);(US$#,#0)US$#,#0_);紅色(US$#,#0)US$#,#0.00_);(US$#,#0.00)US$#,#0.00_);紅色(US$#,#0.00)dmmmdmmmmyymmmyym/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/PMyyyy/m/d hh:mmmm:ssmm:ss.0h:mm:ss60. Custom Format: (自訂格式)函數 Functions2961. 自定清單Auto FillHoroscope?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函數 Functions30