Comment utiliser les fonctions ChooseCols et ChOOSEROWS dans Google Sheets pour extraire les données

Les fonctions ChooseCols et Chooserows dans Google Sheets sont idéales pour extraire rapidement des colonnes ou des lignes spécifiques de vos données sans créer de formules imbriquées confuses. De plus, comme ce sont des fonctions dynamiques, ils s'adaptent aux changements dans les données source.

Pour suivre lorsque vous lisez, créez votre propre copie du modèle Google Sheets. Après avoir cliqué sur le lien, connectez-vous à votre compte Google, puis cliquez sur « Faire une copie » pour ajouter une copie du fichier à votre Google Drive. Ensuite, suivez les onglets en bas de la fenêtre Google Sheets lorsque vous travaillez à travers les exemples ci-dessous.

Les syntaxes ChooseCols et Chooserows

Une fois que vous avez trouvé comment utiliser l'une de ces deux fonctions dans Google Sheets, vous comprenez bien comment l'autre fonctionne.

Ceci est la syntaxe des choosecols:

=CHOOSECOLS(a,b,c)

Et voici la syntaxe pour Chooserows:

=CHOOSEROWS(a,b,c)

Voici ce que représente chaque argument:

Si l'un des numéros d'index est zéro ou dépasse le nombre de colonnes ou de lignes dans le tableau, Google Sheets renverra le #Value! erreur.

Si un numéro d'index tire une colonne ou une ligne du centre du tableau, l'insertion de colonnes ou de lignes supplémentaires modifiera quelle colonne ou ligne est extraite.

Exemple 1: Extraction des premières et dernières colonnes ou lignes

Depuis que j'ai appris comment fonctionnent les fonctions ChooseCols et Chooserows, je les ai utilisées sans fin pour extraire les première et dernière colonnes ou lignes de mes données. Il s'agit d'une astuce particulièrement pratique si la première colonne ou ligne est un en-tête, et la dernière colonne ou ligne contient des totaux.

Extraction des premières et dernières colonnes

Dans cet exemple, imaginons que vous êtes un administrateur d'entreprise et que vous souhaitez créer un rapport qui résume le nombre de produits vendus par chaque magasin au cours des deux premiers trimestres de l'année.

Pour ce faire, dans une cellule vierge, type:

