2. Créer et utiliser des formules de calcul

Elaborez une formule

Il existe plusieurs manières de concevoir des formules dans Excel : utiliser des modèles prêts à l’emploi, passer par un assistant spécialisé ou les écrire entièrement à la main !

Utilisez les fonctions prêtes à l’emploi

La barre d’outils principale Standard (en haut de la fenêtre) possède des boutons d’accès direct à des formules simples, comme la somme (voir écran 1). Ainsi, pour totaliser le contenu d’une colonne
ou d’une ligne, sélectionnez la cellule placée en dessous de cette colonne ou à droite de cette ligne (il ne doit pas y avoir de ligne blanche entre les nombres et le total) et cliquez deux fois sur le bouton Somme automatique de la
barre d’outils. Si vous préférez obtenir la moyenne en bas d’une colonne de chiffres, faites un clic droit sur la flèche placée à droite du bouton Somme automatique. Dans la liste qui apparaît, sélectionnez Moyenne puis
cliquez deux fois sur le bouton Somme automatique.

Employez l’Assistant de fonctions

Vous ne savez pas quelle fonction utiliser ? L’Assistant de fonctions vous rend un double service : il vous aide à choisir celle qui vous convient et vous montre comment l’utiliser. Prenons un exemple fondé sur le
jeu de Loto : combien existe-t-il de tirages différents de six boules, sachant que le jeu en comporte 49 ? Dans la cellule B1, saisissez 49 et tapez 6 en
B2. Sélectionnez B3 et cliquez sur le bouton Insérer une fonction (il a la forme d’un ?x). L’assistant Insérer une fonction s’ouvre. Dans la liste Sélectionnez une
catégorie, choisissez Math et trigo, puis, dans la liste qui s’affiche en dessous, choisissez COMBIN. Lisez la brève description qui s’affiche et cliquez sur OK. Dans la nouvelle fenêtre, vous voyez que
la fonction COMBIN a besoin de deux valeurs. Cliquez dans le champ Nombre_éléments, puis sur la cellule B1. Enfin, cliquez dans le champ Nb_éléments_ choisis, dans la cellule B2 et
validez par OK (voir écran 3). Le résultat s’affiche en B3. Si, à la place du nombre, vous voyez une série de ‘ # ‘ s’afficher, élargissez la colonne en faisant glisser, à la
souris, le trait qui la sépare de sa colonne de droite.

Ecrivez vos propres formules

