Discussion :

[awa123]

Bonjour

je cherche une méthode pour pouvoir identifier le nbre de commande différente que j'ai par jour et cela sans compter les doublons à partir du fichier excel joint

merci de votre aide !

[joe.levrai]

Bonjour,

merci de prendre le soin de décrire la conception de ta feuille plutôt que de joindre un fichier

donc, en aveugle, charge à toi d'adapter :

- soit une feuille où j'ai en Colonne A des dates / En colonne B des numéros de commandes (ou l'inverse, ça ne changera rien)
- la ligne 1 est occupée par les titres "DATE" et "COMMANDE"
- cette plage est un tableau structuré (Menu Insertion >> Tableau) avec le nom par défaut Tableau1


Après avoir écrit en colonne D (à partir de D2) la liste des dates sans doublon :
1) recopier l'ensemble de la colonne A dans la colonne D
2) utiliser l'outil de nettoyage des doublon : Menu Données >> Supprimer les doublons


Ecrire en E2 la formule matricielle suivante (à valider par Ctrl+Maj+Entrée)

=NB(1/FREQUENCE(SI((Tableau1[[#Données];[DATE]]=D2);Tableau1[[#Données];[COMMANDE]]);Tableau1[[#Données];[COMMANDE]]))

Ceci fait, tirer la formule vers le bas pour chaque date.

[awa123]

Waaw EXCELLENT

mille merci !

[Pierre Fauconnier]

Salut.

Un TCD me semblerait utile, dans ce cas.

N'oublie pas non plus de travailler avec un tableau de données (transformer ta plage en tableau de données via Insert\Tableaux\Tableau). Ainsi, si tu ajoutes tes données, elles sont prises en compte dans tes formules et tes traitements.

[awa123]

mais il me semble que le TCD compte les doublons, non ?

Et c'est bien la le pb, si pour une date donnée nous trouvons plusieurs n° de commande identique pour cette même date, le TCD fera une somme sans tenir compte des doublons

[Pierre Fauconnier]

Tu as tout à fait raison. J'avais zappé cette info.

Note que dans la chouette formule de Joe.Levrai ( ), tu peux laisser tomber ce qui est en gras dans Tableau1[[#Données];[DATE]]= et utiliser uniquement Tableau1[DATE]=, vu que tu es en 2016. Idem pour la colonne des commandes.

Pour réaliser cela par TCD (si tu avais besoin d'autres analyses), tu pourrais te tourner vers PowerPivot pour préparer ton TCD.

[joe.levrai]

Bonjour,

Pierre, je rebondis sur ta remarque concernant le caractère optionnel de ciblage sur la zone #Données

en fait, j'ai pris l'habitude de toujours les mettre car parfois c'était impératif, mais effectivement pas toujours.
Et pas moyen de comprendre la raison ...

A te lire, c'est uniquement lié à la version Excel ? J'avais presque trouvé une explication liées aux formules (suivant le type de formules qu'on utilise ou le fait que ça du matriciel) mais rien de bien probant.

[Pierre Fauconnier]

Salut Joe,

La version 2007 était très verbeuse, mais depuis la 2010, je n'ai jamais rencontré de problèmes en n'utilisant pas [#Données] et par défaut, Excel prend uniquement la zone de données du tableau ou de la colonne si précisée...

[joe.levrai]

Cool merci

effectivement, maintenant que tu le dis, je rencontrais le problème quand au sein de mon organisation nous avions des environnements hybrides (2007 - 2010 - 2013 - 2016)

un peu moins bête ce soir en me couchant

[awa123]

Bonjour Joe,

merci bcp pour la manipulation décrite ci-dessus, cette formule matricielle fonctionne à merveille.

toutefois lorsque j'ai plus de 200 milles lignes, excel surchauffe et le temps de traitement est très long,
auriez vous une solution/conseils pour cela ?

merci d'avance

[joe.levrai]

Bonjour,

il est rare de pouvoir obtenir le beurre et l'argent du beurre.

un listing de 200 000 lignes et des formules matricielles pour dénombrer en dédoublonnant, c'est forcément mauvais mariage pour les impatients
on peut éventuellement partir sur une piste non matricielle, mais qui va nécessiter une colonne supplémentaire et un tri des données afin de mâcher le travail et alléger la formule pour ôter l'aspect matriciel qui provoque l'indigestion de calcul.

j'ai, fait rare, ouvert ton classeur pour la démonstration

1) J'ai inséré un tableau (Tableau1) sur ta plage de données (pas obligatoire mais plus pratique et lisible pour faire les formules)
2) J'ai trié le tableau sur la colonne DATE (plus ancien au plus récent) puis sur la colonne num_commande (du plus petit au plus grand)
3) J'ai ajouté une troisième colonne (je l'ai appelée "OK") et écrit sur la première ligne la formule suivante :

=SI(ET([@[num_commande]]=A1;[@date]=B1);"";1)

Cette formule, puisqu'appliquée à une colonne de mon tableau structurée, s'est répercutée sur toutes les lignes de mon tableau
J'ai maintenant une colonne qui m'indique "1" si pour une journée donnée, la commande apparaît pour la première fois, et "rien" si c'est une commande déjà référencée sur cette date

J'ai écrit sur le côté les différentes dates (colonne F)
En colonne G (G1), j'ai écrit la formule suivante :

=SOMMEPROD(1*(Tableau1[date]=F1)*(Tableau1[OK]=1))

J'ai tiré la formule vers le bas pour calculer les autres dates

et le tour est joué ?

[Pierre Fauconnier]

Pour moi, l'analyse de 200.000 lignes, ça passe par PowerPivot. La puissance de PowerPivot est vraiment là pour alléger ce genre de travail et Power Pivot offre les possibilités de tests sur les doublons.

[joe.levrai]

Une autre piste :

1) Utiliser un filtre avancé en mode "copie des éléments", sans aucun critère filtre, en ayant coché "extraction sans doublon"
2) On se retrouve avec une nouvelle plage totalement dédoublonnée sur laquelle un simple NB.SI (ou un TCD pour reprendre la proposition de Pierre) se suffira à lui-même



Réalisable également en automatique via VBA, en 5 lignes de codes

[awa123]

super merci beaucoup !!!