Problème avec formule index - equiv

Y compris Power BI, Power Query et toute autre question en lien avec Excel
p
papillon_diabolik
Jeune membre
Jeune membre
Messages : 17
Inscrit le : 12 juin 2011
Version d'Excel : 2016

Message par papillon_diabolik » 26 août 2018, 18:50

Bonjour,

J'ai travaillé sur un fichier pour automatiser la construction d'un tableau pour mes relances clients.

J'ai un plusieurs soucis:

- D'abord jusque je mets un code il me prend la référence d'avant et je ne sais pas pourquoi ?

- Ensuite j'ai fait une formule index equiv pour recopier les factures non payées par client ( une feuille par client par rapport à son code) mais il me prend que la première facture et je sais pas comment faire quand il y a que 5 factures ou s'il y en a 100.

- Enfin je ne sais pas s'il est possible dans le TCD de créer des champs personnalisés par exemple je chercher à mettre des colonnes non pas avec toutes les dates mais des périodes de 15 jours ?

J'ai réussi à trouver pour recopier un modèle de feuille, les renommer et les trier mais le reste je ne trouve pas.

Auriez-vous des pistes à me donner pour que je trouve la solution ?

Merci d'avance

Audrey
Relance model.xlsm
(89.75 Kio) Téléchargé 10 fois
Avatar du membre
jpbt84
Membre dévoué
Membre dévoué
Messages : 561
Appréciations reçues : 7
Inscrit le : 18 juin 2016
Version d'Excel : Pro Plus 2019 FR

Message par jpbt84 » 26 août 2018, 18:57

Bonjour,

Déjà, pour la récupération du nom, au niveau de ton INDEX/EQUIV, comme tu travailles avec des tableaux, ce qui est très bien, il y a une subtilité.

Tu écris
=INDEX(Tableau1[#Tout];EQUIV('45002'!B1;Tableau1[Code];0);2)
Alors qu'il faut écrire
=INDEX(Tableau1[#Tout];EQUIV('45002'!B1;Tableau1[[#Tout];[Code]];0);2)
Il est vrai que pour cette partie là, tu aurais pu utiliser un RECHERCHEV...
Mais le plus important est que cela fonctionne.

Je regarde pour le reste....
Mais tu pourrais un peu expliquer les différentes feuilles ?

To be continued
Formateur bureautique, certifié ECDL Expert
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 5'045
Appréciations reçues : 384
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 26 août 2018, 20:01

Bonjour

Quelques remarques : un tableau
  • ne doit pas contenir de lignes vides : TIERS devrait s’arrêter à la ligne 23 : il s’agrandira automatiquement lors de l'ajout de nouvelles données sur le ligne 24
  • doit avoir un nom faisant sens : tableau1 n'en a pas et vu le nombre de tableaux de ton classeur, cela va devenir difficilement maintenable
La bonne formule est
=INDEX(Tiers;EQUIV(MODEL!B1;Tiers[Code];0);2)
Pour récupérer la liste des factures, dans MODEL la solution n'est pas la même selon que ECHEANCE est trié par tiers ou non.
La formule
=INDEX(ECHEANCE!$A$1:$F$151;EQUIV(MODEL!$B$1;ECHEANCE!B:B;0);5)
répétée à l'identique ne pourra que donner un résultat identique sur toutes les lignes...

Si le tableau de droite de MODEL (et autres) est lié à celui de gauche, il ne devrait pas y avoir 2 tableaux mais un seul, sinon il devrait ne comporter qu'une ligne : lui aussi s'agrandira au remplissage.

ECHEANCE, source du TCD, devrait être sous forme de tableau.

Pour des quinzaines : on peut soit grouper par 15 jours dans le TCD mais cela ne fera pas des 1/2 mois. IL vaut mieux ajouter une colonne à la source.

Sachant que tu as 2016, des solutions PowerQuery peuvent aussi être envisagées...
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
p
papillon_diabolik
Jeune membre
Jeune membre
Messages : 17
Inscrit le : 12 juin 2011
Version d'Excel : 2016

Message par papillon_diabolik » 26 août 2018, 21:12

Bonjour 78chris

Merci tout d'abord pour ta réponse.

Alors merci oui je vais changer le nom de tableau des tiers (Dans la vraie base il y a 2500 clients) et je vais retirer les lignes vides

Le tableau de droite de MODEL est complètement indépendant de celui de gauche car il sert surtout à faire nos relances et à marquer nos commentaires.

Et pour le PowerQuery, je n'ai jamais travaillé avec cet outil.

Par contre du coup pour la récupération des factures tu n'as pas de solution ?

Merci
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 5'045
Appréciations reçues : 384
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 26 août 2018, 21:40

RE

Tu n'as pas répondu sur "la solution n'est pas la même selon que ECHEANCE est trié par tiers ou non".

Est-ce trié ou non ?
Si oui envisageable par formule, si non, formules possibles mais ça va ramer grave.

Je partirais plutôt sur PowerQuery mais dans tous les cas attention au nombre d'onglets car cela risque de ramer que ce ce soit formules ou PQ. Tu n'envisages pas un onglet par client (2500 ?) ?
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
p
papillon_diabolik
Jeune membre
Jeune membre
Messages : 17
Inscrit le : 12 juin 2011
Version d'Excel : 2016

Message par papillon_diabolik » 27 août 2018, 13:33

Pardon,

Oui on peut faire un tri par numéro de tiers.
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 5'045
Appréciations reçues : 384
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 27 août 2018, 20:00

Re

J'ai mis le tableau d'échéances sous forme de tableau, renommé certaines tableaux et modifié toutes les formules.
J'ai ajouté le calcul de quinzaine et adapté le TCD

Cependant le nombre de lignes étant inconnu à l'avance, cela oblige à prévoir beaucoup de lignes avec des formules lourdes en calcul sur un nombre d'onglets qui va croitre...

Je crains que cela devienne vite inexploitable...

De plus j'ai un sérieux doute sur l'intérêt de ventiler les échéances par Tiers chacun dans un onglet alors qu'un filtre sur le tableau principal donne le même résultat et qu'avec 2016 le filtre peut être fait très rapidement par segment...
Relance model2.xlsm
(116.23 Kio) Téléchargé 9 fois
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
p
papillon_diabolik
Jeune membre
Jeune membre
Messages : 17
Inscrit le : 12 juin 2011
Version d'Excel : 2016

Message par papillon_diabolik » 7 septembre 2018, 12:26

Merci beaucoup

En faite je veux garder un historique de mes relances et cela ne sera pas possible si je change mon TCD à chaque quinzaine.

Je regarde ça et je te dis.

Bon week-end
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'603
Appréciations reçues : 251
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 7 septembre 2018, 13:46

bonjour

salut au passage Chris

la balance âgée destinée à un suivi des relances, c'est un seul onglet.
-conserve uniquement ECHEANCE
- ajoute une colonne DateDeRelance et une ActionDeRelance et une DateDeRèglement

c'est tout

ensuite, tu filtres (menu Données Filtre) pour ne pas voir les Dates de Règlement remplies (masque les factures réglées)
et tu tries par DateEchéance pour voir les retards.

c'est hyper simple (ça fonctionne dans 3 minutes sans problème), hyper efficace et durable et compatible smartphone et Mac et te durera pendant 10 ou 20 ans.

supprime les autres onglets et toutes les formules.

oui, d'accord, fais un second onglet pour un TCD pour voir les totaux par client et par mois (utile pour se motiver à appeler les clients)

ainsi tu pourras consacrer du temps à relancer et faire rentrer les factures, et non à bricoler Excel.

bon travail.
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message