Comment créer un champ calculé dans Excel. Colonnes calculées dans Transact-SQL Comment créer une colonne calculée

Dans cet article, nous parlerons des champs calculés dans Requêtes d'accès. Une requête, comme une table, peut effectuer des calculs sur des valeurs numériques, de chaîne ou de date pour chaque enregistrement 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 des tables de base de données source ne créent pas de nouveaux champs. Chaque fois que la requête est exécutée, des calculs sont effectués sur la base des valeurs actuelles des champs.

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

Tache 1. La table PRODUIT contient les champs PRICE et RATE_VAT. Calculez le prix TTC et comparez-le avec le prix obtenu dans le champ calculé de la table Prix TTC.

1. Créez une requête de sélection en mode conception pour la table PRODUCT. Faites glisser les champs NAME_TOV, PRICE, RATE_VAT et Price with TVA dans le formulaire de demande (Fig. 4.6).
2. Pour calculer le prix TTC, créez un champ calculé en écrivant l'expression [PRICE]+[PRICE]*[RATE_VAT] dans la cellule vide de la ligne Champ.
3. Pour sélectionner des enregistrements avec une valeur supérieure à 5 000 dans le champ calculé, saisissez > 5 000 dans la ligne Critères.
4. Après avoir saisi une expression, le système génère par défaut le nom du champ calculé Expression 1, qui devient l'en-tête de colonne dans le tableau contenant 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 d'expression augmente 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 cliquez 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, entrez un nouveau nom de champ - Prix ​​TTC1. Désormais dans le tableau des 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 Exécuter(Courir) dans le groupe résultats(Résultats). Le champ calculé de la table et la requête ont les mêmes valeurs.
6. Modifiez le prix du produit dans l'un des enregistrements de la 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 champs nécessaires dans l'expression à partir de tables, requêtes, signes d'opération et fonctions. Supprimez l'expression dans le champ calculé et utilisez le générateur pour la générer.
8. Appelez le Générateur d'expressions en cliquant sur le bouton Constructeur(Constructeur) dans le groupe Configuration de la requête(Configuration de la requête) Conception du ruban, ou en sélectionnant Construire(Construire) dans le menu contextuel. Le curseur de la souris doit être préalablement positionné 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 basée. Une liste de ses champs s'affichera à droite. Sélectionnez séquentiellement les champs et les opérateurs requis en double-cliquant dessus dans l'expression. L'expression sera formée en haut de la fenêtre. Veuillez noter que le constructeur a indiqué le nom de la table à laquelle il appartient avant le nom du champ et l'a séparé du nom du champ par un point d'exclamation.
10. Terminez le processus de construction d'une expression dans un champ calculé en cliquant sur le bouton OK.
11. Enregistrez la demande sous le nom ― Prix avec TVA et fermez-la.
12. Exécutez le fichier enregistré en le sélectionnant dans la zone de navigation et en sélectionnant 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 filtrage. Access a plus de 150 fonctions définies.
Supposons qu'il soit nécessaire de sélectionner toutes les factures pour lesquelles l'expédition a été effectuée au cours d'un mois donné. Dans INVOICE, la date d'expédition est stockée dans le champ DATE_OTG avec le type de données Date/Heure.

1. Créez une requête de sélection en mode conception pour la table INVOICE. Faites glisser 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([INVOICE]![DISPOSITION_DATE];"mmmm") - cette fonction renverra le nom complet du mois
   ou Format([INBOOK]![DISPOSITION_DATE];"mm") - cette fonction renverra le numéro du mois.
