Lire en vertical les données d'un tableur
Rien de plus exaspérant d'essayer de lire un fichier Excel avec 150 colonnes, remplis de données chiffrées à analyser, ou avec plusieurs contacts à valider par ligne.
Bref, face à cet embarras, je vous propose une matrice Excel qui vous facilitera la vie pour valider votre jeu de données avant de charger votre jeu de données dans une base de données ou une BI.
Cette matrice est originellement issue de mes travaux pour le DVDRisk, solution d'analyse financière, permettant l'export de données marketing et de données bilancielles. Cette matrice était un complément à cette solution.
L'avantage de cette matrice est, qu'une fois les données exportées et collées dans le bon onglet, vous pouvez:
- Consulter et imprimer n'importe quel rapport sur les sociétés exportées
- Comparer face à face la situation financière de 2 entreprises
- Agréger les montants financiers de toutes ces entreprises, afin d'en déterminer une moyenne, une norme
Principe de fonctionnement
Il est assez simple, il se base sur 2 fonctions standard d'Excel, et n'utilise aucune macro ou tout code complexe. C'est donc ouvert à toute personne qui maîtrise les fonctions d'Excel (comme les fameuses fonctions RECHERCHEV ou SOMME.SI).
Les 2 fonctions standards sont:
- Fonction "LISTE" pour lister le contenu d'une colonne Excel, depuis une listbox
- Fonction "INDEX" pour lire une des cellules d'une même ligne
Le principe pour générer un beau rapport, se déroule en 6 étapes:
- Je copie-colle mon fichier exporté dans l'onglet réceptacle prédéfini (nommée liste ou export)
- Ma fonction liste fait appel à la première colonne de cet onglet (avec toutes les données) et me génère ma listox avec toutes les lignes
- En faut de mon onglet de rapport, je pose cette listbox
- J'utilise la fonction index pour lire les cellules de la même ligne de l'entité sélectionné dans ma listbox
- Je positionne tous ces appels "index" sur mon onglet rapport afin de lire en mode rapport les données souhaitées
- Je peux ajouter des graphiques et du style sur ces données pour apporter une meilleur lisibilité aux données restituées
Et c'est la dernière étape qui vous prendra le plus de temps. Pour vous faciliter la vie, j'ai préparé l'onglet rapport, en y collant la listbox, et j'ai mis sur la droite 2 colonnes toutes les données de votre export.
- La première colonne reprend les valeurs de la première ligne de votre export (vos entêtes)
- La deuxième la fameuse fonction "INDEX"
Vous n'avez qu'à positionner ces cellules pour faire en sorte que cela s'affiche comme un rapport d'analyse. Copier / déplacer ces cellules (drag&drop) selon votre bon vouloir. En jouant sur le contenu de la listbox, vous verrez que les données qui y sont liées s'affichent de suite, comme par magie!
La finalité est que vous puissiez créer votre modèle de présentation. Une fois la mise en page effectuée, il suffit de recoller vos différent exports dans l'onglet réceptable.
Une matrice par modèle d'export
Cette matrice mise en page à vos besoins fonctionne pour le même modèle d'export. Faites en sorte qu'il n'évolue pas mais comme vous le verrez, la matrice reste très facile à adapter.
Conseils et astuces
J'ai mis un onglet "AIDE" pour vous rappeler les bonnes pratiques
- Le clic-droit sur la listbox permet de gérer la colonne à afficher, le nombre d'élément à afficher dans la listbox, et le nombre de lignes à prendre en compte
- La listbox ne s'imprime pas: pensez à afficher le contenu de cette même cellule
- A noter que l'onglet réceptacle de vos données (LISTE) est sous forme de tableau afin de standardiser son contenu et surtout son entête.
- Sélectionnez à nouveau la première ligne de la listbox pour réafficher le nom de la valeur attendue.
- Une fois votre matrice terminée, pensez à la mettre en lecture seule en ayant au préalable vider les lignes de l'onglet LISTE (sauf l'entête). Cela devient un modèle à partager et vos collègues n'ont qu'à copier-coller le contenu de leur export.
- Pour éviter que vos collègues changent l'ordre ou modifie le rapprt, pensez à protéger les cellules en vérouillant toutes les cellules, sauf celles de la listbox.
Téléchargement
Je vous propose 1 fichier vierge prêt à 'emploi et 2 exemples issues de données extractibles depuis OpenDatasoft, à partir duquel vous pourrez exporter les données de votre choix, et les visualiser de suite! J'ai ajouté également un de mes modèles d'analyse financière qui était disponible dans mon ancienne solution DVDRisk, pour vous aidez à visualiser toute la simplicité et la puissance de la matrice.
- Matrice Excel "vierge" prête à l'emploi
- Matrice de lecture de bilans Consolidés issues
- Matrice de lecture de données INSEE pour votre prospection (à venir)
Les fichiers à télécharger:
- Matrice Lecture Verticale | Excel 2007 ou + | 5 lignes / 157 colonnes |463Ko
- Matrice Bilan consolidés | Excel 2007 ou + | 337 lignes / 450 colonnes | 949Ko
ul>
Quant à la propriété intellectuelle de cette matrice, il m'est difficile de dire si quelqu'un l'a déjà inventé. Je ne prétend donc pas à une paternité "copyrightée". Ce n'est qu'une idée, et une idée qui ne se partage pas n'a aucune valeur.
Pour vos remarques et commentaires, je vous laisse le soin de me contacter directement ou bien sur la page de mon blog dédiée à ces sujets..
|
