XL 2010 Construction d'une hiérarchie

[fredd]

Bonjour, j'ai besoin d'aide pour touver la bonne logique de formule sur un tableur.
En exemple, en pièce jointe.
Ce que j'aimerais construire justre avec des formules:
j'ai dans les colonnes G un équipement et H l'équipement supérieur.
Construire sur A, B, C, D et E la hiérarchie

30064
      I- 998823
             I-  998826
             I-  AL212242
             I- AL212243
             I- AL212243
       I- 998825

En ligne: le 998826 de la colonne G:
En E; vide puisque 998826 n'est pas présent en F
En D; 998826 => Pas de niveau supérieur
En C, le parent de D 998826 => 998824
En B, le parent de C 998824 => 998823
En A, le parent de B 998823 => 30064

Je ne sais pas si je suis clair.
merci pour votre aide, je ne sais pas par que bout attaquer.

 

[CISCO]

Bonjour

Tu peux faire dans G2 avec

INDEX(A2:E2;EQUIV(9^9;A2:E2;1))

et dans F2 avec

SI(EQUIV(9^9;A2:E2;1)<>1;INDEX(A2:E2;EQUIV(G2;A2:E2;0)-1);"")

@ plus

 

[mapomme]

Bonsoir fredd, CISCO ,

Bon, j'ai interprêté complètement différemment la demande de fredd . J'ai compris que les données de départ étaient en colonnes G et H et qu'il fallait remplir les données des colonnes A à F.

Par formules, je patauge complètement. J'ai donc dérogé à la demande de solution par formules pour une petite maco VBA dans le module de code de la feuille "EQ - V16". Le code est un peu commenté.

nota: si mon interprétation est la bonne, j'attends avec impatience le formuliste qui trouvera la solution.

 

[CISCO]

Bonsoir

@ mapomme : Je vais essayer de remplir les premières colonnes avec des formules, comme tu l'as fait. Mais avant cela, pourrais-tu me dire si tu as eu le même problème que moi, à savoir que le calcul des formules ne se fait pas dans certaines colonnes (et pourtant le calcul est en mode automatique). Si oui, comment fait-on pour résoudre ce problème ?

Merci d'avance.

@ plus

 

[CISCO]

Bonsoir

Cf. en pièce jointe.

@ plus

P.S. : Vu que les calculs ne se faisaient pas automatiquement dans certaines colonnes, je les ai recréées vers la droite, puis ai supprimer les anciennes.

 

[mapomme]

Bonjour fredd, CISCO ,

Un essai via formules.

Je suis parti du fait que seules les colonnes G et H sont connues. Il faut donc compléter les colonnes A à D. Mais il faut aussi compléter la colonne F qui est le niveau à attribuer à chaque ligne.

Pour la formule de la colonne F, j'ai utilisé quatre colonnes masquées de I à L.

Mais avant cela, pourrais-tu me dire si tu as eu le même problème que moi, à savoir que le calcul des formules ne se fait pas dans certaines colonnes (et pourtant le calcul est en mode automatique).

Salut CISCO . Je n'ai rien remarqué. mais en cherchant la solution en VBA, le compilateur m'effaçait carrément des lignes de codes (de quel droit a t-il pris cette liberté?). Je suis donc reparti d'un nouveau fichier où j'ai collé les données. Le compilateur (plutôt interpréteur) s'est à nouveau comporté de manière normale.
Mes formules diffèrent des tiennes (plus la formule en colonne F). On arrive me semble-t-il au même résultat.

nota : si dans les données sources, la colonne F est connue, on peut directement effacer
les colonnes I à L

 

[CISCO]

Bonjour à tous

@ mapomme : J'ai considéré qu'on connaissait le contenu de la colonne F. Si ce n'est pas le cas, il faut effectivement faire autrement.

@ plus

 

[fredd]

Bonjour, tout d'abord merci pour vos réponses, j'épluche tout ça.
Et effectivement, je pensais tout de meme passer par VBA si je ne trouvais pas de solutions

