Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
Le blog d'education et de formation

excel : les formules creer des formules simples

11 Mai 2009 , Rédigé par mazagan Publié dans #TICEتكنولوجيا المعلومات

Si Excel possède des fonctions de mise en forme plus que correctes, elles ne sont en aucun cas la vocation de ce logiciel. En effet, Excel se distingue surtout par ses fonctionnalités de traitement des données, c'est d'ailleurs pour cela qu'on dit que c'est un Tableur.

La première fonctionnalité offerte est la possibilité de créer des formules de calcul. En effet, Excel est basée sur une logique qui dit que seules les données de base doivent être fournies par l'utilisateur, tous les calculs devant être pris en charge par Excel.

Dans notre tableau d'exemple, si vous vous placez sur la cellule C2, vous verrez s'afficher dans la barre de formule « 2900 » c'est-à-dire le contenu de la cellule ni plus ni moins car celle-ci est une donnée de base. Par contre, si nous nous plaçons sur la cellule D2 nous voyons ce qui suit :

« =B2-C2 » est une formule. Une formule est reconnue sur Excel par un signe « = » en début de la zone de formule. La formule « =B2-C2 » qui se trouve dans la cellule D2 indique à Excel que pour connaître la valeur à afficher dans la cellule D2, il faut soustraire à la valeur de la cellule B2, la valeur contenue dans la cellule C2. La valeur affichée dans D2 est donc une information calculée par le logiciel et non pas introduite par l'utilisateur.

De même on aura les formules suivantes :

Dans D3 : =B3-C3 

Dans D4 : =B4-C4 

Dans le même esprit, on aura dans la cellule B5 la formule suivante « =B2+B3+B4 » car la cellule B5 représente la somme des valeurs des trois cellules qui se trouvent dans la même colonne « B ».

Il est bien évidemment possible d'utiliser d'autres opérateurs arithmétiques notamment :

« + » : Addition

« - » : Soustraction

« * » : Multiplication

« / » : Division

Pour vous familiariser par vous-même à l'utilisation des formules, nous vous proposons de télécharger le fichier ayant servi d'exemple en cliquant sur ce lien. Pensez notamment à changer la valeur d'une des cellules contenant les données de base et vous verrez que les cellules contenant des formules qui la prennent en compte changeront de valeur automatiquement.


Utilisation des fonctions dans Excel 2003 >> Accueil

Outre les opérateurs arithmétiques, Excel offre la possibilité d'utiliser des fonctions. Pour faire simple, une fonction renvoie une valeur sur la base de données d'entrée fournies par l'utilisateur.

L'expression de la fonction prend généralement la forme suivante :
y = f (x), où « y » est la valeur renvoyée, « f » la fonction et « x » la variable contenant la donnée d'entrée qui une fois transformée grâce à la fonction « f » donne « y ».

Dans Excel cette notion est reprise de manière identique à l'exception de l'expression écrite. En effet, la valeur « y » est affichée dans la cellule contenant la formule de la fonction.

A titre d'exemple, considérons le tableau suivant reprenant les notes obtenues par trois étudiants dans quatre matières :

 

Et 1

Et 2

Et 3

Mathématiques

17

12

5

Chimie

15

9

11

Français

13

18

12

Informatique

16

17

14

MOYENNE

 

 

 

Il s'agit donc de calculer la moyenne de chaque étudiant, pour cela on peut procéder de deux manières différentes :

utilisation des opérateurs

La moyenne est la somme des notes obtenues divisée par le nombre de matières. Ce qui peut être traduit par les formules suivantes :

Dans B6 : = (B2+B3+B4+B5) / 4

Dans C6 : = (C2+C3+C4+C5) / 4

Dans D6 : = (D2+D3+D4+D5) / 4

Toutefois, cette méthode est fastidieuse et devient peu pratique lorsqu'il s'agit de faire la moyenne d'une vingtaine de matières par exemple. Pour cela, nous conseillons de lui préférer la méthode des fonctions.

utilisation des fonctions

Excel propose une fonction appelée « Moyenne » qui a pour rôle de renvoyer la moyenne des valeurs contenues dans les cellules entrées comme argument.

