Les trois plus couramment utilisé des formules dans Excel pour effectuer de simples calculs mathématiques sont le COMTE, SOMME et MOYENNE. Si vous êtes à la gestion d’un budget financier dans Excel ou tout simplement garder une trace de vos prochaines vacances, vous avez probablement utilisé l’une de ces fonctions avant.
Dans cet article, nous allons passer en revue les bases de ces trois fonctions et leur pertinentes et utiles homologues: COUNTIFS, SUMIFS et AVERAGEIFS.
Disons que nous sommes au début d’une nouvelle entreprise en ligne de vente de téléphones mobiles et nous avons une feuille qui répertorie les ventes que nous avons fait dans les deux premiers mois. Télécharger exemple de feuille de calcul Excel ici.
Excel le nombre, la SOMME et la MOYENNE
Pour savoir combien de téléphones mobiles que nous avons vendus, on peut rapidement utiliser la NUMÉRATION formule comme indiqué ci-dessous:
=COUNT(E2:E16)
D’autre part, pour obtenir le montant total des ventes que nous avons fait, nous pouvons utiliser la SOMME formule comme indiqué ci-dessous:
=SOMME(E2:E16)
Enfin, pour trouver le moyen de ventes que nous avons fait pour tous les téléphones, on peut utiliser le MOYEN de la formule ci-dessous:
=MOYENNE(E2:E16)
Le résultat devrait être comme ci-dessous:
Le nombre, la SOMME et la MOYENNE des formules ne fonctionnent que pour les enregistrements pour lesquels la valeur de la cellule est au format. Tout enregistrement dans la formule de la gamme (c’est à dire E2:E16 dans cet exemple) ne sont pas dans le format de nombre sera ignoré.
Ainsi, assurez-vous que toutes les cellules dans le nombre, la SOMME et la MOYENNE de la formule sont tous formatés en tant que Nombre, pas de Texte. Essayez d’utiliser la même formule, mais avec E:E comme la plage au lieu de E2:E16. Il sera de retour le même résultat qu’avant, car il ignore l’en-tête (c’est à dire le Prix de Vente), qui est au format texte.
Maintenant, si nous voulons connaître le nombre de ventes, le montant total des ventes et le montant moyen des ventes par téléphone, juste pour ceux qui sont vendus dans les etats-unis? C’est là que COUNTIFS, SUMIFS et AVERAGEIFS jouer un rôle important. Observer la formule ci-dessous:
COUNTIFS
La formule de répartition:
- =COUNTIFS( Le “=” indique le début d’une formule dans la cellule et COUNTIFS est la première partie de la Excel fonction que nous utilisons.
- D2:D16 – se Réfère à la plage de données pour vérifier si elle remplit les critères pour être inclus dans le décompte de la formule.
- “Etats-unis” – les Critères à rechercher dans la plage de données spécifiée (D2:D16)
- ) – Fermeture de parenthèse indiquant la fin de la formule.
La formule renvoie 6, qui est le nombre de ventes pour les produits expédiés à partir des états-unis de l’entrepôt.
SUMIFS
La formule de répartition:
- =SUMIFS( Le “=” indique le début de la formule de nouveau.
- E2:E16 – se Réfère à la plage de données que nous aimerions total, c’est à dire le prix de vente dans notre exemple.
- D2:D16 – se Réfère à la plage de données pour vérifier si elle remplit les critères pour être inclus dans le montant total.
- “Etats-unis” – les Critères à rechercher dans la plage de données spécifiée (D2:D16)
- ) – Fermeture de parenthèse indiquant la fin de la formule.
La formule montre acompte de 6 050$ total des ventes qui ont été faites pour des produits expédiés à partir des états-unis de l’entrepôt.
AVERAGEIFS
La formule de répartition:
- =AVERAGEIFS( Le “=” indique le début de la formule.
- E2:E16 – se Réfère à la plage de données que nous tenons à la moyenne. Dans cet exemple, nous voulons obtenir le montant moyen des ventes pour tous les téléphones vendus aux états-unis.
- D2:D16 – se Réfère à la plage de données pour vérifier si elle remplit les critères pour être inclus dans la moyenne de la formule.
- “Etats-unis” – les Critères à rechercher dans la plage de données spécifiée
- ) – Fermeture de parenthèse indiquant les extrémités de la formule.
La formule montre, nous avons vendu le produit pour environ 1 008$de plus par téléphone aux etats-unis.
Tous les trois formule peut prendre plus d’un des critères. Par exemple, si nous voulons connaître les mêmes chiffres (c’est à dire le COMTE, SOMME et MOYENNE) pour les produits vendus dans les etats-unis, mais uniquement pour le Samsung de marque, nous avons juste besoin d’ajouter de la plage de données à vérifier, suivi de ses critères.
Voir l’exemple ci-dessous où une deuxième critère est ajouté aux critères des contrôles. (Texte en bleu indique le premier critère et le rouge indique le deuxième critère)
=COUNTIFS(D2:D16,”états-unis”, B2:B16,”Samsung”)
=SUMIFS(E2:E16,D2:D16,”états-unis”, B2:B16,”Samsung”)
=AVERAGEIFS(E2:E16,D2:D16,”états-unis”, B2:B16,”Samsung”)
Vous remarquerez que Excel a également NB.SI, SUMIF et AVERAGEIF formules sans le suffixe “S”. Ceux-ci sont utilisées similaire à COUNTIFS, SUMIFS et AVERAGEIFS. Toutefois, ceux qui, sans le suffixe “S” dans la formule sont à la limitation de permettre l’un des critères par la formule.
Comme la syntaxe est légèrement différente, je vous conseille d’utiliser COUNTIFS, SUMIFS et AVERAGEIFS seulement qu’il peut être utilisé pour l’un des critères ou plus, si nécessaire. Profitez-en!