Chatalova L.M.

Travail de laboratoire n°8

"L'informatique enEXCELLER. Utilisation des fonctions"

Objectif du travail - explorez les capacités des fonctions Excel standard et acquérez des compétences pratiques dans l'utilisation du mécanisme de saisie des arguments de fonction en utilisant l'exemple des fonctions logiques, des fonctions de lien et de tableau, ainsi que certaines fonctions financières.
Excel possède plus de trois cents fonctions intégrées qui peuvent être utilisées séparément ou dans le cadre d'une formule. Toute fonction est une formule pré-créée qui effectue certaines opérations. Pour saisir des fonctions, utilisez les commandes de section bibliothèques de fonctions onglets FORMULES Rubans. Lorsque vous sélectionnez « insérer une fonction » (Maj + F3), la fenêtre étape 1 de « Function Wizard » apparaît, dans laquelle vous sélectionnez une fonction spécifique (ici vous pouvez obtenir de l'aide sur la fonction sélectionnée). Après avoir appuyé sur la touche Ok, la fenêtre de l'étape 2 apparaît, dans laquelle les lignes avec les arguments sont renseignées. Les données saisies sont affichées à droite et le résultat est affiché à la fin. A ce stade, vous pouvez « travailler sur les erreurs de saisie ». La fonction a le format suivant :
Nom de la fonction > (Argument 1 ; Argument 2 ;…; Argument N ) ,
Où ";" -délimiteur. Délimiteur"point-virgule" est défini par Windows.

Argument– c'est : 1) Un nombre ou un texte, 2) un lien vers une adresse de cellule, 3) Une plage de cellules, 4) Une expression arithmétique (par exemple, A7/A10*35), 5) Une autre fonction

Excel contient les catégories suivantes de fonctions standard :

Financier, Date et heure, Mathématique, Statistique, Liens et tableaux, Travailler avec une base de données, Texte, Logique, Vérification des propriétés et des valeurs, Ingénierie, Analytique.

Travail de laboratoire n°1. Formules et fonctions MS Excel

Préparez les déclarations pertinentes en utilisant les recommandations données.

Exercice 1. Créez des annonces pour la vente d'appartements selon l'échantillon (Fig. 1).

Riz. 1. Annonces d'appartements à vendre

Regroupez les données disponibles par appartement sous forme de liste (Fig. 2).

Riz. 2. Données sur les appartements à vendre

Vers la cellule G2 entrez la formule :

