15 formules Excel qui vous aideront à résoudre des problèmes réels

Beaucoup de gens voient Microsoft Excel comme un outil uniquement utile dans les affaires. La vérité est qu’il existe de nombreuses façons dont cela peut vous être bénéfique à la maison également. La clé pour trouver des utilisations d’Excel dans la vie quotidienne est de choisir les bonnes formules qui résolvent les problèmes.

Que vous recherchiez un nouveau prêt automobile, que vous souhaitiez déterminer quel placement dans un fonds commun de placement vous convient le mieux ou que vous essayiez simplement de donner un sens à votre compte bancaire, Excel est un outil puissant qui peut vous aider.

Nous avons sélectionné 15 formules simples, puissantes et qui vous aident à résoudre des problèmes complexes.

Formules financières

Vous magasinez pour une nouvelle maison et vous êtes confus par tout le jargon hypothécaire? Vous cherchez une nouvelle voiture et vous vous sentez confus par les conditions du prêt automobile que le vendeur continue de vous lancer?

Examen de la formule Excel

N’ai pas peur. Avant de contracter un prêt, faites vos recherches avec Excel à vos côtés!

1. PMT — Paiement

Chaque fois que vous comparez des conditions de prêt et que vous souhaitez calculer rapidement votre paiement mensuel réel en fonction des différentes variations de conditions, profitez de la puissance (et de la simplicité) PMT formule.

Formule Excel PMT

Voici ce dont vous avez besoin pour utiliser cette formule:

  • Le taux d’intérêt du prêt
  • La durée du prêt (combien de paiements?)
  • Le principe de départ du prêt
  • Valeur future, si, pour une raison quelconque, le prêt sera considéré comme remboursé avant qu’il n’atteigne zéro (facultatif)
  • Type de prêt: 0 si les paiements sont dus à la fin de chaque mois, ou 1 s’ils sont dus au début (facultatif)

Voici une façon intéressante de comparer rapidement une variété de prêts pour voir à quoi ressembleront vos paiements. Créez une feuille Excel qui répertorie tous les prêts potentiels et toutes les informations disponibles à leur sujet. Ensuite, créez une colonne “Paiements” et utilisez le PMT formule.

Formule PMT Excel d'intérêt

Saisissez simplement le coin inférieur droit de la cellule PMT que vous venez de créer et faites-la glisser vers le bas pour qu’elle calcule le total du paiement pour toutes les conditions de prêt répertoriées dans la feuille. La fonctionnalité de remplissage automatique d’Excel est une fonctionnalité que vous utiliserez beaucoup avec ces astuces.

Tableau Excel d'intérêt

Vous pouvez désormais comparer les versements mensuels pour différents types de prêts.

(Un très grand merci à Mark Jones (@redtexture sur Twitter) qui a souligné que pour les formules PMT et FV, vous devez être très prudent lorsque vous utilisez la même période – dans ce cas, l’utilisation de paiements mensuels nécessite de diviser le terme d’intérêt de 12 mois)

C’est pourquoi nos lecteurs sont si grands. Merci d’avoir aidé avec ce correctif Mark!

2. FV — Valeur future

La formule suivante est utile lorsque vous cherchez à investir de l’argent dans quelque chose comme un certificat de dépôt (CD) et que vous voulez savoir ce que cela va valoir à la fin du terme.

Voici ce que vous devez savoir pour utiliser le Formule FV:

  • Le taux d’intérêt du prêt
  • Nombre de paiements (ou durée d’investissement en mois)
  • Le paiement pour chaque période (généralement mensuelle)
  • Solde de départ actuel (facultatif)
  • Type de prêt: 0 si les paiements sont dus à la fin de chaque mois, ou 1 s’ils sont dus au début (facultatif)

Comparons donc plusieurs CD en utilisant les termes que vous connaissez d’après les informations que les banques vous ont fournies. Dans l’exemple ci-dessous, disons que vous avez un héritage de 20 000 $ à investir dans un CD.

Formule Excel FV

Les taux d’intérêt sont à nouveau représentés sous forme décimale (prenez le taux d’intérêt que la banque vous a donné et divisez par 100). Les paiements sont nuls car les CD sont généralement basés sur une valeur de départ et une valeur future versée. Voici à quoi ressemble la comparaison lorsque vous utilisez la formule FV pour chaque CD que vous envisagez.

Formule de valeur future pour Excel

Sans aucun doute, le CD à intérêt plus élevé sur une période plus longue rapporte beaucoup plus. Le seul inconvénient est que vous ne pouvez toucher à aucun de vos fonds pendant trois années entières, mais c’est la nature de l’investissement!

En relation :  3 raisons pour lesquelles vous devriez utiliser un VPN avec Kodi

3-4. Formules logiques – IF et AND

