Formule de tableau croisé dynamique dans Excel (Table des matières)
- Formule de tableau croisé dynamique dans Excel
- Champ personnalisé pour calculer le montant des bénéfices
- Formule avancée dans le champ calculé
Formule de tableau croisé dynamique dans Excel
Le tableau croisé dynamique est un outil qui nous permet d'analyser de grandes plages de données. Nous pouvons analyser, interpréter et faire beaucoup d'autres choses sans nous casser la tête ni transpirer. Il peut donner presque tout ce qui se trouve dans les données source.
Si certaines informations se trouvent dans les données source, nous pouvons être amenés à les calculer par nous-mêmes. Par exemple, si nous avons un montant total des ventes et un coût total, nous devrons peut-être calculer le profit total ou la perte totale par nous-mêmes.
Nous n'avons pas besoin de le faire dans la source, mais nous pouvons le faire à l'intérieur du tableau croisé dynamique lui-même, ils sont appelés champs calculés à l'intérieur du tableau croisé dynamique. Nous pouvons utiliser des formules personnalisées pour que les données racontent plus d'histoires à partir des données. Les champs calculés nous permettent de créer une nouvelle colonne calculée qui n'existe pas dans la source de données réelle.
Dans cet article, nous allons montrer les façons d'utiliser les champs calculés de tableau croisé dynamique pour créer de nouvelles colonnes en fonction de nos besoins.
Champ personnalisé pour calculer le montant des bénéfices
Vous pouvez télécharger ce modèle Excel de formule de tableau croisé dynamique ici - Modèle Excel de formule de tableau croisé dynamiqueIl s'agit du champ calculé le plus souvent utilisé dans le tableau croisé dynamique. Jetez un œil aux données ci-dessous, j'ai la colonne Nom du pays, Nom du produit, Unités vendues, Prix unitaire, Ventes brutes, COGS (coût des marchandises vendues), Date et Année.
Permettez-moi d'appliquer le tableau croisé dynamique pour trouver les ventes totales et le coût total pour chaque pays. Voici le tableau croisé dynamique pour les données ci-dessus.
Le problème est que je n'ai pas de colonne de profit dans les données source. J'ai besoin de connaître le profit et le pourcentage de profit pour chaque pays. Nous pouvons ajouter ces deux colonnes dans le tableau croisé dynamique lui-même.
Étape 1: sélectionnez une cellule dans le tableau croisé dynamique. Accédez à l'onglet Analyser dans le ruban et sélectionnez Champs, éléments et ensembles. En dessous, sélectionnez Champ calculé.
Étape 2: Dans la boîte de dialogue ci-dessous, donnez un nom à votre nouveau champ calculé.
Étape 3: Dans la section Formule, appliquez la formule pour rechercher le profit. La formule pour trouver le profit est les ventes brutes - COGS.
Allez dans la barre de formule> Sélectionnez Ventes brutes dans le champ ci-dessous et double-cliquez dessus, il apparaîtra dans la barre de formule.
Tapez maintenant le symbole moins (-) et sélectionnez COGS> Double-cliquez.
Étape 4: Cliquez sur AJOUTER et OK pour terminer la formule.
Étape 5: Nous avons maintenant notre colonne TOTAL PROFIT dans le tableau croisé dynamique.
Ce champ calculé est flexible, il n'est pas seulement limité à l'analyse par pays mais nous pouvons l'utiliser pour tout type d'analyse. Si je veux voir l'analyse par pays et par produit, je n'ai qu'à glisser-déposer la colonne des produits dans le champ ROW, elle affichera la répartition des bénéfices pour chaque produit dans chaque pays.
Étape 6: Maintenant, nous devons calculer le pourcentage de profit. La formule pour calculer le pourcentage de profit est le bénéfice total / ventes brutes.
Accédez à Analyser et sélectionnez à nouveau Champ calculé sous Champs, éléments et ensembles.
Étape 7: Maintenant, nous devons voir le champ calculé Total Profit nouvellement inséré dans la liste des champs. Insérez ce champ dans la formule.
Étape 8: Tapez le symbole du séparateur (/) et insérez le champ Ventes brutes.
Étape 9: Nommez ce champ calculé en pourcentage de profit.
Étape 10: Cliquez sur AJOUTER et OK pour terminer la formule. Nous avons le pourcentage de profit comme nouvelle colonne.
Formule avancée dans le champ calculé
Tout ce que j'ai montré maintenant est le truc de base du champ calculé. Dans cet exemple, je vais vous montrer les formules avancées dans les champs calculés du tableau croisé dynamique. Maintenant, je veux calculer le montant de l'incitatif en fonction du pourcentage de profit.
Si le% de profit est> 15%, l'incitation devrait être de 6% du bénéfice total.
Si le% de profit est> 10%, l'incitation devrait être de 5% du bénéfice total.
Si le% de profit est <10%, l'incitation devrait être de 3% du bénéfice total.
Étape 1: Accédez au champ calculé et ouvrez la boîte de dialogue ci-dessous. Donnez le nom comme montant incitatif.
Étape 2: Je vais maintenant utiliser la condition IF pour calculer le montant de l'incitatif. Appliquez les formules ci-dessous comme indiqué dans l'image.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Étape 3: Cliquez sur AJOUTER et OK pour terminer. Nous avons maintenant une colonne Montant des primes.
Limitation du champ calculé
Nous avons vu la merveille des champs calculés mais elle a aussi certaines limites. Maintenant, regardez l'image ci-dessous, si je veux voir la répartition du montant de l'incitation au produit, nous aurons un mauvais sous-total et un grand total de montant incitatif.
Soyez donc prudent lorsque vous affichez le sous-total des champs calculés. Il vous montrera les mauvais montants.
Obtenir la liste de toutes les formules de champ calculées
Si vous ne savez pas combien de formules se trouvent dans le champ calculé du tableau croisé dynamique, vous pouvez obtenir le résumé de toutes ces formules dans une feuille de calcul distincte.
Accédez à Analyser> Champs, éléments et ensembles -> Formules de liste.
Il vous donnera un résumé de toutes les formules dans une nouvelle feuille de calcul.
Choses à retenir sur la formule de tableau croisé dynamique dans Excel
- Nous pouvons supprimer, modifier tous les champs calculés.
- Nous ne pouvons pas utiliser des formules comme RECHERCHEV, SUMIF et bien d'autres formules impliquant des plages dans les champs calculés, c'est-à-dire que toutes les formules qui nécessitent une plage ne peuvent pas être utilisées.
Articles recommandés
Cela a été un guide pour la formule de tableau croisé dynamique dans Excel. Ici, nous avons discuté des étapes pour utiliser la formule du tableau croisé dynamique dans Excel avec des exemples et un modèle Excel téléchargeable. Vous pouvez également consulter ces fonctions utiles dans Excel -
- Tutoriels sur le graphique croisé dynamique dans Excel
- Création d'un tableau croisé dynamique dans Excel
- Tutoriel RECHERCHEV dans Excel
- Excel Créer une base de données