Université de La Rochelle Informatique Transversale
Partie 1
TP n°6 : Fixer les cellules, Fonctions
Objectif du TP :
Le but de cette séance est d'approfondir l'utilisation des fonctions
mathématiques élémentaires. Nous aborderons un aspect important :
l'adressage des cellules sous Calc.
Table des matières
PREPARATION.......................................................................................................1
PARTIE 1 : Utilisation du tableur..........................................................................2
I- Insertion de formules et de fonctions..........................................................2
# 1 Révisions...............................................................................................2
# 2 Extension de formules...........................................................................2
II- Utilisation du $ pour fixer des cellules........................................................3
# 1 Qu'est-ce que c'est ?.............................................................................3
# 2 Application.............................................................................................3
# 3 Récapitulatif..........................................................................................4
III- Recherche..................................................................................................4
# 1 Description de la fonction......................................................................4
# 2 Application.............................................................................................5
IV- Fonctions supplémentaires........................................................................5
# 1 Description des fonctions......................................................................5
# 2 Application.............................................................................................5
PARTIE 2 : Exercices.............................................................................................6
I- Données utilisées.........................................................................................6
II- Travail à réaliser au cours de la séance......................................................6
III- Dépôt sur Moodle.......................................................................................7
PREPARATION
Comme d’habitude, créez un dossier « TP6 » dans votre dossier « Informatique
Transversale ». Récupérez sur Moodle, l’archive « Matériel.zip » et
décompressez-la dans votre dossier « TP6 ».
Pour les étudiants possédant un compte PiX, suivez la procédure de
transfert de compte expliquée au début du cours Moodle (lien)
Page 1/7
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 1 : Utilisation du tableur
I- Insertion de formules et de fonctions
# 1Révisions
Pour commencer, voyons si vous n'avez pas oublié ce que nous avons vu lors
de la dernière séance.
Ouvrir le fichier compteannuels.ods.
Dans la cellule F4, calculer les recettes annuelles.
Dans la cellule B6, calculer le bénéfice du trimestre 1.
Le tableau n'est pas complet, car il reste le calcul des coûts annuels en F5, les
bénéfices des trimestres 2, 3 et 4, respectivement en C6, D6 et E6 ainsi que le
bénéfice annuel en F6.
Si la question suivante était d'établir les formules pour compléter les cellules
mentionnées ci-dessus, une majorité d'entre vous déciderait de créer de
nouvelles formules. Cette méthode est correcte mais elle n'est pas efficace.
Supposez que votre tableau comporte les 4 trimestres des années 1998 à
2008, établiriez-vous 55 fois la même formule ? Dans ce genre de situation, il
est préférable d'utiliser la technique proposée par le tableur : l'extension de
formule.
# 2Extension de formules
Étendre la formule consiste à :
• Saisir la formule sur la première cellule (par exemple ici en
B6).
• Sélectionner la cellule contenant la formule
• Cliquer avec la souris sur la « poignée » en bas à droite
• Tout en maintenant le bouton gauche de la souris enfoncé, étendre la
formule jusqu'en E6.
• Enfin relâcher le bouton et observer le résultat.
Compléter les cellules C6, D6, E6, F5 et F6 en utilisant l'extension de
formules
Page 2/7
Université de La Rochelle Informatique Transversale
Partie 1
II- Utilisation du $ pour fixer des cellules
# 1Qu'est-ce que c'est ?
Lorsqu’on glisse une formule, nous ne voulons pas toujours tout glisser.
Lorsqu’on veut fixer certaines cellules, on utilise le caractère $ pour cela.
# 2Application
Mettons tout cela en application.
Ouvrir le fichier CommandeFruits.ods
Calculer le prix total de chaque article (cellules D4 à D11).
Remarque : n'oubliez pas d'utiliser l'extension de formules.
Conseil : Prenez l'habitude de vérifier que votre extension de formules est
correcte.
Par exemple, ici:
• Sélectionner la dernière cellule D11.
• Regarder la formule dans la barre de formules ou appuyez sur la touche
« F2 » pour mettre en surbrillance les cellules qui sont utilisées dans la
formule.
• Vérifier si la formule est correcte (« =C11*B11 »).
En faisant, cette rapide vérification vous éviterez de commettre des erreurs,
notamment quand l’utilisation du $ est nécessaire.
En D13, calculez le prix total
On souhaite ensuite calculer pour chaque article, le pourcentage du prix de
celui-ci par rapport à la somme totale de la commande.
Sélectionner la cellule E4.
Pour calculer le rapport demandé, écrire la formule « =D4/D13 ».
Recopier (par extension) la formule en E5.
Un message d'erreur apparaît. Analysons ce qui s'est passé.
Sélectionner la cellule E5.
Regarder la formule dans la barre de formule ou appuyer sur la touche
« F2 ».
La formule est « =D5/D14 ». D5 fait bien référence aux prix des poires. Mais à
quoi fait référence la cellule D14 ?
Page 3/7
Université de La Rochelle Informatique Transversale
Partie 1
Réponse : « A rien ».
La référence à la cellule D13 (de la formule écrite en E4) a été incrémentée de
1 pour donner D14 (13 +1 = 14) lorsque l'on a étendu la formule vers le bas.
Dans notre exemple, le calcul du pourcentage doit toujours être réalisé par
rapport à la cellule D13. Il faut donc pour pouvoir étendre la formule, «fixer» la
cellule D13. Comme l'extension est réalisée vers le bas, il faut empêcher
l'incrémentation des lignes. On insère donc un « $ » devant la référence à la
ligne 13 et non devant la colonne D.
En E4, écrire la formule « =D4/D$13 ».
Recopier (par extension) la formule jusqu'en E11.
Vérifier si l'extension est correcte.
Insérer les formules adéquates dans les cellules D15 et D16.
# 3Récapitulatif
Fiche : Fixer des cellules à l’aide du $
III- Recherche
# 1Description de la fonction
Il existe trois fonctions de recherche (RECHERCHE, RECHERCHEV et
RECHERCHEH) permettant de trouver une information dans un tableau et de la
récupérer dans un autre tableau.
Nous utiliserons uniquement RECHERCHEV(Critère, Matrice, Indice)
• Critère : indique la valeur à rechercher dans la « Matrice »
(deuxième argument)
• Matrice : tableau à deux dimensions contenant dans la première
colonne les valeurs prises par le critère (premier argument) et dans une
autre colonne les valeurs à retourner par la fonction.
• Indice : le numéro de la colonne de la « Matrice » contenant les
valeurs à retourner.
• Ordre : indique si la colonne des critères est ordonnée. Mettez toujours
0 (C’est moins performant mais plus simple).
Page 4/7
Université de La Rochelle Informatique Transversale
Partie 1
# 2Application
Ouvrir le fichier rechercheV.ods. L’objectif est le suivant : dans la feuille
« Article », vous allez récupérer les taux de TVA (colonne E) de la feuille
« Taux » en fonction de la catégorie (colonne D) à l’aide de la fonction
RECHERCHEV. Suivez les étapes ci-dessous :
Dans la cellule « E2 », utilisez l’assistant fonction pour configurer la fonction
RECHECHEV.
Le premier argument de RECHERCHEV est la catégorie de TVA, cellule D2.
Le deuxième argument de RECHERCHEV est la matrice des taux de TVA du
la feuille « Taux », il s’agit de la plage A2:C5.
Le troisième argument de RECHERCHEV est le numéro de la colonne de la
matrice contant les taux de TVA : 3.
Vous pouvez valider votre formule. Insérez les $ pour figez les bonnes
cellules et étendre la formule E2 jusqu’à la cellule E24.
Adaptez le travail précédent, pour compléter la colonne I avec la fonction
RECHERCHEV.
IV- Fonctions supplémentaires
# 1Description des fonctions
Il arrive que l'on ait besoin de compter les cellules qui contiennent un certain
type d'information. Calc dispose de 2 fonctions :
• NB ( ref ) : compte le nombre de cellules contenant des valeurs
numériques (les cellules vides ou contenant du texte sont ignorées).
• NBVAL( ref. ) : compte le nombre de cellules contenant des valeurs
textuelle ou numérique (les cellules vides sont ignorées).
ref: correspond à un ensemble de cellules séparées par des points virgules « ; »
(ex : A1;B23;C4;D5) ou une plage de cellule (B3:G3).
# 2Application
Mettons en pratique les fonctions précédentes.
Ouvrir le fichier compter.ods.
Pour les colonnes « colonne 1 », « colonne 2 » et « colonne 3 », comparer
les résultats que donnent les fonctions NB et NBVAL que vous définirez
respectivement sur lignes 30 et 31.
En G18, saisir la formule permettant de compter le nombre de cellules de la
« colonne 5 » contenant une valeur numérique.
En H18, saisir la formule permettant de compter le nombre de cellules de la
« colonne 6 » contenant des lettres.
Page 5/7
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 2 : Exercices
I- Données utilisées
Dans cet exercice, nous utiliserons le fichier
nuitesHotelsPoitouCharentes.ods. Ce fichier contient le nombre de nuitées
dans les hôtels de la région Poitou Charente de 2011 à 2015.
Remarque : Ces données ont été fournies par le site opendata.gouv.fr qui recense
l’ensemble des données accessibles pour la France dans un tas de domaine.
II- Travail à réaliser au cours de la séance
Ouvrir le fichier nuitesHotelsPoitouCharentes.ods.
Sélectionner ensuite l'onglet Valeurs et observer le contenu du tableau
Sélectionner à l’aide de votre souris la ligne 3, puis dans le menu Affichage-
>Fixer lignes et colonnes. Faites défiler la page pour voir le résultat.
Sur la colonne C, calculer la somme des données de chaque mois : lignes
de 3 à 74.
Petite astuce, vous pouvez faire la première formule puis la glisser mais
vous pouvez également double cliquer sur la petite poignée pour remplir
jusqu’en bas. (ne fonctionne que sur une colonne)
Sur la ligne 76, calculer la somme des nuitées de toutes les années
(colonne de C à E)
Sur la ligne 77, calculez le nombre de valeurs présentes dans les colonnes
CàE
Sur la ligne 78, calculer la valeur minimale de chaque colonne (C à E)
Sur la ligne 79, calculer la valeur maximale de chaque colonne (C à E)
Sur la ligne 80, calculer les moyennes à l'aide de la fonction moyenne.
L'objectif des questions précédentes était de vous faire acquérir un certain
nombre d'automatismes. Maintenant nous allons exploiter les données année
par année. Il existe des techniques plus efficaces permettant d'arriver au
Page 6/7
Université de La Rochelle Informatique Transversale
Partie 1
même résultat, mais nous les étudierons ultérieurement.
L'idée sera, pour une année donnée, de calculer le nombre de nuitées par
année.
Vous réaliserez les calculs décrits ci-dessous :
- H5 à M5 : déterminez le total de nuitées pour chaque année
- H6 à M6 : calculez le rapport en pourcentage entre le nombre de
nuitées de l’année et le nombre de nuitées de l’ensemble des années
Attention :
• Il n'est pas demandé de recopier le tableau ci-dessus, mais de mettre en
place les formules permettant d'aboutir à ce résultat.
• Pensez notamment à utiliser les méthodes d'adressage vu
précédemment.
III- Dépôt sur Moodle
Afin de ne pas perdre vos bonnes habitudes, vous allez compresser et déposer
sur Moodle le travail réalisé aujourd'hui.
• Enregistrer votre travail et fermer Libre Office. Aucune fenêtre Calc ne
doit rester ouverte.
• Compresser le dossier TP6.
• Renommez l'archive obtenue afin qu'elle se nomme nom_prénom.zip
(Attention avec VOTRE nom et VOTRE prénom).
• Déposer l'archive sur Moodle en accédant au lien Dépôt_TP6 dans la
rubrique TP6
Page 7/7
