Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : comment réparer l’erreur “dans la fonction ARRAY_LITERAL, il manque des valeurs…”

Google Sheets : comment réparer l’erreur “dans la fonction ARRAY_LITERAL, il manque des valeurs…”

La fonction matricielle ou ARRAY_LITERAL sous Google Sheets, permet d’afficher plusieurs colonnes les unes en dessous des autres. C’est très pratique pour consolider des données issues de tables différentes. Voici l’article récent de Thierry pour […]

personnes ont consulté cet article

3 minutes

Rédigé par Antoine MARTIN - il y a 2 ans

Ce que vous allez découvrir

  • Comment gérer les erreurs de la fonction matricielle ?
  • Les formules ou les plages internes ne renvoient pas le même nombre de colonnes 
  • Une des formules qui compose la fonction matricielle renvoie une erreur 
  • Une des formules renvoie un nombre de ligne nul
  • Un ou plusieurs Importrange() n’a pas été validé(s)

Google Sheets : comment réparer l’erreur “dans la fonction ARRAY_LITERAL, il manque des valeurs…”

La fonction matricielle ou ARRAY_LITERAL sous Google Sheets, permet d’afficher plusieurs colonnes les unes en dessous des autres. C’est très pratique pour consolider des données issues de tables différentes.

Voici l’article récent de Thierry pour en savoir plus sur cette fonctionnalité de cumul des tables.


Prenons l’exemple d’une équipe dont chaque collaborateur doit renseigner le temps passé sur différentes tâches de la journée. Chacun remplit ses informations sur son onglet et la direction les rassemble dans une seule table pour faire ses analyses globales. 

Afficher toutes les données en un seul endroit avec les accolades.

={Annabelle!A1:F9;Bastien!A2:F9;Claudia!A2:F9}

Chaque table peut être une plage du même SpreadSheet, comme dans l’exemple, mais aussi une formule de tri, de filtres (FILTER() ou QUERY()) ou encore des IMPORTRANGE() ou IMPORTDATA() IMPORTHTML()… 

Comment gérer les erreurs de la fonction matricielle ?

Si vous essayez des combinaisons un peu rock’n’roll, vous risquez forcément de tomber sur l’erreur suivante : 

« Dans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d’un littéral de tableau”. 

Personnellement, la formulation de l’erreur m’a toujours laissé perplexe. 

Autant les autres messages d’erreurs sont précis et détaillés (cf. encore un article de Thierry), autant celui-ci brille par son manque d’explications ! Après plusieurs heures de recherches sur des cas précis, de tests et de quêtes dans les documentations et forums, j’ai pu repérer plusieurs explications différentes pour le même message.

Cet article vous présente le résultat de mes observations. Si vous en connaissez d’autres, n’hésitez pas à partager dans les commentaires.

Les formules ou les plages internes ne renvoient pas le même nombre de colonnes 

Exemple :

Exemple formule matricielle erreur nb de colonnes
={Annabelle!A1:E9;Bastien!A2:B9;Claudia!A2:F9}

ici la plage A1:E9 a cinq colonnes, A2:B9 seulement deux et A2:F9, six colonnes.

Erreur formule matricielle nombre de colonnes
={query(Annabelle!A1:F;"SELECT A,B,C,E WHERE A is not null");Bastien!A2:E9;Claudia!A2:E9}

Le Query() renvoie quatre colonnes, alors que les autres plages en ont cinq.

Google Sheets ne peut pas cumuler des plages si elles n’ont pas le même nombre de colonnes.

Résolution :

Vérifiez le nombre de colonnes renvoyées par chaque plage ou chaque formule. Dans le premier exemple, c’est relativement simple car il s’agit de trois plages explicites. Mais cela se complexifie si vous cumulez un grand nombre de tables, ou si vous utilisez des plages nommées, qu’il faut prospecter une à une, et s’assurer qu’elles recouvrent le même nombre de colonnes, ou si vous utilisez des formules (Filter() ou Query() par exemple) où il est plus difficile de gérer le nombre de colonnes.

Une des formules qui compose la fonction matricielle renvoie une erreur 

Exemple :

J’utilise des Query() pour afficher les informations filtrées (et ainsi éviter les lignes vides avec la condition « where B Is Not Null » bonne pratique plutôt que de limiter le nombre de lignes en dur comme dans l’exemple précédent (en savoir plus sur QUERY)).

Résolution :

