Lorsque vous analysez des données à l'aide de tableaux croisés dynamiques, vous devez souvent inclure dans votre rapport des valeurs résultant de calculs effectués en dehors du jeu de données d'origine. Programme Excel fournit à l'utilisateur les moyens d'effectuer des calculs dans des tableaux croisés dynamiques à l'aide de champs calculés et d'éléments calculés. Sur la fig. La figure 1 montre comment un champ calculé peut présenter les données d'une manière différente. Le tableau croisé dynamique affiche le volume des ventes et le temps (en heures) consacré à la vente d'équipements sur chaque marché. Un champ calculé qui calcule le revenu moyen par heure améliore la capacité d'analyser les données dans un tableau croisé dynamique.

Riz. 1. Vous devez créer un champ calculé dans un tableau croisé dynamique existant Revenu moyen par heure, ce qui donnera un nouveau sens aux données analysées

Télécharger note au format ou, exemples au format

Un champ calculé est un champ de données créé à la suite de calculs basés sur des champs existants dans un tableau croisé dynamique. Le champ calculé est ajouté au jeu de données en tant que colonne virtuelle. Cette colonne n'est pas incluse dans les données source, contient des valeurs dérivées de formules et interagit avec les données de tableau croisé dynamique de la même manière que les autres champs de tableau croisé dynamique.

Avec les champs calculés, vous pouvez insérer une formule dans un tableau croisé dynamique qui vous permet de créer votre propre champ. Les données nouvellement créées feront partie du tableau croisé dynamique, interagissant avec les données existantes. Lorsque les tableaux croisés dynamiques sont actualisés, les champs calculés sont recalculés et le tableau croisé dynamique lui-même est rempli avec des informations qui ne figuraient pas dans le jeu de données d'origine.

Regardez maintenant la fig. 5.1 et demandez-vous : « Pourquoi avez-vous eu besoin d'ajouter des champs calculés ? Pourquoi ne pas utiliser des formules de cellule normales ou effectuer les calculs nécessaires directement sur la feuille de calcul d'origine pour obtenir les informations dont vous avez besoin ? » Pour répondre à ces questions, considérons diverses méthodes, qui peut être utilisé lors de la création du champ calculé illustré à la figure 1.

Méthode 1 : Ajouter un champ calculé à la source de données

Vous pouvez ajouter un champ calculé à votre source de données, comme illustré à la figure 1. 2, permettant au tableau croisé dynamique d'utiliser ce champ comme champ de données standard. Si la disposition des informations sur la feuille avec la source de données le permet, alors quelle pourrait être la meilleure solution. Soit dit en passant, l'ajout d'une nouvelle colonne nécessitera non seulement la mise à jour du tableau croisé dynamique, mais également la modification de la zone sur laquelle le tableau croisé dynamique est basé. Pour cela, cliquez sur le résumé et passez par le menu Analyse → Source de données → Source de données.

J'essaie de transformer les données d'origine en un tableau (ce qui est fait dans la Fig. 2 et dans le fichier Excel joint). Dans le même temps, premièrement, les en-têtes sont mis en évidence, deuxièmement, des boutons avec des filtres apparaissent, ce qui est souvent utile pour l'exploration des données, troisièmement, les lignes sont colorées d'une ligne à l'autre et, enfin, le plus important, lors de l'ajout de lignes et de colonnes, ce n'est pas nécessaire de modifier la zone de données du tableau croisé dynamique. En tant que telle, la zone n'est pas une plage rectangulaire, mais le nom de la table. Dans notre cas - La source(Fig. 3).

Riz. 3. Formation d'un résumé basé sur un outil Excel spécial - Tableau

Mais cette méthode a aussi des inconvénients : premièrement, il n'est pas toujours pratique d'ajouter une colonne aux données source, et deuxièmement, les possibilités sont limitées si la structure des données source change (par exemple, vous exportez des données depuis 1C, et en la nouvelle exportation, il y a une colonne de plus ; cette nouvelle colonne écrasera votre colonne de calcul).

Méthode 2 : utiliser une formule en dehors d'un tableau croisé dynamique pour créer un champ calculé

Vous pouvez ajouter un champ calculé à côté d'un tableau croisé dynamique. Sur la fig. 4 chaque cellule dans une colonne Revenu moyen par heure fourni avec une formule qui fait référence à un tableau croisé dynamique. D'ailleurs, si lorsque vous essayez de vous référer à une cellule d'un tableau croisé dynamique, la fonction GET. DATA. PIVOT. TABLE "monte" dans votre formule, alors vous ne pourrez pas "glisser" la formule. Pour surmonter cette difficulté, voir la note.

Riz. 4. Lorsque vous entrez une formule après avoir créé un tableau croisé dynamique, vous ajoutez essentiellement un champ calculé qui change lorsque vous mettez à jour le tableau croisé dynamique lui-même

Bien que cette méthode ajoute un champ calculé qui est mis à jour lorsque le tableau croisé dynamique est mis à jour, toute modification apportée à la structure du tableau croisé dynamique peut rendre la formule inutile. Par exemple, en faisant glisser le champ Marché des ventes vers la zone FILTRES de la liste des champs du tableau croisé dynamique, la structure du rapport change, à la suite de quoi un message d'erreur apparaît dans le champ calculé (Fig. 5). Si vous ajoutez un champ supplémentaire aux COLONNES du tableau croisé dynamique, la colonne Revenu moyen par heure sera écrasé.

Méthode 3. Insertion directe d'un champ calculé dans un tableau croisé dynamique

L'insertion d'un champ calculé directement dans un tableau croisé dynamique est souvent la meilleure solution. Si vous vous référez à cette méthode, vous n'avez pas besoin d'ajuster les formules. Il sera également possible d'ajuster automatiquement le tableau en fonction de la source de données modifiée. Le degré de flexibilité requis sera atteint pour s'assurer que les champs sont automatiquement recalculés si les unités de mesure sont modifiées.

Un autre avantage de cette méthode est que si vous l'utilisez, vous pouvez modifier la structure du tableau croisé dynamique et même les champs de données pour différentes unités de mesure utilisées dans les champs calculés. En même temps, vous pouvez être sûr qu'il n'y aura pas d'erreurs dans les formules et que les références de cellule ne seront pas perdues.

Le rapport de tableau croisé dynamique illustré à la fig. 6 est un tableau croisé dynamique tel qu'illustré à la Fig. 1, sauf qu'il est rétro-conçu pour produire le revenu moyen par heure pour chaque service et chaque marché.

Riz. 6. Le champ calculé précédemment créé reste "valide" même si vous modifiez la structure du tableau croisé dynamique, ce qui entraîne l'affichage du revenu moyen par heure pour chaque service et chaque zone de vente

Créer un champ calculé

Avant de pouvoir créer un champ calculé, vous devez ouvrir ou générer un tableau croisé dynamique. Dans notre exemple, nous utilisons le tableau croisé dynamique illustré à la Fig. 1 mais sans la colonne D. Pour créer un champ calculé, activez la boîte de dialogue Insérer un champ calculé. Pour cela, cliquez sur le tableau croisé dynamique pour activer le groupe d'onglets contextuels Travailler avec des tableaux croisés dynamiques. Allez dans l'onglet Une analyse, au groupe L'informatique, cliquez sur le bouton Champs, éléments et ensembles et sélectionnez la commande Champ calculé dans le menu (Fig. 7).

Une boîte de dialogue apparaîtra à l'écran. Insérer un champ calculé(Fig. 8). Il y a deux zones de texte en haut de la boîte de dialogue : Nom et Formule. Dans ces champs, vous devez spécifier le nom du champ calculé et créer une formule, en spécifiant les champs de données requis et les opérateurs mathématiques. Champ calculé doté d'un nom descriptif Revenu moyen par heure. Ce nom doit être choisi de manière à caractériser avec précision le type de opération mathématique. Zone de texte par défaut Formule boite de dialogue Insérer un champ calculé contient expression = 0. Zéro doit être supprimé avant d'entrer la formule. Sélectionnez l'un des champs de la zone des champs; dans notre cas - Volume des ventes et cliquez Ajouter le champ. Le nom du champ apparaîtra dans la ligne Formule. Entrez le signe de division / puis sélectionnez le deuxième champ − Période de vente (en heures). Cliquez sur Ajouter, et alors D'ACCORD pour activer le nouveau champ calculé.

