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

Microsoft Excel offre aux utilisateurs des centaines de fonctions et de formules différentes à des fins diverses. Que vous deviez analyser vos finances personnelles ou tout autre ensemble de données volumineux, les fonctions facilitent le travail. Cela permet également d'économiser beaucoup de temps et d'efforts. Cependant, trouver le bon travail pour votre ensemble de données peut être très difficile.

Donc, si vous avez du mal à trouver le bon travail Excel pour l'analyse de données, vous êtes au bon endroit. Voici une liste de certaines des fonctions de base de Microsoft Excel que vous pouvez utiliser pour analyser les données et augmenter votre productivité dans le processus.

Remarque : Pour commencer, les fonctions mentionnées dans cet article doivent être ajoutées dans la barre de formule d'une feuille de calcul Excel contenant des données ou à l'intérieur de la cellule où vous souhaitez obtenir le résultat.

1. Séquence

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

Dans l'exemple ci-dessus, je voulais que le mois et les ventes soient réunis dans une seule colonne. J'ai donc utilisé la formule = CONCATENER (A2, B2) dans la cellule C2 pour obtenir 700 $ à partir de janvier.

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

2.LEN

=LEN est une autre fonction d'analyse de données utile qui renvoie 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 ayant une limite de caractères. Cela peut également être utile lorsque vous essayez de comprendre les différences entre différents identifiants uniques qui sont souvent très longs et pas dans le bon ordre.

Dans l'exemple ci-dessus, je voulais calculer 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 en conséquence.

Formule : =NBCAR(cellule)

3. RECHERCHEV

= VLOOKUP est probablement l'une des fonctions les plus reconnaissables pour quiconque est familier 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 offre deux modes de correspondance - exact et approximatif, qui sont contrôlés par la plage de recherche. Si vous définissez la plage sur FALSE, elle recherchera une correspondance exacte, mais si vous la définissez sur TRUE, elle recherchera une correspondance approximative.

Dans l'exemple ci-dessus, je voulais rechercher le nombre de vues au cours d'un mois particulier. Donc, j'ai utilisé la formule =VLOOKUP ("Jun", A2:C13, 3) dans la cellule G4 et j'ai obtenu 74992 en conséquence. Ici, "Jun" est la valeur de recherche, A2:C13 est le tableau dans lequel je recherche "Jun" et 3 est le numéro de colonne dans laquelle la formule trouvera les vues correspondant au mois de juin.

Le seul inconvénient de l'utilisation de cette fonction est qu'elle ne fonctionne qu'avec des données organisées en colonnes, d'où le nom - Recherche verticale. Ainsi, si vos données sont organisées en lignes, vous devez d'abord convertir les lignes en colonnes.

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

4. Index/correspondance

Comme la fonction VLOOKUP, les fonctions INDEX et MATCH sont utiles pour rechercher des données spécifiques basées sur une valeur d'entrée. INDEX et MATCH, lorsqu'ils sont utilisés ensemble, peuvent contourner les limites de VLOOKUP de fournir de faux résultats (si vous ne faites pas attention). Ainsi, lorsque ces deux fonctions se combinent, elles peuvent localiser la référence de données et rechercher une valeur dans un tableau unidimensionnel. Cela renvoie les coordonnées des données sous forme de nombre.

Dans l'exemple ci-dessus, je voulais trouver le nombre de vues en janvier. Par conséquent, j'ai utilisé la formule =INDEX(A2:C13, MATCH ("Jan", A2:A13), 0). Ici, A3: C2 est la colonne de données que je veux que la formule renvoie, "Jan" est la valeur que je veux faire correspondre, A13: A2 est la colonne que la formule trouvera "Jan" et un 13 indique que je veux la formule pour trouver une correspondance exacte pour la valeur.

Si vous voulez trouver une correspondance approximative, vous devrez remplacer 0 par 1 ou -1. Ainsi, 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, vous utiliserez la formule 1, par .

Maintenant, si vous ne voulez pas taper le nom du mois avec force, vous pouvez le remplacer par le numéro de cellule. Nous pouvons donc remplacer "Jan" dans la formule ci-dessus par F3 ou A2 pour obtenir le même résultat.

