Créez une Liste à partir de Coches à Cases à Cocher dans Google Sheets

GiGiKing·mars 9, 2024·0 commentaire

Avec l’aide de la fonction FILTER, nous pouvons créer une liste à partir de cases à cocher cochées dans une seule colonne dans Google Sheets. Lorsqu’il y a plusieurs colonnes avec des cases à cocher, nous pouvons copier la formule pour chaque colonne. Mais existe-t-il une solution alternative qui peut gérer toutes les cases à cocher de plusieurs colonnes à la fois ?

Oui ! Même si utiliser plusieurs formules FILTER est la manière la plus simple, nous pouvons écrire une formule dynamique pour créer une liste à partir de cases à cocher cochées dans plusieurs colonnes dans Google Sheets. Voici un exemple.

J’ai une liste de légumes (produits) dans la première colonne, et la sélection des clients se trouve dans les trois colonnes suivantes. Comment créer une liste des produits sélectionnés par client ?


image #1

Créez une Liste à partir de Cases à Cocher Cochées dans Google Sheets (Non-Dynamique)

Dans la cellule F2, insérez la formule FILTER suivante :

={B2;filter($A$3:$A$7,B3:B7=true)}

Copiez-la dans les cellules G2 et H2.

La formule filtre les légumes dans la plage A3:A7 si elles sont cochées par le client dans les plages B3:B7. La référence de la plage de légumes est absolue. C’est $A$3:$A$7, pas seulement A3:A7. Ainsi, lorsque vous copiez la formule, la référence de colonne ne change pas. Mais ce n’est pas le cas de la plage de cases à cocher B3:B7. Elle est relative, donc elle change de colonne lorsque vous copiez la formule. Les ACCOLADES permettent d’ajouter l’en-tête de chaque colonne de cases à cocher en haut de la liste. Ce qui précède est la manière la plus simple de créer une liste à partir de cases à cocher cochées dans une ou plusieurs colonnes dans Google Sheets.

Les solutions suivantes sont pour ceux qui ne veulent pas copier la formule.

Il existe deux méthodes. L’une utilise des fonctions classiques (ancienne méthode), et l’autre utilise une solution Lambda (nouvelle méthode). Je préfère cette dernière car elle est beaucoup plus simple que l’ancienne méthode.

Créez une Liste Dynamique à partir de Cases à Cocher Cochées dans Plusieurs Colonnes (Ancienne Méthode)

Dans cette méthode, nous suivrons une approche d’unpivot, de comptage cumulatif et de pivot max (agrégation de chaînes). La formule qui crée une liste dynamique à partir de cases à cocher cochées dans plusieurs colonnes n’est pas si compliquée. Mais si je vous donne la formule sans explication, vous ne pourrez pas la comprendre correctement. Laissez-moi donc adopter une méthode étape par étape pour écrire le code/la formule pour vous. Il y a trois étapes au total. Vous obtiendrez chaque morceau de code dans chaque étape, et nous les combinerons.

1. Dépliez la Liste

Formule :

=ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select * where Col2 is not null"))

La formule ci-dessus créera une liste DÉPLIÉE des cases à cocher cochées. Nous avons utilisé une instruction logique IF, c’est-à-dire if(B3:D=true,A3:A7,), à l’intérieur de FLATTEN pour apporter l’effet de dépliage uniquement aux cases à cocher cochées (valeurs TRUE).

2. Comptage Cumulatif des Valeurs TRUE

Dans cette étape, nous devrions créer une colonne virtuelle (auxiliaire). Quel sera le contenu de cette colonne ? Elle doit contenir le comptage cumulatif des clients dans la plage de colonnes F2:F9. Nous combinerons cela avec la sortie de l’étape 1 selon la syntaxe suivante : {comptage_cumulatif_des_valeurs_TRUE,liste_dépliée}. Cela équivaut à {étape_2,étape_1}. Il est facile de créer une colonne auxiliaire physique de comptage cumulatif des valeurs TRUE, également appelées cases à cocher cochées. La formule ci-dessous s’en occupe dans la cellule E2.

Colonne Auxiliaire Physique

=ArrayFormula(countifs(row(F2:F9),"<="&row(F2:F9),F2:F9,F2:F9))

image #3

Comment le convertir en une colonne auxiliaire virtuelle ?

