Formation Excel - niveau1Tutoriel élaboré à partir du site http://www.infx.info/quidnovi/spip.php?rubrique89

Classeur

Un classeur est un document Excel comportant plusieurs feuilles de calculs (par défaut 3). Par un clic droit sur un onglet feuille ( par exemple “feuil1”) , les options suivantes apparaissent :

Lorsqu’on a plusieurs travaux à mettre sur des feuilles différentes, l’option “Renommer” permet d’attribuer des noms personnalisés

Si par la suite, on désire avoir des feuilles de calculs on peut en ajouter par le menu Insertion --> Feuille ou par un clic droit sur la feuille insertion.

Il est possible de déplacer une feuille de calcul dans la barre contenant les noms des feuilles en cliquant sur un nom, et tout en restant cliquer, déplacer la feuille de calcul où l’on souhaite .

Cette option s’avère utile lorsque des feuilles deviennent obsolètes, pour les mettre de côté.

Définitions : les Cellules, les agrandir, les selectionner Object 1

Cellules

Une feuille de calcul est composée de différentes cellules (cases) repérées à l’aide de lignes ( de 1 à 16 384 ) et de colonnes ( de A à IV ). Sur la feuille active, cliquer sur la cellule B3: la cellule devient active.

Chaque ligne et chaque colonne peuvent être élargies ou rétrécies en se plaçant à l’intersection des lignes (ou des colonnes)

Pour se déplacer sur la feuille de calcul entre les différentes cellules après saisie :• Tab : déplacement vers la droite• Entrée : déplacement vers le bas• Utilisation des flèches ( haut, bas , gauche, droite )

Différents types de sélection ( bloc ou groupe de Cellule ) :Il est possible de sélectionner plusieurs cellules en même temps et de définir un bloc. 1) A la souris en maintenant le clic de la souris et en se déplaçant sur la feuille.2) En maintenant la touche [ctrl] appuyée et en cliquant sur différentes cellules pour des cellules non concomitantes

Définitions : Formats de cellule

Object 2

Format de Cellules :Par un clic droit sur une cellule ou un groupe sélectionné, les options suivantes apparaissent :

En fonction de ce qui est saisi dans une cellule, Excel vous propose automatiquement les différents types de formats de cellules ci-dessus.

Les différents onglets sont :• Alignement : permet d’afficher le texte de cellule à la verticale• Police : permet de choisir la taille, le style, la couleur, le soulignement de l’affichage.• Bordure : permet de personnaliser le quadrillage ( trait plein, pointillé, ...)• Motifs : permet de colorier l’arrière plan d’une cellule• Protection : permet de protéger une cellule d’une modification malencontreuse *.

* Cette option n’est fonctionnelle que si la feuille de calcul est protégée. Pour cela, dans le menu Outils --> Protection ---> protéger la feuille (un mot de passe est facultatif )

Première Feuille de calcul : les comptes

Object 3

Première Feuille de calcul : les comptesOn souhaite élaborer une feuille de calcul de suivi de compte .On dispose des éléments suivants :

Date Opération Solde +2560

05/05/2002

--100 ?

06/05/2002

--150 ?

07/05/2002

+300 ?

08/05/2002

--50 ?

09/05/2002

+250 ?

Total ? ?

Pratique : Formules et Copie de Cellules

Object 4

Formules et Copie de CellulesExcel dispose d’un nombre très important de formules qui permettent d’automatiser les calculs. Pour faire appel à un calcul, la formule doit commencer par le signe égal ( = ).

Si on souhaite réaliser la même opération pour les cellules B3 et B4, copies de A3 et A4, plusieurs possibilités s’offrent à vous :1) En B3 saisir =A3 ( idem en B4)2) copier la cellule B2 ( crtl C ou clic droit->copier ou menu Edition->Copier) , se déplacer en B3 et coller : Excel ne recopie pas le contenu de la cellule, mais la formule en effectuant une translation de la formule. Par ce procédé, la formule copiée en B3 n’est plus la formule ( =A2 ) mais (=A3) car on recopie vers une cellule plus bas, de (B2 ) vers (B3).3) vous avez la possibilité de tirer la formule vers le bas en utilisant le petit carré (voir ).

Pour utiliser le contenu de cellules placées sur une autre feuille de calcul, la syntaxe est :nom_de_la_feuille!Cellule . Nota : l’utilisation de la souris après avoir saisi “=” est plus sûre.

Option “Copie de Cellules” : collage spécial

Au moment de coller une cellule vers une autre , vous avez la possibilité de choisir un collage spécial par lequel vous choisissez le contenu de de ce que vous copier :1) “Tout” recopie les formules, le format, les bordures, les commentaires ...2) “Formules” ne recopie que la formule en déplaçant la formule comme ci-dessus.3) “Valeurs” ne recopie que la valeur et non la formule : si une modification de la cellule de départ est effectuée, elle ne sera pas transmise à cette nouvelle cellule.4) “Formats” vous permet de recopier uniquement le format de la cellule.

