Même si ce n'est pas exactement le cours Excel
que je donne dans la salle de formation informatique à Chiny ou en entreprise,
ce tutorial permet à un débutant de créer rapidement une facture - devis
personnalisés avec le logiciel de Microsoft, et même de reprendre des
fonctionnalités complexes. Cette facture ne remplace pas une gestion commerciale
(qui gère également les stocks) mais devrait permettre à un indépendant de
faire rapidement une facture simple et correcte reprenant les différents taux
de TVA, les quantités, prix, ... éventuellement repris avec les codes des
produits.
EXCEL est un tableur.
Sa principale utilisation est le calcul automatique de formules. Il est utilisé
dans différents domaines de la gestion informatique et financière, calculs de
statistiques, ...
Excel peut être vu
comme une feuille de calcul. Les colonnes sont référencées par des lettres, les
lignes par des chiffres. La référence d'une cellule est reprise avec la
combinaison de la colonne suivie de la ligne. La référence de la cellule en
haut à droite (à l'intersection de la colonne A et de la ligne 1) est donc A1.
Celle à l'intersection de la ligne 8 et de la colonne D se nomme D8. Les
majuscules - minuscules n'ont aucune importance.
Pour sélectionner une
cellule, il suffit de se trouver dans la cellule. Pour sélectionner une
colonne, cliquez sur la référence de la colonne (A par exemple). Pour
sélectionner plusieurs colonnes, cliquez sur une référence colonne et
poursuivez en maintenant la touche gauche de la souris enfoncée. C'est de même
pour les lignes. Pour sélectionner un groupe de cellule, maintenez la touche
gauche de la souris enfoncée et bougez la souris (quelque soit le sens).
|
A
retenir: les cellules Excel sont référencées par leur numéro de colonne
suivi de leur numéro de ligne
|
Un petit truc, pour
sélectionner des ensembles de cellules non adjacentes, maintenez la touche
<CTRL> du clavier enfoncée lorsque vous sélectionnez la zone suivante.
Pour élargir (ou
rétrécir) un colonne ou une ligne, pointez entre les références de colonnes
dont vous désirez modifier la taille. Il n'est pas possible comme dans Word de
modifier la taille d'une seule cellule.
Commençons tout de
suite par un petit calcul.
Positionnez votre
curseur sur la cellule A1 et tapez un nombre (ici le chiffre 45). Faites de
même dans la cellule A2. Remarquez que le chiffre se met à deux place: dans la
cellule et dans la barre de formule.
Nous souhaitons faire
la somme de ces cellules et obtenir le résultat dans la cellule A3. Dans un cas
général, nous utiliserions le calcul mental pour taper dans la cellule le
chiffre 99. Microsoft Excel va nous faciliter la vie puisque non seulement, il
va faire le calcul mais va tenir également compte des modifications dans les 2
cellules pour afficher le résultat correct.
Pour signaler à EXCEL
que le contenu de la cellule est un calcul à exécuter, le contenu de la cellule
doit commencer par = (+ est également accepté). Tapons dans A3 =A1+A2 (les
références des cellules que nous souhaitons additionner). Lorsque nous
acceptons la formule (par la touche <Entrée>), le résultat de l'addition
apparaît automatiquement dans la cellule A3.
En repassant sur
cette cellule, le résultat apparaît dans la cellule, tandis que la formule est
affichée dans la barre de formule. Pour modifier la formule, il vous faut donc
repasser dans la barre de formule. Un petit truc, si vous appuyez sur la touche
<F2>, vous pouvez directement modifier le contenu de la formule dans
la cellule.
Si vous modifiez le
contenu de la cellule A1 ou A2, le résultat est directement affiché dans la
cellule A3.
|
A
retenir: les calculs commencent toujours par =, il ne faut pas d'espaces
entre les chiffres, références de cellules. La référence de cellule peut être
tapée en minuscule ou majuscule
|
Si nous souhaitons le
montant TVA comprise dans la cellule A4 (ou ailleurs), tapons la formule =A3*1,21
et Excel reprend le résultat, même si nous changeons la valeur en A1 ou en
A2 (21 % sur la majorité des produits en Belgique).
Comme vous le voyez,
vous pouvez mélanger des références de cellules et des chiffres sans problèmes.
Ceci est valable pour les 4 opérations
- +
pour l'addition
- -
pour la soustraction
- *
pour la multiplication (la lettre X n'est pas reconnue)
- /
pour la division.
Ce sont les 4 sigles du pavé numérique.
Attention aux parenthèses: Le calcul 5+6*5 nous donnerait comme résultat 5+6=11 * 5=
55. Malheureusement, les règles des parenthèses sont d'application. Pour
l'ordinateur, la multiplication et la division sont prioritaires. Le calcul
ci-dessus donnera 6*5=30 + 5 = 35. En utilisant les parenthèses =(5+6)*5
le résultat sera effectivement 55.
|
A
retenir: EXCEL ne fait des calculs que sur des cellules contenant
uniquement des chiffres. Le calcul 5 * "5 PC" donnera un message
d'erreur, le contenu 5 PC est vu comme du texte.
|
1. Dans la cellule B2, tapons le chiffre
45 et positionnons notre souris juste dans le coin droit de la cellule, le curseur
devient un +. En maintenant la souris enfoncée, déplaçons le curseur vers la
droite. Le chiffre est automatiquement recopié dans les cellules adjacentes, de
même pour un texte (YBET en C2 et déplacement vers la gauche). Le sens de
déplacement n'a pas d'importances.
3. Dans la cellule B8, tapons LUN, en
recopiant les cellules, EXCEL recopie automatiquement les abréviations des
jours de la semaine, de même pour le jour en toute lettre, abréviation du mois
et mois complet.
7. En I5 (par exemple), tapons maintenant une date
et recopions la cellule, les dates se suivent automatiquement, tenant compte
des mois et même des années.
Nous souhaiterions
faire une suite de nombre (1 2 3, ...). Par le point 1, nous savons que
sélectionner un nombre et le recopier ne fait que remplir les cellules avec le
même nombre. Pour faire cette suite, nous devons d'abord signaler au tableur
que nous voulons effectivement cette progression arithmétique. Sélectionnons la
cellule contenant le chiffre 1 et la cellule adjacente contenant le chiffre 2
et seulement ensuite, recopions les cellules. Excel va automatiquement créer la
suite de nombre.
Ceci marche également
avec les formules de calculs (avec des modifications) comme nous le verrons
dans l'exercice ci-dessous et au chapitre suivant
|
A
retenir: le coin droit en bas d'une cellule permet de recopier son
contenu vers d'autres cellules.
|
Nous allons utiliser
ces notions pour faire un petit tableau de type facture. Tous le cours Excel
premier niveau se basera sur ce petit exercice qui sera ammélioré à chaque
chapitre. Commençons par effacer le contenu des cellules que nous avons tapé.
Pour cela, sélectionnons l'ensemble des cellules où se trouvent des nombres ou
du texte et appuyons sur la touche <DELETE> (clavier belge) ou
<Suppr> (clavier français).
Tapons maintenant les
textes ci-dessous dans les bonnes cellules.
Le but est de taper
la quantité, la description de la marchandise, le prix et de retrouver
automatiquement (par calcul) le montant total soit le prix multiplié par la
quantité.
Rentrons les
données suivantes dans la ligne 2 (par exemple)
Le but est de
retrouver automatiquement le prix dans la cellule A4. Nous allons donc
faire une formule dans la cellule D2 en multipliant la quantité et le prix
unitaire dans la ligne associée (ici la ligne 2). La formule sera: =A2*c2.
Si la formule est tapée correctement, le résultat est effectivement 800. Un
changement dans la quantité ou dans le prix donnera automatiquement le
résultat.
- =
pour signaler un calcul
- A2
pour signaler que nous souhaitons la quantité (colonne A) associée à la
ligne 2
- C2
pour signaler que nous souhaitons le prix unitaire (colonne A) associé à
la ligne 2
Une facture, un
devis, ... utilise généralement plusieurs lignes. Nous allons donc compléter
notre tableau en conséquence.
Commençons par positionner
la souris dans le coin droit de la cellule D2 et recopions le contenu vers le
bas jusqu'à la ligne 20. Les autres cellules affichent 0. En effet, si nous
positionnons le curseur sur la cellule D20, le contenu est 0 mais la formule
est devenue =A20*C20.
Excel a automatiquement
mis les formules à jour en fonction du numéro de ligne. Même si ceci n'est
pas toujours intéressant, cette mise à jour permet de faire de grands tableaux
comprenant une multitudes de formules sans trop se fatiguer.
Nous allons compléter
notre facture - devis Excel par une colonne Réduction. La première opération
est d'insérer une colonne entre la colonne C (prix unitaire) et D (Total). Sélectionnons
la colonne D et utilisons la touche droite de la souris pour afficher le menu
contextuel. Sélectionnons la commande "Insérer" et une colonne est
insérée entre C et D. La colonne D devient donc la colonne E. La formule dans
la colonne de total est automatiquement mise à jour.
Remplissons les 2
cellules comme ci-dessous. A ce stade, n'utiliser le sigle des pourcentages %,
tapez uniquement le montant de la réduction (ici 10). Notre montant total est
faux puisqu'il ne tient pas compte de la réduction. Nous allons donc modifier
la formule en E2 pour inclure cette réduction.
La formule en E2
devient: = montant de base - réduction, en sachant que la réduction vaut
le montant de base multiplié par la réduction divisée par 100 (puisque nous
n'utilisons pas de pourcentages).
<E2>=A2*C2 -
A2*C2*D2/100 ou <E2>=A2*C2*(1-D2/100).
Les 2 formules donnent le même résultat. Il ne reste plus qu'à recopier la
formule vers le bas jusqu'à la cellule D20 comme précédemment. Excel met les
formules par ligne à jour automatiquement
Avec cette première
partie du cours, nous manipulons déjà les cellules et leur contenu et les 4
opérations de calcul. Ceci donne déjà un petit aperçu de notre facture - devis.
Néanmoins, Microsoft Excel est nettement plus complet puisque des fonctions
sont directement intégrées dans le logiciel. Le chapitre suivant de ce tutorial
va nous permettre de les manipuler
Même si les calculs
avec les 4 opérations sous Excel sont intéressants, l'utilisation principale du
tableur reprend surtout les fonctions. Cette partie va nous permettre
d'utiliser quelques fonctions Excel simples, mais surtout de pouvoir en
utiliser d'autres.
Reprenons l'exercice
facture du chapitre précédant. La première modification serait d'ajouter un
total de nos lignes, en dessous du montant des articles. Nous pourrions
utiliser en E21 une formule de type =E2+E3+ .... un peu long (en autre). Nous
allons utiliser notre première fonction Excel, la SOMME. Positionnons le
curseur sur la cellule E21 et cliquons dans la barre d'outils sur le symbole
=SOMME(E2:E20) s'inscrit dans la barre de formule, dans la cellule E21,
E2:E20 est en sur brillance et la zone E2 à 22 est entourée. Si nous
sélectionnons une autre zone avec la souris, la zone entre parenthèse dans la
formule est automatiquement modifiée en conséquence mais ce n'est pas notre
but. Pour accepter la plage de cellule, tapez <Entrée>. Remarquez le
":" entre la référence E2 et la référence E20. Ce sera important pour
la suite du cours.
L'intérêt d'utiliser
cette formule à la place de =E+E3+E4+... est déjà explicite mais il y en a une
autre. Sélectionnez une ligne dans cet ensemble et utilisez le menu contextuel
pour insérer une ligne (touche droite de la souris)
Reprenons notre
résultat (maintenant en E22) et vérifions dans la barre de formule la fonction.
Elle a été modifiée automatiquement par EXCEL pour tenir compte de cette
insertion. Il faudra néanmoins recopier la formule de prix de la ligne.
Il nous reste deux
petites formules à rajouter: le montant de TVA et le montant à payer. Si nous
utilisons une TVA à 21 % (Belgique), la formule en E23 devient =E22*0,21 et la
formule en E24 (montant à payer) devient =E22+E23
A ce stade, il reste
deux petits problèmes: les chiffres derrière la virgule et la présentation.
Nous les verrons plus tard.
|
A
retenir: L'insertion d'une ligne ou d'une colonne modifie automatiquement
la plage des cellules d'une fonction.
|
Le tableur EXCEL est
utilisé par différentes professions: des mathématiciens, logisticiens,
banquiers, ingénieurs, ... La liste des formules disponibles est donc
importante. Le but n'est pas de les étudier toutes mais de vous permettre
d'utiliser les fonctions qui vous intéressent.
|
Sélectionnons une cellule
vide. Pour appeler la liste des fonctions, il suffit de cliquer dans la barre
d'outils sur
|
|
|
ou d'utiliser la commande
fonction dans le menu Insertion.
|
|
Après cette commande,
la liste des fonctions possibles classées par catégorie apparaît comme
ci-dessous. Les catégories de fonctions sont à gauche et les fonctions incluses
dans chaque catégories à droite. Cette structure est très courante dans
l'informatique. La présentation est un peu changée en EXCEL XP et 2003
(notamment une aide pour la recherche) mais le résultat est le même.
La catégorie "Dernières utilisées" reprend la
dizaine des dernières commandes que vous avez faites. La catégorie
"toutes" reprend l'ensemble des fonctions d'Excel, les utilisées, les
moins utilisées et les … jamais utilisées. Sélectionnons la fonction somme que
nous avons utilisée plus haut.
Je lis! Nombre1: nombre1;nombre2 représentent de 1 à 30
arguments .... Chaque fonction est livrée avec une aide sur les différentes
rubriques à remplir.
Pour désigner la
plage de cellule dont je veux la somme, je vais utiliser le bouton à coté de la
plage "Nombre 1" demandée (cerclé en rouge). Une petite barre
d'outils blanche apparaît. En sélectionnant une plage de cellule avec la
souris, les références s'inscrivent automatiquement. Pour revenir à notre
fenêtre précédente, il suffit de cliquer de nouveau sur le petit icône ou de
taper <Entrée>.
Remarquez que les 2
références extrêmes de cette zone sont notées A2:A15. Le : signale à Excel
que vous souhaitez toute la plage. Faisons de même mais en regard de Nombre2.
La formule (affichée au-dessus) devient =SOMME(A2:A15;C2:C10)
|
A
retenir: Les " : " signalent à Excel que la plage
reprend toutes les cellules entre les 2 références désignées. Par contre, le
signe " ; " découpe des zone distinctes.
|
Le résultat ci-dessus reprendra donc la somme
des cellules comprises entre A2 et A15 + la somme des cellules reprises entres
les cellules C2 et C10.
Les fonctions Max,
Min, Moyenne, Mode s'utilisent de la même méthode.
Dans le menu
Insertion, sélectionnons Fonction. Dans la liste, sélectionnons ENT qui
affiche la valeur entière d'un nombre (nombre sans les chiffres derrière la
virgule).
La fenêtre qui apparaît est identique à celle
ci-dessus. Tout au-dessus, dans la barre des formules, =ent(). En effet, une
fonction nécessite un calcul et tous calculs commencent par =. Il est suivi par
le nom de la fonction, sans espaces.
Nombre est le nombre
que nous voulons arrondir à la décimale inférieure. Cliquons sur la flèche rouge
et la fenêtre disparaît pour laisser la place à notre grille habituelle.
A l'aide de la
souris, cliquons sur la cellule pour laquelle nous souhaitons avoir le nombre
entier (exemple A3) et appuyons sur la touche <Entrée> ou sur la flèche
en rouge en haut. La fenêtre de fonction réapparaît comme ci-dessous.
La formule dans la barre des formules devient =ENT(A1).
Nous pouvons maintenant appuyer sur OK pour récupérer le résultat.
Intéressons-nous à la manière dont Excel a écrit cette fonction:
=
puisque c'est un calcul
ENT, la fonction
(référence
de cellule), la partie à calculer est entre parenthèses.
Remarquez qu'il n'y a pas d'espaces.
Voyons cette formule
de manière plus théorique: =ent(nombre à calculer). Ce nombre à calculer peut
en fait être n'importe quoi, un chiffre, une référence de cellule ou .. le
résultat d'un calcul. Dans la barre de formules, nous pouvons par exemple
taper =ent(A1+A2). Ceci fera d'abord l'addition du contenu de A1 et du
contenu de A2, la formule retirera la valeur entière de cette formule. Voyons
cette formule avec les aides de Microsoft.
Revenons à notre fonction ENT() de départ et utilisons l'icône pour
faire apparaître la petite barre.
Sélectionnons la
cellule A2 et tapons immédiatement sur le sigle +. Si nous sélectionnons la
cellule A3 avec la souris, ceci devient dans la barre blanche A2+A3, et ainsi
de suite. Nous aurions également pu prendre une cellule contenant une autre
fonction mais cette façon de travailler pose quelques problèmes de
présentation. Nous verrons les fonctions imbriquées à la fin de ce chapitre.
|
A
retenir: Lorsque vous utilisez une fonction, vous pouvez utiliser les 4
opérations à l'intérieur de l'opérante de la formule.
|
Une remarque, cette
fonction ne permet pas vraiment d'arrondir les chiffres, elle se contente de
supprimer les chiffres derrière la virgule.
La fonction arrondi
permet d'arrondir à un nombre de chiffres souhaités un nombre réel. Même si
nous verrons qu'il y a moyen de demander de n'afficher qu'un certain nombre de
chiffres derrière la virgule, cette formule est primordiale. En effet, si vous
demandez de n'afficher que les 2 derniers chiffres derrière la virgule, EXCEL
utilise tous les chiffres derrière la virgule. La somme des arrondis n'égale
pas forcément l'arrondi de la somme.
|
Prenons
un petit exemple
|
Nombres
|
arrondi
|
|
20,3
|
20
|
|
20,3
|
20
|
|
20,3
|
20
|
|
20,3
|
20
|
|
Somme=81,2
|
somme des arrondis = 80
|
|
arrondi de la somme = 81
|
|
Ceci peut poser
quelques petits problèmes de calcul. Par contre, dans le cas de la fonction
ARRONDI() (idem pour la fonction ENT() ci-dessus), les calculs à base de ces
cellules n'utiliseront que les nombres résultats de ces fonctions.
Remplissons Nombre
par la cellule E22 (le résultat est l'arrondi de la valeur reprise dans cette
cellule - même si notre cellule E22 reprend un résultat de fonction) et le
nombre de chiffres derrière la virgule, utilisons 2 pour l'€. La formule
devient =arrondi(E22;2).
L'étape suivante de notre cours excel va être de
mettre des calculs (fonctions) inclus dans d'autres calculs (fonctions), par
exemple l'arrondi d'une somme.
Excel permet de
calculer des fonctions complexes directement intégrées. Néanmoins, souvent
l'utilisation d'une seule fonction est insuffisante, il faut utiliser une
fonction sur un résultat d'une formule ou d'une autre fonction. Imbriquer
une fonction consiste à remplacer des opérantes par d'autres fonctions. Nous en
profiterons pour en voire de nouvelles. Reprenons notre exercice de facturation
des chapitres précédents.
J'ai déjà mentionné
le problème des arrondis dans les résultats. Nous avons également déjà vu au
chapitre précédent la fonction arrondi.
Prenons la cellule E2=A2*C2*(1-D2/100).
Pour ne pas avoir d'erreurs, cette fonction devrait inclure l'arrondi de ce
résultat. Nous pourrions utiliser la méthode du chapitre précédent mais voyons
cette possibilité d'une manière plus manuelle.
- La
fonction arrondi est de type: =ARRONDI(nombre
à arrondir;nombre de chiffres)
- Le
calcul qui donne le prix total (réduction incluse) est =A2*C2*(1-D2/100)
- Il
va nous suffire de remplacer dans la fonction arrondi le nombre à arrondir par la formule de prix de vente
Le résultat donne
comme formule =ARRONDI(A2*C2*(1-D2/100);2). Il ne nous
reste plus qu'à recopier cette formule (coin en bas à droite de la cellule) sur
les 20 lignes de la facture.
Nous pourrions le
faire sur la somme des lignes. Ceci ne sert pas à grand chose puisque la somme
de chiffres avec 2 chiffres derrière la virgule maximum donnera toujours au
plus 2 chiffres derrière la virgule. Faisons-le pour le plaisir.
La cellule E22
contient =SOMME(E2:E21)
et la fonction
arrondi est de type =ARRONDI(nombre à
arrondir;nombre de chiffres)
Dans notre cas, nous
souhaitons arrondir la somme. La fonction extérieure est dont la fonction
Arrondi qui reprendra comme argument "nombre à arrondir" le résultat
de la somme. La formule devient:
=ARRONDI(SOMME(E2:E21);2)
Nous pourrions faire
de même avec la TVA et le montant total (à payer) mais nous pouvons nous
contenter de n'afficher que les 2 derniers chiffres si nous n'utilisons pas ces
résultats pour d'autres calculs.
Même si je ne vois
pas beaucoup de fonctions, quelques unes s'utilisent régulièrement. La fonction
SI permet d'exécuter une opération si la condition est remplie et une
autre si cette condition n'est pas remplie.
=SI(Condition;opération
à effectuer si VRAI; Opération à effectuer si FAUX)
Les conditions
possibles sont
|
Supérieur
|
>
|
|
Supérieur ou égal
|
>=
|
|
Inférieur
|
<
|
|
Inférieur ou égale
|
<=
|
|
égal
|
=
|
|
différent
|
<>
|
Les opérantes
peuvent être de tous types. Voici quelques exemples: a1<>A2 - A12>=20
- G15 <= 0 mais peuvent également être de type A2<>"":
le contenu de A2 différent de rien. Attention, si le chiffre 0 est contenu dans
cette cellule, le résultat est vrai. Nous allons utiliser cette possibilité
dans notre facture dans quelques secondes.
L'opération Fausse peut être omise, mais le mot
FAUX sera affiché. VRAI ou FAUX peuvent être de tous type mais opèrent sur la
cellule où se trouve la fonction. Par exemples:
- 10
affichera le chiffre 10 dans la cellule
- "Facture":
affichera le mot facture dans la cellule (remarquez les " ")
- A2+A3
Prenons quelques
exemples de cette fonction:
<A3> =SI(A1<>A2;a1+a2;a1*2)
si le contenu de A1 est différent de celui d'A2, additionner les 2 nombres,
sinon, multiplier le contenu par 2 (j'en convient, le résultat est le même)
<A1> =SI(E32>=0;"Facture";"Note
de Crédit") Si le résultat en E32, est supérieur ou égal à 0, afficher
le mot Facture, sinon, affiche Note de crédit
En imbriquant:
=Si(D10<2;"Recommence
son année";si(d10<5;"Deuxième
session";si(d10<7;"Réussite";"Félicitations")))
Si la moyenne de
l'étudiant est inférieure à 2, l'élève recommence son année, si l'étudiant à
<5 (mais supérieur ou égal à 2 avec la première condition), en deuxième
session, si l'élève est compris entre 5 et 7 non inclus, réussit et si ses
points sont supérieurs ou égal à 7, réussite avec félicitations.
Ces 2 fonctions
donne les valeurs VRAI ou FAUX. C'est justement ce que nous avons utilisé dans
la fonction SI.
ET(condition1;condition2;...)
donne VRAI si toutes les conditions sont rempliées
OU(condition1;condition2;...)
donne VRAI si au moins une condition est remplie
La fonction
maintenant() permet d'afficher la date du jour (celle de l'ordinateur) avec le
jour, mois, année, heure, minute. Elle nécessite néanmoins de modifier le
format de la cellule en format date, ce que nous verrons au chapitre suivant.
Par contre, =aujourd'hui() affiche directement la date (sans les heures).
Reprenons notre
facture modifiée avec les fonctions arrondi() ci-dessus. Le premier
problème est l'affichage des 0 dans la dernière colonne même si la ligne est
vide. Nous allons corriger ce problème. Commençons par le plus simple. Nous
souhaitons afficher les 0 dans la dernière colonne uniquement si une condition
est renseignée. La formule actuelle en E2 est =ARRONDI(A2*C2*(1-D2/100);2).
La condition pour effectuer ce résultat serais que la colonne 1 (quantités de
la ligne correspondante) soit vide. La condition devient:
=SI(A2<>"";calcul;"")
soit en imbriquant les 2 formules: =SI(A2<>"";ARRONDI(A2*C2*(1-D2/100);2);"")
Remplaçons le
contenu de la cellule E2 par cette formule et recopions jusqu'à la ligne 20
incluse et les lignes où il n'y a pas de quantités n'affichent plus de zéro.
Nous pourrions
encore modifier la fonction si pour que le calcul ne se fasse que si la colonne
quantité et la colonne prix soient différents de rien
=SI(ET(A2<>"";C2<>"");ARRONDI(A2*C2*(1-D2/100);2);"")
|
A
retenir: dans la fonction SI, "" permet de faire une condition
sur une case vide ou de n'afficher aucun caractère comme résultat.
|
Notre facture EXCEL est déjà bien en route, il
nous faut maintenant la personnalisée pour notre propre entreprise. Ce sera le
chapitre suivant.
La facture que nous
utilisons ne peut pas encore être utilisée, la mise en page est plutôt
sommaire. Il nous manque les en-têtes, les cases pour délimiter les cellules,
les dates, ... Ce chapitre va nous permettre de mettre en forme notre document.
Le suivant sera l'impression.
Même si nous le verrons
par la suite avec la barre des tâches, l'ensemble des mises en forme de cellule
se trouve dans la commande CELLULE du menu FORMAT
- Nombre
reprend le format du contenu des cellules sélectionnées
- Alignement
reprend le format "paragraphe" des cellules
- Police
reprend la police, couleurs, ... du contenu de la cellule
- Bordure
reprend les encadrements des cellules sélectionnées
- Motifs
reprend les fond des cellules (couleurs et dessins)
- Protection
permet d'empêcher la modification de cellules
Plusieurs
possibilités existent pour la manière qu'affiche le contenu d'une cellule.
STANDARD
est le format standard des cellules d'EXCEL. Le texte est aligné à gauche, les
nombres alignés à droite/
NOMBRE
permet de modifier la présentation des nombres (texte est néanmoins accepté).
Vous pouvez "formater" par ici les nombres négatifs et insérer un
espace automatiquement entre les centaines et les milliers.
MONETAIRE
permet d'insérer le sigle € automatiquement après les chiffres. Pour que le
symbole € soit utilisé par défaut, vous devez modifier le format monétaire dans
les paramètres régionaux du panneau de configuration de Windows.
COMPTABILITE est identique au format monétaire, seul le sigle -
éventuel est mis à gauche.
DATE
permet des formats de jour.
|
A
retenir: Lorsque vous tapez une date dans une cellule, EXCEL intègre
automatiquement le format date par défaut dans la cellule. Pour utiliser
ensuite cette cellule avec un autre format, il faut remodifier le
format
|
HEURE permet
des mises en formes d'heures, minutes et secondes.
POURCENTAGE insère un format de pourcentage (/100) pour des nombres.
|
A
retenir: Lorsque vous utiliser un format de pourcentage, les calculs à
partir de cette cellule utilisent également le pourcentage. Il faut donc
modifier les formules en conséquence
|
TEXTE
modifie le contenu de la cellule pour que le contenu soit vu uniquement comme
du texte, même si c'est un nombre.
L'alignement permet
de modifier le sens du texte dans une cellule. Revers de la médaille, toute la
hauteur de ligne est modifiée en conséquence.
|
|
La fonction "Fusionner
des cellules" permet de rassembler plusieurs cellules en une seule.
Cette fonction est accessible par la barre d'outils. Par contre, pour
défusionner, il faut repasser dans le menu (sauf Excel XP et 2003).
|
L'option Ajuster va, dans le cas
de textes, va diminuer la taille des caractères pour rentrer toute la
phrase dans la cellule. Elle n'a aucune influence sur les chiffres.
Rien de bien spécial
pour cette commande, à part des soulignements doubles, ... La majorité des
commandes est accessible via la barre d'outils.
Remarque, vous ne
pouvez pas utiliser 2 polices différentes dans la même cellule.
- permet
de créer les bordures internes à la plage de cellules sélectionnées ou
externe.
- permet
de sélectionner le type de bordure (y compris les épaisseurs)
- permet
d'insérer des bordures de différentes manières
Vous devez à chaque
fois sélectionner en premier la taille, suivie de la couleur et seulement taper
le contour ou intérieur ou directement un des bord.
Sélection de la
couleur de fond de la plage de cellules sélectionnées ou même motifs suivant
les couleurs en dessous. Pour une impression lisible, seules quelques couleurs
claires peuvent être utilisées si la cellule contient du texte.
La protection des
cellules est un peu spéciale. Elle s'utilise avec la fonction protection du
menu OUTILS. Lorsque vous protégez la feuille, toutes les cellules dont la case
verrouillée est cochée ne pourront être modifiées. Par contre, si une cellule
verrouillée contient une formule, le résultat sera quand même affiché si
vous modifiez les cellules de départ de cette formule. Nous utiliserons
cette fonction pour protéger les formules dans l'exercice ci-dessous
Dans Word,
l'insertion d'images pose de multiples problèmes de mise en page. Par contre,
dans EXCEL, l'image se place au-dessus des cellules. La commande se trouve dans
le menu INSERTION.
|
A
retenir: l'insertion d'une image sous EXCEL peut également se faire par
le presse-papier de Windows: fonctions copier - coller. Le résultat est le
même qu'en utilisant la fonction INSERTION > Images du menu
|
Reprenons notre
facture. Commençons par mettre une bordure autour et à l'intérieur en
sélectionnant la zone de lignes. Nous ferons ensuite une zone pour les totaux.
Pour pouvoir insérer
le logo de l'entreprise, son adresse, les coordonnées du client, numéro et
dates de factures, nous devons insérer des lignes au-dessus. Sélectionnons la
ligne 1 et avec la touche droite de la souris (menu contextuel), utilisons la
commande INSERER. Cette commande doit être répétée 7 - 8 fois ou plus si
nécessaire.
Insérons le logo
comme précédemment et dans les cellules en dessous les coordonnées de notre
entreprise.
Pour le numéro de
facture, nous utiliserons 2 cellules, 1 pour le texte (N° facture) et une pour
le numéro à insérer manuellement (à ce stade). D'autres indications peuvent
être rajoutées.
Pour la date de la
facture, nous utiliserons la fonction =maintenant() ou la fonction
=Aujourdhui(). Ces 2 fonctions dates sont équivalentes, sauf que
maintenant() donne le format en jour/mois/année/date/heure, tandis que
aujourdhui() ne donne que jour/mois/date. L'utilisation de cette fonction passe
par le format > Nombre > Date de la cellule.
Il ne reste plus
qu'à délimiter l'emplacement pour l'adresse du client.
La dernière
opération consiste à protéger les formules. Nous allons donc décocher la case
Verrouillée" pour toutes les cellules que nous pourrons modifier avant de
protéger la feuille.
Il ne nous restera
plus qu'à utiliser la commande Protection -> Protéger la feuille du menu
outils.
Remarque: les cellules protégées contenant des formules sont
mises à jour mais vous ne pouvez pas modifier le contenu.
Dans un logiciel bureautique courant, les
impressions ne posent pas de problèmes particuliers. Par contre, dans Excel,
l'impression doit être préprogrammée: logique puisqu'une feuille EXCEL peut
prendre plusieurs pages. Ceci implique également qu'au stade de notre facture,
l'impression ne donne pas une mise en page correcte: décalage vers la gauche,
tableau trop haut, ...
Le plus souvent,
l'utilisateur augmente la hauteur des lignes et la largeur des colonnes ou même
d'insérer plus de lignes dans la facture. Les possibilités d'impressions
d'Excel vont nous faciliter la vie même si nous utiliserons également ces
possibilités.
Les commandes
d'impressions sont toutes reprises dans le menu FICHIER (à part une qui ne sera
pas vue dans ce tutorial EXCEL).
Mode portrait ou
paysage est connu. Par contre, première commande spécifique, l'échelle. Cette
commande permet de réduire (ou d'agrandir) l'impression sur 1 (ou plus) page(s)
en largeur et en hauteur. Une petite remarque: si votre tableau fait au départ
5 pages en hauteur, le réduire à une page en hauteur réduite également la
hauteur des textes par 5. Le texte devient donc illisible.
L'utilisation des
marges est identique à n'importe quel programme sous Windows. Pour rappel, les
marges ne peuvent pas être inférieures à une dimension spécifique à
l'imprimante (typiquement 1 cm) sous peine de voir une partie du texte non
imprimée.
|
A retenir: La commande de mise en page est valable pour toutes les
feuilles jusqu'à la version 2000 incluse. Dans EXCEL XP et 2003, seulement
pour la feuille en cours.
|
Sélectionnez une
zone de cellule et dans le menu Fichier, utilisez la commande "Zone
d'impression" -> Définir.
A partir de
maintenant, seule la zone sélectionnée sera imprimée (sur cette feuille). Vous
pouvez annuler cette zone d'impression. Ceci permet par exemple de ne pas
imprimer des calculs annexes à coté de votre facture.
|
A retenir: si vous définissez une zone d'impression, lors d'une
impression de la feuille, seule cette zone sera imprimée. Les zones
d'impression sont définie (ou non) pour chaque feuille du classeur.
|
Cette commande permet
d'afficher sur votre écran la (les) feuille(s) comme elle sera effectivement
imprimée.
Cette commande est
presque identique à la commande d'impression d'autres logiciels bureautiques.
Sauf que dans la partie impression, vous pouvez soit:
·
si vous avez préalablement
sélectionner une zone, n'imprimer que la zone sélectionnée.
·
Imprimer les feuilles
sélectionnées (à notre niveau la feuille en cours) suivant la zone d'impression
si elle est définie - sinon toute la feuille.
Nous allons terminer
ce petit tutorial Excel par la mise en page pour impression de notre facture.
Pour rappel, si vous augmentez la taille d'une ligne (ou d'une colonne), toutes
les cellules de la ligne (ou de la colonne) sont modifiées.
Commençons par
augmenter la hauteur de quelques lignes au-dessus du tableau de calcul. Ceci va
nous permettre de descendre le tableau. Pour cela nous devons supprimer la
commande de protection de la feuille dans le menu Outils, nous la remettrons
ensuite.
En insérant une
ligne entre le tableau de calcul et l'en-tête de facture (+ refaire les
bordures) et en sélectionnant la mise en page 1 page en hauteur et une page en
largeur, modifiant le bord gauche à 3 cm, nous obtenons notre facture définitive.
D'autres commandes
(qui seront vues plus tard dans ce tutorial) permettent de rechercher
automatiquement les coordonnées du clients dans une liste, descriptions, prix
et taux de TVA pour chaque produit et même d'obtenir les base de TVA suivant
les taux de TVA de chaque produits. Pour une utilisation de ce type de
fonctionnalité, l'utilisation d'une gestion commerciale est préférable.
Vous pouvez charger
notre facture terminée ici, prête à imprimer, même si nous améliorerons le
calcul TVA plus tard. N'oubliez pas d'ôter la protection de la feuille Excel
pour la modifier à votre convenance (logo et coordonnées), conditions générales
de vente en bas de la facture, ...
La création d'un graphique Excel permet une
visualisation de l'évolution de chiffre (vous vous en doutiez) pour
statistiques. Les nombre de graphiques possibles est important, bien que
seulement quelques uns soient utilisables en pratique. Commencez par remplir
sous Excel un tableau avec l'exemple ci-dessous:
Pour rappel, la somme sur la dernière ligne se
fait à l'aide d'une addition par la fonction dans les barres d'outils. Avant de
commencer, remarquez que la case (cellule) en haut à gauche est restée vide.
Ceci est obligatoire si vous entrez des chiffres (et non des textes) comme
en-tête de lignes et de colonnes.
Sélectionnez
l'ensemble du tableau avec la souris et sélectionnez la commande graphique dans
le menu Insertion. La fenêtre suivante apparaît.
Cette fenêtre
reprend les types de graphiques possibles: à gauche les catégories et chaque
fois à droite les possibilités. Dans notre cas d'histogrammes, chaque ligne
(sauf la dernière) reprend 3 possibilités: chaque ligne à côté l'une de
l'autre, chaque lignes une au-dessus de l'autre et en dernier, par pourcentages
du total. Cliquez sur suivant pour continuer
Excel propose alors
d'organiser les données suivant les lignes ou les colonnes. Dans le cas
ci-dessus, l'organisation se fait par colonnes. Chaque groupe est repris
ensemble avec les mois déterminés par les couleurs des barres. En sélectionnant
ligne, les couleurs des barres seraient "consommables, cartes, …". Si
vous souhaitez sélectionner une autre zone de données, vous pouvez également la
changer ici. Par l'onglet "Série", vous pouvez également donner d'autres
noms aux séries (à condition quelles soient sur la feuille de calcul. Cliquez
sur Suivant.
La fenêtre suivante
offre de multiples possibilités:
- titre
permet d'insérer des titres sur le graphique et les axes. Par exemple:
type de vente en "axe des X".
- Axe
est peu utilisé
- Quadrillage
permet d'afficher plus de quadrillage.
- Légende
est lié aux carré reprenant Janvier, …
- Etiquettes
de données permet d'afficher les montants au-dessus de chaque barre du
graphique. Cette option n'est généralement pas à utiliser.
- Table
de données permet d'afficher les données comme sur notre feuille de
donnée.
Bref, ces options ne
sont pas souvent utilisées parce qu'elles encombrent les graphiques Excel. Elle
réduisent la taille disponible dans la fenêtre réservée au graphique. En
cliquant sur suivant, Excel propose de l'insérer sur cette feuille ou sur une
nouvelle feuille.
Notre graphique
terminé (sur la même feuille), intéressons-nous maintenant aux diverses
possibilités de ces graphiques.
Commençons par
modifier des valeurs dans notre table de données. En appuyant sur la touche
<Entrée>, le graphique est automatiquement mis à jour.
Cliquez sur le graphique avec la touche droite de la
souris et vous obtenez le menu contextuel ci-dessus. Sinon, cliquez en dehors
de la zone (pour le désélectionner) et recliquez avec la touche droite de la
souris.
Format de
la zone
permet de modifier la couleur de fond, les couleurs et types de bordures, …
Type de
graphique permet de revenir à un autre type de graphique Excel. Prenons par
exemple ci-dessus.
Ce type de graphique est le plus couramment utilisé. Dans
ce cas, vous pouvez même modifier les valeurs de la table en modifiant des
données du graphique (sur les points représentant les valeurs. En cliquant 2
fois sur une des lignes, vous pouvez modifier la couleur d'une série. C'était
également possible avec les barre graphe ci-dessus.
Données
sources
permet de resélectionner les cellules à utiliser.
Options du
graphique reprend les titres, axes, quadrillage, …
Emplacement permet d'insérer le
graphique sur une nouvelle feuille.
Les autres commandes
sont soit inutiles (ou presque), soient déjà utilisées.
Si vous utilisez un
graphique, vous verrez que chaque fois que vous sélectionnez quelque chose et
cliquez 2 fois dessus, vous pouvez modifier la présentation de chaque parties.
Ceci dépasse le cadre de ces notes, il suffit d'essayer.
Une dernière chose.
Nous avons vus que si vous modifiez les données de la table, le graphique est
modifié. Ceci est parfois gênant pour garder un graphique à un moment donné.
Une solution est de créer une copie de la feuille. Cliquez sur feuille 1 avec
la touche droite de la souris et sélectionnez la commande "Déplacer ou
copier". Cochez la case "Créer une copie". Votre feuille ne
bougera plus tant que vous ne modifierez pas les données sur la copie.
Si vous désirez
insérer votre graphique sur Word. Sélectionnez-le et utilisez la fonction
"copier" sous Excel et "Coller" sous Word. Les graphiques
se comportent comme des objets. Cliquez 1 fois pour le déplacer et 2 fois pour
le modifier. En supprimant la liaison (menu Edition, aucune modifications des
chiffres dans Excel ne modifiera votre graph. sous Word).
Reprenons notre modèle de facture. Quoique
intéressante pour la majorité des entreprises, certains utilisent des taux de
TVA différents d'un produits à l'autre. Cette partie va nous permettre de
dissocier les taux de TVA par ligne et de recalculer les bases et TVA suivant
les codes repris sur chaque ligne. La fonction Excel utilisée est la
fonction conditionnelle somme.si()
Sur notre facture,
supprimons la protection (Menu outils -> Protection) et insérons une colonne
entre "réduction" et "total"
Ceci va nous obliger
à reprendre la mise en forme des colonnes supérieures, et même de modifier la
mise en page de notre facture éventuellement. Comme en-tête de colonne dans la
partie insérée, tapons "Taux TVA". Les lignes en-dessous reprendrons
les taux de TVA de chaque produits.
Dans la partie
en-dessous (pied de facture), tapons: base 0%, base 6%, base 12% et base 21%
(Belgique)Les utilisateurs adapteront pour les taux de TVA en vigueur en France
et au Luxembourg. La colonne adjacente nous permettra de mettre la formule
Excel adéquate.
La fonction Excel
Somme.si effectue une somme en vérifiant un critère sur la ligne équivalente.
=Somme.si(plage
de tests;test à effectuer;éléments à additionner).
En plus claire, si
la cellule dans la plage de tests est égale au test à effectuer, l'élément en
regard dans la plage à additionner sera évaluée.
Après avoir
positionner le curseur en regard de la case "Base 0%", insérons la
fonction Somme.si par la commande Menu Insertion ->Fonctions
Comme plage,
sélectionnons la zone E14 à E23 (la zone où nous rentrons les taux de TVA),
comme critère 0 (puisque nous gérons dans cette case la base 0 %) et comme
somme plage la zone F14;F33 (la zone de total par ligne). Reprenons cette
fonction chaque fois en regard des cellules bases de TVA en changeant
uniquement le taux de TVA comme critère. Par exemple, pour le taux à 6 %, la
formule en B36 devient: =SOMME.SI(E14:E33;6;F14:F33); Et voici le tour est
joué.
Un classeur Excel est la dénomination du fichier
Excel en lui-même. Chaque classeur peut inclure plusieurs feuilles. Lorsque
vous démarrez un nouveau classeur, EXCEL crée automatiquement un certain nombre
de feuilles variables suivant les options du menu outils. Chaque feuille Excel
est individuelle. Néanmoins, vous pouvez transférer des données d'une feuille à
l'autre. De même, Excel offre la possibilité de récupérer des données d'un
classeur à l'autre. C'est l'objet de ce chapitre de la formation.
La gestion des
feuilles Excel est reprises dans différents menus. Pour la facilité, nous
allons utiliser le menu contextuel. Je reprend 2 versions de la gestion des
feuilles puisque de grosses modifications sont intervenues entre Excel 2000 et
versions inférieures et Excel XP, 2003 et 2007 d'autre part.
Pour passer d'une
feuille à l'autre, cliquez simplement sur la feuille.
|
|
|
|
Menu contextuel des feuilles EXCEL 2000 et
inférieurs
|
Menu contextuel des feuilles Excel version XP, 2003
et 2007
|
Cliquez avec la touche droite de la souris sur
une des feuilles en bas de l'écran. Un menu contextuel spécifique est affiché.
Cette commande permet
d'insérer une nouvelle feuille, à gauche de la feuille sélectionnée. D'autres
possibilités sont également proposées, mais dans ce cours, nous nous limiterons
aux feuilles. Excel incrémente automatiquement le nom de la feuille en feuil4,
...
Cette commande ne
pose pas de problème particulier, elle supprime la feuille en cours: son
contenu est définitivement supprimé.
Pour la gestion de
votre classeur, un nom distinct pour chaque feuille est préférable. Cette
fonction permet de donner un nouveau nom pour la feuille sélectionnée. Le nom
doit être unique pour le classeur.
Une autre possibilité
est de double cliquer sur le nom de la feuille.
Même si la commande
du menu contextuel peut être utilisée, il est plus facile de sélectionner la
feuille et de la déplacer de à gauche ou à droite en maintenant la
souris enfoncée. Un petit triangle permet de déterminer l'emplacement futur de
la feuille.
L'autre possibilité (copier)
est nettement plus intéressante puisque non seulement elle copie la feuille
sélectionnée (cochez la petite croix), mais elle garde les données, formules et
formats de celle de départ. Bref, la feuille copiée est identique à celle de
départ. Il ne reste plus qu'à renommer et déplacer la nouvelle feuille avant de
supprimer les données pour reprendre uniquement les formules par exemple.
Cette fonction Excel
permet également de transférer ou copier une feuille Excel d'un classeur à
l'autre.
Excel offre la
possibilité de sélectionner toutes les feuilles, commandes du menu contextuel,
ou même certaines feuilles. Cette possibilité est utilisée dans 2 cas distincts.
La première est liée aux
impressions. Vous pouvez de cette manière n'imprimer que certaines feuilles
La deuxième
possibilité permet de remplir une cellule (données, formules, format de
cellule) sur une feuille tout en modifiant toutes les feuilles sélectionnées.
Cette fonctionnalité est à utiliser avec précaution, puisque aucun message ne
signale si des données sur les autres feuilles sélectionnées sont écrasées.
Pour sélectionner
toutes les feuilles, utilisez la commande du menu contextuel. Par contre, pour
ne sélectionner que quelques feuilles, sélectionnez les feuilles souhaitées en
maintenant la touche <CTRL> enfoncée.
Une large différence
entre les classeurs 2000 et versions inférieures et les classeurs Excel XP et
2003. En Excel 2000, le nom du classeur actif est en gras, les autres feuilles
sélectionnées en fond blanc, pas en version XP et 2003.
Pour désélectionner
les feuilles, vous pouvez utiliser le menu contextuel, ou simplement cliquer
sur une autre feuille.
Si vous n'avez que
quelques feuilles, vous pouvez directement cliquer sur la feuille en bas. Par
contre, avec plus de feuilles Excel, vous devez d'abord vous déplacer pour
visualiser la feuille souhaitée, ensuite cliquer sur la feuille.
Cette fonction
existe depuis la version XP. Elle n'est pas présente dans les versions
inférieures d'Excel. Elle permet de sélectionner une couleur de fond pour
l'onglet de chaque feuille.
La nom de la feuille
utilisée est soulignée dans le couleur choisie (dans l'exemple ci contre, en
rouge), les onglets des feuilles non sélectionnées sont remplis par la couleur
choisie.
Si vous sélectionnez
plusieurs cellules en même temps (sélection avec la touche control enfoncée),
c'est la seule méthode de visualiser les feuilles sélectionnées en Excel XP et
2003. Dans les versions inférieures, les noms des feuilles sélectionnées sont
en gras.
La méthode la plus
simple pour reprendre des valeurs d'une feuille à l'autre est d'utiliser la
simple formule =ref-cellule.
Pour assurer la
liaison, positionnons le curseur sur la feuille et la cellule où nous
souhaitons insérer le résultat et tapons =. Cliquons ensuite en bas sur la
feuille ou se trouve la donnée de départ et directement sur la cellule où se
trouve ce chiffre. Excel modifie la référence de la cellule par feuil1!A3 dans
notre cas. Il nous suffit de terminer notre fonction en appuyant su la touche
<entrée>
Si vous renommez la feuille, les formules sont
automatiquement modifiées.
Pour une formule reprenant des données d'une autre
feuille, la technique est identique. Reprenons notre feuille 2 et utilisons par
exemple la formule =somme(). Passons à la feuille 1 par la méthode précédente
et sélectionnons les chiffres souhaités.
Excel modifie
automatiquement la référence des cellules par Feuil1!A1:A2
Excel permet
également de reprendre des données d'un classeur (fichier Excel) à l'autre.
Créons un nouveau fichier Excel. Dans une cellule de ce nouveau classeur,
tapons = et dans la barre d'outils Windows, sélectionnons le classeur
précédant.
Dans le classeur,
sélectionnons la feuille, suivi de la cellule souhaitée.
Excel reprend
automatiquement le nom du classeur, suivi du nom de la feuille et de la
référence de la cellule. L'utilisation de formules est identique. Si nous
enregistrons notre classeur1, la référence de la cellule est automatiquement
modifiée. Si nous fermons ce classeur, le nom du classeur est remplacé par
l'emplacement sur le disque dur et le nom du classeur.
Attention à ne pas déplacer le fichier sur le
disque dur.
Si vous fermez les 2
classeurs et ré ouvrez le classeur contenant la formule, la fenêtre suivante
apparaît permettant de mettre à jour la liaison. En effet, le fichier contenant
les données de base à pu être modifié directement.
- Mettre
à jour la liaison: permet de vérifier si les données du classeur de
départ n'ont pas été modifiées et, le cas échéant, de récupérer les
nouvelles valeurs de la liaison.
- Ne
pas mettre à jour: garde les anciennes valeurs. Ceci ne coupe
néanmoins pas la liaison entre les classeurs.
Cette fonction
n'oblige pas à ouvrir le classeur qui contient les données de départ. Excel va
automatiquement les récupérer sur le disque dur.
Cette commande n'est
accessible dans le menu édition que si vous avez effectivement créer une
liaison vers un autre classeur dans le classeur Excel en cours.
|
La
commande liaison du menu Edition.
|
|
La liaison va
permettre différentes manipulations sur la liaison en cours.
- Mettre
à jour les valeurs permet de recréer une liaison, notamment si vous
n'avez pas mis à jour lors de l'ouverture du document. De toute
façon, si les 2 classeurs sont ouverts sur le même ordinateur, la
modification est automatiquement répertoriée.
- Modifier
la source permet de sélectionner un autre classeur pour la source de
donnée. Ceci est utile en cas de déplacement des classeurs. Cette option
ne permet pas de sélectionner la feuille. Si le nom de la feuille est
faux, vous devez modifier manuellement la formule dans la feuille de
départ.
- Ouvrir
la source permet d'ouvrir le classeur qui contient les données de
départ.
- Rompre
la liaison supprime le lien entre les 2 classeurs. Les formules sont
automatiquement remplacées par les valeurs des cellules du classeur de
départ.
- Vérifier
l'état: vérifie si la liaison est toujours présente.
- Invite
de démarrage: permet de spécifier quelques options sur l'ouverture des
liaisons:
- Laisser
les utilisateurs choisir d'afficher ou non l'alerte permet d'afficher la
fenêtre au démarrage du classeur en cas de liaisons.
- Ne
pas afficher l'alerte et ne pas mettre à jour les liens automatiques ne
pose aucune question, ni ne met à jour les données. La touche F9
permet de recréer la mise à jour.
- Ne
pas afficher l'alerte et mettre à jour les liens ne pose pas de
questions, met automatiquement la liaison à jour.
Même si Excel n'a
pas les mêmes possibilités que la base de donnée Access pour trier et filtrer
les enregistrements d'une liste, les fonctions implantées suffiront à la
majorité des utilisateurs. Cette fonctionnalité va nous permettre plus tard de
reprendre nos clients et produits directement dans notre facture par un simple
code.
Lorsque vous créez
un classeur Excel, 3 feuilles sont automatiquement créées (ceci est modifiable
dans les options). Pour passer d'une feuille à l'autre, cliquez simplement sur
la feuille en bas de votre écran.
Si votre classeur
Excel ne comporte qu'une feuille, cliquez avec la touche droite de votre souris
sur la feuille et sélectionnez la commande insérer. Une nouvelle feuille est
ainsi créée. Elle est indépendant de la feuille précédente, même si nous
pouvons faire passer des données d'une à l'autre. Le transfert des données
entre les feuilles et classeurs, manipulations, ... a été vu complètement dans
le chapitre: gestion des feuilles et classeurs
Commençons par créer
notre liste de clients sous Excel. Cette liste d'adresse doit reprendre les
coordonnées complètes du client. Un code client nous permettra de le relier à
notre facture Excel dans un exercice futur. Comme nous utiliserons également
cette partie en combinaison avec Word pour un publipostage, Outlook pour
récupérer comme carnet d'adresse et même Access, quelques champs (colonnes)
seront également rajoutés à titre indicatif
La structure de
notre liste va reprendre:
- Code
client: nécessaire pour lier la liste avec la facture Excel
- Entreprise
- Type:
Monsieur, Madame, ... nécessaire en cas de publipostage
- Nom
- Prénom
- Adresse:
rue, numéro de maison
- Code
Postal: pour publipostage, mais va également délimiter les informations
- Ville
- Pays
- Code
TVA
- et
quelques renseignements complémentaires éventuellement.
Nous allons rentrer
quelques adresses de clients en respectant à chaque fois les bonnes colonnes.
Le code doit être unique pour chaque client. Toutes les données ne doivent pas
forcément être rentrées. Par exemple, une société ne comportera pas le titre,
nom, prénom de la personne et inversement, une personne n'aura pas de nom
d'entreprise.
Une liste similaire
pourrait également servir pour un calcul d'inventaire.
Dans le menu
Données, sélectionnons la commande TRIER.
Le tri peut se faire
suivant 3 critères, laissés au libre choix. La première question est Ligne de
Titre (oui ou non). Cette notion est importante puisque q'en cas de ligne de
titre, la première ligne ne sera pas trier. Généralement, Excel le propose par
défaut, mais mieux vaut vérifier le cas échéant. Au contraire, si vous n'avez
pas créé d'en-tête, vous devez cocher la case Non.
Le premier tri va
servir de tri de départ. Les 2 suivants sont facultatifs. Dans notre exemple, le
tri se fera par le code de l'entreprise. Dans le cas où 2 sociétés
utiliseraient le même code, suivant le nom de l'entreprise, suivi du nom, ....
Le bouton option
permet dans certains cas de trier suivant les jours ou les mois. Dans ce cas,
une colonne n'utilisant pas ces listes n'en tiendra pas compte.
Il ne nous reste
plus qu'à accepter le tri.
Dans certains cas,
les tris Excel peuvent poser quelques problèmes. Insérions une colonne vide au
milieu de notre liste et exécutons un tri. Vous remarquez qu'Excel ne tient pas
compte des colonnes après la colonne vide pour effectuer le tri. Il faudra sélectionner
manuellement l'ensemble des colonnes pour que le tri tienne compte des
colonnes supplémentaires.
Supprimez cette
colonne vide pour que tout rentre dans l'ordre est également une solution..
Si vous sélectionnez
une colonne, Excel propose d'étendre la sélection. Cette possibilité permet de
sélectionner toutes les colonnes pour le tri. Dans le cas contraire, seule la
colonne sélectionnée sera utilisée. Les ;données par lignes ne seront donc plus
en rapport. Sauf par la commande annuler, cette opération sera irréversible.
Le filtrage des
enregistrements va nous permettre de n'afficher que quelques lignes de notre
liste de client. Dans le menu Données, sélectionnons la commande
"Filtre", suivi de l'option "Filtre automatique".
Cette commande va
modifier la présentation de notre feuille Excel
Pour chaque colonne
de notre liste, nous pouvons maintenant sélectionner tous, soit les 10
premiers, soit 1 valeur.
Dans le cas d'un
filtre Excel personnalisé, nous allons pouvoir utiliser des critères plus
complexes sur la valeur de la cellule. Vous pouvez même utiliser 2 critères
différents en utilisant les commandes ET (toutes les conditions doivent être
remplies) ou OU (au moins 1 des critère doit être rempli).
Si un filtrage est
effectué sur une colonne, la flèche est mise en bleu. Les filtres sont
cumulatifs. Pour supprimer le filtrage, utilisez de nouveau la commande filtre
automatique du menu Données. La commande Filtre: Afficher tout du menu permet
de supprimer les filtres effectués, mais garde la possibilité de filtre
automatique. Par cette fonctionnalité, vous pouvez également trier les
enregistrements. Le filtre Excel n'affiche que les enregistrements filtrés mais
trie l'ensemble de la plage de donnée.
Est-il possible de
conserver des données triées pour un usage ultérieur? Oui, le truc est de
filtrer selon les critères souhaités, de sélectionner la plage et de coller le
résultat sur une autre feuille.
Ce chapitre du
tutorial sur Microsoft Excel va reprendre différentes notions difficiles à
caser dans des chapitres distincts. Il est néanmoins important dans toute
formation Excel. Nous allons commencer par les références absolues et relatives
des cellules pour continuer avec les différences fonctions copier - coller
spécifiques à Excel.
Nous avons recopié
des contenus de cellules dès le premier chapitre de notre tutorial Excel.
Automatiquement, Excel met les références des cellules dans les formules à
jour. Cette méthode de travail est appelé "référence relative".
Malheureusement, cette méthode de travail pose quelques fois différents
problèmes.
Prenons un exemple,
nous plaçons une somme d'argent sur un livret dans la ferme intention de ne
retirer l'argent que dans quelques années et souhaitons connaître le montant
que nous pourrons retirer au bout de ce laps de temps (capital et intérêts
cumulés).
Tapons les valeurs
suivantes dans notre feuille Excel.
Pour connaître le
montant disponible à la fin de la deuxième année, nous devons rentrer la
formule suivante en B3: = capital + intérêt, soit =B2+B2*C2. Le résultat nous
donne effectivement 102 €. Pour la deuxième année, nous allons recopier la
formule vers le bas.
Le résultat est
faux, tout simplement parce que Excel a adapté la formule de calcul d'intérêt
vers le bas. Par exemple, la formule en B3 (soit la deuxième année) est devenue
=B3+B3*C3. La formule reprend bien le capital de l'année précédente, mais le
taux d'intérêt est maintenant repris en C3, soit 0. Même si une solution serait
de recopier le taux d'intérêt vers le bas, nous allons utiliser une référence
absolue dans notre fonction Excel en B3. L'utilisation de référence absolue
ne modifie pas la référence de la cellule lorsque nous recopions la fonction.
Pour signaler au
tableur Excel que nous souhaitons des valeurs absolues au lieu de référence
relatives, nous allons utiliser le caractère $ devant la référence de la
cellule dans la fonction
|
$ devant la lettre de la
cellule (colonne) ne modifie pas la
référence de la colonne si vous recopiez la fonction d'une colonne à l'autre.
$ devant le numéro de la ligne ne modifie pas le numéro de la ligne lorsque vous
recopiez la fonction vers le haut ou vers le bas.
$ devant la lettre de la colonne et le
numéro de la ligne ne modifie jamais
la référence de la cellule quel que soit le sens pour recopier la fonction.
|
Dans notre cas, nous
descendons chaque fois la référence de la cellule contenant le taux d'intérêt
d'une ligne. La solution est donc de modifier la fonction en B3 qui devient
=B2+B2*C$2
Nous verrons de
nombreux exemples de cette fonctionnalité par la suite.
Dans de nombreux
cas, nous devons modifier une fonction existante pour la faire passer de
relatif à absolu. La solution est d'utiliser la barre de formule d'Excel pour
modifier directement la fonction.
Positionnez votre
curseur directement dans la barre de formule sur la référence de cellule à
modifier et appuyez sur la touche F4. Excel modifie automatiquement la
référence de la cellule de relatif en absolu.
Vous connaissez tous
les fonctionnalités du presse papier. Cette fonction implantée directement dans
Windows permet de copier une sélection vers le presse papier et de le copier à
un autre endroit (y compris entre 2 logiciels différents). Simple dans la
majorité des cas, le tableur de Microsoft gère le copier-coller d'une manière
différente. La version Excel 2003 a encore compliqué son utilisation par un presse
papier office qui accepte jusque 24 niveaux contre 1 pour le presse papier
standard. Il fait partie du volet office (CTRL + F1 pour rappel).
Commençons justement
par ce presse papier office. Si vous copier une cellule, le presse papier
office d'Excel 2003 insère automatiquement le contenu. Petite nuance néanmoins,
si la cellule de départ contient une formule, Excel ne copie que le résultat
(pas la formule).
Par contre, le
presse papier standard copie lui la formule. Copions une des cellules contenant
une formule ci-dessus:
Plaçons le curseur
dans une autre cellule et utilisons la fonction coller. De nouveau, Excel
travaille en référence relative et adapte les références des cellules
contenues dans la fonction. Vous pouvez coller autant de fois que vous voulez
dans le classeur excel tant que vous ne modifiez pas le contenu d'une cellule
(quelle qu'elle soit). Pour vous signaler que le presse papier est actif, la
cellule contenant la formule de départ est entourée d'un cadre pointillé
clignotant.
Néanmoins, nous
souhaitons travailler en référence absolue lors du copier-coller. Nous
pourrions modifier la formule comme ci-dessus (en utilisant la touche F4 par
exemple). Une autre méthode existe, quoiqu'un peu plus compliquée. Positionnez
le curseur dans la cellule de départ et sélectionnez le contenu dans la barre
de formule:
Vous venez de copier
non pas le contenu de la cellule, mais la formule contenue dans la cellule
(nuance). Appuyez sur la touche entrée ou <Echap> pour quitter le mode
modification. Positionnez le curseur à l'endroit désiré et utilisez la fonction
coller. Le tour est joué, Excel n'adapte plus la fonction dans la nouvelle
cellule.
Pour déplacer le
contenu d'une cellule vers une autre, 2 manières sont possibles. La première va
utiliser le presse papier et la fonction COUPER - COLLER. Cette fonction ne
marche qu'une fois. Le simple fait de coller et le presse papier Excel est
vidé. Par contre, la référence des cellules n'est pas modifié cette fois.
L'autre méthode va
utilisé le glisser-déplacé (à partir d'Excel 2000). Sélectionnez une cellule ou
un groupe de cellule. Positionnez le curseur en bas de la zone sélectionnée.
Une flèche apparaît, il suffit maintenant de déplacer le curseur de la souris
en maintenant le bouton gauche de la souris enfoncée pour déplacer le contenu
sans modifier les références des cellules déplacées. Cette fonctionnalité est
identique au couper - coller.
Les tables permettent
d'automatiser certains calculs, elles permettent de comparer par exemple des
prix de revient en fonction d'une ou 2 variables. Elles sont souvent utilisées
dans les fonctions financières ou pour des calculs de rentabilité, notamment en
utilisation avec des graphiques.
Supposons la
fabrication d'un ordinateur. Nous souhaitons calculer le prix de vente. Pour
cela, nous allons créer un tableau reprenant les composants, prix et quantités
(les prix sont fictifs).
La formule dans les
cellules D2 à D10 reprend le prix multiplié par la quantité (ex: D2=B2*C2
). La formule en D11 reprend la somme des cellules au-dessus soit
=somme(D2:D10). La cellule D12 reprend le prix TVAc. Jusqu'ici, rien de
particulier. Nous souhaitons vérifier les modifications du prix de vente en
fonction d'un prix d'un composant (par exemple du disque dur). Nous pourrions
créer plusieurs tableaux identiques en modifiant chaque fois le prix du disque
dur mais les tables Excel vont nous permettre d'automatiser cette fonction.
Commençons par
rentrer les différents prix en fonction de la quantité
Pour vérifier le prix
de vente hors TVA de l'ordinateur, recréons la formule de D11 en B14
(=somme(d2:d10). Nous ne pouvons pas la copier par le presse papier puisque
qu'elle s'adapterait. Par contre, nous pourrions la déplacer (en utilisant la
double flèche sous la cellule).
|
A retenir: Dans une table
rentrée en colonne, la formule se place toujours en haut à droite de la
colonne de chiffres à analyser.
|
En sélectionnant la
plage de cellules A14 à B19(où se trouve notre formule et les différentes
valeurs possibles), sélectionnons la commande table du menu données. Excel fait
apparaître la fenêtre suivante:
Comme nos données
sont rentrées en colonne, nous devons saisir la valeur qui va être modifiée
(dans notre cas le prix du disque dur) dans la cellule d'entrée en colonne,
soit D7. Excel va automatiquement remplir les cellules C15 à C19 avec le
résultat en fonction du prix effectif du composant.
Si nous modifions le
prix d'un autre composant, la table sera également modifiée.
Nous pouvons
également créer plusieurs formules, par exemple TVA comprise. Il suffit de
rentrer chaque fois les formules en haut à droite à la suite les unes des
autres et de reprendre la zone avant de créer notre table.
Les tables peuvent également
être rentrées en ligne. Dans ce cas, la (les) formule(s) doivent être rentrées
en dessous à gauche comme dans l'exemple ci-dessous.
|
A retenir: Dans une table
rentrée en ligne, la formule se place toujours en bas à gauche de la ligne de
chiffres à analyser.
|
Excel permet également de
créer des tables avec une double entrée (2 valeur modifiées), mais avec une
seule formule. La fonction doit être insérée à l'intersection des données en
ligne et colonne. Prenons comme exemple le tableau reprenant le prix de revient
TVA comprise en fonction du prix des disques durs et de la quantité de main
d'oeuvre:
|
A retenir: Dans une table
rentrée en ligne colonne, la formule se place toujours à gauche de la ligne
de chiffres à analyser et en haut de la colonne à analyser.
|
Pour supprimer ou
modifier une table Excel existante, vous devez supprimer l'entièreté du contenu
des cellules créer par la table.
Excel regroupe des fonctions de plusieurs types.
Ce chapitre reprend une partie des fonctions financières: emprunts, taux
d'intérêt, amortissement. Sans les voire toutes, intéressons-nous aux
principales.
Avant d'attaquer la
création d'une fonction financière sous Excel, une petite introduction sur les
arguments (les paramètres des fonctions). Les arguments des fonctions sont
souvent notées comme suit:
VA: Valeur actuelle, montant de départ
- Elle
est égale à 0 pour des économies
- Positive
pour un placement
- Négative
pour un prêt
Taux:
taux d'intérêt.
NPM: nombre total de périodes.
- Si
le remboursement est mensuel, le nombre de périodes correspond au nombre
de mois
- Si
le remboursement est trimestriel: nombre de trimestre
- Si
le remboursement (amortissement) est annuel, nombre d'années
VPM: montant périodique
- du
remboursement (prêt)
- du
dépôt (économie, livret)
- du
retrait (placement)
VC: valeur future. Elle est égale à 0 sur un prêt, valeur résiduelle pour
un leasing
Type: date d'échéance des payements (début ou fin de mois). Les emprunts sont généralement
remboursable fin de mois. Certains crédits hypothécaires sont remboursables
début de mois.
Remarque: TAUX, NPM
et VPM doivent être calculés sur la même période (mensuel, trimestriel,
annuel).
Les types d'amortissements
peuvent être:
- amortissement
linéaire: le montant de l'amortissement est égale pendant chaque année
de la durée
- amortissement
dégressif: le montant varie suivant l'année d'amortissement.
L'amortissement de départ étant plus important que l'amortissement final
(fin de vie du bien d'investissement).
- Amortissement
dégressif à taux double: le montant varie suivant l'année
d'amortissement. L'amortissement de départ étant plus important que
l'amortissement final (fin de vie du bien d'investissement). C'est une
variante de l'amortissement dégressif.
|
La formule de calcul des
amortissement linéaire est: =amorlin(valeur achat;valeur résiduelle;durée
de vie)
|
Dans quelques cas
particuliers, la valeur résiduelle n'est pas égale à ZERO (cas du leasing par
exemple même si le taux est plus souvent calculé en amortissement dégressif
dans ce cas).
Dans les amortissements
dégressif, le montant de l'amortissement n'est pas fixe. Le montant de
l'amortissement doit donc se calculer année par année. Attention, la formule
Excel n'accepte pas une valeur résiduelle à ZERO.
|
Amortissement dégressif à taux
fixe: =DB(Valeur achat;valeur résiduelle;durée en année;année du
calcul;mois de la période)
|
Cette fonction
financière appelle plusieurs remarques.
- Le
coût est la valeur d'achat du bien
- Valeur
résiduelle, la valeur en fin d'amortissement du bien. Pour la fonction
DB, elle ne peut être nulle, sinon, le résultat est faux.
- La
durée est exprimée en année
- La
période est l'année de calcul de l'amortissement
- Le
mois est facultatif (sinon, fin d'année). Ceci est nécessaire si
l'amortissement de la première année ne démarre pas le premier janvier.
Pour le calcul avec
un mois, il suffit de modifier la dernière variable.
Par exemple, nous
pouvons modifier notre formule pour calculer chaque mois comme ci-dessus. Pour
rappel, les $ devant une référence de colonne ou de ligne permette que lors de
la recopie de cellule, la référence ne soit pas modifié.
Attention, les
chiffres mensuels d'amortissent sont faux dès la deuxième années.
Ce type
d'amortissement est une variante de l'amortissement dégressif à taux fixe. Le
taux peut-être double ou supérieur. Ce type d'amortissement suivant la formule
d'Excel est automatiquement annuel.
La valeur de
l'amortissement de la première année est légèrement supérieure à celle d'un
amortissement dégressif à taux fixe.
Attention, selon
la valeur résiduelle, les résultats d'amortissements dégressifs doubles sont
corrects ou faux pour la fonction financière DDB. Les résultats cohérents sont obtenus avec une valeur
résiduelle d'environ 10% de la valeur d'achat.
Prenons maintenant
les calculs financiers d'épargne (livret par exemple).
Supposons que je
dépose 1000 € sur un livret pendant 3 ans. Le calcul du montant épargné
(montant déposé au départ + intérêt) se calcule par les règles de référence
absolue des formules excel.
Dans le cas, où vous
n'utilisez pas la référence absolue pour désigner le taux d'intérêt, le calcul
sera faux dès la deuxième année (le taux d'intérêt sera 0).
Supposons que nous
déposons en banque un même montant chaque début de mois. La formule suivante va
nous permettre de déterminer le montant sur le compte après une période donnée.
Remarque, les taux
des livrets sont donnés annuellement. Comme nous déposons de l'argent tous les
mois, la formule doit tenir compte des mois. De plus, la formule VC est
également utilisée en remboursement d'emprunt, le VPM (montant périodique doit
être mis en négatif
|
Calcul d'épargne: =VC(Taux
d'épargne;nombre d'épargne;montant déposé par périodes)
|
Nous verrons les
autres paramètres de cette fonction Excel dans les placements. VA peut par
exemple être utilisé si de l'argent est actuellement déposé sur le compte (avec
le signe négatif). Dans notre cas, comme le montant des intérêts est toujours
calculé en fin de mois, la valeur peut-être omise.
- Supposons
que nous avons déjà 1000 € sur notre compte d'épargne, la formule devient:
=VC(B1/12;B2*12;-B3;1000)
- Si
la durée n'est pas de 3 ans, mais de 18 mois par exemple, la formule
devient: =VC(B1/12;18;-B3)
- et
ainsi de suite. Cette formule ne fonctionne pas pour des durées en jours
Analysons maintenant
les emprunts bancaires.
|
remboursement: =VPM(taux
remboursement;Nombre de remboursements;Valeur de départ;montant final après
le dernier remboursement;type de remboursement)
|
- Taux
remboursement: taux périodique de l'emprunt. Il est généralement donné
annuellement, si les remboursements sont mensuels, il doit être divisé par
12.
- Nombre
de remboursements, typiquement le nombre d'années multiplié par 12
- Valeur
de départ: capital emprunté
- Valeur
finale: valeur résiduelle après le dernier remboursement, typiquement 0
- Type:
1 si payement en début de période (généralement les emprunts
hypothécaires), 0 sinon (valeur par défaut)
Les 2 derniers
paramètres peuvent être omis.
Pour un emprunt
"normal"
Pour un emprunt
hypothécaire, comme le capital est généralement remboursé en début de période,
le montant est légèrement inférieur.
Dans le cas d'un
emprunt hypothécaire avec remboursement trimestriel: le taux n'est plus
multiplié que par 4 (et le nombre de période multiplié par 4). Comme le
remboursement est anticipatif, le type est 1.
Une autre formule
financière permet de calculer la durée d'un remboursement lorsque le montant
est fixe, mais la durée inconnue.
|
durée d'un emprunt:
=NPM(taux;montant du remboursement périodique;valeur de l'emprunt;valeur
finale;type)
|
Comme pour la formule
VPM, l'argument VC (valeur restant en fin de période) est généralement de 0. Le
type est également de 0 (ou omis) si le remboursement se fin en fin de mois, 1
si le remboursement se fait en début de mois.
Excel permet de travailler sur de grandes
quantités de données en listes (clients, factures, ...). Même si ses
fonctionnalités sont relativement restreintes par rapport à une base de donnée
de type Access (notamment la limitation à 65.536 lignes et 256 colonnes), elles
sont suffisantes pour la majorité des utilisateurs professionnels.
Dans les précédents
chapitres, nous avons déjà vu quelques méthodes de travail EXCEL sur des listes
de données, notamment le tri et le filtre automatique. Ces 2 fonctions
permettent déjà une certaine analyse des données mais sont encore relativement
faible. Ce chapitre va nous permettre de faire des analyses plus approfondies,
quoique perfectibles en regroupant des données et en créant des calculs par
regroupement.
Commençons par
créer une petite liste de donnée comme celle ci-dessous
Ceci est une liste
de facture reprenant le nom du client, le numéro de facture, la famille de
produit, sa description et le prix. Nous souhaitons faire quelques statistiques
sur cette liste, comme le montant des ventes en ordinateur, ou la somme des
ventes sur une famille donnée, en gros créer des sous-totaux, par famille, type
de produits informatiques, ... Cette fonctionnalité est liée aux sous-totaux
Excel.
La première commande
à exécuter pour créer ces sous-totaux est de trier suivant le type de
regroupement que nous souhaitons par la fonction tri du menu Données. Le choix
du tri va dépendre des statistiques que nous souhaitons afficher.
Supposons que nous
souhaitons faire des regroupements sur les clients, le tri devra se faire
suivant le nom du client.
Dans le menu Données
d'Excel, sélectionnons la commande sous-totaux
A chaque
changement de sélectionne la colonne qui
va servir à regrouper les données (d'où l'importance de trier au préalable). La
fonction représente le calcul que nous souhaitons exécuter à chaque changement
de client suivant 11 fonctions possibles (somme, moyenne, nombre de valeurs,
...). Le choix doit être identique pour toutes les colonnes.
Le dernier choix
permet de sélectionner la ou les colonnes sur les quelles nous souhaitons les
calculs. Dans notre liste, seul le montant est réellement calculable.
Le résulta obtenu
affiche une somme totale pour chaque client (encadré en vert dans l'exemple
ci-dessus) et en rouge pour le total des factures.
Nous pouvons
également réduire l'affichage pour ne garder que le total pour chaque client.
en cliquant sur le 2 à gauche ou même réduire les résultats pour 1 seul client
en cliquant sur le 2 (le 1 n'affiche plus que le total) ou par les + et les _ à
gauche qui permettent d'afficher ou de masquer les détails.
Les sous-totaux
permettent également de créer un graphique Excel sur les données regroupées.
Supposons que nous
voulons maintenant faire un sous-total sur les catégories de produits. Pour
rassembler les lignes de notre facture suivant les catégories, nous devons
refaire un tri. Malheureusement, à chaque nouveau tri, Excel supprime les
sous-totaux existants.
Effectivement, Excel
ne garde pas les sous-totaux existant lorsque vous refaites un tri sur la plage
de données. Il ne reste plus qu'à réexécuter la commande pour regrouper sur le
nouveau tri.
Jusque maintenant,
nous n'avons effectuer des sous-totaux que sur 1 seul critère. Nous souhaitons
regrouper les données d'abord sur les familles de produits informatiques,
ensuite sur le produit lui-même (sa description).
Commençons par faire
un tri sur 2 niveaux, d'abord sur la famille de produit informatique, ensuite
sur la description du produit vendu.
Cette opération
effectuée, reprenons notre commande Sous-totaux et sélectionnons comme "A
chaque changement de" la famille de produits. Ceci crée effectivement 1
fonction sous-total sur le premier critère comme ci-dessus. Exécutons cette
même commande sous-totaux du menu données, sélectionnons cette fois le produit
et décochons la case "Remplacer les sous-totaux existants".
En théorie, le
nombre de niveaux de sous-total n'est pas limité, mais comme le nombre de
niveau de tri sous Excel est limité à 3, la fonction sous-total() est également
limitée à 3 niveaux. Le chapitre suivant de cette formation va analyser en
profondeur le fonctionnement de cette fonction sous-total() avec les filtres
élaborés, ultime étape des fonctionnalités base de données d'Excel.
Dans un précédent chapitre, nous avons vus le
tri et le filtrage d'une liste sous Excel et les sous-totaux. Chaque
possibilité a ses avantages et ses défauts lorsque l'on recherche des
enregistrements particuliers:
1. le tri ne permet que de trier les enregistrements,
mais ne permet de faire directement des calculs comme critère de tris.
- le
filtre automatique permet de filtrer les enregistrements dans
chaque colonne. Par contre, le filtrage sur une même colonne est limité à
2 critères (suivant les fonctions logiques ET, OU). Même si on peux
effectivement faire un calcul (formule éventuellement) sur les zones
filtrées, la limitation des critères de tri rend l'utilisation parfois
compliquée.
- Les
Sous-totaux permettent des calculs mais suivant une seule formule
(sauf en utilisant manuellement la fonction sous-total). De plus, le
filtrage suivant des critères spécifiques nécessite de repasser par un tri
et de recréer le sous-total. De plus, les calculs intermédiaires
La solution finale
reste les filtres élaborées. Même si ce n'est pas la solution parfaite
(on pourrait également utiliser les listes Excel), le filtrage va être beaucoup
plus "élaboré" mais plus complexe puisque le nombre de critères est
infini. La solution idéale passe par un mélange de ces fonctions, même si
l'utilisation d'Access permettrait une solution plus facile.
Encodons les données
suivantes:
Cette petite liste de
produits va nous permettre de tester les listes élaborées sans trop de
problèmes.
Insérons quelques
lignes au-dessus de cette liste, et copions les en-têtes de colonnes sur la
première ligne. Une remarque concernant la position des listes de critères. La
majorité des cours Excel imposent de positionner cette liste de tris sur la
première ligne. Ce n'est pas obligatoire, mais conseillé. Ceci facilite la mise
en place des filtres.
Les critères de
filtres se mettront juste en dessous de notre première ligne.
Commençons par un
petit filtre élaboré tout simple. Nous souhaitons uniquement les produits de la
famille ordinateur compatible. Commençons par copier PC compatible dans la
cellule B2 (en dessous de notre titre de critère). Le plus simple est
d'utiliser le copier-coller.
Dans le menu Données,
sélectionnons Filtrer ->filtre élaboré.
La fenêtre de critère
élaboré apparaît:
- Filtrer
la liste sur place filtre directement dans la zone de cellules Excel où
sont placées les données de départ. Par contre "Copier vers un autre
emplacement" permet de créer la liste triée à un autre endroit de
la même feuille
- Plages:
représente la zone où se trouvent les données qui doivent être filtrées
(ici en vert).
- Zone
de critères désigne la zones ou vous avez inséré vos critères de filtrage
(ici en bleu).
- Extraction
sans doublon permet de ne pas afficher les données en doubles.
Essayons pour
vérifier notre résultat.
Remarquez que les
lignes de résultats du filtre élaboré sont notées en bleu. Nous venons de faire
un premier filtre, bien que le filtrage automatique aurait donné le même
résultat.
Pour réafficher
l'ensemble de la liste, utilisez la commande "Afficher tout"
du menu Données -> Filtrer, même si ce n'est pas nécessaire pour créer un
nouveau filtre.
Comme deuxième
filtrage, nous recherchons les produits informatiques de la famille ordinateur
compatible ET dont le stock est de 10. Nous allons tout simplement compléter le
filtre en rajoutant 10 dans la cellule C2 (en dessous de l'en-tête
"quantité" dans la zone de critère). De nouveau, exécutons la même
commande de filtrage en choisissant les mêmes zones.
Le filtre
affiche les composants de la famille "Ordinateur Compatible" ET dont
la quantité = 10
|
A retenir: les critères sur une même ligne utilise la fonction
logique ET: toutes les conditions doivent être remplies.
|
Supposons que nous
souhaitions afficher les ordinateurs compatibles dont la quantité en stock est
>5. Nous allons modifier le critère dans la cellule C2 par >5. Vous
pouvez essayer les mêmes commandes avec comme critères: <5, <>10, ...
Jusqu'ici, nous avons
utilisé 1 seul critère de filtre par colonne. Nous allons maintenant utiliser
plusieurs critères simultanés. Supposons que nous souhaitons afficher les PC à
base de Sempron 2600 et 2800. Reprenons notre zone de critères. Dans la cellule
A2, tapons Sempron 2600, dans la cellule A3, tapons Sempron 2800. Reprenons
notre commande Filtres élaborés, gardons les mêmes cellules comme plage, mais
sélectionnons maintenant les cellules A1 à A3. Le filtrage fonctionne de la
même manière sauf que nous affichons les lignes dont le produit est Sempron
2600 et Sempron 2800.
|
A
retenir: si vous utilisez plusieurs
lignes de critères, la fonction logique utilisée est OU. Toutes les
lignes répondant au critère sont affichées.
|
Une petite remarque,
nous n'avons pas dans ce cas sélectionné toutes les colonnes de critères. Ceci
n'est pas nécessaire puisque le filtre ne s'applique qu'à la colonne
"Produits".
Pouvons nous mélanger
des critères qui n'on rien à voire entre eux? Oui.
Supposons que nous
souhaitons afficher les Sempron 2600 et les produits dont le stock est
supérieur à 5. Le critère devient:
Remarquez que les 2
critères sont sur 2 lignes différentes du filtrage élaboré pour utiliser entre
ces 2 lignes la fonction logique OU.
Pour créer votre
filtres élaborés, vous pouvez également utiliser des caractères génériques. Ils
sont les mêmes qu'en DOS:
- *
remplace un ensemble de caractères (l'* en fin n'est pas nécessaire).
- ?
remplace 1 caractère quelconque dans une zone de texte
- Attention,
le filtre élaboré fait la distinction entre les majuscules et les
minuscules.
Supposons que nous
souhaitons visualise tous les produits commençant par SEM. Le filtre devient
Comme autres
exemples de caractères génériques:
- *e:
affiche tous les enregistrements contenant la lettre e
- e:
filtre tous les enregistrements commençant par la lettre e
- ??e
tous les enregistrements dont la troisième lettre est e
- Sempron
2?00: affiche tous les enregistrements Sempron 2600, Sempron 2800, ...
Pour filtrer les
champs dont le contenu est vide, tapez dans la zone de critère ="="
Pour filtrer les
enregistrements non vides: tapez =""
Remarque: Excel n'affiche pas ces caractères dans les cellules
lorsque vous avez tapé la valeur. Par contre, la barre de formule affiche la
formule correcte.
Commençons par
rajouter quelques colonnes dans notre tableau de données de départ: Date de
création du code. Cette nouvelle colonne va nous permettre de définir plusieurs
critères sur un même champ dans cet exercice.
Supposons que nous
souhaitons filtrer les produits inscrits entre le 01/01/2002 et le 31/12/2004
Nous allons tout
simplement mettre 2 colonnes avec le même champ que le champ à filtrer et
mettre sur la même ligne (fonction logique ET) les 2 critères:
Ces critères peuvent
être cumulés en ligne (OU) ou en colonne (ET).
C'est peut-être le
réel avantage d'un filtre élaboré, l'utilisation de critères calculés, même si
son utilisation pose souvent quelques problèmes.
Supposons que nous
souhaitons filtrer sur les produits datant de 2005. Nous pourrions faire un
filtre entre 2 dates comme ci-dessus mais nous allons utiliser les fonctions de
dates d'Excel pour le filtrage. La fonction qui récupère l'année est =annee(numero).
Dans une des cellules
de la première ligne, tapez année. Dans la cellule en dessous, tapez: =annee(F6)=valeur
où F6 est la première cellule de la colonne de la zones de liste qui contient
les données dates. Valeur est la valeur recherchée. La réponse doit toujours
être une valeur logique (Vrai ou faux).
Créez maintenant le
filtre élaboré suivant, Excel n'affiche que les produits inscrits en 2005.
Nous pouvons rajouter
des critères. Nous souhaitons les produits sortis en juillet 2005. Nous allons
rajouter un autre critère: celui du mois. Comme nous souhaitons mois = 07 ET
Année = 2005, les 2 critères du filtrage doivent être sur la même ligne. Dans
la cellule D1, tapons le mot mois. Dans la cellule D2, =MOIS(F6)=7
L'utilisation de
critères calculés dans les filtres élaborés va nous permettre également de
faire des filtrages sur des calculs. Prenons par exemple une moyenne: nous
souhaitons afficher tous les produits dont le prix de vente est supérieur au
prix moyen. Commençons par créer notre critère.
Dans la cellule E1,
tapons le terme valeur. Dans la cellule E2, tapons la fonction logique
=E7>moyenne(E7:E11). Créez ensuite un filtre élaboré avec comme zone de
critère les cellules E1:E2. Le filtre n'affiche plus que les produits dont la
valeur est supérieure à la moyenne
Quasiment toutes les
filtres calculés sont possibles, tant que le critère est une condition. Comme
autres exemples:
|
2 X le Minimum
|
Comparaison avec la valeur d'une cellule
|
|
=E7>2*min(E7:E11)
|
=ANNEE(F6)=Feuil2!A1
|
Remarquez que pour la
comparaison, la référence de la cellule contenant l'année est sur une autre
feuille. Sinon, le filtre élaboré n'affiche rien!
Nous pouvons
également utiliser les 4 opérations comme filtrage.
=C6*E6>1000 est
permis. Il vérifie si le stock d'un produit est supérieur à 1000 euro.
Nous venons de voire
comment créer des filtres, mais est-il possible de créer des fonctions sur les
résultats obtenus suivant une colonne?
Oui et non. En
premier, si vous insérez la fonction en bas de votre liste avant de faire le
filtre, la fonction ne tient pas compte du filtre. Dans l'exemple par
exemple ci-dessous, la somme des prix???
Par contre, vous
pouvez insérer la fonction avec le filtre en fonction (pas toutes). Si vous
cliquez sur le bouton de fonction 
,
Excel va modifier la fonction en sous-total. Le premier argument (un chiffre de
1 à 11) reprend la fonction standard effective.
|
|
no_fonction
|
Fonction
|
|
Excel 2003 uniquement
|
|
|
1
|
MOYENNE
|
|
101
|
|
2
|
NB
|
(nombre de cellule avec
des chiffres)
|
102
|
|
3
|
NBVAL
|
(nombre de cellules avec des
valeurs (textes, chiffres, ...)
|
103
|
|
4
|
MAX
|
|
104
|
|
5
|
MIN
|
|
105
|
|
6
|
PRODUIT
|
|
106
|
|
7
|
ECARTYPE
|
|
107
|
|
Remarque: il est souvent
nécessaire de corriger la plage de cellule de la fonction manuellement, sous
peine d'oublier des cellules.
|
8
|
ECARTYPEP
|
|
108
|
|
9
|
SOMME
|
|
109
|
|
10
|
VAR
|
|
110
|
|
11
|
VAR.P
|
|
111
|
Ceci n'est donc pas
valable pour les fonctions personnelles, ... Lorsque le filtre est enlevé, le
sous-total se comporte comme la fonction standard.
La version Excel 2003
a modifié la fonction sous.total() en rajoutant des options. Les numéros de
fonctions entre 1 et 11 inclut les valeurs masquées (comme les autres
versions), les numéros de fonctions entre 101 et 111 (nouvelle fonctionnalité)
ignore les lignes masquées. Ces valeurs masquées sont liées à la commande ligne
du menu Format: Masquer qui permet de ne pas afficher des lignes de valeurs.
L'utilisation des
filtres élaborés permet de multiplier le nombre de critères par rapport aux
filtres automatiques. Ce type de filtrage permet également de faire des critères
calculés. Par contre, l'utilisation de fonctions Excel dans ces résultats passe
par la commande sous-total(), limitée à quelques opérations possibles
(éventuellement utiliser la commande sous-totaux du menu Donnée). Par contre,
les résultats intermédiaires sont supprimés lorsque vous modifiez le filtre.
L'utilisation de la
commande sous-totaux du menu Données permet de faire plus d'opérations, mais la
fonction doit rester la même dans l'ensemble des groupes de données. Les
résultats des calculs disparaissent avec la suppression de la commande
sous-totaux.
La commande LISTE,
apparue avec Excel 2003 permet de faire les sous-totaux de manière plus
facile, selon les 11 fonctions définies, différentes d'une colonne à l'autre si
nécessaire. Ici aussi les calculs disparaissent avec la suppression de la liste
ou restent si vous convertissez en plage avec les calculs affichés. Dans un
sens, cette nouvelle commande n'est que l'adaptation des commandes des
anciennes versions.
Dans tous les cas,
EXCEL montre ses limites lorsqu'il s'agit de faire des calculs dans des listes
de données importantes. L'utilisation d'une base de donnée (Etats Access par
exemple) est largement préférable.
Une dernière
remarque: si vous souhaitez garder les calculs sur des filtres ou des
sous-totaux particuliers, vous pouvez sélectionner la zone souhaitée et copier
les valeurs. Les calculs avec fonctions Excel standards sont transposées
directement. Par contre, dans un filtre élaboré, si vous copiez des plages, les
résultats des fonctions sous.total() sont transférées en valeurs.
Dans les précédents
chapitres, nous avons abordé les recherches sous Excel, sous forme de tri,
filtres, ... (en plus de la fonction recherche du menu Edition). Ce chapitre de
la formation Excel en ligne va nous permettre de rechercher des données dans
une liste sous forme de fonction.
Les applications
sont nombreuses. Ces fonctions de recherche vont par exemple nous permettre de
taper un code client dans notre facture Excel et de reprendre automatiquement
ses coordonnées. Ceci est également valable en tapant le code d'un
produit, la recherche va automatiquement récupérer la description, le prix et
le taux de TVA.
Reprenons notre
fichier facture Excel. Cette version inclus déjà quelques petites
fonctionnalités comme le non affichage des calculs dont le résultat est zéro et
le calcul des différentes bases de TVA. Nous allons modifier cette facture pour
reprendre en premier les coordonnées du client et en deuxième, rajouter une
colonne dans les lignes de produits que nous appellerons "Code".
N'oubliez pas d'enlever la protection des cellules si nécessaire. Rajoutons les
2 cases pour pouvoir encoder le code client en A11 et rajoutons une colonne à
gauche de Quantité pour insérer le code du produit par ligne.
La deuxième étape va
être de créer une lise de clients reprenant le code du client, son nom,
adresse, code postal, ville et numéro de TVA. Pour cela, utilisons une autre
feuille du classeur (même si la liste des clients peut être dans un classeur
différent).
Sur la feuille (que
nous appellerons clients), tapons dans la première ligne les différents
renseignements comme en-tête, les lignes suivantes nous permettrons de taper
les coordonnées effectives des clients. Nous pouvons éventuellement reprendre
d'autres renseignements, mais à ce stade, ce n'est pas nécessaire.
Sur une autre feuille
(que nous appellerons produits), nous allons reprendre les différentes
caractéristiques de nos produits:
Jusqu'ici, rien de
compliqué. Les fonctions de recherche vont reprendre dans les différentes
listes les renseignements souhaités pour notre facture.
Cette solution a des
limites et ne permet pas de reprendre une liste trop longue de produits, Excel
est limité à 65535 lignes maximum.
Commençons par les
produits. Le but est donc de taper le code en A14 dans la feuille facture et
que Excel recherche ce code dans la feuille produits, puis insère dans notre
facture les descriptions, prix et taux de TVA correspondants. Positionnons le
curseur en C14 et dans insertions fonction sélectionnons rechercheV.
Pour les versions Excel XP et 2003, Microsoft nous a compliqué la tâche: il
faut quasiment passer par une recherche de fonction et la fonction recherche
n'est plus utilisée (compatibilité avec les versions antérieures).
RechercheV recherche une valeur dans la première colonne à gauche
du tableau, puis renvoie une valeur dans la même ligne à partir d'une colonne
spécifié. Par défaut, le tableau doit être
trié par ordre croissant. Cette petite
phrase en rouge va déjà nous poser quelques problèmes, même si l'utilisation
des tris Excel n'a plus de secret pour nous. La fonction rechercheh() est
identique sauf que les listes sont données en lignes au lien d'en colonnes.
- La
valeur recherchée est la cellule dans laquelle nous tapons le code
de notre produit sur la feuille facture (soit A14)
- La
table de matrice est la liste de produits que nous avons créé, soit
le contenu de la feuille produits (sans la première ligne qui reprend les
en-têtes). Premier problème de cette fonction, si vous créez de nouveaux
produits, ils ne seront pas repris dans la liste. Une solution est de
sélectionner plus de lignes que vous n'avez de produits.
- No_index_col
est le numéro de colonne dans la table de matrice (liste des produits).
Comme dans notre facture, notre colonne reprend les descriptions, le
numéro est 2 (le numéro correspondant dans notre liste de produits pour la
description)
- Valeur_proche
(facultatif) propose de rechercher la valeur exacte ou non. Par défaut,
elle ne recherche pas la valeur exacte, mais la valeur la plus proche dans
la liste. Nous allons voire ci-dessous les conséquences de l'utilisation
de cette valeur proche. Pour l'instant, omettons cette valeur
Et voici notre
fonction rechercheV() remplie. Essayons en A14 de taper le code PC (repris dans
notre liste de codes). Excel nous donne:
Ce n'est pas
franchement ce que nous recherchons. Dans notre feuille produits, trions
notre liste en ordre croissant. Le résultat dans notre feuille facture est
maintenant correcte.
Dans notre feuille
Produits, tapons les produits suivants:
Ces produits vont
nous permettre d'y voire un peu plus claire. Trions ensuite l'ensemble de la
liste par ordre croissant. Dans notre cellule A14 de la feuille facture, tapons
a comme code, effectivement la description est correcte. Tapons maintenant aa
comme code.
La valeur reprise
est la description du produit dont le code est a et non un message d'erreur
nous signalant que le produit n'existe pas.
|
A retenir: dans une recherche verticale ou horizontale avec la valeur
proche omise
- si
la liste n'est pas triée par ordre croissant, le résultat est
généralement faux
- Si
un code n'existe pas dans une liste triée par ordre croissant, Excel
reprend automatiquement la ligne avant la valeur recherchée (la plus proche)
|
Dans la cellule C14,
modifions notre formule de recherche
Comme le code aa
n'est pas repris dans la liste, la description affichée est #N/A, un message
d'erreur Excel. Cette solution est déjà beaucoup plus pratique. En fin de liste,
ajoutons justement ce code aa avec comme description produit aa (ne faites pas
de tri, mais modifiez la table de matrice dans la fonction pour reprendre tous
les produits si nécessaires). Le résultat devient correct
|
A retenir, dans une recherche
verticale (RECHERCHEV) ou horizontale (RECHERCHEH), si vous utilisez la
valeur proche à zéro (faux):
- la
liste n'a plus besoin d'être triée par ordre croissant
- si
la valeur recherchée n'est pas dans la liste, Excel affiche un message
d'erreur signalant que le produit n'existe pas.
|
Insérons de nouveau
la fonction rechercheV() dans la cellule D1' pour rechercher le prix 
unitaire. Je vous laisse le soin de remplir la fonction
pour récupérer le code TVA du produit.
Il ne nous reste
plus qu'à recopier les fonctions dans les différentes colonnes en utilisant le petit
carré en bas à droite.
Malheureusement,
cette manière de travailler ne fonctionne pas. Excel va adapter les références
de cellules lignes par lignes. Il nous faut utiliser des références de cellules
absolues dans les cellules pour la table de matrice. La fonction devient dans
la table de matrice:
Truc: utilisez la
touche F4 dans la formule pour passer de relatif en absolu.
Il nous reste un
dernier petit problème. Si nous ne tapons pas de code, notre facture ressemble
à ceci:
Les différents #N/A ne sont pas très
esthétiques. Nous allons utiliser la fonction Si() pour corriger ce problème
d'affichage en l'imbriquant avec notre rechercheV:
=SI(A14<>"";RECHERCHEV(A14;produits!$A$2:$D$9;2;0);"").
De cette manière, si aucun code n'est rentré, rien n'est affiché. Vous
pouvez directement modifier votre fonction dans la barre de formule:
Modifiez les autres
fonctions de la même manière sur la première ligne de produits et recopiez les
cellules vers le bas.
Il nous reste
maintenant à récupérer les coordonnées du client. Ceci ne va pas poser de
problèmes particulier. Le code du client est tapé en A10. Le nom du client est
affiché en D6. Dans cette cellule, la fonction Excel de recherche devient:
=RECHERCHEV(A11;'adresse client'!A2:J17;2;0), et ainsi de suite pour les autres
coordonnées. Remarquez que le code de recherche dans notre feuille facture peut
être positionné n'importe où, pas seulement dans la même ligne.
Une macro permet
d'automatiser des tâches répétitives. Deux méthodes différentes sont utilisées
pour créer des macros Excel:
- enregistrer
une séquence utilisateur (déplacements, textes, chiffres et formules,
commandes diverses, ...)
- programmation
en VBA (Visual Basic Application, une version particulière du langage de
programmation Visual Basic).
Dans cette formation,
voyons la méthode "enregistrer une séquence d'instructions". Excel
crée automatiquement la liste des commandes en VBA. Cette méthode pour créer
une macro Excel est similaire à celle de Microsoft Word. Par contre, la
création d'une macro Access ne peut pas se faire en enregistrant une suite de
déplacements, mais par VBA ou en sélectionnant la liste des commandes
disponibles.
Les commandes
sont reprises dans le menu Outils: Macro et Macros complémentaires.
Pour faciliter la
compréhension, nous allons tout de suite commencer par créer une nouvelle
macro. Positionnez votre curseur dans la cellule A1 avant de commencer.
4 options sont
disponibles:
- Le
nom de la macro: il doit être en 1 seul mot, sans espaces, trait
d'union, underscore, ...
- Le
raccourci clavier. Ceci permet de démarrer la macro par une
combinaison de touche. N'utilisez pas de raccourcis existants comme CTRL +
G (gras), CTRL + I (italique), CTRL + U (souligné). Les raccourcis peuvent
être constitués des touches <CTRL>, <ALT>, suivi ou non de
<Shift>, suivi d'une lettre ou d'un chiffre.
- Ce
classeur ou nouveau classeur ou classeur de macros personnelles. Pour
l'instant, sélectionnons ce classeur.
- Description
de la suite de commandes comme commentaire (optionnel)
Cliquez sur le
bouton OK pour continuer. La fenêtre d'enregistrement disparaît et une petite
fenêtre :
Cette fenêtre va nous
suivre tout au long de l'enregistrement de notre suite de commandes. La croix
ferme la fenêtre, n'arrête pas la macro. Pour récupérer cette fenêtre en cours
d'enregistrement, vous pouvez utiliser le menu Affichage -> Barre d'outils
-> Arrêter l'enregistrement.
Le mode relatif ou
absolu permet d'enregistrer les déplacements du pointeur dans les cellules ou
non. Elle n'est disponible que depuis la version Excel XP et 2003. Notre
premier exemple va clarifier cette option.
Le bouton
"Référence absolu / relatif" ne dois pas être enfoncé, nous
travaillons donc en absolu. Nous avons placé le curseur sur la cellule A1 avant
de commencer l'enregistrement. Tapez les chiffres ci-dessus dans votre macro
Dans la cellule A3,
faites la sommes des 2 cellules supérieures, soit =somme(A1:A2) et arrêtez
l'enregistrement de la macro. Supprimez le contenu des 3 cellules et
positionnez le curseur dans n'importe quelle cellule (sauf A1), par exemple en
B2.
Nous allons
exécuter notre première macro. Dans le menu Outils, sélectionnez la commande
Macro -> Macros. Vous pouvez également utiliser le raccourci clavier
<ALT> + <F8>. Ceci fait apparaître la listes des macros disponibles
pour ce classeur.
Utilisez la
commande Exécuter.
La cellule de départ
B2 reçoit notre premier chiffre tapé durant l'enregistrement, soit 34. La
cellule A2 reçoit le deuxième chiffre, effectivement tapé durant
l'enregistrement en A2. La cellule A3 reçoit effectivement la fonction Somme.
Que c'est-il passé. Dans
le mode absolu, la macro enregistre les déplacements en mode absolu (la
référence des cellules). Reprenons le cheminement de notre commande.
|
|
Enregistrement
|
Exécution
|
|
Avant
|
Le curseur est placé en A1
|
Le curseur est placé en B2
|
|
Début
|
Nous tapons 34, sans déplacer
le pointeur
|
La macro tape 34 dans la
cellule en cours, soit B2
|
|
|
Nous déplaçons le curseur en
A2
|
Excel déplace le curseur en A2
|
|
|
Nous tapons 45
|
Excel insère 45 dans la
cellule en cours
|
|
|
Nous déplaçons le curseur en
A3
|
Le curseur est déplacé en A3
|
|
|
Nous faisons la somme des 2
cellules supérieures (A1:A2)
|
La fonction somme des 2
cellules supérieures est insérée dans la cellule active
|
En suivant ce tableau,
Excel a strictement suivi les instructions de l'enregistrement.
Si nous recommençons
la même macro en déplaçant le curseur vers la cellule A1 pendant
l'enregistrement, la liste de commande tapera effectivement 34 dans la cellule
A1.
Nous venons
d'utiliser la méthode absolu, essayons maintenant la méthode relative. Effacez
le contenu de la feuille et positionnez le curseur en A1 comme précédemment.
Créez une nouvelle macro. Lorsque la fenêtre arrêter l'enregistrement apparaît,
cliquez sur le bouton relatif. Il doit avoir l'aspect coloré.
Tapons de
nouveau 34 en A1, 45 en A2 et la somme de ces nombres en A3. Fermer
l'enregistrement, effacez le contenu et positionnez le curseur en B2 par
exemple. Exécuter la macro relative.
Le résultat est
maintenant différent.34 est bien repris dans notre cellule de départ, soit
B2. Vous pouvez positionner votre curseur n'importe où dans votre feuille,
la suite de chiffre est toujours tapée à l'endroit de départ.
Effaçons le contenu
de notre feuille et positionnons le curseur dans la cellule B2 par exemple.
Commençons l'enregistrement d'une nouvelle macro en mode relatif.
Comme opération,
descendons le curseur de 2 lignes et tapons un chiffre. Terminez
l'enregistrement et exécutez la macro. Quel que soit la position de votre
curseur avant l'exécution, le chiffre 34 est toujours tapé 2 cellules en
dessous.
Remarque: vous pouvez passer d'un mode à l'autre en cours
d'enregistrement.
|
A retenir: Lorsque vous enregistrez une macro Excel en mode
absolu, l'exécution suit les mouvements du pointeur en absolu, suivant la
référence exacte de la cellule. Par contre, enregistrer une macro en mode
relatif enregistre les déplacements suivant la différence du nombre de
lignes et de colonnes.
|
En mode absolu,
le curseur se déplace vers une référence de cellule bien définie. Par contre,
en relatif, l'enregistrement se fait sur le déplacement. En relatif,
l'exécution peut déplacer le curseur sur des cellules inexistantes.
Comme exemple,
positionnons le curseur en B5 et débutons l'enregistrement d'une macro en mode
relatif. Déplacez le curseur en A1 (soit -1 colonne et - 4 ligne) et tapez par
exemple YBET informatique. Arrêtez l'enregistrement. Positionnez votre curseur
en B6, le texte est effectivement tapé en A2. Par contre, placez votre texte en
A2 et exécutez la macro. La fenêtre de débogage apparaît
.
Ce type d'erreur
n'apparaît jamais en absolu sauf si vous déplacez le curseur vers une feuille
préalablement effacée.
Il y a 4 méthodes
possibles pour exécuter une macro Excel, c'est identique en Word.
1. Par le menu Outils, c'est la méthode que nous venons
d'utiliser
2. En créant un bouton dans une barre d'outils ou même en
ajoutant une commande dans un menu.
3. En créant un raccourci clavier
4. En créant un bouton dans la feuille Excel.
C'est la méthode la
plus courante.
Sélectionnez la
commande "Barre d'outils" dans le menu Affichage et la sous commande
Personnaliser tout en dessous.
Dans l'onglet
"Commande", sélectionnez dans les catégories Macros. Glissez le
"bouton personnalisé" vers une boîte d'outils. le résultat doit être
similaire à ceci:
Cliquez avec le
bouton droit de la souris sur ce bouton (menu contextuel) pou faire apparaître
le menu suivant:
Réinitialiser permet de remettre le bouton de départ en cas de
modifications
Supprimer permet de supprimer ce bouton de la barre d'outils.
L'autre solution est de glisser ce bouton en bas de la barre d'outils.
Nom permet de donner un nouveau nom à ce bouton.
Copier l'image du
bouton permet de copier l'icône pour
l'utiliser sur un autre bouton personnalisé.
Coller l'image du
bouton permet de reprendre une image
copiée.
Rétablir l'image
du bouton permet de reprendre l'aspect
avant modification
Editeur de bouton permet de dessiner l'icône
Modifier l'image
du bouton propose des icônes différentes
Les 4 lignes
suivantes sont liées à l'aspect de cette commande dans la barre d'outils:
Par défaut (icône) - Texte seul (le texte tapé comme nom) - Masquer
les images dans les menus (options pour ne plus afficher les icônes à coté
des commandes dans les menus) - Image et texte affiche le bouton et le
nom dans la barre d'outils.
Nous pouvons
directement affecter une macro par ce menu, mais si vous ne le faites pas, en
cliquant sur le bouton la première fois (après avoir quitter le mode
personnalisé), Excel le propose automatiquement et affiche les macros
disponibles.
Cette méthode permet
également de modifier, ajouter ou supprimer des commandes dans les menus.
Exercice: créez un bouton de même aspect que celui de
l'impression standard mais avec une ligne rouge 
Vous pouvez
également exécuter une macro en lui associant un raccourci clavier. Pour créer
ce raccourci, reprenez votre fenêtre de macros. Sélectionnez la macro souhaitée
et cliquez sur le bouton option.
Vous pouvez ainsi
affecter le raccourci souhaité. Sont autorisés (en combinaison)
· les touches ALT - CTRL et ALTGR (ou 2 ensembles)
· Shift (optionnel)
· 1 lettre ou 1 chiffre - Excel distingue les chiffres du
pavé numérique de ceux du clavier standard.
Cette solution passe
par l'utilisation de la barre d'outils Formulaires.
Utilisez le bouton dans
la barre d'outils pour créer un bouton directement sur votre classeur Excel. La
fenêtre pour affecter une macro apparaît immédiatement.
Il ne vous reste plus
qu'à sélectionner la macro.
Avec le chapitre
précédant, nous avons étudier une vue d'ensemble des macros Excel. Cette partie
va expliquer d'autres spécificités des macros, mais surtout donner des exemples
de macros. Au final, nous utiliserons une macro pour automatiser certaines
parties de notre facture Excel.
Lorsque vous
créez une macro Excel, la première fenêtre pose la question d'enregistres dans?
(3).
Les différentes
possibilités sont:
- Classeur
de macros personnelles: (dans votre classeur personnel perso.xls) Ce
choix vous permet d'utiliser la macro créée à partir de n'importe quel
classeur.
- Nouveau
classeur dans un nouveau classeur qu'Excel crée automatiquement. La
macro ne sera utilisable que dans ce classeur
- Ce
classeur, macro valable uniquement dans le classeur en cours
- Description,
un commentaire personnel
Remarque: Vous disposez de 3 méthodes pour
qu'une macro soit accessible dans tous les classeurs:
- l'écrire
dans un module du classeur perso.xls.
- l'écrire
dans un module d'un classeur ouvert automatiquement avec Excel ( le
classeur doit être enregistré dans le dossier C:\program Files\Microsoft
Office\Office\XLStart ou C:\Windows\Application
Data\Microsoft\Excel\xlstart selon votre version de Windows.
- Dans
l'environnement VB, vous pouvez enregistrer le classeur en tant que macro
complémentaire avec le type Macro complémentaire.
Excel intègre
quelques fonctions complémentaires optionnelles. La liste se trouve en macros
complémentaires dans le menu Outils. Leur installation est optionnelle. Ces
macros sont insérées comme commandes additionnelles dans le menu Outils.
Par exemple, le
solver est une fonction étendue de la valeur cible permettant d'insérer des
conditions dans les cellules à modifier pour obtenir le bon résultat.
Comme exemple
d'utilisation des macros et comme exercice, nous allons créer différents types
de macros Excel. Nous terminerons par une macro pour notre facture (l'exercice
de toute cette formation Excel).
Avant de commencer à
programmer des macros, songer que l'ordinateur est bête. Il exécute très vite
et sans erreurs des tâches simples, à condition de lui signaler tout ce qu'il
doit faire, pas à pas.
De plus, lorsque vous
enregistrez une macro par cette méthode, une simple erreur peut vous faire
recommencer tout l'enregistrement de votre macro. Mieux vaut donc faire la
procédure lentement.
Cette partie va
reprendre des exemples de macros concernant l'ouverture, enregistrement de
classeurs Excel, impression, ...
Cette macro va nous
permettre d'ouvrir un fichier spécifique à l'aide d'un bouton dans une barre
des tâches. Elle doit être créée avec l'option "Classeur de macro
personnel".
Commencer par créer
une nouvelle macro. Utilisez la commande Ouvrir du menu Fichier et sélectionnez
"Autre classeur" dans le volet Office(version XP et 2003)
Lorsque la
fenêtre de choix Windows apparaît, sélectionnez le dossier où se trouve votre
document. Cette étape est obligatoire, sinon, la macro Excel cherchera le
document dans le dossier en cours.
Une fois le dossier
sélectionné, sélectionnez le document dans la liste et arrêtez l'enregistrement
de votre macro.
Exercice: créez un bouton dans la barre d'outils standard exécutant
cette commande.
L'enregistrement de
cette suite de commande est exactement identique à une opération courante
d'ouverture de fichier. Une seule petite distinction est la sélection
obligatoire du dossier avant de sélectionner le fichier.
Nous allons créer une
macro qui imprime le classeur courant sur une autre imprimante (une imprimante
réseau par exemple) et remet l'imprimante par défaut. De nouveau, nous devons
donner comme option "Classeur de macro personnel".
Une fois
l'enregistrement commencé, sélectionnez Imprimer dans le menu Fichier,
sélectionnez l'imprimante sur la qu'elle vous désirez imprimer et faites OK.
L'impression démarre (y compris en mode enregistrement). Resélectionnez la
commande Imprimer du menu Fichier, sélectionnez votre imprimante par défaut et
cliquez sur le bouton "Fermer", pas OK qui démarrerais l'impression
sur votre imprimante une seconde fois. Arrêter l'enregistrement de la macro.
Exercice: utilisez le bouton imprimante créé ci-dessus.
Ce type de
macros va nous permettre de faire des mises en formes (police, alignement,
couleur de fond, ...) sur des cellules sélectionnées. Une seule difficultés
pour ce type de macros, l'enregistrement ne permet pas de sélectionner une zone
de cellules. Vous devez donc sélectionnez une zone de cellules avant de
démarrer l'enregistrement. De même, vous devrez sélectionner les cellules avant
d'exécuter la macro de commande.
Commençons par créer
une nouvelle macro avec une plage de cellule sélectionné. Modifiez la mise en
forme des cellules à l'aide de la commande cellule du menu Format. Une fois
terminé, arrêter l'enregistrement.
Exercice: affecter un raccourci clavier à cette macro, par exemple
<CTRL> + >Shift> + I. Pour cette opération, utilisez la commande
macros du menu outils, sélectionnez la macro et cliquez sur le bouton Options.
Cette parties des
exemples d'utilisation des macros en Excel va sans doutes être la plus
difficile. D'une part, elle va utiliser les notions relatives et absolues,
d'autre part, elle nécessite de connaître quelques raccourcis claviers.
· CTRL + END:
positionne le curseur le plus en bas à gauche du tableau
· CTRL + Home:
positionne le curseur en A1
· Shift + CTRL + END:
sélectionne les cellules à droites et en bas de la cellule courante
· Shift + CTRL + HOME:
sélectionne les cellules à gauches et en haut jusqu'à A1
· Shift (maj) + END
suivi de la flèche vers la gauche: positionne le curseur vers la cellule
utilisée la plus à gauche de la cellule active
· Shift (maj) + END
(Fin) suivi de la flèche vers la droite: positionne le curseur vers la cellule
utilisée la plus à droite de la cellule active.
· Shift (maj) + END
(Fin) suivi de la flèche vers le haut: positionne le curseur vers la cellule
utilisée la plus à proche en direction du haut de la cellule active.
· Shift (maj) + END
(Fin) suivi de la flèche vers le bas: positionne le curseur vers la cellule
utilisée la plus à proche en direction du bas de la cellule active.
Par contre:
Si vous maintenez
les 3 touches ensembles, Excel
sélectionne l'ensemble des cellules:
. à droite, flèche
droites (y compris les cellules vides si vous êtes à la dernière colonne de
données)
. à gauche, flèche
gauche, jusqu'à la colonne A
. en haut, flèche
haute jusqu'à la ligne 1
. en bas, flèche bas
(jusqu'à la ligne 65635 si vous être à la dernière ligne de données).
Le mieux est
d'essayer ces raccourcis claviers au préalable.
Dans ce cas, comme la
référence de cellule est connue, nous devons travailler en mode absolu (bouton
grisé). Durant l'enregistrement, sélectionnez la cellule A3 (même si le curseur
est déjà positionné) et tapez la valeur souhaitée.
Comme premier
exercice, nous allons copier le contenu de la cellule active dans la cellule 2
lignes plus bas. Dans ce cas, comme la référence de cellule est inconnue (où
plutôt relative par rapport à la cellule en cours), nous devons sélectionner le
mode relatif durant l'enregistrement de notre macro. Si vous souhaitez
copier toute une plage de cellule, vous devez le faire avant de débuter
l'enregistrement.
Dans la cellule en
cours, utilisez la commande "copier" (soit à partir du menu Edition,
soit à l'aide du raccourci clavier CTRL + C). Descendez de 2 cellules et
utilisez la fonction copier.
Par contre, si nous
souhaitons copier le contenu de la cellule en cours vers la cellule A3 par
exemple, vous devez utiliser le mode absolu.
Si nous désirons
insérer une colonne entre la troisième et la quatrième par exemple, il suffit
(en mode absolu) de sélectionner la colonne 4 et d'insérer une colonne (menu
contextuel).
Par contre,
voyons l'exemple ci-dessous.
A partir de ce
tableau, nous souhaitons rajouter à droite de la dernière colonne le total.
Nous ne savons pas combien de lignes seront présentes et pour compliquer, ce
tableau peut comporter des colonnes avant notre colonne quantité et des
colonnes à droite.. Voici comment créer la macro.
Commencez
l'enregistrement en mode relatif. Pour positionner le curseur le plus en bas à
droite, utilisez le raccourci CTRL + END. Premier problème, les colonnes sont
toujours insérées à gauche sous Excel. Déplaçons le curseur d'une case à gauche
(d'où le mode relatif) et par le menu insertion, sélectionnons colonne. Tapons maintenant
notre formule en relatif: =ref-cell-quantité*ref-cell-prix (utilisez les
flèches, c'est plus facile). Il ne nous reste plus qu'à recopier notre formule
vers le haut. Déplacez le curseur vers la cellule contenant la formule. CTRL +
shift + flèche haute va sélectionner les cellules vers le haut. Il ne reste
plus qu'à utiliser la commande remplissage en haut du menu Edition.
Dernier problème.
Comme notre première ligne contient du texte (les en-têtes de colonnes), cette
cellule contient un message d'erreur. CTRL + flèche haut positionne le curseur
sur cette cellule. Il ne reste plus qu'à effacer le contenu.
Cette
fonctionnalité est un peu plus compliquée. Cette fonctionnalité va nous permettre
de sauvegarder les données de notre facture sur une nouvelle feuille par
exemple. Si vous copier et coller une formule, comme Excel adapte les formules,
le résultat est modifié lors du déplacement.
La solution passe par
un copier suivi d'un collage spécial "valeur".
Nous allons utiliser
les notions de macros ci-dessus pour faire une superbe macro adaptée à notre
facture. Cette macro doit permettre à l'aide d'un bouton associé au classeur
facture de:
1. Copier les données de facturation sur une autre feuille
en colonne (à créer au préalable). Ceci permet de conserver un résumé des
factures effectuées (éventuellement ceci peut servir pour le listing TVA).
2. Repasser sur la feuille du contenu de la facture, effacer
les données à l'intérieur de la facture, remettre "Code client" au
lien du code modifié. Remettre la date avec la fonction aujourd'hui()
3. Incrémenter le numéro de facture.
Cette dernière partie nécessite en mode absolu:
de copier le numéro de facture dans une cellule vide. Dans la cellule
adjacente, additionner cette valeur avec 1. Copier le contenu en collage
spécial vers la cellule reprenant le numéro de facture et effacer le contenu
des calculs intermédiaires.
Comment faire une
barre de graphe pour statistiques avec un tableau Excel sous Word.
Sous Word, dans le
menu insertion, sélectionnez Objet (nouvel objet) Feuille de calcul Microsoft
Excel. Revenu dans Word, double-cliquez sur la feuille excell. Les menus de
Word sont maintenant remplacés par ceux du tableur.
Commençons par créer
une série en A2 des mois de l’année par l’utilisation de la croix en bas à
droite de la cellule. Dans la colonne B, tapons le budget prévu, ici 300. Dans
la colonne C, tapons les budgets effectifs. Pour le calcul de différence,
tapons la formule =(C2-B2)/b2 dans la cellule D2 (= puisque c’est une
formule ; c2-D2 pour obtenir la différence et / D2 pour revenir en
pourcentages). Pour obtenir les moyennes, faisons dans les cellules B14, C14 et
D14 la somme des nombres dans les cellules au-dessus divisées par 12. Par
exemple, la formule en B14 doit être =moyenne(b2;b13)/12
Reste à créer notre barre
de graphe dans les colonnes E et G. Dans la cellule E2, nous allons utiliser la
formule si, la fonction rept (répéter) et la fonction arrondi.
La syntaxe de cette
fonction est de type =si(condition a effectuer si c’est vrai ;à effectuer si
c’est faux). La choses à effectuer (conditions vrai et fausses),
"" remplace "ne rien faire". La fonction rept est de type
=rept("texte"; nombre de fois). La fonction arrondi est de type
=arrondi(nombre ou cellule; nombre de décimales). La formule en E2 devient: =si(d2<0;rept("n";-arrondi(d2*100;0));"").
Le nombre arrondi est donc d2 * 100 pour une facilité de visualisation, mais
d'autres facteurs multiplicatifs sont possibles.
La colonne F2
reprend bêtement le contenu de son équivalente en A, soit pour F2 la formule
=A2 recopiée vers le bas. Pour obtenir le contenu excédant, nous reprenons en
G2 la formule =si(d2>0;rept("n";round(d2*100;0));"").
Actuellement, dans les colonnes E et H, les cellules Excel sont remplies de n,
pas folichons. Sélectionnons tour à tour les cellules E2 à E14 et G2 à G14 et
sélectionnons la police WingDing. Voici qui nous donne les barres de graphes
ci-dessous.
Ce qui suit est valable pour tous les objets
Word. Pour revenir à votre texte Word, plusieurs solutions sont possibles. Le
plus courant consiste à cliquer à coté du dessin. L'objet revient en mode
sélection. Pour reprendre votre texte, il suffit de cliquer en dehors de
l'objet. Un autre type d'objet (cas des tableaux Excel) remplace directement le
menu de Word par celui du menu associé au programme modifiant l'objet. Pour
sortir d'Excel, il suffit également de cliquer à coté, vous revenez en mode
sélection