Créer des tableaux et réaliser des calculs Excel

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


Sujets que vous pourriez aussi aimer :


Découvrir Excel - Réaliser des tableaux et des calculs

Cette formation permet de mettre en pratique les notions de base abordées avec le support de formation Démarrer avec Excel. Au travers de quatre petits exercices, nous allons nous entraîner à réaliser la saisie et la mise en forme de tableaux. Puis nous effectuerons des calculs simples pour faire ressortir les données de synthèse.

• Télécharger le classeur 01-commencer-avec-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Il s'agit d'un classeur constitué de quatre feuilles, une par exercice. La première feuille, Relevé de banque, est totalement vide. Nous allons devoir saisir le tableau, le mettre en forme et réaliser les calculs. Les trois autres en revanche contiennent les données pré-saisies mais non mises en forme. Nous allons devoir formater les tableaux et poser les formules.

Suivi des opérations de compte bancaire
Nous allons réaliser un tableau qui permet de suivre chaque opération réalisée sur un compte en banque, les débits comme les crédits. A l'issue, nous serons capables d'indiquer le solde du compte à l'instant t.


Le tableau à reproduire est présenté par la capture ci-dessus.

• Activer la feuille Relevé de banque puis cliquer sur la cellule B3 pour la sélectionner,
• Taper le texte Relevé de banque,

Bien sûr ce texte déborde de la cellule, mais il est bien écrit en B3 et seulement en B3. Comme le montre la figure, il doit être étalé sur une largeur de 5 cellules et centré sur cette largeur. C'est la fusion des cellules dans Excel qui permet de réaliser cette mise en forme spécifique.

• En cliquant et glissant avec la souris, sélectionner les cellules B3 à F3,
• Dans le groupe Alignement du ruban Accueil, cliquer sur le bouton Fusionner et centrer,

Les 5 cellules sont fusionnées en une seule qui possède la largeur totale de l'ensemble de la plage. Cette cellule fusionnée conserve la référence B3 comme l'indique la zone Nom. Le texte quant à lui est centré sur la largeur de la cellule résultante. Une fusion doit toujours concerner une cellule de texte à fusionner avec des cellules vides. En effet, si elle était réalisée sur plusieurs cellules comportant chacune un texte différent, seul le premier serait conservé et centré sur la largeur finale. Ce texte doit maintenant être formaté. La cellule fusionnée B3 étant toujours sélectionnée :

• Dans le groupe Police du ruban Accueil, cliquer sur le bouton G pour mettre en gras,
• Ensuite, saisir 16 dans la zone Taille de police afin de grandir les caractères,
• Changer la couleur de texte en cliquant sur la flèche du bouton Couleur de police,
• Attribuer un vert plus foncé au fond de la cellule, avec la flèche du bouton Couleur de remplissage,
• Enfin, en déroulant l'outil Bordures, choisir une bordure extérieure épaisse,

Nous allons maintenant passer à la saisie des données du tableau.

• Sélectionner la cellule B5,
• Saisir le texte Solde au 30/04/20xx,
• Valider cette saisie en réalisant le raccourci clavier CTRL + Entrée,

Cette combinaison de touches est intéressante dans Excel dans la mesure où elle permet de valider une cellule en la conservant active. Il n'est ainsi pas nécessaire de devoir la re-sélectionner pour la mettre en forme dans la foulée. La touche Entrée seule aurait par défaut activé la cellule du dessous, soit B6. Le support de formation sur les raccourcis clavier dans Excel offre de précieuses astuces sur les combinaisons de touches.

• Mettre cette cellule en gras à l'aide du ruban Accueil,

Le raccourci CTRL + G permet d'obtenir le même résultat. Les info-bulles qui apparaissent lorsque vous pointez avec la souris sur les outils des rubans, rappellent en général les raccourcis clavier associés. Ce texte doit être fusionné et centré sur une largeur de quatre cellules.

• Sélectionner les cellules B5 à E5 avec la souris,
• Cliquer sur le bouton Fusionner et centrer du ruban,
• Enfoncer la touche Tabulation du clavier,

