Como usar a função de gota no Microsoft Excel

Uma das funções de pesquisa e referência mais subutilizadas no Microsoft Excel é a função Drop. Essa função poderosa e simples permite remover um número especificado de linhas ou colunas do início ou final de uma matriz sem alterar o conjunto de dados original.

A sintaxe de gota

A função de queda do Excel tem três argumentos:

=DROP(a,b,c)

onde

• um é a matriz da qual você deseja lançar um certo número de linhas ou colunas,
• b é o número de linhas a cair da matriz e
• c é o número de colunas a cair da matriz.

Ao usar esta função, lembre -se dos seguintes critérios e características:

• Argumento um é obrigatório e pelo menos um dos argumentos b e c deve ser declarado para evitar uma mensagem de erro.
• Para argumentos b e cum número positivo cai linhas de cima ou colunas da esquerda, e um número negativo cai linhas da parte inferior ou colunas da parte inferior.
• Se você inserir um número para argumentos b ou c Isso excede o número de linhas ou colunas na matriz, você verá o Calc! erro.
• Drop é uma função de matriz dinâmica. Em outras palavras, o resultado se estende às células adjacentes a onde a fórmula é inserida – um comportamento conhecido como derramamento. Como as matrizes dinâmicas não podem derramar em colunas de tabela, a fórmula de queda deve ser inserida em uma célula comum.

Exemplos: a função de queda em uso

Em todos os exemplos abaixo, usarei a função Drop para manipular cópias da seguinte tabela do Excel, que contém os IDs dos alunos na coluna A, seus sexos na coluna B, suas classes na coluna C e suas pontuações nos testes na coluna D.

Exercício 1: linhas de soltar do topo de uma matriz

Vamos imaginar que você deseja criar um novo conjunto de dados, compreendendo todos os alunos que não estão entre os 10 primeiros. Primeiro, reordenar os alunos de acordo com suas pontuações clicando no botão Filtro no cabeçalho da coluna e selecionando “classificar a maior para a menor”.

Uma maneira de duplicar o conjunto de dados com as 10 principais linhas removidas seria o argumento do código rígido b:

=DROP(T_Stud,10)

onde T_stud é a tabela que contém a matriz da qual você deseja soltar algumas linhas e 10 é o número de linhas a cair. Como esse é um número positivo, as linhas são retiradas da parte superior do conjunto de dados. Argumento c é omitido porque você deseja que todas as colunas sejam transferidas para a nova matriz.

Um dos problemas com valores de codificação rígida nas fórmulas do Excel é que isso as torna inflexíveis. Ou seja, se, em vez disso, você quisesse mostrar a todos os alunos que não estão entre os 15 primeiros, precisaria editar a fórmula.

Em vez disso, você pode digitar o valor em uma célula e fazer referência a isso na fórmula:

=DROP(T_Stud,H1)

Agora, se você precisar alterar o número de linhas removidas do resultado, poderá simplesmente digitar um novo valor na célula H1.

Exercício 2: Solte as linhas do fundo de uma matriz

O princípio para soltar linhas do fundo da matriz é o mesmo que soltar linhas do topo, exceto por um pequeno ajuste na fórmula. Especificamente, argumento b precisa ser um número negativo.

Desta vez, digamos que você queira mostrar a todos os alunos que não estão nos 10 inferiores. Para fazer isso, depois de classificar os dados na tabela chamada T_stud2 em ordem decrescente pela coluna de pontuação e digitação 10 na célula H1, tipo:

=DROP(T_Stud2,-H1)

onde o sinal de menos antes da referência da célula gira o número negativo.

Exercício 3: colunas de soltar da esquerda de uma matriz

Agora, imagine que você está criando um relatório das pontuações dos alunos, mas deseja anonimizar os dados. Em outras palavras, você deseja duplicar o conjunto de dados em T_stud3mas com a primeira coluna removida. Para fazer isso, digite:

=DROP(T_Stud3,,1)

Observe como a fórmula pula o argumento b e apenas especifica um número para argumento c.

Exercício 4: colunas de soltar da direita de uma matriz

Desta vez, seu objetivo é produzir uma lista de alunos, seu sexo e a aula da mesa T_stud4sem incluir sua pontuação. Para conseguir isso, tipo:

=DROP(T_Stud4,,-1)

Lembre -se, o sinal de menos diz ao Excel para soltar colunas da direita dos dados. Isso significa que, se você adicionar outra coluna de dados à direita da coluna de pontuação, a nova coluna seria descartada e o resultado retornaria quatro colunas em geral.

