This is an excerpt from Pierre Leclerc (www.excel-vba.com)

 

 

Leçon 7 de 23: Introduction et astuces générales sur les fonctions et formules Excel

Pour devenir un expert, il ne s'agit pas de maîtriser toutes les fonctions d'Excel mais d'apprendre à assembler celles dont on a besoin pour créer de puissantes formules. Voyez la liste des 56 fonctions importantes d'Excel à la leçon 30.

Même les experts de PLI Consultant Inc. qui travaillent sur des centaines de projets chaque année n'utilisent que 20% des 17 signes et 328 fonctions offertes par Excel.

On découvre les fonctions et formules d'Excel comme on apprend une nouvelle langue. Les fonctions sont les mots et les formules sont des phrases. Si vous pouvez le dire en français, il existe une formule pour le dire à Excel. Par exemple, vous pouvez ne connaître que quatre mots en français et pouvoir dire: Être ou ne pas être

L'assistant formules

Pour saisir une formule dans une cellule vous pouvez l'écrire manuellement ou vous pouvez utiliser l'assistant formules. L'assistant formules est un outil intéressant pour découvrir de nouvelles fonctions et se familiariser avec ses arguments. L'utilisateur devient cependant rapidement limité. En effet, il est impossible de combiner des fonctions avec l'assistant et c'est dans cette combinaison de fonctions que réside la puissance d'Excel. Pour développer les formules les plus importantes SOMMEPROD et INDEX/EQUIV, l'assistant est totalement inutile.

Pour accéder à l'assistant formules, vous pouvez aller au menu "Insérer/Formule" ou cliquer sur l'icône . La fenêtre de dialogue suivante apparaît alors pour vous permettre de choisir une fonction:

Quand vous avez sélectionné une fonction, une autre fenêtre ouvre pour vous permettre de soumettre les arguments nécessaires:

Quand vous cliquez dans une des boîtes de texte, un court texte plus bas vous indique ce qui est attendu comme argument.

Trois mises en garde:

1- L'assistant formule ne vous offre pas la fonction DATEDIF qui est pourtant très utile pour calculer des intervalles entre deux dates (jours, semaines, mois ou années)

2- L'assistant formule devient inutile quand vous voulez utiliser plus d'une fonction dans votre formule

3- Pour réaliser des miracles avec la plus utile et puissante fonction d'Excel, SOMMEPROD, vous ne pouvez pas utiliser l'assistant formule qui semble totalement ignorer la versatilité de cette fonction

Autres astuces

- Supposons que vous avez des nombres dans les cellules A1 à A5000 et dans B1 à B5000. Dans la cellule C1 vous inscrivez la formule =A1+B1. Bougez le curseur vers le coin inférieur droit de la cellule C1 et quand celui-ci devient un signe plus (+), double cliquez et la formule est copiée de C1 à C5000. C'est l'auto-remplissage.

- Quand vous commencez à travailler avec de longues formules, il devient souvent intéressant de copier des parties de ces formules. Pour réaliser cette opération, vous devez d'abord sélectionner la partie à copier dans la barre de formule, ensuite vous cliquez sur l'icône de copie (ou CTRL c), vous DEVEZ ensuite cliquer sur ENTRÉE. Vous vous déplacez vers la cellule de destination et vous cliquez sur l'icône de collage (ou CTRL v (contrôle velcro)).

- Pour saisir la même formule dans plusieurs cellules, sélectionnez-les, saisissez votre formule dans la barre de formule, tenez la touche CTRL enfoncée et cliquez sur ENTRÉE. Si vous utilisez une adresses relative (A1), elle s'adaptera pour chaque cellule alors que si vous utilisez des adresses absolues ($A$1) une seule et même formule apparaîtra dans toutes les cellules.

- Pour ajouter des signes de dollars ($) aux adresses afin de les rendre absolues, cliquez sur celle-ci dans la barre de formule et utilisez la touche F4. Le premier clic rend la colonne et la ligne absolues, le deuxième rend seulement la ligne absolue, le troisième rend seulement la colonne absolue alors que le quatrième ramène l'adresse relative. Vous pouvez sélectionner l'ensemble de la formule ou plusieurs adresses en même temps dans la formule avant de cliquez sur la touche F4. Voyez la leçon 16 pour comprendre les notions de références absolues et relatives,

- Une formule peut faire référence à une cellule d'un classeur qui réside sur Internet:
='http://www.simple-excel.com/[excel-enligne.xls]Rapport1'!D8

