1 tâche (générale) (2 points).

À l'aide d'une feuille de calcul, traitez les données à l'aide de fonctions statistiques.
1. Des informations sur les élèves de la classe (10 personnes) sont données, y compris les notes d'un mois en mathématiques. Comptez le nombre de cinq, quatre, deux et trois, trouvez la note moyenne de chaque élève et la note moyenne de l'ensemble du groupe. Créez un graphique illustrant le pourcentage de notes dans un groupe.

Tâche 2.1 (2 points).

Quatre amis voyagent par trois modes de transport : train, avion et bateau. Nikolaï a nagé 150 km sur un bateau à vapeur, parcouru 140 km en train et parcouru 1 100 km en avion. Vasily a parcouru 200 km sur un bateau à vapeur, parcouru 220 km en train et parcouru 1 160 km en avion. Anatoly a parcouru 1 200 km en avion, parcouru 110 km en train et navigué 125 km en bateau à vapeur. Maria a parcouru 130 km en train, parcouru 1 500 km en avion et navigué 160 km en bateau.
Créez une feuille de calcul basée sur les données ci-dessus.

Ajoutez une colonne au tableau qui affichera le nombre total de kilomètres parcourus par chacun des gars.

Calculez le nombre total de kilomètres que les gars ont parcourus en train, volé en avion et navigué en bateau (chaque mode de transport séparément).

Calculez le nombre total de kilomètres de tous les amis.

Déterminez le nombre maximum et minimum de kilomètres parcourus par vos amis pour tous les modes de transport.

Déterminez le nombre moyen de kilomètres pour tous les modes de transport.

Tâche 2.2 (2 points).

Créez un tableau « Lacs d'Europe » en utilisant les données suivantes pour la superficie (km²) et la profondeur la plus profonde (m) : Ladoga 17 700 et 225 ; Onega 9510 et 110 ; Mer Caspienne 371 000 et 995 ; Venern 5550 et 100 ; Chudskoye avec Pskov 3560 et 14 ; Balaton 591 et 11 ; Genève 581 et 310 ; Vättern 1900 et 119 ; Constance 538 et 252 ; Mälaren 1140 et 64. Déterminez le lac le plus grand et le plus petit en termes de superficie, le lac le plus profond et le moins profond.

Tâche 2.3 (2 points).

Créez un tableau « Fleuves d'Europe » en utilisant les données suivantes de longueur (km) et de superficie de bassin (milliers de km²) : Volga 3688 et 1350 ; Danube 2850 et 817 ; Rhin 1330 et 224 ; Île d'Elbe 1150 et 148 ; Vistule 1090 et 198 ; Loire 1020 et 120 ; Oural 2530 et 220 ; Don 1870 et 422 ; Seines 780 et 79 ; Tamise 340 et 15. Déterminez les rivières les plus longues et les plus courtes, calculez la superficie totale des bassins fluviaux, la longueur moyenne des rivières dans la partie européenne de la Russie.

3 tâches (2 points).

La banque enregistre la ponctualité des remboursements des prêts accordés à plusieurs organismes. Le montant du prêt et le montant déjà payé par l'organisme sont connus. Des pénalités sont fixées pour les débiteurs : si l'entreprise a remboursé le prêt à plus de 70 pour cent, alors l'amende sera de 10 pour cent de la dette, sinon l'amende sera de 15 pour cent. Calculez l'amende pour chaque organisation, l'amende moyenne, le montant total que la banque va recevoir en plus. Déterminer l'amende moyenne des organismes budgétaires.

Trouver du matériel pour n'importe quelle leçon,

Dans un tableau croisé dynamique standard, les données sources sont stockées sur le disque dur local. De cette façon, vous pouvez toujours les gérer et les réorganiser, même si vous n'avez pas accès au réseau. Mais cela ne s'applique en aucun cas aux tableaux croisés dynamiques OLAP. Dans les tableaux croisés dynamiques OLAP, le cache n'est jamais stocké sur le disque dur local. Par conséquent, immédiatement après la déconnexion du réseau local, votre tableau croisé dynamique perdra sa fonctionnalité. Vous ne pourrez déplacer aucun des champs qu'il contient.

Si vous devez encore analyser les données OLAP après une mise hors ligne, créez un cube de données hors ligne. Le cube de données hors ligne est fichier séparé, qui est un cache de tableau croisé dynamique qui stocke les données OLAP affichées après avoir été déconnectées du réseau local. Les données OLAP copiées dans un tableau croisé dynamique peuvent être imprimées, le site http://everest.ua le décrit en détail.

Pour créer un cube de données autonome, créez d'abord un tableau croisé dynamique OLAP. Placez le curseur dans le tableau croisé dynamique et cliquez sur le bouton Outils OLAP dans l'onglet contextuel Outils, qui fait partie du groupe d'onglets contextuels Outils de tableau croisé dynamique. Sélectionnez la commande OLAP hors ligne (Fig. 9.8).

Riz. 9.8. Créer un cube de données hors ligne

La boîte de dialogue Paramètres du cube de données OLAP hors ligne s'affiche. Cliquez sur le bouton Créer un fichier de données hors ligne. Vous avez démarré l'assistant de création de fichier de cube de données. Cliquez sur le bouton Suivant pour continuer la procédure.

Vous devez d'abord spécifier les dimensions et les niveaux qui seront inclus dans le cube de données. Dans la boîte de dialogue, vous devez sélectionner les données qui seront importées de la base de données OLAP. L'idée est de spécifier uniquement les dimensions qui seront nécessaires une fois l'ordinateur déconnecté du réseau local. Plus vous spécifiez de dimensions, plus le cube de données hors ligne sera grand.

Cliquez sur le bouton Suivant pour passer à la boîte de dialogue suivante de l'assistant. Il vous donne la possibilité de spécifier des membres ou des éléments de données qui ne seront pas inclus dans le cube. En particulier, vous n'aurez pas besoin de la mesure Internet Sales-Extended Amount, elle sera donc décochée dans la liste. Une case décochée indique que l'élément spécifié ne sera pas importé et occupera de l'espace supplémentaire sur le disque dur local.

