第三四次課件公式及函數(shù)綜合應(yīng)用

函數(shù)和公式綜合應(yīng)用1、公式編寫原則2、理解單元格引用3、了解運算符4、掌握數(shù)組和數(shù)組公式的應(yīng)用5、理解函數(shù)結(jié)構(gòu)6、運用嵌套函數(shù)7、掌握常用函數(shù)8、函數(shù)與公式綜合應(yīng)用實例學(xué)習(xí)目標(biāo)１、公式編寫原則Excel的公式由運算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號“=”開頭，公式中盡量使用變量，避免使用常量。2、引用1）、引用的概念在公式中用到了其他單元格在表格中的位置。引用的作用在于標(biāo)識工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的單元格位置。引用不同工作簿中的單元格稱為鏈接。2）、引用的類型A1引用R1C1引用3）、相對引用相對引用也稱為相對地址引用，是指在一個公式中直接用單元格的列標(biāo)與行號來取用某個單元格中的內(nèi)容。相對引用的例子：某超市進(jìn)貨單如下圖所示，計算“金額”。在G3計算公式=C3*D3*F3然后向下復(fù)制該公式！4）、絕對引用絕對引用總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對引用保持不變。絕對引用的形式是在引用單元格的列號與行號前面加“$”符號。如$A$1…絕對引用的案例絕對引用的案例

在E8輸入公式：=$c$5*C8然后向下復(fù)制此公式！5）、混合引用混合引用具有絕對列和相對行，或是絕對行和相對列。比如：$A1。案例—在公式中可以使用運算符。

—Excel中的運算符運算符運算功能優(yōu)先級()括號1-負(fù)號2%百分號3^乘方4*與/乘、除法5+與-加、減法6&文本連接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于83、運算符4、數(shù)組公式及其應(yīng)用1）、數(shù)組公式的含義數(shù)組公式能夠?qū)山M或兩組以上的數(shù)據(jù)（兩個或兩個以上的單元格區(qū)域）同時進(jìn)行計算。在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。2）、數(shù)組公式的建立方法

（1）選中需要保存數(shù)組公式結(jié)果的單元格或單元格區(qū)域。（2）輸入公式的內(nèi)容。（3）按Ctrl+Shift+Enter鍵。3）、用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域乘積【例4-1】某超市進(jìn)貨單如圖4-5所示，要計算各種產(chǎn)品的應(yīng)付金額。4)、數(shù)組擴(kuò)展

在公式中用數(shù)組作為參數(shù)時，所有的數(shù)組必須是同維的。例如：某商場29寸電視機(jī)售價為3128元，有一個10天的銷售記錄，如下圖的B、C兩列所示?，F(xiàn)要計算每天的銷售額。

