Sommeprod plusieurs critères
Depuis hier, je cherche une solution au problème suivant:
Je souhaite récupérer un numéro de ligne en 1 seule formule (et sans colonnes intermédiaires) :
Le numéro de ligne correspond à un sommeprod((matrice1=critere1)*(matrice2=critere2)......
Cependant, bien que j'arrive à récupérer le numéro de ligne avec la fonction sommeprod, ce n'est pas très pratique si jamais j'ai 200 critères à rechercher par exemple.
Il doit bien y avoir une formule qui dois pouvoir le faire en selectionnant seulement ma matrice avec les valeurs en entier et celle des critères directement.
J'ai essayer plusieurs approche mais j'avouerai que là je m'approche de la solution mais j'ai pas encore trouvé.
Voici un fichier avec plus de détail montrant un peu la logique.
Merci
Une fonction personnalisée ?
Copie cela dans un module et écris dans une cellule:
=criteres(A2:J4;A9:J9)
Function criteres(rangedepart As Range, rangearrivee As Range) As Integer
Dim i As Long, j As Integer
compar = rangearrivee.Row
debut = rangedepart.Row
fincol = rangedepart.End(xlToRight).Column - 1
finlig = rangedepart.End(xlDown).Row
For i = debut To finlig
estvrai = True
For j = 1 To fincol
If Cells(i, j) <> Cells(compar, j) Then
estvrai = False
End If
Next j
If estvrai = True Then
criteres = Cells(i, fincol + 1)
Exit Function
End If
Next i
End FunctionMerci pour ta réponse.
Je cherche une solution par formule et non par macro (j'ai oublié de le préciser)
merci encore
Tu n'en trouveras pas. Bon courage.
Tu es sûr ?
Qu'est ce qui te fait dire ça ?
cdt
Ma connaissance d'excel.
Tu ne peux pas boucler via des formules en faisant des comparaisons avec les bornes en dynamique... Tu veux comparer un array 1,x avec une matrice y,x et ce ligne à ligne puis faire un offset... not possible.
Merci pour tes explications.
Ca répond bien à ma question.
Excel à donc ses limites
cdt
Excel n'a pas de limites à ce niveau là puisque tu peux coder des fonctions excel personnalisées toutes simples te permettant de le faire .
bonjour
pour un sommeprod a 200 criteres il te faut l'ordinateur de la NASA et des ventilateurs
cordialement
et du code pour splitter ta fonction parce que stocker autant de texte dans une fonction...
Bonjour,
=SINA(EQUIV(10;FREQUENCE(SI(A2:J4=A9:J9;LIGNE(A2:J4));LIGNE(A2:A4));0);"aucune ligne ne répond à tous les critères")A+
Bonjour @ tous,
Il faut télécharger et installer la macro complémentaire MoreFunc c'est gratuit sur la toile..
Profitant de MCONCAT() :
=EQUIV(MCONCAT(A9:J9);STXT(MCONCAT(A2:J4);(LIGNE(A2:J4)-2)*COLONNES(A2:J4)+1;COLONNES(A2:J4)))@ valider par Ctrl+Maj+Entree
@ + +
Salut Ooiste, R@chid; merci également pour vos réponses.
Celle de Ooiste me convient parfaitement, faut que je regarde comment elle fonctionne mais ça m'a l'air d'être bon.
=EQUIV(10;FREQUENCE(SI(A2:J4=A9:J9;LIGNE(A2:J4));LIGNE(A2:A4)))
Merci encore.
Re,
Oui, parfaite et concise..
@ + +
Fonction testée et approuvée
J'aurais pû chercher longtemps car je pense que c'était la seule solution de répondre à ma question (sans macro ou utilisation de morefun).
Merci à tous
EngueEngue
Ma connaissance d'excel.
Tu ne peux pas boucler via des formules en faisant des comparaisons avec les bornes en dynamique... Tu veux comparer un array 1,x avec une matrice y,x et ce ligne à ligne puis faire un offset... not possible.
Comme quoi ma question pourra être utile à tout le monde
Est ce que tes bornes sont dynamiques ?
Personnellement je préfère me faire une petite fonction simple et à deux paramètres que d'enchainer cinq fonctions et devoir spécifier tous ses paramètres.
En tout cas bien trouvé, je n'y aurais pas pensé.
Enfait, l'objectif cherché c'était de supprimer les conditions a saisir pour chaque colonne (sommeprod(colonne1=critere1. etc....)
La maintenant, tu n'as plus qu'à étirer la formule et le résultat est bon.
remplacer 10 par NBVAL(1:1)-1 par exemple;
et étirer les cadres.
C'est quand même très rapide comme solution, même si je le conçois, ta proposition en vba est également très juste.
Cordialement