Séance 2 - FORMULES, CALCULS, FONCTIONS - Cours et exercices en autonomie

2.1 Formules

Un tableur donne la possibilité de créer des formules qui permettent d'effectuer des opérations sur une donnée (ou un ensemble de données) stockée dans une (ou plusieurs respectivement) cellule. La formule qui est saisie dans une cellule et le résultat de l'opération sont stockés et affichés dans cette même cellule. La saisie d'une formule doit employer un sous-ensemble de termes et respecter une certaine syntaxe :

• La formule commence nécessairement par le symbole « = ».
• Elle utilise les références des cellules pour appliquer l'opération sur les données ou valeurs que l'on souhaite utiliser.
• Les types d'opération permis sont les fonctions prédéfinies par le tableur (que nous voyons par la suite) et les opérations arithmétiques classiques sur les nombres selon les symboles suivants : « + », « - », « * », « / » pour respectivement l'addition, la soustraction, la multiplication et la division.
• Le résultat de l'opération est affiché dans la cellule au sein de laquelle est définie la formule. En revanche, pour visualiser la définition même de la formule, il faut se positionner sur la cellule au sein de laquelle est définie (afin de rendre cette cellule active). La formule est alors affichée dans la barre de formule:

Ci-dessus nous avons saisi en A2 et A3 les nombres 15 et 10 et défini les formules suivantes (il faut appuyer sur ENTRÉE après chaque saisie de formule pour la valider) :

• Dans B2 (cellule active) nous avons saisi : « =A2+A3 ».
• Dans C2, nous avons saisi : « =A2-A3 ».
• Dans D2, nous avons saisi : « =A2*A3 ».
• Dans E2, nous avons saisi : « =A2/A3 ».

Ici les formules sont définies en fonction des cellules A2 et A3. Si nous changeons les valeurs saisies en A2 et A3, les résultats des opérations sont automatiquement mis à jour en fonction de ces nouvelles valeurs.

 Remarque importante

Si après avoir saisi une formule le résultat affiché est #VALEUR ! cela indique qu'il y a un problème avec la syntaxe de la formule ou un mauvais emploi de celle-ci. Il faut alors trouver l'erreur. Ce « débogage » se fait au cas par cas car chaque fonction a son propre fonctionnement.

 À vous de jouer ! Additionner, soustraire, multiplier, diviser

Démarrez Excel, ouvrez un nouveau classeur, puis enregistrez ce classeur avec le nom de fichier seance2Partie1Calculatrice : Fichier / Enregistrer sous / Parcourir Clic(s) dans l'arborescence jusqu'au répertoire choisi Nom de fichier remplir avec le nom ci-dessus / Enregistrer


Démonstration : Enregistrer son travail


Dans ce classeur, saisissez dans les cellules appropriées les nombres, textes et formules énoncés précédemment. Vérifiez que vous obtenez les mêmes valeurs que dans l'illustration précédente
Saisissez dans A2 et A3 les valeurs 9 et 3. Vérifiez que vous obtenez automatiquement les nombres montrés ci-dessous :



Saisissez à présent dans A2 la valeur 774
Regardez la valeur obtenue en E2
Reportez cette valeur ci-dessous pour attester de votre participation à cette séance 

2.2 Fonctions et traitements prédéfinis

Une formule peut contenir une fonction qui est un algorithme permettant d'effectuer une opération (ou un traitement) prédéfinie sur un ensemble de données (identifiées par leurs cellules). Certaines de ces opérations utilisent des paramètres qu'il est donc également nécessaire de fournir lors de la saisie de la formule.
Les données en entrée et les paramètres représentent les arguments de la fonction (lorsque celle-ci en requiert). Dit autrement, les arguments précisent « sur quoi » et « comment », la fonction doit être effectuée.
Il existe aussi des fonctions sans argument (par exemple, la fonction PI() qui donne la valeur du nombre pi), des fonctions qui n'ont qu'un seul argument, et des fonctions qui acceptent plusieurs arguments.

