excel functions - the writer's personal coach - professional excel函数-作家的个人教练专业

EXCELFUNCTIONSMATHEMATICALOPERATORS2FUNCTIONS2SUM3MULTIPLY3DIVIDE4AVERAGE5MAX6MIN6COUNT7COUNTA8IF8PMT9SIN,COS,TAN9COPYING10FILLDOWN10MATHEMATICALOPERATORSTHEMATHEMATICALOPERATORSUSEDINEXCELFORMULASARESIMILARTOTHEONESUSEDINMATHCLASSSUBTRACTIONMINUSSIGNADDITIONPLUSSIGNDIVISIONFORWARDSLASH/MULTIPLICATIONASTERISKEXPONENTIATIONCARETORDEROFOPERATIONSIFMORETHANONEOPERATORISUSEDINAFORMULA,THEREISASPECIFICORDERTHATEXCELWILLFOLLOWTOPERFORMTHESEMATHEMATICALOPERATIONSTHISORDEROFOPERATIONSCANBECHANGEDBYADDINGBRACKETSTOTHEEQUATIONANEASYWAYTOREMEMBERTHEORDEROFOPERATIONSISTOUSETHEACRONYMBEDMASTHEORDEROFOPERATIONSISBRACKETSEXPONENTSDIVISIONMULTIPLICATIONADDITIONSUBTRACTIONHOWTHEORDEROFOPERATIONSWORKSANYOPERATIONSCONTAINEDINBRACKETSWILLBECARRIEDOUTFIRSTFOLLOWEDBYANYEXPONENTSAFTERTHAT,EXCELCONSIDERSDIVISIONORMULTIPLICATIONOPERATIONSTOBEOFEQUALIMPORTANCE,ANDCARRIESOUTTHESEOPERATIONSINTHEORDERTHEYOCCURLEFTTORIGHTINTHEEQUATIONTHESAMEGOESFORTHENEXTTWOOPERATIONSADDITIONANDSUBTRACTIONTHEYARECONSIDEREDEQUALINTHEORDEROFOPERATIONSWHICHEVERONEAPPEARSFIRSTINANEQUATION,EITHERADDITIONORSUBTRACTION,ISTHEOPERATIONCARRIEDOUTFIRSTFUNCTIONSINEXCELTHEREISAHELPTOOLFORFUNCTIONSCALLEDTHEFUNCTIONWIZARDTHEREARETWOWAYSTOGETTHEFUNCTIONWIZARDIFYOULOOKATTHESTANDARDTOOLBAR,THEFUNCTIONWIZARDICONLOOKSLIKETHISTHEOTHERWAYTOGETTOTHEFUNCTIONWIZARDISTOGOTOTHEMENUINSERTDOWNTOFUNCTIONEITHERWAYYOUGETTHERE,ATTHISPOINTEXCELWILLLISTALLOFTHEFUNCTIONSAVAILABLEUPONCHOOSINGTHEFUNCTION,EXCELWILLPROMPTYOUFORTHEINFORMATIONITNEEDSTOCOMPLETETHEFUNCTIONMINIDESCRIPTIONSAREAVAILABLEFOREACHOFTHECELLSITISOFTENNECESSARYFORYOUTOUNDERSTANDTHEFUNCTIONSINORDERTOBEABLETOFIGUREOUTTHESEDESCRIPTIONSONCEYOUVELEARNEDTHEFUNCTIONS,THOUGH,ITISFASTERTOTYPETHEBASICFUNCTIONINFROMTHEKEYBOARDASOPPOSEDTOGOINGTHROUGHTHESTEPSOFTHISTOOLSUMPROBABLYTHEMOSTPOPULARFUNCTIONINANYSPREADSHEETISTHESUMFUNCTIONTHESUMFUNCTIONTAKESALLOFTHEVALUESINEACHOFTHESPECIFIEDCELLSANDTOTALSTHEIRVALUESTHESYNTAXISSUMFIRSTVALUE,SECONDVALUE,ETCINTHEFIRSTANDSECONDSPOTSYOUCANENTERANYOFTHEFOLLOWINGCONSTANT,CELL,RANGEOFCELLSBLANKCELLSWILLRETURNAVALUEOFZEROTOBEADDEDTOTHETOTALTEXTCELLSCANNOTBEADDEDTOANUMBERANDWILLPRODUCEANERRORLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHETYPICALFUNCTIONCANBEUSEDNOTICETHATINA4THEREISATEXTENTRYTHISHASNONUMERICVALUEANDCANNOTBEINCLUDEDINATOTALA1252503754TEST5EXAMPLECELLSTOADDANSWERSUMA1A3A1,A2,A3150SUMA1A3,100A1,A2,A3AND100250SUMA1A4A1,A4VALUESUMA1A2,A5A1,A2,A575MULTIPLYANEXAMPLEOFMULTIPLYINGTWONUMBERS,SUCHAS235AND546,USINGTHEPRODUCTFUNCTIONWOULDBEPRODUCT235,546THEANSWEROF128,310WILLAPPEARINTHECELLWHEREYOUTYPETHEFUNCTIONWHILETHISAPPROACHTOUSINGTHEPRODUCTFUNCTIONWORKS,ITLIMITSTHEUSEFULNESSOFTHEFUNCTIONABETTERWAYOFUSINGTHEFUNCTIONISTOTYPETHENUMBERSYOUAREMULTIPLYINGINTOCELLSONTHESPREADSHEETANDTHENENTERTHOSECELLREFERENCESTHEADDRESSOFTHECELLSINTOTHEFUNCTIONFOREXAMPLE,IFWEENTERTHENUMBERS235AND546INTOCELLSC1ANDC2,WEWOULDWRITETHEFUNCTIONASPRODUCTC1C2THEANSWERISSTILL128,310,BUTTHEADVANTAGEOFTHISAPPROACHISTHATIFTHENUMBERSEVERCHANGE,YOUONLYNEEDTOCHANGETHENUMBERSINCELLSC1ORC2ANDTHEFUNCTIONAUTOMATICALLYUPDATESTHEANSWERFOREXAMPLE,IFYOUFINDTHATTHENUMBERINC1WASNT235BUT230,SIMPLYTYPE230INCELLC1ANDTHEFUNCTIONUPDATESTHEANSWERTO128,580THISAPPROACHWORKSWELLFORINSTANCESWHEREYOUHAVECONSTANTLYCHANGINGNUMBERSSAYONAMONTHLYINCOMESTATEMENTWHERETHEINCOMEAMOUNTSGETMULTIPLIEDBYSETNUMBERSTOFINDDEDUCTIONRATESFORTAXES,PENSIONS,ORMEDICALBENEFITSDIVIDETODIVIDETWONUMBERSINEXCELYOUNEEDTOCREATEAFORMULAIMPORTANTPOINTSTOREMEMBERABOUTEXCELFORMULASFORMULASINEXCELALWAYSBEGINWITHTHEEQUALSIGNTHEEQUALSIGNALWAYSGOESINTHECELLWHEREYOUWANTTHEANSWERTOGOTHEDIVISIONSYMBOLISTHEFORWARDSLASH/USECELLREFERENCESINFORMULASEVENTHOUGHYOUCANUSENUMBERSDIRECTLYINYOURDIVISIONFORMULA,ITISMUCHBETTERTOUSETHEREFERENCESORADDRESSESOFTHECELLSCONTAININGYOURDATAIFYOUUSETHECELLREFERENCESA1,B1,F2RATHERTHANTHEACTUALDATA,LATER,IFYOUNEEDTOCHANGETHEDATAINEITHERCELL,THERESULTSOFTHEFORMULAWILLUPDATEAUTOMATICALLYWITHOUTYOUHAVINGTOREWRITETHEFORMULASETTINGUPTHEDIVISIONFORMULAASANEXAMPLE,LETSCREATEAFORMULAINCELLE1THATWILLDIVIDETHECONTENTSOFCELLC1BYCELLD1OURFORMULAC1/D1OURDATAPLACETHENUMBER20INCELLC1PLACETHENUMBER10INCELLD1DIVISIONFORMULASTEPSTODIVIDE20BY10ANDHAVETHEANSWERAPPEARINCELLE11TYPEANEQUALSIGNINCELLE12CLICKONCELLC1WITHTHEMOUSEPOINTER3TYPETHEDIVISIONSIGN/INCELLE14CLICKONCELLD1WITHTHEMOUSEPOINTER5PRESSTHEENTERKEYONTHEKEYBOARD6THEANSWER2SHOULDBEPRESENTINCELLE17EVENTHOUGHYOUSEETHEANSWERINCELLE1,IFYOUCLICKONTHATCELLYOUWILLSEEOURFORMULAINTHEFORMULABARABOVETHEWORKAREATOEXPANDYOURFORMULATOINCLUDEADDITIONALOPERATIONSSUCHASSUBTRACTIONORADDITIONJUSTCONTINUETOADDTHECORRECTMATHEMATICALOPERATORFOLLOWEDBYTHECELLREFERENCECONTAININGYOURDATAAVERAGETHEREAREMANYFUNCTIONSBUILTINTOMANYSPREADSHEETSONEOFTHEFIRSTONESTHATWEAREGOINGTODISCUSSISTHEAVERAGEFUNCTIONTHEAVERAGEFUNCTIONFINDSTHEAVERAGEOFTHESPECIFIEDDATASIMPLIFIESADDINGALLOFTHEINDICATEDCELLSTOGETHERANDDIVIDINGBYTHETOTALNUMBEROFCELLSTHESYNTAXISASFOLLOWSAVERAGEFIRSTVALUE,SECONDVALUE,ETCTEXTFIELDSANDBLANKENTRIESARENOTINCLUDEDINTHECALCULATIONSOFTHEAVERAGEFUNCTIONLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHEAVERAGEFUNCTIONCANBEUSEDA12525037541005EXAMPLECELLSTOAVERAGEANSWERAVERAGEA1A4A1,A2,A3,A4625AVERAGEA1A4,300A1,A2,A3,A4AND300110AVERAGEA1A5A1,A2,A3,A4,A5625AVERAGEA1A2,A4A1,A2,A45833MAXTHENEXTFUNCTIONISMAX,WHICHSTANDFORMAXIMUMTHISWILLRETURNTHELARGESTMAXVALUEINTHESELECTEDRANGEOFCELLSBLANKENTRIESARENOTINCLUDEDINTHECALCULATIONSOFTHEMAXFUNCTIONTEXTENTRIESARENOTINCLUDEDINTHECALCULATIONSOFTHEMAXFUNCTIONLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHEMAXFUNCTIONSCANBEUSEDA1102203304TEST5EXAMPLEOFMAXCELLSTOLOOKATANSMAXMAXA1A4A1,A2,A3,A430MAXA1A4,100A1,A2,A3,A4AND100100MAXA1,A3A1,A330MAXA1,A5A1,A510MINTHENEXTFUNCTIONWEWILLDISCUSSISMINWHICHSTANDSFORMINIMUMTHISWILLRETURNTHESMALLESTMINVALUEINTHESELECTEDRANGEOFCELLSBLANKENTRIESARENOTINCLUDEDINTHECALCULATIONSOFTHEMINFUNCTIONTEXTENTRIESARENOTINCLUDEDINTHECALCULATIONSOFTHEMINFUNCTIONLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHEMINFUNCTIONSCANBEUSEDA1102203304TEST5EXAMPLEOFMINCELLSTOLOOKATANSMINMINA1A4A1,A2,A3,A410MINA2A3,100A2,A3AND10020MINA1,A3A1,A310MINA1,A5A1,A5DISPLAYSTHESMALLESTNUMBER10COUNTTHENEXTFUNCTIONWEWILLDISCUSSISCOUNTTHISWILLRETURNTHENUMBEROFENTRIESACTUALLYCOUNTSEACHCELLTHATCONTAINSNUMBERDATAINTHESELECTEDRANGEOFCELLSBLANKENTRIESARENOTCOUNTEDTEXTENTRIESARENOTCOUNTEDLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHECOUNTFUNCTIONSCANBEUSEDA1102203304TEST5EXAMPLEOFCOUNTCELLSTOLOOKATANSWERCOUNTA1A3A1,A2,A33COUNTA1A3,100A1,A2,A3AND1004COUNTA1,A3A1,A32COUNTA1,A4A1,A41COUNTA1,A5A1,A51COUNTATHENEXTFUNCTIONWEWILLDISCUSSISCOUNTATHISWILLRETURNTHENUMBEROFENTRIESACTUALLYCOUNTSEACHCELLTHATCONTAINSNUMBERDATAORTEXTDATAINTHESELECTEDRANGEOFCELLSBLANKENTRIESARENOTCOUNTEDTEXTENTRIESARECOUNTEDLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHECOUNTAFUNCTIONSCANBEUSEDA1102203304TEST5EXAMPLEOFCOUNTACELLSTOLOOKATANSWERCOUNTAA1A3A1,A2,A33COUNTAA1A3,100A1,A2,A3AND1004COUNTAA1,A3A1,A32COUNTAA1,A4A1,A42COUNTAA1,A5A1,A51IFTHENEXTFUNCTIONWEWILLDISCUSSISIFTHEIFFUNCTIONWILLCHECKTHELOGICALCONDITIONOFASTATEMENTANDRETURNONEVALUEIFTRUEANDADIFFERENTVALUEIFFALSETHESYNTAXISIFCONDITION,VALUEIFTRUE,VALUEIFFALSEVALUERETURNEDMAYBEEITHERANUMBERORTEXTIFVALUERETURNEDISTEXT,ITMUSTBEINQUOTESLETSUSETHETABLEHEREFORTHEDISCUSSIONTHATFOLLOWSWEWILLLOOKATSEVERALDIFFERENTSPECIFICEXAMPLESTHATSHOWHOWTHEIFFUNCTIONSCANBEUSEDAB1PRICEOVERADOLLAR295NO3137YES4COMPARINGRETURNING51400000868453005EXAMPLEOFIFTYPEDINTOCOLUMNBCOMPARESANSWERIFA21,“YES“,“NO“IS951NOIFA31,“YES“,“NO“IS1371YESIFA510000,08,05IS140001000008IFA610000,08,05IS84531000005PMTTHEPMTFUNCTIONRETURNSTHEPERIODICINTHISCASEMONTHLYPAYMENTFORANANNUITYINTHISCASEALOANTHISISTHEPMTFUNCTIONTHATWASUSEDFORTHECARPURCHASEINTHEFIRSTEXAMPLETHEREAREAFEWTHINGSTHATWEMUSTKNOWINORDERFORTHISFUNCTIONTOWORKTOCALCULATETHELOANWEMUSTKNOWACOMBINATIONOFTHEFOLLOWINGRATEINTERESTRATEPERPERIODNPERNUMBEROFPAYMENTSUNTILREPAIDPVPRESENTVALUEOFTHELOANAMOUNTWEAREBORROWINGFVFUTUREVALUEOFTHEMONEYFORSAVINGORINVESTINGTYPEENTER0OR1TOINDICATEWHENPAYMENTSAREDUEPMTRATE,NPER,PV,FV,TYPEEQUATIONGOESINTOC7PMTC4/12,C5,C3C4ISTHEYEARLYINTERESTANDSINCEITSCOMPOUNDEDMONTHLYWEDIVIDEBY12C5ISTHENUMBEROFMONTHSOFPAYMENTSC3ISTHEAMOUNTOFMONEYWEHAVEBORROWNEGATIVENOTETHATTHERATEISPERPERIODIFWEHAVEANANNUALINTERESTRATEOF96ANDWEARECALCULATINGMONTHLYPAYMENTS,WEMUSTDIVIDETHEANNUALINTERESTRATEBY12TOCALCULATETHEMONTHLYINTERESTRATESIN,COS,TANEXCELHASMOSTOFTHEMATHANDTRIGFUNCTIONSBUILTINTOITIFYOUNEEDTOUSETHESIN,COS,TANFUNCTIONS,THEYCANBETYPEDINTOANYCELLIFYOUWANTEDTOFINDANGLESINCOSTANREFSINREFCOSREFTANREF0000100000300500870584507107110090100000180000100000FORMATFORDEGREESFORMULASINANGLEPI/180THEARGUMENTANGLEISINDEGREESFORMATFORRADIANSFORMULASINANGLETHEARGUMENTANGLEISINRADIANSTOCALCULATETRIGFUNCTIONSINDEGREESYOUMUSTCONVERTTHEMOTHERWISEEXCELWILLCALCULATETHEMINRADIANSYOUCANTYPEINEITHERANACTUALNUMBERFORTHEREFORYOUCANALSOTYPEINAREFERENCEFROMTHEEXCELSPREADSHEETLIKEA2COPYINGSOMETIMESWHENWEENTERAFORMULA,WENEEDTOREPEATTHESAMEFORMULAFORMANYDIFFERENTCELLSINTHESPREADSHEETWECANUSETHECOPYANDPASTECOMMANDTHECELLLO