Excel est une application bureautique puissante qui vous permet d'effectuer des calculs informatiques complexes, d'organiser des informations, de créer des calculs analytiques. Malheureusement, de nombreux utilisateurs, ne comprenant pas les subtilités d'Excel, ne l'utilisent que comme un simple outil de calcul comme une calculatrice.

Analyser dans le dossier un bref délais les informations présentées, accompagnées d'indicateurs numériques, aident à prendre des décisions de gestion efficaces en fonction de celles-ci, un tableau croisé dynamique dans Excel aide, et comment le faire, vous pouvez facilement comprendre en lisant les recommandations des utilisateurs avancés.

Le tableau croisé dynamique est créé en prenant comme base un tableau existant, qui peut être un produit Excel ou une autre base de données.

Si l'utilisateur a décidé de suivre le cours initial sur la création d'un tableau croisé dynamique dans Excel, il est alors préférable pour lui de s'exercer sur la version tabulaire créée par ce application bureautique... Après une excellente maîtrise des bases pour faire des tableaux croisés dynamiques dans Excel, vous pouvez passer à des options plus compliquées, lorsque les indicateurs sont obtenus en chargeant à partir de documents créés par des applications complètement différentes.

Exigences primaires

Un certain nombre d'exigences doivent être remplies, sinon le tableau croisé dynamique n'effectuera pas les tâches souhaitées par l'utilisateur.

L'exigence principale est que toutes les données doivent être saisies sous la forme d'un tableau, dans la partie supérieure duquel, bien sûr, un en-tête est établi (indiquant les noms des colonnes).

De plus, les lignes et colonnes vides doivent être complètement exclues. En dehors du tableau, il ne doit pas y avoir de notes et d'entrées superflues entrées dans les lignes ou les colonnes adjacentes à la zone utilisée.

Il est souhaitable que toutes les cellules du tableau contiennent également des données ; si possible, vous devez éviter les cellules vides, car cela peut affecter négativement la systématisation et l'analyse ultérieure des informations.

Souvent dans Excel, les utilisateurs fusionnent plusieurs cellules. Si vous devez créer des rapports de synthèse, vous devez éviter une telle combinaison. Il est inacceptable de masquer des lignes ou des colonnes qui, de l'avis de l'utilisateur, ne sont pas importantes dans cette situation.

Instructions pour la création

Une fois que l'utilisateur a satisfait à toutes les exigences ci-dessus, vous pouvez passer aux étapes suivantes pour comprendre comment créer un tableau croisé dynamique dans Excel.

Tout d'abord, vous devez trouver la première cellule, qui se trouve juste en dessous de l'en-tête du tableau, et la rendre active. Pour ce faire, vous devez placer le curseur de la souris sur cette cellule et faire un clic gauche.

Ensuite, dans le menu principal, allez dans l'onglet "Insérer", après quoi son sous-menu s'affichera, parmi lesquels se trouvera une icône avec le nom de l'élément "Tableau croisé dynamique". En cliquant dessus, l'assistant de création de rapports de synthèse est activé, grâce auquel il sera beaucoup plus simple de faire un rapport analytique tabulaire.

Dans la boîte de dialogue qui apparaît éditeur de tableur vous demandera de préciser la plage à analyser. Le plus souvent, l'assistant l'identifie correctement par lui-même, aucune modification n'est donc requise.

Il est également nécessaire d'indiquer où l'utilisateur souhaite créer un rapport de synthèse :

• sur la même feuille que les principaux indicateurs ;
• sur une nouvelle feuille.

En allant sur une nouvelle feuille, l'utilisateur y trouvera une zone pour le tableau, ainsi qu'une liste de champs valides. Après avoir décidé quels indicateurs spécifiques doivent être analysés, l'utilisateur doit faire glisser les champs sélectionnés dans la zone "Filtre", "Colonne" avec la souris. De cette façon, il sera possible de créer les colonnes du rapport de synthèse.

De plus, exactement de la même manière, les lignes sont formatées en faisant glisser les champs requis avec la souris. S'il y a plusieurs lignes, il est important de déterminer laquelle doit être en haut et laquelle doit être en dessous. Vous pouvez d'abord organiser les lignes dans un ordre et observer à quel point ce sera pratique, à quel point les informations récapitulatives seront affichées correctement. S'il devient difficile d'analyser les données avec un tel arrangement, alors tout peut être facilement corrigé - maintenez simplement un certain champ enfoncé avec la souris et déplacez-le vers le haut ou vers le bas.

Au final, il reste à faire la dernière zone du rapport de synthèse "Valeurs". Un champ clé pour l'analyse lui est transféré, par exemple, la masse totale, la quantité totale.

Si tout est fait strictement selon les instructions, alors un rapport analytique sera déjà affiché à ce stade. Si des erreurs sont détectées, il suffit d'échanger les champs, de mettre à jour les données, et la table sera automatiquement reconstruite.

