[formule] ttal des jrs d'une mê semaine liés à +ieurs libellés identiques d'1 colonne

Version imprimable

12/08/2007, 09h29
intactsaphir

1 pièce(s) jointe(s)

[formule] ttal des jrs d'une mê semaine liés à +ieurs libellés identiques d'1 colonne

Bonjour,
J'ai une feuille excel avec une liste de noms.
Sur chaque ligne de nom, ont été saisies une date d’entrée et une date de fin.
Un même nom peut apparaître sur plusieurs lignes et doit être daté différemment sinon c’est un doublon.
Les données sont saisies au fur et à mesure, le classeur est partagé.
Pouvez vous m'aider pour totaliser, sur chaque nom de la liste (hors doublon), le nombre de jours compris dans une même semaine et dans un même mois le cas échéant. (Voir résultat espéré de la colonne K du fichier lié).
Merci
IS
12/08/2007, 11h14
ullan

Bonjour,

Pas évident !

Une première approche :
Sous "Résultat" lister les noms sans doublons, même faisable par ordre alphabétique.
Créer un calendrier mensuel, en ligne 1 les dates, en colonne A les noms, dans lequel on indique par formule les jours en prendre en considération.
Il suffit dès lors d'additionner pour le mois ou par semaine.

Le problème à résoudre est de mettre cette formule au point.
S'il n'y a pas de meilleure solution, j'essaierai.
12/08/2007, 22h42
intactsaphir

1 pièce(s) jointe(s)

1)doublon

Bonsoir et merci à ullan de m'avoir répondu.

Citation:

1) Sous "Résultat" lister les noms sans doublons, même faisable par ordre alphabétique.

Pour afficher "doublon", car je souhaite garder la saisie telle quelle, j'ai concaténé les 3 colonnes de données de la saisie dans une nouvelle colonne(voir en exemple le fichier attaché). Ensuite je me suis servi d'une formule du fichier "ListeValidationSansDoublons.zip" téléchargé sur ce site.

Code:

=SI(E3="";"";SI(NB.SI(E$2:E3;E3)>1;"doublon";""))

J'aurais souhaité ne pas créer une colonne de plus en intégrant la concaténation dans la formule mais je n'y suis pas arrivé.
Reste la solution de la colonne Résultat...

Pièce jointe 17883

IS
13/08/2007, 08h18
ullan

Bonjour,

En pièce jointe une proposition de formule pour afficher par une MFC les doublons sans passer par une colonne supplémentaire.
13/08/2007, 13h11
intactsaphir

1 pièce(s) jointe(s)

2) proposition de matrice de calendrier mensuel

Bonjour.

1) La proposition de la formule convient. Lorsqu'on regarde la zone nom on observe les doublons "allumés".
Cependant peut-on différencier ces "doublons" par formule du fait de la saisie de l'un par rapport à l'autre?
Ils sont en double mais lequel est le doublon de l'autre?
2)Dans le fichier attaché a été créé un calendrier mensuel. Est-ce qu'il correspond à votre suggestion?
Merci de l'intérêt porté à ce cas.
Cordialement
IS
13/08/2007, 13h41
ullan

Bonjour,

Les doublons c'est comme les jumeaux, l'un ne l'est pas plus que l'autre.
En principe c'est le second qui pourrait poser problème, puisqu'il est le dernier à avoir été encodé.
Il y a moyen d'empêcher la création d'un doublon (menu Données / Vaidation), mais il faudrait définir ce qu'on entend par doublon, pour un même nom deux périodes identiques ou deux parties de périodes qui se chevauchent ?
Il y aussi moyen de compter le nombre de jours de chevauchement.

Pour le calendrier, ce n'est pas exactement cela, car je ne vois pas comment tu pourrais y inscrire les périodes d'activité, par nom et par date, afin de les compter, par semaine et par mois.
Je vais t'établir un petit exemple en partant des principes suivants :
On ne tient compte ni des weekend ni des jours fériés, il sont comptés comme jour d'activité.
Le calcul se fait par semaine, même lorsqu'une semaine est à cheval sur deux mois.
Une semaine commence le lundi et se termine le dimanche (norme ISO).

Peux-tu confirmer ?
13/08/2007, 14h10
intactsaphir

Bonjour,
Ok pour l'exemple
merci
IS

tu pourrais éventuellement utiliser ceci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
Function mcumul(plage As Range, typecum As String, an As Integer, periode As Byte) As Double
    Dim boucle As Variant
    Dim clef As String
    Dim col As Integer
    Dim deb As Variant
    Dim fin As Variant
    Dim macoll As New Collection
    Dim premjour As Variant
    Dim derjour As Variant
    Dim tempo As Double
    Dim nbcoll As Long
    nbcoll = 0
    Select Case typecum
    Case "m"
        If periode < 1 Or periode > 12 Then
            MsgBox ("mois non valide")
            Exit Function
        End If
        premjour = CLng(DateSerial(an, periode, 1))
        derjour = CLng(DateSerial(an, periode + 1, 0))
    Case "s"
        If periode < 1 Or periode > 53 Then
            MsgBox ("semaine non valide")
            Exit Function
        End If
        premjour = (DateSerial(an, 1, 1) + ((periode - 2) * 7))
        For boucle = 1 To 16
            premjour = CLng(premjour) + 1
            If Val(Format(premjour, "ww", 2, vbFirstFourDays)) = periode Then Exit For
        Next boucle
        premjour = CLng(premjour)
        derjour = premjour + 6
    Case Else
        MsgBox ("type période non valide")
        Exit Function
    End Select
    For Each boucle In plage
        col = col + 1
        Select Case col Mod 3
        Case 1
            clef = boucle.Value
        Case 2
            clef = clef & Format(boucle.Value, "yyyymmdd")
            deb = boucle.Value
        Case 0
            clef = clef & Format(boucle.Value, "yyyymmdd")
            fin = boucle.Value
            ' verif doublon
            On Error Resume Next
            macoll.Add Item:=1, key:=clef
            If macoll.Count > nbcoll Then
                ' calcul
                nbcoll = nbcoll + 1
                If fin < deb Then
                    MsgBox ("donnée non valide pour " & clef)
                    Exit Function
                End If
                tempo = tempo + Application.WorksheetFunction.Max(Application.WorksheetFunction.Min(derjour, fin) - Application.WorksheetFunction.Max(premjour, deb) + 1, 0)
            End If
        End Select
    Next boucle
    mcumul = tempo
    Set macoll = Nothing
