Neste artigo, aprenderemos como indexar e classificar valores usando a coluna auxiliar do Excel.
Cenário:
Às vezes, trabalhando com dados desumanos. Precisamos obter a versão mais curta como dados necessários dos dados da tabela com base na coluna auxiliar. Aqui, primeiro entenderemos como obter a coluna auxiliar e, em seguida, obter os pequenos dados necessários classificados com base nos dados da tabela.
Como indexar valores no intervalo?
Para este artigo, seremos obrigados a usar a função CONT.SE. Agora faremos uma fórmula da função. Aqui, o texto e o número de valores mistos em um intervalo. Precisamos indexá-los em ordem crescente, primeiro numericamente e depois em ordem alfabética.
Fórmula genérica: -
| = CONT.SE (lista, "<=" & valor_f) + (CONTAGEM (lista) * ISTEXT (valor_f)) |
lista: lista de números e texto
f_value: primeiro valor do intervalo
Explicação:
- A função CONT.SE conta o número de valores na lista retornada
- A função COUNT obtém a contagem de números no intervalo.
- A função ISTEXT verifica se o valor na célula selecionada é texto ou não. Retorna TRUE para texto e FALSE para números.
Exemplo:
Tudo isso pode ser confuso de entender. Vamos entender como obter a coluna auxiliar usando a fórmula explicada. Aqui, a coluna auxiliar deve ser baseada nos valores de classificação em ordem (primeiros números e depois em ordem alfabética). Para isso, usaremos a fórmula para obter o índice ou coluna de classificação como coluna auxiliar para os dados de despesas mostrados abaixo.
Aqui para encontrar os valores no intervalo. Para fins de cálculo, usamos o intervalo nomeado para a matriz fixa D5: D12 como despesa.
Use a fórmula:
| = CONT.SE (despesa, "<=" & D5) + (CONTAGEM (despesa) * ISTEXT (D5)) |
A fórmula é semelhante à mostrada no instantâneo acima. O valor e a matriz são fornecidos como intervalo nomeado e referência de célula na fórmula.
Como você pode ver, o índice da primeira célula no intervalo é 5, o que significa que se colocarmos as despesas em ordem de classificação, Eletricidade será colocado na 5ª posição. Agora copie a fórmula em outras células usando a opção arrastar para baixo ou usando a tecla de atalho Ctrl + D como mostrado abaixo para obter o Índice ou classificação para o resto dos valores.
Como você pode ver, agora temos uma lista de índice ou classificação que é a forma de classificação das Despesas. Agora usaremos esta coluna Rank como coluna auxiliar para obter a versão mais curta da tabela com a mesma ordem na nova lista ou tabela.
Como classificar valores usando coluna auxiliar no Excel?
O número de classificação no Excel usando a coluna auxiliar deve ter números de índice e intervalo necessário para classificar. A coluna auxiliar pode ser qualquer pedido obrigatório. Por exemplo, se você precisa obter a lista na ordem aleatória exigida, basta colocar a classificação correspondente ao valor no índice ou coluna de classificação que funcionará como coluna auxiliar.
- Função INDEX
- Função MATCH
- Função ROWS
Agora faremos uma fórmula usando as funções acima. A função MATCH retornará o índice da correspondência mais baixa do intervalo. A função INDEX leva o índice da linha como um argumento e retorna seus resultados necessários correspondentes. Esta função encontra o
Fórmula Genérica:
| = INDEX (return_array, MATCH (ROWS (relative_reference), Index, 0)) |
return_array: array para retornar o valor de
Referência relativa: gera uma ordem crescente na fórmula. Pode ser usado com qualquer array
Índice: matriz de índice da tabela
0: correspondência exata
Explicação:
- ROWS (referência_relativa) retorna um valor conforme o comprimento da fórmula estendida. Se aplicado na primeira célula, então será uma, depois na segunda e continuará até que se estenda.
- A função MATCH combina o índice com o valor da linha para obtê-los em ordem crescente usando a função ROWS.
- A função INDEX retorna o índice combinado com o valor correspondente.
Exemplo:
Tudo isso pode ser confuso de entender. Então, vamos entender essa fórmula executando-a no exemplo mostrado abaixo. Aqui, classificamos os dados em ordem aleatória para obter os três primeiros valores com base na coluna Índice. Use a fórmula para obter o primeiro valor da tabela curta necessária.
Use a fórmula:
| = ÍNDICE (B5: B13, CORRESPONDÊNCIA (LINHAS (D5: D5), D5: D13,0)) |
A fórmula é semelhante à mostrada no instantâneo acima. A matriz de valor é fornecida como intervalo nomeado e referência de célula.
Como você pode ver, o primeiro valor é o número do intervalo que acaba sendo "Leite", o índice correspondente é 1. Agora copie a fórmula em outras células usando a opção de arrastar para baixo ou usando a tecla de atalho Ctrl + D como mostrado abaixo para obter o resto dos valores.
Como você pode ver, os valores são indexados em ordem crescente. Obtivemos todos os valores, pois isso também prova que a fórmula funciona bem. Podemos extrair os valores numéricos usando a fórmula da mesma fórmula.
Como você pode ver, temos os valores mais curtos e classificados da tabela usando a coluna auxiliar que é indexada de acordo.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula só funciona com números e texto.
- A fórmula ignora o valor do texto ao comparar números e ignora os números quando corresponde aos valores do texto.
- A coluna auxiliar pode ser qualquer tipo de ordem de coluna necessária.
Espero que este artigo sobre como indexar e classificar valores com coluna auxiliar no Excel seja explicativo. Explore mais artigos sobre fórmulas de pesquisa no Excel aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.