Vous pouvez effectivement raccourcir une formule Query dans Google Sheets lorsque vous souhaitez tester des conditions dans plusieurs colonnes dans la clause WHERE de Query. Cela peut certainement impliquer les opérateurs logiques ET, OU dans la clause WHERE de Query.
Je dédie ce tutoriel à tous les amoureux de Query dans Google Sheets. Ici, je vais essayer de vous donner quelques idées pour simplifier votre formule Query dans Google Sheets en cas de conditions impliquant plusieurs colonnes.
En réalité, simplifier une formule Query dépend du problème que vous souhaitez résoudre. Donc, je vais donner quelques exemples dans l’espoir de vous guider dans la bonne direction.
Je vais tester les conditions suivantes dans plusieurs colonnes dans Query :
- Je vais utiliser l’opérateur logique ET pour tester si toutes les colonnes spécifiques (dans chaque ligne) sont vides.
- Dans le deuxième test, je vais utiliser l’opérateur logique OU pour trouver si l’une quelconque des colonnes spécifiques dans chaque ligne contient des valeurs.
Dans ces tests, toutes les colonnes en question doivent contenir des valeurs de type texte ou numérique. Cela signifie que je veux tester une condition dans 5 colonnes et que toutes les colonnes doivent contenir des valeurs de type texte ou numérique.
Passons maintenant aux exemples pour mieux comprendre.
Les conditions OU dans plusieurs colonnes dans Query
Dans un test logique OU, l’une quelconque des conditions doit être satisfaite.
Commençons par les valeurs numériques dans la colonne à tester.
Test OU – Valeurs numériques comme conditions dans plusieurs colonnes dans Query
Voici les données d’exemple. La formule Query pour simplifier est la suivante :
=query(A1:K,"Select A where C is not null or E is not null or G is not null or I is not null or K is not null",1)Comment simplifier les conditions dans plusieurs colonnes dans cette formule Query ?
Nous pouvons le faire à l’aide d’une colonne auxiliaire virtuelle ainsi qu’une colonne auxiliaire physique. Commençons d’abord par la colonne auxiliaire virtuelle.
Utilisation d’une colonne auxiliaire virtuelle :
Tout d’abord, voici la version simplifiée de la formule Query. Ensuite, vous pourrez trouver l’explication.
=ArrayFormula(query({A1:K,C1:C+E1:E+G1:G+I1:I+K1:K},"Select Col1 where Col12>0",1))Pour être honnête, ce n’est pas la version la plus simple car elle implique une colonne auxiliaire virtuelle. Mais les conditions dans plusieurs colonnes dans la clause WHERE de Query ont été simplifiées.
La formule Query serait encore plus simple si vous utilisez une colonne auxiliaire physique. Avant d’aborder cela, permettez-moi d’expliquer ce que j’ai fait dans cette Query pour la simplifier.
J’ai raccourci cette partie de la clause WHERE de Query :
where C is not null or E is not null or G is not null or I is not null or K is not nullElle est raccourcie à :
where Col12>0En fait, il y a 11 colonnes dans les données dans la plage de A1:K. Vous pouvez compter de A à K. La douzième colonne est la colonne virtuelle créée dans les données de Query sous la forme suivante :
{C1:C+E1:E+G1:G+I1:I+K1:K}Puisque les colonnes C, E, G, I et K contiennent des valeurs numériques, j’ai ajouté ces valeurs et créé une seule colonne.
Utilisation d’une colonne auxiliaire physique :
Il vous suffit d’entrer cette formule de colonne auxiliaire dans la cellule L1. Nous utilisons déjà les colonnes jusqu’à K.
=ArrayFormula(if(len(A1:A),C1:C+E1:E+G1:G+I1:I+K1:K,))Voici la formule Query qui est simplifiée au maximum pour simplifier l’utilisation de conditions dans plusieurs colonnes dans Query. Vous pouvez placer cette formule dans la cellule M1.
=query(A1:L,"Select A where L>0",1)Test OU – Chaîne de texte comme conditions dans plusieurs colonnes dans Query
Ici aussi, je vous propose des solutions basées sur des colonnes auxiliaires virtuelles et physiques.
Approche avec colonne auxiliaire virtuelle :
Ici, les colonnes de critères contiennent des valeurs de type texte, donc au lieu de les additionner, vous pouvez les combiner comme suit.
{A1:K,C1:C&E1:E&G1:G&I1:I&K1:K}Dans la clause WHERE, la condition raccourcie sera :
where Col12 is not nullVoici comment vous pouvez utiliser une colonne auxiliaire physique pour raccourcir davantage la formule.
Approche avec colonne auxiliaire physique :
Vous pouvez simplement entrer cette formule dans la cellule L1. C’est notre formule de colonne auxiliaire.
=ArrayFormula(if(len(A1:A),C1:C&E1:E&G1:G&I1:I&K1:K,))Voici la version simplifiée de Query à placer dans la cellule M1.
=query(A1:L,"Select A where L is not null",1)De cette façon, vous pouvez simplifier les conditions dans plusieurs colonnes dans Query. Nous avons maintenant terminé la partie logique OU. Passons à la prochaine partie.
Les conditions ET dans plusieurs colonnes dans Query
Dans un test logique ET, toutes les conditions doivent être satisfaites.
Ici, j’utilise à nouveau les mêmes données d’exemple ci-dessus. Voici la formule pour simplifier.
=query(A1:K,"Select A where C is not null and E is not null and G is not null and I is not null and K is not null",1)Test ET – Valeurs numériques comme conditions dans plusieurs colonnes
Je vais simplifier les conditions ET dans plusieurs colonnes dans cette formule Query. Voici la version simplifiée de la formule.
Sans colonne auxiliaire :
=ArrayFormula(query({A1:K,0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K},"Select Col1 where Col12=0",1))Que fait la colonne auxiliaire virtuelle ?
Lorsque vous divisez 0 par un nombre dans une colonne, cela renvoie 0. Cela signifie que zéro divisé par n renvoie 0. Mais si vous divisez 0 par 0 dans une autre colonne, cela renvoie une erreur #DIV/0! car la division par 0 est indéfinie.
Si toutes les colonnes d’une ligne ont des valeurs, la formule de la colonne virtuelle a la valeur 0 dans cette ligne.
=0/C2+0/E2+0/G2+0/I2+0/K2Consultez la capture d’écran ci-dessous pour plus de détails (cela ne sert qu’à des fins d’explication).
Le 0 surligné en couleur cyan indique que toutes les colonnes dans cette ligne (soulignées en rouge) ont des valeurs, sinon vous pouvez voir l’erreur #DIV/0!.
Dans la formule simplifiée ci-dessus, la partie ci-dessous a :
where C is not null and E is not null and G is not null and I is not null and K is not nullRaccourcie à :
where Col12=0Avec colonne auxiliaire :
Entrez simplement cette formule dans la cellule L1.
=ArrayFormula(if(len(A1:A),0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K,))Ensuite, la formule simplifiée dans la cellule M1.
=query(A1:L,"Select A where L=0",1)Que se passe-t-il si nous avons des chaînes de texte comme critères dans ce cas ?
Test ET – Chaîne de texte comme conditions dans plusieurs colonnes
Voici la formule normale.
=query(A1:K,"Select A where C is not null and E is not null and G is not null and I is not null and K is not null",1)Sans colonne auxiliaire :
Je vous suggère d’utiliser uniquement la formule de la colonne auxiliaire. Mais ici aussi, vous pouvez utiliser ma formule simplifiée ci-dessus même si elle ne semble pas propre.
Il suffit d’entourer les colonnes où nous voulons tester les critères avec la fonction ISTEXTE.
=ArrayFormula(query({A1:K,0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K)},"Select Col1 where Col12=0",1))Avec colonne auxiliaire :
Entrez simplement cette formule dans la cellule L1.
=ArrayFormula(if(len(A1:A),0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K),))Utilisez cette Query dans la cellule M1.
=query(A1:L,"Select A where L=0",1)Et voilà. Profitez-en !
