Excel - Função Índice, Corresp e Fórmulatexto
Índice:
- Index, Match e Index Match
- A. Função INDEX
- B. INDEX ranges
- C. ÍNDICE com fórmulas SUM & MÉDIA
- D. INDEX MATCH with MAX
Originalmente, o Excel não foi projetado para ser um banco de dados real. Suas funções iniciais de banco de dados eram limitadas em quantidade e em qualidade. E como cada registro em um banco de dados do Excel é visível na tela ao mesmo tempo - o que significa tudo na memória de uma só vez - os bancos de dados do Excel precisavam ser muito pequenos: vários campos com poucos registros ou poucos campos com muitos registros; e cálculos mínimos.
VLOOKUP (vertical) e HLOOKUP (horizontal) eram as únicas funções disponíveis para consultar um banco de dados para obter informações específicas. Por exemplo, você poderia consultar para localizar e extrair todos os registros que continham vendas maiores de $ 1000, mas menos de $ 5000, mas apenas em arquivos simples (apenas uma matriz de banco de dados).
Tabelas dinâmicas foram desenvolvidas para que os usuários pudessem criar bancos de dados relacionais. mais fácil de consultar, usar menos memória e fornecer resultados mais precisos. Se, no entanto, você não tiver ou precisar de um banco de dados relacional, mas precisar de funções de banco de dados mais poderosas e confiáveis, experimente-as.
[Mais leitura: Seu novo PC precisa desses 15 programas gratuitos e excelentes]Index, Match e Index Match
No Excel, a função INDEX retorna um item de uma posição específica (em uma lista, tabela, banco de dados)
A função MATCH retorna a posição de um valor (em uma lista , tabela, banco de dados). E, as funções INDEX-MATCH usadas juntas tornam a extração de dados de uma tabela muito fácil
A sintaxe para a função INDEX é: INDEX (array, row_num, [column_num]). A matriz é o intervalo de células com as quais você está trabalhando. Row_num é, obviamente, o número da linha no intervalo que contém os dados que você está procurando. Column_num é o número da coluna no intervalo que contém os dados que você está procurando. A fórmula INDEX não reconhece letras de coluna, então você deve usar números (contando a partir da esquerda).
A sintaxe da função MATCH é: MATCH (lookup_value, lookup_array, [match_type]). O lookup_value é o número ou texto que você está procurando, que pode ser um valor, um valor lógico ou uma referência de célula. O lookup_array é o intervalo de células com o qual você está trabalhando. O match_type determina a função MATCH, ou seja, uma correspondência exata ou a correspondência mais próxima.
A. Função INDEX
Em nosso exemplo, o famoso Comodoro James Norrington tem uma planilha que acompanha todos os navios piratas do Caribe. A lista de Norrington é organizada pelas formações de combate dos navios, que correspondem às suas cartas náuticas da área. Quando ele vê um navio avançando, ele insere a fórmula do Índice em sua planilha eletrônica, para que ele possa identificar o navio e suas capacidades. Nessa primeira consulta, Norrington quer saber o tipo de nave que está avançando.
1. Selecione um local (célula ou intervalo de células) para suas consultas (ou seja, funções e resultados) e, em seguida, mova o cursor para essa célula. Por exemplo: qualquer célula na linha 18.
2. Digite a função INDEX (precedida pelo sinal de igual), mais um parêntese de abertura, então destaque (ou digite) o banco de dados / intervalo de tabela da seguinte forma: = INDEX (A2: I16
Nota: Se você quiser uma referência absoluta Nesse caso, significa codificar a fórmula de forma que quando / se for copiado, o intervalo não seja alterado), pressione F4 uma vez após cada referência de célula.Você também pode destacar o intervalo: Basta pressionar F4 uma vez depois de selecionar o intervalo completo , e os símbolos de referência absoluta completos são adicionados.
3. Em seguida, insira uma vírgula para separar os argumentos (ou seja, as partes da fórmula separadas) e digite o número da linha e uma vírgula, seguido do número da coluna , deve ser um número e não a letra da coluna usual) e o parêntese direito (ou simplesmente pressione Enter e deixe o Excel adicionar o parêntese final para você.) A fórmula completa é semelhante a: = ÍNDICE ($ A $ 2: $ I $ 16 , 15,2).
Observação: a numeração de linhas começa com o primeiro número no intervalo, não com o primeiro número na planilha. Embora o navio pirata Cavalleria esteja na linha 16 do Excel, na verdade é a linha 15 em nossa fórmula, porque nosso alcance começa na A2 e passa para a I16. Se A2 é linha 1, então A16 é linha 15).
4. Observe que o tipo de navio que Norrington estava procurando é um War Sloop.
Use a função de índice para localizar informações específicas em seu banco de dados.
B. INDEX ranges
Agora, podemos fazer muito mais com essa tabela de banco de dados. Você não precisa redefinir o intervalo toda vez que quiser saber alguma coisa. Para facilitar, definiremos o intervalo uma vez e depois nomearemos. Então podemos apenas colocar o nome do intervalo em nossas fórmulas.
1. Vá para A2 e destaque o intervalo de A2 a I16.
2. Na guia Fórmulas, selecione Definir nome no grupo Nomes Definidos.
3. Na caixa de diálogo pop-up, insira um nome para o seu intervalo na caixa de campo Nome.
4. Em seguida, insira o Escopo (onde o intervalo está localizado), que é a pasta de trabalho ou uma das planilhas na pasta de trabalho.
5. Digite um comentário, se necessário.
6. Por fim, verifique se o campo Refere-se a Para exibir o nome e o intervalo corretos e clique em OK .
7. Se você quiser verificar se o seu intervalo é, de fato, salvo no Excel, tente este pequeno teste: Pressione Ctrl + G (o comando GoTo). Selecione Navios na caixa de diálogo Ir para, clique em OK e o Excel realça novamente o intervalo A2: I16.
C. ÍNDICE com fórmulas SUM & MÉDIA
Norrington está avaliando as capacidades de batalha da frota. Primeiro, ele quer saber quantos canhões os piratas têm, o número médio de canhões por navio e o número total de tripulantes que tripulam todos esses navios piratas. Ele insere as seguintes fórmulas:
1. = SOMA (ÍNDICE (Navios, 8)) é igual a 334, o número total de canhões e
2. = MÉDIA (ÍNDICE (Navios, 8)) é igual a 22,27, ou aproximadamente 22,27 canhões por navio.
3. = SOMA (ÍNDICE (Navios, 7)) é igual a 2350, o número total de toda a tripulação em todos os navios
Por que há uma vírgula, espaço, vírgula entre Navios e o número 8, e o que esses números significam? Ships é o Range (seguido por uma vírgula), o argumento Row está em branco (ou um espaço) porque Norrington quer todas as linhas e o 8 representa a 8ª coluna (que é a coluna H, Canhões).
Alguns podem perguntar , por que não inserir as fórmulas SUM e / ou AVERAGE na parte inferior dessas colunas? Nesta minúscula planilha, sim, seria igualmente fácil. Mas se a planilha tiver 5000 linhas e 300 colunas, você desejará usar as fórmulas INDEX
03 INDEX usando SUM e AVERAGE.
Quando o intervalo for nomeado, Norrington poderá abrir uma planilha em branco em essa mesma pasta de trabalho e escreva suas consultas (fórmulas) na coluna B (que mostra os resultados em vez das fórmulas) com uma descrição que define essas consultas na coluna A. (Nota: A coluna C mostra as fórmulas reais que estão na coluna B).
Ele não precisa ver visualmente seu enorme banco de dados de 5000 registros ou esperar vários segundos enquanto as fórmulas são calculadas. Ele pode obter todas as informações necessárias de sua folha de consulta. Lembre-se, quanto maior a planilha, mais lenta ela funciona, especialmente se houver muitas fórmulas
04 Folha de informações / consulta do navio pirata do Comodoro James Norrington.
D. INDEX MATCH with MAX
Agora, Norrington quer saber quantos piratas estão no navio mais povoado, e qual é o navio? Ele usa a fórmula ÍNDICE com MAX para obter o maior número de piratas, mas ele também precisa saber qual navio os está transportando. Então ele usa a fórmula INDEX / MATCH with MAX para descobrir qual navio possui mais piratas a bordo.
1. = MAX (ÍNDICE (Navios, 7)) é igual a 300, o maior número de piratas em um dos navios
2. = ÍNDICE ($ A $ 2: $ A $ 16, MATCH (MAX (Navios), $ G $ 2: G $ 16, 0)) é igual ao Royal James, o navio com mais piratas a bordo
3. = ÍNDICE ($ F $ 2: $ F $ 16, MATCH (MAX (Navios), $ G $ 2: G $ 16, 0)) é igual a Stede Bonnet, Capitão do Royal James com uma tripulação pirata de 300
Use INDEX-MATCH e MAX para recuperar informações específicas do seu banco de dados.
A Apple poderia usar a tecnologia do FoundationDB para melhorar o desempenho de sua própria infra-estrutura. A aquisição relatada ocorre apenas alguns meses depois que o FoundationDB anunciou uma grande atualização de seu principal banco de dados NoSQL. O banco de dados foi reprojetado para lidar com milhões de gravações aleatórias por segundo e torná-lo mais adequado para sistemas IoT.
[Mais leitura: Seu novo PC precisa desses 15 programas gratuitos e excelentes]
Use INDEX e MATCH para consultas de banco de dados simples no Excel
Quanto maior sua planilha, mais funções INDEX e MATCH, que você pode combine com SUM, AVERAGE e MAX para refinar suas pesquisas.
