Microsoft 365 Compter des jours consécutifs

[Aloha]

Bonjour,
Dans le fichier joint je comptabilise, (dans le fichier réel avec une feuille par mois), la présence (P) et le séjour en weekend (W) d'un certain nombre de personnes.
La règle: lorsque 2 W se suivent, ils sont comptés comme W consécutifs, de sorte à avoir les résultats comme je les ai introduits manuellement.

La tâche:
* arriver à distinguer les W consécutifs des autres
* ce qui rend les choses compliquées c'est le fait que certains WE sont à cheval sur 2 mois (c'est pour une deuxième étape)

Est-ce que quelqu'un parmi vous, les spécialistes, a une idée comment résoudre ce problème?
Bien à vous
Aloha

 

[Aloha]

ok, ton attitude est à respecter!
Je ne fais que chercher une solution à un problème que je n'ai pas créé moi.
Hélas, des fois je n'e m'y mets pas d'une façon optimale, et d'autres fois, comme ici, les choses manquent de clareté.

 

[job75]

Ce que les gens ne veulent pas comprendre c'est que faire des choses tordues et tarabiscotées avec Excel :

- lasse tous ceux qui pourraient aider

- conduit forcément à des échecs qu'il sera difficile de corriger ensuite.

 

[Aloha]

Oui, tu as sûrement raison, mais jusqu'ici les échecs étaient rares et une solution a pu être élaborée la plupart du temps grâce à votre aide formidable et généreuse!
Je le fais d'ailleurs en bénévole et je cherche de l'aide là où mes cacpacités sont dépassées.

 

[CISCO]

Bonsoir à tous