Edit: effectivement la colonne F n'est pas connu, mais à calculer.

 

[mapomme]

Bonjour fredd ,

Bonjour, tout d'abord merci pour vos réponses, j'épluche tout ça.
Et effectivement, je pensais tout de meme passer par VBA si je ne trouvais pas de solutions

Pouvez vous nous préciser (par curiosité) quelle interprétation de la demande est la bonne ?

1. valeurs de la colonne F connues
2. valeurs de la colonne F à calculer

 

[fredd]

Bonsoir fredd, CISCO ,

Bon, j'ai interprêté complètement différemment la demande de fredd . J'ai compris que les données de départ étaient en colonnes G et H et qu'il fallait remplir les données des colonnes A à F.

Par formules, je patauge complètement. J'ai donc dérogé à la demande de solution par formules pour une petite maco VBA dans le module de code de la feuille "EQ - V16". Le code est un peu commenté.

nota: si mon interprétation est la bonne, j'attends avec impatience le formuliste qui trouvera la solution.

C'est un truc de ouf!!!!
J'ai effectivement testé et ça répond à ma demande.

Bon l'idée étais que je suis un peu novice en VBA et que je suis un peu plus à l'aise avec les formules et que c'est, pour moi un peu plus facilement duplicable.
Le VBA que j'aurais ESSAYER de déveloper consiste à l'enregistrement de macro avec quelques petite notions de code par çi et par là => certainement un échec.
mais là le code, il ne fais que quelques lignes si on enleve les commentaires...

C'est fou.

Je pense que je vais prendre tels quels, essayer de transposer dans mon fichier réel et privé.

Merci. mille fois, j'ai gagné beaucoup de temps.
Bon je vais en perdre personnellement à déchiffrer si je le peux le codage mais ça c'est juste pour ma culture

 

[fredd]

valeurs de la colonne F à calculer

Bonjour fredd ,


Pouvez vous nous préciser (par curiosité) quelle interprétation de la demande est la bonne ?

1. valeurs de la colonne F connues
2. valeurs de la colonne F à calculer

2. valeurs de la colonne F à calculer

 

[CISCO]

Bonjour

Une autre possibilité en pièce jointe, avec des formules, en considérant que la colonne F doit être calculée, sans colonne intermédiaire. Attention, chaque colonne contient une formule différente (Pour simplifier, on ne fait pas exactement le même nombre de RECHERCHEV dans chaque colonne).

@ plus

 

[fredd]

Bonjour Cisco. Tout d'abord, merci beaucoup.
Voilà 10 minutes que j'essaye de comprendre le raisonnement de la formule mais l'accumulation de recherche vertical, ben je me perd complet.
L'idée est que si ( on ne sais jamais ) j'ai besoin d'un niveau supplémentaire (mais je ne pense pas) que je suis capable de décupler ....

A2:
=SI(B2="";$H2;RECHERCHEV(B2;$H$2:$I$1440;2;0))
Si B2 est vide, je mets H2, sinon, je recherche 1 case à droite de H2

B2:
=SI(ESTNA(RECHERCHEV(RECHERCHEV($H2;$H$2:$I$1440;2;0);$H$2:$I$1440;2;0));"";SI(C2="";$H2;RECHERCHEV(C2;$H$2:$I$1440;2;0)))
ESTNA renvoi vrai
si ... est erreur je met vide sinon comme pour A2 mas décalé d'un cran
Mais les recherche des recherche je n'y arrive pas.