Dans notre exemple il suffira d'entrer la formule suivante « =MOYENNE(B2:B5) » dans la cellule B6 pour obtenir le même résultat que précédemment. L'utilisation des « : » permet de définir une « plage de cellules » qui s'étant de la cellule B2 à la cellule B5, chose qui est impossible en utilisant les opérateurs. De plus, la fonction se charge de compter automatiquement le nombre de matières et vous évite ainsi tout risque d'erreur. Enfin, l'utilisation des fonctions permet d'avoir des formules plus courtes et donc plus lisibles.

Outre les fonctions de base comme la MOYENNE ou la SOMME, Excel propose une multitude de fonctions plus élaborées accessibles via le menu  Insertion > Fonction.

Pour insérer une fonction dans une cellule, il vous suffit de cliquer sur la cellule et d'aller dans le menu Insertion > Fonction ce qui affichera la fenêtre suivante :

Cette fenêtre vous propose l'ensemble des fonctions supportées par Excel classées en catégories accessibles par une liste déroulante. Une fois la catégorie choisie, les fonctions qui y sont proposées sont affichées dans la liste qui se trouve un peu plus bas.

Cliquez alors sur la fonction souhaitée et vous verrez s'afficher en bas de la liste des fonctions une brève description de la fonction et de son utilisation. Ceci est particulièrement pratique quand vous utilisez une fonction pour la première fois.

Reprenons notre exemple précédent et utilisons cet assistant plutôt que la saisie directe de la fonction. Après avoir cliqué sur la cellule B6 et avoir accédé à la fenêtre d'insertion des fonctions, choisissez la catégorie « tous » pour afficher toutes les fonctions. Faites dérouler jusqu'à arriver à la lettre M et choisissez la fonction MOYENNE.

En appuyant sur OK vous voyez s'afficher à l'écran la fenêtre suivante :

Comme vous pouvez le voir, Excel devine automatiquement la plage de cellules la plus probable pour cette fonction. Toutefois, à des fins pédagogiques, il nous semble préférable de vous montrer la méthode à suivre car il arrive qu'Excel n'affiche pas les bons arguments.

Appuyez d'abord sur le petit bouton qui se trouve en regard de chaque argument. Cela affichera la fenêtre suivante :

Ensuite, il vous suffira de sélectionner au moyen de la souris les cellules souhaitées pour voir se modifier le texte de la fenêtre en même temps. Une fois la bonne plage sélectionnée, il ne vous reste qu'à enfoncer la touche Entrer du clavier pour valider votre travail. Vous reviendrez alors à la fenêtre précédente et verrez une simulation du résultat de la fonction, si vous êtes satisfait, appuyez sur OK et le tour sera joué.

Signalons que l'argument2 est facultatif et sert à introduire une seconde plage de cellules quand cela est nécessaire.

Vous pouvez maintenant procéder de la même manière pour toutes les autres fonctions d'Excel, nous traiterons toutefois en détails certaines fonctions dont l'utilisation s'avère délicate.


la fonction SI >> Accueil

en théorie

Les structures conditionnelles (ou de choix) sont indeniablement la base de la programmation informatique et donc aussi la base du fonctionnement des ordinateurs et des logiciels comme EXCEL.

Exemple :

SI "condition 1 réalisée" ALORS "faire opération 1" SINON "faire operation 2"

La traduction des structures conditionnelles dans la version francaise d'Excel se fait grace à la fonction "SI". Voici ce que vous devez saisir dans la barre de formule pour convertir l'exemple précédent en fonction comprehensible par EXCLE :

=Si (condition 1 réalisée; faire opération 1; faire opération 2)

Quelques remarques s'imposent:

- les mots "alors" et "Sinon" ne sont pas ecrits explicitement mais sous entendus.
- les différentes parites de la structure conditionnelle sont séparées par des points virgules ";"
- l'orde est TRES important car il remplace les mots, en effet, après le premier on retrouve toujours l'action à faire ne cas de vérification de la condition et celle à faire sinon se trouve toujours après le second point virgule.

en pratique

Prenons maintenant un petit exemple pratique. Supposons que vous disposez de la base de données suivante sur Excel :

Employé
Ventes
Commission
Jean 700  
Bernard 372  
Pierre 440  
Rachid 801  
David 975  

Pour motiver vos vendeurs, vous avez décidé de doubler la commission que touche chaque vendeur pour toutes le ventes qui dépassent les 500 pièces. La vente est payée 7 Euros (une fois doublée elle sera donc payée 14 Euros).

