|
Ａ、设置一级（Ｆ列）数据有效性
步骤１．建立辅助列，提取不重复的省份名称。
选定Ｊ２单元格，输入数组公式“=IF(AND(COUNTIF(J$1:J1,A$2:A$20)),"",OFFSET(A$1,MATCH(0,COUNTIF(J$1,A$2:A$20),0)0))",按<Ctrl+
Shift+Enter>组合键结束，并把公式向下复制到J20。
步骤２．把辅助列（Ｊ列）数据定义为名称。
把光标放在第２行，单击菜单“插入”——“名称”——“定义”（或按ＣＴＲＬ＋Ｆ３组合键）。在“名称”框中输入名称“Province"
引用位置“=OFFSET($J$2,,,COUNTIF($J$2:$J$100">"))"
步骤３．设置目标区域（Ｆ列）的数据有效性。
选定Ｆ２：Ｆ２０， 单击菜单“数据”——“有效性”，在“设置”选项卡中选择“序列”，“来源”中输入“＝Province”，单击“确定”按钮。
Ｂ、设置二级（Ｇ列）数据有效性
步骤４．按J列的省份名称把市I（地）名定义为名称。
按步骤２方法，定义名称"data"，引用位置为“=OFFSET($B$2,MATCH($J2,$A:$A,0)-2,COUNTIF($A:$A,$J2))”。
步骤５．建立辅助列，按省份分类显示市（地）名。
选定Ｋ２单元格，输入数组公式“＝IF(AND(COUNTIF($J2=""),"",OFFSET(data,MATCH(0,COUNTIF($J2,J2,data),0)-1,0))"，按CTRL+SHIFT
+ENTER组合键盘结束，并把公式向右再向下复制到N20单元格。
步骤６．根据Ｆ列选定省份，在Ｋ：Ｎ区域引用相关的数据行，并把它定义为名称。
把光标放第２行，用步骤２的方法再定义１个名称“City”，引用位置为“=OFFSET($K$2,MATCH($F2,Province,0)-1,,,COUNTIF(OFFSET($K$2,MATCH($F2,Province,0)-1,,,100,">"))"
步骤７．设置目标区域（Ｇ列）的数据有效性。
用步骤３的方法，设置Ｇ２：Ｇ２０的数据有效性，“来源”为“＝City”。
步骤８．设置三级（Ｈ列）数据有效性。
用步骤３的方法，设置Ｈ２：Ｈ２０的数据有效性，“来源”为“＝OFFSET（$C$2，MATCH（$G2，$B：$B，0）-2,,COUNTIF($B:$B,
$G2))"。
到此，全部设置完毕，效果与上个例子一样，但多了一级的选择。
３３．５数据有效性的规则与限制
设置数据有效性的条件时，应该注意其规则与限制，以避免做出错误和无效的设置。
３３．５．１ 数据有效性的规则
数据有效性的条件使用“自定义”时，当公式的结果返回逻辑值TRUE或非零数字时，条件才为真；其他结果，条件都为假。
３３．５．２数据有效性的限制
数据有效性的条件中，主要有以下几种限制条件：
１）不能直接使用对其他工作簿或工作表的引用，如“＝Sheet3!$A$1:$A$10”。如果需要，必须定义名称或使用IN
DIRECT函数。
２）不能直接引用联合、交叉引用或数组常量，如“＝OR（$A$1={1;2})”。
３）不能直接引用内存数组，如“＝ＲOW($1:$10)”。
第３４章 分级显示和合并计算
Excel的工作表分级显示功能提供了类似目录树的显示效果，它可以根据层次的需要显示不同级别的数据。用户可以根据需要展开某个级别，查看该级别下的明细数据，也可以收缩某个级别，只查看该级别的汇总数据。
合并计算是Excel的另一重要功能，它可以把多个工作表的数据，按字段、项目进行自动汇总计算，特别在报表合并、汇总数据时凸显其魅力。例如，一个公司可能有很多的分公司，各个分公司具有各自的销售报表和会计报表，为了对整个公司的情况进行全面了解，就要将这些分散的数据进行合并，从而得到完整的报表。
３４．１ 分级显示
分级显示能够将一个明细数据表中的数据按类别组合在一起，通过鼠标单击分级显示按钮，可迅速地设定只显示数据表格中那些提供汇总或标题的行或列，也可使用分级显示按钮来查看单个汇总和标题下的明细数据。比如一个银行的报表，总行只想看各省分行的汇总数据，省分行只想查看各市分（支）行的汇总数据，而不想查看其他下几级敌对支行或分理处的明细数据，此时使用分级显示就可以轻松实现。
３４．１．１ 分级显示概述
示例３４．１ 按地区，省份和季度分级显示数据表
图展示了一份已建立分级显示的数据表。该数据表是各地区、各省上半年每季度的汇总数据，以及各省每个月的明细数据。
如果希望隐藏各省明细数据，仅显示各地区的汇总数据，可用鼠标单击行分级显示按钮中的＋。此时，各省的明细数据行已全部隐藏，用户可以更直观地看到各地区每个月的汇总数据。
如果用户只想查看“华北地区”第一季度明细，可在表格单击“华北地区”左侧的分级显示按钮＋，不单击“第二季度”正上方的分级显示按钮“－”。
用户可根据需要单击行、列的分级显示按钮，显示不同级别下的数据，也可以单击行、列的分级显示按钮进行任意组合，以查看局部数据，不同组合显示不同的查看效果。
３４．１．２ 建立分级显示
为数据表格建立分级显示，有自动建立、手动建立和对数据进行分类汇总３种方法。
１．自动建立分级显示
自动建立分级显示对数据表格的要求较高，如果表格具备以下特征，用户可以使用自动分级显示。
１）同一组中的行或列均放在一起，把属于各地区的省份的行都放在一起。
２）汇总行均在每组数据的上方或下方，汇总列均在每组数据的左侧或右侧。汇总行和汇总列使用求和公式SUM或分类汇总公式SUBTOTAL引用数据中的单元格。在图所示的表格中，单元格C7公式是“=SUM（C2：C6）”，单元格F2公式是“=SUM（C2：E2）”，单元格K2的公式是“=F2+J2”。
示例３４．２自动建立分级显示
图展示了数据表在未建立分级显示前的状态，各汇总行、列已用求和公式ＳＵＭ引用了各求和单元格。要自动建立分级显示，可按以下操作步骤执行。
步骤１．选定需要分级显示的单元格区域Ａ１：Ｋ３９，若要对整个工作表的数据区域分级显示，可以选定任意单元格。
步骤２．单击菜单“数据”——“组及分级显示”——“自动建立分级显示”。
如果工作表已有分级显示，Excel将弹出对话框，请单击“确定”按钮，Excel会用新的分级显示替换掉原有的分级显示。
通过上述操作后，Excel将自动根据用户小计行或列、合计行或列中的公式来判断如何分级，并建立工作表分级显示。
若用户只需要建立行的分级显示，请在步骤１选定单元格区域Ａ１：Ｃ３９，再执行步骤２。
若要对建立的分级显示应用样式，步骤２操作如下：
单击菜单“数据”——“组及分级显示”——“设置”对话框，根据数据汇总行、列的方位，决定是否勾选”明细数据的下方”和“明细数据的右侧”复选按钮，再勾选“自动设置样式”复选框。最后单击“创建”按钮。
应用样式后，所有的小计行（第２级别）都使用斜体字形，字号为Excel默认的12号，而不是用户原来设置的字号（本例设置为10号），所有的合计行（第1级别）字体都加粗，字号与小计行相同。
２．手动建立分级显示
要手动建立分级显示，要求数据中同一组中的行或列均放在一起，汇总行均在本组数据的上方或下方，汇总列均在本组数据的左侧或右侧，汇总行、列中不要求使用公式。
注意：每个汇总行在其所在组中的位置必须一致，即要么都在上方，要么都在下方。汇总列亦同。
示例３４．３手动建立分级显示
步骤１．把光标定位行号处，选定华北地区省份所在的第２～６行（不含小计行）。
步骤２．单击菜单“数据”——“组有分级显示”——“组合”。
步骤３．用同样的方法对其他地区进行组合。
步骤４．选定所有地区的行，不含合计行，即第２～３８行，单击菜单“数据”——“组及分级显示”——“组合”。
通过上述操作后，就建立了行分级显示，如果还要对列进行分级显示，可用同样的方法对列进行组合。
注意：对行进行组合时，不能把汇总行（如本示例中的小计、合计）与明细数据行一起选定进行组合。
３．利用分类汇总建立分级显示
当用户选定单元格区域，执行菜单“数据”——“分类汇总”命令后。Excel会自动使用SUBTOTAL函数插入分类求和公式，并自动根据汇总字段，建立行向分级显示。
３４．１．３清除分级显示
当用户不需要分级显示时，可清除分级显示。清除分级显示不会改变任何数据。操作步骤如下：
步骤１．单击分级显示按钮中的最大数字，以显示所有明细数据。
步骤２．选定数据区域内任意单元格，单击菜单“数据”——“组及分级显示”——“清除分级显示”。
通过以上操作后，分级显示就会自动清除。
注意：建立分级显示和清除分级显示都不能使用“撤消”按钮来撤消，在操作前，请慎重考虑是否建立或清除。
深入了解 分级显示的深入理解
（１）一个工作表只能有一个分级显示。
（２）分级显示最多只能有８个级别的明细数据，每个内部级别为前面的外部级别提供明细数据。“总计”行为第１级别，各地区的汇总行为第２级别，其他数据为第３级别。若要显示某个级别的行，可在分级显示按钮中单击想要查看的级别对应的数字。
（３）可以在不删除整个分级显示的情况下取消分级显示中某些部分的组合。操作方法为：按住SHIFT键，单击该组的分级显示按钮，再单击菜单“数据”——“组及分级显示”——“取消组合”。
（４）若要在不删除分级显示的情况下隐藏分级显示，可单击菜单“工具”——“选项”，再单击“视图”选项卡，在“窗口选项”中，取消“分级显示符号”复选框的勾选。
３４．２合并计算
在Excel中可以通过合并计算功能来汇总一个或多个源区域中的数据，具体有两种方法。一是通过位置，即当源区域有相同位置的数据汇总。二是通过分类，当源区域没有相同的布局时，则采用分类方式进行汇总。
合并计算的源区域可以是同一工作簿中的多个工作表，也可以是多个不同工作簿中的工作表。
３４．２．１对同一工作簿中多个工作表的合并计算
假设在汇总公司员工全年工资时，由于每个月都可能发生员工入职或离职，而且排序也不完全一样，因此，用户必须通过分类方式进行合并计算。
示例３４．４ 合并计算员工年度工资
图展示了某公司１年中每个月份的工资总额。每个月份的数据都保存在单独的工作表中，而且格式相同。Ａ列为该月份在职员工姓名，Ｂ列为员工在该月份的工资总额。公司必须计算出每位员工全年的工资总额，以确定全年应缴交社保的月数和基数。为了合并计算方便，用户需先把每个月的工资总额列的标题改为该月份，如１月、２月等。
具体操作步骤如下：
步骤１．在汇总表第一行输入如图所示的内容。
步骤２．选定Ａ１：Ｍ１（也可以整行选定），单击菜单“数据”——“合并计算”，Excel弹出“合并计算”对话框，在“函数”下拉列表框中选择“求和”。]
步骤３．单击“引用位置”框，接着单击１月工作表标签以激活１月工作表，鼠标指向列标处以选定Ａ：Ｂ列，“引用位置”框中会自动输入“１月”，单击“添加”按钮，“所有引用位置”就自动添加一条记录“１月”。
步骤４．单击２月工作表标签以激活２月工作表，“引用位置”框中自动显示“２月”（若显示的区域不正确，则需要手动选择正确区域），再单击“添加”按钮。
步骤５．用同样的方法，添加其他月份的引用位置。
步骤６．勾选“首行”和“最左列”复选框，如果需要创建连至源数据的链接，则勾选“创建连至源数据的链接”复选框，最后单击“确定”按钮。
通过此操作后，合并计算就完成了，结果如图所示。全年所有在职过的员工都排在Ａ列，每个月的工资总额也全部排列在各月份下，如果某月份没有该员工的记录，则该单元格为空。如最后一行的“苏永中”，只有１月份有工资，因为该员工２月份已离职，只领了一个月工资。
步骤７．在Ｎ２输入公式“=COUNT（B2：M2）”，在O2输入公式“=SUM（B2：M2）”，再选定N2：O2，把公式下拖复制到第30行，即计算出每位员工的缴费月数和缴费基数。
如果不需要计算应缴费月数，可把各月份工作表的Ｂ１单元格都改为同样的内容，如“工资总额”，汇总表Ａ１和Ｂ１分别输入‘姓名’和“工资总额”，再按步骤２～步骤６操作。
３４．２．２ 对不同工作簿工作表的合并计算
分公司往往将各自的报表保存在单独的工作簿文件上，报送到总公司。此时总公司必须将各公司的报表合并计算，才能形成总公司的报表，这就需要对多个工作簿的工作表进行合并计算。
示例３４．５ 汇总分公司报表
图分别是济南和青岛分公司报送的报表，总公司要汇总的报表，因为报表的布局完全一样，用户可以通过位置来合并计算数据。
步骤１．打开总公司的汇总报表文件，选定要汇总区域的左上角单元格Ｂ３，单击菜单“数据”——“合并计算”，Excel弹出“合并计算”对话框，在“函数”下拉列表框中选择“求和”。
步骤２．单击“浏览”按钮，Excel弹出“浏览”对话框，在“查找范围”下拉列表框中，定位到济南分公司报表文件所在的文件夹。
步骤３．单击“济南.xls”文件图标，再单击“确定”按钮， Excel自动关闭“浏览”对话框，并在“合并计算”对话框的“引用位置”中输入完整路径及文件名，后面附加符号“！”（‘H：\报表 \济南.xls‘！）。
步骤４．修改“引用位置”框中的内容为：［文件名］＋工作表名＋英文感叹号（！）＋引用的单元地址或名称。也可修改为包含完整路径：完整路径＋［文件名］＋工作表名＋英文感叹号（！）＋单元格地址或名称。
步骤５．单击“添加”按钮，“引用位置”的字符串将被添加到“所有引用位置”列表框中。
步骤６．重复步骤２～步骤５，将青岛公公司的数据来源也添加到“所有引用位置”列表框中。
步骤７．单击“确定”按钮，济南和青岛分公司的报表将被合并到汇总表中。
３４．２．３ 自动更新合并计算的数据
创建合并计算后，用户可以利用链接功能来实现计算的自动更新，这样当源数据改变时，Excel会自动更新合并计算表格中的结果。要实现该功能，需在创建合并计算时在“合并计算”对话框中勾选“创建连至源数据的链接”复选框。如此，合并计算表格中将插入外部引用公式、链接到源数据区域并自动建立分级显示。
注意：当源和目标区域在同一张工作表时，则无法建立这种链接。
３４．２．４ 为合并计算添加、删除或修改源区域引用
对于一个建立合并计算的工作表文件，用户还可以进一步编辑，以添加、删除或修改对源区域的引用。
注意：这些操作仅适用于合并计算表格没有建立与源区域的链接的情况下，否则必须先删除合并计算表格中的数据，需要的话还要清除分级显示。
１．添加源区域引用
仍以汇总公司报表为例，如果总公司新收取南京公司报送的报表，则需要把此报表的数据也汇总到汇总表中。
示例３４．６ 为合并计算汇总表添加分公司报表
图显示的是南京分公司报送的报表。
步骤１．选定总公司报表的Ｂ３单元格，单击菜单“数据”——“合并计算”，Excel弹出“合并计算”对话框。
步骤２．如果南京分公司报表处于打开状态，单击“引用位置”框，在任务栏单击南京分公司的文件图标，以激活南京分公司的工作簿，选定Sheet1工作表的单元格区域B3：D6，单击“添加”按钮，最后单击“确定”按钮。Excel将重新计算“所有引用位置”框中的数据区域。
如果南京分公司报表不在打开状态，重复上例中步骤２～步骤５，再单击“确定”按钮。
２．删除或修改源区域引用
合并计算引用的源区域，也可以被删除或修改。
１）删除一个源区域引用
步骤１．选定合并计算表格中目标区域左上角用第１个单元格，单击菜单“数据”——“合并计算”命令。
步骤２．在“合并计算”对话框的“所有引用位置”框中选定想要删除的源区域。
步骤３．单击“删除”按钮，最后单击“确定”按钮，利用新的源区域来重新合并计算。
注意“源区域引用被删除后，不可使用“撤消”按钮来撤消删除，只能重新添加。
２）修改一个源区域引用
步骤１．选定合并计算表格中目标区域左上角第１个单元格，单击菜单“数据”—合并计算”命令。
步骤２．在“合并计算”对话框的“所有引用位置”框中选定想要修改的源区域。
步骤３．在“引用位置”框中修改引用源区域，单击“添加”按钮。
步骤４．删除原有引用源区域，利用新的源区域来重新合并计算，单击“确定”按钮。
也可以直接把原有引用源删除，再添加新的引用源。
|
|
|
来自： 昵称380475 > 《Excel 大全》
