Agence de voyage, Exercice Excel débutant

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Agence de voyage : Chiffres d'affaires

Ce deuxième exercice Excel, niveau débutant, fait suite au premier sur le calcul des bénéfices d'une entreprise. Nous y avons décomposé l'apprentissage pour une prise de contact en douceur. Nous y avons énoncé la philosophie du tableur et les techniques professionnelles de calculs. De même, nous avons insisté sur la terminologie, essentielle pour la bonne compréhension. Dans cette nouvelle mise en pratique, nous souhaitons démontrer l'efficacité des méthodes de calculs que nous avons apprises. Bien sûr, nous reviendrons sur les bases abordées précédemment.



La capture ci-dessus illustre un extrait du tableau Excel à partir duquel nous allons devoir résoudre les calculs.

Source et présentation de l'objectif
Comme toujours, nous proposons de récupérer une source de travail préconçue.

• Télécharger le classeur ventes-agence-de-voyage.xlsx en cliquant sur son lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur Excel est constitué d'une seule feuille comme l'indique son onglet, en bas à gauche de l'interface. Et cette feuille est nommée Ventes. Elle propose trois tableaux. Ils sont intimement liés. Ils relatent l'activité d'une agence de voyage.

Le premier tableau synthétise les nombres de billets ayant été vendus sur une période de trois mois, pour plusieurs destinations.



Deux rangées de synthèse sont à considérer. Tout d'abord, le nombre de billets vendus doit être calculé en colonne F, pour chaque destination. Il s'agit d'un bon moyen de les comparer les unes aux autres. Et puis, le nombre de billets vendus doit être calculé en ligne 12 pour chaque mois. C'est un bon moyen de déceler les périodes les plus propices. Dans les deux cas, l'opération est une addition. Et dans ce contexte précis, nous avions appris une méthode, tout à fait efficace, pour exploiter la somme automatique d'Excel.



Le deuxième tableau énumère les prix des billets pour ces destinations et périodes respectives. Il ne demande aucune synthèse. Il doit être utilisé comme intermédiaire pour produire les calculs du tableau final, celui des chiffres d'affaires réalisés.

Le dernier tableau précisément est vierge de données numériques. Elles doivent toutes être calculées. Elles doivent se déduire des deux tableaux précédents. Si nous connaissons le prix du billet pour une période et une destination et le nombrede billets vendus pour ces mêmes période et destination, nous pouvons en déduire le chiffre réalisé. Le calcul consiste en une simple multiplication. Mais souvenez-vous des fondamentaux Excel sur lesquels nous avons insisté lors du premier exercice. Tout d'abord, chaque calcul doit être dynamique. Aucune constante n'est tolérée. Ils doivent être construits en impliquant les cellules par leurs références. Elles seront ainsi considérées comme des variables. Si le nombre de billets ou le prix du billet évolue, le calcul du chiffre d'affaires bâti sur ces variables évoluera aussi. De plus, on ne refait jamais deux fois le même calcul dans Excel. Une fois un chiffre d'affaire calculé, sa formule devra être répliquée automatiquement sur l'ensemble des autres cellules concernées.



Enfin, vous remarquez la présence de deus rangées de synthèse, comme pour le premier tableau, respectivement en colonne F et en ligne 31. Elles consistent à déduire la somme des chiffres d'affaires produits d'une part par destination, et d'autre part par mois. Ces résultats statistiques permettront de comparer efficacement les chiffres entre eux pour établir les conclusions qui s'imposent.

