如何实现有下拉菜单的跨表数据汇总并查询
时间:2023-04-08 16:10:49
如何实现有下拉菜单的跨表数据汇总并查询
问题情境
汇总查询表”部门费用“如下,其中A2单元格是下拉菜单,内容是12个月份。
查询表”部门费用“中12个月份的数据来源于同一工作薄中不同的12个以月份命名的工作表:
汇总并查询效果如下:
公式实现
在B3单元格输入公式:“=INDIRECT($A$2&”!”&ADDRESS(ROW(2:2),COLUMN(B:B),4))“,按Enter键结束计算,并将公式向右向下填充,可得结果。
如下图:
公式解析
公式为“=INDIRECT($A$2&”!”&ADDRESS(ROW(2:2),COLUMN(B:B),4))“,其中:
ROW(2:2):该部分公式返回值为2,即所取数据来源于第2行,当公式向下填充时,本部分返回值随公式填充而改变,每向下填充一行,返回值加1,公式向右填充,返回值不变;
COLUMN(B:B):该部分公式返回值为2,即所取数据来源于第2列,当公式向右填充时,本部分返回值随公式填充而改变,每向右填充一列,返回值加1,公式向下填充,返回值不变;
ADDRESS(ROW(2:2),COLUMN(B:B),4):该部分公式返回值为B2,即所取数据来源于B2单元格,公式每下向填充一行,行数加1,每向右填充一列,列数加1;
$A$2&”!”&ADDRESS(ROW(2:2),COLUMN(B:B),4):该部分公式返回值为A2单元格指向的工作表,即1月的B2单元格。由于月份均在A2单元格,所以此单元格绝对引用,不随公式的填充而改变;
INDIRECT($A$2&”!”&ADDRESS(ROW(2:2),COLUMN(B:B),4)):该部分公式返回A2向的工作表的B2单元格数据。
函数解析
附函数ADDRESS的用法:
【功能】
ADDRESS 函数,根据指定行号和列号获得工作表中的某个单元格的地址。例如,ADDRESS(2,3) 返回 $C$2。再例如,ADDRESS(77,300) 返回 $KN$77。可以使用其他函数(如 ROW 和 COLUMN 函数)为ADDRESS 函数提供行号和列号参数。
【语法】
ADDRESS(row_num, column_num, [abs_num],[a1], [sheet_text])
【中文语法】
ADDRESS(行号, 列号, [引用类型],[引用样式], [引用工作表])
【参数】
row_num 必需。一个数值,指定要在单元格引用中使用的行号。
column_num 必需。一个数值,指定要在单元格引用中使用的列号。
abs_num 可选。一个数值,指定要返回的引用类型。不同数字对应的引用类型如下表:
abs_num返回的引用类型
1 或省略绝对值
2绝对行号,相对列标
3相对行号,绝对列标
4相对值
a1 可选。 一个逻辑值,指定 A1 或 R1C1 引用样式。在 A1 样式中,列和行将分别按字母和数字顺序添加标签。 在 R1C1 引用样式中,列和行均按数字顺序添加标签。如果参数 A1 为 TRUE 或被省略,则 ADDRESS 函数返回 A1 样式引用;如果为 FALSE,则 ADDRESS 函数返回 R1C1 样式引用。
sheet_text 可选。一个文本值, 指定要用作外部引用的工作表的名称。例如, 公式=ADDRESS (1, 1,,,”Sheet2″)返回Sheet2! $A $1。如果省略了sheet_text参数, 则不使用工作表名称, 并且该函数返回的地址引用当前工作表上的单元格。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
要使用本计算机,用户必须输入用户名和密码选项不见了怎么办
![](https://img.aspxhome.com/file/2023/2/46522_0s.jpg)
Excel怎么插入二次公式
![](https://img.aspxhome.com/file/2023/9/a153939_0s.png)
excel如何在表格页眉处插入图片
由于找不到vcruntime140.dll怎么解决?
![](https://img.aspxhome.com/file/2023/1/a271903_0s.png)
win10系统应用商店没了怎么恢复?
![](https://img.aspxhome.com/file/2023/1/a282147_0s.png)
干货分享|Google Chrome Helper占用过高CPU解决办法
![](https://img.aspxhome.com/file/2023/5/a209955_0s.png)
Excel使用IMEXP函数计算复数的指数
咪咕善跑怎么设置每日目标?咪咕善跑怎么设置每日目标的方法
![](https://img.aspxhome.com/file/2023/8/a334069_0s.jpg)
Excel最常用的公式运算技巧汇总
![](https://img.aspxhome.com/file/2023/5/a142895_0s.jpg)
Win10系统下如何给cpu降温?
![](https://img.aspxhome.com/file/2023/26/a221807_0s.png)
WPS演示中的音乐播放图标如何隐藏
MBR和GPT硬盘分区表哪个比较好?
![](https://img.aspxhome.com/file/2023/6/a326203_0s.jpg)
PC版Windows 10硬件需求公布
![](https://img.aspxhome.com/file/2023/29/a251401_0s.jpg)
如何将大括号./ 和 * 出现在word2007文档
![](https://img.aspxhome.com/file/2023/3/21433_0s.jpg)
Win10内置虚拟光驱,可直接装载打开ISO光盘镜像文件
![](https://img.aspxhome.com/file/2023/30/a262850_0s.png)
Mac如何使用活动监视器在Dock中显示系统使用状况
![](https://img.aspxhome.com/file/2023/5/a216195_0s.jpg)
蓝叠模拟器游戏黑屏或无法运行怎么办?蓝叠模拟器游戏黑屏或无法运行的解决方法
![](https://img.aspxhome.com/file/2023/7/a327763_0s.jpg)
手机版 WPS 怎样快速把文档中的页面提取出来
![](https://img.aspxhome.com/file/2023/4/a184584_0s.png)
win8平板电脑升级安装win10系统怎么样的情况分析
![](https://img.aspxhome.com/file/2023/3/a292088_0s.jpg)
U盘存储空间提示已满的原因及解决
![](https://img.aspxhome.com/file/2023/2/a289178_0s.jpg)