A2&" carré, par "&B2&", zone : "SD2&", "&E2&"étage, "&TEXT(C2;"# ##0р.")&", "&ECJIИ(F2="+"; "téléphone"; "pas de téléphone"

Expliquez cette formule à l'aide de l'aide.

Pour la gamme G3:G5 utilisez le marqueur de saisie semi-automatique ou copiez cette formule.

Si nécessaire, formatez les publicités reçues à l'aide de la commande Formater | Format automatique.

Tâche 2. Créer et remplir une fiche de réévaluation des immobilisations de production selon le formulaire présenté à la Fig. 3.

Vers la cellule A1 entrez le nom de la déclaration.

Aux cellules A4:F4 saisissez les noms des champs du relevé : Nom de l'objet, Valeur comptable (BC), Dépréciation de l'objet (IO), Valeur résiduelle (RE), Valeur totale de remplacement (RFC), Valeur résiduelle de remplacement (res). Champ Nom de l'objet comprend les lignes suivantes : Service de gestion et de marketing, service des transports, atelier de montage, atelier de finition, entrepôt n°1, entrepôt n°2, entrepôt n°3, Total.

Formules de calculs :

OS = BS - IO

VPS = BS*K

BOS = OS * K

où k est un coefficient égal à :

3.3 - si BS est inférieur ou égal à 650 millions de roubles ;

4.2 - si le BS est supérieur à 650 millions de roubles, mais inférieur à 1 000 millions de roubles ;

5.1 - si BS est égal à 1000 millions de roubles. ou plus.

Pour générer des calculs automatiques, utilisez les formules suivantes :

pour cellule D5: =B5-C5

pour cellule E5: =В5*IF(В5<=650;3,3;ЕСЛИ(И(В5>b50;B5<1000);4,2;5,1))

pour cellule F5: =D5*SI(B5<=650;3.3;ЕСЛИ(И(В5>650;B5<1000);4,2;5.1))

La ligne résultante peut être obtenue en utilisant, par exemple, pour une cellule À 12 formules :

SUM(B5:B11), ou vous devez sélectionner la plage de cellules B12:F12 et utiliser la fonction de somme automatique (cliquez sur le bouton Somme automatique dans la barre d'outils).

Formatez les résultats obtenus dans le tableau, ainsi que le nom de l'instruction.

Riz. 3. État de réévaluation des immobilisations de production

Tâche 3. Générer et remplir une fiche de reporting pour le fonctionnement d'un réseau de clubs informatiques selon le formulaire illustré à la Fig. 4.

Riz. 4. Énoncé des travaux du réseau des clubs informatiques

Vers la cellule A1 entrez le nom de la déclaration.

Aux cellules AZ : Nouvelle-Zélande saisissez les noms des champs du relevé : club, janvier, février, mars, Revenu total, Lieu, Revenu moyen, pourcentage. Le champ Club comprend les lignes suivantes : Altair, Gruvit, Polygon, Helax, Zvezda, Hexen, Antey, Arsenal, Arena, Dugout, Total.

Les formules de base pour les calculs, qui sont copiées pour des calculs similaires par ligne, sont présentées dans le tableau 1.

Formules de calcul

Formatez la déclaration reçue.

Tâche 4. Créez un relevé sur la feuille de travail « Calcul des salaires des employés du département de recherche et de conception « Alpha » (Fig. 5).

Riz. 5 Feuille de paie

Placez le nom de l'instruction dans la cellule A2- Calcul des salaires des salariés du bureau d'études "Alpha", centré à gauche (par exemple, par la commande Formater | Cellules| languette Alignement ou le bouton correspondant Aligné à droite ( sur la barre d'outils).

Aux cellules AZ:KZ saisir les noms des champs du relevé : N° pp, Nom I.O., Fonction, Taux tarifaire, Expérience, k, Augmentation pour expérience, Total, Pourcentage d'impôt, Retenue, Paiement.

dans l'en-tête de la déclaration - pour chaque colonne - créez des notes cachées. Les notes sont créées par la commande Insérer | Note(vous pouvez également utiliser la barre d'outils Révision, ce qui ajoute la commande Voir| Barres d'outils| Revoir):

N° pp - numéro d'employé du département ;

Nom de famille agissant - tous les noms des personnes travaillant dans le bureau d'études et de conception sont inscrits ;

Poste - poste occupé au moment de remplir le relevé ;

le taux de droit est l'équivalent monétaire du poste occupé ;

Expérience - le nombre total d'années travaillées au moment de remplir la déclaration est inscrit ;

k - coefficient d'ancienneté ;

prime d'ancienneté - équivalent en espèces pour l'ancienneté ;

total - calcul du salaire en tenant compte du taux tarifaire et de l'ancienneté ;

pourcentage d'impôt - détermine le pourcentage des contributions au budget ;

Retenir - l'équivalent monétaire des contributions au budget ;

Paiement - le montant destiné au paiement.

Lorsque vous effectuez des calculs dans le relevé, tenez compte des éléments suivants :

k, Bonus d'expérience, Total, Pourcentage d'impôt, Retenue, paiement - sont calculés à l'aide des formules appropriées, en utilisant le remplissage automatique ou en copiant la formule.

Le coefficient k est attribué sur la base du calcul suivant :

0,1 - travaillé jusqu'à 5 ans inclus, 0,2 - de 5 à 10 ans inclus, 0,25 - de 10 à 15 ans inclus, 0,3 - plus de 15 ans. Formule cellulaire F4:

SI(E4<=5;0,1;ЕСЛИ(И(Е4>5;E4<=10);0,2;ЕСЛИ(И(Е4>dix; E4<=15);0,25;0,3)))

prime d'ancienneté - l'équivalent monétaire de l'ancienneté. Formule cellulaire G4:

G4:

(saisi en utilisant le Format | Cellules| Onglet Numéro, dans la liste Formats de nombres choisir Tous les formats et sur le terrain Taper entrez le format spécifié).

total - le taux tarifaire tenant compte de l'ancienneté. Formule cellulaire H4:

Format numérique personnalisé pour la cellule H4:

Pourcentage de taxe : prend en compte : 2% - l'accumulation (total) peut aller jusqu'à 7 000 roubles. inclus, 10% - plus de 7 000 roubles. jusqu'à 10 000 roubles. inclus, 20% - plus de 10 000 roubles. jusqu'à 25 000 roubles. inclus, 35% - dépassant 25 000 roubles. Formule cellulaire I4:

SI(H4<=7000;0,02;ЕСЛИ(И(Н4>7000;H4<=10000);0,1;ЕСЛИ(И(Н4>10000;H4<=25000);0,2;0,35)))

Format numérique pour la cellule I4- Intérêt.

Retenir - l'équivalent en espèces des impôts. Formule cellulaire J4:

Format numérique personnalisé pour la cellule J4:

Paiement - montant à émettre : Total sans retenue.

Exigences pour la colonne expérience :

Créez un format de données personnalisé qui prend en compte l'expérience professionnelle : jusqu'à 5 ans - les données sont présentées en jaune, de 5 à 10 - en bleu, de 10 à 15 - en vert, au-dessus de 15 - en rouge.

Utilisez la commande Formater | Cellules et entrez un format personnalisé pour la cellule E4:

[Rouge]# ##0 ;

et utilisez également la commande Formater | Mise en forme conditionnelle.

Si vous saisissez un nombre d'années négatif, une fenêtre correspondante devrait apparaître. Pour chèque

entrez des nombres utilisez la commande Données | Vérifier| languette Message d'erreur.

Pour le champ Tarif, afficher un message permanent : Taux tarifaire. Soyez prudent lors de la saisie de votre taux tarifaire pour obtenir lequel, utilisez la commande Données | Examen| languette Message à saisir.

Si vous saisissez des valeurs négatives dans la colonne taux tarifaire, un avertissement correspondant apparaît : Le taux de droit ne peut pas être négatif, qui est généré via un format personnalisé :

# ##0.00r.;[Rouge]"Le taux tarifaire ne peut pas être

négatif!"

Solution

Pour les principaux versements d'un prêt, qui sont remboursés en versements égaux à la fin ou au début de chaque période de facturation, MS Excel XP utilise la fonction :

OSPLT (Taux, Période, Nper, Ps, Bs)

(dans les versions antérieures de MS Excel, cette fonction s'appelait OSPLT) Dans notre cas, la fonction SPPLT a la forme : OSPLT(12%, 4, 5, 1000000000)

La saisie des données et les calculs sont effectués conformément à la Fig. 4.4.

Riz. 11. Calcul des remboursements du principal du prêt

Vers la cellule À 8 la formule est introduite :

OSPLT(В5;В6;В4;ВЗ)

Solution

Pour calculer la valeur d'un versement de rente périodique constant (par exemple, des versements réguliers de prêt) à un taux d'intérêt constant, utilisez la fonction PMT (dans les versions antérieures - la fonction PPLAT) :

PMT (Taux ; Nper ; Bs ; Ps ; Type)

Dans notre cas, la fonction PMT a la forme :

PMT(10%/12; 20*12; -(350000* (1-25%))) - paiements mensuels ;

PMT(10%; 20; -(350000* (1-25%))) - paiements annuels.

La solution au problème est présentée sur la Fig. 12 et 13.

Riz. 12. Calcul du prêt hypothécaire

Riz. 13. Formules de calcul des versements hypothécaires

Tâche 3. Déterminez quel montant sera sur le compte si 52 000 roubles. mis pendant 20 ans à 11% par an. Les intérêts sont calculés mensuellement.

Solution

Pour calculer la valeur future d'un montant de dépôt unique, des intérêts composés sont utilisés et la formule de calcul est basée sur la fonction BS (dans les versions antérieures - la fonction BZ) :

BS (taux ; Nper ; Plt ; Ps ; Type)

Pour notre tâche, la fonction BS prendra la forme :

BS(11%/12;20*12;,-52000)

La solution au problème est présentée sur la Fig. 14, et la formule de la cellule est B26:

BS(B22/B23;B24*B23;;-B21)

Riz. 14. Calcul de la valeur future du dépôt

Tâche 4. Obligation d'une valeur nominale de 200 000 roubles. libéré pendant 7 ans. La procédure suivante de calcul des intérêts est prévue : la première année - 11 %, les trois années suivantes - 16 % chacune, les années restantes

Solution

Pour calculer la valeur accumulée d'une obligation à un taux d'intérêt composé, utilisez la fonction :

BZSCHEDULE(Primaire ; Plan)

Pour notre tâche, la fonction prend la forme :

BZRASPIS(200 000 ; (11 % ; 16 % ; 16 % ; 16 % ; 20 % ; 20 % ; 20 %))

La solution est présentée sur la Fig. 15, et la formule de calcul dans la cellule B42:

HORAIRE BZ(B30,B34:B40)

De même, vous pouvez utiliser les fonctions intégrées de MS Excel pour d'autres calculs financiers.

Riz. 15 Calcul de la valeur accumulée d'une obligation à un taux d'intérêt composé

Solution

Le taux de rotation interne des investissements est calculé à l'aide de la fonction IRR (dans les versions antérieures - IRR) : IRR (Valeurs ; Hypothèses)

Riz. 16 Feuille de travail pour déterminer les coûts initiaux du projet

Les données initiales sont saisies conformément à la Fig. 16. Initialement, pour le calcul, le montant des coûts du projet est choisi arbitrairement (la cellule de ce montant peut même être laissée vide) et des calculs sont effectués.

Vers la cellule À 12 entrez la formule =VSD(B4:B9).

Un exemple est montré sur la Fig. 18.

Riz. 17 Fenêtre de sélection des paramètres

Riz. 18 Montant calculé des coûts initiaux du projet

Un exemple de calcul de l'efficacité d'investissements en capital inégaux

Tâche 2. On vous demande de prêter 15 000 roubles. et ils promettent de rendre 3 000 roubles par an, 5 000 roubles en deux et 9 000 roubles en trois. À quel taux d’intérêt cette opération est-elle rentable ?

Solution

Pour résoudre ce problème, vous devez utiliser la fonction NPV et l'outil Sélection du paramètre :

NPV(Taux; valeur 1; valeur 2; . . .)

Les données initiales sont saisies conformément à la Fig. 19. Initialement, un pourcentage arbitraire du taux d'actualisation annuel est sélectionné pour le calcul (la cellule avec cette valeur peut même être laissée vide) et des calculs sont effectués. Vers la cellule À 9H entrez la formule : = NPV(B6,B2:B4)

Riz. 19. Feuille de travail pour résoudre le problème des investissements inégaux

Vers la cellule C7 vous pouvez saisir la formule suivante :

SI(B7=1;"année";SI(ET(B7>=2,B7<=4);"года";"лет"))

Sur le terrain Définir sur la cellule entrer À 9H, c'est-à-dire l'adresse de la cellule dans laquelle il faut obtenir la valeur souhaitée du montant de la transaction (15000) ;

Sur le terrain Signification saisissez 15000, c'est-à-dire la valeur souhaitée du montant de la transaction ;

Sur le terrain Changer la valeur d'une cellule entrez l'adresse de la cellule - B8, dans laquelle, à l'aide de l'outil Sélection des paramètres le taux d'intérêt requis pour le problème considéré sera obtenu (s'il existe).

La solution finale au problème est présentée dans la Fig. 20.

Riz. 19. Fenêtre Sélection des paramètres pour le problème des investissements inégaux en capital

Riz. 20 Taux d'intérêt optimal

Travail de laboratoire n°6. Table de recherche

Table de recherche vous permet d'analyser les changements dans le résultat pour une plage arbitraire de données initiales. Vous pouvez avoir plusieurs tables de recherche sur une seule feuille de calcul. Cela permet d'analyser simultanément diverses formules et données statistiques.

Une table de recherche peut être utilisée pour :

Modifiez une valeur initiale tout en visualisant les résultats d'une ou plusieurs formules ;

- modifie deux valeurs d'origine en affichant les résultats d'une seule formule.

Utilisation d'une table de recherche avec une variable changeante et plusieurs formules

Considérons cette technique en utilisant l'exemple du calcul des mensualités d'un prêt (le calcul s'effectue à l'aide de la fonction PMT) et des intérêts (fonction PROCPAT) :

La résolution de ce problème implique les étapes suivantes :

1. Créez ou accédez à la feuille de travail où le problème analysé sera résolu.

2. Organisez l'interface de manière à ce que toutes les données saisies soient claires pour l'utilisateur :

Les signatures et données nécessaires sont saisies dans les cellules correspondantes de la feuille de calcul (Fig. 21).

Vers la cellule À 5 heures- formule: =PLT ($B$4/12,$B$3*12,$B$2)

Vers la cellule D6- formule: =PROCAY($D$4,$D$5,$D$3,$D$2)

Riz. 21 Préparation des données initiales

Astuce : Lors de la résolution de problèmes impliquant l'utilisation d'une table de recherche, il est recommandé d'utiliser l'adressage absolu des cellules dans les formules. Cela aide les cellules de la feuille de calcul à effectuer correctement les calculs.

3. Après avoir préparé les données initiales, rendez-vous à l'endroit de la feuille de calcul où se situeront les valeurs calculées en fonction de l'évolution d'une variable et des différentes formules calculées (Fig. 22).

Dans la ligne supérieure du futur tableau, au-dessus de l'emplacement des résultats, indiquez l'adresse de chaque formule pour laquelle vous souhaitez obtenir une liste de résultats. Au lieu d'une adresse, vous pouvez placer la formule elle-même dans une cellule (cellules B10 Et C10 contiennent des formules).

Sur la gauche se trouvent les différentes valeurs de données d'entrée qui doivent être testées.

Riz. 22 Préparation d'une plage de variables et de formules de calcul pour l'utilisation d'une table de recherche unidimensionnelle

4. Sélectionnez les cellules qui contiendront le tableau. Dans ce cas, la colonne la plus à gauche de ce tableau doit être la colonne des valeurs initiales et la ligne la plus haute doit être la ligne des formules analysées. Le résultat sera placé dans des cellules vides.

5. Utilisez la commande Données | Table de recherche et dans la boîte de dialogue Table de recherche(Fig. 23) indiquent où et quelles valeurs doivent être substituées.

Dans notre exemple, substitution des valeurs de taux d'intérêt (colonne des valeurs initiales A10:A19) se produit dans la cellule À 4 HEURES, puisque cette cellule indique initialement le taux d'intérêt inclus dans les formules calculées. Les résultats du calcul seront placés dans les cellules vides de la plage sélectionnée (Fig. 24).

Riz. 23 Utiliser une table de recherche

Riz. 24 Valeurs calculées pour une table de recherche unidimensionnelle

Utilisation d'une table de recherche avec deux variables changeantes et une formule

Considérons cette technique en utilisant l'exemple du calcul des mensualités d'un prêt en fonction de différentes durées de remboursement et de différents taux d'intérêt. Résoudre le problème implique

prochaines étapes:

1. Organisez sur la feuille de calcul l'interface utilisateur appropriée pour un certain ensemble de données d'entrée (Fig. 25) :

Taux d'intérêt spécifique - 3% (cellulaire À 4 HEURES);

Durée de remboursement spécifique - 3 ans (cellule VZ);

Formule cellulaire À 5 heures:

PMT(4 $B$/12 ; $B$3*12 ;$B$2)

Riz. 25. Préparation des données de tâche

2. Préparez le tableau suivant (Fig. 26) :

Placez les données modifiables dans la colonne de gauche et la ligne du haut - dans notre cas, les valeurs du taux d'intérêt (cellule À 4 HEURES) sont situés dans la gamme B10:B14, et les valeurs de maturité (cellule VZ) - dans le périmètre C9:F9;

À l'intersection de la ligne et de la colonne dans le coin supérieur gauche, placez la formule souhaitée ou un lien vers celle-ci (cellule À 9H contient la formule cellulaire À 5 heures, dans lequel un adressage absolu des cellules est requis).

Riz. 26. Préparation d'une plage pour utiliser une table de recherche bidimensionnelle

3. Sélectionnez une plage de cellules, qui est une zone rectangulaire et contient une formule de calcul, des données modifiables pour deux variables et des cellules pour les résultats du calcul.

4. Exécutez la commande Données | Table de recherche et dans la fenêtre qui apparaît (Fig. 27) indiquez où et quelles valeurs doivent être substituées.

Riz. 27 Utilisation d'une table de substitution lors d'un calcul utilisant deux paramètres

Dans l'exemple considéré, substitution des valeurs de taux d'intérêt (colonne des valeurs initiales B10:B14) se produit dans la cellule À 4 HEURES, puisque cette cellule indique initialement la valeur du taux d'intérêt inclus dans la formule calculée, et la substitution des valeurs de période de remboursement (ligne de valeur C9:F9) - dans une cellule VZ. Les résultats du calcul seront placés dans les cellules vides de la plage sélectionnée (28).

Riz. 28.Données calculées à l'aide d'une table de recherche bidimensionnelle

Commentaire: Après avoir construit une table de recherche, vous ne pouvez pas modifier une formule individuelle dans la table. Les valeurs des données dans le tableau peuvent être modifiées en modifiant les valeurs des données sources dans la colonne de gauche et la rangée du haut.

Données initiales : les coûts du projet s'élèveront à 700 millions de roubles. Les revenus attendus pour les 5 prochaines années seront respectivement de 70, 90 300 250 et 300 millions de roubles. Évaluer la situation économique

faisabilité du projet en termes de taux de rotation des investissements, si le taux de rentabilité du marché est de 12 %. Considérez également les options suivantes (les coûts du projet sont représentés par un nombre avec un signe moins) :

600; 50;100; 200; 200; 300;

650; 90;120;200;250; 250;

500, 100,100, 200, 250, 250.

Riz. 29 Fenêtre Gestionnaire de scripts

Solution

Pour calculer le taux interne de rotation des investissements (taux de rendement interne), la fonction TRI est utilisée :

VSD (Valeurs ; Hypothèses)

Dans notre cas, la fonction pour résoudre le problème utilise uniquement l'argument Valeurs dont l'un est nécessairement négatif (coûts du projet). Si le taux de rotation des investissements internes est supérieur au taux de rendement du marché, alors le projet est considéré comme économiquement réalisable. Dans le cas contraire, le projet doit être rejeté.

1. La solution est présentée sur la Fig. 30. Formules de calcul :

dans une cellule À 11 HEURES:=VSD(B75:B80)

dans une cellule C11 :=IF(B84>B82 ; « Le projet est économiquement réalisable » ; « Le projet doit être rejeté »)

Riz. 30. Calcul du taux de rotation interne des investissements

2. Considérez cet exemple pour toutes les combinaisons de données d'entrée. Pour créer un script, vous devez utiliser la commande Services | Scénarios| bouton Ajouter(Fig. 31).

Riz. 31. Ajout d'un script pour la première combinaison de données sources

Après avoir cliqué sur le bouton OK, il devient possible de saisir de nouvelles valeurs pour les cellules en cours de modification (Fig. 32).

Riz. 32. Fenêtre de modification des valeurs des cellules de script

Pour enregistrer les résultats du premier scénario, il n'est pas nécessaire de modifier les valeurs des cellules ; cliquez simplement sur OK pour confirmer les valeurs par défaut et quitter la fenêtre. Gestionnaire de scripts(riz . 33).

Riz. 33 Fenêtre Gestionnaire de scripts avec le premier scénario enregistré

3. Pour ajouter de nouveaux scénarios à la tâche considérée, cliquez simplement sur le bouton Ajouter dans la fenêtre Gestionnaire de scripts et répétez les étapes ci-dessus en modifiant les valeurs dans les cellules de données source (Fig. 34).

En figue. 34 scénario Speed_of_1 correspond aux données (-700 ; 70 ; 90 ; 300 ; 250 ; 300), scénario Speed_of_2 correspond aux données (-600 ; 50 ; 100 ; 200 ; 200 ; 300), scénario Speed_of_W- data (-650 ; 90 ; 120 ; 200 ; 250 ; 250), scénario speed_rev_4- data (-500, 100, 100, 200,

En appuyant sur le bouton Retirer, Vous pouvez afficher les résultats du calcul pour la combinaison correspondante de valeurs initiales sur la feuille de calcul.

Riz. 34. Fenêtre Gestionnaire de scripts avec des scripts ajoutés

en calculant le taux de rotation des investissements

Riz. 4.31. Ajout de cellules de résultat à une fenêtre Rapport de scénario

4. Pour recevoir un rapport final pour tous les scénarios ajoutés, cliquez sur le bouton Rapport dans la fenêtre Gestionnaire de scripts. Dans la fenêtre qui apparaît Rapport de scénario(Fig. 35) sélectionnez le type de rapport requis et fournissez des liens vers les cellules dans lesquelles les fonctions résultantes sont calculées. Lorsque vous cliquez sur le bouton OK, un rapport sur les scénarios s'affiche sur la feuille correspondante du classeur (Fig. 36 et Fig. 37).

Riz. 36 Type de rapport Structure

Riz. 37 Type de rapport Tableau croisé dynamique selon des scénarios de calcul du taux de rotation des investissements

Solution

Pour terminer la tâche :

1. Saisissez les données dans la feuille de calcul conformément à la Fig. 38.

Fig.38 Préparation des données pour la cartographie

2. Sélectionnez la plage avec la souris A5:B12 et exécutez la commande Insérer | Diagramme ou cliquez sur le bouton Assistant Graphique dans la barre d'outils Standard.

Choix du type de graphique - compte tenu du fait que l'exemple propose des données statistiques discrètes, le type de graphique approprié est le type histogramme.

Quelle série est sélectionnée comme valeur de données (remplissez les champs Rangée Et Valeurs). Dans notre cas, sur le terrain Rangée entrez le volume dans le champ Valeurs- gamme 5 $ : 12 $ ;

D quelle ligne servira d'étiquettes d'axe X- sur le terrain Étiquettes de l'axe X entrez l'année et indiquez la plage 5 $AU : 12 $AU.

Riz. 39. Définition des séries de données

4. Formatez le diagramme obtenu à l'aide du menu contextuel de chaque élément (Fig. 40).

Riz. 40. Diagramme construit Volume des ventes

Tâche 2. Représentez graphiquement la fonction : à= cos 3 (πx).

Solution

Le résultat de cet exemple est présenté sur la Fig. 41.

Pour terminer la tâche :

1. Définir la portée X en saisissant les données initiales : 0 et 0,1, puis à l'aide du marqueur de remplissage automatique, préparez toute la plage R7 : R27.

2. Vers la cellule À 7 HEURES entrez la formule :

=(SOZ(PI()*A7))^3 et copiez-le dans la plage Q7 : Q27.

3. Représentez graphiquement la fonction à l'aide de l'assistant graphique.

4. Formatez le graphique obtenu.

Riz. 41 Exemple de tracé d'une fonction

Solution

A1:J35, A1:J1)

2. Créez une gamme de critères pour le filtre avancé conformément à la Fig. 43.

Riz. 43. Gamme de critères pour un filtre avancé pour le problème des voitures blanches et noires

3. Exécutez la commande

4. Les données filtrées sont présentées sur la Fig. 44.

Riz. 44. Données pour le problème des voitures blanches et noires, sélectionnées par un filtre avancé

Tâche 2. Déterminez si la liste (voir Fig. 42) contient des voitures dont l'année de fabrication est supérieure à 2000 et dont le kilométrage est supérieur à 100 000 km, mais inférieur à 100 000 km, ou des Mercedes noires dont le prix est supérieur à 20 000 $. e., mais moins de 30 000 USD. e.

Solution

1. Ouvrez la liste à filtrer (la liste se situe dans la plage A1:JЗ5, ligne d'en-tête - à portée A1:J1)

MZ:M4. Vers la cellule Ministère de la Santé entrez Condition. Vers la cellule M4 entrez la formule :

OU(ET(G2>10000;G2<100000;D2>1990);I(C2="Mercedes";F2="Noir";H2>20000;H2<30000))

3. Exécutez la commande Données | Filtrer | Filtre avancé.

4. Les données filtrées sont présentées sur la Fig. 45.

Riz. 45. Données pour le problème de kilométrage, sélectionnées par un filtre avancé

Tâche 3. Identifiez les voitures blanches ou rouges dont le prix est inférieur au prix moyen de toutes les voitures et dont le kilométrage est supérieur ou égal au kilométrage moyen de toutes les voitures (voir Fig. 42).

Solution

1. Ouvrez la liste à filtrer (la liste se situe dans la plage A1:JЗ5, ligne d'en-tête - dans la plage A1:J1)

2. Générez un critère calculé pour le filtre avancé dans la plage L1:L2. Vers la cellule L1 entrez Condition. Vers la cellule L2 entrez la formule :

ET(OU(G2="blanc";G2="rouge");H2<СРЗНАЧ($Н$2:$Н$133); G2 >=MOYENNE($G$ 2 :$G$13 3))

3. Exécutez la commande Données | Filtrer | Filtre avancé.

Solution

1. Sélectionnez la liste (ou - placez le pointeur de cellule sur la liste) et triez (commande Données | Tri) d'abord - à travers le terrain Vendeur, puis à travers le champ Date de la vente(Fig. 47).

Riz. 46. ​​​​​​Liste des ventes

2. Appliquez la commande Données | Résultats. Dans la fenêtre Sous-totaux régler les paramètres selon la fig. 48 : pour obtenir le (premier) niveau de totaux supérieur - le nombre total de biens vendus par un vendeur spécifique.

Riz. 47.Tri d'une liste

Riz. 48. Fenêtre Sous-totaux pour obtenir les totaux des champs Vendeur

3. Pour obtenir le deuxième niveau de totaux, placez le pointeur de cellule dans la liste avec les totaux résultants, puis exécutez la commande Données | Résultats, installé dans la fenêtre Sous-totaux paramètres conformément à la fig. 49.

Figure 49. Fenêtre Sous-totaux pour obtenir les totaux des champs Date de la vente

4. Les résultats intermédiaires obtenus sont présentés dans la Fig. 50.

Riz. 50. Sous-totaux imbriqués

Consolidation des données

La consolidation a pour but de synthétiser des données homogènes. Sa mise en œuvre implique l’utilisation de la méthodologie suivante :

1. Précisez l'emplacement des futures données consolidées.

2. Sélectionnez une équipe Données | Consolidation.

3. Dans la fenêtre qui s'ouvre, précisez les plages de données à consolider.

4. Précisez la méthode de consolidation :

Selon l'emplacement dans la plage - toutes les cases de zone sont décochées Utiliser comme noms ;

Selon les en-têtes de lignes et de colonnes - définissez

cases à cocher signatures de la première ligne Et valeurs de la colonne de gauche.

5. Sélectionnez le type de consolidation, c'est-à-dire indiquez quelle opération sera réalisée avec les données consolidées.

6. Si nécessaire, indiquez l'ajout d'une structure - cochez la case Créez des connexions avec les données sources.

Exercice 1. Combinez les données sur la quantité et le coût des marchandises vendues dans une chaîne de magasins, qui sont présentées sous la forme d'une liste avec les champs suivants (Fig. 51) : Produit, coût, quantité, situé sur la feuille 2, la feuille 4 et la feuille 5.

Riz. 51. Données sur les biens vendus

Consolidation conformément aux données de la Fig. 52. Les données combinées sont présentées dans la Fig. 53.

Tableaux croisés dynamiques

Les tableaux croisés dynamiques sont un outil permettant de regrouper, de résumer et d'analyser les données trouvées dans les listes MS Excel ou dans les tableaux créés dans d'autres applications. Consolidé en externe

Riz. 52 . Saisir des données dans la fenêtre Consolidation

Riz. 53. Présentation des données consolidées

Tableaux croisés dynamiques

Les tableaux croisés dynamiques sont un outil permettant de regrouper, de résumer et d'analyser les données trouvées dans des listes MS Excel ou des tableaux liés dans d'autres applications. Les tableaux croisés dynamiques externes sont une structure qui vous permet d'afficher des données en trois dimensions. Les tableaux croisés dynamiques peuvent être utilisés :

Résumer une grande quantité de données similaires ;

Réorganiser les données (par glisser-déposer) ;

Pour sélectionner et regrouper des données ;

Pour créer des diagrammes.

Les tableaux croisés dynamiques sont créés à l'aide de l'assistant de tableau croisé dynamique (commande Données | Tableau croisé dynamique) selon la méthode suivante :

1. Sélectionnez un emplacement pour le tableau croisé dynamique, c'est-à-dire placez le pointeur de cellule à l'emplacement requis sur la feuille de calcul.

2. Exécutez la commande Données| Tableau croisé dynamique.

3. Définissez la plage de données initiale en effectuant les étapes 1 et 2 de l'assistant (Fig. 54 et 55). Après avoir cliqué sur le bouton Suivant dans la fenêtre de l'assistant illustrée à la Fig. 55, la fenêtre de la 3ème étape de l'assistant s'ouvrira (Fig. 56).

4. Avant de préciser l'emplacement de la future table (Fig. 56), vous devez cliquer sur le bouton Mise en page et dans la fenêtre qui s'ouvre (Fig. 57), créez la disposition du tableau croisé dynamique (c'est-à-dire définissez la page, les lignes, les colonnes, les champs totaux et calculés du tableau croisé dynamique).

Riz. 54Localisation des données pour un tableau croisé dynamique

Riz. 55. Plage de données pour un tableau croisé dynamique

Riz. 56. Préciser l'emplacement du futur tableau croisé dynamique

5. Déterminer l'opération requise pour les champs placés dans la zone Données, ou pour paramétrer un champ calculé, double-cliquez avec le bouton gauche de la souris sur le champ placé dans la zone Données(Fig. 57), et sélectionnez les actions nécessaires dans la fenêtre Calcul du champ du tableau croisé dynamique(Fig. 58).

6. Appuyez sur le bouton Possibilités(Fig. 56) et dans la fenêtre qui s'ouvre (Fig. 59) définissez les paramètres nécessaires pour le tableau croisé dynamique.

Riz. 57.Création d'une disposition de tableau croisé dynamique

Riz. 58. Fenêtre de calcul du champ du tableau croisé dynamique

Riz. 59 . Définition des options du tableau croisé dynamique

7. Une fois toutes les opérations préparatoires terminées, appuyez sur le bouton Prêt(Fig. 56).

Lorsque vous créez, modifiez et travaillez avec des tableaux croisés dynamiques, tenez compte des points suivants :

Emplacement du tableau croisé dynamique - il est conseillé de le placer sur une feuille séparée, car lors de la mise à jour ou du regroupement du tableau croisé dynamique, les informations contenues sur les feuilles de calcul à côté du tableau croisé dynamique peuvent être masquées ;

Emplacement des données sources – liste MS Excel, source externe, plages de consolidation situées dans un autre tableau croisé dynamique ;

Lors de la création d'une structure de tableau croisé dynamique, déterminez les éléments suivants :

1) champs situés dans les lignes et colonnes du tableau ;

2) les champs pour lesquels les résultats sont résumés (avec choix de l'opération souhaitée) ;

3) marges des pages, qui permettent de présenter les informations sous forme tridimensionnelle.

Un tableau croisé dynamique est un outil permettant d'afficher uniquement des données. Par conséquent, les données ne peuvent pas être modifiées dans le tableau lui-même. Pour modifier des données dans un tableau croisé dynamique, vous devez apporter des modifications à la source de données, puis mettre à jour le tableau croisé dynamique (à l'aide de l'option Mettre à jour les données sur la barre d'outils Tableaux croisés dynamiques(Fig. 60) ;

Riz. 60. Barre d'outils Tableaux croisés dynamiques

Dans les tableaux croisés dynamiques, vous pouvez modifier les noms des champs, ce qui n'entraîne pas de modification des champs des données sources. Les éléments du tableau croisé dynamique peuvent également être manipulés avec la souris.

Le but du travail de laboratoire est d'étudier et de consolider les compétences en saisie de données et en utilisation de formules dans Microsoft Excel 2007.

Saisir des données dans une feuille de calcul

Les cellules d'une feuille de calcul peuvent contenir trois types de données : des valeurs numériques (y compris l'heure et la date), du texte et des formules. La feuille de calcul, mais dans une « couche graphique » au-dessus de la feuille, peut également contenir des images, des graphiques, des images, des boutons et d'autres objets.

Saisir des chiffres

Les nombres sont saisis à l'aide de la rangée supérieure du clavier ou du pavé numérique. Une virgule ou un point est utilisé comme séparateur décimal ; vous pouvez saisir des symboles monétaires. Si vous entrez un moins ou des parenthèses avant un nombre, celui-ci est considéré comme négatif. Les zéros tapés avant le nombre sont ignorés par le programme. Si vous devez obtenir une valeur avec des zéros non significatifs, elle doit être interprétée comme du texte.

Excel utilise 15 chiffres pour représenter les nombres ; lorsque vous entrez un nombre à 16 chiffres, il sera automatiquement enregistré à 15 chiffres. Les valeurs numériques sont automatiquement alignées sur le bord droit de la cellule.

Saisir des dates et des heures

Excel utilise un système de numérotation de dates interne pour représenter les dates. (Ainsi, la date la plus ancienne que le programme peut reconnaître est le 1er janvier 1900, cette date reçoit le numéro de série 1, la date suivante reçoit le numéro de série 2, etc.). Les dates sont saisies dans un format familier à l'utilisateur et sont reconnues automatiquement. Les valeurs temporelles sont également saisies dans l'un des formats horaires reconnus. La présentation de la date et de l'heure directement sur la feuille de calcul est contrôlée en définissant le format d'affichage de la cellule.

Saisir du texte

Toutes les données saisies qui ne sont pas reconnues comme des nombres ou des formules sont traitées comme des valeurs de texte. Les valeurs du texte sont alignées sur le bord gauche du tableau. Si le texte ne tient pas dans une seule cellule, il est alors placé au-dessus des cellules adjacentes si elles sont libres. Les paramètres permettant de placer du texte dans une cellule sont définis à l'aide du format de cellule.

Saisir une formule

Une formule est une expression mathématique. La formule commence toujours par le signe « = » et peut inclure, en plus des opérateurs et des références de cellules, des fonctions Excel intégrées.

Formats de données

Après avoir saisi des données dans une cellule, Excel essaie automatiquement de déterminer son type et d'attribuer à la cellule le format approprié - la forme de présentation des données. Il est important d'attribuer le format de cellule correct afin que, par exemple, la cellule puisse participer aux calculs (et non être du texte).

Excel dispose d'un ensemble de formats de cellule standard qui peuvent être utilisés dans tous les classeurs (Figure 2.2.17). Vous pouvez l'activer en sélectionnant Accueil – Nombre – Format numérique ou en utilisant le menu contextuel de la cellule sélectionnée dans l'onglet Nombre de la fenêtre Format de cellule.

Graphique 2.2.17. Formats standards

Initialement, toutes les cellules du tableau ont le format Général. L'utilisation de formats affecte la manière dont le contenu des cellules sera affiché : général - les nombres sont affichés sous forme d'entiers, de fractions décimales, si le nombre est trop grand, puis sous forme exponentielle ; numérique – format numérique standard ; financier et monétaire – le nombre est arrondi à 2 décimales, le signe monétaire est placé après le nombre, le format monétaire permet d'afficher les montants négatifs sans le signe moins et dans une couleur différente ; Format de date courte et de date longue – vous permet de sélectionner l'un des formats de date ; heure – propose plusieurs formats d'heure parmi lesquels choisir ; - pourcentage – le nombre (de 0 à 1) dans la cellule est multiplié par 100, arrondi au nombre entier le plus proche et écrit avec le signe % ; fractionnaire – utilisé pour afficher les nombres sous la forme d'une fraction ordinaire plutôt que d'un nombre décimal ; exponentiel – conçu pour afficher des nombres comme le produit de deux composants : un nombre de 0 à 10 et une puissance de 10 (positive ou négative) ; texte – lors de la définition de ce format, toute valeur saisie sera perçue comme du texte ; supplémentaire – inclut les formats Code postal, Code postal+4, Numéro de téléphone, Numéro personnel ; tous les formats – vous permet de créer de nouveaux formats en tant que modèle personnalisé.

Utiliser des outils pour accélérer la saisie des données

Lors de la saisie de données dans des feuilles de tableau, vous pouvez utiliser certaines techniques pour accélérer leur saisie.

1) Remplissage automatique au fur et à mesure que vous tapez. Lorsque vous saisissez les mêmes valeurs dans plusieurs cellules, vous pouvez utiliser le marqueur de remplissage automatique (la croix dans le coin inférieur droit de la cellule active) pour copier les valeurs dans les cellules adjacentes. À l'aide du menu contextuel qui s'ouvre en cliquant avec le bouton droit après le glissement, vous pouvez définir des paramètres de remplissage automatique supplémentaires (par exemple, en saisissant les chiffres 1 et 3 dans les cellules, vous pouvez obtenir une séquence de nombres par incréments de 2 pour la plage de valeurs sélectionnée cellules).

2) Utilisation de la progression. Si une cellule contient un nombre, une date ou une période de temps pouvant faire partie d'une série, alors lors de la copie, sa valeur est incrémentée (une progression arithmétique ou géométrique, une liste de dates est obtenue). Pour définir une progression, vous devez sélectionner le bouton Remplir dans le panneau d'édition de l'onglet Accueil et dans la boîte de dialogue Progression qui apparaît, définir les paramètres d'une progression arithmétique ou géométrique.

3) Remplissage automatique au fur et à mesure que vous tapez. Cette fonctionnalité vous permet de saisir automatiquement des données textuelles répétitives. Après avoir saisi du texte dans une cellule, Excel le mémorise et la prochaine fois que vous le saisissez, après avoir tapé les premières lettres du mot, il propose une option pour terminer la saisie. Pour terminer la saisie, appuyez sur « Entrée ». Cette commande est également accessible en sélectionnant Sélectionner dans la liste déroulante dans le menu contextuel à l'aide du bouton droit de la souris. La fonction de saisie semi-automatique ne fonctionne que sur une séquence continue de cellules.

4) Utiliser la correction automatique pendant que vous tapez. La correction automatique est conçue pour remplacer automatiquement certaines combinaisons de caractères spécifiées par d'autres au fur et à mesure que vous tapez. Par exemple, vous pouvez spécifier de saisir un seul caractère au lieu de saisir plusieurs mots. La commande est disponible via le bouton Office – Options Excel. Dans Orthographe - Options de correction automatique, vous devez définir le texte et son abréviation.

