Aide-mémoire sur les formules Excel : Guide intermédiaire

Vous avez donc réussi à maîtriser les bases d'Excel , bravo à vous ! Maintenant, vous avez décidé de passer à la vitesse supérieure et de vous plonger dans les choses les plus avancées, n'est-ce pas ? Eh bien, vous avez de la chance car nous avons créé une feuille de triche Excel intermédiaire pratique rien que pour vous !

Cette feuille de triche intermédiaire Microsoft Excel couvre un large éventail de sujets qui amélioreront vos compétences en analyse numérique, en traitement de texte et en logique complexe de date et d'heure.

En utilisant Excel Intermediate Cheat Sheet comme référence de bureau, vous pouvez approfondir les formules et les fonctions les plus puissantes de Microsoft Excel.

Maintenant, assurez-vous de lire la suite, mais imprimez et enregistrez également la feuille de triche ci-dessous !

Commençons!

Excel-formules-cheat-sheet-intermediate-1 Télécharger

Table des matières

• Formules mathématiques intermédiaires
  • Logique conditionnelle
• Formules statistiques intermédiaires
  • 1.MINA et MAXA
  • 2. NBVAL et NBSI
• Formules Excel pour l'analyse financière
  • 1. Fonction PRODUIT
  • 2. Fonctions QUOTIENT et LOG
  • Fonctions pour les scénarios d'investissement
• Formules de date et d'heure Excel intermédiaires
  • 1. Fonctions du mois intermédiaire
  • 2. Fonctions de la semaine intermédiaire
  • 3. Fonctions de temps intermédiaires
• Fonctions Excel pour l'algèbre conditionnelle et logique
  • 1. Fonction SI
  • 2. Fonction ET
  • 3. Fonction OU
  • 4. PAS Fonction
• Cellules de référence
  • 1. Fonction INDIRECTE
  • 2. Fonction INDEX
  • 3. Fonction de décalage
• Formules de texte intermédiaires
  • 1. Fonction TEXTJOIN
  • 2. Fonction REMPLACER
  • 3. Fonction REMPLACEMENT
• Formules de recherche intermédiaires
• Formules de gestion des erreurs Microsoft Excel
  • 1. Fonction SIERREUR
  • 2. Fonction IFNA
• Raccourcis de formule et de fonction
• Formatage de cellule intermédiaire
• Dernières pensées

Formules mathématiques intermédiaires

Notre aide-mémoire pour débutant couvrait les fonctions mathématiques les plus courantes pour débutants, telles que la fonction SOMME. Les utilisateurs intermédiaires doivent se familiariser avec ces fonctions plus complexes :

• ABS() renvoie la valeur absolue d'un nombre.

• SQRT() renvoie la racine carrée d'un nombre

• RAND() fournit un nombre aléatoire entre 0 et 1

Logique conditionnelle

Les utilisateurs intermédiaires doivent également savoir comment effectuer des calculs mathématiques basés sur plusieurs conditions à l'aide de ces fonctions :

• SUMIFS(plage_somme, plage_critères1, critères1, [….])

• NB.SI(critères_plage1, critères1, [critères_plage2, critères2], …)

• MOYENNESI(critères_plage1, critères1, [critères_plage2, critères2], …)

Les fonctions évaluent les critères fournis et produisent une valeur logique VRAI ou FAUX.

Supposons que vous ayez les données de ventes suivantes dans les colonnes A, B et C :

Pour calculer la somme des ventes de t-shirts rouges, utilisez la formule SUMIFS :

=SUMIFS(C2:C6, A2:A6, "T-shirt", B2:B6, "Rouge")

Pour compter le nombre de lignes avec des t-shirts rouges, utilisez la formule COUNTIFS :

=COUNTIFS(C2:C6, A2:A6, "T-shirt", B2:B6, "Rouge")

Pour calculer les ventes moyennes de t-shirts rouges, utilisez la formule AVERAGEIFS :

=AVERAGEIFS(C2:C6, A2:A6, "T-shirt", B2:B6, "Rouge")

Formules statistiques intermédiaires

