cours tableur partie 1

Apprentissage tableur : première partie


Table des matières1

1. L’ECRAN EXCEL.............................................................................................................2 2. ENVIRONNEMENT DE TRAVAIL ET BARRES D’OUTILS............................................4

2.1. Eviter les encombrements....................................................................................................... 4

2.2. Positionner correctement les barres d’outils........................................................................ 4

2.3. Les barres d’outils Format et Mise en forme......................................................................... 5 2.3.1. La barre d’outils « Standard » ............................................................................................................................... 5 2.3.2. La barre d’outils « Mise en forme » ...................................................................................................................... 5 2.3.3. Personnaliser les barres d’outils ............................................................................................................................ 6 2.3.4. L’outil le plus important........................................................................................................................................ 6

3. SAISIR LES DONNEES ET ENREGISTRER LE CLASSEUR........................................7

3.1. La saisie .................................................................................................................................... 7 3.1.1. Texte, nombres ou dates........................................................................................................................................ 7 3.1.2. Et si l’on se facilitait la vie en accélérant les saisies ?........................................................................................... 8 3.1.3. Préalable : savoir manipuler dame souris .............................................................................................................. 8 3.1.4. Quelques options à connaître ................................................................................................................................ 9 3.1.5. Saisies groupées .................................................................................................................................................... 9 3.1.6. Créer ses propres listes........................................................................................................................................ 11 3.1.7. Evaluer une valeur future .................................................................................................................................... 12

3.2. Enregistrer puis rouvrir un document.................................................................................. 13 3.2.1. Enregistrer........................................................................................................................................................... 13 3.2.2. Ouvrir un document déjà enregistré .................................................................................................................... 13 3.2.3. Fermer le document ou quitter Excel .................................................................................................................. 13

4. LA MISE EN FORME DES CELLULES.........................................................................14

4.1. Préalable : sélectionner des cellules.................................................................................... 14

4.2. La mise en forme .................................................................................................................... 15

4.3. Les pourcentages ou l’icône pas sympa ............................................................................. 17

4.4. Onglets de la commande Format Cellule ............................................................................ 18

4.5. Formats de dates et pièges à éviter ..................................................................................... 21 4.5.1. Comprendre les formats de dates ........................................................................................................................ 21 4.5.2. Les différents formats de date ............................................................................................................................. 22

4.6. Personnalisation des formats ............................................................................................... 24

4.7. Format des titres : la fusion de cellules............................................................................... 26 5. DIVERS TRAVAUX SUR UNE FEUILLE DE CLASSEUR............................................27

5.1. Insérer/supprimer ligne(s)/colonne(s).................................................................................. 27

5.2. Insérer/supprimer cellules..................................................................................................... 27

5.3. Masquer une ou plusieurs colonnes .................................................................................... 28

1 Pour réaliser numérotation hiérarchisée, table des matières, table des index, en-tête et pied de page personnalisés, numérotation des pages Formation Word documents longs à prendre ici : http://www.ecogesam.ac-aix-marseille.fr/outils/autofor/index.htm

http://www.ecogesam.ac-aix-marseille.fr/outils/autofor/index.htm


6. LES CALCULS ..............................................................................................................29

6.1. Barre d’état : la petite machine à calculer ........................................................................... 29

6.2. Mauvais calculs ...................................................................................................................... 30

6.3. Calculs et références relatives ou absolues ....................................................................... 32 6.3.1. Calcul de la commission en G9........................................................................................................................... 32 6.3.2. Calcul des frais de vie en H9............................................................................................................................... 33 6.3.3. Mise en place des autres formules de calcul........................................................................................................ 34

7. FORMULES DE CALCUL AVEC LES NOMS DE CELLULES.....................................35

7.1. Gérer les noms de cellules.................................................................................................... 35 7.1.1. Attribuer les noms ............................................................................................................................................... 35 7.1.2. Vérifier/corriger les noms attribués..................................................................................................................... 36

7.2. Calculs avec les noms ........................................................................................................... 39

7.3. Diagnostic et correction des erreurs.................................................................................... 41

7.4. Quand les noms ne sont pas pertinents… .......................................................................... 43 7.4.1. Calculs de moyennes mobiles ............................................................................................................................. 43 7.4.2. Calculs sur les stocks........................................................................................................................................... 44

8. PROTECTION DU CLASSEUR ET DE SON CONTENU..............................................46

8.1. Sécurité classeur par mot de passe ..................................................................................... 46

8.2. Sécurité du contenu des feuilles .......................................................................................... 48 9. MISE EN PAGE ET IMPRESSION ................................................................................50 10. LES GRAPHIQUES ...................................................................................................55

10.1. Les données du graphique.................................................................................................... 55

10.2. L’assistant graphique ............................................................................................................ 56 10.2.1. Ecran 1 : type du graphique ........................................................................................................................... 56 10.2.2. Ecran 2 : données sources du graphique ........................................................................................................ 57 10.2.3. Ecran 3 : options du graphique....................................................................................................................... 58 10.2.4. Ecran 4 : emplacement du graphique ............................................................................................................. 59 10.2.5. Travaux sur un graphique réalisé ................................................................................................................... 61 10.2.6. Gérer le problème des séries chronologiques................................................................................................. 65

11. LES FONCTIONS ......................................................................................................66

11.1. Syntaxe des fonctions ........................................................................................................... 66 11.1.1. Fonctions sans argument................................................................................................................................ 67 11.1.2. Fonctions avec un argument........................................................................................................................... 68 11.1.3. Fonctions avec deux arguments et plus.......................................................................................................... 70 11.1.4. Fonctions et grands classiques de dates et textes ........................................................................................... 72 11.1.5. ................................................................................................................. 79 Complément sur le collage spécial

11.2. ....................................................................................................................... 82 La fonction SI()11.2.1. ............................................................................................................................... 84 Le SI() sur du numérique11.2.2. ........................................................................................................................................ 84 Le SI() sur du texte11.2.3. Les SI() imbriqués.......................................................................................................................................... 85 11.2.4. .................................................................................................................. 86 Les SI() sur une échelle de valeur11.2.5. Les SI()avec du ET() ou du OU()................................................................................................................... 87


Recherche indexée des procédures et fonctions traitées Barres d’outils...........................................4 Calcul manuels.......................................29 Calculs....................................................29 Collage spécial .................................78, 79 Données saisie.........................................7 Ecran Excel ..............................................2 Enregistrer classeur ...............................13 Fonctions

ALEA() ..........................................71 ANNEE..........................................72 ANNEE() .......................................73 ARRONDI() .............................70, 81 ARRONDI.INF() ............................71 ARRONDI.SUP()...........................71 AUJOURDHUI() ......................67, 73 CHERCHE()..................................76 CONCATENER() ..........................77 DROITE() ......................................74 ECARTYPE() ................................68 ENT() ............................................71 ET() ...............................................87 GAUCHE() ..................73, 74, 81, 88 JOUR()..........................................72 JOURSEM() ..................................73 MAINTENANT() ............................67 MAJUSCULE()..............................81 MAX()............................................68 MIN() .............................................68 MOIS() ..........................................72 MOYENNE() ...........................43, 68 NBCAR() .......................................76 OU() ..............................................87 SOMME() ......................................68 STXT() ..........................................74

Fonctions Catégorie Dates.............................................72 Texte .............................................73

Format Cellule .......................................... 18 Dates............................................ 21

Format des titres ....................................26 Format Mise en forme conditionnelle .....83 Format Personnalisation ........................24 Graphiques.............................................55

Emplacement ............................... 59 Mise en forme .............................. 61 Problèmes.................................... 65

Hauteur Lignes .......................................16 Insérer ....................................................27 Largeur Largeur Colonnes .....................16 Masquer .................................................28 Mise en forme.........................................14 Mise en page et impression ...................50 Noms

Attribuer les noms ........................ 35 Calculs ......................................... 39 Incompatibilités ............................ 43 Messages erreur .......................... 41 Vérifier.......................................... 36

Outils Options.................................3, 9, 11 Pourcentages .........................................17 Problèmes d'arrondi ...............................68 Protection

Classeur ....................................... 46 Formules ...................................... 48

Références relatives ou absolues ..........32 Saisies groupées......................................9 SI()..........................................................82

Avec du ET() ou du OU() ............. 87 imbriqués...................................... 85 sur du numérique ......................... 84 sur du texte .................................. 84 sur une échelle de valeur ............. 86

Souris manipuler ......................................8 Supprimer...............................................27


Avant-propos : Ce document consacré au tableur porte sur l’apprentissage de Microsoft Excel. En fin de dossier, on trouvera les adaptations au passage sur le tableur d’Open Office qui, est-il besoin de le rappeler, est libre au téléchargement et gratuit à l’utilisation : http://fr.openoffice.org/index.html. La diffusion de cette suite progresse à pas de géant. Il est à noter qu’il est possible de se créer un véritable bureau itinérant sur clé USB, comprenant outre la suite Open Office, le navigateur Firefox, le logiciel de messagerie Thunderbird et autres logiciels libres : http://www.framakey.org/. Un grand merci à Karine Petit dont l’incontournable site doit figurer en première place de tous les favoris ou marque-pages : http://www.petitkar.com/. Bon nombre de copies d’écran et d’éléments techniques figurant dans ce document sont repris de ses propres productions. Un second merci aux aimables mais exigeants correcteurs ou cobayes qui auront testé, corrigé et aidé à améliorer ce document avant sa diffusion. Un merci d’avance pour ceux qui le feront après l’avoir mis en œuvre. En effet, toute contribution, source de correction et d’amélioration profitable aux futurs utilisateurs, sera la bienvenue. La version électronique de cette auto-formation, les exercices d’apprentissage, les applications ainsi que les corrigés sont téléchargeables sur le site Ecogesam: http://www.ecogesam.ac-aix-marseille.fr/outils/autofor/index.htm

La reprise, la copie et la reproduction de ce document sont entièrement libres… … à des fins non commerciales, il va de soi … +~~] ;O)

La diffusion, si tel était le cas, laisserait à penser qu’un apprentissage à l’ancienne des logiciels bureautiques n’est pas tout à fait dénué d’intérêt.

Un conseil : user et abuser de l’aide proposée par le logiciel 1.

Par la touche F1 ou …

Par un clic sur le ? situé à gauche de la barre d’outils Standard.

Vous pourrez alors :

lancer une recherche indexée sur mot(s)-clé à indiquer. faire usage de la table des matières de l’aide générale. enfin, depuis la version Excel2003, avoir recours à l’aide en ligne de Microsoft

... à supposer bien sûr que vous disposiez d’une connexion internet. Outre l’incomparable site de Karine Petit, cité plus haut, une excellente source d’informations, notamment sur les fonctions, et un outil original – l’AMI – sont à découvrir sur le site d’E. Chouard : http://etienne.chouard.free.fr/Info/index.html

Une précision : une « superbe » auto-formation au texteur précède celle-ci 2. Le lecteur est supposé avoir commencé par là. Certaines procédures basiques sont en conséquence supposées connues. Elles seront ainsi moins développées.

1 Ceci est valable d’ailleurs pour tout (bon) logiciel qui se respecte. 2 Téléchargeable évidemment sur le site gratuit Ecogesam dont on ne se lassera jamais de louer les qualités : http://www.ecogesam.ac-aix-marseille.fr/outils/autofor/index.htm

Page : - 1 -

http://fr.openoffice.org/index.html

http://www.framakey.org/

http://www.petitkar.com/


http://etienne.chouard.free.fr/Info/index.html


Environnement de travail

Il apparaît utile de commencer par faire les présentations avec l’écran de travail.

1. L’écran EXCEL

On notera qu’un classeur est composé de différentes feuilles ou onglets, voir en bas de l’écran à gauche.

Petite récréation :

Nouvelle feuille : Insertion Feuille ou, plus simplement, clic droit et Insérer

Supprimer feuille : évident non ? clic droit sur l’onglet et Supprimer

Renommer feuille : si vous n’avez pas compris, cela devient désespérant.

Déplacer feuille : si c’est dans le même classeur, cliquez sur l’onglet et le déplacer.

Copier/Déplacer dans un autre classeur : Edition Déplacer ou copier feuille. Il reste à désigner le classeur d’accueil concerné et décider si c’est une copie ou un déplacement … avec perte dans le classeur initial.

Page : - 2 -


Paramétrer le nombre de feuilles à l’ouverture d’un nouveau classeur :

Passez par Outils Options onglet Général

Par défaut : - Police - Taille - Dossier enregistrement

NB feuilles

Nous aurons l’occasion de revenir sur cette commande Outils Options mais une personne dotée d’un minimum de curiosité et d’astuce, ce qui est bien évidemment le cas en ce qui vous concerne, trouvera ici, outre le nombre de feuilles ouvertes par défaut, de quoi :

décider la police et la taille par défaut

définir le dossier dans lequel Fichier Enregistrer va « cacher » vos classeurs si vous avez oublié d’en préciser l’emplacement.

paramétrer le nombre de classeurs récemment utilisés qui doivent apparaître dans la commande Fichier.

préciser ou masquer par discrétion, le nom de l’utilisateur, en fait l’auteur, qui figurera dans les propriétés du classeur enregistré.

Puisque nous y sommes, portez un regard sur l’onglet Modification. Nous aurons l’occasion d’y revenir lors de la saisie des données, mais certains inconforts se règlent souvent ici :

Page : - 3 -


2. Environnement de travail et barres d’outils Ceux qui préfèrent travailler dans un fouillis visuel inextricable et se casser les yeux devant un écran pourront ne pas lire ni exploiter ce qui suit.

2.1. Eviter les encombrements

Pourquoi s’encombrer et réduire la place dédiée au document ?

Dans un premier temps, deux barres d’outils seulement sont vraiment utiles :

Dans Affichage Barres d’outils

Ne gardez que Standard et Mise en forme en décochant toutes les autres.

Ici, la barre d’outils dessin peut être ôtée.

Veillez à toujours garder également cochées dans Affichage : Barre de formules et Barre d’état.

2.2. Positionner correctement les barres d’outils

Très souvent, y compris à la première ouverture après l’installation des logiciels Microsoft, les barres d’outils sont côte à côte. On croit les avoir perdues.

Sur la première copie d’écran, une des deux barres d’outils « semble avoir disparu » ainsi qu’un certains nombre de commandes.

Affichage Barres d’outils mentionne pourtant bien la présence des deux !

En fait, il « suffit » de déplacer celle qui est mal positionnée et la remettre en place :

L’essentiel : les barres d’outils commencent toujours par un trait vertical pointillé, à positionner correctement en faisant glisser avec la souris : garder clic gauche enfoncé et déplacer.

Chercher et se positionner sur le trait vertical pointillé Il marque le début de toute barre d’outils.

Page : - 4 -


2.3. Les barres d’outils Format et Mise en forme Les barres d’outils permettent d’appeler des commandes, identifiées par des icônes. Faites mieux connaissance avec les deux barres qui vous seront indispensable.

2.3.1. La barre d’outils « Standard »

2.3.2. La barre d’outils « Mise en forme »

On notera qu’il manque des outils fort intéressants comme : définir la zone d’impression, coller valeurs ou coller format.

D’où l’utilité de personnaliser ses barres d’outils…