Utilisation des rapports de synthèse

L'utilisateur décide de faire un tableau croisé dynamique s'il devient nécessaire de recourir fréquemment à des activités analytiques. À cet égard, il est utile de savoir que le tableau est équipé d'un filtre qui permet d'organiser les données selon les critères spécifiés.

Filtrer l'application

Il est utile pour les utilisateurs novices de savoir comment mettre à jour un tableau croisé dynamique dans Excel et comment interroger en fonction du regroupement de données.

L'actualisation des données du tableau croisé dynamique est importante lorsque l'utilisateur a apporté des modifications au tableau principal. Pour mettre à jour les données, cliquez avec la souris dans la zone du tableau, en appelant menu supplémentaire... Il est facile d'y trouver l'élément "Mettre à jour", et en le sélectionnant, la mise à jour se produira automatiquement.

Le filtrage dans les rapports de synthèse est également très important. Très souvent, les données quotidiennes des employés sont saisies et l'employé responsable des rapports analytiques doit analyser les données pendant un trimestre, six mois.

Le filtre aide à résoudre ce problème. Vous n'aurez plus besoin de refaire quoi que ce soit de compliqué. Il suffit de cliquer sur n'importe quelle date avec la souris (touche droite), en menu contextuel sélectionnez "Groupe", après quoi une boîte de dialogue s'ouvrira dans laquelle vous pourrez définir les méthodes de regroupement qui vous conviennent. Après cela, le tableau sera automatiquement reconstruit, sa taille sera considérablement réduite et il sera donc beaucoup plus facile d'analyser les données.

Ainsi, créer des tableaux croisés dynamiques n'est pas difficile si vous vous entraînez d'abord sur un tableau simple, en respectant toutes les exigences et en entrant les données correctes. Après avoir maîtrisé ces compétences, l'utilisateur en ressentira très rapidement les avantages, car l'analyse des données sera effectuée dans un court laps de temps, accompagné d'une précision absolue.

Bonjour les amis. À quelle fréquence devez-vous généraliser de grands ensembles de données ? Recevoir des sous-totaux ? Si souvent, alors les tableaux croisés dynamiques Excel sont ce dont vous avez besoin de toute urgence ! Cela ne prend que quelques minutes pour créer un tableau croisé dynamique, et le résultat est comme s'il avait fonctionné pendant une semaine entière. Tentant? Nous lisons!

Le tableau croisé dynamique est un outil puissant Microsoft Excel, qui résout de nombreux problèmes et, surtout, répond à de nombreuses questions sur les processus décrits par les numéros de votre dossier. Laisse moi te donner un exemple. L'image ci-dessous présente une liste des ventes des points de vente dans différentes régions, réparties par jour tout au long de l'année :

C'est vrai, ce tableau n'est pas très informatif et sous cette forme n'est pas utile ? Et voici un tableau croisé dynamique formé à partir de ces données :

Ici, toutes les ventes sont triées par régions et responsables en lignes, par groupes de produits en colonnes - en colonnes. De telles données sont déjà appropriées, au moins, pour une analyse ultérieure. Nous pouvons clairement voir que le tableau croisé dynamique résume efficacement de grandes quantités de données et, comme je le discuterai plus loin, ne nécessite pas d'efforts et de temps importants pour construire.

Quelles données conviennent aux tableaux croisés dynamiques

Toutes les plages de données dans Excel ne peuvent pas être utilisées pour créer un tableau croisé dynamique. Les données doivent être normalisées. Dans notre cas, cela signifie que chaque ligne doit décrire un événement et que toutes les colonnes doivent être remplies pour cela. Chaque ligne d'une colonne doit contenir les données d'une. Jetez un autre coup d'œil à ce à quoi cela ressemble dans la première image du message.

Il est impératif que chaque colonne ait un titre informatif, c'est-à-dire le chapeau doit être plein.

Comment faire un tableau croisé dynamique

Pour créer un tableau croisé dynamique basé sur vos données, suivez cette séquence d'actions :

1. Placez le curseur dans n'importe quelle cellule du tableau
2. Cliquez sur le ruban : Insérer - Tableau croisé dynamique
3. Précisez l'emplacement du futur tableau croisé dynamique. Pour le placer sur une nouvelle feuille - cochez la case "Sur une nouvelle feuille". Pour sélectionner un emplacement sur les feuilles existantes - sélectionnez "Sur la feuille existante" et dans le champ "Plage" spécifiez l'emplacement de la cellule en haut à gauche du tableau croisé dynamique ;

1. Cliquez sur OK

Une zone de tableau croisé dynamique vide et un menu de composition de données s'ouvriront. Ce dernier se compose de cinq fenêtres :

