http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

Transposer un tableau en utilisant les fonctions de recherche INDEX et EQUIV

Bonjour

Dans un précédent article (cliquer ici), j’ai déjà abordé le thème sur comment transposer un tableau en utilisant la fonction TRANSPOSE et le collage spécial/tranpose. Ces 2 techniques ont tout de même quelques limites:

  • La fonction TRANSPOSE est une fonction matricielle, donc difficile à modifier de plus elle oblige à présélectionner le nombre de cellules concernées;
  • Le collage spécial/transpose n’est pas automatique. En d’autres termes si le tableau initial a des lignes supplémentaires, il faudra refaire à chaque fois le collage spécial…

 Pour parer à tous ces petits inconvénients, on peut utiliser les fonctions de recherche. C’est d’ailleurs une technique que j’utilise souvent dans mes tableaux de bord. Par exemple dans REMPART, pour quitter de la liste des départements dans la feuille de donnée et les mettre automatiquement dans le tableau des budgets, j’utilise les fonctions de recherche.

Concrètement regardons le tableau ci-dessous: 

donnees transpose

 

Notre objectif sera de mettre une formule en E2 que l’on incrémentera jusqu’à … Z2 par exemple pour avoir automatiquement tous les départements insérés à partir de B4.

  • La 1ère étape est de mettre sous forme de tableau la liste de département: sélectionner B3:B9, dans le ruban, Accueil/Style/Mettre sous forme de tableau et choisissez le format qui vous plait (bien sûr valider le fait que le tableau comporte des entêtes)
  • Donner un nom à ce tableau, dans le nouveau menu (après avoir cliqué sur une cellule du tableau), Outils de tableau/Propriétés/Nom du tableau, insérer le nom qui vous plait et valider. J’ai insérer « Département »

nom tableau

 

  • En E2 écrire =INDEX( ensuite avec la souris sélectionner B3:B9, cliquer sur point-virgule de votre clavier, et écrique EQUIV(D2; ensuite sélectionner encore B3:B9 avec la souris, cliquer sur point-virgule du clavier et 0 et fermer la parenthèse. Écrire +1 et fermer la parenthèse. Au final. Votre formule ressemble à celle-ci-dessous:

=INDEX(Departement[[#Tout]; [Départements]]; EQUIV(D2;Departement[[#Tout]; [Départements]];0)+1)

À ce niveau notre formule est presque prête le seul problème que lorsqu’on tirera la formule sur plus d’éléments que le tableau initial en a, alors on aura des erreurs comme résultats. Pour gérer les erreurs, on va insérer la fonction SIERREUR et le résultat final sera celui-ci:

 =SIERREUR(INDEX(Departement[[#Tout]; [Départements]]; EQUIV(D2;Departement[[#Tout] ;[Départements]];0)+1); » ») 

  • Maintenant on peut incrémenter la formule jusqu’en Z2 et si on rajoute des départements alors l’incrémentation sera automatique en horizontal. 

formule

Pour en savoir plus:

, , , , ,

No comments yet.

Laisser un commentaire