3. Pour sélectionner les factures émises un mois donné, dans le champ calculé de la ligne Condition de sélection (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 Format.
4. Complétez votre demande en cliquant sur le bouton Exécuter(Courir) dans le groupe résultats(Résultats) sur l'onglet du ruban Travailler avec des requêtes | Constructeur(Outils de requête | Conception).
5. Écrivez la fonction Mois(INVOICE!DATE_OTG) dans le champ calculé et assurez-vous que cette fonction renvoie le numéro du mois extrait de la date.
6. Pour sélectionner toutes les lignes liées au deuxième trimestre, dans la ligne Critères, saisissez l'opérateur Entre 4 et 6, qui détermine si la valeur de l'expression se situe dans l'intervalle spécifié.
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 renforcer cela, regardez le didacticiel vidéo.

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 de l'ensemble 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. En figue. La figure 1 montre comment un champ calculé peut présenter des données sous un angle différent. Le tableau croisé dynamique affiche le volume des ventes et le temps (en heures) passé à vendre des équipements sur chaque marché. Un champ calculé qui calcule le revenu moyen par heure améliore la capacité d'analyse des 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, qui donnera un nouveau sens aux données analysées

Téléchargez la 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 de tableau croisé dynamique existants. Le champ calculé est ajouté à l'ensemble de données sous forme de colonne virtuelle. Cette colonne n'est pas incluse dans les données source, contient des valeurs déterminées par une formule et interagit avec les données du tableau croisé dynamique de la même manière que les autres champs du tableau croisé dynamique.

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

Regardez maintenant la fig. 5.1 et demandez-vous : Pourquoi avez-vous dû ajouter des champs calculés ? Pourquoi ne pas utiliser des formules cellulaires classiques ou effectuer les calculs nécessaires directement sur la table source pour obtenir les informations dont vous avez besoin ? » Pour répondre à ces questions, examinons les différentes méthodes que vous pouvez utiliser pour créer le 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 le montre la figure. 2, permettant au tableau croisé dynamique d'utiliser ce champ comme champ de données standard. Si la disposition des informations sur la feuille source de données permet cela, quelle pourrait être la meilleure solution. À propos, l'ajout d'une nouvelle colonne nécessitera non seulement de mettre à jour le tableau croisé dynamique, mais également de modifier la portée sur laquelle le tableau croisé dynamique est basé. Pour cela, cliquez sur résumé et parcourez le menu Analyse → Source de données → Source de données.

J'essaie de transformer les données originales en tableau (ce qui est fait sur la figure 2 et dans le fichier Excel ci-joint). Dans ce cas, d'une part, les titres sont mis en évidence, d'autre part, des boutons avec des filtres apparaissent, ce qui est souvent utile pour l'exploration des données, troisièmement, les lignes sont colorées une à une et, enfin, le plus important, lors de l'ajout de lignes et colonnes, il n'est pas nécessaire de modifier la zone de données d'un tableau croisé dynamique. La zone spécifiée comme telle n'est pas une plage rectangulaire, mais le nom de la Table. Dans notre cas - Source(Fig. 3).

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

Mais cette méthode présente aussi des inconvénients : d'une part, il n'est pas toujours pratique d'ajouter une colonne aux données source, et d'autre part, les possibilités sont limitées si la structure des données source change (par exemple, vous exportez des données depuis 1C, et le nouvel export comporte une colonne supplémentaire ; cette nouvelle colonne écrasera votre colonne de calcul).

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

Vous pouvez ajouter un champ calculé à côté d'un tableau croisé dynamique. En figue. 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 faire référence à une cellule d'un tableau croisé dynamique, la fonction GET.PIOTTABLE.DATA apparaît dans votre formule, alors vous ne pourrez pas « faire glisser » la formule. Pour surmonter cette difficulté, lisez la note.

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

Bien que cette méthode ajoute un champ calculé qui est mis à jour lorsque le tableau croisé dynamique est actualisé, 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 dans la zone FILTRES de la liste des champs d'un 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 autre champ à la zone COLONNES du tableau croisé dynamique, la colonne Revenu moyen par heure sera écrasé.

Méthode 3 : insérer directement un champ calculé dans le tableau croisé dynamique

Insérer un champ calculé directement dans un tableau croisé dynamique est souvent la meilleure solution. Si vous utilisez cette méthode, vous n'aurez pas à 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é nécessaire sera atteint pour assurer un recalcul automatique des champs en cas de changement d'unités de mesure.

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 les différentes unités de mesure utilisées dans les champs calculés. Dans le 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 cellules ne seront pas perdues.

Le rapport de tableau croisé dynamique présenté dans la figure 6 présente un tableau récapitulatif, qui est illustré à la Fig. 1, sauf qu’il est reconstitué pour montrer le revenu horaire moyen pour chaque service et chaque marché.

Riz. 6. Le champ calculé précédemment créé reste « valide » même si la structure du tableau croisé dynamique est modifiée, entraînant l'affichage du revenu moyen par heure pour chaque service et chaque marché 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. Notre exemple utilise 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 Analyse, au groupe Calculs, 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 champs de texte en haut de la boîte de dialogue : Nom Et Formule. Dans ces champs, vous devez préciser 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. Le champ calculé reçoit un nom descriptif Revenu moyen par heure. Ce nom doit être choisi pour décrire avec précision le type d’opération mathématique effectuée. Champ de texte par défaut Formule boite de dialogue Insérer un champ calculé contient l'expression = 0. Vous devez supprimer le zéro avant de saisir la formule. Sélectionnez l'un des champs de la zone Des champs; dans notre cas - Volume des ventes et cliquez Ajouter un 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 puis D'ACCORD pour activer le nouveau champ calculé.

Un nouveau champ calculé apparaîtra dans le tableau croisé dynamique Revenu moyen par heure(voir fig. 1). Un nouveau champ calculé vient d'être ajouté avec succès à un tableau croisé dynamique. Vous pouvez désormais modifier les paramètres de ce nouveau champ comme vous modifieriez les paramètres de n'importe quel autre champ (par exemple, le nom du champ, le format des nombres ou la couleur).

Cela signifie-t-il que vous avez ajouté la colonne à la source de données ? Non. Les champs calculés sont comme des éléments Total Et montant total Les fonctions de tableau croisé dynamique sont celles par défaut car ce sont toutes des fonctions mathématiques qui recalculent les données lorsque le tableau croisé dynamique est modifié ou mis à jour. Les champs calculés imitent simplement les champs explicitement définis dans la source de données. Vous pouvez les faire glisser, modifier les paramètres des champs et les utiliser avec d'autres champs calculés.

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

Création d'éléments calculés

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

Dans le tableau récapitulatif présenté à la Fig. 9, le volume total des ventes pour certaines périodes de reporting est indiqué (champ Période de déclaration). Imaginez que vous souhaitiez comparer le volume de ventes moyen des six dernières périodes avec le volume de ventes moyen 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 à la moyenne des périodes P08 à P13.

Placez le pointeur dans n'importe quelle cellule du champ Période de déclaration accédez à l'onglet contextuel du ruban 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 indique le champ avec lequel 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 à l'élément calculé, puis créez une formule utilisant la combinaison nécessaire d'éléments 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 élément calculé

De même, créez un élément calculé qui représentera les ventes moyennes pour les périodes P08 à P13. Masquez ensuite les périodes de reporting individuelles, en ne laissant que deux éléments calculés. Avec un peu de mise en forme, nos éléments 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 de tableau croisé dynamique

L'utilisation de champs et d'éléments calculés présente également 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 de comprendre les limites des champs calculés et des éléments calculés pour éviter les erreurs lors de l'analyse de vos données.

Priorité des opérations. Comme dans les feuilles de calcul, n'importe quel opérateur peut être utilisé dans les formules pour les éléments et champs calculés : +, –, *, /, %, ^, etc. De plus, comme dans les feuilles de calcul, les calculs dans les tableaux croisés dynamiques sont effectués selon la priorité des opérateurs. Lorsque vous effectuez un calcul combinant plusieurs opérateurs, tels que (2+3) *4/50 %, Excel évalue l'expression et effectue le calcul dans un ordre spécifique. Connaître cet ordre vous évitera de nombreuses erreurs.

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

• traiter les expressions entre parenthèses ;
• traitement de la plage (:);
• traiter les intersections (zones);
• traitement des jointures (;);
• effectuer une opération de négation ;
• conversion de valeurs en pourcentage (par exemple, 50 % est converti en 0,50) ;
• exponentiation (^);
• Multiplication et division (/); ces opérations ont une priorité égale ;
• addition (+) et soustraction (–); ces opérations ont une priorité égale ;
• en combinant des données texte (&), 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 l'ordre (de gauche à droite).

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

Regardons un autre exemple. Si vous entrez l'expression 10^2 sous forme de formule, c'est-à-dire Si vous spécifiez de mettre au carré le nombre 10, le programme renverra la valeur 100 comme réponse. Si vous entrez l'expression –10^2, vous pouvez vous attendre à ce que la valeur renvoie –100. Cependant, Excel renverra à nouveau la valeur 100. La raison en est que le programme effectue l'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 -10*-10 est en réalité 100. L'utilisation de parenthèses dans la formule -(10^2) garantit que le programme effectue d'abord l'exponentiation, puis la négation, renvoyant la valeur attendue. résultat - 100.

Références de cellules et plages nommées. Les références de cellules 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 aller au-delà du cache, vous ne pouvez pas référencer des cellules ou des plages nommées dans la formule que vous créez.

Fonctions de la 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 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 le sens Unités vendues peut changer 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 faire référence aux sous-totaux ou aux totaux d'un tableau croisé dynamique. En d’autres termes, vous ne pouvez pas utiliser le résultat d’un calcul en cours ou total 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 avant d'évaluer un champ calculé. Cela signifie que votre champ calculé est toujours appliqué aux totaux des données sources. L'exemple montré sur la Fig. 14 montre comment cela peut affecter l’analyse des données.

Riz. 14. Bien qu'un champ calculé soit valide pour des éléments de données individuels, il n'est pas mathématiquement correct de l'utiliser pour des sous-totaux.

Chaque trimestre, vous devez obtenir les volumes de ventes finaux pour chaque produit en multipliant le nombre d'unités vendues par le prix unitaire de ce produit. Si vous examinez 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, soit 1 184, la somme du nombre d’unités est multipliée par la somme des prix des marchandises, ce qui donne une valeur incorrecte. Malheureusement, ce problème ne peut pas être résolu, mais il peut être contourné. Éliminez les sous-totaux et les sous-totaux du tableau croisé dynamique, puis calculez un nouveau total en bas en dehors du tableau croisé dynamique.

Règles spéciales pour l'utilisation des membres calculés. Un tableau croisé dynamique ne peut pas utiliser d'éléments calculés qui calculent des moyennes, des écarts types ou des variances. À l’inverse, vous ne pouvez pas utiliser des moyennes, des écarts types ou des variances dans un tableau croisé dynamique contenant un élément calculé. Vous ne pourrez pas utiliser un champ de page pour créer un élément calculé, ni déplacer les éléments calculés vers la zone de filtre du rapport. Vous ne pouvez pas ajouter un élément calculé à un rapport contenant un champ groupé et vous ne pouvez pas regrouper un champ dans un tableau croisé dynamique contenant un élément calculé. Lorsque vous créez une formule d'élément calculé, vous ne pouvez pas référencer un élément à partir d'un champ externe.

Gérer et prendre en charge les calculs dans les tableaux croisés dynamiques

Lorsque vous travaillez avec des tableaux croisés dynamiques, des situations surviennent souvent où cela n'a aucun sens 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 rentable de stocker le tableau croisé dynamique et tous ses éléments inchangés. Si vous gérez et gérez un tableau croisé dynamique en modifiant les exigences et en augmentant le volume des données sources, vous devez gérer les champs calculés et les membres calculés.

Modification et suppression de calculs dans des tableaux croisés dynamiques. Si vous modifiez les paramètres de calcul ou si vous n'avez plus besoin d'un champ calculé ou d'un membre calculé, vous pouvez ouvrir la boîte de dialogue correspondante pour modifier ou supprimer le calcul. Activer la boîte de dialogue Insérer un champ calculé ou Insertion d'un élément calculé(voir la description des figures 7 et 10) et sélectionnez-y 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 d'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 dans les champs calculés sont effectuées. En d’autres termes, vous pouvez spécifier l’ordre dans lequel les calculs individuels seront effectués. Pour ouvrir la boîte de dialogue , placez le pointeur n'importe où dans le tableau croisé dynamique et 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 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, Vers le bas Et Supprimer. L'ordre dans lequel les formules apparaissent dans la liste détermine l'ordre exact dans lequel les calculs sont effectués dans le tableau croisé dynamique.

Documentation des formules. Excel vous fournit un excellent outil qui affiche les champs calculés et les éléments calculés utilisés dans un tableau croisé dynamique, ainsi que la manière d'effectuer des calculs et d'appliquer des formules. Cet outil est très utile pour analyser un tableau croisé dynamique tiers ou lorsque vous devez déterminer rapidement quels calculs y sont utilisés et quels champs et éléments ils affectent. Pour créer un rapport sur les calculs du tableau croisé dynamique, placez votre pointeur n'importe où dans le tableau croisé dynamique et cliquez sur 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 vous permet de documenter rapidement et facilement les calculs dans un tableau croisé dynamique

La note a été rédigée sur la base du livre de Jelen, Alexander. . Chapitre 5.

Le mot Tableau est en 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é repose sur le fait que le formulaire repose sur une requête qui inclut des colonnes calculées. Le formulaire crée un champ de texte qui spécifie 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 Designer dans la fenêtre Propriétés champ calculé dans l'onglet Données dans une rangée Blocage entrer OUI.

Dans le second cas, le formulaire est construit à partir d'un tableau. Un nouveau champ est créé dans le formulaire, dans lequel une expression est spécifiée comme source de données, par exemple = [Prix unitaire]*Quantité. Lorsque vous faites référence à des champs dans des expressions, il est obligatoire d'utiliser des crochets si le nom du champ comprend plus d'un mot. La saisie d'une expression directement dans un champ (champ) est autorisée 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 fenêtre Propriétés. Vous pouvez utiliser le générateur d'expressions pour ce faire. 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, vous devez définir le format du champ lorsqu'il est affiché à l'écran.

6. Création d'un formulaire de bouton

Sélectionner la commande de menu Service/Utilitaires/Gestionnaire de formulaires de boutons-poussoirs.

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

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

Une boîte de dialogue s'ouvre Changer une page de formulaire de bouton. Modifiez le nom du formulaire.

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

Sur le terrain Texte Saisissez 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 cliqué sur ce bouton. Sélectionnez la commande requise pour votre application dans la liste déroulante.

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

Ajoutez un bouton pour quitter l'application, ainsi qu'un bouton pour accès rapideÀ Gestionnaire de formulaires de boutons.

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

EXEMPLE:

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

Atelier 3 Créer des requêtes

But du travail - acquérir des compétences pratiques dans le développement de requêtes de bases de données.

Exercice:

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

Séquence de travail

1.Créer une requête de sélection de table unique à l'aide de Query Builder

DANS fenêtre de 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 Ajouter un tableau sélectionnez dans la fenêtre de liste la table (ou requête) sur laquelle sera basée la requête et cliquez sur le bouton Ajouter. Fermez la fenêtre actuelle. Ensuite, une fenêtre apparaîtra à l'écran Générateur de requêtes.

Ajoutez un champ à la demande. A cet effet dans table source, situé en haut de la fenêtre Demande d'échantillon, sélectionnez le champ souhaité. Double-cliquez avec le bouton gauche de la souris sur le champ en surbrillance. Au même moment, 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 demande comme suit : cliquez sur bouton gauche passez la 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 cellule dans le formulaire de demande et saisissez le nom du champ.

Pour ajouter tous les champs de table à une requête, sélectionnez le champ indiqué par un astérisque (*). L'astérisque facilite le travail de 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 préciser dans la ligne Tri formulaire de requête, comment afficher les valeurs des colonnes : ascendantes ou décroissantes. Lorsque vous spécifiez un ordre de tri dans plusieurs champs, les enregistrements sont triés d'abord par la colonne la plus à gauche, puis par chaque colonne suivante de gauche à droite.

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

Dans le formulaire de demande dans la ligne Condition de sélection et dans la ligne Ou préciser les conditions d'échantillonnage à partir de la base de données. Les conditions sont des expressions logiques.

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 : saisissez toutes les conditions dans une cellule de la ligne. Condition de sélection, en les connectant avec un opérateur logique OU, ou entrez 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 une cellule du formulaire de demande sont saisies au clavier ou utilisées pour les créer. Générateur d'expressions, qui est lancé par la commande Construire depuis le menu contextuel associé à la ligne Condition de sélection(ou vous pouvez appuyer sur le bouton Construire sur la barre d'outils Accéder).