Si les formules prêtes à l’emploi ne vous suffisent pas, vous pouvez en créer de toute pièce, à condition de connaître quelques règles élémentaires dans l’utilisation des symboles et des opérations. Pour créer une formule
dans une cellule, sélectionnez-la et tapez le symbole = (toute formule d’Excel commence par ce symbole). Par exemple, pour faire la différence entre le contenu des cellules A3 et B2, écrivez
=A3-B2, et validez par Entrée. Le résultat s’affichera dans la cellule de la formule, et il changera automatiquement quand vous modifierez le contenu de l’une des deux autres cellules. Bien sûr, vous pouvez perfectionner à
l’envi vos formules, en combinant plusieurs cellules avec divers opérateurs, des parenthèses, etc. Mais vous n’êtes pas obligé de tout taper ; il est plus simple d’indiquer les références des cellules en les sélectionnant directement à la
souris. Ainsi, pour inscrire par exemple la formule =B3*(B5-B7)/C9, tapez =, cliquez sur la cellule B3, tapez *(, cliquez sur
B5 et ainsi de suite. Pendant l’écriture de la formule, Excel identifie chaque cellule par un code de couleur, visible dans la formule (voir écran 2) et dans un filet entourant la cellule : vous êtes donc sûr de ne pas
vous tromper.

Choisissez votre système de coordonnées

Par défaut, toute coordonnée de cellule insérée dans une formule placée dans une cellule (par exemple A1 ou B50) est relative. Cela signifie qu’Excel ne conserve pas en mémoire la position absolue
de la cellule ?” ses numéros de ligne et de colonne ?” mais son emplacement relatif par rapport à la formule qui l’utilise. Par exemple, si A10 contient la formule =A8+A9, Excel la mémorise, de façon interne,
comme ‘ la somme des deux cellules placées au dessus. ‘ Si vous copiez A10 en C10, cette dernière contiendra alors la formule =C8+C9. Logique : c’est toujours
la somme des deux cellules du dessus. Il est parfois indispensable de préciser à Excel que vous utilisez des coordonnées absolues. Ainsi, dans l’exemple ci-contre, vous avez noté, dans le champ B2:B5, les puissances de quelques
locomotives, exprimées en kilowatts. Vous voulez, dans la colonne C, exprimer cette même puissance en chevaux (CV). Or, un kilowatt vaut environ 1,36 CV. Inscrivez en B7 la constante 1,36
et, en C2, la formule =B2*B7. Si vous recopiez C2 sur le champ C3:C5 vous obtiendrez une série de zéros ! Pour voir la cause de l’erreur, double-cliquez sur C3 (la première
formule contenant un 0). Vous y lisez la formule =B3*B8. Excel a en effet considéré que votre formule étant déplacée vers le bas, toutes ses composantes devaient ‘ suivre ‘. Or, c’est vrai
pour B2 qui est devenu B3, mais cela ne l’est pas pour B7 (notre constante de conversion) qui est devenu B8 (une cellule vierge). Pour indiquer à Excel que B7 est une référence
absolue, tapez en C2 la formule =B2*$B$7 (il y a deux fois le symbole ‘ $ ‘). Validez et recopiez C2 sur le champ C3:C5 :
l’erreur est réparée. Notez que le symbole ‘ $ ‘ utilisé ici n’a rien à voir avec un quelconque format monétaire.

Déterminez des noms de champs

Une formule n’est pas toujours facile à lire, les coordonnées des cellules n’étant guère parlantes. La solution : donnez des noms à vos cellules ou à vos plages de cellules.

Nommez une plage de cellules

Sélectionnez une plage puis, dans la liste des noms (affichée en haut à gauche, juste au-dessus du titre de la colonne A), tapez le nom de votre choix, par exemple Dépenses (voir écran
1) et validez par Entrée. Désormais, chaque fois que vous sélectionnez cette plage, le nom Dépenses apparaîtra dans cette liste, et vice-versa. Attention : dans les noms de champs, Excel tient compte des caractères
avec accents (Depenses et Dépenses sont ainsi deux noms de champs différents).

Utilisez ce nom dans une formule

Vous voulez totaliser les cellules formant le champ Dépenses ? Tapez simplement la formule =Somme(Dépenses) et validez. Encore plus simple : tapez
=somme( puis appuyez sur F3. La fenêtre qui s’ouvre affiche tous les noms utilisés dans le classeur, par ordre alphabétique. Cliquez sur Dépenses, puis sur OK (voir écran 2).
Il ne vous reste plus qu’à fermer la parenthèse et à valider par OK.

Sélectionnez une plage nommée

Vous avez oublié à quelle zone correspond le nom Dépenses ? Déroulez Edition, Atteindre (ou appuyez sur F5). La fenêtre Atteindre s’ouvre. Cliquez sur Dépenses
puis sur OK : ce champ est sélectionné.

Visualisez des grandes plages nommées

Dans une feuille de calcul, vous avez attribué des noms de champs à de vastes plages de cellules. Pour en avoir un aperçu, donnez à votre feuille un taux d’agrandissement (zoom) de 39 % (ou une valeur plus faible). Pour
cela, tapez 39 dans la liste Zoom (là où vous lisez 100 %) et validez. Les noms s’affichent en surimpression sur le tableau (voir écran 3).

Atteignez un objectif chiffré

Vous avez créé une formule de calcul et vous voulez qu’elle atteigne un ‘ objectif ‘, autrement dit une valeur numérique déterminée. Pour y parvenir, la solution classique
consiste à faire varier le contenu d’une des cellules intervenant dans la formule jusqu’à obtenir le résultat souhaité. Simple, mais fastidieux, hasardeux et interminable, surtout quand il faut jongler simultanément avec plusieurs paramètres situés
dans des cellules différentes ! Mieux vaut utiliser la fonction Valeur cible, ce que nous illustrons par l’exemple ici illustré (voir écran). Votre magasin vend des gommes et des crayons, le prix unitaire de ces
articles étant inscrit dans les cellules B11 et B12. Vous calculez les recettes à partir des nombres d’articles vendus (en lignes 3 et 6). Vous voulez que le bénéfice total (la cellule
E9 en rouge) atteigne 2 600,00 ?. Pour cela, combien devez-vous vendre de gommes en février (en supposant que les autres cellules ne changent pas) ? En d’autres termes, quelle doit être la valeur en
C6 pour que la formule en E9 atteigne 2 600 ? Pour le déterminer, sélectionnez E9 et déroulez Outils, Valeur cible. La fenêtre Valeur cible s’affiche. Dans le champ
Valeur à atteindre, tapez 2 600 et, dans Cellule à modifier, tapez C6 et cliquez sur OK (voir écran). La réponse s’affiche : vous devez vendre 831 gommes (nombre
fractionnaire, arrondi à l’entier supérieur).

👉🏻 Suivez l’actualité tech en temps réel : ajoutez 01net à vos sources sur Google, et abonnez-vous à notre canal WhatsApp.

Etienne Oehmichen