XL 2016 fonction Indirect

[poussy67]

Bonjour Le Forum


Dans un classeur "planning", j'ai 53 onglets qui représente les semaines de l'année. J'ai une colonne avec nom/prénom qui n'est forcément pas dans la même adresse cellule. ex.: le nom A pourrait se trouver dans la ligne 6 ou 9 ou 10. J'ai plusieurs colonnes "journée" ou je saisis leur présence avec des lettres ex.: R ou CP etc. Mon souci avec plusieurs essais avec la fonction INDIRECT j'ai le résultat #REF ou "VALEUR". J'ai essayé en nommer une plage avec les noms des feuilles rien à faire je ne trouve pas malgré plusieurs recherches sans résultat je désespère et c'est pour cela que je fais appel au expert.

en vous remerciant d'avance pour votre aide

ci-joint un exemple.

Cdt Poussy

 

[shinozak]

Bonsoir,
J'attends les experts aussi, en attendant voici une formule de bricolage

 

[job75]

Bonjour poussy67, shinozak, le forum,

Les plages utilisées dans SOMMEPROD (ou NB.SI.ENS) doivent appartenir à une même feuille.

Donc ici il faudrait faire la somme de 53 SOMMEPROD, le plus simple est une fonction VBA :

Option Compare Text 'la casse est ignorée

Function SommeFeuilles(nom$, adresse$, critere$)
Application.Volatile
Dim w As Worksheet, tablo, i&, j%
For Each w In Worksheets
    If w.Name Like "S#*" Then
        tablo = w.Range(adresse) 'matrice, plus rapide
        For i = 1 To UBound(tablo)
            If tablo(i, 1) = nom Then
                For j = 2 To UBound(tablo, 2)
                    If tablo(i, j) = critere Then SommeFeuilles = SommeFeuilles + 1
                Next j
            End If
        Next i
    End If
Next w
End Function

Le code doit être placé obligatoirement dans un module standard (Module1).

La formule en B6 de la feuille "Synthèse" =SommeFeuilles(A6;"A7:H13";"R")

Bonne journée.

A+

 

[CISCO]

Bonjour

Une possibilité en pièce jointe, avec une colonne "Total" supplémentaire, de I7 à I13, dans chacune des feuilles S01, S02...

SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A7:A13");A6;INDIRECT("'"&NomFeuilles&"'!I7:I13")))

On peut faire sans ces colonnes supplémentaires, mais alors on se retrouve avec une formule à rallonge utilisant autant de parties semblables que de colonnes, une pour B, une pour C... et une pour H.

SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A7:A13");A6;INDIRECT("'"&NomFeuilles&"'!B7:B13"))) +SOMMEPROD(SOMME.SI(INDIRECT("'"&NomFeuilles&"'!A7:A13");A6;INDIRECT("'"&NomFeuilles&"'!C7:C13")))+...

@ plus

 

[goube]

Bonjour à tous,

Un problème similaire au tien avec une solution.

Etendre un "somme.si.ens" sur plusieurs onglets

Je me présente étant nouveau sur ce forum, j'ai 33 ans et travail en logistique, j'ai créé un fichier excel pour gérer le planning des caristes, cependant je...

forum.excel-pratique.com

Cordialement.

 

[CISCO]

Bonjour à tous

@ goube : La proposition de J. Boisgontier ne fonctionne pas tel que si on va chercher des informations dans plusieurs colonnes en même temps. Dommage... J'ai cherché sur son site (mal peut-être), mais n'ai pas trouvé... d'où les colonnes intermédiaires dont je parle dans mon post #4.

@ plus

 

[job75]

Re, salut CISCO, goube,

Ma fonction précédente étant volatile, toutes les formules de la feuille "Synthèse" sont recalculées quand on modifie une cellule quelconque dans le classeur, cela peut prendre un certain temps.

Il vaut mieux alors utiliser une macro évènementielle, voyez ce fichier (2) et le code :

Option Compare Text 'la casse est ignorée