À la dernière étape, spécifiez l'emplacement et le nom du cube de données. Dans notre cas, le fichier cube s'appellera MyOfflineCube.cub et se trouvera dans le dossier Work.

Les fichiers de cubes de données portent l'extension .lionceau

Après un certain temps, Excel enregistre le cube de données hors ligne dans le dossier spécifié. Pour le tester, double-cliquez sur le fichier, ce qui générera automatiquement un classeur Excel contenant un tableau croisé dynamique associé au cube de données sélectionné. Une fois créé, vous pouvez distribuer le cube de données hors ligne à tous les utilisateurs intéressés qui travaillent en mode LAN hors ligne.

Une fois connecté au réseau local, vous pouvez ouvrir le fichier du cube de données hors ligne et le mettre à jour, ainsi que la table de données correspondante. Principe principal dit que le cube de données hors ligne n'est utilisé que pour fonctionner lorsque le réseau local est déconnecté, mais il est nécessairement mis à jour une fois la connexion rétablie. Toute tentative de mise à jour d'un cube de données hors ligne après une interruption de la connexion échouera.

Travailler avec des fichiers de cube hors ligne

Un fichier de cube hors ligne (.cub) stocke les données sous la forme d'un cube OLAP (Online Analytical Processing). Ces données peuvent faire partie de la base de données OLAP sur le serveur OLAP ou peuvent être générées indépendamment de la base de données OLAP. Utilisez un fichier de cube hors ligne pour continuer à travailler avec les rapports de tableau croisé dynamique et de graphique croisé dynamique lorsque le serveur n'est pas disponible ou lorsque vous êtes hors ligne.

Note de sécurité : Soyez prudent lorsque vous utilisez ou distribuez un fichier de cube hors ligne contenant des informations sensibles ou personnelles. Au lieu d'un fichier cube, il est recommandé de stocker les données dans un classeur afin de pouvoir contrôler l'accès aux données à l'aide de la gestion des droits. Informations Complémentaires peuvent être trouvés dans Gestion des droits relatifs à l’information dans Office.

Lorsque vous travaillez avec un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur les données source du serveur OLAP, vous pouvez utiliser l'Assistant Cube hors ligne pour copier les données source dans un fichier de cube hors ligne distinct sur votre ordinateur. La création de ces fichiers hors ligne nécessite un fournisseur de données OLAP prenant en charge cette fonctionnalité, tel que MSOLAP de Microsoft SQL Server Analysis Services, installé sur l'ordinateur.

Note: La création et l'utilisation de fichiers de cube hors ligne à partir de Microsoft SQL Server Analysis Services sont soumises à des conditions et à une licence. Installations Microsoft Serveur SQL. Consultez les informations de licence pertinentes de l’édition SQL Server.

Travailler avec l'assistant de cube hors ligne

Pour créer un fichier de cube hors ligne, vous pouvez sélectionner un sous-ensemble de données dans une base de données OLAP à l'aide de l'assistant de cube hors ligne, puis enregistrer ce sous-ensemble. Le rapport n'a pas besoin d'inclure tous les champs inclus dans le fichier, ni d'en sélectionner aucun ni les champs de données disponibles dans la base de données OLAP. Pour réduire le fichier au minimum, vous pouvez inclure uniquement les données que vous souhaitez voir apparaître dans le rapport. Vous pouvez omettre toutes les dimensions et, pour la plupart des types de dimensions, vous pouvez également exclure les informations de niveau inférieur et les éléments de niveau supérieur que vous ne souhaitez pas afficher. Pour tous les éléments que vous incluez, les champs de propriété disponibles dans la base de données pour ces éléments sont également stockés dans le fichier hors ligne.

Transfert de données vers mode hors-ligne et leur connexion inverse

Pour ce faire, vous devez d'abord créer un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur la base de données du serveur, puis créer un fichier cube autonome à partir du rapport. Après cela, vous pouvez à tout moment basculer le rapport entre la base de données du serveur et le fichier hors ligne. Par exemple, si vous utilisez ordinateur portable pour les voyages à domicile et en vidéo, puis reconnectez votre ordinateur au réseau.

Voici les principales étapes à suivre pour vie de la batterie avec les données, puis transférez-les à nouveau sur Internet.

Créez ou ouvrez un rapport de tableau croisé dynamique ou de graphique croisé dynamique basé sur les données OLAP auxquelles vous souhaitez accéder hors ligne.

Créez un fichier cube hors ligne sur votre ordinateur. Au chapitre Créer un fichier de cube hors ligne à partir d'une base de données de serveur OLAP(ci-dessous dans cet article).

Se déconnecter du réseau et travailler avec un fichier cube hors ligne.

Allez en ligne et rattachez le fichier cube hors ligne. Consultez la rubrique Reconnexion du fichier de cube hors ligne à la base de données du serveur OLAP(ci-dessous dans cet article).

Mettez à jour le fichier de cube hors ligne avec de nouvelles données et régénérez le fichier de cube hors ligne. Consultez la rubrique mettre à jour et recréer le fichier de cube hors ligne(ci-dessous dans cet article).

BLOG

Uniquement des publications de qualité

Que sont les tableaux croisés dynamiques Excel et les cubes OLAP

Regardez la vidéo de l'article :

OLAP- c'est Anglais. traitement analytique en ligne, technologie de traitement des données analytiques en temps réel. en langage clair- un stockage de données multidimensionnelles (Cube), encore plus simple - juste une base de données à partir de laquelle vous pouvez récupérer des données dans Excel et les analyser à l'aide de l'outil Excel - PivotTables.

Tableaux croisés dynamiques est une interface utilisateur permettant d'afficher des données multidimensionnelles. En d’autres termes, un type spécial de tableaux avec lequel vous pouvez réaliser presque n’importe quel rapport.

Pour que ce soit clair, comparons le « Tableau régulier » avec le « Tableau croisé dynamique »

Table régulière :

Tableau croisé dynamique :

Différence principale Tableaux croisés dynamiques est la présence d'une fenêtre Liste des champs du tableau croisé dynamique parmi lesquels vous pouvez choisir Champs obligatoires et recevez n'importe quelle table automatiquement !