Formule : =INDEX(colonne de données que vous souhaitez renvoyer, MATCH(point de données commun que vous essayez de faire correspondre, colonne d'une autre source de données contenant un 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 selon certains critères. Donc, fondamentalement, la fonction recherche les valeurs minimales/maximales et les compare aux critères d'entrée.

Dans l'exemple ci-dessus, je voulais trouver le score minimum en fonction du sexe de l'élève. Donc, 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 recherchera les scores, B2:B10 est la colonne dans lequel la formule cherchera les critères (genre) La lettre "M" est la norme.

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

Syntaxe MINIFS : = MINIFS(plage_min, plage_critères1, critères1, …)

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

6. Moyennes

La fonction =AVERAGEIFS vous permet de trouver la moyenne d'un ensemble de données donné en fonction d'un ou plusieurs critères. Lors de l'utilisation de cette fonction, vous devez garder à l'esprit que chaque standard et milieu de gamme peut être différent. Cependant, dans la fonction =AVERAGEIF, la plage de critères et la plage de somme doivent avoir la même plage de taille. Remarquez la différence entre le singulier et le pluriel de ces deux 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. J'ai donc utilisé la formule =AVERAGEIFS(C2:C10, B2:B10,"M") et j'ai obtenu 56.8 en conséquence. Ici, C2:C10 est la plage dans laquelle la formule recherchera la moyenne, B2:B10 est la plage des normes et "M" est la norme.

Formule : =AVERAGEIFS(intervalle_moyenne, intervalle_critères1, critères_1, ...)

7. COMPTES

Maintenant, si vous voulez compter le nombre d'instances auxquelles répond un jeu de données avec 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, ce qui en fait 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 masculins ou féminins qui ont obtenu la note de passage (c'est-à-dire> = 40). J'ai donc utilisé la formule =COUNTIFS(B2:B10,"M", C2:C10,">=40"). Ici, B2:B10 est la plage dans laquelle la formule va rechercher le premier critère (genre), "M" est le premier critère, C2:C10 est la plage dans laquelle la formule va rechercher le second critère (flags), et ">=40" est le deuxième critère .

Formule : =COUNTIFS(critères_catégorie1, critères1,…)

8.SOMMEPRODUIT

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

Dans cet exemple, je voulais trouver le total de tous les produits vendus. J'ai donc utilisé la formule =SUMPRODUCT(B2:B8, C2:C8). Ici, B2:B8 est la première matrice (la quantité de produits vendus) et C2:C8 est la deuxième matrice (le prix de chaque produit). La formule multiplie ensuite la quantité de chaque produit vendu par son prix, puis additionne le tout pour obtenir le total des ventes.

Formule : =SOMMEPROD(matrice1, [matrice2], [matrice3], …)

9. Supprimer

La fonction =TRIM est particulièrement utile lorsque vous travaillez avec un jeu 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 en A7. J'ai donc utilisé la formule =TRIM(A7).

La formule supprimait simplement les espaces supplémentaires et rendait le tapis de souris résultant avec un seul espace.

Format : =TRIM(texte)

10. Trouver/rechercher

Pour compléter 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 restrictions. 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, j'ai utilisé la formule =FIND(A2,B2), qui a donné le résultat 1. Maintenant, si je voulais trouver le nombre de fois 'gui' à l'intérieur de Guiding Tech à la place, je dois utiliser la formule =SEARCH car elle n'est pas sensible à la casse.

Rechercher la syntaxe : = FIND(find_text, within_text, [start_num])

Syntaxe de recherche : = SEARCH(find_text, within_text, [start_num])

Maîtrise de l'analyse des données

Ces fonctions de base de Microsoft Excel vous aideront certainement dans l'analyse des données, mais cette liste ne fait qu'effleurer la pointe de l'iceberg. Excel comprend également de nombreuses autres fonctions avancées pour obtenir des résultats spécifiques. Si vous souhaitez en savoir plus sur ces fonctions, faites-le nous savoir dans la section commentaires ci-dessous.