1. "Sélectionner les champs à ajouter au rapport" Sont les en-têtes de toutes les colonnes du tableau. Avec ces données, nous remplirons les 4 prochains blocs
2. Filtres- une liste de champs par lesquels le filtre sera appliqué. Ces champs apparaissent au-dessus du tableau croisé dynamique
3. Colonnes- la zone où il est précisé ce qui sera contenu dans les colonnes
4. Cordes- la zone où il est indiqué ce qui sera contenu dans les lignes
5. Les valeurs- on définit ce qui sera affiché ou calculé à l'intersection des lignes ou des colonnes. C'est-à-dire que le corps principal du tableau est

Les zones 2 à 5 sont remplies de données en faisant glisser les en-têtes de l'élément 1. Par exemple, vous devez savoir quel est le montant des ventes par an pour les managers dans toutes les régions. Cela signifie que dans les lignes, nous aurons des régions et des gestionnaires, et dans les valeurs - le total des ventes. Faites glisser les noms de colonnes correspondants de la première zone du menu de mise en page vers Lignes et valeurs. Voici ce que vous obtenez :

Si maintenant nous voulons que les données dans les colonnes soient ventilées par groupes de produits. Faites glisser le champ « Groupe de produits » vers « Colonnes », nous obtenons le résultat :

Et si tout à coup nous décidons que nous n'avons besoin de données que pour la première région, ajoutez le champ "Région" et dans les "Filtres", une zone de filtre apparaîtra au-dessus du tableau croisé dynamique. Ouvrez la liste déroulante dans cette zone et sélectionnez uniquement la première région.

Il me semble que c'est un outil très simple, et il faut absolument le maîtriser. Imaginez que ma liste, qui sert d'exemple pour cet article, compte 9 883 lignes, et je les traite sans effort, en quelques clics. Et un tel tableau, comme nous venons de le faire, ressemble à un rapport professionnel.

Et maintenant, par exemple, nous voulions savoir lequel des gérants vend le plus. Supprimons tous les filtres, supprimons la case à cocher "Région" des lignes. Nous obtenons une liste des gestionnaires et de leurs ventes. cliquons clic-droit souris dans l'une des lignes "ventes" de la colonne "Total", dans le menu contextuel, sélectionnez Trier - Trier par ordre décroissant... Naturellement, le haut sera le manager avec les ventes les plus élevées, le bas - avec le plus bas.

Dans le champ "Valeurs", vous pouvez faire plus que simplement résumer les données. Par exemple, vous pouvez compter le nombre de valeurs, afficher le minimum ou valeur maximum et beaucoup plus. Pour ce faire, faites un clic droit sur n'importe quelle cellule de la colonne souhaitée, dans le menu contextuel, cliquez sur "Total par", puis sélectionnez la fonction dont vous avez besoin.

Comment changer la disposition d'un tableau croisé dynamique

Vous pouvez personnaliser la disposition du tableau croisé dynamique dans la partie logique de construction. Sélectionnez l'une de ses cellules et recherchez sur le ruban Constructeur - Mise en page... Ici, vous pouvez effectuer des réglages pour quatre points :

1. Sous-totaux- activer ou désactiver les totaux pour les groupes intermédiaires dans le tableau
2. Résumé général- mettre en place le calcul des totaux généraux pour l'ensemble du tableau
3. Présentation du rapport- une manière d'organiser les données pour le plus grand confort
4. Lignes vides- insérer ou supprimer des lignes vides à la fin de chaque catégorie pour améliorer la lisibilité des données.

Formatage du tableau croisé dynamique Excel

Étant donné que les tableaux croisés dynamiques prétendent être un outil universel pour l'exécution de rapports, ils doivent être flexibles dans la personnalisation de leur apparence. Pour vous inscrire vous pouvez :

1. Régler
2. Changer
3. Appliquer

Et appliquez les opérations de formatage auxquelles vous êtes déjà habitué. En règle générale, cela suffit pour que le rapport final soit informatif et facile à comprendre.

Pour personnaliser davantage apparence- faites un clic droit sur n'importe quelle cellule du tableau croisé dynamique et sélectionnez "Paramètres du tableau croisé dynamique" dans le menu contextuel. Plusieurs paramètres utiles sont rassemblés ici. Par exemple, définissez ce qu'il faut afficher à la place ou personnalisez la granularité du tableau croisé dynamique.

Si votre tableau ne vous satisfait pas entièrement et que vous souhaitez modifier légèrement son contenu au niveau du contenu des lignes, des colonnes et des données de base, vous pouvez le faire à tout moment. Faites glisser et déposez les blocs d'en-tête dans le menu des paramètres du tableau croisé dynamique, supprimez et ajoutez, modifiez les filtres. Le programme réagira immédiatement aux changements que vous apportez.

