Excel, extraction de données

On m’a souvent demandé de modifier des tableaux Excel pour en extraire certaines données.
Notre exemple se base sur un fait vécu : les noms et prénoms sont identifiés dans la même colonne, séparés par une virgule. On veut bien sûr obtenir les noms dans une colonne et les prénoms dans une autre.

1. En premier lieu, il faut connaître la position du caractère qui sépare les deux données, dans notre cas il s’agit de la virgule. La fonction CHERCHE est utilisée pour repérer cette position.
   
   
   

   

   
   

   
   Nous cherchons ici le caractère « virgule » dans le contenu de la cellule A2. Il se trouve à la huitième position.



2. Maintenant nous pouvons utiliser la fonction GAUCHE, qui permet d'extraire un certain nombre de caractères dans une chaîne, à partir de la gauche.
   
   
   
   
   
   Dans la cellule A2 et à partir de la gauche, nous prenons le nombre de caractères renvoyés par la fonction CHERCHE. La mention « -1 » permet de ne pas prendre la virgule elle-même. Et voilà pour les Noms.
   
3. La logique utilisée pour extraire les prénoms est la suivante : nous prenons le nombre total de caractères de la cellule, auquel nous enlevons le nombre de caractères utilisés pour le nom, la virgule et l’espace qui les séparent.
   
   

   

   Le nombre total de caractères d’une chaîne est renvoyé par la fonction NBCAR. Ici, dans la cellule A2 et à partir de la droite nous prenons le nombre total de caractères (soit 15) auquel nous enlevons autant de caractères qu’il faut pour arriver à la virgule. Nous avons ajouté la mention « +1 » à la fonction CHERCHE, pour ne pas prendre en compte l’espace. Et voilà pour les prénoms.
   Le résultat est juste, mais pas final, car nous ne pouvons pas encore supprimer la colonne A puisque les colonnes B et C existent grâce à elle
   
   
4. Sélectionnons le contenu des colonnes B et C pour le copier.
   
5. Le curseur est placé sur la cellule D2. Nous activons ensuite la commande Collage spécial du menu Edition, pour choisir l’option Valeurs.

Comme illustré, la cellule D2 contient le texte. Les cellules A, B et C peuvent maintenant être supprimées car les données ont été réorganisées correctement.

Vous avez des questions ? n'hésitez pas à me contacter !

Excel, regroupement de données

Quand vous travaillez dans un grand tableau, vous avez parfois besoin d'imprimer uniquement les données essentielles et de masquer les détails. Bien sûr, les commandes Masquer et Afficher du menu Format, Colonne ou Format, Ligne peuvent être utilisées. Toutefois elles sont un peu longues à mettre en oeuvre si vous voulez rapidement passer d'un affichage à l'autre.

Voici une autre solution beaucoup plus efficace à long terme.

1. Sélectionnez les colonnes ou les lignes que vous désirez regrouper.


2. Activez le menu Données, Grouper et créer un plan et choisissez la commande Grouper.
   
   
   
   
   
   Le plan fonctionne comme si la feuille de votre tableau était momentanément repliée pour masquer certaines données.
   Dans notre exemple nous avons regroupé des colonnes. Une ligne supplémentaire est maintenant affichée au-dessus des têtes de colonnes. Elle permet de passer rapidement d'un niveau de plan à un autre. Le niveau 1 regroupe les colonnes en les cachant. Le niveau 2 affiche tout le tableau. Vous pouvez utiliser jusqu'à 8 niveaux de plan.
   
   


3. Les colonnes regroupées sont identifiées par un point, juste au-dessus de leur nom. Les symboles - et + permettent respectivement de fermer et ouvrir le groupe.
   
   Ainsi, vous pouvez maintenant afficher et cacher des colonnes ou des lignes en un seul clic, tout en conservant le plan dans le document. L'impression ne tiendra compte que des données affichées.

Pour supprimer un niveau de plan

1. Sélectionnez les colonnes ou les lignes désirées.
   
2. Activez le menu Données, Grouper et créer un plan et choisissez la commande Dissocier.

Pour supprimer tous les niveaux de plan

1. Positionnez le curseur dans le tableau, nul besoin d'une sélection.
2. Activez le menu Données, Grouper et créer un plan et choisissez la commande Effacer le plan.

Création d'un plan automatique

Pour créer automatiquement un plan, Excel regroupe les données utilisées dans les formules pour afficher seulement les totaux, selon son interprétation du tableau.

1. Positionnez le curseur dans le tableau, nul besoin d'une sélection.
2. Activez le menu Données, Grouper et créer un plan, et choisissez la commande Plan automatique.
   Dans notre exemple, les calculs intermédiaires ont été automatiquement regroupés (colonnes G, H et I).
   
   
   

Le plan peut aussi être utilisé pour travailler dans de très grands tableaux et éviter l'utilisation répétée des barres de défilement.

Bon travail !

Excel, sous-totaux

Dans la plupart des tableaux, nous avons besoin de calculer des sous-totaux pour fins de statistiques. Voici une manière rapide d'obtenir les calculs sans faire de formule.

• Il faut tout d'abord respecter les règles suivantes : le tableau ne contient aucune colonne vide ni aucune ligne vide, sinon Excel le considère comme autant de "morceaux" de tableaux et non comme un tout.

Dans l'exemple illustré ci-dessous, nous désirons obtenir le total des ventes par secteur.

1. Triez la colonne Secteur dans l'ordre croissant pour regrouper les secteurs identiques ensembles.
2. En s'assurant que le curseur soit positionné dans le tableau, activez le menu Données, Sous-totaux. Le tableau de données est automatiquement sélectionné.
3. La logique est la suivante : à chaque changement de Secteur nous obtiendrons la somme du chiffre d'affaires.
   

• Différentes fonctions statistiques sont disponibles (Moyenne, Min, Max...). De plus, il est possible de réaliser plusieurs totaux en même temps : cochez plus d'un champ dans la zone Ajouter un sous-total à.
• Si la case Remplacer les sous-totaux existants n'est pas cochée, il est alors possible d'obtenir la somme et la moyenne (par exemple) dans le même tableau.
• La Synthèse peut s'afficher au-dessus ou sous les données.
• Enfin, le bouton Supprimer tout, comme son nom l'indique, efface toute trace de sous-totaux dans le tableau.
  
  Et voici le résultat.
  
  
  


• Excel crée automatiquement un plan, permettant de masquer à volonté les lignes de détail.
• Les chiffres en haut à gauche représentent les différents niveaux de plans. Plus le chiffre est élevé, plus l'affichage est détaillé. Ainsi, le bouton "2" permet d'afficher seulement les sous-totaux et le total général. Les symboles "+" et "-" permettent respectivement d'ouvrir ou de fermer un niveau de plan spécifique.
  
  
  
  Notons que les symboles ne s'impriment pas.

Bonnes statistiques !