重點(diǎn)函數(shù)筆記5 表格與計(jì)算.docx

重點(diǎn)函數(shù)筆記5 表格與計(jì)算SNM求和函數(shù)SUMIF符合條件單元格求和函數(shù)AB1SNM求和函數(shù)SUMIF函數(shù)對(duì)符合指定條件的單元格求和 2計(jì)算A列中多個(gè)參數(shù)之和對(duì)B列中大于等于8的數(shù)求和3SUMSUMIF416165886777668373797461SUM(A4:A8)SUMIF(B4:B8,=8)SUMIF函數(shù)對(duì)符合指定條件的單元格求和 AB對(duì)A列中大于7右邊對(duì)應(yīng)的數(shù)求和116100282003730046400 B列4數(shù)之和=10001000 符合條件單元格之和600600 SUMIF(A1:A4,=7,B1:B4)SUMIF函數(shù)應(yīng)用:對(duì)工齡三年以上的人員工齡工資求和當(dāng)前日期：2009-12-26按年100元計(jì) ABCDE姓名入職日期工齡工齡/整數(shù)工齡工資1張三2005-4-34.73424657544002李四2001-3-68.8136986388003王五2004-9-125.29041095955004趙七2008-5-31.64931506811005劉生成2003-6-236.51506849366006蔡中成2009-8-90.380821918007成功2007-6-92.55068493222008大老三2006-5-13.65753424733009小五子2007-3-22.821917808220010中山2008-6-241100工齡列可直接用公式TRUNC($D$39-D50)/365)TRUNC2600對(duì)工齡三年以上人員的工齡工資求和SUMIF(F41:F50,=3,G41:G50)日期時(shí)間函數(shù)TODAY函數(shù)和NOW函數(shù)TODAY函數(shù)返回計(jì)算機(jī)內(nèi)部時(shí)鐘當(dāng)前日期的序列數(shù)語法：TODAY（）2009-12-26NOW函數(shù)返回計(jì)算機(jī)內(nèi)部時(shí)鐘當(dāng)前日期和時(shí)間的序列數(shù)語法：NOW（）2009-12-26 10:21以上兩個(gè)函數(shù)都不需輸入?yún)?shù)星期函數(shù)的生成yyyy-m-d aaaa2009-12-26 星期六yyyy-m-d aaa2009-12-26 六DAY取年月日中的日期數(shù)字2009-10-12009-11-19119小數(shù)與整數(shù)小數(shù)四舍五入后保留兩位小數(shù)語法12.5678512.57ROUND(E16,2)5.6532165.65 小數(shù)四舍五入后保留兩位0值小數(shù)語法3.14159263.00TEXT(C153,0)&.005.6532166.00 小數(shù)取整數(shù)語法5.6532165TRUNC(E19)5.6532165INT(E19)求最高分與平均分 ABCDEFGHI 姓名語文數(shù)學(xué)英語物理化學(xué)政治總分最高分1張102149120120120987091492王11112011499125996681253劉119122991201301006901304李981431101151031056741435陳991321121131041106701326趙971251301021021126681307胡89989811199115610115最高分=MAX（B1：B7） 平均分670 平均分=AVERAGE(H1：H7) AVERAGE() 倒計(jì)時(shí)與工程進(jìn)度顯示當(dāng)前日期2009-12-26交貨日期2009-12-31竣工日期 剩余/日(倒計(jì)時(shí)）5進(jìn)度在建IF(O6=1,完工,IF(G6$E$4,超期,IF(O6=0,在建)求兩數(shù)相除的余數(shù)被除數(shù)79除數(shù)8得數(shù)9.875余數(shù)小數(shù)0.875MOD(F260/F261,2)余數(shù)整數(shù)7直接求余數(shù)的函數(shù)7MOD()求最大值與最小值返回最大值MAX()261222MAX(G7:G11,H7:H11)1323（忽略邏輯值與文本）文件24返回最小值MIN()15學(xué)生121626MIN(G7:H11) 評(píng)委亮分求最大值與最小值函數(shù)應(yīng)用評(píng)委評(píng)委亮分評(píng)委18評(píng)委29.8評(píng)委39.9評(píng)委49評(píng)委58評(píng)委68.9評(píng)委79.1評(píng)委89.4評(píng)委97評(píng)委108最低分7最高分9.9去掉一個(gè)最高分去掉一個(gè)最低分計(jì)算平均分78.8625(SUM(F6:O6)-MIN(F6:O6)-MAX(F6:O6)/8)排序與排位ABCDEFG序序比率/%百分比排序 序比率/%排位LARGE($C$1:$C$18,B1)RANK(G1,$G$1:$G$18)1155%99%155%112265%98%265%103367%91%367%94474%88%474%85588%82%588%46691%79%691%3778%78%78%188812%74%812%179978%67%978%7101079%65%1079%6111182%55%1182%5121298%45%1298%2131399%44%1399%1141434%38%1434%15151534%34%1534%15161638%34%1638%14171744%12%1744%13181845%8%1845%12平均值61%AVERAGE(G1:G18)錄入身份證號(hào)后用函數(shù)生成性別（倒數(shù)第二位單數(shù)為男雙數(shù)為女）身份證號(hào)性422427195905240022422427198312160046422427195612240024IF(MOD(RIGHT(I20,2),2)=0,女,男)計(jì)算男女生各自的平均分序性別分?jǐn)?shù)1男992女1003男1004男985男986女987男988男879男9810男9811女9812女9813女9814男9815女9816女9817女8818男9819女