1、選中F8:F14；2、輸入公式：=C8:C14*C53、按Ctrl+Shift+Enter函數(shù)快功能禽：返回桐某一凱單元孤格區(qū)繼域中剪所有毒數(shù)字重之和居。示例藏：如果悉單元默格A2衛(wèi):E執(zhí)2包含5,逮15僻,3渠0,處40和50，則抖：SU覽M(謙A2辜:C租2)等于50函數(shù)名參數(shù)SUM(number1,number2,...)函數(shù)養(yǎng)值函數(shù)窮結(jié)構(gòu)練：5、函慈數(shù)結(jié)核構(gòu)6、嵌凍套函齊數(shù)函數(shù)將可以幸用作跡其它邁函數(shù)愚的參博數(shù)。突當(dāng)函翼數(shù)被方用作箏參數(shù)口，稱煤為嵌臣套。公式杏中最涂多可覽以包頑含七級鬧嵌套委函數(shù)AV寬ER子AG吳E(券F2巴:F愈5)SU響M(峰G2善:G倘5)IF請(A>在5死,B,惕0款)=陪A=甘B7、必常用難函數(shù)數(shù)學(xué)漆和三霸角函好數(shù)AB竿S(胸nu嚷mb應(yīng)er)：返啦回數(shù)占字的秧絕對辨值。IN春T(職nu炭mb健er)：將寒?dāng)?shù)字瓜向下彎舍入嗓為最些接近鳴的整選數(shù)。MO詠D(淹nu永m(xù)b傘er飲,d既iv狂is稱or)：返畝回兩斃數(shù)相懶除的蠢余數(shù)隔。PR芽OD沙UC每T(彎nu耍mb昆er情1,蕉nu焰mb裕er雁2,瓜..夾.)：將哲所有慶參數(shù)剪的數(shù)搶字相奔乘，井并返電回乘庭積值畜。PO樂WE刮R(隸nu溫mb墳er者,p以ow攻er)：返巡壽回給院定數(shù)逢字的癢乘冪毒。RA岔ND狐(崇)：返迫回0到1之間餅的均東勻分票布隨科機(jī)數(shù)春，每饑次計把算都原將返廈回一變個新勤的數(shù)希值。RO令UN掛D(房誠nu織mb倦er右,n咳um踏_d制ig份it狗s)：將項數(shù)字噴舍入理到指湖定位暢數(shù)。SU稿M(驢nu膜mb殺er絕1,管nu猴mb戒er杜2,雅.象..撿)：返臟回某攏一單拉元格玻區(qū)域拾中所桐有數(shù)蝴字之胖和。SU鳥MI陸F(健ra差ng攝e,宜cr伴it苦er卷ia晃,s畏um淺_r畢an踢ge)：根聯(lián)據(jù)指輔定條部件對媽若干石單元輩格求摸和。文本脾函數(shù)LE至N(菌te洗xt)：返貍回文異本字圍符串帥中的急字符腔數(shù)。LO逗WE刪R(絞te悼xt)：將現(xiàn)文本曠轉(zhuǎn)換犁為小性寫形防式。UP披PE尖R(俘te擇xt)：將沖文本軋轉(zhuǎn)換檢成大響寫形紫式。FI模ND思(f議in較d_垃te慨xt絹,w聯(lián)it偷hi楚n_拜te喘xt扛,s忍ta呀rt碎_n隙um)：在盟一文瘡本值鞠內(nèi)查滅找另扶一文匪本值瓶（區(qū)惰分大換小寫老）。SE纖AR求CH眉(f郵in活d_撲te綿xt故,w嘴it否hi排n_藍(lán)te刪xt吳,s詠ta嫂rt搶_n該um)：在螞一文艱本值黎中查縣找另罩一文斧本值滿（不戲區(qū)分朽大小具寫）叔。文本微的截移取MI恒D(槳te篇xt芒,s來ta懶rt鑒_n傍um后,n鼻um裁_c織ha繞rs)：從獅文本喂字符繪串中衰的指闖定位批置起鴿返回棕指定板個數(shù)體的字壩符。LE躍FT姨(t駝ex塑t,浙nu舉m_撐ch愈ar司s)：根抗據(jù)所詞指定巖的字譽符數(shù)節(jié)返回攪文本逃字符尋串中陰的第確一個顆或前嚴(yán)幾個祖字符灰。RI核GH谷T(旅te物xt昏,n田um姓_c妖ha鳳rs)：根徒據(jù)所鳥指定晉的字耍符數(shù)摟返回劈燕文本冶字符掘串中服最后縫一個域或多暈個字狂符。CO飄NC毀AT拘EN輝AT宮E電(t逝ex裹t1報,t杜ex瘦t2撥,.廟..惕)：將豈幾個緊文本煤字符草串合訪并為畜一個隱文本割字符兇串。RE只PL枕AC嘴E(獵ol挑d_建te偵xt噸,s先ta有rt臂_n援um諸,n夜um塌_c曉ha蘋rs啦,n壓ew弱_t右ex殘t)：使瓶用其倚他文施本字大符串掠并根時據(jù)所慌指定仇的字壓符數(shù)法替換套某文洞本字等符串族中的袋部分腎文本總。邏輯蹄函數(shù)NO券T(申lo濁gi瓣ca比l)：對波參數(shù)控值求安反。AN羽D(迷lo鞠gi午ca手l1亦,l同og顫ic蜻al喇2,覆.潔..甚)：所師有參粥數(shù)的房誠邏輯禿值為頁真時脹，返屈回TR勢UE。OR掉(l切og省ic五al涌1,木lo單gi矛ca渣l2夫,.徒..訓(xùn))：在職其參太數(shù)組扎中，救任何蹈一個評參數(shù)藝邏輯塞值為TR丙UE，即送返回TR顫UE。IF龍(l翠og弄ic獻(xiàn)al倚_t樣es此t,艷va謝lu覽e_場if徑_t爪ru昆e,米va頑lu數(shù)e_個if含_f失al配se)：執(zhí)蚊行真創(chuàng)假值赴判斷磁，根蘇據(jù)邏顯輯計賄算的夕真假蜘值，規(guī)返回借不同甩結(jié)果斬。日期冬和時類間函外數(shù)YE失AR獻(xiàn)(s熱er傳ia批l_獸nu竊mb梯er)：返依回某咳日期哀對應(yīng)每的年便份。MO單NT勒H(噴se聯(lián)ri漂al駱_n將um強(qiáng)be東r)：返澡回某柳日期售中的淺月份艱。DA茄Y(毅se押ri段al煩_n蠻um慨be德r)：返驢回某艇日期餐中的控天數(shù)演。TO壇DA冰Y(睛)：返顫回當(dāng)洪前日塵期的今序列哀號。刃如果翼在輸頁入函綢數(shù)前木，單獄元格巨的格寶式為龍“常羽規(guī)”浴，則干結(jié)果感將設(shè)搜為日慰期格秘式。WE蹦EK自DA濫Y(熄se烈ri示al嚷_n倒um業(yè)be釣r,蔑re銜tu量rn煩_t磚yp洪e)：返擦回某巡壽日期襯為星綱期幾及。默粉認(rèn)情勵況下筆，其煎值為1（星裹期天競）到7（星叫期六雄）之淡間的蠢整數(shù)躁。查詢宴和引排用函亂數(shù)LO個OK習(xí)UP輝(l端oo帽ku甘p_澇va黨lu電e,互lo奧ok望up秋_v膀ec眠to奔r,煌re僑su團(tuán)lt誤_v懶ec羨to遠(yuǎn)r)：在毯向量通或數(shù)籠組中盯查找辰值，夢然后綱返回哨第二社個單與行區(qū)程域或甩單列風(fēng)區(qū)域殼中相伯同位歪置的糊數(shù)值道。VL屢OO肺KU團(tuán)P(赴l(fā)o銜ok厲up脹_v傭al雄ue聞,t舌ab牧le渾_a傾rr割ay它,c您ol雞_i黨nd油ex掉_n膚um輪,r浮an魂ge望_l喘oo謀ku護(hù)p)：在竄數(shù)組闊第一宴列中捐查找康，然餐后在孫行之篩間移殃動以企返回時單元剩格的姥值。HL淚OO墾KU罵P(炸lo堆ok觀up豬_v瓣al碌ue吹,t惹ab練le之_a加rr區(qū)ay慮,r筍ow億_i屠nd亦ex前_n搬um略,r弓an吸ge倚_l筆oo藏ku壩p)：在虧表格艷或數(shù)乞值數(shù)抱組的撞首行摸查找皇指定形的數(shù)惕值，上并由束此返糟回表言格或嗎數(shù)組米當(dāng)前驕列中尿指定僚行處壩的數(shù)炮值。佳當(dāng)比繪較值息位于地數(shù)據(jù)墻表的蹲首行擔(dān)，并裹且要惱查找賓下面中給定脊行中堵的數(shù)跪據(jù)時鴉，請否使用粉函數(shù)HL疤OO紀(jì)KU蛾P(guān)。當(dāng)詞比較騎值位狐于要惜查找比的數(shù)國據(jù)左附邊的累一列楚時，戀請使由用函狗數(shù)VL繡OO催KU恐P。財務(wù)督函數(shù)PM延T(題ra邪te汗,n漁pe劍r,斯pv半,f僻v,喬ty宣pe)：基畏于固呈定利斥率及劃等額誓分期粗付款怨方式饅，返毛回貸鄉(xiāng)豐款的輝每期塘付款蘋額。例如弦：某霸人買砍房，銀貸款20救0毀00父0元，趴利率棟為7%，分10年/月付殲清，友則他警的月靜支付訓(xùn)額為完：PM沿T(林7%離,1閉0,配20也00柱00浩)=￥-2爹84鴿75粱.5拖0PM劈燕T(僅7%摔/1撒2,稀10淋,2匙00型00污0)手=￥20綿64嫁7.于26統(tǒng)計顆函數(shù)AV難ER捆AG組E(暖nu切mb更er架1,旅nu迎mb航er病2,喬..什.)：返乞回參貌數(shù)的遲平均勉值（昂算術(shù)蜓平均滴值）增。CO區(qū)UN游T(標(biāo)va筑lu野e1扔,v運al京ue惰2,臟..芽.)：計脈算參捉數(shù)列衫表中素數(shù)字濁的個臘數(shù)。FR說EQ劇UE盞NC搖Y(廉da肌ta泊_a消rr逆ay刻,b風(fēng)in醬s_艷ar聾ra懲y)：以遣向量預(yù)數(shù)組型的形嘆式返叨回頻購率分梅布。MA睛X(罵nu露mb號er蠢1,旋nu磁mb捏er昆2,戶..低.)：返徑回參鞋數(shù)列衣表中藏的最榴大值仇。MI相N(擔(dān)nu捕mb穴er映1,勒nu虹mb鏈er山2,情..滋.)：返賞回參尊數(shù)列叫表中崇的最芳小值創(chuàng)。實例逃一羽算術(shù)淹考試躁自動御判分愈表保護(hù)件工作腫表中涂的部蒸分單任元格IF函數(shù)娘的使層用用法IF親(條件,表達(dá)刺式1,表達(dá)要式2)。例子If溜(A巨1>椅60肺,“及格萌了”,“不及吊格”)，若A1單元英格的查值是76，則劃該函貍數(shù)的袍結(jié)果哨是：之及格胞了；珠若A1單元撫格的喂值是50，則蛛該函皇數(shù)的萄結(jié)果服是:不及徹格。計數(shù)鏟函數(shù)CO陽UN媽T、CO汗UN厘TA、CO束UN葛TB險LA恨NK、CO喝UN額TI煉F的使難用用法CO打UN龍T(躬v1拿,炎v2丘,勻…)CO瞎UN厘TA膊(v祖1,礙v梁2,風(fēng)…化)CO登UN放TB蒜LA等NK潮(稻ra貸ng餓e)CO召UN匆TI迷F粱(r榨an朽ge欺,想cr梳it女er肯ia農(nóng))功能Co屬un舟t統(tǒng)計需數(shù)字略的個鎖數(shù)Co顆un蛇tA統(tǒng)計賀數(shù)字命和文翁本的卵個數(shù)Co評un缺tB謀la竭nk統(tǒng)計絲式空白偷單元茂格的性個數(shù)Co拍un縮慧ti福f統(tǒng)計澆滿足浴條件Cr勿it筒er牽ia的單血元格卻個數(shù)實例途二棵學(xué)怖生成須績表—數(shù)學(xué)央類函續(xù)數(shù)平均呢分的獅計算四舍系五入數(shù)、舍槍入和粥去尾街函數(shù)用法億與功努能ro滿un歐d(焦nu靜mb奪er想,n參um徹_d返ig管it五s)將數(shù)避字舍辟入到狠指定憐位數(shù)ro消un愿du嘉p(寶nu蒸mb筆er揪,n智um畏_d些ig衣it守s)將數(shù)蘋朝遠(yuǎn)霞離零眠的方魔向舍座入ro火un榨dd仗ow居n(宿nu蘆mb盛er魔,n螺um雞_d奸ig逐it晶s)將數(shù)嘩字朝葵零的清方向冠舍入按平璃均分女劃分毛等級實例瓜三蒜某學(xué)遲校成迅績分撤科表—邏輯串判斷補類函媽數(shù)求最費大值蔽、最銀小值警函數(shù)MA封X，MI吩N用法MA魂X(姻nu趨mb插er矩1,擔(dān)nu腿mb航er合2,居..船.)MI恩N(災(zāi)nu盯mb籌er舟1,諸nu攝mb集er透2,繳..兆.)功能MA撤X函數(shù)坊返回煎一組辣值中側(cè)的最悼大值眼，MI倡N函數(shù)寄返回逐一組懇值中嘗的最黎小值建立四“成天績統(tǒng)檔計”鍋工作孔表求每閘個同陪學(xué)的槍總分描、平出均分求各租門課私程的響最高茄分、村最低沈分和橋平均悄分用粗橫體、灑黃色住背景像色標(biāo)曉識出戚各門萍課程帶的10費0分根據(jù)提所求腫數(shù)據(jù)越繪制累各科庸最高只分、新最低擇分和近平均喬分的閱圖表單元脾格區(qū)席域名級稱的程應(yīng)用—編寫慈自然濁語言吊公式按照糞單元奧格地紋址編斷寫公爭式按照鴿單元誓格區(qū)劉域名騾稱編潛寫公亦式建立音“補聰考名糕單”撕工作耳表建立燒表頭“姓原名”辮列數(shù)碌據(jù)引止用其他題數(shù)據(jù)視列“栗補考摩”判合定利用頁“條驚件格揉式”察設(shè)置查“補照考名儉單”謎工作右表的惑格式實例趴四在計算儀機(jī)維調(diào)修登萌記表—查找倘、字歌符和跌日期艱類函瘦數(shù)LO芝OK揪UP函數(shù)零幫助指定冊單元疊格區(qū)夕域名地稱LO笨OK贊UP函數(shù)陶應(yīng)用設(shè)定防“輸堂入錯蕉誤”胖提示自動也登記棵“維輝修日偉期”兄和“紛星期如幾”閥列的第值保護(hù)繁“故洞障登泳記”續(xù)工作銜表的烤公式池單元與格利用痛“條若件格策式”騾設(shè)定耐“故期障登紐奉記”銅工作棗表的壞格式查找柴函數(shù)1）LO蛛OK碌UP函數(shù)用法LO許OK唯UP從(v宜al掠ue,霞r1環(huán),便r2術(shù))功能該函匪數(shù)在r1狠(必須哭按升才序排蘆列)所在循的行肉或列某中查膚找值勻為va男l(wèi)u泳e的單豆元格核，然礎(chǔ)后返客回r2中與r1同行歐或同厘列的氣單元回格中肆的值提。2）HL跪OO攀KU柳P函數(shù)用法HL摩OO間KU鞏P(斧va蕩lu刻e,清ta狠bl擱e,立n虎,ra比ng敗e_顯lo鐵ok漏up)功能在數(shù)席據(jù)清丙單（擇或區(qū)共域）ta費bl騾e的第漁一行沒中查攤找值環(huán)為va壟lu變e的單囑元格根，如堵果找鄙到，東則返婦回該嘗列第n行單仁元格眠的值侄。3）Vl輔oo廉ku化p函數(shù)VL僵OO現(xiàn)KU升P（x,肉t欠ab堵le鍋,洋n,驢r）案例4）MA泉TC械H函數(shù)用法MA算TC打H(面va黑lu白e,找ar營ra傭y,始t凝yp妹e)其中va船lu宜e是在ar市ra奶y中查驅(qū)找的舟數(shù)值ar丘ra飾y包含席要查縮慧找數(shù)杏據(jù)的議區(qū)域饑或數(shù)拆組ty編pe為數(shù)櫻字-1、0或1例:M妖AT餐CH腔(“欺b”浪,{憲“a港”,腳”b逃”,歐”c極”}竟,0火)返回2MA喪TC出H函數(shù)之的查啞找方登法取值函數(shù)功能-1array必須按降序排列，查找大于或等于value的最小數(shù)值0array不必排序，查找等于value的第一個數(shù)值，精確查找1array必須按升序排列，查找小于或等于value的最大數(shù)值5）IN輛DE急X函數(shù)用法IN戴DE般X(窗ar抬ra組y,療ro某w_岡nu疤m,顏co專lu忽mn己_n忽um)功能in螺de始x函數(shù)孟返回ar幟ra膊y中ro煉w_辛nu敘m和co例lu伍mn襲_n倍um交叉撫處的什單元談格引功用。日期徐及時襲間函籍?dāng)?shù)1）WE虹EK街DA毫Y函數(shù)用法WE日EK刃DA怕Y(槳se斧ri蕉al埋_n盡um惕be現(xiàn)r,re孩tu口rn軟_t堡yp嘉e)功能WE對EK維DA故Y計算創(chuàng)給定號的日丟期是角星期模幾2）DA勵TE函數(shù)用法DA箱TE杰(y山ea倆r,面mo畝nt藥h,抱d稅ay滋)例如DA隊TE坡(2涌00支5,忍3,打21駛)的結(jié)緣瑞果是20示05您-3殺-2刪13）YE建AR，MO醉NT效H，DA奪Y函數(shù)用法YE胖AR形(s疾er詠ia含l_播nu零mb榮er)MO葬NT斷H(料se渣ri敗al題_n獄um賽be凈r)DA診Y(搏se戒ri謝al鏈_n耳um爐be晌r)其中詠的se央ri姿al蠅_n躁um秘be稻r是一捷個日給期或語數(shù)字昨。。4）TO蠶DA吧Y、NO悶W函數(shù)用法TO掛DA寧Y(亞)NO容W(花)功能TO缺DA耕Y函數(shù)踏返回蹲系統(tǒng)這的當(dāng)可前日篩期。NO斑W函數(shù)爬計算便當(dāng)前而日期愚和時軍間。實例殖五繁考內(nèi)試題克目統(tǒng)貝計分肥析表—文本刮、統(tǒng)脾計扣類脹函數(shù)文本紛函數(shù)邪調(diào)用帝語法限：le倡ft壞(t記ex煩t,葵n)、ri搭gh悶t(丈te培xt燥,n)、mi涂d(抬te仔xt儀,n鳴1,誼n2港)比如當(dāng)：ri鞏gh鑒t(判"1障23槍4"鳴,2帝)=休"健34斃“比如哀：MI噴D(陵"1旋23罵4"快,2帆,2礎(chǔ))=顆"刺23古"答案庸分解提取割“學(xué)額號”提取歡“答蠢案”分解臂“答毯案”統(tǒng)計頑分析秧每道搏題目攜的正替確率實例騾六記銀行賢貸款—財務(wù)狼類函移數(shù)PM砍T函數(shù)扒語法PM學(xué)T(爛ra戶te嶄,n訪pe焦r,攤pv屋,f豪v,杰ty外pe)函數(shù)演有五撕個參綱數(shù)，持是一湊個比剩較復(fù)律雜的怒函數(shù)違。各疾參數(shù)歲的含昨義如貞下：Ra菠te：各君期利賠率Np該er：投鑼資（考或貸致款）親的付啄款期Pv：本城金Fv：最側(cè)后一罵次支痛付后碗的現(xiàn)荒金余峰額，矛省略fv，則世其值赤為零Ty怨pe：指