Médiane à critères multiples

Bonjour amis Exceliens,

Je me retrouve une fois de plus face à une situation quelque peu délicate.

J'aimerai faire une médiane avec plusieurs critères? J'ai trouvé une formule : =MEDIANE(SI((Feuil1!D2:D200="83A")*(Feuil1!G2:G200="E0");Feuil1!K2:K200)) que je dois valider en matricielle et ça marche ! Seulement je suis limité à 200-210 lignes, alors que dans mon fichier j'aimerai monter à 5000... J'ai essayer de faire : =MEDIANE(SI((Feuil1!D2:D200="83A")*(Feuil1!G2:G200="E0");Feuil1!K2:K200)) + MEDIANE(SI((Feuil1!D2:D200="83A")*(Feuil1!G2:G200="E0");Feuil1!K2:K200)), mais cela ne marche malheureusement pas...

J'ai trouvé une macro sur le net qui permettrait peut être de ne plus utiliser de formule, mais je comprends pas tout, je suis en train de la décortiquer !

La question est donc : Peut-on passer outre la limite des 200 lignes sur ma formule ou suis-je obligé de passer par une macro?

Merci d'avance pour vos retours

Pierre

Bonjour,

étrange cette limite de 200. Quel est le message ?

Sinon un fichier de données pour faire des tests serait le bienvenu.

eric

Edit : ah oui, mediane() est limité à 255

Quand à la macro quelle est ta difficulté ?

Une fonction personnalisée doit être mise dans module Standard pour pouvoir être appelée depuis une feuille.

Ah mince, je pensais que je pourrai contourner cette limite !

Pour la macro la seule difficulté est de comprendre celle que j'ai trouvé sur le net qui répondrai au problème ! Rien de bien compliqué je pense, j'ai juste pas mal de trucs en parallèle du coup pas beaucoup de temps pour la déchiffrer mais je vais trouver je pense !

Ma question était juste de savoir si le passage par une macro était obligatoire ou pas quoi, et à priori oui !

Merci bien

Pierre

255 c'est le nombre d'arguments passés, pas le nombre de nombres.

Peut-être qu'en triant tu peux limiter le nombre de plages (?)

Tu en es sur?

J'ai remplacer les 200 par des 5000 dans la formule mais ça ne marche pas alors que pourtant il n'y a qu'une vingtaine de valeur à prendre en compte... C'est bien là le problème et mon incompréhension !

Pour ce qui est de la plage, j'ai dans mon CdC de la garder à 5000 pour avoir une marge (bien qu'en pratique actuellement on est qu'à 245 lignes remplies).

Je sais pas si je suis clair... (pour donner un fichier test c'est un petit peu compliqué car je travaille dans l'aéro et que les données sont confidentielles... Je pourrai toujours me débrouiller pour faire un exemple avec des fruits et légumes ahah) !

Merci de ton aide en tout cas !

Pierre

Je ne suis de rien sans avoir vu le fichier.

Mais ne perd pas de temps, utilise la macro

Alors j'ai sorti ce petit exemple pour comprendre la macro...

En fait il s'agit de la programmation d'une fonction et je ne peux pas regarder ce que ça donne au pas à pas pour mieux comprendre.

Peux-tu me dire ce que tu penses de ce bout de code? S'il y a des modifications à apporter et surtout pourquoi est-ce que ce n'est pas la bonne valeur qui sort dans l'exemple?

Merci d'avance

Pierre

Je me suis trompé de ficihier dans le message précédent ^^'

J'ai tenté par formule mais ça devient rapidement imbuvable...

Là où tu as trouvé cette macro il y a des explications ? Peut-être l'utilises-tu mal ?

Sinon je l'aurais plutôt faite comme ça, ce qui me parait plus simple :

Function mediane_si(plM As Range, pl1 As Range, crit1 As String, pl2 As Range, crit2 As String)
    Dim dataM, data1, data2
    Dim i As Long, nb As Long
    Dim ObjSortedList As Object
    Set ObjSortedList = CreateObject("System.Collections.ArrayList")
    dataM = plM.Value: data1 = pl1.Value: data2 = pl2.Value
    For i = 1 To UBound(dataM)
        If data1(i, 1) = crit1 Then
            If data2(i, 1) = crit2 Then
                ObjSortedList.Add dataM(i, 1)
            End If
        End If
    Next i
    ObjSortedList.Sort
    nb = ObjSortedList.Count
    If nb Mod 2 Then
        mediane_si = ObjSortedList(nb \ 2)
    Else
        mediane_si = (ObjSortedList(nb \ 2 - 1) + ObjSortedList(nb \ 2)) / 2
    End If
End Function

eric

EDIT : voir complément post https://forum.excel-pratique.com/excel/mediane-a-criteres-multiples-t95848.html#p555250

