Tableur 3 : formule magique

Tableur 3 : formule magique

Rentrons maintenant dans les calculs savants d’Excel. Tu as demandé à tes contacts leur adresse et tu voudrais simplement le département, à partir du code postal. Dois tu tout taper ? Non ! Tant de choses pour simplifier ce genre de tâches !

tableur

 

 

Les formules

Tout contenu de cellule est une formule. Une formule, ou formule interprétée affichera un résultat différent de ce qui aura été entré dans la cellule. Elle commence par =. Les espaces ont une signification particulière : l’interserction entre deux plages. Aussi, sauf dans un champ alphanumérique borné par des guillemets, si il n’y a pas besoin de la fonction d’intersection il ne faut pas en mettre. Je parlerais de formule excel même pour Calc par exemple. Il en existe de deux types : les opération et les fonctions.

Les opérations

Les opérations sont des calculs avec les opérateurs arithmétiques utilisés conventionnellement en maths. Exemple : =A2+A3. Les opérations impossibles en maths le sont tout autant ici : par exemple la division par 0. Quelques opérateurs : + – / * ^ > <

> Exercice : plateforme/Exercices/Tableur pour le debutant/somme multiplication division soustraction/exo VA.xls

Les fonctions

Les fonctions d’Excel sont des mots réservés pour obtenir facilement un résultat élaboré. Toutes les fonctions d’Excel sont composées d’un opérande pour signifier la fonction utilisée, suivi de parenthèses entre lesquelles on précise le ou les arguments, alors séparés par un opérateur d’intersection comme ; ou :.
Quelques fonctions n’ont pas d’arguments : on tape alors 2 parenthèses collées.
> Exercice : plateforme/Exercices/Tableur pour le debutant/somme multiplication division soustraction/multiplication_addition.xls

La Syntaxe

= début d’une formule
SOMME opérande, code de la formule.
() espace dans lequel sera tapé les arguments ou critères auxquels vont s’appliquer la fonction.
$ bloquer lors d’une duplication.
; dans =SOMME(A2;A5) additionner A2 et A5
: dans =SOMME(A2;A5) additionner de A2 à A5 (A2 + A3 + A4 + A5)

Premières fonctions

Addition =SOMME(A2;A3)

Multiplication =PRODUIT(A2;A3)
Division =QUOTIENT(A2;A3)
Chiffre maximum =MAX(A2;A5)
Chiffre minimum =MIN(A2;A5)
Moyenne =MOYENNE(A2;A5)
Attention, pour Drive les formules son en anglais. Exemple :
Somme > Sum

La référence

Les formules peuvent utiliser des valeurs tapées ou reprises d’autres cellules. Dans ce cas lors d’une incrémentation la référence à la cellule est relative par défaut : elle va se déplacer de la même façon. Pour créer un cellule référence absolue, il faut ajouter l’opérateur $. On peut bloquer le déplacement en horizontale ($ devant la lettre qui correspond à la colonne) et à la verticale ($ devant le chiffre qui correspond à la ligne). Si on ne fait que l’un on parle de référence mixte.

Vidéo du site netprof EXCEL : Cellule référence.

> Exercice : plateforme/Exercices/Tableur pour le debutant/ref_absolues/somme_reference absolue.xls

Référence à une autre feuille ou classeur

On peut vouloir faire référence à une cellule ou une plage contenue dans une autre feuille ou page. Pour faire référence à la cellule C33 de la Feuille 2 du même Classeur la syntaxe sera celle-ci :

=Feuil2!C33

On peut faire référence à une même plage sur plusieurs feuille par exemple pour faire une recherche sur plusieurs tableaux construits de la même façon (ou en partie) mais avec des catégories différentes (tableau client en feuille 1, tableau prospect en feuille 2, tableau fournisseur en feuille 3). Pour l’union des plages B3:B5 des trois feuilles la syntaxe sera :

=Feuil1:Feuil3!B3:C5

On peut donc faire des calculs sur plusieurs feuilles. Cela peut être utile pour consolider les comptes de plusieurs sociétés ou utiliser une donnée de plusieurs fiches client.

=SOMME(Feuil1:Feuil20!B15)

Pour faire référence à une autre classeur (un autre fichier) lorsque que celui-ci est ouvert, le nom entre crochets sera ajouté. Donc pour la cellule A3 de la Feuille 1 du Classeur 1 ouvert la syntaxe sera :

=[Classeur1.xls]Feuil1!A3