Page : - 5 -


2.3.3. Personnaliser les barres d’outils Personnaliser les barres d’outils, afin d’y retrouver de quoi accomplir des tâches souvent répétées :

Affichage Barres d’outils

Personnaliser puis Commandes

Dans la catégorie Fichier, on désire faire monter Définir la zone d’impression

Il suffit de la faire glisser avec la souris afin de la positionner dans la barre d’outils à l’emplacement que vous aurez choisi.

Conséquemment, enlever une icône d’une barre d’outils se fait par la même procédure mais en « jetant » l’icône hors de son emplacement (sympathique).

2.3.4. L’outil le plus important Dans la barre d’outils Standard, tout bonnement, l’icône permettant de revenir en arrière et donc d’annuler la ou les dernières actions.

A droite de celle-ci : de quoi revenir sur l’annulation et donc rétablir la dernière action.

La petite flèche permet d’avoir la liste des derniers mouvements effectués.

Cela permet d’annuler au choix :

- la dernière action

- une certains nombre d’opérations à partir de la dernière effectuée (16 au maximum).

Page : - 6 -

Saisir les données et enregistrer le classeur

3. Saisir les données et enregistrer le classeur

3.1. La saisie Lancez le logiciel, puis ouvrir un nouveau document par :

Fichier Nouveau Document vierge Ou mieux, en cliquant sur la première icône de la barre d’outils STANDARD :

3.1.1. Texte, nombres ou dates De manière générale, il suffit de cliquer sur la cellule concernée et de taper directement.

Ne pas mélanger textes et nombres si on souhaite faire du calcul :

Ecrit ainsi, dans la seule cellule B2, il sera impossible d’intégrer le taux de TVA dans une formule de calcul.

Nettement meilleur sur deux cellules

Pour aller à la ligne dans une cellule Alt + Entrée, ici après TTC

Pour les nombres, sauf peut-être les pourcentages, veillez à saisir la valeur brute sans vous préoccuper du format. En effet, mélanger texte et numérique posera problème lors de la mise en place des formules de calcul.

En B4, écrivez =B2*B3 Comme j’ai tapé 2 m3 en B2, Excel considère que c’est du texte et refuse la multiplication en B4

Ici, en B2 j’ai saisi 2, puis 100 en B3 . Comme on le verra plus loin p24, les cellules ont été mises à un format qui permet de garder le nombre mais l’« habille » avec m3 ou les attributs monétaires.

Dans le même esprit que ce qui précède, une date sera toujours écrite sous la forme JJ/MM/AA avec le « / » comme séparateur.

A gauche, tel que je saisis les données puis, à droite, après formatage des cellules (voir p22 et 24)

Page : - 7 -


Pour corriger des erreurs de saisie appuyez sur la touche F2 ou, plus facilement, cliquez dans la barre de formule.

Prenez l’habitude d’utiliser cette barre de formule car elle sera d’une grande utilité pour la suite. En particulier pour connaître le contenu exact d’une cellule. Après correction, cliquer sur la coche verte ou Entrée pour valider et pour annuler sur le X en rouge ou la touche Echap

3.1.2. Et si l’on se facilitait la vie en accélérant les saisies ? Si vous êtes de ceux ou celles qui se sentent obligés de passer de longues minutes à saisir ce qui suit, évitez de lire le développement à venir ...

3.1.3. Préalable : savoir manipuler dame souris Sur un nouveau classeur écrivez Lundi dans la cellule A2.

Jusque là, rien de difficile

Visez le carré de recopie, en bas et au coin droit de la cellule 1

Saisissez ce carré de recopie, la souris prend la forme d’une petite croix noire, puis tirez vers le bas – ou toute autre direction –

Et Hop …

SELECTIONNER Cliquez A L’INTERIEUR de la cellule et étendre … la sélection

DEPLACER Une fois sélectionnées les cellules, tirez sur n’importe quel bord de la zone pour … déplacer

Note : à défaut, gardez le couper/coller classique.

1 Si ce carré n’apparaît pas, lire comment le rendre apparent à la page suivante

Page : - 8 -


3.1.4. Quelques options à connaître Nous avons évoqué plus haut Outils Options onglet Modification, il est temps d’y revenir.

3.1.5. Saisies groupées Saisissez les données ci-dessous, sachant qu’en tapant Janvier 2006, Excel écrit Jan-06, mais on pourra le présenter autrement.

Si cette case n’est pas cochée : 1) la croix de recopie n’apparaît pas. 2) le glisser-déplacer est impossible

Cochez cette case pour déplacer la cellule active, dans un sens à préciser, après chaque entrée de donnée gain de temps à la saisie.

Maintenant que vous savez recopier, tirez donc un peu vers le bas chacune des cellules pour en constater les effets.

Belle économie de temps, sauf pour le dernier. Il eut été de bon ton d’avoir la suite 2006, 2007, 2008 etc.

Qu’à cela ne tienne … on efface de F3 à F7 1 et on recommence … autrement

1 Au cas où, car on ne sait jamais. Pour effacer sélectionnez les cellules et touche Suppr

Page : - 9 -


Entrez la valeur suivante, soit 2007 en F3, sélectionnez LES DEUX CELLULES, et tirez le tout vers le bas grâce au carré de recopie situé tout en bas de la sélection.

En fait, Excel analyse le pas, ici 1, et le reproduit de cellule en cellule.

Il est temps de jouer un peu, saisissez les informations suivantes, sélectionnez LES DEUX CELLULES (compris cette fois ?), et recopiez.

Avant recopie :

Après recopie :

Page : - 10 -


3.1.6. Créer ses propres listes Les concepteurs d’Excel n’ont pas prévu tout ce que l’on pouvait avoir envie de reproduire. Il suffit d’aller dans Outils Options puis onglet Listes pers.

J’ai annoncé une nouvelle liste, puis saisi les entrées en les séparant avec une virgule.

Pour Valider, il suffit de cliquer sur ajouter.

Autre possibilité : Ecrire la liste directement dans des cellules du tableur, sélectionner ces cellules et lancer Outils Options puis onglet Listes pers.

Au bas de l’écran, la plage sélectionnée est proposée, il suffit de cliquer sur Importer

Vous voilà devenu expert en géographie ou autres arts martiaux.

La recopie d’un point cardinal ou d’une couleur quelconque de la liste, permettra d’afficher la suite définie au préalable.

Avertissement : ce qui suit n’est absolument pas un encouragement à ne pas savoir calculer les valeurs de a et b sur une droite des moindres carrés.

Page : - 11 -


3.1.7. Evaluer une valeur future Nous disposons des Chiffres d’affaires annuels d’une entreprise, et souhaitons estimer les montants prévisionnels par la méthode des moindres carrés.

Attention : comme on vient de « jouer » avec des dates, ouvrez un nouveau classeur plutôt que de travailler sur celui qui a servi jusque là. Sinon vous risquez des surprises dont on reparlera lors du traitement des formats.

Faut-il préciser que saisir les années peut se faire facilement sans tout taper ?

Et donc, au grand désespoir des collègues statisticiens, merci de bien vouloir sélectionner les CA de 1998 à 2005 soit l’ensemble des cellules entre B2 et B9.

Et, et, et … recopier le tout vers le bas, toujours et encore, grâce au carré de recopie.

Cela donne le résultat suivant. Le graphique, que vous apprendrez à faire, confirme qu’on est bien sur la droite de tendance.

Note : pour faire joli, j’ai présenté avec un format dit entier. Quant à vous, gardez les virguleset patientez un peu, on arrive bientôt au format.

Votre serviteur a affiché la courbe de tendance linéaire pour vérifier que l’on est bien pile dessus à partir de 2006.

Faites les opérations avec une machine à calculer et vérifiez la concordance des résultats.

Page : - 12 -


3.2. Enregistrer puis rouvrir un document Il ne vous sera jamais assez répété d’enregistrer régulièrement en cours de travail et de faire des copies de sauvegarde de vos documents. Souvenez-vous qu’un ordinateur peut se bloquer à tout moment, qu’un disque dur peut lâcher ou une clé USB perdre soudainement des données, à proximité d’un téléphone portable par exemple …

3.2.1. Enregistrer

Fichier Enregistrer ou l’icône sont à éviter lors du premier enregistrement de votre document. En effet, le logiciel se révèle un peu « brutal » ; vous ne décidez ni du nom du fichier (Excel propose Classeur1.xls) ni de l’emplacement (dossier par défaut). De même si vous ouvrez un document existant, cette procédure remplace, sans avertissement, la version précédente qui sera écrasée. Certaines fois, sera-ce votre souhait ?

Afin de garder une liberté de choix, il semble très préférable de recourir à :

Fichier Enregistrer sous une fenêtre s’ouvre et il suffit de :

fournir un nom : ici « Estimation CA»

(l’extension .xls est implicite). désigner le dossier

concerné si ce n’est pas celui par défaut.

Emplacement

Nom du document

3.2.2. Ouvrir un document déjà enregistré Soit par Fichier Ouvrir à partir d’Excel soit plus simplement, à partir de l’explorateur un double-clic sur le document. Le système d’exploitation associe en effet l’extension .xls du classeur au logiciel Excel et ouvre ainsi cette application et le classeur désigné.

3.2.3. Fermer le document ou quitter Excel Rien d’original Fichier Fermer et Fichier Quitter avec, comme toujours, un avertissement si vous n’avez pas enregistré une dernière fois votre document avant d’en demander la fermeture.

Page : - 13 -

Mise en forme des cellules

4. La mise en forme des cellules Par principe, il s’agit de mettre en forme une cellule ou plusieurs. On commencera donc par la ou les sélectionner.

A partir du dossier TDcours ouvrez le classeur : intro tableur.xls

4.1. Préalable : sélectionner des cellules Quelques raccourcis sont à essayer même si tout peut être fait par glissement de la souris.

CTRL, comme toujours, permet d’ajouter à la sélection.

Ci-dessous, j’ai sélectionné, en gardant la touche CTRL enfoncée, les cellules appelées à passer au format monétaire : B5, B6, B9:C13 et G9:J13

Maj + clic permet de sélectionner tout ce qui va d’une cellule à une autre. Prenons par exemple les dates de D9 à E13

- clic sur la 1ere cellule, ici D9

- Maj + clic sur E13 tout ce qui est compris entre D9 et E13 est bien sélectionné.

C’est pourquoi je note D9:E13 pour sélectionner de D9 à E13.

Sélectionner tout un tableau : CTRL + * sur une cellule occupée.

Déplacement « TGV », très utile également dans des tableaux volumineux.

Page : - 14 -


Se placer en B8

FIN puis (à la suite, pas simultanément) On saute à la dernière ligne.

FIN puis On s’en serait douté, nous voici sur la dernière cellule occupée.

FIN puis S’il n’y a aucune cellule occupée dessous, nous voilà en ligne 65536

CTRL et Début (désolé, là en simultané) Retour direct en A1 Cette manipulation toute simple vous aura permis de constater que la feuille Excel est composée de 216 soit 65 536 lignes et 28 soit 256 colonnes.

A l’écran, on ne voit qu’un confetti. On comprendra que, disposant de nettement plus de lignes que de colonnes, les tableaux de données seront saisis plutôt verticalement : les titres en en-tête de colonnes ; les données avec un enregistrement par ligne.

4.2. La mise en forme Désolé mais il n’est pas question de revenir sur le basique. Il suffit d’utiliser la barre d’outils Mise en forme ou la commande Format Cellule (voir plus loin).

Police, Taille, Gras, Italique, Souligné, Droite, Gauche, Centré, €, Bordure, Trame et Couleur de police, ne sont pas une découverte. Si c’est le cas, merci de reprendre l’auto-formation sur le texteur.

A ceci près, que si on désire donner un format différent à une partie seulement d’un texte, il convient de sélectionner l’extrait dans la barre de formule.

Ici en A4, seul le mot « Taux » a été mis en gras et souligné.

A l’évidence, à défaut de sélectionner une partie de la cellule, c’est toute la sélection qui prendra le format.

Page : - 15 -


Petit entrainement pour vérifier que tout va bien :

- Bordures sur les paramètres et le tableau principal

- Titres en gras et centrés

- Monétaire, y compris sur les cellules non renseignées mais qui serviront bientôt pour les formules de calcul.

Et le résultat fait un peu peur …

Vous venez de rencontrer pour les colonnes CA et Fixe (B9:C13) votre premier message d’erreur ##########

Pas de panique, il suffit d’élargir les colonnes B et C.

Attention : ce ne sont pas les cellules mais les COLONNES qu’il faut élargir, cela se passe donc en haut de la feuille !

Là !

Il vous faut, soit écarter physiquement le séparateur entre les lettres de colonnes B et C puis C et D, soit double-cliquer pour qu’Excel adapte automatiquement toute la colonne à la cellule la plus large.

Voilà qui est mieux …d’autant plus que l’on a fait de même avec la ligne 8 où les titres étaient jusque là à l’étroit.

Page : - 16 -


4.3. Les pourcentages ou l’icône pas sympa

En B4, utiliser l’icône semble une évidence.

Aussitôt dit aussitôt fait et tout semble fort bien en ordre avec un superbe 3% affiché.

Modifiez la valeur et saisissez 0,196.

HORREUR en B4 s’affiche 20%. On peut se dire que l’arrondi est « social » pour un taux de commission …

MAIS dans la barre de formule, on lit le contenu exact, sur lequel seront effectués les calculs futurs ! 0,196

FONDAMENTAL : Excel affiche 20% mais calculera sur la vraie valeur sise dans la barre de formule, avec tous les risques de litiges et autres récriminations que l’on peut imaginer.

On peut résoudre cela en augmentant les décimales (celle de gauche augmente et l’autre diminue). En un clic le 20% affiché revient sagement à 19.6%.

On peut aussi passer par Format Cellule onglet Nombre

On peut aussi, et c’est le plus simple, saisir dès l’origine la valeur sous la forme la plus évidente 19,6%. Essayez dans une cellule vierge puis remettez la commission à son bon taux de 3% Cela dit, Format Cellules onglet Nombre va se révéler fort utile pour la suite. Car tout ce qui ne se fait pas par icône, s’exécute avec cette commande.

Page : - 17 -


4.4. Onglets de la commande Format Cellule

Onglet Nombre :

A utiliser de façon systématique car les valeurs numériques seront saisies sous une forme brute puis mises au format.

Onglet alignement : Sur ce point, Excel est moins riche en icônes que Word.

En particulier, c’est ici que l’on peut choisir l’alignement centré sur la hauteur de la cellule (bas est la valeur par défaut) ou l’orientation du texte.

Page : - 18 -


Onglet Police :

Exposant et Indice viendront compléter ce qui est facilement obtenu par icône.

Onglet Bordure :

Là aussi, Excel n’est pas aussi « riche » que la barre d’outils Bordures et Tableaux de Word.

Vous viendrez ici, en particulier, pour choisir le type de trait, son épaisseur et sa couleur.

Page : - 19 -


Onglet Motifs :

Comme l’indique le nom de l’onglet, vous trouverez un complément à l’outil

en matière … de motifs

Onglet Protection :

Nous aurons l’occasion d’y revenir après la mise en place des formules de calcul, afin d’éviter que celles-ci ne soient effacées par une manipulation quelque peu hasardeuse de l’utilisateur final.

