Renvoie référence Résolu/Fermé

Question

Bonjour, 

je cherche a faire en sorte d'obtenir des références de facture pour un bilan de fin d'année…

ici je met un exemple de ce que je cherche a faire, un fichier totalement fictif… mais qui montre bien ce que je cherche a avoir…

je planche dessus depuis un certain temps mais je trouve pas…

j'ai chercher sur le forum, mais j'ai pas trouvé de solution…

j'ai essayer avec la formule rechercheV. mais il me que la première référence de facture du tableau

Merci pour votre aide

https://1drv.ms/x/s!AsYEfNiLAPe7zACPEVfwiYy1GPLU

Configuration: Windows / Edge 17.17134

Mike-31 · Answer

Bonsoir,

Ton lien semble corrompu

pour joindre ton fichier 
1) Tu vas dans https://www.cjoint.com/ 
2) Tu cliques sur [Parcourir] pour sélectionner ton fichier (15 Mo maxi) 
3) Tu défiles vers le bas pour cliquer sur le bouton bleu [Créer le lien Cjoint] 
4) Au bout de quelques secondes la deuxième page s'affiche, avec le lien en gras ; tu fais un clic-droit dessus et tu choisis "Copier le lien" 
5) Tu reviens dans ta discussion sur CCM, et dans ton message tu fais "Coller". 
=>Voir la fiche https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers 

dans le même style tu as https://mon-partage.fr/

dixian · Answer

pour lien…

https://www.cjoint.com/c/HIkr6RRKhnh

Mike-31 · Answer

Re,

regarde ton fichier si c'est ce que tu cherches je t'expliquerais comment ça marche

https://www.cjoint.com/c/HIksFFqwpsj

dixian · Answer

Re,

Merci pour ta réponse, malheureusement ce n'est pas ce que je cherche…
ce que je veux, c'est que dans la colone C (Référence Facture) de la page "Total" ce retrouve ce que j'ai écris manuellement dans la colone D

Merci

Mike-31 · Answer

Re,

Regarde comme cela et on en reparle

https://www.cjoint.com/c/HIkuew6codj

dixian · Answer

la formule ne fonctionne pas…

si je change par exemple le premier item du tableau par cartable, les références ne sont plus bonne…

:-(

Mike-31 · Answer

Re,

Si tu ne dis pas tout, et de mon coté ce n'était qu'un début, regarde maintenant

https://www.cjoint.com/c/HIkuGdG7Rzj

par contre si ton tableau devient très important il faudra certainement traité par VBA

Mike-31 · Answer

Re,

Pour t'expliquer ce que j'ai fait, sur chaque onglet en colonne E j'ai simplement fait un copier/Coller avec liaison pour avoir la liste critère que tu as onglet Total ce qui donne =Total!A2 et incrémenté vers le bas

ensuite colonne F une concaténation des cellules de G à M =G2&H2&I2&J2&K2&L2&M2 

 colonne G une formule matricielle incrémentée vers le bas 
=SI(COLONNE()-6<=NB.SI($A$2:$A$20;$E2);INDEX($C$2:$C$20;PETITE.VALEUR(SI($A$2:$A$20=$E2;LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20))));COLONNE()-6))&SI(H2="";"";" - ");"")

Incrémenter les formule colonne G vers la droite jusqu’à la colonne M

et pour terminer afin de simplifier les formule j'ai nommé chaque tableau E2:F8 Tab_1 pour le premier puis Tab_2 pour l'autre onglet etc ...

Les colonnes E à M peuvent être masquées

Onglet Total colonne F j'ai simplement imbriqué des RECHERCHEV
=SIERREUR(RECHERCHEV(A13;Tab_1;2;0);"")&SIERREUR(RECHERCHEV(A13;Tab_2;2;0);"")&SIERREUR(RECHERCHEV(A13;Tab_3;2;0);"")&SIERREUR(RECHERCHEV(A13;Tab_4;2;0);"")
Et voilà le tour est joué, demain je regarderai voir s'il est possible de simplifier les formules

Mike-31 · Answer

Re,

Les dollars $ fixe la ligne, la colonne ou la cellule en fonction de l'emplacement que l'on lui donne tu peux te documenter sur les références absolues et relatives sur Google tu trouveras de nombreuses explications et vidéo sur le sujet comme 
https://www.youtube.com/watch?v=D71B-ffZwZc

