Simplifier la compréhension des formules : une obligation pour une utilisation optimale d’un fichier

Sommaire

Différence entre formule et fonction dans Excel :

Formule : calcul créé par l’utilisateur et exécuté par Excel. Il n’y a pas de syntaxe particulière pour une formule hormis le fait de commencer par le signe =

Fonction : élément mis à disposition par Excel, exécutant un calcul spécifique en fonction des arguments saisis entre parenthèses. Une fonction a une syntaxe à respecter. Voir PDF à télécharger en bas de l'article. 

 

Une formule peut contenir une ou plusieurs fonctions.

Constat actuel des fichiers en entreprises :

Un fichier Excel est utilisé entre 5 et 10 ans, en moyenne dans une entreprise, souvent par plusieurs utilisateurs ayant des niveaux très hétérogènes sur l’utilisation numérique en général et Excel en particulier.

 

Mais que se passe-t-il durant ces 10 années d’utilisation ?

·      Le concepteur du fichier n’est plus un de ses utilisateurs 2 ou 3 ans plus tard (changement de fonction, d’entreprise…).

·      Le ou les utilisateurs reprenant le fichier n’ont aucune notice d’utilisation, n’ont aucune formation sur le fichier. Ils doivent, avec plus ou moins de connaissances, deviner le fonctionnement du fichier et la conception des formules contenues.

·      Le fichier devient une usine à gaz où tout le monde y va de sa petite création, modification pour essayer de faire au mieux.

 

Conséquences :

·      Perte de temps à essayer de comprendre un fichier.

·      Risque d’erreurs dans les manipulations pour« se dépatouiller ».

·      Baisse de fiabilité dans les données ou les indicateurs qui en découlent.

·      Création en parallèle d’autres fichiers pour compenser.

Maintenant imaginez…

Imaginez que vous êtes un utilisateur d’Excel peu à l’aise avec cet outil.

Imaginez qu’on vous mette entre les mains ce fichier sans aucune explication :

 

 

Imaginez que dans ce fichier, le calcul de la date de livraison en colonne P est la suivante :

 

Quelle serait votre réaction ? Combien de temps passerez-vous à essayer de la comprendre ?

Un utilisateur peu à l’aise avec Excel, ne connaît généralement pas les fonctions SERIE.JOUR.OUVRE.INTL, RECHERCHEX et INDIRECT. Si on rajoute à cela, la difficulté des plages de cellules figées avec les dollars, on met l’utilisateur en échec total sur la compréhension de la formule.

 

Imaginez que vous avez un nouveau pays de livraison à ajouter. Comment intégrer les nouvelles données ? Comment modifier une formule de calcul qu’on ne comprend pas ?

Comment simplifier la compréhension d’une formule ?

Etape 1 : utiliser les tableaux structurés

Le tableau structuré possède plusieurs avantages :

·      Mise en forme automatique lors de l’ajout de lignes ou colonnes.

·      Recopie automatique des formules de calcul dans une colonne.

·      Mise à jour automatique des graphiques associés.

·      Mise à jour automatique de tous les tableaux croisés dynamiques associés via l’onglet Données | Actualiser Tout.

 

Concernant la compréhension d’une formule, son avantage est d’utiliser le nom des colonnes plutôt que les références des cellules.

Explication de la syntaxe avec la formule pour le Prix Total :

·      Les signes [ ] symbolisent la colonne.

·      Le signe @ symbolise la valeur sur la ligne du tableau.

Prix total = la valeur de la colonne Quantité sur la ligne actuelle * par la valeur de la colonne Prix Unitaire sur la ligne actuelle.

 

Vous pouvez constater qu’il y a une double paire de [ ] pour la colonne Prix Unitaire. La raison est simple : l’intitulé de la colonne contient un espace. Les [ ] extérieurs symbolisent la colonne. Les [ ]intérieurs englobent l’intitulé.

 

Pour information, en validant la formule avec la touche Entrée, la formule se recopie automatiquement sur toute la longueur de la colonne.

 

Comparaison avec un tableau non structuré utilisant les références des cellules

