EXCEL1000个小技巧-第04章 灵活应用公式和函数

44.1SUMExcelExcelCtrl+`Ctrl+Shift+{Ctrl+[A4C4B41A4C44.14.12B4A4C44.24.2EnterC84.3EXCEL100014.3A3B3A310%1A330004.44.42B3A3*110%EnterB34.54.5Excel1A1A22353354.64.62A3=IF(A1A2,,)EnterA34.74.71A1A22354.84.82A3A1A2A3A3A3=IF(A1-A2=0,,A1-A2);=IF(A1-A2=0,-,A1-A2)EnterA34.94.91EXCEL331C1YEAR4.104.10YEAR2SerialnumberNow()4.114.113C14.124.121C24.134.132MONTH4.144.14MONTH3Serial_numberTODAY()4.154.154C24.16EXCEL100014.16Office1||217183124.174.173Ctrl+`124.184.1844.194.195121EXCEL353F9EnterCtrl+Shift+EnterEsc123415Shift67Enter1Sheet1Sheet5MicrosoftExcelA1A42Sheet1Sheet5Excel3Sheet1Sheet5Excel4Sheet1Sheet5Excel5Sheet1Sheet5ExcelTRUEFALSETRUEFALSEMicrosoftExcelVALUE7BAB4.204.2012|4.214.213Excel4.22EXCEL100014.224ExcelExcel12|31|22004+20053Excel|123||4.2############1231900Excel######19001904#VALUE!1MicrosoftExcel2Enter1EXCEL37F2Ctrl+Shift+Enter3456VALU-E!#DIV0!0#DIV0!1MicrosoftExcel203#DIV0!#NAME?123456#NA#NA1HLOOKUPLOOKUPMATCHVLOOKUPlookupvaluelookupvalue2VLOOK-UPHLOOKUPMATCH3456#NA#REF!12#REF!EXCEL100013DDE#NUM!123#NULL!#NULL!SUMA1:A10,C1:C10A1:A10TC1:C104.234.23124.244.243Excel1Excel21EXCEL394.3C4.25=SUM(C:C)4.264.254.2614.274.27”24.284.28414.2924.30EXCEL100014.294.30312BackSpace34Shift5123512Delete344.4124.314.3131EXCEL41||Excel124.324.323F9Shift+F9124.334.33ExcelSheet1A1Sheet2A1~A41Sheet12A1=SUM(Sheet2!A1:A4)3EnterA1EXCEL1000112345Enter1sheet12C3=SUM(sheet2!A1:A3)4.343EnterC3sheet2A1~A34.354.344.351Book32C4=SUM([book1.xls]Sheet1!A1:A3)4.364.363EnterC4Book1sheet1A1~A3124.374.3744.384.3851EXCEL4314.394.39234124.404.4034.414.4114.424.422344.5SUMSUMSUM(number1,number2,…)number1,number2,…1~305EXCEL100011B7SUM4.434.43SUM2Number1B2B64.444.4434.454.45AVERAGEAVERAGEAVERAGE(number1,number2,…)number1,number2,…1~3051B7AVERA-GE4.464.46AVERAGE2Number1B2B64.474.4734.481EXCEL454.48MAXMAX(number1,number2,…)number1,number2,…1~3051B7MAX4.494.49MAX2Number1B2B64.504.5034.514.51MINMIN(number1,number2,…)number1,number2,…1~3051B7MIN4.52EXCEL100014.52MIN2Number1B2B64.534.5334.544.54STDEVSTDEVSTDEV(number1,number2,…)number1,number2,…1~3051B7STDEV4.554.55STDEV2Number1B2B64.564.5634.574.571EXCEL47FREQUENCYFREQUENCYFREQUENCYFREQUENCYFREQUENCY(dataarray,bingarray)dataarraybingarraydataarrayFREQUENCYdataarray209090~70701F15F17FREQU-ENCY4.584.58FREQUENCY2DataarrayC15D24BingarrayE15E164.594.593Shift+Ctrl4.6NO-W()NOW()1A3NOW4.604.60NOW24.61EXCEL100014.6134.624.62PVPV14.634.632A4PV4.644.64PV34.654.654A44.664.669000RATERATERATEnper,pmt,pv,fv,type,guess1EXCEL4990006200014.674.672B4RATE4.684.68RATE34.694.694B44.704.70IRRIRRIRR(values,guess)100000054000045000500005500014.714.712B7IRR4.72EXCEL100014.72IRR34.734.734B74.744.74INTNIT(numbre)numberA21A2A3INT4.754.75INT24.764.763A34.774.77ROUNDROUND(number,numdigits)numbernumdigitsA21EXCEL511A2A3ROUND4.784.78ROUND24.794.793A34.804.80PI1A3PI4.814.81PI24.824.823A34.834.83EXCEL10001SINSIN(number)number301A230A3SIN4.844.84SIN24.854.853A34.864.86COSCOS(mumber)number601A260A3COS4.874.87COS24.884.883A34.891EXCEL534.89TANTAN(number)number451A245A3TAN4.904.90TAN24.914.913A34.924.92S-QRTSQRT(number)numbernumberSQRT#NUM!1211A2121A3SQRT4.934.93SQRT24.94EXCEL100014.943A34.954.95SUMIFSUMIF(range,criteria,sumrange)rangecriteriasumrange1A1A8B1SUMIF4.964.96SUMIF24.974.974B14.984.98LENLENTextText1A21EXCEL55A3LEN4.994.99LEN24.1004.1003A34.1014.101MIDMIDtext,startnum,numcharstextstartnum11startnum1numcharsMIDchinabidding.combidding1A2chinabidding.comA3MID4.1024.102MID24.1034.1034D124.104EXCEL100014.104REPLACEREPLACEREPLACE(oldtext,startnum,numchars,newtext)oldtextstartnumnewtextoldte-xtnumcharsREPLACEnewte-xtoldtextnewtextoldtextchinabidding.combiddingdemands1A2chinabidding.comA3REPLACE4.1054.105REPLACE24.1064.1063D124.1074.107SEARCHstartnumSEARCH(findtext,withintext,startnu-m)findtextwithintextfindtextstartnumwithintextchinabidding.comb1A2chinabidding.comA3SEARCH4.1081EXCEL574.108SEARCH24.1094.1093D124.1104.110UPPERUPPER(text)textchinabidding.com1A2chinabidding.comA3UPPER4.1114.111UPPER24.1124.1123D124.1134.113CELLCELL(infotype,reference)EXCEL10001infotypereferencechinabidding.com1A2chinabidding.comA3CELL4.1144.114CELL24.1154.1153A34.1164.116ROW1=ROW(A1)2ROW=ROW(A1)13COUNTIFCOUNTEF(range,criteria)rangecriteria11002COUNT-IF4.1174.117COUNTIF34.1181EXCEL594.1184EXACTTRUEFALSEEXACT(text1,text2)text11text22ChinaChian1C15D15ChinaChianC162EXACT4.1194.119EXACT34.1204.1204C16FALSELEFTLEFT(text,numchars)textnumcharsLEFTIamaboy1C15IamaboyC162LEFT4.1214.121LEFT34.122EXCEL100014.1224C16RIGHTRIGHT(text,numchars)textnumcharsRIGHTIamaboy1C15IamaboyC172RIGHT4.1234.123RIGHT34.1242LEN(C15)-FIND(“*”,SUBSTITUTE(C-15,””,“*”,LEN(C15)-LEN(SUBST-ITUTE(C15,“”,“”))))4.1244C1751A1TODAY()Enter4.1254.1252A2A1+5354.1264.1261EXCEL611A2=DATE(YEAR(A1),MONTH(A1)+1,DAY(A-1)Enter24.1274.1271A1B12005-8-12005-9-14.1284.1282C1B1-A1+1Enter4.1294.1293C14.1304.1304C14.1314.1311A1B12004-8-12005-9-12C1=YEAR(B1)-YEAR(A1)Enter4.1324.1323C14.133EXCEL100014.1334C14.1344.1341A11982-5-102A23INT4.1354.135INT44.1364.1365A2WEEKDAYWEEKDAY(serialnumber,returntype)serialnumberreturntyp