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

上机题11、在EXCel条件格式中运用公式进行筛选公式是使用Excel处理和分析数据的重要工具，在Excel的条件格式中使用公式能够速准确地筛选到满足条件的数据，从而更好地发挥Excel的条件格式功能。在Excel的条件格式中使用公式的具体操作步骤如下.打开素材，选择F2:F14单元格区域，单击“开始”选项卡下“样式”选项组中的“条件格式”按钮，在弹出的下拉菜单中选择“新建规则”选项，如图IB-Ia所示:文件插入页面布局公式数据审阅视IX粘我最前/最后先翦贴板0就条件格式-突出显示单民三中义治?赤海卫正运历陈扬刘任罗相-J部部8p三郃部CJI发务试发试务百开财测开溯财i_*i1'l'H11BO男男女男男女DE职务Il都门处理10国皴职员12高级职员8高级取员0言通职员2晶门妞理3SSKa6言遢联员6高皴职员7善通也贡8第门经理978&500430043003600360043005000525000GSOO28007000IB-图数据条9)色阶(三)图标集(0新建规则)规则gA三sr11a÷11rln¾弹出“新建格式规则”对话框，在“选择规则类型”列表框中选择最后一项“使用公式确定要设置格式的单元格”选项，在“编辑规则说明“选项区域“为符合此公式的值设置格式”文本框中输入公式“=F2>1.ARGE($F$2:$F$14.4)”。输入完成后单出“格式”按钮来设W.相应的格式，如图IBJb所示：图IB-Ib在“新建规则”中输入公式单击“确定”按钮即可将设置好的格式应用到销售额排名前：的数据所在的单元格中,效果如图IB-IC所示。123456-3男男女男男女男女男男男男女1部部部部88部部部都部部部部Sil米务试发试务发试发发«发务*开财测开测财开观开开洪开财B11S3sa5高娘昭员鬲娘联QSfiKS部门经住工龄工由ioI700126W843043360033S00言息织员6告通职员6高级岷黄7百通织员8部门蛙理O°51三通取五10GrOG图IB-IcItJHJ公式的式效果提示：公式中的“1.ARGE(SF$2:$F$14.4)”表示在E2:F14单元格区域中的数值按从大到小的顺序排列，排位第4的值。很显然，这实际上是在进行类似于“数字筛选”的筛选操作，对筛选出的结果应用指定格式.所以条件格式本质上仍然是筛选。同样的，如果要选择最小的三位，可改成“=F2<SMA1.1.($FS2:SF$I4,4)”。另外，如果排名第.三位的值有两个，则总共只有两个单元格应用r指定格式,而不是我们通常以为的会有四个单元格(即存在并列第三)应用指定格式。2、在筛选结果中重新排序我们都曾遇到过这种情况：数据在筛选前序号(如排名是连续的，但筛选后就不连续了，如图IB-2所示：ABCDE2019年云贵川三省城市GDP博名前十排名«1BJII2云雨3贵州4QJII市区GDP总量13102506893241.981610.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四川成都1213102云南昆明7150689贵州四川四川四川贵州-云雷云南四川图lB3a将取&散粥区域转化为EXCd表接着在A2单元格输入公式=SUBToTA1.(IO3.B$2:B2),该公式使用了汇总函数SUBTOTA1.,其第一个参数表示counta统计，即统计第二个参数所表示的区域中非空单元格个数，此时SUBTOTA1.就在这些个数中自动给出排名，如图IB-现在要改成按“数学”排序，如果选中整个表，势必会使“序号”发生改变，因此只需选中B2:EII单元格区域，将“主要关键字”设置.为“数学”后进行降序排列即可，如图IB-Ib所示：图IB-4b不变更序号行了排序更仃甚者，如果连字段也没有，例如下图lB-4c所示表格:ABC1语文成绩表21杨源喜tf32雷民8143刘卫中9054柳星9265张爱国8876桐明玉7487马献华6798罗远洁62109任正义65知IBTC没行原来的数据是随机的，现在需要按姓名发音排序，只需选择B2:ClO雌元格区域，在“排序”对话框中去掉“数据包含标题”前的对勾，然后将“主要关键字”设置为“列B”后升序排序即可。提示：在排序之前选中数据区域则只对数据区域内的数据进行排序。实际上，让序号不参与排序，使用的原理就是前面讲到的，只选择部分字段排序，排序结果只影响部分字段。本例中就是除/序号以外的“部分字段”参与了排序，所以结果只影响了除了序号以外的其他字段。课后习题1I、在自定义筛选时，如果要筛选“川”开头的字符串，可使用的方法有哪些？如果要筛选包含“川''的字符串，乂该怎么做？2,操作：使用筛选汇息分析员工工资本例将统计分析员工的工资，涉及以卜内容：按照“工资”降序排列按照“部门”升序排列,按照“工资”降序排列筛选出“高级职员”的相应数据筛选出“部门”为“开发部”、工资低于5000的数据以“性别”为分类依据，统计男女的人数以“部门”为分类依据.统计各部门的工资总和原始数据如图所示：ABCDE12取工姓名陈亦民杨源军刘卫中任正义罗远洁和原张爱国何明玉马旌华李光东雷民n三马羽W里处女里丹女再女SM:男再女部门开发部财夯部工龄10工资78005004300430036003600430050005200500065002800ZDOO3124567891011121314Pliitse开发部S1i三财夯部开发部副过部开发部开发部则成都开发部财务部892366789110请将每个操作步骤的结果被图保存。操作如F：按列简单排序：直接点击“工资”列中任意一个单元格后，在“数据I排序和筛选”组中单击“降序”按钮JU即可.多关键字排序：在“'数据排序和筛选”组中单击“持序”按钮五在打开的“排序”对话的中设置主要关键字和次要关键字即可。自动筛选：在“数据排序和筛选”组中.单击“筛选”按钮T,打开“职务”标题的下拉箭头后选择“高级职员”即可。高级筛选：先退出自动筛选，然后数据表中建立如下图所示的条件区域：117,部门IT-IIisI开发部匚遨ZI再在“数据I排序和筛选”组中雎击“高级筛选”按钮V,在师出的“高级筛选”对话框中选择筛选的数据区域和条件区域，完成高'级筛选。最后，再在“数据I排序和筛选'组中单击“清除”按钮*,清除当前的筛选。 分类汇总：首先在“数据排序和筛选”组中单击“持序”按钮77,在打开的“排序”对话框中设置主要关键字为“性别”、次耍关键字“部门”后进行排序,本次分类汇总只需用到“性别”这个分类依据,“部门”这个评估依据是为卜面的操作预告准备的：排序完成后，再单击“数据分级显示”成中的“分类汇总”按4唱i,在弹出的“分类汇总”对话框中以“性别”为分类依据、以“计数”为汇总方式,以“职工姓名”为汇总项进行汇总。 嵌套分类汇总：用同样方式再次进入“分类汇总”对话框，以“部门”为分类依据、以“求和”为汇总方式，以“工资”为汇总项进行汇总,此时注意要清除“替换当前分类汇总”及选框。3、操作：利用模糊筛选从给定范用中筛选出适当的数据模糊筛选通常也可称为通配符筛选，模糊筛选常用的数值类型有数值型、日期型和文本型，通配符“？”和“只能配合“文本型”数据使用,如果数据是日期型和数值型，则需要通过设置限定范围（如大于、小于、等于等）来实现。例如，筛选出姓“马”且名字只有一个字的人名的顼:鸵工号XgOOlZgoO2ZgOO3ZgOO4zg005ZgoO6zg007ZgOO8zg009名.11.部具体操作步骤如下：选择列表任意一个单元格，单击“数据I排芹和筛选I筛选“按钮，在标鹿行每列的右侧出现一个下拉按钮，然后单击“姓名”列右侧的下拉按钮，在弹出的下拉列表中选择“文本筛选I等于”（或“文本筛选I自定义筛选”）选项，如图所示：12345678910n12l3141516lr18l,J202122x股工号ZgOOlzg002ZgOO3Zgocl4NOo5ZgOo6ZgOO7NgOo8“009ZgOlOZgOllzg012“013ZgOUzg015zgl6Ig(H7zg!8zg019BI_CD姓名性别部门名祢丁杨源百看民IIH办公空办公主升序(三)降序(Q)按颜色排序CD工作表视图M从姓名“中清除筛选(按颜色筛选(1)文本笳选(E)搜索a(全选)等于和T(M).开头是(1)151921及23242S262728293031323334第3637都将弹出“自定义自动筛选方式”对话框,在“等于”后面的文本框中输入"马？,注意此处的问号是英文状态下输入的，单击“确定”按钮，即可筛选出姓“刘”且名字只有一个字的人名,如出所示:职3W三jZgoO4马羽女zl8马苗女部门名称倘售部开发部自定义自动筛选方式显示行：姓名卜于g画与OSc(Q)IEIF可用？代表单个字符用惊任意冬个字行提示：通配符中"?"代表单个字符，可代表多个字符.如输入“马?表示姓马，H.名字只有个字，输入“马*”则表示姓刘，且名字至少是一个字。此例中，如果中使用快捷文本快选项“包含”“不包含”“开头是”“结尾是”等几个选项是达不到目的的.必须使用自定义选咖，以公式来筛选.上机题21、应用数据透视表进行多地区经济指标统计分析以某市1辖各县市区2O182O2O年的各项经济指标为依据,创建数据透视表并进行相应的分析”原始数据如图2B1所示：jC画定,广投奥2.74D耳国W生产总值国艮生产总值指数人均GDP7813.073831.904161.544659.992120.352348.542662.089456.8410606.8512442.872175.684212.824757.45109.22109.71109.03108.99109.60109.301