report de valeurs

[JG]

Bonjour

Dans le but de récapituler des données contenues dans plusieurs feuilles
d'un même classeur, je cherche à écrire la formule suivante (résultat en C2)
:

Si la valeur de la cellule B2 de Feuil1 est égale à l'une des valeurs de la
cellule F7 contenue dans Feuil2 à Feuil10, alors C2 de Feuil1 est égale à la
valeur de la cellule H12 de la feuille correspondante.

Merci de votre aide.

Excel 97

 

[LaurentTBT]

Bonsoir

J'ai une réponse incomplète:
Tu tapes en C2 de ta feuille 1:
=SI(B2=Feuil2!F7;Feuil2!H12;SI(B2=Feuil3!F7;Feuil3!H12;SI(B2=Feuil4!F7;Feuil4!H12;SI(B2=Feuil5!F7;Feuil5!H12;SI(B2=Feuil6!F7;Feuil6!H12;SI(B2=Feuil7!F7;Feuil7!H12;SI(B2=Feuil8!F7;Feuil8!H12;SI(B2=Feuil9!F7;Feuil9!H12;"Aucune"))))))))
Malheureusement, on ne peux imbriquer que 7 fois la fonction SI. Ma formule fonctionne donc pour les données de feuil1 à feuil9 et non feuil10 comme tu le souhaites.
Si tu as absolument besoin de travailler jusqu'à la feuille 10, à mon avis, il faut créer une fonction sous VBA puis l'appeler sur ta feuille 1. Je regarde ça d'ici quelques minutes...

Autre problème: si il y a une correspondance possible dans plusieures feuilles, c'est seulement la valeur de la première feuille ou il y a correspondance qui sera prise.

 

[LaurentTBT]

Comme promis, voilà une fonction à insérer sous VBA:

Function Recapitulatif()
For i = 2 To 10
If Worksheets(i).Range("F7") = Range("B2") Then Recapitulatif = _
Worksheets(i).Range("H12")
Next i
End Function

Il ne reste plus qu'à appeler cette fonction en cellule C2 de la feuille 1:
=recapitulatif()

