下载掌阅APP，畅读海量书库
立即打开
小张将按照“一个萝卜一个坑”的规则录完的超大表格，交给老板以后，本以为可以下班了。结果领导觉得表格不规范（见图7-1），要求他将表格：标题在上，明细在下。
图7-1
小张一边手动调整，一边吐槽：“本以为把数据‘一个萝卜一个坑’地录完就完事了，这么长的表格，领导非得让我把横着的表给变成竖着的表，该不会是在刁难我吧？”（见图7-2）
图7-2
表姐语录
信息录入参差不齐，数据协同Bug多。
实际上，老板要求小张将“横表变竖表”，并不是在刁难他。改成竖着的表，一共有以下两个好处。
（1）便于查看：竖着的表，在查看时，只要滚动一下鼠标滚轮，就可以快速翻阅。
（2）便于统计：竖着的表，可以直接对列进行纵向筛选，而横着的表，无法进行对行的横向筛选；并且竖着的表还可以使用数据透视表的工具，一键生成各类报表。
温馨提示
竖向表格，不仅便于查看，更加利于统计。
小张的这张表（见图7-1）不需要重新录入、手工整理，只要一步“转置”就行了。
（1）复制数据源：选中数据源B1:S3→按Ctrl+C复制（见图7-3）。
图7-3
（2）选择性粘贴：选中另一个工作表中的目标单元格A2→右击选择“粘贴选项”中的“转置”（见图7-4），粘贴后的效果如图7-5所示。
图7-4
图7-5
温馨提示
规范的数据源表格就像图7-5所示的“竖表”：标题在上面，明细在下面。录入数据的时候，就要做成这样竖着、纵向向下填写内容的表，这也利于我们后面做数据透视、使用函数或者进行所有的统计分析。
选择性粘贴，除了转置以外，还有其他的妙用。如图7-6所示的表是由3个人员分别统计的成本数据，我们要把这些数据迁移和整合到一起，就可以使用选择性粘贴提高效率。
（1）复制原始表：选中原始表（红表）数据源E1:G19→按Ctrl+C复制（见图7-6）。
做表规范：标题在上，明细在下，竖着做表。
图7-6
（2）选择性粘贴：单击目标表（绿表）单元格A1→右击选择“选择性粘贴”（见图7-7）→在弹出的“选择性粘贴”对话框，选中“跳过空单元”→单击“确定”（见图7-8）。
图7-7
图7-8
读书笔记
______________________________________________________
______________________________________________________
温馨提示
跳过空单元的意思是：旧表格当中的空白的单元格，不把它粘贴到新表当中。其他有字的单元格都粘贴到新表。需要注意的是，粘贴过去的时候，原来表格的颜色、格式，会被新表覆盖。
如果我们只粘贴数字，不粘贴格式，可以在做“选择性粘贴”的时候，同时选中“数值”。
选中原始表（黄表）数据源I1:K19→按Ctrl+C复制→单击目标表（绿表）A1单元格→右击选择“选择性粘贴”→在弹出的“选择性粘贴”对话框，同时选中“数值”和“跳过空单元”→单击“确定”（见图7-9）。
图7-9
除了格式的整理外，选择性粘贴还能实现不用公式的计算。如图7-10所示，要把C列的场地成本+D列的人员成本，汇总生成为E列的总成本。
图7-10
（1）按Ctrl+C复制数据源C2:C19，选中E2单元格，按Ctrl+V粘贴（见图7-11）。
图7-11
（2）复制数据源D2:D19→再选中到目标单元格区域E2:E19→右击选择“选择性粘贴”→在弹出的“选择性粘贴”对话框，选择“加”（见图7-12），即可实现两组数据的相加计算（见图7-13）。
图7-12
图7-13
温馨提示
选择性粘贴，可以同时应用“粘贴”+“运算”两种模式。
读书笔记
______________________________________________________
______________________________________________________
在工作当中经常会发生同一类数据分到多个工作表，甚至是多个工作簿文件保存的情况（见图7-14）。例如：
（1）有的人在登记数据的时候会做得很细，按照一天一个工作表保存。
（2）有的人可能会按季度或者按年度进行分表格去登记。
（3）有的公司可能会分不同的店面，各自一个工作簿文件进行登记。
图7-14
这种情况下，我们就要把不同工作表、不同工作簿当中的内容快速自动合并在一起，生成一个“完整的数据源表”。
如果手工做的话，需要把每个表里的数据都复制，然后再粘贴到一张汇总表里，如果有100个门店，每个门店按12个月分别登记，就要复制、粘贴1 200次，而且还有可能会出错，费力不讨好。
Excel 2016中的Power Query能轻松完成上述工作。
将需要合并的文件，全部关闭后，新建一个空白工作簿→选择“数据”选项卡→“新建查询”→选择“从文件”→“从文件夹”（见图7-15）。
图7-15
（1）在弹出的“文件夹”对话框，单击“浏览”（见图7-16）。
（2）在弹出的“浏览文件夹”对话框→单击目标文件夹→如素材文件提供的“07-讲课素材-我要合并的文件”文件夹→单击“确定”（见图7-17）→返回到“选择文件夹”对话框→单击“确定”。确定后，Excel将自动打开Power Query的编辑器界面（见图7-18）。
图7-16
图7-17
编辑器界面当中，出现了我们刚刚准备合并的3个文件：北京分公司.xlsx、广州分公司.xlsx、深圳分公司.xlsx。第1列Content代表着这些表里的内容，后面的列对应的是文件的名称、文件类型的后缀名、日期等。这些列只是为了让我们检查一下这个文件的来源对不对。在数据汇总的时候，它们是没有意义的，所以要把这些列删掉。
选中第1列Content→右击选择“删除其他列”（见图7-19）。
图7-18
图7-19
（1）选择“添加列”选项卡→单击“添加自定义列”（见图7-20）。
图7-20
（2）在弹出的“添加自定义列”对话框→添加“新列名”，如“凌祯的query大法”→在“自定义列公式”输入：Excel.Workbook([Content],true)→单击“确定”（见图7-21）。
读书笔记
______________________________________________________
______________________________________________________
图7-21
（1）单击“凌祯的query大法”字段名右侧的展开按钮（见图7-22）。
图7-22
（2）在展开的字段列表中，仅选中Data，单击“确定”（见图7-23）。
图7-23
温馨提示
取消选中“使用原始列名作为前缀”，在生成的汇总表中就不会显示刚刚自定义的列名了。
（1）单击Data字段名右侧的展开按钮（见图7-24）。
图7-24
（2）在展开的字段列表中，选中“选择所有列”→单击“确定”完成（见图7-25）。
图7-25
（3）在解析的明细表界面，选择第1列Content列→右击选择“删除”，即仅保留数据源表中的5列数据（见图7-26）。
图7-26
（1）选择“开始”选项卡→“关闭并上载”→“关闭并上载至”（见图7-27）
图7-27
（2）在弹出的“加载到”对话框→选择要上载的位置，如“新建工作表”→单击“加载”（见图7-28），加载完毕后的效果如图7-29所示。
图7-28
图7-29
温馨提示
使用Power Query查询后的表格，会自动套用表格格式，变身超级表。
使用Power Query查询的表格，当数据改变或新增文件时，可以实现一键自动刷新。如图7-30所示，我们在待合并的文件夹中，新增一个“上海分公司”的Excel文件。
图7-30
此时，在刚刚利用Power Query合并后的（见图7-29）表中，只需要右击选择“刷新”，即可将“上海分公司”的数据一起更新过来（见图7-31）。
这是因为通过Power Query合并表，建立了合并后的“汇总表”和原始“数据源表”之间的动态连接，当数据源发生增减变化的时候，合并以后的“汇总表”都是实时更新变化的。
图7-31
温馨提示
利用Power Query做多表合并的基本要求：数据源的表格结构、标题内容完全一致。
表姐说
在日常工作当中，当遇到数据源表分散存储，需要快速合并多表的问题，就立刻搬出Power Query来解决。
温馨提示
除Excel 2016自带Power Query，其他版本可能需要下载插件，然后才能使用。
（1）Excel 2010、Excel 2013需要到微软官网搜索Power Query后，下载插件并安装，方可使用。
（2）Excel 2003~2007及更早的版本、WPS，无法使用。
（1）在工作簿底部选中要移动或复制的工作表→右击选择“移动或复制”（见图7-32）。
图7-32
（2）在弹出的“移动或复制工作表”对话框→选中“建立副本”，即复制工作表；不选中，即为移动工作表→单击“将选定工作表移至工作簿”右侧的下拉按钮→在下拉框中，选择需要移动到的工作簿，或者创建一个新工作簿→设置完毕后，单击“确定”，即可完成工作表整体的快速移动或复制（见图7-33～图7-74）。
图7-33
图7-34
读书笔记
______________________________________________________
______________________________________________________