Lorsque vous travaillez avec des données dans Excel, certaines tâches peuvent paraître inutilement fastidieuses. Vous devez peut-être diviser une colonne de noms complets en colonnes distinctes pour les prénoms et les noms, ou combiner le texte de plusieurs cellules avec des virgules spécifiques. Il ne s'agit pas de défis analytiques complexes, mais de tâches de traitement de données de base qui surviennent régulièrement.
La bonne nouvelle, c'est qu'Excel dispose de fonctions intégrées spécialement conçues pour ces situations. Cependant, elles sont souvent négligées car elles ne font pas partie intégrante de L'ensemble d'outils Excel standard que la plupart des gens apprennent, moi y compris. Les fonctions que je vais aborder ici ne concernent pas les calculs avancés, mais si vous effectuez des opérations répétitives sur les données, elles peuvent vous faire gagner du temps.
Liens rapides
5. TEXTESPLIT
Sépare les textes collés ensemble
Si vous avez déjà reçu une feuille de calcul où quelqu'un a encombré son nom, son prénom et peut-être même ses initiales du deuxième prénom dans une seule cellule, vous savez combien il est difficile de séparer ces données. TextSplit résout précisément ce problème : il extrait le texte d'une seule cellule et le répartit sur plusieurs colonnes selon un séparateur que vous spécifiez.
Prenons un exemple de feuille de calcul commerciale. Les noms des commerciaux sont répertoriés comme « Sarah Chen », « Mike Johnson » et « Lisa Park », tous dans une seule colonne. Au lieu de saisir manuellement chaque nom dans des colonnes distinctes, TextSplit s'en charge automatiquement.
La formule est la suivante :
=TEXTSPLIT(texte, délimiteur_colonne, [délimiteur_ligne], [ignorer_vide], [mode_correspondance], [remplir_avec])
Voici ce que fait chaque enseignant :
- texte: La cellule qui contient le texte que vous souhaitez diviser.
- col_delimiter: Le caractère qui sépare vos données (comme un espace, une virgule ou un point-virgule).
- row_delimiter (facultatif) : Utilisé lors du fractionnement en lignes et en colonnes.
- ignore_empty (facultatif) : TRUE ignore les valeurs vides, FALSE les conserve (la valeur par défaut est FALSE).
- match_mode (facultatif) : Contrôle la sensibilité à la casse (0 pour sensible à la casse, 1 pour insensible à la casse).
- pad_with (facultatif) : Avec quoi remplissez-vous les cellules vides lorsque les résultats sont de longueurs inégales ?
Pour les noms des représentants commerciaux, par exemple, j'utiliserais la formule suivante pour diviser les noms en colonnes distinctes :
=TEXTESPLIT(A2, " ")
La fonction crée automatiquement le nombre de colonnes nécessaire en fonction de vos données. Bien que cette approche de base fonctionne dans la plupart des cas, des paramètres supplémentaires vous permettent de contrôler précisément la taille des colonnes. Fonction TEXTSPLIT dans Excel.
4. TEXTEJOINDRE
Fusionner plusieurs cellules en une seule cellule
TEXTJOIN a l'effet inverse de TEXTSPLIT. Il extrait le texte de plusieurs cellules et les combine en une seule cellule à l'aide du séparateur de votre choix. Ceci est utile pour créer des valeurs séquentielles telles que des adresses complètes, des descriptions de produits ou des listes d'adresses e-mail.
La formule ressemble à ceci :
=TEXTEJOIN(délimiteur, ignorer_vide, texte1, [texte2], ...)
Voici ce que chaque paramètre contrôle :
- délimiteur: Le caractère ou le texte qui sépare les valeurs incorporées (virgule, espace, tiret, etc.).
- ignorer_vide : VRAI pour ignorer les cellules vides, FAUX pour les inclure dans le résultat.
- texte1, texte2, etc. : Les cellules ou plages que vous souhaitez fusionner (vous pouvez spécifier des cellules individuelles ou des plages entières).
En regardant la feuille de calcul des ventes, si j'ai des colonnes séparées pour le prénom et la région, mais que j'en ai besoin dans une colonne qui les combine, j'utiliserais TEXTJOIN. ignore_empty VRAI signifie que toutes les cellules vides sont automatiquement ignorées.
=TEXTEJOIN(" - ", VRAI, B2, D2)Lors du choix entre différentes méthodes d'intégration de texte, la compréhension Différences entre les fonctions CONCAT et TEXTJOIN Il peut vous aider à choisir l’outil adapté à vos besoins spécifiques d’intégration de données.
3. CHOISISSEZCOLLES
Spécifiez des colonnes spécifiques de vos données.
CHOOSECOLS vous permet d'extraire des colonnes spécifiques d'une plage sans copier-coller ni créer de références. Si vous disposez d'un jeu de données volumineux mais que vous n'avez besoin que des colonnes 2, 5 et 8 pour votre analyse, cette fonction prendra en compte les colonnes nécessaires et ignorera le reste.
En fonction des données de vente, je souhaiterais extraire uniquement le vendeur et son nom, en ignorant les dates de commande, les catégories de produits et autres détails. Au lieu de sélectionner et de copier manuellement les colonnes, la fonction CHOOSECOLS crée une référence dynamique qui se met automatiquement à jour lorsque les données sources changent.
La fonction suit la formule suivante :
=CHOOSECOLS(tableau, col_num1, [col_num2], ...)
Voici comment fonctionne chaque paramètre :
- tableau: La plage ou le tableau qui contient vos données source (il peut s'agir d'une plage de cellules comme A1:F100 ou d'une référence de tableau).
- col_num1: Le numéro de la première colonne que vous souhaitez extraire (1 pour la première colonne, 2 pour la deuxième colonne, etc.).
- col_num2, etc. : Numéros de colonnes supplémentaires que vous souhaitez inclure (facultatif – vous pouvez en spécifier autant que vous le souhaitez).
Par exemple, si je voulais extraire les noms des commerciaux de la colonne 2 et leur statut de la colonne 9, j'utiliserais :
=CHOIXS(A1:I23, 2, 9)
La fonction renvoie les deux colonnes sous forme de tableau en continu, automatiquement redimensionné pour s'adapter aux données. C'est pourquoi CHOOSECOLS est l'une des Fonctions Excel qui peuvent vous faire gagner beaucoup de tempsIl élimine le besoin de plusieurs formules RECHERCHEV ou de copier manuellement des colonnes lorsque vous travaillez avec de grands ensembles de données.
Excel dispose également d'une fonction CHOOSEROWS, qui fonctionne de manière similaire mais sélectionne des lignes spécifiques au lieu de colonnes, en utilisant la même structure de formule avec des numéros de ligne.
2. PRENDRE et DÉPOSER
Extraire des parties de vos données
Les fonctions TAKE et DROP fonctionnent en tandem pour capturer des parties spécifiques de votre plage de données. TAKE extrait un nombre spécifique de lignes ou de colonnes du début ou de la fin de votre ensemble de données, tandis que DROP supprime des lignes ou des colonnes du début ou de la fin, vous laissant avec ce qui reste.
Ces fonctions constituent des outils précis pour l'échantillonnage des données. Que vous ayez besoin des dix premières lignes de données pour une analyse rapide ou que vous souhaitiez supprimer les lignes d'en-tête qui encombrent vos calculs, ces fonctions s'en chargent parfaitement.
TAKE utilise cette formule :
=PRENEZ(tableau, lignes, [colonnes])
DROP suit un modèle similaire :
=DROP(tableau, lignes, [colonnes])
Voici comment fonctionnent les paramètres pour les deux fonctions :
- tableau: La plage de données source que vous souhaitez extraire ou modifier.
- Lignes: Nombre de lignes à prendre/supprimer (les nombres positifs partent du haut, les nombres négatifs partent du bas).
- colonnes (facultatif) :
Le nombre de colonnes que vous souhaitez prendre ou supprimer (positif à partir de la gauche, négatif à partir de la droite).
Pour obtenir les cinq premières lignes de données de vente, utilisez la formule suivante :
=PRENEZ(A1:C100, 5)
Pour supprimer les 20 premières lignes et travailler avec des données propres, essayez :
=DROP(A1:C23, 20)
Vous pouvez combiner les opérations sur les lignes et les colonnes. Par exemple, la formule suivante vous donne les dix premières lignes et les trois premières colonnes :
=PRENEZ(A1:F23, 10, 3)
Ces fonctions sont très utiles, notamment lorsque vous avez besoin de sous-ensembles de données dynamiques qui s'adaptent automatiquement. Comment utiliser les fonctions TAKE et DROP dans Excel Il vous ouvre des possibilités de création de rapports flexibles qui s'adaptent aux tailles changeantes des ensembles de données.
1. GRANULAT
Des calculs puissants qui gèrent des données désordonnées
AGRÉGATE combine les fonctionnalités de 19 fonctions statistiques différentes en une formule flexible. Sa particularité réside dans sa capacité à ignorer les erreurs, les lignes masquées ou les données filtrées, ce que les fonctions standard comme SOMME ou MOYENNE ne peuvent pas faire de manière fiable.
Si vos données contiennent des erreurs #N/A, ou si vous filtrez pour n'afficher que certaines régions, AGGREGATE peut calculer des sommes, des moyennes ou d'autres statistiques sans que ces problèmes ne perturbent vos résultats. Je le trouve utile lorsque je travaille avec des ensembles de données dynamiques où la visibilité et la qualité des données changent fréquemment.
La structure de la phrase comprend plusieurs éléments :
=AGRÉGATE(num_fonction, options, tableau, [k])
Chaque critère contrôle différents aspects du calcul :
- numéro_de_fonction : Un nombre de 1 à 19 qui spécifie la fonction à utiliser (1 = MOYENNE, 4 = MAX, 9 = SOMME, 12 = MÉDIANE, etc.).
- options: Contrôle ce qu'il faut ignorer pendant le calcul (0 = aucun, 1 = lignes masquées, 2 = valeurs d'erreur, 3 = lignes masquées et erreurs, 5 = valeurs d'erreur uniquement, 6 = lignes masquées et valeurs d'erreur).
- tableau: La plage de cellules à calculer.
- k (facultatif) :
- Utilisé uniquement avec certaines fonctions telles que GRAND, PETIT ou PERCENTILE.
Pour résumer les montants des ventes affichés en ignorant les éventuelles erreurs, je peux utiliser :
=AGRÉGATE(9; 6; D2:D23)
Le nombre 9 spécifie la SOMME et le nombre 6 indique à la fonction d'ignorer les lignes masquées et les valeurs d'erreur.
C'est précisément cette puissante capacité à effectuer des calculs qui explique pourquoi AGGREGATE est inclus. Liste des fonctions Excel que tout employé de bureau devrait connaître—Il gère le chaos des données du monde réel que les fonctions plus simples ne peuvent pas gérer efficacement.
Outils intégrés à utiliser
Les fonctions Excel les plus importantes ne sont souvent pas celles que l'on apprend en premier. Pourtant, elles permettent de résoudre les problèmes subtils rencontrés dans le travail sur tableur, notamment la gestion de données textuelles complexes, l'extraction de parties spécifiques de grands ensembles de données et la réalisation de calculs sur des données incomplètes. Aucune des fonctions présentées ne nécessite des compétences avancées en Excel. Cependant, TEXTSPLIT, CHOOSECOLS, TAKE et DROP ne sont disponibles que dans Microsoft 365 et Excel pour le web.
La prochaine fois que vous devrez nettoyer des données ou copier manuellement des colonnes à répétition, rappelez-vous que ces fonctions existent. Elles sont intégrées à Excel pour gérer les tâches fastidieuses, vous permettant ainsi de vous concentrer sur ce que les données vous disent réellement.