Ceci-dit, j'ai un nouveau problème: ma fonction personnalisée ne se recalcule plus à chaque modification.
Il faudra donc utiliser une procèdure événementielle, par exemple, tu rajoute dans le module lié à la feuil1:
Private Sub Worksheet_Activate()
Range("C2") = Recapitulatif()
End Sub
Ainsi, le calcul se refera à chaque fois que tu activeras la feuille 1 (par exemple après avoir modifié les données des autres feuilles.

Enfin, tu peux ajouter à la suite:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then _
Range("C2") = Recapitulatif()
End Sub
afin que le calcul se fasse à nouveau lorsque tu modifieras la valeur à rechercher en B2.

Voilà, c'est un peu lourd, mais j'espère que cela répond à ton problème.

 

[Jean-Marie]

Bonsoir Laurent

Dans ton premier post, tu dis Malheureusement, on ne peux imbriquer que 7 fois la fonction SI. Oui d'accord.

Pourquoi ne pas utiliser la formule suivante
=SI(..(..(..(..(..(SI(...;...;0)))))))+SI(..(..(SI(...;...;0))))

Je n'ai pas trouvé beaucoup de solution à part le SI, ou alors faire un tableau avec une liaison sur la cellule de chaque feuille.

@+Jean-Marie

 

[andré]

Salut Jg, Laurent TBT, Jean-Marie et les autres,

Essaie donc la formule suivante :

=SI(B2="";"";SI(OU(Feuil2!F7=B2;Feuil3!F7=B2;Feuil4!F7=B2;Feuil5!F7=B2;Feuil6!F7=B2;Feuil7!F7=B2;Feuil8!F7=B2;Feuil9!F7=B2;Feuil10!F7=B2);H2;""))

J'ai ajouté le premier SI pour éviter que la valeur soit renvoyée si B2 et une des cellules F7 des feuilles 2 à 10 sont vides.

Si les SI sont limités, les OU ne le sont pas.

Ândré.

 

[JG]

Bonjour,

Je remercie tous ceux qui ont répondu à ma demande d'aide, et je m'adresse plus particulièrement à LaurentTBT.

La "Function Recapitulatif()" convient très bien.

Mais j'ai été incomplet dans la formulation de ma demande : la comparaison ne se fait pas seulement avec une seule cellule (B2), mais avec une plage de cellules (B2 à B6).

Autrement dit :
Si une des valeurs de la plage de cellules B2 à B6 de Feuil1 est égale à l'une des valeurs de la cellule F7 contenue dans Feuil2 à Feuil10, alors C2 de Feuil1 est égale à la valeur de la cellule H12 de la feuille correspondante.

Cordialement.

 

[LaurentTBT]

Bonsoir à tous, et désolé de vous avoir quitté si lâchement hier soir, une dure journée de labeur m'attendant (je confirme, elle fut bien dure)

Pour Jean-Marie: Bien vu, ton astuce pour scinder en 2 les SI imbriqués. J'ai malheureusement un petit bémol: si une correspondance est trouvée dans la première partie mais également une autre correspondance trouvée aussicdans la deuxième partie, tu auras la somme des deux résultats!

Pour André: là aussi, je crois qu'il y a un petit Hic: s'il existe une correspondance, ta formule renverra la valeur de H2 de la feuille 1, alors que je crois comprendre que JG voudrait la valeur de H2 de la feuille ou il y a correspondance...

Pour JG: je suis heureux de t'avoir aidé, car c'est ma première modeste contribution à ce forum. Pour ta nouvelle demande, ceci devrait convenir:
Function Recapitulatif()
Dim i, j as integer
For i = 2 To 10
For j=1 to 5
If Worksheets(i).Range("F7") = Range("B2").cells(j,1) Then _
Recapitulatif = Worksheets(i).Range("H12")
Next j
Next i
End Function

 

[LaurentTBT]

Salut,

Je met en pièce jointe la version finale, car avec ta nouvelle demande (comparaison à partir de B2:B6, et non B2 seulement), l'ajout d'une seconde boucle ne suffit pas. Il fallait aussi modifier
Private Sub Worksheet_Change(ByVal Target As Range)

Sinon, j'ai un point que je voudrais soulever:
si plusieurs correspondances existent, il peut y avoir un problème
Par exemple, si sur feuil1, tu as 3 en B4, 5 en B6, et qu'on retrouve, par ex, 3 en F7 de la feuil2 et 5 en F7 de la feuil8, tu as deux solutions possibles: le résultat peut être la valeur de H12 de la feuil2 ,
mais aussi la valeur de H12 de la feuil8 . Es-tu sûr que ces deux valeurs sont les mêmes? Car sinon, ton récapitulatif ne sera pas complet.
PS: Avec ma macro, la valeur retenue est celle de la feuille correspondante la plus élevée (ie dans l'exemple ci-dessus, Feuil8!H12).

@+

 

[LaurentTBT]

Avec la pièce jointe promise, c'est beaucoup mieux!

Sorry

 

[LaurentTBT]

Pour le fun

Je pensais avoir trouvé une formule équivalente, à partir de l'idée de Jean-Marie de scinder en 2 les SI imbriqués:
=SI(..(..(..(..(..(SI(...;...;0)))))))+SI(..(..(SI(...;...;0))))
Si j'appelle la première partie de sa formule VALEUR1, et la deuxième partie valeur 2, je rajoute un SI devant pour ne pas avoir de somme dans le cas où il existe une correspondance dans les deux sous-formules:
=si(VALEUR1=0;VALEUR2;VALEUR1)
Il n'y a plus qu'à remplacer VALEUR1 et VALEUR2 par leurs formules avec SI imbriqués, et le tour est joué. J'ai également remplacé les valeurs 0 rendues s'il n'y a pas de correspondance par "Aucune" afin de palier le cas où il existerait une correspondance débouchant sur un résultat dans la feuille correspondante en H12 justement égal à 0.
Voilà ce que ça donne (accrochez-vous)

=SI(SI(B2=Feuil2!F7;Feuil2!H12;SI(B2=Feuil3!F7;Feuil3!H12;SI(B2=Feuil4!F7;Feuil4!H12;SI(B2=Feuil5!F7;Feuil5!H12;"Aucune"))))="Aucune";SI(B2=Feuil6!F7;Feuil6!H12;SI(B2=Feuil7!F7;Feuil7!H12;SI(B2=Feuil8!F7;Feuil8!H12;SI(B2=Feuil9!F7;Feuil9!H12;SI(B2=Feuil10!F7;Feuil10!H12;"Aucune")))));SI(B2=Feuil2!F7;Feuil2!H12;SI(B2=Feuil3!F7;Feuil3!H12;SI(B2=Feuil4!F7;Feuil4!H12;SI(B2=Feuil5!F7;Feuil5!H12;"Aucune")))))

Et en plus, ça marche. Pas mal, non?

Maintenant, reste la deuxième question de JG, à savoir faire les test de correspondance non pas sur B2, mais sur B2 à B6.

Et là, j'ai pensé utiliser l'idée d'André, avec des OU:
il suffit, dans la formule ci-dessus, de remplacer tous les
B2=Feuiln!F7 par
OU(B2=Feuiln!F7;B3=Feuiln!F7;B4=Feuiln!F7;B5=Feuiln!F7;B6=Feuiln!F7)

Malheureusement, j'ai réussi à le faire jusqu'à Feuil8, et j'obtiens alors le message "Formule trop longue"

Dommage, je me voyais déjà en pôle position pour le record de la plus grosse formule!

 

[Celeda]

Re: Pour le fun

Bonjour,

Laurent, je vais me permettre une petite incartade en ce beau vendredi,

je ne sais pas si tu vas atteindre la pôle position de la plus grosse formule, mais en attendant, pour un tout nouveau venu, tu t'es trés bien intégré dans le peloton du forum xld tant par ton esprit que par le suivi des réponses - mais j'ai une question indiscréte :

TBT cela veut-dire quoi : tres bon travailleur ou
tres beau travailleur ou
to be teenager ou
??????????????????????? pour le fun ...

Merci pour tes réponses.

et bon week-end à tout le forum.
Celeda

ps: il n'y a naturellement aucune connotation de type...... harassement et cie....juste for laugh.

 

[Jean-Marie]

Re: Pour le fun

Bonjour, Tout le monde

Je crois que la formule la plus longue mise sur le forum était pour le post "prise de tête", elle dépassait les 640 caractères.

Bon week-end à tous les amis du forum.

 

[Monique]

Re: Pour le fun

Bonsoir,

Pour tenir compte de la plage B2:B6 et pas seulement de la cellule B2, on peut remplacer
SI(B2=Feuil2!F7;Feuil2!H12;SI(B2=Feuil3!F7;Feuil3!H12;SI(etc
par ceci :
SI(NB.SI(B2:B6;Feuil2!F7)>0;Feuil2!H12;SI(NB.SI(B2:B6;Feuil3!F7)>0;Feuil3!H12;SI(etc
C'est un tout petit peu plus long, mais ça fonctionne et ça donne :
=SI(SI(NB.SI(B2:B6;Feuil2!F7)>0;Feuil2!H12;SI(NB.SI(B2:B6;Feuil3!F7)>0;Feuil3!H12;SI(NB.SI(B2:B6;Feuil4!F7)>0;Feuil4!H12;SI(NB.SI(B2:B6;Feuil5!F7)>0;Feuil5!H12;"Aucune"))))="Aucune";SI(NB.SI(B2:B6;Feuil6!F7)>0;Feuil6!H12;SI(NB.SI(B2:B6;Feuil7!F7)>0;Feuil7!H12;SI(NB.SI(B2:B6;Feuil8!F7)>0;Feuil8!H12;SI(NB.SI(B2:B6;Feuil9!F7)>0;Feuil9!H12;SI(NB.SI(B2:B6;Feuil10!F7)>0;Feuil10!H12;"Aucune")))));SI(NB.SI(B2:B6;Feuil2!F7)>0;Feuil2!H12;SI(NB.SI(B2:B6;Feuil3!F7)>0;Feuil3!H12;SI(NB.SI(B2:B6;Feuil4!F7)>0;Feuil4!H12;SI(NB.SI(B2:B6;Feuil5!F7)>0;Feuil5!H12;"Aucune")))))
Moa avait cherché et trouvé une solution pour des NB.SI éparpillés; ça date de quelques mois.