10 fonctions essentielles de Microsoft Excel pour l'analyse des données

1. ENCHAÎNER

=CONCATENATE est l'une des fonctions les plus cruciales pour l'analyse de données car elle vous permet de combiner du texte, des nombres, des dates, etc. de plusieurs cellules en une seule. La fonction est particulièrement utile pour combiner des données de différentes cellules en une seule cellule. Par exemple, il est pratique pour créer les paramètres de suivi des campagnes marketing, créer des requêtes API, ajouter du texte à un format numérique et plusieurs autres choses.

Dans l'exemple ci-dessus, je voulais le mois et les ventes ensemble dans une seule colonne. Pour cela, j'ai utilisé la formule =CONCATENATE(A2, B2) dans la cellule C2 pour obtenir 700 janviers comme résultat.

Formule: =CONCATENER(les cellules que vous souhaitez combiner)

2. LEN

=LEN est une autre fonction pratique pour l'analyse des données qui affiche essentiellement le nombre de caractères dans une cellule donnée. La fonction est principalement utilisable lors de la création de balises de titre ou de descriptions avec une limite de caractères. Cela peut également être utile lorsque vous essayez de découvrir les différences entre différents identifiants uniques qui sont souvent assez longs et pas dans le bon ordre.

Dans l'exemple ci-dessus, je voulais compter les chiffres du nombre de vues que j'obtenais chaque mois. Pour cela, j'ai utilisé la formule =LEN(C2) dans la cellule D2 pour obtenir 5 comme résultat.

Formule: =LEN(cellule)

3. RECHERCHEV

=VLOOKUP est probablement l'une des fonctions les plus reconnaissables pour toute personne familiarisée avec l'analyse de données. Vous pouvez l'utiliser pour faire correspondre les données d'une table avec une valeur d'entrée. La fonction propose deux modes de correspondance - exact et approximatif, qui est contrôlé par la plage de la recherche. Si vous définissez la plage sur FALSE, il recherchera une correspondance exacte, mais si vous le définissez sur TRUE, il recherchera une correspondance approximative.

Dans l'exemple ci-dessus, je voulais rechercher le nombre de vues au cours d'un mois donné. Pour cela, j'ai utilisé la formule =RECHERCHEV("Jun", A2:C13, 3) dans la cellule G4 et j'ai obtenu 74992 comme résultat. Ici, "Jun" est la valeur de recherche, A2:C13 est le tableau dans lequel je recherche "Jun" et 3 est le numéro de la colonne dans laquelle la formule trouvera les vues correspondantes pour juin.

Le seul inconvénient de l'utilisation de cette fonction est qu'elle ne fonctionne qu'avec des données qui ont été organisées en colonnes, d'où le nom - recherche verticale. Donc, si vous avez organisé vos données en lignes, vous devez d'abord transposer les lignes en colonnes.

Formule: =RECHERCHEV(lookup_value, table_array, col_index_num, [range_lookup])

4. INDEX/MATCH

Tout comme la fonction RECHERCHEV, les fonctions INDEX et MATCH sont pratiques pour rechercher des données spécifiques en fonction d'une valeur d'entrée. L'INDEX et le MATCH, lorsqu'ils sont utilisés ensemble, peuvent surmonter les limitations de VLOOKUP de fournir de mauvais résultats (si vous ne faites pas attention). Ainsi, lorsque vous combinez ces deux fonctions, elles peuvent localiser la référence des données et rechercher une valeur dans un tableau à une seule dimension. Cela renvoie les coordonnées des données sous forme de nombre.

Dans l'exemple ci-dessus, je voulais rechercher le nombre de vues en janvier. Pour cela, j'ai utilisé la formule =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Ici, A2:C13 est la colonne de données que je veux que la formule renvoie, "Jan" est la valeur que je veux faire correspondre, A2:A13 est le colonne dans laquelle la formule trouvera "Jan" et le 0 signifie que je veux que la formule trouve une correspondance exacte pour le valeur.

Si vous voulez trouver une correspondance approximative, vous devrez remplacer le 0 par 1 ou -1. Ainsi, le 1 trouvera la plus grande valeur inférieure ou égale à la valeur de recherche et -1 trouvera la plus petite valeur inférieure ou égale à la valeur de recherche. Notez que si vous n'utilisez pas 0, 1 ou -1, la formule utilisera 1, par.

Maintenant, si vous ne voulez pas coder en dur le nom du mois, vous pouvez le remplacer par le numéro de portable. On peut donc remplacer "Jan" dans la formule citée plus haut par F3 ou A2 pour obtenir le même résultat.

