Excel : cours

Présentation de l'écran d'Excel

Après un double click sur l'icône Excel dans le bureau de Windows, l'écran Excel apparaît comme cela.




Les barres d'outils permettent un accès rapide aux fonctions les plus souvent utilisées en cliquant sur le bouton que l'on désire activer avec le bouton gauche de la souris. Il existe plusieurs barres d'outils que l'on peut afficher ou non en allant dans le menu affichage puis barres d'outils. Cliquez sur celles que vous désirez faire apparaître à l'écran.

Description des barres d'outils :

1)La barre d'outil standard



2)La barre de mise en forme

Notions de base

Les cellules d'excel :

Dans une feuille de calcul excel on voit un document qui ressemble à une grille composée de cellules ayant toutes une référence correspondant à ses coordonnées ou à un nom si cette cellule est nommée.

C'est à l'intérieur de chaque cellule que l'on va saisir les données du tableau que l'on veut réaliser.

Regardons de plus près :



La cellule sélectionnée est entourée d'une bordure noire, c'est dans cette cellule que va s'inscrire ce que l'on saisit sur le clavier. Pour écrire dans une autre cellule, il faudra déplacer la sélection, soit avec la souris en cliquant sur la cellule choisie, soit avec les flèches de déplacement du clavier ou encore avec la touche tabulation (déplacement sur la même ligne). Tout ce que l'on saisit dans une cellule s'inscrit en même temps dans la zone de formule et si l'on écrit dans cette zone en cliquant à l'intérieur avec la souris le contenu s'inscrit dans la cellule sélectionnée. On utilisera cette méthode lorsque l'on voudra modifier le contenu d'une cellule ou lorsque l'on voudra faire apparaître une formule de calcul correspondant à un résultat dans une cellule.

Par défaut, les colonnes sont indexées par des lettres et les lignes par des chiffres, si vous avez que des chiffres ne paniquez pas, les coordonnées sont alors indexées par L1C1 L signifiant ligne et C colonne. Pour revenir à l'indexation par défaut, cliquez sur le menu Outils puis Options, dans la nouvelle fenêtre cliquez sur l'onglet Général et décochez Style de référence L1C1 comme ci-dessous.



Dans les cellules d'excel on peut saisir aussi bien des chiffres ou du texte et même les deux dans une même cellule mais dans ce cas excel considérera la cellule comme du texte et interdira tout calcul numérique. (à l'exception des formats monétaire F, de date et heure H...)

Lorsque l'on saisit des données dans une cellule excel par défaut, les chiffres sont alignés à droite et le texte à gauche mais libre à vous de modifier cet alignement comme bon vous semble avec les boutons d'alignement de la barre de mise en forme.

Sélectionner des cellules :

Pour sélectionner plusieurs cellules, positionnez le pointeur en forme de croix épaisse sur la première cellule, cliquez et sans relâcher le bouton déplacez vous jusqu'à la dernière cellule que vous voulez sélectionner et relâchez le bouton.



Nous avons ici sélectionné les cellules A1 à D3. Les cellules sélectionnées deviennent foncées sauf la premiere qui reste blanche mais la plage de cellules sélectionnée est entourée d'une bordure noire.

Pour sélectionner des cellules non adjacentes, sélectionnez votre première place de cellule puis appuyez sur la touche Contrôle(Ctrl) du clavier et sans le relâcher, sélectionnez une autre plage de cellules.

La poignée de recopie :

La poignée de recopie se trouve en bas à droite de la cellule ou de la plage de cellules sélectionnée et a la forme d'un petit carré noir.


Saisissez le mot Informatique dans la cellule A1, changez de cellule et resélectionnez la cellule A1, positionnez votre pointeur (croix ) sur la poignée de recopie, il va se transformer en croix noire et fine , cliquez et sans relâcher le bouton, déplacez le pointeur jusqu'en A3, relâchez vous devez avoir ceci :
Elle permet aussi de la même façon de recopier des formules de calculs, de faire des suites avec les jours de la semaine ou les mois et des suites de chiffres si on lui indique quel type de suite on désire. (voir calendrier rubrique tutorials)

