EMENTO XCEL F C - pjece.free.frpjece.free.fr/.x/Cours-cEce1-Excel-Vs1a.pdf · Permet dans le cas d’une formule devant être recopiée, de figer certaines références de cellules

Ecole de Commerce Européenne - Lyon

Année 2012-2013 1ère année

MEMENTO EXCEL

Formules de Calculs

Auteur : Philippe Jurain Mai 2013 Vs 1.a

© P. Jurain – Memento Excel – vs 1.a – Mai 2013 1 n

Sommaire

1 LISTE DES NOTIONS ET DES FONCTIONS ABORDÉES .............................................................................. 2

2 RÉFÉRENCES RELATIVES / ABSOLUES AVEC LE $ ..................................................................................... 3

3 FONCTION SOMME .............................................................................................................................................. 4

4 FONCTIONS MIN ET MAX ................................................................................................................................... 5 4.1 MINIMUM ............................................................................................................................................................................ 5 4.2 MAXIMUM ........................................................................................................................................................................... 5 4.3 UTILISATION POUR LIMITER UNE VALEUR .................................................................................................................... 5

5 FONCTION MOYENNE ......................................................................................................................................... 6

6 COMPTAGE DE CELLULES : NB.VIDE(), NBVAL(), NB() ............................................................................ 7

7 COMPTAGE CONDITIONNEL AVEC NB.SI OU NB.SI.ENS ........................................................................... 8 7.1 NB.SI() : COMPTAGE AVEC CONDITIONS SIMPLES ...................................................................................................... 8 7.2 NB.SI.ENS() : COMPTAGE AVEC CONDITIONS MULTIPLES SIMULTANÉES OU NON .............................................. 9

8 SOMME, MOYENNE CONDITIONNELLE AVEC SOMME.SI, MOYENNE.SI, SOMME.SI.ENS, MOYENNE.SI.ENS ................................................................................................................................................ 10 8.1 SOMME ET MOYENNE AVEC UNE SEULE CONDITION ................................................................................................. 10 8.2 SOMME, MOYENNE AVEC CONDITIONS MULTIPLES SIMULTANÉES OU NON .......................................................... 10

9 FONCTION SI ........................................................................................................................................................ 11 9.1 FONCTION SI UNIQUE ..................................................................................................................................................... 11 9.2 FONCTION SI IMBRIQUÉE .............................................................................................................................................. 11

10 FONCTIONS ET ET OU ....................................................................................................................................... 12

11 FONCTION RANG() ............................................................................................................................................ 13 11.1 LES DEUX TYPES DE CLASSEMENT : CROISSANT / DÉCROISSANT ........................................................................... 13 11.2 APPLICATIONS À DES CALCULS DE RANG PLUS COMPLEXES .................................................................................... 13

12 CALCULS DE POURCENTAGE .......................................................................................................................... 14

13 COMPLÉMENTS ................................................................................................................................................... 15 13.1 QUELQUES RACCOURCIS CLAVIER EXCEL .................................................................................................................... 15 13.2 VIDÉOS SUR YOUTUBE ................................................................................................................................................... 15


1 Liste des notions et des fonctions abordées Fonctions

ET Dans un SI, permet d’exprimer des CONDITIONS SIMULTANEES

MAX Renvoie la valeur maximale d’une plage de cellules

MIN Renvoie la valeur minimale d’une plage de cellules

MOYENNE Renvoie la valeur moyenne des valeurs numériques d’une plage de cellules

MOYENNE.SI Filtre les cellules d’une plage suivant une condition donnée, et pour les cellules filtrées, calcule la moyenne.

MOYENNE.SI.ENS Filtre les cellules d’une plage suivant deux ou plusieurs condition données, et pour les cellules filtrées, calcule la moyenne. Les conditions doivent être simultanément vérifiables.

NB Compte dans une plage de cellules, le nombre de valeurs de type numérique.

