PRODUCTION D’UNE FEUILLE DE CALCUL(EXCEL)

Plan du cours (cliquez sur un lien pour aller directement sur la partie qui vous intéresse)

I- GENERALITES SUR LES TABLEURS

II- DEMARRER EXCEL 2016

III- MISE EN FORME D’UNE FEUILLE DE CALCUL

IV- FORMULES , FONCTIONS ET GRAPHIQUES

V- IMPRIMER UN DOCUMENT

VI- EXERCICES

I- GENERALITES SUR LES TABLEURS

Identifier les parties de l’interface d’un tableur

Un tableur est un logiciel d’application qui permet de:

· Créer et manipuler des tableaux

· Calculer des données

· Présenter des données sous forme de graphique

Il existe de nombreux logiciels tableur parmi lesquels on peut citer:

· Microsoft Excel de la suite Microsoft Office

· Quattro pro de la suite Corel WordPerfect Office

· Open calc  de la suite Open Office

Dans un logiciel tableur, on utilise généralement des feuilles de calcul. Une feuille de calcul est un tableau électronique formé de plusieurs lignes et de plusieurs colonnes qui contient des éléments qui aideront à présenter les données et à effectuer les calculs de manière efficace.

· Une ligne est une suite horizontale des cases, identifiée par un chiffre. Dans Excel, les lignes sont numérotées de 1 à 1048576.

· Une colonneest une suite verticale de cases, identifiées par une lettre. Dans Excel, les colonnes sont numérotées de A…Z, AA……AZ, BA…ZZ, AAA…XFD

· Une cellule est la case d’une feuille de calcul à l’intersection d’une ligne et d’une colonne. Une cellule est active lorsque sa bordure apparait en gras, la rendant aisément identifiable.

Pour rendre une cellule active, il suffit de placer le pointeur de la souris sur la cellule souhaitée, puis cliquer avec le bouton gauche de la souris.

· La référence d’une cellule indique son emplacement dans la feuille de calcul. Elle est constituée d’une lettre désignant la colonne et d’un chiffre désignant la ligne.

Exemple: A2 (l’intersection de la ligne 2 et la colonne A)

Dans Excel, lorsqu’une cellule est active sa référence s’affiche dans la zone de référence.

Un classeurest un ensemble de feuille de calcul.

II-DEMARRER EXCEL

Pour démarrer Microsoft Excel   sous Windows 10:

· Cliquer sur le bouton Démarrer

· Cliquer sur Toutes les applications

La liste des applications apparait, classée par ordre alphabétique. Faire défiler jusqu’à la lettre E.

· Cliquer sur  Excel, puis sur Nouveau classeur.

Une feuille de calcul vierge apparait à l’écran(Fig.17)

Feuille de calcul d’Excel 2013

Description de la fenêtre d’Excel

Les différents éléments de la fenêtre d’Excel sont

· La barre des titres: Le titre du classeur enregistré s’affiche dans la barre des titres. Elle comporte également:

- A l’extrême gauche les boutons de fermetureT, d’agrandissement + et de réduction

- A l’extrême droit le bouton d’office et les boutons de barre d’outils à accès rapide (sauvegarde annulation tec…)

Le ruban: Le ruban d’Excel 2013 est un bandeau constitué des onglets qui parcourent toute la largeur de la fenêtre. Lorsqu’on clique sur un onglet,

- les boutons  de commande apparaissent. Elles permettent de sélectionner les commandes les plus fréquemment utilisées.

-on voit aussi apparaitre pour cet onglet  des galeries accessibles via une flèche déroulante (lanceur des boîtes de dialogues). La barre des formules: Elle affiche le contenu de la cellule active.

· La zone de référence: Elle affiche la référence de la cellule active (E3)

· La barre d’état: Elle donne des informations concernant la tâche qu’on effectue.

· Les barres de défilement: Si la qualité des données excède la capacité d’affichage de l’écran, on peut faire défiler la feuille dans le sens vertical ou dans le sens horizontal.

· Les onglets: Les feuilles de calcul d’un classeur sont accessibles à partir de certains boutons appelés onglets, situés en bas de page, au-dessus de la barre d’état (exemple: feuill1).

