XL 2016 retranscrire des périodes (date début-date de fin) sur un calendrier

[OA29]

 

[mapomme]

Bonjour @OA29 ,

Essayez:

=REPT("absent";SOMMEPROD(((E2>=A$2:A$999)*(E2<=B$2:B$999))))

edit : bonsoir @Hasco

 

[Hasco]

Bonjour,

Deux propositions par power query (intégré à votre version).
Je ne sais quelle requête sera la plus rapide sur de grands volumes de données.
Si vous les testez, merci de nous signaler laquelle est la plus rapide des deux requêtes.

[aparté]
Hello @mapomme
[/aparté]

 

[OA29]

Merci à vous deux pour vos réponses.

@ mapomme : ça fonctionne même si, pour le moment, je ne comprends pas vraiment la formule.

@ Hasco : je n'ai pas réussi à faire fonctionner la proposition et donc impossible de comparer les deux options.

@ tous : j'avais oublié une subtilité sur les périodes : la dernière peut être en cours, c'est à dire qu'il y a une date de début mais pas de date de fin ; la date de fin à prendre en compte serait alors celle du jour (aujourd'hui()) afin de tenir le calendrier à jour dans le suivi des absences.

encore merci pour vos retours,
bonne soirée

 

[goube]

Bonsoir à tous,

@mapomme ,

Question de béotien,
Quelle est l'utilité des signes -- dans la formule SOMMEPROD et dans quel cas l'utiliser ?
D'avance merci,
Bonne continuation.

 

[mapomme]

Re,

@ tous : j'avais oublié une subtilité sur les périodes : la dernière peut être en cours, c'est à dire qu'il y a une date de début mais pas de date de fin ; la date de fin à prendre en compte serait alors celle du jour (aujourd'hui()) afin de tenir le calendrier à jour dans le suivi des absences.

Nouvelle formule en G2 à copier vers le bas :

=REPT("absent";SOMMEPROD(--((E2>=A$2:A$999)*(E2<=SI((A$2:A$999<>"")*(B$2:B$999="")>0;AUJOURDHUI();B$2:B$999))))>0)

Voir fichier joint.

 

[chris]

Bonjour à tous

@ Hasco : je n'ai pas réussi à faire fonctionner la proposition et donc impossible de comparer les deux options.

Mais encore ? As-tu actualisé ?

Si tu as 2016 il n'y a aucune raison pour ce cela ne fonctionne pas

Le fichier modifié avec une ligne sans date de fin

 

[mapomme]

Bonjour @goube ,

Quelle est l'utilité des signes -- dans la formule SOMMEPROD et dans quel cas l'utiliser ?

C'est très bien vu car dans mes formules le "--" est inutile.
C'est une question d'habitude. Et comme on dit: "ça ne mange de pain".

Quand l'expression entre les parenthèses de SOMMEPROD ne renvoie que des VRAI ou FAUX, SOMMEPPROD ne sait pas faire la somme. SOMMEPROD renvoie 0. Pour que SOMMEPROD soit correcte il faut transformer les VRAI et FAUX en nombres. On peut le faire en prenant deux fois leur opposé (--), en les multipliant par 1 (*1) ou encore en utilisant la fonction N().

Dans mes précédentes formules, les termes de la SOMMEPROD sont des produits de deux valeurs booléennes. Le produit de deux valeurs booléennes est converti automatiquement par excel en nombres (0 ou 1). Donc les "--" sont inutiles.

Voir illustration dans le fichier joint.

 

[Amilo]

Bonjour à tous,
Une autre proposition avec Power query,

Edit : comme je ne suis pas certain d'avoir compris, je mets 2 résultats différents (voir "Test 1" et "Test 2")

Cordialement

 

[goube]

re,

@mapomme,

Merci pour l'explication, j'en prends bonne note.
Cordialement.

 

[Hasco]

@ Hasco : je n'ai pas réussi à faire fonctionner la proposition et donc impossible de comparer les deux options.

Bonjour,

Oui, j'aurais du vous dire qu'il fallait actualiser les requêtes lorsque les données de l'onglet 'Datas' changent.
Merci @chris d'avoir parfaitement répondu.

Bon dimanche

 

[job75]

Bonjour le fil, le forum,

Pour faire bon poids voyez cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, tabP, Q As Range, tabQ, datmin&, datmax&, d As Object, i&, deb&, fin&, dat
Set P = [A1].CurrentRegion.Resize(, 2): tabP = P.Value2 'matrice, plus rapide
Set Q = [E1].CurrentRegion.Resize(, 2): tabQ = Q.Value2 'matrice, plus rapide
datmin = Application.Min(Q.Columns(1))
datmax = Application.Max(Q.Columns(1))
'---mémorise les dates du tableau P---
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tabP)
    deb = Val(tabP(i, 1))
    If deb > 0 Then
        deb = IIf(deb > datmin, deb, datmin)
        fin = Val(tabP(i, 2))
        If fin = 0 Then fin = Date
        fin = IIf(fin < datmax, fin, datmax)
        For dat = deb To fin
            d(dat) = "absent"
        Next dat
    End If
Next i
'---remplissage du tableau Q---
For i = 2 To UBound(tabQ)
    tabQ(i, 2) = d(tabQ(i, 1))
Next i
'---restitution---
Application.EnableEvents = False 'désactive les évènements
Q = tabQ
Application.EnableEvents = True 'réactive les évènements
End Sub

Elle se déclenche automatiquement quand on modifie ou valide une cellule quelconque.

A+

 

[job75]

Comparaison des durées des calculs :

- formules matricielles du post #6 => 3,2 millièmes de seconde, pour cela j'ai rendu les formules volatiles

- calcul du tableau VBA du post #12 => 0,45 millième de seconde.

La macro est rapide car elle utilise des tableaux VBA et le Dictionary.

 

[job75]

J'ai testé le post #7 de chris avec les mêmes données.

ThisWorkbook.RefreshAll s'exécute en 22 millièmes de seconde pour chacun des 2 tableaux.

 

[OA29]

Bonjour à tous


Mais encore ? As-tu actualisé ?

Si tu as 2016 il n'y a aucune raison pour ce cela ne fonctionne pas

Le fichier modifié avec une ligne sans date de fin

ça fonctionne quand on actualise les données.
cependant, je ne trouve aucune formule dans le fichier.
comment tester cette solution dans le document de travail ?
bonne soirée