Cours Microsoft Excel avancé : les tableaux et calcules

E X C E L

NIVEAU II

Mireille Ducellier – Novembre 2002

MISE EN FORME DU TABLEAU

Ouvrez le tableau "Dépense" feuille1 qui se trouve dans le répertoire Stage. Centrer les titres sur l'ensemble des cellules. 

Sélectionnez les cellules de A1 à D1: FORMAT / CELLULE / onglet ALIGNEMENT  Dans la fenêtre Horizontal, choisissez: centrer sur plusieurs colonnes. Si on coche :

-      Renvoyer à la ligne automatique, en fonction de la largeur de la cellule, le texte sera automatiquement scindé et passera à la ligne en dessous

-      Ajuster en fonction de la largeur de la cellule, la police des caractères sera automatiquement ajustée

-      Fusionner les cellules: les cellules sélectionnées ne formeront plus qu'une seule et même cellule.

Si vous voulez personnaliser votre tableau , sélectionnez le tableau : FORMAT , CELLULE , cliquez sur l' OngletBORDURE

Pour mettre les titres en couleur, sélectionnez la ligne , : FORMAT , CELLULE , cliquez sur l' Onglet MOTIFS. 

Modifier l'encadrement du tableau et mettez une couleur dans les lignes grises.

MODIFICATION D'UN TABLEAU

Insertion de cellules

Ouvrez un nouveau classeur , sélectionnez les lignes et les colonnes de B5 à F14 , encadrez le tableau. 

Positionnez-vous sur la cellule B9, faire INSERTION , CELLULE.

Une boîte de dialogue s'affiche vous demandant : "Décaler les cellules vers le bas", cliquez OK, faîtes la même manipulation en cochant : 'Décaler les cellules vers la droite ",

Insertion de lignes et de colonnes

Il existe 2 manières différentes 

Faire à nouveau INSERTION , CELLULE sur la cellule B9. Dans la boîte de dialogue, cochez sur LIGNE ENTIERE, faîtes la même manipulation en cochant COLONNE ENTIERE 

 Sélectionnez le ligne 10, faire INSERTION LIGNE. La ligne se met au dessus de la ligne 10

 Sélectionnez la colonne C ; faire INSERTION, COLONNE. La colonne s'insère à gauche de la colonne C.

Suppression de lignes et de colonnes.

Pour supprimer une ligne ou une colonne qui ne sont pas utiles., cliquez dans la zone grisée de ligne ou de colonne pour la sélectionner intégralement et faire EDITION, SUPPRIMER, OK.

Maintenant, positionnez-vous sur la cellule B12. Faire EDITION, SUPPRIMER. Dans la boîte de dialogue qui s'affiche choisissez DECALER VERS LE BAS, OK.  Faîtes la même opération  en cochant  LIGNE ENTIERE

En résumé : 

Première méthode :pour supprimer ou insérer ligne ou colonne, sélectionnez la ligne ou la colonne en entier, puis EDITION,SUPPRIMER ou INSERTION,ligne ou colonne 

Deuxième méthode : positionnez-vous sur la cellule et dans la boîte de dialogue qui s'ouvre après le choix, INSERERCELLULE ou SUPPRIMER, cochez LIGNE ENTIERE ou COLONNE ENTIERE.

Effacer des données

Faire EDITION, EFFACER. Dans le sous-menu si vous choisissez : 

-  tout : vous effacerez le contenu de la cellule ainsi que l'encadrement s'il existe,

-  format : vous effacerez uniquement l'encadrement,

-  formule : vous effacerez le contenu de la cellule (équivalent à la touche SUPPR du clavier) 

-  commentaire : vous effacerez les commentaires.

Un commentaire est une information supplémentaire associée à une cellule. 

Pour l'activer, cliquez sur la cellule – bouton de droite, choisissez insérer un commentaire , et tapez le texte.

Si dans le menu Outils, options, Affichage vous cochez :

Indicateur seul : le commentaire s'affiche uniquement quand vous pointez la souris sur la cellule. 

Commentaire et indicateur : le commentaire s'affiche en permanence.

C'est dans le menu Fichier, Mise en page et onglet feuille que vous avez la possibilité d'imprimer le commentaire.

DEPLACEMENT D'UNE LIGNE OU D'UNE COLONNE