III- MISE EN FORME D’UNE FEUILLE DE CALCUL

Mise en forme des cellules

Pour améliorer la présentation et l’apparence du texte d’un tableau :

· Sélectionnez la ou les cellules souhaitées.

· Sous l’onglet Accueil, Cliquez sur le groupe Police, on obtient le tableau suivant:

Boîte de dialogue de Mise en forme des cellules

Gras, italique et souligné

· Sélectionner la ou les cellules à modifier

· Sous l’onglet Accueil, Cliquez su G pour le gras, I pour l’italique et S pour le souligné

Police et taille de caractère

· Sélectionner la ou les cellules à modifier

· Sous l’onglet Accueil, choisissez une police et une taille

Ajout des bordures

· Sélectionner la ou les cellules à modifier

· Sous l’onglet Accueil, choisissez un type de bordure

OU

Pour sélectionner un style de  bordure,

· Cliquer sur Bordure,

· Choisir Contour pour une bordure extérieure ou Intérieur pour une bordure intérieure.

Ensuite choisir un contour parmi les 8 contours proposés;

· Cliquer sur OK

Modifier les couleurs

· Sélectionner la ou les cellules à colorier

· Sous l’onglet Accueil, choisissez une couleur de remplissage

Fusionner et centrer

· Sélectionner la ou les cellules à fusionner

· Cliquez sur Accueil

· Dans le groupe Alignement, cliquez sur Fusionner

OU

· Cliquer sur  Alignement,

· Cocher Fusion des cellules

Excel avertit que la fusion des cellules va permettre uniquement de conserver la valeur de la cellule supérieure gauche, les autres valeurs seront ignorées.

· Cliquer sur OK

Aligner le texte à gauche, au centre et à droite

· Cliquez sur             pour la gauche

· Cliquez sur             pour le centre

· Cliquez sur             pour la droite

Renvoyer automatiquement à la ligne

· Cliquez sur Accueil

· Cliquez sur Renvoi automatique à la ligne

Aligner le texte en haut, au centre et en bas

· Cliquez sur Accueil

· Dans le groupe Alignement

· Cliquez sur   haut, centre, bas    

Orientation du texte

· Cliquer sur Alignement

· Saisir un angle d’inclinaison

· Cliquer sur OK.

Couleur

· Cliquer sur Remplissage pour choisir la couleur du motif.

Tableau croisé dynamique

Le tableau croise dynamique qui permet de composer rapidement un tableau synthèse provenant d'une masse de données. Comme le nom l’indique, ce tableau permet d'avoir le sommaire d'une ou de plusieurs variables à la fois. De plus, ce tableau est dynamique, cela veut dire qu'on peut ajouter, retirer et modifier la présentation du tableau.

Créer un tableau croisé dynamique

1-Sélectionner les cellules à partir desquelles vous voulez créer un tableau croisé dynamique

2-Sélectionner insertion/tableau croisé dynamique.

3-sélectionner Nouvelle feuille de calcul, puis OK.

4-Pour ajouter un champ à votre tableau croisé dynamique, sélectionner les champs qui vont faire partie du tableau et les glisser vers  le zone cible (valeurs, colonnes, lignes, filtres).

4-Protéger des cellules

· Cliquer sur Protection pour verrouiller ou masquer les cellules

NB. Pour protéger une cellule ou un classeur, allez dans le menu Révision et choisir Protéger la feuille ou le classeur.

5-Trier et filtrer

Pour trier un tableau,

· Sélectionner le tableau

· Dans le menu Données, choisir Trier

· Choisir un critère de tri (par ordre alphabétique, par valeur)

· Valider

Pour appliquer un filtre

· Sélectionner le tableau

· Dans le menu Données, choisir Filtre. De petites flèches apparaissent à droites des cellules titres, cliquer sur la flèche duchamp de critère et cocher un critère.

·  Valider.

6-Mise en forme conditionnelle

 La mise en forme conditionnelle facilite la mise en évidence de certaines valeurs ou l’identification de cellules particulières. Elle modifie l’apparence d’une plage de cellules en fonction d’une condition (ou d’un critère). Vous pouvez utiliser la mise en forme conditionnelle pour mettre en surbrillance des cellules qui contiennent des valeurs correspondantes à une condition.

