Top 10 des fonctions pour les data analystes

30 janvier 202230 janvier 2022 AZ

Dans ce tutoriel Excel, nous avons pensé aux data analystes. Nous leur montrons les 10 formules Excel les plus importantes.

Si vous souhaitez devenir analyste de données ou d’entreprise, vous devez développer une bonne compréhension des formules Excel et les maîtriser.

Un bon analyste devrait être familiarisé avec moins de 10 formules pour commencer.

Formule SUMIFS

La formule SUMIFS est utilisée pour additionner une plage de valeurs soumises à diverses conditions
Si vous écoutez très attentivement, vous pouvez entendre des milliers de managers à travers le monde crier… “Combien de x avons-nous fait dans la région A, le produit B, le type de client C au cours du mois M ?” à l’heure actuelle.

Pour répondre à cette question sans le chant et la danse du filtrage et de la sélection excessifs, vous devez apprendre la formule SUMIFS.

Cette formule magique peut résumer un ensemble de valeurs qui répondent à plusieurs conditions.

La syntaxe de SUMIFS est la suivante :

=SUMIFS( what you want to sumup, condition column 1, condition, condition column 2, condition….)

Exemple:

=SUMIFS(sales, regions, “A”, products, “B”, customer types, “C”, month, “M”

Formule X/VLOOKUP

Laquelle des choses ci-dessous arrêterait brutalement le monde ?

A. Arrêtez de creuser la terre pour plus de pétrole
B. Laissons les États-Unis sauter du précipice budgétaire ou atteindre le plafond de la dette
C. Soudain, la formule VLOOKUP (ou XLOOKUP) cesse de fonctionner sur tous les ordinateurs, dans le monde entier, pour toujours

Si vous avez répondu A ou B, alors il est grand temps que vous retiriez votre tête du sable et que vous voyiez le monde.

La réponse est C (Eh bien, si toutes les machines à café du monde s’unissent et fonctionnent miraculeusement mal, cela créerait un chaos. Mais heureusement, cette option n’existe pas)

La formule XLOOKUP ou VLOOKUP vous permet de rechercher une valeur dans une table et de renvoyer une valeur correspondante. Par exemple, vous pouvez demander Quel est le nom du client avec ID = C00023 ou Combien coûte le prix du produit pour le code produit = p0089 et VLOOKUP vous donnera les réponses.

La syntaxe de VLOOKUP est simple.

=VLOOKUP(what you want to lookup, table, column from which you want the output, is your table sorted? )

Exemple:

=VLOOKUP(“C00023”, customers, 2, false)

Recherchez l’ID client C00023 dans la première colonne de la table des clients et renvoyez la valeur de la 2e colonne. Supposons que la table des clients n’est pas triée

Fonctions de tableau dynamique – DYNAMIC ARRAY FUNCTIONS

Excel 365 a introduit une nouvelle classe de fonctions appelée DYNAMIC ARRAY FUNCTIONS. Ceux-ci vous permettront de filtrer, trier, extraire facilement des valeurs distinctes de vos données. Il a également ajouté une fonctionnalité de formule spéciale appelée comportement de déversement. Cela signifie que les formules Excel peuvent désormais renvoyer plusieurs valeurs en conséquence et les répartir selon les besoins. Voir cette rapide démo GIF.

Formules IF et IFS

Q : Comment appelle-t-on une entreprise qui ne prend pas une seule décision ?

Un gouvernement!

Blague à part, chaque entreprise doit prendre des décisions, même les gouvernements !!! Alors, comment modélisons-nous ces décisions dans Excel.

En utilisant des formules SI bien sûr.

Par exemple, supposons que votre entreprise décide d’accorder une augmentation de salaire de 10 % à toutes les personnes lisant Chandoo.org et une augmentation de 5 % pour se reposer. Maintenant, comment exprimeriez-vous cela dans Excel ?

Simple, nous écrivons =IF(l’employé lit Chandoo.org, “10 % de hausse”, “5 % de hausse”)

La syntaxe de la formule SI est simple :

=IF (condition to test, output for TRUE, output for FALSE)

Formules imbriquées

Malheureusement, les entreprises ne prennent pas de décisions simples. Ils compliquent toujours les choses. Je veux dire, avez-vous déjà lu les règles de l’impôt sur le revenu ? ! ? Votre tête commence à tourner au moment où vous atteignez le 2e paragraphe.

Pour modéliser des décisions et des situations aussi complexes, vous devez imbriquer des formules.

L’imbrication fait référence à l’inclusion d’une formule dans une autre formule.

Une situation d’exemple : Donnez 12 % de hausse aux employés qui lisent Chandoo.org au moins 3 jours par semaine, Donnez 10 % de hausse à ceux qui lisent Chandoo.org au moins une fois par semaine, pour le reste, donnez 5 % de hausse.

Formule Excel : = IF(number of times employee reads chandoo.org in a week >=3, “12% hike”, IF( number of times employee reads chandoo.org in a week >0, “10% hike”, “5% hike”))

Vous voyez ce que nous avons fait ci-dessus? Nous avons utilisé la formule IF dans une autre formule IF. Ce n’est rien d’autre que la nidification.

Vous pouvez imbriquer n’importe quelle formule dans une autre formule presque n’importe quel nombre de fois.

Les formules imbriquées nous aident à exprimer facilement une logique et des règles métier complexes. En tant qu’analyste, vous devez apprendre l’art de l’imbrication.

Expressions arithmétiques de base

=(((123+456)*(789+987)) > ((123-456)/(789-987)))^3 & " time I saw a tiger"

Si vous avez lu l’expression ci-dessus et que vous n’avez pas eu à vous gratter la tête une seule fois, alors vous êtes en passe de devenir un analyste formidable.

La plupart des gens se lancent dans les formules Excel sans avoir d’abord appris divers opérateurs et expressions de base. Heureusement, leur apprentissage demande très peu de temps. La plupart d’entre nous ont appris l’arithmétique et les expressions de base à l’école. Voici un résumé si vous avez été surpris en train de faire la sieste en Math 101.

Formules de texte

Bien qu’il existe plus de deux douzaines de formules de texte dans Excel, y compris le mystérieux BHATTEXT (qui est utilisé pour convertir des nombres en Thai Bhats, apparemment conçu par l’équipe Excel afin qu’ils puissent commander des plats thaïlandais à emporter #), vous n’avez pas besoin d’apprendre tout d’eux. En apprenant quelques formules TEXT très utiles, vous pouvez gagner beaucoup de temps lors du nettoyage des données ou de l’extraction de portions de montagnes de texte.

En tant qu’analyste data, familiarisez-vous au moins avec les formules ci-dessous :

LEFT, RIGHT ET MID – pour extraire des portions de texte de gauche, de droite et du milieu.
TRIM – pour supprimer les espaces inutiles au début, au milieu et à la fin d’un texte.
SUBSTITUTE – pour remplacer des portions de texte par autre chose.
LEN – pour calculer la longueur d’un texte
TEXT – pour convertir une valeur au format TEXT
FIND – pour trouver si quelque chose est présent dans un texte, si oui à quelle position

Formules NETWORKDAYS & WORKDAY

“Il n’y a pas assez de jours dans le week-end” – un data analyste

Qu’un week-end ait suffisamment de jours ou non, en tant qu’analyste de travail, vous devez faire face aux calculs des jours ouvrables. Par exemple, si un projet prend 180 jours ouvrés et commence le 16 janvier 2013, comment trouveriez-vous la date de fin ?

Heureusement, nous n’avons pas à inventer une formule pour cela. Excel a quelque chose exactement pour cela. La formule WORKDAY prend une date de début et des jours ouvrables et vous indique quelle serait la date de fin.

De la même manière, la formule NETWORKDAYS nous indique combien de jours ouvrables il y a entre 2 dates données.

Ces deux formules acceptent également une liste de jours fériés supplémentaires à prendre en compte.

NETWORKDAYS : calcule le nombre de jours ouvrés entre 2 dates (en supposant samedi, dimanche week-end)
NETWORKDAYS.INTL : identique à NETWORKDAYS, mais vous permet d’utiliser des week-ends personnalisés [Excel 2010+ uniquement]
WORKDAY : Calculer la date de fin à partir d’une date de début et d’un nombre de jours ouvrés
WORKDAY.INTL : identique à WORKDAY, mais vous permet d’utiliser des week-ends personnalisés. [Excel 2010+ uniquement]

Formules SMALL & LARGE

Presque personne ne demande “Qui était la deuxième personne à gravir le mont Everest, ou à marcher sur la lune ou à terminer la course de 100 mètres le plus rapide ?”.

Et pourtant, toutes les entreprises posent des questions telles que « Qui est notre deuxième client le plus précieux ?, troisième fournisseur en partant du bas en matière de retard de paiement ? 4e café célèbre en Jamaïque ? »

Donc, en tant qu’analystes, notre travail consiste à répondre à ces questions sans perdre trop de temps. C’est là que les formules SMALL , LARGE deviennent utiles.

SMALL: Utilisé pour trouver la nième plus petite valeur d’une liste. Utilisez-le comme =SMALL(range of values, n).
LARGE: Utilisé pour trouver la nième plus grande valeur d’une liste.
MIN: Donne la valeur minimale d’une liste.
MAX: Donne la valeur maximale d’une liste.
RANK: Trouve le rang d’une valeur dans une liste. Utilisez-le comme =RANK(valeur, dans cette liste, ordre)

Formule IFERROR

Les erreurs, la mauvaise nourriture de la cantine et les machines à café dysfonctionnelles sont des vérités éternelles de la vie en entreprise. Bien que vous puissiez toujours mettre votre déjeuner dans un sac brun et apporter une bouteille de café finement infusé au travail, il n’y a pas d’échappatoire lorsque votre VLOOKUP #N/As. Ou est-il?

Eh bien, vous pouvez toujours utiliser la belle formule IFERROR pour gérer les erreurs dans vos formules.