Ouvrez le tableau PORTEFEUILLE

Vous voulez déplacer la colonne "NBRE DE TITRES" et la placer entre la colonne "NOMS DES TITRES" et "DATE D'ACHAT".

Sélectionnez la colonne " NBRE DE TITRES " puis faire EDITION, COUPER ou cliquez sur l 'icône de raccourci.

 Le contour de la colonne sélectionnée se met en pointillés ; sélectionnez la colonne "DATE D'ACHAT" et faire INSERTIONcellulescoupées. Les colonnes se déplacent vers la droite.

Si vous aviez fait uniquement COLLER la colonne " NBRE DE TITRES " se serait mise à la place de la colonne " DATE D'ACHAT ".

L'action est la même pour la ligne ou une cellule.

- COUPER-COLLER : déplace les données d'un endroit à un autre.

Si vous voulez copier votre tableau ou certaines parties (ligne, colonne, cellule) il faut faire

COPIER-COLLER.

ATTENTION : pour une copie les plages doivent avoir la même taille 

En résumé :

COUPER-COLLER : transfert de données d'un endroit dans un autre, COPIER-COLLER : recopie  de données d'un endroit dans un autre.

Vous pouvez également faire copier-coller sur des plages non contiguës, pour cela, sélectionnez la  1ere plage + touche CTRL enfoncée, sélectionnez l'autre plage de cellules.

FORMATAGE DES DONNEES

Dans une cellule Excel, les chiffres sont cadrés à droite et les caractères à gauche. La saisie du prix se fait avec une virgule.

Vous voulez deux chiffres après la virgule pour le prix. Sélectionnez la colonne, dans le menu FORMAT cliquez sur CELLULE, Onglet NOMBRE, validez nombre dans la fenêtre de gauche et tapez '2' dans la fenêtre nombre de décimales à droite . puis OK.

Si vous voulez l'unité monétaire et la séparation avec les milliers. Sélectionnez la colonne salaire, FORMAT, CELLULE, ongletNOMBRE, dans la boîte de dialogue à gauche, sélectionnez "monétaire", ou plus simplement cliquez sur l'icône de raccourci

Vous voulez également le mois en lettre dans la colonne "DATE D'ACHAT"

Sélectionnez la colonne FORMAT, CELLULE, NOMBRE : sélectionnez "date" et le format '4 septembre 97' ,validez.

NB: Si aucun des formats ne convient, vous pouvez le personnaliser , il suffit pour cela de cliquer dans la zone personnalisé et de taper le format désiré.

Certains formats sont disponibles directement dans la barre des icônes de raccourci:

2 décimales et séparateur

ajouter une décimale

supprimer une décimale

EXPLOITATION DES DONNEES

Le but d'Excel n'est la saisie banale d'un tableau ; c'est plutôt l'exploitation des chiffres pour fournir des statistiques, des graphiques.

1) La gestion des calculs

Revenez au tableau ' PORTEFEUILLE'. Vous allez calculer le coût global, ( c est à dire que vous allez multiplier le prix d'achat par le nombre de titres) de la manière suivante :

-  activez la cellule E7

-  tapez =  pour indiquer à EXCEL que vous entrez une formule de calcul,

-  avec la souris positionnez-vous sur la cellule D7 dans la barre de formule s'affichent les références de la cellule, - tapez * (pour multiplier),

-  sélectionnez la cellule C7,

-  validez  

Recommencer avec la cellule E8. Pour éviter de refaire la manipulation dans chaque cellule, il existe une méthode plus simple. sélectionnez de E7 à E13 et faites RECOPIER VERS LE BAS (ou raccourci clavier  Ctrl B)

En C14 cliquez sur l'icône somme automatique , EXCEL vous propose la zone de cellules à additionner : D7 à D13. La syntaxe est la suivante : =SOMME(D7:D13). La plage de cellules à additionner s'arrêtera à la première ligne ou colonne qui ne contient pas de chiffres, validez.

Si vous voulez additionner des plages de cellules non contiguës , la syntaxe est la suivante : =SOMME(D1:D10;D25:D31) : le point virgule est le séparateur des plages à additionner..

Pour entrer un calcul dans une cellule, toujours commencer par = pour indiquer que vous entrer une formule mathématique et utiliser les symboles d'opération suivants 

                        + somme,                 - soustraction,                         * multiplication,

                        / division.

L'ordre des opérations est le même qu'en mathématique : en premier la multiplication, la division puis l'addition et la soustraction sauf s'il y a des parenthèses dans ce cas , c est le contenu des parenthèses qui prime.

 Exemple: pour calculer le pourcentage de variation, sélectionnez la cellule H5 et tapez  =(D7-F7)/100, validez et recopiez vers le bas.

NB :  N'entrez jamais d'espace dans une formule Si vous voyez s'afficher : 

####  votre cellule est trop petite ; agrandissez-la.

#DIV/0! vous voulez diviser par 0.

  #VALEUR! une cellule contient une valeur non valide (souvent non numérique)

2) Option COLLER UNE FONCTION

