分类：首页

如何快速实现跨表查询？

当需要把多个工作表内的数据汇总到同一张工作表内时，你是用什么方法去实现的呢？逐个复制粘贴耗时又耗力，反复修改公式也麻烦。成都office培训中心小编说，其实只需要把VLOOKUP函数和INDIRECT函数结合起来使用，就可以写出一个通用的跨表查询的公式了。

1月到4月内的工作表内容如下图所示：

只需要在汇总表格的B2单元格内输入公式：=VLOOKUP($A2,INDIRECT(B$1&”!A:B”),2,0)后向右向下拖动填充剂可完成最终的数据汇总

公式说明：

  1. INDIRECT函数作用：返回由文本字符串指定的引用
  2. 本例中INDIRECT函数指定的引用为：INDIRECT(B$1&”!A:B”)。即为以B1单元格内容为名称的工作表内的A列到B列单元格。如B1单元格内容为1月，INDIRECT则指定引用1月这张工作表内的A列到 B列
  3. 其中B1单元格有使用混合引用锁定了单元格所在行，当公式向下填充时，此单元格所在行不会发生变化。当公式向右填充时，其所在列发生相应变化，依次变为C$1、D$1、E$1以便形成动态引用
  4. 当公式从B2单元格向右填充到 C2单元格时，则引用名为2月白的这张工作表内的A列和B列内容，以此类推。
  5. 最后，现使用VLOOKUP函数去查询相应的数据即可 。

如何在求和时忽略错误值？

对数据进行汇总计算是很多工作中都会遇到的问题。一般情况下来说，求和汇总是一个很简单的问题，只需要SUM函数出马即可。如果再复杂一点需要带条件求和呢，还有SUMIF、SUMIFS和SUMPRODUCT这一众求和函数可以即刻走马上任。而成都office培训机构小编今天遇到的问题，却不单单是求和的问题了，为大家呈上源数据：

如上图所示，需要计算总销售额。而问题也是一目了然的，就是在求和的数据中出现了错误值。错误值出现的原因很多，这里就不一一说明了（本案例中的错误值是因为单价列查找匹配数据时出现未找到的情况，导致销售额计算结果也出现错误值）。今天主要想分享的，是如何在一众数据中忽略其中的错误值进行求和。小编为大家总结了3种方法，相信总有一种方法是属于你的。

方法1：sum函数和iferror函数

在E18单元格中输入函数：=SUM(IFERROR(E2:E17,””))后按Ctrl+Shift+回车三键结束哦（如果您使用的是office2019或者office365版本则可以直接按回车结束哦）

公式说明：

  • IFERROR函数作用：检查公式计算结果是否为错误值，如何公式结果不是错误值，则返回公式本身的计算结果，如果公式计算结果为错误值，则返回指定结果
  • IFERROR函数参数：IFERROR(检查是否存在错误的公式, 公式计算结果为错误值时要返回的结果)
  • 在本案例中，是先使用IFERROR检查E2:E17区域中的公式计算结果是否存在错误值，如果存在则将错误值显示为空（”” 代表没有内容，在计算时，默认以0参与计算）后，再使用sum函数对E2:E17区域中的数字进行计算即可
  • 如果小伙伴觉得将错误值显示为空不好理解的话，也可以直接将错误值显示为0哦

方法2：SUMIF函数

在F18单元格输入公式：=SUMIF(F2:F17,”>0″)

公式说明：此方法是使用sumif函数对F2:F17区域中大于0的数据进行求和，自然就能够把错误值排除在外（sumif函数的求和区域忽略时，则默认对满足条件的条件区域内的数据进行求和，如本例中没有求和区域，在满足条件后，sumif则直接对条件区域F2:F17区域内的数据进行求和）

方法3：使用AGGREGATE忽略错误值求和

在G2单元格输入公式：=AGGREGATE(9,6,G2:G17)

公式说明：

  • AGGREGATE函数作用：返回列表或数据库中的合计。 AGGREGATE 函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。
  • AGGREGATE函数参数：AGGREGATE(指定要使用的函数, 计算区域内要忽略哪些值,需要计算的对象1, 需要计算的对象2, …)
  • AGGREGATE函数的第1和第2参数都是数字代码模式，具体情况如下：
    • 参数1代码与函数对应图
  • 参数2代码与忽略值对照表

本例中，需要用到的函数为求和，所以AGGREGATE第1参数为9，需要忽略的是计算区域中的错误值，所以AGGREGATE函数第2参数为6，计算区域则为G2:G17区域