C2:
=SI(ESTNA(RECHERCHEV(RECHERCHEV(RECHERCHEV($H2;$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0));"";SI(D2="";$H2;RECHERCHEV(D2;$H$2:$I$1440;2;0)))

D2:
=SI(ESTNA(RECHERCHEV(RECHERCHEV(RECHERCHEV(RECHERCHEV($H2;$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0));"";SI(E2="";$H2;RECHERCHEV(E2;$H$2:$I$1440;2;0)))

E2:
=SI(ESTNA(RECHERCHEV(RECHERCHEV(RECHERCHEV(RECHERCHEV(RECHERCHEV($H2;$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0);$H$2:$I$1440;2;0));"";H2)

 

[mapomme]

Bonsoir à tous,

Ma version à formules adaptée au niveau 6.

 

[CISCO]

Bonsoir à tous, bonsoir Mapomme

Bonjour Cisco. Tout d'abord, merci beaucoup.
Voilà 10 minutes que j'essaye de comprendre le raisonnement ...

Il y a deux catégories de formules, d'une part,
* celle de la première colonne : Dans A2 on a

SI(B2="";$H2;RECHERCHEV(B2;$H$2:$I$1440;2;0))

Ce qui signifie : S'il ni y a rien dans B2 (et donc aussi dans C2, dans D2, E2 et ainsi de suite), c'est donc que la valeur dans H2 doit être de niveau 1 et qu'il faut l'écrire dans A2.

* d'autre part les autres, plus facilement compréhensibles (peut être) en commençant par celles dans la colonne la plus à droite, dans la colonne E dans le dernier fichier.
Dans E2 on a

SI(ESTNA(RECHERCHEV(RECHERCHEV(RECHERCHEV(RECHERCHEV(RECHERCHEV($G2;$G$2:$H$1440;2;0);$G$2:$H$1440;2;0);$G$2:$H$1440;2;0);$G$2:$H$1440;2;0);$G$2:$H$1440;2;0));"";G2)

RECHERCHEV($G2;$G$2:$H$1440;2;0) recherche $G2 dans la plage $G$2:$H$1440 et renvoie le résultat correspondant dans la seconde colonne. Si cette recherche donne #NA, tous les autres RECHERECHEV donneront aussi des #NA et il faudra mettre "" dans E2.

RECHERCHEV(RECHERCHEV($G2;$G$2:$H$1440;2;0);$G$2:$H$1440;2;0) fait de même.

Si on prend la formule complète, on voit qu'il y a 5 RECHERCHEV imbriqués les uns dans les autres. S'ils renvoient #NA, c'est que $G2 n'est pas de niveau 5, et donc, qu'il faut mettre "" dans E2. Sinon, on écrit G2.

Idem dans D2, mais avec seulement 4 RECHERCHEV imbriqués les uns dans les autres. Si ces 4 RECHERCHEV imbriqués renvoient #NA, on met "" dans D2. Sinon, on calcule SI(E2="";$G2;RECHERCHEV(E2;$G$2:$H$1440;2;0)) qui renvoie soit $G2 (G2 est de niveau 4), soit l'élément précédent G2 (E2 = G2 est de niveau 5 et on renvoie H2 qui est de niveau 4).

Idem dans C2, mais avec seulement 3 RECHERCHEV.

Idem dans B2 mais avec seulement 2 RECHERCHEV.

En fait, les RECHERCHEV permettent de voir si les antécédents existent.

En pièce jointe, la même méthode appliquée à 6 niveaux. A vérifier bien sûr.

@ plus

P.S 23/5/2017 : Pour passer d'un fichier à 5 niveaux à un fichier à 6 niveaux avec cette méthode, il faut :
* Insérer une nouvelle colonne (colonne F) avant la colonne NIVEAU
* Tirer la formule de la colonne E dans la colonne F. Rajouter un RECHERCHEV(.......;$H$2:$I$1440;2;0) englobant tout les autres RECHERCHEV dans la formule dans F2.
* Modifier la fin de la formule dans E2 en remplaçant le ;"";H2) final par ;"";RECHERCHEV(F2;$H$2:$I$1440;2;0).
* Modifier la formule de la colonne NIVEAU en mettant dans G2 ="NIV"&NB.SI(A2:F2;">0") au lieu de ="NIV"&NB.SI(A2:E2;">0"). Tirer cette formule vers le bas.

J'avais oublié de modifier les formules des colonnes E et F. C'est fait dans la pièce jointe.