Baixe a apostila de exemplo
Este tutorial demonstrará como classificar dados dinamicamente por uma ordem de classificação personalizada no Excel e nas Planilhas Google.
Função SORTBY no Excel 365
A função SORTBY produz uma matriz dinâmica de dados classificados.
Neste exemplo, usamos a função SORTBY para pegar uma tabela de dados e produzir uma matriz dinâmica que a classifica por Equipe Depois de uma Lista de classificação personalizada:
| 1 | = SORTBY (B3: D8, CORRESPONDÊNCIA (B3: B8, F3: F5,0)) |
Observação: A função SORTBY é escrita apenas na célula H3. O resto da tabela será preenchido automaticamente.
A tabela classificada é dinâmica, o que significa que se algum dos Equipe ou Nome do jogador os valores mudam ou são registrados ou adicionados / excluídos, então a tabela de saída será automaticamente redimensionada e reclassificada.
Observação: A função SORTBY não está disponível no Excel 2022 ou anterior. Leia abaixo as opções de classificação disponíveis para versões anteriores do Excel.
Para explicar como essa fórmula funciona, vamos dividi-la em etapas:
Nossa fórmula final é:
| 1 | = SORTBY (B3: D8, CORRESPONDÊNCIA (B3: B8, F3: F5,0)) |
Primeiro, definimos a tabela de dados que precisa ser classificada. Este será B3: D8.
Em seguida, precisamos definir a ordem de classificação. Usaremos a função MATCH para encontrar a ordem em que cada Equipe nome aparece no Lista de classificação personalizada. Esta parte da fórmula é:
| 1 | = CORRESPONDÊNCIA (B3: B8, F3: F5,0) |
Adicionando os valores dos intervalos de células, obtemos:
| 1 | = CORRESPONDÊNCIA ({"Azul"; "Vermelho"; "Amarelo"; "Vermelho"; "Azul"; "Amarelo"}, {"Amarelo"; "Azul"; "Vermelho"}, 0) |
A função MATCH substitui todos Equipe nomes com a ordem em que aparecem no Lista de classificação personalizada, usando uma correspondência de texto exata:
| 1 | ={2; 3; 1; 3; 2; 1} |
Esta matriz de valores é então usada como a ordem de classificação pela função SORTBY principal:
| 1 | = SORTBY (B3: D8, {2; 3; 1; 3; 2; 1}) |
A tabela classificada é então produzida
Lista de classificação personalizada - Pré-Excel 365
Como a função SORTBY não está disponível para usuários do Excel antes da versão do Excel 365, agora mostraremos como realizar a mesma tarefa usando um processo mais manual.
A ferramenta de classificação embutida do Excel pode ser usada para classificar por um Lista de classificação personalizada selecionando o ‘Lista Personalizada…’ nas opções de ordem de classificação na ferramenta de classificação:
Um novo Lista Personalizada deve ser adicionado como uma lista separada por vírgulas. Neste exemplo, adicionamos: ‘Amarelo, Azul, Vermelho '
o Lista Personalizada é então mostrado na seleção de ordem de classificação
A tabela de dados é então classificada pelo Lista Personalizada valores
Observe que essa ordem de classificação personalizada é salva na pasta de trabalho e está disponível para ser usada novamente. Se você não deseja adicionar um Lista Personalizada para o Excel, então um método usando uma coluna auxiliar pode ser tentado.
Lista de classificação personalizada com coluna auxiliar - Pré-Excel 365
Neste próximo exemplo, usamos a função MATCH em uma coluna auxiliar para definir um Ordem de classificação personalizada referindo-se a um Lista de classificação personalizada:
| 1 | = CORRESPONDÊNCIA (B3, $ G $ 3: $ G $ 5,0) |
Observe o uso do símbolo $ ao fazer referência ao Lista de classificação personalizada. Leia nosso artigo sobre referências de células de bloqueio para saber mais sobre este assunto.
Uma vez que a coluna auxiliar foi adicionada, um processo de classificação manual pode ser executado usando o Assistente de classificação:
Isso então produz a tabela classificada de acordo com o Ordem de classificação personalizada:
Se alguma alteração for feita no Equipe ou Lista de classificação personalizada valores, o assistente de classificação precisará ser executado novamente para atualizar a ordem de classificação da tabela de dados.
Lista de classificação personalizada com fórmula no Planilhas Google
A função SORTBY não está disponível no Google Sheets, mas sua função SORT pode ser usada e é mais poderosa do que a função SORT no Excel 365. Ela nos permite usar listas de classificação personalizadas para classificar intervalos de dados dinamicamente.
Neste exemplo, usamos a função SORT do Google Sheets para obter uma tabela de dados e produzir uma matriz dinâmica que classifica Equipe valores por um Lista de classificação personalizada:
| 1 | = CLASSIFICAR (B3: D8, CORRESPONDÊNCIA (B3: B8, F3: F5,0), VERDADEIRO) |
A função SORT do Planilhas Google classifica os dados pelos números de coluna especificados ou ordens de classificação personalizadas nos dados de entrada e nos permite definir se a ordem de classificação é crescente ou não