Outre les fonctions de base telles que MIN et MAX, Excel propose une gamme plus large de formules statistiques pour les utilisateurs intermédiaires. Voici quelques-uns des plus utiles :

• MINE

• MAXA

• COUNTA

• NB.SI

1.MINA et MAXA

Les fonctions MIN et MAX les plus couramment utilisées ignorent les valeurs textuelles.

Les fonctions alternatives MINA et MAXA prennent en compte à la fois le texte et les nombres lors de la recherche des valeurs les plus élevées ou les plus basses. Les valeurs de texte sont évaluées comme si elles étaient nulles.

Les différences sont illustrées dans l'image ci-dessous. La première ligne évalue une valeur minimale de 10, tandis que la deuxième ligne évalue comme 0 en raison de la présence d'une valeur de texte.

2. NBVAL et NBSI

La fonction NBVAL est utilisée pour compter le nombre de cellules non vides dans une plage.

La fonction NB.SI est moins spécifique car elle est utilisée pour compter le nombre de cellules dans une plage qui répondent à une condition ou à des critères spécifiques.

Vous pouvez également utiliser ces fonctions pour compter le nombre de valeurs distinctes dans une colonne. Cette vidéo montre comment procéder.

Formules Excel pour l'analyse financière

Il existe plusieurs formules que vous voudrez utiliser lors de l'exécution d'analyses financières telles que la prévision des investissements :

• PRODUIT(numéro1, [numéro2…])

• QUOTIENT(numérateur, dénominateur)

• LOG(nombre, [base])

1. Fonction PRODUIT

Supposons que vous vouliez calculer la valeur future d'un investissement initial de 1 000 $ (cellule B1) à un taux d'intérêt annuel de 4 % (cellule B2) après cinq ans (cellule B3).

Utilisez la fonction PRODUIT pour calculer la valeur future avec cette formule :

=B1 * PRODUIT(1 + B2)^B3

Cette formule retournera la valeur future de votre investissement.

2. Fonctions QUOTIENT et LOG

L'étape suivante consiste à calculer combien d'années il faudra pour qu'un investissement double de valeur au taux d'intérêt donné.

Utilisez la fonction QUOTIENT en combinaison avec la fonction LOG pour calculer ceci comme suit :

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

Cette formule renverra 17 avec les données de l'échantillon, indiquant qu'il faudra 17 ans pour que l'investissement double de valeur à un taux d'intérêt annuel de 4 %.

Cette image montre les formules dans une feuille de calcul Excel :

Fonctions pour les scénarios d'investissement

Voici quelques fonctions qui conviennent à des scénarios financiers spécifiques. L'un d'eux peut être exactement ce dont vous avez besoin :

• NPV - La fonction NPV (Net Present Value) calcule la valeur actuelle nette d'un investissement sur la base d'une série de flux de trésorerie futurs basés sur un taux d'actualisation.

• ACCRINT – La fonction ACCRINT calcule les intérêts courus d'un titre qui paie des intérêts périodiques. Ceci est utile pour déterminer les intérêts gagnés sur un titre depuis la dernière date de paiement jusqu'à une date de règlement donnée.

• INTRATE – La fonction INTRATE calcule le taux d'intérêt pour un titre entièrement investi.

• PMT – La fonction PMT calcule le paiement total sur un titre de créance.

• IRR – La fonction IRR fournit le taux interne d'un rendement.

• RENDEMENT – La fonction RENDEMENT fournit le rendement d'un titre en fonction du taux d'intérêt, de la valeur nominale et de l'échéance.

Formules de date et d'heure Excel intermédiaires

Les fonctions de date et d'heure de base dans Excel incluent les fonctions MAINTENANT et AUJOURD'HUI pour la date actuelle. Les utilisateurs intermédiaires doivent également savoir extraire des composants à partir d'une date donnée à l'aide de :

• JOUR(date)

• MOIS(date)

• ANNEE(date)

La formule =MOIS(“23 avril 2023″) renverra un résultat de 4 pour le 4ème mois. De même, les fonctions JOUR et ANNÉE renverront respectivement 23 et 2023.