=CHOOSECOLS(Sales(#ALL),1,-1)

où

• Choosecols est la fonction que vous devez utiliser pour extraire les données des colonnes de boutique et de TOT,
• Ventes est le nom de la table contenant le tableau, et (#TOUS) Dit Google Sheets que vous voulez que la ligne d'en-tête soit incluse dans le résultat,
• 1 indique que vous souhaitez extraire la première colonne (dans ce cas, la colonne de la boutique) et
• -1 Indique que vous souhaitez extraire la dernière colonne (dans ce cas, la colonne TOT).

Plutôt que de taper le tableau manuellement en argument unutilisez votre souris pour sélectionner les cellules que vous souhaitez référencer.

Voici le résultat lorsque vous appuyez sur Entrée:

Remarquez comment, même si vous n'avez tapé la formule que dans la cellule J1, le résultat a débordé de cette cellule à la cellule K12 pour accueillir le nombre de colonnes et de lignes dans le résultat.

De plus, parce que les données source se trouvent dans un tableau structuré, si les données changent, le résultat de la fonction ChooseCols. Cela est également vrai si d'autres colonnes ou lignes sont ajoutées à l'ensemble de données.

Si vous utilisez des références de cellules directes dans l'argument un Plutôt qu'une référence basée sur une table, la formule ne ramassera pas de nouvelles données à moins que vous ne modifiez manuellement les références cellulaires. Que vous utilisiez Google Sheets ou Microsoft Excel, je recommanderais toujours d'utiliser des tables avant les cellules non structurées, car ils offrent de meilleurs outils et l'adaptabilité.

Maintenant, vous pouvez dupliquer les données de résumé sur une autre feuille de calcul Google Sheets (comme un onglet de tableau de bord) ou un document Google Docs.

Extraction des premières et dernières lignes

De même, tirer les premières et dernières lignes est pratique pour générer des résumés horizontaux de vos données.

En utilisant le même exemple qu'auparavant, y compris la boutique ajoutée K, disons que vous souhaitez maintenant résumer le nombre total de produits vendus chaque mois.

Dans une cellule vierge, Type:

=chooserows(Sales(#ALL),1,-1)

et appuyez sur Entrée. Comme dans le dernier exemple, l'ajout (#TOUS) Après le nom de la table indique à Google Sheets que vous voulez que la ligne d'en-tête soit comptée comme la ligne 1 dans la recherche d'index.

Exemple 2: Extraction des colonnes de plus d'une plage

Dans l'exemple 1, nous avons extrait des colonnes et des lignes spécifiques des gammes individuelles. Cependant, vous pouvez combiner des choosecols avec la fonction VSTACK pour combiner les extractions à partir de plusieurs plages en même temps.

Ici, vous prévoyez de générer une liste des scores totaux de chaque équipe de la ligue 1 (rouge) et de la ligue 2 (vert).

La première chose à noter est que vous ne voulez pas extraire les en-têtes de colonne, car vous souhaitez empiler les données de chaque table, l'une directement sur l'autre. Si vous incluez les en-têtes de colonne, ils apparaîtront en haut des deux ensembles de données dans le résultat, supprimant ainsi le sentiment que vous regardez un ensemble de données combiné.

En conséquence, dans les cellules J1 et K1, tapez manuellement les en-têtes de colonne.

Ensuite, dans la cellule J2, type:

=CHOOSECOLS(VSTACK(League1,League2),1,-1)

où

• Choosecols dit à Google Sheets que vous souhaitez extraire certaines colonnes,
• Vstack combine le résultat verticalement (d'où le « V » au nom de la fonction),
• Ligue1, ligue2 Les tableaux qui contiennent les colonnes, et (#all) ne sont pas inclus dans la formule indique aux feuilles Google de ne pas inclure les lignes d'en-tête,
• 1 extrait la première colonne (équipe) de chaque tableau, et
• -1 extrait la dernière colonne (tot) de chaque tableau.

Lorsque vous appuyez sur Entrée, voici le résultat que vous obtenez:

Vous pouvez aller plus loin et nid toute la formule à l'intérieur de la fonction de tri, de sorte que le tableau récapitulatif affiche les équipes dans l'ordre de leurs scores globaux.

Pour ce faire, double-cliquez sur la cellule où vous avez déjà tapé la combinaison ChooseCols-Vstack, et ajoutez les arguments de tri suivants:

=SORT(CHOOSECOLS(VSTACK(League1,League2),1,-1),2,FALSE)

où

• TRIER est la fonction qui trie une plage basée sur une colonne spécifiée,
• 2 dit à Google Sheets de trier la gamme par la deuxième colonne, et
• FAUX indique que les données doivent être triées par ordre décroissant.

Impressionnant, si des valeurs dans le changement de jeu de données d'origine, le tableau de résumé s'ajuste en conséquence. Ici, l'évolution du score du match 6 de l'équipe H de 51 à 89 les favorise de la neuvième position à la quatrième position.

Exemple 3: Rendre dynamique les fonctions ChooseCols et ChooseRows

Jusqu'à présent, je vous ai montré comment utiliser ChooseCols et ChooseRows pour extraire les première et dernière colonnes et lignes d'un tableau dans Google Sheets. Cependant, dans cet exemple final, je vais vous montrer comment extraire d'autres colonnes ou lignes dynamiquement en faisant référence aux cellules – plutôt que d'utiliser des nombres d'index codés en dur – dans les arguments.

Je vais vous montrer comment utiliser ChooseCols dynamiquement, bien que vous puissiez utiliser le même principe avec la fonction ChooseRows.

Cette table Google Sheets montre cinq scores des équipes sur six matchs, ainsi que le total combiné pour chaque jeu, et votre objectif est de produire un résumé en fonction du numéro de jeu que vous spécifiez dans la cellule B9. Tout d'abord, tapez un numéro de jeu dans cette cellule afin que vous ayez quelque chose à travailler lorsque vous générez votre formule ChooseCols.

Ensuite, dans la cellule A11, type:

=CHOOSECOLS(TeamScores,1,B9+1)

où

• Choosecols dit à Google Sheets que vous allez extraire certaines colonnes,
• Coéquipiers est le nom du tableau contenant les données que vous souhaitez extraire,
• 1 est le numéro d'index pour la colonne d'équipe, et
• B9 + 1 Indique Google Sheets que le deuxième argument d'index est calculé en ajoutant 1 à la valeur dans la cellule B9. La raison pour laquelle vous devez inclure +1 Voici parce que les numéros de jeu commencent dans la deuxième colonne de la ligne 1, pas le premier. En conséquence, taper 4 dans la cellule B9 extrait les données de la cinquième colonne du tableau, qui est le jeu 4.

Voici ce que vous obtenez lorsque vous appuyez sur Entrée:

Maintenant, saisissez un numéro de jeu différent dans la cellule B9 pour voir la mise à jour du résultat en conséquence.

Conseil de pro: ajouter une règle de validation des données

Bien que la saisie du numéro de jeu dans la cellule B9 fonctionne exactement comme prévu pour rendre la fonction de fonction ChooseCols, si un numéro non valide est entré, vous serez brusquement rencontré un #Value! erreur. Ainsi, pour éviter que cela ne se produise, vous pouvez utiliser la validation des données pour contrôler les paramètres d'entrée cellulaire.

Pour ce faire, sélectionnez la cellule B9, et dans l'onglet Données, cliquez sur «Validation des données».

Ensuite, dans le volet des règles de validation des données sur le côté droit, cliquez sur « Ajouter la règle ».

Ensuite, dans le champ des critères, sélectionnez « Dropdown » et saisissez la première entrée possible pour cette cellule dans le champ de texte. Ensuite, cliquez sur «Ajouter un autre élément» et répétez ce processus jusqu'à ce que toutes les entrées soient indiquées. Lorsque toutes les options sont répertoriées, cliquez sur « Terminé ».

Vous pouvez également choisir « Dutdown à partir d'une gamme » dans le champ Critères, où vous pouvez sélectionner des cellules qui contiennent les entrées valides. Cependant, cela ne fonctionne pas toujours comme prévu avec les en-têtes de colonne, même si vous nommez la gamme. Au lieu de cela, configurez une feuille de calcul distincte où vous pouvez conserver toutes vos plages de validation des données.

Maintenant, retournez à la cellule B9, cliquez sur la flèche déroulante et sélectionnez un autre numéro de jeu pour voir la mise à jour du résultat en conséquence.

---

Les fonctions ChooseCols et Chooserows dans Google Sheets produisent des tableaux dynamiques, ce qui signifie que le résultat se déverse de la cellule où vous avez entré la formule. En conséquence, vous devez vous assurer qu'il y a suffisamment de place dans votre tableur avant d'entrer votre formule. Sinon, vous verrez un #ref! erreur.