De nos jours, la plupart des banques vous offrent la possibilité de télécharger l’équivalent de près d’un an de transactions bancaires dans un format comme CSV. C’est un format parfait pour analyser vos dépenses à l’aide d’Excel, mais parfois les données que vous recevez des banques sont très désorganisées.

L’utilisation de formules logiques est un excellent moyen de repérer les dépenses excessives.

Idéalement, la banque catégorise automatiquement vos dépenses ou vous configurez votre compte de sorte que les choses soient placées dans des catégories de dépenses. Par exemple, tous les restaurants que nous visitons sont étiquetés Dîner à l’extérieur étiquette.

Catégories de dépenses dans Excel

Cela facilite l’utilisation d’une formule logique pour identifier chaque fois que nous sortons pour manger et dépenser plus de 20 $.

Pour ce faire, il suffit de créer une formule logique dans une nouvelle colonne à la recherche de toute valeur où la colonne de catégorie est «DiningOut» et la colonne de transaction est supérieure à – 20 $

Remarque: La comparaison ci-dessous montre «

Voici à quoi cela ressemble:

IF AND Formula pour Excel

En utilisant SI et ET ensemble dans une formule semble difficile, mais c’est en fait assez simple. L’instruction IF affichera le montant en dollars (C2) si le ET est vraie, ou FAUX si ce n’est pas le cas. le ET L’instruction vérifie si la catégorie est «DiningOut» et si la transaction est supérieure à 20 $.

Tableau Excel pour la formule

Voilà! Sans avoir à passer au crible manuellement toutes ces transactions, vous savez maintenant exactement à quel moment vous dépensez trop dans une certaine catégorie.

Donner un sens aux listes

Les listes sont une grande partie de la vie quotidienne. Si vous gérez un ménage, vous utilisez constamment des listes. Excel possède des outils assez puissants pour être productif avec des listes de contrôle, ainsi que d’autres types de formats de liste.

5-6. COUNT et COUNTIF

Excel peut vous aider à organiser rapidement et à trier les valeurs est une liste. Prenons l’exemple PTC. Voici une liste des dons des membres de la communauté.

COUNT et COUNTIF Formula pour Excel

Nous voulons voir combien de fois le nom d’une personne apparaît sur la liste. Pour ce faire, vous pouvez combiner COMPTER formule avec un SI formule. Créez d’abord une colonne pour vérifier si la personne est Michelle ou non. La formule utilisera un SI pour remplir la cellule avec un «1» si cela est vrai.

Formule IF pour Excel

Ensuite, créez une autre colonne qui compte le nombre de fois où vous avez trouvé Michelle Johnson sur la liste.

COUNT formule pour Excel

Cela vous donne le nombre de chaque place dans la colonne E où il y a un 1 plutôt qu’un blanc.

Formule CountIf du résultat du tableau Excel

C’est donc la façon la plus simple de faire ce genre de choses, mais cela nécessite deux étapes.

6-8. SUMIF, COUNTIF, AVERAGEIF

Si cela ne vous dérange pas d’utiliser une formule légèrement plus avancée, vous pourriez envisager d’utiliser l’une des nombreuses formules combinées «IF» comme SUMIF, COUNTIF, ou MOYENNEIF. Ceux-ci vous permettent d’effectuer la formule (COUNT, SUM ou MOYENNE) si la condition logique est vraie. Voici comment cela fonctionne en utilisant l’exemple ci-dessus.

Formule COUNTIF pour Excel

Cette formule examine la colonne A, qui contient tous les noms des donateurs, et si la cellule dans la plage correspond aux critères entre guillemets, elle compte par un. Cela vous donne un compte de toutes les fois où le nom du donateur est égal à «Michelle Johnson» en une seule étape.

Tableau Excel pour la formule CountIF

C’est beaucoup plus rapide que d’utiliser deux colonnes, mais c’est un peu complexe. Utilisez donc l’approche qui convient le mieux à votre situation.

le SUMIF et MOYENNEIF les formules fonctionnent de la même manière, juste avec des résultats mathématiques différents. En utilisant SUMIF dans cet exemple vous donnerait le total des dons pour Michelle Johnson si vous l’utilisez à la place.

9. LEN

Une autre formule que vous pouvez parfois utiliser de manière créative est la formule LEN. Cette formule est l’une des nombreuses formules de texte Excel qui vous indique le nombre de caractères dans une chaîne de texte.

Une façon intéressante de l’utiliser dans l’exemple ci-dessus serait de mettre en évidence les donateurs qui ont fait un don de plus de 1 000 $ en comptant le nombre de chiffres dans la colonne don. Si la longueur du nombre est de 4 ou plus, ils ont fait un don d’au moins 1 000 $.

En relation :  Comment créer un organigramme dans Microsoft Excel

Formule LEN pour Excel

Vous pouvez maintenant ajouter un formatage supplémentaire pour le rendre plus agréable pour les yeux.