Comment utiliser

ouvrir fichier Excel, qui est connecté au cube OLAP, par exemple "BIWEB" :

Maintenant, qu’est-ce que cela signifie et comment l’utiliser ?

Glissez et déposez les champs obligatoires pour obtenir, par exemple, le tableau suivant :

« Avantages» vous permettent d'explorer le rapport en profondeur. Dans cet exemple, « Marque » est regroupé en « Noms abrégés » et « Trimestre » en « Mois », c'est-à-dire Donc:

Fonctions analytiques dans Excel (fonctions cube)

Microsoft ajoute constamment de nouvelles fonctionnalités à Excel en termes d'analyse et de visualisation de données. Travailler avec des informations dans Excel peut être représenté comme trois couches relativement indépendantes :

• données sources "correctement" organisées
• mathématiques (logique) du traitement des données
• présentation des données

Riz. 1. Analyse des données dans Excel : a) données brutes, b) mesure dans Power Pivot, c) tableau de bord ; Pour agrandir une image, faites un clic droit dessus et sélectionnez Ouvrir l'image dans un nouvel onglet

Télécharger la note dans Format Word ou pdf, exemples Excel

Fonctions de cube et tableaux croisés dynamiques

Les tableaux croisés dynamiques sont le moyen le plus simple et en même temps le plus puissant de présenter des données. Ils peuvent être construits à partir de données contenues dans : a) une feuille de calcul Excel, b) un cube OLAP ou c) un modèle de données Power Pivot. Dans les deux derniers cas, en plus du tableau croisé dynamique, vous pouvez utiliser des fonctions analytiques (fonctions cube) pour générer un rapport sur une feuille Excel. Les tableaux croisés dynamiques sont plus faciles. Les fonctions des cubes sont plus complexes, mais offrent plus de flexibilité, notamment en matière de reporting, elles sont donc largement utilisées dans les tableaux de bord.

La discussion suivante s'applique aux formules de cube et aux tableaux croisés dynamiques basés sur le modèle Power Pivot et, dans quelques cas, basés sur des cubes OLAP.

Un moyen simple d'obtenir les fonctions des cubes

Lorsque (si) vous avez commencé à apprendre le code VBA, vous avez appris que le moyen le plus simple d'obtenir le code est d'enregistrer une macro. De plus, le code peut être modifié, des boucles, des vérifications peuvent être ajoutées... De même, le moyen le plus simple d'obtenir un ensemble de fonctions de cube est de convertir le tableau croisé dynamique (Fig. 2). Placez-vous sur n'importe quelle cellule du tableau croisé dynamique, accédez à l'onglet Analyse, cliquez sur le bouton Installations OLAP, et appuyez sur Convertir en formules.

Riz. 2. Conversion d'un tableau croisé dynamique en un ensemble de fonctions de cube

Les nombres seront enregistrés, et ce ne seront pas des valeurs, mais des formules qui extraient les données du modèle de données Power Pivot (Fig. 3). Vous pouvez formater le tableau résultant. Vous pouvez notamment supprimer et insérer des lignes et des colonnes à l’intérieur du tableau. La tranche reste et affecte les données du tableau. Lorsque les données d'origine sont mises à jour, les chiffres du tableau seront également mis à jour.

Riz. 3. Tableau basé sur des formules cubiques

Fonction CUBEVALUE()

C'est peut-être la fonction principale des cubes. Cela équivaut à la superficie Valeurs tableau croisé dynamique. CUBEVALUE récupère les données d'un cube ou d'un modèle Power Pivot et les affiche en dehors d'un tableau croisé dynamique. Cela signifie que vous n'êtes pas limité par les limites du tableau croisé dynamique et que vous pouvez créer des rapports avec d'innombrables possibilités.

Écrire une formule à partir de zéro

Vous n'êtes pas obligé de convertir le tableau croisé dynamique terminé. Vous pouvez écrire n’importe quelle formule de cube à partir de zéro. Par exemple, la formule suivante est saisie dans la cellule C10 (Fig. 4) :

Riz. 4. La fonction CUBEVALUE() dans la cellule C10 renvoie les ventes de vélos pour toutes les années, comme dans le tableau croisé dynamique

Petite astuce. Pour faciliter la lecture des formules de cube, il est souhaitable qu'un seul argument soit placé sur chaque ligne. Vous pouvez réduire la fenêtre Excel. Pour cela, cliquez sur l'icône Roule dans la fenêtre situé dans le coin supérieur droit de l’écran. Et puis ajustez la taille de la fenêtre horizontalement. Option alternative– force le texte de la formule à passer à la ligne nouvelle ligne. Pour cela, dans la barre de formule, placez le curseur à l'endroit où vous souhaitez effectuer un transfert et appuyez sur Alt + Entrée.

Riz. 5. Réduire la fenêtre

Syntaxe de la fonction CUBEVALUE()

L'aide d'Excel est tout à fait précise et totalement inutile pour les débutants :

CUBEVALUE(connexion, [expression_élément1], [expression_élément2], ...)

Connexion est un argument obligatoire ; une chaîne de texte représentant le nom de la connexion au cube.

expression_élément– argument facultatif; une chaîne de texte représentant un MDX qui renvoie un élément ou un tuple dans le cube. De plus, "element_expression" peut être un ensemble défini à l'aide de la fonction CUBESET. Utilisez "member_expression" comme tranche pour déterminer la partie du cube pour laquelle vous souhaitez renvoyer une valeur agrégée. Si aucune mesure n'est spécifiée dans element_expression, la mesure par défaut de ce cube sera utilisée.

Avant d'expliquer la syntaxe de la fonction CUBEVALUE, quelques mots sur les cubes, les modèles de données et les cryptiques tuple.

Quelques informations sur les cubes OLAP et les modèles de données Power Pivot

