Início / coding / Perguntas / 79011427
Accepted
Alexandros
Asked: 2024-09-22 18:32:16 +0800 CST

Qual é a melhor maneira de compensar a referência de destino de outra célula?

  • 772

Suponha que eu tenha um valor na célula $SheetX.A1(digamos, número 42).

Agora, eu referencio esse valor em outra célula, digamos, $SheetY.M5definindo seu conteúdo como =$SheetX.A1(para que eu veja "42" na célula M5 da planilha Y).

Como posso deslocar a referência visada por M5 em outra célula? Por exemplo, eu gostaria que M6 visasse o mesmo local que M5 está referenciando, deslocado por 6 linhas, em outras palavras, eu gostaria que M6 mostrasse o valor de $SheetX.A7(seis linhas abaixo de $SheetX.A1) porque é isso que M5 está referenciando. Algo como "=OFFSET(TARGET(M5),6)".

  • Eu sei que se eu puder obter um valor com =INDIRECT("addr")
  • Eu também sei que posso obter a fórmula de uma célula com =FORMULA(ref), então =FORMULA(M5)retornará$SheetX.A1

Acho que eu poderia fazer alguma manipulação de string para remover o sinal de igual e então alterar o número 1 à direita para incrementá-lo, gerando de alguma forma o texto "$Sheet.A7" que eu posso passar para INDIRECT()?

Como se faz isso? Qual é a melhor maneira?

excel

4 respostas

  1. Best Answer

    Você pode tentar esta fórmula:

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

    Ele será compensado se INDIRECTa fórmula retornar um resultado válido - caso contrário, retornará na (por exemplo, se a fórmula estiver em D1 =$SheetX.A1+1)

    • 2

  2. Você pode usar esta fórmula:

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

    Primeiro, ele extrai a planilha e depois constrói a referência para a célula 6 linhas abaixo da linha atual.

    • 2

  3. Eu assumo INDEX()que SEQUENCE()deveria funcionar. Tentar-

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

    insira a descrição da imagem aqui

    • 1

  4. Se você tiver o Microsoft 365, poderá definir uma fórmula nomeada para facilitar o uso:

    • O uso de INDIRECTgeralmente é desaprovado, mas usá-lo aqui é uma atitude consciente :)
    • Definir uma fórmula nomeadaindirectOFFSET
    • refpode ser uma célula única ou uma matriz
    • Por exemplo =indirectOFFSET(M5,6)ou=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)
    )
)

    Sem formatação, conforme inserido no Gerenciador de Nomes:

    =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)))

    Resultado e fórmula nomeada

    • 1

relate perguntas