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

1.絕對值(|x|)2.整數=INT(18/5)=INT(3.65)=INT(–4.3)33–53.去除尾數(小數)=TRUNC(3.65)=TRUNC(–4.3)=TRUNC(123.456)=TRUNC(123.456,1)=TRUNC(123.456,2)=TRUNC(129.456,–1)3–4123123.4123.45120truncate截尾=INT(123.456)準確至1位小數(0.1)準確至2位小數(0.01)準確至十位(10)=ABS(1–4)=ABS(–2)3#NAME?Error-noblank函數Functions14.餘數(a%b)5.四捨五入=MOD(19,4)=MOD(–15,4)=MOD(15,–4)=MOD(–15,–4)31–1–3餘數remainderof19/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.平方根=SQRT(16)=SQRT(–16)4#NUM!Error7.次方=POWER(2,3)=POWER(3,2)=POWER(2,–3)8=2³ 三次方9=3² 二次方0.125=2–38.隨機數=RAND()=RAND()*100=INT(RAND()*100)=INT(RAND()*6)+10..0.9990..99.9990–991–6n=rand()%100;n=rand()%6+1;函數Functions39.平方和(a²+b²)=SUMSQ(3,4)=SUMSQ(10)=SUMSQ(A1:A3)25=3²+4²100=10²35=3²+5²+1²ABCDE13TRUE25103110.總和=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由大至小descending1同上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非空白non–blank71空白blankcells=COUNT(E1:E7)=COUNT(E1:E3)=COUNT(E1:E7,2)=COUNTA(E1:E7)=COUNTA(E1:E7,2)=COUNTBLANK(E1:E7)函數Functions6ABCDE1applesorangespeachesapples=IF(C3<50,"",C3)232547586=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函數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紫**SortedList19.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:B7isunsortedorangeorangeviolet#N/A橙紅#N/A函數Functions920Lookup=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在1atm壓力下的空氣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.1notfound偵錯設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.2days=4hours48minutes)=NOW()=DATE(2007,12,25)=HOUR(0.2)=MINUTE(0.2)=WEEKDAY(TODAY(),2)=1*2+2*3+3*1=2*3+1*2dateandtimeyyyy,mm,dd小時hours分鐘minutes(sunday)mon(1)–sun(7)118242007/9/123:452007/12/254487函數Functions1327.字符串String=CHAR(65)=CODE("ABC")=CONCATENATE("abc","xyz")=ISBLANK(A2)28.字符串長度Stringlength=LEN("")=LEN("abcde")29.從左面取字串的部分=LEFT("abcde")=LEFT("abcde",3)=LEFT("abcde",LEN("abcde")–1)=LEFT(“陳大文",2)子字串sub–stringASCIICharCharASCII="abc"&"xyz"trueorfalse是否空白05aabcAbcd陳大A65abcxyz函數Functions1430.從中間取字串的部分=MID(字串,開始,字數)=MID("abcde",2,3)=MID("abcde",9,2)emptystring31.從右面取字串的部分=RIGHT("abcde",3)=RIGHT("abcde")32.轉小寫、一般、大寫、去除多餘空白=LOWER("chANtaiMAN")=PROPER("chANtaiMAN")=UPPER("chANtaiMAN")=TRIM("abcdef")去除多餘空白spacesbcdcdee小寫一般大寫chantaimanChanTaiManCHANTAIMANabcdef函數Functions1533.找尋(目標，搜索範圍，開始位置)=FIND("M","MiriamMcGovern")=FIND("m","MiriamMcGovern")=FIND("M","MiriamMcGovern",3)A1零件及代號2CeramicInsulators#124–TD45–873CopperCoils#12–671–67724VariableResistors#11601034.MID+FIND=MID(A2,1,FIND("#",A2,1)–1)=MID(A3,1,FIND("#",A3,1)–1)=MID(A4,1,FIND("#",A4,1)–1)168CeramicInsulatorsCopperCoilsVariableResistorscasesensitivewithoutwildcards函數Functions1635.比較字符串=EXACT("abc","abc")=EXACT("abc","ABC")36.格式輸出=TEXT(2.715,"$0.00")=TEXT("1991/4/15","mmmmdd,yyyy")=TEXT(9,"00")&":"&TEXT(5,"00")True真相同False假不同$2.72April15,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.5days0.20.2days=4hrs48min函數Functions17A1Margin2ProfitMargin38.找尋(目標，搜索範圍，開始位置)=SEARCH("e","Statements",6)=SEARCH("E","Statements",6)=SEARCH(A1,A2)778non-casesensitivewithwildcards=REPLACE("ProfitMargin",8,6,"Amount")=REPLACE(A2,8,6,"Amount")=REPLACE(A2,SEARCH(A1,A2),LEN(A2),"Amount")=SEARCH("ch*n","Cheong")ProfitAmountProfitAmountProfitAmount139.找尋(目標，搜索範圍，開始位置)=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.邏輯表TruthtableANDtruefalsetruefalse42.邏輯運算子Logicaloperators=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)negationNOTtruefalseORtruefalsetruefalsetruefalsefalsefalsetruetruetruefalsefalsetruetruefalsefalsetruetruefalsefalsetruetrue函數Functions1943.潤年測試LeapYearTestABC11980LeapYear?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=B1or(B2andB3)=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)10x=5=LOG(81,3)3x=81=LOG10(5)10x=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,"mmmdd,yyyy")53.T只顯示文字=T(56)=T("56")56=T("56ABC")56ABC"Acer"Acor1234.01,234.00$7,678.87767905123.71,2772003/12/31Dec31,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)CombinationnCr=PERMUT(n,r)PermutationnPr57.INTERCEPTy-intercept(whenx=0)=INTERCEPT(y-values,x-values)=INTERCEPT(A1:A5,B1:B5)=INDIRECT(A1)函數Functions2760.CustomFormat:(自訂格式)G/通用格式00.00#,##0#,##0.00_–*#,##0_–;–*#,##0_–;_–*"–"_–;_–@_–_–*#,##0.00_–;–*#,##0.00_–;_–*"–"??_–;_–@_–_–$*#,##0_–;–$*#,##0_–;_–$*"–"_–;_–@_–_–$*#,##0.00_–;–$*#,##0.00_–;_–$*"–"??_–;_–@_–#,##0;–#,##