Affichez les résultats de la requête en cliquant sur le bouton Lancement(bouton avec un point d'exclamation) ou bouton Voir sur 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 Constructeur de requête - une fenêtre s'ouvrira Propriétés de la demande.

Requêtes créées dans les modes Designer 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 condition

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

2. Jointure interne sur un champ

Sélectionnez un 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. Choisir une option Constructeur.

Dans la fenêtre Ajouter 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 tableaux associés sont affichés.

Remplissez le formulaire de demande.

Cliquez sur le bouton Lancement ou Voir pour afficher les résultats de la requête.

EXEMPLE:

Sélectionnez les noms des produits pour la fourniture desquels un contrat a été conclu, en indiquant les noms de leurs fabricants et le volume de l'approvisionnement.

Riz. 12. Concepteur 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éalablement enregistrée dans Schème Base de données.

Cliquez sur la ligne de fusion de champs pour la mettre en surbrillance.

Sélectionnez les commandes dans le menu Voir option Options de fusion. Une boîte de dialogue apparaît 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. Inclure une transaction de groupe dans le formulaire de demande

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

Remplacer en ligne Opération de groupe installation Regroupement pour le nécessaire opération de groupe en le sélectionnant dans la liste déroulante : Somme, Moy., Compter, Mdans, Max., StDev, Var, D'abord, 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 certaine colonne X:Max.([ X]) – Min.([ X]).

Condition indique que ce champ sera utilisé pour définir les conditions de sélection des enregistrements. La condition de sélection elle-même est écrite dans la cellule de 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êtes avec regroupement d'enregistrements

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

5. Inclure des champs calculés dans un formulaire de demande

Générateur de requêtes .

Cliquez dans la ligne Champ selon la colonne vide du formulaire de demande. Entrez le nom de la colonne, suivi de deux points et d'une expression. Pour saisir 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 Opération de groupe et sélectionnez dans la liste déroulante Expression(si vous ne sélectionnez pas l'option Expression, une fois exécutée, la requête ouvre une boîte de dialogue Entrez la valeur du paramètre ou renvoie un message d'erreur).

Exécutez la requête.

EXEMPLE:

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

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

Riz. 17. Résultat d'une requête avec un champ calculé

6.Création d'une requête paramétrique

Créez une requête de sélection en mode Générateur de requêtes.

Entrez des chaînes dans l'une des cellules Condition de sélection un paramètre, pas une valeur. Par exemple, si vous sélectionnez des enregistrements par nom de famille, alors requête paramétrique sur le terrain Nom de famille dans les conditions de sélection, la phrase [indiquer le nom] peut être écrite entre crochets.

Sélectionner dans le menu Demande option Possibilités. Dans la boîte de dialogue Paramètres de la demande dans la colonne de gauche, saisissez 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.

Exécutez la demande d'exécution. Accéder affichera la fenêtre Entrez la valeur du paramètre, dans lequel entrez la valeur du paramètre souhaitée 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. Générateur de requêtes paramétriques

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

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

7. Création de requêtes croisées

1. Créez avec Designer nouvelle requête et inclure les tableaux nécessaires.

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

3. Sélectionnez une commande dans le menu Requête/Croix. Titre Designer va changer de Requête 1 : par échantillon sur 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 Ligne. Tableau croisé sélectionner une option En-têtes de ligne. Chaque champ qui est un en-tête de ligne doit avoir un paramètre d'opération de groupe dans la ligne Regroupement.

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

en ligne Tableau croisé sélectionner une option En-têtes de colonnes et en ligne Opération de groupe installation regroupement, ou en ligne Opération de groupe – Condition, en lignes Condition de sélection Et OUécrire expression logique.

6. Définissez le champ sur la base duquel la valeur totale sera créée. En ligne Tableau croisé choisir Signification. En ligne Opération de groupe sélectionnez la 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 et les colonnes sont les noms des fabricants.

Riz. 21. Générateur de requêtes croisées

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 d'une requête utilisée pour sélectionner des enregistrements dans des tables associées, 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).

Ouvrez la requête créée en mode designer, en mettant son nom en surbrillance dans la liste des requêtes de la fenêtre de la base de données et en cliquant sur le bouton Constructeur.

Sélectionnez une commande dans le menu Requête/Créer une table.Une boîte de dialogue s'ouvrira Création d'un tableau. Entrez le nom de la nouvelle table, laissez le bouton radio en position Dans la base de données actuelle.

Cliquez sur le bouton D'ACCORD. La requête de sélection est convertie en 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 d'exécuter la requête, un message apparaît pour vous 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ît 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 ; Un nouveau tableau devrait apparaître dans la liste.

9.Demande de mise à jour des dossiers

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

Sélectionnez une commande dans le menu Requête/Mise à jour. Dans le formulaire de demande de ligne Tri Et Sortie affichée sera remplacé par la chaîne Mise à jour.

En ligne Mise à jour spécifiez une expression qui représente la nouvelle valeur du champ actuel.

Si les informations ne sont pas mises à jour dans tous les enregistrements de la table, mais seulement dans une partie de celle-ci, saisissez une condition de sélection des enregistrements.

Complétez 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 initial.

Riz. 23. Mettre à jour le constructeur de demande

10.Demande de suppression d'enregistrements

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

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

Glissez et déposez des champs dans le formulaire de demande dont les valeurs seront utilisées pour sélectionner les champs. En ligne Conditions de sélection indiquer les critères de sélection.

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

Ouvrir fenêtre de base de données. Sélectionnez un onglet les tables.

Activez la demande de sélection créée et passez en mode Constructeur de requête.

Sélectionnez une commande dans le menu Requête/Suppression. Dans le formulaire de demande de ligne Tri Et Sortie affichée sera remplacé par la chaîne Suppression.

Cliquez sur le bouton Lancement barres d'outils. Une boîte de message apparaît demandant la confirmation de la suppression des entrées.

Suppression d'enregistrements dans le tableau " un", pour lequel dans le tableau « beaucoup" il existe des enregistrements associés, viole la règle d'intégrité des données. Par conséquent, l’option d’intégrité des données définie sur une relation entre les tables peut empêcher la suppression des enregistrements. Une suppression en cascade doit être appliquée.

EXEMPLE:

Riz. 24. Supprimer le constructeur de requête

11. Demande d'ajout d'enregistrements

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

Sélectionnez un 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. Choisir une option Constructeur.

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

Passer à la ligne Champ formulaire de demande de champs dont les valeurs seront incluses dans un autre tableau.

Sélectionner les commandes du menu Demander/Ajouter. Une fenêtre apparaîtra Ajout, où vous devez spécifier le nom de la table à laquelle vous êtes censé ajouter des données et à quelle base de données elle appartient. Si la base de données n'est pas celle actuelle, mais une autre, alors vous devrez préciser 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 Ajout, dans lequel vous devez indiquer les champs du tableau dans lesquels les données sont ajoutées. Si les noms de champs sont les mêmes UNAveccession s'insérera automatiquement dans la ligne Ajout noms de champs.

Exécutez la demande d'exécution. Découvrez comment le tableau a changé avec l'ajout de nouvelles lignes.

Si dans une demande de Ajout il y a des champs comme Comptoir, vous pouvez alors travailler avec ces champs de deux manières :

ne pas inclure les champs de type Comptoir dans les champs qui doivent être ajoutés ; puis dans le tableau où les données sont ajoutées, la numérotation de la séquence de données continuera, c'est-à-dire s'il existe un tableau des employés avec des nombres de 1 à 100 et ajoutez-y de nouvelles entrées, alors leur numérotation commencera à partir du numéro 101 ;

inclure des champs de type Comptoirà la liste de demandes en tant que champs ajoutés, les valeurs qu'elles avaient dans la table d'origine seront enregistré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. Un message d'erreur apparaît à l'écran avant l'exécution de la requête. S'il y a une erreur, sélectionnez le bouton Annuler et corrigez l'erreur.

Méthode 2 : faites un clic droit sur les valeurs du champ souhaité dans la zone ΣValues ​​et sélectionnez :

De plus, pour sélectionner une fonction différente dans la liste suggérée, configurez le format du champ et modifiez le nom du champ.

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

Ensuite, vous pouvez obtenir les informations. Lors de la création de graphiques, peu importe que le tableau comporte une bordure ou d'autres éléments de formatage. Introduction aux formules et fonctions Effectuer des calculs, qu'ils soient simples ou complexes, peut être fastidieux et prendre beaucoup de 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

Vous pouvez effectuer plusieurs calculs en utilisant le même champ source. Pour ce faire, vous devez ajouter le champ d'origine de la liste des champs du tableau croisé dynamique à la plage de valeurs Σ Valeurs [Σ

Values] plusieurs fois, puis ajustez les paramètres du champ. Cela vous permettra de voir simultanément à la fois le calcul lui-même et le calcul effectué sur cette base.

