EXCEL实训案例与操作步骤

1、Excel实训案例与操作步骤一、函数错误!未定义书签。1. sum 函数错误!未定义书签。2. max 函数错误!未定义书签。3. min 函数错误!未定义书签。4. if 函数 错误!未定义书签。5. sumif 函数错误!未定义书签。6. sumproduct 函数 错误!未定义书签。7. vlookup 函数错误!未定义书签。8. countif 函数错误!未定义书签。二、基本操作与数据处理错误! 未定义书签。（一）数据有效性错误!未定义书签。1、输入序列数据错误! 未定义书签。2、输入指定区间的数据错误! 未定义书签。（二）条件格式错误!未定义书签。1、挑选重复数据错误! 未定义书签。

2、2、突出显示最大值与最小值错误 !未定义书签。3、图标集与数据条的使用 错误 ! 未定义书签。（三）筛选 错误!未定义书签。1、多条件高级筛选 错误 ! 未定义书签。2、利用列表（表）实现高效筛选 错误!未定义书签。三、数据透视表错误 !未定义书签。（一）制作基本的数据透视表错误 !未定义书签。（二）利用多重数据区域制作数据透视表错误 !未定义书签。（三）运用数据透视表进行表格数据对比分析错误 !未定义书签。（四）动态数据透视表制作错误 !未定义书签。（五）通过自定义计算字段进行统计分析错误 !未定义书签。四、图表制作与美化 错误 !未定义书签。（一）柱形图错误!未定义书签。（二）折线图错误!

3、未定义书签。（三）饼图 错误!未定义书签。一、函数主要介绍如下函数：max min sum if sumif sumproduct vlookup countif1. sum函数功能：计算单元格区域中所有数值的和语法： =sum(number1,number2, )anumber1,number2,”为需要求和的参数。参数可以是数值、文本、逻辑值和单元格引用。单元格引用如果是空单元格，那么该单元格引用将被忽略。2. max函数功能：返回一组值中的最大值语法： =max(number1,number2, )numberl, number2, numberl 是必需的，后续数值是可选的。3. mi

4、n函数功能：返回一组值中的最小值语法： =min(number1,number2, )number1, number2, number1 是必需的，后续数值是可选的。例财务工作中常用函数：化工集团含若干分工厂，2014年各月利润如表所示，需求出各工厂年度利润合计、各月最大利润数与各月最小利润数。ECDCEJKn工0F般_1某02。14年利国城甘羞(JbTE)21月4月E门也门侑9月ionIE目计12个月巾是大利相助仃中月中 剧响翻13危利优工厂449505027LF5333?85mS50273Dfi4也通牝工厂门-J：-27 3Q羽”r J !-MH斗舟】1 LDbOMtn3言界比丁厂7371

5、IDE?06I1&93西口3S01hJ 91767B9I则需在N3单元格中输入=SUM(B3:M3)在O3单元格中输入=MAX(B3:M3)在P3单元格中输入 =MIN(B3:M3)再将N3至P3单元格选中，下拉填充柄，即将公式填充至下一行可得出下表结果：口区EC , D5G K：JK -LYUoF12住T型图0隼利国期计名(77)1月23巾4月5月商月T月洞泪I。月“月月合外1史十月申 盘黄中间累L改月中I小利拼獭3耳胜司比工厂7149白3IBD270561413K012730W73T37L的以I50Cmt光邙化工厂3DDS332E273021323913432s3M97喇IW5D95036

6、L&01106013B5三厘优工厂7 7371S6D?7306168325036D1四57566S1787851MEH91T8nea4. if函数功能：判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。语法：=if(logical_test,value_if_true,value_if_false)其中第一个参数logical_test为任何一个可判断为 true或false的数值或表达式。第二个参数 value_if_true为logical_test为true时函数的返回值，可以是某一个公式。如果 value_if_true 省略，当logical_test为真时，函数返