Il existe dans EXCEL une bibliothèque de fonctions. Une fonction est un ordre mathématique prédéfini qui effectue des calculs en utilisant des valeurs particulières appelées arguments, dans un certain ordre appelé syntaxe. La somme que vous venez d'utiliser est une fonction.

Cliquez dans INSERTION, FONCTION. La boîte de dialogue vous proposant toutes les fonctions va s'afficher 

Dans le tableau 'PORTEFEUILLE', vous devez déterminer le prix d'achat le plus élevé, le moins élevé  et le prix moyen qui a été fait ; vous allez faire ce travail grâce aux fonctions.

Sous votre tableau, créez 3 lignes : prix d'achat maximum (A17), prix d'achat minimum (A18), prix d'achat moyen (A19). 

Activez la cellule B17 puis INSERTION, FONCTION ; à l'aide de l'ascenseur dans la fenêtre de gauche cherchez statistique et dans la fenêtre de droite sélectionnez MAX :  Une boite de dialogue spécifique aux fonctions s'affiche:

Dans la zone Nombre1 : inscrivez la plage de cellules et OK. Excel va afficher la valeur maxi de la plage de cellules. 

ATTENTION : si vous rentrez une référence dans la zone Nombre1 et une autre dans la zone Nombre2, Excel affichera la valeur maxi des 2 nombres.

Application :

Faire de même pour la valeur minimum et la valeur moyenne.

L'utilisation de ces formules et fonctions sont plus pratiques que la simple saisie des valeurs déjà calculées car si vous modifiez une valeur tout se recalcule automatiquement.

3) Référence relative - Référence absolue.

Faites le tableau suivant : le début du tableau en B7, nommez le 'AVANCE'

DATE	NOM	SALAIRE	AVANCE	RESTE A PAYERÂ
18/02/99	BASAR	7 687,40	1 000,00
15/01/99	DEJOUX	8 548,75	1 500,00
31/01/99	LEPAGE	10 654,00	2 500,00
25/02/99	PERRIN	6 354,00	1 000,00
28/02/99	TURI	9 487,60	2 500,00

Pour calculer le reste à payer, il faut faire la différence entre le salaire et l'avance. 

Dans la cellule F8, on a fait =D8-E8 et si on recopie la formule dans la cellule F9  on a =D9-E9, EXCEL a modifié de lui-même car les cellules F8 et F9 sont des références relatives.

La position relative d'une cellule se définie par rapport aux cellules voisines. Recopiez vers le bas.

Dans votre tableau AVANCE, rajoutez un titre en A2 et une colonne pour obtenir le tableau suivant : 

AUGMENTATION AU 01/04/2000                                                                                               2,50%

DATE	NOM	SALAIRE	AVANCE	RESTE A PAYERÂ	AUGMENTATIONÂ
18/02/99	BASAR	7 687,40	1 000,00	6 687,40
15/01/99	DEJOUX	8 548,75	1 500,00	7 048,75
31/01/99	LEPAGE	10 654,00	2 500,00	8 154,00
25/02/99	PERRIN	6 354,00	1 000,00	5 354,00
28/02/99	TURI	9 487,60	2 500,00	6 987,60

Dans la cellule G8, tapez la formule qui multiplie le salaire par le pourcentage d'augmentation : =D8 * G2 cellule où se trouve le pourcentage.

Recopiez la formule dans la cellule G9. Que remarquez-vous ?

