Après des années passées à manipuler des feuilles de calcul complexes et désordonnées, j'ai découvert quatre fonctions Excel qui me font gagner des heures de travail chaque semaine en automatisant des tâches routinières que la plupart des gens effectuent manuellement. Ces fonctions sont indispensables à quiconque travaille régulièrement avec des données, que vous soyez analyste de données professionnel ou simple utilisateur occasionnel cherchant à simplifier son travail.
Liens rapides
4. XLOOKUP : Recherche avancée dans les feuilles de calcul
XLOOKUP Il s'agit d'une fonction de recherche avancée dans les tableurs tels que Microsoft Excel et Google Sheets, qui va au-delà des capacités des fonctions de recherche traditionnelles telles que RECHERCHEV و RECHERCHEHDisponibilité. XLOOKUP Une plus grande flexibilité, une gestion des données plus efficace et une réduction des erreurs courantes associées aux fonctions héritées. XLOOKUP Un outil essentiel pour les analystes financiers, les data scientists et toute personne travaillant avec de grandes quantités de données et ayant besoin d'extraire des informations spécifiques rapidement et avec précision. XLOOKUPVous pouvez rechercher une valeur dans une plage spécifique et obtenir la valeur correspondante d'une autre plage, quel que soit l'emplacement des colonnes ou des lignes. Ce service prend également en charge XLOOKUP Recherche de droite à gauche et de bas en haut, ce qui la rend plus polyvalente que d'autres fonctions.
Adieu RECHERCHEV : RECHERCHEXL est la solution parfaite
J'ai arrêté d'utiliser RECHERCHEV il y a des années, lorsque j'ai découvert RECHERCHEXL. Alors que RECHERCHEV ne recherche que vers la droite et plante lorsque l'on déplace des colonnes, RECHERCHEXL fonctionne dans toutes les directions et reste flexible. RECHERCHEXL est l'une des Fonctions Excel qui peuvent vous faire gagner du temps Recherchez des données spécifiques dans vos feuilles de calcul.
Dans mes données de prix de composants informatiques, je dois trouver les prix spécifiques des GPU en fonction des modèles. Avec RECHERCHEV, je devrais restructurer l'ensemble du tableau. Mais avec RECHERCHEXL, il me suffit de saisir :
=RECHERCHEX("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP recherche l'intégralité de la colonne produit, trouve mon GPU et renvoie le prix correspondant. L'emplacement de la colonne prix importe peu et la fonction ne plante pas si j'ajoute d'autres colonnes ultérieurement. Je l'utilise régulièrement pour référencer les informations produit sur différentes feuilles sans avoir à reformater quoi que ce soit.
La formule de base de XLOOKUP est :
=XLOOKUP(valeur_recherchée, tableau_recherché, tableau_retour)
- valeur_recherche : La valeur que vous souhaitez rechercher.
- tableau de recherche : L'endroit où vous recherchez de la valeur.
- tableau_retour : La colonne ou la ligne qui contient la valeur que vous souhaitez renvoyer.
Dans mon cas, la valeur que je voulais trouver était « GIGABYTE GeForce RTX 3060 12 Go Gaming OC ». Je voulais rechercher cette valeur dans la colonne C:C et renvoyer la valeur correspondante de D:D sur la même ligne où la correspondance a été trouvée.
Un autre avantage de XLOOKUP est que si j'ajoute « ,-1 » à la fin de la formule, la recherche s'effectue de bas en haut, ce qui me permet de trouver automatiquement le prix le plus récent. Cela m'évite de devoir trier manuellement les données à chaque actualisation de mes feuilles de calcul.
3. Utiliser mes fonctions SUMIFS و COUNTIFS Dans les feuilles de calcul
Gérer plusieurs normes de manière professionnelle
Les fonctions SOMME et COMPTE de base suffisent pour des tâches simples, mais elles sont insuffisantes pour une analyse concrète. Lorsque je dois analyser mes données de prix sous plusieurs conditions, j'utilise généralement les fonctions SOMME.SI.ENS et COMPTE.SI.ENS. Elles me permettent de segmenter facilement des centaines de lignes.
Imaginons que je veuille compter le nombre de processeurs AMD disponibles sur Amazon US. Au lieu de filtrer manuellement, je saisis :
=NB.SI.ENS(F:F; "Amazon US"; K:K; "AMD")
Cela me montre immédiatement que 14 processeurs AMD sont répertoriés sur Amazon dans mon ensemble de données. L'avantage, c'est que je peux compiler autant de benchmarks que nécessaire.
Pour l'analyse des prix, la fonction SOMME.SI fonctionne de la même manière. Pour calculer la valeur totale de tous les processeurs Intel actuellement en stock, j'utilise :
=SOMME.SI.ENS(D:D; K:K; "Intel"; G:G; "En stock")
Cela ajoute tous les prix dans la colonne D où la marque est « Intel » et l'état du stock est « En stock ».
La syntaxe de la fonction SOMME.SI est :
=SOMME.SI.ENS(plage_somme; plage_critères1; critère1; plage_critères2; critère2...)
- sum_range: La colonne que vous souhaitez additionner.
- critères_plage1 : La première colonne pour vérifier les conditions.
- critères1: Première condition de portée.
- critères_plage2, critères2 : Conditions générales supplémentaires (facultatif).
La fonction NB.SI.ENS fonctionne de manière similaire, sauf qu'elle compte les lignes correspondantes au lieu de sommer les valeurs :
=NB.SI.ENS(plage_critères1; critère1; plage_critères2; critère2...)
Je préfère utiliser SOMME.SI.ENS et NB.SI.ENS pour les rapports rapides, car ils mettent instantanément à jour les nouvelles données, s'intègrent parfaitement à mes formules existantes et me permettent de tout conserver en ligne sans créer de tableau croisé dynamique distinct. Ces outils permettent une analyse précise et efficace des données, ce qui permet de gagner du temps et de l'énergie dans la création de rapports complexes. Utiliser des fonctions comme SOMME.SI.ENS et NB.SI.ENS est une compétence essentielle pour tout analyste de données souhaitant extraire rapidement et facilement des informations précieuses de ses données.
2. Taille et nettoyage : étapes essentielles pour préserver l'apparence
Adieu l'encombrement des données
Rien ne gâche plus une feuille de calcul que des données non structurées remplies d'espaces inutiles et de caractères cachés. J'en ai fait l'expérience à mes dépens lorsque mes recherches échouaient systématiquement à cause d'espaces inutiles à la fin des noms de formulaires.
La fonction TRIM supprime les espaces superflus au début et à la fin du texte, ainsi que les espaces entre les mots. Lorsque j'importe des données de différentes sources, les noms de produits contiennent souvent des espaces incohérents. Au lieu de nettoyer manuellement chaque cellule, je crée une colonne auxiliaire et j'utilise :
=SUPPRESPACE(C2)
Ensuite, je déplace le pointeur de la souris vers le bord de la cellule jusqu'à ce qu'il se transforme en signe plus (+), puis je le fais glisser vers toutes les lignes sur lesquelles je souhaite que la fonction TRIM opère.
1. TEXTBEFORE et TEXTBAFTER : Une explication détaillée et leur importance
Extraire avec précision les données requises
Les fonctions TEXTBEFORE et TEXTBAFTER comptent parmi mes fonctions Excel préférées pour nettoyer les feuilles de calcul désordonnées. Les fonctions de texte modernes d'Excel excellent dans l'extraction d'informations spécifiques à partir de chaînes de texte non structurées. Par exemple, ma colonne de prix contenait des entrées telles que « 177.52 $ », « 178.33 USD », « 9055 9645.50 ₱ » et « XNUMX XNUMX PHP » mélangées.
La fonction TEXTBEFORE extrait tout ce qui précède un séparateur spécifié :
=TEXTBEFORE(D2, "USD")
De cette façon, la fonction a extrait instantanément « 178.33 » de « 178.33 USD ».
La fonction TEXTAFTER fonctionne à l'envers, en extrayant tout ce qui se trouve après le séparateur :
=TEXTEAPRÈS(C2, "AMD ")
De cette façon, j'ai extrait la fonction « Processeur Ryzen 5 5700X 8-Core AM4 » de « Processeur AMD Ryzen 5 5700X 8-Core AM4 ».
Pour les extractions complexes, je combine les deux fonctions. Pour obtenir le prix numérique de 177.52 USD :
=TEXTE AVANT(TEXTE APRÈS(D8; "$"); "USD")
La syntaxe générale des fonctions TEXTBEFORE et TEXTAFTER est :
=TEXTEAVANT(texte, délimiteur) et =TEXTEAPRÈS(texte, délimiteur)
L'amélioration majeure apportée par ces deux fonctions réside dans leur précision. Au lieu d'utiliser des combinaisons complexes de fonctions MID, FIND et LEN, je peux réaliser des extractions nettes à l'aide de formules simples et lisibles. J'utilise fréquemment ces fonctions pour séparer les numéros de modèle, extraire les spécifications des produits et extraire des données nettes de textes importés, ce qui nécessitait auparavant des heures de retouche manuelle.
Ces quatre fonctions permettent de gérer certaines des tâches les plus chronophages d'Excel, comme la recherche de données à l'aide de recherches flexibles, l'analyse multicritère, le nettoyage de textes importés désordonnés et l'extraction d'informations spécifiques à partir de chaînes de texte complexes. La plupart des utilisateurs gèrent ces tâches manuellement, consacrant des heures à la mise en œuvre de formules correctes qui ne prendraient normalement que quelques minutes.
Vous avez utilisé ces fonctions pour tout, de l'analyse des prix des composants aux rapports de gestion des stocks. Elles fonctionnent quel que soit votre secteur d'activité, car les données désordonnées et les exigences de recherche complexes sont des problèmes courants. Une fois ces fonctions maîtrisées, vous vous demanderez comment vous avez pu gérer des feuilles de calcul sans elles.