5) Utilisation du raccourci clavier Ctrl+Entrée pour saisir des valeurs répétitives. Pour saisir les mêmes valeurs dans plusieurs cellules, vous pouvez les sélectionner, saisir la valeur dans une cellule et appuyer sur Ctrl+Entrée. En conséquence, les mêmes données seront saisies dans toutes les cellules sélectionnées.

Validation des données lors de votre saisie

Si vous souhaitez vous assurer que les données correctes sont saisies dans une feuille de calcul, vous pouvez spécifier des critères valides pour des cellules individuelles ou des plages de cellules. Pour configurer une vérification, exécutez la commande Data – Working with Data – Checking Data. Dans la fenêtre qui apparaît (Figure 2.2.18), définissez les critères de vérification dans l'onglet Paramètres, le texte du message d'invite que l'utilisateur doit saisir dans l'onglet Message d'entrée et le texte du message d'erreur dans l'onglet Message d'erreur. .

Après avoir utilisé la commande Data – Working with Data – Circle Invalid Data, toutes les données incorrectes seront encerclées en rouge.

Graphique 2.2.18. Fenêtre de définition des paramètres de vérification des données

Utiliser des formules

Une formule dans Excel est une expression mathématique sur la base de laquelle la valeur d'une certaine cellule est calculée. Les formules peuvent utiliser : des valeurs numériques ; adresses de cellules (références relatives, absolues et mixtes) ; opérateurs : mathématiques (+, -, *, /, %, ^), comparaisons (=,<, >, >=, <=, < >), opérateur de texte & (pour combiner plusieurs chaînes de texte en une seule), opérateurs de relation de plage (deux-points (:) - plage, virgule (,) - pour combiner des plages, espace - intersection de plages) ; les fonctions.