Regardez ce qui s'affiche dans la barre de formule de la cellule G9. EXCEL a considéré que la cellule G2 était une référence relative ; mais en réalité c'est une référence absolue, on doit toujours faire référence à G2 pour le calcul de l'augmentation.

Pour dire à EXCEL que G2 est une référence absolue dans la barre de formule après G2, appuyer sur F4. Validez, recopier en G9, cliquez dans la cellule G9, vous remarquerez qu'EXCEL multiplie la cellule G8 (référence relative) avec G2 (référence absolue). Complétez le tableau et enregistrez votre document.

En résumé :

Une référence relative d'une cellule se définie par rapport aux autres cellules.

Une référence absolue d'une cellule se définie par sa position dans la feuille de travail. Si vous modifiez le contenu d'une cellule référence absolue , automatiquement tous les calculs qui intègrent cette cellule seront modifiés : passez le pourcentage à 3%.

4) Le tri simple de données

EXCEL vous permet de trier très facilement un tableau 

Ouvrir un nouveau document, tapez le tableau suivant en commençant le tableau en C3

LISTE DES EMPLOYES

NOM	PRENOM	NE(E) LE	FONCTION	SALAIRE
DALMASSO	Hervé	21/10/75	Ag. Technique	5400,45
HERBERT	Sophie	17/01/70	Secrétaire	6680,00
BRIFOUX	Paul	07/04/50	Ouvrier	4500,50
SORIANO	Daniel	26/06/74	Comptable	20875,75
MIET	Charlotte	31/12/73	Chef de Service	12474,00
BOLAN	Zoé	12/12/56	Secrétaire	7242,50
HERBERT	Christophe	12/05/60	Chef d'équipe	8547,40
APPLECOURT	Anne	27/03/68	Employée	6012,85

Faire l'encadrement.

Mettre les salaires avec 2 chiffres après la virgule.

Enregistrez sous le nom EMPLOYES

Deux boutons permettent de trier  soit en ordre croissant         

soit en ordre décroissant  

 Pour trier un tableau par ordre alphabétique de nom, il suffit de sélectionner une cellule de la colonne sur laquelle vous allez trier et cliquer sur l'icône tri croissant, automatiquement, le tableau sera trié, même manipulation pour le tri décroissant.  

Si vous voulez trier sur plusieurs colonnes, en cas d'homonymie, il faut faire: menu DONNEE cliquez sur TRIER., dans la boite de dialogue qui s'affiche choisir les clés de tri.

Assurez vous que le bouton 'Oui' dans la partie ligne de titre est coché.

            validez la colonne nom dans la 1ere fenêtre ( automatiquement proposé)  validez le prénom dans la 2^ème fenêtre      vérifiez que le bouton croissant est bien coché.

Maintenant  triez le tableau par salaire décroissant.

5) Création d'une suite de nombres

Vous voulez numéroter chaque ligne du tableau EMPLOYES: 

            en B3 :tapez  N°       en B4 tapez 1     en B5 tapez 2

 Amenez le pointeur de la souris sur le coin inférieur droit de la cellule B5 où il y a un petit carré noir quand le pointeur se transforme en croix noire appelée poignée de recopie, faîtes glisser jusqu'à la dernière cellule (de haut en bas ou de gauche à droite) , il y a numérotation automatique les lignes .C'est également valable pour les jours de la semaine ou les mois de l'année.

Dans le menu : Outils, Options, Onglet, listes pers., vous pouvez créer vos listes.

LE TEXTE DANS LA FEUILLE DE CALCUL

La méthode la plus simple pour ajouter du texte dans une feuille de calcul consiste à taper le texte directement dans une cellule. 

Lorsque vous souhaitez ajouter du texte ne se trouvant pas dans une zone particulière, utilisez l'icône de dessin.

Une barre d'icônes spécifique pour les dessins s'affiche :

Vous pourrez ajouter du texte à la plupart des formes automatiques, à l'exception des traits, des connecteurs. Le texte ainsi ajouté fait partie intégrante de la forme. 

Ouvrez votre document AVANCES et entrez le texte suivant : "Etat des salaires et avances du personnel" avec une forme au choix.

Quand vous validez, si la fenêtre reste blanche, c'est qu'elle est trop petite. Il faut la redimensionner. 

 Ces 3 icônes permettent de mettre de la couleur pour le fond, le contour et le texte.