La touche Tabulation ou Tab est la touche matérialisée par deux flèches inversées, Ouest et Est. Elle est située généralement en haut à gauche du clavier. Elle permet de passer à la cellule suivante sur la droite tandis que la touche Entrée permet de passer à la cellule suivante en dessous. Ainsi la cellule F5 doit être active.

• Saisir le solde 3723,28 dans cette cellule,
• Valider cette saisie comme précédemment par CTRL + Entrée pour la garder active,
• La mettre en gras et lui attribuer une taille de police de 14,
• Changer sa couleur de texte avec un violet par exemple,

Il nous manque le symbole de la monnaie, l'Euro ici en l'occurrence (€). Les unités de mesure (Kg, ml, Kmh) et les devises (€, $...) ne doivent jamais être saisies dans une cellule. Il s'agit d'un format à appliquer. Si vous le saisissez directement dans la cellule, vous transformez cette dernière en texte et donc, plus aucune opération n'est pas possible. Or ce solde est la base de tous les calculs à venir pour la dernière colonne. La cellule étant toujours sélectionnée :

• Cliquer sur le bouton Format Nombre Comptabilité du ruban Accueil,

Cette fonction permet d'appliquer le format monétaire en vigueur pour le pays dans lequel la version d'Excel a été installée. S'il s'était agi d'appliquer un autre format, il aurait fallu dérouler ce bouton en cliquant sur sa flèche associée.


La devise Euro accompagne bien le nombre dans l'affichage de la cellule. Mais si vous regardez sa barre de formule juste au-dessus, vous remarquez que le symbole n'apparaît pas. La barre de formule restitue le contenu exact d'une cellule. Dans le cas d'un calcul, vous y verrez la formule et non le résultat du calcul. Cela signifie que le format est correctement appliqué et que la cellule est toujours bien numérique. Ces deux cellules possèdent une bordure avec un trait épais. Il s'agit de la même bordure que celle que nous avons appliquée pour le titre. Mais une bordure s'applique à une sélection et il s'agit d'une bordure extérieure. Si bien que si vous sélectionnez les deux cellules ensemble et que vous cliquez sur l'outil Bordures, vous obtiendrez une bordure d'encadrement de la sélection. La séparation verticale entre la cellule B5 fusionnée et la cellule F5 n'apparaîtra pas. Il est bien sûr possible de sélectionner les cellules tour à tour pour appliquer la bordure. Ou encore vous pouvez sélectionner les cellules ensemble en indiquant explicitement à Excel qu'il s'agit de deux sélections indépendantes. Pour ce faire :

• Cliquer sur la cellule B5 pour la sélectionner,
• Tout en maintenant la touche CTRL enfoncée, cliquer sur la cellule F5,

Les deux cellules sont ainsi sélectionnées ensemble dans deux sélections indépendantes. Cette notion et beaucoup d'autres sont enseignées dans le support de formation Trucs et astuces Excel.

• Cliquer ensuite sur le bouton Bordures du ruban Accueil,

Chaque bouton du ruban propose par défaut la dernière option utilisée, en l'occurrence pour nous, la bordure extérieure épaisse. Lorsque vous désélectionnez en cliquant sur une autre cellule, vous constatez que les deux cellules sont bien encadrées avec un trait de séparation vertical.

• Sélectionner la cellule B6 et taper le texte Date,
• Enfoncer la touche Tabulation pour activer la cellule suivante sur la droite, soit C6,
• Taper le texte Opération et enfoncer la touche Tabulation,
• Taper le texte Débit et enfoncer la touche Tabulation,
• Taper le texte Crédit et enfoncer la touche Tabulation,
• Taper le texte Solde et valider par Entrée,

Nous venons de saisir tous les titres de colonne. Il s'agit maintenant de les formater.

• Sélectionner l'ensemble de ces titres, B6:F6,
• Cliquer sur le bouton G du ruban pour les passer en gras,

Les trois colonnes Débit, Crédit et Solde sont des colonnes de valeurs numériques. Par défaut, les nombres s'alignent à droite dans leur cellule. Donc pour que les titres soient alignés de la même façon que les valeurs, nous devons changer leur alignement horizontal.

