Fonction query, Erreur affichage si tableau vide

Le 18/10/2021 à 16:55

Bonjour à tous,

Il y a quelques temps j'ai bien avancé sur un projet, grâce à votre aide.

Sur ce même projet je me suis rendu d'un petit soucis que je n'arrive pas à résoudre.

J'ai une fonction query depuis une feuille, vers plusieurs feuilles.

=sort({
QUERY('Pierre'!N100:AC136; "SELECT * where N is not null AND (AC='R' OR AC='P')");
QUERY('Marie'!N236:AC274; "SELECT * where N is not null AND (AC='R' OR AC='P')");
QUERY('Paul'!N44:AC80; "SELECT * where N is not null AND (AC='R' OR AC='P')");
QUERY('Anne'!N132:AC168; "SELECT * where N is not null AND (AC='R' OR AC='P')")
};16;0;1;1)

Ca fonctionne bien. Par contre si une plage d'une des feuille est vide, et si je n'ai pas au moins une ligne avec une des conditions

AC='R' OR AC='P'

j'ai une erreur d'affichage.

Je tourne en rond...

Il y a moyen de contourner ?

Le 18/10/2021 à 18:58

Bonsoir,

Peut-être comme ceci ???

=sort({
SIERREUR(QUERY(Pierre!N100:AC136; "SELECT * where N is not null AND (AC='R' OR AC='P')");""); 
SIERREUR(QUERY(Marie!N236:AC274; "SELECT * where N is not null AND (AC='R' OR AC='P')");"");
SIERREUR(QUERY(Paul!N44:AC80; "SELECT * where N is not null AND (AC='R' OR AC='P')");""); 
SIERREUR(QUERY(Anne!N132:AC168; "SELECT * where N is not null AND (AC='R' OR AC='P')");"") }
;16;0;1;1)
Le 18/10/2021 à 22:42

Bonsoir,

Merci pour votre réponse.

J'ai toujours cette erreur 'ErreurDans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d'un littéral de tableau.', si N est vide ou AC n'est pas égal à R ou P.

Le 20/10/2021 à 10:14

J'ai essayé une imbrication de si mais attention c'est une vraie usine à gaz

pour plus de facilité pour moi j'ai mis 4 pages Pierre, Marie, Paul, et Anne

j'ai mis aussi les plages de report identiques N1:Z100

=SI(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)))))))))))))))
Le 20/10/2021 à 10:34

Après contrôle la fonction ne fonctionne bien qu'avec une seule page non renseignée

l'erreur revient avec plus de pages non renseignées

Désolé ;-(((

peut-être un autre contributeur pourra faire mieux ???

Le 20/10/2021 à 15:53

Il me semble avoir trouvé 

=SIERREUR(SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ET(ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')")));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Pierre!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Marie!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Paul!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
SI(ESTERREUR(QUERY(Anne!$N$1:$Z$100; "SELECT * where N is not null AND (Z='R' OR Z='P')"));sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)
;
sort({
QUERY(Pierre!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Marie!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')");
QUERY(Paul!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')"); 
QUERY(Anne!N1:Z100; "SELECT * where N is not null AND (Z='R' OR Z='P')") };16;0;1;1)))))))))))))));"Aucune données")
Le 22/10/2021 à 14:10

Bonjour,

yannchile a écrit :

Bonsoir,

Merci pour votre réponse.

J'ai toujours cette erreur 'ErreurDans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d'un littéral de tableau.', si N est vide ou AC n'est pas égal à R ou P.

Il faut dans ce cas, lorsqu'il y a une erreur, ne pas se limiter à "", mais à un nombre de "" équivalent au nb de colonnes attendues, ce qui ferait ""\""\""\"" ...

Le 23/10/2021 à 14:22

Salut Mike

voici le fichier test, peux tu me faire parvenir ta solution la mienne est en B131 de l'onglet "Resultats"

Merci

Fichier test

Le 24/10/2021 à 19:49

ajoute ensuite le tri

={
iferror(QUERY(Pierre!N1:Z; "SELECT * where N is not null AND (Z='R' OR Z='P')");{""\""\""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(Marie!N1:Z; "SELECT * where N is not null AND (Z='R' OR Z='P')");{""\""\""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(Paul!N1:Z; "SELECT * where N is not null AND (Z='R' OR Z='P')");{""\""\""\""\""\""\""\""\""\""\""\""\""});
iferror(QUERY(Anne!N1:Z; "SELECT * where N is not null AND (Z='R' OR Z='P')");{""\""\""\""\""\""\""\""\""\""\""\""\""})
}
Le 25/10/2021 à 07:13

solution ultra simple si les critères de sélection sont les mêmes

=query({Pierre!N1:Z;Marie!N1:Z;Paul!N1:Z;Anne!N1:Z};"SELECT * where Col1 is not null AND (Col13='R' OR Col13='P')")
Le 25/10/2021 à 09:33

comme d'hab très bonnes solutions

Merci

Rechercher des sujets similaires à "fonction query erreur affichage tableau vide"