Excel中插入表格的妙用

欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.comExcel中“插入表格”的妙用“插入表格”也许是Excel里最不起眼的一个大功能，至今为止却很少见有人将该功能进行深入的讲解和应用，微软在其帮助中也轻描淡写，完全体现不出其所隐藏的重要作用，而且这个名字也起得怪怪的，一开始我还以为是在Excel里面再插入一个新的表格，殊不知其背后，却是另一番景象，隐藏着如此美妙之大作用。本部分内容练习文件请详见《Excel重点功能_插入表格》。一、从【插入/表格】开始首先让我们来一睹其朴素之芳容（菜单【插入】下，数据透视旁）：欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com把鼠标放在该按钮上，我们可以看到使用该功能的快捷键是【Ctrl+T】，同时可以看到微软对该功能的描述，说句实话，看到这个描述我真不觉得它有啥用，因为这几句话完全可以用来形容Excel的一般表格，而且一开始的“创建”就给人一个错误的认知，很容易让人误以为会创建出一个新的表格，实际上，该功能是将现有的普通表格转换为一个规范的可自动扩展的数据表单！我们来试一下：1.选中现有表格的全部或其中任一单元格，点击【插入/表格】这时，Excel会弹出“创建表”对话框，默认“表的数据来源”为当前表格的全部内容，并勾选“表包含标题”选项。如下图所示：2.点击“确认”按钮生成表，并修改表格名称此时，我们可以看到，原有的表格 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 改变了，同时菜单上多了一个“表格工具/设计”的内容，如下图所示：欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com同时，我们可以看到，在“表格工具/设计”菜单的最左边有个“表名称”的文本框，里面默认为“表1”、“表2”之类的，在此，我们可以对该表单起个方便自己记住的名字，如改为“student”——这一点很重要，也是“插入表格”功能为后续极大地方便对该表的选择、引用起最大作用的关键地方。至此，“插入表格”的工作基本完成，这时，我们还可以在输入公式栏左侧的表格区域选择框里看到它的身影，如下图所示：格式变化，变成隔行着色的规范表单点击该表格区域选择框右侧的“选择按钮”，将出现该工作簿中所有经过“插入表格”转换得到的表单，如刚刚转换并且命名好的“student”。欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com同样地，我们可以将该练习工作簿中的其他几个表转换为“表格”，共形成四个表格，分别如下：学生表：student教师表：teacher课程表：course成绩表：score如下图所示：这一点看上去简单的变化，将在后续的数据处理中体现出其极大的精妙之处。二、妙用1：表格的快速切换，不再为找个表头晕眼花在需要处理大量表格的时候，你是不是常常为找到其中的某一个表而看到头晕眼花？心烦意燥？——当你使用了“插入表”功能，你将不再需要为此烦恼！欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com回到我们的练习文件上，在“表格选择区域”中，一旦选择其中一个，Excel将切换到相应的表单所在的工作表上，如下：更方便的是，当一个工作簿中的表格很多，看着都眼花的情况下，你可以直接输入自己给表格所起的名字，如“teacher”进行简单搜索或定位，然后按下回车键即可快速切换到相应的表格。注意，要先点击“表格选择区域”的下拉按钮，如下图所示：12结果12欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com本人曾经在一个项目中同时处理160多张表（涉及到多个集团企业多套系统的基础数据整合、比对和规范化），为了方便处理，我将所有表全部放到同一个工作簿中，然后通过“插入表格”的方式统一转换，便可以直接在表格区域的选择框中直接搜索相应的表，通过键盘的↑或↓选择需要的表，从而实现快速的切换。具体见下图：同时，在这里说一下该搜索方法存在的缺点——该搜索不是全字段的模糊搜索，而只是从前往后逐字符匹配的搜索，而且只是匹配到表格列表中的第一个，而对于相匹配表格后续的其他表格不会完全过滤掉，如上图中输入“wlwa”找到了“wlWarehouse1024”的表，这时，可通过键盘的“↑”或“↓”选择相应的表，然后“回车”切换。但后续的非“wlwa”开头的表仍然存在，因此，在表格欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com特别多的情况下，给“表格”命名时应自己确定一定的分类及分段方法，如同一类表格使用同样的起始标识，并起一个自己方便记忆的名字。三、妙用2：方便的跨表引用，不再为到处找数据列而烦恼很多时候，我们都需要进行跨表的数据引用，比如要通过函数引用其他表的数据参与计算，或做匹配等，经常要拿着鼠标到处找需要引用的那一列或那个区域，尤其当数据表很大列数很多的时候，找起来更是费劲，但自从你将普通的表转成了“表格”，你就轻松了——因为你可以直接在写公式的时候得到相应的提示！是的，就是这么神奇！以下将通过一个简单的例子来见证“奇迹的时刻”——在“成绩表”里根据“学生编号”从“学生表”里面匹配出“学生姓名”（这里使用index和match函数，这两个函数的具体使用方法请参考Excel帮助、网络相关文章，或本人文章《Excel函数应用手记_index和match》）。1.在成绩表里插入新的列“学生姓名”只要在成绩表的右边，标题行上输入“学生姓名”，然后回车，Excel将自动生成一个新的列，如下图所示：回车后Excel将在原来的score表上加入一列，如下图所示：欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com2.输入公式，根据提示快速选择表接下来我们开始输入公式“=index(stu……”See？student表随着公式的输入出来了！Excel就是这么牛B，直接给你提示！欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com此时，如果还有多个表的话，我们可以通过键盘的↑或↓进行表的选择，当选到我们需要的表时，按Tab键即选中该表进入公式。3.在公式中快速选择要引用的列引用表后，我们还要指定要引用的列，这时，我们在表名后面输入“[”——真正见证奇迹的时候！student表中所有的列名都出来了！欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com此时，同样地，如果列很多的话，我们可以直接输列名，或者可以通过键盘的↑或↓进行列的选择，当选到我们需要的列时，按Tab键即选中该列进入公式，然后输入“]”完成列的引用。4.在公式中仅引用某列的当前行为完成“学生姓名”的提取，我们继续，到match的时候，我们的lookup_value可是要用当前行的值，怎么办？——当然没问题，在“[”后再输入“@”，提示还在！是的，在Excel的“表格”中，对“[列名]”表示对整列的引用，“[@列名]”表示对该列当前行的引用，如下图所示：欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com5.公式的自动填充公式全部输入完毕后，回车，该公式将自动填充到该列的所有单元格中，不需要再动鼠标了——就是这么方便！如下图所示：至此，通过在公式输入时得到的提示，快速地实现了跨表的引用，当你开始习惯了这种输入的方法后，你将会发现原来通过鼠标到处找数据的过程是多么的痛苦，尤其是表很多、列很多的时候！欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com同时，你会发现，vlookup这个也许曾经陪你度过很多过日夜的牛B函数不再那么好用，因为要去数那个列数，或者再加个函数去算那个列数，真的是挺麻烦的。四、妙用3：表格自动扩展及动态行问题迎刃而解曾经有多个Excel大牛在网络上分享了大量关于“表格扩展时如何动态引用相应的数据”问题的解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 ，其中最多的方法无疑是使用offset函数，说句实话，我也曾一度疯迷offset函数的使用，但是，自从习惯于“插入/表格”，offset函数真的很久没怎么用了。“插入/表格”的这个妙用其实已经没什么好演示的了，因为“表格”一旦生成，其他地方对这个“表格”的引用就是动态的，引用的范围随着“表格”内容的扩展而扩展。不过，这里倒有一个很有趣的东西是，即使把“表格”的所有内容都删除（当然不能删除标题），“表格”中的公式依然存在，只要对公式列以外的列进行相应的编辑，公式列就会自动地扩展（如增加相应的行），给出计算结果。1.删除内容的表格下面这个是删除了内容的“表格”，注意到了吗？“表格”的内容全部删除后，“表格”中自动保留了一行空白行，此时的公式列“学生姓名”里也没有显示公式内容，但是，实际上，该公式仍然存在。欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com2.填充内容，公式自动重算这时候，如果我们对前面几列的数据再填充（复制粘贴：“表格”起始单元格右键，选择性粘贴数值即可）进去，“学生姓名”列会自动完成填充。如下图所示：对于这个功能如何用到日常的工作中，做成数据分析模板，实现数据的自动处理等等，也许不需要我在此再啰嗦了。欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com五、妙用4：格式套用，使表格精美并不影响计算效率曾一度，为了使数据表能隔行显示不同的颜色而在条件格式里大费周章，而且想换个颜色又得重新去设置，而且，对数据表过多的格式设置容易导致计算效率的下降，而现在，转换成“表格”后，可直接套用Excel中的格式，【表格工具/设计/表格样式】里有大量的模板供选择，十分方便，如下图所示：这种方式无需借助任何函数和额外功能，对表格的计算效率几乎没有影响，且随着表格的扩展而自动扩展。此外，在这里顺带说一下“清除格式”的功能，因为有很多数据表，当我们拿到的时候，可能设置了各种各样的格式，有些格式设置对表格的计算效率影响还很大。该功能位于【开始/编辑/清除】菜单下，如下图所示：欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com这个功能很简单，自己试几下就很清楚了。六、妙用5：导出到BI数据模型，大型数据分析的第一步说了那么多基本的使用方法，再来个“高大上”一点的，现在Excel里可使用PowerBIforExcel插件，对数据进行接入、转换、加载和分析，极大的增强了Excel的商务智能应用效能，具体使用方法敬请期待本人即将推出的《企业商务智能，从PowerBIforExcel开始》相关系列文章。当我们将普通的数据表通过“插入表格”的方式转换成为“表格”后，该表可以在PowerQuery中快速加载为“数据模型”（操作方法：选择“表格”中的任意单元格，点击【PowerQuery/从表】，在弹出PowerQuery的查询编辑器后，点击【关闭&加载】即可），从而进入后续的智能化分析中。欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com同时，在PowerBI中，我们将可以看到其中通过PowerQuery接入的数据都统一加载成为规范的“表格”，且表格之间可以像在欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com数据库里面一样建立“关系”，进而极大的方便数据的分析，避免需要进行各种繁杂的表间引用。该部分内容将在《企业商务智能，从PowerBIforExcel开始》相关系列文章中一并讲解。七、“插入表格”功能使用后的限制好处说完，也得说说使用了“插入表格”功能后带来的一些限制——任何事情都是这样，有得就有失。就像Excel的灵活性导致了目前大量不规范的数据存在一样，“插入表格”的功能刚好相反，由于是对普通数据表格的“规范”或者说格式化的过程，因而将导致该“表格”丧失一定的随意性，主要体现在以下几个方面：1.不允许在“表格”内进行合并单元格等设置选中多个单元格时，合并居中功能变灰，无法使用欢迎关注新浪微博：@爱格思喜爱·表格·思考欢迎保留以下联系方式进行转载Excel培训，请邮件联系：excel_aigesi@sina.com2.不允许批量复制或移动多个包含“表格”的工作表如下图，但我选定2个以上的工作表，然后希望通过“移动或复制”功能移动这些工作表到其他工作簿时，将会收到“不能复制或移动一组包含表的工作表”的警告提示。