Technique de somme automatique
Nous savons écrire un calcul manuellement. Et pour cela, nous avons appris qu'il devait nécessairement débuter par le symbole égal (=). Mais dans le cas de l'addition d'une grande quantité de valeurs, nous avons compris que la fonction somme automatique d'Excel était plus productive. Le premier exercice sur le calcul des bénéfices a contribué à le démontrer. Et dans un cas particulier comme celui-ci, il existe une méthode on ne peut plus efficace. Les cellules des résultats sont toutes adjacentes aux données à sommer. Il n'y a ni ligne ni colonne de séparation. Et dans ce contexte, comme le prouve le livre Excel gratuit pour débuter les calculs, il suffit d'étendre la sélection des données à additionner jusqu'aux cellules de résultats. Dès lors, la fonction Somme automatique d'Excel se charge de réaliser les additions ligne à ligne et colonne à colonne. Comme nous avons le souci d'insister sur les techniques professionnelles de calculs, nous allons le prouver.

• Dans le premier tableau, bouton de la souris maintenu enfoncé, cliquer et glisser de la cellule C6 à la cellule F12,

Nous sélectionnons ainsi la plage de cellules C6:F12. Dans la syntaxe Excel, les deux points (:) signifient : de C6 à F12. Nous aurions aussi pu commencer par cliquer seulement sur la cellule C6, au croisement donc de la colonne C et de la ligne 6. Alors, avec la touche MAJ maintenue enfoncée, nous aurions cliqué sur la cellule F12. Toutes les cases comprises entre la première et la dernière auraient été sélectionnées. C'est une technique que nous avons approuvée dans les exercices Word débutant, afin de réaliser des sélections précises de portions de textes. Rappelez-vous, la touche MAJ est généralement matérialisée par une flèche verticale orientée vers le haut. Elle est située au-dessus de la touche CTRL sur le clavier. Et sa désignation anglaise est Shift.

• Tout à fait à droite du ruban Accueil situé au-dessus de la feuille, cliquer directement sur le bouton Somme automatique, comme l'illustre la capture ci-dessous,

Instantanément, tous les résultats en ligne et en colonne surgissent. La méthode est presque scandaleuse de simplicité et d'efficacité. Mais souvenez-vous, le contexte est particulier. Les rangées de résultats sont collées au tableau. Il s'agit néanmoins du cas le plus fréquemment rencontré.

Nous avions donné la définition des rubans dans le premier exercice Excel. Il s'agit de ces bandeaux orientés à l'horizontale, au-dessus de la feuille Excel. Ils proposent de très riches fonctionnalités selon l'onglet activé. Et le ruban actif par défaut est le ruban Accueil. Il est décomposé en sections. Chaque section est séparée d'une autre par un trait vertical. Elles regroupent les outils par thème. La section Police offre tous les outils de mise en forme de texte. La section alignement propose des réglages de positionnement des données dans les cellules.

Comme vous l'avez remarqué, le bouton de la somme automatique est matérialisé par une lettre M orientée à 90 degrés. En réalité, il s'agit de la lettre grecque Sigma. Son info-bulle nous rappelle que nous aurions pu réaliser le raccourci ALT + = à la place du clic.

Quoiqu'il en soit, pour obtenir tous ces résultats de synthèse, deux simples actions ont suffi. Il s'est d'abord agi de sélectionner l'ensemble des cellules additionnées en incluant les cellules vides des résultats à placer. Ainsi, aucune ambiguïté n'est soufferte pour Excel. Le tableur comprend qu'il doit tout sommer ligne à ligne et colonne à colonne. Et puis, nous avons simplement cliqué sur le bouton Somme automatique pour procéder.

Enfin et c'est important, bien que nous n'ayons pas énoncé et décomposé le calcul nous-mêmes, Excel a fort logiquement respecté le principe des références relatives. Il a décalé les indices de lignes ou de colonnes des cellules impliquées dans l'addition, selon le sens de déplacement du calcul.

• Cliquer sur le premier total de ligne, soit la cellule F6,

Nous mettons ainsi en évidence le nombre de billets vendus pour la ville de Paris sur la période des trois mois.

Si vous consultez la barre de formule au-dessus de la feuille, vous découvrez la syntaxe de la fonction construite par Excel : =SOMME(C6:E6).