Ajouter des 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 ajouter des calculs supplémentaires, mais vous pouvez ajouter un champ calculé qui vous permet de saisir des formules pour calculer de nouvelles données basées sur des données existantes.

Feuille de travail sur les bases de données 1 Feuille de travail sur les bases de données 1 Créez un dossier « Bases de données » pour stocker toutes les données du cours. Crée une requête qui affiche les titres et les descriptions. La boîte de dialogue Gérer la base de données apparaît. A l'aide 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 ? Une feuille de calcul est un programme permettant d'effectuer des calculs mathématiques. Avec son aide, nous pouvons également effectuer des calculs lisibles sous la forme. Pour bénéficier d'actions spécifiques. La partie la plus exigeante en main-d’œuvre est la préparation des données. Problème 1 Groupe de mathématiques 2 Suivez ces étapes tour à tour. Exécutez la table.

L'insertion d'un champ calculé directement dans un tableau croisé dynamique est meilleure solution. Il n'est pas nécessaire de gérer les formules ni de s'inquiéter de l'extensibilité du tableau à mesure que la source de données s'agrandit ou se modifie, et le tableau croisé dynamique reste suffisamment flexible pour modifier les définitions de champs.

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é technologique de Poméranie occidentale à Szczecin Faculté de technologie maritime et de transport Département des structures, de la mécanique et de la technologie Navire dans un laboratoire de recherche opérationnelle. Les tableaux croisés dynamiques sont utilisés pour analyser rapidement les 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 maintenir la lisibilité des données, vous devez laisser l'option Nouvelle feuille, qui insérera nouvelle feuilleà la table.

