Renvoyer du texte avec SOMMEPROD ?

[choup67]

Bonjour à tous,

Je suis bloquée avec SOMMEPROD car je cherche à renvoyer une valeur texte en fonction de plusieurs critères.

Je ne peux pas poster de fichier depuis l'ordinateur où je suis donc je vais essayer d'expliquer.

Dans l'onglet Stockage, j'ai un ensemble d'emplacement de stockage identifiés par un nom de rack et un nom d'emplacement. Exemple : Zec_A 1F1

En face de chaque emplacement, je voudrais aller récupérer dans d'autres onglets la palette qui y est stockée. Pour cela, la formule doit aller dans 4 autres onglets qui pourront changer de nom.

J'ai d'abord essayé avec ça :

=SI(INDEX(INDIRECT("'"&Rame1&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame1&"'!EplRack");0);6)<>" ";INDEX(INDIRECT("'"&Rame1&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame1&"'!EplRack");0);6);SI(INDEX(INDIRECT("'"&Rame2&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame2&"'!EplRack");0);6)<>" ";INDEX(INDIRECT("'"&Rame2&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame2&"'!EplRack");0);6);SI(INDEX(INDIRECT("'"&Rame3&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame3&"'!EplRack");0);6)<>" ";INDEX(INDIRECT("'"&Rame3&"'!BasePlanning");EQUIV(C2;INDIRECT("'"&Rame3&"'!EplRack");0);6);"")))

Mais le problème c'est que je ne peux prends qu'une condition en considération. Du coup, vu que j'ai deux rack (Zec_A et Zec_B), pour l'emplacement 1F1, le nom de la palette se mettra dans les deux.

J'ai maintenant tenté ça :

=SOMMEPROD((INDIRECT("'"&Rame1&"'!EplZec")=B2)*(INDIRECT("'"&Rame1&"'!EplRack")=C2)*(INDIRECT("'"&Rame1&"'!NomKit")))

Mais ça me renvoi évidemment une erreur puisque je cherche à afficher du texte.

Une idée?

 

[Tibo]

Re : Renvoyer du texte avec SOMMEPROD ?

Bonjour,

Sans voir ton fichier, ça va pas être facile de t'aider.

Donc, dès que tu peux, joins nous un extrait de ton fichier.

A te (re)lire

@+

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Salut,

Oui je me doute que c'est pas évident. Je viens de réessayer, j'ai réussi à joindre le fichier.

Donc dans l'onglet stockage, pour chaque emplacement, je dois aller vérifier dans chaque onglet si il y a quelque chose dedans ou pas.

Les noms des onglets sont repris sur la fiche, c'est le nom des rames. Il peut y avoir 4 rames au maximum en parallèle.

J'aimerai trouvé une solution à base de formules pour éviter les macros. SOMMEPROD pourrait faire ce que je veux, le problème est qu'il ne renvoit pas de données textuelles.

Merci à vous.

 

[Dugenou]

Re : Renvoyer du texte avec SOMMEPROD ?

Salut,

Difficile de voir ce qui se passe puisque les fichiers liés ne sont pas joints.

Pour renvoyer une valeur de texte avec sommeprod : essaye de renvoyer un n° de ligne en ajoutant *ligne(taplage) dans le sommeprod et ensuite tu fais une formule index avec le n° de ligne renvoyé. Soit = index(plage;sommeprod(...))

Cordialement

 

[BOISGONTIER]

Re : Renvoyer du texte avec SOMMEPROD ?

Bonjour,

Sommeprod() ne renvoie pas du texte.

Exemple recherche 2 critères
Formules Matricielles

Exemple multiFeuilles
Formules Matricielles 3D

JB

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Le fichier lié ne sert à rien, j'ai viré l'onglet concerné.

Dans les onglet avec des numéros, je renseigne manuellement les emplacements où je mets des palettes. Je voudrais que l'onglet stockage récupère pour chaque emplacement la palette qui s'y trouve.

Mais pour ça, il faut qu'il vérifie sur chaque onglet si l'emplacement en question contient une palette ou non.

Je vais essayer de comprendre ton conseil Dugenou.

Merci BOISGONTIER, car j'aurai besoin de créer des liens hypertexte après sur un autre tableau. Seulement ton cours est assez complexe à comprendre, j'ai un peu du mal.

 

[Dugenou]

Re : Renvoyer du texte avec SOMMEPROD ?

Re,

Le somme rpod ne marche pas car tes plages n'ont pas la même longueur.

la formule suivante en D2 renvoie bien un résultat
={INDEX(INDIRECT("'"&Rame1&"'!NomKit");SOMMEPROD((INDIRECT("'"&Rame1&"'!EplZec")=B2)*(INDIRECT("'"&Rame1&"'!EplRack")=C2)*LIGNE(INDIRECT("'"&Rame1&"'!EplZec"))))}

