主页 / coding / 问题 / 79011427
Accepted
Alexandros
Asked: 2024-09-22 18:32:16 +0800 CST

偏移另一个单元格的目标引用的最佳方法？

  • 772

假设我在单元格中有一个值$SheetX.A1（比如说数字 42）。

现在，我在另一个单元格中引用该值，比如$SheetY.M5通过将其内容设置为=$SheetX.A1（因此我在工作表 Y 的单元格 M5 中看到“42”）。

如何在另一个单元格中偏移 M5 所针对的引用？例如，我希望 M6 以 M5 所引用的相同位置为目标，偏移 6 行，换句话说，我希望 M6 显示$SheetX.A7(向下六行$SheetX.A1) 的值，因为这是 M5 所引用的。类似于“=OFFSET(TARGET(M5),6)”。

  • 我知道如果我可以使用 =INDIRECT("addr") 获取一个值
  • 我还知道我可以使用 =FORMULA(ref) 获取单元格的公式，因此=FORMULA(M5)将返回$SheetX.A1

我想我可以对其进行一些字符串操作以删除等号，然后修改尾随数字 1 以增加它，以某种方式生成我可以传递给的文本“$Sheet.A7” INDIRECT()？

怎样才能做到这一点？最好的方法是什么？

excel

4 个回答

  1. Best Answer

    你可以尝试这个公式：

    =LET(start,M5,offsetRows,5,
target,INDIRECT(MID(FORMULATEXT(start),2,LEN(FORMULATEXT(start))-1)),
IF(ISERROR(target),"n.a.",OFFSET(target,offsetRows,0)
))

    如果公式返回有效结果，它将偏移INDIRECT- 否则它将返回 na （例如，如果公式在 D1 中=$SheetX.A1+1）

    • 2

  2. 您可以使用以下公式：

    =INDIRECT("'" & MID(FORMULATEXT(M1), 2, FIND("!", FORMULATEXT(M1)) - 2) & "'!" & ADDRESS(ROW(M1) + 6, COLUMN(A1)))

    它首先提取工作表，然后构建对当前行下方 6 行单元格的引用。

    • 2

  3. 我认为INDEX()应该SEQUENCE()可以。尝试-

    =INDEX(Sheet1!A1:A100,SEQUENCE(5,1,1,6))

    在此处输入图片描述

    • 1

  4. 如果您有 Microsoft 365，则可以定义一个命名公式以方便使用：

    • INDIRECT通常人们不赞成使用，但在这里使用时要意识到这一点：)
    • 定义命名公式indirectOFFSET
    • ref可以是单个单元格或数组
    • 例如=indirectOFFSET(M5,6)或=indirectOFFSET(M5:M10,6,1)
    =LAMBDA(ref, [rows_], [cols_],
    LET(
        rows_, IF(ISOMITTED(rows_), 0, rows_),
        cols_, IF(ISOMITTED(cols_), 0, cols_),
        offset_one, LAMBDA(ref,
            LET(
                ref_addr, IF(
                    ISREF(ref),
                    LET(f, FORMULATEXT(ref), RIGHT(f, LEN(f) - 1)),
                    NA()
                ),
                OFFSET(INDIRECT(ref_addr), rows_, cols_)
            )
        ),
        MAP(ref, offset_one)
    )
)

    无需格式化，如在名称管理器中输入的：

    =LAMBDA(ref,[rows_],[cols_], LET(rows_, IF(ISOMITTED(rows_), 0, rows_), cols_, IF(ISOMITTED(cols_), 0, cols_), offset_one, LAMBDA(ref, LET(ref_addr, IF(ISREF(ref), LET(f, FORMULATEXT(ref), RIGHT(f, LEN(f) - 1)), NA()), OFFSET(INDIRECT(ref_addr), rows_, cols_))), MAP(ref, offset_one)))

    结果和命名公式

    • 1

相关问题