Atelier excel : Opérations diverses sur un fichier

De Bibliopedia
Aller à la navigation Aller à la recherche

Atelier initiation excel à destination des personnels de BU - 1 heure[modifier le wikicode]


Les instructions de cette synthèse sont associées à Excel 2010, il pourra être nécessaire de les adapter pour une autre version de Excel.

Analyser le contenu d'une colonne[modifier | modifier le wikicode]

Fichier d'exercice
Fichier:AtelierExcel 20140326 exercice1.txt à ouvrir dans Excel en indiquant que le séparateur de colonne est une tabulation.
Problématique
On a un fichier de plusieurs centaines de titre, chacun est associé à un thème et on souhaite connaître la répartition des ouvrages par domaine.
Solution
On va utiliser les tableaux croisés dynamiques (TCD). Pour cela, on se rend dans le menu Insertion > TblCroiséDynamique. Par défaut Excel va sélectionner l'ensemble de votre tableau puis créer un TCD à partir de ces données.
À partir de ce TCD, on va faire glisser les champs (si la liste des en-têtes de colonne ne s'affiche pas, la réactiver avec : Outils de tableau croisé dynamique > Afficher > Liste des champs) sur le tableau. Dans les "étiquettes de ligne", on va utiliser la colonne sur laquelle on veut obtenir des statistiques (DOMAINE dans le cas qui nous intéresse), puis dans la zone de droite (champs de valeur), on va choisir les titres, afin de compter le nombre de titres concernés par chaque domaine.
Résultat
Voir le résultat attendu.

Ajouter un préfixe à toutes les cellules d'une colonne[modifier | modifier le wikicode]

Fichier d'exercice
Fichier:AtelierExcel 20140326 exercice2.txt à ouvrir dans Excel en indiquant que le séparateur de colonne est une tabulation.
Problématique
Dans le fichier d'exemple, on dispose d'une colonne Cote BU pour laquelle un certain nombre de cotes (débutant par A ou E) sont destinées à aller en magasin. Avant de charger ce fichier nous souhaitons préfixer ces cotes magasin par un code permettant d'identifier leur bibliothèque d'origine (BJM).
Solution
On doit commencer par identifier les cotes à préfixer (débutant par A ou E). Pour cela on va créer un filtre : Données > Filtrer. Puis on va se positionner sur l'en-tête de colonne qui nous intéresse, cliquer sur la petite flèche qui se trouve en haut de cette colonne "filtrée", puis choisir Filtre textuel > Commence par. On va alors demander à filtrer toutes les cotes qui commencent par A OU qui commencent par E.
Excel devrait vous indiquer en bas à gauche de la fenêtre le nombre de lignes identifiées (sous la forme : 761 enregistrement(s) trouvé(s) sur 1070).
Une fois cette étape de filtrage effectuée, on va utiliser une colonne temporaire, la colonne E sur notre exemple, pour générer la cote "fusionnée". On se place donc en cellule E169 (croisement de la colonne E et la ligne 169, première ligne qui reste affichée, en raison du filtre précédemment appliqué) puis on va saisir une formule. On a pour cela deux solutions :
  • Utiliser l'assistant de fonction (Formules > Insérer une fonction qui va nous donner accès à toutes les fonctions, ou bien, comme le permet Excel 2010, on va directement choisir la fonction à partir de son type). Dans le cas qui nous intéresse nous cherchons la fonction CONCATENER qui permet d'assembler deux chaînes de caractères.
  • Taper directement le nom de la fonction en l'appelant à l'aide d'un = sous la forme en lui passant les paramètres nécessaires.
L'opération que nous souhaitons faire va consister à concaténer "BJM " (on entoure de guillemets pour qu'Excel comprenne que c'est une chaîne de caractères) et le contenu de la cellule qui contient la cote pour la ligne courant : D169. La formule finale est donc =CONCATENER("BJM";D169) qui aura pour effet d'afficher BJM A398 en E169.
À partir de là il nous suffit de faire glisser cette formule automatiquement sur toutes les lignes. Pour cela on sélectionne la cellule E169, puis on clique sur le petit carré noir qui se trouve en bas à droite de la cellule et on fait glisser sa souris vers le bas pour copier la formule sur toutes les cases. On peut aussi faire un copier / coller pour plus d'efficacité, en particulier si l'on maître les navigations dans le fichier vues dans un autre atelier.
À cette étape, les cotes fusionnées sont affichées mais ne sont pas stockées dans le fichier (le résultat est dynamique, si l'on modifie une Cote BU, la cote fusionnée va changer. On va donc copier toute la colonne E, puis choisir de faire un collage spécial dans la colonne D (dans l'onglet Accueil, sous le bouton Coller, on utilise la flèche qui nous affiche un menu supplémentaire dans lequel on choisit Coller des valeurs). On peut alors supprimer la colonne temporaire et refermer le fichier.
Résultat
On enlève le filtre (en haut de la colonne filtrée, on clique comme pour ajouter un filtre puis on choisit Effacer le filtre de "Cote BU") et on observe que les nouvelles cotes sont présentes au milieu des autres.

Compléter des PPN dont les 0 de tête ont disparu[modifier | modifier le wikicode]

Fichier d'exercice
on reste sur le même fichier que précédemment, la première colonne contient des PPN qui posent problème.
Problématique
Il arrive parfois qu'Excel supprime les 0 de tête d'une zone lorsqu'il considère que c'est un nombre. Cela peut être problématique pour les PPN qui ont besoin d'avoir ces 0 de tête.
Solution
On sait qu'un PPN est composé de 9 caractères, il va donc falloir créer un PPN_OK en complétant le PPN initial par autant de 0 que nécessaire pour atteindre cette taille.
Comme précédemment on va utiliser une colonne temporaire, en insérant une colonne entre le PPN et le code barre. Pour cela, on sélectionne la colonne B (en cliquant sur le B de l'en-tête), puis clic droit : Insertion).
On utilisera la formule suivante : =CONCATENER(REPT("0";9-NBCAR(A2));A2) (on peut aussi remplacer la formule CONCATENER par un & pour rendre les choses plus lisibles : =REPT("0";9-NBCAR(A2)) & A2.
Pour l'expliquer, on partira du principe que pour créer le PPN correct on devra concaténer :
  • Le nombre de 0 manquants, obtenu en répétant (REPT) le caractère 0. Pour savoir combien de fois répéter le caractère, on fera la différence entre 9 (la taille attendue d'un PPN) et la taille actuelle (NBCAR(A2))
  • Le PPN tronqué (A2 dans le cas qui nous intéresse)
Comme tout à l'heure : on copie sur toutes les lignes, on fait un collage spécial pour remplacer la première colonne.
Résultat
On se retrouve avec une liste de PPN qui possèdent tous 9 caractères et sont exploitables.

Pistes pour d'autres ateliers[modifier | modifier le wikicode]

  • Trouver les lignes communes à deux fichiers
  • Analyser un fonds par cote Dewey dans le cadre d'une carte documentaire (convertir données et tableau croisé dynamique)
  • Récupérer des stats en csv ou en html et les exploiter (créer des graphiques avec des barres et des courbes, afficher des infos différentes sur le même graphique)
  • Transformer une colonne d'ISSN avec tiret en sans tiret
  • Séparer et nettoyer des données téléchargées depuis WinIBW (récupérer les PPN sans les url, le titre sans les $ et sans les termes entre [], etc.)
Récupérée de « https://bibliopedia.geobib.fr/index.php?title=Atelier_excel_:_Opérations_diverses_sur_un_fichier&oldid=10429 »