Page : - 20 -


4.5. Formats de dates et pièges à éviter 4.5.1. Comprendre les formats de dates

La gestion des dates ne devrait pas poser trop de problèmes mais se révèle sources d’erreurs potentielles qu’il s’agit de bien diagnostiquer. Pour ce faire, oublions quelques instants le thème en cours, et saisissons en B16, une date au hasard soit 13/10/06.

En un mot BRAVO

Puis effaçons et tapons, dans la même cellule B16, le nombre 25

Surprise … peu agréable

Note : les puristes pourront dire que l’on aurait pu saisir directement, mais j’ai voulu nous mettre en situation où la saisie d’un nombre dans une case vierge amène un affichage inattendu.

Analyse : la cellule a gardé le format de la saisie sous forme de date, il suffira par Format Cellule onglet Nombre de revenir à Standard. Vérifiez, cela fonctionne, on récupère le 25 attendu. Mais il est impératif de revenir en arrière et de se pencher sur les dates et leur format.

Nous allons réaliser une seconde expérience « malheureuse » en calculant la durée d’un séjour dans le désert 1.

Cinq des six cellules relèvent d’une simple saisie.

En B18 tapez =B17-B16

Une durée de 07/01/1900 avouez qu’il y a de quoi se poser des questions !

Ici, le calcul portant sur des dates, Excel a pris sur lui de formater le résultat en date également. Initiative peu heureuse … pour le moins.

Comme plus haut, vous remettrez au format standard et le résultat attendu, soit 7 en l’occurence, sera enfin affiché.

Profitons de la démonstration pour mettre également les cellules B16 et B17 au format standard. Je sais, cela paraît assez curieux, mais c’est pour la bonne cause.

1 Désolé d’anticiper sur un calcul mais nous y arriverons très bientôt.

Page : - 21 -


On en déduit que, pour pouvoir faire des calculs sur les dates, Excel travaille sur des valeurs dont la référence initiale est au 1er janvier 1900. Il se trouve que le 15/07/2006 se situe 38 913 jours après le 01/01/1900. Donc le 22/07/2006, soit 7 jours après, est bien au rang 38 920 … ET quand on fait la soustraction en B18, le résultat attendu 7, formaté par erreur sous forme date, est bien 07/01/1900 Soit un rang 7 sur la base du 1 correspondant au 01/01/1900 désormais connu de vous.

Tout cela pour dire qu’ayant connaissance de ce « souci » vous le gèrerez ainsi :

J’attends Mais si je lis Alors je corrige Une date : 15/07/06 38913 Je mets au format date Un nombre : 1421 21/11/1903

1 Je choisis un format numérique

Vous m’excuserez d’avoir été un peu long, mais le gestionnaire est si souvent appelé à manipuler des dates – merci de lire « manipuler » au 1er degré – que l’on ne pouvait passer ce problème sous silence.

4.5.2. Les différents formats de date Rappel : une date se saisit sous la forme jj/mm/aa, Excel la considère en interne comme un nombre de jours après le 01/01/1900, MAIS nous pouvons donner le format que nous souhaitons tout en GARDANT LA POSSIBILITE DE CALCULER.

Format Cellules onglet Nombre. Soit vous trouvez votre bonheur dans les propositions de la catégorie Date soit tout en bas de la liste vous choisissez Personnalisé et déterminez votre propre format.

Attention, c’est le tout dernier

A saisir pour s’exercer

1 En espérant que vous avez bien compris que 1 421 jours après le 01/01/1900 nous amènent à fin de l’année 1903.

Page : - 22 -


Personnalisons donc :

Saisissez ici, votre format. Au départ rien, donc, au-dessus l’exemple présente 38904. Au fur et à mesure, l’exemple vous éclaire …

Je saisis Signification Résultat j Quantième du jour avec 0 omis 6 jj Idem mais 0 affiché 06 jjj Jour abrégé jeu jjjj Jour complet jeudi

Les mois sont comme les jours … m 7

mm 07 mmm juil

mmmm juillet aa Année raccourcie 06

aaaa Année complète 2006

A vous de jouer en sachant que le séparateur relève de votre goût. Mais ne perdez jamais de vue que le format retenu, quel qu’il soit, ne saurait empêcher le calcul.

Le format de type jj-mm-aa permettra de lire 06-07-06

Alors que jjjj j mmmm aaaa donnera jeudi 6 juillet 2006

Il est temps de revenir à notre thème introductif. Les dates de départ et d’arrivée, soit D9:E13, sont au format jj-mmm-aa

Page : - 23 -


4.6. Personnalisation des formats De F9 à F13, on aimerait bien faire apparaître le « km » de kilomètres.

Résistez à la tentation de saisir sous la forme 1200 km tout calcul à venir serait rendu impossible car 1200 km est du TEXTE !

Comme toujours, passons par Format Cellules onglet Nombre et Personnalisé

Cherchez le type standard, derrière laissez un espace et mettez le symbole voulu entre guillemets.

Page : - 24 -


Je vous sens joueurs, essayez avec des litres, des bananes ou autres ballons.

… Fin de récréation …

S’en arrêter ici serait dommage car la présentation n’est pas convenable en termes de lisibilité.

Toute personne amenée à lire vos tableaux sera profondément gênée par l’absence du séparateur de milliers.

Excel l’intègre par défaut seulement avec le format monétaire.

On va donc reprendre la même commande mais au lieu d’inscrire « km » derrière standard, on va l’écrire juste après le format du séparateur de milliers.

Pratique : la case dite Exemple, présentée juste au-dessus permet de se faire une idée.

Voilà un format à utiliser gagner l’estime des persodonnées de vos tableaux.

En même temps vous contryeux. Donc utilisez sansmilliers.

Page : - 25 -

Attention : # ##0’’ km’’ Pas d’espace après le O mais un avant le km.

de façon systématique pour nnes appelées à analyser les

ibuez à préserver vos propres compter le séparateur de


A RETENIR Le format change l’aspect de la cellule mais pas son contenu

On l’a déjà vu avec le pourcentage où on lisait 20% alors que le contenu réel exact de la cellule était 19,6%. De même, la cellule F9 « affiche » 1 200 km mais la barre de formule mentionne bien la valeur numérique 1200 qui permettra de … calculer.

La saisie directe de 1 200 km l’eut empêché. On retrouvera ce problème avec les erreurs liés à des formats arrondis qui ne sont qu’apparence et faussent les calculs.

4.7. Format des titres : la fusion de cellules Le titre demande à être centré sur le tableau …

Sélectionnez les cellules A1:K1

Procédure omnibus – mais permettant de découvrir un onglet de la commande Format

Format Cellule onglet Alignement

Cocher fusionner et centrer

Avec du gras et une police 20, cela nous donne …

Même résultat mais en TGV avec l’icône Fusionner/centrer appliquez à A1:K1

Modifiez le titre, il restera toujours centré entre A1 et K1

Pour scinder les cellules, et revenir, à la normale, décliquez la même icône.

Page : - 26 -

Insérer, supprimer, masquer

5. Divers travaux sur une feuille de classeur Avant de passer à la partie calcul, passons en revue quelques manipulations usuelles

5.1. Insérer/supprimer ligne(s)/colonne(s) Cliquez sur le numéro de la ligne puis Insertion Lignes.

Si on veut insérer plusieurs lignes, inutile de répéter n fois l’opération. Il suffit de sélectionner n lignes. Ainsi, en sélectionnant les lignes 7, 8 et 9 (cliquez à gauche sur le 7 et étendez jusqu’à 9), Insertion Lignes permet d’ajouter 3 lignes complètes.

Sera-ce utile de vous expliquer comment insérer des colonnes ?

Il semble de même inutile d’indiquer comment supprimer des Lignes et des Colonnes sauf qu’après sélection vous passerez par Edition Supprimer et non Insertion

5.2. Insérer/supprimer cellules On retrouve le même processus.

Sauf que l’on ne travaille pas sur des lignes ou colonnes entières..

1) Sélection des cellules concernées, puis

2) Insertion Cellules ou Edition Supprimer.

3) Dans la mesure ou le tableau va subir des décalages, une boîte s’ouvre afin de vous laisser préciser comment vous souhaitez opérer l’insertion ou la suppression.

Page : - 27 -

Insérer, supprimer, masquer

5.3. Masquer une ou plusieurs colonnes Plus loin dans ce cours, je vais calculer la commission en colonne G, mais désire masquer momentanément les colonnes C, D E et F

Cliquez sur la lettre de colonne C puis étendez jusqu’à F

Format Colonne Masquer

Les quatre colonnes sont invisibles et ne seraient donc pas imprimées.

Notez quand-même que l’on saute directement de B à G.

Il n’y a donc pas eu de suppression effective.

Pour afficher de nouveau ce qui a été masqué, il convient de sélectionner les colonnes voisines restées visibles, ici B et G, puis Format Colonne Afficher

Page : - 28 -

Monter des formules de calcul

6. Les calculs

6.1. Barre d’état : la petite machine à calculer Commençons par un gadget, qui peut s’avérer quand même utile …

Sélectionnez les Kilomètres de F9 à F13 et regardez en bas à droite de l’écran.

Rappel : si la barre d’état n’est pas affichée Affichage et cochez Barre d’état.

Un clic sur le bouton droit de la souris dans la barre d’état permet de choisir le calcul désiré, et le résultat s’affiche, soit ici 6 900 km. Compteur donnerait 5 car il y a 5 valeurs. Chiffres donnerait le même résultat de 5 car les 5 valeurs sont des chiffres … Les enseignants vont adorer car, en quelques clics, après avoir saisi les notes et les absents, puis bien sélectionné toute la série, ils auront relevé sans peine : - le nombre d’inscrits par Compteur - le nombre de présents par Chiffres Et bien sûr les indispensables Min, Max et Moyenne.

Page : - 29 -


6.2. Mauvais calculs Nous souhaitons en G9 calculer la commission du vendeur nommé Carles soit un CA de 12 000, 00 € lu en B9 auquel s’applique la commission de 3,00 % lue en B4.

Toujours avec le même humour à pleurer, voyons ce qu’il ne faut pas faire.

Soyez assez aimable pour commettre ces atrocités et constater le problème de visu.

Horreur 1 : tapez en G9 12000*3%. Excel, au lieu d’afficher le résultat, écrit bêtement votre TEXTE.

Ainsi tout calcul doit être annoncé par =

Horreur 2 : reprenons avec ce fameux signe = obligatoire =1200*3%

Victoire … éphémère, car si le 360,00 € attendu est bien présent en G9, une recopie vers le bas va afficher stupidement 360,00 € dans toutes les cellules !

A celui qui dirait « il suffit de refaire 5 fois l’opération », nous fournirions un tableau de 5 000 vendeurs à rémunérer et lui conseillerions de vendre un ordinateur bien trop onéreux pour une utilisation aussi sotte.

En effet, imaginons que, suite à une âpre négociation collective, le taux soit porté de 3% à 3, 05% - immense concession remettant en cause l’existence même de l’entreprise – il faudrait alors refaire 5 000 calculs !

Horreur 3 : les plus fins d’entre les lecteurs auront compris, et ce depuis longtemps, qu’il faut utiliser des références de cellules.

Donc effacez tout cela puis revenez en G9 et montez la formule

- soit en tapant directement =B9*B4 puis validez

- soit, après le =, en cliquant sur B9 puis la touche * puis un clic sur B4 et validez 1.

Le résultat est « meeeeerveilleux » puisque si l’on modifie le CA de Carles et/ou le taux de commission, la commission est correcte.

Recopiez-donc vers le bas, avant de crier victoire...

1 Plus long à écrire mais plus rapide à exécuter

Page : - 30 -


Pensez à élargir la colonne G afin d’afficher le 420 000, 00 € en G10

Pour comprendre, j’affiche les formules et examine comment la recopie a été effectuée 1

Ok

B10 oui, mais en B5 on a 30€

B11 oui, mais en B6 Excel prend 0,25€

B12, oui mais B7est vide donc 0