Cubes de données OLAP ( Ô n je iné UN analytique P. traitement - analyse des données opérationnelles) ont été développés spécifiquement pour le traitement analytique et l'extraction rapide des données. Imaginez un espace tridimensionnel, où les axes sont les périodes de temps, les villes et les biens (Fig. 5a). Les nœuds d'une telle grille de coordonnées contiennent les valeurs de diverses mesures : volume des ventes, profit, coûts, nombre d'unités vendues, etc. Imaginez maintenant qu'il existe des dizaines, voire des centaines de mesures... et qu'il y en a aussi une beaucoup de mesures. Ce sera le cube OLAP multidimensionnel. Créer, configurer et tenir à jour les cubes OLAP est l'affaire des professionnels de l'informatique.

Riz. 5a. Cube OLAP 3D

Les formules analytiques Excel (formules de cube) extraient les titres des axes (par exemple, Temps), noms des éléments sur ces axes (août, septembre), valeurs des mesures à l'intersection des coordonnées. C'est cette structure qui permet aux tableaux croisés dynamiques et aux formules cubiques basés sur des cubes d'être si flexibles et de s'adapter aux besoins des utilisateurs. Tableaux croisés dynamiques basés Feuilles Excel n’utilisent pas de mesures, elles ne sont donc pas aussi flexibles à des fins d’analyse des données.

Pivot de puissance – Relativement nouvelle fonctionnalité Microsoft. Il s'agit d'un environnement Excel intégré et quelque peu indépendant avec une interface familière. Power Pivot est de loin supérieur aux tableaux croisés dynamiques standard. Dans le même temps, développer des cubes dans Power Pivot est relativement simple et, surtout, il ne nécessite pas la participation d'un informaticien. Microsoft réalise son slogan : "Business Analytics - to the masses !". Bien que les modèles Power Pivot ne soient pas 100 % cubes, ils peuvent également être appelés cubes (voir le cours d'introduction Power Pivot de Mark Moore et l'édition plus longue des formules Power Pivot DAX de Rob Colley pour plus de détails).

Les principaux composants d'un cube sont les dimensions, les hiérarchies, les niveaux, les éléments (ou membres ; en anglais membres) et les mesures (mesures). La mesure - la principale caractéristique des données analysées. Par exemple, catégorie de produit, période, zone géographique des ventes. Une dimension est quelque chose que l'on peut mettre sur l'un des axes du tableau croisé dynamique. Chaque dimension en dehors de des valeurs uniques inclut un élément qui regroupe tous les membres de cette dimension.

Les mesures sont basées sur hiérarchie. Par exemple, une catégorie de produits peut être divisée en sous-catégories, puis en modèles et enfin en noms de produits (Fig. 5b).La hiérarchie permet de créer des données récapitulatives et de les analyser à différents niveaux de structure. Dans notre exemple, la hiérarchie Catégorie comprend 4 niveau.

Éléments(membres individuels) sont présents à tous les niveaux. Par exemple, le niveau Catégorie comporte quatre éléments : Accessoires, Vélos, Vêtements, Composants. Les autres niveaux ont leurs propres éléments.

mesures sont des valeurs calculées, telles que le volume des ventes. Les mesures dans les cubes sont stockées dans leur propre dimension, appelée (voir Figure 9 ci-dessous). Les mesures n'ont pas de hiérarchie. Chaque mesure calcule et stocke une valeur pour toutes les dimensions et tous les membres, ainsi que des tranches en fonction des membres de dimension que nous plaçons sur l'axe. Ils indiquent également quelles coordonnées nous allons définir ou quel contexte de filtre nous allons définir. Par exemple, sur la fig. 5a dans chaque petit cube, la même mesure est calculée - Profit. Et la valeur renvoyée par la mesure dépend des coordonnées. À droite de la figure 5a, il est montré que le bénéfice (en trois coordonnées) pour Moscou en octobre sur les pommes = 63 000 roubles. La mesure peut être interprétée, et comme l'une des mesures. Par exemple, sur la fig. 5a au lieu d'un essieu Marchandises, placer l'axe Mesures avec des éléments Volume des ventes, Profit, Unités vendues. Ensuite, chaque cellule aura une certaine valeur, par exemple Moscou, septembre, volume des ventes.

Tuple- plusieurs articles différentes dimensions, définissant les coordonnées le long des axes du cube, dans lequel on calcule la mesure. Par exemple, sur la fig. 5a Tuple= Moscou, octobre, pommes. Un tuple valide est également Perm, pommes. Un autre concerne les pommes, en août. Les dimensions non incluses dans le tuple sont implicitement présentes dans le tuple et sont représentées par le membre par défaut . Ainsi, une cellule d'un espace multidimensionnel est toujours définie ensemble complet coordonnées, même si certaines d'entre elles sont omises du tuple. Vous ne pouvez pas inclure deux éléments de même dimension dans un tuple, la syntaxe ne le permet pas. Par exemple, le tuple invalide Moscou et Perm, pommes. Pour implémenter une telle expression multidimensionnelle, vous avez besoin d'un ensemble de deux tuples : Moscou et pommes + Perm et pommes.

Ensemble d'éléments– plusieurs éléments de même dimension. Par exemple, les pommes et les poires. Ensemble de tuples- plusieurs tuples dont chacun est constitué des mêmes dimensions dans la même séquence. Par exemple, un ensemble de deux tuples : Moscou, pommes et Perm, bananes.

La saisie semi-automatique à la rescousse

Revenons à la syntaxe de la fonction CUBEVALUE. Utilisons la saisie semi-automatique. Commencez à saisir une formule dans une cellule :

Excel proposera toutes les connexions disponibles dans le classeur Excel :

Riz. 6. La connexion au modèle de données Power Pivot est toujours appelée ThisWorkbookDataModel

Riz. 7. Connexions aux cubes

Continuons à saisir la formule (dans notre cas, pour le modèle de données) :

La saisie semi-automatique suggérera tous les tableaux et mesures de modèles de données disponibles :

Riz. 8. Éléments disponibles du premier niveau - noms de tableaux et un ensemble de mesures (mis en surbrillance)

Choisissez un icône Mesures. Pointer vers:

