想要得到更多的Excel ?在微软的就职数据洞察力峰会上个月,一些专家提出一系列建议的Excel 2016。这里有10个最好的。
(注意:键盘快捷键将Excel的2016年版本,包括Mac;那些版本测试。和许多在Excel 2016的数据查询的选项选项卡来自权力查询插件Excel 2010和2013。所以如果你有权力的早期版本查询Excel在Windows上,这些技巧也会为你工作,尽管他们可能不会对Mac Excel工作。)
1。使用快捷方式创建一个表。表在Excel中最为有用的特性在相邻的列和行数据。表更容易排序,过滤和可视化,以及添加新行,保持相同的格式如上的行。此外,如果你从你的数据图表,使用一个表意味着表将自动更新如果你添加新行。
如果你已经从你的数据创建表将Excel丝带,点击插入表,有一个简单的键盘快捷键:首先选择数据后ctrl - a (command-shift-spacebar Mac),把它变成一个表Ctrl-T (command-T Mac)。
小贴士:确保重命名表相关的特定的数据,而不是离开默认标题表或表二。未来的自己会感谢你如果你需要访问这些信息从一个新的,更复杂的工作簿。
2。添加一个汇总行到一个表。你可以添加一个总结行到一个表在设计带在Windows或表丝带Mac通过检查“总一行。”Although it's called Total Row, you can select from a variety of summary statistics, not just a total sum: count, standard deviation, average and more.
当然你可以手动将信息插入一个电子表格的公式,把信息在共行意味着“附加”表但会留在底下一行,不管你如何选择你的表数据。这可以非常方便如果你做大量的数据探索。
注意,您需要创建一个总一行每一列分别;为一列不会自动生成创建和总结你的余生表(因为不是所有列有相同类型的数据——一笔列的日期没有多大意义,例如)。
3所示。轻松地选择行和列。如果你的数据表中,你需要把一整列的新公式,单击列名。将充分参考列的名字——有用如果你以后添加更多的行,因为你不需要调整一个更具体的参考如B2: B194。
注意:重要的是要确保你的光标看起来像一个向下的箭头在你单击列名。如果你的鼠标看起来像一个十字架当你这样做时,你会得到一个参考单独的细胞,不整列。
你的数据表中,是否有一些方便的快捷键可以选择使用:Shift +空格键选择整行和Ctrl +空格键(或控制+空格键为Mac)选择一整列。请注意,如果您的数据表中没有,这些选择超越可用数据,包括任何空细胞。表数据,选择停留在表的边界。
如果你想选择不是一整列在一个表中数据在他们的细胞,把光标放在旁边的一个列,Ctrl-down箭射中后,使用左右箭头键移动到你想要的列,然后点击Ctrl-Shift-up(使用命令而不是Ctrl Mac)。这可以方便的如果你的数据列很长。
4所示。过滤表数据切片机。Excel表提供每个列标题旁边的下拉箭头,简单的排序、搜索和过滤。然而,试图用小过滤数据下拉当你有大量的物品可能有点麻烦。几个主持人的数据洞察力峰会建议使用切片机。
“谁给你发送一个没有切片机的数据透视表,你应该教他们切片机在30秒。人们喜欢切片机,”印第安纳大学教授韦恩·温斯顿说,他还建议达拉斯小牛队老板马克在篮球统计数据。
虽然切片机最初是为数据透视表开发的,他们现在工作在“常规”表(在Windows上,此后Excel 2013)。“这实际上是更有用的,”温斯顿说。(切片机供数据透视表而不是普通表为Mac Excel 2016。)
添加一个切片机表,您的光标已经在桌上,头带设计,选择插入切片机,然后选择列(s)你想过滤器。
切片机将出现在你的工作表,出现一列宽几项显示。但是如果你有一个又长又窄的电子表格的空间数据的权利,您可以调整比默认的切片机是相当广泛的。您可以添加列在切片机切片机布局选项上。
如果你想过滤由多个项切片机,ctrl - click。清除所有过滤器,有一个清晰的按钮在右上角的切片机。
5。创建一个过滤器表时总结细胞变化。如果您创建了一个表,总结了数据在表外细胞——一个列的总和,例如,你想显示细胞的更新和如果你过滤表,一个基本的求和公式不能工作。
而不是简单地在细胞,使用和使用细胞内的聚合函数你的手机,然后可以与你表过滤器。
Excel的聚合函数需要三个参数,其中两个是数字。Excel为Windows提供了可用选项的列表。
总需要三个参数:一个函数个数,所需的选项数目和细胞需要操作的范围。类型=总(在Excel为Windows和您将看到可用的功能和选项;在Excel中Mac,你必须点击总帮助函数为了看到可用的功能和选项的数字。
是9号函数总和;忽略隐藏选项5行。所以,细胞用以下代码:
5 =总(9日,Table1[费用])
给你的总和可见行。如果一个过滤器改变哪些行是可见的,你的金额会相应地改变。
总提供的选项总结只有可见的行。
6。数据透视表中的数据进行排序。有时你想在数据透视表数据由一个特定的列进行排序——就像一个常规表。但与普通表,数据透视表没有下拉菜单每一列排序的功能。然而,如果你选择在第一列的下拉箭头,你会得到一个菜单允许您以任何列进行排序。
7所示。“透视”数据。一些调用这个重塑数据从“大”到“长”。在数据库中,它被称为“折叠”:把数据从单独的列和移动到行。基本上,它的对面是创建数据透视表,数据透视表,你把类别在一列成自己的列。
透视列,您需要在Excel 2016中使用查询编辑器。通过数据访问查询编辑器带:在得到&变换部分,选择表。
一旦查询编辑器出现(如果你的数据不是已经在表中,你会被要求确认数据范围),选择您想要透视的列,单击Transform选项卡并选择透视列。
Excel的查询编辑器为用户提供选择透视列。
这将创建两个新列你的电子表格,在正确的属性和值,列你透视。你可以将这些列重命名为更有意义的东西,如“产品”和“价格”或“季度”和“收入”。
保存您的工作,选择文件>关闭&负载(缺省目标)文件> &负载要问,你想保存您的结果。如果你想关闭不保存,你会问你是否想继续您的更改;说,是的,他们会被保存在一个新的工作表。
透视数据宽表转化为一个长,结合多个列成两个:属性(类别)和价值。
微软办公网站透视的更多信息。
8。使多个数据透视表的一列类别。如果你有一个数据透视表,添加一个过滤器一列包含类别,您可以生成数据透视表的副本,每个类别过滤器,一个去分析>选项>显示报告过滤页然后选择你想要的过滤器。这可以方便,不必手动点击每个类别的过滤器。
(在Mac Excel 2016,到数据透视表选项卡上,选择进行分析过滤选项>显示报告页面。)
9。与索引查找数据匹配。虽然VLOOKUP是一个受欢迎的方式找到一个Excel表中的数据插入到另一个索引结合匹配可以更加灵活和强大。下面是如何使用它们。
假设您有一个查找表,列一个计算机模型名称,列B价格信息,列也D计算机模型的名称,你想添加价格信息。使用这种格式创建一个公式:
=指数(ColumnToSearchForValue匹配(CellWithLookupKey ColumnToSearchForLookupKey 0)
一个示例的样子:
=指数(B2: B73,匹配(D2 A2: A73, 0))
这是/为什么指数匹配是如何工作的(如果你不需要知道,跳到下一个提示):选择一个特定的细胞指数数值位置。你第一次给它一个范围的细胞,在一列或一行,然后告诉你想要的特定数量的细胞。
例如,您可以选择B栏中的第六项:
=指数(B2: B19, 6)。
你会使用以下格式:
=指数(ColumnOrRowToSearch ItemNumberInThatColumnOrRow)
然而,使用索引就没有多少帮助,如果你想找到另一个列的值根据一些条件。也就是说,你不希望第六项价格列B;你想要的物品在你的价格列匹配的列,如一个特定的计算机模型。
这就是比赛。匹配搜索一个值在一系列细胞的位置,并返回匹配,使用以下格式:
=匹配(SearchValue RangeToSearch MatchType)
(匹配类型可以是0完全相等,最大1值小于或等于你寻找或1的最小值大于或等于查找值。)
所以,如果你想找到细胞B柱的位置,正是999年,您可以使用:
=匹配(999 B2: B79 0)。
因此组合:匹配,寻找一个特定值基于搜索词,返回一个细胞位置;和索引需要一个位置作为第二个公式的参数。
10。看一个公式计算一步一步(Windows)。有复杂的公式吗?如果你想看它如何被评估,去公式>评估公式一步一步的计算运行。
11。从网上和刷新数据导入到Excel。你有格式良好的HTML表最好能够在Web页面;有更多的自由格式的文本(甚至不格式化的表),你需要做大量的额外的编辑你可以分析你的数据到一个形式。
记住,警告,如果你想从网上一个HTML表拖到Excel,头在Excel为Windows和选择Data选项卡:从其他来源新的查询> >从Web
输入适当的Web页面的URL。Excel会寻找和可用的HTML表列表页面。点击表看到预览;当你发现一个你想要的,点击加载。
为什么不一个格式良好的HTML表复制并粘贴到Excel ?如果数据更新频繁,你可以很容易地更新表中单击右键,选择更新而不是复制粘贴新的数据。
更多的会议,检查微软数据视频在YouTube上的见解。
这个故事,“高级用户11 Excel技巧”最初发表的《计算机世界》 。