Table des matières
Les tableaux croisés dynamiques sont bons pour tout le monde - ils calculent rapidement et sont configurés de manière flexible, et la conception peut y être intégrée avec élégance, si nécessaire. Mais il y a aussi quelques mouches dans la pommade, notamment l'impossibilité de créer un résumé, où la zone de valeur ne doit pas contenir de chiffres, mais du texte.
Essayons de contourner cette limitation et inventons quelques « béquilles » dans une situation similaire.
Supposons que notre entreprise transporte ses produits dans des conteneurs vers plusieurs villes de notre pays et du Kazakhstan. Les conteneurs ne sont pas envoyés plus d'une fois par mois. Chaque conteneur a un numéro alphanumérique. Comme données initiales, il existe un tableau standard répertoriant les livraisons, à partir duquel vous devez faire une sorte de résumé afin de voir clairement le nombre de conteneurs envoyés à chaque ville et chaque mois :
Pour plus de commodité, rendons la table avec les données initiales "intelligente" à l'avance en utilisant la commande Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau) et donne lui un nom Livraisons languette Constructeur (Conception). À l'avenir, cela simplifiera la vie, car. il sera possible d'utiliser le nom du tableau et de ses colonnes directement dans les formules.
Méthode 1. Le plus simple - utilisez Power Query
Power Query est un outil super puissant pour charger et transformer des données dans Excel. Ce complément est intégré à Excel par défaut depuis 2016. Si vous possédez Excel 2010 ou 2013, vous pouvez le télécharger et l'installer séparément (entièrement gratuit).
L'ensemble du processus, pour plus de clarté, j'ai analysé étape par étape dans la vidéo suivante :
S'il n'est pas possible d'utiliser Power Query, vous pouvez utiliser d'autres méthodes - via un tableau croisé dynamique ou des formules.
Méthode 2. Résumé auxiliaire
Ajoutons une colonne supplémentaire à notre tableau d'origine, où, à l'aide d'une formule simple, nous calculons le nombre de chaque ligne du tableau :
De toute évidence, -1 est nécessaire, car nous avons un en-tête d'une ligne dans notre tableau. Si votre tableau n'est pas au début de la feuille, vous pouvez utiliser une formule légèrement plus complexe, mais universelle, qui calcule la différence entre les numéros de la ligne actuelle et l'en-tête du tableau :
Maintenant, de manière standard, nous allons construire un tableau croisé dynamique du type souhaité en fonction de nos données, mais dans le champ valeur, nous allons supprimer le champ Numéro de ligne au lieu de ce que nous voulons récipient:
Étant donné que nous n'avons pas plusieurs conteneurs dans la même ville au cours d'un même mois, notre récapitulatif indiquera en fait non pas le montant, mais les numéros de ligne des conteneurs dont nous avons besoin.
De plus, vous pouvez désactiver les totaux généraux et les sous-totaux dans l'onglet Constructeur – Totaux généraux и Sous-totaux (Conception — totaux généraux, sous-totaux) et au même endroit, basculez le résumé vers une disposition de tableau plus pratique avec le bouton Maquette de rapport (Mise en page du rapport).
Ainsi, nous sommes déjà à mi-chemin du résultat : nous avons une table où, à l'intersection de la ville et du mois, il y a un numéro de ligne dans la table source, où se trouve le code conteneur dont nous avons besoin.
Maintenant, copions le résumé (sur la même feuille ou une autre) et collons-le en tant que valeurs, puis entrons notre formule dans la zone de valeur, ce qui extraira le code du conteneur par le numéro de ligne trouvé dans le résumé :
Fonction IF (SI), dans ce cas, vérifie que la cellule suivante du résumé n'est pas vide. S'il est vide, affichez une chaîne de texte vide "", c'est-à-dire laissez la cellule vide. Si non vide, puis extrait de la colonne Contenant tableau des sources Livraisons contenu de la cellule par numéro de ligne à l'aide de la fonction INDEX (INDICE).
Peut-être que le seul point pas très évident ici est le double mot Contenant dans la formule. Une écriture si étrange :
Fournitures[[Récipient]:[Récipient]]
… n'est nécessaire que pour référencer la colonne Contenant était absolu (comme une référence avec des signes $ pour les tables ordinaires "non intelligentes") et ne glissait pas vers les colonnes voisines lors de la copie de notre formule vers la droite.
À l'avenir, lors de la modification des données dans la table source Livraisons, nous devons nous rappeler de mettre à jour notre résumé auxiliaire avec les numéros de ligne en faisant un clic droit dessus et en choisissant la commande Mettre à jour et enregistrer (Rafraîchir).
Méthode 3. Formules
Cette méthode ne nécessite pas la création d'un tableau croisé dynamique intermédiaire et une mise à jour manuelle, mais utilise "l'arme lourde" d'Excel - la fonction SUMMESLIMN (SUMIFS). Au lieu de rechercher des numéros de ligne dans un récapitulatif, vous pouvez les calculer à l'aide de cette formule :
Avec un certain encombrement externe, en fait, il s'agit d'un cas d'utilisation standard pour la fonction de sommation sélective SUMMESLIMNA qui additionne les numéros de ligne pour la ville et le mois donnés. Encore une fois, puisque nous n'avons pas plusieurs conteneurs dans la même ville au cours du même mois, notre fonction ne donnera en fait pas le montant, mais le numéro de ligne lui-même. Et puis la fonction déjà familière de la méthode précédente INDEX Vous pouvez également extraire les codes de conteneur :
Bien sûr, dans ce cas, vous n'avez plus besoin de penser à mettre à jour le récapitulatif, mais sur les grandes tables, la fonction SOMMESLI peut être sensiblement lent. Ensuite, vous devrez désactiver la mise à jour automatique des formules ou utiliser la première méthode - un tableau croisé dynamique.
Si l'apparence du résumé n'est pas très adaptée à votre rapport, vous pouvez en extraire les numéros de ligne dans le tableau final non pas directement, comme nous l'avons fait, mais en utilisant la fonction GET.PIVOT.TABLE.DATA (OBTENIR.PIVOT.DONNEES). Comment faire cela peut être trouvé ici.
- Comment créer un rapport à l'aide d'un tableau croisé dynamique
- Comment configurer des calculs dans des tableaux croisés dynamiques
- Comptage sélectif avec SUMIFS, COUNTIFS, etc.