Le calcul débute bien par le symbole égal (=), mais Excel l'a inscrit pour nous. Il s'agit en effet d'une fonction automatique. Puis, suit le nom de la fonction pour l'opération à réaliser, soit Somme. Entre les parenthèses de cette fonction, sont désignées les cellules impliquées dans ce calcul : C6:E6. Souvenez-vous de la signification des deux points dans la syntaxe Excel. La somme est réalisée de C6 à E6. Toutes les cellules intermédiaires sont incluses dans l'opération. Si les deux points étaient remplacés par un point-virgule (;), la somme serait réalisée seulement sur C6 et E6. Quoiqu'il en soit, pour le résultat en F6, l'addition a été opérée sur les cellules de la ligne 6.

• Cliquer maintenant sur la cellule F11 pour la sélectionner,

Nous mettons en évidence la somme des billets vendus pour la ville d'Athènes sur la période des trois mois.



Comme l'indique sa barre de formule, le calcul est fort naturellement identique. Mais les références des cellules impliquées ont été adaptées, ici en ligne, pour suivre le déplacement de la formule. L'opération est réalisée 5 lignes plus bas. Elles intègrent donc les cellules situées 5 lignes en-dessous, à l'indice 11.

La logique est bien sûr respectée pour les colonnes. En cellule C12, la somme est réalisée pour le mois de Juin, soit sur la colonne C : =SOMME(C6:C11). Deux rangées plus à droite, en E12 pour le mois d'Août, les références des cellules impliquées sont donc ajustées en colonne : =SOMME(E6:E11).

Ces remarques sont donc fondamentales pour tous les calculs que nous construirons manuellement. Nous maîtriserons ainsi parfaitement leur réplication.

Souvenez-vous aussi de la zone Nom en haut de la feuille Excel, à gauche de la barre de formule. Elle est encerclée de rouge sur les captures ci-dessus. Elle rappelle sans ambiguïté la référence de la cellule active.

• Sélectionner la cellule du nombre de billets pour Paris en Juin, soit C6,
• Saisir le nombre 54 à la place de 48,
• Puis, valider par la touche Entrée,

Instantanément, trois résultats ont été mis à jour. Cela prouve que nos calculs, bâtis sur les références de cellules donc des variables, sont bien dynamiques. Donc notre tableau peut être exploité comme modèle à l'avenir, puisque désormais les calculs attendent les données pour produire les synthèses. Ces trois résultats sont la somme en Juin en C12, la somme pour Paris en F6 et la somme de tous les billets vendus au croisement en F12.

Enfin, vous notez un léger défaut d'alignement en ligne 12, aussi bien à l'horizontale qu'à la verticale. Ce sera l'occasion, à l'issue de cet exercice, de manipuler quelques outils de mise en forme spécifiques.

Calcul des chiffres d'affaires
Le dernier des trois tableaux est vide de résultats. Il doit afficher les chiffres d'affaires réalisés chaque mois, pour chaque destination. Le calcul est simple. Il consiste en une multiplication, celle du nombre de billets vendus par le prix du billet. A l'instar de la fonction Somme, Excel propose la fonction Produit. Mais son utilisation est justifiée lorsque le nombre de cellules impliquées dans le calcul est important. Dans le cas contraire, comme ici, elle n'apporte pas grand intérêt.

Bien sûr, une fois le premier chiffre d'affaires calculé, pour Paris en Juin, la formule doit être répliquée sur l'ensemble des autres cellules. Il est hors de question de réécrire la syntaxe à chaque fois. Et à ce titre, nous proposons d'exploiter deux méthodes. Nous les avons présentées lors du premier exercice. La première est conventionnelle. Elle consiste à exploiter la poignée de la cellule du résultat. La seconde est très efficace. Elle consiste à présélectionner toutes les cellules concernées avant de débuter la saisie du calcul.

