Atelier excel : manipuler un fichier de budget
Atelier initiation excel à destination des personnels de BU - 1 heure[modifier le wikicode]
- Auteur : Sylvain Machefert
- Date : 24 novembre 2012
La séance se base sur un fichier excel exporté d'un SIGB et reprenant des listes d'acquisitions et a pour but de sensibiliser les collègues, à partir d'un cas concret, aux fonctions de bases d'excel : navigation dans un tableau et utilisation basique des fonctions.
Fichier initial[modifier | modifier le wikicode]
Le fichier utilisé est disponible sur le wiki, c'est un fichier tabulé, qu'excel ouvrira sans problème si vous le renommez en .xls au lieu du .txt.
[modifier | modifier le wikicode]
Quelques exercices simple pour comprendre comment les touches ctrl et shift peuvent permettre de sélectionner facilement une partie de tableau sans avoir besoin de jongler avec les ascenseurs.
- Ctrl + flèche : envoie à la fin de la zone de cellules contiguës qui sont toutes vides ou pleines dans la direction indiquée par la flèche (tester pour voir ce que cela donne, éventuellement après avoir supprimé le contenu de certains cases).
- Shift + flèche : permet de faire une sélection de cellules contiguës et de l'étendre progressivement.
- Shift + Ctrl + flèche : permet de faire des sélections plus importantes (exemple : sélectionner tous les montants des cellules "En cde" et "Dépensé", zone B2 à C192).
Trier le tableau[modifier | modifier le wikicode]
Cliquer tout en haut à gauche du tableau (au dessus du 1 et à gauche du A) pour sélectionner l'ensemble de la feuille de calcul. Puis utiliser le menu "Données > Trier" pour trier le tableau par ordre de tri décroissant (penser à utiliser l'option "Ligne de titres" = oui).
En observant le tableau on s'aperçoit que le tri a été mal effectué, les livres à 9€ arrivent avant ceux à 50€, cela s'explique par le fait que le SIGB exporte les données sous la forme américaine, avec comme séparateur un . et notre version française d'Excel ne comprend pas que c'est un chiffre. Il fait donc un tri comme s'il se trouvait face à des chaînes de caractères sans identifier qu'il doit faire un tri numérique. Il faudrait pour changer cela que le séparateur soit une virgule. Pour résoudre le problème, on va donc remplacer tous les points par des virgules. On commence donc par sélectionner les cellules qui contiennent des chiffres (zone B2 à D192) en utilisant les touches Ctrl & Shift comme vu à l'étape précédente. Pour cela, on se met en B2, on maintient shift enfoncé, puis on appuie deux fois sur la flèche droite afin de sélectionner les trois colonnes qui nous intéressent. En maintenant shift enfoncé, on descend alors jusqu'en bas du tableau en appuyant aussi sur Ctrl puis la flèche bas.
Une fois la sélection effectuée, on va utiliser le menu Édition > Remplacer (ou Ctrl+H) et remplacer « . » (point) par « , » (virgule). Avec le fichier d'exemple, cela devrait correspondre à 573 remplacements.
Analyse du fichier[modifier | modifier le wikicode]
Si l'on regarde la manière dont est constitué ce fichier on se rend compte que les colonnes C ("En Cde") et D ("Dépensé") sont complémentaires et que dans certains cas elles valent toutes les deux 0. Cela correspond à la situation où un titre n'a pu être fourni par le libraire et l'ouvrage n'a donc pas été livré. Il peut être intéressant d'isoler ces notices, pour cela, plusieurs méthodes sont possibles.
Tri sur plusieurs colonnes[modifier | modifier le wikicode]
Si l'on sélectionne l'ensemble du tableau et que l'on effectue un tri (Données > Trier), on va demander à trier de manière croissante sur la colonne "En Cde" puis par "Dépensé", toujours de manière croissante. On se retrouve avec les ouvrages qui n'ont pu être livré dans les premières lignes.
Somme des colonnes "En Cde" et "Dépensé"[modifier | modifier le wikicode]
On peut aussi considérer que les ouvrages non livrés sont ceux pour lesquelles la somme des colonnes C et D est égale à 0.
Pour calculer automatiquement cette somme, on va se placer dans la cellule E2 et utiliser l'assistant de fonction ("insertion > fonction" ou fx à gauche de la zone de saisie des valeurs, au dessus du tableau). On en profitera pour analyser la manière dont sont accessibles les différentes fonctions d'excel (recherche libre, accès par catégorie ou dernières fonctions utilisées). Trouver la fonction qui nous intéresse : SOMME et faire la somme des zones C2 et D2. La formule qui se trouve alors dans la cellule est "=SOMME(C2;D2)"
Pour cette opération simple, on constatera qu'il n'est pas nécessaire de passer par la fonction somme mais que l'on peut obtenir directement cette somme en saisissant dans la cellule : "=C2+D2" (sans les guillemets). La présence du = en début de cellule indiquera à excel qu'il doit effectuer un calcul. On pourrait aussi taper à la main "=SOMME(C2;D2)" si l'on connaît déjà le nom de la fonction.
Pour répéter l'opération sur l'ensemble des lignes, plusieurs solutions possibles :
- utiliser à nouveau l'assistant de fonction sur la ligne suivante
- copier la cellule dans les cellules du dessous, en cliquant sur le carré noir en bas à droite de la cellule sélectionnée, puis en maintenant le bouton de la souris enfoncé et en étirant la sélection.
- faire un copier coller général. On se met sur E2, on fait un copier (Ctrl+C) puis on se rend en E192. Le plus simple pour cela est de se mettre dans la colonne D que l'on sait remplie pour toutes les lignes et de descendre jusqu'en bas (Ctrl + flèche bas) puis de se déplacer d'un cran sur la droite. À partir de E192, on va revenir jusqu'à E2 en sélectionnant toutes les cases de la colonne (shift + ctrl + flèche haut). Une fois la sélection effectuée on colle la fonction (Ctrl+V). La somme s'est adaptée à chaque ligne, et sur la ligne 3 la formule est devenue "=SOMME(C3;D3)", sur la ligne 4 "=SOMME(C4;D4)" etc.
Toutes les lignes pour lesquelles la colonne E vaut 0 sont donc les lignes non livrées. On peut trier selon cette colonne pour les isoler ou effectuer une mise en forme conditionnelle comme expliqué ci-dessous.
Mise en forme conditionnelle[modifier | modifier le wikicode]
On va sélectionner l'ensemble des cellules de la colonne "Total" (E), soit les cellules de E2 à E192. Puis on va aller dans le menu "Format > Mise en forme conditionnelle" puis choisir "la valeur de cellule est" "égale à" puis saisir la valeur 0 dans la dernière case. On clique alors sur Format et on va par exemple choisir un "motif" rouge pour isoler les cellules. Les lignes non livrées seront alors identifiables par un fond rouge dans la cellule de la colonne E.
On peut éventuellement effectuer la mise en rouge sur le titre directement, pour cela on sélectionne les cellules A2 à A192, puis "Format > Mise en forme conditionnelle" puis on va choisir : "La formule est" puis saisir la formule suivante : "=SI((C1+D1)=0;1;0)" (sans les guillemets) et appliquer une mise en forme comme précédemment. Cette seconde solution a l'avantage de mettre en forme les titres en fonction d'informations contenues dans d'autres cellules (ici une somme de deux cellules). La formule utilisée est la formule conditionnelle SI qui prend en premier paramètre la condition, en second paramètre le résultat de la fonction si la condition est vraie, en dernier paramètre la valeur si c'est faux. Ici, on utilise 1 pour VRAI, 0 pour FAUX, ce sont des standards en informatique.