Les fonctions avancées qui servent réellement

Il existe des centaines de fonctions. Une quinzaine sépare un utilisateur intermédiaire d'un utilisateur avancé.

Les plages dynamiques

Les versions récentes propagent automatiquement un résultat sur plusieurs cellules à partir d'une seule formule.

FILTRE / FILTER renvoie les lignes qui satisfont une condition.

=FILTRE(A2:D500; C2:C500>1000)

Une seule formule, et le résultat occupe autant de lignes que nécessaire. Il se met à jour tout seul quand les données changent.

TRIER / SORT et TRIERPAR / SORTBY trient un résultat sans toucher aux données.

UNIQUE renvoie les valeurs distinctes d'une plage. C'est la façon la plus rapide d'obtenir une liste de référence — utile pour alimenter une liste déroulante qui s'entretient seule.

SEQUENCE produit une suite de nombres, pratique pour générer des dates ou des numéros de ligne.

Réserve importante : ces fonctions n'existent pas dans les versions anciennes. Un classeur qui les utilise s'ouvre mal ailleurs. Si vous échangez des fichiers, vérifiez ce que vos correspondants utilisent.

Les fonctions de recherche modernes

RECHERCHEX / XLOOKUP remplace avantageusement les anciennes formes : elle cherche dans les deux sens, exige la correspondance exacte par défaut, et gère l'absence de résultat sans formule supplémentaire.

=RECHERCHEX(A2; Tarifs[Ref]; Tarifs[Prix]; "Inconnu")

EQUIVX / XMATCH fait de même pour les positions.

Sur un classeur destiné à circuler, restez sur INDEX et EQUIV, universels.

L'agrégation conditionnelle multiple

SOMME.SI.ENS, NB.SI.ENS, MOYENNE.SI.ENS acceptent plusieurs conditions.

=SOMME.SI.ENS(D:D; B:B;"Lyon"; A:A;">="&DATE(2027;1;1))

Deux points à connaître :

L'ordre des arguments diffère de la version à condition unique : la plage à sommer est en premier ici.

Une condition qui combine un opérateur et une référence s'écrit en concaténant : ">="&F1, jamais ">=F1".

Le décalage de période

DECALER / OFFSET construit une plage relative à une cellule. Utile pour des moyennes glissantes ou des comparaisons à la période précédente.

=MOYENNE(DECALER(B2;-2;0;3;1))

Attention : cette fonction est volatile, c'est-à-dire recalculée à chaque modification du classeur. Sur un grand fichier, un usage massif ralentit tout. INDEX permet souvent d'obtenir le même résultat sans volatilité.

Le texte structuré

FRACTIONNER.TEXTE / TEXTSPLIT découpe une chaîne selon un séparateur, sur les versions récentes.

À défaut, la combinaison GAUCHE, STXT, TROUVE fait le travail — laborieusement. C'est précisément le genre de tâche que l'outil de requête traité au chapitre suivant règle mieux.

JOINDRE.TEXTE / TEXTJOIN assemble une plage avec un séparateur, en ignorant les vides.

Les dates

FIN.MOIS / EOMONTH renvoie le dernier jour du mois, indispensable pour les échéanciers. NB.JOURS.OUVRES.INTL / NETWORKDAYS.INTL permet de définir quels jours sont chômés — utile si vous travaillez le samedi. DATEDIF calcule un écart en années, mois ou jours révolus. Peu documentée, très utile pour les anciennetés.

Les fonctions d'information

ESTNA, ESTVIDE, ESTTEXTE, ESTNUM permettent de traiter les cas particuliers avant qu'ils ne produisent des erreurs.

SIERREUR reste utile, mais utilisez-le en dernier, jamais pour masquer une erreur que vous n'avez pas comprise.

Les noms et les tableaux structurés

Sur un classeur avancé, deux habitudes changent la lisibilité :

Nommer les constantes et les plages. Taux_TVA plutôt que $B$1.

Convertir les données en tableaux structurés. Les formules deviennent Ventes[Montant], les plages grandissent seules, et les références restent justes quand on ajoute des lignes.

Sur un classeur qui doit durer, ces deux habitudes valent plus que la connaissance de vingt fonctions.

La règle de complexité

Si une formule dépasse trois lignes ou trois niveaux d'imbrication, découpez-la en colonnes intermédiaires.

Une formule illisible est une formule que personne ne corrigera — vous compris, dans six mois. Les colonnes intermédiaires peuvent être masquées ; elles se relisent quand il le faut.