Excel数据分析教程（第2版）上机题及课后习题答案.docx

《Excel数据分析教程（第2版）上机题及课后习题答案.docx》由会员分享，可在线阅读，更多相关《Excel数据分析教程（第2版）上机题及课后习题答案.docx（38页珍藏版）》请在课桌文档上搜索。

1、上机题11、在EXCel条件格式中运用公式进行筛选公式是使用Excel处理和分析数据的重要工具，在Excel的条件格式中使用公式能够速准确地筛选到满足条件的数据，从而更好地发挥Excel的条件格式功能。在Excel的条件格式中使用公式的具体操作步骤如下.打开素材，选择F2:F14单元格区域，单击“开始”选项卡下“样式”选项组中的“条件格式”按钮，在弹出的下拉菜单中选择“新建规则”选项，如图IB-Ia所示:文件插入页面布局公式数据审阅视IX粘我最前/最后先翦贴板0就条件格式-突出显示单民三中义治?赤海卫正运历陈扬刘任罗相-J部部8p三郃部CJI发务试发试务百开财测开溯财i_*i1lH11BO男男

2、女男男女DE职务Il都门处理10国皴职员12高级职员8高级取员0言通职员2晶门妞理3SSKa6言遢联员6高皴职员7善通也贡8第门经理978&500430043003600360043005000525000GSOO28007000IB-图数据条9)色阶(三)图标集(0新建规则)规则gA三sr11a11rln弹出“新建格式规则”对话框，在“选择规则类型”列表框中选择最后一项“使用公式确定要设置格式的单元格”选项，在“编辑规则说明“选项区域“为符合此公式的值设置格式”文本框中输入公式“=F21.ARGE($F$2:$F$14.4)”。输入完成后单出“格式”按钮来设W.相应的格式，如图IBJb所示：

3、图IB-Ib在“新建规则”中输入公式单击“确定”按钮即可将设置好的格式应用到销售额排名前：的数据所在的单元格中,效果如图IB-IC所示。123456-3男男女男男女男女男男男男女1部部部部88部部部都部部部部Sil米务试发试务发试发发发务*开财测开测财开观开开洪开财B11S3sa5高娘昭员鬲娘联QSfiKS部门经住工龄工由ioI700126W843043360033S00言息织员6告通职员6高级岷黄7百通织员8部门蛙理O51三通取五10GrOG图IB-IcItJHJ公式的式效果提示：公式中的“1.ARGE(SF$2:$F$14.4)”表示在E2:F14单元格区域中的数值按从大到小的顺序排列，排

