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 !
Sommaire
Les formules
Les opérations
> 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
Premières fonctions
Addition =SOMME(A2;A3)
Division =QUOTIENT(A2;A3)
Chiffre maximum =MAX(A2;A5)
Chiffre minimum =MIN(A2;A5)
Moyenne =MOYENNE(A2;A5)
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
> 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
L’assistant fonctions
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
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 !