了解如何使表格更有效、重塑数据、轻松查找数据等等。
想要从 Excel 中获得更多功能？在微软的首届数据洞察峰会上个月，一些专家提出了一系列建议，以便充分利用 Excel 2016。以下是其中 10 条最佳建议。
（注意：键盘快捷键适用于 2016 版 Excel，包括 Mac；这些是经过测试的版本。Excel 2016 的数据选项卡中的许多查询选项来自 Excel 2010 和 2013 的 Power Query 插件。因此，如果您在 Windows 上较早版本的 Excel 上安装了 Power Query，那么这些技巧中的许多技巧也适用于您，尽管它们可能不适用于 Mac 版 Excel。）
1. 使用快捷方式创建表格
对于连续的列和行中的数据，表格是 Excel 中最有用的功能之一。表格使排序、筛选和可视化变得更容易，还可以添加与上方行保持相同格式的新行。此外，如果您根据数据制作图表，使用表格意味着图表会在您添加新行时自动更新。
如果您通过转到 Excel 功能区，单击“插入”，然后单击“表格”来根据数据创建表格，则有一个简单的键盘快捷键：首先使用 Ctrl-A（Mac 为 command-shift-spacebar）选择所有数据，然后使用 Ctrl-T（Mac 上为 command-T）将其转换为表格。
额外提示：确保将表格重命名为与特定数据相关的名称，而不是保留默认标题 Table1 或 Table2。如果您需要从新的、更复杂的工作簿访问该信息，您将来会感谢您。
2. 向表中添加摘要行
您可以在 Windows 上的“设计”功能区或 Mac 上的“表格”功能区中选中“总计行”，从而向表格添加摘要行。虽然它被称为“总计行”，但您可以从各种汇总统计数据中进行选择，而不仅仅是总数：计数、标准差、平均值等等。
虽然您当然可以使用公式手动将这些信息插入电子表格，但将信息放入“总计行”意味着它“附加”到您的表格中，但无论您选择如何对表格数据进行排序，它都会保留在最底行。如果您正在进行大量数据探索，这会非常方便。
请注意，您需要为每一列单独创建一个总计行；为某一列创建总计不会自动为表格的其余部分生成总计（因为并非所有列都具有相同类型的数据 - 例如，对日期列进行总计就没有多大意义）。
3. 轻松选择列和行
如果您的数据在表格中，而您需要在新公式中引用整个列，请单击列名。这将按名称引用整个列 — 如果您稍后在表格中添加更多行，这将非常有用，因为您不必重新调整更具体的引用，例如 B2:B194。
注意：在单击列名之前，务必确保光标看起来像向下箭头。如果单击时光标看起来像十字，则您将获得对该单元格的引用，而不是对整个列的引用。
无论您的数据是否在表格中，您都可以使用几个方便的选择快捷键：Shift+空格键选择整行，Ctrl+空格键（或 Mac 上的 Control+空格键）选择整列。请注意，如果您的数据不在表格中，这些选择将超出可用数据的范围，并包括超出范围的任何空单元格。对于表格数据，选择将停止在表格的边界处。
如果您想要选择表格中不包含数据的整个列，请将光标放在旁边的列中，按 Ctrl-向下箭头，使用向右或向左箭头键移动到所需的列，然后按 Ctrl-Shift-向上（在 Mac 上使用 command 而不是 Ctrl）。如果您的数据列很长，这会很方便。
4. 使用切片器过滤表格数据
Excel 表格在每个列标题旁边都提供了下拉箭头，便于排序、搜索和过滤。但是，当您有大量项目时，尝试使用这个小下拉列表过滤数据可能会有些麻烦。数据洞察峰会上的几位演讲者建议改用切片器。
印第安纳大学教授韦恩·温斯顿 (Wayne Winston) 表示：“如果有人给你发了一张没有切片器的数据透视表，你应该在 30 秒内教会他们切片器。人们喜欢切片器。”他还为达拉斯小牛队老板马克·库班 (Mark Cuban) 提供篮球统计方面的建议。
但是，虽然切片器最初是为数据透视表开发的，但现在它们也适用于“常规”表（自 Windows 上的 Excel 2013 以来）。“这实际上更有用，”温斯顿辩称。（切片器可用于数据透视表，但不适用于 Excel for Mac 2016 中的常规表。）
要向表中添加切片器，请将光标放在表的某个位置，前往设计功能区，选择“插入切片器”，然后选择要过滤的列。
切片器将显示在您的工作表中，显示一列宽，仅显示几个项目。但是，如果您的电子表格又长又窄，数据右侧有大量空间，则可以将切片器的大小调整为比默认的宽得多。您可以在功能区上的切片器选项中将列添加到切片器布局中。
如果要按切片器中的多个项目进行过滤，请按住 Ctrl 键并单击。要清除所有过滤器，请使用切片器右上角的清除按钮。
5. 创建在筛选表格时发生变化的摘要单元格
如果您在表格外部创建一个单元格来汇总表格内的数据（例如，某一列的总和），并且您希望该单元格在您按某些内容过滤表格时显示更新的总和，则基本的 SUM 公式将不起作用。
不要简单地在该单元格中使用 SUM，而是使用单元格内的 AGGREGATE 函数，然后您的单元格就可以链接到您的表格过滤器。
AGGREGATE 需要三个参数：函数编号、所需选项编号以及要操作的单元格范围。输入=AGGREGATE(在 Windows 版 Excel 中，您将看到可用的函数和选项；在 Mac 版 Excel 中，您必须单击 AGGREGATE 帮助函数才能查看可用的函数和选项编号。
SUM 是函数编号 9；忽略隐藏行是选项 5。因此，单元格中包含以下代码：
=AGGREGATE(9,5,Table1[Expenditures])
给你所有的总和可见的仅限行。如果过滤器更改了可见的行，则总数也会相应更改。
AGGREGATE 提供了仅汇总可见行的选项。
6. 对数据透视表中的数据进行排序
有时，您希望按数据透视表中的特定列对数据进行排序 — 就像常规表一样。但与常规表不同，数据透视表没有在每列上提供下拉菜单来提供排序功能。但是，如果您选择第一列上的单个下拉箭头，您将获得一个菜单，允许您按任何列进行排序。
7. '逆透视' 数据
有些人称之为在数据库世界中，这被称为“折叠”：从各个列中获取数据并将其移动到行中。基本上，这与创建数据透视表相反 - 在数据透视表中，您将一列中的类别拉到它们自己的列中。
要取消透视列，您需要使用 Excel 2016 中的查询编辑器。通过数据功能区访问查询编辑器：在获取和转换部分中，选择从表。
查询编辑器出现后（如果您的数据尚未在表中，则会要求您先确认数据范围），选择要取消透视的列，单击“转换”选项卡并选择“取消透视列”。
Excel 的查询编辑器为用户提供了取消透视列的选项。
这将在电子表格右侧创建两个新列，即“属性”和“值”，其中包含您取消透视的列。您可以将这些列重命名为更有意义的名称，例如“产品”和“价格”或“季度”和“收入”。
要保存您的工作，请选择文件 > 关闭并加载（到默认目的地）或文件 > 关闭并加载到以便询问您想要将结果保存到哪里。如果您尝试关闭而不保存，系统将询问您是否要保留更改；选择“是”，更改将保存在新的工作表中。
逆透视数据将宽表变成长表，将多列合并为两列：属性（类别）和值。
Microsoft Office 网站有有关逆透视的更多信息。
8. 为同一类别列创建多个数据透视表
如果您有一个数据透视表，并为包含类别的一列添加了过滤器，则可以通过以下方式生成该数据透视表的副本，为过滤器中的每个类别生成一个副本：分析 > 选项 > 显示报告筛选页面然后选择所需的过滤器。这比手动点击过滤器中的每个类别更方便。
（在 Excel 2016 for Mac 上，转到功能区上的“数据透视表分析”选项卡，然后选择选项 > 显示报告筛选页面。
9. 使用 INDEX MATCH 查找数据
虽然 VLOOKUP 是一种在 Excel 表中查找数据并将其插入另一个表中的常用方法，但 INDEX 与 MATCH 结合使用可以更加强大和灵活。以下是它们的使用方法。
假设您有一个查找表，其中 A 列包含计算机型号名称，B 列包含价格信息，D 列还包含要添加价格信息的计算机型号名称。使用以下格式创建公式：
=INDEX(ColumnToSearchForValue, MATCH(CellWithLookupKey, ColumnToSearchForLookupKey, 0)
示例可能如下所示：
=INDEX(B2:B73, MATCH(D2, A2:A73, 0))
INDEX MATCH 的工作原理/原因如下（如果您不需要知道，请跳到下一个提示）：INDEX 根据数字位置选择特定单元格。您首先为其提供一个单元格范围（可以是单列或单行），然后告诉它您想要的单元格的具体编号。
例如，你可以选择 B 列中的第 6 项：
=INDEX(B2:B19, 6)。
您将使用以下格式：
=INDEX(ColumnOrRowToSearch, ItemNumberInThatColumnOrRow)
但是，如果您想根据另一列中的某些条件查找值，仅使用 INDEX 并没有多大帮助。也就是说，您不想要价格列 B 中的第 6 个项目；您想要价格列中与 A 列中的某些内容相匹配的项目，例如某个计算机型号。
这就是 MATCH 的作用所在。MATCH 在单元格区域内搜索某个值并返回匹配项的位置，使用以下格式：
=MATCH(SearchValue,RangeToSearch,MatchType)
（匹配类型可以是 0（表示完全相等）、1（表示小于或等于您要搜索的最大值）、或 -1（表示大于或等于查找值的最小值）。
因此，如果您想找到 B 列中正好为 999 的单元格的位置，您可以使用：
=MATCH(999, B2:B79, 0)。
因此，组合：MATCH 根据搜索词寻找特定值，返回单元格位置；INDEX 需要一个位置作为其第二个公式参数。
10. 观看公式的逐步求值（仅适用于 Windows）
公式很复杂？如果你想了解它的求值方式，请访问公式 > 计算公式查看计算逐步进行的过程。
11. 将 Web 上的数据导入并刷新到 Excel
当网页上有格式良好的 HTML 表格时，这种方法最有效；如果存在更多自由格式的文本（甚至是格式不佳的表格），您将需要进行大量额外的编辑才能将数据转换为可以分析的形式。
考虑到该警告，如果您想将 HTML 表从 Web 拉入 Excel，请转到 Windows 版 Excel 上的“数据”选项卡并选择：新查询 > 来自其他来源 > 来自网络
输入相应网页的 URL。Excel 将查找并列出该页面上可用的 HTML 表格。单击表格以查看预览；找到所需的表格后，单击“加载”。
为什么不直接将格式良好的 HTML 表格复制并粘贴到 Excel 中呢？如果数据经常更新，您可以通过右键单击表格并选择“刷新”轻松刷新数据，而不必复制和粘贴新数据。
有关会议的更多信息，请查看YouTube 上的 Microsoft Data Insights 视频。
