Index() avec des trous

Le comportement de la fonction Index(), quand un argument est manquant, varie selon la version d’Excel dont vous disposez, et ce de façon drastique.

Il nous faut alors distinguer entre le fonctionnement traditionnel de la fonction et son fonctionnement avec une version récente, comme par exemple la version 365 Pro Plus.

Le comportement traditionnel d’Excel

Dans l’exemple ci-dessous, la formule en G9 fait référence à la ligne 2 de la matrice n°2 d’Alfred, c’est-à-dire la matrice B8:C9. En effet, Alfred est constitué, dans cet ordre, des matrices A1:B3, B8:C9 et C14:E16.

Quand on valide la formule dans la cellule G9, on obtient l’erreur #VALEUR ! car le résultat est un vecteur et qu’un vecteur ne tient pas dans une cellule unique.

Si en revanche on clique dans la barre de formule – pour activer celle-ci – et que l’on fait F9 pour calculer la formule, on constate que le résultat est bien le vecteur (29;4).

Il y a bien une façon d’obtenir le résultat : pour cela, sélectionnez G9:H9, puis cliquez dans la barre de formule pour activer la formule de G9, et enfin validez en formule matricielle avec [Ctrl]-[Maj]-[Entrée]. Et le tour est joué : vous obtenez bien 29 en G9 et 4 en H9 !

Le comportement d’Excel 365 Pro Plus

Avec la version 365 Pro Plus, on se trouve dans un autre monde car Excel fait alors du remplissage automatique.

En G9, nous demandons à Excel de nous donner le contenu de la 2^ème ligne de la 2^nde matrice de l’ensemble Alfred de trois matrices. Comme cette ligne comporte deux éléments, Excel remplit automatiquement H9 avec la suite du contenu de la ligne. 

  

Remarque – On voit que c’est du remplissage automatique car, si l’on clique en H9, on aperçoit la même formule qu’en G9, mais cette formule est en police grise !

En G12, nous demandons à Excel de renvoyer le contenu de la seconde colonne de la première matrice. Excel remplit alors G13 et G14 avec la même formule que G12.

Qu’arrive-t-il donc si l’on entre une formule dans une des deux cellules remplies automatiquement par Excel. Simple à faire : entrons donc 10 en G13. Nous constatons que cela fonctionne, la cellule G14 est à présent vide et G12 affiche le message d’erreur #EPARS ! (pour PARSing Error) qui indique que l’on bloque Excel dans son remplissage automatique.

Dès que l’on efface le « 10 » entré en G13 ci-dessus, le remplissage automatique est restauré !