• Sélectionner ces trois cellules, D6:E6,
• Dans le groupe Alignement du ruban Accueil, cliquer sur le bouton Aligner à droite,

Chacune de ces cinq cellules possède une bordure extérieure épaisse avec un trait de séparation vertical. Nous allons donc optimiser les actions en utilisant la même astuce que précédemment.

• Sélectionner la cellule B6,
• Puis en maintenant la touche CTRL enfoncée, cliquer tour à tour sur les cellules C6, D6, E6 et F6,
• Puis, cliquer sur l'outil Bordures du ruban,

Nous devons maintenant nous occuper des saisies du tableau. Nous allons procéder par colonne.

• Sélectionner la cellule B7, taper la valeur 1 et valider par Entrée cette fois,

1 est une valeur numérique. Elle s'aligne donc à droite par défaut dans sa cellule. La touche Entrée permet de sélectionner automatiquement la cellule du dessous. Elle est donc prête à la saisie.

• Taper la valeur 2 puis enfoncer la touche Entrée et poursuivre ainsi jusqu'à la dernière valeur 28 en B16,
• Sélectionner maintenant la cellule C7, taper le texte Chèque 1732564 et enfoncer Entrée,

Il s'agit d'un texte, il s'aligne donc à gauche dans sa cellule.

• Poursuivre la saisie de cette colonne jusqu'au texte Chèque 1732564 en C16,
• Sélectionner la cellule D7 et taper la valeur numérique 56,80 puis enfoncer Entrée,

Souvenez-vous que vous ne devez pas taper le symbole de la devise. Cette cellule est un nombre à partir duquel nous allons réaliser des calculs.

• Dans la cellule suivante, saisir la valeur 400 et valider par Entrée,

Vous pourriez très bien saisir les décimales mais ce n'est pas nécessaire. Elles apparaîtront naturellement lorsque nous appliquerons le format monétaire à l'ensemble de ces valeurs numériques.

• Poursuivre la saisie sur toute la colonne jusqu'à la valeur 890 en D16, en conservant les cellules qui doivent rester vides comme l'indique le modèle,
• Réaliser la saisie des deux valeurs de la colonne Crédit en E11 et E14,

Nous allons maintenant mettre en place les bordures en appliquant la même astuce que celle que nous avons apprise précédemment. Il y a bien un trait vertical de séparation entre chaque colonne mais il n'y a pas de trait de séparation horizontal entre chaque cellule. Souvenez-vous, une bordure s'applique à une sélection.

• En cliquant et glissant la souris, sélectionner la première colonne, B7:B16,
• En maintenant la touche CTRL enfoncée, sélectionner la deuxième colonne, C7:C16,
• Toujours en maintenant CTRL enfoncée, sélectionner tour à tour les 3 autres colonnes,
• Puis cliquer sur le bouton Bordures qui a mémorisé le dernier choix,

Nous obtenons bien les bordures souhaitées. Il reste à appliquer le format Euro aux valeurs numériques.

• Sélectionner les trois colonnes de valeurs numériques, D7:F16,
• Cliquer sur le bouton du format monétaire dans le ruban comme nous l'avons fait plus haut,

Le raccourci clavier CTRL + M permet d'appliquer le format monétaire par défaut (€) et permet donc d'éviter de cliquer sur le bouton du ruban, si vous le souhaitez.


Même si la colonne Solde est vide, nous l'avons intégrée dans le formatage. Ainsi, lorsque les résultats des calculs apparaîtront, ils seront directement au format Euro et il ne sera pas nécessaire de refaire la manipulation. Comme nous l'avions dit, vous voyez apparaître les deux décimales même lorsque la saisie ne les prenait pas en compte. Toutes ces valeurs sont bien alignées à droite dans leur cellule. La barre de formule des cellules respectives n'affiche pas le symbole Euro, nous avons donc bien des cellules numériques prêtes pour les calculs. Pour finaliser la saisie du tableau :

• Saisir le texte Solde au 29/05/20xx en B18 et valider par CTRL + Entrée,
• Formater ce texte en gras,
• Le fusionner et centrer sur la plage de cellules B18:E18,
• Sélectionner la cellule fusionnée et la cellule F18 à l'aide de la touche CTRL,
• Appliquer la bordure extérieure habituelle,