Je te comprend Job75.
Dans les années 70, on disait que grâce à l'informatique, on allait utiliser moins de papier. Est-ce le cas ?
Et pour ce qui est des problèmes tordus, j'ai sur ce forum, fait un fichier permettant de calculer les heures sup du personnel d'une entreprise : Il y avait 27 régimes différents. Comment pourrait on résoudre ce problème sans l'informatique ? Comment chaque employé peut contrôler son salaire pour voir s'il y a une erreur ou pas s'il n'a pas ce petit "programme". Autrement dit, au lieu de faire simple (2 ou 3 régimes d'heures sup), on fait une règle compliquée non vérifiable manuellement et "Faites confiance au comptable et à l'informatique"

Aloha, comme je ne suis pas certain d'avoir compris la nouvelle règle, pourrais-tu m'expliquer à quoi correspondent les valeurs dans les cellules AK2 et AN2 des feuilles JANVIER, FEVRIER et MARS ?

@ plus

 

[job75]

Bonsoir CISCO,

Bien sûr que l'informatique permet de résoudre des problèmes compliqués.

Mais justement il faut pour cela utiliser les méthodes les plus simples, moi c'est ce que j'essaie toujours de faire.

A+

 

[Aloha]

Re-bonsoir,
La colonne AK comprend le compteur qui additionne les W lorsqu'il n'y en a qu'un par semaine; la colonne AN héberge le deuxième compteur: tous les W du mois moins ceux de la colonne AK, ce qui revient à additionner les W qui ne se trouvent pas isolés dans une semaine, mais qui sont au moins à 2.

1 seul W dans une semaine --> compteur 1 colonne AK
La feuille ANNEE est uniquement utilisée comme aide pour arriver à les calculer (à nourrir le compteur 1), et sert aussi à tenir compte de la situation des semaines à cheval puisqu'elle regroupe les W de tous les mois

Dans le fichier exemple il s'agit des W des dates suivantes (en jaune dans ANNEE): 4.1., 15.1., 16.2., 29.2., 15.3., 17.3., 28.3.

>1W dans une semaine: tous les W de la semaine --> compteur 2 colonne AN (mais ils ne doivent donc pas être déterminés par quelque formule compliquée comme ceux du compteur 1, mais par la simple méthode de la soustraction)
Tous les W qui ne sont pas dans le compteur 1 atterrissent (par soustraction) dans le compteur 2.

Et, comme je me suis efforcé d'expliquer, les choses se compliquent encore plus les semaines à cheval sur deux mois, où il faut considérer la semaine en entier, mais seulement pour déterminer s'il y a 1 W ou plus dans la semaine;
* s'il y en a un seul (semaine du 24.2. au 1.3.: le 29.2.) il va dans le compteur 1 de février
* s'il y en a >1 (semaine du 27.1. au 2.2.: il y en a 2, un en janvier: 29.1. et un en février: 2.2.); celui du 29.1.--> compteur 2 de janvier; celui du 2.2.--> compteur 2 de février


Les chiffres dans les compteurs sont exacts parce que j'ai mis les chiffres 1 manuellement dans ANNEE colonne F; il semble donc que les formules dans les compteurs 1 des mois (colonne AK), qui vont les prendre dans ANNEE, soient exactes.

Il "ne manque que" la formule magique qui met les chiffres que j'ai mis manuellement.

Compliqué, je sais, mais telle est la règle.

J'ai repris mon fichier et j'ai ajouté des commentaires et de la couleur dans l'espoir de mieux faire comprendre.

Bonne nuit
Aloha

 

[CISCO]

Bonjour à tous

Est-ce qu'on est obligé de repasser par la feuille ANNEE dans les formules dans les colonne AK et AN ? Ne serait-ce pas plus simple en n'utilisant que les informations des colonnes E à AI de la feuille en cours, et de la feuille précédente, peut-être avec le numéro de la semaine NO.SEMAINE () pour faire a liaison entre chaque mois ?

@ plus

 

[Aloha]

Bonjour,
on n'est bien sûr pas obligé de passer par là; c'est comme cela que le système actuel a été élaboré, non sans aide externe. Je pensais que c'était plus simple comme cela puisqu'on a toujours toutes les données requises ensemble dans une colonne (par usager; comme il y en a 70 possibles et que chacun nécessite 2 colonnes, il y en a des colonnes!) et qu'on n'a pas besoin de se référer aux données de 3 feuilles (le mois actuel, le mois précédent et le mois suivant).
Mais il n'y a aucune obligation, si une autre solution est possible. Cela rendrait la feuille ANNEE superflue puisqu'elle sert juste à la comptabilisation des W.
Bonne journée
Aloha

 

[job75]

Bonjour Aloha, CISCO, le forum,

Quand les choses sont compliquées le plus simple est d'utiliser le VBA, voyez le fichier joint et cette fonction personnalisée :

Function NbWseul%(nom As Range)
Application.Volatile
Dim a, F As Worksheet, i, F1 As Worksheet, F2 As Worksheet, lig&, lig1&, lig2&, col%, compte1%, compte%
'---préparation---
a = Array("janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre")
Set F = nom.Parent 'feuille en cours
On Error Resume Next
i = Application.Match(F.Name, a, 0)
Set F1 = Sheets(a(i - 2)) 'feuille précédente
Set F2 = Sheets(a(i)) 'feuille suivante
lig = nom.Row
lig1 = Application.Match(nom, F1.[B:B], 0)
lig2 = Application.Match(nom, F2.[B:B], 0)
On Error GoTo 0
'---comptage des W de la feuille précédente---
If Weekday(F.Cells(2, 5), 2) > 1 And lig1 Then
    For col = 35 To 5 Step -1
        If F1.Cells(lig1, col) = "W" Then compte1 = compte1 + 1
        If Weekday(F1.Cells(2, col), 2) = 1 Then Exit For
    Next col
End If
'---comptage des W de la feuille en cours---
For col = 5 To 35
    If F.Cells(lig, col) = "W" Then compte = compte + 1
    If Weekday(F.Cells(2, col), 2) = 7 Then
        If compte1 = 0 And compte = 1 Then NbWseul = NbWseul + 1
        compte1 = 0
        compte = 0
    End If
Next col
If lig2 = 0 Then
    If compte = 1 Then NbWseul = NbWseul + 1
    Exit Function
End If
'---comptage des W de la feuille suivante---
If Weekday(F2.Cells(2, 5), 2) > 1 Then
    For col = 5 To 10
        If F2.Cells(lig2, col) = "W" Then compte1 = compte1 + 1
        If Weekday(F2.Cells(2, col), 2) = 7 Then
            If compte = 1 And compte1 = 0 Then NbWseul = NbWseul + 1
            Exit For
        End If
    Next col
End If
End Function

Elle est utilisée en colonne AK des feuilles des mois, voyez le fichier joint.

Edit : j'avais placé Application.Volatile sur la 3ème ligne, c'est mieux sur la 2ème ligne.

A+

 

[Aloha]

Bonsoir,

Je te crois sur parole qu'il vaut mieux utiliser VBA et je te remercie pour ton code!

J'ai testé le fichier que tu m'as renvoyé. J'ai constaté que les calculs sont corrects sauf 1 cas:
DUPONG janvier : 3 semaines avec 1 W, donc 3 W dans le compteur 1, alors que les calculs mettent 1 W dans le compteur 1 et 2 dans le compteur 2.

Ah que c'est compliqué.

Je reprends la règle, exprimée autrement:
1 W par semaine: tous les W compteur 1
>1 W par semaine (qu'elle soit située entièrement dans le mois ou une partie dans le mois précédent ou dans le mois prochain): tous les W dans le compteur 2 (mais seulement ceux situés dans le mois).

J'ai fait un tableau dans janvier et février pour illustrer.

A+
Aloha

 

[job75]

Non rien de bien compliqué : vous avez mis 2 fois un w en minuscule alors qu'il faut le mettre en majuscule.

Si vous voulez éviter ce genre d'erreur il suffit de mettre en haut de Module1 Option Compare Text

Par ailleurs dans le fichier de mon post #24, feuille FÉVRIER, j'ai mis cette formule qui va mieux en AG2 (29 février) :

=(AF2+1)*(MOIS(AF2+1)=2)

Il ne faut surtout pas de texte vide "" dans cette cellule sinon la fonction NbWseul renvoie une erreur.

 

[Aloha]

Ok, je ne savais pas qu'il y a une différence pour le code entre minuscules et majuscules. Et il ne vaut mieux pas parce que j'ai déjà remarqué que les gens n'y font pas attention.
Mais si je ne me trompe, il y a moyen de forcer les majuscules par validation, non? Sinon je vais ajouter ce bout de code.

Il ne faut surtout pas de texte vide "" dans cette cellule sinon la fonction NbWseul renvoie une erreur.

Donc si je change l'année en 2021, cela fonctionnera toujours parce que la formule reste dans la cellule, même si elle n'affiche pas de valeur?
Merci pour cette formule.

Je vais procéder à un test plus étendu; je dispose de fichiers remplis de l'année passer et je vais copier/coller des données.
A+
Et merci encore
Aloha

 

[Aloha]

Re,
mes tests n'ont révélé aucune inexactitude! Chapeau!
Et les w sont aussi acceptés maintenant.
Voilà le principal terminé!
Que ferais-je sans le forum?
Bonne nuit
Aloha

 

[job75]

Bonjour Aloha, le forum,

Le fait que la fonction NbWseul soit volatile entraîne que toutes les formules en colonne AK sont recalculées sur toutes les feuilles chaque fois qu'une cellule est modifiée n'importe où.

Avec des tableaux de 70 lignes c'est assez rapide (0,4 seconde chez moi sur 12 mois) mais si les tableaux sont plus grands ce peut être gênant.

Pour y remédier dans ce fichier (2) la fonction n'est plus volatile, par contre cette macro dans ThisWorkbook :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next 'si aucune SpecialCell
If IsDate("1/" & Sh.Name) Then Intersect(Sh.[B:B].SpecialCells(xlCellTypeFormulas).EntireRow, Sh.[AK:AK]) = "=NbWseul(RC[-35]:RC[-2])"
End Sub

entre cette formule en AK3 et cellules suivantes à chaque activation d'une feuille =NbWseul(B3:AI3)

A+

 

[Aloha]

Bonsoir,

Désolé, je ne sais pas si j'ai bien compris (la suite montre sûrement que non): donc, parce que la formule est volatile et qu'elle calcule toutes les feuilles à toute modification dans une feuille, il faut l'enlever et ce bout de macro la remet à l'activation d'une feuille uniquement dans cette feuille,, donc uniquement au besoin, pour l'enlever à nouveau lorsque je quitte la feuille?

En fait qu'appelles-tu volatile? J'ai un tableau qui porte sur tous les mois et les valeurs s'affichent; donc les formules sont toujours là.

J'ai fait un test: dans la feuille de février j'ai mis la formule =JANVIER!AK3 et il y a bien le chiffre 2 qui s'affiche, donc la formule est là.
Je n'ai donc pas bien compris.

Si c'est un problème, ne vaudrait-il pas mieux remplacer les formules par les valeurs produites lorsqu'on quitte la feuille et remettre les formules par ta macro lorsqu'on va à nouveau sur cette feuille?

J'ai besoin des valeurs de tous les mois en permanence pour divers tableaux.

D'ailleurs il n'y aura jamais plus de 70 lignes.

Pour résumer, je ne crois pas avoir vraiment bien compris.

A+
Aloha

P.S.: d'ailleurs le @ se met au début de la formule:
=@NbWseul(B3:AI3)