Il s'agit de créer des tableaux croisés dynamiques, mais le sujet n'est toujours pas clos et dans le prochain article j'en parlerai. Je recommande de le lire et de l'utiliser, car il n'y a rien de mieux que d'obtenir des résultats rapidement et sans effort !

Comme toujours, j'attends vos questions et commentaires, nous deviendrons des professionnels ensemble !

Bonjour à tous! Le matériel d'aujourd'hui est destiné à ceux qui continuent à maîtriser le travail avec les programmes d'application et ne savent pas comment créer un tableau croisé dynamique dans Excel.

En créant tableau général, dans l'un des documents texte, vous pouvez l'analyser en créant des tableaux croisés dynamiques dans Excel.

La création d'un tableau croisé dynamique Excel nécessite le respect de certaines conditions :

1. Les données s'insèrent dans un tableau avec des colonnes et des listes avec des noms.
2. Manque de formulaires vierges.
3. Manque d'objets cachés.

Comment faire un tableau croisé dynamique dans Excel: instructions étape par étape

Pour créer un tableau croisé dynamique, vous devez :

Établi page blanche où vous pouvez voir les listes des zones et des champs. Les rubriques sont devenues des champs dans notre nouveau tableau... Le tableau croisé dynamique sera formé par glisser-déposer des champs.

Ils seront marqués d'une coche, et pour la commodité de l'analyse, vous les échangerez dans les zones du tableau.


J'ai décidé d'analyser les données à travers un filtre par vendeurs, afin qu'il soit visible par qui et pour combien chaque mois a été vendu, et quel type de produit.

Nous sélectionnons un vendeur spécifique. Appuyez sur la souris et faites glisser le champ « Vendeur » vers le « Filtre de rapport ». Le nouveau champ est coché et la vue du tableau change légèrement.


Nous mettrons la catégorie "Produits" sous forme de chaînes. Nous transférons le champ requis dans "Noms de ligne".


Pour afficher la liste déroulante, l'ordre dans lequel nous spécifions le nom est important. Si au départ dans les lignes nous faisons un choix en faveur du produit, puis nous indiquons le prix, alors les produits ne seront que des listes déroulantes, et vice versa.

La colonne "Unités", étant dans le tableau principal, affichait la quantité de marchandises vendues par un vendeur spécifique à un prix spécifique.


Pour afficher les ventes, par exemple, pour chaque mois, vous devez remplacer le champ « Date » par les « Noms des colonnes ». Sélectionnez la commande "Grouper" en cliquant sur la date.


Nous indiquons les périodes de la date et de l'étape. Nous confirmons le choix.

Nous voyons un tel tableau.


Transférons le champ "Montant" dans la zone "Valeurs".


L'affichage des nombres est devenu visible, mais nous avons besoin exactement du format des nombres


Pour y remédier, sélectionnez les cellules, appelez la fenêtre avec la souris, sélectionnez "Format des nombres".

Nous choisissons le format de nombre pour la fenêtre suivante et marquons le "Séparateur de groupe". Validez avec le bouton "OK".

Conception de table pivotante

Si nous cochons la case qui confirme la sélection de plusieurs objets à la fois, alors nous pourrons traiter les données de plusieurs vendeurs à la fois.


Le filtre peut être appliqué aux colonnes et aux lignes. En cochant la case sur l'une des variétés de produits, vous pouvez savoir quelle quantité a été vendue par un ou plusieurs vendeurs.


Les paramètres de champ sont également configurés séparément. Dans l'exemple, nous voyons qu'un certain vendeur rom a vendu des chemises pour un montant spécifique au cours d'un mois spécifique. En cliquant sur la souris, nous appelons le menu dans la ligne "Somme par champ..." et sélectionnons "Paramètres des champs de valeur".


De plus, pour la réduction des données sur le terrain, sélectionnez « Quantité ». Nous confirmons le choix.

Jetez un œil au tableau. Cela montre clairement qu'au cours de l'un des mois, le vendeur a vendu 2 chemises.


Maintenant, nous changeons la table et faisons fonctionner le filtre par mois. Nous transférons le champ "Date" dans le "Filtre du rapport", et où les "Noms des colonnes" seront "Vendeur". Le tableau affiche toute la période de vente ou pour un mois spécifique.

La sélection de cellules dans un tableau croisé dynamique affichera un onglet tel que Outils de tableau croisé dynamique, et il y aura deux autres onglets, Options et Conception.


En fait, on peut encore très longtemps parler de paramétrage des tableaux croisés dynamiques. Apportez des modifications à votre goût, en obtenant une utilisation pratique pour vous. N'hésitez pas à pousser et à expérimenter. Vous pouvez toujours modifier n'importe quelle action en appuyant sur le raccourci clavier Ctrl + Z.

J'espère que vous maîtrisez tout le matériel et que vous savez maintenant comment créer un tableau croisé dynamique dans Excel.

