Comment utiliser la fonction de dépôt dans Microsoft Excel

L'une des fonctions de recherche et de référence les plus sous-utilisées dans Microsoft Excel est la fonction de dépôt. Cette fonction puissante mais simple vous permet de supprimer un nombre spécifié de lignes ou de colonnes du début ou de la fin d'un tableau sans modifier l'ensemble de données d'origine.

OS

Windows, macOS, iPhone, iPad, Android

Essai gratuit

1 mois

Microsoft 365 comprend l'accès aux applications Office comme Word, Excel et PowerPoint sur jusqu'à cinq appareils, 1 To de stockage OneDrive, et plus encore.

La syntaxe de drop

La fonction de chute d'Excel a trois arguments:

=DROP(a,b,c)

où

• un est le tableau à partir duquel vous souhaitez supprimer un certain nombre de lignes ou de colonnes,
• b est le nombre de lignes à tomber du tableau, et
• c est le nombre de colonnes à passer du tableau.

Lorsque vous utilisez cette fonction, gardez à l'esprit les critères et les caractéristiques suivantes:

• Argument un est obligatoire, et au moins l'un des arguments b et c Doit être indiqué pour éviter un message d'erreur.

• Pour les arguments b et cun nombre positif laisse tomber les lignes du haut ou des colonnes de la gauche, et un nombre négatif laisse tomber les lignes du bas ou des colonnes en bas.

• Si vous saisissez un nombre pour les arguments b ou c qui dépasse le nombre de lignes ou de colonnes dans le tableau, vous verrez le calc! erreur.

• Drop est une fonction de tableau dynamique. En d'autres termes, le résultat s'étend aux cellules adjacentes à l'endroit où la formule est entrée – un comportement appelé effusion. Étant donné que les tableaux dynamiques ne peuvent pas se répandre dans les colonnes de table, la formule de goutte doit être entrée dans une cellule ordinaire.

Exemples: la fonction de dépôt utilisée

Dans tous les exemples ci-dessous, j'utiliserai la fonction de dépôt pour manipuler des copies de la table Excel suivante, qui contient des identifiants des élèves dans la colonne A, leurs sexes dans la colonne B, leurs classes dans la colonne C et leurs scores de test dans la colonne D.

Exercice 1: déposez les lignes du haut d'un tableau

Imaginons que vous souhaitiez créer un nouvel ensemble de données comprenant tous les étudiants qui ne sont pas dans le top 10. Tout d'abord, réorganisez les étudiants en fonction de leurs scores en cliquant sur le bouton Filtre dans l'en-tête de la colonne et en sélectionnant « Trier le plus grand au plus petit ».

Une façon de dupliquer l'ensemble de données avec les 10 premières lignes supprimées serait de l'argument du code dur b:

=DROP(T_Stud,10)

où T_stud Le tableau contenant le tableau à partir duquel vous souhaitez laisser tomber certaines lignes, et 10 est le nombre de lignes à tomber. Parce qu'il s'agit d'un nombre positif, les lignes sont supprimées en haut de l'ensemble de données. Argument c est omis parce que vous voulez que toutes les colonnes soient transférées au nouveau tableau.

L'un des problèmes avec les valeurs du codage dur dans les formules Excel est qu'il les rend inflexibles. C'est-à-dire que si, au lieu de cela, vous vouliez montrer à tous les étudiants et non dans le top 15, vous devrez modifier la formule.

Au lieu de cela, vous pouvez taper la valeur dans une cellule et faire référence à ceci dans la formule:

=DROP(T_Stud,H1)

Maintenant, si vous devez modifier le nombre de lignes supprimées du résultat, vous pouvez simplement taper une nouvelle valeur en cellule H1.

Exercice 2: déposez les lignes du fond d'un tableau

Le principe pour laisser tomber les lignes du bas du tableau est le même que la suppression des lignes du haut, à l'exception d'un minuscule ajustement à la formule. Plus précisément, l'argument b doit être un nombre négatif.

Cette fois, disons que vous voulez montrer à tous les élèves pas dans les 10 derniers. Pour ce faire, après avoir trié les données dans le tableau appelé T_stud2 en ordre décroissant par la colonne de score et en tapant 10 dans la cellule H1, type:

=DROP(T_Stud2,-H1)

où le signe moins avant la référence de la cellule tourne le nombre négatif.

Exercice 3: Colonnes Drop de la gauche d'un tableau

Maintenant, imaginez que vous créez un rapport de scores d'étudiants, mais vous souhaitez anonymiser les données. En d'autres termes, vous souhaitez dupliquer l'ensemble de données dans T_stud3mais avec la première colonne supprimée. Pour ce faire, tapez:

=DROP(T_Stud3,,1)

Remarquez comment la formule saute sur l'argument b et spécifie seulement un nombre pour l'argument c.

Exercice 4: Colonnes Drop à droite d'un tableau

Cette fois, votre objectif est de produire une liste d'étudiants, leur sexe et leur classe de la table T_stud4sans inclure leur score. Pour y parvenir, type:

