Cours Excel fonction SI, methodes et formules


Télécharger Cours Excel fonction SI, methodes et formules

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Cours Excel fonction SI, méthodes et formules

  1. La fonction SI:

5.1. Définition d’une formule :

Les Formules effectuent des opérations (Addition, Soustraction …) sur des valeurs directes et/ou sur le contenu de cellules (en indiquant leurs références). Une formule peut aussi comporter une fonction (Somme …). Une formule commence toujours par le signe ‘=’ suivi des opérations à effectuer. Exemple ‘=15-2, ‘=A5’, ‘=A10*30’, ‘= Somme (A1 :A15)'.

Dans une formule, vous pouvez utiliser les opérateurs arithmétiques (+, -, *, /, % et de puissance ^), de comparaison (=, >, >=, <, >= et <>).

5.2. Définition de la fonction SI :

La fonction Si et avant tout une fonction qui nécessite 3 paramètres et qui renvoie une valeur (paramètre 2) si la condition (paramètre 1) que vous spécifiez est VRAIE et une autre valeur (paramètre 3) si cette valeur est FAUSSE. 

Syntaxe simple

SI.CONDITIONS([Un élément est Vrai1, la valeur si Vrai1; [Un élément est Vrai2, la valeur si Vrai2];...[Un élément est Vrai127, la valeur si Vrai127])

Remarques :

La fonction SI.CONDITIONS vous permet de tester jusqu’à 127 conditions différentes.

Par exemple :

La formule a la signification suivante : SI(A1 est égal à 1, afficher 1; SI A1 est égal à 2, afficher 2; sinon si A1 est égal à 3, afficher 3).

Nous vous déconseillons d’utiliser trop de conditions avec les instructions SI et SI.CONDITIONS, car vous devez les entrer dans l’ordre correct. Par ailleurs, il peut être difficile de les créer, de les tester et de les mettre à jour.

=SI.CONDITIONS(A1=1;1,A1=2;2;A1=3;3)

Utilisez la fonction SI pour effectuer un test conditionnel sur des valeurs et des formules : SI(Test_logique, Valeur_si_vrai, Valeur_si_faux)

  • Test_logique : est toute valeur ou expression dont le résultat peut être VRAI ou FAUX. On y place surtout les opérateurs de comparaison (>, <,=…).
  • Valeur_si_vrai  : est la valeur qui est renvoyée si le test logique est VRAI. L'argument valeur_si_vrai peut être une autre formule.
  • Valeur_si_faux : est la valeur qui est renvoyée si le test logique est FAUX. L'argument valeur_si_faux peut être une autre formule.

...

Remarques

Pour spécifier un résultat par défaut, entrez VRAI pour votre argument test_logique final. Si aucune autre condition n’est remplie, la valeur correspondante sera renvoyée. Ce résultat est démontré sur les lignes 6 et 7 (avec la note 58) du premier exemple.

 Si un argument test_logique est fourni sans argument valeur_si_vrai correspondant, cette fonction renvoie le message d’erreur « Le nombre d’arguments entrés est insuffisant pour cette fonction ».

 Si un argument test_logique est évalué et résulte en une autre valeur que VRAI ou FAUX, cette fonction renvoie une erreur #VALEUR!.

 Si aucune condition VRAI n’est trouvée, cette fonction renvoie l’erreur #N/A.

La fonction SI peut également être utilisée de manière imbriquée. Cela signifie qu’au sein d’une fonction SI il peut y en avoir plusieurs. Excel accepte de recevoir jusqu’à 64 fonctions SI imbriquées.

Dans notre exemple précédent, les SI imbriqués vont permettre de mettre en évidence des critères supplémentaires. Ainsi, nous allons pouvoir définir des mentions en fonction des notes attribués aux élèves.

Les nouveaux critères impliquent 5 fonctions SI imbriquées :

Les notes supérieures à 16 : mention très bien

Les notes comprises entre 14 et 16 : mention bien

Les notes comprises entre 12 et 14 : mention assez bien

Les notes comprises entre 10 et 12 : mention passable

Les notes comprises entre 8 et 10 : rattrapage

Les notes inférieures à 8 : doublement

Afin d’intégrer ses critères, il convient d’utiliser la formule ET qui permet d’associer plusieurs tests de logique pour la première donnée de la fonction SI. Pour intégrer le SI imbriqué, il faut inscrire au niveau de la « valeur_si_faux » la nouvelle fonction SI. Ainsi la formule va s’écrire en cellule C2 :

=SI(B2>=16;"Très bien";SI(ET(B2<16;B2>=14);"Bien";SI(ET(B2<14;B2>=12);"Assez bien";SI(ET(B2<12;B2>=10);"Passable";SI(ET(B2<10;B2>=8);"Rattrapage";"doublement")))))

La fonction MOYENNE.SI

Présentation de la fonction MOYENNE.SI

La fonction moyenne.si permet de calculer la moyenne des valeurs d’une plage répondant à un critère.

Elle se présente sous la forme :

MOYENNE.SI(plage; critères; [plage_moyenne]

...

Particularités de la fonction SOMME.SI

Si l’argument plage somme n’est pas renseigné alors Excel additionne les cellules de la plage de critère.

La chaine de caractères a une taille maximale de 255.

La plage_critère1 et la somme_plage peuvent avoir une taille différente.



Les critères textes sont obligatoirement exprimés entre guillemets.

La fonction SOMME.SI.ENS

Présentation de la fonction SOMME.SI.ENS

Cette fonction permet d’additionner des valeurs répondant à plusieurs critères dans une plage.

Elle se présente sous la forme:

SOMME.SI.ENS(somme_plage;plage_critères;critères1;[plage_critères2;critères2];…)

...

RECHERCHEV ET RECHERCHEH

Présentation des fonctions RechercheV et RechercheH

Les fonctions de recherche d'Excel permettent de faire exécuter au tableur une recherche dans un tableau de valeurs et de renvoyer la valeur trouvée dans une cellule déterminée à l'avance. On pourra, par exemple, faire trouver par Excel la désignation et le prix unitaire d'un produit en saisissant simplement sa référence. Comme le fait l'ordinateur central d'un hypermarché quand il renvoie la référence et le prix d'un produit dont le code-barres a été lu en caisse ou saisi par la caissière. Cette fonction peut avoir de nombreuses applications : facture, bulletin de salaire, base de données...

L'abréviation de V signifie verticale et celle du H horizontale.

La RechercheV permet de rechercher des données en colonne et la RechercheH recherche des données en lignes.

Syntaxes

Les syntaxes générales des RechercheV et RechercheH sont les suivantes :

'=RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche])'

valeur_cherchée : il s'agit de la cellule où Excel lit la valeur qui entraîne la recherche (exemple : la référence d'un produit conduit à la recherche de son prix et de sa désignation).

table_matrice : il s'agit de la plage de cellules où Excel recherche les valeurs (exemple : un tarif comportant prix et désignations des produits).

'no_index_col' : il s'agit du numéro de la colonne du tableau où Excel doit trouver la valeur cherchée (exemple : les prix se trouvent dans la troisième colonne du tarif).

valeur_proche : Cette information est facultative. Il s'agit de rentrer FAUX si nous voulons une correspondance exacte ou VRAI si nous voulons une correspondance approximative

=RECHERCHEH(valeur_cherchée, table_matrice, no_index_lig, [valeur_proche])

valeur_cherchée : Obligatoire. Représente la valeur à rechercher dans la première ligne de la table. Il peut s’agir d’une valeur, d’une référence ou d’une chaîne de texte.

table_matrice : Obligatoire. Représente la table de données dans laquelle est exécutée la recherche de la valeur. Utilisez une référence à une plage ou un nom de plage.

no_index_lig : Représente le numéro de la ligne du tableau où Excel doit trouver la valeur cherchée.

valeur_proche : Cette information est facultative. Il s'agit de rentrer FAUX si nous voulons une correspondance exacte ou VRAI si nous voulons une correspondance approximative

INDEX

RECHERCHEV c’est faire du vélo avec les roulettes. INDEX + EQUIV, c’est conduire votre première voiture.

La formule INDEX vous permet de retourner la valeur qui se trouve à la place que vous souhaitez dans une plage de cellule.

La syntaxe générale d'index est la suivant :

=INDEX(matrice; no_lig; [no_col])

Matrice : Elle correspond soit à une plage de cellule soit une constante. C’est un élément indispensable au fonctionnement de la formule.

No_lig : Le numéro de ligne permet de sélectionner la valeur qui doit être renvoyé. C’est un élément indispensable pour le fonctionnement de la formule.

No_col : le numéro de colonne permet de sélectionner la valeur qui doit être renvoyé. Néanmoins, cet élément n’est pas un élément primordial pour le fonctionnement de la formule.

Si la matrice ne possède qu’une seule ligne, le numéro de la colonne n’est pas utile. A contrario, si le numéro de la ligne n’est pas utile, il faut quand même le marquer d’un 0.

Exemple 1 :

Exemple 1 - INDEX

Avec un peu d’automatisation, il n’y a plus besoin de toucher à la fonction. Elle dépendra seulement d’une cellule, avec un menu déroulant par exemple.

Exemple 2 :

Exemple 2 - INDEX

Nous pouvons rendre la fonction Index beaucoup plus intéressante grâce à la fonction EQUIV.

Néanmoins, la fonction INDEX et RECHERCHEV ou H est proche à tel point d’obtenir le même résultat.

INDEX ET EQUIV

La fonction Excel EQUIV recherche la position d'une valeur dans une plage de cellules.

La syntaxe générale d'Equiv est la suivant :

= EQUIV (valeur_recherchée;plage;type)

Valeur_recherchée : il faut y inscrire la valeur dont nous voulons connaitre la position. Cet élément est obligatoire dans la formule.

Plage: elle doit contenir la position que nous recherchons. Cet élément est obligatoire dans la formule. De plus, la plage doit être dans l'ordre croissant ou décroissant suivant le "type".



 Type: 3 valeurs possibles : le 0 pour une valeur exacte; le 1 correspond à la valeur inférieur la plus proche de la valeur recherchée. Le -1 correspond à la valeur supérieur la plus proche de la valeur recherchée.

Exemple 3 - INDEX EQUIV

Pour obtenir le CA du dossier 4, avec la fonction INDEX et RECHERCHEV, voici les formules respectives :

=INDEX(C2:C11;EQUIV(E4;B2:B11;0);1)

=RECHERCHEV(E4;B2:C11;2;FAUX)

Nous arrivons donc au même CA : 10 693€

Néanmoins, la différence commence à se voir si nous voulions chercher un numéro de dossier par rapport à un lieu . Voici les deux formules :

Pour index =INDEX(A2:C11;EQUIV(E4;B2:B11;0);1)

Pour la recherche : =RECHERCHEV(E4;A2:C11;-1)

Les résultats sont différents. INDEX nous donne 4 ce qui est correct, tandis que RECHERCHE nous donne #N/A. Voici la première limite de cette fonction, la valeur cherchée doit être dans la première colonne.

L’intérêt d’utiliser la fonction Index et EQUIV est de les assembler au total. C’est-à-dire de remplacer le numéro de ligne et le numéro de colonne sera recherché par la fonction EQUIV.

Exemple 4 - INDEX EQUIV

Ici encore, une RECHERCHEV suffirait car le résultat est identique à celui de la formule INDEX et EQUIV.

Index =INDEX($B$2:$H$13;EQUIV(D17;Mois;0);EQUIV(C17;Villes;0))

RechercheV = RECHERCHEV(D17;A2:H13;6;FAUX)

Néanmoins, le numéro de la colonne de la RECHERCHEV est une valeur fixe, ce qui signifie que si nous intégrons une nouvelle colonne, la valeur que nous recherchons, ne sera plus la bonne. Ce ne sera pas le cas avec l'utilisation de la fonction INDEX avec la fonction EQUIV car nous demandons à la fonction EQUIV d’aller chercher dans le tableau, le titre de la colonne correspondant à notre recherche.

Exemple 5

Exemple 5 - INDEX EQUIV

Fonction SOMME.SI

La fonction SOMME.SI permet de calculer la somme des valeurs d’une plage qui répond au critère spécifié. Par exemple, supposons que dans une colonne contenant des nombres, vous vouliez uniquement calculer la somme des valeurs supérieures à 5. Vous pouvez utiliser la formule suivante : =SOMME.SI(B2:B25,">5")

Conseils :

Si vous le souhaitez, vous pouvez appliquer le critère à une plage et calculer les valeurs dans une autre plage. Par exemple, la formule =SOMME.SI(B2:B5;"Jean";C2:C5) calcule uniquement la somme des valeurs de la plage C2:C5, dans laquelle les cellules correspondantes de la plage B2:B5 contiennent le mot « Jean ».

Pour calculer la somme de cellules en fonction de plusieurs critères, voir Fonction SOMME.SI.ENS.

Syntaxe

SOMME.SI(plage;critère;[somme_plage])

La syntaxe de la fonction SOMME.SI contient les arguments suivants :

plage   Obligatoire. Plage de cellules à calculer en fonction du critère. Les cellules de chaque plage doivent être des nombres ou des noms, des matrices ou des références contenant des nombres. Les valeurs vides ou textuelles ne sont pas prises en compte. La plage sélectionnée peut contenir des dates au format Excel standard (voir exemples ci-dessous).

critère   Obligatoire. Critère, exprimé sous forme de nombre, d’expression, de référence de cellule, de texte ou de fonction qui définit les cellules à ajouter. Par exemple, l’argument critère peut être exprimé sous l’une des formes suivantes : 32, ">32", B5, "32", "pommes" ou AUJOURDHUI().

Important : Tous les critères textuels et tous les critères qui contiennent des symboles mathématiques ou logiques doivent être placés entre guillemets ("). En revanche, les guillemets ne sont pas nécessaires pour les critères numériques.

plage_somme   Facultatif. Cellules réelles à ajouter, si vous voulez ajouter d’autres cellules que celles qui sont spécifiées dans l’argument plage. Si l’argument plage_somme est omis, Excel ajoute les cellules spécifiées dans l’argument plage (les cellules auxquelles s’applique le critère).

Vous pouvez utiliser les caractères génériques (point d’interrogation (?) et astérisque (*)) dans l’argument critère. Le point d’interrogation correspond à un caractère quelconque et l’astérisque correspond à une séquence de caractères quelconque. Pour rechercher réellement un point d’interrogation ou un astérisque, tapez un tilde (~) devant ce caractère.

Remarques

La fonction SOMME.SI renvoie des résultats incorrects lorsque vous l’utilisez pour mettre en correspondance des chaînes comportant plus de 255 caractères ou avec la chaîne #VALEUR!.

L’argument plage_somme ne doit pas nécessairement avoir la même taille et la même forme que l’argument plage. Les cellules effectivement additionnées sont déterminées en utilisant la cellule supérieure gauche de l’argument plage_somme comme cellule de début, puis en incluant les cellules dont la taille et la forme correspondent à l’argument plage.



1564