Par contre, fichier fermé, il faudra indiquer sa localisation sur le disque dur. Si Classeur 1 est dans Mes Documents la syntaxe sera :

=’C:\Users\LauT\Documents\[Classeur1.xls]Feuil1!’A3

Si votre fichier étais ouvert et que vous avez utilisé la première solution, plus simple, la syntaxe sera automatique convertie à la fermeture.

La fonction SI

La fonction SI est issue de la base de la logique Booléenne qui sert à la programmation informatique.
Par exemple : bouton enfoncé est une condition booléenne et lumière allumée une variable booléenne. Si bouton enfoncé alors lumière allumée sinon lumière allumée.

La syntaxe de la fonction SI est la suivante :
=SI(condition;réponse ou action si vraie;réponse ou action si faux)
Si on applique l’exemple(impossible dans excel bien sur)
=SI(bouton enfoncé;lumière allumée;lumière éteinte)

> Exercice : Plateforme/Exercices/Tableur perfectionnement/si/Fonction SI.zip/Si 2.xls

Prenons l’exemple de l’exercice, applicable à excel.
Dans la colonne « Escompte », si le montant de la commande client est supérieur à 10 000 Euros afficher « OUI » sinon afficher « NON ».
=SI(case>10000;OUI;NON)
Essayer d’afficher le montant de l’escompte dans la colonne D. Il est égal à 2% de la commande, seulement si la commande excède 10 000 euros !

Cela nous montre que l’on peut mettre des calculs dans les arguments. On peut aussi y mettre des fonctions ! Il s’agit des fonctions imbriquées.

Si vous devez revoir : vidéo sur la plateforme Excel : la fonction logique SI

Les fonctions imbriquées

Les fonctions dans des fonctions sont donc des fonctions imbriquées. I peut s’agir comme dans l’exemple de ce que nous allons prendre de SI, ou de toute autre formule. Ce peut être les même opérande ou non. Il peut y en avoir autant que d’argument c’est à dire une infinité. Il faudra bien sur prendre garde à la syntaxe et à bien refermer toutes les parenthèses.
=SI(C1=1; »rouge »; SI(C1=2; »rouge »;SI(C1=3; »rouge »;SI(C1=4; »rouge »; »vert »))))
Si on décompose :
=SI(C1=1; »rouge »;                                                                                               )
                               SI(C1=2; »rouge »;                                                                 )
                                                          SI(C1=3; »rouge »;                                     )
                                                                                    SI(C1=4; »rouge »; »raté »)

> Exercice : Plateforme/Exercices/Tableur perfectionnement/si/SI imbriqués.zip/Si imbriqués 3.xls

> Exercice : Plateforme/Exercices/Tableur perfectionnement/si/SI imbriqués.zip/Si imbriqués.xls

Les fonctions ET et OU

Toujours tirées de la logique Booléenne, ET est une fonction de conjonction qui est utilisée partout (par exemple dans les moteurs de recherche et en programmation). Le résultat de la fonction est VRAI si et seulement si a est VRAI et b est VRAI. OU (inclusif)est une fonction de disjonction également très utilisée. Le résultat est VRAI si et seulement si a est VRAI et/ou b est VRAI (à ne pas confondre avec le OU exclusif : l’un ou l’autre mais pas les deux).
Ce sont deux fonctions qui ne s’utilisent pas seules car elle n’ont que des arguments de critère et non de validation. Elle peuvent donc s’utiliser dans une fonction SI pour en simplifier l’usage. La syntaxe est :
ET(argument1;argument2;…)
OU(argument1;argument2;…)
On peut mettre autant d’arguments que l’on veut. Ainsi au lieu d’écrire :
=SI(C1=1; »rouge »; SI(C1=2; »rouge »;SI(C1=3; »rouge »;SI(C1=4; »rouge »; »vert »))))
On mettra :
=SI(OU(C1=1;C1=2;C1=3;C1=4);rouge;vert)
C’est plus simple ! A vous de jouer !
> Exercice : Plateforme/Exercices/Tableur perfectionnement/si_et_ou/SI_SIET_SIOU.xlsx 

L’assistant fonctions