=DROP(T_Stud4,,-1)

N'oubliez pas que le signe moins indique à Excel de supprimer les colonnes à droite des données. Cela signifie que si vous ajoutez une autre colonne de données à droite de la colonne de score, la nouvelle colonne serait abandonnée et le résultat rendrait quatre colonnes dans l'ensemble.

Exercice 5: Drop lignes et colonnes en même temps

Jusqu'à présent, nous avons laissé tomber des lignes ou des colonnes. Cependant, vous pouvez utiliser tous les arguments de la fonction en même temps pour déposer les deux.

Disons que vous souhaitez produire une liste des identifiants d'étudiants et leur sexe de la table T_stud5mais seulement ceux qui ne sont pas dans les 10 derniers. Après avoir trié la colonne de score en ordre décroissant et tapant 10 dans la cellule H1, type:

=DROP(T_Stud5,-H1,-2)

où -H1 tourne la valeur dans la cellule H1 (10) négative pour éliminer les 10 lignes inférieures, et -2 Supprime les deux colonnes les plus à droite.

Utilisation de Drop avec d'autres fonctions

Bien que la fonction de dépôt soit utile à elle seule, sa vraie valeur et sa puissance deviennent apparentes lorsqu'elles sont combinées avec d'autres fonctions de tableau dynamique.

Exercice 6: Triez et déposez les données

Dans tous les exemples ci-dessus, l'ordre du résultat de dépôt a été déterminé en triant les données source. Bien que cela fonctionne bien, vous rencontrerez des problèmes si vous avez besoin de trier les données source par une autre colonne à d'autres fins analytiques.

Pour vous assurer que le résultat de la formule DROP est toujours trié comme vous le souhaitez, nidine la fonction de tri.

Dans cet exemple, où la table est appelée T_stud6Supposons que vous souhaitiez produire un nouvel ensemble de données anonymisé d'étudiants qui ne sont pas dans les 10 derniers, triés par score. Pour ce faire, tapez:

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

où

• 4 dit à Excel de trier les données par la quatrième colonne (score),
• -1 dit à Excel de trier cette colonne dans l'ordre descendant (le plus grand au plus petit),
• -H1 dit à Excel de supprimer les 10 lignes inférieures, car la cellule H1 contient le numéro 10 et la formule contient un signe moins, et
• 1 dit à Excel de supprimer la première colonne.

Maintenant, même si vous re-sort les données source par la colonne de classe, le résultat reste trié par la colonne Score.

Exercice 7: Combinez et déposez les données

Dans cet exemple, chaque classe a sa propre table: T_blue pour la classe bleue, T_green pour la classe verte, et T_yellow pour la classe jaune.

Plutôt que d'analyser ces tables séparément, vous souhaitez extraire et regrouper les étudiants les moins scores dans toutes les classes. Plus précisément, vous souhaitez que le résultat affiche tous les étudiants qui ne sont pas dans le top 10, tout en triant les données. C'est là que la fonction Vstack est utile.

Taper:

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

où

• La fonction VStack ajoute les trois tables, l'une au-dessus de l'autre,

• 3 dit à Excel de trier le résultat par la troisième colonne (score),
• -1 dit à Excel de trier cette colonne par ordre décroissant, et
• G1 Indique à Excel de supprimer les 10 premières lignes dans les données annexées, car Cell G1 contient un nombre positif 10.

Exercice 8: déposez les lignes ou les colonnes à partir de données sélectionnées à la main

Vous savez déjà que la fonction de dépôt vous permet de supprimer les lignes ou les colonnes du bord d'un tableau. Mais que se passe-t-il si vous souhaitez retirer les lignes ou les colonnes des deux bords, puis couper le résultat plus loin?

Plus précisément, dans ce cas, vous souhaitez répertorier les classes des étudiants qui ne sont pas dans les 10 derniers de la table T_stud8et trier le résultat par la colonne Score, afin que vous puissiez visualiser la classe qui fonctionne le mieux.

Dans ce cas, vous pouvez utiliser la fonction ChooseCols. Taper:

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

où

• La partie de tri de la formule trie les données source par la quatrième colonne (4) par ordre décroissant (-1),

• La partie ChooseCols de la formule extrait la troisième colonne (3) de l'ensemble de données, et

• La partie de chute de la formule exclut les dix lignes inférieures, car la cellule I1 contient le numéro 10, et la référence cellulaire est précédée d'un symbole moins.

Lorsque la fonction de dépôt d'Excel vous permet de supprimer les lignes ou les colonnes de l'ensemble de données d'origine dans le résultat, la fonction de prise fait exactement le contraire – vous permet de conserver des nombres spécifiques de lignes ou de colonnes dans le résultat. Ils suivent tous deux la même syntaxe et sont disponibles dans les mêmes versions d'Excel. Donc, une fois que vous avez appris à en utiliser l'un, il est facile de s'adapter à l'autre.