La colonne auxiliaire virtuelle rend la formule qui crée une liste dynamique à partir de cases à cocher cochées quelque peu complexe. C’est parce que nous allons remplacer les références/plages directes par des formules équivalentes plus longues. Nous devons remplacer les parties de formule/références suivantes.

  1. row(F2:F9) – Elle apparaît deux fois dans la formule. La formule ci-dessus renvoie les numéros de ligne de la plage F2:F9. Le nombre de lignes dans F2:F9 est égal au nombre de cases à cocher cochées dans B3:D7. Nous pouvons donc remplacer la formule ci-dessus par sequence(countif(B3:D7,true)). Vous pouvez remplacer les deux occurrences de la partie de la formule.

  2. F2:F9 – Elle apparaît également deux fois dans la formule. Pour obtenir les valeurs dans F2:F9, nous pouvons extraire la première colonne du résultat de la formule de l’étape 1. Pour cela, remplacez simplement « Select * where Col2 is not null » par « Select Col1 » dans la formule de l’étape 1. Voici comment :

ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"))

image #4

Remarque : N’hésitez pas à supprimer ArrayFormula et la dernière parenthèse fermante.

Voici la formule de l’étape 2 après les modifications mentionnées ci-dessus.

=ArrayFormula(countifs(sequence(countif(B3:D7,true)),"<="&sequence(countif(B3:D7,true)),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null")))

Maintenant, rendons-la une colonne auxiliaire virtuelle. Pour cela, combinez la formule ci-dessus avec la formule de l’étape 1 selon la syntaxe {formule_étape_2, formule_étape_1}.

3. Pivot Max pour Créer une Liste à partir de Cases à Cocher Cochées dans Plusieurs Colonnes

Dans la dernière étape, nous utiliserons la sortie de l’étape 2 comme « données » dans une formule QUERY. En résumé, pour créer une liste dynamique à partir de cases à cocher cochées dans plusieurs colonnes, nous allons faire trois choses.

  1. Déplier les données source (nous exclurons les cases à cocher non cochées).
  2. Ajouter une colonne virtuelle de comptage cumulatif aux données dépliées.
  3. Pivoter les données dépliées.

Maintenant, seule la troisième étape est en attente. Voici :

Nous agrégerons les chaînes (légumes dans la colonne 3) dans les données dépliées en utilisant la fonction MAX. L’agrégation se fera en fonction de la mise en groupe des valeurs de la colonne 1 (comptage cumulatif). Nous ferons pivoter les valeurs de la colonne 2 qui sont les clients.

Syntaxe : Query(data, »Select Col1,max(Col3) group by Col1 pivot Col2″)

Formule (à insérer dans la cellule F2) :

=query({ArrayFormula(countifs(sequence(countif(B3:D7,true)),"<="&sequence(countif(B3:D7,true)),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"))),ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select * where Col2 is not null"))},"Select Col1,max(Col3) group by Col1 pivot Col2")

image #5

Créez une Liste Dynamique à partir de Cases à Cocher Cochées dans Plusieurs Colonnes (Nouvelle Méthode)

Voici une formule moderne utilisant la fonction auxiliaire BYCOL Lambda.

Vous pouvez remplacer la formule complexe ci-dessus dans la cellule F2 par celle-ci :

={B2:D2;ArrayFormula(ifna(transpose(split(transpose(bycol(B3:D7,lambda(c, join("|",filter(A3:A7,c))))),"|"))))}

Permettez-moi de l’expliquer étape par étape.

  1. Nous pouvons utiliser =filter(A3:A7,B3:B7) ou =filter(A3:A7,B3:B7=TRUE) pour obtenir les articles sélectionnés par « Client 1 » (B3:B7).
  2. Nous pouvons utiliser BYCOL pour obtenir tous les clients comme suit : =bycol(B3:D7,lambda(c, join(« | »,filter(A3:A7,c)))).
  3. Transpose > Split > Transpose la sortie de l’étape 2.
  4. Ajouter la ligne d’en-tête (B2:D2) en haut à l’aide des accolades.

Voilà tout ce qu’il faut pour créer une liste (dynamique) à partir de cases à cocher cochées dans une ou plusieurs colonnes dans Google Sheets. Merci de votre attention. Amusez-vous bien !

Example Sheet 11121

Ressources

GiGiKing

Articles en lien