Numbers : ADRESSE() & INDIRECT(), la référence

Mon métier m'amène à concevoir souvent pour les autres des tableaux très élaborés.
Quand on sort des usages habituels de type financier où SOMME(), NB(), NBVAL() et MOYENNE() constituent le kit de base ou du triturage de données qui s'appuie surtout sur les tris, l'identification des doublons et les fonctions de recherche, on se heurte rapidement à des problèmes jugés le plus souvent insolvables avec un tableur même par des utilisateurs chevronnés.

Généralement, les utilisateurs d'Excel se tournent alors vers les macros. Et ils réécrivent (souvent mal) des fonctions présentes en standard dans leur outil favori. Les utilisateurs de Numbers ont la chance de n'avoir pas à leur disposition de véritable langage de macro (même si AppleScript, on l'a déjà montré, peut rendre de véritables services). Du coup, ils ne perdent pas de temps à écrire du code. Par contre, ils risquent de passer un temps considérable à essayer de faire le boulot avec les rares fonctions qu'ils connaissent.

Un exercice nécessaire est de consulter (d'autant plus que c'est très bien fait dans Numbers) régulièrement le "navigateur de fonctions" et de butiner en essayant d'imaginer ce qu'on pourrait faire avec chacune de ces fonctions en les combinant ou pas à ce qu'on connaît déjà.

Parmi les fonctions méconnues et sous-utilisées (pour ne pas dire jamais utilisées ; je n'ai rencontré ces fonctions que dans des tableaux que j'ai moi-même créés), on trouve le couple ADRESSE() et INDIRECT() (ADRESSE() qu'on combine souvent avec LIGNE() et COLONNE()).

Avant d'en montrer une application, commençons à jouer avec.

Commençons par ADRESSE(). N'importe où dans le tableau, entrons :

Code:

=ADRESSE(1;2)

Numbers doit retourner la valeur $B$1, soit l'adresse de la cellule située à l'intersection de la première ligne et de la deuxième colonne (alors qu'on référence habituellement les cellule dans l'ordre colonne-ligne, par exemple A1, l'ordre utilisé dans ADRESSE(), ligne-colonne, vient sans doute de l'époque où les tableurs comme Multiplan ou Lotus 1-2-3 utilisaient des références du type L1C1. Ce qui pourrait laisser penser que ADRESSE() existe depuis cette époque. Je n'en suis pas sûr et ça n'a en fait pas d'importance. Tout ce qui compte, c'est d'insister assez longtemps sur ce point pour qu'il marque la mémoire du lecteur ).

On peut paramétrer ADRESSE() pour qu'elle retourne des adresses absolues ($B$1, relatives (B1) ou mixtes ($B1 ou B$1). Personnellement, cela ne m'a encore jamais servi. Un jour peut-être. Un quatrième paramètre permet de choisir le type de référence à retourner. Ce paramètre n'est là que par compatibilité avec Excel ; seule la valeur par défaut est opérationnelle. Á oublier.

Voyons maintenant INDIRECT().

Commençons par entrer une valeur quelconque en A1.

Puis, n'importe où ailleurs, entrons dans quelques cellules consécutives les formules suivantes :

Code:

=A1
=$A$1
=INDIRECT("A1")
=INDIRECT("$A$1")

Toutes ces choses-là retournent la valeur présente en A1.
Pour autant, déjà à ce stade, pour un même résultat, on dispose déjà de possibilités nouvelles.
Par exemple, insérons une ligne au dessus de la ligne 1 ; on constate que les deux premières formules ont été mises à jour en =A2 et =$A$2 et retournent la valeur désormais présente en A2 alors que les deux formules suivantes sont restées inchangées.
Quel est le meilleur des deux comportements ? Aucun. Tout dépend de ce qu'on cherche à faire. En tout cas, on est maintenant capable de créer une formule qui regarde ce qui est en A1 quels que soient les remaniements du tableau.

On va maintenant combiner les deux fonctions pour parvenir au même résultat :

Code:

=INDIRECT(ADRESSE(1;1))

C'est une façon bien compliquée de dire =A1 mais elle va nous ouvrir des portes nouvelles.
Jouons encore un peu avec avant d'aller plus loin.

Sortir comme ça des valeurs isolées, c'est bien mais on a généralement à faire à des plages de données.
Commençons par faire une simple somme. Entrons cinq valeurs numériques de A1 à A5.
N'importe où ailleurs, entrons les formules suivantes :

Code:

=SOMME(A1:A5)
=SOMME(INDIRECT("A1:A5"))
=SOMME(A1:INDIRECT("A5"))
=SOMME(INDIRECT("A1"):A5)

Tout ça nous ramène la même valeur. On voit qu'on peut combiner une référence conventionnelle avec une référence indirecte. On voit aussi qu'il peut être fait indirectement référence à une plage et non pas uniquement à une cellule isolée (en fait, pour les tableurs, toute référence concerne une plage. Simplement, une plage peut être réduite à une cellule unique).

Par contre, la formule :

Code:

=SOMME(INDIRECT("A1;A3;A5"))

retourne une erreur alors que :

Code:

=SOMME(A1;A3;A5)

fonctionne et retourne A1+A3+A5.
En fait, A1;A3;A5 n'est pas une référence mais une suite de paramètres séparés par des points-virgules. En effet, SOMME() admet plusieurs paramètres représentant des références dont seul le premier est obligatoire.
On aurait pu obtenir le résultat voulu en mettant en œuvre le calcul matriciel mais, contrairement à Excel, Numbers ne sait pas ce que c'est (en tout cas, pas la version que j'utilise). Bref. Continuons.

Créons un nouveau tableau, renommons-le TABLO et dans sa cellule A1, entrons une valeur quelconque.

Revenons au tableau principal et entrons n'importe où les formules :

Code:

=TABLO :: A1
=INDIRECT("TABLO :: A1")

Comme dans les exemples précédents, les deux formules ramènent la même valeur, en l'occurrence, issue d'un autre tableau.

Créons maintenant une nouvelle feuille et renommons le tableau créer automatiquement avec la feuille du nom de TABLO. On se retrouve avec deux tableaux ayant le même nom. Il y a donc ambiguité. Consultons nos deux formules. La première a été mise à jour pour lever l'ambiguité et la seconde retourne une erreur car (avantage ou inconvénient) la formule ne peut pas être mise à jour.

Renommons la nouvelle feuille FEUILLE, entrons une valeur dans la cellule A1 de son tableau TABLO ; une formule indirecte pour récupérer sa valeur serait :

Code:

=INDIRECT("FEUILLE::TABLO :: A1")

Maintenant, revenons à notre tout premier tableau. Dans les cellules D3 à D6, entrons les valeurs et la formule :

Code:

FEUILLE
TABLO
A1
=INDIRECT(D3&"::"&D4&" :: "&D5)

Je vous laisse expérimenter et réfléchir à tout ça.
Une fois un peu familiarisés avec ces fonctions, on pourra passer à une ou deux mises en application.

Première mise en application : planification par décalage de phase.

On va se contenter de montrer le principe. Charge à vous de l'utiliser dans vos propres tableaux.

Commençons par placer de C2 à AA2 les valeurs P1 à P25 (pour période 1 à période 25 ; la période peut-être un jour, une semaine, un mois, ce que vous voudrez. Il faudra sans doute diminuer la largeur des colonnes pour que ça tienne à l'écran).
Plaçons des valeurs (numériques ou pas. Généralement, c'est plutôt numérique. Ça peut représenter des quantités de produits à livrer) de C3 à AA3.
En C4, entrer la formule :

Code:

=INDIRECT(ADRESSE(3;COLONNE()+$B$1))

A l'aide de l'inspecteur, définir le format de C4 pour que les valeurs à zéro ne s'affiche pas (ce n'est pas obligatoire mais ça rend ce type de tableau beaucoup plus lisible).
Recopier C4 jusqu'à AA4.

En B1, à l'aide de l'inspecteur, définir le format de type Curseur avec un mini de −10, un maxi de 10 et un incrément de 1 (ou toute valeur entière). B1 contient le décalage de phase entre la ligne 3 et le ligne 4. Ça peut vouloir dire : « si je veux livrer x pièces en période 9, il faut que j'approvisionne x sous-ensembles en période 3 ». Evidemment, comme ça c'est simple. Mais si on doit combiner de nombreux sous-ensembles acquis auprès de nombreux fournisseurs le tout en introduisant des coefficients entre l'appelant et l'appelé, on conçoit rapidement l'aide que nous apportera l'ordinateur.

Faire varier la valeur de B1 à l'aide du curseur et, comme on dit en apache : enjoy !

On peut faire des usages du même genre en ligne et non plus en colonne.
On utilisera alors la fonction LIGNE() dans le premier paramètre de ADRESSE() au lieu de la fonction COLONNE() dans le deuxième paramètre. On prendra garde à ne pas tomber dans le piège du bug déjà signalé lors de l'utilisation de la fonction LIGNE() avec les catégories.

Deuxième mise en application : tableau récapitulatif.

Imaginons gérer un ensemble de données organisées en tableaux tous pareils (ça peut être des échéanciers, par exemple, à raison d'un par tableau, ou des fiches de projet, ce qu'on voudra).
On a conçu un tableau modèle qu'on duplique à chaque fois qu'on a besoin et on trouve toujours les mêmes données aux mêmes endroits.
Enfin, parce qu'on est organisé, on a pris soin de nommer nos tableaux de façon parlante.

Pour l'exemple, créons 3 ou 4 tableaux qu'on nommera JOE, MARCEL et BERNARD.
Plaçons une valeur en A1 de chacun de ces tableaux.

Créons maintenant notre tableau récapitulatif. De A2 à A4, plaçons les valeurs MARCEL, JOE et BERNARD.
En B2, entrons la formule :

Code:

=INDIRECT(A2&" :: A1")

Tirons sur la poignées pour dupliquer cette formule jusqu'à B4. That's it!

Désormais, à chaque fois qu'on ajoutera des tableaux, il sera facile de mettre à jour le tableau récapitulatif en ajoutant le nom de ces tableaux à la liste et en recopiant la formule vers le bas.

Dernière mise en application : formulaire d'impression.

On est dans le cas de figure évoqué plus haut de tableaux tous pareils (à part le tableaux annexes récapitulatifs, par exemple).
On souhaite pouvoir imprimer chaque tableau. On se rend compte que la mise en page de notre modèle n'est pas terrible et pas adaptée. Devoir remettre en page tous les tableaux ne nous enchante pas.

Avant d'aller plus loin, il n'est pas inutile de rappeler certaines bonnes pratiques.
Les tableurs (Excel, Numbers...) nous incitent à mélanger données, traitements et mise en forme. D'autant plus que, non informaticiens pour la plupart, nous ignorons tout de certains principes comme celui de la séparation des données et des traitements.
Ce principe vise à améliorer la qualité logicielle d'un côté et à faciliter l'évolution d'un autre.
Dans le cadre des tableurs, cela consiste (sans être extrémiste) à enregistrer les données sous leur forme la plus brute dans un ou des tableaux et à effectuer les calculs et/ou la présentation dans d'autres tableaux. Précisons tout de suite que ce n'est pas chose facile. Techniquement, si, rien ne s'y oppose mais c'est à nous-mêmes que nous avons à faire face et à notre tendance d'aller au plus facile immédiat au détriment du plus facile à long terme.

Nous avons donc suivi ce principe en enregistrant dans des tableaux séparés les données de JOE, BERNARD et MARCEL (en nommant ainsi nos tableaux). Chaque tableau est, évidemment, conçu de la même manière que les autres et on trouve toujours la même information (au sens de nature d'information) au même endroit (dans la même cellule).

Pour fixer les idées, on dira que chaque tableau réunit les informations relatives aux mensurations de Bernard, Joe et Marcel, tous trois de beaux gros cochons qui vont concourir à la foire internationale agricole de Saint-Vincent les Lisiers. L'organisateur exige une fiche de présentation par bête avec une mise en page type comportant des informations obligatoires sur l'animal et sur son éleveur.

Dans une version simple, on dira que dans chaque tableau on trouve en A1, l'année de naissance de l'animal, en A2, le mois de naissance, en A3, son poids et en A4 la date de la pesée.

On crée donc un tableau de mise en page qu'on rend joli et tout en prévoyant les emplacement où seront imprimées les informations relatives à chaque goret.
Dans une version simplifiée, on place respectivement de A2 à A6 les mentions :

- Nom de candidat :
- Année de naissance :
- Mois de naissance :
- Date de dernière pesée :
- Poids relevé :

En B2, on entre le nom d'un des cochons (en fait, le nom d'un des tableaux) : JOE
En B3 à B6, on entre l'ensemble de formules :

Code:

=INDIRECT($B$2&" :: A1")
=INDIRECT($B$2&" :: A2")
=INDIRECT($B$2&" :: A4")
=INDIRECT($B$2&" :: A3")

Les valeurs de Joe apparaissent à leur place. Entrons maintenant BERNARD en B2 et le formulaire est mis à jour, prêt à imprimer.
On peut sophistiquer un peu en choisissant pour B2 un format de type Menu local auquel on donnera les valeurs MARCEL, JOE et BERNARD. Ainsi, d'un simple clic, on pourra passer d'un cochon à l'autre.

On n'a pas utilisé ADRESSE() dans cet exemple parce qu'il est très simple (et surtout parce que j'ai pris un malin plaisir à intervertir la date de pesée et le poids dans le tableau de saisie et dans celui d'impression) mais cette fonction permet bien des choses que je vous laisse faire l'effort de découvrir et dont le prototype se trouve dans la première mise en application proposée.

Vous voilà équipés de nouvelles fonctions puissantes dont la plupart des utilisateurs ignorent tout et qui permettent d'envisager d'un esprit totalement nouveau la conception même de vos documents. Elles peuvent évidemment s'utiliser sur des cas déjà existants mais, d'expérience, elles s'expriment surtout dans des documents nouveaux conçus tout exprès.
Et c'est pour ça que je vous conseille de jouer avec et de vous entraîner un peu avant de vous lancer pour vous permettre de tirer les conséquences logiques liées à vos propres travaux.

Voilà un fil que je suis avec beaucoup d’intérêt. Pas le temps de creuser à fond en ce moment mais je note tout ça en attendant de pouvoir bosser là-dessus sérieusement. J’ai commencé à essayer les formules mais sur LibreOffice et jusqu’ici ça se comporte de façon parfaitement identique. La 3.6 qui vient de sortir aujourd’hui, justement, comporte une amélioration du paramétrage d’INDIRECT mais je n’ai pas encore regardé le détail.

Oui. Les fonctions de Numbers sont, bien entendu, tout comme celles de LibreOffice, alignées sur celles d'Excel (aux détails près liés aux plateformes). Du coup, le passage d'un tableur à l'autre s'en trouve facilité.
En tout cas, content de savoir que ça peut servir à quelques uns.

De toute façon, les articles de fond comme celui-là sont toujours utiles à longue échéance. On s’y reporte de temps à autres, comme ceux de Neiluj sur le PHP par exemple.