Vous ne pouvez pas utiliser une simple formule comme nous l'avions fait dans les pages précédentes de ce cours. La solution consiste en l'utilisation d'une structure conditinnelle qui permet de traiter differemment les vendeurs ayant réalisé moins de 500 et ceux ayant dépassé ce chiffre.

Voici ce que vous devez saisir dans la première cellule de la colonne "commission" (cellule C2) :

=Si (B2<=500;B2*7;500*7+(B2-500)*14)

Cette formule indique à Excel que :

- dans le cas ou le vendeur a vendu 500 pièces ou moins, il doit calculer la commission en multipiliant le nombre de pièces vendues par 7

- dans le cas contraire, c'est à dire s'il vent plus de 500 pièces, Excel doit trouver la commission en comptant 7 Euros pour les 500 premières pièces (3500 Euros) et y ajouter 14 Euros par pièce dépassant les 500 ce qui est obtenu en enlevant 500 du total des ventes du vendeur.

A noter que cette formule n'est pas l'unique bonne formule dans ce cas, plusieurs autres formules équivalentes peuvent être utilisées :

=Si (B2>500;500*7+(B2-500)*14;B2*7)

=Si (B2<=500;B2*7;3500+(B2-500)*14)

=Si (B2<=500;B2*7;(B2*14)-3500)

 

En définitive, selon le cas de figure, Excel utilisera l'une OU l'autre des formules pour calculer la commission.

Il ne vous restera plus qu'à tirer la poignée de recopie pour appliquer la formule à tout le tableau. Voici ce que vous devez obtenir :

Employé
Ventes
Commission
Jean 700 6300
Bernard 372 2604
Pierre 440 3080
Rachid 801 7714
David 975 10150

ATTENTION : la fonction SI est extrêmement utile est sa maitrise nécessaire. Si vous avez des questions à ce sujet, n'hésitez pas à nous en faire part sur le forum.


La fonction RechercheV / RechercheH >> Accueil

Pour schématiser, on peut retenir deux cas où l'on a recours à l'utilisation de la fonction RechercheV :

Structures conditionnelles dynamiques

La fonction recherchev est ici une évolution de la structure conditionnelle classique SI. Elle vient répondre à deux limitations majeures de cette dernière :

•  La fonction SI est limitée à un maximum de 7 SI imbriqués et ne peux donc pas traiter plus de 8 cas,

•  La nécessité de définir les tests en les écrivant dans la formule (en dur), elle n'est donc pas dynamique.

Prenons le cas suivant : vous êtes chargé(e) d'effectuer un rapport sur l'activité commerciale de vos vendeurs. On vous fournit le tableau suivant (dans la première feuille du classeur Excel) que vous devez compléter :

Vendeur

Pays

CA HT

CA TTC

Com

Legrand

France

25000

 

 

Paoli

Italie

49500

 

 

Chasseney

France

22750

 

 

De la garde

Italie

18000

 

 

Parlois

Italie

120000

 

 

Legrand

Espagne

84300

 

 

Paoli

Espagne

21200

 

 

Aulin

France

98700

 

 

On vous fourni aussi la grille suivante qui servira de base aux calculs (dans une seconde feuille du classeur appelée "grille")  :

Pays

TVA

Taux Com

France

19,60%

5%

Espagne

21%

4%

Italie

15%

3,5%

Quelle formule faut-il insérer dans les cellules D2 et E2 pour calculer le CA TTC et les commissions des vendeurs ?

Solution 1 : utiliser la fonction SI

Dans D2 : =SI(B2="France";C2*1,196;SI(B2="Espagne";C2*1,21;SI(B2="Italie";C2*1,15;"?")))

Dans E2 : =SI(B2="France";C2*0,05;SI(B2="Espagne";C2*0,04;SI(B2="Italie";C2*0,035;"?")))

Cette solution comporte les inconvénients suivants :

•  en cas de changement des taux, les formules doivent être editées manuellement

•  s'il y a beaucoup de pays, la formule sera longue et fastidieuse

•  à partir de 8 pays, cette méthode est inutilisable

Solution 2 : utiliser la fonction RechercheV

Etape 1 : Définir la source des données :