Private Sub Worksheet_Activate()
Dim critere$, resu, i&, nom$, w As Worksheet, tablo, j&, k%
critere = [B3]
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A5].CurrentRegion.Resize(, 2)
    resu = .Value 'matrice, plus rapide
    For i = 2 To UBound(resu)
        nom = resu(i, 1)
        resu(i, 2) = 0
        For Each w In Worksheets
            If w.Name Like "S#*" Then
                tablo = w.Range("A6").CurrentRegion 'matrice, plus rapide
                For j = 2 To UBound(tablo)
                    If tablo(j, 1) = nom Then
                        For k = 2 To UBound(tablo, 2)
                            If tablo(j, k) = critere Then resu(i, 2) = resu(i, 2) + 1
                        Next k
                    End If
                Next j
            End If
    Next w, i
    .Columns(2) = Application.Index(resu, , 2) 'restitution
End With
End Sub

A placer dans le code de la feuille "Synthèse", la macro se déclenche quand on active la feuille.

A+

 

[goube]

Bonjour à tous,
@CISCO , au temps pour moi.
Cordialement.

 

[poussy67]

Bonjour Le forum

Je te tiens à remercier tous le monde pour votre Aide.
Je vois qu'il y a plusieurs façon de faire. J'aurai une question supplémentaire
En vba est il possible de rajouter un critère supplémentaire dans le style de rajouter
les semaines pour avoir une visu le nombre par semaine?

 

[job75]

En vba est il possible de rajouter un critère supplémentaire dans le style de rajouter
les semaines pour avoir une visu le nombre par semaine?

Tout est possible mais avec les semaines affichées dans la feuille "Synthèse" le VBA est sans intérêt car c'est très simple par formule, voyez le fichier joint et la formule en C6 :

=SIERREUR(SOMMEPROD((INDIRECT("'"&C$5&"'!A7:A13")=$A6)*(INDIRECT("'"&C$5&"'!B7:H13")=$B$3));"")

 

[job75]

Maintenant s'il faut du VBA inutile de se casser la tête, fichier (3) avec :

Private Sub Worksheet_Activate()
With [C6:BC12] '12 à adapter
    .Formula = "=IFERROR(SUMPRODUCT((INDIRECT(""'""&C$5&""'!A7:A13"")=$A6)*(INDIRECT(""'""&C$5&""'!B7:H13"")=$B$3)),"""")"
    .Value = .Value 'supprime les formules
End With
End Sub

On entre simplement la formule utilisée au post précédent.

 

[poussy67]

Bonjour job75

Je te remercie beaucoup pour t'es solution a mon problème, Je vais essayer de les adapter des que j'ai un moment.

encore mille merci

 

[Danixdb]

Bonjour Poussy67, le Forum,

Une solution en utilisant deux requêtes Power query. Je ne sais pas si ti connais, mais ça répond parfaitement à ta demande. Il faut à l'ouverture du fichier autoriser la connexion à la source de données. Et ensuite lors de la mise à jour du tableau croisé dynamique, il exécutera la requête basée sur tes données.
Attention, la première fois il faudra changer le chemin de la source de données, car elle est inscrite en dur dans deux paramètres.
Pour ce faire, il te faut aller dans le menu Données et demander à afficher les requêtes, les deux paramètres Chemin et NomFichier, tu fais un clic droit sur le premier et tu clique sur Modifier, tu changes le chemin et tu pratique de la même façon sur le 2ème en changeant le nom du fichier.
Ensuite pour mettre à jour les données, dans le menu Données tu as un bouton Actualiser tout qui vas faire la même chose sauf qu'il faut enregistrer les modifications avant d'actualiser.
En espérant que cela répondra à ta demande et te permettra de résoudre celle-ci. C'est une façon de faire, d'autres sont possibles mais pour des consolidations, je préfère passer par cette méthode qui ne demande aucune écriture de code tel VBA par exemple.
Cordialement
DanixDB

 

[poussy67]

Bonjour Danixdb

Merci pour ton exemple.
Non je ne connais pas cette solution, je essayer de faire un tableau croiser dynamique en suivat ton exemple.

Encore merci pour ton aide

Cdt poussy