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.