Exercício 5: linhas e colunas de soltar ao mesmo tempo

Até agora, lançamos linhas ou colunas. No entanto, você pode usar todos os argumentos da função ao mesmo tempo para abandonar os dois.

Digamos que você queira produzir uma lista de identificações de estudantes e seu sexo da mesa T_stud5mas apenas aqueles que não estão no fundo 10. Depois de classificar a coluna de pontuação em ordem decrescente e digitar 10 na célula H1, tipo:

=DROP(T_Stud5,-H1,-2)

onde -H1 gira o valor na célula H1 (10) negativo para remover as 10 linhas inferiores e -2 Remove as duas colunas mais à direita.

Usando queda com outras funções

Embora a função de queda seja útil por si só, seu verdadeiro valor e poder se tornam aparentes quando combinados com outras funções de matriz dinâmica.

Exercício 6: classificar e soltar dados

Em todos os exemplos acima, a ordem do resultado da queda foi determinada pela classificação dos dados de origem. Embora isso funcione bem, você terá problemas se precisar classificar os dados de origem por outra coluna para outros fins analíticos.

Para garantir que o resultado da fórmula de queda seja sempre classificado como desejado, aninhe a função de classificação.

Neste exemplo, onde a tabela é chamada T_stud6suponha que você queira produzir um novo conjunto de dados anonimizado dos alunos que não esteja nos 10 inferiores, classificado por pontuação. Para fazer isso, digite:

=DROP(SORT(T_Stud6,4,-1),-H1,1)

onde

• 4 diz ao Excel para classificar os dados da quarta coluna (pontuação),
• -1 diz ao Excel para classificar esta coluna em ordem decrescente (maior à menor),
• -H1 diz ao Excel para soltar as 10 linhas inferiores, uma vez que a célula H1 contém o número 10 e a fórmula contém um sinal de menos, e
• 1 diz ao Excel para soltar a primeira coluna.

Agora, mesmo se você reorganizar os dados de origem pela coluna da classe, o resultado permanece classificado pela coluna de pontuação.

Exercício 7: Combine e soltar dados

Neste exemplo, cada classe tem sua própria tabela: T_blue para a classe azul, T_GREEN para a classe verde, e T_yellow para a classe amarela.

Em vez de analisar essas tabelas separadamente, você deseja extrair e agrupar os alunos com menor pontuação em todas as aulas. Especificamente, você deseja que o resultado exiba todos os alunos que não estão entre os 10 primeiros, além de classificar os dados. É aqui que a função Vstack é útil.

Tipo:

=DROP(SORT(VSTACK(T_Blue,T_Green,T_Yellow),3,-1),G1)

onde

• A função Vstack anexa as três tabelas, uma no topo da outra,
• 3 diz ao Excel para classificar o resultado da terceira coluna (pontuação),
• -1 diz ao Excel para classificar esta coluna em ordem decrescente e
• G1 diz ao Excel para soltar as 10 primeiras linhas nos dados anexados, pois a célula G1 contém um número positivo 10.

Exercício 8: LOW linhas ou colunas de dados escolhidos a dedo

Você já sabe que a função Drop permite remover linhas ou colunas da borda de uma matriz. Mas e se você quiser remover linhas ou colunas de ambas as bordas e depois prendê -lo ainda mais?

Especificamente, neste caso, você deseja listar as classes dos alunos que não estão nos 10 inferiores da tabela T_stud8e classifique o resultado pela coluna de pontuação, para que você possa visualizar qual classe está executando o melhor.

Nesse caso, você pode usar a função Choosecols. Tipo:

=DROP(CHOOSECOLS(SORT(T_Stud8,4,-1),3),-I1)

onde

• A parte de classificação da fórmula classifica os dados de origem pela quarta coluna (4) em ordem decrescente (-1),
• Os Choosecols parte da fórmula extraem a terceira coluna (3) do conjunto de dados e
• A parte de queda da fórmula exclui as dez linhas inferiores, uma vez que a célula I1 contém o número 10 e a referência da célula é precedida por um símbolo menos.

Onde a função de queda do Excel permite remover linhas ou colunas do conjunto de dados original no resultado, a função Take faz exatamente o oposto – isso permite manter números específicos de linhas ou colunas no resultado. Ambos seguem a mesma sintaxe e estão disponíveis nas mesmas versões do Excel. Então, depois de aprender a usar um, é fácil se adaptar ao outro.