今天的分享到这里就结束了，你会选择哪一种方法呢？

VLOOKUP和CHOOSE实现多条件查找

根据条件查询匹配数据，是很多从事文员行政等工作的小伙伴们经常会遇到的问题。当你碰到一样的需求时，你会使用什么方法去完成呢？看到这个地方，可能就会有小伙伴说，这还不简单，用VLOOKUP和MATCH函数就可以了。 是的没错，在二维表格内，VLOOKUP和MATCH是可以实现多条件查询的，可如果是在一维表格里又该怎么办呢？且看成都office培训学校小编如何做：

如下图，表格内统计有公司个销售员的业绩明细数据，现在需要根据员工姓名和所在部门去查询相应的业绩信息：

在这个案例里面，需要使用到的函数有两个，分别是VLOOKUP和CHOOSE函数，G2单元格内公式为：=VLOOKUP(E2&F2,CHOOSE({1,2},A2:A18&B2:B18,C2:C18),2,0) （注意：此公式为数组公式，office365以下版本都需要按Ctrl+Shift+回车三键结束）

公式解释：

  1. VLOOKUP函数的查询依据为E2&F2
  2. CHOOSE函数的作用是根据给到的索引值，返回索引值对应位置的value值，参数形式为CHOOSE(索引值，value1，value2，value3)，其中，value的数量不能低于索引值（如索引值为3，则至少要有3个value值）。如CHOOSE(2,”春”,”夏”,”秋”,”冬”)中，CHOOSE函数的索引值为2，因此CHOOSE函数返回value的值，也就是夏
  3. 此案例中，CHOOSE({1,2},A2:A18&B2:B18,C2:C18)中，当choose索引值为1时，则返回A2:A18&B2:B18，当choose索引值为2时，则返回C2:C18区域（给vlookup函数构建一个新的条件区域，新的查询区域中，第一列数据则为A2:A18&B2:B18，第2列数据为C2:C18）
  4. 最后，VLOOKUP函数以精确查找模式，返回新的查询区域中第2列，即为业绩所在列的数据。

使用VLOOKUP函数进行账龄分析

所谓账龄，指的是指公司尚未收回的应收账款的时间长度，账龄是在分析应收账款时最为重要的信息。账龄越高，则发生坏账的风险越大。今天，成都办公软件培训学校小编将和大家分享，在excel中如何使用VLOOKUP进行账龄分析，下表为应收款账目表，现在需要去计算每个客户的账龄区间：

在D2单元格内输入公式：=VLOOKUP(TODAY()-C2,{0,”0-30天”;30,”30-60天”;60,”60天以上”},2,1)后回车，然后往下填充即可

公式说明：

VLOOKUP(TODAY()-C2,{0,”0-30天”;30,”30-60天”;60,”60天以上”},2,1)，用当前系统时间减去B2单元格内的应收款日期得到的账龄天数作为VLOOKUP函数的查找依据，然后再用二维内存数据{0,”0-30天”;30,”30-60天”;60,”60天以上”}作为查找范围，此二维数组可转换为如下图所示表格

如果你觉得上述公式过长的话，也可以先把账龄天数和账龄级别对应表制作出来后，再使用VLOOKUP函数在表格内进行模糊查询即可。

如何快速补齐数据位数？

当我们需要把位数不足的数据补足为需要的位数时，不知道小伙伴们会选择使用什么方法去实现呢？今天，成都office培训小编将为各位小伙伴们分享四种方法，任君选择哦。

现有数据源如下图所示，其中员工编号列数据需要统一规范为5位数，不足5位的以0占位补齐

方法1：使用自定义格式补齐位数

选择A2:A20区域后，按快捷键Ctrl+1调出设置单元格格式的对话框，选择【自定义】，然后在【类型】下方框内，输入格式代码【00000】（0的个数代表数据位数）

方法2：使用TEXT函数补齐数据位数

Text函数可以把数据根据给定的格式代码把已有数据格式进行转换，参数形式为：

Text（需要转换格式的数据，格式代码）

选择B2单元格，输入公式：=TEXT(A2,”00000″)后往下拖动填充即可（和方法1同理，格式代码中0的个数等于数据位数）

说明：方法1中拖动自定义格式补齐数据位数后，改变的只是显示效果，A2单元格内的数据实际上还是762，并不会直接变成00762，所以法2以及后面的函数方法中，可以直接引用A2单元格

方法3：使用TEXT函数和REPT函数补齐数据位数

此方法可以理解为方法2的进阶版，在格式代码中使用REPT函数重复显示0。