End Function

pour obtenir le cumul d'une semaine
=mcumul(adresse plage de données;"s";année;semaine)
pour obtenir le cumul d'un mois
=mcumul(adresse plage de données;"m";année;mois sous forme 1..12)

évidemment les jours fériés et dimanche sont inclus

13/08/2007, 15h11
ullan

Re,

Si la macro de random convient, je ne fais pas l'exemple par formules.
Prière de confirmer.
13/08/2007, 18h38
intactsaphir

Bons jours à vous ullan et random!

Un grand merci de m'aider dans ce cas "pas évident".

J'ai compulsé le code de random mais malheuresement je ne connais qu'epsilon à VBA.
Je ne sais même pas comment utiliser le code livrré par random ni dans quoi le coller: un module?, une macro,? une feuille? Je ne sais absolument pas. Je n'ai pas la formation adéquate malgrè les tutos et cours mis à disposition et sur votre site et ailleurs sur le net. Par ailleurs comme je suis curieux et et désireux de connaitre je ne désepère pas d'utiliser le code vba concocté, pas dans une autre vie mais bientôt!!!
Je suis un utilisateur "primaire" d'excel et pourrais comprendre 0,5% par formules et 0,1% en vba.
Donc je pencherais plutôt pour les formules que me propose ullan lesquelles, a mon avis, me permettraient une maintenance à peu près compréhensible en l'état actuel de mes connaissances.
En fin il n'est pas dit que ces deux façons d'interprêter un problème ne fussent pas complémentaires pour la compréhension de ceux qui lisent ce post
Cordialement à vous deux.
IS
14/08/2007, 15h42
ullan

Bonjour,

Voilà le petit exemple en pièce jointe, avec un mot d'explication.
S'il y a problème, n'hésite pas.
15/08/2007, 12h36
intactsaphir

ouh lala!

Bonjour,
Merci ullan de l'apport de ton fichier.
Je vais essayer de comprendre la méthode et toutes les formules de ton labeur. Je pressens quelques difficultés!! :bug:
Bone journée.
Cordialement.
IS.
15/08/2007, 22h51
intactsaphir

1 pièce(s) jointe(s)

Etape

Bonjour,

J'ai étudié l'exemple de ullan qui fonctionne et est soumi à l'intervention manuelle du changement de mois. Je dois avouer que pour faire court j'ai reproduit les formules vers le bas des feuilles j'ai constaté un fort ralentissement d'excel. J'ai observé la présence du sablier d'excel pendant au moins une minute.L'enregistement sur le DD est poussif. Ensuite j'ai incorporé les 2600 noms tout en tenant compte du changement des formules pour la ligne 50 et 100 que je ne pouvais pas faire. Là le temps s'est profondément prolongé j'ai donc du faire une erreur quelque part. Je me laisse donc du temps pour compulser chaque type de formule de l'exemple d'ullan et utiliser son travail à bon escient.
Pour ce qui est de la fonction de random écrite en VBA, j'ai suivi les conseil d'un tuto et j'ai appliqué la fonction comme une fonction excel(xla) et ça marche! Elle est soumise, comme indiqué par random, à l'intervention manuelle du changement de mois. Je me laisse donc du temps pour l'apprécier.
Entre temps j'ai continué à chercher.
Dans la colonne Résultat du ficher attaché on observe que l'objectif du départ est, selon l'exemple restreint, relativement atteint dison à 30%:
1) cumuler par nom le nombre de jours ouvrés (-voir colonne J_o-) ou non dans un même mois, le total sur un nom vidant les les différentes lignes le composant;
2) trouver les doublons sans les effacer;
3) Il reste à solutionner pour l'instant
a) le chevauchement sur 2 mois de la ligne14.
b) Si le doublon reste la dernière occurence du nom dans la colonne, la somme des jours s'effectue sur celui-ci.
c) Par ailleurs est-il impossible de faire apparaître dans une même cellule le nombre de jours suivi du n° de la semaine comme indiqué dans la colonne Q:
=1+2+2(2)+4(3)+5(4) ou les chiffres voire les nombres exprimant les semaines sont entre parenthèses, ceci permettant avec des calculs sur les chaines de caractères de pouvoir les utiliser dans des actions à venir (par exemple le prix de la journée pour la semaine, la quinzaine ou pour le mois).

Merci pour votre aide.

Cordialement
IS
16/08/2007, 10h33
ullan

Bonjour,

La lenteur de calcul provient d'une part de la mise en forme conditionnelle sur 2400 lignes (feuille "doublon"), d'autre part des formules matricielles (feuille "janvier").

En supprimant la MFC et en créant une feuille par mois (raison pour laquelle j'avais nommé la deuxième feuille janvier), le problème devrait disparaître.
La suggestion de modifier la date en feuille "janvier" n'était que pour tester.