Pour agrémenter le cadre, d'autres icônes sont disponibles si vous avez sélectionner le cadre 

Si vous demander l'insertion d'une zone de texte par l'icône       

et si vous double cliquez sur le cadre une fois qu il est sélectionné, la boite suivante apparaît: 

C'est un complément à la barre de dessins.

Pour insérer un texte un peu original, cliquer sur l'icône          ou faire INSERTION, IMAGE, WORD ART.

Dans la boîte de dialogue qui s’ouvre, choisir le motif voulu. Tapez votre texte et fermer la fenêtre 

Une autre barre est affichée.

MISE EN FORME POUR L'IMPRESSION

Pour modifier, il faut faire "mise en page" du menu FICHIER.

Onglet Page:

-  l' orientation du papier :  portrait (à la française),

                                                           paysage (à l'italienne)

-  la taille de la feuille de papier : rarement modifiée si vous utilisez le format A4, - Réduire ou agrandir le tableau en donnant le %. Onglet Marges:

 la marge haut, bas, droite et gauche et permet de centrer le tableau dans la page. Les marges peuvent aussi se modifier directement dans l'aperçu en cliquant sur le bouton marge dans la barre de menu de l'aperçu.

Onglet en-tête et pieds de page            le texte qui apparaîtra sur chaque page en haut et en bas . ce texte peut être prédéfini ou personnalisé auquel cas vous pourrez également déterminer l'emplacement et le contenu dans les différentes sections comme suit  

Onglet feuille

  les paramètres qui s'appliqueront à la feuille alors que les onglets précédents s'appliquent au classeur. 

-  Zone d'impression : sélection d'une plage de cellules à imprimer

-  Titres à imprimer : sélection d'une plage de titre qui se répétera sur chaque page 

-  Noir et blanc : impression en nuance de gris même si imprimante couleur - Qualité brouillon :efface les couleurs du graphique 

Si vous voulez faire la même mise en forme pour toutes les feuilles , il faut faire un groupe de travail : cliquer sur l'onglet de la 1ere feuille puis en maintenant la touche MAJ , cliquez sur les autres onglets. Quand le groupe est défini, toute modification dans une feuille se répercute dans les autres.

Pour désactiver un groupe de travail, cliquez sur n'importe quel onglet.

La dernière chose à faire avant l'impression si le document vous convient à l'aperçu, c'est la correction d'orthographe , pour cela faire : Outils, orthographe ou cliquez sur 

Maintenant, vous allez imprimer votre document. FICHIER, IMPRIMERREPRESENTATION GRAPHIQUE

Ouvrez un nouveau document et tapez le tableau suivant.

Prévisions 95	Chiffres réels 95
Salaire                       Â	   950 000 FÂ	   950 000
Stock                         Â	   445 000 FÂ	   470 000
Frais généraux         Â	   657 000 FÂ	   545 000
Total charges           Â	1 900 000 FÂ	1 800 000
Chiffre d'affaires      Â	4 250 000 FÂ	3 900 000
Bénéfice Exploitation	2 350 000 FÂ	2 100 000

Enregistrez le tableau sous le nom : PREVISIONS 

Sélectionnez le tableau en entier, cliquez sur l' assistant graphique;  Cliquez sur SUIVANT dans chaque étape pour avancer à l'étape suivante ou sur le bouton PRECEDENT pour revenir en arrière.

            Etape 1 : choisir le type de graphe : histogramme 

            Etape 2 : concerne les données sources : en ligne  ,    Etape 3 : options du graphe : titre général, PREVISIONS…..

            Etape 4 : emplacement du graphe soit sur la même feuille 

Vous pouvez déplacer le graphe en cliquant dessus pour obtenir les poignées de redimension et de le changer de place avec la souris .

Si vous demandez sur une nouvelle feuille, le graphique sera sur une feuille appelée

Graph1 avec un menu graphique dans la barre de menu pour modifications du graphe.

Pour modifier un titre , cliquez une fois dessus pour changer le libellé, si vous cliquez 2 fois vous changez les attributs ( police, taille des caractères…)

Pour modifier n'importe quelle étape du graphe, cliquez une fois sur le graphe pour le sélectionnez et cliquez une deuxième fois avec le bouton de droite

Entrez le tableau ci-dessous ; calculez le nombre d'articles non livrés et le total à facturer ; encadrez le tableau.

DESIGNATION	COMMANDES	LIVRAISONS	RESTE	P.U. TOTAL
A LIVRER	            A FACTURER
Stylos bleu	150	125	1.25Â
Stylos noir	200	200	1.25Â
Stylos rouge	100	50	1.25Â
Stylos vert	120	75	1.25Â
Stylos encre	250	245	1.25Â
Crayons HB	300	200	0.75Â
Gommes	300	200	1.10Â
Compas	150	40	4.75Â

Sélectionnez les colonnes : désignation, commandes et livraisons et représentez les graphiquement en histogramme et en cochant à la dernière étape " sur une autre feuille". 

Dans le menu Graphique, cliquez sur type de graphique et explorer les. Pour visualiser le résultat cliquez sur le bouton "Maintenir appuyer pour visionner".

Les modifications se feront au fur et à mesure de votre choix.

Choisissez Histogramme en 3D

Voici ce que vous obtenez :

Modifications d'un graphe : 

Quand vous amenez le curseur de la souris sur une zone de couleur, en légende vous avez sa signification , vous lirez la ligne du tableau correspondant. 

Pour ajouter un titre, cliquez avec le bouton droit de la souris en haut du graphe et validez option du graphique. Choisissez l'onglet Titre et tapez le texte dans le zone 'Titre du graphique'. 

Pour changer la couleur , cliquez avec le bouton droit de la souris sur chaque bâtonnet et choisissez "format de la série de données" et modifier la couleur 

Pour changer la police de caractères des légendes d'axe, cliquez avec le bouton droit de la souris, 'Format de l'axe' et onglet Police.

Pour revenir dans le tableau initial et le modifier, cliquez sur l'onglet feuil1, modifier les nombres , le graphe se modifie instantanément.

Pour ajouter une colonne de nombres au graphe, dans le menu graphique, validez Ajouter des données. La boite de dialogue qui s'affiche vous permettra de sélectionner la plage de cellules dans le tableau, validez par OK.

Tout ce qu'on vient de voir s'applique quelque soit le type de graphe choisi.

Rotation d'un graphe

Vous pouvez modifier la position d'un graphique et le présenter sous un angle différent uniquement s'il est en 3 dimensions.

Sur le graphe précédent, cliquer avec le bouton de gauche pour faire apparaître les poignées de rotation notées 'coins' sur le graphe. Amenez le curseur de la souris sur n'importe quelle poignée et sans relevez le doigt faites pivoter le dessin.

Si vous cliquer sur le graphe avec le bouton droit de la souris, dans le menu choisissez  Vue 3D.

Création d'un graphique à secteurs :

Pour réaliser un graphe à secteur, il ne faut sélectionner qu'une série de données. Dans le tableau , sélectionnez les colonnes désignation et quantité.

Avec l'assistant graphique : choisissez secteurs à l'étape1, vérifiez que vous avez colonnes de cocher à l'étape 2, à l'étape 3 cliquez sur l'onglet Légende et cocher afficher la légende, à l'onglet étiquettes de données, cochez afficher étiquette et pourcentage et à l'étape 4 : sélectionnez nouvelle feuille. 

Avec le bouton gauche de la souris, cliquez une fois sur la "portion" encre pour que les poignées l'entourent , déplacez la d'un centimètre : détachement d'un secteur.

Bouton droit de la souris sur la légende, choisissez format de légende pour modification de la police , de la couleur et de l'emplacement sur le graphe.

Toujours avec le bouton de droite cliquer sur le graphe et dans type de secteur , validez le 2^ème dessin du sous type de graphique. Vous passez alors en 3D et vous pouvez effectuer toutes les opérations de rotation en faisant  menu graphique vue 3D. 

PROTECTION DES CELLULES

La protection des feuilles peut se faire de plusieurs façons: 

-  limiter les modifications des cellules : d'autres personnes peuvent utiliser votre feuille mais pas modifier certaines cellules importantes. Le verrouillage des cellules se fait en 2 étapes : 

                        1) Sélectionnez les cellules que vous ne voulez pas protéger pour les déverrouiller et dans format, cellule, protection , enlever la coche 'verrouillée',                 2) Protéger la feuille, outils, protection, protéger la feuille. 

