Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 1 PETIT GUIDE EXCEL Somm

Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 1 PETIT GUIDE EXCEL Sommaire : 1) La fonction =SOMME() Pour additionner entre elles plusieurs cellules contiguës. 2) La formule =SOMME.SI() Cette fonction vous permet d'additionner plusieurs cellules en fonction d'un critère déterminé. 3) La formule =SOMME.SI.ENS() Cette formule permet d'additionner plusieurs cellules en fonction de plusieurs critères. 4) La formule =SOMMEPROD() Réalisation d'opérations conditionnelles comptant et additionnant en utilisant des conditions multiples. 5) La fonction =MOYENNE() Pour obtenir la moyenne de plusieurs cellules contiguës. 6) La formule =ARRONDI() Pour éviter que le résultat d’une division ou d’une multiplication soit un nombre comportant plusieurs décimales. 7) La formule =RECHERCHEV() Pour chercher une valeur dans la première colonne d’un tableau puis afficher le contenu de la cellule qui se situe sur la même ligne que la valeur recherchée. 8) La formule INDEX() et EQUIV() La fonction INDEX utilisée avec la fonction EQUIV permet la recherche de valeur dans une matrice. 9) Choix des références de cellules Référence Absolue, relative, mixte, nommée 10) Nota Bene N'oubliez pas que vous pouvez combiner ces fonctions entre elles. 11) Création d’un graphique Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 2 1) La fonction =SOMME() Pour additionner entre elles plusieurs cellules contiguës, on peut utiliser la fonction SOMME d’EXCEL. • Ecrire dans la cellule où doit apparaître le résultat : =somme( • Sélectionner ensuite à la souris la plage de cellules à additionner. • Valider avec la touche Entrée La fonction renvoie l’addition des sommes sélectionnées. Exemple : • En A5 on écrit la formule suivante : =SOMME(A1:A4) Le résultat donne : 31 2) La formule =SOMME.SI() Cette fonction vous permet d'additionner plusieurs cellules en fonction d'un critère déterminé. Voici sa syntaxe : =SOMME.SI(plage;critère;somme_plage) plage est la plage de cellule contenant le critère à retenir critère est LE critère à retenir (chiffre ou texte) somme_plage est la plage de cellule où se trouvent les nombres à additionner. Exemple : Dans la colonne A on a écrit des nombres. En colonne B, on a indiqué "garçon" ou "fille". On voudrait obtenir le total des nombres de la colonne A auxquels correspondent le mot "fille" en colonne B. Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 3 • En A7 on écrit la formule suivante : =SOMME.SI(B1:B6;"fille";A1:A6) Le résultat donne : 66 3) La formule =SOMME.SI.ENS() Cette formule permet d'additionner plusieurs cellules en fonction de plusieurs critères. Voici sa syntaxe : =SOMME.SI.ENS(Plage_somme ;Plage_critères1;Critères1;[plage_critères2;critères2]; ….) Exemple : Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 4 Ici : • Plage_somme, la plage de cellule contenant les montant à additionner, (dans la formule D:D), • Première page de critères ; la colonne du service, la colonne C (dans la formule C:C), • Premier critère, le service saisi en cellule G1, • Deuxième plage de critères, la colonne du sexe, la colonne B (dans la formule B:B), • Deuxième critère, le sexe saisie en cellule F2, 4) La formule =SOMMEPROD() Réalisation d'opérations conditionnelles comptant et additionnant en utilisant des conditions multiples Les formules permettant de calculer le nombre de valeurs ou les sommes sont fondées sur deux conditions ou plus. La fonction que nous allons utiliser est SOMMEPROD ; celle-ci appartient à la catégorie Math & Trigo. Exemple : Compter le nombre de mois où un commercial fait une vente. Le tableau de départ pourrait se présenter sous cette forme : Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 5 Exemple 1 : Compter le nombre des ventes faites par Jules sur le mois de février. C'est une formule conditionnelle qui emploie deux conditions. = SOMMEPROD((A2:A13="Jules")*(B2:B13="Février")) Résultat de formule: 2 Exemple 2 : Compter les mois où les ventes sont plus grandes qu'une quantité spécifique Vous pouvez employer une formule semblable à celle ci-dessus pour travailler avec des données numériques. Cette formule renvoie le nombre de lignes pour lesquelles le mois est égal à janvier et le montant est supérieur à 200. = SOMMEPROD((B2:B13="Janvier")*(C2:C13>200)) Résultat de formule: 5 Exemple 3 : Compter le nombre de ventes comprises entre certaines valeurs spécifiques Cette formule compte le nombre de fois où les ventes sont comprises entre 200 et 500. La formule emploie deux conditions : la quantité supérieure ou égale à 200, et la quantité inférieure ou égale à 500. =SOMMEPROD((C2:C13>=200)*(C2:C13<=500)) Résultat de formule: 6 Exemple 4 : Compter le nombre de ventes basées sur trois conditions Cette formule renvoie le nombre de fois où le commercial est Jules, le mois est février, et la quantité est supérieure à 500. Cette fonction emploie un argument simple, qui se compose de trois expressions logiques multipliées les unes aux autres. =SOMMEPROD((A2:A13="Jules")*(B2:B13="Février")*(C2:C13>500)) Résultat de formule: 1 Exemple 5 : Totaliser les ventes d'un commercial pour un mois donné La formule calcule la somme des ventes basée sur deux conditions : quand le commercial est Jules et le mois est février. Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 6 =SOMMEPROD((A2:A13="Jules")*(B2:B13="Février")*(C2:C13)) Résultat de formule: 1150 Exemple 6 : Totaliser les ventes combinées pour deux commerciaux La formule ci-dessous renvoie la somme des ventes pour Jules et Pascal. Cette formule emploie un argument simple et se compose de trois expressions. =SOMMEPROD(((A2:A13="Jules")+(A2:A13="Pascal"))*(C2:C13)) Résultat de formule: 2585 Attention : les deux premiers arguments ne sont pas multipliés comme dans les exemples précédents. Dans la mesure où nous réalisons un test logique " OU ", ils sont additionnés. Addition de deux résultats logiques de valeur 1 quand l'une ou l'autre des expressions est VRAIE. 5) La fonction =MOYENNE() Pour obtenir la moyenne de plusieurs cellules contiguës, on peut utiliser la fonction MOYENNE d’EXCEL. • Ecrire dans la cellule où doit apparaître le résultat : =moyenne( • Sélectionner ensuite à la souris la plage de cellules désirée • Valider avec la touche Entrée. La fonction renvoie la moyenne des sommes sélectionnées Exemple : Dans la colonne A on a écrit des nombres. On voudrait obtenir la moyenne des nombres de la colonne A. • En A7 on écrit la formule suivante : =MOYENNE(A1:A6) Le résultat donne : 26 Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 7 6) La formule =ARRONDI() Le résultat d’une division ou d’une multiplication peut donner un nombre comportant plusieurs décimales. Dans le but de créer des formulaires destinés à des tiers (exemple facture) les nombres doivent, en principe, se présenter avec simplement deux décimales. On peut, bien entendu, utiliser le format des nombres. Mais Excel va conserver en mémoire le nombre initial avec plusieurs décimales et l’utilisera pour les calculs ultérieurs. D’où des problèmes d’affichages. Exemple : Le résultat du calcul : 2 divisé par 3 donne le résultat 0.666666666..... Si on additionne 0.66666666.... avec 0.6666666.... on obtient 1.33333333..... Avec un format des nombres à deux décimales, Excel propose un arrondi de présentation qui donnera : 0.67 + 0.67 = 1.33. Evidemment, cet arrondi de présentation n’est pas satisfaisant. Il faut donc utiliser une formule d’EXCEL, la formule ARRONDI() 7) LA Formule =RECHERCHEV() La fonction RECHERCHEV cherche une valeur dans la première colonne d’un tableau puis elle affiche le contenu de la cellule qui se situe sur la même ligne que la valeur recherchée. Prenons un exemple : Nous voulons afficher le prénom d’un candidat en fonction de son numéro. Ici, nous cherchons le prénom du candidat 8. • Dans « Valeur_cherchée », nous entrons E2 qui correspond à 8. • Dans « Table_matrice », nous définissons une plage de cellule qui contient le tableau. Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 8 • Dans « No_index_col », nous devons entrez le n° de la colonne qui contient les données à afficher (ici les prénoms). Le n° de colonne est celui du tableau sélectionné. • « Valeur_proche » est facultatif, mais permet soit de chercher la valeur exacte de « Valeur_cherchée » (en entrant : FAUX), soit de trouver la valeur la plus proche de « Valeur_cherchée » (en entrant : VRAI ou en laissant vide). On obtient finalement le prénom de la candidate 8. Maintenant, il ne reste plus qu’à modifier E2 pour trouver un autre prénom. Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 9 8) Les formules INDEX() et EQUIV() La fonction INDEX utilisée avec la fonction EQUIV permet la recherche de valeur dans une matrice. Prenons un exemple : Nous souhaitons pouvoir trouver l’âge et/ou la ville en fonction du prénom (peut être appliqué également en fonction de l’âge ou de la ville). Voici la formule : =INDEX(C3:E12;EQUIV(H4;C3:C12;0);I6) • C3:E12 : matrice de recherche • H4 : valeur recherchée (ici le prénom) • C3:C12 : plage de cellule dans laquelle H4 sera recherchée (ici les prénoms) • 0 : recherche la valeur exacte (1 : valeur supérieure / -1 : valeur inférieure) • I6 : n° de la colonne contenant les valeurs à afficher (ici les villes) En modifiant I6 on obtient : Petit Guide Excel Stéphane ROUBIEU et Guillaume HARENT 10 Remarque : Dans cet exemple, nous avons utilisé la syntaxe suivante : =INDEX(matrice;EQUIV(valeur_cherchée;matrice;0);no_colonne) Celle-ci est également utilisable (dans cette version, le résultat renvoyé par la fonction EQUIV sera le n° de uploads/Marketing/ petit-guide-excel.pdf