Pour ce faire, vous devez mettre en surbrillance toutes les cellules de la colonne Donation, sélectionner Accueil onglet dans le menu, et cliquez sur Mise en forme conditionnelle dans la barre d’outils. Sélectionnez ensuite Utilisez une formule pour déterminer les cellules à formater.

Formatage conditionnel Excel

Définissez la plage sous Formatez les valeurs où cette formule est vraie: à la colonne / plage où toutes vos sorties de formule LEN sont affichées.

Dans cet exemple, si vous définissez la condition «> 3», tout ce qui dépasse 1 000 $ recevra la mise en forme spéciale. N’oubliez pas de cliquer sur le Format… et choisissez le type de formatage que vous souhaitez pour ces derniers.

Aussi, une note rapide. Vous remarquerez que ma plage est définie comme «$ E2: $ E11», et non «$ E $ 2: $ E $ 11». Lorsque vous sélectionnez la plage, elle est par défaut la première, ce qui ne fonctionnera pas. Vous devez utiliser l’adressage relatif comme indiqué dans l’image ci-dessus. Ensuite, votre mise en forme conditionnelle fonctionnera en fonction de l’état de la deuxième plage.

Organisation des téléchargements bancaires et financiers

Parfois, lorsque vous téléchargez des informations d’entreprises, que ce soit votre banque ou votre compagnie d’assurance maladie, le format des données entrantes ne correspond pas toujours à ce dont vous avez besoin.

Par exemple, supposons que dans les données exportées de votre banque et que vous ayez la date au format standard.

Informations bancaires Excel

Si vous souhaitez ajouter une nouvelle colonne avec la vôtre qui est préfacée par l’année et qui inclut les informations sur le bénéficiaire (pour votre propre tri), il est très facile d’extraire des informations d’une colonne.

10-14. DROITE, GAUCHE, TEXTE et CONCATENER

Vous pouvez extraire l’année du texte dans cette colonne à l’aide du DROITE formule.

Formule CONCATENATE pour Excel

La formule ci-dessus indique à Excel de prendre le texte de la colonne D et d’extraire les quatre caractères du côté droit. le ENCHAÎNER formule formule ensemble ces quatre chiffres, avec le texte du bénéficiaire de la colonne E.

Gardez à l’esprit que si vous souhaitez extraire du texte à partir d’une date, vous devrez le convertir au format texte (au lieu de la date) à l’aide du signe «=TEXTE(D2, ”mm / jj / aaaa”) ”formule. Ensuite, vous pouvez utiliser le DROITE formule pour retirer l’année.

Et si vos informations sont à gauche? Eh bien, utilisez plutôt le LA GAUCHE formule et vous pouvez tirer le texte de gauche à droite.

ENCHAÎNER est vraiment utile lorsque vous avez du texte provenant d’un tas de colonnes différentes que vous souhaitez assembler en une longue chaîne. Il existe également plusieurs façons de séparer le texte dans Excel si vous souhaitez apprendre à manipuler complètement les chaînes.

Choisir des noms aléatoires dans un chapeau

15. RANDBETWEEN

Une dernière formule amusante est celle que vous pouvez utiliser si vous devez faire quelque chose comme choisir des noms dans un chapeau pour une fête de Noël. Rangez ce chapeau et ces bouts de papier et sortez votre ordinateur portable et lancez Excel!

Utilisation de la formule RANDBETWEEN, vous pouvez demander à Excel de sélectionner au hasard un nombre parmi une plage de nombres que vous spécifiez.

Formule RANDBETWEEN pour Excel

Les deux valeurs que vous devez utiliser sont les nombres les plus bas et les plus élevés, qui doivent être à la fin de la plage de nombres que vous avez appliquée au nom de chaque personne.

Une fois que vous avez appuyé sur la touche Entrée, la formule sélectionnera au hasard l’un des nombres dans la plage.

Formule RANDBETWEEN pour Excel

C’est à peu près aussi aléatoire et inviolable que possible. Donc, au lieu de choisir un numéro dans un chapeau, choisissez plutôt un numéro dans Excel!

Utilisation d’Excel pour les problèmes quotidiens

Comme vous pouvez le constater, Excel n’est pas uniquement destiné aux gourous de l’analyse des données et aux professionnels. Tout le monde peut bénéficier des nombreuses formules que vous trouverez dans Excel. Apprenez ces formules et vous pouvez commencer à résoudre des problèmes réels dans Excel.

N’arrêtez pas d’apprendre Excel. Il existe une longue liste de formules et de fonctions Excel que vous pouvez apprendre à utiliser, vous trouverez peut-être quelques astuces intéressantes que vous n’auriez jamais pensé qu’Excel pourrait faire.

Crédit d’image: Goodluz via Shutterstock.com

Moyens Staff
Moyens I/O Staff vous a motivé, donner des conseils sur la technologie, le développement personnel, le style de vie et des stratégies qui vous aider.