La saisie d'une formule commence toujours par le signe « = ». Le résultat de la formule est affiché dans la cellule et la formule elle-même est affichée dans la barre de formule. Les adresses de cellules dans la formule peuvent être saisies manuellement ou simplement en cliquant sur les cellules souhaitées.

Après le calcul, le résultat obtenu est affiché dans la cellule et la formule créée est affichée dans la barre de formule de la fenêtre de saisie.

Méthodes d'adressage des cellules

L'adresse de la cellule se compose du nom de colonne et du numéro de ligne de la feuille de calcul (par exemple A1, BM55). Dans les formules, les adresses sont indiquées à l'aide de liens - relatifs, absolus ou mixtes. Grâce aux liens, les données situées dans différentes parties de la feuille de calcul peuvent être utilisées dans plusieurs formules en même temps.

Une référence relative indique l'emplacement de la cellule souhaitée par rapport à la cellule active (c'est-à-dire actuelle). Lors de la copie de formules, ces liens sont automatiquement modifiés en fonction de la nouvelle position de la formule (Exemple de saisie de lien : A2, C10).

Une référence absolue pointe vers l'emplacement exact de la cellule incluse dans la formule. Lorsque vous copiez des formules, ces liens ne changent pas. Pour créer une référence de cellule absolue, placez un signe dollar ($) devant les désignations de colonnes et de lignes (exemple de notation de référence : $A$2, $C$10). Pour corriger une partie de l'adresse de la cellule des modifications (par colonne ou ligne) lors de la copie de formules, une référence mixte est utilisée avec fixation du paramètre souhaité. (Exemple d'entrée de lien : $A2, C$10).

Remarques

· Pour éviter de taper manuellement des signes dollar lors de la rédaction de liens, vous pouvez utiliser la touche F4, qui permet de « trier » tous les types de liens pour une cellule.

Fonctions Excel intégrées

Chaque fonction a sa propre syntaxe et son propre ordre de fonctionnement qui doivent être suivis pour que les calculs soient corrects. Les arguments des fonctions sont écrits entre parenthèses, et les fonctions peuvent avoir ou non des arguments ; lors de leur utilisation, vous devez prendre en compte le type d'arguments. Une fonction peut servir d’argument à une autre fonction, auquel cas elle est appelée fonction imbriquée. Dans ce cas, jusqu'à plusieurs niveaux d'imbrication de fonctions peuvent être utilisés dans les formules.

Excel 2007 comprend des fonctions mathématiques, logiques, financières, statistiques, textuelles et autres. Le nom de la fonction dans la formule peut être saisi manuellement à partir du clavier (cela active l'outil de formules de saisie semi-automatique, qui permet de sélectionner la fonction souhaitée en fonction des premières lettres saisies (Figure 2.2.19)), ou vous pouvez la sélectionner dans la fenêtre Assistant de fonctions, activé par le bouton du panneau Bibliothèque de fonctions de l'onglet Formules ou à partir de groupes de fonctions sur le même panneau, ou à l'aide du bouton du panneau Modifier de l'onglet Accueil.

Graphique 2.2.19. Formules de saisie semi-automatique

Les formules peuvent être modifiées comme le contenu de n'importe quelle autre cellule. Pour modifier le contenu d'une formule : double-cliquez sur la cellule contenant la formule, ou appuyez sur F2, ou modifiez le contenu dans la ligne de saisie de la formule.

Nommer et utiliser les noms de cellules

Excel 2007 fournit une fonctionnalité utile pour nommer des cellules ou des plages. Cela peut être particulièrement pratique lors de la composition de formules. Par exemple, en spécifiant le nom Total_for_year pour une cellule, vous pouvez utiliser ce nom à la place de l'adresse de la cellule dans toutes les formules.

Le nom de cellule peut être valide dans une feuille ou un classeur ; il doit être unique et ne pas contenir de noms de cellule en double. Pour nommer des cellules, vous devez sélectionner la cellule ou la plage et saisir un nouveau nom dans la barre de titre. Ou utilisez le bouton Attribuer un nom au panneau Noms définis de l'onglet Formules et appelez la boîte de dialogue (Figure 2.2.20) pour définir les paramètres requis.

Graphique 2.2.20. Fenêtre de création de nom

Pour afficher tous les noms attribués, utilisez la commande Name Manager. Vous pouvez également obtenir une liste de tous les noms avec des adresses de cellules sur la feuille à l'aide de la commande Utiliser dans la formule - Insérer des noms du panneau Noms définis.

Pour insérer un nom dans une formule, vous pouvez utiliser la commande Utiliser dans la formule et sélectionner le nom de cellule requis dans la liste.

Commentaire. Un nom peut être attribué non seulement à des plages de cellules, mais également à une formule. Ceci est pratique lorsque vous utilisez des formules imbriquées.

Afficher les dépendances dans les formules

Pour vous aider à identifier les erreurs lors de la création de formules, vous pouvez afficher les dépendances des cellules. Les dépendances sont utilisées pour afficher les relations dans une zone de tableau entre les cellules contenant des formules et les cellules contenant des valeurs impliquées dans ces formules. Les dépendances ne sont affichées que dans le même classeur ouvert. Lors de la création d'une dépendance, vous utilisez des cellules d'influence et des cellules dépendantes.

Une cellule d'influence est une cellule qui fait référence à une formule dans une autre cellule.

Une cellule dépendante est une cellule qui contient une formule.

Pour afficher les relations entre les cellules, vous devez sélectionner les commandes Cellules d'influence ou Cellules dépendantes dans le volet Dépendances de formule de l'onglet Formules. Pour ne pas afficher les dépendances, utilisez la commande Supprimer les flèches du même panneau.

Graphique 2.2.21. Affichage des cellules d'influence

Modes de travail avec des formules

Excel dispose d'un mode de calcul automatique, grâce auquel les formules sur les feuilles sont recalculées instantanément. Lorsque vous placez un très grand nombre (jusqu'à plusieurs milliers) de formules complexes sur une feuille, la vitesse de travail peut diminuer sensiblement en raison du recalcul de toutes les formules sur la feuille. Pour contrôler le processus de calcul à l'aide de formules, vous devez définir le mode de calcul manuel à l'aide de la commande Formules – Calcul – Options de calcul – Manuel. Après avoir apporté des modifications, vous devez appeler la commande Calculer (pour recalculer les données sur une feuille de calcul) ou Recalculer (pour recalculer l'intégralité du classeur) du panneau Calcul.

Une fonctionnalité utile pour travailler avec des formules consiste à afficher toutes les formules sur une feuille. Cela peut être fait à l'aide de la commande Formules – Dépendances de formule – Afficher les formules. Après cela, les formules écrites seront affichées dans les cellules au lieu des valeurs calculées. Pour revenir au mode normal, cliquez à nouveau sur le bouton Afficher les formules.

Si une formule renvoie une valeur incorrecte, Excel peut vous aider à identifier la cellule à l'origine de l'erreur. Pour ce faire, vous devez activer la commande Formules – Dépendances de formule – Rechercher les erreurs – Source des erreurs. La commande Rechercher les erreurs permet d'identifier toutes les entrées de formule erronées.

Pour déboguer les formules, il existe un évaluateur de formule appelé Formules - Dépendances de formule - Évaluer la formule, qui affiche les calculs étape par étape dans des formules complexes.

Atelier:.

1. Créez un tableau pour calculer la somme d'une série (options de tâches pour calculer la somme d'une série - voir ci-dessous). Lors de la création d'un tableau, utilisez les fonctions intégrées, l'adressage absolu et relatif et le remplissage automatique des cellules.

2. En fonction du nombre de termes n, disposez le tableau comme suit :

Tableau 19.

x je	1	2	…	n	S	Oui
0,1
0,2
.
.
1

Tableau 20.

je x	0,1	0,2	…	1
1
2
.
.
n
S
Oui

3. À l'aide de la mise en forme conditionnelle, mettez en surbrillance les nombres négatifs en bleu et les nombres supérieurs à 1,5 en rouge.

4. Créez un tableau. Un exemple de conception est ci-dessous. Le pas de changement x selon la variante de la tâche est de 0,1 (ou Pi/*).

5. Construisez les graphiques s=f(x) et y=f(x) dans une grille de coordonnées (sur un diagramme).

6. Étudiez les possibilités d'utilisation des fonctions (pour une liste des fonctions, voir l'option tâche), donnez un exemple du fonctionnement de chaque fonction.

Tableau 21. Options de tâche

Utiliser une macro DemandeOuverte Dans les bases de données Access, vous pouvez ouvrir des requêtes de sélection et croisées en mode Feuille de données, en mode Création ou en mode Aperçu. Cette action déclenche une demande de modification. Vous pouvez également sélectionner le mode de saisie des données pour la demande.

Note: Cette macro est uniquement disponible dans un environnement de base de données Access (MDB ou ACCDB). Si vous utilisez Access Project Environment (ADP), voir Macros OuvrirVue, Procédure OuvrirSauvegardée Et OuvrirFonction. Commande macro DemandeOuverte non disponible dans les applications Web Access.

Paramètres

Commande macro DemandeOuverte a les arguments suivants :

Argument macro	Description
Nom de la demande	Le nom de la demande à ouvrir. Sélectionnez un nom dans la liste déroulante. C'est un argument requis. Lorsque vous exécutez une bibliothèque de macros dans une base de données contenant une macro DemandeOuverte Access recherche d’abord une requête portant ce nom dans la base de données de la bibliothèque, puis dans la base de données actuelle.
	La vue dans laquelle la demande sera ouverte. Sélectionnez dans le champ Voir signification Tableau, Constructeur, Aperçu, Tableau croisé dynamique ou Graphique croisé dynamique. La valeur par défaut est Tableau. Note: Les vues Tableau croisé dynamique et Graphique croisé dynamique ne sont pas disponibles dans les versions d'Access à partir d'Access 2013.
Mode données	Mode de saisie des données pour la demande. Cette option s'applique uniquement aux requêtes ouvertes en mode Feuille de données. Sélectionner Ajouter(les utilisateurs pourront ajouter de nouvelles entrées, mais pas modifier celles existantes), Changement(les utilisateurs pourront modifier les entrées existantes ainsi qu'en ajouter de nouvelles) ou Uniquement pour lire(les utilisateurs ne pourront voir que les entrées). La valeur par défaut est Changement.

Remarques

Si pour argument Voir valeur donnée Tableau, Access affiche le jeu de résultats si vous utilisez une requête de sélection, une requête croisée, une requête de jointure ou une requête serveur, une propriété RetoursEnregistrements ce qui compte Oui. S'il s'agit d'une demande de modification, d'une demande de définition de données ou d'une demande de serveur pour une propriété RetoursEnregistrements dont la valeur est donnée Non, la requête est en cours d'exécution.

Commande macro DemandeOuverte revient à double-cliquer sur une requête dans le volet de navigation ou à cliquer dessus avec le bouton droit dans le volet de navigation et à sélectionner la vue. Lorsque vous utilisez une macro, vous pouvez sélectionner des options supplémentaires.

Conseil

Vous pouvez faire glisser une requête du volet de navigation vers la fenêtre Macro Designer. Cela créera automatiquement une macro-commande DemandeOuverte, qui ouvre la requête en mode Feuille de données.

Si vous basculez vers le Designer alors que la requête est ouverte, la valeur de l'argument Mode données est supprimé. Ce paramètre n'aura aucun effet même si l'utilisateur revient en mode Feuille de données.

Si vous ne souhaitez pas afficher les messages système qui apparaissent généralement lors de l'exécution de demandes de modification (ceux qui vous indiquent qu'il s'agit d'une demande de modification et le nombre d'enregistrements qu'elle affecte), vous pouvez les désactiver à l'aide d'une macro. DéfinirAvertissement.

Pour exécuter une macro DemandeOuverte dans un module Visual Basic pour Applications (VBA), utilisez la méthode DemandeOuverte objet DoCmd.

Hé, je viens d'apprendre quelque chose sur la façon de mettre mes instructions SQL dans VBA (ou au moins de les écrire), mais je ne sais pas comment récupérer les données ?

J'ai plusieurs formulaires (sous forme de graphique) basés sur des requêtes sur lesquelles j'exécute des paramètres assez réguliers, en changeant simplement le délai (comme les 10 meilleures ventes par mois). Ensuite, j'ai des procédures qui transmettent automatiquement l'objet graphique dans la présentation PowerPoint. J'ai donc toutes ces requêtes pré-construites (comme 63), et un diagramme de formulaire correspondant (ouais.... 63... je sais que c'est mauvais), et ensuite toutes ces choses sont créées sur "ouvrir/fermer" " événement menant au suivant (c'est comme ma meilleure tentative d'être un hack... ou un domino ; selon votre préférence).

J'ai donc essayé d'apprendre à utiliser les instructions SQL dans VBA pour pouvoir éventuellement tout faire là-bas (je devrai peut-être enregistrer tous ces diagrammes de formes, mais je ne sais pas car je manque évidemment de compréhension).

Alors à part la question que j'ai posée en haut, qui peut donner des conseils ? Merci

6 réponses

10

C'est un peu dépassé, vous voudrez peut-être vous procurer un livre sur ce sujet. Mais voici une tonne d’accès à des ressources ainsi que des tutoriels et des exemples. Mais au fond...

Dim dbs As Database Dim rs As Recordset Dim strSQL As String Set dbs = CurrentDb strSQL = "votre requête ici Set rs = dbs.OpenRecordset(strSQL) If Not (rs.EOF And rs.BOF) Then rs.MoveFirst "obtient les résultats en utilisant rs.Fields() Sinon "Utiliser les résultats

Derrière le commentaire : jetez un œil à la classe Post. Il contient une collection appelée champs, qui sont les colonnes renvoyées par votre requête. Sans connaître votre circuit, c'est difficile à dire, mais quelque chose comme...

Rs.MoveFirst Do While Not rs.EOF "faire quelque chose comme rs("SomeFieldName") rs.MoveNext Loop

Comme je l'ai dit, le mieux est de prendre un livre sur ce sujet, ils ont des tonnes d'exemples.

Utilisez un QueryDef paramétré et appelez-le depuis VBA.
La requête est plus facile à concevoir... facilement testable... et facilement accessible à l'aide de VBA ou d'un formulaire.

Dim qd comme querydef set qd = currentdb.querydefs!myquerydef qd.parameters!parm1=val1

ou qd.execute

Dim rs comme jeu d'enregistrements rs = qd.openrecordset()

Voici une fonction que vous pourriez envisager de refactoriser pour intégrer et que vous pourrez réutiliser n'importe où dans votre code.

Alors const ou construisez une chaîne pour votre instruction SQL, et insérez votre désinfection, NON SQL INJECT la chaîne comme argument :)

StrSQL = "SELECT * FROM Customer WHERE ID = " & EnsureParamIsNotSQLInjection(customerID)

Appelez ensuite la fonction /sub depuis l'endroit où vous avez besoin d'obtenir les données/l'ensemble d'enregistrements/d'exécuter l'instruction. En créant plusieurs fonctions/sous-titres d'accès aux données où vous pouvez simplement exécuter une instruction UPDATE ou récupérer une seule valeur, ou récupérer des enregistrements complets.

La clé ici est de regrouper toutes ces fonctions au même endroit et de les utiliser partout. Voici un exemple en VBScript.

Sub DoStuff(strSQL) Set adoCon = Server.CreateObject("ADODB.Connection") strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db\Database.mdb") "strConnString = "DRIVER=(Pilote Microsoft Access (*.mdb)); DBQ=" & Server.MapPath("db\Database.mdb") adoCon.Open strConnString Set rsMain = Server.CreateObject("ADODB.Recordset") rsMain.Open strSQL, adoCon Do While NOT rsMain.EOF customerName = rsMain(" CustomerName") "exemple idiot RsMain.MoveNext Loop rsMain.Close Set adoCon = Nothing End Sub

Une autre façon de procéder, que personne ne semble avoir mentionnée, consiste à associer votre graphique à un seul QueryDef enregistré, puis à écraser le QueryDef au moment de l'exécution. Maintenant, je ne recommande pas de modifier les QueryDefs enregistrés pour la plupart des contextes car cela provoque un gonflement frontal et n'est généralement même pas nécessaire (dans la plupart des contextes où vous utilisez les QueryDefs enregistrés, ils peuvent être filtrés d'une manière ou d'une autre dans le contexte dans lequel ils ont été utilisés, par exemple , en tant que formulaire RecordSource, vous transmettez simplement un argument à DoCmd.OpenForm).

Les graphiques sont différents car les graphiques de pilotage SQL ne peuvent pas être modifiés au moment de l'exécution.

Certains d'entre eux suggèrent des paramètres, mais l'ouverture d'un formulaire contenant un graphique utilisant une chaîne SQL avec des paramètres fera apparaître des boîtes de dialogue de paramètres par défaut. Une façon d'éviter cela consiste à utiliser un formulaire de dialogue pour collecter des critères, puis à définir des références aux contrôles dans la vue de dialogue en tant que paramètres, etc. :

PARAMÈTRES!! Long;

Si vous utilisez des liens de formulaire, il est important de le faire car depuis Access 2002, Jet Expression Service ne les gère pas toujours correctement lorsque les contrôles sont Null. Les définir comme paramètres corrige ce problème (qui n'était pas présent avant Access XP).

Une situation dans laquelle vous devez réécrire QueryDef pour un graphique est si vous souhaitez autoriser l'utilisateur à sélectionner N dans une instruction SQL TOP N. Autrement dit, si vous souhaitez pouvoir sélectionner TOP 5 ou TOP 10 ou TOP 20, vous devrez modifier le QueryDef enregistré puisque N n'est pas paramétrable.