B13, oui mais en B8 on trouve du texte. Pas de calcul possible (Valeur ! est votre deuxième message d’erreur après #####)

Essayons de comprendre : à la recopie, Excel a travaillé en relatif et est donc « descendu » d’un cran à chaque fois. Pour les CA, cela ne pose aucun problème puisqu’ils sont lus en B9, B10, B11 etc. Mais pour le taux de commission il fallait garder LA cellule B4 (sa référence … absolue). On va revenir sur ce point rapidement.

Solution stupide : reprendre les 4 formules erronées et remplacer B5, B6, B7 et B8 à chaque fois par B4. Si vous ne comprenez pas le terme « stupide », je vous colle 5 000 vendeurs comme tantôt.

Solution tentante : Insérer une colonne entre le CA et le Fixe et y saisir le taux de commission. La formule deviendrait =B9*C9 et à la recopie =B10*C10, =B11*C11. Vous le vérifierez en tournant la page.

Or si le taux s ‘applique à tous les vendeurs, pourquoi le reproduire 5 fois … ou 5 000 comme je vous en menace depuis un moment ?

De plus, écrit ainsi, si on est amené à modifier le taux, il faudra le corriger pour chaque vendeur dans toute la nouvelle colonne C. L’idée n’est donc pas bien bonne.

1 Pour ceux que cela intéresse d’afficher les formules, la bascule se fait par CTRL + # (touche 3 au pavé principal, pas celui des chiffres à droite).

Page : - 31 -


Illustration de la fausse bonne idée :

6.3. Calculs et références relatives ou absolues 6.3.1. Calcul de la commission en G9

Reprenons le problème posé. La copie n’a pas été correcte car autant pour le CA on « descendait » bien d’un cran à chaque fois référence relative, autant pour le taux de commission, il fallait garder LA référence absolue de la cellule B4.

Il « suffira » donc, lorsque l’on cliquera sur la cellule B4, de mettre un $ devant et derrière le B $B$4 – sachant que la touche F4 permet de les poser directement –

La formule toujours en G9 devient : = B9* $B$4 et à la recopie tout est bon (ouf et enfin).

Utile : pour recopier, au lieu de tirer sur le carré de recopie, essayez de lui double-cliquer dessus. La recopie va au bout du tableau. Plutôt sympa pour les 5 000 lignes de vendeurs dont je cesserai désormais de vous menacer.

Avec insertion du taux en colonne C

Il est temps d’appliquer tout ceci aux autres calculs à réaliser.

Page : - 32 -


6.3.2. Calcul des frais de vie en H9 Ils correspondent à la durée de la mission en jours, multipliée par le défraiement accordé par journée de travail lu en B5.

Comme nous venons de le voir, les dates d’Arrivée et de Départ sont « relatives » à chaque vendeur. Alors que la journée de travail sera toujours lue en B5 en référence absolue. On écrira sous la forme $B$5.

Avec les parenthèses dont vous ferez usage de façon bien classique, le calcul s’écrira :

=(E9-D9)*$B$5

On notera la présence indispensable de l’opérateur * pour la multiplication.

La forme chère aux matheux A(B+C) n’est ainsi pas admise sans le *.

Après recopie vers le bas sur un double-clic magique du carré de recopie, on obtient bien :

Page : - 33 -


6.3.3. Mise en place des autres formules de calcul Les frais de déplacement en I9 sont calculés par le nombre de kilomètres

parcourus relatif à chaque vendeur auxquels s’applique l’indemnité kilométrique lue en $B$6 Soit la formule : = F9*$B$6

Le total en J9, quant à lui, reste relatif à chaque vendeur et sa formule ne demande aucune sortie de $ : =C9+G9+H9+I9

En ce qui concerne le calcul en Francs, que certains trouveront à juste titre totalement archaïque et déplacé, il me servira plus loin, à seul titre pédagogique, dans le seul dessein de montrer la différence entre la présentation sous forme de format arrondi et un calcul arrondi à deux décimales.

Conclusion : La notion de références relatives et absolues est fondamentale.

Tellement fondamentale, qu’avec l’expérience les utilisateurs s’y perdent.

$ ou pas $, telle est la question ? Certains en perdent la tête, d’autres leur latin.

Et puis, un calcul écrit ainsi : = CA*Taux_commissions ne serait-il pas plus « lisible » ?

Que dire de celui des Frais de vie ? =(Arrivée-Départ)*Journée_travail

Quant au calcul de Total, on doit admettre que la lecture d’une formule telle que celle-ci : =Commission+Frais_de_vie+Frais_Déplacement s’avèrerait particulièrement plus confortable à lire et éviterait aussi d’avoir à poser les doigts sur l’écran pour comprendre.

Meilleur confort et lisibilité des formules, sauf contre-indications que je montrerai plus loin, justifient amplement le recours aux cellules nommées.

PS : à moins que vous ne souhaitiez rester obscur, pour de sombres raisons de « pouvoir du spécialiste» ou plus prosaïquement pour vous rendre « indispensable ». En ces temps de précarité d’emploi, ce recours à l’abscons peut se trouver justifié …

Page : - 34 -

Formules de calcul avec les noms de cellules

7. Formules de calcul avec les noms de cellules

7.1. Gérer les noms de cellules Il s’agit de nommer les cellules puis de vérifier ce travail et, si nécessaire, le corriger.

7.1.1. Attribuer les noms Nommer une cellule isolée. Par exemple le taux de commission lu en B4.

Sélectionnez la cellule B4 qui contient la valeur. Attention, j’ai bien écrit B4, et non B3 qui n’est qu’une étiquette de texte.

Cliquez sur la boite des noms, à gauche de la barre de formule.

Il suffit alors de saisir le nom attribué SANS espace et valider :

Tauxcomm ?

Taux_commissions ?

taux comm (essayez Excel le refuse).

Optons pour le « lisible » taux_commissions

Là !

Il ne vous reste qu’à répéter la procédure en B5 pour Journée_travail puis B6 pour Remboursement_kilomètres.

Constatez, qu’au fur et à mesure, la liste des noms s’allonge.

Nommer un groupe de cellules. Ainsi le CA concernant les cellules B9:B13. - Sélectionnez les CA de B9 à B13

- Faites comme plus haut et nommez la zone CA

Regardez sur la gauche, la liste s’allonge encore.

C’est alors que vous vous dites en soupirant quelque peu devant la tâche à venir :

« Me voilà parti(e) pour répéter le travail sur les 9 colonnes à venir ».

Qu’à cela ne tienne, nous allons pouvoir accélérer les choses et vous éviter un tel tracas.

Page : - 35 -


Nommer tout un tableau, L’idée est d’attribuer aux lignes 9 à 13 de chaque colonne du tableau, le nom qui figure en en-tête de la colonne.

DONC, sélectionnez toutes les données, Y COMPRIS, cette fois-ci les titres A8:K13

Insertion Nom Créer

Et là, il s’agit de réfléchir quelques secondes et de ne pas valider trop vite !

Question : où Excel doit-il trouver les noms à attribuer ?

Réponse : AU-DESSUS !

Conséquence : décochez Colonne de gauche avant de valider. Certes, à gauche, il y avait du texte mais ce ne sont que des données sur les clients, pas des titres.

Ouvrez la boîte des noms, et réjouissez-vous, ils sont tous là, par ordre alphabétique.

Conclusion : nommer les cellules peut aller très vite.

MAIS, aller trop vite peut devenir très pénalisant au moment de monter les calculs.

Il est donc très recommandé de vérifier le travail effectué.

7.1.2. Vérifier/corriger les noms attribués Pour vérifier vous avez le choix :

Solution 1 : cliquez sur une cellule isolée, ainsi B5, et le nom doit apparaître dans la boîte des noms. Il en va de même si vous sélectionnez les cellules composant la zone nommée Kilomètres soit F9:F13

Illustrons :

Ayant cliqué sur le tarif de remboursement des frais pour une journée de travail en B5, je peux lire le nom attribué, journée_travail, en haut à gauche

Décochez puis validez par OK

Page : - 36 -


De même, après sélection des kilomètres parcourus F9:F13, on peut lire kilomètres dans la boîte des noms.

Correct

Solution 2 : ouvrez la boîte des noms, choisissez un nom, Excel va mettre en surbrillance la ou les cellules concernées.

Je choisis taux_commissions dans la liste des noms, la cellule B4 est bien sélectionnée.

Je choisis le nom CA et, tout normalement, les cellules B9:B13 sont en surbrillance.

Page : - 37 -


Prenez le temps pour opérer toutes les vérifications nécessaires.

En cas d’erreur, il est inutile de tenter de redonner un nom déjà utilisé dans l’espoir que le nouveau se substitue au premier. De même, bouton droit puis supprimer n’est pas possible LA solution passe par Insertion Nom Définir

En cliquant sur la zone nommée CA, on peut lire la cellule ou la plage de cellules à laquelle cette zone est associée.

Ici, dans la feuille 1, les cellules $B$9:$B$13

En cas d’erreur, soit corrigez directement, soit plus prosaïquement, supprimez le nom puis créez-le de nouveau comme expliqué plus haut.

Ici, on voit que Journée_travail fait référence à la cellule $B$5 de la feuille 1.

L’étourdi qui aura donné, par erreur, le nom à l’étiquette en A5, sur laquelle tout calcul sera impossible, pourra corriger ou supprimer puis créer de nouveau

Je me permets d’insister, une fois encore, sur le caractère primordial de cette phase de vérification et donc d’éventuelle correction. En effet, sur une base saine, la mise en place des formules de calcul sera très rapide et efficace.

Certes, je vous donnerai le moyen de comprendre les messages d’erreur et d’apporter les correctifs adéquats, mais le temps consacré à vérifier et corriger aurait dû permettre d’éviter ces écueils agaçants.

Astuce à vérifier et intégrer : si vous saisissez un nouveau vendeur en ligne 14, cela ne modifie pas les zones nommées. Elles ne sont pas étendues d’autant.

Ainsi CA reste-t-il bien en référence à B9:B13.

Par contre, insérez une ligne vierge à l’intérieur du tableau, devant la 11 par exemple, et vérifiez que toutes les zones sont bien mises à jour automatiquement.

Vous serez toujours à temps de trier pour remettre les noms des vendeurs, par exemple en ordre alphabétique ou par CA décroissant.

Page : - 38 -


7.2. Calculs avec les noms Commencez par effacer les « anciens calculs » à compter de la colonne G.

Le calcul de la commission en G9 devient tout simplement :

=CA*taux_commissions

Pour monter la formule vous avez le choix :

Frappe directe … pour peu que vous connaissiez bien les noms attribués.

Tapez le = puis Insertion Nom Coller

A ce stade : choisissez un nom, ici CA, puis ok tapez le * pour annoncer la multiplication refaites Insertion Nom Coller et cochez Taux_commissions Valider une fois la formule terminée

Même chose mais en utilisant la touche F3 raccourci du Insertion Nom Coller

Ou …

Ou … ce que me montra jadis une étudiante fort avisée :

« Placez-vous en G9 » me dit-elle. Je le fis.

« Annoncez le calcul par un classique = ».

« Sélectionnez B9:B13, c’est à dire TOUTE la zone nommée CA ».

Sous mes yeux esbaudis, le nom CA, prit place dans la formule …

Je sélectionne B9:B13, le nom CA s’inscrit dans la formule

Page : - 39 -


… Il me suffit alors d’ajouter l’opérateur * puis de cliquer sur la cellule B4, où figurait le taux de commission, pour parachever ce chef d’œuvre de simplicité.

Merci à elle.

Note : la recopie vers le bas ne pose aucun problème de prise de tête avec les références relatives ou absolues car, pour le taux de commission c’est toujours la cellule $B$4 qui est retenue. Pour le CA, Excel retient tout bonnement celui de la ligne où l’on se situe.

Les formules Le résultat

Je sélectionne B4, le nom Taux_commissions s’inscrit …

Des formules faciles à monter, des calculs lisibles par autrui, ou soi-même après plusieurs mois, aucune hésitation, autant que faire se peut, utiliser les noms.

Commencez par finir le travail …

Vous avez bien pris soin de faire suivre le = d’une ouverture de parenthèse, de désigner E9:E13 pour avoir le nom Arrivée puis le signe – puis encore D9:D13 pour le nom Départ puis un fermé de parenthèse suivi du * pour finir par un clic sur B5 permettant d’intégrer le nom Journée_travail

Bref, de nouveau plus facile à exécuter qu’à écrire. A vous de terminer les calculs …

Page : - 40 -


7.3. Diagnostic et correction des erreurs Permettez-moi de vous rappeler que si vous avez vérifié vos noms, ce développement ne saurait vous concerner. Cela dit, errare humanum est.

Alors …je fais l’effort insensé de faire des erreurs pour pouvoir les mettre en évidence ET vous permettre de les identifier.

Ouvrez le classeur intro excel horreurs sur les noms.xls

#NOM ? signifie « qu’est-ce donc ce nom que je ne connais pas ? »

En effet en G9:G13, la formule saisie directement à la frappe est =CA*Taux_comm

Le fait de saisir Taux_comm en lieu et place de Taux_commissions en est la cause principale Il vous suffit de corriger la formule en conséquence.

Le problème est plus subtil, et malheureusement fréquent, pour les frais de vie.

Sachant que le message d’erreur #VALEUR ! signifie « calcul impossible », il faut trouver le pourquoi.

La formule =(Arrivée-Départ)*Journée_travail est tout à fait correcte

Le souci réside dans la déclaration des cellules auxquelles les noms font référence.

Il faut vérifier Arrivée, Départ et Journée_travail qui interviennent dans le calcul.

Allons chercher, comme il se doit, du côté de Insertion Nom Définir

Page : - 41 -


Pour Arrivée $E$9:$E$13 est correct. De même, $D$9:$D$13 pour Départ.

Mais que dire du « vilain » $A$5 pour le taux ???

Corrigez en remplaçant le A du $A$5 par un B pour $B$5.

C’est bien le montant qui nous intéresse, pas le texte sur lequel tout calcul est impossible.

Sans modifier la formule en quoi que ce soit, le calcul devient correct.

Pour les frais de déplacement, pourquoi donc cinq des six calculs sont corrects alors que nous avons un problème sur le seul dernier ?

La formule =Kilomètres*Remboursement_kilomètres est juste, les noms sont les bons.

En cliquant sur la cellule B6, le nom Remboursement_kilomètres apparaît bien dans la boîte des noms.

Surprise … en sélectionnant F9:F13, devrait se lire le nom Kilomètres, ce n’est pas le cas.

Essayez F9:F12 … le nom est là !

Conclusion : la zone nommée cessant en ligne 12, le calcul est impossible en ligne 13, d’où le message d’erreur.

Par Insertion Nom Définir constatez l’erreur et corrigez-la.

Instantanément, sans toucher à la formule, tout devient correct.

Bien évidemment les erreurs étaient ici provoquées. Sera-ce le cas pour vous ?

Pour éviter les soucis, il convient donc de VERIFIER et CORRIGER AVANT de monter les calculs. Merci d’excuser le cri par des majuscules, mais l’expérience montre que de temps en temps cela s’avère nécessaire.

Page : - 42 -


7.4. Quand les noms ne sont pas pertinents… Faciles à mettre en place, d’une utilisation aisée – quand on est rodé(e) – et offrant une excellente lisibilité, les noms ne sont pas toujours LA bonne méthode.

Ouvrir le classeur sans les noms .xls

7.4.1. Calculs de moyennes mobiles Utilisons la feuille Bourse en cliquant sur l’onglet de ce nom en bas à gauche de l’écran.

Nous disposons de cours de bourse sur une période donnée.

L’un des outils de l’analyse graphique est de calculer la moyenne mobile à 20 jours.

Comme on peut s’en douter, le premier calcul ne peut se faire qu’à la 20e donnée, ici en ligne 21, et donc en cellule B21, je ne peux faire autrement que de définir une formule du type : =MOYENNE(A2:A21) 1

A la recopie vers le bas, la moyenne mobile portant sur les 20 derniers jours, on aura bien =MOYENNE(A3:A22) puis =MOYENNE(A4:A23) etc.

L’utilisation des références relatives se justifie, ici, pleinement.

Nommer les cellules une par une, serait totalement inefficace ou inopérant.

1 Certes, les fonctions n’ont pas encore été traitées, mais est-il difficile de comprendre celle-ci ?

Page : - 43 -


7.4.2. Calculs sur les stocks On aurait pu aussi bien prendre un budget de trésorerie pour illustrer ce qui suit.

Toujours dans le même classeur cliquez sur l’onglet Etat des stocks.

Le calcul est sans grande difficulté en termes de gestion : il s’agit chaque mois de calculer le Stock final Stock initial + Entrées – Sorties. Je suis sûr que vous aviez compris.

En ligne, 11,12 et 13, par Insertion Nom Coller puis Coller une liste, j’ai présenté les trois zones nommées présentant un caractère d’opportunité :

Entrées $B$4:$G$4

Sorties $B$5:$G$5

Stock initial $B$3:$G$3

Le calcul du stock final peut se faire avec les noms :

En B6 =Stock_initial+Entrées-Sorties

Excel prend bien les données du mois en cours.

Sans mauvaise surprise, le stock est bien de 170.

Page : - 44 -


Problème : Stock initial Février = Stock final de Janvier.

De mois en mois, j’aurais la bagatelle de six noms à donner SF_Janvier, SF_Février, SF_Mars etc.

ET à composer autant de formules.

En C3=SF_Janvier

En D3=SF_Février

En E3 = SF_Mars Ceci est lourd et longuet.

L’usage des références relatives, comme pour la moyenne à 20 jours, va se justifier.

En C3 j’écris =B6 En effet, je reprends le stock final de Janvier lu en … B6.

La recopie de B6 jusqu’à G6, reprendra bien le calcul avec les noms :

=Stock_initial+Entrées-Sorties. Ici pas de souci avec les noms. Par contre, ils eussent été gênants pour la reprise du stock le mois suivant.

La recopie vers la droite de C3 – bien lire C3 soit le stock initial de Février car le stock initial de Janvier est une donnée et non un résultat de calcul - jusqu’à G3, grâce au seul usage des références relatives, donnera bien =C6 puis =D6 etc.

Ok pour le stock de 170

Ainsi, l’usage des noms est à recommander fermement, sauf quant ils se révèleront peu pratiques ou inadéquates.

Exercices applicatifs de cette phase initiale du cours (énoncé dans les feuilles) :

Location.xls Intérêts.xls Prestige.xls

Page : - 45 -

Protection du classeur et de son contenu

8. Protection du classeur et de son contenu En matière de protection, vous devez bien comprendre la différence entre le fait d’empêcher les personnes non autorisées à ENTRER dans le classeur et la sécurité destinée à éviter que l’on DETRUISE vos formules de calcul ou formats par des manipulations hasardeuses.

8.1. Sécurité classeur par mot de passe La sécurité par mot de passe au niveau d'un classeur utilise une méthode de chiffrage avancé (méthode standard de protection du contenu d'un fichier) pour protéger votre classeur contre toute tentative d'accès non autorisée.