PS : je te conseille de mettre en critère 1 celui qui a le plus d'items différents (ici sûrement statut vu que sexe n'en aura toujours que 2).

Comme j'ai séparé les 2 tests exprès ça sera plus rapide.

J'ai changé le fichier juste pour ça, j'avais inversé dans la formule. La fonction est inchangée, pas la peine de re-télécharger.

Merci beaucoup pour ton retour, en effet, par formule je me suis cassé la tête mais vraiment j'ai rien trouvé de viable quoi.

Pour la macro, je l'ai trouvé sur un autre forum sans vraiment plus d'explications...

Je vais essayer tout de suite ce que tu m'as envoyé !

Pour ce qui est des critères, j'avais déjà en critère 1 celui qui a le plus de possibilités !

Je te tiens au courant

EDIT : J'ai essayé cette macro appliquée à mon cas et dans la case j'ai un #VALEUR! qui apparaît alors que je pense avoir bien rentré la formule... Est-ce que si les plages sont sur une autre feuille ça peut mettre le bazard ou pas spécialement?

Pierre

Bonjour,

Si, tu peux avoir les données dans une autre feuille.

Tu obtiens #VALEUR! si les plages ne sont pas de même taille ou si une donnée n'est pas numérique.

Par contre tu viens de me faire penser que je prend en compte les vides en tant que 0.

Ajouter un 3e test :

'...
                If dataM(i, 1) <> "" Then
                    ObjSortedList.Add dataM(i, 1)
                End If
'...

Et si tu veux faire exactement comme la fonction d'excel mediane() qui ne prend pas en compte les chaines non plus, compléter ce test :

If IsNumeric(dataM(i, 1)) And dataM(i, 1) <> "" Then

Personnellement je pense que c'est mieux d'être averti si tu as "800" au lieu de 800. C'est tellement courant avec des données importées.

Si ton soucis n'est toujours pas résolu il me faudrait le fichier anonymisé.

eric

Merci, j'ai rajouté le 3ème test.

Je n'ai pas compris le coup des données non numériques... Dans notre exemple, les critères sont HOMMES et CADRES, ce ne sont pas des données numériques si?

Je vais essayer de faire un fichier simplifier afin de te le transmettre !