La syntaxe générale d'une fonction Excel saisie dans une cellule est « = fonction (arg1 ; arg2 ; .... ) » où l'on distingue:

• le nom de la fonction,
• puis une série d'arguments entre parenthèses séparés par des points virgules « ; ».
  

Les fonctions sont accessibles dans Excel de différentes façons :

• en saisie directe au clavier (notez que si vous commencez par saisir le symbole « = » dans une cellule et ensuite une première lettre comme « =S », Excel vous propose des fonctions commençant par cette première lettre dans l'ordre alphabétique. Vous pouvez dans ce cas dérouler la liste et cliquer sur la fonction qui vous intéresse. Il s'agit d'une fonction d'auto-complétion qui vous aide dans l'édition de formules),
  
• par le Ruban Accueil Edition Menu déroulant de Somme automatique,
  
• par la Barre de formule Clic sur fx,
• par le Ruban Formules (regroupement des fonctions selon le type de données).

Démonstration : Différentes façons d'écrire une fonction

 À vous de jouer ! La fonction SOMME

1. Dans le classeur ouvert et la feuille active trouvez la cellule B3
2. Utilisez la fonction SOMME afin de faire la somme des cellules A2 et A3
3. Vérifiez que vous obtenez le résultat montré ci-dessous:

Correction : La fonction SOMME

2.3 Les plages de données c'est quoi ?

La plage de données c'est un ensemble de cellules formant un rectangle. Pour désigner une plage de cellules, on utilise le « code » suivant : « référence de la cellule du coin supérieur gauche du rectangle : référence de la cellule du coin inférieur droit du rectangle». Dans l'exemple ci-dessous la plage de données active (représenté par le contour vert) est représentée par le code C4:E10.

Démonstration : Sélectionner une plage de données

2.4 Fonctions simples pour séries de nombres

Différents types de données, donnent lieu à des fonctions distinctes. Voici ci-dessus une liste de fonctions Excel permettant de faire des traitements sur des nombres (entiers ou décimaux):

• SOMME : calcule la somme de plusieurs nombres. Les arguments de cette fonction peuvent être des valeurs constantes ou des références à des cellules ou à des plages de cellules.
• MOYENNE : calcule la moyenne arithmétique de plusieurs nombres. Les arguments de cette fonction peuvent être des valeurs constantes, des références à des cellules ou à des plages de cellules.
• MIN et MAX : renvoient respectivement la plus petite valeur et la plus grande valeur d’un ensemble de nombres.

Ci-dessous en C2 nous avons saisi « =MOYENNE(B2:B21) » pour déterminer la moyenne arithmétique sur toutes les personnes pour la matière Histoire.

Démonstration : Calculer une moyenne, un maximum et un minimum

 À vous de jouer ! MOYENNE, MIN, MAX

1. Téléchargez le fichier Seance2Notes, sauvegardez le dans un répertoire de votre choix (Documents par exemple), puis ouvrez le avec Excel.

Cliquer ici pour télécharger le fichier Seance2Notes

2. Dans la ligne 23, déterminez les moyennes de chaque matière à l’aide de la fx MOYENNE.
3. Dans la ligne 24, déterminez les plus faibles notes de chaque matière à l’aide de la fx MIN.
4. Dans la ligne 25, déterminez les plus fortes notes de chaque matière à l’aide de la fx MAX.
5. Vérifiez que vous obtenez les résultats suivants:

 À vous de jouer !

1. Ouvrez un nouveau classeur Excel.
2. Saisissez dans les cellules appropriées les textes, et formules afin de reproduire l’exemple « Game of Thrones » ci-dessus.
3. Vous faites partie des amateurs qui ont été déçus du dernier épisode de la série ! En C3 saisissez la formule utilisant la fx SUBSTITUE permettant de transformer le texte obtenu en C2 en « Blame of Thrones ».
4. En C4 saisissez la formule permettant de transformer le texte obtenu en C3 en majuscule.

Correction : Fonction NBCAR, CONCAT, SUBSTITUE, MAJUSCULE

2.6 Fonctions simples pour séries de dates

Voici ci-dessus une liste de fonctions Excel permettant de faire des traitements sur des dates :

• AUJOURDHUI : ne prends pas d’argument, AUJOURDHUI() renvoie la date du jour actuel.
• ANNEE : renvoie l’année d’une date.
• MOIS : renvoie le mois d’une date.
• JOUR : renvoie le numéro du jour dans le mois étant donné une date.
• JOURSEM : renvoie le jour de la semaine d’une date.
• NO.SEMAINE : renvoie le numéro de la semaine à laquelle appartient la date.

Exemple ci-dessous : nous avons saisi « 14/02/2022 » en A2, puis nous avons saisi « =JOUR(A2) » n B6.

 À vous de jouer ! Fonction DATES

1. Ajoutez une nouvelle feuille Excel dans le classeur que vous avez ouvert précédemment pour l’exemple de « Game of Thrones ».
   
2. Renommez cette feuille avec le titre « ma date préférée ».
3. Saisissez dans les cellules appropriées les textes, et formules afin de reproduire l’exemple de la Saint-Valentin ci-dessus.
4. Vous faites partie des amateurs de la fête de la musique ! Saisissez en A2 la date 21/06/2022. Quel jour de la semaine (lundi ? mardi ? mercredi ? …) la fête de la musique de 2022 s’est-elle déroulée ?
5. Reportez la réponse ci-dessous pour valider votre participation à la séance.
   

Correction : Fonction DATES

 À vous de jouer !

1. Téléchargez le fichier Seance2Adresses :
   

Cliquer ici pour télécharger le fichier Seance2Adresses

2. Sauvegardez le dans un répertoire de votre choix (Documents par exemple), puis ouvrez le avec Excel. Vérifiez que vous avez les données suivantes :
   



3. Insérez une colonne entre les colonnes B et C. La nouvelle colonne (vide) est de référence C. Saisissez en C1 « Prénom Nom ».
4. De C2 à C6, saisissez respectivement les formules permettant de concaténer en un seul texte le Prénom puis le Nom de chacun de ces 5 étudiants. Vous devez par exemple obtenir « Joseph Aiméride » en C2 (voir aussi ci-dessous le résultat final attendu).
5. Insérez une colonne entre les colonnes H et I. La nouvelle colonne (vide) est de référence I. Saisissez en I1 « Adresse ».
6. De I2 à I6, saisissez respectivement les formules permettant de concaténer en un seul texte l’adresse complète de chacun de ces 5 étudiants. Vous devez par exemple obtenir « 1 Rue du calendrier 69010 OnlyLyon » en I2 (voir aussi ci-dessous le résultat final attendu).
7. Calculez la moyenne des 5 matières de ces 5 étudiants dans la colonne O qui suit la colonne des notes en Anglais. En O1 saisissez « Moyenne ». Vérifiez que votre Feuil1 est similaire à ci-dessous.



8. Insérez une nouvelle feuille dans le classeur intitulée « Feuil2 ». Copiez la plage des données A1:O6 comportant toutes les données. Faites un collage spécial (Valeurs uniquement) dans la nouvelle feuille. Supprimez les colonnes exceptées celles des « Prénom Nom », « Adresse » et « Moyenne ».
9. Vérifiez que vous obtenez les mêmes valeurs que dans la table ci-dessous et appliquez également les mises en forme adéquates afin de reproduire le même rendu visuel.



10. Enregistrez les modifications : Fichier -> Enregistrer.

Correction : Révision fonction CONCAT, MOYENNE et Mise en page

La conception de ce cours a bénéficié d'un financement de :