• Sélectionner la première cellule du calcul, soit C25 pour Paris en Juin,
• Taper le symbole égal (=) pour débuter la formule,
• Cliquer sur la cellule du nombre de billets pour Paris en Juin, soit C6,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Cliquer sur la cellule du prix du billet pour Paris en Juin, soit C16,
• Valider la formule par le raccourci CTRL + Entrée pour conserver la cellule active,
• Cliquer sur le petit carré en bas à droite de la cellule pour sélectionner sa poignée,
• Puis, tout en maintenant le bouton de la souris enfoncé, glisser sur la droite jusqu'à la colonne E du mois d'Août et relâcher le bouton de la souris,

Nous répliquons ainsi le calcul du chiffre d'Affaires pour Paris en Juin, sur les mois de Juillet et Août. Comme nous l'avions expliqué, les références des cellules impliquées dans le calcul ont suivi le déplacement de la formule, imposé par la poignée.

Il n'est pas possible de tirer la poignée d'une cellule en diagonale. C'est la raison pour laquelle, nous n'avons pas pu reproduire la logique de la formule sur l'ensemble du tableau. Mais comme ils ont tous les trois la même organisation, la logique peut bien sûr être reproduite sur les lignes du dessous. La plage de cellules C25:E25 du précédent calcul répercuté doit toujours être sélectionnée. Sinon, vous devez préalablement resélectionner cette plage.

• Cliquer sur la poignée de la sélection, située en bas à droite de la cellule E25,
• Puis, tout en maintenant le bouton de la souris enfoncé, glisser vers le bas jusqu'à la ligne 30,
• Enfin, relâcher le bouton de la souris,

Ainsi, nous répliquons les calculs dynamiques de tous les chiffres d'affaires, jusqu'à obtenir le résultat de la dernière destination pour le dernier mois, soit Athènes pour le mois d'Août.

• Sélectionner cette dernière cellule, soit E30,

Comme vous le constatez en consultant sa barre de formule, la logique a parfaitement été adaptée par le jeu des références relatives. Le calcul original que nous avons bâti en C25 est le suivant : =C6*C16. En le répliquant jusqu'à la cellule E30, nous nous sommes déplacés de 2 colonnes vers la droite et de 5 lignes vers le bas. C devient E, 6 devient 11 et 16 devient 21. De fait, les cellules impliquées ont respecté ce déplacement pour produire le chiffre d'Affaires avec les valeurs concernées pour Athènes en Août : =E11*E21.

Il est temps de réaliser le même calcul par la seconde méthode, très efficace. Mais avant cela, nous devons supprimer les précédents résultats.

• Sélectionner la première cellule des résultats, soit C25,
• Tout en maintenant la touche MAJ enfoncée, cliquer sur la dernière, soit E30,

Cette méthode efficace permet d'englober dans la sélection, toutes les cellules comprises entre le premier et le dernier clic.

• Enfoncer alors la touche Suppr du clavier et conserver la sélection active,

Tous les calculs sont ainsi effacés. Et comme l'illustre la capture ci-dessus, la première cellule d'une plage est active par défaut. C'est ainsi que nous allons naturellement bâtir la formule comme si elle lui était dédiée, afin de la reproduire automatiquement sur l'ensemble des autres.

• Taper le symbole égal (=) pour débuter le calcul,
• Sélectionner le nombre de billets pour Paris en Juin, soit C6,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Cliquer alors sur le prix du billet pour Paris en Juin, soit C16,
• Puis, et c'est fondamental, valider la formule par le raccourci CTRL + Entrée,

Avec strictement la même formule que précédemment, tous les résultats des chiffres d'affaires sont instantanément répliqués dans les cellules présélectionnées. Nous n'avons pas tiré la poignée. De fait, la méthode est plus rapide. Et les références des cellules ont bien suivi le déplacement du calcul.

Rappelez-vous de même qu'ils sont tous bâtis sur des variables. Donc, ces calculs sont parfaitement dynamiques. Ils pourront être exploités pour d'autres périodes ou d'autres agences.