Vous pouvez définir un mot de passe par :

Outils Options onglet Sécurité

Deux mots de passe peuvent être mis en place :

Ouvrir et afficher le fichier Ce mot de passe est chiffré pour protéger vos classeurs contre tout accès non autorisé.

Modifier le fichier Ce mot de passe, qui n'est pas chiffré, sert uniquement à donner à des utilisateurs spécifiques l'autorisation de modifier des données du classeur et d'enregistrer les modifications dans le fichier.

En matière de mots de passe, rappelez-vous qu’il existe sur Internet des logiciels permettant de générer tous les termes d’un dictionnaire. D’où le conseil donné de toujours mélanger chiffres et lettres.

Conseil : certains mots de passe s’égarent, conservez une version non protégée du classeur pour éviter tout futur désagrément.

Application : ouvrez le classeur intro excel td protection.xls

Oups le mot de passe est r2d2

Page : - 46 -


Pas de problème, le classeur est ouvert.

Dans la mesure où seul le premier mot de passe a été donné, vous pouvez en changer – comme tout utilisateur d’ailleurs –

Ne vous en privez pas Outils Options onglet Sécurité.

Donnez comme nouveau mot de passe rom82

Bien entendu confirmation vous est demandée. Il serait bon de lire l’alerte de sécurité relative à une perte de ce sésame ! Impossible de le récupérer

Fermez le classeur, essayez de le rouvrir r2d2 ne marche plus, désormais rom82 s’impose.

D’où l’intérêt du 2e mot de passe

Mot de passe modification :

Ouvrez le classeur intro excel td protection 2e mot de passe.xls

Après r2d2 que j’ai repris, un deuxième est demandé :

A défaut de le fournir, la version que vous avez le droit d’ouvrir, ne pourra pas être modifiée – mots de passe compris –

Par contre, ayant fourni le premier mot de passe, vous aurez malgré tout le droit d’enregistrer sous un autre nom et de vous approprier la copie.

L’original initial reste donc sous la propriété totale de ceux qui peuvent fournir les deux mots de passe

Désolé, j’ai oublié le 2e pot de passe Allons, c’était bien sûr c3po

Pour autant les formules de calcul si chèrement créées sont-elles protégées contre les manipulations malencontreuses de personnes pourtant autorisées aux deux niveaux ? Non ! D’où l’intérêt de protéger le contenu même des feuilles : calculs et autres formats.

Page : - 47 -


8.2. Sécurité du contenu des feuilles Puisque nous l’avons ouvert, restons sur le même classeur :

intro excel fin td protection 2e mot de passe.xls

La mise en place de la protection passe par

Outils Protection Protéger la feuille

Validez sans mettre de mot de passe, car je commence à m’y perdre.

Constatez les dégâts car toute la feuille se retrouve protégée.

Ok pour les formules de calcul – colonnes G à J, ok pour les formats puisque ce sont les miens, ils sont magnifiques …

… mais que dire de l’impossibilité de saisir un taux, un CA, un kilométrage RIDICULE

Empressons-nous d’ôter cette protection !

Outils Protection

Oter la protection de la feuille

Par défaut, Excel protège toutes les cellules du classeur. Il nous faut donc, au préalable, indiquer les cellules qui ne seront pas verrouillées au moment de la mise en place de la protection.

Lesquelles ?

En fait, les trois paramètres B4:B6 et les données relatives aux vendeurs A9:F13

Page : - 48 -


Sélectionnez les cellules … qui ne seront pas verrouillées

Format Cellule onglet Protection

Il suffit de décocher la case verrouillage

Validez et vérifiez qu’il ne se passe strictement rien tout reste accessible.

C’est maintenant qu’il faut mettre en place la protection comme nous l’avons fait plus haut.

Elle concernera toute la feuille sauf les cellules annoncées non verrouillées.

Effets : Impossible de modifier les formules, de changer les formats, y compris pour les cellules laissées libres d’accès, d’adapter la largeur des colonnes ou les hauteurs de lignes, d’insérer ou supprimer lignes ou colonnes on ne fait qu’utiliser la feuille qu’un autre a conçue.

Paramètres de protection : au moment de la mise en place par Outils Protection Protéger la feuille, il vous suffit de cocher les cases correspondant aux libertés que vous entendez accorder

Pratique : pour faire défiler les cellules auxquelles l’accès est laissé, utilisez la touche de tabulation.

Rappel : pour ôter la protection Outils Protection Oter la protection de la feuille

A retenir : il existe deux niveaux de protection, l’un empêche les utilisateurs d’ ouvrir le classeur, l’autre protège contre les modifications de contenu, en particulier les formules de calcul

.

Page : - 49 -

La mise en page et l’impression

9. Mise en page et impression Cette partie est fort bien présentée sur le site de Karine Petit, leçons 22,23 et 24 : http://www.petitkar.com/. Egalement sur un site canadien de grande richesse : http://www.lecompagnon.info/excel/impression.htm

Je me contenterai donc de donner une application pratique.

Ouvrez le classeur : intro excel td protection 2e mot de passe.xls

Rappel des mots de passe : r2d2 pour le premier et c3po pour le second.

Commençons par faire un aperçu avant impression : ou Fichier Aperçu …

Il est sûr qu’en format portrait, tout ne tient pas sur une page …

En revenant à la feuille par Fermer, on peut voir les pointillés, non imprimables, qui délimitent les pages.

Délimiter la zone d’impression : par défaut, Excel imprime toute la feuille. A vous de

délimiter avec l’outil ou sélectionner la zone à imprimer puis Fichier Zone d’impression Définir

Page : - 50 -

http://www.petitkar.com/

http://www.lecompagnon.info/excel/impression.htm


Préparer la mise en page : Fichier Mise en page

4 onglets sont à votre disposition.

Onglet Page

L’inévitable Portrait ou Paysage mais surtout, la possibilité de forcer Excel à n’imprimer par compression automatique que sur un nombre de pages à définir.

Ceci est fort pratique pour éviter le bricolage sur les largeurs de colonnes ou les marges quand tout se joue sur un ou deux centimètres.

Faites l’essai, en mode portrait, ici peu pertinent d’un point de vue esthétique. La compression est de 51%.

Plus intéressant, le tableau même en mode paysage, à 100%, ne tient pas sur la page.

Un ajustement sur une page, sans toucher à autre chose, est obtenu avec un taux de 78%

Onglet marges Pas de commentaire particulier à faire quant à son mode d’emploi mais, pour des tableaux n’occupant pas toute la page, optez pour centrer sur la page horizontalement et/ou verticalement. Cela permet de soigner la présentation.

Page : - 51 -


Onglet En-tête/pied de page

Pour définir ce qui sera reproduit à chaque page imprimée, soit en haut soit en bas de page, cliquez sur l’une des deux personnalisations.

Vous déterminerez l’emplacement, puis correctement positionné, pourrez insérer du texte, une image ou logo, le numéro de page,

Description des Outils (source K. Petit) :

Permet de mettre en forme (souligné, taille) du texte saisi

Insère le numéro de page [page]

Insère le nombre total de page de la feuille de calcul [pages]

Insère la date [date]

Insère l’heure [heure]

Insère le chemin d’accès au fichier sur le disque dur ou la disquette

Insère le nom du classeur [fichier]

Insère le nom de la feuille [onglet]

Insère une image du disque dur

Format de l’image

Page : - 52 -


Onglet Feuille

Cet onglet mérite toute votre attention.

On retrouve la possibilité de définir la zone d’impression et, surtout, pour les grands tableaux – j’y reviendrai dans la 2e partie du cours - la possibilité de répéter à chaque page imprimée, des lignes ou colonnes déterminées les titres de colonnes par exemple.

Pour des raisons pédagogiques de construction de ce cours, j’ai demandé à imprimer les numéros de lignes et lettres de colonnes, afin que vous puissiez vous situer plus facilement. En matière de gestion ou de relation professionnelle, cela ne présente guère d’intérêt décochez la case En-têtes de ligne et de colonnes.

Si la case quadrillage reste cochée, Excel imprime non seulement vos bordures, mais aussi le quadrillage standard gris pâle. La décocher, permettra de n’imprimer que vos propres bordures

Page : - 53 -


Complément sur les sauts de page (source K. Petit, Excel fiche 24) Si un document dépasse la longueur d’une page, EXCEL le divise en plusieurs pages en fonction des marges définies lors de la Mise en page.

Les sauts de page sont indiqués par un trait de tirets. Les sauts de page manuels sont toujours affichés, les sauts de pages créés automatiquement par EXCEL sont visibles uniquement si la case du menu Outils Options Affichage Sauts de page automatiques est cochée.

Les sauts de page sont visibles lors de l’aperçu avant impression. Ils sont enregistrés en même temps que le document.

Insérer un saut de page Pour que le saut de page soit juste inséré avant une cellule, placez le curseur :

dans une cellule de la première colonne pour avoir un saut de page horizontal.

dans une cellule de la première ligne pour avoir un saut de page vertical.

dans une cellule quelconque pour créer un saut de page horizontal et vertical.

Cliquez la commande Insertion Saut de page.

Des traits pointillés apparaissent définissant le saut de page.

Annuler un saut de page Se positionner à l’endroit du saut de page manuel.

Cliquez la commande Insertion Supprimer le saut de Page.

Aperçu des sauts de page L'option Aperçu des sauts de pages vous donne une idée de votre mise en page à partir du tableur et non par l'aperçu avant impression.

Allez dans le menu Affichage Aperçu des sauts de pages. Excel vous donne un message pour vous informer qu'il est possible de déplacer les sauts de pages pour mieux répondre à vos besoins.

Excel va ensuite vous indiquer sur quelle page le contenu de votre feuille de calcul va apparaître. Les sauts de pages apparaissent sous la forme de lignes de couleur bleu.

Pour revenir à la présentation normale, allez dans le menu Affichage Normal.

Page : - 54 -

Les graphiques

10. Les graphiques Comme pour l’impression, consultez la fiche 34 de K. Petit, extrêmement riche en la matière. En toute hypothèse, les variantes sont considérables et le goût artistique de chacun prépondérant. Je m’en tiendrai donc à l’essentiel, tout en comptant fermement sur votre sens de l’imagination créative.

Ouvrir le classeur : intro excel td graphique .xls

Choisir la feuille TD Graphique

10.1. Les données du graphique En préalable, vous devez sélectionner les éléments à incorporer en vous rappelant que la touche CTRL permet d’ajouter des zones non voisines.

Dans un premier temps, jouons à ce qu’il ne faut pas faire …

Sélectionnez seulement de B4 à E7 puis entrez dans l’assistant graphique par Insertion

Graphique ou plus simplement par l’outil

Immédiatement, avant même de choisir un type quelconque, cliquez sur Maintenir appuyé pour visionner. Constatez en quoi la sélection des composantes initiales est inadéquate.

Un graphique présentant une légende avec séries 1, série 2 etc. ainsi qu’un axe des abscisses sous la forme 1,2,3,4 est INDIGNE

Inutile d’aller plus loin, annulons et reprenons sur de meilleures bases.

Page : - 55 -

Les graphiques

Initialement vous DEVEZ sélectionner, les données mais également les en-têtes de lignes et de colonnes

Allez, allez A3:E7, et Maintenir appuyé pour visionner

Une légende, des étiquettes sur l’axe des abscisses.

Voilà qui sera quand même plus présentable.

10.2. L’assistant graphique 10.2.1. Ecran 1 : type du graphique

Types standard, Type personnalisés ?

Vous avez l’embarras du choix.

A chaque type, ses sous-types et toujours le recours au bouton permettant une prévisualisation.

Page : - 56 -

Les graphiques

10.2.2. Ecran 2 : données sources du graphique Eventuellement modifiez le choix lignes ou colonnes pour inverser les données.

En lignes :

CA par trimestre selon les zones

En colonnes :

CA par zone selon les périodes

Page : - 57 -

Les graphiques

10.2.3. Ecran 3 : options du graphique C’est ici que vous aurez à donner un bon nombre de précisions, sachant néanmoins que vous pourrez revenir à cette page, même après avoir quitté l’assistant.

A chaque ajout, l’écran de prévisualisation permet d’en évaluer la pertinence.

Titres

Quadrillage J’ai ajouté le quadrillage de l’axe des abscisses

Etiquette de données Cliquez sur Valeur affiche les données chiffrées

Page : - 58 -

Les graphiques

Table de données : Rappelle le tableau de données sous le graphique.

Peut être opportun sur une feuille indépendante

10.2.4. Ecran 4 : emplacement du graphique

Encapsuler le graphique dans la feuille où se situe le tableau de données est à réserver à des graphiques « peu élaborés ». Dans le cas contraire, il vaut nettement mieux le créer sur une nouvelle feuille.

Comme d’habitude, commençons par l’ajouter à la feuille actuelle.

Quel est l’intérêt de rappeler dans ce cas la table des données ?

Afficher les valeurs eut mieux convenu …

Retour aux options :

Graphique

Options du graphique

Nous revoilà à l’écran 3, prêts à cocher la case Valeur dans l’onglet Etiquettes de données et à décocher Afficher la table de données dans l’onglet Table de données.

Page : - 59 -

Les graphiques

Peut-être serait-il mieux sur une feuille indépendante ? Inutile de le supprimer et de tout refaire Graphique Emplacement et demandez cette fois-ci nouvelle feuille.

Cela rend mieux. Même si ici, la table des données eut pu s’avérer opportune.

Page : - 60 -

Les graphiques

10.2.5. Travaux sur un graphique réalisé Comme on vient de le voir, la commande Graphique est intéressante :

Type de graphique : permet de revenir à la page 1 de l’assistant.

Données source : page 2 de l’assistant, donne l’occasion de modifier la source de données ou ajouter des séries

Options : permet d’accéder à la page 3.

Emplacement : page 4, nous autorise à passer d’une feuille indépendante vers la feuille du tableau source du graphique et vice versa.

Pour les autres tâches, il suffit de retenir double clic sur l’élément à travailler.

Et là cela relève du jubilatoire …

Double-clic sur l’axe des ordonnées

J’ai mis l’échelle à un minimum de 10 000.

Chaque onglet permet de modifier police, taille, couleur … de l’élément choisi. Ici, l’axe des ordonnées.

Faites de même avec l’axe des abscisses.

En quelques clics, j’ai mis un peu mieux en valeur les étiquettes de valeur, l’axe des abscisses.