Un nouveau champ calculé apparaîtra dans le tableau croisé dynamique Revenu moyen par heure(voir figure 1). Un nouveau champ calculé vient d'être ajouté avec succès au tableau croisé dynamique. Vous pouvez maintenant modifier les paramètres de ce nouveau champ de la même manière que vous modifiez les paramètres de tout autre champ (par exemple, le nom du champ, le format numérique ou la couleur).

Cela signifie-t-il que vous avez ajouté une colonne à la source de données ? Non. Les champs calculés sont comme des éléments Total et montant total tableau croisé dynamique, défini par défaut, car ils sont tous fonctions mathématiques, qui effectuent un recalcul des données lorsque le tableau croisé dynamique est modifié ou mis à jour. Les champs calculés imitent simplement les champs explicites de la source de données. Vous pouvez les faire glisser et les déposer, modifier les paramètres de champ et les utiliser avec d'autres champs calculés.

Veuillez noter que dans la fig. 8 le format de la formule que vous avez saisi est similaire à celui utilisé dans la chaîne standard Formules Excel. La principale différence est qu'au lieu d'utiliser des références de cellule strictes ou des valeurs numériques, vous vous référez aux champs de données du tableau croisé dynamique pour attribuer des valeurs aux arguments utilisés dans les calculs.

Créer des membres calculés

Un élément calculé est essentiellement une ligne virtuelle de données qui est ajoutée suite à l'exécution de calculs sur d'autres lignes dans le même champ. Très souvent, un résultat similaire à celui obtenu lors de la création d'un membre calculé peut être obtenu en regroupant les données. En effet, dans de nombreux cas, le regroupement est une excellente alternative aux membres calculés.

Dans le tableau croisé dynamique illustré à la Fig. 9, le volume total des ventes pour certaines périodes de déclaration est indiqué (champ Période de déclaration). Imaginez que vous vouliez comparer les ventes moyennes des six dernières périodes avec les ventes moyennes des sept périodes précédentes. Pour être plus précis, il faut déterminer la moyenne des périodes P01-P07 et la comparer avec la moyenne des périodes P08-P13.

Placez le pointeur dans n'importe quelle cellule du champ période de déclaration, passer à l'onglet contextuel du ruban Une analyse au groupe L'informatique, et cliquez sur le bouton Champs, éléments et ensembles. Dans le menu qui s'ouvre, sélectionnez la commande Objet calculé(Fig. 10).

Une boîte de dialogue s'ouvrira (Fig. 11). Notez qu'en haut de la boîte de dialogue, il est indiqué avec quel champ vous travaillez. Dans ce cas, ce champ Période de déclaration. De plus, la liste contient tous les éléments du champ Période de déclaration. Donnez un nom au membre calculé, puis créez une formule avec la combinaison correcte de membres de données et d'opérateurs qui produira le résultat correct. La formule est saisie dans le champ Formule, et les éléments de données nécessaires qui y sont utilisés sont sélectionnés dans la liste Éléments. Cliquez sur le bouton D'ACCORD, et l'élément calculé sera ajouté au tableau croisé dynamique (Fig. 12).

Riz. 11. Boîte de dialogue Insertion d'un membre calculé

De même, créez un membre calculé qui représentera les ventes moyennes pour les périodes P08-P13. Puis masquez l'individu périodes de déclaration, ne laissant que deux membres calculés. Avec un peu de formatage, nos membres calculés seront utilisés pour comparer les ventes moyennes des six mois précédents et suivants (Figure 13).

Si vous ne masquez pas les éléments de données utilisés dans le calcul des éléments calculés, les sous-totaux et les totaux généraux peuvent être incorrects.

Avantages et inconvénients des calculs dans les tableaux croisés dynamiques

L'utilisation de champs et d'éléments calculés présente certains inconvénients. Il est important de comprendre ce qui se passe lorsque vous calculez des données dans des tableaux croisés dynamiques, mais il est plus important d'être conscient des limites des champs calculés et des éléments calculés pour éviter les erreurs lorsque vous analysez vos données.

Priorité d'exécution des opérations. Comme dans les feuilles de calcul, vous pouvez utiliser n'importe quel opérateur dans les formules pour les éléments et les champs calculés : +, -, *, /, %, ^, etc. De plus, tout comme dans les feuilles de calcul, les calculs des tableaux croisés dynamiques sont effectués en fonction de la priorité des opérateurs. Lorsque vous effectuez un calcul qui combine plusieurs opérateurs, tels que (2+3) *4/50 %, Excel évalue l'expression et l'évalue dans l'ordre spécifié. Connaître cet ordre vous évitera de nombreuses erreurs.

Ainsi, l'ordre des opérations dans Excel est le suivant :

• traitement des expressions entre parenthèses ;
• gestion de plage (:);
• traitement des intersections (zones);
• syndicats de transformation (;);
• effectuer une opération de négation ;
• conversion en pourcentage (par exemple, 50 % est converti en 0,50) ;
• exponentiation (^);
• Multiplication et division (/); ces opérations ont la même priorité ;
• addition (+) et soustraction (-); ces opérations ont la même priorité ;
• concaténation de données textuelles (&) cette opération est également appelée concaténation ;
• effectuer des opérations de comparaison (=,<>, <=, >=).

Les opérateurs équivalents dans la même expression sont toujours exécutés dans un ordre séquentiel (de gauche à droite).

Prenons un exemple simple. Comme vous le savez, l'expression (2+3)*4 renvoie le résultat 20. Si vous supprimez les crochets et laissez l'expression 2+3*4, alors Excel calculera : 3*4 = 12 + 2 = 14.

Prenons un autre exemple. Si vous saisissez l'expression 10^2 sous forme de formule, c'est-à-dire vous spécifiez d'élever au carré le nombre 10, le programme renverra comme réponse la valeur 100. Si vous entrez l'expression -10^2, vous pouvez vous attendre à la valeur de retour -100. Cependant, Excel renverra à nouveau la valeur 100. La raison en est que le programme effectue une opération de négation avant l'exponentiation, c'est-à-dire la valeur 10 est d'abord convertie en -10, et le résultat de l'expression -10*-10 est en fait 100. L'utilisation de parenthèses dans la formule -(10^2) garantit que le programme effectue d'abord l'exponentiation et ensuite seulement la négation opération, renvoyant le résultat attendu - 100.

Références de cellules et plages nommées. Les références de cellule ou les plages nommées ne peuvent pas être utilisées lors de la création de champs et d'éléments calculés, car lorsque vous créez des objets calculés dans un tableau croisé dynamique, vous travaillez essentiellement en dehors de l'espace objet. Les seules données disponibles pour les utilisateurs se trouvent dans le cache du tableau croisé dynamique. Étant donné que vous ne pouvez pas sortir du cache, vous ne pouvez pas faire référence à des cellules ou à des plages nommées dans la formule générée.

Fonctions de feuille de calcul. Vous pouvez utiliser n'importe quelle fonction de feuille de calcul qui n'utilise pas de références de cellule ou d'objets nommés comme arguments. En fait, vous pouvez utiliser n'importe quelle fonction de feuille de calcul qui ne nécessite pas l'utilisation de références de cellule ou d'objets nommés. Les fonctions telles que COUNT, AVERAGE, IF, AND, NO et OR entrent dans cette catégorie.