VALEUR CUBE(» ThisWorkbookDataModel » ; » .

La saisie semi-automatique proposera toutes les mesures disponibles :

Riz. 9. Éléments disponibles du deuxième niveau dans l'ensemble des mesures

Choisissez une mesure. Ajoutez des guillemets, une parenthèse fermante, appuyez sur Entrée.

VALEUR CUBE(" ThisWorkbookDataModel " ; " . ")

Riz. 10. Formule CUBEVALUE dans une cellule Excel

De même, vous pouvez ajouter un troisième argument à la formule :

VBA dans Excel Objet Excel.PivotTable et utilisation de tableaux croisés dynamiques et de cubes OLAP dans Excel

10.8 Travailler avec des tableaux croisés dynamiques (objet PivotTable)

Objet Excel.PivotTable, travaillez par programme avec des tableaux croisés dynamiques et des cubes OLAP dans Excel à l'aide de VBA, objet PivotCache, créez une disposition de tableau croisé dynamique

Au cours du fonctionnement de la plupart des entreprises, les données dites brutes sur les activités sont accumulées. Par exemple, pour une entreprise commerciale, des données sur les ventes de biens peuvent être accumulées - pour chaque achat séparément, pour les entreprises communication cellulaire- charger les statistiques sur stations de base et ainsi de suite. Très souvent, la direction d'une entreprise a besoin d'informations analytiques générées sur la base d'informations brutes - par exemple, pour calculer la contribution de chaque type de produit aux revenus de l'entreprise ou à la qualité du service dans la zone d'une gare donnée. Il est très difficile d’extraire de telles informations à partir d’informations brutes : il faut faire très requêtes SQL complexes, qui prennent beaucoup de temps à réaliser et interfèrent souvent avec le travail en cours. Par conséquent, de plus en plus de données brutes sont actuellement regroupées d'abord dans le Data Warehouse, puis dans des cubes OLAP, qui sont très pratiques pour une analyse interactive. La façon la plus simple de considérer les cubes OLAP est de les considérer comme des tableaux multidimensionnels dans lesquels, au lieu des deux dimensions standard (colonnes et lignes, comme dans les tableaux ordinaires), il peut y avoir de nombreuses dimensions. Le terme « sectionnel » est couramment utilisé pour décrire les dimensions d'un cube. Par exemple, un service marketing peut avoir besoin d'informations par période, par région, par type de produit, par canal de vente, etc. À l'aide de cubes (par opposition aux requêtes SQL standard), il est très facile d'obtenir des réponses à des questions telles que « combien de produits de ce type ont été vendus au quatrième trimestre de l'année dernière dans la région du Nord-Ouest par l'intermédiaire de distributeurs régionaux ».

Bien entendu, vous ne pouvez pas créer de tels cubes dans des bases de données classiques. Les cubes OLAP nécessitent des produits logiciels spécialisés. SQL Server est livré avec une base de données OLAP de Microsoft appelée Analysis Services. Il existe des solutions OLAP d'Oracle, IBM, Sybase, etc.

Pour travailler avec de tels cubes, un client spécial est intégré à Excel. En russe, cela s'appelle tableau croisé dynamique(sur l'écran graphique, il est disponible via le menu Données -> tableau croisé dynamique), et en anglais - tableau croisé dynamique. Par conséquent, l'objet représenté par ce client s'appelle PivotTable. Il convient de noter qu'il peut fonctionner non seulement avec des cubes OLAP, mais également avec des données régulières dans des tableaux ou des bases de données Excel, mais de nombreuses fonctionnalités sont perdues.

Le tableau croisé dynamique et l'objet PivotTable sont des produits logiciels de Panorama Software acquis par Microsoft et intégrés dans Excel. Par conséquent, travailler avec l'objet PivotTable est quelque peu différent de travailler avec d'autres objets Excel. Il est souvent difficile de savoir quoi faire. Par conséquent, il est recommandé d’utiliser activement l’enregistreur de macros pour recevoir des indices. Dans le même temps, lorsqu'ils travaillent avec des tableaux croisés dynamiques, les utilisateurs doivent souvent effectuer les mêmes opérations répétitives, l'automatisation est donc nécessaire dans de nombreuses situations.

À quoi ressemble le travail par programmation avec un tableau croisé dynamique ?

La première chose que nous devons faire est de créer un objet PivotCache qui représentera l'ensemble des enregistrements récupérés de la source OLAP. De manière très conditionnelle, cet objet PivotCache peut être comparé à QueryTable. Un seul objet PivotCache peut être utilisé par objet PivotTable. L'objet PivotCache est créé à l'aide de la méthode Add() de la collection PivotCaches :

Dim PC1 comme PivotCache

Définir PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches est une collection standard, et parmi les méthodes qui méritent un examen détaillé, seule la méthode Add() peut y être nommée. Cette méthode prend deux paramètres :

• Type de Source- obligatoire, définit le type de source de données pour le tableau croisé dynamique. Vous pouvez choisir de créer un tableau croisé dynamique basé sur une plage dans Excel, les données d'une base de données, une source de données externe, un autre tableau croisé dynamique, etc. En pratique, il est généralement judicieux d'utiliser OLAP uniquement lorsqu'il y a beaucoup de données. Par conséquent, vous avez besoin d'un stockage externe(par exemple, Microsoft Analysis Services). Dans cette situation, xlExternal est sélectionné.
• Données source- obligatoire dans tous les cas sauf lorsque la valeur du premier paramètre est xlExternal. À proprement parler, il définit la plage de données sur la base de laquelle le tableau croisé dynamique sera créé. Prend généralement un objet Range.

La tâche suivante consiste à configurer les paramètres de l'objet PivotCache. Comme déjà mentionné, cet objet est très similaire à QueryTable et son ensemble de propriétés et de méthodes est très similaire. Certains des plus propriétés importantes et méthodes :

• ADOConnexion- la possibilité de renvoyer un objet ADO Connection qui est automatiquement créé pour se connecter à une source de données externe. Utilisé pour configurer davantage les propriétés de connexion.
• connexion- fonctionne exactement de la même manière que la propriété d'objet QueryTable du même nom. Il peut accepter une chaîne de connexion, un objet Recordset préparé, un fichier texte, une requête Web. Fichier de requête Microsoft. Le plus souvent, lorsque vous travaillez avec OLAP, une chaîne de connexion est écrite directement (car cela n'a pas de sens de recevoir un objet Recordset, par exemple pour modifier des données - les sources de données OLAP sont presque toujours en lecture seule). Par exemple, la définition de cette propriété pour se connecter à la base de données Foodmart (exemple de base de données Analysis Services) sur le serveur LONDON pourrait ressembler à ceci :