Avec Affichage Barre d’outils Dessin j’ai inséré une flèche et un commentaire dans une zone de texte. Bien entendu, par double-clic, encore et toujours, j’ai déterminé la taille, la couleur et la police de la zone de texte, ainsi que le forme et l’épaisseur de la flèche après … un double-clic.

On pourrait aussi personnaliser le quadrillage. « Comment » ? Me diriez-vous.

« Par … un DOUBLE-CLIC sur le quadrillage », répondrai-je. Vous pourrez aussi modifier l’aspect et la couleur du quadrillage vertical et/ou horizontal.

Page : - 61 -

Les graphiques

En fait, la seule limite sera votre imagination.

Et ce vilain fond gris, ne pourrait-on pas mettre un motif ou même une image ?

Oh que oui mais commencez par double cliquer sur ce même fond (on ne s’en lasse pas).

En page 1, on peut changer la couleur mais choisissez donc Motif et textures.

Prédéfini puis Tombée de la nuit est très mignon.Aube également. Océan, au fond, est un peu sombre. Bicolore offre toutes les fantaisies de mixage. Essayez l’onglet Texture et ne restez pas de marbre. Et quand vous aurez cessé de jouer, merci d’aviser l’onglet Image.

Page : - 62 -

Les graphiques

Sélectionner une image vous permettra d’insérer une image enregistrée, un clipart ou d’opérer un coller d’image prise sur Internet … pour peu que vous en ayez l’autorisation eu égard au respect des droits d’auteur

Et c’est là que j’entends des voix pour dire que les histogrammes ne se fondent pas dans le paysage. Je vous laisse trouver comment sélectionner le vilain violet de la première série, allons ce n’est pas difficile. Pour les étourdis double-clic …

Page : - 63 -

Les graphiques

On retrouve le même enchaînement d’écrans que pour le fond de grille. Mais, après avoir choisi l’image de ce superbe chalet j’ai demandé d’empiler cette image par unité de 10 000.

Le résultat est lamentable en termes de lisibilité. Tout ceci n’avait pour seul but que de vous donner les bons outils pour personnaliser vos graphiques.

D’autre part, comme il s’agit de passer aux fonctions d’Excel, il va falloir reprendre son souffle non sans avoir géré un souci que rencontrent de nombreuses personnes avec les séries chronologiques.

Page : - 64 -

Les graphiques

10.2.6. Gérer le problème des séries chronologiques Passez sur la feuille Graphe chronologique en cliquant sur son onglet.

Comme vous venez de l’apprendre en début de ce point, vous voilà en train de saisir titres et données soit : A2:D5

Vous entrez dans l’assistant, et avec grande discipline, dès l’écran 1 vous faites une prévisualisation patatras !!!

Horreur, les années sont considérées comme une série, la première à gauche.

Effectivement, nous avons bien des valeurs représentées proches de 2000 …

On constate, concomitamment, l’erreur en matière de légende.

Pendant longtemps, j’ai utilisé une solution passant par la page 2 de l’assistant mais un étudiant m’a montré nettement mieux.

« Effacez donc provisoirement l’en-tête années en A2 », me dit-il à cet instant. « Ne changez rien à la procédure en sélectionnant toujours A2:D5 ». Discipliné je devint et la lumière fut.

Pour ne rien gâcher, réécrire le texte années en A2, une fois le graphique terminé, ne gêne en rien.

Avant de passer à plus sérieux, faites un graphique relatif aux kilomètres parcourus dans la feuille Ventes mettant en œuvre tout ce que vous venez de découvrir.

Page : - 65 -

Les fonctions

11. Les fonctions Merci d’utiliser le classeur : td fonctions1.xls

Un tableur est une feuille de calcul électronique. Jusqu’ici, nous n’avons utilisé que les opérateurs classiques et, à l’évidence, il serait impensable d’en rester à un tel niveau de pauvreté.

Une bibliothèque de fonctions est mise à notre disposition, elle est de grande richesse.

Où que vous soyez placé dans la feuille cliquez sur le dans la barre de formules.

En développant la boîte catégorie, vous pouvez vous faire une petite idée de la diversité.

Il n’est absolument pas question, ici, de toutes vous les faire découvrir.

En haut de l’écran, Excel invite à décrire ce que vous attendez en matière de fonction et va vous aider sous forme d’assistant

Nous ne l’utiliserons que très peu ensemble.

Je m’explique en quelques mots sur le peu de recours que j’aurai à l’assistant : je trouve que c’est une entreprise d’abêtissement et pense largement préférable d’exercer notre matière grise et la survie de nos neurones. D’un point de vue pédagogique, je préfère vous amener à comprendre le processus d’écriture des fonctions afin que vous puissiez vous l’approprier et le reproduire. Quitte à utiliser l’assistant ponctuellement, en particulier son

très précieux bouton situé en bas à gauche de la fenêtre.

En tout état de cause, vous serez bien à même de faire comme bon vous semble.

11.1. Syntaxe des fonctions

=NOMFONCTION(argument1 ;argument2 ;…) démarrage systématique par le signe =

puis le nom de la fonction toujours avec ses parenthèses.

à l’intérieur des () soit rien du tout, soit un seul argument soit plusieurs avec le ; comme séparateur. Ne laissez aucun espace où que ce soit.

Page : - 66 -

Les fonctions

11.1.1. Fonctions sans argument L’argument – comprendre précision – se place dans les parenthèses. Si la fonction ne nécessite aucune précision, les parenthèses sont toujours obligatoires mais resteront vides.

Vérifions immédiatement, activez la feuille Essais et en A1 tapez =maintenant

Diantre, que s’est-il passé ?

Elémentaire mon cher. =maintenant est interprété comme : « la cellule prend la valeur de la cellule nommée maintenant ». Vous l’avez déjà rencontré pas plus tard que dans le cas PRESTIGE repris dans la 1ere feuille de ce classeur. Allez-donc jeter un œil en F11. Constatez que pour reprendre le fixe, nom donné à la cellule C5, il a suffi de poser =Fixe. Passez le fixe à 45,00 €, la colonne F est bien mise à jour.

Revenez sur Essais et enfonçons le clou. Sans toucher à votre formule, écrivez 3000 en B1 et nommez la cellule maintenant. Tout à fait logiquement, mais absurdement pour nous, A1 prend la valeur 3000, puisque nous avons sottement demandé de reproduire le contenu de la cellule nommée maintenant. CQFD.

A retenir : tout nom de fonction est suivi d’une paire de parenthèses.

Il est temps de corriger A1 et d’écrire =maintenant(). Le résultat ne se fait pas attendre, notez quand même que, validant la fonction, Excel l’écrit en majuscules. L’heure inscrite me rappelle qu’il est temps de faire une pause …

On préfèrera la fonction =AUJOURDHUI() qui renvoie la date du jour.

