Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função Excel ADDRESS no Excel para retornar um endereço de célula como texto.
Visão geral da função ADDRESS
A função ADDRESS retorna um endereço de célula como texto.
Para usar a função ADDRESS de planilha do Excel, selecione uma célula e digite:
(Observe como as entradas da fórmula aparecem)
Sintaxe da função ADDRESS e entradas:
| 1 | = ENDEREÇO (núm_linha, núm_coluna, núm_abs, C1, texto_folha) |
row_num - O número da linha da referência. Exemplo: 5 para a linha 5.
col_num - O número da coluna para a referência. Exemplo: 5 para a coluna E. Você não pode inserir “E” para a coluna E
abs_num - [opcional] Um número que representa se a referência deve ter referências de linha / coluna absolutas ou relativas. 1 para absoluto. 2 para linha absoluta / coluna relativa. 3 para linha relativa / coluna absoluta. 4 para parente.
a1 - [opcional]. Um número que indica se deve ser usado o formato de referência de célula padrão (A1) ou o formato R1C1. 1 / TRUE para padrão (padrão). 0 / FALSE para R1C1.
folha_texto - [opcional] O nome da planilha a ser usada. O padrão é a planilha atual.
Qual é a função ENDEREÇO?
A função ADDRESS é um pouco única. Na maioria das vezes, em uma planilha, informamos ao computador uma referência de célula e ele nos dá o valor dessa célula. Com o ADDRESS, vamos construir o nome de uma célula. Este endereço pode ser relativo ou absoluto, no estilo A1 ou R1C1, e pode ou não incluir o nome da folha. Na maior parte, essa função não tem muitos usos práticos, mas pode ser divertido de aprender. Quando é usado, geralmente é em combinação com outras funções, como veremos a seguir.
Exemplo Básico
Digamos que queremos construir uma referência para a célula em 4º coluna e 1st linha, também conhecida como célula D1. Podemos usar o layout ilustrado aqui:
Nossa fórmula em A3 é simplesmente
| 1 | = ENDEREÇO (B1, B2) |
Observe que, ao não declarar um argumento específico para relativo / absoluto, obtemos todos absolutos. Também temos o estilo padrão de referência do tipo A1 sem nome de folha.
Combinado com INDIRETO
Como dissemos, a função ADDRESS por si só não nos deu nada de útil. Poderíamos combiná-lo com a função INDIRETO para obter algumas funcionalidades. Considere este layout, onde temos uma lista de itens na coluna D. Se mantivermos a mesma fórmula de antes, vamos gerar uma referência para D1 como assim
| 12 | = ENDEREÇO (B1, B2)= $ D $ 1 |
Ao colocar a função de endereço dentro de uma função INDIRETA, seremos capazes de usar a referência de célula gerada e usá-la de forma prática. O INDIRETO pegará a referência de “$ D $ 1” e a utilizará para buscar o valor daquela célula.
| 123 | = INDIRETO (ENDEREÇO (B1, B2)= INDIRETO ($ D $ 1)= "Apple" |
Observação: Embora o acima forneça um bom exemplo de como tornar a função ADDRESS útil, não é uma boa fórmula para usar normalmente. Requer duas funções e, devido ao INDIRETO, será de natureza volátil. Uma alternativa melhor teria sido usar INDEX assim: = INDEX (1: 1048576, B1, B2)
Endereço de valor específico
Às vezes, quando você tem uma lista grande de itens, precisa saber onde um item foi encontrado na lista. Considere esta tabela de pontuações dos alunos. Seguimos em frente e calculamos os valores mínimo, mediano e máximo dessas pontuações nas células E2: G2.
Poderíamos filtrar nossa tabela para cada um desses itens para descobrir onde ele está (novamente, imagine que esta é uma lista muito maior), ou podemos até mesmo aplicar uma formatação condicional para que apareça visualmente para o usuário. Mas, se a lista tiver milhares de linhas, não queremos ter que rolar tanto para ver o que queremos. Em vez disso, usaremos os valores em E2: G2 para determinar o endereço das células que contêm nossos valores. Para fazer isso, usaremos a função MATCH com ADDRESS. Lembre-se de que MATCH retornará a posição relativa de um valor dentro de um intervalo.
Nossa fórmula no E3 então é:
| 1 | = ENDEREÇO (CORRESPONDÊNCIA (E2, $ B: $ B, 0), 2) |
Podemos copiar esta mesma fórmula para G3, e apenas a referência E2 mudará, pois é a única referência relativa. Olhando para trás no E3, a função MATCH foi capaz de encontrar o valor de 98 em 5º linha da coluna B. Nossa função ADDRESS então usou isso para construir o endereço completo de “$ B $ 5”.
Traduzir letras de coluna de números
Até este ponto, todos os nossos exemplos permitiram que a função ADDRESS retornasse uma referência absoluta. Este próximo exemplo retornará uma referência relativa. Nesta tabela, queremos inserir um número na coluna A e retornar o nome da letra da coluna correspondente.
Para cumprir nosso objetivo, faremos com que a função ADDRESS retorne uma referência na linha 1 em formato relativo e, em seguida, removeremos o “1” da string de texto para que apenas tenhamos a (s) letra (s) restante (s). Considere na linha 3 da nossa tabela, onde nossa entrada é 13. Nossa fórmula em B3 é
| 1 | = SUBSTITUIR (ENDEREÇO (1, A3, 4), "1", "") |
Observe que demos os 3rd argumento dentro da função ADDRESS, que controla a referência relativa vs. absoluta. A função ADDRESS produzirá “M1” e, em seguida, a função SUBSTITUTE remove o “1” para que fiquemos apenas com o “M”.
Encontre o endereço dos intervalos nomeados
No Excel, você pode nomear intervalo ou intervalos de células, permitindo simplesmente referir-se ao intervalo nomeado em vez da referência de célula.
A maioria dos intervalos nomeados são estáticos, o que significa que sempre se referem ao mesmo intervalo. No entanto, você também pode criar intervalos nomeados dinâmicos que mudam de tamanho com base em algumas fórmulas.
Com um intervalo nomeado dinâmico, você pode precisar saber o endereço exato para o qual seu intervalo nomeado está apontando. Podemos fazer isso com a função ADDRESS.
Neste exemplo, veremos como definir o endereço para nosso intervalo nomeado chamado “Notas”.
Vamos trazer de volta nossa mesa de antes:
Para obter o endereço de um intervalo, você precisa saber a célula superior esquerda e a célula inferior esquerda. A primeira parte é fácil de realizar com a ajuda das funções ROW e COLUMN. Nossa fórmula em E1 pode ser
| 1 | = ENDEREÇO (LINHA (notas), COLUNA (notas)) |
A função ROW retornará a linha da primeira célula em nosso intervalo (que será 1), e COLUMN fará o mesmo para a coluna (também 1).
Para obter a célula inferior direita, usaremos as funções ROWS e COLUMNS. Uma vez que podemos descobrir o ponto inicial do nosso intervalo, se calcularmos a altura do intervalo e subtrairmos o nosso ponto inicial, obteremos o ponto final correto. A fórmula para isso parece
| 1 | = ENDEREÇO (LINHAS (notas) -ROW (notas) +1, COLUNAS (notas) -COLUNA (notas) +1) |
Finalmente, para colocar tudo junto em uma única string, podemos simplesmente concatenar os valores com dois pontos no meio. A fórmula na E3 pode ser
| 1 | = E1 & ":" & E2 |
Observação: Embora pudéssemos determinar o endereço, do intervalo, nossa função ADDRESS determinou se listar as referências como relativas ou absolutas. Seus intervalos dinâmicos terão referências relativas que esta técnica não vai pegar.
2WL Observação: Essa técnica funciona apenas em um intervalo de nomes contínuo. Se você tivesse um intervalo nomeado que foi definido como algo como esta fórmula
| 1 | = A1: B2, A5: B6 |
então, a técnica acima resultaria em erros.
Notas Adicionais
Use a função ADDRESS para gerar um endereço de uma determinada linha e número de coluna. Importante: Você deve inserir a coluna número. Inserir a letra da coluna gerará um erro. Se necessário, você pode usar a Função de coluna para calcular o número da coluna para uma referência de célula.
Abs_num permite alternar as referências de células absolutas e relativas.
1,2,3,4 a1, $ a $ 2 … relativo / absoluto etc …
Em seguida, indique se deseja usar a1 ou R1C1. O modo a1 é o modo padrão em que as células são referenciadas por sua letra de coluna e número de linha (por exemplo, a4). O modo R1C1 é onde as células são referenciadas por seus números de linha e coluna (ex. R4C1). a1 é o modo padrão. Use-o, a menos que tenha um bom motivo para não o fazer.
No último argumento, você pode inserir um nome de planilha se a referência de célula estiver em outra planilha. Digite o nome da folha entre parênteses (ex “folha3”).
Voltar para a lista de todas as funções no Excel
MORADA função no Google Sheets
A função ENDEREÇO funciona exatamente da mesma forma no Planilhas Google e no Excel.