Appliquer une mise en forme conditionnelle à du texte

1-Sélectionnez la plage de cellules, le tableau ou l’ensemble de la feuille où vous voulez appliquer une mise en forme conditionnelle.

2-Sous l’onglet Accueil, cliquez sur Mise en forme conditionnelle, pointez sur Règles de mise en surbrillance des cellules, puis cliquez sur Texte qui contient.

3-Dans la zone à côté du contenant, tapez le texte que vous voulez mettre en surbrillance, puis cliquez sur OK

IV-FORMULES, FONCTIONS ET GRAPHIQUES

LES FORMULES

Les formules sont des équations qui effectuent des calculs sur les valeurs d’une feuille de calcul. Elles commencent par un signe égal (=).Une formule peut également contenir les éléments suivants : des valeurs, des références, des opérateurs et des parenthèses.

Exemples: = 60+10-8 avec (+ l’addition et – la soustraction)

= (14+A3)*D6 avec (* : multiplication)

= (B2-C4)^2 avec (^ : exposant)

La saisie d’une formule

Pour faire l’addition de 100 et 300, par exemple:

-Saisissez 100 dans la cellule A2

-Saisissez 300 dans la cellule A3

-Saisissez la formule = A2+A3 dans la cellule A4

-Appuyez sur la touche Entrée

Recopie d’une formule

Pour recopier une formule dans les autres cellules d’une ligne:

· Saisir la formule dans la cellule A4 par exemple, puis Entrée

· Sélectionner les cellules B4, C4, C5….

· Dans le groupe Edition, cliquez sur Remplissage à droite

La formule est recopiée

References des cellules (adressage)

           Pour recopier une formule ou une fonction, on utilise les options copier / coller Ou la recopie incrémentée. La recopie incrémentée est utilisées la formule ou la fonction se trouve dans les cellules adjacentes de la même ligne ou de la même colonne.

Ces méthodes sont très utiles et très pratiques mais elles peuvent générer des problèmes. On constate que, pendant que la lettre varie de A à ZZZ en mouvement de translation horizontal, le chiffre varie aussi de 1 à 1048576…. en mouvement de translation vertical.

 Par exemple, Si on copie la formule =A1+A2 de la cellule A3 et on la colle dans la celluleB7, elle devient

- dans la cellule B7 plutôt la formule =B5+B6(Notez que la lettre change ainsi que les chiffres)

-et dans la cellule C3, on trouvera =C1+C2. Dans tous les cas ce n’est plus la formule saisie en A3.

Référence relative

Les références d’une cellule sont dites relatives lorsqu’elles dépendent de la position de la cellule qui contient la formule. Dans le cas de l’exemple précédant, les références (A1, A2) – (B5, B6) - (C1, C2) s’appellent des références relatives respectivement aux cellules A3, B7, C3 (elles changent).

Référence absolue

Une référence absolue est une adresse exacte (fixe) d’une cellule, quelle que soit la position de la cellule qui contient la formule.

Pour rendre une référence absolue, on place un signe $ aussi bien devant la lettre que devant le chiffre: =$A$1. Lors de la recopie, cette adresse sera fixe.

Référence semi-relative

Une référence est semi-relative lorsqu’un seul élément varie (soit la lettre, soit le chiffre). Dans ce cas on fixe l’élément qui ne doit pas varier avec $.

Exemple: A$1 et $A1 sont semi-relatives

Résumé:

Référence relative Exemple: N38	· N varie en cas de translation horizontale de la formule qui contient la référence · 38 varie en cas de translation verticale de la formule qui contient la référence
Référence semi-relative Exemple: $N38	· Seul 38 varie en cas de translation verticale de la formule
Référence semi-relative Exemple: N$38	· Seul N varie en cas de translation horizontale de la formule
Référence absolue Exemple: $N$38	· La référence n’est pas modifiée en cas de recopie.

LES FONCTIONS

Les fonctions sont des formules prédéfinies qui effectuent des calculs en utilisant des valeurs particulières appelées arguments.

Syntaxe d'une fonction

