Prenons trois feuilles :
- Result_1
- Result_2
- Result_3
Chacune de ces feuilles est le résultat d’un Google form contient les résultats d’un quizz sur 4 colonnes (A,B,C,D). Ces 3 quizz ont été soumis au mêmes personnes, ce qui fait que j’ai les mêmes mails dans les trois feuilles. Je veux récupérer la liste de toutes les adresses mails dans une feuille nommée DATA pour totaliser les points obtenus pas chacun ; avec un UNIQUE() et un SOMME.SI()
Pour ça, dans la colonne A1 de la feuille DATA, j’utilise ce code :
=ARRAYFORMULA(
QUERY(
{
QUERY(Result_1!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_2!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_3!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL")
};
"SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL"
)
)Cette fonction Google Sheets utilise une combinaison des fonctions ARRAYFORMULA, QUERY et des accolades {} pour manipuler et filtrer des données provenant de plusieurs plages de cellules. Voici une explication détaillée de chaque partie de cette fonction :
Structure de la fonction
=ARRAYFORMULA(
QUERY(
{
QUERY(Result_1!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_2!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_3!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL")
};
"SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL"
)
)Explication des composants
1 | QUERY sur chaque plage de cellules :
QUERY(Result_1!A:D; « SELECT A, B, C, D WHERE A IS NOT NULL »)
Result_1!A:D : Plage de cellules de la feuille Result_1 allant de la colonne A à D.
« SELECT A, B, C, D WHERE A IS NOT NULL » : Instruction SQL-like qui sélectionne toutes les colonnes (A, B, C, D) où la colonne A n’est pas nulle.
Cette requête est répétée pour les plages Result_2!A:D et Result_3!A:D.
2 | Concaténation des résultats avec des accolades {} :
Nouvelle requête QUERY sur la table combinée :
{
QUERY(Result_1!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_2!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_3!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL")
}Les accolades {} permettent de combiner les résultats des trois requêtes en une seule table. Cela crée une sorte de « réunion » des résultats des trois requêtes QUERY.
3 | Nouvelle requête QUERY sur la table combinée
QUERY(
{
QUERY(Result_1!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_2!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_3!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL")
};
"SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL"
)Cette nouvelle QUERY sélectionne les colonnes Col1, Col2, Col3, Col4 (les colonnes résultantes de la première étape) où Col1 n’est pas nulle.
Cela permet de s’assurer que toutes les lignes de la table combinée ont des valeurs non nulles dans la première colonne.
4 | ARRAYFORMULA
ARRAYFORMULA(
QUERY(
{
QUERY(Result_1!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_2!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL");
QUERY(Result_3!A:D; "SELECT A, B, C, D WHERE A IS NOT NULL")
};
"SELECT Col1, Col2, Col3, Col4 WHERE Col1 IS NOT NULL"
)
)ARRAYFORMULA permet d’appliquer la fonction QUERY à une plage de cellules, ce qui facilite le traitement de plusieurs lignes à la fois.
Conclusion
Cette fonction combine les données des plages Result_1!A:D, Result_2!A:D et Result_3!A:D, en filtrant les lignes où la colonne A n’est pas nulle dans chaque plage. Ensuite, elle combine ces résultats en une seule table et applique un nouveau filtre pour s’assurer que la première colonne de la table combinée n’est pas nulle. ARRAYFORMULA permet de traiter cette table combinée en une seule formule.
En somme, cette formule est utile pour agréger et filtrer des données provenant de plusieurs feuilles ou plages de cellules tout en s’assurant que certaines conditions de non-nullité sont respectées.