Sélectionnez la plage de cellules "$A$2:$C$4" dans la feuille nommée « grille » (il ne faut pas sélectionner les entêtes des colonnes). Ensuite dans le menu "Insertion" allez sur "Nom" puis "Définir" et donnez un nom à la zone sélectionnée (exemple : Source).

Etape 2 : Définir la fonction RechercheV

Placez vous sur D2 et allez dans Insertion > Fonction puis sélectionnez la fonction « RechercheV » qui se trouve dans la catégorie Recherche & Matrices.

Apparaîtra alors l'assistant qui vous propose 4 champs à renseigner (les trois premiers sont obligatoires) :

arguments fonction recherchev

- Valeur_cherchée : c'est la valeur sur laquelle doit être effectué le test, pour la retrouver facilement, gardez à l'esprit que c'est en général la seule qui est partagée entre les deux tableaux elle assure donc le lien entre les données sources et tableau des résultats. Il s'agit dans notre cas de la cellule B2 (Pays).

- Table_matrice : c'est la matrice ou le tableau source des données. Il doit nécessairement comporter au moins 2 colonnes de manière à faire correspondre la valeur cherchée (toujours en première colonne) à une ou plusieurs données (taux de TVA, taux de commission.) dans le colonnes suivantes. Pour la définir, vous avez le choix entre l'insertion de la référence de la plage de données (Grille!$A$2:$C$4) ou l'insertion d'un nom préalablement attribué à la source de données (dans notre cas « Source »).

- No_index_col : c'est le numéro de la colonne dans le tableau source de données qui contient les valeurs que vous souhaitez obtenir. Mettez 2 pour obtenir le taux de TVA et 3 pour le taux de commission (le 1 étant toujours réservé à la valeur cherchée).

- Valeur_proche : ici vous decidez si Excel doit trouver la correspondance exacte ou la correspondance la plus proche. Ceci est utile dans le cas des valeur numeriques. Dans notre cas on mettra « Faux » pour obtenir uniquement des correspondances exactes.

Voici donc la formule à insérer dans la cellule D2 : =C2*(1+RECHERCHEV(B2;Source;2;FAUX))

Pour résumer, cela dit à Excel de rechercher la valeur de B2 dans le tableau appelé Source et de renvoyez la valeur qui lui correspond dans la deuxième colonne de ce tableau. Une fois cette valeur récupérée, elle sera utilisée pour calculer le CA TTC.

De même on aura dans E2 la formule suivante :

=C2*RECHERCHEV(B2;Source;3)

Comme vous pouvez le deviner, ette fonction offre donc les avantages suivants :

•  simplicité, car la formule est plus courte et plus simple à comprendre,

•  dynamisme, car il suffit de modifier les données dans le tableau source pour que les changements soient répercutés automatiquement sans avoir à modifier la formule,

•  il n'y a pas de limite de nombre de pays.

Nota : Dans notre cas, on a eu recours à la fonction RechercheV car les données sont organisées verticalement (les pays sont dans une même colonne et non pas sur une même ligne). Si les pays avaient été organisés horizontalement, c'est la fonction RechercheH qui aurait été utilisée.

Cas 2 : Aide à la saisie

Prenons un cas simple : Vous êtes chargé(e) d'organiser un cycle de formation pour les commerciaux de l'entreprise. Le service Ressources Humaines vous donne la liste des matricules, noms et prenons des participants, mais le formateur souhaiterait avoir plus d'informations sur chacun d'eux pour mieux cibler son intervention. Vous disposez pour cela d'un fichier Excel interne à votre service qui regroupe pour chaque commercial : age, région, expérience, formation, segment de clientèle.

Comment compléter le tableau fourni par les RH avec les données issues de votre fichier interne ?

Solution manuelle : pour chaque matricule vous faites une recherche dans le fichier et vous copiez / collez les données dans le fichier à fournir au formateur. Cette méthode n'est toutefois pas pratique si vous avez plus d'une vingtaine de participants.

Solution automatisée : vous utilisez la fonction RechercheV qui se charge de remplir automatiquement et dynamiquement le fichier à votre place en quelques secondes quel que soit le nombre de participants qu'il contient. Pour ce faire, il suffit de définir le tableau contenant les données sur tous les commerciaux comme source de données (Table_matrice), la valeur_cherchée est qui est le matricule et les numeros des colonnes dont vous souhaitez obtenir les informations.

 


Les types de graphiques >> Accueil

Partager cet article

Commenter cet article