- Le résultat de la formule =2+3*5 est 17 puisque Excel exécute les multiplications avant les additions. Le résultat de la formule =(2+3)*5 est 25 puisque Excel exécute d'abord les opérations de la parenthèse la plus intérieure. Pour éviter ce type d'ambiguïté, ABUSEZ des parenthèses. Par exemple, si vous devez additionner les cellules A1 et A2 et ensuite ajouter la somme des cellules B1 et B2, vous pourriez simplement écrire =A1+A2+B1+B2. À la place inscrivez =(A1+A2)+(B1+B2). Servez-vous des parenthèses pour préciser l'opération et pour rendre vos formules plus faciles à lire. Quand vous parlez à un individu ou à Excel, assurez-vous d'être clair et complet.

- Une formule Excel commence toujours par le signe égal (=). Si vous commencez la formule par un + ou un -, c'est que vous êtes un dinosaure qui a commencé sa carrière avec Lotus 123 et Excel se chargera d'ajouter un signe = au début de votre formule.

- Il n'est pas nécessaire de saisir manuellement tous les caractères dans une formule. Ainsi, dans la cellule A1, saisissez le signe égal (=), sélectionnez la cellule A2 et cliquez sur ENTRÉE. La formule dans la cellule A1 est alors =A2. Dans la cellule A3, écrivez =SOMME( , sélectionnez les cellules B2 à B5 et cliquez ENTRÉE. La formule devient =SOMME(B2:B5). Vous pouvez ainsi facilement écrire des formules que font référence à des cellules d'autres feuilles et même d'autres classeurs. Cette astuce deviendra très importante quand vous commencerez à développer des formules référant à des ensembles de cellules comme SOMMEPROD et INDEX/EQUIV.

- Si vous n'écrivez pas correctement le nom d'une fonction, un message d'erreur #NOM? apparaît dans la cellule quand vous cliquez sur "Entrée". Corriger le nom de la fonction. Le même message d'erreur apparaît quand vous utilisez une chaîne de caractère (texte) sans l'entourer de guillemets. ex. : =Pierre retournera un message d'erreur alors que ="Pierre" sera accepté.

- Une formule ne peut contenir plus de 1 024 caractères incluant tous les signes.

- Quand vous écrivez une formule qui réfère à une autre feuille, l'adresse de la cellule et le nom de la feuille sont séparés par un point d'exclamation (!) :
=autreFeuille!B9
Cependant si le nom de la feuille comprend autre chose que des lettres, des nombres ou le signe souligné (_), le nom de la feuille sera entouré d'apostrophes :
=autre_feuille!B9
='autre feuille'!B9
='autre-feuille'!B9

- Il est préférable de ne pas créer de liens entre les classeurs. Cependant, quand vous écrivez une formule référant à un autre classeur, le nom du classeur et son suffixe (.xls) doivent être entre parenthèses carrées et le nom de la feuille doit y être :
=[autreClasseur.xls]feuille1!B9
Cependant si le nom de la feuille comprend autre chose que des lettres, des nombres ou le signe souligné (_), le nom de la feuille sera entouré d'apostrophes :
=[autreClasseur.xls]autre_feuille!B9
='[autreClasseur.xls]autre feuille'!B9
='[autreClasseur.xls]'autre-feuille!B9

Le nom du classeur peut contenir des espaces et des caractères spéciaux sans qu'il soit nécessaire d'utiliser des apostrophes si le nom de la feuille n'en contient pas.
=[autre Classeur.xls]autreFeuille!B9

- Parfois quand vous ouvrez un classeur Excel qu'un collègue vous a fait parvenir, le message suivant apparaît:

Ce message signifie qu'une formule dans le classeur fait référence à une cellule d'un classeur qui est introuvable. Pour éviter ce problème, il existe deux précautions à prendre:

1- Ne développez jamais de formules qui font référence à d'autres classeurs,
2- Quand vous copiez une feuille entière d'un classeur à l'autre faites disparaître les liens externes par un "collage spécial valeurs".

Pour régler ce problème il existe deux solutions:

1- Effectuez un Copie/Collage spécial/Valeurs sur toutes les feuilles du classeur.
2- Allez au menu "Edition/Liens" et identifiez le nom du classeur externe. Recherchez ce nom dans la feuille et corrigez la formule.

- Une formule ne peut faire référence à elle-même ou à une autre cellule dont la formule réfère à elle-même. Ainsi, si dans la cellule A1 vous écrivez =A1 ou =A2+A3 alors que la formule dans A3 est =A1, Excel vous préviens de la présence d'une référence circulaire. Corrigez la formule ou activez la fonctionnalité d'itération (Outils/Options/Calcul). Excel recalculera un certain nombre de fois la formule et retournera un résultat.

left arrow Retour à la page d'accueil