我不是 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)