· =Nom de la fonction (arguments1 ; arguments2 ;…)

 Les arguments peuvent être des nombres, du texte, des valeurs logiques, des Références et des fonctions. Excel dispose de nombreuses fonctions réparties en catégories :

Math&trigo, Date &heure, Statistique, Texte, …

Catégories Fonctions

Math&trigo

- Somme (nombre1 ; nombre2 ;…)

- Produit (nombre1 ; nombre2 ;…)

- Pi ()

- Puissance (nombre ; puissance)

- Racine (nombre)

- Cos (nombre)

-Sin (nombre)…

Date &heure

-Aujourd’hui()

-Maintenant() …

Statistique

- Max (nombre1 ; nombre2 ; …)

- Min (nombre1 ; nombre2 ; …)

- MOYENNE (cellule1 ; cellule2 ; …)

-ECARTYPE (cellule 1:cellule2)

-VAR.P(cellule 1: cellule 2)

Texte

- Majuscule (texte)

–Minuscule (texte)…

La saisie d’une fonction

Pour calculer la somme des nombres suivants : 20,10,25, 45

-Saisissez chaque nombre dans une cellule (par exemple dans B2,B5)

-Dans la cellule résultat (B8 par exemple), saisissez la fonction suivante :

= Somme (B2 ;B3 ;B4 ;B5) ou =Somme (B2 : B5) ou = Somme (20 ;10 ;25 ;45)

Remarques:

i) Les deux points (:) dans la fonction signifient que la sélection va de la cellule B2 jusqu'à B5.

ii) Le bouton somme automatique permet de faire la somme des valeurs de la même ligne ou de la même colonne.

iii) Ne laissez aucun espace entre le nom de la fonction et la première parenthèse telle que:

 =Somme (...). Elle ne donnera pas de résultat.

Quelques fonctions prédéfinies avancées

· Fonction RANG ()

Elle permet de trouver une classification.

Syntaxe:

=RANG (nombre; référence;[ordre])

Ordre peut prendre la valeur 0 pour Décroissant et la valeur 1 pour croissant

Exemple:

=RANG (E3; E$3: E$8;0)

· Fonction SI ()

Cette fonction permet d’établir des comparaisons logiques entre une valeur et le résultat attendu. Une instruction SI peut avoir deux résultats. Le premier résultat est appliqué si la comparaison est vérifiée, sinon le deuxième résultat est appliqué.

Syntaxe:

            SI (test_logique; [valeur_si_vrai];[valeur_si_faux])

Exemple:

SI (C2=" oui";1;2) indique SI (C2=» oui», renvoyer 1, sinon renvoyer un 2)

· Fonction NB.SI ()

Cette fonction permet de compter le nombre de cellules qui répondent à un critère; par exemple pour compter le nombre de fois où le nom d’une ville apparait dans une liste de client.

Syntaxe:

=NB.SI (plage; critère)

