Share
Apprendre Microsoft Excel consiste à ajouter de plus en plus de formules et de fonctions à votre toolbelt. Combinez-en assez et vous pouvez pratiquement tout faire avec une feuille de calcul.
Dans ce didacticiel, vous apprendrez à utiliser trois formules Excel puissantes: SUMIF, COUNTIF, et MOYENNEIF.
Ici nous avons une liste des transactions pour le mois avec quelques données sur les dépenses.Dans la capture d'écran ci-dessus, vous pouvez voir que nous avons une liste de transactions sur le côté gauche. Si je veux garder un œil sur mes dépenses, je peux utiliser ces trois formules pour les surveiller..
A droite, le Frais de repas au restaurant box utilise les trois formules pour m'aider à suivre mes dépenses:
Plus généralement, voici ce que chacune de ces formules fait pour vous et comment vous pourriez les utiliser à votre avantage:
Ces formules vous permettent d’ajouter de la logique à votre feuille de calcul. Voyons comment utiliser chaque formule.
COUNTIF, SUMIF et AVERAGEIF dans Excel (didacticiel vidéo rapide)
Les screencasts sont l’un des meilleurs moyens de regarder et d’acquérir une nouvelle compétence, notamment de se familiariser avec ces trois formules clés dans Microsoft Excel. Regardez la vidéo ci-dessous pour voir comment je travaille dans Excel. Assurez-vous et téléchargez l'exemple de classeur gratuit à utiliser avec ce tutoriel..
Si vous préférez apprendre à l'aide d'instructions écrites pas à pas, continuez à lire. Je vais partager des conseils sur la façon d'utiliser ces formules et des idées sur leur utilité..
Utilisez l'onglet intitulé SUMIF dans l'exemple de classeur gratuit pour cette section du didacticiel.
Pensez à SUMIF comme moyen d’ajouter des valeurs qui répondent à une règle. Nous pouvons ajouter une liste de valeurs appartenant à une certaine catégorie ou toutes les valeurs supérieures ou inférieures à un montant spécifique..
Voici comment fonctionne la formule SUMIF:
= SUMIF (cellules à vérifier, que vérifier, somme des cellules qui répondent aux règles)
Revenons à mon exemple de dépense de restaurant pour apprendre la formule SUMIF. Ci-dessous, je montre une liste de mes transactions pour le mois.
J'ai une liste de transactions et je vais utiliser SUMIF pour m'aider à garder un œil sur mes dépenses.Je veux savoir deux choses:
Au lieu d’ajouter manuellement des données, nous pouvons écrire deux SUMIF formules pour automatiser le processus. Je vais mettre les résultats dans le vert Dépenses de restaurant boîte sur le côté droit. Regardons comment.
Pour connaître mes dépenses totales de restaurant, je vais résumer toutes les valeurs avec le type de dépense de "Restaurant", qui se trouve dans la colonne B.
Voici la formule que je vais utiliser pour cet exemple:
= SUMIF (B2: B17, "Restaurant", C2: C17)
Notez que chaque section est séparée par une virgule. Cette formule fait trois choses clés:
Dans cet exemple, je résume toutes les valeurs du type de dépense "Restaurant".Lorsque j'appuie sur Entrée, Excel calcule le total de mes dépenses de restaurant. Avec SUMIF, il est facile de créer ces statistiques rapides pour vous aider à surveiller les données de certains types.
Nous avons vérifié pour une catégorie spécifique, mais maintenant résumons toutes les valeurs qui sont plus grand que un montant de n'importe quelle catégorie. Dans ce cas, je veux trouver tous les achats de plus de 50 $.
Écrivons une formule simple pour trouver la somme de tous les achats supérieurs à 50 $:
= SUMIF (C2: C17, "> 50")
Dans ce cas, la formule est un peu plus simple: puisque nous additionnons les mêmes valeurs que nous testons (C2 à C17), nous devons simplement spécifier ces cellules. Ensuite, nous ajouterons une virgule et "> 50" pour n’additionner que les valeurs supérieures à 50 $..
Faites la somme de tous les achats supérieurs à 50 $ avec une simple formule Excel.Cet exemple utilise un signe supérieur à, mais pour les points bonus: essayez de résumer tous les petits achats, tels que tous les achats de 20 $ ou moins..
Utilisez l'onglet intitulé COUNTIF dans l'exemple de classeur gratuit pour cette section du didacticiel.
Tandis que SUMIF est utilisé pour ajouter des valeurs qui répondent à une certaine condition, COUNTIF comptera le nombre de fois où quelque chose apparaît dans un ensemble de données donné.
Voici le format général pour le COUNTIF formule:
= COUNTIF (cellules à compter, critères à compter)
En utilisant le même ensemble de données, comptons deux informations essentielles:
Mon premier COUNTIF examinera le type de dépense et comptera le nombre d'achats "Vêtements" dans mes transactions..
La formule finale sera:
= COUNTIF (B2: B17, "Vêtements")
Cette formule examine la colonne "Type de dépense", compte le nombre de fois qu'il voit des vêtements et les compte. Le résultat est 2.
La formule COUNTIF compte le nombre de dépenses marquées "Vêtements" et les compte.Maintenant, comptons le nombre de transactions de 100 $ ou plus dans ma liste.
Voici la formule que je vais utiliser:
= COUNTIF (C2: C17, "> 100")
Il s'agit d'une formule simple en deux parties: il suffit de pointer Excel vers la liste des données à compter et la règle à compter. Dans ce cas, nous vérifions les cellules C2 à C17 pour toutes les valeurs supérieures à 100 $..
Comptez le nombre de transactions de 100 $ + avec cette formule Excel COUNTIF.Utilisez l'onglet intitulé AVERAGEIF dans l'exemple de classeur gratuit pour cette section du didacticiel.
Enfin, voyons comment utiliser un MOYENNEIF formule. À l’heure actuelle, il n’est pas surprenant que vous puissiez utiliser AVERAGEIF pour calculer la moyenne de valeurs spécifiques, à la condition que nous donnions Excel.
Le format d'une formule AVERAGEIF est:
= AVERAGEIF (cellules à vérifier, les éléments à vérifier, moyenne des cellules qui répondent aux règles)
Le format de la formule AVERAGEIF est le plus similaire à la formule SUMIF.
Utilisons la formule AVERAGEIF pour calculer deux statistiques clés concernant mes dépenses:
Pour faire la moyenne de mes dépenses de restaurant, J'écrirai une formule AVERAGEIF pour calculer la moyenne de tous les montants en fonction de la catégorie..
= AVERAGEIF (B2: B17, "Restaurant", C2: C17)
Cette formule comporte trois parties, chacune étant séparée par une virgule:
Ici, j'utilise un AVERAGEIF pour calculer le coût moyen de mon voyage annuel au restaurant.À la fin, Excel calcule la charge de mes déplacements au restaurant. Je lui ai donné cette formule.
Vous pouvez également essayer cette formule en remplaçant "Restaurant" par une autre catégorie, comme "Vêtements".
Si je garde un œil sur mes petits achats et que je veux connaître ma moyenne, je peux écrire un AVERAGEIF pour tous les achats inférieurs à un montant..
Voici la formule que je vais utiliser pour faire cela:
= AVERAGEIF (C2: C17, "<25")
Cette formule simple vérifie simplement les valeurs de la colonne C et fait la moyenne de toutes les valeurs supérieures à 25 $..
Formule Excel simple AVERAGEIF pour les dépenses inférieures à 25 USD.Dans ce tutoriel, vous avez appris à utiliser trois formules mathématiques conditionnelles pour réviser vos données. Que vous additionniez, comptiez ou calculiez la moyenne des données, ces fonctions sont des compétences Excel avancées que vous pouvez utiliser à bon escient.
Pour toutes les formules "SI" de ce didacticiel, l'essentiel est que vous pouvez appliquer des conditions à vos calculs dans Excel..
L'apprentissage engendre l'apprentissage. Voici trois autres tutoriels Excel pour continuer à grandir:
Pouvez-vous penser à d'autres manières d'utiliser ces formules pour ajouter une logique et des conditions à votre feuille de calcul? Faites le moi savoir avec un commentaire ci-dessous.
Accentsconagua