Microsoft Excel有一些很酷的技巧,使用SUM和INDIRECT动态范围公式只是两种轻松操作数据的方法。
SUM - INDIRECT公式概述
在Excel公式中使用INDIRECT函数可以轻松更改公式中使用的单元格引用的范围,而无需编辑公式本身。
INDIRECT可以和许多接受单元格引用的函数一起使用,例如OFFSET和SUM函数。
在后一种情况下,使用INDIRECT作为SUM函数的参数可以创建SUM函数然后累加的单元格引用的动态范围。
INDIRECT通过间接通过中间位置引用单元格中的数据来完成此操作。
示例:SUM - INDIRECT用于总计动态值范围的公式
此示例基于上图中显示的数据。
使用以下教程步骤创建的SUM - INDIRECT 公式为:
= SUM(INDIRECT(“D”&E1&“:D”&E2))
在此公式中,嵌套的INDIRECT函数的参数包含对单元格E1和E2的引用。 这些单元格中的数字1和4与INDIRECT的其余参数结合在一起形成单元格D1和D4。
结果,由SUM函数总计的数字范围是包含在单元格D1至D4 范围内的数据,即50。
通过改变位于小区E1和E2中的号码; 但是,要汇总的范围可以轻松更改。
本例首先使用上面的公式来计算单元格D1:D4中的数据,然后将总计范围更改为D3:D6,而无需编辑单元格F1中的公式。
03年3月
输入公式 - 选项
输入公式的选项包括:
- 将上述公式直接输入到单元格F1中并按下键盘上的Enter键
- 使用SUM函数的对话框作为参数输入INDIRECT函数
Excel中的大多数函数都有一个对话框,它允许您在单独的行中输入每个函数的参数,而不必担心语法 。
在这种情况下,SUM函数的对话框可用于在一定程度上简化公式。 因为INDIRECT函数嵌套在SUM中,所以仍然必须手动输入INDIRECT函数及其参数。
以下步骤使用SUM对话框输入公式。
输入教程数据
单元 数据 D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- 将以下数据输入到单元格D1到E2中
开始SUM - INDIRECT公式 - 打开SUM函数对话框
- 点击单元格F1 - 这将显示此示例的结果
- 点击功能区菜单的公式选项卡
- 从功能区中选择Math&Trig打开功能下拉列表
- 点击列表中的SUM打开函数的对话框
02 03
输入INDIRECT功能 - 点击查看大图
需要输入INDIRECT公式作为SUM函数的参数。
在嵌套函数的情况下,Excel不允许打开第二个函数的对话框来输入它的参数。
因此,INDIRECT函数必须在SUM函数对话框的Number1行中手动输入。
- 在对话框中,单击Number1行
- 输入以下INDIRECT功能: INDIRECT(“D”&E1&“:D”&E2)
- 点击OK完成该功能并关闭对话框
- 数字50应出现在单元格F1中,因为这是位于单元格D1到D4中的数据的总和
- 当您单击单元格F1时,完整公式= SUM(INDIRECT(“D”&E1&“:D”&E2))出现在工作表上方的公式栏中
打破INDIRECT功能
为了使用INDIRECT在列D中创建动态范围,我们必须将INDIRECT函数参数中的字母D与单元格E1和E2中包含的数字结合起来。
这是通过以下来完成的:
- 和号( & )用于将文本数据(在本例中为字母D)与单元格引用(E1和E2) 连接或连接在一起,
- 此外,与单元格引用连接的文本数据必须用双引号( “” )包围,
- 最后,范围的结束点用冒号(:)分隔
因此,范围的起始点由字符“D”&E1定义 。
第二组字符: “:D”&E2将冒号与终点相结合。 这是因为冒号是文本字符,因此必须包含在引号内。
中间的第三个连字符用于将两部分连接成一个参数 :
“D”&E1 & “:D”&E203年03月
动态改变SUM函数的范围
这个公式的全部要点是,可以很容易地改变SUM函数总计的范围,而不必编辑函数的参数。
通过在公式中包含INDIRECT函数,更改单元格E1和E2中的数字将更改由SUM函数读取的单元格范围。
从上图中可以看出,这也导致公式的答案位于单元格F1中,因为它总计了新的数据范围。
- 点击单元格E1
- 输入数字3
- 按下键盘上的Enter键
- 点击单元格E2
- 输入数字6
- 按下键盘上的Enter键
- 单元格F1中的答案应该更改为90 - 这是单元格D3至D6中包含的总数
- 通过将单元格B1和B2的内容更改为1到6之间的任意数字来进一步测试公式
间接和#REF! 错误值
裁判! 如果INDIRECT函数的参数为错误值,将出现在单元格F1中:
- 不是有效的单元格引用
- 包含对不同工作簿的外部引用,并且该工作簿未打开
- 指的是工作表限制之外的单元格范围(行1,048,576或列XFD)