Les formules de calculs

Dans un tableau Excel, il est très utile de pouvoir faire des calculs de toute sorte et le logiciel nous permet d'en faire une multitude ; des calculs simples comme les additions, soustractions, produits et d'autres plus complexe que nous aborderons plus tard.

Pour les calculs il est nécessaire d'indiquer à Excel quel type de calculs on désire en insérant la formule dans la cellule où on veut obtenir le résultat. Pour cela il y a deux possibilités, la première est la plus simple elle consiste à insérer une formule à l'aide du bouton coller une fonction ou du bouton somme automatique , la seconde peut être plus rapide mais nécessite de connaître le langage des formules de calculs car dans ce cas c'est vous qui écrivez la formule

Exemple

Ouvrez dans Excel une nouvelle feuille de calculs et saisissez le tableau suivant représentant le nombre de produit vendu par trois vendeurs d'une société pour le premier trimestre de l'année.



On désire connaître le total des ventes de chaque vendeur pour le premier trimestre.

Première méthode

Cliquez en E2 la cellule où on va insérer la formule, puis cliquez sur le bouton somme la formule suivante va s'afficher en E2 : =SOMME(B2:D2) . B2:D2 représente la plage de cellules à additionner qui se trouve maintenant entourée d'une sélection clignotante, appuyez simplement sur la touche entrer du clavier pour obtenir le résultat.

Remarque: Si à la place de Jacky nous avions eu un chiffre, il aurait été pris dans la sélection clignotante et de ce fait additionné aux autres chiffres, or on ne veut pas cela, il faut alors à l'aide de la souris restreindre la sélection aux chiffres que l'on veut additionner en les sélectionnant. De la même façon si l'on veut ajouter d'autres chiffres à la somme il nous faut les sélectionner.

Deuxième méthode

Cliquez en E2, saisissez "=" puis cliquez sur la cellule B2 relâchez et saisissez "+" sur le pavé numérique puis cliquez sur la cellule C2 relâchez et saisissez "+" puis cliquez sur la cellule D2 on obtient la formule suivante =B2+C2+D2, faites entrer sur le clavier pour obtenir le résultat.

Vous pouvez poursuivre les deux totaux suivant avec l'une des deux précédentes méthodes ou pour gagner du temps, cliquez sur le premier résultat pour le sélectionner, attrapez la poignée de recopie et tirez la formule sur les deux cellules suivantes. De cette façon on peut faire une centaine de calculs en une seconde.

Vous pouvez faire tout type de formule soustraction, division, produit et même les combiner si vous utiliser les parenthèses. N'oubliez jamais qu'une formule commence toujours par le signe =.

Pour calculer la moyenne de trois chiffres on saisirait la formule suivante =(B2+B3+B4)/3

Insérer une fonction avec le bouton


Ouvrez dans Excel une nouvelle feuille de calculs et saisissez le tableau suivant représentant une facture



Sélectionner la cellule D2 et cliquez sur vous obtenez :



Dans catégorie, choisissez Tous et dans nom de la fonction choisissez PRODUIT puis cliquez sur OK la fenêtre suivante apparaît:



Cliquez dans la case blanche à coté de Nombre1 et sélectionnez dans le tableau la cellule B2, même opération pour le Nombre2 avec la cellule C2 puis OK pour obtenir le résultat. Pour un produit de plus de deux chiffres passer à Nombre3 etc.

Vous trouverez d'autres exemples sous la rubrique tutorials.

Les graphiques
Dans Excel les données que nous avons dans les tableaux ne sont pas toujours faciles à analyser à l'œil nu c'est pour cette raison que l'assistant graphique nous permet d'en obtenir une représentation graphique sous forme d'histogramme de barre de courbe ou de secteur plus communément appelé camembert.