Comme il existe de nombreuses fonctions que nous ne connaissons pas toutes par cœur, l’assistant fonction nous aide. Dans la première fenêtre il trie les fonctions par type, nous propose une fenêtre de recherche de fonction par mots clés et nous propose une aide avancée. Lorsque vous avez choisi la fonction il vous aide dans le choix de chacun de vos arguments. Cela permet, en ayant une compréhension globale de la syntaxe à pouvoir utiliser toutes les fonctions voulues. Certaine permettent de combiner plusieurs fonctions ou de simplifier un fonction imbriquée très compliquée. Parfois une petite recherche internet ou sur l’aide permettra de gagner énormément de temps.
À savoir : Par défautExcel met à jour automatiquement les formules par exemple lors desincrémentations. Lorsque ce n’est pas le cas c’est parce que votre fichier est enregistré en mode manuel. Cette option se change depuis l’onglet Formule, Options de Calcul, à côté de l’option pour afficher les formules plutôt que leurs résultats.Excel ne peut pas ouvrir en même temps des fichiers avec des Options de calcul différentes : le paramétrage du premier prend la main sur les autres.

Les plages de cellules

Jusqu’alors, nous avons vu le système de coordonnées des cellules pour désigner les informations de vos tableaux. Les plages de cellules peuvent les remplacer. Cela est particulièrement pertinent losrque l’on souhaite faire des références entre des feuilles différentes d’un même Classeur.

Nommer une plage de cellules

Prenons l’exemple de ce tableau listant la consommation mensuelle de fruits et de légumes sur une année. La consommation sur une année de fraises se calcule ainsi :

=SOMME(H5:H16).

Pour y voir plus clair, Excel vous permet de donner des noms à vos plages de cellules et de les utiliser ensuite dans vos formules. Ici :

=SOMME(Fraises)

Pour ce faire, sélectionnez la plage de cellules à laquelle vous souhaitez donner un nom. Ouvrez l’onglet Formules du ruban. Cliquez sur le bouton Définir un nom. Donnez un nom à la plage de cellules, ici Fraises et cliquez sur OK.Attention à la casse.

Vous pouvez créer de la même façon autant de plages nommées que vous le souhaitez. Elles peuvent même se chevaucher. Dans notre exemple, nous créons des plages nommées pour chaque fruit, chaque légume et chaque mois de l’année.

Localiser un nom dans une feuille

Par défaut, les noms des plages ne sont pas affichés. Pour sélectionner une plage, dans l’onglet Formules du ruban, cliquez sur le bouton Gestionnaires de noms. Dans la fenêtre qui s’affiche et qui liste toutes les plages nommées, cliquez sur le nom de la plage à sélectionner. Cliquez dans le champ Fait référence à : les cellules de la plage nommée sont sélectionnées dans votre classeur.

Le gestionnaire de noms vous permet aussi de supprimer ou modifier les noms de plages.

Utiliser un nom dans une formule

Sélectionnez une cellule vide pour insérer une formule. Saisissez la formule=somme(choux) pour calculer la somme de toutes les cellules de la plage « choux ». Appuyez sur la touche Entrée pour afficher le résultat.

Exploiter l’intersection de deux plages

Vous souhaitez connaître votre consommation d’oranges en avril ? Avec l’opérateur d’intersection, vous allez pouvoir récupérer la valeur située à l’intersection de deux plages.
Sélectionnez une cellule vide pour insérer une formule.
Saisissez la formule =Avril Oranges. Ici l’espace entre Avril et Oranges indique à Excel de retenir la valeur de la cellule placée à l’intersection des deux plages. Appuyez sur Entrée. Toutes les combinaisons sont possibles. Vous pouvez par exemple afficher la consommation de pêches et de bananes en Septembre avec la formule =SOMME(Pêches Septembre; Bananes Septembre).

Les plages et les feuilles

Les plages permettent de ne pas metionner la feuille de référence même si elle est différente, au sein d’un même classeur. Aussi, pour afficher la consommation sur une année de fraises sur une autre feuille nous passons de :
=SOMME(Feuil1!H5:H16)
à tout simplement :
=SOMME(Fraises)
D’un fichier à l’autre il faudra conserver le nom du fichier mais les plages nommées raccourcissent tout de même la formule. Donc pour afficher la consommation sur une année de fraises sur un autre Classeur ouvert nous passons de :
=SOMME([Classeur1.xls]Feuil1!H5:H16)
à
=SOMME([Classeur1.xls]Fraises)

Conclusion

Dense n’est ce pas ? Nous reviendrons aux autres (oui il y en a d’autres !) fonctions plus tard. Pour le moment nous allons nous changer la tête avec d’autres fonctionnalités plus ludiques du tableur, comme les graphiques !

prev-tableur2next-tableur4

2 thoughts on “Tableur 3 : formule magique

Laisser un commentaire