Exemples :=NB.SI (A2: A5;"Londres"

=NB.SI (A2: A5; A4)

· Fonction SOMME.SI ()

Cette fonction est utilisée pour calculer la somme des valeurs d’une plage qui répond aux critères spécifiés.

Syntaxe:

=SOMME.SI (plage; critère; [somme_plage])

Remarques :

• Dans une même formule, on peut utiliser plusieurs fonctions prédéfinies.

Exemple :

= Somme (moyenne (A1 : A10) ; moyenne (C1 : C10))/NB(B1 : B5)

• Si la fonction SI doit choisir entre plus que deux valeurs, on peut utiliser la même fonction comme argument de la première.

Exemple :

= SI(A2 < 9 ; "Redouble" ; SI (A2 <10 ; " Racheté " ; " Passe "))

5-Somme sur plusieurs feuilles de calcul

La somme peut être réalisée sur plusieurs feuilles de calcul de la façon suivante: soient quatre feuilles de calcul Feuil1, Feuil2, Feuil3, Feuil4. La cellule B2 dans la feuille de calcul Feuil4 va réaliser le calcul des valeurs sommées. Les cellules B2 des trois autres feuilles de calcul seront alimentées en valeurs. Pour simplifier, prenons 5,10 et 15.

1-Erire la formule= SOMME( )     dans la cellule B2 de la feuille de calcul Feuil4.

2-Cliquer avec le pointeur de la souris sur la cellule B2 dans la première feuille de calcul Feuil1.

3-Saisissez ensuite un signe «+»

4-Aller dans la Feuil2 et cliquer sur B2, puis appuyer le signe «+».

5-Répéter le processus avec la troisième feuille de calcul Feuil3.

6-Fermer ensuite la parenthèse.

Remarque: Une variante sera de procéder ainsi qu’il suit: les cellules B2 des trois feuilles de calcul Feuil1, Feuil2 et Feuil3 sont remplies des valeurs 5,10 et 15. Sélectionner directement toutes les feuilles de calcul. Ecrivez, dans la cellule B4 de la feuille de calcul SOMME, la formule =SOMME (Feuil1:Feuil3! B2). La somme de ces feuilles de calcul indépendantes sera effectuée automatiquement.

LES GRAPHIQUES

1-Définition

Le grapheur est un logiciel qui permet de représenter graphiquement des données d'une feuille de calcul. Il est intégré dans le tableur.

Remarque

_ Excel dispose11 modèlesde graphiques :Aires,Barres,Histogrammes,Courbes…

_ L'utilisation d'un type de graphique dépend des données à représenter.

2- Créer un graphique

Microsoft Excel contient de nombreux types de graphique pour vous permettre d'afficher des données de façon lisible et attrayante.

Pour créer un graphique :

·Sélectionnez les cellules contenant les données que vous voulez utiliser dans le graphique.

·Sous l'ongletInsertion,dans le groupeGraphique, cliquez sur untype de graphique, puis sur lesous-type de graphiqueque vous souhaitez utiliser

3- Mise en forme un graphique

Vous pouvez mettre en forme des éléments de graphique individuels, tels quela zone de graphique, la zone de traçage, la série de données, les axes, les titres, les étiquettes de données ou la légende.

Dans un graphique, cliquez sur l'élément que vous voulez mettre en forme ou sélectionnez un élément de graphique dans la liste en procédant de l'une des façons suivantes :

·Cliquez sur le graphique.

·Sous l'ongletMise en forme, dans legroupe Sélection actuelle, cliquez sur la flèche à côté de la zone Éléments de graphique, puis sélectionnez l'élément de graphique que vous voulez mettre en forme.

·Cliquez sur Mise en forme de la sélection, puis sélectionnez les options de mises en forme de votre choix.

Remarque

Les graphiques sont liés dynamiquement aux données ; toute modification des données dans la feuille de calcul entraîne un changement dans le graphique.

V- IMPRIMER UN DOCUMENT

Définir une zone d’impression

Une zone d’impression est une ou plusieurs plages de cellules que vous désignez pour imprimer lorsque vous ne voulez pas imprimer la feuille de calcul entière. Une feuille de calcul peut avoir plusieurs zones d’impression. Chaque zone d’impression s’imprime sur une page distincte.

Pour définir une zone d’impression dans une feuille de calcul:

· Dans la feuille de calcul, sélectionnez les cellules que vous voulez définir comme zone d’impression.

· Sous l’onglet Mise en page

·  cliquez sur Zone d’impression, puis sur Définir.

2- Aperçu avant impression

· Pour visualiser le document tel qu’il apparaîtra à l’impression :

· Ouvrez le document à visualiser

· Cliquez sur Fichier, puis sur Imprimer. La fenêtre suivante s’affiche :

:
Aperçu avant impression

On voit à droite de cette fenêtre, un aperçu de la page.

· Utilisez les boutons de la barre d'outils suivante pour parcourir la page ou effectuer des ajustements avant d'imprimer.

Paramétrer le document

Pour imprimer un document:

· Indiquez le nombre de copies et le nombre des pages à imprimer en utilisant les zones suivantes :

_ La zone Etendue de page :

_ Tout : pour imprimer toutes les pages d’un document.

_ Pages en cours : pour imprimer la page sélectionnée.

_ Pages : pour imprimer quelques pages (pour imprimer par exemple les pages 1, 4, 5, 6, 7,8 et 12 tapez 1 ; 4-8 ; 12)

_ La zone Copies : entrez le nombre de copies à imprimer.

· Cliquez sur OK.

Fenêtre d’impression

Imprimer un document

· Cliquez sur cliquez sur OK

EXERCICES

CONTROLE DE CONNAISSANCES

1-Définir: feuille de calcul, classeur.

2-Citer 02 tableurs.

3-Quels sont les rôles d’un tableur?

4- Comment appelle-t-on l’intersection d’une ligne et d’une colonne d’un tableur?

5- Comment peut-on rendre une cellule activesous Excel?

6- La référence d’une cellule est « B5».Que signifient cette indication?

EXERCICE I: On donne le tableau suivant:

	A	B	C	D	E	F	G	G
1
2		Noms des commerciaux	Quantités vendues	Prix	Montant	Prime (20% du montant)	Rang	Mention
3		ITALEN	150	20050
4		BELKA	220	19090
5		DIEUDONNE	300	27000
6		PATRICK	404	15900
7		TOTAL

Travail à faire

1-Quelle est la formule qu’on doit introduire en E3 pour calculer le montant de ITALEN?

2-Comment obtenir rapidement les montants en E4, E5, E6

3- Quelle est la formule qu’on doit introduire en F3 pour calculer le montant de la prime de ITALEN?

4- Comment obtenir rapidement les montants en F4, F5, F6

5- en G3 on trouve4: que signifie ce résultat?Quelle est la formule utilisée?

6-Quelle formule doit-on Insérer en H3 la formule pour trouver la mention de chaque commercial

EXERCICE II: L’élève Ali marque des points quand il répond aux questions posées dans les différentes matières de sa classe. Les points sont regroupés dans le tableau suivant:

A

B

C

1	MATIERES	POINTS
2	MATHS	5
3	PHYS	2
4	CHIM	3
5	INFO	4
6	HIST	1
7	GEO	3
8	MATHS	2
9	MATHS	3
10	HIST	3
11	MATHS	1
12	GEO	3
13	ECM	4
14	MATHS	2
15	ECM	6
16	MATHS	1

·

18	MATIERES	NOMBRE TOTAL DE POINTS OBTENUS	NOMBRE DE BONNES REPONSES
19	MATHS
20	PHYS
21	CHIM
22	INFO
23	HIST
24	GEO
25	ECM

Questions:

1- Donner en c17 la formule qui permet de calculer les totaux des points d’Ali

2-En introduisant en C19 la formule  =NB.SI (A2: A16; A19), on trouve 6

Quelle est la signification de ce résultat?

3-Quelle est la formule qu’on devra introduire en B19 pour calculer automatiquement le total des points en MATHS?

EXERCICE III:

	A                                    B                  C                  D                  E           F               G
1	Facture  N°
2					Code client	5000
3	TVA	25%
4	Produit	Catégorie	Quantité	Prix unitaire (FCFA)	Total HT	Remise	Montant à payer
5	clavier	A	40	3500
6	Souris	A	52	1500
7	Haut-parleur	B	22	500
8	Webcam	C	9	32000
9	Appareil photo	C	4	70000
10
11			Total facture

·

TRAVAIL A FAIRE

1- Quelle est la formule Excel qu’on devra introduire en E5 pour calculer le prix HT de clavier?

2-Quelle technique  doit-on utiliser pour calculer rapidement  les autres prixen E6, E7, E8 et E9?

3-On donne:

Remise = 10%*total HT Si catégorie «A»

Remise = 7%*total HT Si catégorie «B»

Remise = 5%*total HT Si catégorie «C»

Ecrire la formule Excel qu’on doit utiliser pour calculer les remises selon les catégories des produits..

4- pour calculer le montant à payer:

            Montant à payer=total HT+ (total HT*TVA) - remise

On a introduit. En G5: E5+E5*B3-F5 pour trouver 161000 qui est juste. Mais en faisant la recopie vers le bas on trouve un résultat erroné (VALEUR!)

Corriger cette formule.

5-Quelle  est la procédure  pour représenter sous forme  histogramme le prix unitaire de chaque  produit.

Avez-vous un exercice à proposer au Forum?Cliquez-ici

CORRIGES

Merci de votre visite
Laissez un commentaire