Les autres options sont explicites.

Deuxième feuille de calcul : une feuille de paye

Une assistante maternelle pratique les tarifs suivants : 18, 30 € Net par jour de garde et sa feuille de paye doit être éditée avec les renseignements suivants :

Nombre de Jours de Garde :

Cotisations salariales

* CSG + RDS ( Salaire Net x 0,0347 ) :

Salaire Net : * CSG déductible ( Salaire Net x 0,0612 ) :

* Sécurité Sociale ( Salaire Net x 0,0934) :

* Assedic+AG ( Salaire Net x 0,0732) : Total des cotisations :

Réaliser sur une nouvelle feuille intitulée “Paye” , le calcul automatisé du salaire de l’assistante maternelle ainsi que des cotisations salariales , sachant que l’on modifiera le nombre de jours de garde tous les mois.

exemple 20 jours

exemple 15 jours

AstucePour appeler une cellule autrement que par sa position (B3) il est possible de lui affecter un nom : par exemple on peut nommer la cellule B3 : salairenet

Types de références (relative, absolue, mixte, attribution du nom)

Object 5Types de références (relative, absolue, mixte, attribution du nom) Lors de la copie des formules dans les cellules, Excel déplace la formule pour l’appliquer à la nouvelle cellule. Il est possible d’utiliser des références différentes pour appliquer des modifications à ce déplacement de formule.

1) Référence relative :Il s’agit des références colonne-ligne classiques :si B2 = A1+A2 alors en copiant dans B3 on obtient B2 = A2+A3

2) Référence Absolue :Il s’agit des références $colonne$-$ligne$ :le $ placé dans la référence de la cellule “fixe” la ligne ou la colonne. Lors du copiage, l’élément mis avec un $ est fixé et ne change pas de valeur :si B2= $A$1+$A$2 alors en copiant dans B3 on a B3 = $A$1+$A$2 .La valeur de B3 est la même que B2.

3) Référence Mixte :Il s’agit de coupler les deux références précédentes en fonction des besoins.Si on souhaite faire la somme de A1 avec la cellule de gauche, il nous faut fixer A1 et laisser le copiage de la cellule de gauche libre.Dans ce cas si B2=$A$1+A2, alors en copiant dans B3 on a B3=$A$1+A3

Il est possible de ne mettre un $ que devant la référence de la ligne ou de la colonne : par exemple B2=$A1. Dans ce cas la colonne A est fixée et seule la référence de la ligne sera modifiée par copier/coller dans C3

Si vous enlevez $ , C3 prend pour formule =B2 par copier/coller !

Pratique : Les fonctions

Object 6 Les fonctions

Pour des calculs plus rapide que l’usage de *,+,-,/ , Excel dispose d’un large éventails de fonctions qui permettent de donner un résultat en respectant une forme pré définie.Pour découvrir l’ensemble des fonctions présentes, cliquer dans le menu Insertion--> fonction

Les fonctions utiles

=SOMME( ) : calcule la somme de plusieurs cellules dans un bloc de cellules.=MOYENNE( ) : calcule la moyenne de plusieurs cellules dans un bloc de cellules.=MAX( ) et =MIN( ) : donne le maximum ou le minimum d’un bloc de cellules.

Pratique : La fonction =SI ( test_logique; valeur_si_vraie ; valeur_si_faux)Parmi les fonctions excel, il existe des fonctions logiques ( Dans Catégories de fonctions, choisir Fonctions logiques) et parmi celles-ci la fonction =SI ().

La fonction =SI renvoie une valeur différente en fonction d’un test fait sur le contenu d’une cellule :

• le test logique utilise les symboles suivant :• SI ( A1=”janvier”; ... ) : teste si la cellule A1 contient exactement le mot janvier.• SI ( A1=120; ... ) : teste si le contenu de la cellule A1 est égale à 120.• SI ( A1>120; ... ) : teste si le contenu de la cellule A1 est supérieur à 120.• SI ( A1>=120 ; ...) : teste si le contenu de la cellule A1 est supérieur ou égal à 120.• de la même façon, on utilisera < et <= pour inférieur et inférieur ou égal .

• En fonction du test ci-dessous, il est possible d’afficher des valeurs différentes dans la cellule qui contient la formule en utilisant la syntaxe suivante : =SI ( test_logique; valeur_si_vraie ; valeur_si_faux)

Par exemples, tester les formules dans les cases suivantes: • en B1 = SI(A1=”janvier” ; “Bonne année”; “Vivement Noël” ) et saisir un mois dans A1• en B2 =SI(A2>10 ; A2+10 ; A2+4) et saisir un nombre dans A2• en B3 =SI(A3<=60; “Pensez à acheter des repas “ ; “ il vous reste plus de 4 repas “)

La forme si imbriquée :

