Excel cache des fonctions peu connues mais puissantes, utiles au quotidien pour l’analyse et l’automatisation. Ces outils permettent de réduire des tâches manuelles répétitives et d’améliorer la fiabilité des calculs.
Je présente des fonctions pratiques, des exemples concrets et des cas d’usage adaptables. Ces éléments essentiels sont utiles pour gagner du temps et éviter des erreurs fréquentes, conduisant à la synthèse suivante :
A retenir :
- Arrondis multiples précis pour rapports financiers et échéanciers
- Conversions d’unités fiables pour ingénierie, logistique et calculs scientifiques
- Recherche et extraction rapides de valeurs récurrentes dans de larges tables
- Gestion d’erreurs automatisée, validations et agrégations robustes pour reporting
Arrondis et conversions avancés : FLOOR, CEILING et CONVERT
Partant de ces besoins, les arrondis précis et les conversions robustes deviennent indispensables. La paire FLOOR et CEILING permet d’aligner des valeurs sur un multiple défini, pratique en comptabilité. La fonction CONVERT convertit entre unités, utile pour ingénieurs et logisticiens.
FLOOR et CEILING pour arrondis par multiple
Ce lien technique illustre l’utilisation de FLOOR et CEILING pour arrondir selon un multiple. Par exemple, la formule =FLOOR(4.4,2) arrondit 4,4 vers le bas au multiple de deux.
Exemples d’arrondis pratiques : Cette série d’exemples montre des usages concrets en comptabilité et planification, avec implications pour les rapports périodiques.
- Arrondir salaires à un multiple de centimes
- Aligner échéances sur des intervalles fixes
- Générer rapports financiers sans arrondis incohérents
CONVERT pour basculer entre unités sans erreur
Cette logique complète l’arrondi en permettant des conversions fiables entre unités diverses. Par exemple, =CONVERT(A1, »C », »F ») convertit Celsius en Fahrenheit sans calcul manuel. Entourer CONVERT de SIERREUR évite les erreurs lors des entrées manquantes.
Fonction
Syntaxe
Exemple d’utilisation
FLOOR
FLOOR(valeur, multiple)
=FLOOR(4.4,2) pour arrondir vers le bas
CEILING / PLAFOND
CEILING(valeur, multiple)
=CEILING(5.6,2) pour arrondir vers le haut
CONVERT
CONVERT(valeur, « de », « à »)
=CONVERT(A1, »C », »F ») pour Celsius en Fahrenheit
SIERREUR
SIERREUR(valeur, valeur_si_erreur)
=SIERREUR(CONVERT(B1, »cm », »in »), » ») pour sécuriser
Statistiques et recherche : MODE.SNGL, DELTA, GESTEP, INDEX et EQUIV
En prolongement des conversions, l’analyse statistique et la recherche de valeurs deviennent cruciales. Selon Microsoft, MODE.SNGL remplace l’ancien MODE pour une meilleure précision sur des jeux de données. Ces fonctions aident à repérer les valeurs fréquentes et à tester des égalités ou seuils.
MODE.SNGL et DELTA pour fréquences et égalités
Ce point détaille l’usage de MODE.SNGL pour modes et de DELTA pour comparaisons numériques. Par exemple, =MODE.SNGL(A1:A5) renvoie le nombre le plus fréquent dans la plage. DELTA renvoie 1 en cas d’égalité et 0 sinon, utile pour les contrôles automatiques.
« J’ai détecté rapidement la référence la plus fréquente dans mes ventes grâce à MODE.SNGL. »
Alice B.
Combiner GESTEP, INDEX et EQUIV pour localiser et tester
Ce passage explique comment GESTEP complète INDEX et EQUIV pour retrouver et valider des valeurs. Utilisez =GESTEP(A1,4) pour tester un seuil, et combinez INDEX+EQUIV pour extraire une cellule ciblée dans une matrice. Selon How-To Geek, INDEX+EQUIV reste une combinaison robuste pour remplacer des recherches manuelles.
Cas d’usage fréquents : Ces scénarios montrent comment automatiser la recherche de fournisseur et la récupération de tarifs, indispensables en gestion des stocks.
- Identifier fournisseurs à partir d’une référence produit
- Vérifier seuils de stock par rapport au minimum requis
- Extraire valeurs sur plusieurs critères avec INDEX+EQUIV
Action
Formule
Usage
Trouver position
EQUIV(valeur, plage, 0)
Localiser une référence précise
Extraire valeur
INDEX(plage, ligne, colonne)
Récupérer le montant associé
Combinaison
INDEX(…,EQUIV(…),EQUIV(…))
Remplacer une recherche croisée manuelle
Tester seuil
GESTEP(valeur, pas)
Retour binaire pour contrôle de stock
« En combinant INDEX et EQUIV j’ai réduit mes recherches manuelles à quelques secondes. »
Paul D.
Nettoyage et calculs avancés : TRANSPOSE, FILTRE, X.RECHERCHE, AGREGAT
Par conséquent, le nettoyage et les calculs globaux permettent d’obtenir des tableaux exploitables. Selon Microsoft, l’usage de fonctions dynamiques comme FILTRE et TRANSPOSE facilite la restructuration des données. Maîtriser ces outils rend les exports plus fiables pour le reporting.
Réorganiser matrices avec TRANSPOSE et filtrer lignes avec FILTRE
Cette section montre comment TRANSPOSE et FILTRE modifient la forme des données pour l’analyse. TRANSPOSE retourne une matrice transposée, idéale pour pivoter rapports sans copier-coller. FILTRE permet d’isoler lignes répondant à des critères, simplifiant les revues manuelles.
Astuce de nettoyage : Utilisez SUPPRESPACE puis TRANSPOSE pour retirer espaces parasites avant transformation, cela améliore l’exactitude des jointures. Entourer les opérations par SIERREUR évite les ruptures dans les tableaux dynamiques.
« Le responsable projet explique que FILTRE a transformé le traitement des données en équipe. »
Claire M.
Calculs robustes avec X.RECHERCHE, AGREGAT, SOMMEPROD et NB.VALIDES
Ce point aborde les fonctions de calcul robustes pour synthèses et validations d’indicateurs. X.RECHERCHE remplace souvent RECHERCHEV pour plus de souplesse et pour gérer les valeurs manquantes affichées si_non_trouvé. AGREGAT permet d’agréger en ignorant les erreurs, utile en présence de données imparfaites.
Calculs et validations : SOMMEPROD sert aux calculs conditionnels sans colonne intermédiaire, NB.VALIDES compte les entrées valides, et DÉCALER alimente des plages dynamiques pour graphiques. Selon How-To Geek, X.RECHERCHE et AGREGAT accélèrent les tableaux de bord réactifs.
- Utiliser X.RECHERCHE pour recherches multi-colonnes sans contraintes
- AGREGAT pour agrégations robustes malgré erreurs #DIV/0!
- SOMMEPROD pour sommes pondérées sans colonnes auxiliaires
« AGREGAT et SOMMEPROD ont réduit nos erreurs de reporting de manière sensible. »
Marc L.
« J’ai automatisé la facturation grâce à RECHERCHEX et AGREGAT, cela m’a fait gagner des heures. »
Paul D.
Source : Microsoft, « Fonctions Excel », Microsoft Support, 2024 ; How-To Geek, « 11 fonctions Excel peu connues », How-To Geek, 2023 ; Journal du Net, « Fonctions Excel à connaître », Journal du Net, 2022.