NBVAL Compte dans une plage de cellules, le nombre de cellules non vides.

NB.VIDE Compte dans une plage de cellules, le nombre de cellules vides.

NB.SI Compte dans une plage de cellules, le nombre de cellules qui vérifient une condition donnée.

NB.SI.ENS Compte dans une plage de cellules, le nombre de cellules qui vérifient deux ou plusieurs conditions données. Les conditions doivent être simultanément vérifiables.

OU Dans un SI, permet d’exprimer des conditions pas forcément simultanées

RANG Renvoie le classement d’une valeur numérique par rapport à un ensemble de valeurs. Le classement peut être croissant ou décroissant.

SI Permet dans une cellule d’afficher deux valeurs distinctes suivant qu’une condition est vraie ou non.

SI imbriqué dans un SI

Permet dans une cellule d’afficher trois, quatre, etc valeurs distinctes suivant que deux, trois, etc… condition sont vraies ou faux.

SOMME Renvoie la somme des valeurs numériques d’une plage de cellules

SOMME.SI Filtre les cellules d’une plage suivant une condition donnée, et pour les cellules filtrées, calcule la somme.

SOMME.SI.ENS Filtre les cellules d’une plage suivant deux ou plusieurs condition données, et pour les cellules filtrées, calcule la somme. Les conditions doivent être simultanément vérifiables.

Notions

$ pour les références fixes

Permet dans le cas d’une formule devant être recopiée, de figer certaines références de cellules qui ne doivent pas être incrémentées par Excel lors de la recopie.

Pourcentages Répond à de nombreuses questions posées. Calculer un %, c’est forcéméent diviser deux nombres entre eux.


2 Références relatives / absolues avec le $ Contexte : Vous avez écrit une formule, et vous avez besoin de la recopier. La recopie de votre formule changera les n° de lignes des cellules si vous recopiez la formule sur une autre ligne et changera les n° de colonne si vous la recopiez sur une autre colonne. Si il est nécessaire que la formule initiale et la formule copiée fassent toutes deux références à une même cellule, il faut que celle-ci soit bloquée dans la formule initiale. C’est là qu’intervient le symbole $.

• Placé debant la lettre de colonne, il bloque celle-ci • Placé devant le n° de ligne, il bloque celui-ci.

Voici 4 exemples pour illustrer cette notion.

Retenir : Le $ n’a de sens que lorsque la formule doit être recopiée ET qu’une ou plusieurs cellules doit être figée.


3 Fonction SOMME Rôle : Additionner plusieurs cellules Syntaxe : = SOMME(plage_cellules) Exemple : =SOMME(A1 :A4) signifie additionner les cellules A1, A2, A3 et A4. C’est donc équivalent à A1+A2+A3+A4 Distinguer les symboles point-virgule et deux points La formule =SOMME(A1 ;A4) signifie additionner les cellules A1 et A4 uniquement. C’est équivalent à =A1+A4 Addition de plusieurs zones : =SOMME(B5:B9;F5:F9) est équivalent à =B5+B6+B7+B8+B9+F5+F6+F7+F8+F9 La fonction SOMME( ) admet jusqu’à 30 zones, séparées par des points-virgules. On peut aussi séparer les zones en plusieurs sommes. ✔ Voir l’exemple. Restons simple dans nos formules : L’usage est d’utiliser la fonction SOMME pour additionner les cellules d’une plage contenant au moins 3 cellules. Pour additionner simplement deux cellules, on conservera l’écriture simple n’utilisant que l’opérateur d’addition « + ». =A1+A2 est plus simple que =SOMME(A1 ;A2), n’est-ce pas ? Texte intrus dans la plage : Lorsque dans la plage, il y a une cellule contenant du texte, elle est ignorée.


4 Fonctions MIN et MAX

