Início / computer / Perguntas / 1663824
Accepted
Lluser
Asked: 2021-07-22 02:05:38 +0800 CST

Excel: Obtenha o endereço do intervalo de uma única entrada (procurando uma fórmula compacta)

  • 772

Estou tentando obter um endereço de intervalo absoluto de entrada única. Algo assim.

Formula
=SOME.MAGIC(A1:B10)

Output (string)
"$A$1:$B$10"

Tentativa 1

Eu sou capaz de obter esse resultado, mas usando uma abordagem significativamente complexa com a seguinte fórmula (array).

{=ADDRESS(MIN(ROW(A1:B10)),MIN(COLUMN(A1:B10)),1,1)&":"&ADDRESS(MAX(ROW(A1:B10)),MAX(COLUMN(A1:B10)),1,1)}

Onde (como você pode ver) a string é A1:B10usada quatro vezes.

Tentativa 2

Minha outra tentativa foi usar function FORMULATEXT. O link para um intervalo existe apenas uma vez, mas é uma solução desagradável usar mais uma célula e funciona relativamente bem apenas para endereços relativos. Não funciona para intervalos nomeados.

C1=$A$1:$B$10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "$A$1:$B$10" ✓

But
C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "A1:B10" ✘

And
C1=Named_range
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1) //Returns string "Named_range" ✘

Tentativa 3

O próximo passo é a combinação da tentativa 1+2. Funciona, mas não estou feliz com isso.

C1=A1:B10
C2=MID(FORMULATEXT(C1),2,LEN(FORMULATEXT(C1))-1)
C3{=ADDRESS(MIN(ROW(INDIRECT(C2))),MIN(COLUMN(INDIRECT(C2))),1,1)&":"&ADDRESS(MAX(ROW(INDIRECT(C2))),MAX(COLUMN(INDIRECT(C2))),1,1)}

Existe uma maneira mais fácil (fórmula menos complexa) com a mesma saída?

microsoft-excel worksheet-function

3 respostas

  1. Considere a função definida pelo usuário:

    Option Explicit

Public Function adres(rng As Range) As String
    adres = rng.Address(True, True)
End Function

    insira a descrição da imagem aqui

    As Funções Definidas pelo Usuário (UDFs) são muito fáceis de instalar e usar:

    1. ALT-F11 abre a janela VBE
    2. ALT-I ALT-M abre um novo módulo
    3. cole o material e feche a janela do VBE

    Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, salve o arquivo como .xlsm em vez de .xlsx

    Para remover a UDF:

    1. abra a janela VBE como acima
    2. limpe o código
    3. feche a janela VBE

    Para usar a UDF de uma célula do Excel:

    =adres(A1:B10)

    Para saber mais sobre macros em geral, consulte:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    e

    http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

    e para detalhes sobre UDFs, veja:

    http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

    As macros devem estar habilitadas para que isso funcione!

    • 5
  2. Best Answer

    Esta seria outra forma:

    =CELL("address",A1:B10)&":"&CELL("address",INDEX(A1:B10,ROWS(A1:B10),COLUMNS(A1:B10)))

    E se você tiver o Office 365, use LET para fazer referência ao intervalo apenas uma vez

    =LET(ref, A1:B10, CELL("address",ref)&":"&CELL("address",INDEX(ref,ROWS(ref),COLUMNS(ref)))
    • 3

  3. Na verdade, sua tentativa nº 1 é exatamente a maneira de fazê-lo. Se você está procurando uma maneira de fazê-lo parecer mais com uma função (menos repetições da referência/uso único do endereço "argumento"), a melhor maneira é usar LAMBDA no Office 365. Com LAMDA, você pode fornecer argumentos para um intervalo nomeado, definindo MAGIC como

    =LAMBDA(ref, ADDRESS(MIN(ROW(ref)),MIN(COLUMN(ref)),1,1)&":"&ADDRESS(MAX(ROW(ref)),MAX(COLUMN(ref)),1,1)}

    em seguida, usando =MAGIC(A1:B10) na planilha.

    • 2

relate perguntas