EXCEL多表合并、汇总,二级联动下拉菜单,一个函数5种经典应用

EXCEL多表合并、汇总,二级联动下拉菜单,一个函数5种经典应用

首页休闲益智永恒合并更新时间:2024-07-30
导读:

在EXCEL知识的海洋里,办公室工作的每个人,从小白到高手,每个阶段,都有必须掌握的技巧和公式,而有一条通向高手的毕竟之路,就是indirect函数,今天重点讲解一下。

作者简介:

MOS办公软件国际认证大师级

10年办公应用经验,企业部门负责人

函数释义:

INDIRECT(ref_text,[a1])

此函数立即对引用进行计算,并显示其内容。当需要更改公式中 单元格 的引用,而不更改公式本身,请使用此函数,INDIRECT为间接引用。

题引:

在上图中,F1单元格值为100,G2单元格值为F1,如果我们在H2输入公式=inderect(G2),得到的结果,是100,而不是F1,这是因为这个函数不是直接的引用函数,而是作为一种迂回存在,先得到G2只为F1,而后返回F1的值。

功能简介:5种用法,助你成就高手之路

用法1:二级下拉菜单制作

图二

如图二,制作步骤:

  1. 在D列输入列标题,部门,而后在部门下面,新建公司的部门, 这里只做两个部门,品质部和销售部
  2. 以D列新建的两个部门为列标题,而后新建两列,并在每个部门下面,输入部门的人员名单
  3. 选中基础数据区域,就是部门区域,而后点击公式,根据所选内容创建,在弹出的窗口,我们选中首行
  4. 选中首行后,就产生了三个名称区域,1,名称为部门,包含销售部,品质部;2,销售部,包含销售部人员;3品质部,包含品质部人员
  5. 开始引用,选中B列,而后点击数据,有效性,而后允许值改为序列,在公式那里,输入=部门,因为部门下面,有品质部和销售部,所以我们再输入B列部门的时候,下拉菜单的序列,才显示的是销售部,品质部
  6. 选中C列,而后点击数据,有效性,序列,而后输入公式=indirect(B1)这里的B1,因为要进行变化的所以,不能用绝对引用,如果写作indirect(B$1),就是错误的,如果这样写,永远返回以B1为名称的值,就是部门

用法2:创建开始区域插入行列不受影响的数据区域

如图1,当我们计算总和的时候,用到了sum公式,当我们在中间插入一行,发现没有问题,很多亲们就认为会了,其实不然。

当我们在引用区域的首行,插入一行,而后我们就可以看到,原来引用的区域,是无法自动调节的,这时原来的sum(F2:F12),变成了sum(F3:F12),首行引用区域变了,就导致了总和的变化,不准确,那么再这种情况下,如何创建固定引用范围呢?

正确做法:将原来的公式,调整为=sum(indirect("F2"):F13)

这样在indirect("F2")的引用区域内,是不会自动变化的,不会因为插入,或是删除,而改变,就达到了固定数据区域的目的


用法3:一列转三列

如果需要转4列,就讲原来的3,改为4,而后将2,改为3,就可以了,这里不做解释,大家理解下

公式:=INDIRECT("B"&3*ROW(C6)-2 COLUMN(C6))&""


用法4:多工作表合并

用到的公式:=INDIRECT(B$1&"!B"&ROW())


用法5:多工作表合并汇总求和

用到的公式:=SUMPRODUCT(SUMIF(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a"),B2,INDIRECT({"销售部","品质部","采购部","公关部"}&"!B:B")))

这个公式,是有难度的,是indirect函数和sum的配合,(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a"),这部分,其实就是为了返回每个工作表的A列,而后和关键词B2对比,求出每个工作表B列对应的值的汇总,刚开始的亲们,理解不了,只要知道有这种用法,知道用就行了

评论 转发,让更多的人学会

今天就做到这里,希望对在办公室工作的亲们,有所帮助,如有疑问,大家一起讨论!

查看全文
大家还看了
也许喜欢
更多游戏

Copyright © 2024 妖气游戏网 www.17u1u.com All Rights Reserved