Formation tableur niveau 2 – ycinotti.free.fr

 

 

Formation tableur niveau 2 (Excel 2016 Mac)

L’objectif général de cette formation est de vous permettre d’améliorer votre confort de travail et de
découvrir quelques fonctions avancées d’Excel.

Sommaire

1.

Fractionner un tableau ……………………………………………………………………………………………………………………………………….. 1

2. Renvoyer le contenu d’une cellule à la ligne et le centrer en hauteur ………………………………………………………………………… 1
3. Masquer des colonnes ………………………………………………………………………………………………………………………………………… 2

4. Utiliser une recopie incrémentée …………………………………………………………………………………………………………………………. 2
Inverser les lignes et les colonnes d’un tableau ……………………………………………………………………………………………………… 2
5.

6. Réaliser la copie d’une feuille …………………………………………………………………………………………………………………………….. 2
7. Nommer une cellule et une zone …………………………………………………………………………………………………………………………. 3

8. Copier la valeur d’une cellule ……………………………………………………………………………………………………………………………… 3
9. Utiliser une condition dans une formule (fonction SI) ……………………………………………………………………………………………. 3
10. Quelques fonctions Excel fort utiles …………………………………………………………………………………………………………………….. 4

11. Réaliser des calculs entre dates ……………………………………………………………………………………………………………………………. 4
12. Formater des dates …………………………………………………………………………………………………………………………………………….. 5

13. Formater un résultat pour ajouter du texte à un nombre ………………………………………………………………………………………….. 5
14. Afficher les lignes correspondant à un critère ……………………………………………………………………………………………………….. 5

15. Réaliser des sous-totaux …………………………………………………………………………………………………………………………………….. 6

 Télécharger le fichier Clients.xlsx disponible à l’URL http://goo.gl/mX2eAi
 Ouvrir le fichier

1. FRACTIONNER UN TABLEAU

Problème :

Je souhaite que la ligne 1, qui comporte les titres, soit toujours visible même si je
consulte les dernières lignes du tableau

Solution : ▪ Dans l’onglet Affichage, au milieu, cliquer sur Figer la ligne supérieure  descendre

dans le tableau pour constater que la ligne 1 reste affichée

▪ Pour voir toujours la première ligne et le nom de famille : clic en C2 > dans l’onglet

Affichage, au milieu, cliquer sur Libérer les volets > cliquer sur Fractionner

▪ Pour annuler la dernière opération, cliquer à nouveau sur Fractionner

2. RENVOYER LE CONTENU D’UNE CELLULE À LA LIGNE ET LE CENTRER EN HAUTEUR
Problème :
Solution : ▪ En A1, saisir TITRE PERSONNE > Entrée

Comment faire en sorte que le texte d’une cellule soit renvoyé à la ligne ?

▪ Dans l’onglet Accueil, à droite, cliquer sur Mise en forme > Largeur de colonne…
> Largeur de la colonne : 2,5 cm> OK  l’intitulé de la colonne A est tronqué

▪ Cliquer en A1 > dans l’onglet Accueil, au milieu, cliquer sur l’icône Renvoyer à la

▪ Sélectionner les cellules A1:H1 > dans l’onglet Accueil, cliquer sur l’icône Aligner au

ligne automatiquement

centre

TAB2_Excel2016Mac.docx

1 / 7

 Y. CINOTTI

NB :

➢ Il est aussi possible d’adapter automatiquement la taille de la police à la largeur de la

cellule : en F1, après CODE, saisir POSTAL > clic droit sur la cellule F1 > Format de
cellule > onglet Alignement > cocher  Ajuster > OK

3. MASQUER DES COLONNES
Problème :

Comment voir la colonne B (NOM) et la colonne G (VILLE) l’une à côté de l’autre sans
déplacer ou supprimer des colonnes ?

Solution : ▪ Sélectionner les colonnes C, D, E et F > clic droit sur la sélection > Masquer

▪ Pour afficher à nouveau les colonnes masquées : sélectionner les colonnes B et G > clic

droit sur la sélection > Afficher

4. UTILISER UNE RECOPIE INCRÉMENTÉE

Problème :
Solution : ▪ cmd+N (ou cliquer en bas de la fenêtre, à droite de la feuille Client, sur l’icône

Comment obtenir la suite logique d’une série de données ?

Insérer une feuille)