Microsoft Excel 2010. Niveau 2. Fonctionnalités avancées

Ainsi, les avantages de l'ajout de vos propres champs calculés à un tableau croisé dynamique sont clairs :

 Éliminez les erreurs potentielles dans les formules et les références de cellules.

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

Il existe une nouvelle feuille de tableau croisé dynamique qui est vide. Sur le côté droit se trouve une liste de champs, qui contient toutes les rubriques de notre tableau, et un plan avec ses zones. Disons que nous voulons voir qui est le best-seller. 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 le plus vendu, annulez simplement l'achat et marquez-le auprès du vendeur. Nous recevons immédiatement un nouveau rapport dont vous pouvez lire le résultat.

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

 Offre la flexibilité nécessaire pour modifier les calculs lorsque les définitions d'éléments changent.

 Capacité à gérer efficacement les 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 qu'il affiche, mais ces totaux rendent souvent l'analyse difficile. Nous pouvons également supprimer le total et modifier la présentation du rapport.

Pour modifier rapidement le rapport et voir comment il a vendu Dobrovalsky à d'autres pays, modifiez simplement l'ordre des conditions dans les champs en capturant le vendeur et en le traînant à travers l'État. Hormis l’Irlande et 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. Sous l'onglet Options, dans le groupe Calculs, développez la liste Champs.

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

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

 Dans le champ Nom, saisissez le nom du nouveau champ.

 Dans le champ Formule, créez une formule en commençant par le signe = et en sélectionnant

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

