AOA浙江省二级计算机考试高级办公软件运用历年真题Excel解答详细操作.docx

E1.-产品灯，1 .使用数祖公式，计算Sheet1.中的每种产品的价值,将结果保存到表中的“价值”列中。计算价值的计算方法为：“总价*每食数鼠*采购食数”.掾作步Sb将鼠标定位在H2,给入“=H2*F2M；2”，弁以“Ctr1.+Shift+Enter”结束该公式的输入（数组公式的标志为-r>.自动填充.2 .在Sheet2中,利用数据库函数及已设置的条件区域.计算以下情况的结果.并将结果保存和府的单元格中。a.计算：商标为上海，寿命大于等于100O的白炽灯的平均单价；b.计算：产品为白炽灯，其瓦数大于等于80旦小于等于100的盒数.掾作步Shu.将鼠标定位在G23,输入数据库函数Daverage（计律满足给定条件的列表或数据库的列中数值的平均值）."AVERAGE（A1.:1117.E1.J2:1.3）.（其中A1.:1117是Datebase.构成列表或数据库的单元格区域,数据库是-系列相关的数掘的列衣：EI是Fie1.d,用双引号插住的列标签，或是&示核列在类表巾位置的数伯：J2:1.3是Criteria,包含给定条件的总元格区域,该区域包括标彩及列标签下湎足条件的单元格，）b.在I列增加一列“数量”，井求出其数扇"用盒数量采购盒数）：将鼠标定位在G24,输入数据库函数DS1.M（求满足给定条件的数据库中记求的字段（列）数据的和."=DSUM（1.117,11,J7:1.8>,各区域含义同上。3 .某公司对各个部门员_1.吸烟情况进行统计,作为人力资源搭8的一个数据依据.对于阿查对思，只能答史Y吸烟或者N（不吸烟）.根据两查情况制做出Sheet3.请使用函数.统计符合以下条件的数值.a.统计未登记的部门个数；b.统计在徵记的部门中，吸烟的部门个数，操作步:a.将网标定位在BM.插入函数CoUNTB1.AXK.-C0CNTB1.NK（B2E1.1.）*b.将IU标定位在B15,插入函数CaJNTE"=COJNTIF（B2:E1.1.,*y，"4 .使用函数，对Sheet3中的B21单元格中的内容进行判断,判断其是否为文本，如果是,结果为“TRIE"：如果不是,结果为“FA1.SE".并将结果保存在为eet3中的B22单元格当中.掾作步将鼠标定位在B22,插入函数ISTEXT（桂测个值是否为文本，返回TRUE或FA1.SE）,“=ISTEXT（B2D”,5 .将Sheet1.货制到Sheet4中,对Sheet4进行高级筛选，要求：a,筛选条件：“产品为白炽灯,商标为上海"，并将结果保存；b.将结果保存在SheetI中。操作步:构SheetI复制到Sheet4.建立条件区域：产品高标自炽灯I心1.执行“插入""高级筛选”.选择列表区域和条件区域,确定即可。6 .根据SheeU的结果,在Shem5中创立一张数据透视表,要求：a.显示不同商标的不同产品的采购数玳；b行区域设置为“产品"1:c.列区域设置为“商标：<1.计数项为“采购盒数°.掾作步:执行"数据”"数据透视表和数则透视图”,按图2.1所示选择“数据源类型”和“报我类里”.图21选择“数据源类型”和“报类型单击“下一步”，选定区域,如图2.2所示.图2.2选定数据源区城单击“下一步”，选择数据透视衣的显示位在Sheet5中雎击A18单元格图2.3选样数据透视我的显示位置单击“完成”，即出现如图2.4所示的登的数据陈透觇衣及相关工具,若25请将数据项拖至此处国迎0E«图2.4空的数据库透视表及相关工只将图2.I中“数据透视龙字段列入”中的“产品”拖到"行字段”，将"商标”拖到"列字BT,将“采购意数”拖到,数据项"。败后的数据透视表效果如图2.5所示。M翻Eii幅胸需商标G，北京上海(空白)胞计白炽灯459至管112其他22日光灯22总计7815图2.5数据透视表效果图E2-房产销售表1 .利用公式,计算SheetI中的房价总赧.房价总额的计贪公式为：“面枳*单价”掾作步h将鼠标定位在13,输入公式“=F3*C3-，自动填充，2 .使用数组公式，计算SheetI中的契税总额契税总额的计算公式为：“契税*房价总额"掾作步，格就标定位在J3,输入公式“=I3*J3"，并以“CuI-Shif1.Enier”结束该公式的输入，自动填充.3 .使用函数，根据Shee1.I中的结果，统计每个销件人员的销售总额，将结果保存在SheeI2中的相应的单元格中.掾作步h将鼠标定位在ShCeI2的B2,插入函数“=SIMIF(ShCe1.I!K3:K26B2”,Shec1.1.!13:126).,自动埴充.1.使用RANK函数，根据Sheet2的结果，对鼻个销件人员的销件情况进行排序，并将结果保存在“排名”列当中。掾作步h将鼠标定位花Sheet2的B3,插入函数-=RK(B2,SB$2:$B$6,0)".自动埴充.5,将SheCII更制到Shee1.3中，并对Sheot3进行高级筛选.要求:a.筛选条件为：“户Sr为两室一厅，“琼价总额”>1000000：b.将结果保存在Sheet3中.掾作步，将ShCCI1.笈制到ShwI3。建立条件区城：户型房价总领两室-厅I>1000000执行“输入”"高拨行选”.选择列表域和条件区域，确定即可，6.根据Sheet1.的结果，创立一张数据透视图Char1.1,要求：a.显示每个销件人员销伤房屋所缴纳契税总制：b.行区域设置为“tfit与人员"：c.计数项设置为契税总额；d.将对应的数据透视表保存在Shee1.1中。操作步：执行"数据*"数据透视表和数据透视图".按图2.6所示选择“数据源类型.和“报农类型”.图2.6选择“数据源类型和“报表类型”单击“下一步”，选定区域，如图2.7所示.数解冻预装和数盘冻就窗1.K零步骤之广?×话侵入或选定要注立数髡透视我的数砧相域：AS2:$KK6I取湎I上一步II下1步Qi）II¾w1512.7选定数据源区域单击“F-步”，选择数据透视衣的显示位置.垠而SheeM工作会标签选择数据透视表的显示彼捏.将图2.9中“数据透视衣字段列衣"中的“销传人员”拖到“分类字段，将“契税总Sr拖到“数据项”最后的数据透视图效果如图2.10所示.（注：数据透视图在Chart1.生成的同时在Sheet5中生成了数据透视表）XOOOO2!4X>X>2COOOO150000IWOOOWOOO工总IxMW.ME:“：条定用型帜於n仔!1.tftK懵情户IS0«*IK命T1.dd3dJ11Jn图2.10数据透视图效果图E3-电话号码1 .使用时间函数，对SheetI中用户的年龄进行计算.要求：计算用户的年龄，并将其计算结果填充到“年龄”列当中.掾作步h将鼠标定位在D2,插入函数“=YEAR(NWo)-YEAR(C2)”,白动填充即可。2 .使用REP1.ACE函数，对Shee1.I中用户的电话号码阴亍升曲要求：对“版电话号码”列中的电话号码进行升级.升级方法是在区号<0571)后面加上“8”,并构其计算给果保存在“升级电话号码”列的相应单元格中.掾作步h将鼠标定位在G2,插入文本谈定REP1.ACE(将个字符申中的局部字符用另个字符串替换)，*=REMJCE(F2,5,0,8)”(其中，F2½01(itext,要进行字符昔换的文本：5½Startnum.要替换为newjext的字符在O1.dtext中的位在：0的Num.chars,.要从OIdtext中替换的字符个数,8是NeW_textJH来对o1.dtext中指定字符串进行件换的字符;i；),r动堵充即可，.3 .使用逻辑函数，判断SheetI中的“大于等于40岁的男性*,将结果保存在SheeH中的“是否>=4Q男性”.掾作步h将鼠标定位在H2,插入条件函数IF,“=IF(AND(D2>=4O,B2=*见)/是","否.自动埴充即可.4 .对Sheet1.中的数据,根据以下条件,利用函数进行统计：a.统计性别为“男"的用户人数，将结果埴入Sheet2的B2单元格中：b,统计年龄为“>40”岁的用户人数，将结果埴入ShCe1.2的B3单元格中。掾作步:a.将鼠标定位在SheCt2的B2,插入函数CWNTIF（计算某个区域中满足给定的服元格数目）.“=C0UNnF（Sheet1.!B2:B37,"男"）".（其中，“Sheet1.!B2:B37”是Range,要计算其中非空单元格数目的区域:“男”是CriIeria,以数字、表达式或文本形式定义的条件，）b.将狼标定位在SheVI2的B3,插入函数”=C0CNTIF（Shcc1.1.!D2:D37,”>4（r）"5 .将Shee1.I复制到SheeI3,并对Shee1.3进行高级籍选。a.筛选条件为："性别”一女、"所在区域"一西湖区：b.符筛选结果保存在Sheet3中.掾作步h将Shee1.1.复制到Sheet3.建立条件区域：性别所在区域女西湖区执行“插入-”高级篇选”，选择列表区域和条件区域，确定即可。6 .根业;Sheet1.的结果，创立一数据透视图ChartI,要求：a.地示每个区域所拥有的用户数量：b,*坐标设置为“所在区域"；C.计数项为“所在区域“：d将对应的数据透视表保存在Sheet4中.操作步h（见E26）E4T温1 .使用IF函数，对Sheet1.中的“温度较高的城市”列进行.掾作步将鼠标定位在1）2,插入IF数."=IF（B2>C2,”杭州/上海）；自动填充.2 .使用数跄公式，对Sheet1.中的相差温度值（杭州相对于上海的温差进行填充.操作步:格鼠标定位在£2,就入公式“=H2-C2”,并以“Ctr1.+Shif1.+Entcr”结束该公式的输入,自动填充.3 .利用函数,根据SheetI中的结果,符合以下条件的进行统计.a.杭州这半个月以洪的最高墨度和最低温度：b.上海这半个月以来的最高温度和最低温度。媒作步Sha.将S1.标定位在C18,插入函数MAt”=MAX（B2：B16）*：将鼠标定位在CI9,播入9数MIN,“=MIN（B2:B16）"b.将鼠标定位在C20,插入函数WMJ=MAX（C2:C16）：将IiI标定位在C21,插入函数MINj=VIN（C2:C16）4,将Sheeu型制到ShM1.2中,在Shee12中,一新编辑数组公式,将ShBI2中的“相差的温度值”中的效做取其绝对值（均为正数）.掾作步:将SheetI红恻到Shee1.2,将鼠标定位在Sheet2的E2,插入函数ABS,"=ARS（B12<12）"，门动地充.5 .将Sheet2复制到Sheet3中.并对Sheet3进行级筛选,要求：筛选条件：“杭州平均气温>=20,“上海平均气温”<20.骞作步将ShCC1.2女制到ShCeI3.建立条件区域，杭州平均，温上WJF均气温>=20I<20执行“插入”"高级