▪ En B1, saisir : Janvier > Entrée > cliquer en B1 > placer le pointeur sur le petit carré
dans le coin inférieur droit de la cellule B1 de façon à faire apparaître une croix noire
> cliquer-glisser vers la droite jusqu’à la cellule M1 pour afficher les autres mois.

▪ En A2, saisir : 1 > En A3, saisir : 3 > sélectionner les cellules A2 et A3 > étirer jusqu’en

NB :

➢ De la même manière, il est possible d’obtenir les jours de la semaine en étirant une

A8  la suite 5, 7, 9, 11, 13 s’affichent.

cellule dans laquelle a été saisi lundi.

5. INVERSER LES LIGNES ET LES COLONNES D’UN TABLEAU
Problème :

Après réflexion, je voudrais inverser la présentation du tableau de façon que les mois
soient placés en colonne et les nombres en ligne

Solution : ▪ Sélectionner les cellules A1 à M8 > clic droit sur la sélection > Copier > cliquer en A10

> dans l’onglet Accueil, à gauche, cliquer sur la flèche à droite l’icône Coller
> Transposer (ou Collage spécial… > cocher  Transposé > OK)

▪ Supprimer les lignes 1 à 9

6. RÉALISER LA COPIE D’UNE FEUILLE
Problème :
Solution : ▪ Cliquer en A1 > saisir : Mois > dans l’onglet Accueil, cliquer sur l’icône Mettre
sous forme de tableau > cliquer sur un style de tableau à votre goût > cocher  Mon
tableau comporte des en-têtes > OK

Je veux disposer d’une autre feuille exactement semblable.

▪ En bas à gauche de la fenêtre, clic-droit sur l’onglet Feuil1 > Déplacer ou copier…
> Avant la feuille : Feuil2 ou (en dernier) > cocher  Créer une copie > OK
▪ Double-cliquer sur l’onglet de la nouvelle feuille > Saisir : REGION EST > Entrée
➢ Lorsqu’une feuille a été mise en forme, le fait de copier-coller un tableau dans une autre
feuille ne permet pas de conserver exactement la même mise en forme. C’est pourquoi il
est préférable de passer par Déplacer ou copier…

NB :

TAB2_Excel2016Mac.docx

 Y. CINOTTI

2 / 7

7. NOMMER UNE CELLULE ET UNE ZONE
Problème :
Solution : ▪ Saisir dans la colonne B, les données suivantes

Comment écrire les formules en langage un peu plus naturel pour faciliter la lecture ?

▪ En A16, saisir TVA
▪ En B16, saisir 20 % (sans espace avant %)
▪ Cliquer en B15 > dans l’onglet Formules, au milieu,

cliquer sur Définir un nom  le nom TVA est proposé
> OK  le nom de la cellule apparaît dans la zone
Nom, à gauche de la barre de formule.

▪ En C2, pour calculer le montant de la TVA, saisir : =B2*TVA
▪ Revenir vers la feuille Clients > cliquer en H2 > cmd+maj+ (pour sélectionner toutes
les cellules jusqu’à la première cellule vide) > clic droit sur la sélection > Définir un
nom… > sous Entrer un nom pour la plage de données, saisir : CA > OK
▪ En H133 calculer le chiffre d’affaires total en saisissant : =SOMME(CA)
▪ En H134 calculer la moyenne en utilisant le nom CA. Le résultat doit être 621,24

8. COPIER LA VALEUR D’UNE CELLULE
Problème :
Solution : ▪ Revenir vers la feuille REGION EST

Comment copier non pas la formule contenue dans une cellule mais le résultat affiché ?

▪ Copier les cellules C2 à C6 > cliquer en D2 > dans l’onglet Accueil, à gauche, cliquer
sur la flèche à droite de l’icône Coller > Coller une valeur  en cliquant en D2
vous constatez que la cellule contient bien un nombre et non pas une formule comme en
C2.

9. UTILISER UNE CONDITION DANS UNE FORMULE (FONCTION SI)

Problème :

Le contenu des cellules C7 à C13 est inutile puisque je ne dispose pas encore des
données pour les mois de juin à décembre. Il faudrait donc ne réaliser le calcul de la
TVA que si une valeur a été saisie dans la colonne B.

Solution : ▪ Cliquer en C2 > à la place de la formule actuelle, saisir : =SI(B2>0;B2*TVA;””)
NB :