• Sélectionner la cellule du nombre de billets pour Athènes en Août, soit E11,
• Saisir 40 à la place de 38 et valider par la touche Entrée,
• Sélectionner le prix du billet pour Athènes en Août, soit E21,
• Saisir 1210 à la place de 1050 et valider par la touche Entrée,

Comme vous le constatez, le chiffre d'affaires pour Athènes en Août s'est automatiquement actualisé. Il est passé de 39900 à 48400.

• Double cliquer sur ce résultat, soit sur la cellule E30,

Cette méthode est intéressante pour activer la saisie, et donc la modification de la cellule. Nous pouvons y visualiser, en lieu et place, la syntaxe de la formule répliquée.



Cette méthode est de même très intéressante pour obtenir une traçabilité du calcul. Excel repère les cellules impliquées par des jeux de couleurs. Et à ce titre, nous confirmons la cohérence de la bonne réplication de la logique, en ligne et en colonne.

Une fois que la saisie d'une cellule portant une formule est activée, vous ne devez pas cliquer dans une autre cellule pour la désactiver. Vous endommageriez le calcul en désignant une nouvelle référence.

• Enfoncer la touche Echap en haut à gauche du clavier ou la touche Entrée,

La touche Echap permet d'abandonner une action en cours. La touche Entrée permet de valider une action. Comme aucune modification n'a été entreprise, dans ce contexte, les deux touches produisent le même résultat. Et nous retrouvons le résultat numérique de la cellule.

Il convient maintenant de calculer les sommes des chiffres d'affaires, en ligne et en colonne. Comme pour le premier tableau, le contexte est favorable. Les cellules des résultats sont accolées aux données à additionner. Nous allons donc pouvoir reproduire la méthode la plus efficace pour la somme automatique.

• Sélectionner la première cellule des chiffres d'affaires à additionner, soit C25,
• Avec la touche MAJ enfoncée, cliquer sur la dernière cellule du total à trouver, soit F31,
• Puis, relâcher la touche Maj,
• Réaliser alors le raccourci clavier Alt + =,

Toutes les sommes sont automatiquement produites. Souvenez-vous, il s'agit du raccourci pour la somme automatique. S'il ne vous convient pas, vous pouvez choisir de cliquer sur le bouton Somme automatique à droite dans le ruban Accueil.

Une sélection suivie d'un clic ou d'un raccourci a suffi pour produire tous les résultats, automatiquement adaptés aux indices de lignes et de colonnes. Cette méthode est donc définitivement précieuse.

Formater les cellules
Les calculs sont désormais terminés. Mais il est important de livrer un rendu clair et explicite. Quelques défauts d'alignement doivent être corrigés. De même, hormis pour le premier tableau, les valeurs numériques doivent être affichées en Euros.

• Sélectionner la dernière ligne du premier tableau, soit la plage C12:F12,

Ces quatre cellules présentent les deux mêmes défauts. L'alignement vertical n'est pas centré. En d'autres termes, les données numériques ne sont pas placées au milieu dans la hauteur de la cellule. Leur alignement à droite présente un décalage par rapport à toutes les autres valeurs numériques du tableau. Il s'agit d'un retrait que nous allons devoir supprimer.

• Dans la section Alignement du ruban Accueil, cliquer une fois sur le bouton Diminuer le retrait,

Cet outil est situé juste à côte du bouton Aligner à droite. Et comme vous le remarquez, les valeurs sélectionnées se sont déplacées dans leurs cellules. Elles sont désormais calées sur la même verticale que les autres données du tableau.

• Toujours dans la section Alignement du ruban Accueil, cliquer cette fois sur le bouton Aligner au centre,

Les données sélectionnées remontent ainsi dans la hauteur de la cellule. Nous aurions pu optimiser ces opérations de correction en incluant la dernière ligne du troisième tableau dans la sélection. Souvenez-vous, c'est la touche CTRL qui permet de réaliser des multi-sélections. Mais qu'à cela ne tienne, nous avons une autre méthode intéressante à proposer pour optimiser les tâches.



