11个超级用户的Excel提示

11个超级用户的Excel提示

想从Excel中获得更多信息吗?在上个月举行的微软首届数据洞察峰会上,几位专家为充分利用Excel 2016提出了一系列建议。这里有10个最好的。

(注:键盘快捷键适用于2016版Excel,包括Mac;这些是测试过的版本。Excel 2016的“数据”选项卡中的许多查询选项来自Excel 2010和2013的Power query外接程序。因此,如果您在Windows上的早期版本的Excel上有powerquery,那么这些提示中的很多也适用于您,尽管它们可能不适用于excelformac。)

1使用快捷方式创建表。对于连续列和行中的数据,表格是Excel中最有用的功能之一。表格使排序、筛选和可视化更容易,还可以添加与上面的行保持相同格式的新行。此外,如果使用数据制作图表,则使用表意味着如果添加新行,图表将自动更新。

如果您是通过转到Excel功能区,单击“插入”,然后单击“表格”来从数据创建表格的,那么有一个简单的键盘快捷键:首先使用Ctrl-A(Mac上的command-shift空格键)选择所有数据后,使用Ctrl-T(Mac上的command-T)将其转换为表格。

额外提示:确保将表重命名为与特定数据相关的内容,而不是保留默认标题Table1或Table2。如果您需要从新的、更复杂的工作簿中获取这些信息,您未来的自我将会感谢您。

2向表中添加摘要行。您可以通过选中“总计行”将摘要行添加到Windows上的设计功能区或Mac上的表功能区中的表中。虽然它被称为总计行,但您可以从各种摘要统计信息中进行选择,而不仅仅是总计:计数、标准差、平均值等。

当然,您可以使用公式将这些信息手动插入电子表格,但将这些信息放在合计行中意味着它“附加”到表中,但不管您选择如何对表数据进行排序,这些信息都将保留在最下面的行中。如果您正在进行大量的数据探索,这将非常方便。

注意,您需要为每一列分别创建一个总计行;为一列创建总和不会自动为表的其余部分生成总和(因为并非所有列都具有相同类型的数据——例如,为日期列创建总和就没有多大意义)。

三。轻松选择列和行。如果数据在表中,并且需要引用新公式中的整列,请单击列名。这将按名称提供对完整列的引用—如果以后向表中添加更多行,这将非常有用,因为不必重新调整更具体的引用,例如B2:B194。

注意:在单击列名之前,确保光标看起来像向下箭头是很重要的。如果这样做时光标看起来像一个十字,则只会引用那个单独的单元格,而不是整个列。

无论数据是否在表中,都可以使用几个方便的选择快捷方式:Shift+空格键选择整行,Ctrl+空格键(或Mac的control+空格键)选择整列。请注意,如果您的数据不在表中,则这些选择超出了可用数据的范围,并包含了超出范围的所有空单元格。对于表数据,选择在表的边框处停止。

如果要选择不在表中的整列,而该列中只有包含数据的单元格,请将光标放在该列旁边的列中,按Ctrl向下箭头键,使用向右或向左箭头键移动到所需列,然后按Ctrl Shift up键(在Mac上使用命令而不是Ctrl键)。如果您的数据列很长,这会很方便。

4使用切片器筛选表数据。Excel表格在每个列标题旁边提供了下拉箭头,便于排序、搜索和筛选。但是,当您有大量项目时,尝试使用这个小下拉列表来过滤数据可能会有些麻烦。Data Insights峰会上的一些演示者建议改用切片器。

“任何人给你一个没有切片器的透视表,你应该在30秒内教他们切片器。印第安纳大学教授韦恩·温斯顿说:“人们喜欢切片机。”他还为达拉斯小牛队老板马克·库班提供篮球数据方面的建议。

不过,虽然切片器最初是为透视表开发的,但它们现在也可以在“常规”表上工作(而且从Excel 2013在Windows上开始)这实际上更有用,”温斯顿争辩道(切片器可用于透视表,但不适用于Mac 2016 Excel中的常规表。)

若要将切片器添加到表中,且光标已位于表中的某个位置,请转到“设计”功能区,选择“插入切片器”,然后选择要筛选的列。

切片器将显示在您的工作表上,只显示一列宽的项目。但是,如果您有一个长而窄的电子表格,并且数据右侧有大量空间,则可以调整切片器的大小,使其比默认值宽得多。可以在功能区的切片器选项中向切片器布局添加列。

如果要按切片器中的多个项目进行筛选,请按住Ctrl键并单击。要清除所有过滤器,切片器右上角有一个“清除”按钮。

5创建筛选表时更改的摘要单元格。如果在汇总表中数据的表外创建一个单元格(例如,列的总和),并且希望该单元格显示更新的总和(如果按某些内容筛选表),则基本的总和公式将不起作用。

不要简单地在单元格中使用SUM,而是在单元格中使用AGGREGATE函数,这样您的单元格就可以链接到表过滤器。

Excel的聚合函数需要三个参数,其中两个是数字。Excel for Windows提供了可用选项列表。

聚合需要三个参数:函数号、所需选项号和要对其进行操作的单元格范围。类型=聚合(在Excel for Windows中,您将看到可用的函数和选项;在Excel for Mac中,您必须单击“聚合帮助”函数才能查看可用的函数和选项号。

和是9号函数;忽略隐藏行是选项5。所以,一个具有以下代码的单元格:

=合计(9,5,表1[支出])

只提供所有可见行的总和。如果一个过滤器改变了哪些行是可见的,那么你的总和也会相应地改变。

AGGREGATE提供了只汇总可见行的选项。

6对数据透视表中的数据进行排序。有时,您希望按照数据透视表中的特定列对数据进行排序,就像对常规表一样。但与常规表不同,pivot表的每一列上都没有提供排序功能的下拉菜单。但是,如果您选择第一列上的单独下拉箭头,您将得到一个允许您按任何列排序的菜单。

7. "“取消打印”数据。有人称之为将数据从“宽”重塑为“长”。在数据库世界中,它被称为“fold”:从单个列中获取数据并将其移动到行中。基本上,这与创建透视表相反——在透视表中,将一列中的类别向上拉到它们自己的列中。

要取消PIVOT列,需要使用Excel 2016中的查询编辑器。通过数据功能区访问查询编辑器:在“获取和转换”区域中,选择“从表”。

一旦查询编辑器出现(如果您的数据不在表中,系统会要求您首先确认数据范围),选择要取消IVOT的列,单击“转换”选项卡并选择“取消IVOT列”。