➢ La formule =SI(B2>0;B2*TVA;””) signifie, en langage naturel : « si le contenu de la
cellule B2 est supérieur à 0 alors calculer B2 × TVA sinon ne rien afficher dans la
cellule ».

➢ Pour afficher du texte, il faut le placer entre guillemets. Si on veut que rien ne s’affiche,

il suffit de saisir deux fois des guillemets

➢ La fonction SI permet de réaliser des tests. Sa syntaxe est la suivante :

SI(condition;alors…;sinon…)

TAB2_Excel2016Mac.docx

 Y. CINOTTI

3 / 7

10. QUELQUES FONCTIONS EXCEL FORT UTILES
▪ Ouvrir une nouvelle feuille dans le classeur. En A1, saisir 12,75 et en A2 saisir 13,83.
▪ Sélectionner les deux cellules et cliquer-glisser le petit carré dans le coin inférieur droit de la cellule

A2 jusqu’en A7 de façon à obtenir une suite incrémentée (de 12,75 à 19,23).

▪ En A8, saisir Cheval
▪ Dans la colonne C, saisir les formules

Fonction

Formule à saisir

Résultat

En langage
naturel

ARRONDI(nombre;nombre de
décimales)
 L’icône Réduire les décimales
ne permet pas d’arrondir mais
seulement de masquer les décimales.
NBVAL(valeur1;valeur2;…)
 la fonction NB(valeur1;valeur2;…)
ne compte que les cellules comportant
des nombres

=ARRONDI(A1;1)

Arrondir le nombre
en A1 avec une
décimale

=NBVAL(A1:A8)

12,8

8

ALEA()

=ALEA()*20

aléatoire

Compter le nombre
de cellules non vides
de A1 à A8

