Liste des 10 principales formules et fonctions 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 dans ces tables. Supposons que vous ayez deux tables avec des détails sur le salaire et le nom de l'employé d'une entreprise avec l'ID d'employé comme 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 principales délimitations 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 d'employé.
- Dans les 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'un poste que vous avez mentionné dans la formule (Dans le cas ci-dessus, le résultat 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 5 e 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 la 5 e 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 chaîne ou un nombre spécifique dans la plage donnée. Dans l'exemple ci-dessous, nous essayons de trouver «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 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 devons 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 («»).
# 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. Que faire si nous voulons connaître la somme des salaires des seuls employés dont l'ID 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 vous utilisez 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 pour ce faire.
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 pour extraire une sous-chaîne spécifique d'une chaîne donnée. Il pourrait être utilisé en fonction de nos besoins. 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 RIGHT 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 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 fournit 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é 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.
