Excel/Calc INDIRECT+ADDRESS+ROW+COLUMN 清洁替代品

Excel/Calc INDIRECT+ADDRESS+ROW+COLUMN 清洁替代品

我不是 Excel/Calc 的常规用户,但我目前正在使用 LibreOffice Calc 来记录一些资金的进出。下面两个函数按预期运行良好,但我仍然想知道一个更简洁的替代方案;尤其是第一个。

这计算出每​​日收入,

=INDIRECT(地址(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(地址(ROW()-1,COLUMN()-2))),INDIRECT(地址(ROW()-1,COLUMN()-2)),0)-INDIRECT(地址(ROW(),COLUMN()-1))

计算净收入,

=SUM(INDIRECT(ADDRESS(2,COLUMN()-1)&":"&ADDRESS(ROW(),COLUMN()-1)))

答案1

使用标准单元格引用,您可以更轻松地实现相同的目的。您的第一个公式仅使用相对引用。假设公式输入到单元格 D3 中,则可以写成:

=B3-IF(ISNUMBER(B2),B2,0)-C3

第二个公式同时使用了绝对引用和相对引用。这次假设我们在单元格 E3 中,它可以写成:

=SUM(D$2:D3)

注意中的 $ D$2。这是绝对引用,这意味着当您将公式向下拖动时,上限将保持不变,而下限则向下扩展。

答案2

正如 Kyle 所指出的,人们通常只使用相对寻址,他的回答描述了如何做到这一点。您可以看到公式变得多么简单。

原始公式的解释

您在问题中包含的示例公式可能就是以这种方式完成的,以突出单元格地址之间的关系。只要结构不变,这种寻址方式就会侧重于单元格物理关系的逻辑。但请注意,如果电子表格的结构发生变化,一切都会崩溃,因为引用与原始结构相关联。

为了理解他们在做什么,ADDRESS 会构建单元格引用的文本描述。这可让您使用计算来确定引用应指向的位置。一个好的、简短的演示在这里. INDIRECT 将单元格引用的文本描述转换为实际引用。

因此,如果您想要一个更简洁的替代方案,最简洁的方法是使用实​​际的单元格引用,就像 Kyle 的答案一样。如果您确实希望将重点放在电子表格结构上,那么仍然有几种方法可以简化此过程。

R1C1寻址

有一种称为 R1C1 的寻址形式,它将地址表示为绝对或相对的行和列,以便可以直接计算和使用,而无需 ADDRESS、ROW 和 COLUMN 函数。

对于绝对地址,请在 R 和 C 标签后使用行号和列号。因此A3将是R3C1

对于相对寻址,请省略表示当前行或列的数字,或将相对数字放在方括号中。因此,同一行左侧两列的单元格将被引用为RC[-2]

在 Excel 和 Calc 中,都有一个选项可以将地址样式更改为 R1C1,这将适用于所有内容。但是,您可以在特定公式中使用它,而无需这样做,只需将其设为文本字符串并使用 INDIRECT(这需要将可选的寻址样式参数设置为零才能识别 R1C1 样式)。因此第一个公式:

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))), 
 INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

会成为:

=INDIRECT("RC[-2]",0)-IF(ISNUMBER(INDIRECT("R[-1]C[-2]",0)), 
 INDIRECT("R[-1]C[-2]",0),0)-INDIRECT("RC[-1]",0)

您可以看到,这是一种 ADDRESS/ROW/COLUMN 构造的简写。

抵消

但是,当您需要使相对关系显而易见时,执行此操作的典型方法比这更短。您可以使用 OFFSET 函数。引用单个单元格的简短形式是OFFSET(reference,rows,columns)。您指定起点,然后指定距离起点的行数和列数。

在您的示例中,公式确定当前单元格是什么,您可以在 OFFSET 中执行此操作,但通常没有必要。通常使用实际的单元格引用。就像 Kyle 的回答一样,如果您将公式复制到另一个单元格,通常会得到正确的翻译。OFFSET 是一种使位置关系明确的方法。因此,如果您的第一个公式在单元格中C5,它将如下所示:

=OFFSET(C5,0,-2)-IF(ISNUMBER(OFFSET(C5,-1,-2)), 
 OFFSET(C5,-1,-2),0)-OFFSET(C5,0,-1)

相关内容