La vie d'une personne du monde de la technologie est une incroyable combinaison de chiffres, des indicateurs qui nécessitent périodiquement une systématisation. Pour ce faire, vous devez utiliser des outils techniques spéciaux. Notre article prouvera que les tableaux croisés dynamiques Excel sont accessibles même pour les nuls.

Un peu de théorie

Les tableaux croisés dynamiques Excel (pour un mannequin) sont une sorte de registre qui contient une donnée spécifique d'une source à analyser et est représenté de manière à ce que vous puissiez tracer les connexions logiques entre eux. La base de sa conception est une liste spécifique de valeurs.

Avant de commencer à travailler, cela vaut la peine de se préparer pour elle matériel nécessaire qui peuvent être résumés pour analyse. Lors de la formation de la version préparatoire, assurez-vous que les données sont classées, par exemple, les chiffres ne sont pas confondus avec la désignation des lettres et toutes les colonnes ont des en-têtes.

Les tableaux croisés dynamiques dans Excel sont indispensables pour ceux qui, de par la nature de leur emploi, traitent un grand nombre de chiffres qui doivent périodiquement être systématisés et rapportés. programme Excel vous aidera à calculer et à analyser un grand nombre de valeurs, ce qui vous fera gagner du temps.

Avantages de l'utilisation de ce type de regroupement de données :

• pendant le travail, vous n'avez pas besoin de connaissances particulières dans le domaine de la programmation, la méthode convient aux nuls;
• la capacité de combiner des informations provenant d'autres sources primaires ;
• vous pouvez reconstituer l'instance de base nouvelle information en ajustant légèrement les paramètres.

Apprendre à travailler avec des tableaux croisés dynamiques dans Excel ne prend pas longtemps et peut être basé sur une vidéo.

Exemple de tableau croisé dynamique Excel - Algorithme pour les nuls

Après vous être familiarisé avec les nuances théoriques de base des tableaux croisés dynamiques dans Excel, passons à leur application pratique. Pour commencer à créer un tableau croisé dynamique dans Excel 2016, 2010 ou 2007, vous devez installer Logiciel... En règle générale, si vous utilisez les programmes système Microsoft Office alors Excel est déjà sur votre ordinateur.

En la lançant, vous verrez un vaste champ, divisé en un grand nombre de cellules. Plus en détail sur la création de tableaux croisés dynamiques dans Excel, le didacticiel vidéo ci-dessus vous le dira.

À l'aide de l'algorithme suivant, nous examinerons en détail un exemple de création d'un tableau croisé dynamique dans Excel.
Sur le panneau en haut de la fenêtre, allez dans l'onglet "Insérer", où dans le coin gauche, sélectionnez "Tableau croisé dynamique".


Ensuite, une boîte de dialogue s'ouvre sur l'écran où vous devez spécifier les paramètres appropriés. Il y a plusieurs points importants à considérer à ce stade de la création de tableaux croisés dynamiques dans Excel. Si, avant de commencer la formation du registre, vous placez l'icône du curseur sur la feuille, alors le remplissage des lignes vides de la fenêtre se fera automatiquement. Sinon, l'adresse de la plage de données doit être désignée indépendamment.


Examinons de plus près comment remplir vous-même les éléments de la boîte de dialogue.

Ne laissez pas la première ligne vide, sinon le programme générera une erreur. S'il existe une source à partir de laquelle vous prévoyez de transférer des données, sélectionnez-la dans l'élément "Utiliser une source de données externe". Une source externe signifie une autre classeur Excel ou un ensemble de modèles de données à partir d'un SGBD.

Pré-titrer chaque colonne

Sélectionnez l'endroit où sera situé le futur cadre avec des cellules. Il peut s'agir d'une nouvelle fenêtre ou de cette feuille, nous vous recommandons d'utiliser une autre feuille.


Après avoir réglé tous les paramètres, nous obtenons une base toute faite. Sur le côté gauche se trouve la zone où se trouve la base du futur cadre. Sur le côté droit, il y a une fenêtre de paramètres qui vous aide à gérer le registre.


Maintenant, vous devez comprendre comment toute cette structure est construite. Dans la fenêtre de paramétrage "Champs de table libres", vous désignez les données qui seront présentes.


Toute la structure est construite de telle sorte que les données textuelles jouent le rôle d'éléments fédérateurs, et les données numériques montrent des valeurs consolidées. Par exemple, combinons tous les reçus par département et découvrons combien chacun a reçu les marchandises. Pour cela, cochez la case à côté de deux rubriques : rayon et coût des marchandises d'un lot.


Remarquez comment ces données sont situées dans la zone inférieure du panneau de personnalisation.


Le département est entré automatiquement en lignes et les données numériques en valeurs. Si vous essayez de cliquer sur une colonne de chiffres, ils apparaîtront dans cette zone. Une nouvelle colonne sera ajoutée à la table elle-même.


