17 formules Excel avancées - Un must pour tous les professionnels

Si vous vous retrouvez souvent à fléchir les mains et à perdre des heures à faire des choses manuellement dans Excel, vous manquez la puissance d'Excel si vous savez comment l'utiliser correctement. Microsoft Excel est connu pour ses formules qui peuvent fonctionner efficacement sur un grand nombre de données dans la cellule, sans trop d'intervention manuelle nécessaire dans le processus.

Nous avons compilé 17 formules Excel avancées qui ont une incroyable capacité à transformer de longues tâches manuelles banales en quelques secondes de travail. Ce sont des formules que tout professionnel devrait avoir à portée de main pour gagner un temps précieux ou pour impressionner son patron à un moment critique.

Parcourez la liste et dites-nous votre préféré !

Les apprenants reçoivent une augmentation salariale moyenne de 58 %, la plus élevée allant jusqu'à 400 %.

1. CORRESPONDANCE D'INDICE

Formule = INDEX(C3:E9,EQUIV(B13, C3:C9,0),EQUIV(B14,C3:E3,0))

Une excellente alternative à la formule VLOOKUP ou HLOOKUP sur Excel qui présente certains inconvénients pour effectuer des tâches de recherche. L'INDEX MATCH est une formule combinée de 2 fonctions distinctes :

INDEX : cette formule renvoie la valeur d'une cellule dans un tableau en fonction de la colonne et du numéro de ligne.

MATCH : cette formule renvoie la position d'une cellule dans une ligne ou une colonne.

Un exemple de la combinaison des formules INDEX et MATCH est : lorsque vous recherchez et renvoyez la taille d'une personne en fonction de son nom, vous pouvez utiliser cette formule pour modifier les deux variables (dans ce cas, le nom et la taille) à l'aide de la formule INDEX MATCH .

La source

Décomposons-le étape par étape :

Comment combiner INDEX et MATCH

• Tapez INDEX
• Sélectionnez la zone que vous souhaitez INDEXER
• Verrouiller la zone avec F4
• Trouvez la ligne à partir de laquelle vous souhaitez rechercher les données
• Tapez MATCH avec la série d'informations et verrouillez la zone avec F4
• Tapez 0 pour la correspondance exacte, fermez les parenthèses
• Appuyez sur Entrée

Idées intéressantes de projets de science des données

Vous disposez maintenant d'un ensemble de colonnes et de lignes entièrement dynamique et fonctionnel dans la feuille où tout le balayage des cellules et des lignes pour les bonnes données est effectué automatiquement.

2. MOYENNE

Pour effectuer la formule moyenne pour trouver la moyenne de n'importe quelle plage de nombres, voici les étapes que vous devez suivre :

Entrez les valeurs, les cellules ou l'échelle des cellules que vous calculez dans le format.

La formule doit commencer par =MOYENNE(nombre1, nombre 2, etc.)

Si vous souhaitez effectuer la moyenne d'une plage de cellules dans la feuille, voici les étapes à suivre :

• Entrez les valeurs, les cellules ou l'échelle des cellules que vous calculez dans le format, comme vous l'avez fait ci-dessus
• La formule doit ressembler à =AVERAGE(valeur de départ : valeur de fin).

Si vous vous demandez comment entrer les valeurs de la région des cellules, vous pouvez sélectionner la zone sur votre feuille avec votre souris et la verrouiller avec F4. Cela obligera Excel à faire le reste du travail pour vous sans faire de somme ni de division du chiffre avec le nombre d'éléments du groupe.

3. SUMIF