4.1 Minimum Rôle : renvoie la valeur minimale d’un ensemble de cellules. Syntaxe 1 : =MIN(plage_cellules) => renvoie la valeur minimale de la plage Syntaxe 2 : =MIN(A1;A3;B2) => renvoie la valeur minimale parmi les 3 valeurs de A1, A3 et B2

4.2 Maximum Rôle : renvoie la valeur maximale d’un ensemble de cellules. Syntaxe 1 : =MAX(plage_cellules) => renvoie la valeur maximale de la plage Syntaxe 2 : =MAX(A1;A3;B2) => renvoie la valeur maximale parmi les 3 valeurs de A1, A3 et B2

4.3 Utilisation pour limiter une valeur Utilisation pour limiter une valeur par le haut (Limite HAUTE) Votre prime sera de 10% de votre CA mais ne pourra pas dépasser 250 €. L’idée est de motiver le vendeur, tout en mettant une limite à la prime. Considérons que le CA est affiché en cellule A1. Formule de la prime : =MIN(10%*A1;250) Si A9 contient 2000€, la formule calcule MIN(10%*2000;250) , donc MIN(200;250) et le résultat sera de 200 €, qui est la valeur la plus faible. Si A10 contient 4000€, la formule calcule MIN(10%*4000;250) donc MIN(400;250) et le résultat sera 250€, plus faible des deux valeurs. Remarque : En français, on a coutume de dire. “Votre prime sera de 10% de votre CA, mais au maximum elle fera 250€.” Dans cette expression, on utilise le mot “maximum”, pour autant, dans Excel, on utilise la fonction MIN() pour traduire ce calcul. Il n‘y a pas correspondance obligatoire entre les mots du français et leur expression mathématique. Utilisation pour limiter une valeur par le bas (Limite BASSE) Sur votre facture, le montant du transport sera de 3% du montant de la commande, mais fera au minimum 10€. L’idée est de faire payer le transport au client, et de lui indiquer que le transport est d’autant plus cher que la commande est élevée. Mais, d’un autre côté, si la commande est très faible, le transport ne pourra pas être trop faible. Il faut bien payer le transporteur. Considérons que le montant de la commande est affiché en cellule A19. Formule du montant du transport : =MAX(3%*A19;10) Si A19 contient 2000€, la formule calcule MAX(3%*2000;10) , donc MAX(60;10), et le résultat sera de 60 €, qui est la valeur la plus élevée. Le client aura payé 3% du montant de la commande. Une ligne plus bas (on a recopié la formule), si A20 contient 200€, la formule calcule MAX(3%*200;10) , donc MAX(6;10), et le résultat sera 10€, plus forte des deux valeurs. Le client ne paiera pas 3% de sa commande (qui aurait fait 6€), mais il paiera bien 10€ de transport. Allons plus loin … Imaginons maintenant la double règle suivante. Le montant du transport est de 3% du montant de la commande, mais il ne peut pas être inférieur à 10 €, et il ne peut pas être supérieur à 100€. Quelle serait la formule qui calculerait le montant du transport si le montant de la commande se trouve en A1 ?


5 Fonction MOYENNE Rôle : renvoie la MOYENNE d’un ensemble de cellules. Syntaxe 1 : =MOYENNE(plage_cellules) Syntaxe 2 : =MOYENNE(A1;A3;B2) => renvoie la moyenne des 3 valeurs de A1, A3 et B2

Moyennes particulières Que se passe-t-il lorsque une cellule de la plage ne contient pas de valeur numérique ?


6 Comptage de cellules : NB.VIDE(), NBVAL(), NB() Quand on parle de ”comptage”, il s’égît de dénombrer, c’est à dire d’identifier combien de cellules répondent à un critère donné. Cela n’a rien à voir avec le fait d’additionner des cellules. Différents critères pour dénombrer les cellules :

Compter les cellules vides dans une plage de cellules NB.VIDE(plage_cellules) Compter les cellules non vides dans une plage de cellules NBVAL(plage_cellules)

Compter les cellules contenant des valeurs numériques NB(plage_cellules) Exemples