Dans ce cas, la sommation a lieu. Maintenant, nous pouvons savoir à partir de notre rapport combien de marchandises sont arrivées dans chaque département et leur coût total.

Vous pouvez ajuster librement la largeur des colonnes pour un positionnement optimal des données. Développez simplement la largeur des colonnes ou la hauteur des lignes comme vous en avez l'habitude dans Excel.


Si vous n'aimez pas cette orientation, vous pouvez faire glisser et déposer les noms de ligne dans la zone de colonne - survolez simplement la souris, cliquez sur bouton gauche et tirez.


Lorsqu'il s'agit de calculer des résultats, la somme est loin d'être la seule fonction. Pour voir ce que propose Excel d'autre, cliquez sur le titre dans le volet Valeurs et sélectionnez la dernière commande.


Dans les options du champ de valeur, vous trouverez de nombreuses options à analyser.


Pour chaque valeur, vous pouvez sélectionner une fonction différente. Par exemple, ajoutons un champ "Prix" et trouvons le prix maximum d'un article dans chaque rayon. En fait, nous découvrirons combien coûte le plus cher.


Nous voyons maintenant que le département «Accessoires» a reçu des marchandises d'une valeur de 267 660 roubles, tandis que le plus cher a un prix de 2 700 roubles.
La zone "Filtres" permet de définir les critères de sélection des enregistrements. Ajoutez le champ "Date de réception" en cochant simplement la case à côté.


Maintenant, le tableau croisé dynamique dans Excel semble gênant si nous devons analyser par date. Par conséquent, déplaçons la date des lignes vers les filtres - faites simplement glisser et déposez, comme mentionné ci-dessus.


Le résultat de ces actions a été l'apparition d'un autre champ au sommet. Pour sélectionner une date, cliquez sur la flèche à côté du mot "Tous".


Maintenant, nous pouvons sélectionner un jour spécifique, pour ouvrir la liste, cliquez sur le triangle dans le coin droit.


Vous pouvez également sélectionner des valeurs pour un département.


Décochez les cases qui ne vous intéressent pas et vous n'obtenez que information nécessaire.

Pendant que vous travaillez, vous pouvez rencontrer un message similaire "Nom du tableau croisé dynamique Excel non valide". Cela signifie que la première ligne de la plage à partir de laquelle ils essaient d'extraire des informations est laissée avec des cellules vides. Pour résoudre ce problème, vous devez remplir les vides dans la colonne.

Actualiser les données dans un tableau croisé dynamique dans Excel

La question importante est de savoir comment créer et mettre à jour un tableau croisé dynamique dans Excel 2010 ou une autre version. Ceci est pertinent lorsque vous êtes sur le point d'ajouter de nouvelles données. Si la mise à jour n'a lieu que pour une colonne, vous devez cliquer avec le bouton droit n'importe où dessus. Dans la fenêtre qui apparaît, vous devez cliquer sur "Mettre à jour".


Si une telle action doit être effectuée avec plusieurs colonnes et lignes à la fois, sélectionnez n'importe quelle zone et sur panneau du haut ouvrez l'onglet "Analyse" et cliquez sur l'icône "Actualiser". Choisissez ensuite l'action souhaitée.


Si vous n'avez pas besoin d'un tableau croisé dynamique dans Excel, cela vaut la peine de trouver comment le supprimer. Ce ne sera pas difficile. Sélectionnez tous les composants manuellement, ou en utilisant le raccourci clavier "CTRL + A". Appuyez ensuite sur la touche SUPPRIMER et le champ sera effacé.

Comment ajouter une colonne ou un tableau à un tableau croisé dynamique Excel

Pour ajouter une colonne supplémentaire, vous devez l'ajouter aux données d'origine et étendre la plage de notre registre.


Cliquez sur l'onglet Analyser et ouvrez la source de données.


Excel va tout suggérer.


Actualisez et vous obtiendrez une nouvelle liste de champs dans la zone de personnalisation.

Vous ne pouvez ajouter un tableau que si vous le "collez" à celui d'origine. Vous pouvez remplacer une plage dans une plage existante, mais vous ne pouvez pas ajouter une autre plage à la volée. Mais vous pouvez créer un nouveau tableau croisé dynamique basé sur plusieurs originaux, même situés sur des feuilles différentes.

Comment faire un tableau croisé dynamique dans Excel à partir de plusieurs feuilles

Pour cela, nous avons besoin de l'assistant de tableau croisé dynamique. Ajoutons-le au panneau accès rapide(tout en haut de la fenêtre est à gauche). Cliquez sur la flèche déroulante et choisissez Plus de commandes.


Sélectionnez toutes les équipes.


Et trouver le maître des pivots feuilles de calcul Excel, cliquez dessus, puis sur "Ajouter" et OK.


L'icône apparaîtra en haut.