Il s'agit maintenant de réaliser les calculs en commençant par la première cellule de la colonne Solde. Le calcul consiste à soustraire du solde de départ la valeur du débit puis à ajouter la valeur du crédit.

• Sélectionner la cellule F7 et taper le symbole = pour débuter le calcul,
• Cliquer la cellule du solde de départ (F5) avec la souris,
• Taper le symbole - pour réaliser la soustraction et cliquer la première cellule du débit D7,

Les références (F5 et D7) s'inscrivent dans la cellule. En effet avec Excel, nous ne réalisons pas des calculs sur des constantes (3723,28-56,80). Nous réalisons des calculs sur des variables (F5-D7). Comme les résultats de calculs sont dynamiques si les valeurs de F5 et/ou D7 changent, le résultat en F7 se met à jour instantanément et automatiquement.

• Taper maintenant le symbole + pour l'addition et cliquer la première cellule du crédit E7,
• Valider le calcul par le raccourci clavier CTRL + Entrée afin de conserver la cellule active,

Le résultat affiché est 3666,48 mais si vous regardez la barre de formule, son vrai contenu est : =F5-D7+E7. Un calcul Excel, une fois qu'il est posé, ne se refait pas, il se reproduit. C'est ce que nous allons tenter de faire pour toutes les cellules de la colonne Solde.

• Placer le curseur de la souris en bas à droite de la cellule, sur le petit carré,

Il s'agit de la poignée de la cellule. Lorsque vous pointez dessus, le curseur de la souris se transforme en une petite croix noire. On utilise généralement la poignée d'une cellule pour répliquer la logique de calcul sur les autres cellules.

• Cliquer et glisser cette poignée jusqu'en F16 puis relâcher le bouton de la souris,

Vous venez de tirer la poignée. Mais comme l'illustre la capture ci-dessous, le résultat obtenu n'est pas celui attendu. D'une part, les résultats quand ils sont obtenus ne sont pas les bons et d'autre part des erreurs s'affichent avec le message #VALEUR!. Ceci est en réalité tout à fait normal. Lorsque vous reproduisez la logique d'un calcul, avec la poignée par exemple, les références du calcul suivent le sens du déplacement. Concrètement, le calcul de départ fait référence aux cellules placées sur les lignes 5 et 7 (=F5-D7+E7). Lorsque vous tirez la poignée vers le bas, vous descendez d'une ligne. Excel fait de même pour les références du calcul (=F6-D8+E8) et ainsi de suite jusqu'à la dernière ligne pour laquelle nous obtenons : =F14-D16+E16. Or F6 est la cellule de titre de la colonne et non le nouveau solde à prendre comme référence pour la suite du calcul.

• Supprimer le contenu des cellules F8 à F16 ou réaliser le raccourci CTRL + Z pour annuler cette dernière action,
• Sélectionner la cellule F8 et reposer le calcul suivant : =F7-D8+E8 qui fait référence au nouveau solde et aux cellules du débit et de crédit de la même ligne,
• Valider ce calcul par CTRL + Entrée et tirer la poignée de la cellule jusqu'en F16,

Cette fois, comme il n'y a plus de ligne de texte intercalée, la logique est reproduite correctement. Néanmoins vous remarquez que les formats des cellules, notamment les bordures, sont affectés. La poignée recopie les calculs certes, mais aussi la mise en forme de la cellule qui ne possède pas de bordure inférieure. Donc la bordure inférieure de F16 disparaît. Pour corriger le problème à la volée :

• Cliquer sur le petit carré qui apparaît en bas à droite du calcul,
• Dans la liste, choisir Recopier les valeurs sans la mise en forme,

Les calculs restent et la bordure inférieure du tableau réapparaît. Une autre méthode pour éviter ce problème consiste à agir en amont. Il faut présélectionner l'ensemble des cellules, saisir le calcul et le valider par CTRL + Entrée. Ainsi en même temps qu'il est validé pour la première cellule de la plage, il est répercuté pour l'ensemble des cellules sélectionnées sans reproduire la mise en forme. Il ne reste plus que la cellule du solde à l'issue.