Autre exemple =ALEA() qui affiche un nombre réel compris dans l’intervalle [0,1[

en A5, saisir =ALEA() puis recopier un certain nombre de fois vers le bas.

Merci de ne pas vous étonner d’avoir des chiffres différents, puisque nous parlons de hasard. Appuyez sur la touche F9 et constatez que le recalcul vous permet d ‘avoir une autre série et … la mise à jour de l’heure, si vous avez conservé =MAINTENANT() en A1.

J’entends des langues fielleuses demander, à voix basse de peur des représailles : « d’accord, mais quel intérêt ?».

Activez la feuille HASARD, certes j’y utilise des fonctions que nous étudierons sous peu, mais cela peut vous donner des idées … ou éviter d’être désagréable.

Page : - 67 -

Les fonctions

11.1.2. Fonctions avec un argument Rappel : argument peut s’interpréter comme précision.

Ainsi je veux la valeur entière d’un nombre =ENT(nombre)

ENT() est la fonction qui extrait une valeur entière. Entre les parenthèses, je précise « de quoi ».Nous allons donc rencontrer les grands classiques :

=SOMME() =MIN() =MAX() =MOYENNE() =ECARTYPE()

Ne cherchez pas la bébête, =ECARTYPE() s’écrit bien ainsi

La fonction SOMME() étant tellement utilisée, vous pourrez gagner du temps en cliquant

sur l’outil sans même taper le traditionnel signe =

Retournez-donc sur la feuille Prestige et remplissez le tableau des statistiques après avoir renseigné la ligne 20 des totaux. Il est évident que vous utiliserez les zones nommées comme il se doit.

Trouvez ci-dessous les formules pour les totaux puis pour les statistiques

Savourez votre bonheur de progresser et venez vous horrifier de ce qui suit pour peu qu’un minimum de rigueur soit votre exigence. Faisons un petit zoom sur HT, TVA et rappel Francs. Cherchez l’erreur …

Avez-vous trouvé ? Encore un peu … Stop ! Additionnez les derniers chiffres des centimes : - HT finale 5 pour un total xx,13 €, de même pour la TVA, finale 5 et un total xx,37 € et pour les francs 7 et xx,16 €

Page : - 68 -

Les fonctions

Ne pas confondre le format à deux décimales et l’arrondi à deux décimales

Pour certains le problème est marginal mais les calculs sont faux. D’autre part en matière légale, la TVA est arrondie à deux décimales, il en va de même pour la conversion franc-euro. Vous avez bien lu calcul arrondi et non présentation arrondie par un format qui fausse les calculs.

Pour illustrer le problème, faisons un exercice simple. Saisissez ces données et faites la multiplication pour avoir le poids total.

Bravo !

Sélectionnez les trois cellules et mettez au format avec 0 décimale et le séparateur de milliers :

Si le résultat ne vous choque pas, ne lisez surtout pas la suite et n’apprenez donc pas à arrondir vos calculs …

Problème : présenter le poids sans décimale, n’empêche en rien que le calcul se fasse sur la valeur saisie soit

99,24. Et 99,24 * 100 = 9 924.

Cliquez sur G24, la barre de formule annonce bien un contenu réel de 99,24 affiché 99.

Page : - 69 -

Les fonctions

Nous allons donc utiliser la fonction =ARRONDI() qui nécessite deux arguments.

11.1.3. Fonctions avec deux arguments et plus Retenez que les arguments – précisions – resteront dans les parenthèses et seront séparés par le ; Nous en profiterons également pour intégrer des fonctions dans les fonctions.

Revenons à notre problème relatif aux HT, TVA et HT en francs.

Analyse pour le HT =TTC/(1+TXTVA)

Que dois-je faire ? Arrondir le calcul, certes mais à combien de décimales 2

Le calcul sera la première précision donc le premier argument et le nombre de décimales le second. Quant à la fonction, on s’en serait douté, =ARRONDI() fera l’affaire.

D’où l’écriture =ARRONDI(TTC/(1+TXTVA);2)

Ne retapez pas tout. Insérez après le = arrondi( puis après la parenthèse fermant le calcul ;2) (rappel : la saisie en minuscule est passée automatiquement en majuscule lors de la validation).

Après avoir fait de même pour les deux autres calculs, le résultat devient nettement meilleur, pour peu que vous preniez la peine de vérifier les additions aux centimes.

Faisons de même pour arrondir la moyenne à une décimale :

« Je veux l’arrondi de MOYENNE(KM) précision 1 et à une décimale, précision 2 ».

Sans souci aucun, ni questionnement inutile =ARRONDI(MOYENNE(KM);1)

Page : - 70 -

Les fonctions

Merci de le saisir en E25 pour comparer.

L’arrondi se fait au plus proche soit 1067,8 km. Augmentez le nombre de décimales, on lit bien 1067,80 km, puis 1067,800 km. Le calcul a bien été arrondi et ne présentera aucun problème si on utilise ultérieurement la moyenne dans une autre formule.

A droite, j’ai utilisé les fonctions =ARRONDI.INF() et ARRONDI.SUP() pour lesquelles je pense ne pas avoir à vous expliquer le type d’arrondi effectué.

Dans le même ordre d’idée soyez vigilant à la différence entre l’arrondi à l’entier le plus proche et la valeur entière.

=ARRONDI(MOYENNE(km) ;0) renvoie 1 068 km.

La valeur entière qui relève de la fonction ENT() renverra 1 067 km.

Puisque nous y sommes : j’utilise la fonction ENT() appliquée à MOYENNE(km)

Avez-vous une autre précision à apporter ? Non.

Donc il n’y aura qu’un seul argument =ENT(MOYENNE(km))

Pour les « moyens en maths », rappelons que la valeur entière de -3,98 est -4. Si vous n’en êtes pas persuadé, saisissez quelque part dans la feuille =ENT(-3,5)

Puisque nous y sommes, éclairons notre tirage de dés sur la feuille Hasard et analysons ensemble.

=ALEA() renvoie un nombre réel sur l’intervalle [0,1[ soit 0,9999999999 maximum.

=ALEA()*6 renvoie donc un nombre réel sur l’intervalle [0,6[ donc 5,99999999 maximum.

=ENT(ALEA()*6) en prend la valeur entière soit un entier de 0 à 5.

Pour finir, ajoutons 1 pour retrouver la face du dé 1 à 6 : =ENT(ALEA()*6)+1

Page : - 71 -

Les fonctions

11.1.4. Fonctions et grands classiques de dates et textes Vous possédez déjà un beau catalogue, lesté de quelques inutilités à vos yeux, je vous le concède. Passons à des incontournables que je traiterai par l’exemple, pour mieux vous convaincre de leur intérêt.

Merci d’ouvrir le classeur td fonctions2.xls et d’utiliser la feuille RH

Donnez les noms en sélectionnant le tableau complet puis Insertion Nom Créer sans omettre de décocher Colonne de gauche comme vous avez si bien appris à le faire.

Travaux sur les dates :

Nous avons déjà rencontré la fonction =AUJOURDHUI() qui servira par exemple pour calculer le nombre de jours de retard après l'échéance d'une créance.

Même si les exemples à venir relèvent du ludique, soyez convaincu que pouvoir extraire l’année et le mois d’une date de facture sera extraordinairement utile pour répondre à des demandes telles que : « faites-moi un tableau des ventes mensuelles sur les 3 dernières années » ou « on veut nous faire fermer le dimanche, réalisez-moi une étude sur notre activité selon les jours de semaine ».

Colonne E : je souhaite extraire l’année de la date de naissance. Aurez-vous grand mal à trouver la fonction ANNEE() dont on relèvera l’absence d’accent ?

Cette fonction s’applique à la date de naissance sans autre précision – argument – nécessaire. Soit =ANNEE(Date_naissance)

Comme je ne doute pas un instant de vous, je vous sens déjà bondir pour renvoyer le mois avec MOIS() et le jour avec JOUR(). Allons, allons, dépêchons. Mettez le 10/05/81 en C2 et vérifiez que les résultats sont corrects.

Page : - 72 -

Les fonctions

Et le jour de semaine ? Une recherche dans la catégorie des fonctions de type date/heure vous mène sur JOURSEM().

En H2, nous voilà donc en train d’écrire =JOURSEM(Date_naissance).

Et la réponse est 1 pour le 10/05/81 qui chacun sait fut un dimanche (jour de liesse pour certains et de tristesse pour d’autres).

Excel renvoie donc 1 pour dimanche, 2 pour lundi et donc 6 pour samedi.

Ben oui quoi, les anglo-saxons commencent la semaine un dimanche que nous avons traduit par Week-end. Cherchez l’erreur.

Comment revenir sur la terre ferme et avoir le lundi au rang 1 et le dimanche en 7 ?

Allez donc voir l’aide de la formule et vous aurez la réponse !

Vous voilà revenu avec la réponse préciser en supplément la valeur 2.

Il suffit d’appliquer ce que je me tue à vous faire comprendre depuis des pages, ajoutez un ; et 2 =JOURSEM(Date_naissance;2). Pas belle la vie ? 10/05/81 7

Petite récréation de trois secondes pour taper votre date de naissance et vérifier quel jour de la semaine vous naquîtes.

Fonctions de texte :

Vous n’êtes pas sans connaître la propension des informaticiens à coder les données.

Le matricule de Mr Henri Duchemin, 96AC001 doit se traduire ainsi : entrée en 1996, au service AC avec le rang 001.

A vous de calculer son ancienneté, et une prime qui sera différente selon que l’on appartient au service AC (commercial) ou AD (administratif). Avec un tel codage, vous fussiez bien déconfit … avant de suivre cette meeeeeeeeeerveilleuse formation.

Note : la solution à venir ne pourra pas gérer une embauche postérieure à 1999 car celle-ci nécessite du SI() qui, vous le subodorez ne saurait trop tarder.

En I2, nous souhaitons afficher l’année d’embauche.

« J’extrais les 2 premiers caractères à gauche de matricule et j’ajoute 1900 ».

Un coup d’œil sur la liste des fonctions catégorie texte nous indique GAUCHE().

GAUCHE() de quoi ? De matricule (argument1).

Combien de caractères ? 2 s’il vous plait (argument 2)

Pour faire bonne mesure, on ajoutera 1900.

Soit =GAUCHE(Matricule;2)+1900

96AC001 donnera sur sa gauche et de longueur 2 96 et si on ajoute 1900 1996.

En J2 nous voulons l’ancienneté

Si l’on est en 2006 =2006-Année_embauche (pensez à nommer au fur et à mesure).

Oui mais en 2007, 2008 etc. ?

Il serait mieux de remplacer 2006 par la l’année de la date du jour.

Vous vous souvenez ? C’est tout récent.

Date du jour =AUJOURDHUI()

Extraire l’année d’une date ANNEE()

DONC =ANNEE(AUJOURDHUI())-Année_embauche

Page : - 73 -

Les fonctions

En K2, nous souhaitons le rang, soit à droite de matricule une chaîne de caractères de longueur 3.

A gauche, GAUCHE() !, à droite ? DROITE() … comme à l’armée.

=DROITE(Matricule;3)

On relèvera que, bien qu’affichant 001 pour l’ami Duchemin, bien que parlant plus haut de chaîne de caractères et pas de nombre, on pourra faire du calcul sur ce rang.

En L2, nous voulons le code du service.

La fonction est, ici, difficile à trouver seul. Ce n’est pas MILIEU() ni CENTRE() car on doit extraire à partir de n’importe où à l’intérieur – ni tout à gauche ni tout à droite -

Elle vient d’une contraction : chaîne de caractères se dit « string text » en anglais. Je reprends « String TeXT d’où STXT()

Donc par STXT(), j’extrais dans code (argument 1) à partir du caractère en position 3 (argument 2), une chaîne de longueur 2 (argument 3).

Comme toujours, les arguments sont séparés par un ; et il se trouve que trois arguments sont à fournir =STXT(Matricule;3;2)

Petit interlude : comment extraire le sexe, puis le département de naissance d’un numéro INSEE ? 1821042156147 nommé numsec.

Sexe : =GAUCHE(numsec;1)

Département : =STXT(numsec;6;2) (on démarre au rang 6 pour une longueur 2)

A vous de déterminer l’année de naissance, avant 2000 s’entend.

Page : - 74 -

Les fonctions

Comme si vous n’aviez pas assez souffert à cause de ce codage du matricule, ne voilà-t-il pas que vous vous apercevez que les identités ont été saisies, ou importées d’Internet, sous la forme catastrophique de « Mr Henri Duchemin ».

Comment offrir un bouquet au personnel féminin (Civilité différente de Mr) ?

Comment souhaiter la fête de nos collaborateurs sans le Prénom ?

Comment, plus sérieusement, éditer une liste alphabétique avec Nom + Prénom ?

Tout informaticien, même débutant, vous rappellerait que les informations mémorisées doivent être non décomposables (1ere forme normale non respectée ici) et qu’il eut donc été judicieux de saisir dans trois cellules distinctes : Civilité puis Prénom puis Nom.

Malheureusement, le mal est fait et des centaines ou milliers d’identités sont ainsi mémorisées. On aura du mal à trouver un volontaire prêt à faire la correction manuellement.

Imaginez votre aura immédiate quand vous arriverez désormais tel le sauveur de l’humanité (on dit sauveuse au féminin ?). Je plaisante à peine car le cas présenté se rencontre très souvent mauvais listing tiré d’un logiciel commercial mal paramétré ou mauvaise récupération d’informations saisies sur Internet.

Faites une pause, gardez la tête fraîche car cela va chauffer pour les neurones.

En M2 pour obtenir la civilité, je ne m’inquiète guère, vous savez faire :

=GAUCHE(Identité;2)

En N2 on désire afficher le prénom suivi du nom.

Enlever trois caractères sur la gauche à Identité n’a pas de fonction associée sur Excel du type ROGNER() 1. Lisez bien 3 caractères et non deux car l’espace est un caractère.

Analyse : « comme l’identité est de longueur variable d’un salarié à l’autre, je dois extraire d’identité, à partir de la droite, le nombre total de caractères de cette même identité moins les trois à éliminer. »

Ici, pour vous aider, j’ai affiché le nombre de caractères de l’identité de chacun.

Vérifiez 17 pour le premier y compris les espaces, 14 pour la seconde etc.

Si je prends, à partir de la droite, cette longueur totale moins trois, j’obtiens bien ‘prénom et nom’.

1 L’auteur de ce cours recevra volontiers toute suggestion visant à simplifier la solution proposée

Page : - 75 -

Les fonctions

Comment compter le nombre de caractères ? Il suffit d’utiliser la fonction NBCAR() qui permet de compter le nombre de caractères d’une chaîne,.

Donc la longueur à extraire sera : NBCAR(identité)-3

=DROITE(Identité;NBCAR(Identité)-3)

Pendant que vous le faites, imaginez sans les noms à quoi ressemblerait la formule :

=DROITE(B2;NBCAR(B2)-3) question lisibilité ce n’est pas le top.

Vérifiez que cela fonctionne avec votre identité personnelle en B2.

Pensez à nommer Prénom_et_nom la sélection N2:N19.

En O2 on veut le prénom.

Là aussi le problème est que tous les prénoms ne sont pas de la même longueur.

Si on trouve cette longueur, il « suffit » d’extraire sur la gauche d’identité une chaîne de cette longueur.

Je vous aide Henri Duchemin, le prénom fait 5 caractères car il s’arrête à l’espace qui est en 6e position.

Sautons à Paul Lecat en ligne 5, avec le même raisonnement, l’espace est en position 5 donc le prénom fait 4 caractères (position de l’espace -1).

Le salut vient donc de notre capacité à obtenir la position d’un caractère donné dans une chaîne C’est la fonction CHERCHE() dont une judicieuse lecture de l’aide vous explique la syntaxe : CHERCHE(‘’quoi’’;où). Notez que le caractère recherché est à mettre entre ‘’ ‘’ ET que, si c’est l’espace, et c’est notre cas, il faut le laisser à l’intérieur des ‘’ ‘’.

Essayons déjà : =CHERCHE(‘’ ‘’;Prénom_et_Nom) dans l’idée d’avoir un nombre représentant la position de notre espace. C’est vital.

Vérifiez … superbe

Il ne reste, quel euphémisme, qu’à prendre sur la gauche de Prénom_et_Nom cette valeur -1

=GAUCHE(Prénom_et_nom;CHERCHE(‘’ ‘’;Prénom_et_Nom)-1)

On y est presque.

Page : - 76 -

Les fonctions

En P2 on veut récupérer le nom.

La solution est facilitée par la présence du prénom en colonne O : je prends sur la droite de Prénom_et_Nom, la longueur totale de la chaîne de caractères moins la longueur du Prénom puis encore -1 à cause de l’espace.

Henri Duchemin : longueur totale 14

Longueur du prénom Henri 5 -1 de plus pour l’espace soit 6

14 – 5 - 1 font 8. C’est bien la longueur de la chaîne « Duchemin ».

La Force est avec nous … si vous avez nommé Prénom O2:O19.

=DROITE(Prénom_et_nom;NBCAR(Prénom_et_Nom)-NBCAR(Prénom)-1)

En gras la longueur ci-dessus évoquée.

En Q2 on veut associer Nom et Prénom (avez-vous nommé Nom P2:P19 ?)

En fait on veut former une nouvelle chaîne de caractères. Les latinistes distingués, quelques dinosaures sont encore en vie, reconnaîtront « CUM CATENARE », en français faire chaîne avec CONCATENER().

CONCATENER() quoi ? Le nom ; puis un espace ; et enfin le prénom

=CONCATENER(Nom;’’ ‘’;Prénom)

Sur les chaînes de caractères le signe + n’existant pas, le ; joue le rôle.

On aurait pu également utiliser le & qui eut évité la fonction CONCATENER() en écrivant simplement =Nom&’’ ‘’&Prénom. Avec des nombres, cela revient en fait à écrire =SOMME(2;3) alors que un simple =2+3 eut fort bien suffit.

Il vous reste une dernière chose à prendre en considération Ne conserver que les trois colonnes où nous avons, enfin, pu récupérer Civilité, Nom et Prénom.

Malvenu serait celui qui supprimerait les colonnes primitives sans réfléchir.

Page : - 77 -

Les fonctions

Comme vous en avez pris l’habitude, exécutons la fausse manœuvre après avoir enregistré le corrigé au cas où le retour en arrière serait inopérant…

Sélectionnons gaiement B1:B19.

Edition Supprimer avec un décaler vers la gauche …

Et c’est la catastrophe annoncée !

Réfléchissez donc quelques secondes. « Eureka », vous entends-je à distance vous écrier. « Tout a été obtenu par des formules annoncées par un signe =, donc si la source est supprimée, le travail d’extraction devient impossible », ajoutez-vous.

« Que oui » vous réponds-je en écho.

Il faudrait garder les résultats obtenus mais sans faire référence aux formules. Essayez donc la bonne recette du tonton.

1) Sélectionnez L2:P19 soit toutes les extractions réalisées à partir de l’Identité.

2) Exécutez un très classique Edition Copier

3) En L2, soit à l’endroit même de vos traitements qui vont être écrasés, lancez Edition Collage spécial en prenant bien soin de cocher la case Valeurs.

Vérifiez. Désormais nous avons un texte tout à fait classique.

Vous venez donc de découvrir comment transformer le résultat d’un calcul ou de tout traitement par son seul résultat.

Certes les formules sont perdues, car écrasées par le Collage spécial, mais la source, ici l’identité, peut être supprimée. Rappelons-le, nous l’avions fermement remise en cause dès l’origine.

Une fois l’erreur corrigée, ne gardons que le résultat.

Page : - 78 -

Complément : le collage spécial

11.1.5. Complément sur le collage spécial Découvrons plus avant cette commande Collage spécial qui se révèle très pratique.

Restons sur le classeur td fonctions2 .xls et activons l’onglet Collage spécial.

L’exercice qui suit se prêtera à l’apprentissage des macro-commandes dans la deuxième partie de ce cours sur le tableur.

La présentation en colonnes vous déplait, vous souhaiteriez lire le tableau verticalement, autrement dit avoir les produits en lignes.

Sélectionnez le tableau A3:I6

Faites-en un Copier par tout moyen à votre convenance

Placez-vous en A8 et, au lieu de faire un simple Coller, faites Edition Collage spécial puis, sur le bas et au milieu de l’écran, cliquez sur Transposé

Il ne reste qu’à supprimer les lignes 2 à 6 de l’ancienne version.

Vous voilà à même de résoudre un embarras assez courant lors de l’import de données à partir d’Internet ou du traitement d’un tableau dont la présentation est à inverser.

Poursuivons donc. Nous venons de voir que le Collage spécial Valeurs permet de ne recopier « que » les résultats des formules. Nous allons l’expliciter avec la mise à jour du stock initial pour le mois suivant.

Commencez par calculer le stock final, après avoir nommé les cellules par un désormais classique Insertion Nom Créer.

En D4 vous montez cette formule =Stock_initial-Sorties puis recopiez de D4 à D11

Page : - 79 -

Complément : le collage spécial

Il s’agit pour le mois suivant de reporter ce qui sera la nouvelle valeur du Stock initial à l’ouverture du document

Un copier/coller serait catastrophique, car on chercherait à recopier un formule de calcul sur le stock initial qui comporterait une référence sur ce même Stock initial message d’erreur de références circulaires.

Un Collage spécial Valeurs va résoudre le problème.

Essayez cela fonctionne sans problème.

Encore plus fort mais juste pour que vous connaissiez la fonctionnalité, nous allons nous passer du calcul du stock final. Supprimez la colonne D.

Sélectionnez les sorties C4:C11 Positionnez-vous en B4 Et Edition Collage spécial Cette fois-ci veillez à cocher dans le cadre du milieu Opération Soustraction

OK

Page : - 80 -

Application sur les fonctions

Revenez sur la feuille FC et essayez de faire l’exercice sans aide.

En principe, sauf la fonction MAJUSCULE(), vous savez tout faire.

Solution :

Bien sûr nous nommerons

Prénom B4:B6 Nom C4:C6 CA D4:D6

Lisons le sujet et analysons au fur et à mesure :

accoler & ou CONCATENER()

la 1ere lettre GAUCHE() de Prénom, longueur 1 =GAUCHE(Prénom;1)

cette 1ere lettre est à mettre en majuscule – et pas à la main ! –

Soit =MAJUSCULE(GAUCHE(Prénom;1))

pour le nom c’est la même chose mais en remplaçant Prénom par Nom

=MAJUSCULE(GAUCHE(Nom;1))

concaténer par &

=MAJUSCULE(GAUCHE(Prénom;1))&MAJUSCULE(GAUCHE(Nom;1))

arrondir le CA à une décimale ARRONDI(CA;1) et concaténer au reste …

Je suis sûr que vous avez trouvé cela facile !

Chiche de l’écrire sans les noms et d’essayer de comprendre ?

Il ne reste que la fonction SI(), joli programme, mais prenez du repos avant d’attaquer.

Page : - 81 -

La fonction SI()

11.2. La fonction SI() Ouvrez le classeur td si().xls et, à titre de gymnastique de réveil, merci de préparer le travail :

soignez la présentation, montez le graphique selon le modèle ci-dessous,

nommez les cellules,

calculez le résultat obtenu tout simplement =CA-Objectif

éditez les statistiques en suivant les indications de la zone de texte à droite

Recevez alors deux petits présents pour vous prouver mes bonnes intentions.

Le premier est d’avoir mis cette avancée sur td si() mise en bouche.xls

La seconde est l’illustration du résultat par une mise en forme conditionnelle.

On constate, les contrôleurs de gestion m’en sauront gré, que selon le résultat obtenu, la cellule prend la couleur verte (résultat >=0) ou jaune (résultat<0).

Si vous étiez tenté de le de mettre grâce à un format manuel, je réitère ma menace, quelque peu oubliée, de vous coller 5 000 commerciaux dans la liste.

Mettez l’objectif de Colin à 16 000,00 €, la cellule passe au jaune, inscrivez 15 000,00 €, elle revient au vert. Si vous tiquez sur le vert pour un résultat nul, je vous rappelle la signification d’un résultat égal à zéro objectif atteint. Auriez-vous apprécié que l’on vous refusât le bac avec 10,00 de moyenne générale ? Je ne pense pas.

Page : - 82 -

La fonction SI()

Sélectionnez la zone nommée Résultat puis : Format Mise en forme conditionnelle.

A

Aà

L

AlpLSddCuECphN

Format puis Motif pour la trame de fond

r

Ajouter pour la condition 2 avec un maximum de 3

llons plus loin dans le conditionnel

Si … coprès des heures et des heures pa écrire la formule en respectant un

a fonction SI() avec, à l'intérieur de

=SI(cvant de commencer, je tien

’usage des cellules nomméesrofonde. ’écriture sera une simple forI, ET SEULEMENT SI, vous fae veiller à mener une ana’ordinateur. ette phase incontournable dn papier et un crayon. nfin, je ne saurai trop vouhouard. D’une grande richearticulièrement complet sur ttp://etienne.chouard.free.fr/Inous étudierons 5 thèmes

SI() sur du numérique.

SI() sur du texte

Les SI() imbriqués

SI() sur une échelle de valeu

SI() avec des ET() et des OU

Condition à rempli

avec la fonction SI()

ndition … alors … sinon. ssées sur ce cours, vous n’avez aucun mal désormais e syntaxe bien rabâchée.

s parenthèses, trois arguments séparés par un ;

ondition;alors;sinon) s à rappeler le caractère indispensable de

, sans quoi les formules seront d’une obscurité

malité – conventions de langage à respecter – ites l’effort de bien lire ce qui est demandé et lyse préalable … à l’écart de votre écran

e réflexion doit se faire avec pour seuls outils

s recommander d’aller sur le site d’Etienne sse sur les fonctions en général il se révèle cette classique fonction SI() en particulier : fo/index.html

rs

()

= < <= > >= <> p

Page : - 83 -

Les opérateurs à utiliser

our différent

http://etienne.chouard.free.fr/Info/index.html

La fonction SI()

11.2.1. Le SI() sur du numérique H4:H7 la partie variable de la rémunération consiste en une prime versée par application du pourcentage de dépassement lu en B10.

Analyse :

si le résultat est négatif

alors la cellule prend la valeur 0

sinon elle a pour formule : Pourcentage_dépassement*Résultat.

Notez « la cellule prend la valeur » il n’y aura pas d’écriture du type « alors variable = » ou même de signe = dans le Alors ou le sinon.

L’analyse faite, l’écriture est simplissime SANS AUCUN ESPACE

=SI(Résultat<0;0;Pourcentage_dépassement*Résultat)

Que l’on aurait tout aussi bien pu écrire :

=SI(Résultat>=0;Pourcentage_dépassement*Résultat;0)

11.2.2. Le SI() sur du texte Le salariés, de code A touchent un fixe de 1 000 €, les autres 1 200 €

si le code est A

alors la cellule prend la 1000

sinon elle prend la valeur 1200

Notez l’absence de format, il sera posé après.

Ce que vous devez savoir est que le texte est à mettre IMPERATIVEMENT entre ‘’ ‘’. Ceci sera valable à la fois dans la condition et/ou le alors et/ou le sinon.

En tenant compte de cela, l’écriture est sans difficulté toujours SANS AUCUN ESPACE:

=SI(Code=’’A’’;1000;1200)

Saisissez la formule en I4, recopiez vers le bas, vous voilà en plein contentement. Voire !

Reproduisez mes modifications provisoires et constatez par vous-même l’illégitimité de votre joie. B4 a au lieu de A pas de souci ‘’A’’= ‘’a’’ B5 aa pour Excel ‘’aa’’ <> ‘’a’’ ou ‘’A’’ ; la réponse est non donc il applique la règle 1200 B6 c n’étant pas ‘’A’’, la réponse à la condition est NON donc Excel applique ce NON 1200. B7 En fait j’ai tapé A mais avec un espace derrière, invisible à l’œil. Ici aussi ‘’A+espace’’ <> ‘’A’’

Page : - 84 -

La fonction SI()

11.2.3. Les SI() imbriqués Nous venons de toucher du doigt une apparence d’incohérence qui n’est en fait que pure logique. Ce n’est pas une erreur de l’ordinateur, c’est une déficience en matière d’analyse.

L’analyse eut dû être la suivante

si le code est A

alors la cellule prend la valeur 1000

sinon le code est B

alors elle prend la valeur 1200

sinon demander de taper un code A ou B

Merci de prendre tout le temps nécessaire afin de bien comprendre car toute la suite en sera largement facilitée.

1) Avec deux conditions, je gère trois traitements.

En effet, suivez bien sur l’organigramme : si ce n’est pas le code ‘’A’’ et si ce n’est pas le code ‘’ B ‘’, soit une réponse Non aux deux questions, alors je n’ai pas à poser de question supplémentaire … puisque ce n’est ni l’un ni l’autre, j’affiche un message d’erreur. Comme il se doit le message sera entre des ‘’ ‘’.

2) J’en déduis que si avec 2 SI() je gère 3 traitements différents (1 000 , 1 200 et message d’erreur) alors, et c’est à retenir, par extension N traitements possibles nécessiteront N-1 SI(). 3) L’écriture de la formule doit se faire comme une musique. Ne lisez pas la suite, je suis sûr que vous n’avez absolument pas besoin de la solution …

