Formules compliquées ou tableau croisé dynamique ?

[Blafi]

Bonjour à tous et bonne soirée,

J'ai un pb assez compliqué que les spécialistes formules de ce forum doivent pouvoir résoudre..

Dans le fichier joint, j'ai un tableau qui reprend pour une année le détail des accidents de travail survenu à 2 catégories de personnels (C et M) suivant la date, la nature (trajet, sport, manutention...), la durée de l'indisponibilité etc....

Je cherche à extraire de ce tableau les renseignements afin de remplir l'autre tableau (qui se trouve à droite dans la même feuille) et qui récapitule, suivant la catégorie concernée (C ou M) et le mois, le total des accidents survenu dans chaque catégorie de circonstance et sa durée....

c'est pas évident à expliquer mais en regardant les tableaux on comprend assez vite...

Mon problème est d'arriver à remplir ce deuxième tableau et je n'y arrive pas :

- faut-il employer des formules compliquées avec des si et des somprod (que j'ai bien du mal à utiliser..)
-ou bien faut-il utiliser un tableau croisé dynamique qui arriverait à me sortir les données voulues

Merci d'avance si vous pouvez me proposer une solution (rapide) car, comme toujours on me demande la chose pour hier...

A ++

 

[PMO2]

Re : Formules compliquées ou tableau croisé dynamique ?

Bonjour la Marine Nationale,

Une piste, en VBA, avec le code suivant à copier dans un module standard

'### Constante de la feuille source à adapter ###
Const FEUILLE As String = "2007"
'################################################

Sub Recapitulatif()
Dim S As Worksheet
Dim R As Range
Dim var
Dim i&
Dim j&
Dim k&
Dim mois&
Dim cpt&
Dim CATM()
Dim CATC()
Dim MJoursTravail&
Dim MJoursSport&
Dim MJoursTrajet&
Dim CJoursTravail&
Dim CJoursSport&
Dim CJoursTrajet&
Dim Vehicule&
Dim ColDepart&
ColDepart& = 32
Sheets(FEUILLE).Copy After:=Sheets(Sheets.Count)
Set S = ActiveSheet
Set R = S.Range("a5:y" & S.[c65536].End(xlUp).Row & "")
var = R
For mois& = 1 To 12
  Vehicule& = 0
  MJoursTravail& = 0
  MJoursSport& = 0
  MJoursTrajet& = 0
  CJoursTravail& = 0
  CJoursSport& = 0
  CJoursTrajet& = 0
  Erase CATM
  Erase CATC
  ReDim CATM(7 To 22)
  ReDim CATC(7 To 22)
  For i& = 1 To UBound(var, 1)
    If Month(var(i&, 4)) = mois& Then
      If UCase(Trim(var(i&, 3))) = "M" Then
        For j& = 7 To 22
          If var(i&, j&) <> "" Then
            CATM(j&) = CATM(j&) + 1
            If j& < 14 Or j& = 18 Then MJoursTravail& = MJoursTravail& + var(i&, 6)
            If j& > 13 And j& < 18 Then MJoursSport& = MJoursSport& + var(i&, 6)
            If j& > 18 Then MJoursTrajet& = MJoursTrajet& + var(i&, 6)
            Exit For
          End If
        Next j&
      ElseIf UCase(Trim(var(i&, 3))) = "C" Then
        For j& = 7 To 22
          If var(i&, j&) <> "" Then
            CATC(j&) = CATC(j&) + 1
            If j& < 14 Or j& = 18 Then CJoursTravail& = CJoursTravail& + var(i&, 6)
            If j& > 13 And j& < 18 Then CJoursSport& = CJoursSport& + var(i&, 6)
            If j& > 18 Then CJoursTrajet& = CJoursTrajet& + var(i&, 6)
            Exit For
          End If
        Next j&
      End If
    End If
  Next i&
  '--- C ---
  For k& = 19 To 22
    S.Range(Cells(k& - 12, ColDepart&), Cells(k& - 12, ColDepart&)) = CATC(k&)
  Next k&
  For k& = 7 To 9
    Vehicule& = Vehicule& + CATC(k&)
  Next k&
  If Vehicule& > 0 Then S.Range(Cells(13, ColDepart&), Cells(13, ColDepart&)) = Vehicule&
  Vehicule& = 0
  For k& = 10 To 13
    S.Range(Cells(k& + 4, ColDepart&), Cells(k& + 4, ColDepart&)) = CATC(k&)
  Next k&
  S.Range(Cells(18, ColDepart&), Cells(18, ColDepart&)) = CATC(18)
  For k& = 14 To 17
    S.Range(Cells(k& + 7, ColDepart&), Cells(k& + 7, ColDepart&)) = CATC(k&)
  Next k&
  If CJoursTrajet& > 0 Then S.Range(Cells(12, ColDepart&), Cells(12, ColDepart&)) = CJoursTrajet&
  If CJoursTravail& > 0 Then S.Range(Cells(20, ColDepart&), Cells(20, ColDepart&)) = CJoursTravail&
  If CJoursSport& > 0 Then S.Range(Cells(26, ColDepart&), Cells(26, ColDepart&)) = CJoursSport&
  ColDepart& = ColDepart& + 1
  '--- M ---
  For k& = 19 To 22
    S.Range(Cells(k& - 12, ColDepart&), Cells(k& - 12, ColDepart&)) = CATM(k&)
  Next k&
  For k& = 7 To 9
    Vehicule& = Vehicule& + CATM(k&)
  Next k&
  If Vehicule& > 0 Then S.Range(Cells(13, ColDepart&), Cells(13, ColDepart&)) = Vehicule&
  Vehicule& = 0
  For k& = 10 To 13
    S.Range(Cells(k& + 4, ColDepart&), Cells(k& + 4, ColDepart&)) = CATM(k&)
  Next k&
  S.Range(Cells(18, ColDepart&), Cells(18, ColDepart&)) = CATM(18)
  For k& = 14 To 17
    S.Range(Cells(k& + 7, ColDepart&), Cells(k& + 7, ColDepart&)) = CATM(k&)
  Next k&
  If MJoursTrajet& > 0 Then S.Range(Cells(12, ColDepart&), Cells(12, ColDepart&)) = MJoursTrajet&
  If MJoursTravail& > 0 Then S.Range(Cells(20, ColDepart&), Cells(20, ColDepart&)) = MJoursTravail&
  If MJoursSport& > 0 Then S.Range(Cells(26, ColDepart&), Cells(26, ColDepart&)) = MJoursSport&
  ColDepart& = ColDepart& + 2
Next mois&
End Sub

Je me suis référé entièrement à votre exemple aussi est-il IMPERATIF de conserver la structure qui figure dans votre feuille "2007"
(respect des lignes et des colonnes ainsi que les cellules de départ des lignes et des colonnes)

Lancez la macro "Recapitulatif" et le résutat s'affiche dans une nouvelle feuille. Je vous laisse le soin de mettre
les formules qui vont bien pour les totaux, les cumuls et l'effectif mois. Ces formules pourront être présentes sur la feuille
source ("2007" dans l'exemple) et seront répercutées automatiquement sur la feuille nouvelle.

Cordialement.

PMO
Patrick Morange

 

[ROGER2327]

Re : Formules compliquées ou tableau croisé dynamique ?

Bonjour à tous

Avec des formules...​

ROGER2327
#2398