按 Ctrl+T 把数据变成 Excel 表格,省掉大半表格烦恼

新增一行数据,合计却不变、公式也没填?选中数据按 Ctrl+T 变成 Excel 表格,自动扩展、公式自动填充、汇总行随筛选变化,三秒搞定大部分表格杂活。

您有没有遇到过这种事:在销售清单最下面加了一行新数据,表底的合计纹丝不动;最后一列的公式,新行里也是空的,因为没人把它往下拖。这真不怪您,是因为普通单元格区域根本不知道数据从哪开始、到哪结束。

解决办法就一个快捷键:点一下数据里的任意单元格,按 Ctrl+T,确认“表包含标题”,Excel 就会把它变成真正的“表格”。之后加行表格会自动扩展,公式自动填充,每个标题都带排序筛选按钮,勾一个选项就能给任意一列求和。大约三秒钟,却能省掉大部分琐碎的表格杂活。小编下面的演示基于 Windows 11 上的 Microsoft 365 版 Excel,示例区域是 A1:E6,新版本在这一步还可以直接给表格起名。

数据多了,范围自己长大

先看本文通篇用的示例数据,一开始只是 A1:E5 的普通区域。

日期销售区域数量单价
11/3AnaEast1225
11/3BenWest840
11/4CaraEast1525
11/4DevSouth560

变成表格后,在紧挨着的下一个空行里输入内容,表格就会自动延伸。微软官方的说法很直白:在表格最后一行下面的单元格开始输入,表格会扩展并把新行包含进来。粘贴也一样,只要粘到表格下方最左边的单元格。条纹底色、筛选按钮,以及所有引用这个表格的公式,都会跟着变。以后再也不用把区域地址从 A2:E5 手动改成 A2:E6 了。

公式读起来像句子

Excel 会把新表格依次命名为 Table1、Table2……您可以在“表设计”选项卡的“表名称”框里改名,这里叫它 Sales。这样公式里就能用表名和列名代替单元格地址,微软把这叫“结构化引用”。

  • =SUM(Sales[Units]) 对“数量”列求和,上面四行数据的结果是 40。
  • =AVERAGE(Sales[Price]) 的结果是 37.5。
  • [@Units] 表示“同一行的数量”,表格内部的公式就是这样引用同行数据的。

真正的好处是,增删数据时这些引用会自动调整。再加一行:Eli,West 区域,数量 10,单价 40,=SUM(Sales[Units]) 立刻变成 50,公式一个字都不用改。半年后别人看到 Sales[Units],也清楚它加的是什么,换成 D2:D5 就做不到了。

公式只写一次,整列自动填满

在 F1 输入标题“Total”,F 列就成了表格的新列。在 F2 输入 =[@Units]*[@Price] 并按回车,Excel 会把这个公式填进整列的每一行,这就是“计算列”。它保持一致:改动其中一个单元格的公式,整列会一起更新。

销售数量单价Total
Ana1225300
Ben840320
Cara1525375
Dev560300
Eli1040400

不用拖填充柄,不用双击单元格右下角,也不会有哪一行忘了复制公式。Eli 一加进表格,400 就自己算出来了。

会尊重筛选结果的汇总行

点表格里任意位置,进入“表设计”,勾选汇总行。表格底部会多出一行,每个单元格有个下拉菜单,可以选求和、平均值等函数。在 Total 列选“求和”,上面五行得到 1,695(300 + 320 + 375 + 300 + 400)。

不过请留意 Excel 写进去的公式:它不是普通的 SUM,而是 =SUBTOTAL(109,[Total]),而 SUBTOTAL 会忽略被筛选隐藏的行。用“区域”标题的筛选按钮只看 East,合计就变成 675(Ana 的 300 加 Cara 的 375);清除筛选又回到 1,695。想快速回答“这个区域卖了多少”,不用再写 SUMIF。

Excel 表格汇总行在区域筛选为 East 时显示 675
区域筛选为 East 时,汇总行显示 675,编辑栏里的公式是 =SUBTOTAL(109,[Total])。

筛选、条纹和切片器,开箱即用

表格的每个标题默认都有筛选按钮,按“数量”排序或只看某位销售,两下点击就行。条纹行会给隔行上色,列很多的时候眼睛也不容易看串行。

想要更直观的做法,点表格后选“插入”,再选“切片器”,勾选“区域”点确定,就会出现一个带 East、West、South 按钮的面板,点一下就筛选。把筛选好的视图交给不爱点下拉箭头的同事,会友好很多。

Excel 切片器按区域筛选表格
“区域”切片器选中 East 后,Sales 表格只显示 Ana 和 Cara。

基于表格做的东西,自动保持同步

表格是您其他常用工具的好地基。微软建议先把数据设置成表格,再创建数据透视表;之后新增了数据,只要刷新透视表即可(选中后按 Alt+F5)。

下拉列表也受益。如果数据验证下拉列表的选项放在表格里,微软说明,增删选项时,基于该表格的下拉列表会自动更新。给“区域”表加一个新区域,所有用到它的下拉列表里都会出现。条件格式也更好管,因为被高亮的数据有了固定、有名字的“家”。

表格能用在哪,不能做什么

项目说明
支持的 Excel 版本Microsoft 365 版 Excel(Windows 和 Mac)、网页版 Excel,以及 Excel 2024、2021、2019、2016
创建表格Ctrl+T,或“插入”→“表格”
更换样式“开始”→“套用表格格式”
“分类汇总”命令表格内不可用(用汇总行,或先转换为区域)
数据透视表新增数据后仍需刷新
撤销表格“表设计”→“转换为区域”

还有两点提醒。如果没勾选“表包含标题”,Excel 会自己插入一行 Column1、Column2 之类的标题,把您的数据整体往下推一行。另外,把表格转回区域后,样式会保留,但筛选按钮消失,公式里的结构化引用也会变回普通的单元格引用。

把数据变成表格的步骤

  1. 先整理:确保只有一行标题,中间没有整行空白。有重复项的话,先删除重复项。
  2. 点击数据里的任意单元格。
  3. 按 Ctrl+T(或“插入”→“表格”)。
  4. 确认区域,勾选“表包含标题”,点确定。
  5. 在“表设计”选项卡的“表名称”框里,输入一个简短的名字,比如 Sales。

微软官方的创建 Excel 表格说明页,把 Windows、Mac 和网页版的步骤都讲了。

常见问题(FAQ)

Excel 表格和“数据表”是一回事吗?

不是。模拟分析里的“数据表”是另一个功能。本文说的表格,就是 Excel 早先所说的“列表”。

一个工作表里可以放多个表格吗?

可以。一个工作表里想建几个就建几个,每个表格都有自己的名字。

转成表格会改变我原来的格式吗?

创建时 Excel 会套用一种表格样式。您可以在“开始”→“套用表格格式”里换别的样式;以后转回区域,样式格式会保留。

按 Ctrl+T 之后,公式不会全乱吗?

引用这块区域的公式一般会自动改成结构化引用并继续算对。不放心的话,先另存一份再操作。

小编的建议:从您更新最频繁的那张清单开始,就是总要拉长 SUM 范围、总要往下复制公式的那张。按 Ctrl+T,起个名,打开汇总行,然后在下面加一条新记录。看着合计自己变化,您大概率就会想把手里其他清单也都改成表格。