Support de cours Excel 2013
COURS INFORMATIQUE
AOUATEF ROUAHI
ISG |
Table des matières
1. Avant-propos__________________________________________________________________ 3
2. Introduction___________________________________________________________________ 3
3. Ecran Excel___________________________________________________________________ 3
4. Les pointeurs__________________________________________________________________ 3
5. Gestion des classeurs____________________________________________________________ 4
5.1. Créer un classeur_____________________________________________________________ 4
5.2. Ouvrir un classeur existant______________________________________________________ 5
5.3. Enregistrer un classeur_________________________________________________________ 5
5.3.1. Premier enregistrement_______________________________________________________ 5
5.3.2. Enregistrement du travail______________________________________________________ 5
5.4. Imprimer un classeur__________________________________________________________ 5
6. Gestion des feuilles de calcul_____________________________________________________ 6
6.1. Insérer une feuille de calcul_____________________________________________________ 6
6.2. Supprimer une feuille de calcul___________________________________________________ 6
6.3. Renommer une feuille de calcul__________________________________________________ 6
6.4. Déplacer ou copier une feuille de calcul_____________________________________________ 6
7. Manipulation des données________________________________________________________ 7
7.1. Créer une série de données______________________________________________________ 7
7.1.1. Recopier des données________________________________________________________ 7
7.1.2. Recopier des données incrémentées______________________________________________ 7
7.2. Modifier des données__________________________________________________________ 7
7.2.1. Couper___________________________________________________________________ 7
7.2.2. Copier___________________________________________________________________ 7
7.2.3. Reproduire la mise en forme___________________________________________________ 7
7.3. Saisir des données____________________________________________________________ 8
7.3.1. Saisir du texte ou des chiffres___________________________________________________ 8
7.3.2. Formater les données numériques________________________________________________ 8
7.4. Manipuler des cellules_________________________________________________________ 8
7.4.1. Insérer des cellules, des lignes, ou des colonnes______________________________________ 8
7.4.2. Supprimer des cellules, des lignes, ou des colonnes___________________________________ 9
7.4.3. Déplacer des cellules_________________________________________________________ 9
7.4.4. Nommer une plage de données__________________________________________________ 9
7.4.5. Titrer des cellules___________________________________________________________ 9
8. Les graphiques_________________________________________________________________ 9
8.1. Créer un graphique____________________________________________________________ 9
8.2. Modifier un graphique_________________________________________________________ 9
8.2.1. L’onglet Création___________________________________________________________ 10
8.2.2. L’onglet Disposition________________________________________________________ 10
8.2.3. L’onglet Mise en forme______________________________________________________ 11
9. Les formules_________________________________________________________________ 11
9.1. Les opérateurs______________________________________________________________ 11
9.1.1. Opérateurs arithmétiques_____________________________________________________ 11
9.1.2. Opérateurs de comparaison____________________________________________________ 12
9.1.3. Opérateurs logiques_________________________________________________________ 12
9.1.4. Autres opérateurs___________________________________________________________ 12
9.2. Les fonctions_______________________________________________________________ 12
9.3. Les références de cellules______________________________________________________ 13
9.3.1. La référence relative________________________________________________________ 13
9.3.2. La référence absolue________________________________________________________ 13
9.3.3. La référence mixte__________________________________________________________ 14
9.3.4. La référence nominale_______________________________________________________ 14
10. Les bases de données__________________________________________________________ 14
10.1. Contrôles de formulaire_______________________________________________________ 15
10.1.1. Liste déroulante___________________________________________________________ 15
10.1.2. Case à cocher____________________________________________________________ 15
10.1.3. Zone de groupe___________________________________________________________ 16
10.1.4. Case d’option____________________________________________________________ 16
11. Tableau Croisé Dynamique _______________________________________________ 18
12. Sécurité _______________________________________________________________ 18
Ce Document propose un aperçu détaillé sur les fonctions principales de MO Excel (version 2007). Il sert d’un support de cours pour les étudiants non informaticiens de L’ISG Tunis.
Microsoft Office Excel est un logiciel tableur qui permet de faire des calculs numériques automatisés, des illustrations graphiques et d’analyses de données à des fins stratégiques pour l’entreprise.
Un fichier Excel (.xls ou .xlsx) est un classeur comportant plusieurs feuilles de calcul organisées. Chaque feuille de calcules est composée d’un ensemble de cellules arrangés en lignes et en colonnes.
Pointeur | Fonction |
permet de sélectionner une ou plusieurs cellules. | |
Permet de recopier le contenu des cellules. Positionnez-le sur le coin inférieur droit d’une cellule (sur le petit carré) et le pointeur se change en une petite croix noire. Enfoncez le bouton gauche de la souris et glissez vers le bas, le haut, à gauche ou à droite, pour recopier le contenu de la cellule. | |
Lorsque vous vous déplacez hors de la feuille de calculs, dans les menus ou les rubans, le curseur devient une flèche blanche. Vous pouvez sélectionner les options avec cette flèche. | |
Quand vous placez le pointeur entre deux en-têtes de colonne, cliquez sur la fine ligne et glissez vers la droite ou la gauche pour modifier la largeur de la colonne de gauche. Un double clic sur ce pointeur vous permet de modifier la largeur de la colonne automatiquement en fonction de la taille du contenu. | |
Quand vous placez le pointeur entre deux en-têtes de ligne, cliquez sur la fine ligne et glissez vers le haut ou le bas pour modifier la hauteur de la ligne située au-dessus du pointeur. | |
Permet de déplacer le contenu d’une sélection dans une autre cellule de votre choix. Sélectionnez une cellule ou un groupe de cellules et déplacez la souris jusqu’au nouvel emplacement puis relâchez. | |
Quand vous travaillez avec du texte ou bien avec une fonction, ce pointeur vous permet d’insérer du texte à l’aide de votre clavier. Il apparait soit dans une cellule, soit dans la barre de fonction pour en modifier le contenu, dès que vous avez cette possibilité. | |
Ce pointeur apparaît lorsque vous le placez sur l'en-tête d’une colonne. En cliquant à ce moment-là, vous pouvez en faire la sélection complète des cellules de cette colonne. | |
Ce pointeur apparaît lorsque vous le placez sur l'en-tête d’une ligne. En cliquant à ce moment-là, vous pouvez en faire la sélection complète des cellules de cette ligne. | |
Ce pointeur sert à modifier la hauteur d’un objet. | |
Ce pointeur sert à modifier la largeur d’un objet. |
Lorsque on démarre MO Excel, un classeur par défaut est crée mais si on veut créer un nouveau, on utilise la commande Nouveau (Ctrl+N).
• A partir du bouton Office , cliquez sur Nouveau. La boîte de dialogue Nouveau apparaît
• Dans le volet central, vérifiez que Document vierge est sélectionné
• Validez par OK
Pour ouvrir un document enregistré sur votre ordinateur, on utilise la commande Ouvrir (Ctrl+O).
• A partir du bouton Office, cliquez sur Ouvrir
• Dans la boîte de dialogue Ouvrir qui s’affiche, spécifiez l’emplacement de votre fichier Excel en double-cliquant sur les dossiers jusqu'à ce que vous ouvriez le dossier qui contient le fichier
• Cliquez sur le nom du fichier puis sur Ouvrir
Pour enregistrer le classeur nouvellement crée, on utilise la commande Enregistrer sous (F12).
• A partir du bouton Office, cliquez sur Enregistrer sous
• Dans la boîte de dialogue qui s’affiche, spécifiez l’emplacement de votre fichier Excel
• Saisissez un nom pour votre fichier dans le champ Nom de fichier
• Cliquez sur Enregistrer
Il est préférable d’enregistrer au fur et à mesure les modifications apportées au fichier pour ne pas perdre vos données après une fermeture imprévue du logiciel. Pour ce faire, on utilise la commande Enregistrer (Ctrl+S).
? Dans le bouton Office, cliquez sur enregistrer
Après la finalisation du fichier, vous voulez avoir une copie sur papier de votre travail, on utilise la commande Imprimer (Ctrl+P).
• A partir du bouton Office, cliquez sur Imprimer puis Imprimer
• Dans la boîte de dialogue qui s’affiche, spécifiez l’imprimante, les propriétés, nombre de copies ainsi que les données à imprimer
• Pour afficher un aperçu du document avant l’impression, cliquez sur Aperçu
• S’il ne reste aucune rectification, validez par OK
Pour insérer une nouvelle feuille de calcul, soit vous cliquez sur la commande suivez les étapes suivantes :
• Placez le pointeur de la souris sur l’onglet d’une des feuilles de calcul
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Insérer
• Sélectionnez feuille de calcul
• Validez par OK
• Placez le pointeur de la souris sur l’onglet de la feuille de calcul
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Supprimer
• Un message d’alerte s’affiche, cliquez sur OK
une feuille de calcul
• Placez le pointeur de la souris sur l’onglet de la feuille de calcul
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Renommer
• Le texte de l’onglet se met en surbrillance, tapez le nom désiré
une feuille de calcul
• Placez le pointeur de la souris sur l’onglet de la feuille de calcul
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Déplacer ou copier
• Une boîte de dialogue s’affiche
• Choisissez l’endroit où vous désirez insérer votre feuille ? Validez par OK.
• Sélectionnez votre texte, positionnez le pointeur de la souris sur le coin bas à droite de la cellule, un + apparaît.
• Glissez la souris sans relâcher et avancez en colonne ou en ligne.
La recopie incrémentée s’applique aux nombres, texte suivi d’un numéro (a 1, a 2, etc.), jours (Lundi, Mardi, etc.), mois, heures, dates, ainsi qu’aux formules.
Le presse-papiers est une mémoire temporaire qui permet de modifier une portion du document.
? A partir de l’onglet Accueil, cliquez dans le groupe Pressepapiers sur l’un des commandes Couper, Copier, Coller, Reproduire la mise en forme.
• Sélectionnez le texte à couper, cliquez sur le bouton Couper (Ctrl+X)
• Placez le curseur à l’endroit souhaité
• Cliquez sur le bouton Coller (Ctrl+V)
• Sélectionnez le texte à couper, cliquez sur le bouton Copier (Ctrl+C)
• Placez le curseur à l’endroit souhaité
• Cliquez sur le bouton Coller (Ctrl+V)
• Sélectionnez la cellule avec le format que vous voulez reproduire, cliquez (simple clic pour une seule reprise, double clic pour plusieurs reprises) sur le pinceau de reproduction de format
• Sélectionnez les cellules que vous voulez formater puis relâchez la souris Pour supprimer la mise en forme ou le format, on procède comme suit :
• Sélectionnez la cellule avec le format que vous voulez supprimer
• Sous l'onglet Accueil, dans le groupe Édition, cliquez sur la flèche située à côté du bouton Effacer
• Sélectionner l’option Effacer les formats
? Cliquez sur la cellule dans laquelle vous voulez saisir du texte, puis tapez votre texte : il s'affiche à la fois dans la cellule et dans la barre de formule.
Si le contenu saisi est plus large que la taille de la cellule, il faut redimensionner la cellule en double cliquant sur la séparatrice entre la colonne en question et sa voisine de droite.
Le format des nombres permet de choisir le mode d’affichage des valeurs dans une cellule. Le format par défaut est Standard.
• Sélectionnez la cellule ou la plage de cellules à laquelle vous souhaitez appliquer le format désiré
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Format de cellule
• Sélectionnez le format désiré
• Validez par OK
• Sélectionnez la cellule ou la plage de cellules
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Insérer
• Sélectionnez l’option Décaler les cellules vers la droite pour insérer des cellules avec décalage à droite, l’option Décaler les cellules vers le bas pour insérer des cellules avec décalage vers le bas, l’option Ligne entière pour insérer une ligne, l’option Colonne entière pour insérer une colonne
• Validez par OK
• Sélectionnez la cellule ou la plage de cellules
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Supprimer
• Sélectionnez l’option Décaler les cellules vers la droite pour supprimer des cellules avec décalage à droite, l’option Décaler les cellules vers le bas pour supprimer des cellules avec décalage vers le bas, l’option Ligne entière pour supprimer une ligne, l’option Colonne entière pour supprimer une colonne
• Validez par OK
• Sélectionnez la cellule ou la plage de cellules à déplacer
• Placez le pointeur sur le bord de la sélection, de façon à obtenir un pointeur ? Cliquez sans relâcher et déplacez la sélection.
• Sélectionnez les données de la plage à nommer.
• Cliquez dans l’onglet Formules, dans le groupe Noms définis sur l’option définir un nom
• Choisissez le nom de la plage
• Valider par OK
• Insérez les titres des colonnes dans la première ligne
• Sélectionnez vos données
• Cliquez sur l’option Mettre sous forme de tableau dans l’onglet Accueil dans le groupe Style.
• Choisissez le style désiré
Les graphiques sont une représentation plus intuitive que les données numériques surtout pour les non experts dans le domaine en question. Ils offrent une lecture aisée d’une quantité importante de données en soulignant les résultats et les relations issus de ces données.
Pour créer un graphique, il faut d’abord préparer les données à représenter.
• Sélectionnez les cellules contenant les données que vous voulez utiliser pour votre graphique
• Cliquez sur l’onglet Insertion, choisissez Autres graphiques, puis cliquez sur l’option tous types de graphiques
• Choisissez le modèle de votre graphique (Histogrammes, courbes, etc.) ? Validez par OK
Une fois que le graphique soit crée, trois nouveaux onglets apparaissent :
L’onglet Création vous permet de :
• Modifier le type de graphique
• Enregistrer le graphique comme modèle
• Sélectionner les données du graphique
Pour modifier le nom de la série, les entrées de l’axe vertical ou horizontal, cliquez sur Modifier. Vous pouvez aussi les supprimer.
• Déplacer le graphique.
L’onglet Disposition vous permet de :
• Modifier le titre du graphique
• Modifier les titres des axes du graphique
• Modifier la légende (le nom de la série) du graphique
L’onglet Mise en forme vous permet de mettre en forme votre graphique. La mise en forme ne fera pas partie de ce document.
Une formule est une relation qui permet de réaliser un calcul ou une analyse sur un ensemble de données. Toute formule commence par (=) et combine les cellules avec des nombres à travers tous les opérateurs usuels (arithmétiques, de comparaison, logiques, etc.). On peut aussi utiliser des fonctions prédéfinis dans les formules.
Opérateur | Signification (Exemple) |
+ | Addition (A1+B1) |
- | Soustraction (A1-B1) |
* | Multiplication (A1*B1) |
/ | Division (A1/B1) |
% | Pourcentage (20%) |
^ | Puissance (A1^2) |
Opérateur | Signification (Exemple) |
= | Égal à (A1=20) |
> | Supérieur à (A1>20) |
< | Inférieur à (A1<20) |
>= | Supérieur ou égal à (A1>=20) |
<= | Inférieur ou égal à (A1<=20) |
<> | Différent (A1<> 20) |
Opérateur | Signification (Exemple) |
ET | Joint deux conditions ou plus ou toutes les conditions doivent être vraies (ET (formule_1 ; formule_2 ; formule_3)) |
OU | Joint deux conditions ou plus ou au moins une condition soit vraie (ET (formule_1 ; formule_2 ; formule_3)) |
Opérateur | Signification (Exemple) |
& | Concatène deux valeurs pour obtenir une valeur de texte continue ("La valeur" & A1 & "est élevée") |
: | Opérateur de plage qui permet d'obtenir une référence pour toutes les cellules entre deux références, y compris les deux références (A1 : A10 à de A1 jusqu’à A10) |
; | Opérateur d'union qui combine plusieurs références dans une référence (A1 ; B2 à A1 et B2) |
Vous trouvez ici une liste succincte des fonctions les plus utilisées
Fonction | Signification | ||
Date et heure | |||
AUJOURDHUI () | Renvoie la date actualisée du jour. Cette fonction est utilisée pour calculer l’intervalle entre la date du jour et une date donnée. | ||
MAINTENANT () | Renvoie la date et l’heure du jour. | ||
JOUR (date), MOIS (date), ANNEE (date) | Permet d’extraire le jour, le mois ou l’année de la date en paramètre | ||
DATE (année ; mois ; jour) | Calcule une date à partir une d’une autre. | ||
Logique | |||
SI (test ; valeur si vrai ; valeur si faux) | Renvoie selon le résultat (VRAI ou FAUX) du test logique une valeur différente. Vous pouvez imbriquer d’autres fonctions SI comme deuxième ou troisième argument. Vous pouvez ainsi combiner plusieurs tests logiques en utilisant les opérateurs logiques ET / OU. | ||
Mathématique | |||
ALEA () | Renvoie un nombre aléatoire entre 0 et 1. | ||
ARRONDI (nombre ; no_chiffres) | Arrondi un nombre selon le nombre de chiffres donné (ARRONDI (4,1258 ; 2)= 4,13) | ||
SOMME (plage) | Calcule la somme des nombres dans la plage de données en paramètre. | ||
(plage ; critère ; somme_plage) | Calcule la somme d’une plage de données selon un critère. | ||
MOYENNE (plage) | Calcule la moyenne des nombres dans la plage de données en paramètre. | ||
MAX (plage) | Renvoie le maximum des nombres dans la plage de données en paramètre. | ||
MIN (plage) | Renvoie le minimum des nombres dans la plage de données en paramètre. | ||
Recherche | |||
INDEX (plage ; no_ligne ; no_colonne) | Recherche une valeur dans la plage de données en paramètre en fonction d’un numéro de ligne et de colonne |
Une liste exhaustive des fonctions dans l’onglet Formules.
La référence en Excel sert d’un identifiant unique pour la cellule. Il est constitué du nom de la colonne suivi du numéro de la ligne, par exemple A4. Lorsque une formule est utilisée plusieurs fois, on peut utiliser la poignée de recopie (+) mais cette action modifie d’une manière automatique les références insérées dans la formule à recopier. Pour cette raison, il faut être prudent en utilisant cet outil.
En fait, il existe quatre manières pour référencer une cellule.
Une référence relative dépend de la cellule dans laquelle elle est écrite. Elle sera automatiquement modifiée si on déplace ou si on recopie la formule dans une autre cellule. Par exemple, A1.
Une référence absolue ne dépend pas de la cellule dans laquelle elle est écrite. Elle ne sera pas modifiée si on déplace ou si on recopie la formule dans une autre cellule. Par exemple, $A$1. Le $ fixe la colonne et la ligne.
Une référence mixte est obtenue en combinant référence relative et référence absolue. Par exemple, $A1, A$1 où on fixe soit la colonne ou la ligne.
Une référence nominale est une référence absolue, où on utilise des noms au lieu des références colonne_ligne. Par exemple, Salaire, Taux, CA, etc.
Une base de données est un ensemble structuré et cohérent de données enregistrées dans des tables, chaque table représente une entité du monde réel (Client, Etudiant, Patient, etc.). En fait, Toute collection de données sous forme de tableau, sous Excel, est automatiquement considérée comme une base de données si :
• La première ligne contient les noms des champs (nom, prénom, âge, etc.)
• Un seul type de données est considéré par champ (colonne)
• Un seul enregistrement (ensemble des informations lié à une seule entité) par ligne
• Il n’y a pas de lignes ou de colonnes vides dans la base
Excel vous permet de travailler sur des bases de données comme étant une source de données extérieure pour les calculs ainsi que l’analyse.
La manipulation et l’exploitation des bases de données sous Excel peuvent se faire soit d’une manière directe avec laquelle vous contrôlez vos données directement dans la base, soit d’une manière automatisée à travers les formulaires. L’automatisation est très importante au niveau des actions répétitives car elle nous permet de gagner énormément en termes de temps et d’efficacité.
Pour cette raison, ce document n’aborde que la manipulation automatisée des bases de données. Pour ce faire, vous devez activer l’onglet Développeur.
• A partir du bouton Office, cliquez sur Options Excel.
• Dans le volet Standard, cochez « Afficher l’onglet Développeur dans le ruban » ? Validez par OK
Pour insérer une liste déroulante :
• A partir de l’onglet Développeur, dans le groupe Contrôles, Cliquez sur Insérer
• Choisissez la zone de liste déroulante , dans le groupe Contrôles de formulaire ? Cliquez sur l’emplacement où vous voulez insérer votre liste déroulante ? Modifier la taille de la liste en utilisant le pointeur .
Pour modifier les propriétés de la liste :
• Placez le pointeur de la souris sur la liste déroulante
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Format de contrôle
• Une boite de dialogue s’affiche, spécifiez les propriétés
• Valisez par OK
La liste déroulante doit afficher la liste d'éléments spécifiée en plage d’entrée. Lorsque vous cliquez sur un élément de la liste déroulante, la cellule liée spécifiée est mise à jour vers un nombre qui indique la position de l'élément sélectionné dans la liste. La fonction INDEX permet de retrouver les informations liées à cet élément sélectionné en utilisant le résultat affichée dans la cellule liée.
Pour insérer une case à cocher :
• A partir de l’onglet Développeur, dans le groupe Contrôles, Cliquez sur Insérer
• Choisissez la case à cocher , dans le groupe Contrôles de formulaire ? Cliquez sur l’emplacement où vous voulez insérer votre case à cocher
• Modifier la taille de la case en utilisant le pointeur .
• Modifier l’étiquette de la case à cocher en cliquant sur le nom par défaut
Pour modifier les propriétés de la case à cocher :
• Placez le pointeur de la souris sur la case à cocher
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Format de contrôle
• Une boite de dialogue s’affiche, spécifiez les propriétés
• Valisez par OK
Lorsque la case à cocher est activée , la cellule liée renvoie la valeur VRAI, sinon elle renvoie la valeur FAUX. Pour exploiter ce résultat dans la cellule liée, on utilise La fonction SI.
Pour insérer une zone de groupe :
• A partir de l’onglet Développeur, dans le groupe Contrôles, Cliquez sur Insérer
• Choisissez la zone de groupe , dans le groupe Contrôles de formulaire ? Cliquez sur l’emplacement où vous voulez insérer votre zone de groupe
• Modifier la taille de la zone en utilisant le pointeur .
• Modifier l’étiquette de la zone de groupe en cliquant sur le nom par défaut
La zone de groupe permet de regrouper les contrôles liés en une seule unité visuelle dans un rectangle éventuellement assorti d’une étiquette, tels que les cases d’option et les cases à cocher.
Pour insérer une case d’option :
• A partir de l’onglet Développeur, dans le groupe Contrôles, Cliquez sur Insérer
• Choisissez la case d’option , dans le groupe Contrôles de formulaire ? Cliquez sur l’emplacement où vous voulez insérer votre case d’option
• Modifier la taille de la case en utilisant le pointeur .
• Modifier l’étiquette de la case d’option en cliquant sur le nom par défaut Pour modifier les propriétés de la case d’option :
• Placez le pointeur de la souris sur la case d’option
• Appuyer sur le bouton droit de la souris
• Sélectionnez l’option Format de contrôle
• Une boite de dialogue s’affiche, spécifiez les propriétés
• Valisez par OK
Généralement, les cases d’option sont utilisées en groupe. Lorsque la case d’option est activée , la cellule liée renvoie le rang de cette case d’option dans le groupe. Pour exploiter ce résultat dans la cellule liée, on utilise La fonction SI.