XL 2016 Occurence la plus fréquente avec conditions

Dpi- · 5 Janvier 2021

Bonjour à tous,

Inspiré par le site de J. Boisgontier (merci à lui pour toutes les fois où il m'a aidé sans le savoir !), j'ai résolu mon problème de formule matricielle.
Il consistait à faire apparaître les occurences de texte les plus fréquentes afin d'obtenir un top 10.
Si aucune donnée, je souhaite ne rien afficher.

Voici la première formule en E6 :
{=IFERROR(INDEX(Col_ok;MODE(IF(Col_ok<>"");MATCH(Col_ok;Col_ok;0))));"")}
Et les formules suivantes juste en dessous :
{=IFERROR(INDEX(Col_ok;MODE(IF((Col_ok<>"")*(Col_ok<>E6);MATCH(Col_ok;Col_ok;0))));"")}

Mon problème, c'est que j'ai besoin d'ajouter une condition pour 2021 :
Toutes les lignes dont la date est inféreure à 2021 ne doivent pas être prises en compte.

Je pensais m'en sortir avec cette condition supplémentaire :
{=IFERROR(INDEX(Col_ok;MODE(IF((Col_date<"01/01/2021")*(Col_ok<>"")*(Col_ok<>E6);MATCH(Col_ok;Col_ok;0))));"")}
Mais les résultats indiquent des textes qui datent d'avant 2021...

Pourriez-vous m'aider ?

R@chid · 5 Janvier 2021

Re,
Oui c'est vrai avec 1400 lignes c'est trop pour cette formules matricielle.
Sinon on pourrait le faire avec une colonne intermédiaire, et si tu étais sous Excel pour Microsoft 365, on peut faire facilement avec des formules et très vite.

T'es sous Excel 2016, tu peux toute fois utiliser Power Query, Voir PJ.

Cordialement

R@chid · 5 Janvier 2021

Bonjour et Bienvenue sur XLD,
Si tu peux joindre un fichier exemple sans données confidentielles se serait sympa.

Dpi- · 5 Janvier 2021

Voici mon fichier en pièce jointe, recomposé de toutes pièces pour éviter les données confidentielles.
Merci beaucoup !

R@chid · 5 Janvier 2021

Bonjour,
voir PJ

Cordialement

Dpi- · 5 Janvier 2021

Bonsoir R@chid,

C'est exactement ce dont j'ai besoin, un grand merci !
J'ai simplement dû aménager la première ligne qui m'était refusée pour cause de référence circulaire.

Malheureusement, j'ai presque 1400 lignes de données dans le vrai fichier, et les calculs de tes formules sont très très longs (surtout que le tableau de stat est lié à un graphique).
Comme il y a en plus de nombreuses macros et quelques requêtes sql, je crains que ce soit ingérable.

D'après toi y aurait-il plus rapide (sans vba) ?

Merci encore,

Dpi

R@chid · 5 Janvier 2021

Re,
Oui c'est vrai avec 1400 lignes c'est trop pour cette formules matricielle.
Sinon on pourrait le faire avec une colonne intermédiaire, et si tu étais sous Excel pour Microsoft 365, on peut faire facilement avec des formules et très vite.

T'es sous Excel 2016, tu peux toute fois utiliser Power Query, Voir PJ.

Cordialement

Dpi- · 6 Janvier 2021

Woaw, c'est vraiment bien !

Les pivots je connais, par contre je n'ai jamais utilisé Power Query, ça m'a l'air magique.
Je vais essayer de reproduire ce que tu as fait.

Pourrais-tu m'orienter pour le départ ?
Je viens d'activer les compléments pour avoir Power Pivot, j'ai maintenant un onglet supplémentaire.
Peux-tu m'expliquer (assez brièvement) comment tu fais pour lancer Power Query, créer la requête (sachant que les stat sont sur une autre feuille que les données) et définir la première étape ?

Juste en quelques mots, je ferais d'autres recherches sur internet ensuite

Merci beaucoup pour ton aide précieuse !

Dpi

R@chid · 6 Janvier 2021

Bonjour,
je t'ai laissé deux captures d'écran sur le fichier
la première : te permet d'afficher la requête
la deuxième : clique droit sur la requête {"Résultat" / Modifier} pour retrouver toutes les étapes sur Power Query.

Sinon, pour commencer, mets le curseur dans la plage source, puis {Onglet : Données / A partir d'un tableau ou d'une plage}, et après tu n'as qu'à suivre les étapes comme ce que j'ai fait.
Une fois que tu as terminé, dans Power Query, il y a Fermer et Charger, et tu peux mettre le résultat là où tu veux.

Dpi- à dit:
Je viens d'activer les compléments pour avoir Power Pivot

Tu n'as pas besoin de Power Pivot, où d'activer un Addin, puisqu'à partir de la version 2016, Power Query est inclus.
Onglet "Données", la partie gauche contient les fonctions de Power Query.

Cordialement

Dpi- · 7 Janvier 2021

Désolé pour la réponse tardive, j'étais bien occupé.
Merci beaucoup pour ces informations R@chid.

A priori, mon premier problème vient de la source (qui n'est pas un tableau mais qui doit rester une plage).

Mon bouton s'appelle "A partir de la table" et non pas "A partir de Tableau ou d'une Plage" comme sur ton écran.
Quand je clique dessus après avoir sélectionné une cellule de ma plage, j'obtiens ce message :
Expression.Error. We couldn't find an Excel table named '[nomdemafeuille]!_FilterDatabase'.

Le problème semble connu sur les forums, mais je n'ai pas trouvé d'explication convaincante pour l'instant.
Si tu as une idée je suis preneur, sinon je peux classer la discussion en Résolu, puisque mon besoin initial est comblé.

Encore un grand merci !

Dpi

R@chid · 7 Janvier 2021

Bonjour,

Dpi- à dit:
A priori, mon premier problème vient de la source (qui n'est pas un tableau mais qui doit rester une plage).

Une fois il est importé sur Power Query il doit rester un tableau, il ne faut pas le convertir en plage.

Dpi- à dit:
Mon bouton s'appelle "A partir de la table" et non pas "A partir de Tableau ou d'une Plage" comme sur ton écran.

Oui t'es sous Excel 2016 et moi je suis sous Excel pour Microsoft 365.

Dpi- à dit:
Quand je clique dessus après avoir sélectionné une cellule de ma plage, j'obtiens ce message :
Expression.Error. We couldn't find an Excel table named '[nomdemafeuille]!_FilterDatabase'.

Je ne sais pas, d'où vient ce message.

Dpi- · 7 Janvier 2021

J'ai déjà regardé un peu cette après-midi, en convertissant ma plage en table je n'ai plus de message d'erreur.
Je vais adapter le fichier, ce qui risque d'être un peu fastidieux.
En tout cas un grand merci pour ton aide et pour la solution Power Query !

XL 2016 Occurence la plus fréquente avec conditions

Dpi-

XLDnaute Nouveau

R@chid

R@chid

XLDnaute Barbatruc

Dpi-

XLDnaute Nouveau

Pièces jointes

R@chid

XLDnaute Barbatruc

Pièces jointes

Dpi-

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

Pièces jointes

Dpi-

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

Dpi-

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

Dpi-

XLDnaute Nouveau

Discussions similaires