Cette formule dans Excel est notée SUMIF (plage, critères, [plage somme]). Cela se traduirait par la somme des valeurs dans la plage de cellules souhaitée qui répondrait aux exigences que vous avez définies. Par exemple, =SUMIF(C3 : C12, ">70 000) renverrait la somme des valeurs entre les cellules de C3 et C12 uniquement à partir des cellules dont la valeur est supérieure à 70 000.

Dans le cas des finances, des salaires ou même des calculs de coûts, vous avez besoin de la valeur des prospects associés à des employés spécifiques déterminés par la condition que vous avez définie. Faire cela manuellement prend beaucoup de temps et ralentit les choses.

La formule pour la condition ci-dessus peut être =SUMIF(plage, critères, [sum_range]), où la plage peut être définie comme la plage de la feuille à partir de laquelle vous devez choisir les valeurs.

[sum_range] est défini comme la plage supplémentaire ou facultative que vous allez additionner en plus de la première plage saisie.

Voici un exemple :

La source

4. OFFSET COMBINÉ AVEC SOMME

Seule, la fonction OFFSET n'est peut-être pas pratique, mais lorsqu'elle est combinée avec d'autres services, vous pouvez obtenir une formule complexe avec des résultats plus rapides si vous souhaitez créer un rôle dynamique qui peut additionner n'importe quel nombre variable de cellules, la formule SUM régulière, qui est statique doit être combiné avec la fonction OFFSET.

Ainsi, pour connaître la somme des valeurs dans les cellules variables, la formule doit être :

=SOMME(B4:DECALAGE(B4,0,E2-1))

La source

Ici, la référence de fin de la fonction SUM est remplacée par la fonction OFFSET. La formule SUM commençant en B4 se termine par une variable et est une formule OFFSET commençant en B4, se poursuivant par la valeur en E2 ("3") moins un. Cela aide la méthode à se déplacer sur deux cellules et à additionner trois années de données. Dans la référence ci-dessus, vous pouvez voir que la cellule F7 contient la somme des cellules B4 : D4 = 15.

5. SI ET

Cette formule est pratique dans les situations où vous devez créer certaines conditions pour parcourir un tas de données. L'instruction IF aide à utiliser la fonction avancée d'Excel IF pour créer un nouveau champ basé sur ces conditions sur une piste déjà existante.

Par exemple, si vous souhaitez marquer les employés avec des salaires supérieurs à 50 000 et un ID d'employé supérieur à 3, la formule sera :

SI(ET(E4>3,F4>50000)1,0)

La source

Puisqu'il n'y a pas de cas réels dans les données ci-dessus, la formule renverrait la valeur 0.

6. CONCATENER

Si vous avez beaucoup de chaînes de texte dans votre feuille de données et que vous souhaitez faire apparaître les données sur une seule ligne, cette formule est votre solution. Par exemple, si vous souhaitez représenter l'ID de l'employé et le nom de l'employé dans une seule colonne, la méthode doit être :

=CONCATENER(B3, C3)

La source

7. EMP

Cette fonction vous aidera à déterminer les paiements futurs dus pour un prêt, compte tenu d'un taux d'intérêt, d'un montant principal et d'une durée de prêt spécifiques. Voici ce dont vous avez besoin pour commencer :

• La durée du prêt
• Le capital de départ (argent) du prêt
• La valeur future
• Le type de prêt

Maintenant, si vous voulez trouver le paiement mensuel pour un montant principal, la formule pour le même sera :

= PMT (taux, par, PV, [fv], [type])

Comprenons cela avec un exemple :

La source

=PMT(C2/12.B2.A2)

Et le meilleur, c'est que vous pouvez faire glisser le coin inférieur droit des cellules PMT sur les champs pour calculer automatiquement le montant du paiement mensuel pour tous les champs !

Types populaires d'emplois en science des données

8. TRIM

C'est l'une des formules les plus utilisées dans Excel qui nettoie tout espace indésirable dans les champs. Par exemple : si vous devez supprimer les espaces au début d'un nom, vous pouvez le faire en utilisant la fonction TRIM :

=COUPER(C6)

La source

Cela vous renverrait avec la valeur de Chandan Kale sans aucun espace supplémentaire attaché à l'avant ou à la fin.

9. LEN

Il s'agit d'une fonction qui vous indique le nombre de caractères dans une chaîne de texte. L'une des façons les plus intéressantes de l'utiliser est, par exemple, si vous souhaitez mettre en évidence les donateurs qui ont donné plus de 1 000 $ en comptant le nombre de chiffres dans la colonne des dons, la formule sera :

=NBCAR(B2)

La source

Et si vous souhaitez mettre en surbrillance toutes les cellules de la colonne Donation, vous devez :

• Sélectionnez l'onglet Accueil dans le menu
• Cliquez sur Mise en forme conditionnelle (dans la barre d'outils)
• Sélectionnez Utiliser une formule pour déterminer les cellules à formater

La source

Ici, si vous faites la condition "> 3", alors tout ce qui dépasse 1 000 $ recevrait le formatage unique, que vous pouvez choisir en cliquant sur l'option Format.

10. CHOISIR

C'est une excellente fonction pour l'analyse de scénarios dans la modélisation financière. Il vous permet de choisir entre un nombre spécifique d'options et de retourner le "choix" que vous avez sélectionné. Par exemple, si vous avez trois valeurs différentes pour la croissance des revenus l'année prochaine sur la base d'hypothèses, à l'aide de la fonction CHOISIR, vous pouvez renvoyer le montant en fonction de vos besoins.

La formule est :

=CHOISIR(C7,D3,D4,D5)

La source

11. CELLULE, GAUCHE, MILIEU et DROITE

Toutes les fonctions ci-dessus peuvent être combinées de plusieurs façons pour obtenir des formules avancées.

• La fonction CELLULE est utilisée pour renvoyer différents types d'informations sur le contenu de la cellule
• Le service LEFT est utilisé pour remplacer le texte depuis le début de la cellule.
• La fonction MID peut renvoyer du texte à partir de n'importe quel point de départ de la cellule.
• La fonction DROITE renvoie uniquement le texte à partir de la fin de la cellule.

La source

12. XNPV et XIRR

Pour tous les analystes qui rencontrent la banque d'investissement, la recherche sur les actions ou tout autre domaine de la finance d'entreprise qui nécessite l'actualisation des flux de trésorerie, ces formules vous feront certainement économiser beaucoup de temps et de grogne !

Questions et réponses pour l'entrevue en science des données

Par exemple, si vous souhaitez calculer la valeur nette actuelle (VAN) pour tout investissement en utilisant un taux d'actualisation spécifié et des flux de trésorerie se produisant à intervalles irréguliers, utilisez la fonction suivante.

=XNPV(taux d'actualisation, flux de trésorerie, dates)

La source

D'autre part, la fonction XIRR vous indique le taux de rendement interne (où sortie = entrée) pour une série de flux de trésorerie se produisant à intervalles irréguliers, tels que :

13. COMPTE ( )

Les analystes ont souvent du mal à trouver le nombre de cellules contenant des valeurs (nombres, erreurs, texte, valeurs logiques) et celles qui sont vides. La fonction NBVAL( ) peut vous aider à trouver le nom des cellules non vides dans une plage sélectionnée. Par exemple, la formule de comptage des valeurs pour une feuille de données comportant les colonnes A1-A10 est :

=COMPTERA(A1 : A10)

La source

14. VF

Cette formule est pratique si vous cherchez à investir de l'argent dans quelque chose et à connaître sa valeur. Les exigences de la formule FV sont :

• Le taux d'intérêt du prêt
• Nombre de paiements
• Le paiement pour chaque période
• Solde de départ actuel
• Type de prêt

Par exemple, si vous souhaitez comparer plusieurs CD vierges et que vous disposez d'un héritage de 20 000 $ à investir dans un CD. Les taux d'intérêt sont représentés au format décimal ; les paiements sont nuls. La formule du scénario sera :

=VF(A2/12,B2,C2,D2)

La source

Les résultats seront :

La source

15. RAND ENTRE

Cette fonction permet de sélectionner aléatoirement un nombre dans une plage prédéfinie de nombres. Une fois que vous avez mis les nombres les plus bas et les plus élevés dans la formule, Excel peut choisir les bonnes données dans les champs auxquels les noms de la plage sont attachés et les choisir au hasard. La méthode pour le scénario est :

=RANDBETWEEN(point de départ, point d'arrivée)

La source

16. PETIT

La fonction SMALL dans Excel renvoie des valeurs numériques en fonction de la position de la valeur dans une liste classée par importance. Cette fonction permet de récupérer les "nièmes plus petites valeurs" d'un tableau ou d'une plage de cellules comme la plus petite valeur, la 2ème valeur la plus basse, la 3ème valeur la plus basse, etc.

La syntaxe de la formule est

= PETIT (temps, plage)

Par exemple,

La source

Comme la fonction SMALL est automatique, vous devez fournir une plage et un entier pour 'nth' pour spécifier la valeur classée. Les noms officiels de ces arguments sont 'array' et 'k'.

17. QUARTILE

Cette fonction renvoie le quartile (chacun des quatre groupes égaux) dans un ensemble de données donné et peut renvoyer la valeur minimale, le premier quartile, la valeur maximale du deuxième quartile. Cette fonction apporte la quantité de quartile des champs dans un tableau. La fonction renvoie une valeur numérique en fonction du centile demandé.

Syntaxe : =QUARTILE (tableau, quart)

La source

CONCLUSION:

Microsoft Excel est tout à fait inévitable lorsqu'il s'agit du lieu de travail du 21e siècle, mais l'astuce est qu'il n'a pas à être si intimidant. Faites-en votre ami et regardez votre productivité exploser !