财务人必会的excel功能分类汇总.doc

财务人必会的excel功能分类汇总

excel贯穿财务工作之中，非常方便实用。但是绝大多数的财务人员都不知道excel的隐藏功能。
假设您要建立一个其中每个账户代码均可出现多次的月度事务列表。在月末，您需要按账户代码对数据进行组织和分类汇总。以下是这5种方法的介绍：
注释：示例数据集中的账户号码在列A中，金额在列B中。数据区域是A2:B100，数据目前未排序。
方法1：使用创造性的IF语句和选择性粘贴
步骤如下：
按账户(列A)对数据进行排序。
在列C中创建一个公式，以持续对每个账户进行动态汇总。例如，在单元格C2中创建公式：=IF(A2=A1,C1+B2,B2)
在列D中创建一个公式，以标识特定账户的最后一个条目，例如，在单元格D2中创建公式：=IF(A2=A3,FALSE,TRUE)
将C2：D2中的公式向下复制到所有行中。
复制C2：D100。在此区域仍处于选中状态时，单击编辑菜单上的选择性粘贴，单击数值，然后单击确定，将C2100中的公式改为数值。
按列D排序，降序排列。
对于在列D中值为TRUE的行，列A中是账户号码的唯一列表，列C中是账户的最终动态汇总。
优点：速度快。只需对写IF语句感觉敏锐。
缺点：还有更好的方法。
方法2：使用高级筛选获得唯一账户列表
这是一种获得唯一账户号码列表的方法：
突出显示区域A1:A100。
在数据菜单上，指向筛选，然后单击高级筛选。
单击将筛选结果复制到其他位置。
选中选择不重复的记录复选框。
选择要在其中显示唯一列表的工作表空白部分。将此位置键入复制到框中。
注释单击将筛选结果复制到其他位置之前，复制到框显示为灰色。
单击确定。唯一账户号码将显示在输入的位置。
输入获得结果所需的所有进一步操作、数组公式等。
优点：比方法1快。无需排序。
缺点：此后所需输入的数组公式将使您头晕。
方法3：使用合并计算命令
此方法使用合并计算命令，这有几项要求：账户号码必须在要汇总的数值字段的左侧。每列上方必须有标题。需要对其中包括左列中的账户号码和顶部标题的单元格矩形块指定区域名称。在本例中，该区域为A1:B100。
突出显示区域A1:B100。
通过在名称框(在编辑栏左侧)中单击并键入TotalMe之类的名称，对此区域指定区域名称。(也可以在插入菜单上单击名称。)
将单元格指针置于工作表的空白部分。
在数据菜单上，单击合并计算。
在引用位置框中，键入区域名称(TotalMe)。
在标志位置部分，选中首行和最左列。
单击确定。
优点：无需排序。可用一系列键盘快捷键将其实现：Alt+D+N(区域名称)、ALT+T、ALT+L、Enter。易于缩放。如果区域包括12个月份列，则结果将是每月的汇总。
缺点：如果在同一工作表上再次使用合并计算功能，则需要通过使用Delete键从所有引用位置中清除旧区域名称。账户号码必须位于数值数据的左侧。这要比数据透视表稍慢，对于具有超过10,000个记录的数据集，这会变得很明显。
方法4：使用分类汇总命令
这是一种很棒的功能。但因为得出的数据处理起来很陌生，所以与合并计算相比，您可能不常使用此功能。
按列A排序，降序排列。
选择数据区域内的任一单元格。
在数据菜单上，单击分类汇总。
默认情况下，Excel支持对最后一列数据的分类汇总。这在此例中有效，但您往往必须在选定汇总项列表中滚动才能选择正确的字段。
单击确定。Excel将在每当更改账户号码时插入一个新行，并进行分类汇总。
添加汇总后，您将看到小按钮1、2和3显示在名称框下，单击2只查看每个账户其中有汇总的一行。单击3查看所有行。
优点：很棒的功能。极适于打印有汇总和每节后都有汇总的报告。
缺点：必须先对数据进行排序。对于大量数据，这可能会很慢。必须使用定位命令(编辑菜单)，然后单击定位条件才能只选择可见的单元格，将汇总移动到其他位置。必须使用分类汇总命令(数据菜单)，然后单击全部删除才能恢复原始数据。
方法5：使用数据透视表
数据透视表是所有解决方案中最全面的。不必对数据进行排序。数值列可位于账户号码的左侧或右侧。可轻易使账户号码向下或跨页排列。
选择数据区域内的任一单元格。
在数据菜单上，单击数据透视表和数据透视图。
单击下一步接受步骤1中的默认设置。
确保步骤2中的数据区域是正确的(通常是正确的)，然后单击下一步。
单击步骤3中的布局按钮。(Excel97用户会自动转到布局作为步骤3。)
在布局对话框中，将账户按钮从右侧拖放到行区域。
将金额按钮从右侧拖放到数据区域。
单击确定。(Excel97用户单击下一步。)
指定是要将结果放在新工作表中还是放在现有工作表的特定部分内，然后单击完成。
优点