Constantes. Vous pouvez utiliser n'importe quelle constante dans les calculs effectués dans les tableaux croisés dynamiques. Les constantes sont des valeurs statiques qui ne changent pas dans le temps. Par exemple, dans la formule suivante, le nombre 5 est une constante : [Unités vendues] *5. Même si la valeur Unités vendues peut varier en fonction des données disponibles, le chiffre 5 sera toujours le même.

Liens vers les totaux. Les formules qui effectuent des calculs ne peuvent pas référencer des sous-totaux ou des totaux de tableau croisé dynamique. En d'autres termes, vous ne pouvez pas utiliser le résultat d'un calcul de total cumulé ou général comme variable ou argument dans un champ calculé.

Règles spéciales pour les champs calculés. Les calculs dans les champs calculés sont toujours effectués sur des totaux et non sur des éléments de données individuels. En termes simples, Excel calcule toujours les champs de données, les sous-totaux et les totaux généraux avant d'évaluer un champ calculé. Cela signifie que votre champ calculé est toujours appliqué aux totaux des données d'origine. L'exemple illustré à la fig. 14 montre comment cela peut affecter l'analyse des données.

Riz. 14. Bien que le champ calculé soit valable pour des éléments de données individuels, il est mathématiquement incorrect de l'utiliser pour des sous-totaux

Pour chaque trimestre, vous devez obtenir le total des ventes de chaque produit en multipliant le nombre d'unités vendues par le prix unitaire de ce produit. Si vous regardez d'abord les données du premier trimestre, vous identifierez immédiatement le problème. Au lieu de calculer la somme de 220+150+220+594, ce qui équivaut à 1184, la somme du nombre d'articles est multipliée par la somme des prix des articles, ce qui entraîne le renvoi d'une valeur incorrecte. Malheureusement, ce problème ne peut pas être résolu, mais il peut être contourné. Excluez les sous-totaux et les totaux du tableau croisé dynamique, puis calculez un nouveau total général en bas à l'extérieur du tableau croisé dynamique.

Règles spéciales pour l'utilisation de membres calculés. Vous ne pouvez pas utiliser de membres calculés dans un tableau croisé dynamique qui calculent des moyennes, des écarts types ou des variances. Inversement, vous ne pouvez pas utiliser de moyennes, d'écarts types ou de variances dans un tableau croisé dynamique contenant un membre calculé. Vous ne pourrez pas utiliser un champ de page pour créer un élément calculé, et vous ne pourrez pas déplacer des éléments calculés vers la zone de filtre d'un rapport. Vous ne pouvez pas ajouter un élément calculé à un rapport qui contient un champ groupé, et vous ne pouvez pas regrouper un champ dans un tableau croisé dynamique qui contient un élément calculé. Lors de la création d'une formule de membre calculé, vous ne pouvez pas faire référence au membre à partir d'un champ externe.

Gestion et prise en charge des calculs dans les tableaux croisés dynamiques

Lorsque vous travaillez avec des tableaux croisés dynamiques, des situations surviennent souvent lorsqu'il n'est pas logique de conserver le tableau croisé dynamique plus longtemps que nécessaire pour copier des valeurs individuelles. Cependant, il existe souvent des situations où il est plus avantageux de conserver le tableau croisé dynamique et tous ses éléments inchangés. Si vous maintenez et gérez un tableau croisé dynamique en modifiant les exigences et en augmentant la quantité de données source, il est alors nécessaire de gérer les champs calculés et les membres calculés.

Modifier et supprimer des calculs dans les tableaux croisés dynamiques. Si vous modifiez les options de calcul ou si vous n'avez pas besoin d'un champ calculé ou d'un élément calculé, vous pouvez ouvrir la boîte de dialogue appropriée pour modifier ou supprimer le calcul. Activer la boîte de dialogue Insérer un champ calculé ou Insertion d'un membre calculé(voir la description des Fig. 7 et 10) et sélectionner la liste déroulante Nom(Fig. 15).

Modifiez l'ordre dans lequel les calculs sont effectués dans les champs calculés. Si la valeur d'une cellule dans un tableau croisé dynamique dépend du résultat du calcul de plusieurs éléments calculés, vous pouvez modifier l'ordre dans lequel les opérations sont effectuées dans les champs calculés. En d'autres termes, vous pouvez spécifier l'ordre dans lequel les calculs individuels seront effectués. Pour ouvrir une boîte de dialogue , déplacez le pointeur n'importe où dans le tableau croisé dynamique, puis cliquez sur l'icône Champs, éléments et ensembles. Dans le menu déroulant, sélectionnez la commande Ordre de calcul (Fig. 16).

Riz. 16. Ouvrez la boîte de dialogue Ordre de calcul

Dans la boîte de dialogue Ordre de calcul(Fig. 17), tous les éléments calculés qui sont ajoutés au tableau croisé dynamique sont affichés. Sélectionnez n'importe quel élément calculé dans la liste et utilisez les boutons En haut, Descente et Effacer. L'ordre des formules dans la liste détermine commande exacte effectuer des calculs dans un tableau croisé dynamique.

Documents de formule. Excel vous fournit un excellent outil qui vous montre les champs calculés et les éléments calculés utilisés dans un tableau croisé dynamique, ainsi que l'ordre dans lequel les calculs et les formules sont appliqués. Cet outil est très utile pour analyser un tableau croisé dynamique tiers ou lorsque vous devez déterminer rapidement quels calculs y sont appliqués et quels champs et éléments ils affectent. Pour créer un rapport de calcul de tableau croisé dynamique, survolez n'importe où dans le tableau croisé dynamique, cliquez sur l'icône Champs, éléments et ensembles et sélectionnez la commande dans le menu déroulant Dériver des formules. Excel créera un rapport sur les champs et éléments calculés sur une feuille séparée (Fig. 18).

Riz. 18. Équipe Dériver des formules permet de documenter facilement et rapidement les calculs disponibles dans le tableau croisé dynamique

La note est écrite sur la base du livre de Jelen, Alexander. . Chapitre 5

Le mot Tableau est écrit avec une majuscule, car il ne s'agit pas simplement d'un tableau, mais d'un outil Excel distinct.

La première façon de créer un champ calculé est basée sur le fait que le formulaire est basé sur une requête qui inclut des colonnes calculées. Un champ de texte est créé dans le formulaire avec une colonne de requête calculée comme source de données. Le champ est verrouillé afin que l'utilisateur ne puisse pas modifier les données qu'il contient. Les données sont calculées lors de l'exécution de la requête. Pour verrouiller un champ en mode Constructeur dans la fenêtre Propriétés champ calculé de l'onglet Données dans la ligne blocage Entrer OUI.

Dans le second cas, le formulaire est construit à partir du tableau. Un nouveau champ est créé dans le formulaire qui spécifie une expression comme source de données, par exemple, = [Prix unitaire]*Quantité. Lorsque vous faites référence à des champs dans des expressions, des crochets doivent être utilisés si le nom du champ se compose de plusieurs mots. La saisie d'une expression est autorisée directement dans l'élément de contrôle (champ) si elle est courte et que la longueur du champ le permet. Sinon, l'expression est saisie dans la cellule de propriété Données la fenêtre Propriétés. Dans ce cas, vous pouvez utiliser le générateur d'expression. Si l'expression ne rentre pas dans la cellule de propriété, vous devez appuyer sur les touches + et ouvrir la fenêtre Zone de saisie. Si nécessaire, définissez le format du champ lors de son affichage à l'écran.

6. Création d'un formulaire de bouton

Sélectionnez une commande de menu Outils/Utilitaires/Gestionnaire de formulaires de boutons.

Dans la boîte de dialogue vous demandant si vous souhaitez créer un formulaire de bouton, cliquez sur OUI.

Une fenêtre apparaîtra à l'écran. Gestionnaire de formulaire de bouton. Cette fenêtre contient une liste de boutons de pages. Au début du travail dans la fenêtre une page - Formulaire de bouton principal. Dans la fenêtre cliquez Changer.

