[Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

excelnoob

XLDnaute Nouveau
Bonjour à tous,

Je sollicite votre aide pour traiter un sujet sur lequel je bloque.

Explication du sujet (avec un fichier d'exemple):J'utilise 2 tableaux de plusieurs millers de lignes et d'une centaines de colonnes
Ces 2 tableaux sont dans le même fichier mais sont sur 2 feuilles différentes

Dans le Tableau 1, Chaque ligne est unique grace aux croisements de 4 caractéristiques"mis en jaune dans le tableau "(colonne 1 à 4; libellés "Nom";"Prénom";"2ème prénom";vêtement)
Du coup, pour l'exmple 2 bouba , mais en prenant en compte, les autres colonnes, on a bien 2 lignes différentes

Dans le Tableau 2,

On retrouve les colonnes de crirères du tableau 1
Mais une ligne du tableau 1 peut se retrouver plusieurs fois dans le tableau 2
Les autres colonnes, c'est un rangement par dates (01/04/2017,…)

Mon objectif:Je souhaite pour chaque ligne du tableau 1, faire la "moyenne/max/min" selon une des colonnes datées du tableau 2

exemple : pour Dédé X1 Y1 Z1 (en jaune)

je souhaite connaître la valeur max pour le 01/05/2017 (correspondant à une des colonnes du tableau 2, colonne 14) qui serait ici au final la valeur 25543

A savoir:J'ai essayé d'utiliser les fonctions INDEX/EQUIV/MAX,.. Mais je n'arrive à rien.
Si ma demande est réalisable (par formules préférable ou Vba si pas d'autres choix), l'idéale ce serait en plus de pouvoir mettre par exemple en L1C2: la date de la colonne (du tableau 2) analysée et que le tableau 1 se remplisse

J'espère que vous serez en mesure de m'aider.
 

Pièces jointes

  • ex1.xlsx
    10.7 KB · Affichages: 37
  • ex1.xlsx
    10.7 KB · Affichages: 38
  • ex1.xlsx
    10.7 KB · Affichages: 41
Dernière édition:

vgendron

XLDnaute Barbatruc
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Bonjour

avec ces deux formules pour moyenne et Max. (pour le Min. je cherche encore ;-) )

en E6:
Code:
=MOYENNE.SI.ENS(DECALER($M$5;1;EQUIV($B$1;$M$5:$Q$5;0)-1;NBVAL($J:$J)-1);$I$6:$I$18;A6;$J$6:$J$18;B6;$K$6:$K$18;C6;$L$6:$L$18;D6)

en F6
Code:
= SOMMEPROD(MAX((A6=$I$6:$I$18)*(B6=$J$6:$J$18)*(C6=$K$6:$K$18)*(D6=$L$6:$L$18)*(DECALER($M$5;1;EQUIV($B$1;$M$5:$Q$5;0)-1;NBVAL($J:$J)-1))))
 
Dernière édition:

excelnoob

XLDnaute Nouveau
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Bonjour Vgendron,

Merci déjà pour ce premier retour, rapide et éfficace (impressionné)

Je n'étais pas prêt de le faire seul ^^
j'aurais aussi besoin des formules pour "ECARTYPE.STANDARD" ; MEDIANE si tu as ça en stock^^

Je te remercie encore à l'avance^^
 

excelnoob

XLDnaute Nouveau
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Pour trouver le Min, en fouillant un peu partout il existerait peut-être cette formule , mais je n'arrive pas à l'adapter :
"=SOMMEPROD(PETITE.VALEUR(('DONNEES BASE'!$A$2:$A$33=$A$1)*('DONNEES BASE'!$B$2:$B$33=$E$2)*('DONNEES BASE'!$J$2:$J$33);LIGNES($B$2:$B$33)-SOMMEPROD(--('DONNEES BASE'!$A$2:$A$33=$A$1)*('DONNEES BASE'!$B$2:$B$33=$E$2)*('DONNEES BASE'!$J$2:$J$33<>""))+1))"

POur "ECARTYPE.STANDARD" ; MEDIANE , la c'est la panne...
 

CISCO

XLDnaute Barbatruc
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Bonsoir

Cf. d'autres possibilités en pièce jointe, toujours avec des formules matricielles. Le principe : On fait un test, et on met le contenu de la colonne désirée si le test donne VRAI, et "" lorsqu'il donne FAUX. Avec ça, on peut aussi bien trouver la moyenne, que le max que le min. A vérifier et à appliquer aux autres paramètres demandés (Ecart-type....).

@ plus

P.S. : Ca fonctionne aussi sans mettre "" quand le test donne FAUX.
 

Pièces jointes

  • ex1.xlsx
    11.5 KB · Affichages: 37
  • ex1.xlsx
    11.5 KB · Affichages: 40
  • ex1.xlsx
    11.5 KB · Affichages: 40
Dernière édition:

excelnoob

XLDnaute Nouveau
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Effectivement ça fonctionne.

En revanche sij'ai une ligne unique au niveau des critères pour l'écartype.standard, j'obtiens en résultat Div/0.
Comment, je peux palier à ça ?
 

CISCO

XLDnaute Barbatruc
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Bonjour

C'est normal, pour avoir un écart type, il faut un écart, donc plusieurs valeurs !

Pour supprimer ce message d'erreur, tu peux faire avec SIERREUR(la formule;"")

@ plus
 

excelnoob

XLDnaute Nouveau
Re : [Excel 2013] - calcul de valeurs d'un tableau par choix multi-critère

Re-bonjour à tous,

en utilisant les conseils, je viens de m'apercevoir d"un petit problème.


Si je mets des critères dans le 1er tableau qui n'existent pas dans le 2ème tableau, avec les fonctions si(maxiet si(mini, j'obtiens systematiquement par défaut la valeur 0. Même si je rentre une formule du genre sierreur(max(si....; "sinon toto"), le résultat est le même (et quelque soit le format, standard , nombre ou autre)

Comment puis-je remédier à ce problème? (le problème étant le même avec la fonction min)

PS: a savoir que dans la zone de recherche, je met du genre 3000 lignes au lieu d'une zone définie en nombre de ligne (car susceptible d'en rajouter)
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 925
Membres
101 841
dernier inscrit
ferid87