excel找出普通鼠标、光电鼠标的单元格 查找内容中加入通配符的使用,在鼠标前面加入两个问号(?)代表两个字符,实现普通鼠标和光电鼠标单元格的查找。 这里注意不能使用...
2020-09-26 193 excel教程
数据透视表绝对是Excel的一大利器,用好它可以让你分分钟完成复杂的工作。本例就是利用数据透视表查找两组复杂数据间最细微的差异,堪称“明察秋毫的鹰眼”,可供财务对账、统计查找差异参考。
例:某公司9月30日和10月15日累计收到衬衫订单如下图(原始数据成千上万行):
图1 原始数据
两个时点的数据结构都是一样的,均有3个项目和2项数据,现在要求按同样的数据结构列出前后的差异以供后续工作的开展。
在保证所有项目数据的格式对应相同的前提下,操作步骤如下:
1、单击“0930”工作表名,按住Ctrl后水平向右移动,即可复制一张表,改名为“差异”,置于最末:
图2 复制工作表
2、将“差异表”中的“数量”改为“0930数量”、“金额”改为“0930金额”。
3、拖选第2张表“1015”中的A~E列,并复制,粘到差异表I列(与第1组数据空数据项数+1=3列),并将“数量”改为“1015数量”、“金额”改为“10150金额”:
图3 数据准备1
4、在第2组数据值项前(L列前)插入与数据项数相等(此处为2)的空列,这一步极为关键,目的在于经数据透视表汇总时不会与第1组数据混在一起,但相同项的数据会合计为一行:
图4 数据准备2
5、将第2组数据中除标题外的所有数据剪切(单击数据的最左上角单元格,使用Ctrl+Shift+光标向下,再向右、向右选择完全部数据)拼接至第1组数据下一行,不要有空行,并将第2组数据中的数据项标题拼在第1组数据后边,使两组数据形成一个整体,但各数据项都是分开列的。再清除原来第2组数据的项目标题。为有效展示,下图隐藏了若干中间行:
图5 数据准备3
6、选中合并后的数据区中的任意一单元格,在本表的J1(或第1行的任意空白单元格)插入数据透视表:
图6 插入数据透视表
7、将所有项目名拖动进透视表的“行”框、将所有数据项拖进透视表的“Σ值”框中:
图7 添加透视表字段
8、点击“Σ值”框中的每一个项目,点“值字段设置”,将汇总方式由“计数项”改为“求和项”:
图8 透视汇总方式调整
9、单击透视表的任意一单元格,在新境的“数据透视表工具”菜单的“分析”标签里点 “选项”命令:
图9 调整透视设置1
在弹出的界面里点“显示”标签项,勾选“经典数据透视表布局(启用网络格中的字段拖放)”并确定:
图10 调整透视设置2
10、再点“数据透视表工具”菜单中的“设计”-“报表布局”-“重复所有项目标签”:
图11 调整透视设置3
完成后的效果见图12。
11、逐一右击所有项目的任意单元格,去掉“分类汇总…”前的勾:
图12 调整透视设置4
完成后的数据透视表效果图:
图13 调整透视设置后的效果
12、选择所有透视表列,复制全部数据并原位置选择粘贴数值,按Ctrl+H将“求和项:”替换为空,最后删除透视表的数据源、删除透视的最后一行“总计”:
图14 整理后的数据
13、在标题末续两列,标题名为“数量差”和“金额差”,在数量差下首个单元格写入公式:用1015数量减0924数量,即在H3中写入:=F3-D3,并回车;再选中H3,鼠标右移至右下角直至鼠标变为细黑十字向右拖动复制到金额差中;最后连选H3:I3,同样鼠标右移至右下角直至鼠标变为细黑十字后双击,即自动完成向下填充公式。有多项数据的,以此类推:
图15 计算差异
14、选择所有标题,加上自动筛选,筛选数量差和金额差均为“0”的行并选择筛选出的所有行号(点第1个筛选出的行,然后按Ctrl+Shift+光标向下键可一次选完)右键删除:
图16 删除无差异行
15、最后取消自动筛选,差异结果就呈现在了你的眼前:
图17 差异结果
本方法看似步骤繁多,实际操作起来几分钟就完成了,而且差异项级别随心所欲一次实现,很有“万军从中取‘差异首级’如探囊取物”般容易的感觉。
相关文章
excel找出普通鼠标、光电鼠标的单元格 查找内容中加入通配符的使用,在鼠标前面加入两个问号(?)代表两个字符,实现普通鼠标和光电鼠标单元格的查找。 这里注意不能使用...
2020-09-26 193 excel教程
两张表格合成一张的方法: 1、首先,我们打开需要处理的表格。Excel是电子试算表程序进行数字和预算运算的软件程序),它在市场拥有统治份额。它最初对占优势是个竞争者,...
2020-09-26 893 excel教程
excel的三个重要概念 excel的三个重要概念分别是工作簿、工作表以及单元格。 一、工作簿 用来储存和处理数据的主要文档,也称为电子表格。在计算机中工作簿以文件...
2020-09-26 663 excel教程
excel提示文档未保存怎么办? 1、先来打开Excel ,接着点击固定栏的【文件】选项,等我们进入后点击【选项】按钮。 2、在新弹出的窗口找到并点击【保存】选项。 3、这...
2020-09-26 230 excel提示文档未保存
excel查找后如何整行复制? 假设查找华东区,找到后单元格跳至华东区所在处。 查找后,选中行数字3,整行就选中了,接着复制即可。...
2020-09-26 427 excel教程