EXCEL - FAIRE LA SOMME DES n PLUS GRANDES VALEURS AVEC CONDITIONS (Formules SOMME + GRANDE.VALEUR)
HTML-код
- Опубликовано: 29 фев 2020
- ► Recevez le fichier Excel de la vidéo + des exercices complets (avec sujet et corrigé sur Excel) de la formation Excel GRATUITEMENT : cours.excel-en-ligne.fr
► Lien vers les formations Excel : aidebtscgo.kneo.me/shop/categ...
▬▬▬ 📝 RÉSUMÉ DE LA VIDÉO ▬▬▬▬▬▬▬
Dans cette vidéo, je vous montre comment faire la somme des n plus grandes valeurs avec conditions sur Excel.
-----------------------------------------------------------------------
Pour faire la somme des n plus grandes valeurs il faut déjà retrouver ces n plus grandes valeurs ; Pour cela il faut utiliser la formule excel GRANDE.VALEUR.
Syntaxe de la formule GRANDE.VALEUR :
= GRANDE.VALEUR( matrice ; k )
- matrice = La plage de données dans laquelle on cherche la k-ième plus grande valeur.
- k = La position de la plus grande valeur à renvoyer (Exemple : si k = 2 pour renvoyer la 2ème plus grande valeur de la plage de données).
-----------------------------------------------------------------------
Dans l'exemple on a la liste des ventes réalisées dans 2 villes (Lyon et Paris) sur 2 produits différents (Table et chaise) et on souhaite retrouver les 3 plus grands CA réalisés dans la ville de Lyon pour le produit "Table".
On a un tableau avec en colonnes :
- Ville
- Produit
- CA
Et 2 listes déroulantes :
- Une pour le choix de la ville
- Une pour le choix du produit
-----------------------------------------------------------------------
1) Retrouver les 3 plus grandes valeurs de la liste de CA.
Pour cela il faut donc utiliser la formule GRANDE.VALEUR et définir k pour les valeurs 1, 2 et 3.
= GRANDE.VALEUR($E$3:$E$12 ; 1)
= GRANDE.VALEUR($E$3:$E$12 ; 2)
= GRANDE.VALEUR($E$3:$E$12 ; 3)
Pour regrouper ces 3 valeurs sur une seule formule il faut mettre les valeurs de k entre accolades "{ }"
=GRANDE.VALEUR($E$3:$E$12 ; {1;2;3} )
*$E$3:$E$12 = Liste contenant les CA réalisés
*{1;2;3} = Argument k pour retrouver les 3 plus grandes valeurs de la liste de CA
---------------
2) Faire la somme des 3 plus grandes valeurs de la liste de CA.
Maintenant qu'on a regrouper les 3 plus grandes valeurs de la liste de CA dans une seule formule il ne reste plus qu'à faire la somme de ces 3 valeurs.
Pour cela il suffit de rajouter une formule SOMME devant la formule GRANDE.VALEUR :
= SOMME(GRANDE.VALEUR($E$3:$E$12 ; {1;2;3}))
---------------
3) Rajouter la condition de la ville pour retrouver les plus grandes valeurs dans la ville de Lyon.
Le résultat obtenu par la formule précédente permet de faire la somme des 3 plus grandes valeurs de la liste de CA par contre il n'y a aucune distinction ni pour la ville ni pour le produit.
Pour ajouter une condition il faut créer une matrice condition à l'intérieur de la formule GRANDE.VALEUR :
Attention : Sur les versions Excel 2019 et abonnement office 365 on peut continuer d'utiliser la formule SOMME à ce stade, par contre pour les versions antérieures il faut remplacer la formule SOMME par la formule SOMMEPROD.
= SOMME(GRANDE.VALEUR( ($A$3:$A$12=$F$5) * ($E$3:$E$12) ;{1;2;3} ))
*($A$3:$A$12 = $F$5) = Première condition : La ville doit être celle sélectionnée dans la liste déroulante en cellule F5.
*($E$3:$E$12) = Matrice des résultat à savoir la liste des CA dans l'exemple.
---------------
4) Rajouter la condition du produit pour retrouver les plus grandes valeurs dans la ville de Lyon concernant le produit "Table".
Il ne reste plus qu'à rajouter la 2ème condition concernant le produit ; Pour cela on rajoute une matrice qui concerne la liste des produits :
= SOMME(GRANDE.VALEUR( ($A$3:$A$12=$F$5) * ($B$3:$B$12=$G$5) * ($E$3:$E$12) ; {1;2;3} ))
*($A$3:$A$12 = $F$5) = 1ère condition : La ville doit être celle sélectionnée dans la liste déroulante des villes en cellule F5.
*($B$3:$B$12=$G$5) = 2ème condition : Le produit doit être celui sélectionné dans la liste déroulante des produits en cellule G5.
*($E$3:$E$12) = Matrice des résultat à savoir la liste des CA dans l'exemple.
= On retrouve alors bien le bon résultat, à savoir les 3 plus gros CA réalisés dans la ville de Lyon pour le produit "Table".
-----------------------------------------------------------------------
#Excel #FormuleExcel #FormationExcel
► Le site : excel-en-ligne.fr/
► FaceBook : / axelrobinformations
EXCEL - FAIRE LA SOMME DES n PLUS GRANDES VALEURS AVEC CONDITIONS (Formules SOMME + GRANDE.VALEUR) : • EXCEL - FAIRE LA SOMME...
J ai appris des choses. Merci
Merci ! J'espère vous en faire découvrir de nouvelles prochainement
Merci pour ce tuto 👍👍👍👏👏👏👏
Merci pour le commentaire ! à bientôt
Bonjour, super tuto, mais quand il y a une cellule vide ça ne marche pas du coup je suis obligé de mettre un 0 mais sur une autre cellule je compte le nombre de cellule qui contiennent un chiffre et le 0 en fait parti mais je ne souhaite pas le compter.
Merci pour votre aide.
Bonjour, j'aimerais savoir comment faire une condition pour que la formule ne retourne que le nombre de plus grandes valeurs que j'ai choisi préalablement. La condition serait que je ne veux que la formule ne retourne que la somme des x plus grands nombres, avec x que je choisis via une autre cellule.
Petite précision à 10:34 = Vous pouvez même sur une version Excel antérieure à Excel 2019 (ou abonnement office 365) utiliser la formule SOMME, il faudra simplement la valider en formule matricielle avec les touches "ctrl + maj + entrée" alors qu'avec la formule SOMMEPROD vous pourrez la valider directement avec la touche "entrée".
Je me suis inscrit avec mon adresse mail, j'ai reçu mon mail de confirmation, j'ai confirmé.
Comment reçoit-on les fichiers excel de vidéos ?
@@tomguill8446 Bonjour, comme indiqué dans le premier mail reçu il suffit de répondre au premier mail en indiquant la vidéo visionnée
Lorsque j'utilise la formule grande valeur dans la boîte de dialogue j'obtiens bien un bon résultat mais lorsque j'appuie sur entrer ça n'exécute pas