Une boîte de dialogue s'ouvre Modifier la page du formulaire de bouton. Corrigez le nom du formulaire.

Cliquez sur le bouton Créer. Une fenêtre apparaît Modification d'un élément de formulaire de bouton.

Dans le champ Texte entrez le nom du bouton à créer. Le nom du bouton doit être associé par l'utilisateur aux objets avec lesquels il compte travailler. Deuxième champ Équipe permet de définir l'action qui sera effectuée après avoir appuyé sur ce bouton. Choisissez dans la liste déroulante la commande requise pour votre application.

Ajoutez des boutons pour tous les formulaires et rapports, en les structurant par type.

Ajouter un bouton qui sortira de l'application, ainsi qu'un bouton pour accès rapideà Gestionnaire de formulaire de bouton.

Configurez l'application pour Formulaire de bouton principal ouvert lors de l'ouverture de la base de données. Pour ce faire, utilisez la commande de menu Outils/Options de lancement.

EXEMPLE:

Riz. 28. Formulaire de bouton de niveau supérieur

Lab 3 Création de requêtes

Objectif - acquérir des compétences pratiques dans le développement de requêtes de base de données.

Exercer:

Effectuez des tests de schéma de base de données sur des requêtes typiques. Vous devez créer au moins une requête de chaque type décrit ci-dessous. Dans le rapport, les requêtes doivent être présentées dans une formulation en langage naturel, en SQL, et un exemple du résultat de l'exécution de la requête doit également être donné.

Séquence de travail

1.Création d'une requête de sélection de table unique à l'aide du générateur de requêtes

À fenêtre de la base de données cliquez sur le signet Demandes.

Cliquez sur le bouton Créer.

Dans la fenêtre Nouvelle requête sélectionner Constructeur et appuyez sur le bouton D'ACCORD.

Dans la fenêtre Ajout d'un tableau sélectionnez la table (ou la requête) dans la liste déroulante, sur laquelle la requête sera construite, et cliquez sur le bouton Ajouter. Ferme la fenêtre en cours. Ensuite, une fenêtre apparaîtra à l'écran. Générateur de requêtes.

Ajoutez un champ à la requête. Pour cela dans tableau des sources situé en haut de la fenêtre Demande d'échantillon, sélectionnez le champ requis. Double-cliquez avec le bouton gauche de la souris sur le champ en surbrillance. En même temps, en bas de la fenêtre demandes une colonne correspondant au champ sélectionné apparaîtra dans le formulaire de demande.

Vous pouvez également ajouter un champ à une requête comme suit : cliquez sur bouton gauche souris sur le champ sélectionné et, sans le relâcher, faites glisser le champ à l'endroit souhaité sur le formulaire de demande ; cliquez sur un champ de cellule dans le formulaire de demande pour afficher une liste déroulante de champs et sélectionnez-y le champ requis ; cliquez sur le champ de la cellule dans le formulaire de demande et entrez le nom du champ.

Pour inclure tous les champs d'une table dans une requête, sélectionnez le champ marqué d'un astérisque (*). L'astérisque facilite la construction d'une requête, mais complique le tri et la saisie des conditions de sélection des champs.

Par défaut, tous les champs de requête ne sont pas triés. Si nécessaire, vous pouvez spécifier dans la ligne Tri formulaire de requête affichant les valeurs des colonnes : par ordre croissant ou décroissant. Lorsque vous spécifiez un ordre de tri dans plusieurs champs, les enregistrements sont d'abord triés par la colonne la plus à gauche, puis par chaque colonne suivante de gauche à droite.

En ligne Sortie à l'écran cochez les cases des champs que vous souhaitez voir dans le jeu d'enregistrements après l'exécution de la requête. Par défaut, tous les champs inclus dans le formulaire de demande sont affichés.

Dans le formulaire de demande à la ligne Condition de sélection et en ligne Ou spécifier les conditions d'échantillonnage à partir de la base de données. Les conditions sont des expressions booléennes.

Une opération logique est effectuée sur les conditions de sélection situées sur une ligne ET. Plusieurs conditions de sélection pour un champ peuvent être définies de deux manières : entrez toutes les conditions dans une cellule de la ligne Condition de sélection, en les reliant par un opérateur logique OU, ou saisissez chaque condition dans une cellule distincte de la ligne Ou. Passer à la ligne suivante de la zone de cellule OU par clé<>. Les expressions dans la cellule du formulaire de demande sont saisies au clavier ou utilisées pour les créer. Générateur d'expressions, qui est exécuté par la commande Construire depuis le menu contextuel associé à la ligne Condition de sélection(ou vous pouvez appuyer sur le bouton Construire dans la barre d'outils Accéder).

