Discussion :

[Phil333250]

Bonjour à toutes et tous,

Depuis un outil de prise de commandes, j'exporte les données concernant toutes les commandes dans un fichier Excel nommé "Export".
Les données sont entre autre : le n° de commande, le nom et les coordonnées du client, la référence, la désignation, la quantité et le prix de chaque article commandé, etc (43 colonnes au total) cf. image ci-dessous colonnes C à J.
Seulement, lorsqu'un client commande plusieurs articles, les données concernant la référence, la désignation, la quantité et le prix de chaque article commandé sont inscrites sur autant de lignes qu'il y a d'articles.
Donc une commande peut avoir 1 ligne (1 article commandé) comme 12 lignes (12 articles commandés).

Pour une commande d'1 article, chaque champ correspondant au n° de commande, nom et coordonnées du client, à la référence, à la désignation, à la quantité et au prix l'article commandé est renseigné.
En revanche, dès qu'il y a plusieurs lignes, chaque champ correspondant au n° de commande, nom et coordonnées du client n'est renseigné que sur la première ligne. Sur les lignes suivantes ces champs sont vides (ni 0, ni caractère que quelque forme soit-il).

Je récupère dans mon fichier de travail (par macro) l'ensemble des données concernant toutes les commandes. J'ai rajouté une 44 ème colonne (colonne B image ci-dessous) afin que pour chaque ligne vide du champ N° de commande remonte automatiquement le N° de commande.

Mon problème est le suivant :
Lorsque la référence d'un article débute par L je dois imprimer une étiquette sur laquelle sont repris les champs n° de commande, nom et coordonnées du client. j'ai pour cela un TCD qui me permet d'extraire les données souhaitées.
J'ai créé une colonne (colonne L) qui me permet de tester si la référence de l'article commence par L. Si c'est le cas, la formule renvoie le chiffre 1, sinon elle renvoie 0.
Le TDC filtre sur ce 1, pour extraire les champs n° de commande, nom et coordonnées du client. Cela ne pose pas de problème lorsque le premier article commandé a une référence commençant par L.
En revanche rien ne va plus si la référence du premier article commandé ne commence pas par L.

Donc, j'ai adapté ma formule afin de me permettre de "remonter" le chiffre 1 en première ligne et ainsi d'avoir, quelle que soit la place de la référence avec L, dans mon TCD les champs renseignés correctement et non pas renseignés (vide).

Cette formule fonctionne sauf dans un cas, c'est lorsqu'il y a 2 articles et plus de commandés et qu'aucun n'a une référence commençant par L.

La colonne M "Attendu" correspond à ce que je souhaite voir apparaître dans la colonne L. Les 2 cellules en rouge montre que la formule ne fonctionne pas correctement.

Mais je ne sais résoudre ce problème.

Voici la formule :

=SI(K2=1;SI(STXT($G2;1;1)="L";1;0);SI(B2<>B1;1;0))

La colonne K me permet de connaître le nombre de lignes concernant une commande donnée.

+NB.SI(B$2:B$39;B2)

Merci pour votre aide.

Philippe

[78chris]

Bonjour

Tu te compliques pour rien. Utilise cette méthode, quitte à l'automatiser point "Combler les trous d'un tableau pour disposer d'une véritable liste de données" http://www.excel-formations.fr/Trucs...s/ToucheF5.php

Elle permettra de récupérer une vraie base utilisable, triable, filtrable...

[catapoulpe]

Bonjour

Je suis du même avis que 78chris. Travailler sur des colonnes comprenant des cellules vides est une mauvaise idée. Par une méthode ou par une autre, il faut que tu puisses obtenir des colonnes "pleines". Ensuite ton (tes) TCD ne doi(ven)t porter que sur des colonnes pleines.

Une fois ceci réalisé, quelques précisions s'il te plaît :
Quand tu écris "Lorsque la référence d'un article débute par L" tu veux dire "Lorsque la référence d'un article ou plus parmi ceux d'une commande débute par L" ?
et "je dois imprimer une étiquette" pour la commande ? pour chaque article commençant par L ? pour chaque article de la commande ?
"sur laquelle sont repris les champs n° de commande, nom et coordonnées du client" donc uniquement des information communes à l'ensemble de la commande ? ou bien il y a des informations concernant seulement certains articles de la commande ?

A priori, si tu utilises un TCD pour récupérer les infos d'une étiquette (que je suppose, en attendant tes précisions, commune à toute la commande), il vaudra sûrement mieux afficher "1" dans ta colonne L à chaque ligne de la commande comprenant un article L.

Ce qui donne en L8 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS(B:B;B8;G:G;"L*")>0 affiche VRAI ou FAUX

ou à la rigueur pour te faire plaisir si tu tiens vraiment à n'afficher le 1 qu'à la première ligne de la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(B8<>B7;NB.SI.ENS(B:B;B8;G:G;"L*")>0)

Avec la restriction suivante : ton tableau doit bien être trié par n° de commande, càd que toutes les lignes d'une même commande doivent se suivre. Si tu t'amuses à déplacer les lignes (par exemple en triant selon une autre colonne), la valeur "VRAI" pourra apparaître plusieurs fois pour une même commande.

[Phil333250]

Bonjour Catapoulpe et 78chris,

Merci pour vos réponses.