7 Comptage conditionnel avec NB.SI ou NB.SI.ENS

7.1 NB.SI() : Comptage avec conditions simples Données

Il y a plusieurs cas à savoir traiter : • La condition est un texte • La condition est une valeur numérique : égalité, inégalité • La condition est une inégalité • La condition s‘appuie sur le contenu d’une cellule extérieure : texte, nombre

SYNTAXE : NB.SI( Plage_cellules ; Condition) On peut saisir jusqu’à 30 couples plage_cellules / conditions !

Calculs


7.2 NB.SI.ENS() : Comptage avec conditions multiples simultanées ou non On reprend les mêmes données :

SYNTAXE :

NB.SI.ENS( Plage1_cellules ; Condition1 ; Plage2_cellules ; Condition2; …) On peut saisir jusqu’à 30 couples plage_cellules / conditions !

Calculs


8 Somme, moyenne conditionnelle avec SOMME.SI, MOYENNE.SI, SOMME.SI.ENS, MOYENNE.SI.ENS

Syntaxe SOMME.SI( Plage_Critère ; Critere ; Plage_A_Additionner ) SOMME.SI.ENS( Plage_A_Additionner;Plage_Critère1 ; Critere1 ; Plage_Critère2 ; Critere2;etc…) Les données :

8.1 Somme et moyenne avec une seule condition

8.2 Somme, moyenne avec conditions multiples simultanées ou non


9 Fonction SI Rôle : Permet dans une cellule d’écrire deux choses différentes suivant le résultat d’un test. Syntaxe : =SI(TEST;expression_si_test_VRAI expression_si_test_FAUX)

9.1 Fonction SI unique Données

Calculs

9.2 Fonction SI imbriquée


10 Fonctions ET et OU Rôle : Permet d’exprimer des test logiques complexes dans une fonction SI (entre autres). Syntaxe : ET(Condition_1;Condition_2;…) On peut enchaîner jsqu’à 30 conditions OU(Condition_1;Condition_2;…) Données

Calculs


11 Fonction RANG() Rôle : Permet de classer une valeur numérique par rapport dans une ensemble de valeurs. Syntaxe : RANG( Valeur_a_Classer ; Ensemble_Des_valeurs ; Sens_classement)

• Pour un classement décroissant, Sens_classement sera la valeur 0 (ou ne rien mettre) • Pour un classement croissant, sens_classement sera toute valeur non nulle

11.1 Les deux types de classement : croissant / décroissant

11.2 Applications à des calculs de rang plus complexes


12 Calculs de pourcentage A savoir :

• Un pourcentage est un mode de comparaison de deux quantités. • Calculer un pourcentage, conduit forcément à diviser deux quantités entre elles, l’une servant de référence.

Exemple de comparaison Prenons deux valeurs 2000 et 2500. Si on les compare, on peut dire :

• Que l’une est plus forte que l’autre • Que l’une est plus forte que l’autre d’une quantité de 500. C’est

déjà plus précis. Maintenant prenons deux autres valeurs , à savoir 4000 et 4500. On peut dire exactement les mêmes choses que précedemment. Et pourtant, intuitivement, l’augmentation de 2000 à 2500 est plus marquée que celle de 4000 à 4500. Imaginons que ces couples de valeurs soient les impôts que vous et un ami avez à payer. La première valeur en 2012, la seconde en 2013. Vous constatez que votre imposition est passée de 2000 à 2500 €. Pour votre ami, elle est passée de 4000 à 4500€. Avez-vous, tous les deux le même sentiment d’augmentation ? Non, car intuitivement, pour estimer l’augmentation de votre impôt, vous intégrez une notion de proportion. Vous vous dîtes que 500€ de plus sur une base de 2000 €, c’est plus fort que 500€ sur une base de 4000€. La notion de pourcentage est typiquement là. On dira alors