Vous devriez avoir deux tables identiques dans des champs sur des feuilles différentes. Nous avons ces données sur les recettes aux départements pour les mois de mai et juin. Cliquez sur le raccourci Assistant de tableau croisé dynamique et sélectionnez Consolidation de plage.


Nous avons besoin de plusieurs champs, pas d'un seul.


À l'étape suivante, sélectionnez la première plage et cliquez sur le bouton "Ajouter". Passez ensuite à une autre feuille (cliquez sur son nom ci-dessous) et "Ajouter" à nouveau. Vous aurez deux plages créées.

Ne sélectionnez pas la table entière. Nous avons besoin d'informations sur les reçus du département, nous avons donc sélectionné une plage commençant par la colonne "Département".
Donnez un nom à chacun. Cliquez sur le cercle 1, puis saisissez "Mai" dans le champ, cliquez sur le cercle 2 et saisissez "Juin" dans le champ 2. N'oubliez pas de changer de gamme dans la région. Celui qui est nommé doit être mis en évidence.

Cliquez sur Suivant et créez sur une nouvelle feuille.


Après avoir cliqué sur "Terminer", nous obtenons le résultat. C'est un tableau multidimensionnel, il peut donc être difficile à gérer. Par conséquent, nous avons choisi une gamme plus petite afin de ne pas se tromper dans les mesures.


Notez que nous n'avons plus de noms de champs clairs. Ils peuvent être retirés en cliquant sur les éléments dans zone supérieure.


En décochant ou en cochant les cases, vous ajustez les valeurs que vous souhaitez voir. Il est également gênant que le calcul soit le même pour toutes les valeurs.

Comme vous pouvez le voir, nous avons une valeur dans la zone correspondante.

Modification de la structure du rapport

Nous avons parcouru un exemple étape par étape de la création d'un tableau croisé dynamique Exce, et nous vous expliquerons plus tard comment obtenir des données d'un type différent. Pour ce faire, nous allons modifier la mise en page du rapport. Après avoir placé le curseur sur n'importe quelle cellule, allez dans l'onglet "Conception", puis "Mise en page du rapport".

Vous aurez le choix entre trois types de structuration des informations :

• Forme compressée

Ce type de programme est appliqué automatiquement. Les données ne sont pas étirées, il n'est donc presque pas nécessaire de faire défiler les images. Vous pouvez économiser de l'espace sur les signatures et le laisser pour les numéros.

• Forme structurée

Tous les indicateurs sont présentés hiérarchiquement : du plus petit au plus grand.

• Forme tabulaire

L'information est présentée sous le couvert d'un registre. Cela facilite le transfert de cellules vers de nouvelles feuilles.

En arrêtant la sélection sur une mise en page adaptée, vous consolidez les réglages effectués.

Alors, nous vous avons expliqué comment créer les champs d'un tableau croisé dynamique MS Excel 2016 (en 2007, 2010, suivez l'analogie). Nous espérons que ces informations vous aideront à analyser rapidement les données consolidées.

Passe une bonne journée!

Ce n'est pas la première fois que Renat est un écrivain invité sur Lifehacker. Nous avons déjà publié d'excellents documents de sa part sur la façon de créer un plan d'entraînement : et des ressources en ligne, ainsi que sur la création d'un plan d'entraînement.

Cet article contient des techniques simples qui vous permettent de. Ils sont particulièrement utiles pour ceux qui sont engagés dans le reporting de gestion, préparent une variété de rapports analytiques basés sur des téléchargements de 1C et d'autres rapports, forment des présentations et des diagrammes à partir de ceux-ci pour la gestion. Je ne prétends pas être une nouveauté absolue - sous une forme ou une autre, ces techniques ont probablement été discutées sur les forums ou.

Alternatives simples à RECHERCHEV et RECHERCHEH, si les valeurs souhaitées ne sont pas dans la première colonne du tableau : RECHERCHE, INDEX + RECHERCHE

Résultat:

Dans ce cas, en plus de la fonction CHAR (pour afficher les guillemets), on utilise la fonction SI, qui permet de modifier le texte selon qu'il y a une tendance positive des ventes, et la fonction TEXTE, qui permet d'afficher le numéro dans n'importe quel format. Sa syntaxe est décrite ci-dessous :

TEXTE ( sens; format)

Le format est spécifié entre guillemets, comme si vous entriez un format personnalisé dans la fenêtre Format de cellule.

Des textes plus complexes peuvent également être automatisés. Dans ma pratique, il y avait l'automatisation des commentaires longs mais routiniers au rapport de gestion dans le format « L'INDICATEUR a baissé / augmenté de XX par rapport au plan, principalement en raison de la croissance / baisse de FACTOR1 de XX, croissance / baisse de FACTOR2 de YY ..." avec une liste changeante de facteurs. Si vous écrivez souvent de tels commentaires et que le processus de rédaction peut être algorithmisé, il vaut la peine de créer une formule ou une macro qui vous épargnera au moins une partie du travail.