Je suis d'accord avec vous quand vous dites qu'il ne faut pas laisser de champ vide pour exploiter correctement une base.
Mais j'ai pour principe de ne jamais modifier une base que j'importe afin de la conserver telle qu'elle est dans le fichier source.
Lorsque je dois l'exploiter soit je la duplique et fais ce que j'ai à faire sur l'onglet dupliqué, soit je crée autant de colonnes "à droite" que de besoin, afin de remplir les cellules vides notamment. Dans le fichier que je développe pour l'entreprise de ma fille, j'ai opté pour la seconde façon de procéder.

C'est ce que j'ai fait notamment avec la colonne B "N°_Commandes", j'ai rempli les vides par rapport à la colonne C "N° Commandes".
Ce sont ces colonnes qui me permettent de faire entre autre mes TCD.
Par exemple, à partir d'un TCD, j'ai un ensemble de macro pour réaliser les factures, les éditer en PDF, générer les écritures comptables correspondantes, les exporter mensuellement dans un fichier destiné à être transmis à l'expert comptable.
Comme indiqué dans mon 1er message, la base source comporte 43 colonnes au total. Dans mon fichier actuel, j'ai au total 74 colonnes. Je n'ai traité les vides que pour dont j'avais besoin.

Catapoulpe,

Quand tu écris "Lorsque la référence d'un article débute par L" tu veux dire "Lorsque la référence d'un article ou plus parmi ceux d'une commande débute par L"

Oui

"je dois imprimer une étiquette" pour la commande ? pour chaque article commençant par L ? pour chaque article de la commande ?
"sur laquelle sont repris les champs n° de commande, nom et coordonnées du client" donc uniquement des information communes à l'ensemble de la commande ? ou bien il y a des informations concernant seulement certains articles de la commande ?

Les seules informations reprises sur l'étiquette sont le N°de commande, les nom et prénom, l'adresse 1 et 2, le CP, la ville et le pays du client. Rien concernant les articles.

Voilà, j'espère avoir été clair dans mes explications.

Merci pour cette formule
=ET(B8<>B7;NB.SI.ENS(B:B;B8;G:G;"L*")>0) qui renvoie Vrai ou Faux

Je l'ai adaptée pour avoir 1 ou 0

=SI(ET(B8<>B7;NB.SI.ENS(B:B;B8;G:G;"L*")>0);1;0)

Cela correspond exactement à ce que je voulais.

Encore merci pour votre aide.

Philippe

[catapoulpe]

Pour afficher 0 ou 1, il y a plus simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=1*(ET(B8<>B7;NB.SI.ENS(B:B;B8;G:G;"L*")>0))

En effet, les valeurs VRAI et FAUX valent respectivement 1 et 0 lorsqu'elle sont introduites dans une formule "numérique".

Je comprends ton point de vue sur les colonnes avec des cellules vides, je fais de même pour d'autres raisons (la lisibilité et l'économie de saisie sur les valeurs qui se répètent plusieurs lignes de suite). Et comme toi, je "duplique" ces colonnes en les "remplissant" pour pouvoir les utiliser. Mais du coup je ne me sers pas du tout des colonnes avec vides, sauf pour la saisie et la lecture directe de mon tableau.

A partir du moment où tu as bien quelque part les colonnes Nom, adresse etc... sans cellules vides, et que tu extrais à l'aide d'un TCD ces colonnes pleines et non pas les colonnes avec des vides, je ne vois pas l'intérêt d'avoir la valeur 1 sur une seule ligne de la commande, et encore moins de l'avoir sur la 1ère ligne (et pas, par exemple, sur la ligne de l'article concerné). Il y a une raison particulière à cela ? Ton TCD te sert peut-être aussi à d'autres usages ?

EDIT : et même :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=1*ET(B8<>B7;NB.SI.ENS(B:B;B8;G:G;"L*"))

Sans parenthèses inutiles, et sans préciser NB.SI.ENS >0 car dans une formule logique, 0 est considéré comme FAUX et toute autre valeur est considérée comme VRAI.

[Phil333250]

Merci pour ces nouvelles formules, j'en découvre tous les jours.

A partir du moment où tu as bien quelque part les colonnes Nom, adresse etc... sans cellules vides, et que tu extrais à l'aide d'un TCD ces colonnes pleines et non pas les colonnes avec des vides, je ne vois pas l'intérêt d'avoir la valeur 1 sur une seule ligne de la commande, et encore moins de l'avoir sur la 1ère ligne (et pas, par exemple, sur la ligne de l'article concerné). Il y a une raison particulière à cela ? Ton TCD te sert peut-être aussi à d'autres usages ?

Dans la base récupérée il y a les nom et prénom, l'adresse 1 et 2, le CP, la ville et le pays du client pour la livraison et les nom et prénom, l'adresse 1 et 2, le CP, la ville et le pays du client pour la facturation.
Ce qui fait que pour le TCD lié au processus de facturation, je me sert des champs "facturation" pour lesquels j'ai rajouté les colonnes correspondantes.
Pour le TCD lié aux étiquettes (il ne sert qu'à cela), je me sert des champs "livraison". Il est vrai que j'aurai pu créer d'autres colonnes mais j'essaie tout de même de marcher à l'économie de colonnes. D'autant plus que je viens de voir ma fille et elle me rajoute encore des demandes.

Quant au fait de remonter des 1 sur la 1ère ligne c'est que c'est sur cette première ligne que tous les champs "importés" sont renseignés. A partir de la seconde ligne d'une commande ce ne sont que quelques champs qui sont renseignés (référence article, désignation, quantité et prix).

Encore merci pour ton aide.

Philippe