L’opérateur magique qui met à jour vos formules Excel tout seul (fini le copier-coller !)

Dans ce tutoriel, je vais vous montrer comment écrire une seule formule dans Excel pour calculer toute une colonne — sans jamais tirer la poignée de recopie — et surtout comment faire en sorte que cette formule s'adapte automatiquement dès qu'une nouvelle ligne est ajoutée, sans que vous n'ayez à toucher quoi que ce soit.

Si vous avez déjà passé du temps à recopier une formule sur 50 lignes et découvert le lendemain qu'un collègue en avait écrasé une par erreur, ce tutoriel est fait pour vous.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons utiliser le tableau suivant dans lequel nous retrouvons le suivi des commandes clients d'une boutique de sport en ligne.

Pour chaque commande, nous disposons d'une référence, d'un produit, d'une quantité, d'un prix unitaire, et d'une colonne « Total » que nous allons calculer.

L’opérateur « . » que je vais vous présenter dans cette vidéo permet de construire ce que l’on appelle une formule matricielle.

Avant de commencer, il est donc utile de comprendre ce que signifie une formule matricielle dynamique dans Excel 365.

Contrairement à une formule classique qui produit un seul résultat dans une seule cellule, une formule matricielle dynamique peut retourner plusieurs valeurs à la fois et les « déverser » automatiquement dans les cellules voisines.

Ce comportement s'appelle le déversement (ou « spill » en anglais). Toute la puissance que nous allons exploiter dans ce tutoriel repose sur ce mécanisme.

2. Le problème de la poignée de recopie

   2.1 La méthode classique et ses limites

Pour ce tutoriel, nous allons souhaiter le montant total de chaque vente du tableau.

Nous allons donc ajouter une nouvelle colonne :

Ensuite, pour déterminer ce montant, nous insérons en E7 la formule suivante :

  =C7*D7 

Pour étendre sur toutes les cellules de la colonne, il nous devons encore sélectionner la cellule E7 et « tirer » la poignée de recopie (le petit carré en bas à droite de la cellule) jusqu'à E14.

Excel recopie alors la formule sur 8 lignes.

Le résultat semble correct à première vue. Mais cette approche cache trois problèmes concrets.

• Le premier problème est l'absence de protection. Si un collègue tape une valeur manuellement dans une cellule de formule — par accident ou parce qu'il ne sait pas qu'une formule s'y trouve — Excel ne signale rien. La formule disparaît silencieusement, et les totaux deviennent faux sans le moindre avertissement.
• Le deuxième problème est le manque de dynamisme. Si une nouvelle commande est ajoutée en ligne 15, la formule ne s'y trouvera pas automatiquement. Il faudra penser à l'y recopier manuellement.
• Le troisième problème est la maintenance. Si la logique de calcul évolue (par exemple, appliquer une remise de 10 % sur chaque ligne), il faudra modifier toutes les cellules une par une, ou re-tirer la formule.

   2.2 La tentative avec une plage élargie

Une première idée pour rendre la formule plus dynamique serait d'écrire en E7 :

  =C7:C100*D7:D100 

En validant avec [Entrée], Excel va déverser les résultats sur toutes les lignes de 7 à 100.

Cela fonctionne, mais un problème immédiat apparaît : les lignes 15 à 100, qui ne contiennent aucune donnée, affichent un tiret ou un zéro.

Notre colonne Total est polluée par des résultats parasites.

C'est exactement ce problème que la fonction SUPPR.PLAGE — et son équivalent compact, l'opérateur point — va résoudre.

3. SUPPR.PLAGE : rogner automatiquement les cellules vides

   3.1 Comprendre le principe

La fonction SUPPR.PLAGE prend une plage et la « rogne » pour ne conserver que les cellules réellement utilisées, en ignorant automatiquement les cellules vides au début ou à la fin.

Elle est particulièrement utile lorsque nous travaillons avec des plages larges prévues pour accueillir des données futures.

Sa syntaxe est la suivante :

  =SUPPR.PLAGE(plage ; [mode_lignes] ; [mode_colonnes]) 

Où :

• Le premier argument est la plage à rogner.
• Les deux suivants sont facultatifs et indiquent si l'on souhaite rogner le début, la fin ou les deux extrémités (valeur par défaut : les deux). Dans la très grande majorité des cas, nous n'avons pas besoin de les préciser.

   3.2 Application à notre tableau de commandes

