Addition selon une base de donnée variable

Bonjour,

je dois réaliser un tableau Excel pour répartir les heures des collaborateurs par secteur.

en sommes, j'arrive à le faire, mais je dois rajouter une contrainte de date car chaque collaborateur ne travaille pas forcément tous les jours dans le même secteur.

j'ai créé un fichier anomyne avec mon problème :

• sur la feuille "tabelle des répartitions", il y a les dates auxquelles le collaborateur a travaillé dans quel secteur (de => a, si il n'a a pas de date, il y travaille encore)
• la feuille "horaire" regroupe les horaires par jour selon la timbreuse
• la feuille "répartition par jour" doit contenir la formule voulue

Merci pour votre aide

Bonjour,

sujet intéressant

je n'arrive pas à résoudre par une formule même matricielle,

je vais donc le faire en macro

Bonjour à tous

Pour ce type de calcul il est préférable de croiser le tableau source avec un calendrier via un requêteur.

As-tu installé des add-on Microsoft comme PowerQuery ou PowerPivot ?

Sinon reste MsQuery incorporé mais pas trop convivial

Voir résultat joint où j'ai incorporé un onglet calendrier

+ requête.

Re-

Bonjour Chris,

une version macro "standard"

je vais maintenant aller voir ce que Chris a fait ...

Merci Steelson,

j'espère que j'arriverai à transposer ta macro car elle fonctionne parfaitement

à ta disposition si besoin ...

Salut 78chris,

Merci pour ton travail, mais il faut toutefois que sur la tabelle "Répartion par jour", les horaires de la feuille "Horaire" se reportent selon la clé de répartition " Tabelle des répartitions".

La macro de Steelson fonctionne parfaitement, mais après un long moment de réflexion, je ne la comprends pas

une formule pourrait m'être très utile, surtout dans le but de faire vivre le document dans le futur. Il s'agit d'une base pour une statistique de productivité, donc, ce document devra vivre sur le long, voire très long, terme.

Merci

Sylvain

ecoffeys a écrit :

La macro de Steelson fonctionne parfaitement, mais après un long moment de réflexion, je ne la comprends pas

ok

je vais la documenter dès que j'ai un moment

Bonjour

C'est aussi faisable dans la requête : je n'avais pas compris, ce pourquoi je ne l'ai pas fait.

Pas trop de temps aujourd'hui, surtout ce matin, et comme tu sembles plus intéressé par la macro...

Chris, je suis aussi intéressé par tes explications ...

Pour ce qui me concerne ...

1- Ici je balaye l'ensemble des données et je fais appel à la fonction nbheures avec comme paramètre la date colonne A et le secteur ligne 1

Sub CALCULER()
With Sheets("Répartition par jour")
    For i = 2 To .Cells(1, 1).End(xlDown).Row
        For j = 2 To .Cells(1, 1).End(xlToRight).Column - 1
            .Cells(i, j) = nbheures(.Cells(i, 1), .Cells(1, j))
        Next
    Next
End With
End Sub

j'aurais pu faire appel à cette fonction dans chaque cellule en mettant ) =nbheures(_;_) mais cela ne fonctionnera pas, c'était un premier casse-tête

2- fonction nbheures :

Function nbheures(quand As Range, secteur As Range) As Double
nbheures = 0
With Sheets("Tabelle des répartitions")
    For i = 2 To .Cells(1, 1).End(xlDown).Row
        If .Cells(i, 1).Value <= quand.Value And (.Cells(i, 2).Value = "" Or .Cells(i, 2).Value >= quand.Value) Then
            For j = 2 To .Cells(1, 1).End(xlToRight).Column
                If .Cells(1, j) = secteur.Value And .Cells(i, j) <> "" Then
                    ou = cherche2criteres(quand.Value, .Cells(i, "C").Value)
                    If ou <> 0 Then
                        nbheures = nbheures + .Cells(i, j) * Sheets("Horaire").Cells(ou, "E").Value
                    End If
                End If
            Next
        End If
    Next
End With
End Function

je vais dans Tabelle et je regarde si la date correspond à une plage, puis je cherche le secteur dans les en-têtes (ligne 1) et je regarde si le croisement est renseigné par une valeur de répartition

si ok je vais alors chercher dans Horaires le temps total passé par la personne ce jour là

3- cela se corse un poil

Function cherche2criteres(Criteria1 As Variant, Criteria2 As Variant) As Integer

    Dim Found As Range, Firstfound As String
    Dim rngSearch As Range

    Set rngSearch = Sheets("Horaire").Range("A:A")

    Set Found = rngSearch.Find(What:=Criteria1, _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)

    If Not Found Is Nothing Then

        Firstfound = Found.Address

        Do
            If Found.EntireRow.Range("B1").Value = Criteria2 Then Exit Do  'Match found

            Set Found = rngSearch.FindNext(After:=Found)
            If Found.Address = Firstfound Then Set Found = Nothing

        Loop Until Found Is Nothing

    End If

    If Not Found Is Nothing Then
        cherche2criteres = Found.Row
    Else
        cherche2criteres = 0
    End If

End Function

j'aurais pu concaténer date et individu, la recherche aurait été facile

en fait je me suis fait violence (c'est le seconde difficulté rencontrée) et j'ai adapté un code à la recherche en 2 colonnes

ce code fonctionne comme suit :

on cherche dans la colonne A ici la valeur de la date

si ok, je regrade dans la ligne colonne B si le nom est ok

sinon je passe à la prochaine occorence

la fonction renvoie la ligne trouvée (variable ou dans le $2) ou bien 0 si aucune occurrence trouvée

Voilà voilou, si pas clair, n'hésite pas

Bonjour

Nouvelle mouture : on n'a plus besoin du calendrier puisque les horaires ont des dates donc en tiennent lieu.

merci bien !

super job et content d'avoir fait cogiter tes méninges