Travail de laboratoire
Sujet: fonctions Excel
Cibler:
Connaître les différentes classes de fonctions ;
Apprenez à utiliser l'assistant de fonction ;
Apprenez à utiliser des fonctions imbriquées pour travailler avec des tables.
Les fonctions Exceller
Une fonction Est une variable dépendante dont la valeur est calculée selon certaines règles basées sur les valeurs d'autres quantités - arguments de fonction... Excel propose un grand (plusieurs centaines) de fonctions standard (intégrées) qui peuvent être utilisées dans des formules, par exemple :
Fonction - du latin Functio - exécution.
Le nom de la fonction entre parenthèses est suivi d'une liste d'arguments séparés par des points-virgules. La liste d'arguments peut être constituée de nombres, de texte, de valeurs logiques (VRAI ou FAUX), de références, de formules, de fonctions imbriquées. Si la formule commence par une fonction, le caractère " = ».
De par la nature des arguments, les fonctions intégrées peuvent être divisées en trois types :
Lister les arguments(maximum - 30 arguments) : MOYENNE (A2 : C23 ; E6 ; 200 ; 3) - renvoie la valeur moyenne des arguments
AVEC arguments fixes : PUISSANCE (6.23 ; 4) : Élève le premier argument (6.24) à la puissance du deuxième argument (4)
Aucun argument: AUJOURD'HUI () : renvoie la date actuelle.
Saisie de formules
La séquence de saisie d'une fonction dans une formule :
Nom de la fonction ;
Parenthèse ouvrante ;
Liste d'arguments séparés par des points-virgules ;
Parenthèse fermante.
La saisie de fonction peut se faire de plusieurs manières :
Fonctions et barre de formule
S'ils ne sont pas saisis manuellement, les arguments sont spécifiés à l'aide de la barre de formule :
Argument requis surligné en gras - sans cela, la fonction ne peut pas effectuer de traitement ;
Argument facultatif est affiché par l'orthographe habituelle du nom du champ et sa valeur peut entrer ou non. Dans ce cas, les valeurs par défaut seront utilisées.
Si la formule est constituée de plusieurs fonctions, la barre de formule affiche les arguments de la fonction en gras dans la ligne de formule. Pour que la barre de formule affiche les arguments d'une autre fonction, vous devez cliquer sur son nom dans la barre de formule.
La barre de formule peut être déplacée sur l'écran en la faisant glisser avec la souris.
Fonctions imbriquées
Le résultat de l'évaluation d'une fonction peut être utilisé comme argument d'une autre fonction. Une fonction utilisée comme l'un des arguments d'une autre fonction est appelée imbriqué... Excel prend en charge jusqu'à 7 niveaux d'imbrication de fonctions.
Par exemple:
SI (A4> 0 ; MAX (A9 : B19);0)
Pour saisir une fonction en argument vous devez développer la liste dans la barre de formule et sélectionner l'un des 10 récemment utilisé, ou contactez à l'assistant de fonction en utilisant la commande Autres fonctions.. ou entrez la fonction manuellement.
Pâte spéciale
Le contenu d'une cellule peut être considéré comme un ensemble de quatre couches d'informations : formule, valeur, format et note. Excel vous permet de copier chaque couche séparément. Les informations sont mises en mémoire tampon comme d'habitude (la commande Copie), mais inséré à l'aide de la commande Édition \ Collage spécial ...
Pour copier des formats, comme dans d'autres applications Office, la barre d'outils standard est utilisée - Exemple de format . (Travaux pratiques "Prévisions météorologiques » ).
la tâche:
À l'aide de la fonction, remplissez le bloc A1 : A5 avec des nombres aléatoires dans la plage [-10,10] ;
Dans la cellule B1, entrez la formule de calcul de la partie entière des valeurs de la colonne A;
Copiez la formule résultante dans le bloc B2 : B5 ;
Appliquez la même séquence d'opérations aux fonctions et aux blocs, respectivement :
ABS (A) - C1 : C5 ;
EXP (A) - D1 : D5 ;
CARRÉ (A) - E 1 : E 5 ;
Calcul du reste en divisant par 2 - F 1 : F 5 ;
Arrondi de -1 - H 1 : H 5 ;
Arrondi de +1 - G 1 : G 5
Dans la cellule A7, écrivez la formule sommeséléments de la première colonne (A1 : A5)
Dans la cellule B7 - la moyenne arithmétique sur (B1 : B5)
C7 - l'élément maximum de (C1: C6)
D 7 - élément minimum (D 1 : D 6)
E 7 - nombre d'éléments (E1 : E6)
F 7 - variance des valeurs (F 1 : F 6)
Varier I 1 : I 6 remplir avec les valeurs des fonctions trigonométriques :
I1 - PI
I2 - Péché (A1)
I3 - Cos (A2)
I4 - Tan (A3)
I5 - Atan (A4)
I6 - Asin (A5)
A la ligne 10, saisissez les en-têtes des champs :
Nom \ Prénom Date de naissance Nombre de jours
Corrigez la largeur des colonnes et centrez les titres ;
Dans le bloc A12 : A17, saisissez les noms ou prénoms de vos amis et connaissances. Dans le bloc B12 : B17 - leurs dates de naissance. Saisissez la date au format européen ;
Entrez la date actuelle dans la cellule C9 ;
Dans la cellule C12, la formule de calcul du nombre de jours vécus par une personne pour la date du jour ;
Insérez une colonne Jour de la semaine entre les colonnes Date de naissance et Nombre de jours ;
Entrez la fonction de calcul du jour de la semaine par date de naissance dans la première cellule de la colonne. Copiez la formule résultante dans toutes les cellules de la colonne ;
Dans une colonne F écrivez « Jeune » ou « Vieux » à côté de chaque nom de famille en utilisant la fonction logique SI. Entrez la fonction à l'aide de l'assistant de fonction (SI Nombre de jours<15000, то «Молодой», иначе «Старый»);
Enregistrez la table résultante sur le disque dans un dossier personnel (Nom du groupe).
Tester les questions :
Méthodes de saisie de formules dans les cellules ;
Barre de formule;
Arguments obligatoires et facultatifs dans les formules ;
Procédure d'exécution des fonctions imbriquées dans Microsoft Excel ;
Coller l'algorithme spécial pour les cellules.
but du travail
Apprendre à travailler avec des liens relatifs et absolus
Apprenez à transférer des données de MS Excel vers MS Word
Être capable de composer des formules et de travailler avec diverses fonctions de MS Excel
Maîtriser diverses techniques de mise en forme de texte et de données dans des tableaux MS Excel
Apprenez à préparer divers types de matériel d'illustration en utilisant les moyens de construire des diagrammes et des graphiques
Maîtriser les techniques de construction de tableaux actualisables, en utilisant des informations détaillées comme données initiales
Tâche 1. Créer un lien relatif et absolu
1. Créez un document MS Excel et enregistrez-le sous Lab_2.xcls. Nommez la première feuille « Liens ». Entrez les données comme indiqué dans la Fig. un.
Remarques: Pour nommer une feuille, sélectionnez son nom actuel avec la souris, cliquez avec le bouton droit et sélectionnez Renommer. Dans la colonne B2, pour obtenir 100 roubles, vous devez taper 100 purs et sélectionner le format de devise (bouton droit de la souris - format de cellule ...).
2. Calculez le salaire d'Ivanov en créant une formule contenant une référence relative. Pour ce faire, sélectionnez la cellule C4 et accédez à la barre de formule. Entrez la formule = B2 * B4(fig. 2) et appuyez sur Entrer.
Riz. 2 Expression saisie dans la barre de formule
Noter: lors de la saisie d'une formule, utilisez des lettres latines ou, à l'aide de la souris, sélectionnez les cellules requises.
3. Copiez la formule dans les cellules C5 et C6 en tirant sur la poignée de remplissage. En même temps, en répliquant la formule de cet exemple avec des références relatives dans la cellule C5, un message d'erreur apparaîtra (AUCUNE VALEUR!), puisque l'adresse relative de la cellule B2 a changé, et la formule = B3 * B5.
Riz. 3. Message d'erreur (#VALUE!) Dans la cellule C5.
4. Définissez une référence absolue à la cellule B2. Pour ce faire, sélectionnez la cellule C4. Placez le curseur sur B2 dans la barre de formule et appuyez sur la touche F4, ce qui convertit une référence relative en référence absolue et vice versa (Fig. 4). Signer ( $ ) apparaîtra à la fois avant la référence de colonne et avant la référence de ligne. La formule dans la cellule C4 ressemblera à = $ B $ 2 * B4.
5. Appuyez successivement sur F4, ce qui ajoutera ou supprimera le signe $ devant le numéro de colonne ou de ligne. (B$ 2 ou $ B2 sont les liens dits mixtes).
7. Copiez la formule dans les cellules C5 et C6 en tirant sur la poignée de remplissage. En conséquence, vous devriez obtenir un tableau avec des données correctement reflétées (Fig. 5).
Ancienneté "href =" / text / category / visluga_let / "rel =" bookmark "> ancienneté en utilisant les données générées dans Excel à l'aide de Collage spécial pour le lien de données.
1. Accédez à une nouvelle feuille et nommez-la "Collage spécial" et entrez les données illustrées à la figure 6. Sélectionnez les cellules remplies et copiez-les dans le presse-papiers.
Noter: pour que les mots d'une cellule ne sortent pas des limites et ne s'alignent pas les uns sous les autres, vous devez sélectionner la cellule, faire un clic droit, sélectionner Formater les cellules..., aller dans l'onglet Alignement et mettre une coche à côté de " envelopper par des mots".
Riz. 6. Données sur la feuille "Collage spécial".
2. Créez un fichier texte dans MS Word, enregistrez-le sous Ordre.documents... Dessinez-le arbitrairement comme vous pensez que l'ordre d'attribution des salaires aux employés pourrait ressembler, en laissant un espace vide où, logiquement, vous pouvez insérer une plaque avec les salaires calculés.
3. Placez le curseur à l'endroit où le tableau doit être inséré. Exécutez la commande indiquée dans la figure ci-dessous :
Riz. 7. La commande Collage spécial
Une boîte de dialogue apparaîtra Pâte spéciale(fig. 8)
Microsoft "href =" / text / category / microsoft / "rel =" bookmark "> Microsoft Exceller (un objet).
5. Cochez le bouton radio Attacher et cliquez sur OK.
6. En conséquence, un objet associé au document Excel apparaîtra sur la page du document texte.
7. Retournez dans le document Excel et changez les cellules de la colonne « premium » au format « Monétaire » (sélectionnez la plage de cellules E2 : E10, faites un clic droit pour sélectionner Format Cells...) (Fig. 9). Dans l'onglet Nombre, sélectionnez Monétaire. Cliquez sur OK.
https://pandia.ru/text/78/392/images/image010_15.jpg "width =" 497 "height =" 358 src = ">
Riz. 10. Les données de la colonne Récompense sont affichées au format monétaire.
8. Accédez à votre document Word. Sélectionnez l'objet tableau. Appelez un menu spécifique avec le bouton droit de la souris et sélectionnez dans la ligne répertoriée Actualiser le lien(fig. 11).
https://pandia.ru/text/78/392/images/image012_13.jpg "width =" 627 "height =" 396 src = ">
Riz. 12. Définition du bouton radio dans la boîte de dialogue Collage spécial
Tâche 3. Utiliser la fonction RECHERCHEV pour remplacer automatiquement les données d'une table à une autre
1. Accédez à une nouvelle feuille et renommez-la en RECHERCHEV. Créez deux tableaux comme indiqué sur la fig. 13.
Riz. 13 Données de la feuille de calcul RECHERCHEV
2. Transférez les montants de la table Renvoyer les données de la colonne Revenu(en roubles) à la table Remboursement de dette automatiquement, en se concentrant sur Nom complet afin que vous puissiez calculer plus tard Dette restante. Pour ce faire, donnez une plage de cellules Renvoyer les données propre nom, en mettant tout en surbrillance sauf l'"en-tête" (G2 : H22), puis en cliquant avec le bouton droit de la souris et en sélectionnant dans la liste qui apparaît Nom de la plage.
3. Dans la boîte de dialogue ouverte Créer un nom entrez le nom (pas d'espaces) reste... À l'avenir, utilisez ce nom pour faire référence à la table. Renvoyer les données.
Riz. 14. Boîte de dialogue Créer un nom
4. Sélectionnez la cellule D3 où la formule sera saisie et ouvrez Assistant de fonction, Pressage fxà côté de la barre de formule (Fig. 15).
Riz. 15 Appel de l'assistant de fonction
Riz. Boîte de dialogue de l'assistant 16 fonctions
5. Dans la boîte de dialogue qui s'affiche pour saisir les arguments de la fonction (Fig. 17) :
Riz. 17. Boîte de dialogue Arguments de fonction
Remplissez-les un par un :
· Lookup_value- cellule B3
· numéro_colonne- le nombre ordinal (pas une lettre !) de la colonne dont vous devez prendre la valeur de la somme - 2
· Interval_view - entrez la valeur FALSE, cela signifie que la recherche seule correspondance exacte.
6. Cliquez sur OK et copiez la fonction saisie dans toute la colonne.
7. Entrez dans la cellule E3 la formule de comptage Dette restante (= С3-ré3). Copiez la formule saisie dans toute la colonne pour calculer automatiquement Dette restante.(fig. 18).
https://pandia.ru/text/78/392/images/image019_7.jpg "largeur =" 633 "hauteur =" 491 ">
Riz. 19 Boîte de dialogue Assistant de texte
4. À la première étape de l'assistant, sélectionnez Format des données sources, c'est-à-dire un caractère qui sépare le contenu des futures colonnes individuelles (avec des délimiteurs). Cliquer sur Davantage.
5. A la deuxième étape de l'assistant, vous devez spécifier quel caractère est le délimiteur. Vérifiez-le espace(fig. 20). Cliquer sur Davantage.
Riz. 20 Assistant de texte de boîte de dialogue. Paramétrage des séparateurs
6. À la troisième étape, pour chacune des colonnes résultantes, en les mettant en surbrillance dans la fenêtre de l'assistant, sélectionnez le format Texte(fig. 21). Cliquez sur Terminer lorsqu'Excel vous demande de remplacer les cellules feuilles par l'affirmative.
En conséquence, le texte sera divisé en 3 colonnes, ce qui était requis dans la tâche (Fig. 22).
Riz. 21. Fenêtre de dialogue Assistant texte. Définition du format des données de colonne
Riz. 22. Résultat de la division par colonnes.
Tâche 5 Collez automatiquement le texte de plusieurs cellules à l'aide de la formule et du signe &.
1. Créez une nouvelle feuille. Donne lui un nom et.
2. Entrez dans les cellules A1, B1, C1 - , respectivement.
3. Sélectionnez la cellule D1. Dans la barre de formule, saisissez la formule suivante : = UNE1&" "& B1&" "& C1 puis appuyez sur Entrée.
De ce fait, le nom, le prénom et le patronyme avec les espaces nécessaires seront regroupés dans la cellule D1 (Fig. 23).
Riz. 23. Le résultat de la combinaison du nom complet dans une seule cellule.
Tâche 6. Coller automatiquement le texte de plusieurs cellules à l'aide de la fonction Extraire les premières lettres du texte GAUCHE.
1. Créez une nouvelle feuille. Entrez dans les cellules A1, B1, C1 - , respectivement.
2. Sélectionnez la cellule D1. Dans la barre de formule, saisissez la formule suivante : = UNE1 & "" & GAUCHE (B1; 1) & "." & GAUCHE (C1; 1) & "."
3 Appuyez sur Entrée (Figure 24).
Riz. 24 Résultat du collage de texte dans différentes cellules.
Exercice 7. Transposer les données d'une table à l'aide d'une formule matricielle et de la fonction TRANSPOSE
1. Créez une nouvelle feuille et nommez-la TRANSPOSE. Entrez les données comme indiqué dans la Fig. 25.
Riz. 25 Données de la feuille TRANSPOSER
2. Sélectionnez la plage de cellules devant contenir le tableau transposé. Étant donné que le tableau de cellules d'origine était de 10 lignes et 2 colonnes, nous devons sélectionner une plage de cellules vides de 2 lignes et 10 colonnes.
3. Entrez la fonction de transposition = TRANSPOSE dans la barre de formule
4. En tant qu'argument de la fonction, sélectionnez votre tableau de cellules A1 : B10 et fermez la parenthèse.
Veuillez noter que vous avez affaire à un tableau et que, par conséquent, pour entrer une formule, vous n'avez pas besoin d'appuyer simplement surEntrer!!!
5. Cliquez sur Ctrl+ Changement+ Entrer... Dans la barre de formule, Excel a automatiquement mis la formule que vous avez créée entre des accolades. Le résultat est un "réseau inversé" (Fig. 26).
Riz. 26. Résultat de la transposition des données
Tâche 8. Sélectionner des données dans le tableau qui sont répétées plus d'une fois à l'aide de la mise en forme conditionnelle
1. Créez une nouvelle feuille et nommez-la Mise en forme conditionnelle.
2. Copiez les cellules B3 : B22 de la feuille RECHERCHEV dans celle-ci.
3. Mettez en surbrillance toute la liste. Sélectionnez Principal - Mise en forme conditionnelle - Créer une règle dans le menu.
4. Sélectionnez Type de règle - Utiliser une formule pour définir des cellules formatées... Dans la ligne appropriée, entrez la formule :
COUNTIF ($ A : $ A ; A2)> 1
Cette fonction simple recherche combien de fois le contenu de la cellule actuelle apparaît dans la colonne A. Si ce nombre de répétitions est supérieur à 1, c'est-à-dire que l'élément a des doublons, alors la cellule est remplie.
5. Pour sélectionner la couleur de surbrillance dans la fenêtre Mise en forme conditionnelle appuie sur le bouton Format... et aller à la contribution Vue... Sélectionnez jaune et cliquez sur OK.
Riz. 27. Boîte de dialogue de mise en forme conditionnelle
Par conséquent, les données répétées plus d'une fois seront marquées en jaune dans le tableau.
Tâche 9. Créer un rapport à l'aide du tableau croisé dynamique
1. Créez une nouvelle feuille et nommez-la Tableau croisé dynamique... Remplissez-le comme indiqué sur la fig. 28.
Riz. 28. Fiche technique Tableau croisé dynamique
2. Mettez en surbrillance la cellule active dans le tableau avec des données (n'importe quel champ de la liste) et cliquez dans le menu Insertion - Tableau croisé dynamique - Tableau croisé dynamique
3. Dans la fenêtre qui apparaît, remplissez tout comme indiqué sur la fig. 29.
Riz. 29 Assistant de tableau croisé dynamique
4. Cliquez sur OK. La fenêtre suivante apparaîtra :
Histogramme "href =" / text / category / gistogramma / "rel =" bookmark "> un histogramme.
1. Ouvrez un tableur Microsoft Excel 2007 et créez un classeur nommé Travaux de laboratoire n°1.
2. Vous devez créer une table de paie pour les employés de l'entreprise.
3. Pour simplifier la saisie des données dans le tableau, créez une liste déroulante (Fig. 22) contenant les noms complets des employés de l'entreprise.
Riz. 22. Liste déroulante
4. Insérez une autre feuille dans le classeur Excel en utilisant le raccourci dans la ligne Onglet Feuille.
5. Sur une nouvelle feuille, créez une liste d'employés (fig. 23).
Riz. 23. Liste des employés de l'entreprise
6. Pour trier le nom complet par ordre alphabétique, exécutez la commande : onglet ruban Données Grouper Trier et filtrer bouton .
7. Sélectionnez la plage de cellules A1: A10 et cliquez sur la case Nom sur le bord gauche de la barre de formule. Entrez un nom pour les cellules, par exemple Personnel... Appuyez sur la touche Entrer.
8. Pour empêcher d'autres utilisateurs d'afficher et de modifier la liste résultante, protégez et masquez la feuille sur laquelle elle se trouve.
9. Cliquez avec le bouton droit sur l'onglet de la feuille. Sélectionnez une commande dans le menu contextuel.
10. Dans la boîte de dialogue Protection des feuilles(fig. 24) entrez le mot de passe pour désactiver la protection des feuilles. Au chapitre Autoriser tous les utilisateurs de cette feuille décochez tous les éléments. Cliquez sur le bouton d'accord.
Riz. 24. Boîte de dialogue Feuille de protection
11. Dans la boîte de dialogue Confirmation mot de passe entrez à nouveau le mot de passe.
12. Faites un clic droit sur l'onglet de la feuille et sélectionnez la commande Cacher.
13. Allez à Feuille 1 et créer un tableau Préparation de la paie
(fig. 25). Colonne Nom complet remplir à l'aide de la liste déroulante.
Riz. 25. Structure des tableaux
14. Sélectionnez la plage de cellules dans laquelle vous souhaitez placer la liste déroulante.
15. Sur l'onglet Données en groupe Travailler avec des données choisis une équipe La validation des données.
16. Dans la boîte de dialogue La validation des données préciser le type et la source des données (fig. 26).
17. Ouvrez l'onglet Message à saisir(fig. 27). Remplissez les champs vides.
Riz. 26. Boîte de dialogue de validation des données
Riz. 27. Message lors de la saisie des données
18. Allez dans l'onglet Message d'erreur(fig. 28). Remplissez les champs Vue, Titre et Un message.
Riz. 28. Message sur erreur de saisie de données
19. Pour les en-têtes de tableau, définissez l'habillage de texte(bouton situé sur la barre d'outils Alignement onglets du ruban le principal).
20. Gelez les deux premières colonnes et la ligne d'en-tête du tableau. Pour ce faire, sélectionnez une plage de cellules C5 : I20 et exécutez la commande : onglet du ruban Vue Grouper La fenêtre bouton .
21. Colonne Un salaire remplissez avec des données arbitraires et définissez le format monétaire des cellules à l'aide de la commande :
onglet du ruban le principal barre d'outils Numéro dans la liste déroulante des formats, sélectionnez Format monétaire.
22. Élaborons une formule de calcul du bonus, qui est de 20 % du salaire. Toute formule commence par un signe = , alors va dans la cellule F5 et introduire la formule = E5 * 20%(ou = E5 * 0,2).
23.À l'aide du marqueur de saisie semi-automatique (croix noire près du coin inférieur droit de la cellule sélectionnée), copiez la formule dans la zone
F6 : F11.
24. Entre les colonnes Prix et Impôt sur le revenu insérer une colonne Total cumulé, dans lequel calculer le montant Un salaire + Prix.
25. Remplissez les colonnes restantes du tableau, en tenant compte du fait que l'impôt sur le revenu est de 13% du montant évalué.
26. Calculez le montant à émettre en dollars, pour cela définissez le taux du dollar actuel, par exemple 32, et dans la cellule J5 entrez la formule : = I5 / $ CA 14 $... Signer $ utilisé dans une formule de sorte que lorsqu'il est copié à l'aide d'un marqueur de saisie semi-automatique, l'adressage de la cellule ne change pas.
27. Pour les cellules contenant des données monétaires, définissez le format approprié.
28. Utilisation de la fonction SOMME, calculez le montant total de l'impôt sur le revenu. Pour ça:
Placer le curseur dans une cellule H12;
· Mettez le signe =;
· Dans la barre de formule, cliquez sur le bouton ;
· Dans la boîte de dialogue apparue de l'assistant de fonction (fig. 29) sélectionnez la catégorie Mathématique, une fonction SOMME;
En tant qu'argument de fonction SOMME mettre en évidence la plage de sommation H5 : H11;
appuie sur le bouton D'ACCORD.
29. De même, calculez le montant total à émettre en dollars et le montant total à émettre en roubles.
Riz. 29. Assistant de fonction
30. Trouvez la moyenne ( MOYENNE), le minimum ( MIN) et maximum ( MAXIMUM) les salaires.
31. En utilisant la mise en forme conditionnelle, marquez en rouge Montants à émettre, moins de 5 500 roubles. Exécutez la commande : onglet du ruban le principal Grouper modes la liste déroulante Mise en forme conditionnelle Règles de sélection des cellules.
32. Construisez un diagramme Salaires des employés de l'entreprise
(fig. 30). Sélectionnez les colonnes en même temps NOM COMPLET. et Montant à émettre(tout en maintenant la touche Ctrl enfoncée) et sur l'onglet du ruban Insérer dans la barre d'outils Diagrammes sélectionner une vue graphique à barres.
33. Utilisation de l'onglet du ruban Disposition, insérez les étiquettes des axes et le titre du graphique.
Riz. 30. Un exemple de schéma
34. Construisez un diagramme circulaire montrant la relation entre le montant total par émission et l'impôt sur le revenu total (Figure 31).
Riz. 31. Un exemple de conception de camembert
Travail de laboratoire n°1. Formules et fonctions MS Excel
Préparez des déclarations pertinentes en utilisant les directives fournies.
Exercice 1... Créez une annonce pour la vente d'appartements selon l'échantillon (Fig. 1).
Riz. 1. Annonces de vente d'appartements
Regroupez les données disponibles par appartement sous forme de liste (Fig. 2).
Riz. 2. Données sur les appartements à vendre
Dans la cellule G2 entrez la formule :
A2 & "carré," & B2 & ", zone :" SD2 & "," & E2 & "sol," & TEXTE (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 annonces reçues à l'aide de la commande Formater | Format automatique.
Tâche 2. Générez et remplissez une liste de réévaluation des immobilisations sous la forme illustrée à la Fig. 3.
Dans la cellule A1 entrez le nom de l'instruction.
Dans les cellules A4 : F4 saisir les noms des champs du relevé : Nom de l'objet, Valeur comptable (BC), Amortissement de l'objet (IO), Valeur résiduelle (OS), Coût complet de remplacement (UPC), Valeur dépréciée de remplacement(s). Domaine Nom de l'objet comprend les lignes suivantes : Département de gestion et de marketing, Département des transports, Atelier de montage, Atelier de finition, entrepôt n° 1, entrepôt n° 2, entrepôt n° 3, Total.
Formules de calcul :
OS = BS - E/S
VPS = BS * K
VOS = OS * K
où k est un coefficient égal à :
3.3 - si le 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 le BS est de 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: = B5 * SI (B5<=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))
Le total de lignes résultant à utiliser, par exemple, pour une cellule À 12 formules :
SOMME (B5 : B11), ou sélectionnez la plage de cellules B12 : F12 et utilisez 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. Liste de réévaluation des immobilisations
Devoir 3... Formez et remplissez une feuille de rapport pour le travail d'un réseau de clubs informatiques sous la forme illustrée à la Fig. 4.
Riz. 4. Liste des travaux du réseau des clubs informatiques
Dans la cellule A1 entrez le nom de l'instruction.
Dans les cellules A à Z : 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, Groovit, Polygone, Gelaks, Zvezda, Hexen, Antey, Arsenal, Arena, Pirogue, Total.
Les formules de base pour les calculs, qui sont copiées pour des calculs similaires par lignes, sont présentées dans le tableau 1.
Formules de calcul
Formatez la facture reçue.
Tâche 4. Formez sur la feuille de calcul la déclaration "Calcul des salaires des employés du département de recherche et de conception" Alpha "(Fig. 5).
Riz. 5 Relevé de paie
Placez le nom de la déclaration dans la cellule A2- Calcul des salaires des employés du bureau d'études et de conception "Alpha", centre sur la marge gauche (par exemple, par l'équipe Formater | Cellules| languette Alignement ou le bouton correspondant Aligné à droite ( dans la barre d'outils).
Dans les cellules A à Z : KZ saisir les noms des champs du relevé : n° pp, nom, prénom, patronyme, fonction, taux tarifaire, ancienneté, k, prime d'ancienneté, total, pourcentage d'impôt, retenue, versement.
en tête de feuille - pour chaque colonne - créez des notes cachées. Les notes sont créées par la commande Insérer | Noter(vous pouvez aussi utiliser la barre d'outils Revoir, qui ajoute la commande Vue| Barres d'outils| Revoir):
№ пп - numéro de l'employé du département;
Nom et .environ. - tous les noms des personnes travaillant dans le service d'études et de conception sont saisis ;
Poste - le poste occupé au moment de remplir la déclaration ;
le taux du tarif est l'équivalent monétaire de la position occupée ;
Expérience - le nombre total d'années travaillées est inscrit au moment de remplir la déclaration;
k est le coefficient d'ancienneté ;
prime d'ancienneté - un équivalent en espèces pour l'ancienneté ;
total - accumulation des salaires, compte tenu du taux tarifaire et de l'ancienneté;
pourcentage d'impôt - détermine le pourcentage de déductions au budget;
Retenue - l'équivalent monétaire des déductions au budget ;
Décaissement - le montant à décaisser.
Lors du calcul dans l'énoncé, tenez compte des éléments suivants :
к, Indemnité d'ancienneté, Total, Pourcentage d'impôt, Retenue, paiement - sont calculés à l'aide des formules appropriées, en utilisant la saisie semi-automatique ou en copiant la formule.
Le coefficient k est attribué à partir 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 de cellule F4:
SI (E4<=5;0,1;ЕСЛИ(И(Е4>5 ; E4<=10);0,2;ЕСЛИ(И(Е4>dix; E4<=15);0,25;0,3)))
prime d'ancienneté - un équivalent en espèces pour l'ancienneté. Formule de cellule G4:
G4:
(entré par la commande Format | Cellules| onglet Numéro, dans la liste Formats de nombres choisir Tous les formats et sur le terrain Un type entrez le format spécifié).
total - le taux tarifaire, compte tenu de l'ancienneté. Formule de cellule H4:
Format de nombre personnalisé pour une cellule H4:
Pourcentage d'impôt - prend en compte que : 2% - l'accumulation (au total) peut aller jusqu'à 7000 roubles. inclus, 10% - plus de 7000 roubles. jusqu'à 10 000 r. inclus, 20% - plus de 10 000 roubles. jusqu'à 25 000 roubles. inclus, 35% - dépassant 25 000 roubles. Formule de cellule I4:
SI (H4<=7000;0,02;ЕСЛИ(И(Н4>7000 ; H4<=10000);0,1;ЕСЛИ(И(Н4>10000 ; H4<=25000);0,2;0,35)))
Format du numéro de cellule I4- Pourcentage.
La retenue est l'équivalent monétaire des impôts. Formule de cellule J4:
Format de nombre personnalisé pour une cellule J4:
Paiement - montant Pour émission : Total sans retenue.
Exigences pour la colonne d'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, plus de 15 - en rouge.
Utiliser l'équipe Formater | Cellules et entrez un format personnalisé pour la cellule E4:
[Rouge] # ## 0 ;
et utilisez aussi la commande Formater | Mise en forme conditionnelle.
Si un nombre négatif d'années est entré, une fenêtre correspondante devrait apparaître. Pour chèque
entrer des nombres utiliser la commande Données | Vérification | languette Message d'erreur.
Pour le champ Tarif - afficher un message permanent : Taux de tarif. Attention lors de la saisie du tarif pour obtenir lequel utiliser 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 tarif ne peut pas être négatif qui est formé à travers un format personnalisé :
# ## 0,00 $ ; [Rouge] "Le taux de tarif ne peut pas être
négatif! "
Solution
Pour les principaux versements d'un prêt remboursé 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 OSNPLAT) Dans notre cas, la fonction SPPLT a la forme : OSPLT (12%, 4, 5, 1 000 000 000)
La saisie des données et les calculs sont effectués conformément à la Fig. 4.4.
Riz. 11. Calcul des principaux versements du prêt
Dans 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 sur un prêt) à taux d'intérêt constant, la fonction PMT est utilisée (dans les versions antérieures, la fonction PLAT) :
PMT (Taux ; Nper ; Bs ; Ps ; Type)
Dans notre cas, la fonction PMT a la forme :
PMT (10 % / 12 ; 20 * 12 ; - (350 000 * (1-25 %))) - paiements mensuels ;
PMT (10 % ; 20 ; - (350 000 * (1-25%))) - paiements annuels.
La solution du problème est montrée dans la Fig. 12 et 13.
Riz. 12. Calcul d'un prêt hypothécaire
Riz. 13. Formules de calcul des tribunaux hypothécaires
Tâche 3. Déterminez quel montant sera sur le compte si 52 000 roubles. pondu 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 BS) :
BS (Taux ; Nper ; Pt ; Ps ; Type)
Pour notre tâche, la fonction BS prendra la forme :
BS (11% / 12; 20 * 12;, -52 000)
La solution du problème est montrée dans la Fig. 14, et la formule de la cellule B26:
BS (B22 / B23; B24 * B23 ;; - B21)
Riz. 14. Calcul de la valeur future du dépôt
Devoir 4... Une obligation d'une valeur nominale de 200 000 roubles. sorti depuis 7 ans. La procédure suivante pour le calcul des intérêts est envisagée : la première année - 11 %, les trois années suivantes - 16 % chacune, les autres
Solution
Pour calculer la valeur accumulée d'une obligation à un taux d'intérêt composé, utilisez la fonction :
BZRASPIS (Primaire ; Plan)
Pour notre tâche, la fonction prend la forme :
BZRASPIS (200000 ; (11 % ; 16 % ; 16 % ; 16 % ; 20 % ; 20 % ; 20 %))
La solution est montrée dans la Fig. 15, et la formule de calcul dans la cellule B42:
BZRASPIS (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 du coût couru d'une obligation à un taux d'intérêt composé
Solution
Le taux interne de rotation 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
La saisie des données initiales se fait conformément à la Fig. 16. Initialement, pour le calcul, le montant des coûts du projet est sélectionné arbitrairement (la cellule de ce montant peut même être laissée vide) et les calculs sont effectués.
Dans la cellule À 12 la formule est saisie = VSD (B4 : B9).
un exemple est montré dans la Fig. 18.
Riz. 17 Fenêtre de sélection des paramètres
Riz. 18 Coûts initiaux du projet calculés
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 3000 roubles en un an, après deux - 5000 roubles, après trois - 9000 roubles. A 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 :
VAN (Taux; valeur 1; valeur 2;...)
La saisie des données initiales se fait conformément à la Fig. 19. Initialement, pour le calcul, un pourcentage arbitraire du taux d'actualisation annuel est sélectionné (la cellule avec cette valeur peut même être laissée vide) et les calculs sont effectués. Dans la cellule À 9H la formule est saisie : = VAN (B6 ; B2 : B4)
Riz. 19. Feuille de travail pour résoudre le problème avec un investissement en capital inégal
Dans la cellule C7 vous pouvez entrer la formule suivante :
SI (B7 = 1 ; "année" ; SI (ET (B7> = 2 ; B7<=4);"года";"лет"))
Dans le champ Situé dans la cellule Entrer À 9H, c'est-à-dire l'adresse de la cellule dans laquelle vous devez obtenir la valeur souhaitée du montant de la transaction (15000);
Dans le champ Sens entrez 15000, c'est-à-dire la valeur souhaitée du montant de la transaction lui-même ;
Dans le champ En changeant 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 montrée dans la Fig. vingt.
Riz. 19. Fenêtre Sélection des paramètres pour le problème de l'investissement en capital inégal
Riz. 20 Taux d'intérêt optimal
Travaux de laboratoire n° 6. Table de correspondance
Table de correspondance vous permet d'analyser le changement dans le résultat pour une plage arbitraire de données initiales. Plusieurs tables de recherche peuvent être placées sur une seule feuille de calcul. Cela permet d'analyser simultanément diverses formules et statistiques.
Une table de correspondance peut être utilisée pour :
Passe à une valeur d'origine lors de l'affichage des résultats d'une ou plusieurs formules ;
- changer deux valeurs d'origine en affichant les résultats d'une seule formule.
Utilisation d'une table de recherche avec une variable modifiable et plusieurs formules
Considérons cette technique à l'aide de l'exemple du calcul des mensualités de crédit (le calcul se fait à l'aide de la fonction PMT) et des paiements d'intérêts (la fonction PROTSPLAT) :
La solution à ce problème implique les étapes suivantes :
1. Créez ou accédez à la feuille de calcul où le problème analysé sera résolu.
2. Organisez l'interface de manière à ce que toutes les données saisies soient compréhensibles pour l'utilisateur :
Les signatures et données nécessaires sont saisies dans les cellules correspondantes de la feuille de calcul (Fig. 21).
Dans la cellule À 5- formule: = PMT (G$ 4/12$; G$ 3$ * 12; G$ 2$)
Dans la cellule D6- formule: = PROCPLATE ($ D $ 4; $ D $ 5; $ D $ 3; $ D $2)
Riz. 21 Préparation des données initiales
Conseil : lors de la résolution de problèmes liés à l'utilisation d'une table de recherche, il est recommandé d'utiliser l'adressage absolu des cellules dans les formules. Cela facilite l'exécution correcte des calculs dans les cellules de la feuille de calcul.
3. Après avoir préparé les données initiales, allez à l'endroit de la feuille de calcul où les valeurs calculées seront situées en fonction du changement d'une variable et de diverses formules calculées (Fig. 22).
Dans la première ligne 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 mettre la formule elle-même (cellules B10 et C10 contiennent des formules).
A gauche, placez les différentes valeurs des données sources 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 correspondance 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 l'équipe Données | Table de correspondance et dans le dialogue Table de correspondance(fig. 23) indiquent où et quelles valeurs doivent être remplacées.
Dans notre exemple, substitution des valeurs de taux d'intérêt (colonne des valeurs initiales A10 : A19) aller en cellule À 4 HEURES, puisque cette cellule indique initialement la valeur du 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 Utilisation de la table de recherche
Riz. 24 Valeurs calculées pour une table de recherche unidimensionnelle
Utilisation d'une table de recherche avec deux variables mutables et une formule
Regardons cette technique en utilisant l'exemple du calcul des mensualités de prêt en fonction de différentes échéances et de différents taux d'intérêt. La solution au problème implique
prochaines étapes:
1. Organisez sur la feuille de calcul l'interface utilisateur appropriée pour un ensemble de données d'entrée (Fig. 25) :
Taux d'intérêt spécifique - 3% (cellule À 4 HEURES);
La date d'échéance spécifique est de 3 ans (cellule OT);
Formule de cellule À 5:
PMT (G$ 4/12 $; G$ 3$ * 12; G$ 2$)
Riz. 25. Préparation des données de tâche
2. Préparez le tableau suivant (fig. 26) :
Placez les données à modifier dans la colonne de gauche et dans la ligne du haut - dans notre cas, les valeurs des taux d'intérêt (cellule À 4 HEURES) sont dans la gamme B10 : B14, et les valeurs de maturité (cellule OT) - dans le périmètre C9 : F9;
A l'intersection d'une ligne et d'une colonne dans le coin supérieur gauche, placez la formule souhaitée ou une référence à celle-ci (cellule À 9H contient une formule de cellule À 5, dans laquelle l'adressage absolu des cellules est requis).
Riz. 26. Préparation d'une plage pour l'utilisation d'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 de calcul.
4. Exécutez la commande Données | Table de correspondance et dans la fenêtre qui apparaît (Fig. 27) indiquez où et quelles valeurs doivent être remplacées.
Riz. 27 Utilisation d'une table de recherche dans les calculs à deux paramètres
Dans cet exemple, substitution des valeurs de taux d'intérêt (colonne des valeurs initiales B10 : B14) arrive à 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 maturité (une ligne de valeurs C9 : F9) - dans la cellule OT... 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 2D
Commenter: Une fois la table de recherche créée, vous ne pouvez pas modifier une seule formule dans la table. Les valeurs de données dans un tableau peuvent être modifiées en modifiant les valeurs de données d'origine dans la colonne de gauche et la rangée supérieure.
Données initiales : les coûts du projet s'élèveront à 700 millions de RUB. Les revenus attendus au cours des 5 prochaines années s'élèveront respectivement à 70, 90 300 250 et 300 millions de roubles. Évaluer l'économie
la 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
La fonction IRR permet de calculer le taux de rotation interne d'un investissement (taux de rentabilité interne) :
VSD (Valeurs ; Hypothèses)
Dans notre cas, la fonction de résolution du problème utilise uniquement l'argument Valeurs, dont l'un est nécessairement négatif (coûts du projet). Si le taux de rotation interne des investissements s'avère supérieur au taux de rentabilité du marché, le projet est alors considéré comme économiquement viable. Dans le cas contraire, le projet devrait être rejeté.
1. La solution est illustrée à la fig. 30. Formules de calcul :
dans la cellule À 11 HEURES:= VSD (B75 : B80)
dans la cellule C11 := SI (B84> B82 ; "Le projet est économiquement viable" ; "Le projet doit être rejeté")
Riz. 30. Calcul du taux interne de rotation des investissements
2. Considérez cet exemple pour toutes les combinaisons de données d'entrée. Pour créer un script, utilisez 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 modifiées (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 le bouton 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 script enregistré
3. Pour ajouter de nouveaux scénarios au problème considéré, 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 des données d'origine (Fig. 34).
En figue. 34 scénario RPM_1 correspond aux données (-700; 70; 90; 300; 250; 300), scénario RPM_2 - données (-600; 50; 100; 200; 200; 300), scénario RPM_Z - données (-650; 90; 120 ; 200 ; 250 ; 250), scénario speed_ of_4-data (-500, 100, 100, 200,
En appuyant sur le bouton Se désister, vous pouvez afficher les résultats du calcul pour la combinaison correspondante de valeurs d'origine sur la feuille de calcul.
Riz. 34. Fenêtre Gestionnaire de scripts avec des scripts ajoutés
pour le calcul du taux de rotation des investissements
Riz. 4.31. Ajout de cellules de résultat à la fenêtre Rapport de scénario
4. Pour obtenir un rapport récapitulatif sur tous les scénarios ajoutés, appuyez sur le bouton Reportage 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 de script 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 les scénarios de calcul du taux de rotation des investissements
Solution
Pour terminer la tâche :
1. Saisissez les données sur la feuille de calcul conformément à la fig. 38.
Fig. 38 Préparation des données pour la création d'un graphique
2. Sélectionnez une plage avec la souris A5 : B12 et lance la commande Insérer | Diagramme ou cliquez sur le bouton de l'assistant de diagramme 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, un type de graphique approprié est du type histogramme.
Quelle ligne est sélectionnée comme valeur de données (remplissez les champs Ligne et Valeurs). Dans notre cas, sur le terrain Ligne on ajoute le volume au champ Les valeurs- gamme G$ 5 : G$ 12$;
D quelle ligne servira d'étiquettes d'axe X- sur le terrain Etiquettes de l'axe X entrer l'année et indiquer la plage $ A $ 5: $ A $ 12.
Riz. 39. Définir des séries de données
4. Formatez le diagramme obtenu à l'aide du menu contextuel de chacun de ses éléments (Fig. 40).
Riz. 40. Graphique construit Volume des ventes
Tâche 2. Tracez la fonction : à= cos 3 (πx).
Solution
Le résultat de cet exemple est montré dans 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 avec le marqueur de saisie semi-automatique préparer toute la gamme A7 : A27.
2. Dans la cellule À 7 HEURES entrez la formule :
= (POP (PI () * A7)) ^ 3 et copiez-le dans la plage Q7 : B27.
3. Représentez graphiquement la fonction à l'aide de l'assistant graphique.
4. Formatez le graphique résultant.
Riz. 41 Exemple de tracé d'un graphe de fonction
Solution
A1 : J35, A1 : J1)
2. Formez une gamme de critères pour le filtre étendu conformément à la fig. 43.
Riz. 43. Gamme de critères pour un filtre étendu pour le problème des voitures blanches et noires
3. Exécutez la commande
4. Les données filtrées sont illustrées à la Fig. 44.
Riz. 44. Données pour le problème des voitures blanches et noires, sélectionnées par un filtre étendu
Tâche 2. Déterminez si la liste (voir Fig. 42) contient des voitures dont l'année de production est supérieure à 2000 et dont le kilométrage est supérieur à 100,00 km, mais inférieur à 100 000 km, ou des Mercedes noires dont le prix est plus de 20 000 USD. e., mais moins de 30 000 USD e.
Solution
1. Ouvrez la liste à filtrer (la liste est dans la plage A1 : JЗ5, ligne d'en-tête - dans la plage A1 : J1)
MZ : M4... Dans la cellule MH entrez Condition. Dans la cellule 4 entrez la formule :
OU (ET (G2> 10000; G2<100000;D2>1990); ET (C2 = "Mercedes"; F2 = "Noir"; H2> 20000; H2<30000))
3. Exécutez la commande Données | Filtre | Filtre avancé.
4. Les données filtrées sont illustrées à la Fig. 45.
Riz. 45. Données pour le problème de kilométrage, sélectionnées par le filtre étendu
Tâche 3. Déterminez les voitures de couleur blanche ou rouge, 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 est dans la plage A1 : JЗ5, ligne de titre - dans la plage A1 : J1)
2. Générer un critère calculé pour le filtre étendu dans la plage L1 : L2... Dans la cellule L1 entrez Condition. Dans la cellule L2 entrez la formule :
ET (OU (G2 = "blanc"; G2 = "rouge"); H2<СРЗНАЧ($Н$2:$Н$133); G2 >= MOYEN (G$ 2 : G$ 13$ 3))
3. Exécutez la commande Données | Filtre | Filtre avancé.
Solution
1. Sélectionnez la liste (ou - placez le pointeur de cellule sur la liste) et triez (la commande Données | Tri) d'abord - à travers le champ Vendeur, puis - à travers le champ Date de la vente(fig. 47).
Riz. 46. Liste des ventes
2. Appliquer la commande Données | Résultats. Dans la fenêtre Sous-totaux régler les paramètres selon fig. 48 : Pour obtenir le niveau supérieur (premier) des totaux, le nombre total d'articles vendus par un vendeur particulier.
Riz. 47 trier une liste
Riz. 48. Fenêtre Sous-totaux pour obtenir les totaux par champ Vendeur
3. Pour obtenir le deuxième niveau de totaux, placez le pointeur de cellule dans la liste avec les totaux reçus, puis exécutez la commande Données | Résultats, en mettant dans la fenêtre Sous-totaux paramètres conformément à la Fig. 49.
49. La fenêtre Sous-totaux pour obtenir les totaux par champ Date de la vente
4. Les résultats intermédiaires reçus sont indiqués sur la fig. cinquante.
Riz. 50 sous-totaux imbriqués
Consolidation des données
La consolidation vise à résumer des données homogènes. Sa mise en œuvre implique l'utilisation de la technique suivante :
1. Indiquez l'emplacement des futures données consolidées.
2. Sélectionnez une commande Données | Consolidation.
3. Dans la fenêtre qui s'ouvre, indiquez les plages de données à consolider.
4. Précisez la méthode de consolidation :
Selon la position dans la plage - toutes les cases à cocher de la zone sont décochées Utiliser comme noms ;
Selon les en-têtes de ligne et de colonne - définir
drapeaux légendes de première ligne et les valeurs de la colonne de gauche.
5. Sélectionnez le type de consolidation, c'est-à-dire indiquez quelle opération sera effectuée avec les données consolidées.
6. Si nécessaire, indiquez l'ajout d'une structure - cochez la case Créer des liens vers 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 forme de 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 marchandises vendues
Consolidation conformément aux données de la Fig. 52. Les données combinées sont présentées à la fig. 53.
Tableaux croisés dynamiques
Les tableaux croisés dynamiques sont un outil pour regrouper, résumer et analyser les données trouvées dans les listes MS Excel ou dans les tableaux créés dans d'autres applications. Consolidation externe
Riz. 52 . Saisie 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 pour regrouper, résumer et analyser les données trouvées dans les listes MS Excel ou les tableaux liés dans d'autres applications. Les tableaux croisés dynamiques externes sont une structure qui vous permet de disposer des données en 3D. Les tableaux croisés dynamiques peuvent être utilisés :
Pour résumer une grande quantité du même type de données ;
Réorganiser les données (par glisser-déposer) ;
Pour la sélection et le regroupement des données ;
Pour construire des diagrammes.
Les tableaux croisés dynamiques sont créés à l'aide de l'Assistant Tableau croisé dynamique (commande Données | Sommaire) selon la méthode suivante :
1. Choisissez un emplacement pour le tableau croisé dynamique, c'est-à-dire placez le pointeur de cellule à l'emplacement souhaité sur la feuille de calcul.
2. Exécutez la commande Données| Sommaire.
3. Définissez la plage de données initiale en suivant 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é à la Fig. 55, la fenêtre de la 3ème étape de l'assistant s'ouvrira (fig. 56).
4. Avant de spécifier l'emplacement de la future table (Fig. 56), vous devez appuyer sur le bouton Disposition et dans la fenêtre qui s'ouvre (Fig. 57) formez la mise en page 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. 54 Détermination de l'emplacement des données pour un tableau croisé dynamique
Riz. 55 Plage de données pour le tableau croisé dynamique
Riz. 56. Spécification de l'emplacement du futur tableau croisé dynamique
5. Pour définir l'opération requise pour les champs placés dans la zone Données, ou en définissant 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 d'un champ de tableau croisé dynamique(fig. 58).
6. Appuyez sur le bouton Paramètres(Fig. 56) et dans la fenêtre qui s'ouvre (Fig. 59) définissez les paramètres nécessaires du tableau croisé dynamique.
Riz. 57 façonner la mise en page du tableau croisé dynamique
Riz. 58. Fenêtre de calcul de champ de tableau croisé dynamique
Riz. 59 . Définir les options du tableau croisé dynamique
7. Une fois toutes les opérations préparatoires terminées, appuyez sur le bouton Prêt(fig. 56).
Tenez compte des points suivants lors de la création, de la modification et de l'utilisation de tableaux croisés dynamiques :
Emplacement du tableau croisé dynamique - il est conseillé de le placer sur une feuille séparée, car lors de la mise à jour, en regroupant le tableau croisé dynamique, les informations contenues dans les feuilles de calcul à côté du tableau croisé dynamique peuvent être masquées ;
Emplacement des données source - Liste MS Excel, source externe, plages de consolidation situées dans un autre tableau croisé dynamique ;
La nécessité de déterminer lors de la création de la structure du tableau croisé dynamique :
1) les champs situés dans les lignes et les colonnes du tableau ;
2) les champs sur lesquels sont additionnés les résultats (avec le choix de l'opération nécessaire) ;
3) des marges pour les pages, ce qui vous permet de présenter des informations sous forme tridimensionnelle.
Le tableau croisé dynamique est un outil d'affichage de données uniquement. Par conséquent, les données ne peuvent pas être modifiées dans le tableau lui-même. Pour modifier les données du tableau croisé dynamique, vous devez apporter des modifications à la source de données, puis mettre à jour le tableau croisé dynamique (en cliquant sur Mettre à jour les données dans 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 modifications dans les champs des données d'origine. Vous pouvez également manipuler les éléments du tableau croisé dynamique avec la souris.
18.1 Informations théoriques
L'un des principaux objectifs Microsoft Excel consiste à effectuer divers calculs à l'aide de formules et de fonctions intégrées. Formule commence par un signe égal et est une expression qui peut être constituée de nombres, de références (adresses de cellules) ou de fonctions concaténées avec des signes arithmétiques. Les opérations arithmétiques suivantes sont utilisées dans les formules Excel : exponentiation (^) ; multiplicateur (*) ; division (/); addition (+); soustraction (-).
Une fonction est une formule toute faite qui se compose d'un nom de fonction et d'un argument ou de plusieurs arguments, par exemple SUM (A3; C8). Le nom de la fonction définit les actions et les arguments spécifient des valeurs ou des cellules et sont placés entre parenthèses. De plus, il n'y a pas d'espace entre le nom de la fonction et les parenthèses. Pour les calculs utilisant des fonctions, utilisez l'assistant de fonction - étape 1 sur 2, qui est invoqué en cliquant sur l'icône fx dans la ligne de saisie de la formule ou en exécutant la commande Insérer / Formule. Avant d'appeler la fonction, vous devez positionner le curseur dans la cellule où vous souhaitez saisir la fonction.
Les graphiques sont utilisés pour visualiser les dépendances entre les valeurs dans les feuilles de calcul dans Excel.
Avec Excel, vous pouvez créer des graphiques complexes pour les données des feuilles de calcul. Diagrammes sont créés à l'aide de l'assistant graphique, qui peut être invoqué en cliquant sur le bouton Assistant graphique dans la barre d'outils standard, ou en exécutant la commande Insérer / Graphique. Vous pouvez créer un graphique en une seule étape en cliquant sur la touche F11. Avant d'appeler l'Assistant Graphique, vous devez sélectionner la feuille de calcul ou la partie du tableau pour laquelle vous souhaitez créer un graphique.
Par exemple, pour tracer la fonction Y = 2sin 3 (5px) + 7cos (3px) 2
dans Microsoft Excel, il doit être représenté (à l'aide des opérations arithmétiques utilisées dans Excel) sous une forme pratique pour effectuer des calculs. Après avoir transformé la fonction, cela ressemblera à : Y = 2 * (sin (5 * PI () * x) ^ 3 + 7 * cos (3 * PI () * x) ^ 2.
Ensuite, dans les cellules définies pour l'argument "x", vous devez saisir des nombres avec un certain pas (par exemple, de -2 à +2 avec un pas de 0,1), et dans les cellules destinées à accueillir la fonction Y , vous devez saisir la formule = 2 * (sin (5 * PI () * x) ^ 3 + 7 * cos (3 * PI () * x) ^ 2. Dans ce cas, au lieu de l'argument "x", vous devez entrer des références aux cellules dans lesquelles se trouvent leurs valeurs dans la formule. calculs, vous devez mettre en évidence les résultats des calculs et appeler l'assistant de création de diagrammes de l'une des manières, puis créer un graphique de la fonction en quatre étapes à l'aide de l'assistant de diagramme.
Pour mener à bien ce travail de laboratoire, vous devez vous familiariser avec les fonctions, formules, graphiques de Microsoft Excel, qui sont décrits dans le cours magistral ou dans les sources d'information recommandées.
18.2 Objet du travail
Acquisition de compétences pratiques pour travailler avec des formules, des fonctions et des graphiques dans Microsoft Excel.
18.3 Énoncé du problème
Calculer les fonctions :
- Y = 2sin (nx) cos 5 (2nx) + sin (5 nx) 2, où n est le nombre de PI = 3, 14 ;
- Z = cos (2px) sin 3 (nx) - 3cos (4px);
- la plage de changement de l'argument X start = -3, X end = 3, step - 0,1.
Sur la base des résultats des calculs effectués (fonctions Y et Z), tracez les graphiques des fonctions dans un seul système de coordonnées. Placez les graphiques de fonction sur une feuille séparée et entrez les étiquettes de l'axe X.
18.4 Travail pas à pas
18.4.1 Allumer le PC
Cliquez sur le bouton Pouvoir sur l'unité centrale du PC.
18.4.2 Démarrer Microsoft Excel
18.4.2.1 Démarrez Microsoft Excel à l'aide de la commande Menu principal.
Une fois le système d'exploitation complètement chargé, démarrez Microsoft Excel en cliquant sur le bouton Démarrer et en sélectionnant Programmes / Microsoft Office dans le menu principal, Microsoft Office Excel 2003... Cela ouvrira la fenêtre de l'application Microsoft Excel, qui affiche un classeur vierge "Livre 1" avec trois feuilles de calcul.
18.4.2.2 Enregistrez le classeur Excel.
Pour enregistrer le classeur dans Excel, exécutez la commande Fichier / Enregistrer, dans la boîte de dialogue Enregistrer le document, entrez le nom du fichier : Function Graphs. Cliquez sur le bouton OK pour enregistrer le classeur Excel dans le dossier Mes documents.
18.4.3 Évaluation des fonctions
18.4.3.1 Nommer les colonnes A, B, C (Argument X, Fonction Y, Fonction Z).
Procédez comme suit :
- dans la cellule A1, entrez le nom Argument X ;
- dans la cellule B1, entrez le nom Fonction Y ;
- dans la cellule C1, entrez le nom Fonction Z.
18.4.3.2 Remplissage de la colonne A avec les valeurs de l'argument X.
Procédez comme suit :
- dans la cellule A2, entrez la valeur initiale (égale à -3) ;
- sélectionnez la cellule A2 et exécutez la commande Edition / Remplissage / Progression ... ;
- remplir les champs : Emplacement - par colonnes ; Étape - 0,1 ; Type - arithmétique Valeur limite - valeur finale 3;
18.4.3.3 Introduction à la colonne B de la formule de calcul de la fonction Y.
Saisissez dans la colonne B la formule de calcul de la fonction Y à l'aide de l'assistant Fonction ou en la saisissant au clavier :
18.4.3.4 Introduction à la colonne C de la formule de calcul de la fonction Z.
Saisissez dans la colonne C la formule de calcul de la fonction Z à l'aide de l'assistant de fonction ou en la saisissant au clavier. La saisie de la formule de calcul de la fonction Z est similaire à la saisie de la formule de calcul de la fonction Y, décrite au paragraphe 18.4.3.3. L'argument x pour la formule placée dans la cellule C2 est l'adresse de la cellule A2.
18.4.3.5 Remplissez les cellules restantes des colonnes B et C avec des formules.
Pour remplir les cellules des colonnes B et C avec des formules, il est conseillé d'utiliser la méthode de saisie semi-automatique :
- sélectionnez les cellules B2 et C2 en même temps ;
- positionnez le pointeur de la souris sur la poignée de recopie et, tout en maintenant le bouton gauche de la souris enfoncé, faites glisser la sélection vers le reste des cellules. Les cellules seront remplies de formules.
18.4.4 Tracé
18.4.4.1 Sur la base des résultats des calculs, tracez la fonction Y.
Pour tracer la fonction Y sur une feuille séparée, procédez comme suit :
- mettre en évidence les résultats des calculs avec la rubrique Fonction Y ;
- exécutez la commande Insérer / Graphique ;
- en choisissant les paramètres requis à chacune des quatre étapes, tracez le graphique sur une feuille séparée.
18.4.4.2 Sur la base des résultats des calculs, tracer la fonction Z sur le même diagramme que le graphique de la fonction Y.
Pour tracer la fonction Z sur le même schéma que le graphique de la fonction Y, faites :
- mettre en évidence les résultats des calculs avec la rubrique Fonction Z ;
- exécutez la commande Modifier/Copier ;
- ouvrez la feuille avec le graphique de la fonction Y;
- sélectionnez le schéma en cliquant dessus avec le bouton gauche de la souris ;
- coller les données du presse-papiers à l'aide de la commande Edition / Coller.
18.4.5 Saisie des étiquettes pour l'axe X dans le graphique
Pour saisir une étiquette d'axe X sur un graphique, procédez comme suit :
- mettre le diagramme en surbrillance ;
- exécuter la commande de menu Diagramme / Données initiales ... ;
- ouvrez l'onglet Ligne ;
- cliquez sur le bouton Réduire à droite de la zone de texte Étiquettes de l'axe X ;
- accédez à la feuille avec les fonctions, mettez en surbrillance les valeurs de l'argument X, à l'exception du titre, et appuyez sur la touche Entrée ;
- pour fermer la fenêtre Source Data, cliquez sur le bouton OK.
Enregistrez vos modifications dans le fichier.
18.4.6 Arrêt
Informez l'enseignant du travail effectué. Après l'autorisation d'arrêter, fermez l'application Microsoft Excel, après quoi vous pouvez commencer à passer des tests sur le travail effectué.