Renvoyer un nombre
aléatoire entre 0 et 20
(le résultat change
chaque fois que la
feuille est modifiée
ou en appuyant sur
F9

Afficher le rang de la
cellule A6 parmi les
données de A1 à A7

2
(puisque
18,15 est le
deuxième
nombre le
plus élevé)

RANG(valeur;liste de valeurs)

=RANG(A6;A1:A7)

11. RÉALISER DES CALCULS ENTRE DATES

Problème :
Solution : ▪

Je cherche à calculer le nombre de jours que j’ai vécus depuis ma naissance
Insérer une nouvelle feuille dans le classeur

▪ En A1 saisir : =AUJOURDHUI()  la date du jour s’affiche
▪ En A2 saisir votre date de naissance sous la forme jj/mm/aa
▪ En A4 saisir : =A1-A2
▪ Pour afficher le résultat avec une espace entre les centaines et les milliers (ce qui est
« typographiquement » correct) : cliquer en A4 > dans l’onglet Accueil, au milieu,
cliquer sur l’icône Séparateur de milliers

TAB2_Excel2016Mac.docx

 Y. CINOTTI

4 / 7

12. FORMATER DES DATES
Problème :
Je souhaite que les dates dans les cellules A1 et A2 précisent le jour de la semaine
Solution : ▪ Sélectionner les cellules A1 et A2 > clic droit sur la sélection > Format de cellule…

> onglet Nombre > Catégorie : Personnalisée > saisir dans la zone sous Type :
jjjj j mmmm aaaa  le résultat peut être vérifié dans le cadre Exemple > OK

13. FORMATER UN RÉSULTAT POUR AJOUTER DU TEXTE À UN NOMBRE
Problème :
Solution : ▪ Clic droit sur A4 > Format de cellule

Je veux que le résultat en A4 précise qu’il s’agit d’un nombre de jours

▪ Onglet Nombre > Catégorie : Personnalisée > dans la liste déroulante sous Type,

choisir # ##0 > compléter le format
en saisissant dans la zone sous Type,
juste après le 0 : ” jours” (y compris
les guillemets et avec une espace
avant le j) > OK

 Revenir vers la feuille Clients

14. AFFICHER LES LIGNES CORRESPONDANT À UN CRITÈRE
Problème :
Solution : ▪ Cliquer sur n’importe quel nom dans la colonne G > dans l’onglet Données, au milieu,
cliquer sur l’icône Filtrer  une flèche apparaît dans la ligne 1, à droite de chaque
intitulé de colonne

Comment afficher uniquement les clients habitants Balma ?

▪ Cliquer sur la flèche de la cellule VILLE > dans la liste, décocher  (Sélectionner tout)

> cocher  Balma > fermer la fenêtre VILLE

Problème :
Solution : ▪ Dans l’onglet Données, au milieu, cliquer sur l’icône

Comment supprimer la sélection et afficher tout à nouveau ?

Problème :
Solution : ▪ Cliquer sur la flèche de la cellule TOTAL > cliquer sur la double flèche à droite de

Comment sélectionner les clients générant le plus gros chiffre d’affaires ?

Choisir > 10 premiers

Problème :
Solution : ▪ Dans la fenêtre TOTAL, en bas, cliquer sur le pavé

Comment sélectionner les clients dont le total est compris entre 300 et 600 ?

Effacer le filtre

▪ Cliquer sur la double flèche à droite de Choisir

> Supérieur ou égal à > dans la cellule de droite, saisir :
300 >  Et > cliquer sur la double flèche à droite de
Choisir > Inférieur ou égal à > 600 > OK

5 / 7

TAB2_Excel2016Mac.docx

 Y. CINOTTI

15. RÉALISER DES SOUS-TOTAUX
Problème :
Solution : ▪ Onglet Données > au milieu, cliquer sur l’icône Effacer

Comment calculer le montant moyen dépensé par les clients d’une même commune ?

▪ Cliquer sur une ville de la colonne VILLE > dans l’onglet Données, au milieu, cliquer

sur l’icône Trier de A à Z

▪ Dans l’onglet Données, à droite, cliquer sur l’icône Sous-total
▪ À chaque changement de : VILLE > Utiliser la fonction : Moyenne > Ajouter un sous-

total à :  TOTAL > OK

▪ A gauche de la fenêtre, est apparue une bande grisée avec, en haut, trois

boutons 1 2 3

sont affichées

▪ Cliquer sur le bouton 2  les détails disparaissent, seules les villes et les moyennes

▪ Cliquer sur le bouton 1  seule la moyenne générale est affichée
▪ Pour supprimer les sous-totaux : dans l’onglet Données, cliquer sur l’icône Sous-total

> Supprimer tout



Enregistrer le fichier Clients.xlsx

Évaluation

Vous êtes en charge, dans une entreprise, du suivi des livraisons. Vous voulez, à l’aide d’un tableur, repérer
les livraisons en retard. Vous disposez à l’URL https://tinyurl.com/ybavo4vw du fichier livraisons.xlsx.

Dans la feuille Délais, vous avez saisi les délais de livraison de vos fournisseurs lors des cinq dernières
livraisons. Vous devez :

1. à la ligne 7, calculer le délai moyen de livraison arrondi au nombre entier le plus proche (et non pas

simplement masquer les décimales) ;

2. sélectionner le tableau en A1:O7 et le copier en A9 en inversant les lignes et les colonnes ;

3. supprimer les lignes 1 à 8 ;

4. formater G1 pour que le texte s’affiche sur deux ou trois lignes.

TAB2_Excel2016Mac.docx

 Y. CINOTTI

6 / 7

Dans la feuille Commandes vous avez saisi les commandes actuellement en cours et la date de commande.
Vous devez :

5. en C2:C15, copier la valeur des cellules G2:G15 de la feuille Délais ;

6. formater les cellules en C2:C15 pour qu’elles soient suivies d’une espace et de « jours »

7. formater les cellules A1:E1 pour que le texte s’affiche éventuellement sur deux ou trois lignes ;

8. en D2:D15, calculer la date de livraison prévue ;

9. dans les colonnes B et D, formater les dates pour qu’elles s’affichent, par exemple en B2, sous la

forme « 17 nov » ;

10. en H1, formater la date pour qu’elle s’affiche sous la forme « vendredi 27 novembre 2020 » ;

11. affecter à la cellule H1 le nom datejour ;

12. dans la colonne E, saisir une formule utilisant le nom datejour pour que s’affiche « retard » si la

date de livraison prévue est inférieure à la date du jour (le 27 novembre) sinon rien.

13. en A32, calculer le nombre de commandes en cours, donc le nombre de fournisseurs dans la colonne

 Envoyez au formateur le fichier livraisons.xlsx ainsi que le fichier Clients.xlsx modifié durant la

A.

formation.

TAB2_Excel2016Mac.docx

 Y. CINOTTI

7 / 7