• Sélectionner la cellule F18 et taper la formule =F16,
• Valider le calcul par CTRL + Entrée,
• Passer la cellule du résultat en gras, taille 14 et couleur violette,

Le résultat à obtenir est présenté par la figure ci-dessous.


Calcul des bénéfices - Chiffre d'affaire et charges
L'exercice suivant au travers de calculs très simples et d'un tableau sans mise en forme va nous permettre de continuer à mettre en pratique ce que nous avons appris.

• Cliquer sur l'onglet de la feuille Calculs bénéfices pour l'activer,

Il s'agit des chiffres d'affaire et des charges d'une société pour le premier semestre. Les calculs à réaliser sont très simples puisqu'il s'agit de sommes et de soustractions. La mise en forme quant à elle, reprend des notions que nous avons mises en pratique précédemment. Le résultat à obtenir après formatage est présenté ci-dessous.


Le titre du tableau doit être formaté en conséquence et fusionné sur une largeur de 8 cellules.

• Sélectionner la plage de cellules B3:I3,
• Cliquer sur le bouton Fusionner et centrer du ruban,
• Passer la cellule en gras, en taille 16 avec une bordure extérieure épaisse,
• Lui appliquer un fond vert un peu plus foncé et une couleur de police violette,
• Cliquer enfin sur le bouton Aligner au centre du ruban,

Aligner au centre permet de centrer un texte verticalement dans la cellule. La hauteur de cette ligne avait en effet été agrandie. Et par défaut dans Excel, un texte est aligné en bas et à gauche de la cellule.

Les bordures sont très simples à réaliser cette fois-ci. Il s'agit de bordures extérieures fines avec un quadrillage interne. Et le bouton bordures permet d'appliquer directement ce type de quadrillage à une plage sans devoir réaliser de sélections spécifiques.

• Sélectionner toutes les cellules du tableau B5:I10,
• Cliquer sur la flèche du bouton Bordures dans le ruban,
• Dans la liste, choisir Toutes les bordures,

Cette option permet de réaliser, en traits fins, toutes les bordures extérieures et intérieures sur une sélection. C'est ce qu'illustre la capture ci-dessous.


Les textes de la ligne de titre et de la colonne de titre doivent apparaître en gras. Donc nous pouvons grouper cette opération.

• Sélectionner les cellules de la ligne de titre C5:I5,
• Tout en maintenant le touche CTRL enfoncée, sélectionner les cellules B6:B10,
• Cliquer sur le bouton G du ruban ou réaliser le raccourci CTRL + G,
• Sélectionner de nouveau les cellules de la ligne de titre C5:I5,
• Appliquer un fond vert un peu plus foncé et une couleur de police violette,
• Sélectionner la cellule B9 et lui appliquer une couleur de police Orange assez foncée,
• Sélectionner la cellule B10 et lui attribuer une couleur de police violette,

Il s'agit maintenant de réaliser les calculs. Comme vous allez le remarquer, Excel facilite grandement les opérations. Nous allons commencer par calculer toutes les sommes en ligne.

• Sélectionner les cellules de C6 à I8,
• Puis, cliquer sur le bouton Somme automatique du ruban ou réaliser le raccourci Alt + =,

Dans la sélection, avant de cliquer sur le bouton pour réaliser la somme, nous avons montré à Excel ce que nous souhaitions additionner et à quel endroit placer les résultats de ces sommes en lignes, en débordant sur la colonne Total. Aucune ambiguïté n'existait et en un seul clic, Excel a livré les résultats des sommes par ligne. Nous aurions aussi pu sélectionner la cellule I6, cliquer sur le bouton Somme automatique, valider par CTRL + Entrée et tirer la poignée sur deux cellules vers le bas. Mais c'est plus long dans ce contexte précis. De la même façon, pour calculer la somme des charges :

• Sélectionner les cellules de C7 à I9 et cliquer sur le bouton Somme automatique,

