Prérequis

Vous devez avoir suivis les COURS 1 et 3 ou en maîtriser le contenu.

L'auteur

Aurélie ARROT

Certifiée Microsoft specialist Excel
Habilitée B2i Adultes

EXCEL

Cours suivants :

COURS 1 : Mise en forme et calcul simple

COURS 2 : Les graphiques simples

COURS 3 : Mise en relation des données

COURS 4 : Fonction conditionnelle simple

COURS 5 : Automatiser une recherche

COURS 6 : Manipuler des listes de données

COURS 7 : Les tableaux croisés dynamiques (TCD)

COURS 4 : Fonction Conditionnelle simple


Utilisation de la fonction SI() dans Excel

 

 

LES OBJECTIFS :
Créer une référence mixte, utiliser les fonctions statistiques conditionnelles, utiliser la fonction SI () simple

PUBLIC VISE : Tout utilisateurs qui en maîtrise les bases d'excel

 

 

PROGRAMME

 

Une référence mixte est une référence qui n'est fixée que sur une partie de la référence : soit la ligne, soit la colonne.

Pour rappel : Sur une référence absolue, nous avons 2 $ positionnés : il y en a un pour les lignes et un pour les colonnes.

Excel référence absolue sur une cellule

Maintenant, si par exemple nous écrivons juste =B$1, le $ est à gauche du 1 (soit la ligne).
Alors quand nous recopierons cette formule, la ligne 1 ne variera jamais par contre, la référence de la colonne (soit la lettre B) va être modifiée.
Inversement si nous avions écrit la formule =$B1. Là, c'est la colonne qui ne variera pas.

Prenons l'exemple des tables de multiplication Nous voulons reproduire les tables de multiplications de 1 à 10 en saisissant uniquement la valeur 1 dans une cellule, en saisissant 1 seule formule puis en utilisant 1 glisser-copier et 4 recopie.

Saisie des titres :

Prenez une nouvelle feuille.
Tapez 1 dans les cellule B1 et A2
Sélectionnez de nouveau B1, cliquez sur la poignée de recopie (le petit carré noir en dans le coin inférieur droit) puis tirez votre souris vers la droite tout en maintenant la touche Ctrl. Relâchez la souris, puis Ctrl dès que la valeur 10 est proposée.

Excel Exercice avec les table de mutiplictaion : référence mixte

Sélectionnez A2, cliquez sur la poignée de recopie puis tirez votre souris vers le bas tout en maintenant la touche Ctrl. Relâchez la souris, puis Ctrl dès que la valeur 10 est proposée.

Saisie de la formule :

En B2, saisissez la formule =A2*B1

