Prérequis

Vous devez avoir suivis les COURS 1 et 2 ou en maîtriser le contenu.

L'auteur

Aurélie ARROT

Certifiée Microsoft specialist Excel
Habilitée B2i Adultes

EXCEL

Cours suivants :

COURS 1 : Mise en forme et calcul simple

COURS 2 : Les graphiques simples

COURS 3 : Mise en relation des données

COURS 4 : Fonction conditionnelle simple

COURS 5 : Automatiser une recherche

COURS 6 : Manipuler des listes de données

COURS 7 : Les tableaux croisés dynamiques (TCD)

COURS 3 : Mise en relation des données


Exercice sur les références relatives et absolues sur Excel pour savoir bloquer des cellules.

 

 

LES OBJECTIFS :
Différencier références absolues/références relatives, créer des formules avec des cellules nommées, créer des liaisons inter feuille et inter classeur, imprimer une sélection, utiliser plusieurs feuilles de calcul simultanément.

PUBLIC VISE : Tout utilisateur d’Excel

 

 

PROGRAMME

 

Vous allez réaliser le tableau ci-dessous en respectant bien les références de cellules pour taper le contenu :

Excel tableau calcul de TVA avec blocage de cellule

Calculez le total HT en C4 puis faite glisser la formule jusqu’en C8.
Calculez la TVA an vous aidant du taux en B11 puis faite glisser la formule jusqu’en F8.

Excel tableau calcul de TVA recopie de formule

Vous n’obtenez aucuns résultats ! Regardons de plus près. Si vous vous positionnez en F5, voici la formule que nous observons : =E5*B12
Or notre taux de TVA est en B11 ! Et certainement pas en B12. Comment peut-on faire glisser une formule sans qu’Excel incrémente d’une référence ?

Références Relatives :

Lorsqu'on crée une formule, les références de cellules ou de plages de cellules sont généralement basées sur leur position par rapport à la cellule qui contient la formule. Par exemple la cellule E4 contient la formule =C4*D4
Par défaut Excel recherche la valeur située une cellule au-dessus et une cellule à gauche de la cellule E4. Il s'agit là d'une référence relative.

Lorsqu'on copie une formule utilisant des références relatives, Excel ajuste automatiquement les références contenues dans la formule collée pour faire référence aux différentes cellules par rapport à la position de la formule.

C’est pour cela qu’il est capable de trouver la formule =C5*D5 lorsque l’on fait glisser (copie) la formule de la cellule E4 en E5.
Excel a ajusté automatiquement la formule des cellules C4 et D4 en les changeant en C5 et D5, qui fait référence à la cellule située une cellule au-dessous.

Références absolues :

Si on ne veut pas que Excel ajuste les références lorsqu'on copie une formule dans une autre cellule, il faut utiliser une référence absolue. On peut créer une référence absolue en plaçant le signe $ avant les éléments de la référence qui ne doivent pas être modifiés.

Dans notre exemple, pour créer une référence absolue à la cellule B11, on ajoute les signes $ à la formule comme indiqué ci-contre : =E4*$B$11
Un $ devant la lettre bloque la colonne
Un $ devant le chiffre bloque la ligne.
Bloquer une cellule, cela revient à bloquer la colonne + la ligne, d’où la présence de 2 $.

Modifier votre formule en F5 puis faite-la glisser. Calculez les totaux. Vous obtenez les résultats suivant

Poignées de recopie avec cellules bloquées dans Excel

Améliorer la lisibilité de la formule :

Revenez sur votre cellule F5. Notre formule ne serait-elle pas plus lisible si nous pouvions écrire : =E4*tauxTVA ?
C’est tout à fait possible ! Excel permet de nommer des cellules ou des plages de cellules. La particularité est que nommer une cellule permet aussi de la bloquer. Voilà une méthode qui rendra notre formule plus lisible.

Pour ce faire, rendez-vous sur la cellule B11, puis dans la zone de nom en haut à gauche tapez TauxTVA et faite entrer au clavier.


Nous pouvons maintenant remplacer $B$11 par TauxTVA dans notre formule.

En appuyant sur F3, vous retrouvez toutes les cellules / plages que vous avez nommées, il suffit de double cliquer sur le nom pour l’intégrer dans votre formule.
Nommer des cellules dans Excel

Pour gérer les nommages de cellules / plages :

Dans l’onglet Formule vous retrouvez un groupe Noms définis, cliquez sur Gestionnaire de noms.

Nommer des cellules dans Excel

Nous retrouvons notre nommage TauxTVA qui fait référence à la cellule $B$11 de notre feuil1.
Il est possible ici de supprimer le nommage ou de le modifier. Nous pouvons aussi créer de nouveaux nommage en cliquant sur Nouveau.

Dans cet exercice vous réaliserez 2 tableaux (ANNEXE 1 et ANNEXE 2)

Le premier tableau (ANNEXE 1) sans difficultés particulières, permet de saisir des quantités et de calculer des frais généraux par mois pour un produit X vendu.