选择C2单元格，输入公式：=TEXT(A2,REPT(0,5))后往下拖动填充即可

方法4：使用BASE函数补齐数据位数

在使用BASE函数之前，我们先来简单的了解一下BASE函数，BASE函数的作用是将数字转换为具备给定基数的文本表示。它的参数形式为：BASE(Number, Radix [Min_length])，参数具体意思如下：

Number：必需。 要转换的数。 必须是大于或等于 0 且小于 2^53 的整数。

Radix：必需。 要将数字转换为的基数。 必须是大于或等于 2 且小于或等于 36 的整数。

Min_length：可选。 返回的字符串的最小长度。 必须是大于或等于 0 的整数。

参数注意事项如下图：

选择D2单元格，输入公式：=BASE(A2,10,5)后往下拖动填充即可

使用合并计算实现单条件求和

因工作需要，现需要对各部门的业绩信息对销售1部的业绩进行汇总，从要求上不难看出这是一个单条件求和的计算。相信对于很多小伙伴来说，对于单条件求和，已经有且不仅只有一种方法了，比如sumif函数、数据透视表等等。而成都office培训班小编今天却打算另辟蹊径，使用合并计算去进行单条件求和的计算，不会的小伙伴们快跟着小编一起来实操学习吧：

1.首先，录入条件

在使用合并计算进行汇总之前，我们先需要录入条件，条件录入形式如下图所示：

2.使用合并计算进行汇总

选择F1:G2区域（即为条件和结果输出所在单元格区域）后单击选择【数据选项卡】，然后在【数据工具】组内点击【合并计算】命令，在弹出来的窗口中的【引用位置】下方框内选择【B1:D20】区域后点击【添加】，并在【标签位置】下方勾选【首行】和【最左列】，最后再【确定】即可

说明：合并计算中引用位置区域是根据区域中的最左列进行分类计算的，案例中是根据部门进行计算，所以需要选择B1:D20,标签位置中勾选【首行】和【最左列】是为了让结果显示首行标题和引用位置中区域的最左列部门数据。

今天的分享到这里就结束了，希望对你有所帮助。

如何使用透视表为数据分类？

今天，成都office培训短期班小编将为大家分享在数据透视表内如何根据自己需要对文本内容进行灵活分类处理，现有如下图所示的某商店产品销售明细数据：

备注：本图只为部分数据

现在需要根据上图所示数据计算每个产品的总销售额，且需要把产品进行归类，其中，保宁醋、海天酱油和料酒三个产品归类到调味品内；车厘子、苹果和香蕉归类到水果内；垃圾袋、垃圾桶、脸盆和拖把偶读归类到日用品内，具体效果如下图所示：

操作步骤如下：

1.插入数据透视表

选择数据源内任一单元格—点击【插入】选项卡—点击【数据透视表】—在【选择放置数据透视表的位置】下方选择【现有工作表】后，在【位置】后方框内选择本工作表内的【E2单元格】—确定，如下图所示：

2.设置字段

在字段列表内分别勾选【商品名称】字段和【销售额】字段（商品名称字段位于【行】，销售额字段位于【值】）

3.对文本内容进行分组

同时选择同一组内的商品名称（保宁醋、海天酱油和料酒）后，单击【数据透视表工具】下方的【分析】选项卡，然后选择【组合】组内的【分组选择】，然后选择【数据组1】所在单元格，直接键盘输入类别名称【调味品】即可完成归类

同理，同时选择车厘子、苹果和香蕉三个产品后，单击【数据透视表工具】下方的【分析】选项卡，然后选择【组合】组内的【分组选择】，然后选择【数据组2】所在单元格，直接键盘输入类别名称【水果】完成分类

备注：日用品系列归类方法同上

数据透视表内文本内容的手动分组就完成了，真的是简单又实用哦，快学起来吧！

如何使用SMALL和CHOOSE计算设有上下限的奖金？

俗话说，条条大路通罗马，要解决一个问题，往往有很多种方法，而具体使用哪一种方法就算个人喜好了。在前面的文章里，成都office培训速成班小编已经和大家分享过如何使用最值函数max和min去计算设有上下限的奖金金额，今天小编将会和大家分享另外一种方法去实现上下限奖金金额的计算，想知道是什么方法的话，就跟着小编的步骤往下看吧：

小编手上现在有公司内各部门对的业绩明细表，需要根据业绩计算每个员工的提成奖金，奖金的计算方法为：奖金=业绩*0.2且奖金最高不得超过15000，最低不得低于500.

