Eclatement selon un code
L’objectif est de récupérer, dans les colonnes D à F, les montants selon le code comptable présent dans le titre de la colonne, en D1:F1.
La formule miracle, à entrer sous forme de formule matricielle – donc avec la combinaison [Ctrl]-[Maj]-[Entrée] – en D2:D9 est la suivante :
=index($B$2:$B$7;petite.valeur(si(D1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne($1:$10)))
On reproduit ensuite le bloc D2:D9 dans les colonnes E et F pour obtenir le résultat ci-dessus.
Pour mieux comprendre cette formule, il faut savoir que la partie petite.valeur (si(D1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne($1:$10)) de la formule est évaluée de la façon suivante : {1;4;6;#NOMBRE!;#NOMBRE!;#NOMBRE!;#NOMBRE!;#NOMBRE!;
#NOMBRE!;#NOMBRE!}.
Remarque 1 – On pourrait masquer les messages d’erreur par un format conditionnel mettant en police blanche toutes les cellules évaluées comme erreur, à l’aide de la formule =esterreur(D2)…
Remarque 2 – On pourrait éviter ces erreurs sans format conditionnel, mais au prix d’une formule nettement plus lourde : =si(ligne()-1<=nb(petite.valeur(si(H1=$A$2:$A$7; ligne ($A$2:$A$7)-1);ligne($1:$10)));index($B$2:$B$7;petite.valeur(si(H1=$A$2:$A$7;ligne($A$2:$A$7)-1);ligne ($1:$10)));"")
rédigé par Hervé Thiriez |
7 commentaire(s)
7 Commentaire(s):
Que de contorsions pour arriver à un résultat tellement simple à obtenir avec un tableau croisé dynamique !
(avec les totaux par code en prime...)
Non, décidément, dès qu'il s'agit de gestion des données, hors TCD point de salut !
By
cduigou, sur 7:42 PM
Je suis pour ma part beaucoup plus réservé sur les TCD, ayant constaté de nombreux problèmes qu'ils posaient chez mes clients.
Souvent, il m'a fallu écrire des macros pour rendre ces TCDs à la fois efficaces et fiables.
Le seul avantage que je vois au TCD, c'est la possibilité de croiser rapidement plein de données.
En dehors de ce cas précis, je pense que l'on peut très souvent faire mieux autrement...
De plus, cet article était aussi un "exercice de style"...
By
Hervé Thiriez, sur 9:39 AM
Bonjour,
Je cherchais exactement cette fonctionnalité, malheureusement, elle ne marche pas chez moi, ceci dit, il faut peut etre un excel plus évolué que le mien, je tourne avec excel 2002 ?
La fonction repeté sur plusieurs ligne, ne me ramene que le premier enregistrement trouvée et non les suivants.
votre fonction est elle utilisable sur toute les versions Excel ?
Merci encore pour ce travail sur le blog.
By
Thierry F., sur 9:57 AM
Mailez-moi votre fichier à thiriez@hec.fr pour que je voie ce qui cloche...
By
Hervé Thiriez, sur 10:04 AM
Des formules matricielles alors qu'un simple RECHERCHEH suffit ?
Autant pour comprendre ces formules matricielles, je comprends, autant jamais je ne les conseillerai.
Peu intuitif à la lecture de la formule et lourd à écrire.
By
Molenn, sur 10:58 AM
Bonjour,
Désolé de commenter sur un si vieux post mais ça pourrait être utile pour des personnes qui comme moi parcourent ces précieuses archives :)
Je n'arrive pas non plus à faire fonctionner la formule sur excel 2010, du coup je me permets de proposer la formule suivante un peu différente qui semble fonctionner après quelques tests.
à mettre en D2 et à valider sous forme de formule matricielle (ctlr + shift + entrée)
la version anglaise
=IFERROR(SMALL(IF($A$2:$A$7=E$1;($A$2:$A$7=E$1)*($B$2:$B$7));$D2);"")
et son équivalent en français si je ne me trompe pas
=SIERREUR(PETITE.VALEUR(SI($A$2:$A$7=E$1;($A$2:$A$7=E$1)*($B$2:$B$7));$D2);"")
Merci pour ce partage de connaissances !
By
Antoine, sur 2:28 PM
Pardon j'ai oublié de préciser que j'avais rajouté une colonne avant la colonne D pour y mettre des numéros de récurrence (1, 2, 3, ..).
Comme indiqué dans cette capture d'écran :
http://zupimages.net/viewer.php?id=16/34/k5ek.jpg
By
Antoine, sur 2:40 PM
Enregistrer un commentaire
<< Accueil