XL 2010 Construction d'une hiérarchie

fredd

XLDnaute Occasionnel
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

Code:
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.
 

Pièces jointes

  • Construction d'une hiérarchie.xlsx
    70.1 KB · Affichages: 50

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir fredd, CISCO ;),

Bon, j'ai interprêté complètement différemment la demande de fredd :rolleyes:. 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é :eek: à 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.
 

Pièces jointes

  • Construction d'une hiérarchie-v1.xlsm
    71 KB · Affichages: 35

CISCO

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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.
 

Pièces jointes

  • Construction d'une hiérarchieter.xlsx
    105.3 KB · Affichages: 34
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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 :p
 

Pièces jointes

  • Construction d'une hiérarchie-v3.xlsx
    181.1 KB · Affichages: 28

fredd

XLDnaute Occasionnel
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.
 
Dernière édition:

fredd

XLDnaute Occasionnel
Bonsoir fredd, CISCO ;),

Bon, j'ai interprêté complètement différemment la demande de fredd :rolleyes:. 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é :eek: à 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.

o_O

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

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 :rolleyes:
 

CISCO

XLDnaute Barbatruc
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
 

Pièces jointes

  • Construction d'une hiérarchie V7.xlsx
    109.5 KB · Affichages: 33
Dernière édition:

fredd

XLDnaute Occasionnel
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 .... :confused:
Code:
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)
 

CISCO

XLDnaute Barbatruc
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
Code:
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
Code:
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.
 

Pièces jointes

  • Construction d'une hiérarchie V8.xlsx
    122.3 KB · Affichages: 31
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
312 920
Messages
2 093 641
Membres
105 772
dernier inscrit
Momzo