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

函數Functions11.絕對值(|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行车记录仪函數Functions24.餘數(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函數Functions36.平方根x=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–6nxn=rand()%100;n=rand()%6+1;函數Functions49.平方和(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)函數Functions5ABCD1710423.571833.597412752211.排名次=RANK(數值Num,範圍Ref,次序Order)=RANK(A1,A1:A5,0)=RANK(A1,A1:A5)=RANK(A1,A1:A5,1)1=0由大至小descending1同上50由小至大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函數Functions6E1Sales225/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)函數Functions7ABCDE1applesorangespeachesapples=IF(C350,,C3)232547586=IF(C350,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(A289,A,IF(A279,B,IF(A269,C,IF(A259,D,F))))2如果等如apples2如果大於或等如552如果大於54函數Functions8ABCD1房屋價值銷售佣金102100,0007,00073200,00014,00094300,00021,000275400,00028,0002=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函數Functions9ABCD1FrequencyColor顏色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函數Functions1020Lookup=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函數Functions11ABCD1在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函數Functions1222.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函數Functions13ABCDE11323221323321125.先乘，然後再加總=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函數Functions1427.字符串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–stringASCIICharCharASCII=abc&xyztrueorfalse是否空白05aabcAbcd陳大A65abcxyz函數Functions1530.從中間取字串的部分=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函數Functions1633.找尋(目標，搜索範圍，開始位置)=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函數Functions1735.比較字符串=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函數Functions18A1Margin2ProfitMargin38.找尋(目標，搜索範圍，開始位置)=SEARCH(e,Statements,6)=SEARCH(E,Statements,6)=SEAR