Encore une fois, en amont grâce à la sélection, nous montrons à Excel ce que nous souhaitons sommer et où placer les résultats en colonne cette fois. Vous remarquez des coches apparaître dans l'angle supérieur gauche des résultats de calculs. Lorsque vous pointez dessus et que vous cliquez sur la balise active, Excel vous suggère une erreur potentielle, puisque des cellules adjacentes n'ont pas été intégrées dans la somme. En effet, il s'agit de la somme des charges. Le chiffre d'affaire ne doit pas être inclus. Pour masquer ces annotations, il suffit après avoir présélectionné la plage concernée, de cliquer sur Ignorer l'erreur.


Le dernier calcul, celui des bénéfices, est on ne peut plus simple à réaliser. Il s'agit de soustraire la somme des charges précédemment calculée au chiffre d'affaire.

• Sélectionner la cellule C10 et taper le symbole = pour débuter le calcul,
• Cliquer la première cellule du chiffre d'affaire, C6,
• Taper le symbole - pour la soustraction et cliquer le premier total des charges, C9,
• Valider le calcul par CTRL + Entrée pour conserver la cellule active,
• Dans la foulée, tirer la poignée de la cellule sur la droite jusqu'à la cellule I10,

Vous répliquez ainsi la logique de calcul pour l'ensemble des cellules de la ligne. Sur chaque colonne, c'est bien le chiffre d'affaire de la colonne auquel on soustrait le total des charges. Pour en avoir le coeur net, vous pouvez sélectionner n'importe quelle cellule de la ligne 10 des bénéfices et consulter sa barre de formule. Il ne reste plus qu'à réaliser quelques mises en forme.

• Sélectionner toutes les valeurs numériques, C6:I10,
• Réaliser le raccourci clavier CTRL + M,

Souvenez-vous, il s'agit de l'astuce pour appliquer le format monétaire aux cellules numériques, sans passer par le bouton du ruban.

• Passer toutes les cellules des résultats de calcul en gras,
• Attribuer des couleurs de police correspondant aux couleurs des titres.

Vous devez obtenir un résultat similaire à celui proposé par la capture ci-dessous.


Chiffres d'affaire des ventes en ligne
L'exercice suivant concerne les ventes en ligne réalisées par une parapharmacie, Paranet. L'objectif final, en fonction des articles vendus et des quantités achetées, est de calculer le bénéfice total réalisé.

• Cliquer sur l'onglet CA Paranet pour afficher sa feuille,

Les données sont déjà saisies sans les calculs. Mais en l'absence de mise en forme, le tableau manque de clarté. Le résultat à obtenir après formatage est celui présenté par la capture ci-dessous.


Concernant le titre du tableau, il s'agit d'une réplique de ce que nous avons déjà fait pour les deux exercices précédents.

• Fusionner et centrer le titre Ventes en ligne - Paranet sur la largeur du tableau,
• Le mettre en gras sur fond vert plus foncé avec une couleur de police violette,
• Lui attribuer une taille de police de 16 et une bordure extérieure épaisse,

Concernant le tableau, vous remarquez que les textes de la ligne de titre sont trop long par rapport à la largeur de colonne. Nous pourrions élargir les colonnes mais elles seraient anormalement larges par rapport au reste du contenu. Donc nous préférons imposer un retour à la ligne lorsque le texte déborde. Pour cela :

• Sélectionner les cellules de la ligne de titre, C5:G5,
• Les aligner à droite dans la largeur et les centrer verticalement,
• Les mettre en gras puis cliquer sur le bouton Renvoyer à la ligne automatiquement,

Il est aussi possible d'imposer le retour à la ligne d'un texte durant la saisie grâce au raccourci clavier ALT + Entrée, dans Excel.

• Sélectionner ensuite la plage de cellules B6:B10 et la passer en gras,
• Sélectionner toutes les cellules numériques, y compris les cellules vides, C6:G10,
• Leur appliquer le format monétaire par CTRL + M par exemple,

Concernant les bordures, il s'agit d'un quadrillage fin. La cellule de l'angle supérieur gauche du tableau ne doit pas en avoir. Soit vous réalisez ces bordures en deux fois, soit vous réalisez une multi-sélection comme nous l'avons vu précédemment :