Quand vous voudrez modifier une cellule protégée, un message d'alerte va s'afficher. Pour ôter la protection , il suffit de faire outils, protection, ôter la protection de la feuille (sauf si vous avez rentré un mot de passe, il vous sera alors demandé)

-  protéger le classeur par un mot de passe

Dans le menu Fichier, enregistrer sous, options

Seules les personnes ayant ce mot de passe pourront ouvrir le classeur

Le classeur sera protégé contre l'enregistrement, les autres utilisateurs peuvent l'ouvrir le modifier mais sont obligés de l'enregistrer sous un autre nom.

ATTENTION : si vous oubliez le mot de passe , vous ne pourrez plus exploiter le classeur. Quand vous tapez le mot de passe, les majuscules et les minuscules n'ont pas la même valeur. 

MODELES

Un modèle est un document réutilisable. C’est une sorte de canevas que l’on créé une seule fois pour pouvoir l'utiliser autant de fois que l'on veut.

Quand vous ouvrez un nouveau document , vous utilisez un modèle : un classeur vide.  Vous allez créer votre propre modèle : ouvrez un nouveau classeur, sélectionnez les cellules B4 à D7, encadrez, mettez une couleur de fond, et 'enregistrez sous'.

Dans la fenêtre 'Type de fichiers' validez 'modèles' , automatiquement Excel enregistre votre tableau dans un répertoire réservé

