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

Excel6689 · 25 Juin 2020

Bonjour,

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

un avec ANom, ADate, ALibelle, AEtat1, AEtat2
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 :

VB:

=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 · 25 Juin 2020

Bonjour

SOMMEPROD sur des colonnes complètes est un non sens

Travaille avec des tableaux structurés

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 · 25 Juin 2020

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 · 25 Juin 2020

@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 · 25 Juin 2020

RE

Dudu2 à dit:
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 · 25 Juin 2020

RE

Dudu2 à dit:
@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 · 25 Juin 2020

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 · 25 Juin 2020

Bonjour à tous

Une autre spécifique 365 pour la colonne AEtat

VB:

=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

Code:

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

Roblochon à dit:
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 · 25 Juin 2020

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 · 26 Juin 2020

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)")

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

Excel6689

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

Tableaux structurés Excel

Pièces jointes

Dudu2

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

Pièces jointes

chris

XLDnaute Barbatruc

Excel6689

XLDnaute Nouveau

Dudu2

XLDnaute Barbatruc