• Sélectionner les cellules de la première colonne, B6:B10,
• Tout en maintenant la touche CTRL enfoncée, sélectionner toutes les autres cellules du tableau soit C5:G10,
• Appliquer le quadrillage à l'aide de l'outil Bordures du ruban,
• Sélectionner la cellule F12 du texte Bénéfice total,
• La mettre en gras et sur deux lignes,
• Enfoncer la touche Tabulation par exemple pour sélectionner la cellule G12 sur sa droite,
• La passer en gras,
• Lui attribuer une taille de police de 14 et une couleur de texte violette,
• Lui attribuer le format monétaire Euro,

Ces réglages de format se verront lorsque la cellule sera remplie par le résultat du calcul.

• Lui appliquer un fond vert un peu plus foncé,
• Puis appliquer des bordures fines aux cellules F12 et G12,

Grâce aux astuces que nous avons apprises et à l'expérience que nous avons acquise, vous remarquez que les opérations de mise en forme des tableaux deviennent de plus en plus simples et rapides. Le résultat à obtenir à ce stade est illustré par la capture ci-dessous.


Il nous reste à réaliser les calculs, très simples là encore. Et nous allons en profiter pour découvrir une astuce qui permet de gagner du temps et du confort dans un deuxième temps. Tout d'abord la marge unitaire consiste à soustraire le prix d'achat au prix de vente :

• Sélectionner la première cellule du calcul, soit E6,
• Taper le symbole = pour débuter le calcul comme toujours,
• Sélectionner le prix de vente à la souris, soit la cellule D6,
• Taper le symbole - pour la soustraction,
• Puis cliquer la cellule du prix d'achat, soit C6,
• Valider le calcul par CTRL + Entrée,
• Tirer la poignée jusqu'à E10 pour reproduire la logique du calcul sur les cellules du dessous,
• Les cellules étant toujours sélectionnées, les passer en gras,

Le calcul des bénéfices consiste bien sûr à multiplier cette marge unitaire par la quantité d'articles vendus. Mais plutôt que de poser le calcul dans la première cellule et de tirer ensuite la poignée pour le reproduire, nous allons utiliser une méthode encore plus efficace.

• Sélectionner toutes les cellules des bénéfices, soit G6:G10,
• Taper le symbole = pour débuter le calcul,

Comme vous le constatez, la saisie se réalise dans la première cellule de la plage sélectionnée. C'est toujours ainsi.

• Cliquer sur la cellule de la première marge unitaire, soit E6,
• Taper le symbole * du pavé numérique pour la multiplication,
• Cliquer la première cellule des quantités vendues, soit F6,
• Valider ce calcul par CTRL + Entrée.

Tous les résultats sont instantanément reproduits sur l'ensemble des cellules présélectionnées grâce au raccourci clavier CTRL + Entrée. Avec cette astuce il n'est plus nécessaire de tirer la poignée d'autant que cette méthode permet de répercuter un calcul sans reproduire le format. Le dernier calcul est trivial. Il s'agit de sommer l'ensemble des bénéfices ainsi calculés. Voyons une méthode parmi d'autres :

• Sélectionner la cellule G12,
• Réaliser la combinaison de touches Alt + =,

A cause de la ligne vide intercalée, Excel a un doute. Le résultat ne s'affiche pas instantanément. Excel vous demande de confirmer la plage de cellules sur laquelle la somme doit être calculée.

• Avant de valider le calcul, à la souris, sélectionner la plage G6:G10,

Vous constatez que les références s'adaptent en temps réel à l'intérieur des parenthèses de la fonction Somme.

• Valider ce calcul par CTRL + Entrée et centrer verticalement le résultat dans sa cellule.

Facturation avec remise
Le dernier exercice consiste à réaliser une petite facturation pour une entreprise.

• Cliquer sur l'onglet Facture pour activer sa feuille,

Là encore les données sont présentes.

• En appliquant tous les principes précédents, réaliser la mise en forme de la feuille afin d'obtenir le résultat présenté par la capture ci-dessous,