Prenons comme exemple une société qui veut voir à l'aide d'un histogramme obtenir l'évolution des ventes de ses vendeurs au fil des mois.

Dans une nouvelle feuille de calcul Excel saisissez les données du tableau suivant en prenant soin d'utiliser la poignée de recopie pour les mois et en utilisant la fonction somme pour les totaux.



Ensuite cliquez sur le bouton assistant graphique de la barre d'outils standard.

La fenêtre suivante va s'afficher :



Dans la partie type de graphique choisissez celui que vous désirez (ici histogramme) puis choisissez le sous type en cliquant dessus puis cliquez sur suivant pour obtenir la fenêtre suivante :

Cliquez dans la zone Plage de données et sélectionnez le tableau de données que vous venez de saisir (cellules A1 à F5). Une fois sélectionné, un aperçu du graphique apparaît dans la partie supérieure vous pourrez cliquer sur lignes ou colonnes pour voir les différences et choisir la série qui vous convient le mieux puis faites suivant pour obtenir ceci :



Cette avant dernière étape nous permet de donner un titre au graphique et de modifier certains paramètres du graphique, vous pouvez cliquer sur suivant



La dernière étape permet de choisir l'endroit où l'on veut insérer le graphique, si vous choisissez sur une nouvelle feuille le graphique sera en plein écran dans une nouvelle feuille qui s'appellera Graph1 et si vous choisissez en tant qu'objet dans Feuil1, il apparaîtra sous votre tableau comme objet que vous pourrez redimensionner et déplacer.

Voilà le résultat :



Dernier détail pour obtenir la légende en bas double cliquez sur la légende, cliquez sur l'onglet emplacement et choisissez bas.

Les formules conditionnelles

Dans Excel les formules conditionnelles permettent d'afficher un résultat si certaines conditions sont respectées ou un autre si elles ne le sont pas.

Dans l'exemple si dessous nous devons créer une formule qui, en fonction de la moyenne obtenue, nous affichera le résultat d'admission. Si la moyenne obtenue est inférieure à 8, l'élève sera recalé, si sa moyenne est inférieure à 10 et supérieure ou égale à 8, il devra passer l'oral et si sa moyenne est supérieure ou égal à 10, il sera admis.

Commencer par ouvrir Excel et recopier le tableau si dessous dans une feuille de calcul



puis faites le calcul de la moyenne en utilisant la fonction moyenne (cliquez dans la celule D2) à l'aide du bouton coller une fonction et dans la liste choisissez Moyenne



OK

Puis cliquez dans le champ de saisie Nombre 1 et sélectionnez les cellules B2 à C2 et cliquez sur OK . Le résultat apparaît, utilisez la poignée de recopie pour reproduire la formule sur les cellules D3, D4, D5, D6.

Passons à la formule conditionnelle

En E2 écrivez la formule suivante :

=SI(D2<8;"Recalé";SI(D2>=10;"Admis";"Orale")) puis entrer