EDIT : le document est en PJ avec seulement 22 lignes pour l'exemple et des données retirées. Je dois afficher la médiane en "feuil2". Pour la calculer j'ai besoin des données de la "feuil1", colonne D et G. La médiane est déterminée grâce aux valeurs de la colonne K en "feuil1". La médiane pour l'exemple doit être calculée pour du 83A et du E0 (respectivement les colonnes D et G). Les modules qui vont t'intéréssés seront le 3 (première macro envoyée) et le 4 (qui contient celle que tu m'as transmise).

Pour le moment je fais des test uniquement pour la profondeur de la baignoire en E0 sur la "feuil2".

J'espère avoir été à peu près clair ^^"

Merci beaucoup pour ton aide !!

Pierre

Je ne te parlais que de la plage où on attend des nombres pour faire la médiane.

Je n'ai pas compris le coup des données non numériques...

met une cellule en format texte, C6 par exemple.

Tu y lis toujours 800 mais aligné à gauche comme les textes. Mediane() d'excel l'exclura du calcul et te renverra donc un résultat faux pour toi si tu es persuadé qu'il lit 800 et non "800".

Et pour peu que tu aies centré ou aligné ta colonne tu ne verras même pas l'anomalie des nombres qui n'en sont pas.

Ah d'accord j'ai compris !

(mais je pense pas que le problème vienne de là, je viens de vérifier et ce sont bien des données numériques)

EDIT : je viens de remarquer que pour le fichier exemple il n'y avait pas de E0, on peut faire la recherche pour du F0 du coup, ça ne change pas la logique

Je n'ai pas vu ton fichier tout à l'heure.

Par contre tu ne t'es pas trompé ? Je n'y vois ni ma fonction, ni une formule l'utilisant.

Si tu pouvais réduire à l'essentiel en précisant où tu as mis la formule. Pas besoin de feuilles inutiles pour l'exemple ni d'autres macros pouvant perturber.

Merci

eriiic a écrit :

Je n'ai pas vu ton fichier tout à l'heure.

Par contre tu ne t'es pas trompé ? Je n'y vois ni ma fonction, ni une formule l'utilisant.

Si tu pouvais réduire à l'essentiel en précisant où tu as mis la formule. Pas besoin de feuilles inutiles pour l'exemple ni d'autres macros pouvant perturber.

Merci

Mon fichier est pourtant bien en PJ dans un de mes messages plutôt long où je décris le problème et le mode opératoire ! (je viens de le télécharger, il y a tout quand je l'ouvre ^^" )

Vu que je n'avais pas trouvé tu aurais pu faire l'effort de me dire en quelle cellule est la formule...

Bref, j'ai fini par trouver...

C'est quoi ? une blague ?

déjà avec :

=mediane_si(Feuil1!K2:K246;"Feuil1!Feuil1!D2:D246";"83A";Feuil1!G2:G246;"E0")

ça ne risquait pas de fonctionner vu le paramètre passé "Feuil1!Feuil1!D2:D246" ce qui fait quand même 2 erreurs sur un seul paramètre.

On va mettre ça sur le compte de tes manip mais tu pourrais contrôler plus sérieusement ce que tu envoies.

Mais d'autre part le 2nd critère est "E0" et il n'y en a pas un seul. Que voudrais-tu qu'elle te retourne d'autre que #VALEUR! ?

Si je corrige tout et que je met "F0" j'obtiens 261, je ne vois rien de spécial à chercher.

eric

En effet pour le coup du double feuil1! j'avais pas remarqué, quand t'as la tête dans quelque chose tu remarques pas forcément certaines erreurs bête, je serai pas là à demander de l'aide si j'en faisais pas...

Pour le E0 comme je l'ai précisé effectivement il n'y en a pas dans le fichier exemple et je l'ai précisé.

J'ai modifié la formule et ça marche du coup.

Merci bien.

Une dernière question penses-tu qu'il est possible de faire un tri pour 5000 lignes? J'ai modifier la formule comme tu as montré, ça a marché et ensuite j'ai augmenter les plages et là de nouveau #VALEUR!... Je ne comprends pas pourquoi puisque c'est un tri donc même les cases sélectionnées non remplies ne devraient pas être prises en compte si?

Je comprendrai que ça te soule de continuer de m'aider, pas de soucis, tu as déjà fait beaucoup, encore merci

Pierre

Bonjour,

ensuite j'ai augmenter les plages et là de nouveau #VALEUR!.

Que fais-tu comme manip exactement dans le détail ?

eric

J'ai juste remplacer les 246 par des 5000.

Le problème vient peut-être du fait qu'à partir d'un certain nombre de ligne, j'ai du #N/A (comme j'ai pris de la marge à un moment je n'ai plus de données donc c'est normal que les #N/A apparaissent). La formule fonctionne parfaitement tant que je suis a 246 et dès que je passe à 247 avec des #N/A dans la colonne, ça ne marche plus.

J'ai essayé plusieurs configurations, peut importe les plages pour la colonne D et G.

Ex : =mediane_si(Feuil1!K2:K246;Feuil1!D2:D5000;"83A";Feuil1!G2:G5000;"E0") ça marche

=mediane_si(Feuil1!K2:K247;Feuil1!D2:D5000;"83A";Feuil1!G2:G5000;"E0") ne marche plus

je pense donc que le problème vient du #N/A sur le 1er critère de tri, mais je ne comprend pas pourquoi en fait... Comme on fait un tri les #N/A ne devraient pas être pris en compte je me dis !

Sinon il faudrait que je rajoute une condition pour le 1er critère, genre une petite macro qui dit que l plage du 1er critère doit être égale aux nombres de ligne ! Mais je trouve ça bizarre que ça ne marche pas en fait

Ah ben oui, si tu as un code erreur il remonte dans la fonction, c'est normal.

On pourrait ajouter un test pour les exclure mais ce n'est pas la meilleure méthode.

Déjà tu peux exclure les #N/A avec :

=sierreur(ta_formule;"")

Mais si tous tes #N/A sont regroupés en fin de tableau ça serait plus efficace de te créer des noms de plage dynamiques avec juste le nombre de lignes nécessaire. Inutile de regarder 5000 lignes si seulement 10 sont concernées.

http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=64 Nommer une Plage Dynamique

Une 3e voie serait (sous toute réserve car apparemment tu n'as pas fourni le fichier réel...) :

Tu crées ta ligne de titre, 1 ligne de données avec tes formules à droite, tu sélectionnes la plage et tu fais 'Insérer / Tableau'

Quand tu colleras tes données dans les colonnes de gauche tes formules s'étendront en conséquence.

Quand tu crées tes formules mediane_si() tu vas sélectionner les plages de données à la souris.

Excel t'inscrira les plage en références structurées et tu obtiendras qq chose comme :

=mediane_si(Tableau1[Salaire];Tableau1[Statut];$I3;Tableau1[Sexe];J$2)

qui d'adaptera automatiquement au nombre de lignes

Si tu dois supprimer des lignes passe par le clic-droit 'supprimer / lignes de tableau'

Ou bien tu sélectionnes ta colonne et 'Sélectionner et rechercher / sélectionner les cellules...' avec Formules et Erreurs, tu valides et Suppr

eric