en matriciel : à valider par ctrl+maj+entrée (ce qui fait apparaître les { rt })
avec
Nomkit ='372'!$F$1:$F$991
EplZec ='372'!$N$6:$N$72
EplRack ='372'!$O$6:$O$72

Reste à compléter avec les 3 autres feuilles !

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Merci Dugenou mais ça ne fonctionne pas correctement. Si on regarde l'onglet 372, seul l'emplacement 1F1 et 1E1 contiennent des palettes. Pourtant, la formule tiré vers le bas, renvoi des valeurs pour les emplacements 1E2, 1E3 et 1D1 alors qu'il n'y a pas de palette dedans.

 

[hoerwind]

Re : Renvoyer du texte avec SOMMEPROD ?

Bonjour, salut les autres,

Il faudrait apporter quelques petites modifications à ton fichier :

Mon commentaire concerne la formule en Stockage!D2

La fonction SOMMEPROD nécessite d'avoir des plages de taille identique.
Ce n'est pas le cas pour EplZec, EplRack et NomKit.
Il serait aussi à conseiller de nommer ces plages de façon dynamique par la fonction DECALER.

Sur base de la proposition de notre ami Dugenou la formule suivante :

=SOMMEPROD((EplZec=B2)*(EplRack=C2)*LIGNE('372'!6:50))

après avoir ajusté la taille des deux plages nommées à 6:50
renvoie 6 (le numéro de la ligne qui répond aux deux critères)
les plages nommées ne se mettent pas entre "" et les fonctions INDIRECT sont inutiles.

Pour obtenir la résultat désiré :

=INDIRECT("'372'!K"&SOMMEPROD((EplZec=B2)*(EplRack=C2)*LIGNE('372'!6:50)))

qui renvoie KIT INCOMPLET NON CRITIQUE

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Salut hoerwind,

Qu'entends tu par "nommer ces plages de façon dynamique"?

Dans ta formule tu indique le nom d'onglet, seulement il n'est pas fixe, il va changer, d'où la fonction indirect.

Je vais toujours avoir 4 onglets à aller vérifier mais leurs noms changera dans le temps.

 

[Dugenou]

Re : Renvoyer du texte avec SOMMEPROD ?

Encore moi,

La formule est déjà complexe pour un onglet, alors pour 3 ou 4 avec si(esterreur... ça va être l'enfer !

Il serait plus simple d'ajouter une colonne dans chaque feuille "rame" avec la concaténation zec + epl : on pourrait alors faire un recherchev

voir essai en pj

ps la deuxieme rame protait le n°386 dans la feuille mais onglet 388

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Merci Dugenou, effectivement c'est plus simple comme ça. Du coup, le nommage des cellules n'est plus important ici.

Par contre, je voudrais que le nom des rames se mette automatiquement dans les champs "rame1", "rame2" etc.

J'ai trouvé la formule de BOISGONTIER sur ce site mais elle me renvoi toujours le même résultat "Stockage" même en la tirant vers le bas.

 

[Dugenou]

Re : Renvoyer du texte avec SOMMEPROD ?

voici le classeur adapté avec la formule macro xl4 de boisgontier

"créer un nom de champ" = insertion /nom /définir entrer le nom du champ et taper la formule dans la partie "fait référence à"

ensuite la formule matricielle comme indiqué (sur des cellules horizontales (sur vertical ça marche pas et je ne sais pas pourquoi)

 

[choup67]

Re : Renvoyer du texte avec SOMMEPROD ?

Ah j'avais bon en fait mais j'essayai de le faire marcher en colonne.

Avec RechercheV j'ai récupérer le nom du kit et celui de l'OF en déplaçant la colonne à coté mais par contre, pour récupérer le numéro de la rame concernant, j'ai mis :

=SI(ESTERREUR(RECHERCHEV(B2&" "&C2;INDIRECT("'"&Rame1&"'!$E$6:$G$100");2;FAUX));SI(ESTERREUR(RECHERCHEV(B2&" "&C2;INDIRECT("'"&Rame2&"'!$E$6:$G$100");2;FAUX));"";INDIRECT("'"&Rame2&"'!$D$2"));INDIRECT("'"&Rame1&"'!$D$2"))

En F2 mais ça ne fonctionne pas. Sais tu pourquoi?

 

[BOISGONTIER]

Re : Renvoyer du texte avec SOMMEPROD ?

pour obtenir le nom des onglets en colonne

-Créer un nom de champ NomFeuilles =LIRE.CLASSEUR(1)


-Sélectionner 4 cellules
=SI(MAINTENANT()>0;TRANSPOSE(STXT(NomFeuilles;TROUVE("]";NomFeuilles)+1;99)))
-valider avec maj+ctrl+entrée


JB