Qui signifie : si la cellule D2 est inférieure à 8 alors il sera recalé sinon si D2 est supérieure ou égale à 10 alors il sera admis sinon (si aucune des deux conditions ne sont respectées, c'est qu'il se trouve entre les deux et il ira par conséquent à l') oral .

Une formule conditionnelle a toujours la même syntaxe mais peut être plus longue s'il y a plus de conditions.

Ici, il y a trois conditions Recalé, Oral ou Admis et le résultat Oral se trouve entre les deux, c'est pour cette raison que nous n'avons pas besoin, dans la formule, de lui indiquer qu'il doit être compris entre 8 et 10 car on a indiqué les valeurs extrêmes et s'il n'est ni inférieur à 8 ni supérieur ou égale à 10, il est forcément compris entre 8 et 10.

Les parenthèses s'ouvrent toujours aprés le si, ce sont elles qui groupent les conditions et s'il y a plus de deux conditions, on ouvre de nouveau des parenthèses aprés chaque si qu'on refermera à la fin de la formule. Lorsqu'il y a plusieurs parenthèses, Excel attribue automatiquement une couleur différente à chaque groupe (la première reste noire) ce qui évite d'oublier de les fermer car la dernière sera toujours noire.

Dans la formule les valeurs résultat sont entre guillemets car il s'agit de texte mais si vos valeurs sont des chiffres, inutile d'en mettre.

Reproduisez la formule à l'aide de la poignée de recopie sur les cellules E3, E4, E5, E6.

Voila c'est fini vous devez obtenir le tableau suivant :

moyenne résultats
11 Admis
6,5 Recalé
12,5 Admis
17,5 Admis
9,5 Oral

Les tableaux croisés dynamiques

L'outil rapport de tableaux croisés dynamiques d'Excel permet de faire une synthèse rapide d'un tableau de données complexes ou d'une base de données.

Prenons comme exemple le tableau suivant qui contient une colonne vendeur, date commande, produits et montant commande.



Vous désirez obtenir le détail des ventes de chaque produit par vendeur.

En utilisant l'assistant de rapport de tableau croisé dynamique, vous obtiendrez ce que vous désirez en quelques clics.

Commencez par sélectionner une cellule à l'intérieur de votre tableau puis dans le menu
Données, cliquez sur vous allez obtenir la fenêtre suivante :

Ici vos données se trouvent dans une base de données Excel et vous voulez créer un tableau croisé dynamique, donc cochez comme ci-dessus puis cliquez sur suivant.

L'étape suivante vous permet de sélectionner la plage de cellules à analyser mais si vous avez bien sélectionné une cellule du tableau au début, il trouve la plage de cellules tout seul, vous pouvez cliquer sur suivant.


L'étape suivante vous permet de choisir la destination du tableau croisé dynamique, soit sur une nouvelle feuille soit dans la feuille existante et vous choisissez alors la cellule de destination du tableau. (Ici choisissez nouvelle feuille).



Cliquez ensuite sur le bouton disposition pour organiser votre tableau croisé dynamique, vous obtiendrez la fenêtre suivante :

Pour organiser le tableau croisé, vous devez déplacer les onglets sur la structure du tableau en cliquant dessus, maintenez le bouton appuyé et déposez l'onglet à l'endroit voulu.

Pour obtenir le détail des ventes de chaque produit par vendeur vous devez déposer vendeur, sur ligne, produit sur colonne et montant commande sur données qui doit se transformer en Somme Montant commande, si c'est une autre formule faites un double clic sur l'onglet et choisissez la fonction somme.



Cliquez sur Ok puis Terminer, vous obtenez le tableau du détail des ventes de chaque produit par vendeur.



La barre d'outils des tableaux croisés dynamiques :

La valeur cible

L'outil valeur cible permet de rechercher un résultat spécifique pour une cellule en ajustant la valeur d'une autre cellule.


Prenez comme exemple le tableau suivant des notes obtenues par un élève :



Saisissez les notes 1 et 2 et en D2 la formule : =MOYENNE(A2:C2)

Avant d'avoir le résultat de la Note 3 la moyenne de l'élève est de 8,5.

Quelle devra -être sa Note 3 pour que la moyenne soit égale à 10 ?

L'outil Valeur cible d'Excel va nous permettre de la calculer.

Cliquez sur le menu Outils puis Valeur cible pour obtenir la fenêtre suivante :



Cellule à définir: Dans cette section, il faut définir la cellule dans laquelle se retrouvera la valeur à atteindre. Cette cellule doit contenir absolument une formule(dans notre exemple la formule =MOYENNE(A2:C2) )

Valeur à atteindre: Dans cette section, il faut y inscrire le résultat souhaité de la formule comprise dans la section Cellule à définir. (Ici 10).

Cellule à modifier: Sélectionnez la cellule qui variera de façon à ce qu'elle prenne une valeur quelconque permettant d'obtenir le résultat inscrit dans la section Valeur à atteindre. Il faut sélectionner qu'une cellule ( ici C2).

Remplissez la fenêtre Valeur cible comme ci-dessous :



Faites OK vous obtiendrez ceci :



Validez par OK et vous obtenez la Note 3 (13) que devra obtenir l'élève pour avoir une moyenne de 10.

Les filtres dans une base de données Excel

Lancer Excel et saisissez les données suivantes dans une feuille de calcul.



Sélectionnez la ligne de titre



et cliquer sur Données puis Filtrer et Filtre automatique.

A droite de chaque titre apparaît une flèche qui dans le menu déroulant permet de faire des choix pour filtrer des éléments de la colonne :

Vous ne voulez afficher que les Femmes :

Pour revenir a l’état initial cliquez sur (Tous).

ou que ceux qui on un code postal dans les départements 55 56 et 57 :

puis choisir :

Les filtres élaborés

Comme vous venez de le voir les filtres simples permettent facilement de filtrer des données mais lorsque le nombre de critère de filtrage est plus important il faudra utiliser les filtres élaborés.

Vous désirez conserver les hommes qui on un nom qui commence par P, M et C.

Pour cela vous devez créer un tableau de critère en recopiant les Noms de champs en dessous ou sur une autre feuilles, suivi des critères de filtrage.



Ensuite cliquer sur Données puis Filtrer et Filtre élaboré.



Il y a deux possibilités soit la liste est filtrer sur place (dans le tableau de départ) ou vers un autre emplacement qu’il faudra renseigné dans le champ Copier dans :.

Dans le champ Plage faites un clic dans la zone de saisie puis sélectionner le tableau de données ($A$1:$F$11), pour la zone de critères, faite la sélection de la zone de critères et cliquez sur Ok.

Pour revenir au tableau de données initiales, sur Données puis Filtrer puis afficher tout.

Excel astuces

Protéger par mot de passe un fichier Excel

Vos documents Excel comportent généralement des données confidentielles (comptes bancaires, etc). Les dernières versions d'Excel (2002/2003) incorporent une fonctionnalité permettant de protéger efficacement un document Excel des regards indiscrets en y appliquant un mot de passe à l'ouverture ce celui-ci.
Pour cela, une fois votre document Excel ouvert, allez dans le menu Outils puis Options. Dans la fenêtre qui apparaît à l'écran, rendez-vous dans le menu Sécurité. Saisissez votre mot de passe dans le champ Mot de passe pour la lecture, puis cliquez sur le bouton Options avancées. Dans la liste déroulante, optez pour le type de cryptage RC4, Microsoft Enhanced Cryptographic Provider v1.0. Laissez la valeur 128 en longueur de clé, puis validez en cliquant sur OK. Votre document Excel est désormais protégé par mot de passe lors de son ouverture.

Créer un lien vers une autre feuille - Excel 2000/2002/2003

Pour passer d'une feuille à une autre dans un document Excel, vous devez utiliser les onglets situés en bas à gauche de la fenêtre Excel. Si votre document Excel est composé de plusieurs feuilles, il est plus pratique d'utiliser un simple lien hypertexte pour changer de feuille (pour par exemple avoir les détails d'un calcul se trouvant sur une autre feuille).
Pour cela, sélectionnez la case contenant votre texte qui servira de lien hypertexte puis cliquez dessus avec le bouton droit de la souris. Dans le menu contextuel qui apparaît, cliquez sur la commande Lien hypertexte puis dans la zone Lien Hypertexte de la nouvelle fenêtre, cliquez sur le bouton Emplacement dans ce document. Sélectionnez enfin le nom de votre feuille vers laquelle vous souhaitez faire pointer cet hyperlien puis cliquez sur le bouton Ok.

Fusionnez plusieurs cellules sous Excel

Il peut arriver que vous ayez besoin de fusionner plusieurs cellules entre elles. Par exemple, il est souvent fréquent que l'on est besoin de regrouper 5 cellules successives en une seule cellule afin d'insérer un long titre, et le tout sans modifier la taille des colonnes afin de conserver la présentation des données contenues sur cette feuille Excel.
Pour cela, sélectionnez les cellules que vous souhaitez fusionner, elles apparaissent alors en surbrillance, et cliquez sur le bouton Fusionner et centrer présent dans la barre d'outils Mise en forme d'Excel.
Cette commande est également accessible via le menu Format puis Cellule. Une nouvelle fenêtre s'ouvre alors, rendez-vous dans l'onglet Alignement puis cochez la case Fusionner les cellules. Cliquez enfin sur le bouton OK pour appliquer la modification.

Fini les dièses - Excel

Si après avoir tapé une formule dans une cellule d'Excel, et que celui-ci vous retourne une série de dièse #####, les choses ne sont pas si mauvaises qu'elles n'y paraissent. Cela signifie simplement que le résultat de votre formule est trop gros pour la colonne.
Il suffit donc d'élargir les colonnes. Pour cela, cliquez entre deux colonnes (le curseur devient alors une double flèche) puis faites glisser la souris pour redimensionner la colonne et voir enfin les résultats de vos formules.

Insérer rapidement la date dans Excel

Il est très facile d'insérer rapidement la date dans un document Excel. Usuellement, vous passez par le menu Insertion, Fonctions, cliquez sur Date & heure puis sur DATE. La date est alors insérée dans la cellule sélectionnée.
Pour parvenir au même résultat encore plus rapidement, appuyez simultanément sur la touche Ctrl et sur la touche ;

Améliorer l'apparence des histogrammes

Avec Excel, il est facile de personnaliser la présentation des histogrammes 2D ou 3D en leur ajoutant des motifs et en modifiant leur couleur.
Il suffit de sélectionner une des barres du graphique, d'effectuer un clic droit et de choisir la commande Format de série de données.
Mais vous avez aussi la possibilité de remplacer cette barre par une image en rapport avec les données.
Par exemple, illustrez le taux de fréquentation de telle ou telle liaison aérienne par un clip art représentant un avion.
Pour cela, cliquez deux fois sur le graphique pour l'éditer, puis sélectionnez la première série de données (symbolisée par des barres de couleur identique).
Lorsque des petits carrés noirs apparaissent sur votre sélection, choisissez la commande Image qui se trouve dans le menu Insertion.
Dans la fenêtre de dialogue qui s'affiche alors, recherchez le dossier contenant l'image, puis validez.
Les barres sélectionnées vont, sur toutes leurs surfaces, recevoir l'image.
C'est pour cette raison que celle-ci semble étirée ou comprimée dans le sens de la longueur.
Pour obtenir un empilement de l'image "parfait", cliquez sur l'onglet Motif, puis cochez la case Empiler et mettre à l 'échelle.
Dans le champ Unités/Image, saisissez éventuellement la valeur numérique que l'image devra représenter.

Enrichir la présentation des tableaux

A partir de la version 97, Excel dispose d'un gestionnaire de vue qui permet de présenter les différentes feuilles de calcul d'un classeur.
Pour que votre présentation ait plus d'impact, placez une photo dans d'arrière-plan d'une feuille.
Pour cela, choisissez la commande Feuille du menu Format, puis cliquez sur Fond.
Dans la fenêtre de dialogue qui s'affiche alors, séléctionnez l'image à insérer.
N'importe lequel des formats graphiques en mode point ou vectoriels installés sur votre système est accepté.
Validez ensuite de votre choix.
Notez que seule la feuille de calcul affichée reçoit l'image en arrière-plan.
Ensuite, si vous désirez supprimer cette illustration, rappelez la fenêtre de dialogue qui permet de choisir une image puis cliquez sur aucun.

Bravo Jawad;
C'est un cours bien illustré et simple
Merci