GUIDE - TRAITEMENTS BUREAUTIQUES MANUELS

Exercice : appliquez chacun de ces raccourcis sous Word, notamment pour les raccourcis en gras. Pour les autres raccourcis, essayez ceux qui vous paraissent intéressants.

Tous les raccourcis ci-dessous issus de Word 2003 fonctionnent toujours sous Word 2007.

Ils ont une correspondance via le bandeau Word (Alt+…). Cependant, la version 2003 peut être préférée, car elle est plus simple à utiliser, ou plus cohérente avec les autres raccourcis, notamment si vous avez déjà l'habitude de les utiliser.

Exercice 1 : quelle que soit la version, utilisez Ctrl+d puis tab, Maj tab et ctrl+lettre soulignée pour effectuer diverses actions.

Exercice 2 : quelle que soit la version, utilisez le raccourci 2003 (ou 2007 pour les puristes adeptes de 2007) notamment pour les raccourcis en noir. Pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

Pour les raccourcis ci-dessous et en version 2007, l'ancien raccourci 2003 est à oublier. Soit parce qu'il ne fonctionne plus en 2007, soit parce qu'il a une correspondance plus simple ou plus « standard » en version 2007.

Exercice : quelle que soit la version, utilisez le raccourci 2007 pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

Voici quelques exemples d'utilisation :

• Ctrl+b, Alt+a, ou plusieurs fois jusqu'à la ligne « tableaux » puis :

Alt+v : Sélectionne le tableau suivant

Alt+d : Sélectionne le tableau précédent

• Ctrl+b, Alt+a, ou plusieurs fois jusqu'à la ligne « titre » puis :

Alt+v : Sélectionne le titre suivant

Alt+d : Sélectionne le titre précédent

Exercice : essayez les raccourcis qui vous paraissent intéressants.

Il existe pléthore de nouveaux raccourcis à partir de Word 2007. N'hésitez pas à constituer votre propre liste.

Exercice 1 : appliquez chacun de ces raccourcis sur un fichier Word.

Exercice 2 : complétez votre propre liste de raccourcis en remplissant par exemple la zone ci-dessous :

Exercice : appliquez chacun de ces raccourcis sous Excel, notamment pour les raccourcis en gras. Pour les autres raccourcis, essayez ceux qui vous paraissent intéressants.

Tous les raccourcis ci-dessous, issus d'Excel 2003, fonctionnent toujours sous Excel 2007.

Ils ont une correspondance via le bandeau Excel (Alt+…). Cependant, la version 2003 peut être préférée, car elle est plus simple à utiliser ou plus cohérente avec les autres raccourcis, notamment si vous en avez déjà l'habitude.

Pour les raccourcis de collage spécial ci-dessous, la version 2003 est préférée, car elle est plus homogène et plus rapide dans bien des cas.

(*) : Pour ces fonctions, il y a deux utilisations possibles :

• soit sélectionner d'abord la ligne ou la colonne, puis appliquer le raccourci ;
• soit appliquer le raccourci sans être entièrement sur une ligne ou colonne et choisir ensuite 'ligne' ou 'colonne' lors de l'apparition de la fenêtre pop-up correspondante.

Exercice : quelle que soit la version, utilisez le raccourci équivalent 2003 (ou 2007 pour les puristes adeptes de 2007) pour les autres lignes du tableau ci-dessus, notamment pour les raccourcis en noir. Pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

Pour les raccourcis ci-dessous et en version 2007, l'ancien raccourci 2003 est plutôt à oublier. Soit parce qu'il ne fonctionne plus en 2007, soit parce qu'il a une correspondance plus simple ou plus « standard » en version 2007.

Exercice : quelle que soit la version, essayez (plutôt) le raccourci 2007.

Il existe pléthore de nouveaux raccourcis à partir d'Excel 2007. Voici ceux que j'utilise le plus souvent. N'hésitez pas à constituer votre propre liste.

Exercice 1 : appliquez chacun de ces raccourcis sur un fichier Excel.

Exercice 2 : complétez votre propre liste de raccourcis en remplissant par exemple la zone ci-dessous :

La méthode utilisant Ctrl semble plus avantageuse, car plus homogène. Néanmoins, celle utilisant le bouton est plus aisée au niveau du placement des doigts.

