Liste des 10 principales formules et fonctions Excel avancées
Vous pouvez télécharger ce modèle de formules Excel avancées ici - Modèle de formules Excel avancées# 1 - Formule RECHERCHEV dans Excel
Cette fonction Excel avancée est l'une des formules les plus utilisées dans Excel. Cela est principalement dû à la simplicité de cette formule et à son application dans la recherche d'une certaine valeur à partir d'autres tables qui a une variable commune à travers ces tables. Supposons que vous ayez deux tables contenant des détails sur le salaire et le nom de l'employé d'une entreprise, l'ID d'employé étant une colonne principale. Vous voulez obtenir le salaire du tableau B du tableau A.
Vous pouvez utiliser RECHERCHEV comme ci-dessous.
Il en résultera le tableau ci-dessous lorsque nous appliquerons cette formule Excel avancée dans d'autres cellules de la colonne Salaire des employés.
Faites glisser la formule vers le reste des cellules.
Il existe trois délimitations majeures de RECHERCHEV:
- Vous ne pouvez pas avoir de colonne principale à droite de la colonne pour laquelle vous souhaitez renseigner la valeur d'une autre table. Dans ce cas, la colonne Salaire de l'employé ne peut pas être avant l'ID de l'employé.
- Dans le cas de valeurs dupliquées dans la colonne principale du tableau B, la première valeur sera renseignée dans la cellule.
- Si vous insérez une nouvelle colonne dans la base de données (Ex Insérer une nouvelle colonne avant Salaire de l'employé dans le tableau B), la sortie de la formule peut être différente en fonction d'une position que vous avez mentionnée dans la formule (Dans le cas ci-dessus, la sortie serait vide)
# 2 - Formule INDEX dans Excel
Cette formule Excel avancée est utilisée pour obtenir la valeur d'une cellule dans un tableau donné en spécifiant le nombre de lignes, de colonnes ou les deux. Ex. Pour obtenir le nom d'un employé à la 5e observation, voici les données.
Nous pouvons utiliser la formule Excel avancée comme ci-dessous:
La même formule INDEX peut être utilisée pour obtenir une valeur le long de la ligne. Lorsque vous utilisez à la fois le numéro de ligne et de colonne, la syntaxe ressemblerait à quelque chose comme ceci:
La formule ci-dessus renverrait «Rajesh Ved».
Remarque: Si vous insérez une autre ligne dans les données de la 5e ligne, la formule renverra «Chandan Kale». Par conséquent, la sortie dépendrait de tout changement intervenant dans le tableau de données au fil du temps.
# 3 - Formule MATCH dans Excel
Cette formule avancée Excel renvoie le numéro de ligne ou de colonne lorsqu'il existe une correspondance d'une certaine chaîne ou d'un certain nombre dans la plage donnée. Dans l'exemple ci-dessous, nous essayons de trouver la position de «Rajesh Ved» dans la colonne Nom de l'employé.
La formule serait la suivante:
La fonction MATCH renverrait 5 comme valeur.
Le 3ème argument est utilisé pour la correspondance exacte. Vous pouvez également utiliser +1 et -1 en fonction de vos besoins.
Remarque: On peut combiner INDEX et MATCH pour surmonter la limitation de VLOOKUP.
# 4 - IF ET Formule dans Excel
Il existe de nombreux cas où il est nécessaire de créer des indicateurs en fonction de certaines contraintes. Nous connaissons tous la syntaxe de base de IF. Nous utilisons cette fonction avancée Excel IF pour créer un nouveau champ basé sur une contrainte sur un champ déjà existant. Mais que se passe-t-il si nous devons prendre en compte plusieurs colonnes lors de la création d'un indicateur? Ex. Dans le cas ci-dessous, nous voulons marquer tous les employés dont le salaire est supérieur à 50K mais dont l'ID d'employé est supérieur à 3.
Nous utiliserions IF AND dans de tels cas. Veuillez trouver ci-dessous la capture d'écran pour le même.
Il renverrait le résultat à 0.
Nous pouvons avoir de nombreuses conditions ou contraintes pour créer un drapeau basé sur plusieurs colonnes en utilisant AND.
# 5 - Formule IF OU dans Excel
De même, nous pouvons également utiliser la fonction OR dans Excel au lieu de AND si nous avons juste besoin de satisfaire l'une des conditions de plusieurs.
Dans les cas ci-dessus, si l'une des conditions est satisfaite, nous aurons la cellule remplie comme 1 sinon 0. Nous pouvons également remplacer 1 ou 0 par certaines sous-chaînes avec des guillemets doubles («»).
# 6 - Formule SUMIF dans Excel
Dans certaines analyses, vous devrez peut-être filtrer certaines observations lors de l'application de la fonction de somme ou de comptage. Dans de tels cas, cette fonction avancée Excel SUMIF d'Excel est à notre secours. Il filtre toutes les observations en fonction de certaines conditions données dans cette formule Excel avancée et les résume. Ex. Et si nous voulons connaître la somme des salaires des seuls employés dont le numéro d'identification d'employé est supérieur à 3.
En appliquant la formule SUMIFS:
La formule renvoie les résultats sous la forme 322000.
Nous pouvons également compter le nombre d'employés dans l'organisation ayant un ID d'employé supérieur à 3 lorsque nous utilisons COUNTIF au lieu de SUMIF.
# 7 - Formule CONCATENER dans Excel
Cette fonction avancée Excel est l'une des formules qui peuvent être utilisées avec plusieurs variantes. Cette formule Excel avancée nous aide à joindre plusieurs chaînes de texte en une seule chaîne de texte. Ex, si nous voulons afficher l'ID d'employé et le nom de l'employé dans une seule colonne.
Nous pouvons utiliser cette formule CONCATENER juste pour faire cela.
La formule ci-dessus donnera «1Aman Gupta».
Nous pouvons avoir une autre variante en mettant un seul trait d'union entre ID et NAME. Ex. CONCATENER (B3, ”-“, C3) donnera “1-Aman Gupta”. Nous pouvons également l'utiliser dans VLOOKUP lorsque LOOKUP en valeur Excel est un mélange de plus d'une variable.
# 8 - Formule GAUCHE, MOYENNE et DROITE dans Excel
Nous pouvons utiliser cette formule Excel avancée si nous voulons extraire une certaine sous-chaîne d'une chaîne donnée. Les formules mentionnées peuvent être utilisées en fonction de nos exigences. Ex. Si nous voulons extraire les 5 premiers caractères du nom de l'employé, nous pouvons utiliser la formule GAUCHE dans Excel avec le nom de la colonne et le deuxième paramètre comme 5.
La sortie est donnée ci-dessous:
L'application de la formule DROITE dans Excel est également la même, c'est juste que nous regarderions le caractère à droite de la chaîne. Cependant, dans le cas d'une fonction MID dans Excel, nous devons donner la position de départ de la chaîne de texte requise et la longueur de la chaîne.
# 9 - Formule OFFSET dans Excel
Cette fonction Excel avancée avec une combinaison d'autres fonctions telles que SOMME ou MOYENNE pourrait être utile pour donner une touche dynamique aux calculs. Il est préférable de l'utiliser dans les cas où nous insérons des lignes continues dans une base de données existante. OFFSET Excel nous donne une plage dans laquelle nous devons mentionner la cellule de référence, le nombre de lignes et de colonnes. Ex. Si nous voulons calculer la moyenne des 5 premiers employés de l'entreprise où nous avons un salaire d'employés triés par ID d'employé, nous pouvons faire ce qui suit. Le calcul ci-dessous nous donnera toujours des salariés.
- Il nous donnera la somme des salaires des 5 premiers employés.
# 10 - Formule TRIM dans Excel
Cette formule Excel avancée est utilisée pour nettoyer les espaces sans importance du texte. Ex. Si nous voulons supprimer des espaces au début d'un nom, nous pouvons l'utiliser en utilisant la fonction TRIM dans Excel comme ci-dessous:
La sortie résultante serait «Chandan Kale» sans aucun espace avant Chandan.