Les références de cellules ne reprennent pas le nom des colonnes. Avec des formules plus complexes, cela ressemble beaucoup à la« bataille navale » d’après les utilisateurs peu à l’aise sur Excel.

De plus, en validant la formule avec la touche Entrée, la formule ne se recopie pas automatiquement sur la longueur de la colonne.

Etape 2 : nommer les cellules en dehors d’un tableau structuré plutôt que d’utiliser les $

Dans certaines formules, il y a des « cellules figées » par des dollars. Cependant leur utilisation requiert un bon niveau de connaissance sur Excel et la codification complexifie la compréhension de la formule.

Explication des références de cellules.

·      B2 : la cellule B2 n’est pas du tout figée. C’est une référence relative.

·      $B$2 : la cellule B2 est complètement figée. C’est une référence absolue.

·      $B2 : seule la colonne de la cellule B2 est figée. C’est une référence colonne semi-absolue.

·      B$2 : seule la ligne de la cellule B2 est figée. C’est une référence ligne semi-absolue.

 

Pour information, il faut faire la touche F4 ou la combinaison de touches Fn F4 pour placer les dollars correctement.

 

Il est donc préférable de nommer les cellules en passant parla zone Nom ou par l’onglet Formules puis Définir un nom.

 

Exemple avec l’utilisation d’un taux fixe au-dessus d’un tableau.

Cas n°1 : la cellule contenant le taux 20 % est nommée TauxTVA

 

Cas n°2 : la cellule contenant le taux 20 % est en référence absolue avec les $

Etape 3 : expliquer !

En écrivant une « notice », il est plus facile de comprendre ce qui a été pensé. Cette notice peut se trouver dans une autre feuille du classeur Excel. Ou même dans un document Word. Cette notice peut aussi se faire au-dessus des tableaux.

 

Etape 4 : aller à la ligne dans la barre de formule

Cette étape est à réaliser lorsque les formules sont longues, complexes, pour faciliter la lecture.

Pour aller à la ligne dans une barre de formule, il faut faire la combinaison de touches ALT ENTREE.

Maintenant imaginez l’exemple du départ…

Avec la mise en place des conseils préconisés, voici à quoi ressemblerait le fichier Excel.

 

 

Chaque tableau est sous forme de tableau structuré.

Une notice est écrite pour chacun des tableaux, expliquant à quoi chaque colonne sert.

Une explication est faite pour le calcul de la date de livraison dans le tableau T_LIVRAISON.

 

Voici ce qu’un utilisateur obtient pour la formule de calcul de la date de livraison.

La formule de calcul est correctement présentée dans la barre de formule.

 

Conclusion

Simplifier une formule pour une meilleure compréhension, prend du temps et nécessite un sens de l’organisation dans son fichier Excel. Cependant il faut le considérer comme un investissement et non une perte de temps.

Pour rappel, le fichier que vous allez modifier est utilisé pendant environ 10 ans. 10 ans durant lesquels vos collaborateurs feront moins d’erreurs parce qu’ils comprendront l’outil qu’ils utilisent.

Télécharger le PDF
ordinateur avec main sur Excel
Logo Solpedinn

Envie de passer un cap sur Excel ?

Commencer

Stéphanie

Formatrice bureautique depuis janvier 2016, Stéphanie a pour ambition de développer la zone de génie qui est en chaque utilisateur Excel (débutant jusqu’à confirmé) pour se simplifier la vie. Elle a intégré l’équipe des formateurs de Solpedinn en Juin 2024.

ensemble de l'équipe Solpedinn, organisme de formation qui forme à Excel

Votre formation, notre priorité

Toute l'équipe de Solpedinn se plie en 4 pour vous proposer des formations aux outils d'analyse de données (Excel, Google Sheet, Power BI...). Qu'importe votre niveau de base, vos attentes ou vos besoins, nous avons sans aucun doute la formation qu'il vous faut.

Logo Solpedinn

Envie de passer un cap sur Excel ?

Commencer

D'autres articles pour vous

No items found.
Logo Solpedinn

Inscrivez-vous à notre newsletter pour recevoir les nouveaux contenus

Message bien reçu ! Notre équipe vous recontactera très vite.
Une erreur est survenue. Merci d'essayer à nouveau.