Afficher les résultats de la requête en cliquant sur le bouton lancement(bouton avec un point d'exclamation) ou le bouton Voir dans la barre d'outils.

Dans les situations où vous devez modifier une propriété de requête, double-cliquez sur la zone vide en haut de la fenêtre Requête constructeur - une fenêtre s'ouvrira Demander des propriétés.

Requêtes créées dans les modes Constructeur et Maîtrise, générer des commandes en langage SQL. Vous pouvez afficher ces commandes en sélectionnant dans le menu Voir option modeSQL.

EXEMPLE:

Sélectionnez des informations sur les produits en quantités supérieures à 100, mais inférieures à 300 et coûtant moins de 100 roubles.

Riz. 10. Constructeur de requête avec une condition

Riz. 11. Résultat de la requête avec condition

2. Jointure interne à champ unique

Sélectionner l'onglet Demandes fenêtre de la base de données, cliquez sur le bouton Créer. Une boîte de dialogue s'ouvrira Nouvelle requête. Choisis une option Constructeur.

Dans la fenêtre Ajout d'un tableau sélectionner séquentiellement plusieurs tables liées sur lesquelles la requête sera construite. Au sommet Générateur de requêtes les tables associées sont affichées.

Remplissez le formulaire de demande.

Cliquez sur le bouton lancement ou Voir pour refléter les résultats de la requête.

EXEMPLE:

Sélectionnez les noms des produits pour lesquels un contrat a été conclu, en indiquant les noms de leurs fabricants et l'étendue de la livraison.

Riz. 12. Constructeur de requêtes multi-tables

Riz. 13. Résultat d'une requête multi-tables

3. Connexions externes

Suivez les étapes 1 à 3 de la section Jointure interne sur un champ.

Établir une relation entre les tables, si elle n'a pas été précédemment enregistrée dans schème BD.

Cliquez sur la ligne d'union de champ pour la mettre en surbrillance.

Choisissez dans le menu de commande Voir option Options de fusion. Une boîte de dialogue apparaîtra. Options de fusion.

Sélectionnez le type de jointure, cliquez sur le bouton D'ACCORD. Après cette opération, une flèche apparaîtra sur la ligne de jointure indiquant le type de connexion.

4. Inscription dans le formulaire de demande d'une opération de groupe

Cliquez sur le bouton Opérations du groupe, pancarte  dans la barre d'outils Générateur de requêtes. Dans le formulaire de demande au-dessus de la ligne Tri ligne apparaîtra Fonctionnement en groupe.

Remplacer en ligne Fonctionnement en groupe installation regroupementà l'exigence opération de groupe en le sélectionnant dans la liste déroulante : somme, Moy, Compter, Mdans, Max, StDev, Var, Première, Dernier, expression(expression),Où(condition),groupe par(regroupement).

Expression permet de saisir une expression au lieu d'un nom de champ dans une cellule , par exemple, pour calculer la plage de valeurs pour une colonne X:Max([ X]) – Min([ X]).

Condition indique que ce champ sera utilisé pour définir les critères de sélection des enregistrements. La condition de sélection elle-même est écrite dans la cellule de la ligne Condition de sélection.

3. Complétez la demande.

EXEMPLE:

Sélectionnez le nombre total de produits de chaque fabricant, en indiquant son nom.

Riz. 14. Constructeur de requête avec regroupement d'enregistrements

Riz. 15. Résultat de la requête avec regroupement d'enregistrements

5. Inclure des champs calculés dans le formulaire de requête

Générateur de requêtes .

Cliquez en ligne Champ sur une colonne vide du formulaire de demande. Tapez un nom de colonne suivi de deux-points suivi d'une expression. Pour entrer une expression, cliquez sur le bouton Construire, pour profiter Générateur d'expressions.

Au sommet Générateur d'expressions dans la zone de saisie, créez une expression.

Dans une colonne de champ calculé, déplacez le curseur sur la ligne Fonctionnement en groupe et sélectionnez dans la liste déroulante Expression(si vous ne sélectionnez pas l'option Expression, lors de son exécution, la requête ouvre une boîte de dialogue Entrez la valeur du paramètre ou renvoie un message d'erreur).

Exécutez une requête.

EXEMPLE:

Calculez le montant total de la commande pour chaque contrat (défini comme le produit de la quantité et du prix plus le coût du transport).

Riz. 16. Constructeur de requête avec un champ calculé

Riz. 17. Résultat de la requête avec un champ calculé

6.Créer une requête paramétrique

Créer une requête de sélection Constructeur de requête.

Entrez la chaîne dans l'une des cellules Condition de sélection paramètre, pas de valeur. Par exemple, si les enregistrements sont sélectionnés par nom de famille, alors dans requête paramétrique sur le terrain Nom de famille dans les critères de sélection, la phrase [entrer le nom de famille] peut être écrite entre crochets.

Choisissez dans le menu Demande option Choix. Dans la boîte de dialogue Paramètres de requête dans la colonne de gauche, entrez entre crochets le paramètre qui correspond au paramètre dans formulaire de demande Générateur de requêtes(le paramètre peut être copié depuis le formulaire de demande). Dans la colonne de droite, précisez le type de données : appuyez sur la touche , sélectionnez un type de données dans la liste.

Lancer une demande d'exécution. Accéder affichera une fenêtre Entrez la valeur du paramètre, dans lequel entrez la valeur de paramètre requise et appuyez sur le bouton D'ACCORD.

5. Exécutez la demande d'exécution.

EXEMPLE:

Sélectionnez une liste de produits dont la date d'expiration dépasse la valeur spécifiée.

Riz. 18. Constructeur de requête de paramètres

Riz. 19. Saisie de la valeur de la date de péremption du produit

Riz. 20. Résultat d'une requête paramétrique

7. Créer des requêtes croisées

1. Créez avec Constructeur nouvelle requête et incluez-y les tables requises.

2. Sélectionnez les champs dont les valeurs seront utilisées pour créer les lignes et les colonnes du tableau dynamique, et faites-les glisser vers le formulaire de requête.

3. Sélectionnez une commande dans le menu Requête/Croix. entête Constructeur changera de Requête 1 : pour la sélection sur le Requête 1 : requête croisée . Le formulaire de demande affichera la ligne tableau croisé .

4. Pour les champs qui seront des lignes de tableau croisé, ouvrez la liste déroulante des lignes tableau croisé sélectionner une option En-têtes de ligne. Chaque champ qui est l'en-tête de la ligne doit avoir le paramètre dans la ligne Opération de groupe Regroupement.

5. Pour les champs qui seront des colonnes du tableau croisé, selon la requête, les actions suivantes sont possibles :

en ligne tableau croisé sélectionner une option En-têtes de colonne ligne ive Fonctionnement en groupe installation regroupement, ou en ligne Fonctionnement en groupe – Condition, dans les lignes Condition de sélection et OUécrire Expression booléenne.

6. Définissez le champ sur la base duquel la valeur totale sera créée. En ligne tableau croisé choisir Sens. En ligne Fonctionnement en groupe choisir une fonction finale ou Expression. Dans ce dernier cas, dans la cellule Champécrivez l'expression.

7. Exécutez la demande d'exécution.

EXEMPLE:

Sélectionnez les quantités totales de produits commandés de chaque type fabriqués par chaque fabricant. Présentez le résultat sous forme de tableau, où les lignes sont les noms des types de produits, les colonnes sont les noms des fabricants.

Riz. 21. Constructeur de requête croisée

Riz. 22. Résultat de la requête croisée

8.Requête pour créer une table

Pour créer une table à partir des résultats de la requête utilisée pour sélectionner des enregistrements dans tableaux associés, procédez comme suit :

Faites une copie de la requête pour sélectionner des enregistrements dans les tables (ou créez-en une nouvelle).

Ouvrir la requête créée en mode Designer, en surlignant son nom dans la liste des requêtes de la fenêtre de la base de données et en appuyant sur le bouton Constructeur.

Sélectionnez une commande dans le menu Interroger/créer une table.Une boîte de dialogue s'ouvrira Créer un tableau. Entrez le nom de la nouvelle table, laissez le commutateur en position dans la base de données actuelle.

Cliquez sur le bouton D'ACCORD. La requête de sélection est convertie en une requête de création de table.

Exécutez la requête en double-cliquant sur le nom de la requête dans la fenêtre DB. Avant l'exécution de la requête, un message s'affiche pour avertir que des modifications seront apportées à la nouvelle table (même si elle n'a pas encore été créée).

Cliquez sur le bouton Oui. Un deuxième message d'avertissement apparaîtra concernant le nombre d'enregistrements qui seront placés dans la nouvelle table à la suite de la requête. Cliquez sur le bouton Oui.

Développez l'onglet Tables dans la fenêtre DB ; la nouvelle table devrait apparaître dans la liste.

9.Demande de mise à jour des enregistrements

Créez une nouvelle requête et incluez-y la copie créée de la table.

Sélectionnez une commande dans le menu Demande/mise à jour. Dans le formulaire de demande de ligne Tri et Sortie à l'écran sera remplacé par la chaîne Mise à jour.

En ligne Mise à jour définir une expression qui est la nouvelle valeur du champ actuel.

Si les informations ne sont pas mises à jour dans tous les enregistrements de la table, mais uniquement dans sa partie, saisissez une condition de sélection d'enregistrement.

Remplissez la demande. Vérifiez que les enregistrements sont correctement mis à jour.

EXEMPLE:

Mettez à jour le coût des produits dont la quantité dépasse 500 unités, en le fixant à 90 % du coût d'origine.

Riz. 23. Mettre à jour le constructeur de requête

10.Demande de suppression d'entrées

Créez une copie de la table où les enregistrements sont censés être mis à jour.

Créez une nouvelle requête et incluez-y la table appropriée.

Dans le formulaire de demande, faites glisser les champs, selon les valeurs dont les champs seront sélectionnés. En ligne Conditions de sélection préciser les critères de sélection.

Exécutez une requête de sélection pour afficher les enregistrements à supprimer.

Ouvert fenêtre de la base de données. Sélectionner l'onglet les tables.

Activez la requête créée pour la sélection et passez en mode Requête constructeur.

Sélectionnez une commande dans le menu Demander/Supprimer. Dans le formulaire de demande de ligne Tri et Sortie à l'écran sera remplacé par la chaîne Suppression.

Cliquez sur le bouton lancement barre d'outils. Une boîte de message apparaîtra vous demandant de confirmer la suppression des entrées.

Suppression d'enregistrements dans la table " une", pour lequel dans le tableau « de nombreux" des enregistrements associés existent enfreint la règle d'intégrité des données. Par conséquent, l'option d'intégrité des données définie pour une relation entre des tables peut empêcher la suppression d'enregistrements. Une suppression en cascade doit être appliquée.

EXEMPLE:

Riz. 24. Supprimer le constructeur de requête

11. Demande d'ajout d'enregistrements

La requête d'ajout d'enregistrement est souvent utilisée pour copier des données d'une table à une autre.

Sélectionner l'onglet Demandes fenêtre de la base de données, cliquez sur le bouton Créer. Une boîte de dialogue s'ouvrira Nouvelle requête. Choisis une option Constructeur.

Dans la fenêtre Ajout d'un tableau sélectionnez la table à partir de laquelle les données seront extraites.

Passer à la ligne Champ formulaire de requête de champ, dont les valeurs seront incluses dans une autre table.

Sélectionner les commandes de menu Demander/Ajouter. Une fenêtre apparaîtra Addenda, où vous devez spécifier le nom de la table à laquelle les données sont censées être ajoutées et la base de données à laquelle elles appartiennent. Si la base de données n'est pas celle en cours, mais une autre, alors vous devrez spécifier le nom complet du fichier contenant la base de données.

Dans le formulaire de demande après la ligne Tri la ligne sera incluse ajouter, dans lequel il est nécessaire de spécifier les champs de la table où les données sont ajoutées. Si les noms de champs correspondent UNAveccession s'insérera automatiquement dans la ligne Addenda noms de champs.

Lancer une demande d'exécution. Voyez comment le tableau a changé avec les nouvelles lignes ajoutées.

Si dans une demande de Addenda il y a des champs comme Compteur, il existe deux manières de travailler avec ces champs :

ne pas inclure les champs de type Compteur dans les champs à ajouter ; puis dans le tableau où les données sont ajoutées, la numérotation de la séquence de données se poursuivra, c'est-à-dire s'il y a une table d'employés avec des nombres de 1 à 100 et y ajouter de nouvelles entrées, alors leur numérotation commencera à partir du numéro 101 ;

inclure des champs de type Compteurà la liste de requêtes en tant que champs ajoutés, les valeurs qu'ils avaient dans la table d'origine seront stockées dans les champs des enregistrements ajoutés ; si de telles valeurs existent déjà dans les champs de la table à laquelle les enregistrements sont ajoutés, cela peut entraîner une erreur. Le message d'erreur s'affiche à l'écran jusqu'à ce que la demande soit exécutée. En cas d'erreur, sélectionnez le bouton Annuler et corrigez l'erreur.

Dans cet article, nous parlerons des champs calculés dans les requêtes Access. Dans une requête, comme dans une table, pour chaque enregistrement, des calculs peuvent être effectués sur des valeurs numériques, de chaîne ou de date en utilisant les données d'un ou plusieurs champs. Le résultat du calcul forme un nouveau champ calculé dans la table de requête. Contrairement aux champs de table calculés, les champs calculés ne créent pas de nouveaux champs dans les tables de base de données source. Chaque fois que la requête est exécutée, des calculs sont effectués en fonction des valeurs actuelles des champs.

Les expressions de champ calculé peuvent utiliser des constantes et des fonctions en plus des noms de champ. Suite au traitement d'une expression, une seule valeur peut être obtenue.

Tache 1. Dans la table MARCHANDISES il y a les champs PRIX et RATE_VAT, calculez le prix TTC et comparez-le avec celui obtenu dans le champ calculé de la table Prix avec TVA.

1. Créez une requête Sélection pour la table PRODUIT en mode Création. Faites glisser les champs LEM_TOV, PRIX, RATE_VAT et Prix avec TVA dans le formulaire de demande (Fig. 4.6).
2. Pour calculer le prix TTC, créez un champ calculé en écrivant l'expression [PRIX]+[PRIX]*[TARIF_TVA] dans une cellule vide de la ligne Champ.
3. Pour sélectionner des enregistrements avec une valeur supérieure à 5000 dans le champ calculé, dans la ligne Critères, saisissez > 5000
4. Après avoir saisi l'expression, le système génère par défaut le nom du champ calculé Expression 1, qui devient l'en-tête de la colonne du tableau avec les résultats de la requête. Ce nom sera inséré avant l'expression [PRICE]+[PRICE]*[VAT_RATE]. Pour chaque nouveau champ calculé dans la requête, le numéro de l'expression est incrémenté de un. Le nom du champ calculé est séparé de l'expression par deux-points. Pour modifier le nom, placez le curseur de la souris dans le champ calculé du formulaire de demande et appuyez sur le bouton droit de la souris. Dans le menu contextuel, sélectionnez Propriétés(Propriétés) et dans la ligne Légende (Légende) entrez un nouveau nom de champ - Prix ​​avec TVA1. Maintenant dans le tableau avec les résultats de la requête, ce nom sera affiché dans l'en-tête de la colonne calculée. Le nom du champ peut également être corrigé directement dans le formulaire de demande.
5. Pour afficher le résultat de la requête, cliquez sur le bouton Courir(Courir) en groupe résultats(résultats). Le champ calculé de la table et de la requête ont les mêmes valeurs.
6. Modifiez le prix de l'article dans l'un des enregistrements de demande. Les valeurs des deux champs calculés seront instantanément recalculées.
7. Pour former une expression complexe dans un champ calculé ou une condition de sélection, il est conseillé d'utiliser le générateur d'expression. Le générateur vous permet de sélectionner les noms de champ nécessaires dans l'expression à partir de tables, de requêtes, de signes d'opération, de fonctions. Supprimez l'expression dans le champ calculé et utilisez le générateur pour la créer.
8. Appelez le générateur d'expressions en cliquant sur le bouton constructeur(Constructeur) en groupe Configuration de la requête(Configuration de la requête) Conception du ruban (Conception), ou en sélectionnant Construire(Construire) dans le menu contextuel. Le curseur de la souris doit d'abord être placé dans la cellule de saisie de l'expression.
9. Sur le côté gauche de la fenêtre Générateur d'expressions(Expression Builder) (Fig. 4.7) sélectionnez la table PRODUCT sur laquelle la requête est construite. Une liste de ses champs sera affichée sur la droite. Sélectionnez séquentiellement les champs et les opérateurs requis en double-cliquant sur l'expression. L'expression sera générée dans la partie supérieure de la fenêtre. Veuillez noter que le constructeur avant le nom du champ indiquait le nom de la table à laquelle il appartient et le séparait du nom du champ par un point d'exclamation.
10. Terminez la création de l'expression dans le champ calculé en cliquant sur OK.
11. Enregistrez la demande sous le nom - Prix avec TVA et fermez-la.
12. Exécutez celui enregistré en le sélectionnant dans le volet de navigation et en sélectionnant la commande Ouvrir dans le menu contextuel.

Tâche 2. Vous pouvez utiliser des fonctions intégrées dans les champs calculés et les conditions de sélection. Access a plus de 150 fonctions définies.
Supposons qu'il soit nécessaire de sélectionner toutes les factures dont l'expédition a été effectuée au cours d'un mois donné. Sur la FACTURE, la date d'expédition est stockée dans le champ DATE_OTG avec le type de données Date/Heure (Date/Heure).

1. Créez une requête de sélection pour la table INVOICE en mode Création. Faites glisser et déposez les champs NOM_NAKL et CODE_SK dans le formulaire (Fig. 4.8).
2. Créer un champ calculé dans une cellule de ligne vide Champ(Champ) en y écrivant l'une des expressions : Format([Facture]![DATE_OFFER];"mmmm") - cette fonction renverra le nom complet du mois
   ou Format([Facture]![SHIPPING_DATE];"mm") - cette fonction renverra le numéro du mois.
3. Pour sélectionner les factures émises au cours d'un mois donné, dans le champ calculé de la ligne Critères, saisissez le nom du mois, par exemple mars (Fig. 4.8), ou le numéro du mois, par exemple 3, conformément au paramètre dans la fonction Formater.
4. Exécutez la demande en cliquant sur le bouton Courir(Courir) en groupe résultats(Résultats) dans l'onglet du ruban Travailler avec des requêtes | Constructeur(Outils de requête | Conception).
5. Écrivez la fonction Month(Invoice!DATE_OTG) dans le champ calculé et assurez-vous que cette fonction renvoie le numéro de mois extrait de la date.
6. Pour sélectionner toutes les lignes du deuxième trimestre, dans la ligne Critères, entrez l'opérateur Entre 4 et 6 pour déterminer si la valeur de l'expression se situe dans la plage spécifiée.
7. Écrivez l'expression MonthName(Month(Invoice!DATE_OTG)) dans le champ calculé et assurez-vous que la fonction MonthName convertit le numéro du mois en son nom complet.

Pour la réparation, regardez le didacticiel vidéo.

2ème manière : clic droit sur les valeurs champ souhaité dans la zone ΣValues ​​​​[Σ Values] et sélectionnez :

Si vous le souhaitez, pour sélectionner une autre fonction dans la liste suggérée, personnalisez le format du champ et modifiez le nom du champ.

Pour profiter des effets. Aujourd'hui, nous allons vous apprendre. Cochez les cellules que vous souhaitez dessiner. Leçon 3 : Tableaux et graphiques croisés dynamiques. Supposons que nous ayons un ensemble pour l'analyse. Pour utiliser un tableau croisé dynamique, sélectionnez n'importe quelle cellule de la feuille de calcul et sélectionnez Tableau croisé dynamique dans le menu Insertion.

Ensuite, vous pouvez obtenir des informations. Lors de la création de graphiques, peu importe si le tableau a une bordure ou une autre mise en forme. Introduction aux formules et aux fonctions Effectuer des calculs, qu'ils soient simples ou complexes, peut être fastidieux et prendre du temps.

 Calculs supplémentaires, puis sélectionnez l'option de calcul souhaitée.

Réutiliser un champ d'origine dans un rapport de tableau croisé dynamique

Plusieurs calculs peuvent être effectués sur le même champ source. Pour ce faire, vous devez ajouter le champ initial de la liste des champs du tableau croisé dynamique à la plage de valeurs Σ Valeurs [Σ

Values] plusieurs fois, puis ajustez les réglages sur place. Cela vous permettra de voir simultanément le calcul lui-même et le calcul effectué sur sa base.

Ajout de champs calculés à un tableau croisé dynamique

Vous ne pouvez pas ajouter de nouvelles lignes ou colonnes à un rapport de tableau croisé dynamique pour insérer des formules ou des calculs supplémentaires, mais vous pouvez ajouter un champ calculé que vous pouvez utiliser pour entrer des formules afin de calculer de nouvelles données basées sur des données existantes.

Base de données Carte de travail 1 Feuille de calcul Bases de données 1 Créez un dossier Bases de données pour stocker toutes les données de cours. Crée une requête qui affiche les titres et les descriptions. La boîte de dialogue de gestion de la base de données apparaît. A partir de cette fenêtre, l'utilisateur gère la base de données et tous ses éléments.

Qu'est-ce qu'une feuille de calcul ? Un tableur est un programme permettant d'effectuer des calculs mathématiques. Avec lui, nous pouvons également faire des calculs lisibles dans le formulaire. Pour profiter d'actions spécifiques. Le plus dur est de préparer les données. Problème 1 Math Groupe 2 Suivez ces étapes à tour de rôle. Exécutez le tableau.

Insérer un champ calculé directement dans un tableau croisé dynamique est meilleure solution. Vous n'aurez pas besoin de gérer les formules ou de vous soucier de l'extensibilité du tableau à mesure que vous développez ou modifiez votre source de données, et le tableau croisé dynamique restera suffisamment flexible à mesure que les définitions de champ changent.

Un autre énorme avantage de cette méthode est que vous pouvez modifier la structure du tableau croisé dynamique et même calculer d'autres champs de données pour les champs calculés sans vous soucier des erreurs de formule ou des références de cellules brisées.

Université de technologie de Poméranie occidentale à Szczecin Faculté de technologie marine et de transport Département des structures, de la mécanique et de la technologie Un navire dans un laboratoire de recherche opérationnelle. Les tableaux croisés dynamiques sont utilisés pour une analyse rapide des données. Pour utiliser des tableaux croisés dynamiques, nous devons avoir les données correctement préparées avec des en-têtes.

Pour éviter de telles situations, vous devez formater nos données sous forme de tableau. Pour formater les données sous forme de tableau. Cela sélectionnera tous les en-têtes. . Le choix suivant est l'emplacement du tableau croisé dynamique, et pour garder les données lisibles, vous devez laisser l'option Nouvelle feuille, qui insérera une nouvelle feuille dans le tableau.

Fonctionnalités avancées de Microsoft Excel 2010 niveau 2

Ainsi, lorsque vous ajoutez vos propres champs calculés à un tableau croisé dynamique, les avantages sont évidents :

 Évitez les erreurs potentielles dans les formules et les références de cellule.

 Possibilité d'ajouter et de supprimer des données d'un tableau croisé dynamique sans modifier les calculs d'origine.

Une nouvelle feuille de tableau croisé dynamique est vide. Sur le côté droit, il y a une liste de champs, qui contient toutes les rubriques de notre table, et un plan avec ses superficies. Supposons que nous voulions voir qui était le meilleur vendeur. Dans ce cas, sélectionnez le champ Vendeur et le champ Montant, car après la vente, nous vérifierons qui était le meilleur. Le rapport ressemblera à ceci.

La valeur par défaut est la colonne. Les tableaux croisés dynamiques peuvent être facilement modifiés, et si vous souhaitez vérifier le pays où les meilleures ventes sont effectuées, il vous suffit d'annuler l'achat et de le marquer comme vendeur. Immédiatement, nous obtenons un nouveau rapport à partir duquel vous pouvez lire le résultat.

 Possibilité de recalculer automatiquement les données lors de la modification ou de la mise à jour d'un tableau croisé dynamique.

 Offre la possibilité de modifier les calculs lorsque les définitions d'éléments changent.

 Possibilité de gestion efficace des calculs.

Créez un champ calculé :

1. Sélectionnez une cellule du tableau croisé dynamique ;

Nous pouvons également ajouter une sous-catégorie au Vendeur en cochant cette case. Le rapport des ventes résume automatiquement les valeurs affichées, mais souvent ces totaux rendent l'analyse difficile. Nous pouvons également supprimer le total général et modifier la mise en page du rapport.

Pour changer rapidement le rapport et voir comment il a vendu Dobrovalsky à d'autres pays, changez simplement l'ordre des conditions dans les marges en saisissant le vendeur et en le traînant à travers l'état. En dehors de l'Irlande et de l'Allemagne, force est de constater que ses résultats commerciaux ne sont pas les meilleurs. Lorsque vous cliquez sur vendre en Irlande, vous verrez une nouvelle feuille avec un tableau contenant toutes les transactions de Dobrovalsky en Irlande.

2. Dans l'onglet Options, dans le groupe Calculs, développez la liste des Champs,

éléments et ensembles et sélectionnez Champ calculé.

3. Dans la fenêtre Insérer un champ calculé:

 Saisissez un nom pour le nouveau champ dans le champ Nom.

 Dans le champ Formule, écrivez une formule en commençant par le signe = et en choisissant

champs disponibles dans la liste Champs (pour insérer, vous pouvez double-cliquer sur le champ ou sélectionner le champ, puis cliquer sur le bouton Ajouter un champ).

Si nous voulons imprimer nos données, nous prendrons le champ Vendeur de la zone "Lignes" et le déplacerons vers la zone "Colonne". Cela rendra le tableau étendu sur toute la feuille. Dans cette fenêtre, nous pouvons également spécifier le format numérique affiché dans toutes les cellules du champ. Utilisez simplement le bouton "Format numérique" et sélectionnez la devise appropriée, par exemple.

Les filtres sont également utiles. Lorsqu'ils sont ajoutés à cette zone, les champs de fournisseur apparaissent dans le coin supérieur gauche d'une liste déroulante à partir de laquelle vous pouvez sélectionner une ou plusieurs options. Voyons quel mois a été la meilleure vente. Ci-dessous, nous sélectionnons la catégorie selon laquelle nous voulons regrouper les données.

 Cliquez sur Ajouter.

L'apparence d'un champ calculé, comme tout autre champ, est configurée dans la fenêtre

Options de champ de valeur.

Modification d'un champ calculé

1. Sous l'onglet Options, dans le groupe Calculs, ouvrez la liste Champs,

éléments et ensembles, sélectionnez Champ calculé.

Nous savons tous comment utiliser les tableaux croisés dynamiques. Ils ont beaucoup d'avantages, mais il y a aussi beaucoup d'inconvénients. Beaucoup de gens pensent que le principal problème avec les tableaux croisés dynamiques est qu'il n'y a aucun moyen d'y faire des calculs - c'est une idée fausse. Pourquoi cela pourrait-il être nécessaire. Supposons que vous établissez un budget à l'aide de tableaux croisés dynamiques. Les données du tableau original sont présentées trimestriellement, à des fins de budgétisation, il sera nécessaire d'ajouter ces données à un montant annuel.

Nous marquons les mois et nous les acceptons. Nous aurons un rapport trié par mois. Vous pouvez également diviser les données en plusieurs catégories. En revenant au regroupement des configurateurs et en sélectionnant "Trimestre et mois", nous obtiendrons l'apparence du rapport. Le regroupement peut également être utilisé pour les nombres.

Lorsque vous ouvrez menu contextuel, le champ Lignes et l'option Groupe affichent une fenêtre différente que si vous regroupiez des dates. Les nombres sont regroupés en plages, que nous définissons par le début, la fin et le volume de l'intervalle. Dans ce cas, nous le définissons sur 500, de sorte que le premier groupe contiendra toutes les transactions d'une valeur comprise entre 0 et 500 PLN.

En fait, il existe une telle opportunité et elle est mise en œuvre de trois manières.

Rafraîchissez vos connaissances sur les bases des tableaux croisés dynamiques.

Première voie. Effectuez des calculs dans le tableau avec les données source. La méthode, franchement, n'est pas une fontaine - par le mauvais endroit.

La deuxième façon. Construisez des calculs à côté du tableau croisé dynamique - encore une fois, la méthode n'est pas la meilleure. Si le tableau croisé dynamique va changer (et cela vaut la peine d'utiliser des tableaux croisés dynamiques exactement au moment où nous attendons des changements dans le tableau).

La troisième voie. Incorporez des champs calculés dans le tableau croisé dynamique lui-même. Mais c'est un bon moyen. Cela se fait de la manière suivante. Lorsque le tableau croisé dynamique est déjà construit, placez le curseur sur le tableau croisé dynamique (deux onglets liés à ce tableau croisé dynamique apparaissent sur le ruban) et cliquez sur l'onglet qui apparaît Choix, alors des champs,Articles , &Ensembles depuis le bloc Calculs, dans le menu déroulant, sélectionnez Calculéchamp. Dans Office 2007, ce bouton se trouvait sur le même onglet Options, seul le bouton était séparé.

Dans la fenêtre qui apparaît, dans le champ Nom entrez le nom de champ souhaité. Dans le champ Formule entrez la formule dont vous avez besoin en insérant les champs de la liste ci-dessous et en cliquant sur le bouton Insérerchamp.

Vous obtiendrez une table avec un nouveau champ calculé. Le tableau est prêt !

Le fichier Excel peut être téléchargé ici.

Fait intéressant. Lors de l'utilisation de la troisième méthode, le programme effectue en fait les calculs pour chaque ligne, puis applique le paramètre nécessaire : somme, moyenne, etc.

Dernières nouvelles

• Excel avancé : pourquoi j'ai arrêté d'utiliser la fonction V…

  J'ai déjà écrit sur le fait que la fonction VLOOKUP (VLOOKUP) est probablement la plus fonction utile après de simples opérations arithmétiques...

• Comment Excel recalcule un classeur et pourquoi vous devriez éviter la volatilité...

  Si vous travaillez souvent avec des fichiers volumineux qui produisent un grand nombre de calculs qui dépendent les uns des autres, à propos de ...

• Comment construire rapidement un graphique en cascade (cascade)

  Excel n'a pas de graphiques en cascade standard. Par conséquent, pour créer des diagrammes de ce type, gis est généralement utilisé ...

• Fenêtre de benchmark pour suivre les résultats

  Il arrive souvent que nous travaillions avec de grandes tables qui calculent finalement 1-2 indicateurs, mais dépendent de ...

• Tableau à puces pour comparer les indicateurs prévus et réels…

  Il est souvent nécessaire de comparer les principaux indicateurs financiers et économiques avec ceux prévus, par exemple, aux fins de…

• Tatsuo Horiuchi est un artiste de 73 ans qui dessine sous Excel.

  "Je n'ai jamais utilisé Excel au travail, mais j'ai vu d'autres personnes créer de jolis tableaux et graphiques...

• Comment élever un nombre à une puissance et prendre une racine

  Savez-vous que pour l'exponentiation d'un nombre dans Excel, il y a Caractère spécial^ (sur les six en anglais...

Le champ calculé le plus simple est le champ dans lequel La date actuelle. Pour créer un tel champ :

1. Créer un champ de type libre Champ(Zone de texte) au bon endroit du formulaire.
2. Directement dans le champ, saisissez l'expression =Date(). Le signe égal est obligatoire.
3. Ouvrez la fenêtre Propriétés(Propriétés) pour cet élément et définissez la propriété sur Format de champ(Format) format de date souhaité, par ex. Format de date longue(date longue).
4. Définir la longueur du champ pour s'adapter quantité requise personnages.
5. Modifiez le nom de l'étiquette de ce champ de texte. Le champ généré ressemblera à celui illustré à la Fig. 9.47.

Riz. 9.47.

Généralement, c'est le champ qui est choisi pour créer les champs calculés. Champ de texte, bien que ce ne soit pas obligatoire. Il est permis d'utiliser pour cela tous les contrôles qui ont la propriété Données(Source de contrôle).

C'est dans la propriété Données(Source de contrôle) doit être une expression qui évalue la valeur de cet élément. La saisie d'expression est autorisée directement dans le contrôle, comme nous l'avons fait dans l'exemple. Cependant, si l'expression est suffisamment longue, il n'est pas pratique de la saisir directement dans le champ. Vous pouvez entrer une expression dans une cellule de propriété Données(Source de contrôle) dans la fenêtre Propriétés(Propriétés), et si l'expression ne tient pas dans la cellule de propriété, appuyez simplement sur la combinaison de touches +et ouvrir une fenêtre Zone de saisie(Zoom). Vous pouvez également utiliser le générateur d'expressions.

Les champs calculés sont le plus souvent créés sous forme de tableau ou de ruban pour afficher une colonne avec une sorte de valeur calculée. Par exemple, le coût d'un produit est calculé comme la valeur de l'expression Prix * Quantité. Pour afficher ces données calculées, deux méthodes sont utilisées :

• La première méthode est basée sur le fait que le formulaire est basé sur une requête qui inclut des colonnes calculées. Un champ de texte est créé dans le formulaire avec une colonne de requête calculée comme source de données. Le champ est verrouillé afin que l'utilisateur ne puisse pas modifier les données qu'il contient, car ces données sont calculées lors de l'exécution de la requête.
• Dans le second cas, le formulaire peut être construit directement à partir du tableau. Il crée une zone de texte qui spécifie une expression comme source de données, par exemple = [Prix unitaire] * Quantité. Dans ce cas, l'expression n'indique pas nécessairement les champs inclus dans le formulaire, mais tous les champs de la table de base.

La première méthode est préférable dans la plupart des cas, car une telle demande peut être utilisée non seulement sous une forme, mais également dans des rapports et sous d'autres formes. Un exemple d'une telle organisation des règlements est le formulaire Orders Subform de la base de données Northwind. Il est basé sur la requête Order Details Extended, qui a créé un champ calculé, ExtendedPrice, contenant une formule.