Pour le pourcentage, il suffit de sélectionner les cellules concernées et de cliquer sur le bouton Style de pourcentage sur le ruban. Les cellules de texte, du petit tableau de synthèse, doivent être fusionnées sur une largeur de deux cellules (Montanttotal brut HT, Taux remise etc...). Il ne reste plus qu'à réaliser les calculs. Le montant de la remise consiste à appliquer le pourcentage de la remise sur le montant total, soit la quantité multipliée par le prix unitaire.

• Sélectionner la cellule G6 et taper = pour débuter le calcul,
• Cliquer la cellule du prix unitaire, soit E6,
• Taper le symbole de la multiplication (*) et cliquer sur la quantité soitD6,
• Taper de nouveau le symbole * et cliquer la cellule de la remise soit F6,
• Valider le calcul par CTRL + Entrée puis tirer la poignée jusqu'en G9,

Le calcul du montant de la remise fait référence aux cellules de la ligne 6. Lorsque vous tirez la poignée vers le bas, vous déplacez le calcul sur les lignes du dessous. Excel adapte alors le calcul en faisant référence aux cellules des mêmes colonnes mais en changeant l'indice de ligne pour suivre le déplacement du calcul. C'est ainsi que nous obtenons en G9 la formule suivante : =E9*D9*F9. Souvenez-vous, en sélectionnant cette cellule, vous pouvez consulter son contenu exact depuis la barre de formule, soit sa formule. Le montant HT de la dernière colonne consiste à soustraire cette remise du montant calculé en fonction du prix unitaire hors taxes et de la quantité. Pour varier les plaisirs, nous allons réaliser le calcul de cette colonne sans faire intervenir la poignée.

• Sélectionner les cellules de la colonne Montant HT, soit H6:H9,
• Taper = pour débuter le calcul comme toujours,
• Cliquer la cellule de la quantité D6, taper le symbole * et cliquer la cellule du prix E6,

Cette multiplication permet de calculer le montant total HT sans remise. Nous devons donc maintenant lui soustraire la remise précédemment calculée.

• Taper le symbole de la soustraction (-) et cliquer la cellule de la remise G6,
• Valider le calcul par CTRL + Entrée,

Comme toutes les cellules de la plage étaient présélectionnées, la formule est répercutée sur l'ensemble des cellules instantanément. Les parenthèses ne sont pas nécessaires. Comme la multiplication est prioritaire sur la soustraction en mathématiques, c'est le calcul du montant HT qui est d'abord réalisé. C'est seulement ensuite que la remise lui est déduite. Passons au petit tableau de synthèse du dessous. Le montant total brut HT est la somme de tous les montant HT calculés avec la remise. Le taux de remise consiste à appliquer un nouveau pourcentage de remise sur cette somme HT.

• En H11, taper la formule suivante : =SOMME(H6:H9),
• En H12, taper la formule =H11*G12,

Le montant total net HT est donc la soustraction de ces deux valeurs. Puis il faudra calculer le pourcentage de la TVA sur ce nouveau total et enfin ajouter ces charges au total précédent afin d'obtenir le montant TTC à régler.

• En H13, taper la formule =H11-H12,
• En H14, calculer les charges dues à la TVA avec la formule =H13*G14,
• Puis en H15, ajouter ces charges au montant HT =H13+H14,

Nous obtenons ainsi une facturation client en bonne et due forme. Il serait intéressant de réaliser une légère mise en forme sur ce petit tableau afin de faire ressortir les valeurs importantes. C'est ce que propose la capture ci-dessous. Enfin et comme vous le savez, tous les résultats de calculs sont dynamiques dans Excel. Une fois qu'ils sont posés, si vous changez les données du problème, toutes les valeurs calculées se mettent à jour. Dans l'exemple ci-dessous, nous avons changé le taux de remise de 3 à 2%. Les calculs de la remise, du montant HT, des charges dues à la TVA et du montant total TTC se sont instantanément recalculés. Puis nous avons changé la remise pour le HD Western 1 Go de 5 à 10%. Son montant HT s'est alors instantanément recalculé induisant le re-calcul de la somme de tous les montants HT et des remises et charges pour le calcul final TTC.