Comment stocker des données dans chaque cellule après la concaténation

Lorsque vous fusionnez des cellules, une seule valeur est conservée. Excel met en garde à ce sujet lorsque vous essayez de fusionner des cellules :

En conséquence, si vous aviez une formule qui dépendait de chaque cellule, elle cesserait de fonctionner après les avoir combinées (erreur # N / A aux lignes 3-4 de l'exemple):

Pour concaténer des cellules tout en conservant les données dans chacune d'elles (vous avez peut-être une formule comme dans cet exemple abstrait ; vous voulez peut-être concaténer des cellules, mais conserver toutes les données pour l'avenir ou les masquer intentionnellement), concaténez toutes les cellules de la feuille , sélectionnez-les, puis utilisez la commande Reproduire la mise en forme pour transférer la mise en forme vers les cellules que vous devez combiner :

Comment construire un pivot à partir de plusieurs sources de données

Si vous devez créer un pivot à partir de plusieurs sources de données à la fois, vous devrez ajouter l'"Assistant Tableau croisé dynamique et graphique" au ruban ou au panneau d'accès rapide, qui dispose d'une telle option.

Vous pouvez le faire comme suit : "Fichier" → "Options" → "Barre d'outils d'accès rapide" → "Toutes les commandes" → "Assistant Tableau croisé dynamique et graphique" → "Ajouter":

Après cela, une icône correspondante apparaîtra sur le ruban, en cliquant sur laquelle appelle le même assistant :

Lorsque vous cliquez dessus, une boîte de dialogue apparaît :

Dans celui-ci, vous devez sélectionner l'élément "Dans plusieurs plages de consolidation" et cliquer sur "Suivant". Dans le paragraphe suivant, vous pouvez sélectionner "Créer un champ de page" ou "Créer des champs de page". Si vous souhaitez créer indépendamment un nom pour chacune des sources de données, sélectionnez le deuxième élément :

Dans la fenêtre suivante, ajoutez toutes les plages sur la base desquelles le pivot sera construit, et nommez-les :

Après cela, dans la dernière boîte de dialogue, spécifiez où le rapport de tableau croisé dynamique sera placé - sur une feuille existante ou nouvelle :

Le rapport du tableau croisé dynamique est prêt. Dans le filtre "Page 1", vous ne pouvez sélectionner qu'une seule des sources de données, si nécessaire :

Comment calculer le nombre d'occurrences du texte A dans le texte B ("Tarif MTS SuperMTS" - deux occurrences de l'abréviation MTS)

V cet exemple il y a plusieurs lignes de texte dans la colonne A, et notre tâche est de savoir combien de fois chacune d'elles contient le texte recherché situé dans la cellule E1 :

Pour résoudre ce problème, vous pouvez utiliser une formule complexe composée des fonctions suivantes :

1. DLSTR (LEN) - calcule la longueur du texte, le seul argument est le texte. Exemple : DLSTR ("machine") = 6.
2. SUBSTITUT - remplace un texte spécifique dans une chaîne de texte par un autre. Syntaxe : SUBSTITUTE ( texte; ancien_texte ; nouveau_texte). Exemple : REMPLACER (« voiture » ; « auto ; » » = « mobile ».
3. UPPER - remplace tous les caractères d'une chaîne par des majuscules. Le seul argument est le texte. Exemple : UPPER (« voiture ») = « VOITURE ». Nous avons besoin de cette fonction pour effectuer des recherches insensibles à la casse. Après tout, UPPER ("voiture") = UPPER ("Machine")

Pour trouver l'occurrence d'un certain chaîne de texteà un autre, vous devez supprimer toutes ses occurrences dans l'original et comparer la longueur de la chaîne résultante avec l'original :

DLSTR (« Tarif MTS Super MTS”) - DLSTR (“ Tarif Super ”) = 6

Et puis divisez cette différence par la longueur de la chaîne que nous recherchions :

6 / DLSTR (« MTS ») = 2

C'est exactement deux fois que la ligne "MTS" est incluse dans celle d'origine.

Il reste à écrire cet algorithme dans le langage des formules (notons le texte dans lequel on cherche des occurrences par « texte », et celui dont le nombre d'occurrences nous intéresse) :

= (DLSTR ( texte) -LSTR (REMPLACEMENT (SUPÉRIEUR ( texte); PLUS HAUT ( recherché); ""))) / DLSTR ( recherché)

Dans notre exemple, la formule ressemble à ceci :

= (DLSTR (A2) -LSTR (REMPLACEMENT (SUPÉRIEUR (A2), SUPÉRIEUR ($ E $ 1), ""))) / DLSTR ($ E $ 1)