Bref, comme l'illustre la capture ci-dessus, les sommes sont désormais présentées de façon homogène par rapport au reste du tableau.

• Sélectionner la ligne de total du tableau des chiffres d'affaires, soit la plage C31:F31,
• Enfoncer la touche F4 du clavier,

Nous l'avions présentée au cours des exercices Word. Cette touche permet de répéter la dernière action automatiquement. Aucune autre action ne doit donc être entreprise entre temps. Et comme vous le remarquez, les données numériques des chiffres d'affaires sont elles aussi désormais convenablement alignées dans la largeur et la hauteur de la cellule.

En cas de souci, vous pouvez toujours reproduire la diminution du retrait et l'alignement vertical, à l'aide des boutons de la section Alignement du ruban Accueil.

Les valeurs du deuxième et du troisième tableau doivent désormais être affichées en Euros, pour plus de clarté. Rappelez-vous et c'est important, une unité de mesure ou une devise ne doit jamais être écrite dans une cellule, au risque de transformer la donnée en texte. Il doit toujours s'agir d'un format à appliquer. Ce format a une incidence sur l'affichage et non le contenu. Nous proposons d'optimiser les opérations en regroupant les sélections.

• Sélectionner les données numériques du deuxième tableau, soit la plage C16:E21,
• Tout en maintenant la touche CTRL enfoncée, sélectionner les données numériques du troisième tableau, soit la plage de cellules C25:F31,

Nous additionnons ainsi la sélection de la seconde plage de cellules à la première. Elles sont en effet toutes deux concernées par ce formatage en Euro. A l'issue de la multi-sélection la touche CTRL doit être relâchée.

• Réaliser alors le raccourci clavier CTRL + M,

Instantanément, toutes les cellules sélectionnées sont formatées en Euros. La combinaison de touches CTRL + M est le raccourci du bouton Format Nombre Comptabilité de la section Nombre dans le ruban Accueil. Il est encerclé de rouge sur la capture ci-dessous. Vous pouvez donc l'utiliser à la place du raccourci si vous le souhaitez.



Ce format monétaire ajoute le symbole de l'Euro mais ce n'est pas tout. Vous notez la présence de décimales. Elles ne sont pas nécessaires dans notre cas. Elles alourdissent la présentation et n'apportent aucune valeur ajoutée. Un espace fait office de séparateur de milliers pour les nombres dépassant les Mille. Cette présentation facilite la lecture des grandes valeurs. La sélection des deux plages doit toujours être active.

• Dans la section Nombre du ruban Accueil, cliquer deux fois de suite sur le bouton Réduire les décimales,

Les décimales disparaissent et la présentation s'en trouve clarifiée. Il est important de comprendre que nous avons réduit la précision sur les décimales, seulement à l'affichage. Pour une valeur comme 5,05 réduite à 5 par ce procédé, Excel continuera toujours de considérer la valeur d'origine pour ses calculs, soit 5,05. La valeur résultante 5 est un affichage simplifié sur demande de l'utilisateur.

• Dans le deuxième tableau, sélectionner le prix du billet pour Paris en Juillet, soit la cellule D16,

Il s'agit d'une confirmation importante. Souvenez-vous, la barre de formule restitue le contenu exact d'une cellule sélectionnée. Et comme l'illustre la capture ci-dessus, Excel continue de considérer la valeur d'origine, soit 1215. Le formatage Euro est destiné à l'affichage, pour les bienfaits de la présentation. Si cette cellule avait proposé des décimales réduites à l'affichage, elles auraient été restituées dans cette barre de formule.

Voilà donc pour ce deuxième exercice. Il a permis de consolider les bonnes méthodes de calcul à assimiler. Nous poursuivrons l'apprentissage des techniques et fonctionnalités Excel dans les exercices suivants.