1. Fonctions du mois intermédiaire

Les utilisateurs intermédiaires devront parfois ajouter ou soustraire des mois à une date et trouver la fin d'un mois.

• EDATE(date_début, nombre_de_mois)

• EOMONTH(date_début, nombre_de_mois)

Par exemple, la formule =EDATE(“23 avril 2023”, 2) calculera la date deux mois plus tard.

La formule =EOMONTH(23 April 2023", 2) calcule la fin du mois deux mois plus tard.

Le résultat est "30 juin 2023", qui tient compte du fait qu'il n'y a que trente jours en juin. Si vous spécifiez 3 mois, le résultat sera "31 juillet 2023".

2. Fonctions de la semaine intermédiaire

Vous pouvez utiliser une combinaison des fonctions SUM et WEEKDAY pour compter le nombre de jours de travail entiers dans une plage de dates.

La fonction WEEKDAY renvoie le jour de la semaine pour une date donnée. Le deuxième paramètre détermine le système de numérotation, la valeur par défaut étant du dimanche au samedi.

Si vous avez une série de dates dans une plage de cellules de B1:B40, utilisez cette formule pour calculer les jours ouvrés :

=SOMME(–(JOUR SEMAINE(B1:B40,2)>5))

3. Fonctions de temps intermédiaires

La fonction YEARFRAC calcule la fraction d'année entre deux dates.

La fonction TIMEVALUE est utilisée pour convertir une heure représentée sous forme de texte en un nombre décimal représentant la proportion d'une journée de 24 heures.

Le résultat est un numéro de série Excel où 1 représente une journée complète de 24 heures, 0,5 représente 12 heures, 0,25 représente 6 heures, etc.

Supposons que vous ayez une heure au format texte "17:45" dans la cellule A1 et que vous souhaitiez la convertir en nombre décimal . C'est la formule :

=TIMEVALUE(A1)

La valeur 0,74 représente la proportion de la journée de 24 heures qui s'est écoulée à l'heure quinze minutes avant six heures.

Utilisez pour changer la cellule d'heure d'un format d'heure à un format de texte. Vous voudrez peut-être formater cela en pourcentage pour être plus clair.

Fonctions Excel pour l'algèbre conditionnelle et logique

Les fonctions conditionnelles et logiques sont essentielles pour la prise de décision dans Excel. Les fonctions suivantes peuvent être combinées avec les formules Excel plus basiques pour une logique puissante :

1. SI(condition, value_if_true, value_if_false) :

2. ET(état1, état2, …)

3. OU(état1, état2, …)

4. NON(condition1, condition2, …)

La fonction SI évalue une condition et renvoie des valeurs différentes selon que la condition est vraie ou fausse.

Les fonctions AND, OR et NOT vérifient quelles conditions sont vraies et vous permettent de prendre des décisions en conséquence.

Voici quelques exemples basés sur les données de ventes que nous avons utilisées auparavant.

1. Fonction SI

Supposons que vous souhaitiez afficher le texte « Low Sales » si les ventes des articles sont inférieures à 15. Si les ventes sont supérieures, vous souhaitez afficher « High Sales » dans une nouvelle colonne.

Utilisez cette formule et copiez-la dans les autres lignes :

=SI(C2<15, « Ventes faibles », « Ventes élevées »)

2. Fonction ET

Supposons que vous souhaitiez afficher VRAI ou FAUX pour les t-shirts avec au moins 15 ventes. Utilisez cette formule :

=ET(A3=”T-Shirt”, C3>=15)

3. Fonction OU

Supposons que vous vouliez tester plusieurs conditions et renvoyer TRUE si l'une des conditions est remplie et FALSE sinon, vous utiliseriez la fonction OR.

Par exemple, si nous avons des scores d'un test dans les cellules A1 et B1, et que nous voulons savoir si l'un ou l'autre des scores est supérieur à 80, nous pourrions utiliser : =OR( A1>80, B1>80) . Cela renverra TRUE si l'un des scores (ou les deux) est supérieur à 80, et FALSE si les deux sont 80 ou moins.

4. PAS Fonction

Supposons que vous souhaitiez vérifier si l'article n'est pas un sweat à capuche. Utilisez cette formule :

=PAS(A2=”Sweat à capuche”)

Cellules de référence

Un débutant se familiarisera rapidement avec l'utilisation de références absolues et relatives telles que $A$1 ou B2. Les utilisateurs intermédiaires doivent se familiariser avec l'utilisation des références indirectes, des index et des décalages :

1. INDIRECT(réf_texte)

2. INDEX(plage, num_ligne, num_colonne)

3. OFFSET(reference_cell, lignes, colonnes) :

1. Fonction INDIRECTE

La fonction INDIRECT renvoie la valeur de la référence de cellule spécifiée saisie sous forme de texte. Pour référencer la cellule B3, utilisez cette formule :

=INDIRECT("B3")

L'avantage par rapport à l'utilisation de simples références de cellule est que cette fonction vous donne une référence dynamique. Cela signifie qu'Excel met automatiquement à jour la référence lorsque la structure de la feuille de calcul change, par exemple une ligne est supprimée.

2. Fonction INDEX

La fonction INDEX est utilisée pour référencer des cellules dans une plage spécifiée en fonction des numéros de ligne et de colonne.

Pour référencer la cellule B3 dans un tableau de six lignes, utilisez cette formule :

=INDICE(B1:B6, 3)

3. Fonction de décalage

La fonction DECALER renvoie une cellule ou une plage qui est éloignée d'un nombre spécifié de lignes et de colonnes d'une cellule de référence.

Si vous souhaitez afficher la valeur de la cellule qui est à deux cellules et une cellule en dessous de A1, utilisez cette formule :

=DECALAGE(A1, 2, 1)

Voici les formules en action :

Formules de texte intermédiaires

Les débutants doivent connaître les fonctions de texte comme LEFT, qui extrait un ou plusieurs caractères du côté gauche d'une chaîne. Un utilisateur expérimenté doit être familiarisé avec des fonctions telles que celles-ci :

1. TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

2. REMPLACER(old_text, start_num, num_chars, new_text)

3. REMPLACER(texte, ancien_texte, nouveau_texte, [instance_num])

1. Fonction TEXTJOIN

La fonction TEXTJOIN concatène des cellules avec le délimiteur que vous fournissez. Vous pouvez également spécifier où ignorer les cellules vides.

Pour produire une liste délimitée par des virgules de certains éléments de la colonne A, utilisez cette formule :

=TEXTEJOIN(“, “, VRAI, A2:A4)

2. Fonction REMPLACER

La fonction de remplacement vous permet de spécifier la position de départ et la longueur de la chaîne que vous souhaitez échanger dans la cible.

Pour remplacer les deux premiers caractères d'une chaîne par "XX", utilisez cette formule :

=TEXTEJOIN(A2, 1, 2, "XX")

3. Fonction REMPLACEMENT

La fonction de substitution remplace les occurrences spécifiées d'une chaîne de texte dans une autre chaîne de texte par une nouvelle chaîne de texte.

Pour remplacer les occurrences du mot "Jeans" par "Leggings", utilisez cette formule :

=SUBSTITUT(A3, "Jeans", "Leggings")

Voici les formules en action :

Formules de recherche intermédiaires

Les fonctions de recherche courantes telles que VLOOKUP et MATCH sont couvertes dans la feuille de triche du débutant.

Les utilisateurs intermédiaires doivent se familiariser avec la fonction de recherche CHOISIR :

• CHOISIR(num_index, valeur1, [valeur2, …])

La fonction CHOISIR renvoie une valeur à partir d'une liste de valeurs basée sur un numéro d'index spécifié.

Supposons que vous ayez une colonne de tailles de t-shirts étiquetées 1, 2 ou 3. Vous souhaitez afficher une catégorie de petites, moyennes et grandes respectivement sur la même ligne. Utilisez cette formule :

=CHOISIR(A1, "Petit", "Moyen", "Grand")

Formules de gestion des erreurs Microsoft Excel

La feuille de triche pour débutant répertorie les messages d'erreur typiques que vous verrez lorsque vous travaillez avec Excel. Les utilisateurs intermédiaires doivent être capables d'utiliser des formules pour gérer les erreurs avec élégance.

1. SIERREUR(valeur, si_erreur)

2. IFNA(valeur, valeur_if_na)

1. Fonction SIERREUR

Les erreurs de division par zéro sont courantes, mais l'affichage par défaut d'Excel est #DIV/0 ! ne sera pas clair pour tous les utilisateurs.

Utilisez la fonction SIERREUR pour remplacer l'erreur standard par "Impossible de diviser par zéro" avec cette formule :

=SIERREUR(A1/B1, "Impossible de diviser par zéro")

Cette image montre l'erreur brute dans la troisième ligne et la fonction SIERREUR gérant la quatrième ligne.

2. Fonction IFNA

La fonction IFNA dans Excel est utilisée pour intercepter et gérer les erreurs #N/A.

Une autre section de cette feuille de triche a montré la fonction CHOISIR. Cette fonction renverra une erreur N/A si le numéro d'index n'est pas un entier.

Supposons qu'une taille de t-shirt ait été saisie comme 1,5, l'utilisateur pensant à tort que cela affichera une catégorie quelque part entre petite et moyenne. Au lieu de cela, il affichera une erreur #N/A .

Pour fournir une erreur plus utile, utilisez cette formule :

=IFNA(CHOISIR(A2, "Bas", "Moyen", "Elevé"), "Indice invalide")

Raccourcis de formule et de fonction

Il existe plusieurs raccourcis clavier qui peuvent vous permettre de travailler plus efficacement avec votre feuille de calcul.

• F2 : Modifier la cellule active et positionner le point d'insertion à la fin du contenu de la cellule.

• F9 : Calcule et affiche le résultat de la partie sélectionnée d'une formule.

• Ctrl + Maj + Entrée : Saisissez une formule matricielle.

• Maj + F3 : Ouvre la boîte de dialogue Insérer une fonction.

• Esc : Annule la saisie d'une formule et revient au contenu de la cellule d'origine.

Certains de ces raccourcis Excel peuvent ne pas être disponibles dans toutes les langues ou dispositions de clavier.

Formatage de cellule intermédiaire

Dans l'analyse financière, le formatage approprié des cellules contenant des nombres, des dates et des devises est crucial. Pour formater des cellules, procédez comme suit :

1. Sélectionnez les cellules que vous souhaitez formater.

2. Faites un clic droit sur les cellules sélectionnées et choisissez "Formater les cellules".

3. Sélectionnez la catégorie appropriée (par exemple, Nombre, Devise, Date) et appliquez le format souhaité.

Certains types de formatage courants à prendre en compte dans la finance incluent :

• Devise : 1 234,56 $

• Pourcentage : 12,34%

• Comptabilité : (1 234,00 $)

• Date : 10-Mai-2023

Dernières pensées

Au fur et à mesure que vous deviendrez plus à l'aise avec les différentes formules et fonctions mises en évidence dans cette aide-mémoire , vous vous retrouverez mieux équipé pour gérer des tâches et des projets de plus en plus complexes.

La pratique est essentielle pour maîtriser n'importe quelle compétence, alors n'ayez pas peur d'expérimenter ces formules dans votre travail quotidien. Ils apporteront votre expertise du niveau débutant au niveau intermédiaire.

Ceci n'est qu'un avant-goût de ce que les fonctions intermédiaires d'Excel peuvent faire pour simplifier votre travail, rationaliser vos analyses et vous aider à découvrir des informations à partir de vos données.

La vraie magie se produit lorsque vous commencez à combiner les fonctions de notre aide-mémoire et que vous les adaptez à vos besoins uniques. Alors, ne vous arrêtez pas là. Continuez à explorer, continuez à expérimenter et vous découvrirez qu'Excel n'est pas seulement un outil, c'est un changeur de jeu !

Vous avez envie de passer au niveau supérieur ? Consultez notre aide-mémoire sur les formules avancées d'Excel.