Formule longue et qui se répète

[gaetan2812]

Bonjour,

Je connais ce forum depuis quelques temps, je ne m'étais pas encore inscrit car j'arrivais toujours à trouver un cas similaire au mien, mais là je bloque.

Je vous explique mon problème. Dans mon onglet 3, je dois vérifier par rapport au nom sélectionné, quelles cases sont cochées dans l'onglet 1, et ensuite cela vérifie s'il y a au moins un "oui" dans l'onglet 2 pour le paramètre (collège, lycée ...) choisi.

Mon problème est donc la longueur de la formule de C5 à C7 qui dépend du nombre de "collège 1, primaire 1, lycée 1 ...". Là on peut voir dans l'onglet 1 qu'il y en a 6 et on doit donc vérifié pour chaque type qu'il y a une X.

La formule est assez longue là, mais le truc c'est qu'en réalité, je peux monter à une très grande quantité de "collège 1, primaire 1, lycée 1 ..." ce qui fait qu'à un moment je dépasse les 8192 caractères maximum d'une formule.

A cela je peux rajouter le fait que l'onglet 2 est parfois accompagné d'un autre onglet genre 2 bis où on pourrait avoir par exemple "collège 3, primaire 3, lycée 3..."

Au final ça multiplie le nombre de "Onglet 2"! ... etc dans ma formule finale, qui accroit donc le nombre de caractères.

Auriez-vous un moyen pour que ça passe tout seul des cases C3 à H3 de l'onglet 1 et donc éviter la répétion du bout de formule (SI(RECHERCHEV($B$2;'Onglet 1'!A4:H8;4;FAUX)="X";SI(NB.SI.ENS('Onglet 2'!A$1:A$100;"Primaire 2";'Onglet 2'!B$1:B$100;"Chimie";'Onglet 2'!C$1:C$100;"X")>0;"X";"");""))="X" ?

J'ai écris en bleu la partie "variable" de la formule.

Je vous remercie par avance.

 

[vgendron]

Re : Formule longue et qui se répète

Bonjour,

Juste un début de piste:
cette formule permet de compter le nombre de croix dans l'onglet 1 pour la personne sélectionnée..

=SOMMEPROD((('Onglet 1'!A4:A13='Onglet 3'!$B$2)*1)*('Onglet 1'!C4:H13="X"))

pour continuer. je pense qu'il faut jouer avec les index, equiv...
la fonction Sommeprod est une formule matricielle qui (je crois) contient intrinsèquement la position des cellules qui répondent au critère: ici; = personne ET croix

 

[gaetan2812]

Re : Formule longue et qui se répète

Je vois pas vraiment comment m'y prendre, car ce n'est pas le nombre de croix, qui m’intéresse, mais la présence de croix avec les critères. C'est là que je bloque

 

[vgendron]

Re : Formule longue et qui se répète

Bonjour,

Voici une autre proposition avec macro..à optimiser
Jette un oeil sur ton fichier (ci joint),
j'ai modifié la présentation (notamment pour l'onglet 2)

 

[gaetan2812]

Re : Formule longue et qui se répète

Bonjour,

J'ai bien regardé le fichier, et je ne trouve pas la macro dont tu parles.
Après, pour la modification de la présentation, je ne peux pas me le permettre, car je ne peux toucher aux à la "présentation" des 3 onglets. Je suis désolé pour toi par rapport au temps que tu as passé à vouloir m'aider. Mais je ne peux absolument pas toucher aux "Présentations". Rajouter une colonne oui, mais je ne peux modifier le reste (législation).

 

[vgendron]

Re : Formule longue et qui se répète

ah oui désolé, j'ai pas mis de bouton pour lancer la macro directement:
pour voir la macro, il faut ouvrir l'editeur VBA : Alt +F11
tu ouvres le module 1 et la macro est dedans/ elle s'appelle "Cherche"

 

[gaetan2812]

Re : Formule longue et qui se répète

Ok, voilà plusieurs minutes que je suis dessus et "j'aime bien comment c'est fait", merci.

Par contre, beaucoup de choses dépendent de la façon dont l'Onglet 2 est présenté, donc par rapport à ma présentation, je coince ...

 

[vgendron]

Re : Formule longue et qui se répète

Re,

1) j'ai adapté pour reprendre ta présentation d'origine de l'onglet 2
2) j'ai ajouté des commentaires dans la macro
3) j'ai ajouté le petit bouton ;-)

 

[gaetan2812]

Re : Formule longue et qui se répète

Alors je viens d'essayer de comprendre ... et j'y arrive plutôt bien.

Il y a juste un problème par exemple avec la Pers. 2 qui devrait avoir 3 N et ce n'est pas le cas.

Ca va peut être avec le passage que je n'ai pas trop compris dans le code :

 If Not e Is Nothing Then
                    LigToCol = e.Row
                    Sheets("Onglet 3").Cells(LigToCol, 2) = "Y"

Et autre chose, je comprends le principe avec un seul onglet 2, mais que se passe t-il si on a "2" onglets 2 (Cf fichier). Cela rajoute bien entendu des écoles dans l'onglet 1, mais pour les recherches avec la macro dans les onglets 2 et 2 bis, cela pose problème non ?

Merci.

 

[vgendron]

Re : Formule longue et qui se répète

1) effectivement, la macro boucle sur les écoles
première ecole: on a bien 3 N qui apparaissent..
seconde école, le résultat donne 1Y et 2N.. sauf que la macro écrase le résultat de la première école. il n'y a pas le test "ou"..
à rajouter donc au moment ou la macro copie dans l'onglet 3

