用Excel2007函数组合求每页小计之总计

用Excel2007函数组合求每页小计之总计

2009-02-06 18:56 作者：Office吧责任编辑：caijunjun (评论0条)

一般会使用求和函数SUM，但笔者做的工资财务报表页数很多，使用SUM函数时很麻烦。要先输入该函数，再用鼠标点选参加计算的单元格，再按加号。

再拖动Excel2007表格找到另一页小计单元格点选，很多时候会点错或看错单元格，导致最后结果错误，又得重来。有没有更简单安全的方法呢？

经试验发现通过一些函数组合就可以实现隔行求和。报表要求每页10人，在每页第11行设计一个小计，表格的最后一页统计出各页小计的总和。所有数据输入完成后，设置好页面并给每页添好小计行，并计算出每页小计数据。

接下来在所有页后的任意一行设计出总计行，在相应的总计单元格中输入公式“=SUM（IF（MOD（ROW（D5:D224）-4,11）=0,D5:D224））”后，按下“Ctrl+Shift+Enter”组合键，公式处出现数组符号“{}”，同时在相应单元格中得到总计。

最后向右填充即可得到所有需要的合计数据了，把所得结果选择性粘贴到财务报表需要的位置即可，方便吧！

PS：本文适用于所有Excel，不局限于Excel2007！

★Lookup——数与行列比

Lookup的工作职责是什么呢？用一个数与一行或一列数据依次进行比较，发现匹配的数值后，将另一组数据中对应的数值提取出来。

·工资税率表：用数值比较

根据不同的工资进行不同的税率计算是一个常见的应用。我们来看这张“工资税率查询”表（见图1）。现在要在右侧根据“收入”（F列），直接得到对应的“税率”（G列）。在计算第1个“税率”时，输入函数公式“=LOOKUP（F4，$B$3：$B$8，$D$3：$D$8）”，回车，便可得到“36.00%”。

图1 数值比较

这个结果是怎么来的？用F4中的第1个收入数“$123，409”，与左侧表的“收入最低”各档数据（“$B$3：$B$8”）进行对比，虽然“$123，409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是会与其中小于它的最大数“$58，501”相匹配。这样，同一行对应的“36.00%”就提取出来了。

·图书销售表：用文本比较

Lookup函数的对比数还可以是文本。在这张图书销售查询表中（见图2），用下表输入的“编号”（A15单元格）文本当作查询数，与上表的“编号”一列（$A$3：$A$11）进行对比，查询到了匹配的文本后，将“教材名称”一列（$B$3：$B$11）对应的数据提取出来。公式是“=LOOKUP（A15，$A$3：$A$11，$B$3：$B$11）”。

图2 文本比较

★Vlookup——数与表格比

Lookup有一个大哥——Vlookup函数。两兄弟有很多相似之处，但大哥本领更大。Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行，并且Vlookup可以选择采用精确查询或是模糊查询方式，而Lookup只有模糊查询。

·模糊匹配

用Vlookup函数进行模糊查询时，几乎与Lookup的作用完全一致。我们用Vlookup函数来提取第1个例子中的工资税率结果。函数公式为“=VLOOKUP（F4，$B$3：$D$8，3，TRUE）”。

在这个函数中，用第1个收入“$123，409”（F4单元格）当作对比数，用它与左侧表（“$B$3：$D$8”）的第1列数进行对比，虽然“$123，409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是函数的最后一个参数是“TURE”（“TURE”就是模糊查询），所以它会与其中小于它的最大数“$58，501”相匹配。并将表中第3列（函数的第3个参数为“3”）对应的数据提取出来，所以结果同样是“36.00%”。

·订单明细表：精确匹配

有时候，我们需要精益求精。在下面这个“订单明细表”（见图3）中，最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司收费表”中进行匹配查询。这是一个典型的精确查询的例子，计算第1个数据的函数公式是“=VLOOKUP（H3，$B$2：$D$6，3，FALSE）”。

图3 精确匹配

小提示：

把最后一个参数从“TRUE”变更成“FLASE”，就是精确匹配。而精确查询，就是查询数要与查询表第1列中的数据完全一致才能匹配提取，否则结果返回错误值“#N/A”。

名师点评：

Excel为我们提供了近20个有关“查找和引用”的函数，除了最常用的Lookup、Vlookup，还有Choos、Row、Colum、Index和Match等，大家可以通过

函数的帮助查看具体的功能。这些函数往往不是单独使用，可以与其他函数和Excel中的一些功能进行配合。希望大家以后再为数据“找朋友”时，不再有“众里寻它千百度”之感，直接“慕然回首，那数已在灯火阑珊处”了。

用Excel的高级筛选比较数据表的异同

2008-02-22 09:30 出处：其它网站作者：佚名责任编辑：shiyun (评论0条)

我们工作中经常会遇到这种需求，有两个数据表，想要知道两个表的公共部分和独有部分，高级筛选就可以达到此目的。例如库房里有一个总的件号明细表，今天有一个销售明细表，想要知道今天销售的那些是库房里还有的，哪些是库房里没有的，并分别表示在两个表里，其实就是求出两个表的公共部分，就可以用高级筛选功能来实现。

图1 实例

实例：如上图1，左方是库房存货，右方是今天销售的货号，想求出两者的

公共部分。

步骤一：把光标放在左方数据表的任意单元格，从“数据／筛选／高级筛选”里调出“高级筛选”对话框，并在“数据（列表）区域”和“条件区域”分别如图示填上内容。

步骤二：点击确定按钮得到如下图2所示结果。

图2 筛选结果

步骤三：在库存表的最右一列里填上一个“1”，并把“1”复制到整个表的最右一列。如图3。

图3 复制操作

步骤四：点击菜单“数据／筛选／全部显示”（2007里是“清除”按钮），使筛选后隐藏的内容显示出来。如图4。