Si nous voulons imprimer nos coordonnées, nous prendrons le champ Vendeur de la zone Lignes et le déplacerons vers la zone Colonne. Cela entraînera la répartition du tableau 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 du nombre" et sélectionnez par exemple la devise appropriée.

Les filtres sont également utiles. Lorsqu'ils sont ajoutés à cette zone, les champs du fournisseur apparaissent dans le coin supérieur gauche d'une liste déroulante dans 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 souhaitons regrouper les données.

 Cliquez sur Ajouter.

La définition de l'apparence d'un champ calculé, comme de tout autre champ, s'effectue dans la fenêtre

Paramètres du champ de valeur.

Modification d'un champ calculé

1. Sous l'onglet Options, dans le groupe Calculs, développez la liste Champs.

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

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

Nous célébrons les mois et nous les embrassons. Nous recevrons 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 obtenons l'apparition du rapport. Le regroupement peut également être utilisé pour les nombres.

Quand tu ouvres menu contextuel, le champ Lignes et l'option Groupe affichent une fenêtre différente de celle si vous regroupiez les 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 fixons à 500, le premier groupe contiendra donc toutes les transactions d'une valeur comprise entre 0 et 500 zlotys.

En fait, une telle possibilité existe et elle se réalise de trois manières.

Vous pouvez parfaire vos connaissances sur les bases des tableaux croisés dynamiques.