2) la macro ne va pas voir ce qui se passe dans l'onglet 2bis..
je pensais que toutes les écoles étaient regroupées dans l'onglet 2:
2 solutions
A) il faudrait donc faire chercher la macro sur tous les onglets possibles.
- créer une liste des onglets 2 2bis etc.. c'est ce qui me semble le mieux parce que. voir ci après
B) faire boucler la macro sur TOUS les onglets du classeur.. sauf qu'il y a des onglets pour lesquelles ca va planter.
ex. l'onglet 1: la macro va bien trouver l'école, sauf que l'info qu'elle récupère à coté ne correspond pas à ce qu'on cherche. puisque l'onglet 1 n'a pas la meme structure que les onglets 2 et 2bis etc..


Dans 1 premier temps.. peux tu expliquer dans quel cas on met un oui?
il suffit qu'il y ait un oui pour la matière sur une des écoles?

 

[gaetan2812]

Re : Formule longue et qui se répète

Pardon ne ne pas avoir pu te répondre plus tôt mais c'est bien cela.
On a un Oui s'il y a au moins un oui pour la matière pour une des écoles.

 

[gaetan2812]

Re : Formule longue et qui se répète

Bonjour,

Je ne sais pas encore ajouter le test "ou" dans la macro.

Par contre, j'ai une petite idée pour le cas où on a plusieurs onglets. Dans l'onglet 3, on peut faire des colonnes de "oui intermédiaire". Une colonne par onglet, et au final faire juste une formule Excel genre si on a au moins une X (un "oui") dans ces cases intermédiaires, on a une X dans la colonne B. Donc ce serait la même macro dans les différents onglets (2 et 2bis) avec quelques changement dus à la différence de nom des onglets.

 

[vgendron]

Re : Formule longue et qui se répète

Hello !!
après ce long et bon week end, petite mise à niveau
le OU est géré,
la liste des écoles se met à jour automatiquement: avec la zone nommée

 

[gaetan2812]

Re : Formule longue et qui se répète

Salut, Merci de me répondre une nouvelle fois.

Je crois que quand tu as fait le fichier, il a du y avoir une liaison avec un fichier nommé "Macrothèque_Complément.xlam" ce qui fait que la matrice n veut pas s'éxécuter.

 

[vgendron]

Re : Formule longue et qui se répète

Bizarre ce que tu me dis la..
effectivement. Macrothèque.. c'est mon fichier ou je rassemble toutes mes macros..
mais dans ton fichier, je n'y ai pas fait référence du tout... et c'est la première fois qu'on me rapporte ce problème...

voici le code que tu peux mettre dans un module VBA

Sub cherche()

'on commence par effacer les matières
Range(Cells(5, 2), Cells(5, 2).End(xlDown)).Clear

'récupère le nom de l'élève concerné
NomEleve = Sheets("Onglet 3").Range("B2")

'rercherche de cet élève dans l'onglet 1 et on récupère la ligne ou il est situé
Set c = Sheets("Onglet 1").Range("TabOng1").Find(NomEleve)
If Not c Is Nothing Then
    LigneEleve = c.Row
End If

'on compte le nombre d'écoles dans la zone nommée ListEcoles
'Note: il faudra définir cette zone dynamiquement avec decaler....
'voir le gestionaire de noms
'ListeEcoles=DECALER('Onglet 1'!$C$3;0;0;1;NBVAL('Onglet 1'!$3:$3)-4)
'permettra ansi de rajouter autant d'écoles que souhaitées, la macro fonctionnera toujours

NbEcoles = Sheets("Onglet 1").Range("ListEcoles").Columns.Count

'pour chaque école, on regarde si l'élève est concerné: présence d'une croix
For j = 3 To 2 + NbEcoles
    If Sheets("onglet 1").Cells(LigneEleve, j) = "X" Then
        'si concerné, alors on note cette école pour ensuite aller voir les matières dans l'onglet2
        EcoleEnCours = Sheets("onglet 1").Cells(3, j)
        'recherche de la position de l'école dans l'onglet 2
        Set d = Sheets("Onglet 2").Range("tabOng2").Columns(1).Find(EcoleEnCours)
        If Not d Is Nothing Then
            'forcément, le .find va trouver la PREMIERE occurence de cette école
            LigneEcole = d.Row
            
            For k = 1 To 3 '3=Nb Matières
                'on récupère la matière k à coté de l'école
                MatEnCours = Sheets("Onglet 2").Cells(LigneEcole + k - 1, 2)
                If IsEmpty(Sheets("Onglet 2").Cells(LigneEcole + k - 1, 3)) Then
                    MatValidée = "N"
                Else: MatValidée = "Y"
                End If
                'on cherche la position de la matière dans l'onglet 3
                Set e = Sheets("Onglet 3").Range("A1:A10").Find(MatEnCours)
                If Not e Is Nothing Then
                    LigToCol = e.Row
                    'condition OU: si il y a déjà un OUI, on ne fait rien
                    If (Sheets("Onglet 3").Cells(LigToCol, 2) = "N") Or IsEmpty((Sheets("Onglet 3").Cells(LigToCol, 2))) Then
                        Sheets("Onglet 3").Cells(LigToCol, 2) = MatValidée
                    End If
                End If
            Next k
        End If
    End If
Next j
End Sub