PC1.Connection = "OLEDB;Provider=MSOLAP.2;Source de données=LONDON1;Catalogue initial = FoodMart 2000"

• propriétés Type de commande Et texte de commande décrivez le type de commande envoyée au serveur de base de données et le texte de la commande elle-même de la même manière. Par exemple, pour accéder au cube Sales et le mettre entièrement en cache sur le client, vous pouvez utiliser du code comme

• propriété Connexion locale vous permet de vous connecter à un cube local (fichier *.cub) créé à l'aide d'Excel. Bien entendu, il est fortement déconseillé d'utiliser de tels fichiers pour travailler avec des volumes de données de « production » - uniquement dans le but de créer des mises en page, etc.
• propriété Mémoire utilisée renvoie le numéro mémoire vive, utilisé par PivotCache. Si le tableau croisé dynamique basé sur ce PivotCache n'a pas encore été créé et ouvert, renvoie 0. Peut être utilisé pour vérifier si votre application fonctionnera sur des clients faibles.
• propriété OLAP renvoie True si le PivotCache est connecté au serveur OLAP.
• Optimiser le cache- la possibilité d'optimiser la structure du cache. Le chargement initial des données prendra plus de temps, mais la vitesse de travail pourra ensuite augmenter. Pour les sources OLE DB, cela ne fonctionne pas.

Les propriétés restantes de l'objet PivotCache sont les mêmes que celles de l'objet QueryTable et ne seront donc pas abordées ici.

La méthode principale de l'objet PivotCache est la méthode CreatePivotTable(). À l'aide de cette méthode, l'étape suivante est effectuée : la création d'un tableau croisé dynamique (objet PivotTable). Cette méthode prend quatre paramètres :

• TableDestination est le seul paramètre obligatoire. Accepte un objet Range, dans le coin supérieur gauche duquel sera placé le tableau croisé dynamique.
• nom de la table- Nom du tableau croisé dynamique. S'il n'est pas précisé, alors le nom du formulaire "PivotTable1" sera automatiquement généré.
• lire des données- s'il est défini sur True, alors tout le contenu du cube sera automatiquement mis en cache. Vous devez être très prudent avec ce paramètre, car son utilisation incorrecte peut augmenter considérablement la charge sur le client.
• Version par défaut- Cette propriété n'est généralement pas spécifiée. Vous permet de spécifier la version du tableau croisé dynamique en cours de création. Par défaut, la version la plus récente est utilisée.

La création d'un tableau croisé dynamique dans la première cellule de la première feuille du classeur pourrait ressembler à ceci :

PC1.CreatePivotTable Range("A1")

Le tableau croisé dynamique a été créé, mais immédiatement après sa création, il est vide. Il propose quatre zones dans lesquelles on peut placer les champs de la source (sur l'écran graphique, tout cela peut être configuré soit à l'aide de la fenêtre Liste des champs du tableau croisé dynamique- il s'ouvre automatiquement ou avec un bouton Mise en page sur le dernier écran de l'assistant de tableau croisé dynamique) :

• zone de colonne- il contient les dimensions (« section » dans laquelle les données seront analysées), dont les membres sont moindres ;
• zone de ligne- les dimensions dont les membres sont plus nombreux ;
• zone de page- les mesures par lesquelles il suffit de filtrer (par exemple, pour afficher les données uniquement pour telle ou telle région ou uniquement pour telle ou telle année) ;
• zone de données- en fait, la partie centrale de la table. Ces données numériques (par exemple, le montant des ventes) que nous analysons.

Il est difficile de compter sur l’utilisateur pour placer correctement les éléments dans les quatre zones. De plus, cela peut prendre un certain temps. Par conséquent, il est souvent nécessaire d’organiser les données dans un tableau croisé dynamique par programmation. Cette opération est effectuée à l'aide de l'objet CubeField. La propriété principale de cet objet est l'Orientation, elle détermine où se situera tel ou tel champ. Par exemple, mettons la dimension Clients dans la zone des colonnes :

PT1.CubeFields("").Orientation = xlColumnField

Puis - la dimension Temps à la zone des chaînes :

PT1.CubeFields(""). Orientation = xlRowField

Ensuite - la dimension Produit dans la zone de la page :

PT1.CubeFields(""). Orientation = xlPageField

Et enfin, l'indicateur (données numériques pour analyse) Ventes unitaires :

PT1.CubeFields(".").Orientation = xlDataField

La première interface de tableau croisé dynamique, également appelée rapports croisés dynamiques, a été incluse avec Excel en 1993 ( Versions Excel 5.0). Malgré de nombreux utiles Fonctionnalité, il n'est pratiquement pas utilisé dans le travail de la plupart des utilisateurs d'Excel. Même les utilisateurs expérimentés entendent souvent par le terme « rapport de synthèse » quelque chose construit à l'aide de formules complexes. Essayons de vulgariser l'utilisation des tableaux croisés dynamiques dans le travail quotidien des économistes. Cet article aborde les bases théoriques de la création de rapports de synthèse, donne des recommandations pratiques pour leur utilisation et fournit également un exemple d'accès aux données basées sur plusieurs tableaux.

Conditions d'analyse de données multivariées

La plupart des économistes ont entendu les termes « données multidimensionnelles », « cube virtuel », « technologies OLAP », etc. Mais avec une conversation détaillée, il s'avère généralement que presque tout le monde ne sait pas vraiment quoi Dans la question. Autrement dit, les gens pensent à quelque chose de complexe et généralement sans rapport avec leurs activités quotidiennes. En fait, ce n'est pas le cas.

Données multidimensionnelles, dimensions

On peut affirmer sans se tromper que les économistes traitent presque tout le temps des données multidimensionnelles, mais ils essaient de les présenter d’une manière prédéfinie à l’aide de feuilles de calcul. La multidimensionnalité signifie ici la capacité de saisir, de visualiser ou d'analyser les mêmes informations avec un changement apparence, en appliquant divers regroupements et tris de données. Par exemple, un plan de vente peut être analysé selon les critères suivants :