• Pour vous, que la variation est de 500 pour 2000. On calcule 500/2000, on trouve 0,25.

• Pour votre ami, la variation est de 500 pour 4000. On calcule 500/4000, on trouve 0,125.

Il se trouve que le nombre 0,25 est aussi le résultat de 25 divisé par 100, que l’on a l’habitude d’écrire sous la forme 25%. Et voilà, pour quoi on dit que votre impôt a augmenté de 25%, et que celui de votre ami de 12,5%.

Le pourcentage, ou l’art de la référence … Calculer un pourcentage, c’est donc comparer, et comparer, c’est observer une quantité par rapport à une autre qui sert de référence. Changer la référence, c’est changer le résultat de la comparaison ! Nous allons le voir sur un exemple. Imaginez que vous ayez eu 15 sur 20 à un devoir. C’est vraiment bien si on prend pour référence la valeur 10. Ca l’est un peu moins si on considère que la moyenne du groupe était de 12. Avec la référence habituelle de 10, vous avez 5 points de plus, donc 5/10, soit 0,5 soit 50% de mieux que la moyenne de 10. Mais par rapport au groupe, vous n’avez que 3 points de mieux, soit 3/12, donc 0,25, donc 25 % de mieux. Votre performance est alors moitié moindre. Changez la référence, changer le résultat ! Et maintenant, quelques jours plus tard, vous avez une nouvelle note de 12 sur 20. Cette fois-ci, ayant bien compris les pourcentages et surtout le choix adéquat de la référence, vous pouvez annoncer que vous avez amélioré votre performance. Et pourtant vous avez eu 12, alors que la dernière fois vous avez eu 15. Transformons cette chute en une victoire ! En effet, vous avez eu 12, mais surtout vous avez noté que la moyenne du groupe ce jour là est descendue à 8 sur 20. Vous avez donc 4 points de plus que la moyenne du groupe, soit 4/8, donc 0,5, donc 50% de mieux que la moyenne du groupe. La fois d’avant, vous n’étiez en avance que de 25% par rapport au groupe. Vous êtes passé de 25 % de mieux à 50% de mieux. Vous avez augmenté votre performance de 25 sur 25, soit 25/25 = 1, donc 100%. Conclusion : Il ne faut pas annoncer une note qui a baissé, mais une performance qui a doublé !

Calculer un % dans Excel

• On divise deux quantité entre elles. • Attention, on ne multiplie pas par 100. • On applique au résultat un format de pourcentage

Gros pourcentages ! Notons que le calcul d’un pourcentage peut dépasser 100%. En effet, pour un prix évoluant de 50€ à 150€, la variation est de 100 pour une base de 50. Le % d’augmentation est don de 100/50, soit 2, c’est à dire 200%. C’est d’ailleurs trompeur. En passant de 50 à 150, on triple le montant, mais le pourcentage d’augmentation est de 200%. En effet, quand on dit qu’on triple on a en fait calculé le coefficient multiplicateur entre 50 et 150. Alors que le résultat de 200% est une comparaison de la variation (c’est à dire 100) par rapport à la valeur de base (50). Si vous préférez les formules, le % de variation est (Vf-Vi)/Vi, alors que le coefficient multiplicateur est Vf/Vi.

© P. Jurain – Memento Excel – vs 0.a – Mai 2013 n

15

13 Compléments

13.1 Quelques raccourcis clavier Excel Actions PC MAC Changer de feuille CTRL PgUp, CTRL PgDown Passer d’un fichier à un autre CTRL F6 Appliquer des $ F4 Cmd T Afficher la liste des noms F3

13.2 Vidéos sur Youtube Sur la page http://www.youtube.com/user/jurainph , vous trouverez des vidéos sur Excel.

Documents

EMENTO XCEL F C - pjece.free.frpjece.free.fr/.x/Cours-cEce1-Excel-Vs1a.pdf · Permet dans le cas d’une formule devant être recopiée, de figer certaines références de cellules