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é !

La fonction Index()

Dans mon article du 28 février dernier titré « Visicalc, le grand-père d’Excel », je vous montrais que le tout premier tableur, Visicalc, possédait déjà les bases de toute modélisation efficace grâce à la présence des fonctions Choose (Index) et Lookup (Recherche).

J’y expliquais aussi un certain nombre de choses sur l’évolution de la fonction Recherche() dans le temps. Il est peut-être utile, et nous le ferons dans cet article, de revoir la fonction Index() car je vois souvent des utilisateurs dans les entreprises qui la maîtrisent mal. Pire encore, certaines vidéos Internet sur cette fonction ont, dans le meilleur des cas, des lacunes évidentes et, dans le pire des cas, disent clairement des contre-vérités.

Il faut avouer que l’aide de Microsoft – loin d’être parfaire ! – a certainement contribué à cette confusion générale, à laquelle j’essaye de répondre dans cet article.

Les erreurs dans l’aide de Microsoft

La partie gauche de l’image ci-dessous montre ce qui apparaît quand on entre =index( dans une cellule. Microsoft a fait très fort, car il y a là trois erreurs, que je leur ai signalées en vain il y a plus de 20 ans et qui n’ont jamais été corrigées !

Ils ont oublié la première syntaxe, celle où il y a un vecteur et non une matrice.

Ils parlent de matrice dans la première syntaxe et de zone dans la seconde : il faudrait dire « matrice » dans les deux cas. Ou, mais moins précisément, « zone » dans les deux cas…                                                    

Si vous écrivez =index(matrice;n°ligne;) vous obtenez une erreur avec un Excel traditionnel et pas du tout ce à quoi vous pensiez avec un Excel en licence 365 ProPlus.

Pour commenter la remarque n°3, cette erreur de documentation est présente dans toute l’aide d’Excel et pour toutes les fonctions à plusieurs arguments : le « ; » devrait être entre crochets : il faudrait écrire =index(matrice;n°ligne[;n°colonne]) et non =index(matrice;n°ligne;[n°colonne]).

Si l’on regarde la partie droite de l’image, on peut faire les mêmes remarques que ci-dessus, plus noter que les arguments sont séparés dans cette aide par des « , » alors que ce devrait être des « ; ».

La bonne syntaxe pour index()

Vous pouvez voir dans la copie d’écran ci-dessous la syntaxe que je propose pour index(). Il me semble bien plus clair d’éliminer tous les crochets représentant des paramètres facultatifs et de dire, comme en I5:I7, qu’il y a en fait trois syntaxes : pour un vecteur, pour une matrice et pour {mat}, c’est-à-dire un ensemble de matrices. Voici donc ma proposition pour une bonne syntaxe d’Index :

=index(vecteur;position)

=index(matrice;n°lig;n°col)

=index({mat};n°lig;n°col;n°mat)

Remarque – Nous avons donné le nom « Alfred » à l’ensemble des trois matrices à fond jaune. Si la matrice retenue n’a pas le nombre de lignes et/ou de colonnes demandé, le résultat est #REF !

Je suis toujours vivant…

Chères lectrices, chers lecteurs,

Je n’ai rien publié sur ce blog depuis le 28 février. C’est – de très loin ! – mon plus grand trou de publication depuis la création du blog en octobre 2005. J’avais créé ce blog en remplacement de La lettre d’Excel, une revue bimestrielle de 24 pages avec disquette que j’avais créée en octobre 1990 et publiée durant 15 ans.

Rassurez-vous, ce trou de publication récent de mes messages n’est pas dû à un problème de santé, mais au fait que, après avoir vécu plus de 40 ans dans une maison que j’avais fait construire, je me prépare à emménager dans un appartement. Passer de 350 m2 (100 m2 sur 3,5 niveaux) à un appartement de 135 m2 n’est pas chose simple…

Je tiens à remercier les lecteurs fidèles qui – jusqu’au Canada ! – se sont inquiétés de mon silence inhabituel et ont pris des nouvelles de ma santé.

Des occasions pour les chineurs

Je profite de l’occasion pour vous proposer les différents objets dont je cherche à me séparer, allant des objets les plus bénins à un objet rare, voire existant à très peu d’exemplaires dans le monde.

N’hésitez pas à transmettre cette liste à vos connaissances qui pourraient être intéressées.

Pour obtenir plus de détails ou envoyer des propositions, contactez-moi à thiriez@hec.fr.

Mad Magazine – La première revue totalement déjantée au monde.

34 exemplaires du n°136 (juin 1970) au n°400 (Décembre 2000). Etat variable : de très fatigué pour certains exemplaires à un état neuf pour d'autres.

San Antonio – 25 romans entre n°11 et n°824 + collections SA1 à SA7.

OSS 117 – 22 romans.

Paul Kenny – 11 romans.

Grand Larousse Universel – 15 volumes – Edition 1987 – Etat neuf.

Logiciels neufs

Windows (emballage d’origine) : Système d’exploitation 3.1

Microsoft (emballage d’origine) : Office 97 Professional « Mise à jour »

Mac Recorder (neuf) de Farallon

Autour de l’Apple II – Plusieurs livres :

Manuel de référence BASIC
Reference Manual for IIe only

Reference Manual addendum for IIe: Monitor ROM listings

Apple IIe Dot Matrix Printer

Travaux pratiques Applesoft IIe

Apple IIe Guide de l’utilisateur

Un objet rarissime : Javelin Plus

Javelin est un tableur génial pour lequel j’avais déjà publié un livre.

Javelin Plus était la version 2 de ce logiciel pour la sortie duquel l’éditeur, qui avait bien aimé mon livre, m’avait commandé un second ouvrage, que j’ai d’ailleurs totalement écrit avant d’apprendre que l’éditeur, suite à une série de bugs trop complexes à éliminer (et que je lui avais annoncés au fur et à mesure de ma rédaction), ne décide de ne finalement pas lancer la commercialisation.

J’ai donc un produit complet Javelin Plus – dans sa version b – qui m’a permis d’écrire ce livre jamais publié.

A titre indicatif, j’ai créé – sur le même modèle de La lettre d’Excel – une Lettre de Javelin qui s’est – et pour cause – arrêtée au n°1. Il me reste aussi quelques exemplaires de cette lettre rarissime car jamais publiée !

Pour la petite histoire, j’ai eu l’occasion de rencontrer en France l’un des deux créateurs de Javelin. Quand je lui ai montré certains des modèles que j’avais créés pour mes clients avec Javelin, il était lui-même très surpris de voir la richesse de ce que l’on pouvait tirer de son logiciel !

La suite du blog

Après cette parenthèse, rassurez-vous, je continuerai avec le fonctionnement normal du blog et, pour me faire pardonner ce grand trou de 20 jours, je le ferai très rapidement.

J’ai essayé de voir ce que m’on pouvait obtenir avec une anagramme de CORONAVIRUS. Pas de chance, je n’en ai trouvé qu’une seule qui, outre le fait qu’elle n’a pas une orthographe très convaincante, transmet un message à la moralité douteuse : SURVI O NARCO !

Bon courage à tous !