Première façon. Faites des calculs dans un tableau avec les données originales. La méthode, à vrai dire, n’est pas une fontaine – au mauvais endroit.

Deuxième façon. Construire des calculs à côté d’un tableau croisé dynamique n’est, encore une fois, pas la meilleure méthode. Si le tableau croisé dynamique change (et cela vaut la peine d'utiliser les tableaux croisés dynamiques exactement au moment où nous attendons des changements dans le tableau).

Troisième voie. Intégrez les champs calculés dans le tableau croisé dynamique lui-même. Mais c'est un bon moyen. Cela se fait comme suit. Lorsque le tableau croisé dynamique a déjà été 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 Possibilités, alors Des champs,Articles, &Ensembles dans le bloc Calculs, sélectionnez dans le menu déroulant Calculéchamp. Dans Office 2007, ce bouton se trouvait dans 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 du champ souhaité. Sur le terrain 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 un tableau avec un nouveau champ de calcul. La table est prête !

Le fichier Excel peut être téléchargé à partir d'ici.

Fait intéressant. Lors de l'utilisation de la troisième méthode, le programme effectue effectivement le calcul 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 RECHERCHEV (RECHERCHEV) est probablement la fonction la plus utile après les opérations arithmétiques simples...

• Comment Excel recalcule un classeur et pourquoi la volatilité doit être évitée...

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

• Comment créer rapidement un graphique en cascade

  Excel ne propose pas de graphiques en cascade standard. Par conséquent, pour créer ce type de diagrammes, le SIG est généralement utilisé...

• Fenêtre de contrôle pour le suivi des résultats

  Il arrive souvent que nous travaillions avec de grands tableaux qui calculent finalement 1 à 2 indicateurs, mais qui 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 dans le but de...

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

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

• Comment élever un nombre à une puissance et en extraire la racine

  Saviez-vous que pour élever un nombre à une puissance dans Excel, il faut Caractère spécial^ (sur six en anglais...

Les champs calculés sont conçus pour afficher les valeurs des expressions basées sur les données sources dans les rapports. Les champs calculés dans les rapports peuvent être utilisés pour obtenir des totaux ou effectuer des calculs spéciaux, par exemple pour afficher le prix total d'une commande en calculant le produit de la quantité d'un article et du prix d'un article. Regardons la procédure de création des champs calculés en utilisant l'exemple du rapport « Articles en entrepôt » créé précédemment.

Pour ajouter un champ calculé « Montant » à un rapport :

1. Ouvrez le rapport Articles dans l'entrepôt en mode Conception.
2. Dans la boîte à outils, cliquez sur le bouton Une inscription(Étiquette) et placez le nouveau texte à droite des « Unités en stock » dans la section d'en-tête. Entrez le mot « Somme » comme texte d'étiquette.
3. Pour créer un champ calculé Somme, cliquez sur le bouton Champ(Zone de texte) dans la boîte à outils et ajoutez une zone de texte à droite de la zone de texte UnitsInStock dans la section de données. Supprimez l'étiquette du nouveau champ de texte.
4. Cliquez dans le champ de texte et saisissez l'expression suivante comme valeur :

   = [En Stock]* [Prix] (= * ).

   Ouvrez la fenêtre des propriétés champs en double-cliquant dessus, et sur l'onglet Mise en page(Format) dans la liste déroulante des propriétés Format du champ(Format) sélectionnez une valeur Monétaire(Devise).

Commentaire

Il existe plusieurs manières de saisir une expression dans le champ de texte : sélectionnez-la et appuyez sur la combinaison de touches +pour passer en mode édition du contenu du champ, ou double-cliquez sur le champ pour afficher une boîte de dialogue Propriétés(Propriétés), puis entrez l'expression comme valeur de propriété Données(Source).

Riz. 6.27.

1. Répétez les étapes 3 et 4 pour créer un champ de texte dans la section commentaires du groupe Catégorie, mais saisissez ce qui suit comme expression de sous-total : =Sum([InStock] * [Price]) (=Sum( * )). Cliquez sur le bouton Audacieux(Gras) dans la barre de mise en forme pour mettre le texte du nouveau champ en gras. Ouvrir la boîte de dialogue Propriétés(Propriétés) et ouvrez l'onglet qu'il contient Autre(Autre), dans le domaine Nom(Nom) Saisissez un nom pour la zone de texte Somme. Développez ensuite l'onglet Mise en page(Format) et dans la liste déroulante des propriétés Format du champ(Format) sélectionnez une valeur Monétaire(Devise).
2. Étant donné que la largeur de la zone de données de notre rapport a augmenté, réduisez la largeur des marges de la page afin que tous les champs tiennent sur une seule page. Pour ce faire, sélectionnez la commande Fichier, mise en page(Fichier, Mise en page) et modifiez les largeurs de marge gauche et droite par défaut à 10 mm. (Voir « Affichage et impression de rapports » dans ce chapitre.)
3. Cliquez sur le bouton Voir(Afficher) dans la barre d'outils Concepteur de rapports(Conception de rapports) pour évaluer les résultats de votre travail. Le rapport ressemblera à celui illustré à la Fig. 6.27. Aux données précédentes, la colonne la plus à droite a été ajoutée avec les valeurs du coût de chaque produit dans la quantité disponible dans l'entrepôt et les sous-totaux du coût des marchandises pour chaque catégorie.