Formule: =INDICE(colonne des données que vous souhaitez renvoyer, MATCH(point de données commun que vous essayez de faire correspondre, colonne de l'autre source de données qui a le point de données commun, 0))

5. MINIFS/MAXIFS

=MINIFS et =MAXIFS sont très similaires aux fonctions =MIN et =MAX, à l'exception du fait qu'elles vous permettent de prendre l'ensemble de valeurs minimum/maximum et de les faire correspondre également sur des critères particuliers. Donc, essentiellement, la fonction recherche les valeurs minimum/maximum et les associe aux critères d'entrée.

Dans l'exemple ci-dessus, je voulais trouver les notes minimales en fonction du sexe de l'élève. Pour cela, j'ai utilisé la formule =MINIFS (C2:C10, B2:B10, "M") et j'ai obtenu le résultat 27. Ici, C2:C10 est la colonne dans laquelle la formule cherchera les scores, B2:B10 est une colonne dans laquelle la formule cherchera les critères (le sexe), et "M" est le critère.

De même, pour les scores maximum, j'ai utilisé la formule =MAXIFS(C2:C10, B2:B10, "M") et j'ai obtenu le résultat 100.

Formule pour MINIFS : =MINIFS(plage_min, plage_critères1, critères1,...)

Formule pour MAXIFS : =MAXIFS(plage_max, plage_critères1, critères1,...)

6. MOYENNES

La fonction =AVERAGEIFS vous permet de trouver une moyenne pour un ensemble de données particulier en fonction d'un ou plusieurs critères. Lors de l'utilisation de cette fonction, vous devez garder à l'esprit que chaque critère et chaque plage moyenne peuvent être différents. Cependant, dans la fonction =AVERAGEIF, la plage de critères et la plage de somme doivent avoir la même plage de tailles. Remarquez la différence entre le singulier et le pluriel entre ces fonctions? Eh bien, c'est là qu'il faut être prudent.

Dans cet exemple, je voulais trouver le score moyen en fonction du sexe des étudiants. Pour cela, j'ai utilisé la formule =AVERAGEIFS(C2:C10, B2:B10, "M") et j'ai obtenu 56,8 comme résultat. Ici, C2:C10 est la plage dans laquelle la formule recherche la moyenne, B2:B10 est la plage de critères et "M" est le critère.

Formule: =AVERAGEIFS(plage_moyenne, plage_critères1, critères1,...)

7. COUNTIFS

Maintenant, si vous souhaitez compter le nombre d'instances où un ensemble de données répond à des critères spécifiques, vous devrez utiliser la fonction =COUNTIFS. Cette fonction vous permet d'ajouter des critères illimités à votre requête et en fait ainsi le moyen le plus simple de trouver le nombre en fonction des critères d'entrée.

Dans cet exemple, je voulais trouver le nombre d'étudiants ou d'étudiantes qui ont obtenu des notes de passage (c'est-à-dire >=40). Pour cela j'ai utilisé la formule =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Ici, B2:B10 est la plage dans laquelle la formule va chercher le premier critère (sexe), "M" est le premier critère, C2:C10 est la plage dans laquelle la formule recherchera le deuxième critère (notes), et ">=40" est le deuxième Critères.

Formule: =COUNTIFS(plage_critères1, critères1,...)

8. SOMMEPRODUIT

La fonction =SUMPRODUCT vous aide à multiplier des plages ou des tableaux, puis renvoie la somme des produits. C'est une fonction assez polyvalente et peut être utilisée pour compter et additionner des tableaux comme COUNTIFS ou SUMIFS, mais avec une flexibilité supplémentaire. Vous pouvez également utiliser d'autres fonctions dans SUMPRODUCT pour étendre encore plus ses fonctionnalités.

Dans cet exemple, je voulais trouver la somme totale de tous les produits vendus. Pour cela, j'ai utilisé la formule =SUMPRODUCT(B2:B8, C2:C8). Ici, B2:B8 est le premier tableau (la quantité de produits vendus) et C2:C8 est le deuxième tableau (le prix de chaque produit). La formule multiplie ensuite la quantité de chaque produit vendu avec son prix, puis additionne le tout pour fournir le total des ventes.

Formule: =SOMMEPROD(tableau1, [tableau2], [tableau3],...)

9. GARNITURE

La fonction =TRIM est particulièrement utile lorsque vous travaillez avec un ensemble de données comportant plusieurs espaces ou caractères indésirables. La fonction vous permet de supprimer facilement ces espaces ou caractères de vos données, vous permettant d'obtenir des résultats précis tout en utilisant d'autres fonctions.

Dans cet exemple, je voulais supprimer tous les espaces supplémentaires entre les mots Mouse et pad dans A7. Pour cela j'ai utilisé la formule =TRIM(A7).

La formule a simplement supprimé les espaces supplémentaires et fourni le résultat Tapis de souris avec un seul espace.

Formule: =COUPER(texte)

10. TROUVER/RECHERCHER

Pour arrondir les choses, les fonctions FIND/SEARCH vous aideront à isoler un texte spécifique dans un ensemble de données. Les deux fonctions sont assez similaires dans ce qu'elles font, à l'exception d'une différence majeure: la fonction =FIND ne renvoie que des correspondances sensibles à la casse. Pendant ce temps, la fonction =SEARCH n'a pas de telles limitations. Ces fonctions sont particulièrement utiles lors de la recherche d'anomalies ou d'identifiants uniques.

Dans cet exemple, je voulais trouver le nombre de fois où 'Gui' est apparu dans Guiding Tech pour lequel j'ai utilisé la formule =TROUVE(A2, B2), qui a donné le résultat 1. Maintenant, si je voulais trouver le nombre de fois où 'gui' est apparu dans Guiding Tech à la place, je devrais utiliser la formule =SEARCH car elle n'est pas sensible à la casse.

Formule de recherche : =TROUVER(find_text, inside_text, [start_num])

Formule de recherche : =RECHERCHE(find_text, inside_text, [start_num])