=SI(Code=’’A’’;1000;SI(Code=’’B’’;1200;’’merci de taper code A ou B’’)) Condition1 alors1/sinon1/condtion2 alors2 sinon2

A la fin, on ferme les deux parenthèses celle du 1er SI() puis celle du second SI().

Ne poursuivez pas tant que vous n’aurez pas compris et assimilé aussi bien l’analyse que la syntaxe.

On aurait pu procéder à une validation directe des saisies et refuser une entrée de donnée n’étant ni A ni B. Ceci est traité dans la formation improprement dite de niveau 3, déjà en ligne dans la meilleure boutique. Je veux parler de cette mine de diamants que constitue le site Ecogesam. En fait, cette trousse à outils se voulait un complément proposé aux étudiants, juste avant le départ en stage.

Si vous êtes impatient, sélectionnez les codes, cellules B4:B7, puis passez par Données Validation et … laissez marcher votre sens logique ou … utilisez l’aide.

Lisez la formule au-dessus. Ici, seul le premier code sera accepté. Au lieu de poser 1 200 pour les autres, nous avons bien 3 messages d’erreur. Corrigez et remettez A, B, B, A Tout est rentré dans l’ordre.

Page : - 85 -

La fonction SI()

Un dernier mot avant de poursuivre et bientôt conclure … cette première partie.

Si vous relisez ces formules, l’usage des noms, s’impose une nouvelle fois, sauf contre-indication. Quant à utiliser l’assistant fonction pour l’écriture, cela m’inspire grande méfiance.

Respectez l’ordre : analyse puis retranscription sur Excel en respectant la syntaxe.

Quel que soit le logiciel utilisé, la partie analyse garde un caractère universel.

11.2.4. Les SI() sur une échelle de valeur La prime performance, sise en colonne J, a fait l’objet de l’information suivante :

« Les vendeurs dépassant l’objectif toucheront le bonus. A partir de 1 500 €, ce bonus sera majoré de 50% et même doublé à partir de 2 800 € ».

On pourra lire avec intérêt les commentaires d’Etienne Chouard sur ce point précis où il insiste sur la nécessité de bien interpréter le sujet en matière d’effets de bord.

« Un vendeur, dépassant l’objectif exactement de 1 500 €, touche-t-il le bonus ou 1,5 fois le bonus ? » Ici, la réponse est claire, à qui prend le temps de lire, 1,5 fois le bonus.

Pour éviter les bêtises, ne vous privez pas de monter la frise suivante :

0 1 500 2800 Résultat Prime

Alors que vous eussiez pu être tenté de ne voir que 3 possibilités, en réalité il existe quatre traitements possibles : 0 € car l’objectif n’est pas atteint, Bonus, Bonus * 1,5 et Bonus * 2.

Or ne venez-vous pas d’apprendre que N traitements se géraient avec N -1 SI() soit ici 3 ?

Autres approches :

si le résultat est négatif

alors 0

sinon,

si le résultat est inférieur à 1500

alors Bonus

sinon,

si le résultat est inférieur à 2800

alors Bonus * 1,5

sinon Bonus * 2

0 € Bonus Bonus*1,5 Bonus*2

Page : - 86 -

La fonction SI()

On retrouve dans les losanges les 3 SI() annoncés lors de l’analyse de la frise.

ET on constate que Bonus * 2 n’a pas besoin de condition car si le résultat n’est pas inférieur à 0, si le résultat n’est pas inférieur à 1 500, si le résultat n’est pas inférieur à 2 800 alors, et quoi qu’il arrive, il est toujours supérieur à 2 800. Quel serait l’intérêt de poser une question, dont la réponse est certaine ?

Notez enfin, que jamais nous n’avons eu besoin de poser une condition sous la forme « le résultat est-il compris entre tel et tel montant ? » On opère par éliminations successives en partant du bas de l’échelle de valeurs. Où du haut selon vos convenances.

L’écriture ne devrait vous poser aucune sorte de souci en appliquant ce que vous avez appris en matière de syntaxe :

=SI(Résultat<0;0;SI(Résultat<1500;Bonus;SI(Résultat<2800;Bonus*1,5;Bonus*2)))

On notera les trois parenthèses finales, fermant les trois SI() et la correspondance parfaite entre le travail papier et sa retranscription en une syntaxe propre à Excel. Rappelons-le une nouvelle fois, le travail d’analyse n’est en rien destiné exclusivement à Excel.

11.2.5. Les SI()avec du ET() ou du OU() « Une prime de 300 €, dite Prime durée, est accordée aux seuls salariés de code B, réalisant leur mission en moins de 8 jours ». Là aussi, merci de bien lire l’énoncé.

Ceux ou celles qui ne sont pas à l’aise dans l’abstraction peuvent se donner des exemples.

Code salarié Durée mission Montant de la prime A 10 0 A 5 0 B 8 0 B 6 300

On constate que seul un salarié de code B et mettant moins de 8 jours touche les 300 €.

Par algorithme :

Si le salarié est de code B et dans le même temps fin-Début est inférieur à 8

Alors 300

Sinon 0

En matière d’écriture, je n’ai qu’à vous dire de recourir à la fonction ET().

Où l’utiliser ? Direz-vous. Suivez la flèche ou relisez l’algorithme « dans la condition »

Avez-vous un problème sur le Alors 300 et le Sinon 0 ?

Normalement non. Donc d’ores et déjà vous n’avez aucun mal à écrire ceci :

=SI(ET();300;0) avec le ET() dans la condition, le alors 300 et le sinon 0.

Où placer les conditions à remplir dans le cadre du ET() ? Dans ses parenthèses et séparées par le désormais traditionnel ;

=SI(ET(Code=’’B’’;Fin-Début<8);300 ;0) Attention : si le code est extrait par GAUCHE(), DROITE() ou STXT(), même si on peut calculer dessus, la valeur n’est pas numérique. Exemple : 95B avec l’extrait 95 sera testé ‘’95’’ (voir exercice SALAIRES.XLS).

Page : - 87 -

La fonction SI()

Aurait-on pu utiliser le OU() ? Diable, Oui.

Reprenez le tableau avec les exemples et décrivez comment relever d’une prime nulle.

« Aura une prime de 0 €, celui qui est en Code A ou atteint la barre des 8 jours, les autres auront 300 € ».

=SI(OU(Code=’’A’’;Fin-Début>=8);0;300)

Comparez les deux formules, tout y est inversé mais on arrive bien au même résultat.

Continuez le travail : Calculez le salaire total composé de la partie variable, du Fixe et des deux Primes.

Ressortez la valeur en francs qui peut paraître archaïque à juste titre mais n’a pour seul objet que de vous faire admonester si vous « oubliez » la fonction arrondie.

Avant de protéger la feuille en ne laissant l’accès qu’aux cellules A4:F7 et les deux paramètres en B9 et B10 …

… nous ne saurions terminer cette première partie de cours sans un cadeau respectant la courtoisie et la tradition de la bonne ville du Roi René.

Dommage, il va falloir satisfaire aux conditions suivantes :

« Un magnum de Châteauneuf du Pape Cuvée 1952 est offert aux vendeurs de code A ayant atteint leur objectif. Les autres ne partiront pas les mains vides, ils recevront une boite de … Calissons ».

Si le vendeur est de code A (ouf c’est moi) ET a un résultat supérieur ou égal à zéro (oui oui et encore oui)

Alors Magnum 1952 (quelle merveille, quel souvenir)

Sinon Calissons (sympa pour les amis, mais nous on connaît).

=SI(ET(Code=’’A’’;Résultat>=0);’’Magnum 1952’’;’’Calissons’’)

Tout est sur td si() fin.xls Un petit dernier pour la fin ? On ne va pas se séparer comme cela.

Ben oui quoi, il reste un exercice à faire sur td fonctions2.xls dont vous retrouverez la solution sur td fonctions2 finalisé.xls. Pour vous aider si vous n’y arrivez pas, étudiez les éléments d’analyse ci-dessous à droite.

Je nomme les cellules comme le veut le bon sens. La prime de 500 € concerne les seuls coureurs dont la marque de voiture commence par OPEL (soit 4 caractères à gauche de la marque) ET pour lesquels la place est <=3. On va utiliser le SI() avec du ET(), lequel intègre la fonction GAUCHE() Pas difficile non ?

=SI(ET(GAUCHE(MARQUE;4)=’’Opel’’;Place<=3);300;0)

Page : - 88 -

Exercices de synthèse avec corrigés

Les énoncés sont directement sur chaque feuille principale

Visea.xls

Refonte.xls

Primes compétition.xls

Salaires.xls (un peu plus difficile, demande à bien connaître tout le cours)

---------------------------

Voilà, c'est fini

On a tant travaillé sur le même dossier

On a tellement tiré chacun du même côté

Que voilà c'est fini

Et que vous allez pouvoir vous exercer…

Et donc vraiment bien progresser …

C’est fini

---------------------------

Merci à JLA pour les paroles, KP pour son dossier et Gadwin, LP, SQ, CJ et MM pour les corrections, BL pour la mise en ligne et … JC pour sa patience et sa compréhension.

Au menu de la 2e partie … à venir sous peu Quelques approfondissements sur Excel avec par ordre d’apparition :

La consolidation

Les valeurs cible ou Solveur

La gestion des tableaux de grande taille

Les tris

Les filtres

Les tableaux croisés

La fonction Recherchev()

Les macro-commandes

Et … une initiation au VBA

Là c’est vraiment fini …

Documents

cours tableur partie 1