Pour des tests plus compliqués , il est possible d’imbriquer les fonctions SI les unes dans les autres.Par exemple si on souhaite tester le contenu de deux cellules, en indiquant si elles sont positives ou négatives :

En C2, on saisit:

=SI(A2<0;SI(B2<0;"2 négatifs";"A négatif , B positif");SI(B2<0;"A positif, B négatif";"2 positifs"))

1)si A est négatif, alors si B est négatif, on affiche “2 négatifs” sinon "A négatif , B positif"

2)si A n’est pas négatif, alors si B est négatif, on affiche "A positif, B négatif" sinon "2 positifs"

Pratique : Les fonctions Texte

Object 7

Les fonctions Texte

=CONCATENER(texte1;texte2;...) : Assemble plusieurs chaînes de caractères ( ou mots ) de façon à n’en former qu’un .• = concatener (“bonjour “, “et bienvenue”) donne "bonjour et bienvenue"

=MAJUSCULE(texte ) : transforme le texte en majuscules.

=MINUSCULE(texte ) : transforme le texte en minuscules.

=NOMPROPRE(texte ) : Met en majuscule la première lettre du mot et met toutes les autres en minuscules.

VerrouillagePour éviter les erreurs de saisies ou pour bloquer les modifications d’une feuille de calcul ou d’une cellule, il est préconisé de verrouiller celles-ci par le menu Outils -> Protection ->Protéger la feuille

Par défaut , tout est coché, sachant que “Contenu” correspond au contenu des cellules qui ne pourra plus être modifié.

Avant de protéger une feuille, si on souhaite laisser des cellules modifiables, par un clic droit sur la cellule--> Format --> Protection, décocher “Verrouiller” puis protéger à nouveau la feuille de calcul. Seules les cellules où la protection a été enlevée seront modifiables par l’utilisateur.

Graphiques

Le choix de graphiques dans excel est une option intéressante de ce logiciel.

L’ajout d’un graphique se fait après avoir sélectionné vos données par le menu Insertion -> Graphique.

Par la suite la modification des paramètres du graphique se fait par double clic sur ce que l’on souhaite modifier, le clic droit permettant d’avoir accès de nouveau à la boîte de dialogue ci-dessous.

Exportations vers Word : La copie simple

Object 8

La copie simple :

Une fois vos documents réalisés sous Excel ( tableaux, graphiques) , il est conseillé de les mettre en page avec un éditeur de texte ( par exemple Word).

Ouvrir Word par le menu démarrer Word et Excel apparaissent tous les deux dans la barres des tâches

Dans cette barre des tâches, pour passer sous Excel cliquer sur le bouton Microsoft Excel, pour revenir sous Word, cliquer sur le bouton Microsoft Word .

Sous Excel sélectionner votre tableau ou votre graphique et dans le menu édition cliquer sur COPIER

Cliquer dans la barre des tâches (en bas) sur le bouton Microsoft Word.

Puis après avoir choisi l’endroit de l’insertion par un clic de souris dans la page de Word , cliquer sur Coller dans le menu Edition

Votre graphique ou votre tableau apparaît alors dans Word.

Le collage spécial :

En réitérant la copie de données dans Excel , puis en basculant dans Word, vous pouvez choisir de faire un Collage Spécial dans le menu Edition.

Deux options importantes sont présentes :• Coller : permet de coller le contenu pris sous Excel sous différentes formes• Coller avec liaison : permet de coller le contenu pris sous Excel en modifiant les données, si elles sont mises à jour dans Excel ( Attention cette option comporte un danger si votre classeur est déplacé ou modifié par un autre utilisateur , mais son avantage est de ne pas avoir à refaire le document Word on modifie les données du classeur Excel )

Exportations vers Word : le publipostageLe publipostage permet d’ envoyer un courrier type à plusieurs utilisateurs ou contacts référencés dans un classeur Excel selon un modèle fait sous Word.

Editer sous Excel un tableau contenant les données à insérer dans le courrier.

Définir un nom de publipostage

Puis Enregistrer votre

classeur Excel

Aller sous Word, ouvrir un nouveau document vierge.Dans le Menu Outils, choisir Publipostage

Choisir votre classeurexcel que vous avez enregistré

Choisir votre classeurexcel que vous avez enregistré

Sélectionner le nom que vous avez donner au Publipostage

Une nouvelle barre de menu apparaît dans Word .Taper votre texte, et à l’endroit où vous le souhaitez, insérer les champs correspondants :

Votre document Word doit apparaître de cette façon

.

Pour vérifier que le publipostage fonctionner, cliquer sur Fusionner vers un nouveau document .

En principe, un nouveau document de plusieurs pages apparaît , contenant votre texte ainsi que les renseignements mis dan s le fichier source Excel .

Vous pouvez alors, l’imprimer .

Pour revenir à votre document de départ, dans le menu fenêtre , choisissez Document 1. il peut être sauvegardé pour une prochaine utilisation.