la position des $ est très importante dans la rédaction de la formule et reprend les dernières formules sur mon post 13 que j'ai adaptées

PETITE.VALEUR permet d'extraire des valeurs croissantes de la plus petite vers la plus importante ou la troisième plus petite par exemple contrairement à MIN qui n'extrait que la plus petite.  

COLONNE() te renvoie l'index colonne exemple =COLONNE() colonne G te renvoie 7 puisque c'est la septième colonne depuis la colonne A et COLONNE()-6 te renvoie 7-6 =1 
mais incrémenté en colonne H COLONNE()-6 te renvoie 2 puisque colonne H= 8-6 = 2 etc ...

cette partie formule en colonne G PETITE.VALEUR($A$2:$A$20;COLONNE()-6) va permettre d'extraire la première petite valeur rencontré dans la plage 
en colonne H va extraire la deuxième plus petite valeur etc ...

dixian · Answer

merci pour l'explication sur le "$", l'explication est très claire,

je cherche encore a comprendre la formule mais j'ai du mal…


je vais un peu travailler encore dessus, mais il est fort probable que je demande encore une aide... 

j'ai besoin de comprend ce que je fais...

dixian · Answer

re,

a quoi correspond:

INDIRECT("1:"&LIGNES($A$2:$A$20)))?

Mike-31 · Answer

Re,

tu peux tester tous les parties d'une formule en sélectionnant dans la barre des formules une chaine valide non pas 
INDIRECT("1:"&LIGNES($A$2:$A$20)))
parce qu'incomplète mais 
LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20)))
et cliquer sur la touche F9 puis Echap pour sortir de la fonction 
ce qui t'affichera {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19} soit l'index ligne de 1 à 20
en fait en complétant cette partie de formule avec PETITE.VALEUR Excel va rechercher la petite valeur dans les lignes de 1 à 20 ou plus exactement 19
PETITE.VALEUR(SI($A$2:$A$20=$E3;LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20)))

dixian · Answer

A quoi correspond "1" dans la formule (INDIRECT("1:"&LIGNES($A$2:$A$20))

merci pour l'astuce F9...

Mike-31 · Answer

Re,
si tu testes chaque partie de la formule comme je te l'ai expliqué post 19

exemple cette partie LIGNES($A$2:$A$20) te renvoie 19 soit la ligne précédente de 20 
 si tu testes "1:"&LIGNES($A$2:$A$20) te renvoie "1:19" autrement dit lignes 1 à 19
si tu testes INDIRECT("1:"&LIGNES($A$2:$A$20)) revient à écrire manuellement INDIRECT("1:19") 
et précédé de LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20))) te renvoie l'index de chaque ligne concernée de 1 à la ligne précédente de la 20éme soit {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}

dixian · Answer

encore re…

quand je copie/colle ta formule, j'arrive pas a l'étendre…

https://www.cjoint.com/c/HIlvuv30jhA

de plus, quand j'éssaye de déplacer la formule, j'arrive pas a retrouvé les données… (exD29)

je comprend pas pk…

autre question… est il possible de figer un tableau dans une formule? comme "$"? j'ai essayé mais ca marche pas… sauf si j'entre manuellement les données…

Merci

Mike-31 · Answer

Re,

tu es resté sur une première formule, en G2 supprime un dollar $ après =$E$2
ensuite avant de faire entrée tu doit cliquer en même temps sur les trois touches du clavier Ctrl, Shift (touche majuscule) et Entrée et si tu fais bien ta formule se placera entre ces accolades {}
ensuite incrémente vers le bas puis vers la droite et tu verras que tes formules fonctionneront correctement

donc colle en G2 cette formule Ctrl, Shift et Entrée
=SI(COLONNE()-6<=NB.SI($A$2:$A$20;$E2);INDEX($C$2:$C$20;PETITE.VALEUR(SI($A$2:$A$20=$E2;LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20))));COLONNE()-6))&SI(H2="";"";" - ");"") 

en E2 remplace ta formule trop lourde par =Total!A2

Mike-31 · Answer

Re,

Si tes attentes sont satisfaites, je passe le statut de la discussion en résolu

Renvoie référence

17 réponses

Discussions similaires