Pour utilisez votre modèle : fichier , nouveau

BASES DE DONNEES

Une base de données est une plage de cellules contiguës située sur une seule feuille.

Elle commence toujours en A1.

On parle alors de champs, d'étiquettes de champs et d'enregistrements.

Les étiquettes sont saisies dans la 1ere ligne de la base et doivent avoir des noms différents (maxi 255 caractères), le champs est contenu dans une cellule et un enregistrement est une ligne d'informations.

A l'aide de filtres, de critères, vous allez pouvoir extraire des éléments spécifiques et reporter le résultat sur une nouvelle plage pour l'exploiter. Ouvrez la base de données 'Agence'. 

Etiquettes des champs 

Utilisation de la base:

Menu données, grille , la boite de dialogue suivante s'ouvre 

Si vous cliquez sur "nouvelle", vous pourrez ajouter des éléments à la base. Les enregistrements crées se mettent à la fin.

Les boutons 'Précédente' et Suivante' permettent de passer d'un enregistrement à l'autre, 'Supprimer' efface définitivement l'enregistrement affiché. 

Pour localiser rapidement un enregistrement, il faut utiliser le bouton 'critère'.

Vous allez alors saisir les critères particuliers qui permettront de localiser les enregistrements. Vous pourrez utiliser le caractère  *.

Quand vous effacer les critères avec le bouton supprimer, le bouton rétablir devient actif. Trier une base de données : 

Le tri est fondamental dans les bases de données. Vous pouvez trier votre base par ordre croissant ou décroissant sur n'importe quel champ, il suffit de cliquer n'importe où dans la colonne prix par exemple, et d'activer une des icônes de tri  

Si vous voulez effectuer un tri sur plusieurs critères en même temps, il faut utiliser le menu Données, Trier 

La base sera triée en premier sur le prix de manière décroissante et en suite par type. 

Vous risquez d'avoir des problèmes pour gérer votre base de données, à force d'ajouter des enregistrements. Excel peut vous aider en vous donnant la possibilité de voir des enregistrements en fonction de critères spécifiques grâce aux commandes de filtres.

1) UTILISATION DU FILTRE AUTOMATIQUE

Le filtre automatique va vous permettre d'afficher des enregistrements sélectionnés à partir d'un nom d'un champ particulier.

Dans la base de données AGENCE activez le menu Données, Filtres, Filtre automatique. La ligne de nom de champ s'affiche comme suit : 

Si vous cliquez sur une flèche, par exemple après étage  , une liste s'ouvre 

Si vous validez "10" , s'afficheront les adresses se trouvant au 10eme étage.

Faites des essais avec le type, la surface.

Si vous demandez  '10 premiers' , voici ce qui s'affiche :

Les 10 éléments à partir de la valeur la plus grande vont s'afficher, si vous choisissez bas, ce sera à partir de la valeur la plus petite.