4、位第4的值。很显然，这实际上是在进行类似于“数字筛选”的筛选操作，对筛选出的结果应用指定格式.所以条件格式本质上仍然是筛选。同样的，如果要选择最小的三位，可改成“=F2SMA1.1.($FS2:SF$I4,4)”。另外，如果排名第.三位的值有两个，则总共只有两个单元格应用r指定格式,而不是我们通常以为的会有四个单元格(即存在并列第三)应用指定格式。2、在筛选结果中重新排序我们都曾遇到过这种情况：数据在筛选前序号(如排名是连续的，但筛选后就不连续了，如图IB-2所示：ABCDE2019年云贵川三省城市GDP博名前十排名1BJII2云雨3贵州4QJII市区GDP总量13102506893241.9

5、81610.761,2019年云贵川三省城市GDP排名R+2I排,省H城一区市区GDP&T人八八A四四四四14RHkV都宾阳州131021610.761592.421150.02Eft12765ESJH6四川7黄州8云雨9云雨10ESJII1592.421150.021122.991105.29749880.06看这里880.C6图IB2筛选结果会打乱原一序号如果想要在筛选后结果依然保持顺序，应按如卜.方式操作：首先，将原表的排名消除，然后选择“插入I表格I表格”聘原来的数据清单转化为EXcd表,如图lB-3a所示：A_E_F排名省D城市Kl区故KI市区GDP息量23456783111213四

6、川成都1213102云南昆明7150689贵州四川四川四川贵州-云雷云南四川图lB3a将取&散粥区域转化为EXCd表接着在A2单元格输入公式=SUBToTA1.(IO3.B$2:B2),该公式使用了汇总函数SUBTOTA1.,其第一个参数表示counta统计，即统计第二个参数所表示的区域中非空单元格个数，此时SUBTOTA1.就在这些个数中自动给出排名，如图IB-现在要改成按“数学”排序，如果选中整个表，势必会使“序号”发生改变，因此只需选中B2:EII单元格区域，将“主要关键字”设置.为“数学”后进行降序排列即可，如图IB-Ib所示：图IB-4b不变更序号行了排序更仃甚者，如果连字段也没有，

7、例如下图lB-4c所示表格:ABC1语文成绩表21杨源喜tf32雷民8143刘卫中9054柳星9265张爱国8876桐明玉7487马献华6798罗远洁62109任正义65知IBTC没行原来的数据是随机的，现在需要按姓名发音排序，只需选择B2:ClO雌元格区域，在“排序”对话框中去掉“数据包含标题”前的对勾，然后将“主要关键字”设置为“列B”后升序排序即可。提示：在排序之前选中数据区域则只对数据区域内的数据进行排序。实际上，让序号不参与排序，使用的原理就是前面讲到的，只选择部分字段排序，排序结果只影响部分字段。本例中就是除/序号以外的“部分字段”参与了排序，所以结果只影响了除了序号以外的其他字段

8、。课后习题1I、在自定义筛选时，如果要筛选“川”开头的字符串，可使用的方法有哪些？如果要筛选包含“川的字符串，乂该怎么做？2,操作：使用筛选汇息分析员工工资本例将统计分析员工的工资，涉及以卜内容：按照“工资”降序排列按照“部门”升序排列,按照“工资”降序排列筛选出“高级职员”的相应数据筛选出“部门”为“开发部”、工资低于5000的数据以“性别”为分类依据，统计男女的人数以“部门”为分类依据.统计各部门的工资总和原始数据如图所示：ABCDE12取工姓名陈亦民杨源军刘卫中任正义罗远洁和原张爱国何明玉马旌华李光东雷民n三马羽W里处女里丹女再女SM:男再女部门开发部财夯部工龄10工资780050043

9、00430036003600430050005200500065002800ZDOO3124567891011121314Pliitse开发部S1i三财夯部开发部副过部开发部开发部则成都开发部财务部892366789110请将每个操作步骤的结果被图保存。操作如F：按列简单排序：直接点击“工资”列中任意一个单元格后，在“数据I排序和筛选”组中单击“降序”按钮JU即可.多关键字排序：在“数据排序和筛选”组中单击“持序”按钮五在打开的“排序”对话的中设置主要关键字和次要关键字即可。自动筛选：在“数据排序和筛选”组中.单击“筛选”按钮T,打开“职务”标题的下拉箭头后选择“高级职员”即可。高级筛选：先退

10、出自动筛选，然后数据表中建立如下图所示的条件区域：117,部门IT-IIisI开发部匚遨ZI再在“数据I排序和筛选”组中雎击“高级筛选”按钮V,在师出的“高级筛选”对话框中选择筛选的数据区域和条件区域，完成高级筛选。最后，再在“数据I排序和筛选组中单击“清除”按钮*,清除当前的筛选。 分类汇总：首先在“数据排序和筛选”组中单击“持序”按钮77,在打开的“排序”对话框中设置主要关键字为“性别”、次耍关键字“部门”后进行排序,本次分类汇总只需用到“性别”这个分类依据,“部门”这个评估依据是为卜面的操作预告准备的：排序完成后，再单击“数据分级显示”成中的“分类汇总”按4唱i,在弹出的“分类汇总”对话

11、框中以“性别”为分类依据、以“计数”为汇总方式,以“职工姓名”为汇总项进行汇总。 嵌套分类汇总：用同样方式再次进入“分类汇总”对话框，以“部门”为分类依据、以“求和”为汇总方式，以“工资”为汇总项进行汇总,此时注意要清除“替换当前分类汇总”及选框。3、操作：利用模糊筛选从给定范用中筛选出适当的数据模糊筛选通常也可称为通配符筛选，模糊筛选常用的数值类型有数值型、日期型和文本型，通配符“？”和“只能配合“文本型”数据使用,如果数据是日期型和数值型，则需要通过设置限定范围（如大于、小于、等于等）来实现。例如，筛选出姓“马”且名字只有一个字的人名的顼:鸵工号XgOOlZgoO2ZgOO3ZgOO4zg

12、005ZgoO6zg007ZgOO8zg009名.11.部具体操作步骤如下：选择列表任意一个单元格，单击“数据I排芹和筛选I筛选“按钮，在标鹿行每列的右侧出现一个下拉按钮，然后单击“姓名”列右侧的下拉按钮，在弹出的下拉列表中选择“文本筛选I等于”（或“文本筛选I自定义筛选”）选项，如图所示：12345678910n12l3141516lr18l,J202122x股工号ZgOOlzg002ZgOO3Zgocl4NOo5ZgOo6ZgOO7NgOo8“009ZgOlOZgOllzg012“013ZgOUzg015zgl6Ig(H7zg!8zg019BI_CD姓名性别部门名祢丁杨源百看民IIH办公空

13、办公主升序(三)降序(Q)按颜色排序CD工作表视图M从姓名“中清除筛选(按颜色筛选(1)文本笳选(E)搜索a(全选)等于和T(M).开头是(1)151921及23242S262728293031323334第3637都将弹出“自定义自动筛选方式”对话框,在“等于”后面的文本框中输入马？,注意此处的问号是英文状态下输入的，单击“确定”按钮，即可筛选出姓“刘”且名字只有一个字的人名,如出所示:职3W三jZgoO4马羽女zl8马苗女部门名称倘售部开发部自定义自动筛选方式显示行：姓名卜于g画与OSc(Q)IEIF可用？代表单个字符用惊任意冬个字行提示：通配符中?代表单个字符，可代表多个字符.如输入“马

14、?表示姓马，H.名字只有个字，输入“马*”则表示姓刘，且名字至少是一个字。此例中，如果中使用快捷文本快选项“包含”“不包含”“开头是”“结尾是”等几个选项是达不到目的的.必须使用自定义选咖，以公式来筛选.上机题21、应用数据透视表进行多地区经济指标统计分析以某市1辖各县市区2O182O2O年的各项经济指标为依据,创建数据透视表并进行相应的分析”原始数据如图2B1所示：jC画定,广投奥2.74D耳国W生产总值国艮生产总值指数人均GDP7813.073831.904161.544659.992120.352348.542662.089456.8410606.8512442.872175.68421

15、2.824757.45109.22109.71109.03108.99109.60109.30109.50110.20110.15111.66113.62108.78110.647434.009010.706054.006734.007654.007640.018714.009338.0012922.0014396.OO16809.005221.005829.003880.53108.862807.41112.966678.005033.08108.9812040.865458.22110.2513000.006002.54111.6014257.011713.81110.606462.521

16、940.94113.188162.002388.38117.608974.65337.44110.095340.00377.16110.216647.00445.36112.706691.00300.13111.715734.57340.65112.08647.00390.20111.867277.00111.73113.4111340.0013661.0010275.5012078.15图2B-I卜竹区经济指标统计分析央始数抵操作步骤如下：（1）建立数据透视表：按规定方式将数据透视表建立在新工作表上，在设计布局时，将“年代”作为“筛选器”（即分类汇总的分类依据），“县市区”作为“行标签”即行

17、字段，其他4个字段都拖到“2数值”区域作为汇总项：建立好的数据透视表如图:ABCDEI年代(全部)3行标普求和项人均CDP未和项固定黄产粮资未和项国民生产2的指r末和项国民生产急侑4北山县23599.1733.31341.386043.13539298.67103.57330.7316493.846定州区28692.0167.633297130.977河油市4412714.26335.4332506.568江战区24257.7718.61327.7912850.89,经开区35466267.51335.1831548.69105Jfl186781.263331159.96Hfa177235.8

18、6332.2S7433.5712JftWE203422.07327.6212643.4313W货县19489.5732.78335.651030.9814Q268678.19546.863328.06128841.93可以看出，该表只显示了各县市区的汇总数据.这是因为我们将年代做为汇总依据了。(2)进行各种数据分析报表筛选(页字段选择：在报表筛选中点下拉菜单选择。同理，也可以在行标签中点下拉菜单选择只显示某(些)省。重排字段：在“E值”区右击某项,在辨出的快捷菜单中单击上移或卜.移，或直接在值”区上下拖动某项。标签筛选和值筛选：例如，：年人均GDP之和大丁3万的“数据排序：单击需排序的字段任意

19、值，例如“人均GDP”，右击之，指向“排序I其他排序选项，弹出如下对话框:不过，这里如果不是在排序字段的任意值上右击，而是在字段名称上右击,则“其他排序选项”不可用：J-S3rMfcknxi4SM(C)23国设黄单元图6式.123(e刷新因Ms)”$1升序(三)X删际.求和项:国民生产总值-Q)H库存(Q)lfi(v亮他/F16.=TRUNC(G16/E16.2)ACZFCSKM1.原材料明细账计U位钳RflaIEESKIKCn5.Q月切络存5001203000oo625JQ入IW3501140.roe00CO3tW-*.0UB0512.n.32BSOIXt-M104ers.eei2raw烟1

20、23.154.79T.00315三w-e120.223.T.32双120.Z2103源：6IORawC!49.025U2t25F-：1BM.no.25111641W.743.312RAWIWnc.23is.eooooil1.-Zf4X发：S电假-：40IIS.2il.如.“X?113.29111.7S3.OO14261期人町假175122.3621,413.00IJf-”,如图所示：116-：I=TRUNC016H16,2)A3C3CFG三：JIIX原材料明细账2E%冬对三W也伫千克31.iljEj453I月120.00.oo,ooe3P(由入钳假350ne.9640.T9.00*守6I友-2

21、：。UB第32.UT32涉Ul95104.eTBMS9；二12%入Mn3%123.IS铳.：9：.89111C发:印拇-400120.224S.087.32SW120.22103.38S.1.C10(*W1Jb15人引42!：13.4,49.0M.2311-A547J发.360IlBX42.T1095IU641.743451213；二53I由入160lie.25IB.W0.0013.22行54!M3JW140118.2916.SCO.44115.291.l1.783.00B:e由人引解1T5S22.3621.413.001229三71I*1601189321.40650US93Ill.TW50

22、31n月1.ttC“8.3：1：M；22J.5011.1*ie82.73在KI6输入m=K5+E16-HI6k.得到本月的结存数fit:在M16输入“=M5+G16-JI6”,得出本月的结存金额：最后再用结存总金额除以结存总数J就得到结存单价，对此单价使用四舍五人保留两位小数的格式显示咨换为131或132替换的过程中需要使用的函数对号码的前三位数字进行奇数和偶数的判断，可以使用MOD函数,另外，判断前三位数字可以使用MID函数或1.EFT函数，这里使用MID函数,选择D3单元，输入以下公式：=IF(MOCMMID(C3.1.3),2)=I.REP1.ACE(C3.1.2.131).REP1.A

23、CE(C3.1.2,-132)就可以给电话号码实现升位,加图所示。D3=IF(MOD(MID(C3jl,3).2)=l,REP1.AC(C3,l,2,13),REP1.ACE(C3,l,2,132)ABCDE1公司通讯录2住址电话升位后的电话号码3I陈亦民港城区1355689XXX13155689XXX4杨源喜游仙区1384879XXX13284879m5刘卫中安州区1368283XX13268283XXX6任正义江油市1378087XXX13178087XXX可以使用1.EFT函数“1.E卜T(C3.3)”代普MlD函数，看结果是否一致。(是样的2、操作：利用1.OOKUP函数的向他形式杳询

24、员工个人信息，原文件如下:入职日期|Kt名性别出生日2002/8/9刘卫中奥1978/5/72691公司员工个人信息表本科41.200技木员H21514*m联系电话里198”S渊南撤本科41,200技术员14107582004/6马猊女1981/5/6上海大专42,400生经悭14%563*u20004旅爱国奥1976/3/6麴侬本科短，4。财芬主隹1432对2388怫玉女1979/2/8四川区中大专1979/5/3四川颤大专41,200让炎员41,500运输员1454130m1496765*1够9/2分麦苗女1975/8/2四川鬲无本科2,400行政主仔14545期要求查询结果如下:需查询的

25、员工信息罗i浩本科技术员提示:先按姓名进行升序排序，然后建立按性别、学历、职位查询的区域.在其后面的B2()、B21、B22单元格分别输入以下函数公式:=1.OOKUP(Bl9,B3:Bl6,C3:CI6)=1.OOKUP(B!9,B3B16.F3:FI6)=1.OOKUP(BI9.B3:B16.H3:H16)第3步，在G4队元裕中插入SUM函数m=SUNKE4*60.F4),然后填充到G5:GI3单元格区域中，求出每个人总的分钟数。第4步,在H4单元格插入TIME函数4=TIME(,G4,RIGHT(D4,2)m,然后填充到H5:H13总元格区域中,求出每个人总的时分杪数。这里有一个技巧,就

26、是“时”数我们不填分”数超过60的会自动护入。注意，这样设刊的时间是以子时开始计算的,是个时间序列值而不是时间间隔值，需在单元格格式中设置为时间中的时间值类型。4,操作：利用DATE函数和TODAY函数制作一个高考倒计时天数表，类似下图(假定是2022年6月8日高考)：在合并单元格F3中输入=CONeATENATE(DATE(2021.6.7)TODAY().天)上机题51、使用统计函数分析航空公司售票速度有顾客反映某家航空公司售票处售票的速度太慢。为此，航空公司收集了解100位顾客购票所花费时间的样本数据(单位：分钟,结果如图所示：1ABCDEFG壕梦航空公司售票速度调查(单位：分钟)HIJ

27、232.31.03.50.71.01.30.81.02.40.941.11.50.28.21.75.21.63.95.42.356.12.62.82.43.93.81.60.31.11.163.11.14.31.40.20.32.72.74.14.073.15.50.93.34.221.72.21.03.33.484.63.64.50.51.23.54.82.60.997.46.91.64.12.15.01.73.86.3103.20.62.13.77.80.81.31.43.51111.08.67.52.02.02.01.22.96.51.0124.62.01.25.82.92.02.96.

28、60.71.5拓物2.50.1-2.5505.02.6-5337.55.1-7.5126010.07.6-10312.510.1-12.5115.012.6-1504017.515.1-17.503020.017.-2002022.520.1-22.5绿梦航空公司存票速度调查（单位：分钟）201.BS17620三117512T1551.1257105r752T5Oi,25（2）用相应的函数进行计算后,结果如下图所示:/ABCDEFGHIJ12绿梦航空公司售票速度调查（里位：分钟）32.31.03.50.71.01.30.81.02.40.9124.62.01.25.82.92.02.96.60

29、.71.51314最大值21.7克术平均值3.1715息小值0.2申竣2.5MEDIAN(A3:J12)16经脸组距2.5众额1MODE(A3:J12)17经整10标准差2.86STOEV(A3:J12)从表中我们可以得到中位数为2.5、众数为I、平均数为3.17、标准差为2.8641.（3）合理.虽然他的平均数是3175平于正常范围,但是依旧有将近20%的购票时间5分钟票于超过正常范用,那就是速度太慢了。平均数不能代表切。所以顾客提出的理由是正确的购票太慢的现象确实存在.（4）平均数比较合理,它能较好的反映购票的大概时间。比较行代表性。2、利用移动平均趋势剔除季节变动因素如图所示是某商品5年

30、的分季节的销售数据，如何利用移动平均趋势别除季节变动？=D9*S8S10年份季节20152016201720180.7621931441.2323458770.9235627811.0650674660.8917987591.16024386231.0922723421.2240860221.1880233751.13871554O.95O545C230.7494764690.7825727610.920104557数率系比均整节平调季-910110.7759000731.094356865,1.0.848273871.1414835O6l?1643129110.853276197先在B9:F

31、9区域分别用公式=AVERAGE(B5:B8)=AVERAGE(C5:C8)=AVERAGE(D4rD7)=AVERAGE(E4:E7)求出每季的平均数：再在BlO小元格用公式=VERAGE(B9:E9)求出调整系数(即总平均值)，最后在BIl单元用公式=B9*$B$IO求出1季节的季节比率并填充到CU:EIl区域。3、规划求解实例1:钢管切割问题某物流配送中心从钢管厂进黄，需要将钢管按照用户的要求切割后进行配送,从钢管厂进货时得到的原料钢管都是7.4米长，而用户分别需要2.9米长、2.1米长和1.5米长的钢管各100根，应如何下料使原材料最节省？第一步：问题分析首先应当确定哪些切割方案是可行

32、的,所谓一个切割方案,是指按照用户需要在原料钢管上安排切割的一种组合,例如,我们可以将7.4米长的钢管截下2.9米的一根、1.5米的：根，所剩料头为0：或者截两根2.9米的、一根1.5米的，所剩料头为0.1米，可行的切割方案是很多的;爨应当确定哪些切割方案是合理的，通常假设一个合理的切割方案的余料应该很小，至少不应该大J或等厂客户需要的钢管的最小尺寸,在这种合理性假设下.可以选择的切割方案一共有4种,如下图所示：方案1I方案2I方案3I方案4于是，间麴转化为在满足用户制要的条件I：，按照现再种合理的方案使原材料最为节省。而所谓节竹,切割后剩余的料头和最小或切割原料钢管的总根数最少.第二步：模型

33、建立假设四种方案切割的钢管数分别为：x1.X2,X3.X决策目标：切割后剩余的料头最小.设切割后剩余的料头和为丫则目标函数为：MinY=OXI+0.1XN).2X田).3Xs约束条件为：Xi+2X2+X4=1(X)2X3+2X4=1003Xi+Xj+2X=100Xi,X2.X3.X40上述实际问题就变成这样一个数学问题.求解满足约束条件的Xl.X2,X.I.X，使丫达到最小值,这是个规划求解问题，建立完数学模型后，如果利用手工求解是非常耳杂的，我们可以利用EXCCl中的“规划求解”工具进行求解。第三步:数据准备利用Excel具体求解步骤如下：(I)启动EXeC1.新建一张工作表。(3)并且在B

34、2在元格中输入公式=B8*B9+C8*C9+D8*D9+E8*E9即规划求解的“口标函数”,也可以用=SUMPRoDUeT(B8:E8.B9:E9)如下图所示：现有资金20万元，准备购买冬季御寒衣物一批，在批发市场调查后得到的最低批发价如图所示：商品名称单价数责金额I鞋4501450帽120I120毛衣20012(X)围巾80I80外套5001500请问应该怎么购买才能刚好用掉20万元（注：确保每种产品都有,Jl鞋不能低于20双）.答：D7=SUM(D2D6：|ABCDE单价黔量金韵经费2450Il4502000003二12011204毛衣20012005H801806夕逢500n5007|1

35、350891011121314151617观刻驰E套数设看目坛:CD5tOS值(M)C）是小值坦目树6：Q）200000通显K可竞SB元格:但）$C$2：$C$6遵守约束;(U)SCS2=SCS6=SK$CS2：$C$6=1SC$3=20课后习题5I、数据表中给出了某厂12个车间加工同一产品所需时间的全部数据，如下图所示。SUM=SQRT(SUMXMY2(B23:B25rC24:C26)/3)1.S910I1.二用电SI(预溯CS45.850.5675小/ASNZA581484#N/A#N/A6585511r515053UWA75.3,r58.1003ffNZA80,r66.4345,7.77

36、76783f73.718417.678185,r704667,r6.6145691,82.7r4.305C708.2186.3667r3.64961lC56r914493IS1920222324252610111213141516172020m120S.2667r6.36358105733r7.27543112967f7.317261175r6C4811118-67r4.12312120233r118541122167r176572121333,2.73040125233fS.21376139067.165X3】59333：IQ834Q178767f20632103.216.33209.33f

37、15.719趋势移动平均法预测结果如下:SUM-:XA=OFFSET(A3,0,$H$3)/ABCDEFGH1数码产品季度销品绫计表3(Ql_4Q2,智能手机1508平板电脑31207Ql1508.14391509Q214395jQ315431333Q315436JQ414111265Q41411以E3:F6单元格区域中的数据为数据源来创建图表，如图7B-2b所示。在“开发工具”选项卡的“控件”组中单击“插入”按钮,在打开的列表中选择“表单控件”表中的“选项按钮”控件，拖动鼠标在图表中添加该控件,如图所示。将“智能手机”选项按钮复制一个（复制的方式最好是选中“智能手机”选项按钮币，按住Clrl键，然后用副标左键拖拽，如果用通常方式第制很容易复制成一个对象而不是控件）,将其，示的标题文字更改为“平板电脑”，按Qrl键同时选抒这两个控件，鼠标右击选择后的对象，选择关联菜单中的“组合组合”命令将它们组合在一起，如图所示。完成设电后就可以通过单击控件来选择图表显示的