Nous nous plaçons en E7 et nous saisissons :

  =SUPPR.PLAGE(C7:C100)*SUPPR.PLAGE(D7:D100) 

Nous validons avec [Entrée]. Excel affiche maintenant les totaux uniquement pour les 8 lignes remplies — sans aucun résultat parasite en dessous.

Et si nous ajoutons une nouvelle commande en ligne 15 (par exemple : CMD-009, Haltères 5 kg, 3, 39,90), la formule détecte automatiquement la nouvelle ligne et calcule son total, sans aucune intervention de notre part.

Attention, étant donné qu’il s’agit d’une formule matricielle, il est indispensable que les cellules de propagations permettent effectivement de se déverser.

C’est pourquoi, si quelqu'un tente de saisir une valeur manuellement dans l'une des cellules de résultat — par exemple E8 — Excel affiche immédiatement une erreur #PROPAGATION! pour indiquer qu'un obstacle bloque la formule.

C'est un signal d'alerte intégré, bien plus fiable que de simplement espérer que personne ne touchera à vos formules.

4. L'opérateur point : la syntaxe compacte

   4.1 Présentation

Écrire SUPPR.PLAGE deux fois pour deux colonnes fonctionne parfaitement, mais alourdit la formule.

Excel 365 propose une notation encore plus concise pour obtenir exactement le même résultat : l'opérateur point.

Le principe est simple : nous plaçons un point (.) directement dans la référence de plage, juste avant ou après les deux-points ( « : »), en fonction de la partie que nous souhaitons rogner.

Si nous voulons rogner la fin de la plage (les lignes vides du bas), nous écrivons :

  C7:.C100 

Le point se place entre les deux-points et le nom de la cellule finale.

Si nous voulons rogner le début de la plage (des lignes vides en haut), nous écrivons :

  C7.:C100 

Si nous voulons rogner les deux extrémités, nous combinons :

  C7.:.C100 

   4.2 Réécriture de notre formule

Nous retournons en E7 et nous remplaçons la formule précédente par :

  =C7:.C100*D7:.D100 

Le résultat est strictement identique à la version avec SUPPR.PLAGE, mais la formule est beaucoup plus lisible.

Le point placé avant C100 et avant D100 indique à Excel de rogner uniquement la fin de chaque plage — c'est-à-dire les lignes vides en bas.

5. Gérer les en-têtes avec EXCLURE

   5.1 Le problème des colonnes entières

Certains utilisateurs voudront aller encore plus loin et sélectionner toute la colonne C (C:C) plutôt qu'une plage limitée à C100. L'idée est séduisante : la formule deviendrait alors vraiment illimitée.

Nous modifions notre formule ainsi :

  =C.:.C*D.:.D 

En validant, Excel affiche une erreur #VALEUR! sur la première ligne.

La raison est simple : la ligne 6 contient l'en-tête « Quantité », qui est un texte, et Excel ne peut pas multiplier un texte par un nombre. La ligne de titre est incluse dans le calcul alors qu'elle ne devrait pas l'être.

   5.2 Utiliser EXCLURE pour ignorer l'en-tête

La fonction EXCLURE (DROP en anglais) nous permet de supprimer un nombre défini de lignes au début ou à la fin d'une plage. Sa syntaxe est :

  =EXCLURE(tableau ; nb_lignes) 

Nous l'utilisons pour exclure la première ligne (l'en-tête) de chaque colonne. Notre formule finale en E7 devient :

  =EXCLURE(C.:.C*D.:.D;1) 

Cette formule combine deux mécanismes : l'opérateur point rogne les cellules vides de début et de fin et EXCLURE retire l'en-tête.

Elle est entièrement dynamique : aucune mise à jour nécessaire, quelle que soit la taille du tableau.

   5.3. Appliquer l'opérateur point sur les lignes avec PARCOL

Maintenant, imaginons que nous souhaitions calculer automatiquement le montant total des ventes avec une seule formule.

Pour cela, il nous suffit de reprendre exactement la même formule, mais encapsulée dans une fonction SOMME :

  =SOMME(EXCLURE(C.:.C*D.:.D;1))