Mark Lucas
1 
4395
14
De nombreuses personnes considèrent Excel comme un outil adapté uniquement aux applications professionnelles. 3 Des formules folles qui font des choses incroyables. 3 Des formules folles qui font des choses incroyables Les formules Excel possèdent un puissant outil de mise en forme conditionnelle. Cet article décrit trois façons d'accroître la productivité avec MS Excel. , mais lorsque vous voyez les formules simples et utiles ci-dessous, vous constaterez qu'Excel peut également vous aider à résoudre des problèmes de la vie réelle à la maison..
Que vous recherchiez un nouveau prêt auto, que vous recherchiez le meilleur placement pour un fonds commun de placement ou que vous vouliez simplement donner un sens à votre compte bancaire, Excel est un outil puissant qui peut vous aider..
Les 15 formules ci-dessous ont été soigneusement sélectionnées pour leur simplicité et pour leur efficacité à aider les gens ordinaires à résoudre des problèmes complexes. Si vous êtes un enseignant qui essaie de trier les notes de ses étudiants, un parent qui essaie de contrôler ses dépenses de ménage ou un jeune couple qui achète son premier prêt immobilier, les formules ci-dessous peuvent vous aider..
Formules financières
Shopping pour une nouvelle maison et confus par tout le jargon hypothécaire? Vous recherchez une nouvelle voiture et vous vous laissez embarrasser par les conditions du prêt-voiture que le vendeur continue de vous jeter?
N'ai pas peur. Avant de commencer à magasiner, faites vos recherches avec Excel à vos côtés!
PMT - Paiement
Chaque fois que vous êtes comparer les conditions de prêt et souhaitez déterminer rapidement votre paiement mensuel réel en fonction des variations, utilisez le puissant (et simple) Formule PMT.
Voici ce dont vous avez besoin pour utiliser cette formule:
- le taux d'intérêt en pourcentage 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 d'atteindre 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 un moyen pratique de comparer rapidement une variété de prêts pour voir à quoi ressembleront vos paiements; il suffit de créer une feuille Excel répertoriant tous les prêts potentiels et toutes les informations disponibles les concernant. Ensuite, créez un “Paiements” colonne et utiliser la formule PMT.
Saisissez simplement le coin inférieur droit de la cellule PMT que vous venez de créer et faites-la glisser pour calculer le montant total du paiement pour toutes les conditions de prêt répertoriées dans la feuille..
Comme vous pouvez le constater, lorsque vous pouvez comparer les conditions de prêt côte à côte, vous constaterez de très mauvaises réalités. Par exemple, pour un prêt auto de 19 500 dollars, si vous prenez le temps de faire la démonstration et obtenez un prêt de quelques points d’intérêt en moins, vous pourriez économiser beaucoup d’argent chaque mois et économiser des milliers de dollars sur toute la durée de la vie. prêt.
(Un très grand merci à Mark Jones (@redtexture sur Twitter) qui a souligné que pour les formules PMT et FV, vous devez faire très attention à utiliser la même période - dans ce cas, utiliser des paiements mensuels nécessite de diviser le terme d'intérêt à 12 mois)
@rdube
Erreurs flagrantes: la formule PMT & FV calcule le pourcentage par mois et non par année. Taux d'intérêt hors 12x.https: //t.co/wrC5eXFzNw- Mark Jones (@redtexture) 26 novembre 2015
C'est pourquoi nos lecteurs sont si géniaux - merci de nous aider à résoudre ce problème. Mark!
FV - Valeur future
La formule suivante est pratique lorsque vous êtes cherche à investir de l'argent quelque chose comme un CD (certificat de dépôt), et vous voulez savoir ce que le CD vaudra à la fin du terme.
Voici ce que vous devez savoir pour utiliser le Formule FV:
- 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 mensuel)
- 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 de banques en utilisant les termes que vous connaissez bien, à partir des informations fournies par les banques. Dans l'exemple ci-dessous, supposons que vous disposiez d'un héritage de 20 000 USD pour investir dans un CD..
Comme vous pouvez le constater, 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-le par 100), et les paiements sont nuls, car les CD sont généralement basés sur une valeur de départ et une valeur future. Voici à quoi ressemble la comparaison lorsque vous utilisez la formule FV pour chaque CD que vous envisagez.
Sans aucun doute, le CD d’intérêts plus élevé sur une plus longue période rapporte beaucoup plus. Le seul inconvénient, bien sûr, est que vous ne pouvez toucher aucun de vos fonds pendant trois années entières. Pour beaucoup de gens, c'est un peu trop long, mais c'est la nature de l'investissement!
Fonctions logiques (IF ou AND)
De nos jours, la plupart des banques vous permettent de télécharger des transactions bancaires d'une durée de près d'un an dans un format tel que CSV. C'est un format parfait pour analyser vos dépenses avec Excel, mais les données que vous recevez des banques sont parfois très désorganisées..
Utiliser des fonctions logiques est un excellent moyen de Dépenses ponctuelles.
Idéalement, la banque catégorise automatiquement vos dépenses ou vous avez configuré votre compte de manière à ce que les choses soient classées dans des catégories de dépenses. Par exemple, tous les restaurants où nous allons nous faire étiqueter avec le “Dîner à l'extérieur” étiquette.
Cela facilite l'utilisation d'une fonction logique pour identifier chaque fois que nous sortons manger et dépensons plus de 20 $.
Pour ce faire, il suffit de créer une fonction logique dans une nouvelle colonne, en recherchant toute valeur contenant la colonne de catégorie. “Dîner à l'extérieur” et la colonne de transaction est supérieure à - 20 $ (la comparaison ci-dessous montre “<“, less than, because the values in column C are all negative. Here's what that looks like:
Utiliser IF et AND ensemble dans une formule semble compliqué, mais si vous y réfléchissez, cela prend tout son sens. L'instruction IF génère le montant (C2) si l'instruction AND est vraie ou FALSE si elle ne l'est pas. L’instruction AND vérifie si la catégorie est “Dîner à l'extérieur” et la transaction est supérieure à 20 $.
Voilà! Sans avoir à passer au crible manuellement toutes ces transactions, vous savez maintenant exactement les moments où vous avez dépassé vos dépenses dans une certaine catégorie. Comme d'habitude, les formules Excel s'avèrent un gain de temps considérable.
Donner un sens aux listes
Les listes font partie intégrante de la vie quotidienne. Si vous gérez un ménage, vous utilisez constamment des listes.
Vous faites peut-être partie du comité parents-enseignants et vous avez reçu une longue liste de dons de la communauté. C'est à vous de déterminer qui dans la communauté a fait un don à un projet d'école spécifique et combien. Ou peut-être que vous dirigez une entreprise familiale et que vous payez la demi-douzaine d'employés que vous employez, et que vous devez résumer leurs dépenses individuelles.
COUNT et COUNTIF
Quelle que soit la liste, Excel peut vous aider à organiser et à trier rapidement les valeurs. Prenons l'exemple de PTC. Voici une liste des dons des membres de la communauté.
Si vous voulez voir combien de fois le nom d'une personne apparaît dans la liste, vous pouvez combiner la formule COUNT avec une formule IF. Donc, commencez par créer une colonne pour vérifier si la personne est Michelle ou non. Si c'est le cas, faites de la cellule un 1. Si ce n'est pas le cas, faites-en un espace ” “.
Ensuite, créez une autre colonne qui compte combien de fois vous avez trouvé Michelle Johnson dans la liste..
Cela vous donne le nombre de places dans la colonne E où il y a un 1 plutôt qu'un blanc.
Donc, c’est la façon la plus simple de faire ce genre de chose, mais cela nécessite deux étapes..
Si cela ne vous dérange pas d’utiliser une fonction légèrement plus avancée, vous pouvez envisager d’utiliser l’un des nombreux outils combinés. “SI” fonctionne comme SUMIF, COUNTIF, ou MOYENNEIF. Ils vous permettent d’exécuter la fonction (COUNT, SUM ou AVERAGE) uniquement si la condition logique est vraie. Voici comment cela fonctionne en utilisant l'exemple ci-dessus.
Cette formule examine la colonne A, qui contient tous les noms de donneurs. Si la cellule de la plage correspond aux critères entre guillemets, elle compte jusqu'à un. Cela vous donne un compte de toutes les fois où le nom du donneur est égal à “Michelle Johnson” en une seule étape.
C'est beaucoup plus rapide que d'utiliser deux colonnes, mais pour certaines personnes, c'est peut-être un peu trop complexe - utilisez donc l'approche qui vous convient le mieux..
Fonction LEN
Une autre fonction que vous pouvez utiliser de manière créative est parfois la fonction LEN. Cette fonction vous indique le nombre de caractères contenus dans une chaîne de texte. Gain de temps avec les opérations de texte dans Excel Gain de temps avec les opérations de texte dans Excel Excel peut faire de la magie avec des chiffres et gérer les caractères de manière égale. Ce manuel explique comment analyser, convertir, remplacer et modifier du texte dans des feuilles de calcul. Ces bases vous permettront d’effectuer des transformations complexes. .
Une façon intéressante d'utiliser ceci dans l'exemple ci-dessus serait de mettre en évidence les donateurs qui ont fait plus de 1 000 dollars en comptant le nombre de chiffres dans la colonne des dons..
Ensuite, si vous le souhaitez, vous pouvez même formater une autre colonne en fonction de la valeur de la colonne E; le résultat de la fonction LEN.
Pour ce faire, vous devez mettre en surbrillance toutes les cellules de la colonne Don, sélectionnez la Accueil onglet dans le menu, et cliquez sur Mise en forme conditionnelle dans la barre d'outils. Puis sélectionnez Utilisez une formule pour déterminer les cellules à formater.
Définir la plage sous Mettez en forme les valeurs où cette formule est vraie: à la colonne / plage où toutes vos sorties de fonction LEN sont affichées.
Dans cet exemple, si vous faites la condition “> 3”, alors, tout ce qui dépasse 1 000 $ recevra le formatage spécial. N'oubliez pas de cliquer sur le Format… bouton et choisissez quel type de formatage spécial vous voulez pour ces.
En outre, une note rapide. Vous remarquerez que ma gamme est définie comme “E2 $: E11 $”, ne pas “$ E $ 2: $ E $ 11”. Pour une raison quelconque, lorsque vous sélectionnez la plage, la valeur par défaut est l'ancienne, ce qui ne fonctionnera pas. Vous devez utiliser un adressage relatif comme indiqué dans l'image ci-dessus. Ensuite, votre mise en forme conditionnelle fonctionnera en fonction de la condition de la deuxième plage..
Organisation des téléchargements bancaires et financiers
Parfois, lorsque vous téléchargez des informations à partir d’entreprises - qu’il s’agisse de votre banque, de votre compagnie d’assurance maladie ou de tout autre endroit où vous exercez des activités, 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, la date soit donnée au format standard.
Si vous souhaitez ajouter une nouvelle colonne de votre propre nom, préfixée par année et incluant les informations sur le bénéficiaire (à des fins de tri), l'extraction d'informations dans une colonne est très simple..
DROITE, GAUCHE, TEXTE et CONCATENER
Vous pouvez extraire l'année du texte de cette colonne à l'aide de la touche DROITE une fonction.
La fonction 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 les fonctions réunissent ces quatre chiffres, avec le texte du bénéficiaire de la colonne suivante.
N'oubliez pas que si vous souhaitez extraire du texte d'une date, vous devrez le convertir au format texte (au lieu de la date) à l'aide de la commande “= TEXT (D2,”mm / jj / aaaa”)” une fonction. Ensuite, vous pouvez utiliser la fonction DROITE pour sortir l'année.
CONCATENATE est vraiment utile lorsque vous souhaitez rassembler du texte provenant d'un groupe de colonnes différentes en une longue chaîne. Par exemple, vous pouvez concaténer le prénom, le nom de famille, l'adresse municipale, la ville et le statut d'une personne dans un champ contenant l'adresse complète de cette personne dans une seule colonne..
Choisir des noms aléatoires dans un chapeau
Une dernière fonction amusante est celle que vous pouvez utiliser si vous devez faire quelque chose comme choisir des noms parmi un chapeau Comment obtenir un modèle de ticket de tombola gratuit pour Microsoft Word Comment obtenir un modèle de billet de tombola gratuit pour Microsoft Word travail. Laissez-nous vous en décharger et vous montrer comment trouver des modèles et personnaliser des billets de tombola gratuits. Tout ce dont vous avez besoin est un traitement de texte. pour une fête de Noël. Rangez votre chapeau et ces bouts de papier et sortez votre ordinateur portable et lancez Excel!
Utiliser la fonction RANDBETWEEN, vous pouvez demander à Excel de sélectionner au hasard un nombre compris dans une plage de nombres que vous spécifiez.
Les deux valeurs que vous devez utiliser sont les nombres les plus bas et les plus élevés, qui doivent se situer aux extrémités de la plage de chiffres que vous avez appliquée au nom de chaque personne..
Une fois que vous avez appuyé sur la touche Entrée, la fonction sélectionnera aléatoirement l'un des nombres compris dans l'intervalle..
C'est à peu près aussi aléatoire et inviolable que possible. Au lieu de choisir un numéro dans un chapeau, choisissez plutôt un numéro dans Excel!
Utiliser Excel pour les problèmes quotidiens
Comme vous pouvez le constater, Excel n’est pas réservé aux gourous de l’analyse de données et aux professionnels de l’entreprise. Tout le monde peut tirer parti des nombreuses fonctions cachées dans Excel..
Cet article vous a-t-il donné une idée créative de la manière dont vous pouvez utiliser Excel pour résoudre des problèmes de votre vie? Avez-vous déjà utilisé certaines de ces fonctions Excel de manière créative?? Partagez vos propres idées et histoires dans la section commentaires ci-dessous, j'aimerais les entendre.!
Crédit d'image: Goodluz via Shutterstock.com