Menu

Renvoie référence [Résolu]

dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 19:09 - Dernière réponse : Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention
- 12 sept. 2018 à 13:04
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

Afficher la suite 

Votre réponse

28 réponses

Meilleure réponse
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:32
1
Merci
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

Merci Mike-31 1

Avec quelques mots c'est encore mieux Ajouter un commentaire

CCM a aidé 26922 internautes ce mois-ci

Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:47
Prend plutôt ce fichier ou j'ai un peu amélioré les formules

https://www.cjoint.com/c/HIkuUSl2auj
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:48
Re,

merci pour le temps passé à ca…

ça marche…

pour le fichier de base, il y a 9 tableau par page, et 12 pages...

certain ont que 3 lignes, d'autre 10

par contre je ne maitrise absolument pas le VBA...
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:51
et je comprend pas les formules que tu as utiliser :-(
encore milles merci pour l'aide...
Commenter la réponse de Mike-31
Meilleure réponse
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - Modifié par Mike-31 le 12/09/2018 à 10:16
1
Merci
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
A+
Mike-31

Je suis responsable de ce que je dis, pas de ce que tu comprends...

Merci Mike-31 1

Avec quelques mots c'est encore mieux Ajouter un commentaire

CCM a aidé 26922 internautes ce mois-ci

dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 12 sept. 2018 à 10:26
Merci, j'arrive donc a copier coller et arriver au même résultat que toi a partir du moment ou je copier colle au même endroit que toi...

par contre, dès que je la place autre part, j'ai comme résultat #Valeur.. je modifie le chiffre après colone en fonction de ou je le colle... ici j'ai éssayé en D29, donc j'ai mis SI(COLONNE()-3 et aussi pour la fin...

a quoi correspond les "{ }"?
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 12 sept. 2018 à 10:35
ok, ça marche, j'avais a nouveau oublie le CTRL + SHIFT + ENTER…

mais a quoi serve les {}????
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 12 sept. 2018 à 11:05
Re,

ces accolades {} servent à déclarer une formule matricielle mais doit se faire automatiquement et non par saisie manuelle
une formule matricielle va (boucler) chercher un résultat sur une plage de cellule contrairement à une formule que va simplement chercher sur une cellule
Souviens toi de mes explications
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}
et bien la formule matricielle va chercher un résultat sur les lignes de 1 à 19 et non sur une seule ligne
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 12 sept. 2018 à 12:59
Super, merci beaucoup!

j'ai réussi a refaire cette formule même si je ne la comprend pas encore a 100%... le résultat est la…


tu m'as été d'une très grande aide Mike-31... un tout grand merci!!!
Commenter la réponse de Mike-31
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 19:13
0
Merci
Bonsoir,

Ton lien semble corrompu

pour joindre ton fichier
1) Tu vas dans http://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 http://www.commentcamarche.net/faq/29493-utiliser-cjoint

dans le même style tu as https://mon-partage.fr/
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 20:00
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 20:32
0
Merci
Re,

regarde ton fichier si c'est ce que tu cherches je t'expliquerais comment ça marche

https://www.cjoint.com/c/HIksFFqwpsj
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 20:40
En relisant ta demande je crois avoir mal compris je te proposait d'additionner les ventes.

je regarde comment rapatrier les références
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 20:39
0
Merci
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
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:05
0
Merci
Re,

Regarde comme cela et on en reparle

https://www.cjoint.com/c/HIkuew6codj
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:15
0
Merci
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…

:-(
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 10 sept. 2018 à 22:18
j'ai besoin que exel cherche automatiquement les items en fonction de l'item du tableau total… en sachant que les tableaux sont sujet à modification pas l'ajout d'une ligne ou du triage (pas ordre alphabétique ou par date (notion que j'ai oublier de préciser :-( )
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - Modifié par Mike-31 le 10/09/2018 à 23:18
0
Merci
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

A+
Mike-31

Je suis responsable de ce que je dis, pas de ce que tu comprends...
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 11 sept. 2018 à 09:28
Merci beaucoup pour ces formules…

petites questions par rapport a celle-ci…

- est important les "$" dans la formule? a quoi cela correspond?
- qu'est ce que veux dire ta formule, elle fonctionne... mais je comprends pas ce qu'elle veux dire, ce qu'elle fait... (ex: que veux dire "petite valeur?", a quoi correspond "-6<" ?
- quand je recopie ta formule sur mon dossier test en G2, et que je l'incrémente vers le bas, pas de soucis, pas contre dès que je l'incrémente vers la droite, il me met #NOMBRE!"

Merci a toi...
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 11 sept. 2018 à 09:52
dans ta formule, ta premiere "condition" est si avec:


- Teste logique: COLONNE()-6<=NB.SI($A$2:$A$20;$E3)
- Valeur si vrai: INDEX($C$2:$C$20;PETITE.VALEUR(SI($A$2:$A$20=$E3;LIGNE(INDIRECT("1:"&LIGNES($A$2:$A$20))));COLONNE()-6))&SI(H3="";"";" - ")
- Valeur si faux: ""

tu peux m'expliquer la veleur logique et la valeur si vrai?
Commenter la réponse de Mike-31
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 11 sept. 2018 à 10:09
0
Merci
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 ...
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 11 sept. 2018 à 11:15
0
Merci
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...
Commenter la réponse de dixian
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 11 sept. 2018 à 12:49
0
Merci
re,

a quoi correspond:

INDIRECT("1:"&LIGNES($A$2:$A$20)))?
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 11 sept. 2018 à 13:06
0
Merci
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)))
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - 11 sept. 2018 à 16:00
0
Merci
A quoi correspond "1" dans la formule (INDIRECT("1:"&LIGNES($A$2:$A$20))

merci pour l'astuce F9...
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 11 sept. 2018 à 16:13
0
Merci
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}
Commenter la réponse de Mike-31
dixian 18 Messages postés lundi 10 septembre 2018Date d'inscription 12 septembre 2018 Dernière intervention - Modifié par dixian le 11/09/2018 à 23:20
0
Merci
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
Commenter la réponse de dixian
Mike-31 16286 Messages postés dimanche 17 février 2008Date d'inscriptionContributeurStatut 20 septembre 2018 Dernière intervention - 12 sept. 2018 à 13:04
0
Merci
Re,

Si tes attentes sont satisfaites, je passe le statut de la discussion en résolu
Commenter la réponse de Mike-31