30 formules Excel pratiques et
indispensables à connaître
… Son
Je vous propose 3
- Les formules de Mathématiques
- Les formules de Logique
- Les formules de Statistiques
- Les formules de Date et Heure
- Les formules de Texte
- Les formules de Recherche de données au sein du fichier.
Les Formules de Mathématiques
SOMME
La fonction SOMME consiste à faire l’addition d’un ensemble de données sur une plage de cellules ►=SOMME(A1;A2;A3). Pour aller plus vite, Excel propose de sélectionner une plage de cellules à ses extrémités, plutôt qu’en détaillant la totalité des données qui la composent, comme le montre l’exemple suivant :
SOMME.SI
La fonction SOMME.SI prend en compte un critère dans l’addition à effectuer. Dans l’exemple ci-dessous, le critère permet de faire l’addition que des ventes journalières inférieures à 45 unités.
Les formules de Logique
SI
La fonction SI pose une condition à respecter. Elle s’applique à de nombreux domaines, et s’imbrique avec de nombreuses autres formules. ►=SI(test logique ; Valeur si vrai ; Valeur si faux). La valeur si faux est optionnelle.
ET
La fonction ET permet de regrouper plusieurs conditions qui doivent être vraies au sein d’une formule. Dans cet exemple, elle est utilisée en combinaison avec la formule SI :
OU
La fonction OU est similaire à la fonction ET, mais sans obligation de vérité de plusieurs conditions. Il suffit qu’une des conditions soit remplie.
Dans l’exemple ci-dessous, on considère un jour de vente normal comme étant compris entre 20 et 60. Toute vente inférieure ou supérieure sera considérée comme anormale :
NB
La fonction NB compte les valeurs numériques dans une plage définie, que ce soit une cellule, une ligne ou une colonne.
Dans cet exemple, la fonction NB sert à compter le nombre de jours où chaque salarié travaille. Les cellules vides sont exclues du compte.
Les formules de statistiques
NB.SI
La fonction NB.SI permet de compter le nombre de cellules selon certains critères. En voici un exemple avec la comptabilisation du nombre de jours où l’objectif de 45 unités vendues est atteint :
NB.VIDE
La fonction NB.VIDE compte le nombre de cellules vide au sein d’une plage données. Voici un exemple :
MOYENNE
La fonction MOYENNE permet de calculer la moyenne d’une série de données. Voici un exemple :
Les formules de date et heure
Les formules de date et d’heure permettent de connaître et d’isoler rapidement des informations issues d’une donnée de date pour ensuite l’analyser grâce à d’autres fonctions.
ANNEE
Pour obtenir l’année correspondante à une date précise. Exemple : =ANNEE(« 15/04/2022 ») renvoie l’année 2022.
MOIS
Pour obtenir le mois : =MOIS(« 15/04/2022 ») renvoie la valeur 04.
JOUR
Pour obtenir le jour :=JOUR(« 15/04/2022 ») renvoie la valeur 15.
JOURSEM
Cette fonction permet de connaître le jour de la semaine associée à la valeur. Exemple : =JOURSEM(« 15/04/2022 »;2) renvoie le chiffre 5, car le vendredi est le cinquième jour de la semaine. Pour commencer la semaine le lundi, il faut utiliser le paramètre 2 (comme dans l’exemple ci-dessus).
NO.SEMAINE
Renvoie le numéro de semaine correspondant à une date. Exemple : =NO.SEMAINE(AUJOURDHUI();2) renvoie le nombre 16, car nous sommes à la semaine 16. Le paramètre 2 permet d’indiquer que les semaines commencent le lundi.
HEURE
Pour obtenir l’heure : =HEURE(« 12:30:40 ») renvoie 12.
MINUTE
Pour obtenir les minutes : =MINUTE(« 12:30:40 ») renvoie 30.
AUJOURDHUI
Cette fonction permet d’afficher la date du jour. Exemple : =AUJOURDHUI() pour afficher la date du jour. Vous pouvez également afficher le lendemain en utilisant =AUJOURDHUI()+1 et ainsi de suite.
MAINTENANT
Cette fonction permet d’obtenir la date exacte (année, mois, jour, heure, minute, seconde). L’affichage dépend du format de la cellule. Exemple : =MAINTENANT() pour afficher la date et l’heure exactes.
Les formules de texte
Du texte a été utilisé pour illustrer les formules, mais vous pouvez le remplacer par les numéros de cellules correspondants au texte que vous souhaitez modifier.
CNUM
Si une chaîne de caractère comporte un nombre et du texte, seul le nombre est retranscrit. Pratique pour supprimer les € ! Exemple : =CNUM(« 54€ ») = 54.
Si 54 € est dans la cellule A6 ► =CNUM(A6) = 54.
CONCATENER
Une fonction très utile permettant d’assembler plusieurs chaînes de caractères. Exemple : =CONCATENER(« Carole Office »; « , » ; « Assistante Indépendante ») permet d’obtenir : Carole Office, Assistante Indépendante.
MAJUSCULE
Pour convertir un texte en majuscules sur Excel =MAJUSCULE(« assistante indépendante – office manager ») renvoie la valeur suivante : ASSISTANTE INDÉPENDANTE – OFFICE MANAGER.
MINUSCULE
C’est exactement l’inverse :=MINUSCULE(« ASSISTANTE ») renvoie assistante.
NBCAR
Permet de compter le nombre de caractères contenus dans une chaîne de texte, espaces compris. Exemple : =NBCAR(« Tout problème a une solution. S’il n’y a pas de solution, c’est qu’il n’y a pas de problème. ») renvoie le nombre 92, puisque cet extrait comporte 92 caractères, espaces compris.
NOMPROPRE
Transforme la première lettre de tous les mots d’une chaîne de caractères. Exemple : =NOMPROPRE(« carole office ») permet d’obtenir Carole Office.
REMPLACER
Permet de remplacer les caractères d’une plage de données. Exemple : =REMPLACER(« commercial »;4;9; « unication ») va remplacer commercial par communication. Le second paramètre (4) permet de commencer le remplacement au quatrième caractère, le troisième paramètre (9) indique le nombre de caractères remplacés.
La fonction SUBSTITUE
La fonction SUBSTITUE permet de remplacer des portions de texte dans d’autres cellules. Voici comment s’applique cette formule :
Les formules de recherche et de référence
RECHERCHE
La fonction RECHERCHE permet de mettre en parallèle plusieurs plages pour trouver la valeur d’une cellule liée à une autre. Dans l’exemple suivant, supposez que le nombre de ventes le plus faible est connu, mais pas la typologie de facture correspondante dans une base de données bien plus vaste et difficile à traiter. Pour cela, la fonction RECHERCHE vient trouver cette valeur (soit 24), rappeler la liste de valeurs qu’elle intègre (D2 à D6) et donner une liste de valeurs parallèles (A2 à A6) pour trouver celle liée aux 24 ventes journalières (11).
RECHERCHEH
La fonction RECHERCHEH permet de chercher des valeurs selon la première ligne d’une matrice. Dans cet exemple, la formule vient cibler l’intitulé « Ventes » et la sélection de la plage D1 à D6 en demandant la valeur comprise dans la ligne 4 de cette même plage.
RECHERCHEV
La fonction RECHERCHEV permet de chercher des valeurs selon la première colonne d’une matrice. Ici, l’objectif est de connaître les ventes pour la facture n°7 sur la période du 1er au 5 février 2020.
La fonction vient prendre en compte la valeur 7 en A10 pour la retrouver dans la large sélection de données A2 à D6, en fournissant la valeur en colonne 4 correspondante, soit en D. Le FAUX qui termine la formule permet d’imposer à la fonction de fournir une valeur exacte, soit 42.
RECHERCHEX
La fonction RECHERCHEX simplifie la sélection de données pour le même résultat, en épargnant certains calculs accessoires. Voici un exemple :
Le saviez-vous ? Microsoft met à disposition gratuitement de nombreux modèles de fichiers Excel à télécharger et à personnaliser pour répondre à de nombreux besoins ► Bibliothèque Office – Modèles Excel
📰 Sources : Le Blog du Modérateur & Hubspot.fr
