科学摸鱼之Excel小技巧(八) 利用透视表自定义数据信息组合 ...

枫落吴江

logo.png

  接上一期我们分享了表格排序的小技巧，本期我们继续聊一聊Excel其它看上去很酷炫的操作。
  本期内容同样是天选打工人熬夜整理出来的，吐血推荐。
  下面就开始我们今天的摸鱼小技巧吧。
<hr/>  数据透视表是Excel一项非常重要的功能，特别是在做数据分析的时候，透视表拥有举足轻重的地位。当然要熟练使用透视表所需要付出的学习成本也并不低，今天并不打算对使用透视表做入门介绍，而是以一个工作中可能出现的场景跟大家分享一下如何利用透视表实现信息的快速组合排列。
  一般正经的公司都会给自己的员工设计工作证（工卡），上面会有员工的姓名、部门甚至工号等基本信息，比如下面这样：



工卡样例

  那么如果某天老板需要我们根据员工信息表对每位员工生成一张带基础信息的工卡照，我们该怎么做呢？方法肯定有千千万万，不过咱的主题是Excel，那么就来看看用Excel怎么做吧。
设计

  首先我们需要在数据表中设计出我们工作证的模板，这里可以任意发挥。比如我设计的是这样的风格：



工卡模板

  里面包含了基本的姓名工号等信息，最顶上也有公司（瞎编的一个名字）信息。这样一个简单的工作证模板就做好了。
创建透视表

  下面再来看看我们的员工信息表长啥样，其实就是我前几章中用到的数据表，是不是有些熟悉？☺



员工名单

  我们选中它，并点击插入数据透视表，并根据工作证模板中的内容选取“照片”、“姓名”、“工号”、“部门”（为了方便，我这里选取的是二级部门信息）。


https://www.zhihu.com/video/1578698343366189056
  由于工作证模板设计是从上到下的的排列，因此在报表字段中按模板中的顺序将字段都放在行当中。



数据字段

  如果Excel没有自动打开报表字段窗口，可先在数据表中选中数据报表区域，然后点击数据透视表分析菜单中的字段列表即可显示。



菜单入口

  然后再选择以压缩布局的方式进行显示透视图，通过以上的操作，我们就能把工作证所需的内容安装模板的顺序排列整齐。



压缩布局

调整布局

  仔细观察工作证模板会发现，姓名与照片之间有一个空行，而照片上方也有一个空行和一个公司名称信息。如果将空行也作为因此工作证上的数据实际是按照下述表格进行排列的：

工作卡中的信息	举例数据	当前透视表数据
公司名称	RAY TECH EMPEEY ID CARD	/
空行	<空白数据>	/
照片	假装此处有照片	/
空行	<空白数据>	照片
姓名	Ray0728	姓名
工号	RID_1234567890	工号
部门	科学摸鱼办公室	二级部门

  显然透视表中现有的数据无法和工作证中所需数据是无法匹配上的，如果按逐行对应来看，甚至会出现错位（行）的情况。
  因此我们还需要向透视表中插入更多的内容。以便数据表的数据能完全符合工作证的要求。但是透视表只能插入原数据表中已有的内容，无法插入自定义内容。那么怎样向透视表中插入数据表中没有的内容呢？
透视表的节点汇聚

  在处理这个问题前，我们需要了解一点透视表的简单小常识。Excel支持为每一个项目后插入空行，这里的项目是指数据透视表中的一级分组或者一级节点。节点是以透视表行字段的先后顺序来决定的，相同的节点会自动合并，比如我们将部门信息作为第一节点，那么这个部门下的所有数据都将汇聚在一个节点下。



节点汇聚

  因此透视表行字段的顺序一定要特别留意，通过调整顺序以便透视表结果符合我们的预期。
  知道上面这个小常识后，再回头看我们的问题，数据透视表是无法插入原数据表中没有的内容，但是从我们的工作证模板中能知道没有的数据不是空行就是固定的公司信息，而我们在《单元格格式更智能》分享了一种简单的单元格内容隐藏方法;;;。
  结合在一起就有了解决的思路，就是通过向透视表行字段的合适位置中插入无关紧要的字段（首字段必须是唯一的），避免数据错位（行）的情况，同时在模板上对应的单元格上设置格式为;;;，将这些无关的数据隐藏即可。因此我们可以得到以下字段信息。

工作卡中的信息	透视表数据
公司名称	/
空行	电话号码
照片	照片
空行	入职时间
姓名	姓名
工号	工号
部门	二级部门

  最后再利用Excel可向数据透视表中插入空行的功能，最终实现如下效果：



增加信息后的行字段

  注意看，第11行和19行都有一个空行，后续字段中也有同样的空行。
批量生成

  至此我们的数据都准备好了，现在可以开始批量生成我们的员工工作证了。首先从透视表中将数据拷贝至新的Sheet页中，当然也可以拷贝至任何想保存的地方。
  注意拷贝后的粘贴，需要选择值粘贴。
值粘贴

  Excel拷贝数据之后，在粘贴的时候可以选择粘贴内容，比如粘贴公式、数值以及格式等等。我们首先将透视表的数值粘贴至新的区域。
  方法很简单，使用鼠标右键粘贴时，选择对应的图标即可。


https://www.zhihu.com/video/1578698854811332608
  注意粘贴的时候错一行粘贴。
模板复制

  有了数据后，就需要将工作证模板复制到刚刚粘贴的数据当中正确的位置上，这里我们使用简单的数据组函数来实现。
  因为模板中的工作证一共有7行，因此选中7个单元格，然后直接输入=工卡模板!$A$1:$A:$7，然后按下CTRL+SHIFT+ENTER。此时工作表会自动变成下列数据：



组函数

  之后就只需要将这7行复制给剩余的行即可。


https://www.zhihu.com/video/1578699120235200512
  注意这里需要使用函数粘贴，其使用方法和值粘贴类似。
格式复制

  现在我们的工作证数据表中的数据已全部准备就绪，接着将模板的格式（比如单元格颜色、字体样式等等）复制给所有的数据单元格即可。


https://www.zhihu.com/video/1578699354415874048
  这个时候已经能看见员工工作证的雏形了，但是由于是默认的行高和列宽，所以显示效果并不算好，比如工号由于单元格列宽限制，只能显示为####，而照片单元格又显得太短了。为了更好的显示工作证，需要对数据表中的特定单元格进行行高、列宽的调整。
行高列宽调整

  以照片单元格举例，每个人员的工作证都会有这样一个单元格，但是如果分别对每一个员工的工作证进行行高调整，似乎太费体力了，有没有什么办法可以批量对特定单元格进行调整呢？当然是有的。
  我们利用Excel的查找替换功能，以照片作为关键字对当前数据表进行搜索，在搜索结果中能看到所有相关的单元格信息。



批量搜索

  使用CTRL + A ，将所有结果全选上。然后在开始菜单的单元格选项中，选择格式下拉菜单。这样就可以对搜索结果批量进行行高和列宽的设置。



调整行高列宽

  这样我们就可以简单快速的对我们的内容进行行高、列宽的调整以便达到最佳的显示效果。
结束语

  如文章开头所说透视表是Excel一个重要的功能，但是它绝不是仅仅用来做数据分析，如果能灵活使用，它还能做很多意料之外的事。
  当然Excel的小技巧不止于此，有特别想了解的，或困惑的都可以给我留言。在后面的摸鱼小技巧中再跟大家分享总结吧。

雪花飘过

我擦！我要沙发！