Le second tableau (ANNEXE 2) est un récapitulatif trimestriel des :
  • Quantités vendues,
  • Frais généraux,
  • Chiffre d'affaires,
  • Coûts des ventes.


  • Les valeurs des quantités vendues par trimestre et des frais généraux par trimestre sont récupérées directement dans le premier tableau sans aucune autre saisie.

    Les seules valeurs saisies du second tableau sont :
  • Le prix de vente
  • Le coût en pourcentage


  • CONSTRUCTION DU TABLEAU « ENREGISTREMENT MENSUEL DES VENTES ET FRAIS GENERAUX », saisir les données suivantes :



    En ce qui concerne les mois (Janvier, Février ...) utiliser la poignée de recopie. La série des mois de l'année doit s'afficher automatiquement.
    Calculs : Montant de la publicité mensuelle = promotion annuelle du produit divisé par 12. Vous n’écrirez qu’une seule fois la formule.
    Montant de l'emballage : dépend de la quantité vendue. Attention les produits sont emballés par lot de 5. Faite glisser la formule pour les autres mois.

    Vous devez obtenir le tableau ci-dessous :



    Sauvegarder le classeur

    Nous souhaitons imprimer une partie du tableau :
  • Sélectionner le bloc à imprimer (cellules A1 jusqu'à M10).
  • Faire un Aperçu avant impression. (Fichier/imprimer). Faite en sorte que le tableau tienne en mode paysage.
  • Imprimer quand tout est correct, en prenant soin de sélectionner l'option Imprimer la Sélection.



  • CONSTRUCTION DU TABLEAU « RESULTAT DES VENTES », saisissez les données suivantes :



  • Mettre le prix de vente au format monétaire
  • Mettre le coût des ventes au format pourcentage

  • A) CALCUL DES QUANTITES PAR TRIMESTRE

    La quantité vendue pour le trimestre 1 est la somme des quantités vendues du tableau précédent des mois de Janvier, Février et Mars. De la même façon, les valeurs des frais généraux de chaque trimestre sont déterminées en fonction du tableau précédent :

  • Placer le curseur en B8
  • Cliquer sur le bouton Somme automatique
  • Cliquer sur l'onglet de la première feuille (Feuil1) où est situé le tableau des enregistrements mensuels
  • Sélectionner les cellules B5:D5 (celles qui contiennent les quantités pour les mois de Janvier, Février et Mars)
  • Validez par la touche Entrée.


  • Le curseur revient automatiquement dans le second tableau et la formule affichée est la suivante : =SOMME (Feuil1!B5:D5)
  • Procéder de la même façon pour récupérer les quantités vendues pour chacun des trimestres.
  • Centrer les quantités dans les cellules
  • Donner le nom QTE aux quatre cellules contenant les quantités
  • Calculer la colonne TOTAL en faisant la somme des quantités grâce à la formule : =SOMME(QTE)


  • B) CALCUL DU CHIFFRE D'AFFAIRES : CA

    Il est obtenu en multipliant la quantité par le prix de vente.

    C) CALCUL DU COUT DES VENTES

    C'est le produit du chiffre d'affaires et du coût en pourcentage.
    Les formules seront saisies au clavier pour le premier trimestre et recopiées pour les autres trimestres.

    D) TRAITEMENT DES FRAIS GENERAUX

    Comme les quantités, ils sont repris dans le tableau 1
  • Placer la surbrillance en B12
  • Cliquer sur le bouton Somme automatique
  • Passer dans le premier tableau en cliquant sur son onglet
  • Placer le curseur en B8 et sélectionner les cellules jusqu’à D10
  • Valider


  • Procéder de la même façon pour les frais généraux des autres trimestres.

    E) CALCUL DES TOTAUX

  • En B14 calculer le résultat du trimestre 1 (chiffre d'affaires moins coût des ventes moins frais généraux) et recopier la formule pour les autres trimestres.
  • Compléter la colonne TOTAL (colonne F)


  • F) FORMATAGE DES CELLULES

  • Choisir le format monétaire (avec 2 décimales) pour toutes les cellules à partir de la ligne contenant le CA
  • Elargir les colonnes si nécessaires


  • Vous devez obtenir les résultats suivants :



    G) VERIFICATION DU FONCTIONNEMENT DES LIENS

  • Dans la feuille TAB-ENR, modifier certaines valeurs :
  • 1 - Promotion annuelle du produit : 22300
    2 - Emballage (par lot de 5) : 1,60
  • Vérifier les modifications des valeurs par mois. (Lignes 8 et 9)
  • Passer dans le second tableau TAB-RECAP et vérifier les résultats. (Lignes 12 et 14)


  •  

    EXERCICES ET TUTOS

    Bloquer des cellules

    EXERCICE : MSA Securité corrigé exercice

    Contact

    SARL AAT's

    N° SIREN : 822580056 - N° TVA intracommunautaire : FR40822580056