Exercice : choisissez la méthode que vous voulez (Ctrl ou via ) et appliquez chacun de ces raccourcis sur un fichier Excel, pour une cellule (ou une plage), une ligne entière ou une colonne.

• Copier par insertion une ou plusieurs lignes (principe identique pour des colonnes)
  

  Sélectionner la/les ligne(s) :	cf. infra.
  Copier :	Ctrl+c
  Se déplacer sous la/les ligne(s) à copier :	ou
  Sélectionner la/les ligne(s) :	Maj+Espace
  Insérer la/les ligne(s) copiée(s) :	Ctrl+ +

• Déplacer une ou plusieurs lignes (principe identique pour des colonnes)
  

  Sélectionner la/les ligne(s) :	cf. infra.
  Couper :	Ctrl+x
  Se déplacer sous la/les ligne(s) à déplacer :	ou
  Sélectionner la/les ligne(s) :	Maj+Espace
  Insérer la/les ligne(s) coupée(s) :	Ctrl+ +

• Changer en une seule action toutes les valeurs d'une plage verticale
  

  Aller sur cette plage :	Ctrl+ (par exemple, si l'on se trouve à gauche)
  Aller en haut de cette plage :	Ctrl+
  Sélectionner la plage :	Maj+Ctrl+
  Taper le texte souhaité :	texte souhaité
  Mise à jour de la plage :	Ctrl+Entrée

• Recopier la hauteur d'une ligne

  Sélectionner la ligne modèle=source :	Maj+Espace
  Mémoriser la hauteur de ligne :	+ r, r, Enter, Enter
  Se déplacer sur la 1re ou dernière ligne cible :	ou
  Sélectionner la/les ligne(s) cibles : Maj+Espace, puis Maj+ :	ou Maj+(ou l'inverse)
  Recopier la hauteur :	F4

• Recopier la largeur d'une colonne
  

  Se positionner sur la colonne modèle (sans pour autant être obligé de la sélectionner entièrement) :	ou
  Copier la cellule :	Ctrl+c
  Se déplacer sur la 1re ou dernière colonne cible :	ou
  Sélectionner la/les colonne (s) cibles :	Maj+ ou Maj+
  Recopier la largeur :	Alt+e, g, g, enter

Exercice : appliquez chacun de ces exemples sur un fichier Excel.

Cette méthode est la plus plus sûre et la plus rapide.

Ces tris peuvent s'effectuer via les boutons de filtre ( ), ou plus rapidement encore via les boutons de tris standards ( ) directement sur une des cellules du tableau.

Ceci s'effectue grâce au raccourci Alt+t, n.

Elle peut aussi s'effectuer via les menus, par exemple sous Excel 2007, dans l'onglet « Données » via le bouton « Trier » :

C'est la méthode conseillée si l'on souhaite maîtriser totalement l'opération de tri, notamment pour trier une partie (=un sous-ensemble de lignes et/ou de colonnes) d'un tableau.

Remarque : ce n'est réalisable que sur un tableau non structuré.

Cette méthode, qui utilise les boutons de filtre (), présente le risque de désynchronisation des tableaux. (Cf. chapitre IV-F-3. Le risque de désynchronisation de tableauxLe risque de désynchronisation de tableaux )

Il s'agit des deux boutons de tris standards suivants : .

Ils effectuent automatiquement les deux tâches suivantes :

• sélection automatique et quasi intuitive du tableau courant ;
• puis tri sur la colonne sélectionnée selon l'ordre du bouton choisi (Asc. ou Desc.).

Ils sont simples et rapides à utiliser pour des tableaux basiques, mais :

• chaque tri annule parfois complètement le tri précédent. On ne peut donc pas toujours trier sur une colonne X puis sur une colonne Y ;
• la (/les) ligne(s) de titre n'est pas toujours conservée.

Exercice : explorez chacune de ces méthodes.

• Cas d'une référence à une plage limitée : si dans une formule, une référence à une plage limitée verticalement se trouve dans une formule que l'on prévoit de recopier verticalement, il est nécessaire d'utiliser le $ pour indiquer les coordonnées verticales de cette référence.

Exemple : plage limitée verticalement = Tableau1 = B10:B20 (limité en ligne 10 et 20)

Voir le tutoriel https://fauconnier.developpez.com/excel/bases/references/ concernant les références absolues et relatives pour une description détaillée.

De même, si dans une formule, une référence à une plage limitée horizontalement se trouve dans une formule que l'on prévoit de recopier horizontalement, il est nécessaire d'utiliser le $ pour indiquer les coordonnées horizontales de cette référence.

• Cas d'une référence à des colonnes entières (resp. lignes entières) :

  Si l'on envisage d'effectuer une copie verticale (resp. horizontale) d'une formule, pointant vers un tableau (ex. ici Tableau1) et s'il n'y a pas d'autres tableaux sous celui-ci (resp. à côté de celui-ci).

  Alors, plutôt que d'utiliser le $, il vaut mieux prendre comme référence l'ensemble de la/des colonne(s) (resp. lignes) du tableau plutôt que de se limiter au tableau seul.

  L'avantage est que la formule est plus simple et que le code est plus robuste en cas d'ajout/suppression de lignes (resp. colonnes) dans le tableau.

  Exemple, dans la copie d'écran ci-dessous : Plage = colonne B:B

  

Reprenons le chapitre précédent.

Si l'on souhaite rester dans le 1er exemple (=cas d'une référence à une plage limitée), et que l'on ajoute une ligne, on risque d'obtenir la situation fausse suivante :

Pour éviter ce risque, il y a trois solutions à priori possibles :

• la meilleure solution serait d'utiliser un tableau structuré pour tableau1. Ainsi en cas d'ajout d'une donnée à la ligne suivante, le tableau structuré est automatiquement étendu et la référence pointant vers ce tableau (ici tab1) est inchangée.

  

• sinon, pour les irréductibles, on peut toujours ajouter une ligne horizontale de délimitation verticale et faire englober cette ligne dans la référence. Cela permet alors de sécuriser le tableau puisque toute ligne ajoutée sera au-dessus de cette ligne de délimitation.

  

• enfin la solution d'adapter toutes les références en cas d'extension du tableau est une solution quasi impossible, trop compliquée et risquée.

On le voit donc, dans cet exemple, les tableaux structurés s'imposent.

La désynchronisation d'un tableau correspond au fait qu'une partie (gauche) du tableau ne correspond plus à l'autre partie (droite)

Exemple :

Cette désynchronisation peut avoir lieu dans deux cas :

• désynchronisation par filtre incomplet : c'est le cas le plus fréquent ; on ajoute des colonnes (à gauche ou à droite du tableau original) et on oublie d'étendre le filtre. Ainsi, dès que l'on va trier via les boutons de filtre, les colonnes ajoutées ne seront pas triées.
  L'utilisation de tableaux structurés résout ce problème.
  Sinon, une autre solution consisterait à ajouter un encadrement vertical de chaque côté du tableau et les inclure dans le filtre. Ces délimitations permettent alors d'alerter l'utilisateur. Ainsi si une colonne est ajoutée « entre » ces deux encadrements verticaux, elle sera automatiquement incluse dans le filtre.
  Avec ces lignes verticales, les tableaux ont ainsi cet aspect :
  

  Dans tous les cas, pensez à faire des copies régulières de votre fichier de travail ;

• désynchronisation par colonne vide. Cas plus rare, cette désynchronisation a lieu (plutôt sur des grands tableaux) à cause d'une ou plusieurs colonnes vides que l'on ne voit pas. Le filtre couvre bien l'ensemble du tableau, mais la désynchronisation s'effectue quand même au niveau de la colonne vide. Elle peut se faire par exemple si on clique sur les boutons standards de tris.
  Solution : ici, l'utilisation de tableaux structurés résout entièrement ce problème puis-qu'aucun titre ne peut être vide.

Exercice : lisez cette page entièrement (c'est déjà suffisant). Éventuellement, testez chacune des deux méthodes de désynchronisation.

Exemple 1 :

Exemple 2, Cas où la clé (clé X) n'est pas en début de tableau :

Personnellement, j'utilise toujours 0 comme dernier paramètre de RerchercheV ce qui permet à cette fonction d'être efficace quels que soient les tris effectués.

Avantages / Inconvénients du RechercheV 

• Avantage : on rapatrie les données voulues avec une seule formule.
• Inconvénient : utilisées de cette manière, les formules sont moins robustes que Equiv + Index (les valeurs renvoyées deviennent différentes si l'on insère des colonnes dans le tableau des données X).
• Inconvénient : S'il y a beaucoup de colonnes de RechercheV: les calculs sont plus longs qu'avec Equiv + Index
• Inconvénient : la colonne clé doit être la première colonne de la plage, donc obligatoirement à gauche de la (des) colonne(s) rapatriée(s).
• Inconvénient : lors de la recopie horizontale de la formule, il faut adapter manuellement le n° de la colonne renvoyée. -> Donc plus long à adapter si beaucoup de colonnes.
• Remarque : depuis la version 2007, les tableaux structurés permettent de circonscrire les plages de traitement. Il n'est donc plus utile (avec les tableaux structurés) d'utiliser RechercheV sur une colonne entière. Par contre pour pouvoir utiliser les tableaux structurés, la colonne clé doit être la première colonne du tableau.

Pour plus d'information sur ce sujet, voir le tutoriel correspondant sur Developpez.com : https://fauconnier.developpez.com/tutoriels/excel/recherchev

Avantages / Inconvénients du Equiv + Index 

• Inconvénient : nécessité d'une colonne supplémentaire, sauf si l'on combine les deux formules dans chaque cellule.
• Avantage : possibilité de recopie horizontale et verticale (= sur tout le tableau) de la formule.
• Avantage : si l'on utilise Equiv dans une seule colonne et Index dans de nombreuses autres colonnes, alors les calculs sont plus rapides qu'avec RechercheV.
• Avantage : les formules sont robustes et indépendantes des insertions de colonnes.
• Remarque : depuis la version 2007, les tableaux structurés permettent de circonscrire les plages de traitement. Il n'est donc plus utile (avec les tableaux structurés) d'utiliser Index/Equiv sur une colonne entière. Par contre, il faut alors adapter les formules sur chacune des colonnes rapatriées.

Exemple :

Exercice : testez les deux méthodes.

Mode opératoire :

• trier les données X selon qu'il y ait ou non une correspondance dans le tableau Y (en utilisant EQUIV, cf. page suivante) ;
• puis, parmi les données X ayant une correspondance, trier selon la clé X ;
• trier les données Y selon qu'il y ait ou non une correspondance dans le tableau X (en utilisant EQUIV) ;
• puis, parmi les données Y ayant une correspondance, trier selon la clé X.

Avantage / Inconvénients 

• Avantage : la méthode par copie est nécessaire si l'on désire conserver les mises en forme des cellules rapatriées.
• Inconvénient : ne fonctionne que s'il n'y a pas de doublons de part et d'autre. Cf. chapitre H 'Les doublons', pour éliminer les doublons (= éliminer les lignes en double) avant d'appliquer cette méthode.
• Inconvénient : risque d'erreur plus important. Vérifier alors à nouveau l'égalité de clé une fois les données X rapatriées. Dans notre exemple, il faut ensuite vérifier l'égalité entre les colonnes E et H de l'onglet Joint3b.
• Note : ne pas hésiter à faire régulièrement des copies de sécurité de ses fichiers Excel.

Exercice : testez cette méthode.

• Sous Excel 2007, la méthode est ultra simple : sélectionnez le tableau à dédoublonner, puis aller dans le menu « Données », puis « Supprimer les doublons ». Remarque : cet outil de suppression conserve alors chaque première ligne de doublon et supprime lignes en double suivantes.
  Exercice : si vous utilisez office 2007 ou suivant, testez cette méthode.

• Sous Excel 2003, il existait deux méthodes. La première méthode est d'utiliser les filtres élaborés :
  

  • Avantage : méthode rapide.
  • Avantage : pas besoin de trier les données.
  • Inconvénient: méthode archaïque lorsque l'on possède Excel version 2007 ou plus.

• Sous Excel 2003, la seconde méthode est par tableau croisé dynamique :

  • Sélectionner la colonne clé avec l'entête ;
  • Alt+d, r, Suivant, Suivant, puis « Nouvelle feuille » ou « Feuille existante » ;
  • Terminer, puis drag & drop du champ clé dans la zone des champs de ligne (colonne à gauche) ;
  • Puis drag & drop du champ clé dans la zone des données (cœur du tableau) ;
  • Éventuellement, double-cliquer sur le champ pivot de table pour sélectionner NbVal ;
  • Vous obtenez alors la liste des données dédoublonnée ainsi que les quantités associées.

  

  • Avantage : permet de connaitre le nombre de lignes par données clés.
  • Avantage : pas besoin de trier les données.
  • Inconvénient: Ne permet de sélectionner/filtrer/recopier les données que d'une seule colonne (la colonne clé).
    Astuce : on peut ajouter une colonne concaténant plusieurs autres colonnes que l'on souhaite étudier.

  
  Exercice : si vous utilisez (encore) office 2003, testez cette méthode.

Le but est ici l'identification des données ayant des doublons, c'est-à-dire d'identifier toutes les lignes dont la clé est présente en plus d'un exemplaire.

À partir d'Excel 2007 : le plus simple est d'utiliser une mise en forme conditionnelle sur les doublons (ou sur les valeurs uniques). 

Sous Excel 2003 : la méthode était plus laborieuse : vous deviez répéter la formule suivante : « =NB.SI(colonne ; cellule)>1 » sur toutes les lignes. Apparait alors VRAI en face de tous les doublons :

Exercice : testez la méthode qui correspond à votre version Excel.

Le but est de marquer les lignes contenant les doublons en trop, c'est-à-dire d'identifier uniquement les lignes en double parmi celles dont la clé est présente en plus d'un exemplaire.

La méthode est la suivante :

• trier d'abord les lignes sur la colonne étudiée (la colonne clé) ;
• puis répéter une des deux formules ci-dessous sur une colonne libre :

• puis, éventuellement :

  • transformer les formules en résultat (Sélection, Ctrl+c, Alt+e, g, v)
  • trier toutes les données sur la colonne D.

Avantages :

• méthode pour identifier les lignes en trop ;
• permet de sélectionner/filtrer/recopier les données de plusieurs colonnes puisque c'est la toute la ligne qui est identifiée (contrairement à la page suivante).

Exercice : testez cette méthode via les deux formules.

Faire un groupement de données cela signifie :

• définir certains champs comme identifiants de groupes ;
• et définir si l'on souhaite grouper les données sur chaque champ horizontalement ou verticalement (cf. exemple suivant) ;
• puis appliquer des fonctions de groupe (min, max, premier= first, dernier=last, nombre=count, moyenne…) sur tout ou partie des autres champs (données groupées).

Nous allons illustrer les regroupements au travers de trois exemples différents.

Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 1 suivant. Les champs « Année » et « Lieu » sont ici tous les deux groupés horizontalement, avec les fonctions : nombre, moyenne (tarif), min (tarif) et max (tarif) :

Comme nous allons le voir avec quatre méthodes différentes, il n'existe pas encore de solution simple, souple et répondant entièrement au besoin de cet exemple.

• Méthode 1, avec un TCD (Tableau croisé dynamique)
  
  La méthode par TCD offre l'avantage de la souplesse et de la rapidité d'exécution.
  Cependant il peut présenter deux inconvénients :

  • le résultat est non normé. Il présente des lignes de totaux que l'on ne souhaite pas forcément. Ainsi dans notre exemple, le TCD montre cinq lignes au lieu des trois souhaitées ;
  • il n'existe pas de fonctions min et max portant sur des chaines de caractères.

• Méthode 2, TCD avec concaténation
  Cette méthode consiste à concaténer les étiquettes de lignes avant d'effectuer le TCD. Le tableau résultant est ainsi normé :
  

  • Avantages : souplesse, tableau normé.
  • Inconvénients : étape supplémentaire de concaténation, pas de fonction de min/max sur chaines de caractères.

• Méthode 3, tableau figé, avec formules
  Cette 3e méthode utilise les fonctions xxx.SI.ENS d'Excel. Exemple SOMME.SI.ENS, NB.SI.ENS, MOYENNE.SI.ENS
  Exemple :
  

  • Avantages :

    • rapidité ;
    • tableau normé.

  • Inconvénients :

    • les fonctions NB.SI.ENS, MOYENNE.SI.ENS et SOMME. SI.ENS existent. Par contre il n'y a pas, en version 2007, de fonction MIN.SI.ENS ni MAX.SI.ENS ;
    • la structure du tableau doit être adaptée manuellement en cas d'ajout de nouvelles étiquettes de lignes (dans notre exemple, en cas de nouvelle année ou de nouveau lieu).

• Méthode 4, via SQL
  En utilisant une base de données (ex. Access, Oracle, SQL server…) il est possible en une seule requête d'obtenir exactement le résultat souhaité.

  • Avantages : souplesse, tableau normé, résultat souhaité.
  • Inconvénients : nécessite de passer par un SGBD (Access, Oracle…)

  Voici une méthode concernant Access :

  • Créer une table virtuelle pointant vers le tableau (<=> lier une table) : Alt + F, D, L…

  • Remarque : Access peut pointer :

    • vers Excel : vers un onglet entier ou vers un tableau défini par un nom,
    • vers un fichier texte, une table Access…

  • Réaliser la requête,

    • avec l'assistant de création,

    • en mode création de requête

      • opération = groupement pour les champs de groupement,
      • ou opération = fonction de groupe (min, max…) pour les autres champs,
    • ou en mode SQL : syntaxe classique du group by.

Exercice : testez au minimum la méthode 1 et la méthode 2.

Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 2 suivant. Les champs « Année » et « Lieu » sont utilisés comme groupement (horizontal et vertical) avec les fonctions : nombre et moyenne (tarif)

• Méthode 1, avec un TCD (Tableau croisé dynamique)
  
  La méthode par TCD offre l'avantage de la souplesse et de la rapidité d'exécution.
  Le seul inconvénient est qu'il n'existe pas de fonctions min et max portant sur des chaines de caractères.

• Méthode 2, tableau figé, avec formules
  Cette méthode n'offre ici aucun avantage par rapport au TCD.

• Méthode 3, avec Access
  La solution consiste à effectuer une requête analyse croisée, que l'on peut générer :

  • avec l'assistant de création,

  • en mode création de requête;

    • opération = groupement pour les champs de groupement (analyse = entête de ligne ou de colonne),
    • ou opération = fonction de groupe (min, max…) pour les autres champs (analyse = valeur)
  • ou en mode SQL : syntaxe propre à Access avec group by + transform + pivot.

Exercice : testez au minium la méthode 1.

  Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 3 suivant.

Les champs « Année » et « Lieu » sont utilisés comme groupement (horizontal et vertical).

La particularité est qu'il n'y a qu'une seule valeur par couple année-lieu.

La conséquence est que fonctionnellement, une fonction min ou max correspond à la même chose ;c'est-à-dire à une fonction d'égalité (puisque le min = le max = la donnée de LA ligne initiale).

Il s'agit donc d'un groupement sans véritable calcul.

La solution sous Excel n'est pas triviale (si vous en avez une meilleure contactez-moi).

En effet, elle nécessite une colonne et une ligne en plus (de préférence masquées toutes les deux).

Voici les différentes actions à effectuer :

• tout d'abord ajoutez la colonne D (que nous masquerons ensuite) et qui concatène l'année et le lieu. formule en D6 : « =B6&C6 ». À recopier verticalement ;
• ensuite, ajoutez la ligne 4 (que nous masquerons ensuite) et dont le rôle est de faire en sorte que le lieu soit renseigné sur toutes les colonnes. Ce qui n'est pas le cas de la ligne 3 où le lieu n'est pas renseigné en J4, L4 et N4. Formule en I4 : « =SI(I3="";H4;I3) » . À recopier horizontalement ;
• indiquez la formule suivante en I6 : « =SIERREUR(RECHERCHEV($H6&I$4;$D:$F;2;0) ; "" ) » ;
• indiquez la formule suivante en J6 : « = SIERREUR (RECHERCHEV($H6&I$4;$D:$F;3;0) ; "")» ;
• recopiez verticalement la plage I6:J6 pour remplir le tableau sous « Londres » ;
• recopiez la plage I6:J8 sur les deux autres lieux.

Remarque pour des raisons de visibilité, je n'ai volontairement pas utilisé de noms dans les formules utilisées.

Nous avons parfois besoin, pour un sujet précis, d'avoir un tableau simple contenant une ligne unique par item, et un certain nombre de colonnes bien définies. C'est ce qu'on pourrait appeler (en simplifiant) « données normalisées ».

Or il est fréquent de trouver des sources de données ayant un format bien éloigné de celui-ci.

Typiquement, lorsque l'on récupère des données d'une page web, il est très rare d'avoir les données souhaitées directement normalisées. En effet, la plupart du temps ces données :

• sont dispersées sur plusieurs pages ;
• sont au milieu d'autres informations ou images inutiles ;
• et ont des formats bien éloignés d'un simple tableau ;

Voici donc deux chapitres méthodologiques concernant cette normalisation :

• le chapitre 2 suivant est un exemple très concret de normalisation de données issues du Web (ici des pages jaunes) ;
• le chapitre 3 présente une généralisation de l'exemple concret, c'est-à-dire une méthode générale.

Dans cet exemple, nous allons récupérer et normaliser les données du site pagesjaune.fr et concernant les Coiffeurs à Conflans Ste Honorine (en 2012). Nous étudierons cet exemple, sachant que le mode opératoire pour la mise en forme de données provenant d'autres sources est souvent très similaire, d'où le chapitre 3 suivant.

Mode opératoire

• Étape 1 : Récupération des données : copier-coller les données des deux pages web dans le même onglet Excel (placer le collage des pages web les uns en dessous des autres) ;

  

• Étape 2 : Élimination des images : copier - collage spécial valeur de la totalité de l'onglet dans un autre onglet.
  En effet, cette méthode en deux étapes permet de conserver les mises en tableau des données web tout en supprimant la totalité des images, des liens hypertexte…

  

• Étape 3 : Numérotation des lignes : insertion d'une colonne à gauche (colonne 1) et numérotation de toutes les lignes (cf. « Recopie d'une cellule vers le bas », chapitre 4.B). (Ceci va permettre de trier à loisir les différentes lignes tout en étant certain de pouvoir revenir à l'ordre initial).

  

• Étape 4 : tri sur colonnes « données » et élimination des données répétitives inutiles
  Dans l'image ci-dessous, et pour une meilleure visibilité, les données supprimées sont colorées en rouge, les données conservées sont en vert :

  

• Étape 5 : retri sur colonne 1 pour vérifier que l'ensemble est correct.
  Dans l'image ci-dessous, et pour une meilleure visibilité, la première ligne pour chaque coiffeur est en gras :

  

• Étape 6 : Marquage des nièmes lignes de chaque groupe.
  Le but de cette étape est d'identifier rapidement la première ligne pour chaque coiffeur (celle qui a été positionnée manuellement en gras dans l'image ci-dessus).
  Il y a deux méthodes possibles. Si nous sommes certains qu'il y a le même nombre de lignes pour chaque groupe (ici chaque coiffeur), nous utilisons la méthode 1. Sinon il faut utiliser la méthode 2.

  • La méthode 1 est à utiliser s'il y a quasiment toujours le même nombre de lignes de chaque groupe.
    Les étapes sont :

    • ajout d'une colonne de marquage (ici col 2) ;
    • marquage de la première ligne du premier groupe (ici un « x ») ;
    • sélection des lignes du premier groupe dans cette colonne (cf. fig. 1) ;
    • double-clic sur la poignée de recopie (cf. fig. 2) ;
    • puis remplacement des x par des 1.

    On voit que cette méthode ne peut pas fonctionner dans notre exemple puisque les « 1 » ne correspondent pas aux premières lignes de chaque groupe (celles en gras) (cf. fig. 2).
    Remarque : si nous n'avions conservé que les noms, adresses et numéro de téléphone (trois lignes par groupe), cette méthode aurait fonctionné.
    

  • Méthode 2 :
    Le but est d'abord de trouver un moyen d'identifier les premières (2e, 3e, .. ou dernières) lignes de chaque groupe par une formule.
    Ici, on voit que chaque 2e ligne contient le mot « 0 conflans », nous allons donc nous servir de cette caractéristique par une formule ajoutée en colonne 3 : ex. =SI(ESTERREUR(CHERCHE(« 0 CONFLANS S »;LC(1))); ""; 1). Remarque pour que cette formule fonctionne, veiller à passer en style de référence L1C1 (Alt+o, o, formules…
    

    

• Étape 7 : Numérotation de toutes les lignes de chaque groupe.
  Maintenant que l'on sait séparer les différents groupes, le but est ici de numéroter les lignes à l'intérieur de chaque groupe. Remarque pour les connaisseurs d'Oracle : cela correspondrait à un rank dans un select.

  • La première étape est de copier les 1 et de les coller en collage spécial valeur.
  • On peut supprimer la colonne 2 inutile.
  • Ensuite, on remonte les 1 d'une cellule pour qu'ils correspondent à la première ligne de chaque groupe.
  • On insère ensuite une colonne à droite des 1 dans laquelle on va numéroter les lignes à l'intérieur de chaque groupe. Par exemple une formule du type =SI(LC(-1)=1;1;L(-1)C+1) en colonne 3 basée sur la col. 2.
  • Enfin, on transforme les formules en valeur (=copier, collage spécial valeur).
    

    

• Étape 8 : Tri par n° de ligne dans le groupe puis décalage.
  Le but de cette étape est de décaler vers la droite certaines cellules.

  • En premier on passe en valeur les n° de ligne.
  • Ensuite on trie sur les n° de lignes.

  • Puis on décale les cellules :

    • les lignes n°1 ne sont pas décalées ;
    • les lignes n°2 sont décalées d'une colonne vers la droite ;
    • les lignes n°N sont décalées de N -1 colonnes vers la droite (utiliser la touche F4).
  • N'oubliez pas d'étendre le filtre sur les nouvelles colonnes (si vous n'utilisez pas de tableaux structurés) :
    
  • Cela donne finalement ceci :
    

    

• Étape 9 : réalignement des données sur les premières lignes.

  • Nous allons tout d'abord retrier les lignes par n° de ligne (ici sur colonne 1).

  • Ensuite, nous allons réaligner les données :

    • se mettre cellule L2C5 (correspondant lignes n° 2, c'est-à-dire ici aux adresses des coiffeurs) et décaler les cellules vers le haut ;
    • puis se mettre sur la cellule juste à droite (les lignes n° 3 = n° de téléphone) et décaler les cellules deux fois vers le haut (utiliser F4) ;
    • etc. (décaler vers la droite et augmenter le nombre de F4)

  

• Étape 10 : suppression des lignes et colonnes inutiles et finalisation.

  • Pour cette étape, nous retrions sur les n° de ligne dans le groupe (colonne 3).
  • Puis nous supprimons les lignes inutiles (les lignes vides).
  • Nous supprimons les colonnes inutiles (colonnes 1 à 3).
  • Nous concaténons les colonnes 4 et 5.
  • Et nous améliorons un peu la présentation.

  Ce qui donne :

  

Si l'on généralise l'exemple précédent, on s'aperçoit que la transformation de données répétitives nécessite toujours quatre phases :

• Phase préliminaire (correspond aux étapes 1 à 5 précédentes) :

  • récupération des données ;
  • éventuellement : élimination des images ;
  • numérotation des lignes ;
  • élimination des données inutiles.

• Identification (et éventuel regroupement) des nièmes lignes de groupe : (étapes 6 et 7 précédentes) :
  Plusieurs outils et méthodes peuvent être utilisés :

  • recopie répétitive grâce à la poignée de répétition (cf.étape 5 - méthode 1 dans l'exemple précédent) ;
  • utilisation de la fonction CHERCHE (cf. étape 5 - méthode 2 dans l'exemple précédent) ;
  • utilisation de la fonction INSTR ;
  • utilisation d'une formule basée sur une ou plusieurs cellules de la ligne + la cellule juste au-dessus (ou juste en dessous) Exemple =SI(LC(-1)=1;1;L(-1)C+1 (cf. étape 6 dans l'exemple précédent) ;
  • découpage de données (avec GAUCHE ou DROITE).
  • …

• Modifications en masse des nièmes lignes de groupe (étapes 8 et 9 précédentes).
  Là aussi, plusieurs outils et méthodes peuvent être utilisés :

  • décalage (cf. étape 7 dans l'exemple précédent) ;
  • découpage de données (avec GAUCHE ou DROITE) ;
  • remplacement avec Ctrl+h ou SUBSTITUTE ;
  • concaténation (avec &) ;
  • …
• Mise en forme finale (étape 10 précédente).