Pour savoir comment conserver ou récupérer tous les libellés de colonnes dans une requête pivot dans Google Sheets, il est important de comprendre la raison de la disparition des colonnes.
Il peut arriver que les données soient obtenues sur plusieurs lignes (mais ce n’est pas systématique) lors de l’utilisation de la clause « Grouper par » conjointement avec la clause « Pivoter » dans une requête.
En d’autres termes, nous obtenons un tableau où chaque cellule contient le résultat d’agrégation pour la ligne pertinente (regroupée) et la colonne (pivot).
Lorsque vous utilisez la clause « Où » pour filtrer certaines lignes lors du regroupement, les colonnes pertinentes peuvent devenir vides et disparaître du résultat.
Cet article explique comment conserver tous les libellés de colonnes dans une requête pivot dans Google Sheets.
Par exemple, en exécutant la requête suivante dans la cellule G1 sur l’étendue de tableau de C1:E, nous obtiendrons la réponse indiquée dans les cellules G1:I3.
=query({C1:E},"Sélectionner Col1, sum(Col3) where Col1 is not null group by Col1 pivot Col2")
Voyons ce qui se passe dans les cellules G6:H7 lorsque nous filtrons « orange » en exécutant la requête suivante dans G6.
=query(C1:E,"Sélectionner C, sum(E) where C='apple' group by C pivot D")Un libellé de colonne pivot est manquant !
Comment conserver tous les libellés de colonnes selon les cellules G10:I11 dans une requête pivot dans Google Sheets ?
Nous allons voir cela avec deux exemples ci-dessous.
Conserver tous les libellés de colonnes dans une requête pivot – Critère dans la colonne de la clause Select
Si vous examinez les trois réponses de requête dans les colonnes G1:I ci-dessus, vous comprendrez la logique pour conserver toutes les colonnes dans le tableau pivot.
Quelle est cette logique ?
Pour conserver tous les libellés de colonnes dans un pivot, imbriquez deux formules de requête.
Nous appliquerons le critère dans la requête externe.
J’ai la formule suivante dans la cellule G10, où vous pouvez voir le critère « moved » mis en évidence.
=query(query(C1:E,"Sélectionner C, sum(E) where C is not null group by C pivot D"),"Sélectionner * where Col1='apple'")Cette logique s’applique uniquement lorsque vous filtrez la colonne spécifiée dans la clause Select.
C’est aussi simple que cela pour conserver tous les libellés de colonnes dans une requête pivot ! Passons à un autre exemple.
Conserver tous les libellés de colonnes dans une requête pivot – Colonnes à critères multiples
La logique précédente peut ne pas fonctionner telle quelle si nous voulons filtrer en fonction de la colonne de la clause Select et d’une autre colonne.
Ici aussi, nous utiliserons les critères dans la requête externe, mais cela s’applique uniquement à la colonne de la clause Select.
Voyons ce que nous ferons ici avec un exemple.
Dans cet exemple, nous sélectionnerons et regrouperons les colonnes 1 (A1:A) et 3 du tableau pivot, c’est-à-dire C1:C.
Mais nous voulons appliquer des conditions aux colonnes 1 et 2 (A1:A et B1:B).
Voici les critères (veuillez vous référer à l’image ci-dessous) :
- E2 : « Centre Speakers » (pour filtrer la colonne B)
- F2 : 2022/06/01 (date de début pour filtrer la colonne A)
- G2 : 2022/06/03 (date de fin pour filtrer la colonne A)
Normalement, nous utiliserions la requête suivante (en E4) pour regrouper la colonne A, appliquer les critères donnés dans les colonnes A et B, et pivoter la colonne C.
=Query({A2:C},"Sélectionner Col1,Count(Col2) where Col1 >= date '"&text(F2,"yyyy-mm-dd")&"' and Col1 <= date '"&text(G2,"yyyy-mm-dd")&"' and Col2='"&E2&"'group by Col1 pivot Col3 label Col1 'Date'",0)Voyez la « réponse réelle » sur l’image. Vous pouvez voir que deux colonnes vides, « Follow Up » et « Preparing », manquent.
Comment obtenir la « réponse attendue », c’est-à-dire conserver tous les libellés de colonnes dans une requête pivot ?
Plage auxiliaire à ajouter aux données de la requête
Bien sûr, nous déplacerons les critères de date vers la requête externe car ils s’appliquent à la colonne de la clause Select, c’est-à-dire A1:A.
En ce qui concerne la condition à appliquer dans la plage de colonnes B1:B, nous devons suivre une solution de contournement. Voici comment procéder.
Tout d’abord, obtenez les valeurs UNIQUES de la colonne pivot C. Pour cela, entrez la formule suivante en K1.
=unique(C2:C)Cela renverra les trois valeurs uniques suivantes : « Sent, » « Preparing » et « Follow up ». Veuillez vous référer à l’image ci-dessous.
Utilisez la formule SUBSTITUTE ci-dessous en J1 pour répéter le critère E2 trois fois.
=ArrayFormula(SUBSTITUTE(E2,"",sequence(counta(K1:K))))Ensuite, la formule SUBSTITUTE ci-dessous en I1 répétera le critère G2 (date de fin) + 1 trois fois.
=ArrayFormula(SUBSTITUTE(G2+1,"",sequence(counta(K1:K))))Pourquoi devons-nous ajouter +1 à la date de fin ?
Les données source sont dans A1:C. Au lieu de cela, nous utiliserons {A2:C;I1:K} comme données source.
Le +1 à la date de fin nous aidera plus tard à exclure la plage auxiliaire de la réponse de la requête.
En fait, les formules auxiliaires ci-dessus nous aident à conserver tous les libellés de colonnes dans le tableau pivot de la requête.
Nous pouvons utiliser la formule suivante (E8), qui conservera les libellés de colonnes manquants dans la requête pivot.
=query(Query({A2:C;I1:K},"Sélectionner Col1,Count(Col2) where Col2='"&E2&"' group by Col1 pivot Col3 label Col1 'Date'",0),"Sélectionner * where Col1 >= date '"&text(F2,"yyyy-mm-dd")&"' and Col1 <= date '"&text(G2,"yyyy-mm-dd")&"'")La condition de la colonne 2 se trouve dans la première requête.
Quant aux conditions de la colonne 1, vous les trouverez dans la requête externe.
C’est tout. Merci d’avoir lu. Amusez-vous bien !
Exemple_Sheet_17622
Ressources
- Quel est l’ordre correct des clauses dans une requête Google Sheets ?
- Comment formater l’en-tête du tableau pivot dans Google Sheets ?
- Comment faire un pivot sur plusieurs colonnes dans une requête Google Sheets ?
- Remplacer les cellules vides par 0 dans un pivot de requête dans Google Sheets
- Comment utiliser la fonction QUERY similaires à un tableau croisé dynamique dans Google Sheets ?
