【技巧分享】表格公式中的[、#、@的另类使用方法

懒得批爆
懒得批爆

创作者俱乐部成员

系统版本如下:

Excel和WPS的版本如下:


记得前不久,在社区或微信上有人问如何将一张有很多行的表格中某些列的数据和格式都复制到另一个工作表或工作簿中,有人说全部复制过去然后删掉不需要的,有人说用数组引用,有人说选中需要的列后再复制,有人说用表公式(我一般喜欢这么操作)。

那么如果这个很多行有五位数、六位数呢?

首先看看下面这张数据表https://kdocs.cn/l/cgCoJ2LUGGnO,由于最近工作比较忙,还是用之前脱敏的表格。

分别在Excel和WPS中打开

如图所示,71426行,A:V列共计22列,那么一共有1561114个单元格,其中还有一些是公式,与后面的[映射表]之间相互有关系,如果需要复制,那么复制时长还与硬件质量有关。

先看看正常的Ctrl + A复制

用Excel和WPS分别测试,都能看见下面的有个进度条表示计算进度。

接下来使用表公式操作,我一般喜欢这么做

为了区分槽点,这次先上WPS的操作,可能有人知道这是怎么回事,但我还是要吐个槽,为啥WPS不把这个功能复制完整呢?

从Excel的操作中能音乐看到一个列表,里面有#、@和(...)看起来都是[表1]中的标题,这是怎么回事呢。

不要着急,看我慢慢讲解。

这个公式不仅能引用整个表格,还可以引用标题、数据、汇总等信息,这个列表及功能只能在Excel中截图,WPS支持这个语法,但不显示这个列表

比如只引用标题

有人说,引用整个标题有什么好稀奇的,我用=基础数据表!1:1也能引用。

确实可以,但这个表1无论放在任意工作表中、任意行中用这个公式=表1[#标题]都能得到标题,如果用=基础数据表!1:1就需要调整引用的行了。

也支持使用函数,而且不需要考虑或查看表1位于哪个sheet下面、从第几行开始引用。

然后开始展示另一个有趣儿的地方

从这两张输入公式=表1[[#汇总],[含税收入(元)]]能否看出为什么WPS中使用复制公式,Excel中使用手写选择公式呢?

显而易见的是WPS没有清单、没有列表,无法用记忆去记录整个表1的所有标题,所以只能事先复制好后再粘贴或粘贴后修改。

Excel则列出了表1的所有选项,用那个选择哪个即可。

接下来说说@符号的用处,大多数知道的都是关于交叉计算的用处,其实还有一种用法,可能有些人看到过,却不知道这是怎么来的。

比如要计算表1的第一行税额和汇总税额,用含税收入-不含税收入即可。

正常情况下计算怎么算,整列求和后相减?

而使用@又怎么计算呢?

又比如在这个查询表中

在不查看原始数据表的情况下,以下两种公式,哪种更易于解读呢:

公式1:=SUMIFS(表1[不含税收入(元)],表1[收入类型],$D$2,表1[量收日期],">="&E$4,表1[量收日期],"<="&E$5,表1[单位],$C29)/10000
公式2:=SUMIFS(基础数据表!L2:L71426,基础数据表!I2:I71426,$D$2,基础数据表!A2:A71426,">="&E$4,基础数据表!A2:A71426,"<="&E$5,基础数据表!V2:V71426,$C29)/10000

在不查看原始数据表的情况下,能否快速说出[基础数据表!]中L列是什么字段、N列是什么字段?

最后再吐个槽,WPS什么时候能将表数据的列表加上,对于动则二十多列的数据表结构标题真心记不完整。

四川省
浏览 2610
3
33
分享
33 +1
31
3 +1
全部评论 31
 
榆次
感谢大佬分享,解惑解惑了
· 广东省
回复
 
最残大脑
码住
· 辽宁省
回复
 
user_300
感谢分享
· 湖北省
回复
user_300
· 湖北省
回复
 
『峰格』
做自己。至于别人,分交情,看心情
· 山东省
回复
 
蒋老师
感谢楼主的教学,学习了。
· 四川省
回复
 
麦子颉
麦子颉

创作者俱乐部成员

学习下
· 北京
回复
 
李科
学习了
· 重庆
回复
 
Oliver
学习
· 广东省
回复
 
Hao123
学习啦
· 内蒙古
回复
 
『峰格』
学到了
· 山东省
回复
 
丹
学习
· 天津
回复
 
lida
来学习
· 上海
回复
 
米斯特王
米斯特王

创作者俱乐部成员

学习
· 浙江省
回复
 
幸福春
收藏慢慢学
· 山东省
回复
 
HC.旋
不够看呀,大佬继续
· 福建省
回复
 
寻残梦
666
· 广东省
回复
 
LG
111
· 新疆
回复
 
悠扬的旋律
我没用过超级表,我关心的是公式引用比复制粘贴快吗
· 浙江省
回复
懒得批爆
懒得批爆

创作者俱乐部成员

复制粘贴需要消耗内存值,而公式是引用的逻辑值。 如果你需要复制的仅仅几十几百行,那么还是复制粘贴吧,如果是我这种上万行(也许往后走会达到几十万行),几十列的数据,推荐还是用公式引用比较方便,至少容错率高一些。
· 四川省
回复
 
皮乐
感谢分享
· 河南省
回复
 
Calvinojr
这是把excel表当轻量化数据库在用啊
· 湖北省
回复