Si la première hypothèse a été vérifiée et validée (est-ce que toutes les formules renvoient le même nombre de colonnes ?), il faut maintenant se demander si chaque formule est correcte. L’erreur globale #VALEUR! ne précise pas si il y a une erreur, et surtout dans quelle formule elle se situe. Il faut donc prospecter chaque formule et y déceler la potentielle erreur. Une seule requête Query() est parfois complexe à réparer, alors plusieurs ensemble … ça devient ubuesque.

Quelques astuces pour déceler l’erreur :

Séparer l’affichage des requêtes :

Optimisez l’affichage en chassant à la ligne chaque formule (ALT+ ENTREE) ;

Formule matricielle afficher les query séparément

La lecture de chaque requête est plus facile et peut faire apparaître l’erreur. Vous avez trouvé ? Sinon, passez à la suite.

Recopier chaque formule indépendamment :

Les deux premiers Query() ont renvoyé chacun un tableau correct, mais le dernier m’a permis de repérer une erreur de syntaxe : la source « Claudia!A:E » ne possède pas de colonne « F » qui est pourtant appelée dans la requête. L’erreur « NO_COLUMN: F » est plus précise. Je peux donc réparer et injecter la réparation dans la vue matricielle.

Une des formules renvoie un nombre de ligne nul

Extrêmement déroutant : une requête vide dont la source n’inclut pas la ligne d’en-tête renvoie une erreur ! De quoi s’arracher le peu de cheveux qui me restent !

Exemple

La requête 1 n’a aucune réponse, et pourtant la vue matricielle fonctionne.

La requête 2 ne renvoie aucune réponse, et la vue bogue. 🙁

La différence entre les deux : la source commence à la ligne 2 (Bastien!A2:F) et non (Bastien!A:F) ; en fait, ici, la réponse est vraiment vide car A2:F ne contient pas la ligne d’en-tête.

Résolution

Si vous utilisez des Query(), assurez-vous de prendre l’intégralité des lignes dans la source, si votre requête peut renvoyer un nombre de lignes nul. Car la vue matricielle n’accepte pas qu’un de ses composant soit une plage vide. Du coup, vous risquez de voir plusieurs fois la ligne d’en-tête dans la consolidation des données.

Un ou plusieurs Importrange() n’a pas été validé(s)

Dernier cas possible que j’ai recensé : l’utilisation de fonctions Importrange() non associées à l’intérieur d’une vue matricielle.

Chaque collaborateur possède son propre SpreadSheet dans lequel il complète ses heures individuellement, un autre SpreadSheet centralise les heures de travail et les compile dans un seul tableau. J’ai placé les URLs de chaque collaborateur dans l’onglet « Collaborateurs ».

L’onglet « Collaborateurs » recense les URL de leurs SpreadSheets respectifs.

Le recap affiche les Importrange() filtrés par un Query().

L’erreur vient du fait qu’un des Importrange() n’a pas été associé au SpreadSheet. Dans la colonne qui suit les URLs des SpreadSheets individuels, je place un Importrange() sur une seule cellule. Cela permet de visualiser si l’association des feuilles indispensables pour l’import des données fonctionne. Ici, on voit qu’une des trois associations n’a pas été autorisée (#REF!).

Un simple clic sur « Autoriser l’accès » et c’est validé :

Valider l’association, et la vue matricielle fonctionnera !

Chaque élément de la vue matricielle est un Importrange() qui a été associé à sa feuille source. Pensez à faire des tests en amont en important uniquement la première cellule du tableau externe, afin de valider le cas échéant l’association des tables.


Ce tour d’horizon, loin d’être exhaustif, permet d’évoquer plusieurs hypothèses dans la recherche de remédiation de l’erreur « ARRAY_LITERAL ». En espérant que ce questionnement et ces pistes de résolution vous permettent de sortir de l’impasse ! Si vous avez décelé d’autres explications au même message d’erreur, merci de partager dans les commentaires.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
guest
2 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Patrick Tellier

Bonjour Antoine,
Extraordinaire Numériblog !
Je fais une recherche sur Google parce que je bute sur un problème depuis ce matin et là je tombe sur ton article….
J’ai un nouveau cas ou un query sur une matrice de plusieurs tableaux donne cette erreur ARRAY_LITERAL et le problème est que les feuilles ont des colonnes jusque Z et le query porte sur la zone A1:AE …….

Cet avis vous a été utile ?

Antoine MARTIN
Répondre à  Patrick Tellier
6 mois il y a

Hello Patrick, Merci pour ton message 🙂 Donc il faut bien décortiquer chaque élément de la matrice pour trouver l’erreur. C’est parfois assez compliqué de trouver le grain de sable avec si peu d’indication.

Cet avis vous a été utile ?