Formules avancées Excel (Table des matières)
- Introduction aux formules avancées dans Excel
- Exemples de formule avancée dans Excel
Introduction aux formules avancées dans Excel
Une fois que vous avez atteint le niveau intermédiaire dans Excel, vous devez travailler dur pour passer au niveau avancé. Pour passer au niveau avancé, vous devez connaître certaines des formules avancées fréquemment utilisées. Dans cet article, je couvrirai les 10 meilleures formules avancées que tous les apprenants Excel doivent connaître. Suivez cet article pour apprendre et explorer.
Exemples de formule avancée dans Excel
Voyons comment utiliser les formules avancées dans Excel avec quelques exemples.
Vous pouvez télécharger ce modèle Excel de formules avancées ici - Modèle Excel de formules avancéesExemple # 1 - Fonction de RECHERCHE partielle
Vous devez avoir rencontré une situation où RECHERCHEV génère une erreur même s'il y avait une légère erreur de correspondance dans la valeur de recherche. Par exemple, si vous recherchez le salaire du nom Abhishek Sinha et si vous n'avez que Abhishek, alors RECHERCHEV ne peut pas vous récupérer les données.
Cependant, en utilisant un astérisque à chaque extrémité de la valeur de recherche, nous pouvons récupérer les données pour la valeur de recherche partielle.
Dans le tableau 1, j'ai le nom complet et le salaire, mais dans le tableau 2, je n'ai que des noms partiels et j'ai besoin de trouver le salaire de chaque employé.
Étape 1: ouvrez la formule RECHERCHEV dans la cellule E3. Avant de sélectionner la valeur de recherche, placez un astérisque (*) de chaque côté de la valeur de recherche.
Étape 2: Comme d'habitude, vous pouvez compléter la formule RECHERCHEV maintenant et nous aurons les résultats.
Après avoir utilisé la formule ci-dessus, la sortie est indiquée ci-dessous.
La même formule est utilisée dans d'autres cellules.
Exemple # 2 - COUNTIFS avec des symboles d'opérateur
Vous devez avoir utilisé la fonction COUNTIF & IFS pour compter les choses dans la liste. Nous pouvons également compter sur la base de symboles d'opérateur comme supérieur à (>) inférieur à (<) et signe égal (=).
Jetez maintenant un œil aux données ci-dessous par exemple. Si vous souhaitez compter le nombre total de factures pour la région SUD après la date du 10 janvier 2018, comment comptez-vous?
Exemple # 3 - Condition IF avec les conditions AND & OR
Les fonctions logiques font partie de nos activités quotidiennes. Vous devez les maîtriser pour passer au niveau suivant. Si vous calculez le bonus en fonction de plusieurs conditions, vous devez imbriquer la condition AND ou OR avec la condition IF pour effectuer le travail.
Supposons que vous devez calculer le montant du bonus pour chaque service en fonction du service et des années de service dont vous avez besoin pour ces fonctions. Sur la base des critères ci-dessous, nous devons calculer le bonus.
Si le service est supérieur à 4 ans et que le département est soit un bonus de vente ou de support est de 50000, soit un bonus de 25000.
Appliquez la formule ci-dessous pour obtenir le montant du bonus.
Après avoir utilisé la formule ci-dessus, la sortie indiquée ci-dessous.
Même formule s'appliquant dans les cellules E3 à E9.
Exemple # 4 - Fonction TEXT pour vous rendre dynamique
Supposons que vous maintenez une table de vente quotidienne et que vous devez la mettre à jour quotidiennement. Au début du tableau, vous avez un en-tête qui indique «Données de vente consolidées de JJ-MM-AAAA à JJ-MM-AAAA». Au fur et à mesure que le tableau est mis à jour, vous devez modifier la date de l'en-tête. N'est-ce pas une tâche frustrante de faire la même chose encore et encore? Nous pouvons rendre cet en-tête dynamique en utilisant les fonctions TETX, MIN et Max avec concaténation de symbole esperluette d'opérateur (&).
Exemple # 5 - INDEX + MATCH + MAX pour trouver le meilleur vendeur
Vous avez une liste des vendeurs et des ventes qu'ils ont faites par rapport à leur nom. Comment savez-vous qui est le meilleur ou le meilleur vendeur de la liste? Bien sûr, nous avons plusieurs autres techniques pour dire le résultat, mais cette fonction peut renvoyer le vendeur le plus élevé du lot.
Appliquez la fonction ci-dessous pour obtenir le nom du vendeur le plus élevé.
Après avoir utilisé la formule ci-dessus, la sortie indiquée ci-dessous.
Exemple # 6 - Obtenez un nombre de valeurs uniques de la liste
Si vous avez plusieurs valeurs en double et que vous devez indiquer le nombre de valeurs uniques dans la façon de le savoir? Vous pouvez le dire en supprimant la valeur en double, mais ce n'est pas la manière dynamique de dire le nombre de valeurs uniques.
En utilisant cette fonction de tableau, nous pouvons dire les valeurs uniques du lot.
Appliquez la formule ci-dessous pour obtenir une liste de valeurs uniques.
Exemple # 7 - Utilisez la plage nommée pour rendre la liste déroulante dynamique
Si vous travaillez souvent avec une liste déroulante et que vous mettez la liste déroulante à jour, vous devez revenir à la plage de la liste source pour supprimer ou ajouter des valeurs. Après cela, vous devez revenir aux cellules déroulantes et mettre à jour à nouveau la plage de la liste déroulante.
Cependant, si vous pouvez créer une plage nommée pour votre liste déroulante, vous pouvez rendre la liste déroulante dynamique et à jour.
Créez une plage de noms comme indiqué dans l'image ci-dessous.
Maintenant, allez dans la cellule déroulante et ouvrez la boîte de dialogue déroulante.
Dans la source, appuyez sur la touche F3, il affichera tous les noms définis, sélectionnez le nom de votre liste déroulante.
Ok, une liste déroulante est prête et elle mettra à jour les valeurs automatiquement en cas de changement de plage de liste déroulante.
Exemple # 8 - Supprimer les valeurs d'erreur à l'aide de la fonction IFERROR
Je suis sûr que vous avez rencontré des valeurs d'erreur lors de l'utilisation de RECHERCHEV, calculs de division. La gestion de ces valeurs d'erreur est une tâche fastidieuse. Mais nous pouvons nous débarrasser de ces valeurs d'erreur en utilisant la fonction IFERROR dans la formule.
Après avoir utilisé la formule ci-dessus, la sortie indiquée ci-dessous.
Même formule utilisée dans d'autres cellules.
Exemple # 9 - Utilisez la fonction PMT pour créer votre propre graphique EMI
De nos jours, l'option EMI n'est pas une chose étrange pour nous tous. En Excel, nous pouvons estimer notre propre graphique EMI en utilisant la fonction PMT. Suivez les étapes ci-dessous pour créer votre propre graphique.
Appliquer la formule ci-dessous dans la cellule B4 pour obtenir le montant EMI.
Exemple # 10 - INDEX + MATCH comme alternative à la fonction RECHERCHEV
J'espère que vous êtes conscient de la limitation de la fonction RECHERCHEV. Une limitation majeure est que RECHERCHEV peut extraire les données de gauche à droite et non de droite à gauche. Les données ne sont pas toujours bien organisées et prêtes à l'emploi. Souvent, la colonne de valeur que nous recherchons se trouve sur le côté gauche de la valeur de recherche, donc RECHERCHEV ne parvient pas à vous aider dans ces cas.
Une combinaison de la fonction INDEX + MATCH sert d'alternative à la fonction RECHERCHEV.
Après avoir utilisé la formule ci-dessus, la sortie est indiquée ci-dessous.
La même formule est utilisée dans d'autres cellules.
Sur la base de l'ID produit, nous devons extraire les valeurs des ventes. Dans le tableau principal, l'ID de produit se trouve à droite de la colonne des ventes. VLOOKUP ne peut donc pas récupérer les données. Utilisez la formule ci-dessous pour récupérer les données.
Articles recommandés
Ceci est un guide des formules avancées dans Excel. Ici, nous discutons de l'utilisation des formules avancées dans Excel avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés -
- Comment utiliser la fonction Excel INDEX?
- Excel avancé | Fonction de base de données
- Exemples de formule TRIM dans Excel
- Guide de la formule Excel OFFSET