向EXCEL要效率

向EXCEL要效率产品部：Jessica有时候，多而复杂的数据让你头晕眼花，手忙脚乱……快速处理数据的技巧1完善你的Excel表格3目录1数据透视表VLOOKUP公式分类汇总高级筛选快速处理数据的技巧数据透视表1选中数据区域2选择“数据”-“数据透视表和数据透视图”3根据向导点“下一步”直到完成4设置行字段和列字段操作步骤数据透视表将相应的项目拖动至行字段、列字段以及数据区域数据透视表选择展示的列（行）项目数据透视表根据实际需要设置数据的汇总方式数据透视表刷新数据－保证数据透视表数据与数据源同步方法一：方法二：在透视表的数据区域内单击右键,在弹出的菜单中选择“刷新数据”。数据透视表刷新数据－保证数据透视表数据与数据源同步方法三：在数据透视表上点右键，点选“表格选项”，在“数据选项”里面勾选“打开时刷新”；数据透视表在数据透视表中，选择“数据”-“筛选”后显示的“自动筛选”项是灰色，即不可操作状态数据透视表+自动筛选：更好地筛选所需信息数据透视表方法1选择数据透视表标题行外紧挨的一个单元格(下图中的F4）,往前拖动至B4，然后应用自动筛选。这样设置的自动筛选将保留行字段原来的筛选按钮。方法2选择数据透视表标题行外紧挨的一个单元格,应用自动筛选。这样设置的自动筛选将不保留行字段原来的筛选按钮。数据透视表+自动筛选：更好地筛选所需信息数据透视表1数据第一行是字段名称，不能为空。2同一字段中数据的类型必须一致（如“商品编码”字段的数据既有文本类型又有数据类型，则无法根据商品编码进行组合）。3选中数据区域时,不要只选中有数据的那个范围，而应该选中整个列。否则原数据区域的行数增加时，刷新数据后透视表仍无法显示增加的那些行的数据。备注：按第3点操作时，数据列会存在空行，透视表会认为空格所在的字段是文本型的，默认以计数的方式显示，需要手动修改成汇总。且在数据透视表最后一行会出现“空白”记录注意点数据透视表VLOOKUP公式分类汇总高级筛选快速处理数据的技巧公式讲解VLOOKUP公式=vlookup(查找目标,查找区域,返回值的列数,精确or模糊查找)注意：参数之间的分隔符号“,”和括号必须是英文状态下的标点，因此输入公式前最好先切换至英文输入法。VLOOKUP公式我们根据产品SKU号来检索产品的库存数量，那么SKU号就是查找目标。比如，想知道1100842的库存数量，那么就在公式的第一个参数位置输入A2单元格查找目标：就是你指定的查找字段VLOOKUP公式查找区域:为EXCEL指定从哪里查找B该区域中一定要包含要返回值所在的列本例中要返回的值是“总计”列中的数据，则E列一定要包括在这个范围内。A查找目标一定要在该区域的第1列本例中查找SKU号，那么SKU号所在的A列一定要是查找区域的第1列。VLOOKUP公式返回值的列数：它是一个整数值，代表返回值在查找范围区域的第几列本例中我们要返回的是“总计”列的值，它是查找区域的第5列。这里一定要注意，列数不是在工作表中的列数，而是在查找范围区域的第几列。VLOOKUP公式精确or模糊查找如果指定值是0或FALSE就表示精确查找，而值为1或TRUE时则表示模糊。精确即完全一样，模糊即包含的意思。如果缺少这个参数，则默认为模糊查找，我们就无法精确查找到结果了。VLOOKUP公式1被查找的列的数值类型要和查找的数值类型一样。2要保证查找目标所在的列中每个单元格中数值是唯一的。注意点1数据透视表VLOOKUP公式分类汇总高级筛选完善你的Excel表格分类汇总操作步骤1以选定的分类字段为主要关键字对数据进行排序如根据购货单位对实发数量进行汇总，就将数据以“购货单位”为主要关键字排序分类汇总操作步骤2选中数据区域，在“数据”菜单中选择“分类汇总”。根据购货单位对实发数量进行汇总，分类字段选择“购货单位”，汇总项选择“实发数量”，汇总方式为“求和”分类汇总是否勾选“替换当前分类汇总”项选中：对数据进行重新汇总未选中：对数据进行多级汇总。根据需求，选择先后汇总顺序，可以实现汇总的级别分布分类汇总汇总结果的分级显示快速显示摘要行或摘要列，或者显示每组的明细数据分类汇总之后，在数据区域的左侧会出现如图的“123”，表示按1,2,3层次查看数据。层次3最详细。分类汇总复制汇总结果1选中汇总后的数据，选择“编辑”-定位”分类汇总2在出现的定位界面，点击“定位条件”，在弹出的界面中选择“可见单元格”，点“确定”完成，复制粘贴即可。复制汇总结果分类汇总对汇总结果进行排序1选择汇总项目所在的列（如例中的“实发数量”列），选择“排序”分类汇总对汇总结果进行排序2在弹出的“排序警告”中选择默认项“扩展选定区域”，点“排序”。在“排序”对话窗口中按照实际需要选择升序或者降序1数据透视表VLOOKUP公式分类汇总高级筛选快速处理数据的技巧高级筛选2选择数据，打开“筛选”→“高级筛选→将筛选结果复制到其他位置”，设置好“列表区域”、“条件区域”和“复制到”的位置，单击“确定”按钮1根据需求编辑条件区域操作步骤高级筛选3设置好“列表区域”、“条件区域”和“复制到”的位置，单击“确定”按钮操作步骤高级筛选高级筛选—条件区域的编辑1特定字符一步筛选想要筛选出某个字段中包含特定字符的所有记录，如筛选产品名称包含“定时器”的所有记录，该如何编辑高级筛选的条件？在数据区域外的任一单元格中输入被筛选的字段名称，然后在紧靠其下方的单元格中输入筛选条件“*定时器”高级筛选高级筛选—条件区域的编辑要筛选字段是文本型在数据区域外的任一单元格中输入被筛选的字段名称，然后在紧靠其下方的单元格中输入筛选条件“*”。反过来，如果要筛选非空的记录，文本型字段只需将筛选条件改为“*”，数值型字段则输入筛选条件改为“”。2空白数据筛选要筛选的字段是数值型则需要将筛选条件更改为“=”（直接输入“=”号后按回车）。高级筛选3满足多条件一起筛选如果将筛选条件输入在同一行中，筛选时系统会自动查找同时满足所有指定条件的记录并将其筛选出来。如果想查找所有字段值都是非空的记录，只需将指定的筛选条件文本型用“﹡”，数值型用“”，并将这些筛选条件输入在同一行中即可。高级筛选—条件区域的编辑高级筛选4多种条件选一筛选若查找时，几个条件中满足一个即可，则在不同行输入筛选条件高级筛选—条件区域的编辑1下拉列表数据分列/合并定位条件及应用打印设置完善你的Excel表格下拉列表我们在用Excel录入表格数据时，常常会遇到某列数据的值只在几个固定值中选择一个的情况，比如：人的性别列只可能录入男或女，对学历列只可能录入高中、大专、本科、研究生之一等。遇到这类数据，如果我们手工录入，效率既低又容易出错，最好的解决办法是提供一个下拉列表框供我们选择其中的值。下拉列表最简单的下拉列表只要你在一列中连续的单元格内输入文字,除了数字以外的内容都会显示在下拉列表中。快捷键是Alt+↓键，在输入数据时,如果想输入上面已经输入过的数据,直接从下拉列表中选择就可以了.下拉列表用数据有效性制作下拉列表1选择要设置的单元格或者整列，选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口操作步骤2在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉菜单”全部打勾。在编辑“来源”时候，有以下两种办法：下拉列表操作步骤A直接输入在“来源”下面输入数据，譬如“货到30天,款到发货,”(不包括双引号，分割符号“,”必须为半角模式)→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。B引用同一工作表内的数据在“来源”下面输入数据“=$E$2:$E$5”，也可以按右边带红色箭头的直接选择E2：E5区域→按“确定”就OK了，再次选择该A1单元格，就出现了下拉菜单。下拉列表操作步骤下拉列表知识拓展用数据有效性制作下拉列表，在选取数据来源时，若使用引用现有数据的方法，就会发现一个局限性，那就是数据来源的拾取按钮是不能跨表去拾取其他表的数据的。于是我们就需要用到名称框。1打开数据源所在的工作表（如本例中的工作表2），选中数据所在的列（或者行），然后在名称框为它输入一个名字（中英文都可以），输入完成后一定要按回车确认。下拉列表知识拓展2为了在表1引用“账期”列的数据，在选中需要生成下拉列表的列，点击菜单栏中的“数据→有效性”，在弹出的“数据有效性”的来源输入框中输入“=账期”，点击“确定”后，在下拉列表中就可选择各种账期了。下拉列表分类下拉列表下拉列表能带来很多便利，省去了重复输入，但当数据数量较多，下拉列表太长，选择起来非常不方便，甚至会花费更多的时间在查找上。于是，我们将对其进行了改进，实现了“分类列表选择、快速统一输入”的目的。下拉列表分类下拉列表步骤一建数据库打开一张工作表（如本例中的表4），将员工姓名按不同的部门分别输入不同的列中，建立一个员工姓名数据库下拉列表步骤二命名在表4中，用前面介绍的名称框依次对每一列进行命名（如A列命名为“财务部”)。仿照上面的操作，将B、C、D、E列分别命名为“市场部”、“产品部”、“商务组”、“物流部”分类下拉列表下拉列表步骤三建立下拉列表切换到表3中，选中需要输入部门的列（如C列），执行“数据→有效性”命令，打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中，输入“财务部,产品部,市场部,物流部,商务组”序列，输入完成后，确定退出。分类下拉列表下拉列表再选中需要输入员工姓名的列（如D列），再次打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=indirect(C2)，确定退出。分类下拉列表注意点这里要用到函数的indirect，它表示从某一单元格中取数据，然后把此数据转换成一个区域。在来源输入框中输入“＝indirect（”，点击C2单元格，出现“=indirect($C$2)”，这里是绝对引用，按F4键改成相对引用“=indirect（C2）”。如果不转换成相对引用，则会出错下拉列表演示表格下拉列表1下拉列表数据分列/合并定位条件及应用打印设置完善你的Excel表格数据分列有时需要将Word或者网页中的大量数据的复制excel中，但是复制到excel中的数据往往都几种在同一列（会分行但是不会自动分列），这时候就可以用EXCEL中的数据分列实现。分列前分列后数据分列操作步骤1.选中要分列的数据，点击上方菜单栏的“数据”，在下拉菜单中选择“分列”。数据分列操作步骤2.在弹出的“文本分列向导-3步骤之1”对话框中，选中其中的“分隔符号-……”选项（通常是默认选项），点击下一步数据分列操作步骤3.进入“文本分列向导-3步骤之2”对话框，选择“其他”选项，并在后面的方框中输入括号（因为数据的品名和SKU号中都有一个间隔“（”号），单击“完成”按钮，分列完成。注意：大家可以通过图3中的“数据预览”窗口查看分列后的效果。数据分列注意点1.“其他”选项后面的方框中可以输入标点，数字和中英文字。操作时需要先观察数据特点再选择合适的分割符。数据中被作为分隔符的字符在分列后的数据中不再存在。（如前面例子中，分列后SKU号的前括号会消失）2.如果几行长度比较一致的数据也可以在“文本分列向导-3步骤之1”对话框中选择“固定宽度”分列。数据分列注意点3.有时数据分列后会弹出提示对话框，要求确定“是否替换目标单元格内容”。若选择取消则无法得到分列结果；若选择“确定”则后一列数据会被分列结果覆盖。此时只需要先在被分列数据之后插入一个空列再进行分列即可。演示表格数据分列数据合并有时需要将几列数据合并到一列，或者需要在所有数据记录中添加同一个内容。如果一个个复制粘贴或者添加将是很繁琐的过程，这时候需要用到数据合并。数据合并单元格和单元格的合并：=单元格1&单元格2公式单元格中添加统一内容：=单元格&”要添加内容”例如，合并图中表格的B,C,D列，将结果保存在E列中，则在公式框中输入=B2&*&C2&*&D2数据合并注意点=单元格&”要添加内容”中的引号必须是英文状态下的演示表格1下拉列表数据分列/合并定位条件及应用打印设置