IUT TC 2006-2007
S2 - TP EXCEL N° 4
Pensez à sauvegarder fréquemment dans votre espace de travail les documents que vous réalisez !
1. FONCTION [Link]
La fonction [Link] permet de compter le nombre de cellules situées à l'intérieur d'une plage qui répondent à un certain
critère. La syntaxe générale est la suivante :
[Link](plage_de_cellules;critère)
Par exemple, étant donné le tableau :
nous souhaitons compter le nombre de fois où apparaît la chaîne de caractères "Indre" figurant en A4. Nous écrivons
donc dans la cellule qui stockera le résultat [Link](A4:B19; "Indre")
E XERCICE
Avant de commencer, copiez le fichier [Link] dans votre espace de travail
Nous allons utiliser d’abord la fonctionnalité de nommage de cellules, c'est à dire donner un nom déterminé à une plage
de cellules. Pour ce faire :
1
IUT TC 2006-2007
• placez vous sur la feuille statVentesDpto
• sélectionnez la plage A4:B19
• utilisez la commande Insertion / Nom / Définir
• nommez la plage « tableau ».
Ecrivez maintenant la formule permettant de remplir la colonne « Nombre de ventes ». Attention aux références
relatives et absolues !!!
2. FONCTION [Link]
La fonction [Link] permet d'additionner des cellules d'une plage si elles répondent à un critère donné. La syntaxe
générale est la suivante :
=[Link](plage_de_cellules;critère;somme_plage)
Les cellules de somme_plage sont additionnées si les cellules correspondantes dans plage_de_cellules répondent au
critère. Si l'argument somme_plage est omis, ce sont les cellules de l'argument plage_de_cellules qui sont additionnées.
Par exemple, pour additionner les valeurs figurant dans la plage B4:B19, quand le contenu d'une cellule de la plage
« tableau » est "Indre" nous écrivons :
[Link](tableau;"Indre";B4:B18)
E XERCICE
• Ecrivez maintenant les formules permettant de remplir la colonne « Total CA / dpto » et la cellule « Chiffre d’affaires
total »
E XERCICE
• Placez vous sur la feuille « fraisDéplacement » du fichier [Link] et remplissez le tableau à gauche avec les
formules nécessaires pour calculer le nombre de factures et les frais de chaque collaborateur ainsi que le total des
frais pour le mois de janvier.
3. LES SOUS - TOTAUX
Excel permet d’insérer dans une liste de données numériques des lignes de sous-totaux ou de statistiques. Pour réaliser
un sous-total dans une liste, il est impératif de classer par ordre croissant ou décroissant la colonne pour laquelle vous
souhaitez obtenir le sous-total. Nous allons utiliser cette fonctionnalité pour totaliser les données d’un tableau de ventes
en utilisant divers critères.
2
IUT TC 2006-2007
E XERCICE
• Placez vous sur la feuille « statVentesPays » du fichier [Link]
• Triez le tableau selon le pays
• Pour appliquer un sous total, cliquez sur Données / Sous-totaux…. La boîte de dialogue Sous-total
s ‘affiche :
• Changez les valeurs des paramètres, comme indiqué dans la figure. Le paramètre À chaque changement
de permet de choisir le champ selon lequel les données seront regroupées pour être totalisées. Ici, les statistiques
seront calculées par pays. Le paramètre Ajouter un sous-total à permet de choisir le champ auquel sera
appliquée la fonction mathématique sélectionnée précédemment. Ici, ce sont les chiffres d'affaires (CA) qui seront
additionnés.
• Le bouton Supprimer tout permet d'effacer les sous-totaux présents dans une feuille.
• Cliquez sur OK pour valider vos choix. Les sous-totaux apparaissent tels qu'illustrés ci-dessous :
3
IUT TC 2006-2007
On observe le total de la France et celui de l’Italie ainsi qu’une ligne Total affichant le total général de la colonne CA. Les
boutons 1, 2 et 3 situés en haut à gauche de la feuille permettent de visualiser plus ou moins de détails sur les
statistiques présentées.
Sur la partie gauche de votre écran, Excel affiche le plan de la liste de données. Les boutons + et - de la colonne de
gauche permettent de développer un niveau de sous-total pour l'afficher en détail, ou, au contraire, de ne montrer que
son total général.
• Cliquez sur le signe – placé en bas de chaque curseur pour ne conserver que les lignes de sous-totaux. Le signe –
se transforme en signe +.
• Cliquez sur le signe + pour afficher les lignes prises en compte par le sous-total.
E XERCICE
• Placez vous sur la feuille « Commerciaux » du fichier [Link] et réalisez les sous-totaux de chiffre d’affaires
réalisé par chaque commercial pour chaque mois.