7、回TRUE值。第三个参数为logical_test为假时的返回值，当该参数省略同时logical_test为假时，函数返回的值为false。If函数可以嵌套，最多可以嵌套7层。例企业在应收账款管理过程中，需及时对到期的应收账款进行偿还提醒，故需筛选出当前 日期已到期的应收客户及应收金额。如下表所示AECDE?1应收账款懵单c 金当白itJMU3必同右群由股金割r.K7 钺未收金,tla 金否到并4菠w2第口2D15-03-M5妙生丸232D15-07-2T&为至9DOOIODOeooo2D15-00-207佳也乐fl 30003002016-00-013隹任事idooaBODQ900。5015

8、-0.1-219在言食品12350IODOHist2 Dl 5-02-2610她生8EOOB&OO；Di6-02-?2为及时筛选出到期客户清单，需在F列显示出其到期状态，可通过IF函数来实现。在F4单元格输入函数：=IF(E4VB2到期，未到期)。表示的意思是，当 E4单元格的到期 日小于当前日期，则 F4单元格显示“到期”，否则，则显示“未到期”。进一步，若将函数中的 B2代表的当前日期固定，也即，将公式改为=IF(E4$B$2,到期，未到期。则下拉填充柄，可轻松实现公式的自动复制。AECDF1应收减款清单D尚前Fl明：?DI V7/233必司江林应收关领己收堂额未收盘颤到勒日是苦到困4屋宜

9、白片5789550022SQ2050 4皿到期热生61296129201E-07-27未到期为曳irrrLOQO2000201E-08-?fi到期7佳佳乐匿0Q6300201E 03-01乐到期1偻佳乐I400D5业19而2015-04-27至谶Lj_康居商品1235 口N叩U3D02016-02-26到细i j他生8E0QB5O02DIS-07-22期期5. sumif 函数功能：对满足条件的单元格求和。语法：=sumif(range,criteria,sum_range)range表示要进行计算的单元格区域， criteria表示用数字、表达式或文本形式定义的条件;sum_range表示用

10、于求和计算的实际单元格。如果省略，将使用区域中的单元格。例：根据表1-工资表得出表2-各部门工资统计AECD1景2姓名部门6月实发工贸301告不本管理普G064402虺连匡苴理就155850S石月平28296钟家明道理都34UT0B万科智39638p06李平财荆17060J10表211音邪1、工资金计12百理都1147E113工程部6T92时野部7060在B12单元格输入=SUMIF(C3:C8,A12,D3:D8)即可得出管理部6月实发工资合 计。6. sumproduct 函数功能：用于计算几组数组间对应元素乘积之和语法：=SUMPRODUCT(array1,array2,array3；)

11、=SUMPRODUC项组1,数组2,数组3,)例：对于如下左图所示数据，要计算所有产品的销售总额，一般的方法是先计算每个产品的销售额(单价乘以销售量)，然后将每个产品的销售额加总在一起，得到销售总额，如下右 图。A.1产品阳i俏亚里中工3产晶f4 匕1114FB32靠5产晶4425k励53抬专TARD1LEG甲%消声审9LBBlJ解网8产a此产品】232*5三23短S声星后仪7产a62J5菱Lb：9)i-ittsff1711但使用sumproduct函数可以完全省略中间的计算过程，计算公式为: 在B9单元格输入=sumproduct(B2:B7,C2:C7)即可直接得出答案7. vlookup

12、 函数功能：根据数据区域的第一列数据，向右侧查找某列的数据语法：=VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)=VLOOKUP查找依据，查找区域，指定取数的列位置，是否精确查询的逻辑值)。表示精确匹配，找一模一样的数据；1表示模糊匹配，找接近的数据。注意，单独使用函数 VLOOKUP法查找指定数据区域的重复数据。例：某公司共生产12种产品，产品资料如下左表，2011年1月销售记录如下右表，销售记录中仅包含所销售产品编码及数量，要求要继续完成空白单元格。kECE中产丽M舁亏产品漏青I-品和跖量位1CL0Q9阳观ii4