Quel que soit la cellule devant contenir le résultat de la multiplication, celle-ci doit multiplier la cellule de la colonne A sur la même ligne par la cellule de la 1ère ligne sur la même colonne.
Exemple :
B2=A2*B1
C4=A4*C1
F8=A8*F1
Vous remarquez que :
  • dans la 1ère partie de la formule, le A est toujours présent, par contre la ligne varie
  • dans la 2e partie, la lettre varie alors que la ligne est toujours 1


  • Il faut donc modifier la formule en B2 pour figer uniquement le A dans A2 et uniquement le 1 dans B1. Ceci se fait en ajoutant des $, c’est ce que l’on appelle des références mixtes.

    Placez votre curseur dans la barre de formule contre A2, puis appuyez 3 fois sur la touche F4 afin de transformer A2 en $A2.

    Placez votre curseur dans la barre de formule contre B1, puis appuyez 2 fois sur la touche F4 afin de transformer B1 en B$1.

    Validez votre formule en appuyant sur la touche Entrée.

    Recopie de la formule :

    Sélectionnez la cellule B2, puis grâce à la poignée de recopie, tirez la cellule jusqu’en K2.
    Conservez la sélection et tirez la plage B2 : K2 jusqu’en ligne 11.
    Mettez en forme la plage A1 : K11 à votre convenance.
    Vous obtenez les tables de multiplications.
    Excel remplir les table de mutiplictaion : référence mixte

    Programmer une fonction : SOMME.SI :

    La fonction Excel Somme.si effectue une somme en vérifiant un critère sur la ligne équivalente.
    Si la cellule dans la plage de tests est égale au test à effectuer, l'élément en regard dans la plage à additionner sera évalué.
    La fonction SOMME SI permet de restreindre une somme par rapport à une condition et comprend trois arguments :

    =SOMME.SI (plage_à_analyser ; condition ; plage_à_additionner)

    Plage_à_analyser = Plage sur laquelle porte le critère
    Condition = Enoncé du critère de sélection
    Plage_à_additionner = Plage à additionner si le critère est vérifié




    Programmer une fonction : NB.SI :

    Pour connaître le nombre de salariés qui ont une ancienneté supérieure à 7 ans, utilisons la fonction NB.SI.
    La fonction NB.SI permet de compter les données d’une plage qui répondent à un critère et comprend deux arguments :

    =NB.SI(plage_à_analyser; condition; plage_nombre)

    Plage_à_analyser = Plage sur laquelle porte le critère
    Condition = Enoncé du critère de sélection
    Plage_nombre = Plage comptabilisée si le critère est vérifié




    Programmer une fonction : MOYENNE.SI :

    Pour connaître la moyenne des primes des salariés qui ont une ancienneté supérieure à 7 ans, utilisons la fonction MOYENNE.SI.
    La fonction MOYENNE.SI permet de calculer une moyenne des données d’une plage qui répondent à un critère et comprend deux arguments :

    =MOYENNE.SI(plage_à_analyser; condition; plage_moyenne)

    Plage_à_analyser = Plage sur laquelle porte le critère
    Condition = Enoncé du critère de sélection
    Plage_moyenne = Plage moyenne si le critère est vérifié





    Utiliser les fonctions statistiques simples sur Excel

    Dans Excel les formules conditionnelles permettent d'afficher un résultat si certaines conditions sont respectées ou un autre si elles ne le sont pas.
    Saisir le tableau ci-dessous :

    Utiliser les fonctions Si pour calculer une Prime dans Excel


    Programmer une formule conditionnelle : Nous allons programmer le calcul d’une prime dont les modalités de calcul sont les suivantes :
    500 € pour les personnes sont le salaire est inférieur à 8 000 €
    700 € pour les personnes dont le salaire est supérieur ou égal à 8 000 €
    Pour cela nous allons utiliser la fonction conditionnelle dont la syntaxe est la suivante :

    = Si(test_logique; valeur_si_vrai; valeur_si_faux)
    = Si(test_logique; alors valeur_si_vrai sinon valeur_si_faux)

    =SI(D2>=8000 ; 700 ; 500)

    Si le salaire est supérieur ou égal à 8 000 € alors la prime est égale à 700 € sinon la prime est égale à 500 €.

  • Cliquez la cellule E2
  • Saisissez la fonction suivante : =SI(D2>=8000;700;500) et Validez. Le résultat apparaît dans la cellule et la formule dans la barre de formule.
  • Recopier la formule sur les cellules E3 à E9
  • Cliquez le bouton de recopie de la cellule E2 et Glissez la souris en restant cliquer jusqu’à la cellule E9
  • La formule est recopiée sur toutes les cellules et les résultats sont affichés.


  • Pour contrôler ou imprimer des formules de calcul, il faut les afficher à l’écran.
    Vous pouvez aussi utiliser le raccourci CTRL + " pour afficher ou masquer les formules.
    Les formules sont visibles dans les cellules à la place des résultats.

    Maintenant que les formules sont vérifiées, nous pouvons afficher de nouveau les résultats dans les cellules à la place des formules. Utiliser le raccourci CTRL + " pour afficher ou masquer les formules

    Eléments de comparaison :

    L’élément comparé peut être :

  • A1 : une référence
  • 100 : un chiffre
  • "toto" : du texte
  • "" : du vide


  • Différentes comparaisons :

  • = est égal à
  • > est supérieur à
  • < est inférieur à
  • >= est supérieur ou égal
  • <= est inférieur ou égal à
  • <> est différent de


  • Dans la formule les valeurs résultat sont entre guillemets car il s'agit de texte mais si vos valeurs sont des chiffres, inutile d'en mettre.

     

    EXERCICES ET TUTOS

    Les fonctions conditionnelles

    EXERCICE : Collège corrigé exercice
    EXERCICE : Note de frais corrigé exercice
    EXERCICE : FonctionSI corrigé exercice

    Contact

    SARL AAT's

    N° SIREN : 822580056 - N° TVA intracommunautaire : FR40822580056