Dans l'exemple précédent, vous avez utiliser des critères faisant intervenir des correspondance exacte,  si vous désirez sélectionner des enregistrements compris entre 2 valeurs, il faut utiliser des critères composés : pour cela il faut prendre l'option 

Le résultat du filtre sera la liste des adresse dont le prix est compris entre 500 000F et  1 000 000F.

Si vous voulez afficher tous les enregistrements, cliquez à nouveau sur le filtre 'Prix' et demandez  'Tous'

Pour supprimer le filtre automatique, décochez le choix données, filtre, filtre automatique. 2) EXTRAIRE DES ENREGISTREMENTS

Pour utiliser des critères de filtre plus complexes, et copier le résultat dans une autre partie de la feuille, il faut utiliser le filtre élaboré.

1ere étape :              sélectionnez la ligne de titre (de A1 à G1) et la copier dans une autre partie de la feuille (J1 à P1)

2eme étape :            tapez le critère F1 dans le type (cellule K2)  pour extraire tous les appartements de type  F1

3eme étape :            Faites Ctrl+origine pour revenir en A1, et ensuite données, filtre, filtre élaboré. L'ensemble de la base se sélectionne et le boite de dialogue suivante apparaît : 

Dans la zone de critères , sélectionnez les cellules K1-K2.

Cochez Copier vers un autre emplacement , la zone destination devient alors accessible, dans cette zone sélectionnez un emplacement de la feuille J19 , si vous ne souhaitez pas voir les enregistrements identiques, cochez extraction sans doublon.

Faire la même manipulation pour les appartements situés au 3eme étage, puis cherchez les appartements de type F2 dont le prix est inférieur à 500 000F.

LES FONCTIONS DANS LA BASE DE DONNEES:

Comme dans un tableau, les fonctions ordinaires peuvent s'utiliser dans une base de données si vous n'utilisez pas de filtres. Il vaut mieux utiliser des fonctions spécifiques elles commencent par le préfixe BD.

Dans la base AGENCE , tapez en A21 Prix maximum, en A22 Prix minimum. En B21, faîtes : insertion, fonction, dans bases de données, BDMAX 

Dans la boite de dialogue qui s'affiche, vous allez rentrer les différents paramètres.

Dans la zone base de données on trouvera : A1:G18 l'ensemble des cellules de la base

dans la zone champs : F1        on cherche le prix maximum dans la zone critères:      K1:K2

Cherchez le prix minimum pour des appartements de type F4

EXERCICES RECAPITULATIFS 

Ouvrez 'PV-DEF' , trier la base par résultat décroissant , extraire les étudiants dont le total est supérieur à 172.

Copiez ces étudiants dans un nouveau classeur et faîtes le tableau suivant : 

UNIVERSITE DE NICE SOPHIA  ANTIPOLIS                                                                                                                                                                                                                                              

ANNEE 1999-2000                                                                                                                                                                                                                                                                                

LISTE DES ETUDIANTS RECUS 

Rang
-.  /00-	. 12- /	(	(	(	(	(	(	(	(	(	(	(
3/ 2-+	4.+-2	(	(	(	(	(	(	(	(	(	(	(
5/6-78/	9:-33.:4/	(	(	(	(	(	(	(	(	(	(	(
;/ -78/3	<.+-4	(	(	(	(	(	(	(	(	(	(	(
&29/30	=/+24/	(	(	(	(	(	(	(	(	(	(	(
728/	4-<./3	(	(	(	(	(	(	(	(	(	(	(
:2 1	.: +/>	(	(	(	(	(	(	(	(	(	(	(
427/+-	.4/3.	(	(	(	(	(	(	(	(	(	(	(
: 2	. 12- /	(	(	(	(	(	(	(	(	(	(	(
4.078-	7/3-.	(	(	(	(	(	(	(	(	(	(	(

Mettez le tableau en forme avec une bordure , le titre des colonnes avec un motif. Affichez le rang de classement dans la première colonne, déplacez les colonnes de manière à avoir la chimie gale à coté de la chimie orga, ajoutez une colonne et calculez le total sur 20 points ( il est sur 313). Enregistrez votre tableau sous 'ETUDIANTS RECUS'

Avec le filtre automatique afficher les 10 meilleurs étudiants en physique  puis les 10 plus mauvaises notes.