13、弓凤1旧户flHini件3E熊r晶J6理黄6耳B10W三fiMurn内T5T1Q07U品渤苣后叫火BETS9?E41KH产Ml豺ft1DIB厕g产品75眸ft11gE4D05iim-irB件1210浦口制22E.0-1311ijcnoi手吊12的甘1当124D0ti产品745i J1 514 :好朝西记=_ *A3c0EP 0I少日呻芦&洞的稿甲产品哲群B值重力35QIWI-05“口4i-ai-0EC 1.105T叩5初3-m讥UML和G初 m-oaCi: JDSit20J3-oi-ogBLJCig35昌S20l-0il-LCCL0054别g加1141加4第自10201J-01-L2CLJ05

14、4JL11JO11-01-t3BtODdi12上01丁 UH|,:135011-01-15IM M2eii142011-OL-LF制如*:1?aoii-oi-p囱J国3酣IB9011-01-11明汕&1.必MM335518TtQ 口 L35s2011-01.-21而M24%用1 21L产品理料.短四记录用，销售记录表：单元格 D3: =VLOOKUP（$B3产品资料！$B$3:$E$14,2,0）单元格 E3: =VLOOKUP（$B品资料！$B$3:$E$14,3,0）单元格 F3: =VLOOKUP（$B3T品资料！$B$3:$E$14,4,0）单元格G3:=C3*E3ABcDE1 Lf1

15、1销售记录2日期齐晶编书置fi置衣品转称单伯单位金新1 32011-31-05AL0D4EOO产出rt5177101-05CIO0520 C声品-15?6营1152002D11-01-（KBIOO0150产firS1150172500&7R2011-31-08CIO05143产firl营二823532D11-D1-031006邱产品T1150h4232002BH-01-1OCLO03434产品-3245 口台10633009102D11-31-UAL0D4356产品-61771件340182B11-D1-12CIO05句产品-1576营248256on 1 1 _ h

16、 ii_ i -iQ 1 C 仆 U九Gd士口 _口1 1 c nCTOTtflin例：禾1J用VLOOKUP函数制作工资条下表1为工资表，要想轻松高效完成每位员工工资条的制作，形成表 21A ，用C 事口0 胆K1 县近次u 小讨奥带一保监队行出心才京里Jfl 械防3_典N tiHftD 丁宸口COJSITE宣珅就整珅B岸弋42 DD-CUO2加MBUM, 809. 6胃犯&q49X0L3rBUBMM12r LIft华ti=iH融1O3D0l：0ICOD7003T141192BG30 M0303413f ISfi.E显保麻就勺厘4SDD疣口口ioa 1?DD7T154539鼠6S3M11队4

17、6333HF L3不工本力升部箝珅4SBQ3CD01削mo由MEM评日案H日3评IS2.J66515L4以漳主产都百J.若口口2 M10Q工冽箕岗1战双BGMU温韩IEr IFi，*窄手他裨叩_1P 口looq和口433皿考4/2S4R其尊17r L6万N告工程部亮mF42005D01504 = 504% 597JI PL 55B35&rr14.31第弗i aLI工J加日毛都就密有42 QD2SDD11007ioa71居245685E22121： 25696ISrLS倚在四生产都相长粘。口QDD就。4400443g304352563.担%帕2.L94 oft生产部原占39 DD32 DO28a

18、；：-：ll53LBJ7. 627b. 1i43D.4ET,34B43融21.20挛1叫芳都的理41004CD0150EJ60艰517365?167故278. 02223H 41 M育市工迹星Sthrei：1 1MII&C0LfGHIJKHu01平排名前】史需享T关需备扑贴小计具北峰瞄国疗保防_公粗工个网蜕宾堂总7干苗1 岂那部mi3007和。；h质：i E60H眩泥Ifl&E沏的31 骗骂姓XI裕口爬西用本工强番贴小M关北理龈总出金且看全/苗_L肃个遍氨其冲E 1r -否至法管母法20 口口230200E4Q03449LGB192I960Q195357 拓号I倏电*n型舞整盘更掌嚏小计丸井保