• types ou groupes de marchandises;
• marques ou catégories de produits ;
• périodes (mois, trimestre, année) ;
• acheteurs ou groupes d'acheteurs ;
• régions de vente
• et ainsi de suite.

Chacun des critères ci-dessus en termes d'analyse de données multivariées est appelé une « dimension ». On peut dire qu'une dimension caractérise des informations sur un certain ensemble de valeurs. Les « données » constituent un type particulier de mesure d'informations multidimensionnelles. Dans notre exemple, les données du plan de vente peuvent être :

• volume des ventes;
• Prix ​​de vente;
• remise individuelle
• et ainsi de suite.

Théoriquement, les données peuvent également constituer une dimension standard d’informations multidimensionnelles (par exemple, vous pouvez regrouper les données par prix de vente), mais les données constituent généralement un type particulier de valeur.

Ainsi, on peut dire que dans les travaux pratiques, les économistes utilisent deux types d'informations : données multidimensionnelles ( numéros réels et prévus avec de nombreuses fonctionnalités) et des répertoires (caractéristiques ou mesures de données).

OLAP

L'abréviation OLAP (traitement analytique en ligne) en traduction littérale ressemble à " traitement analytique en temps réel". La définition n’est pas très précise ; presque tous les rapports sur n’importe quel produit logiciel peuvent y être résumés. Par définition, OLAP désigne une technologie permettant de travailler avec des rapports spéciaux, y compris des logiciels, pour obtenir et analyser des données structurées multidimensionnelles. L'un des produits logiciels populaires implémentant les technologies OLAP est SQL Server Analysis Server. Certains le considèrent même à tort comme le seul représentant de la mise en œuvre logicielle de ce concept.

Cube de données virtuel

« Cube virtuel » (cube multidimensionnel, cube OLAP) est un terme technique inventé par certains fournisseurs de logiciels spécialisés. logiciel. Les systèmes OLAP préparent et stockent généralement les données dans leurs propres structures, et des interfaces d'analyse spéciales (telles que les rapports de synthèse Excel) accèdent aux données de ces cubes virtuels. Dans le même temps, l'utilisation d'un tel stockage dédié n'est pas du tout nécessaire pour traiter des informations multidimensionnelles. En général, cube virtuel- il s'agit d'un ensemble de données multidimensionnelles spécialement optimisées qui sont utilisées pour créer des rapports de synthèse. Il peut être obtenu à la fois via des outils logiciels spécialisés et via un simple accès aux tables de bases de données ou à toute autre source, comme une feuille de calcul Excel.

tableau croisé dynamique

« Pivot Report » (tableau croisé dynamique, tableau croisé dynamique) est une interface utilisateur permettant d'afficher des données multidimensionnelles. Grâce à cette interface, vous pouvez regrouper, trier, filtrer et modifier l'emplacement des données afin d'obtenir divers échantillons analytiques. Le rapport est mis à jour à l'aide d'outils d'interface utilisateur simples, les données sont automatiquement agrégées selon les règles spécifiées et aucune information supplémentaire ou nouvelle saisie n'est requise. L'interface Excel PivotTable est peut-être la plus populaire produit logiciel pour travailler avec des données multidimensionnelles. Il prend en charge à la fois les sources de données externes (cubes OLAP et bases de données relationnelles) et les plages de feuilles de calcul internes en tant que source de données. À partir de la version 2000 (9.0), Excel prend également en charge forme graphique affichage de données multidimensionnelles - Graphique croisé dynamique.

L'interface de tableau croisé dynamique implémentée dans Excel vous permet d'organiser les dimensions des données multidimensionnelles dans la zone de la feuille de calcul. Pour plus de simplicité, vous pouvez considérer un tableau croisé dynamique comme un rapport situé au-dessus d'une plage de cellules (en fait, il existe une certaine liaison des formats de cellules aux champs du tableau croisé dynamique). Un tableau croisé dynamique Excel comporte quatre zones d'affichage : filtre, colonnes, lignes et données. Les dimensions des données sont nommées champs du tableau croisé dynamique. Ces champs ont leurs propres propriétés et format d'affichage.

Encore une fois, je voudrais attirer votre attention sur le fait que le tableau croisé dynamique Excel est destiné uniquement à l'analyse de données sans possibilité de modifier les informations. L'utilisation généralisée du terme « rapport pivot » (Pivot Report) serait plus proche, et c'est ainsi que cette interface était appelée jusqu'en 2000. Mais pour une raison quelconque, dans les versions ultérieures, les développeurs l'ont abandonné.

Modification des tableaux croisés dynamiques

De par sa définition, la technologie OLAP n'implique en principe pas la possibilité de modifier les données source lorsque l'on travaille avec des rapports. Cependant, toute une classe de systèmes logiciels, réalisant les possibilités à la fois d'analyse et d'édition directe des données dans des tableaux multidimensionnels. Fondamentalement, ces systèmes visent à résoudre les problèmes de budgétisation.

Grâce aux outils d'automatisation intégrés d'Excel, vous pouvez résoudre de nombreuses tâches non standard. Un exemple de mise en œuvre de l'édition de tableaux croisés dynamiques Excel basés sur les données d'une feuille de calcul est disponible sur notre site Web.

Préparation de données multidimensionnelles

Abordons l'application pratique des tableaux croisés dynamiques. Essayons d'analyser les données de ventes dans différentes directions. Déposer exemple de tableau croisé dynamique.xls se compose de plusieurs feuilles. Feuille Exemple contient des informations de base sur les ventes pour une certaine période. Pour simplifier l'exemple, nous analyserons le seul indicateur numérique - le volume des ventes en kg. Les dimensions de données clés suivantes sont disponibles : produit, acheteur et transporteur ( compagnie de transport). De plus, il existe plusieurs dimensions supplémentaires de données qui sont des attributs du produit : type, marque, catégorie, fournisseur, ainsi que l'acheteur : type. Ces données sont collectées sur la feuille Annuaires. En pratique, il peut y avoir beaucoup plus de mesures de ce type.