那我们今天使用的方法是small函数choose函数的组合，在D2单元格内输入公式：=SMALL(CHOOSE({1,2,3},500,1500,C2*0.2),2)

函数作用说明：

Choose函数：根据索引值返回数值参数列表中的数值。 如Choose(2,周一,周二,周三)中，索引值为2，则Choose函数返回value2对应的值周二

Small函数：返回一组数据中第K个最小值。

知道了small和choose函数的作用后，本案例中的公式就比较好理解了：

首先，先使用choose函数把500,1500和C2*0.2得到结果后的三个数字组成一个数组

然后，再使用small函数返回这三个数字中第2个最小值，若C2*0.2的结果大于1500，则返回1500；若C2*0.2小于500，则返回500，否则返回C2*0.2的结果

到此，使用small和choose计算设有上下限的奖金金额就完成了，你学会了吗？

如何使用数据透视表统计非重复数量？

成都office培训中心小编手上有某公司在东北、华北和西北三个区域内合作商家的基本信息，现在需要根据合作商家代码分别计算三个区域内的合作商家数量，怎么快速实现呢？小编在这里分享一个非常简单的方法，那就是使用数据透视表进行统计，数据源表如下图：

（因数据过多，只截取了其中一部分数据展示）

从上图中可以看到，在同一区域内合作商家代码列数据时有重复数据存在的，那我们在计算时则需要计算的非重复的数量（即重复数据统计为1），具体操作步骤如下：

1.插入数据透视表

选择数据源表内任一单元格 — 选择【插入】选项卡 — 选择【数据透视表】命令 — 在弹出来的对话框内的【选择防止数据透视表的位置】下方选择【现有工作表】，然后在【位置】后方框内选择本工作表内的E7单元格 — 勾选【将此数据添加到数据模型】— 确定

提示：在创建数据透视表时，必须要勾选【将此数据添加到数据模型】，汇总方式内才会有【非重复计数】

2.设置字段

数据透视表创建好之后，把【区域】字段放进【行】区域内，把【合作商家代码】字段放进【值】区域内

3.修改字段计算方式

在【值】区域内单击字段名 — 选择最后一个选项【值字段设置】— 在弹出来的对话 框中，把【值汇总方式】选择为【非重复计数】— 确定后即可完成计算

最终，数据透视表完成效果如下图所示：

怎么样，这个方法很简单吧。不过再简单的方法还是需要小伙伴们动手实践哦。

如何根据部门创建多级分类汇总？

分类汇总作为数据处理分分析的使用工具，相信很多小伙伴都会使用且多多少少都用过，那我们如何在满足双条件的情况下去对数据做多级分类汇总呢？且跟着小编一起往下细看：

现有公司各部门以及各部门下各组别的业绩情况表，如下图：

现在需要同时根据部门和组别两个类别对数据进行分类汇总，最终结果如下图：

那该如何实现这样的效果呢？且看如下操作步骤

1.首先，我们需要先对分类汇总的分类字段列数据进行排序

排序时选择升序或者降序都可以，因为我们排序的目标并不是为了把谁放在前面把谁放在后面，而是为了把相同的类别并列放在一起

选择数据内任一单元格—【数据】选项卡—在【排序和筛选】组内选择【排序】命令后在弹出来的对话框内单击选择【添加条件】—然后在【主要关键字】下选择【部门】 ，【次要关键字】下选择【组别】，排序的次序默认为【升序】—然后点击【确定】完成排序，排序结果如下图所示：

排序结果如下图：

2.对数据进行第一次分类汇总

全选所有数据—选择【数据选项卡】—在【分级显示】组内选择【分类汇总】命令—然后在弹出来的对话框内把【分类字段】选择为【部门】，【汇总方式】为默认的【求和】（可根据需要选择其他计算方式）—点击【确定】完成，如下图所示：

注意：为了方便截图，此处有提前调整过分类汇总的位置。默认是在数据做差不多最右边

3.对数据进行第二次分类汇总

全选所有数据—选择【数据选项卡】—在【分级显示】组内选择【分类汇总】命令—然后在弹出来的对话框内把【分类字段】选择为【组别】，【汇总方式】为默认的【求和】（可根据需要选择其他计算方式）—取消勾选【替换当前分类汇总】—然后点击【确定】完成，如下图所示：

完成图如下：

注意：序号列在分类汇总完成后会断开显示，如有需要可重新调整

到这里，我们需要的同时按部门和组别进行多级分类汇总就完成了，希望对您有所帮助哦！