19、蜡氏后住南口也全养系续祖讪L接个馈机艾戈士e l 03228C3 302：-021021.35561X6222.422T0. B02290杵书部门草*T滨时贴医点保心滑初士*法安反前工舆富发荔1隼曲莅部2：0Di2007加口2052LS22Q8213202132 |12部门笃事工暴饕至讣贴小4区疗保心矮极先养总至税施工费个祖根冥发悬Uh长八市第部JUIIJU-II:制M36JJ4LSII痢1 H * w nShttcll9-1H操作步骤: 新建表格“工资条”，按规定格式输入第一行列标签1ECr3F_rHIrKL乂W0一二M2而口中血总/丁室朴M小计X韭也既外和金3d就1LL|1B2 单元格：=

20、VLOOKUP($A2工资表！$A$2:$O$21,2,0) 即，以工资条中 A2编号为查找依据，查找区域为工资表 A2至。21,指定取数的列位置为第2歹U,也即如果遇到工资表中第一列编号也为01的情况，则B2单元格取值为被查找匹配单元格向右数第2列单元格中的数值，0表示精确查找。但是鉴于工资条的格式与工资表的格式相同，可将2以COLUMN/弋替(COLUMN(底示本单元格所在的列数)，以方便公式的拖拉，而不需手动修改公式。故，进一步将 B2单元格改为：=VLOOKUP($A2X资表！$A$2:$O$21,COLUMN(),0),向右拖拉 填充柄，即可完成第一位员工工资表的制作。A B C D

21、EFG工HJHNU1翳号1姓电部门 电理茎本工真1量皇补贴|小计“北保总窸力保总先塞生籁信工贵十谓就实发鼻2 r . |工21工管臼部经理 处W|zauieJS2b戛eraS2WI醋SECI的完成后，选中A1至03单元格，向下拖动右下角的填充柄，即可实现所有员工工资条的制 作。AB一HCH_KnN0场茸挂名门里部义畲扑聒不计AikSiL西方讨阮税的工瓷个利税大发孰(11弊拜伽口?ann_jnn_IgnnTC1525326图1必14567&ECDLGHI1KLHN01卿斗nriETTi草木T黑矍$助小计卜槛粉置方必皎上黑需金度怙T宴个泪R201若IL市情理洲&国dsor制aT加口70IF2fl

22、253tdL 2加阻31阚母拄冬部门职劳基本工费支金4U小计表业僚崎医疗保障色故口曾老金祝而工我下调歌实发苴-片土 3它哇方fi蛋EMC2加200240 Q244白LB192im的0196867阚与ft苦的1史弗旦木工出鬻1物贴小仔JUHBEt医疔快吐公迪生茶工学晚苗工用兵袁？W弗栈费止药同2州1 )011:伽2L86.G1毓,4黑4股电1，。师Q1 .弱力鞋咨手口*罟品率J岗交金J补贴小才共助保限西疔集总公积金济书业反蔺工法tlflft其贪苗11r国鼻皆事? IF UGE 丹. nt1年占2iOC3UIII2002和。2fi&220g21320213? I1212*姓名新】也若此木_|_曲交

23、至补贴业计尖北便陶匡行年总斐里养看至个利税宴金弃14F ift汽r.GTfinf匚据3I.IUUJ30JbuM60JL8的241.6G?291.12S29| H 4t 工 表._ 13( . .ShtttlLr lH8. countif 函数功能：用来求满足区域内指定条件的计数函数语法：countif (range, criteria)range表示要计算其中非空单元格数目的区域criteria表示统计条件例：根据工资表，需统计出实发工资各区间段的员工人数。_ b； c _d_ b _r-C HJ- KL1MCPQi MamUTilUH小讨小外雷呜疗也-HE*实*2 r21S 05Tng学 曾

24、裳邱* 任杳05co WQE-i- 2E30D TfiCO 现r 口78 -Koa苔沐就生户都腔膻金DO-95DQ工办5(095ISOf65旧TTSO324T-3664ECC, 60DD心百 0攀占%WIIJIIL：1DTMO用/ITSm血可避” .Ij, WU0HR:m：F3dBr-n42C047*IM1J4*彳11阳2Q10/=:*之二不俎Lifioa/0aran4.4ED44ina皿北 111s；MDG11 ro主亡单;户手祟黄42tXlIDDQ2BD541DE BIDS. .3S3.S444.93. S凡3GlIE】丁主卢军F 二ncn30DtooanoJIiflgt3034.D加乂1

25、3徜北LjlT?CiO旧赞9eic的E乳4M3不大殂可产即515WI如口口COT?iuTT.B166口工却乱由1B3；:15 t41时期世 工斗生卬%： rssr2EW39CU20D 疝LCD tou20fu2a4J笈9fi 101双 冤白25S勺&2占D酹的17 电:咫手*术国:烟SOOLHaco4?. 5J?外9加你？Li jt3M318 J1二旧甲即野甲321X1?600EOOTitoTiH2=6000)在R7单元格中输入公式：=COUNTIF($O$2:$O$21,=4000)-COUNTIF($O$2:$O$21,=6000)在R8单元格中输入公式：=COUNTIF($O$2:$O$

26、21,=2000)-COUNTIF($O$2:$O$21,=4000)在R9单元格中输入公式：=COUNTIF($O$2:$O$21,“-fi.jOCDS? o T 京a=izfon3E4LCD”口口42E42933-S翼刊口HUZ(WQ1Q :二也良“卜no如单箝皿酰烟3S；狐口H工加二 D5111rle1至匚了一丫4 MinJ. MOI m2加M*I IP.IELC4311-44.493.fIN J LiMt生“0工! = 0flML00i?aa第14.2W29苴M03D513 L2杠友已卒保才*三UEDD2 HDLCD17DDn9 04SJ9tiic的M：/ -.；SG3En工窜上巨力卜

27、计出UHUM2LiXn仙?.siSB.：IL CHL U瓦IhAT15也中1也下尸M脸；25002HLLlsl2BD0霹BS陶期_Z?的_p_许16 L5_I茶注小MQ3LiOLOO13郃3Q13433E2dX7Q3E2517 LE上怦餐二阳伽5：(l代GD机59T，或S3M刖L4. 3J: T)吁留4.00007100力保际Gfll_W?2L；12命的中餐将事生产葡记性6TC3%14BB308用正口 83flfiZD r L9和星主声三金世JJDDL2CDzsc31SD.ii.ITAliEUEL24tlJ.fiZl. 3542BC21 30章斗4 IE(llj时00ISO川物5 1I7NLt

28、,/：.TiTJ91 rtlj其他常见小函数： today row column text mid left right二、基本操作与数据处理（一）数据有效性数据有效性是对单元格设置的一个规则，只有满足这个规则的数据才能输入到单元格。1、输入序列数据在很多情况下，经常要输入一些重复的数据，比如要在员工信息表的某列输入该员工所属部门名称，而这些部门名称总是那么几个，此时，利用数据有效性， 不仅可以实现部门名称的快速输入，也可以防止输入错误的部门名称。例，如下左表，一共有三个部门，服装部、家电部、食品部，当单击B2单元格时，出现下拉箭头，就可以选择输入该序列的某个项目。ABCD919Q也则华田军1

29、59前军睥大苧中手3苏月平蚂大寺至小地男4专*福北中告516介质蜕交AT3工耳加胃Ji中心AECA,1争万工龄2.大学43美算T隘*有20聂小年闰X*143何轩毕中专536麻杵淤女太早施操作步骤：选中B2至B7单元格区域，单击“数据”选项卡中的“数据有效性”一一数据有效性,砒口T-ihi后胃 匕卜伫电梅，写UJ.汽许序列7 7客制空。4.7 y中蒯下白常/仁1T.m ；（加印，鼬飒* H他元啕1n仁理西t心全对制片啜？厘定ST.A前if-福不q 暑而愉1华 日 唐奈7 科用2021巾22表陵电忙24 :便出盂2、输入指定区间的数据例：要求输入员工年龄时，年龄区间范围为20-60之间。一旦输入非

30、区间内数值，将提示报错。ABCDE1股客骊口性别学历o菌口至用集部里大学3苏丹三陆装都弄大专4.房小华朗睥部史九有5顿冉华朗理都也中专a张辉黎本电部殳nr料东甲式电部冉中击操作步骤：选中E2至E7单元格，单击“数据”选项卡中的“数据有效性”一一数据有效性，设置有效性条件为允许整数，介于最小值20与最大值60之间，同时出错警告输入错误提示。当E2单元格输入66时，则会弹出对话框如下:ACIIH桂唐凯门性显学田年晶2弟江军刖技部更大学653再月平旅场芾具大看4吴/隼雨要部臭专5福密华郎志利史中专S础狂翳索尾部文大苧7钟定明宸里部坦口百（二）条件格式“开始”选项卡一“条件格式”1、挑选重复数据例使重

31、复的名字突出显示.BCD 豆R1m不W1M第华再午带H定2其至F梏奉上学心出2函3丹；同用料不叵I跖耳睢9十号72H5 畅樨华中专”；BB121, 比总费V史学*4b2J7漆某福乘弭襁科中奇634B 王I：平食品部中专蜴的2 tB 学宜宣匕岁R文字切河直T I ff*盟制1存12鳄网1L 冲军汨肯品都交中安2956B7Y-. 独庚友士昌出交土专13酩如J ：乐化力品学y学*nG胛14 王匚平曾晶部孱中专日而即t15 七安至an4E意中4】7535Jl曾晶却舅躇E加? 方午擎二高中：口M器ia 王iz平*品部尾中专号吕B32C*壬小船J?g时领BCEf1牙1住词学历4喑一诏2眠装前卫*苧心凯依为

32、月平4?制眄大专对行加4l 华解注就-*备- 1q律斗席总即鼻由专E35E1231交事q&p7 Z F里串部中专咫1a壬匚平窟豆白牛育D3Z1g吞国才飞小4人早755210kft相印勇丸毒12WM11w辜弱国解交年有20“7IE曲标女大专的564 313fiAlt甚出S*般亦即14王汇平食品部同上寺中6双115国虻,文岳对S：:皿工1&程Jt舌7nITW星苴诲L其_5_JSM39IS王汇平左生都更年*465321，皿*宣星出交申S446126操作步骤：选中 A2至A19单元格区域，依次点击条件格式一一突出显示单元格规则重复值2、突出显示最大值与最小值例 以上工资表为例，要求突出显示“工资”列中

33、最大工资与最小工资，以红色填充最大工 资，以绿色填充最小工资。2CliEF1性宓里别学田年却ZiS田开军等后停同大学股9q3再中二器餐郃抖大专2irao4烈事解装都二专12345.靠游郃用立吉S355126当电津丸大学光:匚_TII吃或过郁血&J2IR干干育后部科中专40S32114野归郃月大学51151210马上自寒逝郁里-86U211钟蝌坦豆船停女*官23Hm12曲践东也品怦交大白335 m13 Kft泡品林女县中32小的14京吊部国中音40S32115声衷走前在部免京;中ql1512出裴文芝食品都男中当5W1T遂军运森上手虻息中235432土 L牛出品郃更.533119工小M泡辞捋小专MKISS操作步骤:(1)选中F2至F19单元格区域，依次点击条件格式一一项目选取规则一一值最大的10项。将左边的10改为1,右边的设置 中点击自定义格式，选择填充， 红色，即可。(2)选中F2至F19单元格区域，依次点击条件格式一一项目选取规则一一值最小的10项。方法同理。3、图标集与数据条的使用(1)图标集5000至7000之间,5000例 如下表，给下表