Feuille Exemple contient remède standard analyse des données - filtre automatique. En regardant l'exemple de remplissage du tableau, il est évident que les données de ventes par dates (elles sont disposées en colonnes) se prêtent à une analyse normale. De plus, à l'aide du filtre automatique, vous pouvez essayer de résumer les données par des combinaisons d'un ou plusieurs critères clés. Il n'y a absolument aucune information sur les marques, les catégories et les types. Il n'est pas possible de regrouper les données avec synthèse automatique par une clé spécifique (par exemple, par clients). De plus, l'ensemble des dates est fixe et affiche les informations récapitulatives pour une certaine période, par exemple 3 jours, par des moyens automatiqueséchouer.

En général, avoir un lieu de date prédéfini dans cet exemple- le principal inconvénient de la table. En disposant les dates par colonnes, on a en quelque sorte prédéterminé la dimension de ce tableau, se privant ainsi de la possibilité d'utiliser l'analyse par tableaux croisés dynamiques.

Premièrement, nous devons nous débarrasser de cette lacune - c'est-à-dire supprimer l'emplacement prédéfini d'une des dimensions des données source. Un exemple de table valide est une feuille Ventes.

Le tableau se présente sous la forme d'un journal de saisie d'informations. Ici, la date est une dimension de données égale. Il convient également de noter que pour les analyses ultérieures dans les tableaux croisés dynamiques, la position relative des lignes les unes par rapport aux autres (c'est-à-dire le tri) est totalement indifférente. Les enregistrements dans les bases de données relationnelles ont ces propriétés. C'est l'analyse de gros volumes de bases de données qui se concentre principalement sur l'interface des tableaux croisés dynamiques. Par conséquent, vous devez respecter ces règles lorsque vous travaillez avec une source de données sous forme de plages de cellules. Dans le même temps, personne n'interdit d'utiliser les outils de l'interface Excel dans votre travail - les tableaux croisés dynamiques analysent uniquement les données et le formatage, les filtres, le regroupement et le tri des cellules source peuvent être arbitraires.

Du filtre automatique au rapport récapitulatif

Théoriquement, sur les données de la fiche Vente, il est déjà possible d'analyser en trois dimensions : les marchandises, les acheteurs et les transporteurs. Il n'y a pas de données sur les propriétés des produits et des acheteurs sur cette fiche, ce qui ne permettra donc pas de les présenter dans le tableau récapitulatif. Dans le mode normal de création d'un tableau croisé dynamique pour l'original données Excel ne vous permet pas de lier les données de plusieurs tables sur certains champs. Vous pouvez contourner cette limitation outils logiciels- voir un exemple d'ajout à cet article sur notre site Internet. Afin de ne pas avoir recours à méthodes logicielles traitement de l'information (d'autant plus qu'ils ne sont pas universels), il faut ajouter caractéristiques supplémentaires directement au formulaire d'écriture de journal - voir la feuille SalesAnalysis.

L'utilisation des fonctions RECHERCHEV permet de compléter facilement les données originales avec les caractéristiques manquantes. Désormais, en appliquant le filtre automatique, vous pouvez analyser les données dans différentes dimensions. Mais le problème des regroupements reste entier. Par exemple, suivre le montant uniquement pour les marques à certaines dates est assez problématique. Si limité Formules Excel, vous devez alors créer des échantillons supplémentaires à l'aide de la fonction SUMIF.

Voyons maintenant quelles fonctionnalités offre l'interface du tableau croisé dynamique. Sur la feuille Analyse de code construit plusieurs rapports basés sur une plage de cellules avec des données de feuille Analyse des ventes.

Le premier tableau d'analyse est construit via l'interface Excel 2007 Ruban \ Insérer \ Tableau croisé dynamique(dans le menu Excel 2000-2003 Données\Tableau croisé dynamique).

Les deuxième et troisième tables sont créées par copie et personnalisation ultérieure. La source de données pour toutes les tables est la même. Vous pouvez vérifier cela en modifiant les données d'origine, puis vous devez mettre à jour les données des rapports récapitulatifs.

De notre point de vue, les avantages en matière de visibilité de l'information sont évidents. Vous pouvez échanger des filtres, des colonnes et des lignes, masquer certains groupes de valeurs de n'importe quelle dimension, utiliser le glisser-déposer manuel et le tri automatique.

Propriétés et formatage

En plus d'afficher directement les données, il existe un large éventail d'options pour afficher l'apparence des tableaux croisés dynamiques. Les données supplémentaires peuvent être masquées à l'aide de filtres. Pour un seul élément ou champ, il est plus facile d'utiliser l'élément de menu contextuel Supprimer(dans la version 2000-2003 Cacher).

Il est également souhaitable de définir l'affichage des autres éléments du tableau croisé dynamique non pas via le formatage des cellules, mais en définissant le champ ou l'élément du tableau croisé dynamique. Pour cela, déplacez le pointeur de la souris sur l'élément souhaité, attendez l'apparition d'une forme de curseur particulière (en forme de flèche), puis sélectionnez l'élément sélectionné d'un simple clic. Après la sélection, vous pouvez modifier la vue via le ruban, le menu contextuel ou appeler la boîte de dialogue de format de cellule standard :

De plus, Excel 2007 a introduit de nombreux styles d'affichage de tableau croisé dynamique prédéfinis :

Notez que les filtres de contrôle et les zones de déplacement sont actifs dans le graphique.

Accès aux données externes

Comme nous l'avons déjà noté, l'effet le plus important de l'utilisation de tableaux croisés dynamiques peut peut-être être obtenu lors de l'accès aux données. sources externes– Cubes OLAP et requêtes de base de données. De telles sources stockent généralement de grandes quantités d'informations et possèdent également une structure relationnelle prédéfinie, ce qui facilite la définition des dimensions des données multidimensionnelles (champs de tableau croisé dynamique).

Excel prend en charge de nombreux types de sources de données externes :

Le plus grand effet de l'utilisation de sources d'informations externes peut être obtenu en utilisant des outils d'automatisation ( Programmes VBA) tant pour l'acquisition des données que pour leur prétraitement dans des tableaux croisés dynamiques.