Microsoft 365 Recherche d'un texte fonction de plusieurs critères

[Excel6689]

Bonjour,

j'ai deux tableaux qui concerne des formations - je ne vous ai mis que ce qui était important :

1. un avec ANom, ADate, ALibelle, AEtat1, AEtat2
2. l'autre avec les mêmes colonnes BNom, BDate, BLibelle, BEtat1, BEtat2

Dans la colonne AEtat1 j'avais besoin de savoir si "OUI" ou "NON" la formation était maintenue, je m'en suis sorti en écrivant :​

=SI(SOMMEPROD((G:G=$A2)*(I:I=$C2)*(J:J<>"Annulé"))=1;"OUI";"NON")

Cela fonctionne comme je veux... il y a peut-être plus léger ​

​

Par contre et je sèche totalement pour la colonne AEtat2 qui doit reprendre le libellé de la colonne BEtat2​

​

Je tiens à signaler que je ne peux savoir la taille des tableaux pas comme dans l'exemple fourni.​

​

Par avance merci

 

[chris]

Bonjour

SOMMEPROD sur des colonnes complètes est un non sens

Travaille avec des tableaux structurés

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

A noter que SOMMEPROD ne fonctionnera que si pas de doublons sur les éléments recherchés

 

[Dudu2]

Bonjour,
J'ai mis ça en E2: =SI(ET(G:G=$A2;I:I=$C2);K:K;"")
Je l'ai d'abord en matricielle ça ne marchait pas, puis directe et ça marche, je ne comprends pas pourquoi
Edit: en fait ça ne marche plus si on déplace les lignes !
Edit: suite à l'analyse de la formule magique de Chris

(sur le SOMMEPROD, voir ci-dessous) si les lignes doivent ne pas correspondre la formule serait plutôt:
En E2: =INDEX(K:K;SOMMEPROD((G:G=$A2)*(I:I=$C2);LIGNE(G:G)))
Et celle-là je la comprends

SOMMEPROD sur des colonnes complètes est un non sens

C'est vrai des tableaux structurés c'est bien plus mieux !
Dans le cas de formules faisant référence à des colonnes ou lignes entière j'espère qu'Excel ne scanne pas inutilement les millions de cellules concernées et qu'il exploite ses limites sur le UsedRange qu'il connait, sinon en effet c'est la cata.

 

[Dudu2]

@Chris,
Tu pourrais STP expliquer le SOMMEPROD de ta formule, je ne comprends pas la présence du ";LIGNE(TableauB[BEtat2])" dans la fonction:
SOMMEPROD((TableauB[BNom]=$A5)*(TableauB[BLibelle]=$C5)*(TableauB[BEtat]<>"Annulé");LIGNE(TableauB[BEtat2]))

Edit:
OK: c'est un truc magique du SOMMEPROD que je ne connaissais pas.
En ajoutant ce ";LIGNE(X:X)", le SOMMEPROD retourne le n° de ligne concerné !
Jamais je n'ai vu ça documenté !

 

[chris]

RE

Mais dans le cas de formules faisant référence à des colonnes ou lignes entière je ne crois pas qu'Excel scanne inutilement les millions de cellules concernées. Il exploite très certainement ses limites sur le UsedRange. Sinon les temps de calcul seraient rédhibitoires.

J'en doute, d’autant que SOMMEPROD était impossible à faire sur une colonne entière jusqu'à 2007 inclus

Il faudrait tester le timing

 

[chris]

RE

@Chris,
Tu pourrais STP expliquer le SOMMEPROD de ta formule, je ne comprends pas la présence du ";LIGNE(TableauB[BEtat2])" dans la fonction:
SOMMEPROD((TableauB[BNom]=$A5)*(TableauB[BLibelle]=$C5)*(TableauB[BEtat]<>"Annulé");LIGNE(TableauB[BEtat2]))

C'est pour récupérer le numéro de la ligne trouvée et alimenter INDEX

J'aurais pu prendre n'importe quelle colonne du tableau

 

[Hasco]

Bonjour,

Une solution avec la formule agregat qui remonte le n°ordre d'apparition dans le tableau ou 0 . Ce numéro est formaté par "OUI";;"NON" ce qui permet à la colonne BEtat2 de remonter la donnée voulue par un simple INDEX.

Formule RESULTAT : =SIERREUR(AGREGAT(15;6;(LIGNE(TableauB[BNom])-LIGNE(TableauB[#En-têtes]))/(TableauB[BNom]=[@ANom])/(TableauB[BLibelle]=[@ALibelle])/(TableauB[BEtat]<>"Annulé");1);0)

Formule BEtat2 : =SI([@RESULTAT]>0;INDEX(TableauB[BEtat2];[@RESULTAT]);"")

Une solution powerquery serait aussi possible, mais après étude plus avancée du cas.

cordialement

 

[chris]

Bonjour à tous

Une autre spécifique 365 pour la colonne AEtat

=FILTRE(TableauB[BEtat2];(TableauB[BNom]=A2)*(TableauB[BDate]=B2)*(TableauB[BLibelle]=C2)*(TableauB[BEtat]<>"Annulé");"")

Pas sûr que la colonne RESULTAT soit nécessaire... mais si on reste sur l'idée intiale

=SI(FILTRE(LIGNE(TableauB);(TableauB[BNom]=A2)*(TableauB[BDate]=B2)*(TableauB[BLibelle]=C2)*(TableauB[BEtat]<>"Annulé");"")<>"";"OUI";"NON")

Une solution avec la formule agregat qui remonte le n°ordre d'apparition dans le tableau ou 0 . Ce numéro est formaté par "OUI";;"NON" ce qui permet à la colonne BEtat2 de remonter la donnée voulue par un simple INDEX.

C'est effectivement le meilleur compromis si on garde la colonne

 

[Excel6689]

Bonsoir,
Merci à tous, malheureusement pour moi la formule m'a apporté des #Valeurs, il ne trouve pas la bonne ligne mais bon pas grave, ce sujet aura au moins eu le mérite d'intéresser du monde.

Désolé pour la structure des tableaux, mais dans les entreprises on récupère souvent le travail de quelqu'un sans avoir le droit le modifier.

 

[Dudu2]

Malheureusement pour moi la formule m'a apporté des #Valeurs

De quelle formule parles-tu ?
S'il y a des #Valeurs c'est que le fichier exemple n'est pas représentatif.
Éventuellement en E2:
=SI(NB.SI.ENS(G:G;$A2;I:I;$C2)=1;INDEX(K:K;SOMMEPROD((G:G=$A2)*(I:I=$C2);LIGNE(G:G)));"Aucune ou plusieurs correspondance(s)")