Le top 10 des formules Excel

Le top 10 des formules Excel que vous devez connaître

Dans Microsoft Excel, il existe nativement plus de 400 fonctions… avec une réelle utilité. Oui mais ici on livre les 10 formules Excel indispensables à connaître !

 

Réaliser des agrégations multicritères

Une fonction simple qui va vous faire gagner beaucoup de temps : SOMME.SI.ENS(). Elle permet de réaliser des sommes en fonction de 1, 2, 3… critères.

Elle fonctionne en plusieurs zones :

  1. La “Plage_Somme” : cette première zone correspond à la plage de cellules où se trouvent les valeurs à sommer;
  2. La “Plage_Critère1” : cette seconde zone correspond à la plage de cellules où se trouvent les critères de recherche;
  3. Le “Critère1” : cette troisième zone correspond au critère recherché.

Les points 2 et 3 pouvant se répéter autant de fois qu’il y a de filtres à mettre en oeuvre dans la formule. Voici ci-dessous un exemple avec deux conditions :

 

Réaliser une moyenne multicritère

Sur le même principe que la fonction précédente, utiliser la formule MOYENNE.SI.ENS().

Cette fonction vous permet de réaliser des moyennes en excluant par exemple les valeurs à 0 !

 

Effectuer une recherche sans modifier le format d’un tableau

Les fonctions “classiques” de recherche dans Excel permettent de rechercher, soit dans la première ligne d’un tableau (RechercheH), soit dans la première colonne (RechercheV). Parfois, il est nécessaire d’effectuer une recherche à partir de la dernière colonne d’un tableau sans avoir la possibilité de modifier le format de ce dernier… Il existe une solution en utilisant deux fonctions Excel.

La fonction INDEX(), va permettre de sélectionner une plage de cellules. Ensuite nous allons imbriquer la fonction EQUIV() qui permet de connaître la position du critère recherché dans une plage donnée. Ainsi avec la fonction Equiv(), il est possible de connaître le numéro de la ligne (ou de colonne) où se trouve le critère.

Nous reprenons le même exemple et nous cherchons ici à obtenir le produit vendu ayant l’ID “5” :

Utilisez cette méthode de recherche en remplacement des traditionnels RechercheV et RechercheH car elle est beaucoup plus rapide et ouvre la voie de l’optimisation de vos fichiers !

 

Gérer simplement les erreurs dans vos modèles

Savoir gérer les erreurs dans Excel est important pour gagner en lisibilité et éviter ainsi les #Div/0!, #Valeur!… avec les nouvelles versions d’Excel, il existe une manière très simple de les gérer !

Utiliser la fonction SIERREUR() : son fonctionnement est simple, imaginons que vous avez une formule donnant une division par 0, cette dernière affiche dans ce cas là #Div/0!. Il suffit d’enrichir cette fonction de la manière suivante :

SIERREUR([votre formule] ; [indiquer ici ce que doit afficher Excel en cas d’erreur])

Par exemple si l’on souhaite voir “0” s’afficher en cas d’erreur : SIERREUR(A1/B2;0).

 

Afficher la date du jour dans une cellule

Vous avez besoin d’afficher la date du jour dans une cellule pour mettre en page de manière précise un tableau de bord ? Si à chaque fois que vous imprimez vous devez saisir la date manuellement arrêtez tout de suite !  Utilisez la fonction MAINTENANT(). Sans aucun paramètre, cette fonction affiche la date du jour avec la précision de l’heure.

MAINTENANT()

 

Calculer le nombre de jours entre deux dates

Si vous avez des éléments concernant des âges à calculer, un délais d’attente entre deux dates… vous pouvez utiliser la fonction JOURS360(). Cette fonction va vous permettre de compter le nombre de jours qui se sont écoulés entre deux dates. Ainsi la fonction prend deux paramètres… la date de début et la date de fin.

JOURS360([Date de début du calcul] ; [Date de fin du calcul])

 

Calculer simplement l’annuité constante d’un emprunt

Il existe énormément de fonctions financières dans Excel, difficile d’en choisir une, mais celle qui permet de calculer l’annuité constante d’un emprunt est très intéressante et utile lorsqu’on réalise des simulations économiques et financières.

La fonction VPM() permet de calculer l’annuité de la manière suivante :

VPM(taux de l’emprunt ; nombre de période ; valeur de l’emprunt)

Attention vous devez être synchrone entre le taux et le nombre de périodes. Ainsi, si vous connaissez le taux annuel de l’emprunt, le nombre de périodes doit correspondre au nombre d’années de l’emprunt. Si vous connaissez le taux mensuel, le nombre de périodes correspond au nombre de mois de l’emprunt.

 

Extraire la partie gauche d’une cellule

Imaginons que vous importez une base de données de laquelle vous souhaitez extraire une partie d’un code pour enrichir vos analyses… par exemple vous disposez des codes postaux et vous souhaitez en déduire dans une colonne supplémentaire les départements… utilisez la fonction GAUCHE().

Cette fonction permet d’extraire un certain nombre de caractères à partir d’une chaîne de texte ou de valeurs contenue dans une cellule. Dans l’exemple suivant, on extrait les deux caractères des codes postaux pour en calculer notre colonne des départements :

 

Gérer des calculs complexes

La dernière fonction que nous sélectionnons dans ce top 10 des fonctions à connaître et maîtriser est la fonction SOMMEPROD(). C’est une fonction avec de multiples possibilités (agrégation complexe, compteur, calcul matriciel…) nous allons prendre en exemple un calcul permettant de filtrer et de réaliser une moyenne en fonction d’une pondération. Ici nous réalisons un calcul du prix moyen en fonction d’une pondération.

Ici TABLE35[Produit] correspond aux données colonne A, TABLE35[PRIX] correspond aux données colonne C, même principe pour la TABLE35[POIDS Dans le PRODUIT] colonne D.


 Résumé

Avec 10 fonctions Excel, vous êtes en mesure de réaliser de grands modèles Excel, des modèles qui pourront gagner en temps de calcul et en lisibilité notamment. Par ailleurs, ces fonctions vous ouvrent les portes de la découverte et de la compréhension de nouvelles formules qui peuvent en découler. Nous avons vu la fonction GAUCHE(), intuitivement vous imaginez bien qu’il existe une fonction DROITE() etc…

  1. SOMME.SI.ENS()
  2. MOYENNE.SI.ENS()
  3. INDEX()
  4. EQUIV()
  5. SIERREUR()
  6. JOURS360()
  7. MAINTENANT()
  8. VPM()
  9. GAUCHE()
  10. SOMMEPROD()

SmartEx organise des formations dédiées à la modélisation économique et financière sous Excel, n’hésitez pas à consulter notre page dédiée à la formation et à nous contacter pour plus d’informations sur les autres services que nous proposons.

Laisser un commentaire