Fichier présence entre deux dates.xlsx
Voici le fichier, trois colonnes :
1. Identifiant
2. Date et h admission format aaammjjhhmm
3.Date et h sortie format aaaammjjhhmm
Merci et désolé d'être lent :-D
Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter
Discussion :
Pièce jointe
Fichier présence entre deux dates.xlsx
Voici le fichier, trois colonnes :
1. Identifiant
2. Date et h admission format aaammjjhhmm
3.Date et h sortie format aaaammjjhhmm
Merci et désolé d'être lent :-D
RE
J'ai un peu modifié la première requête ce qui allège un poil la seconde.
J'ai aussi limité le calendrier à la dernière date de sortie (il allait jusqu'à la date du jour)
J'ai également annulé l'actualisation en arrière plan des requêtes.
Sur mon PC j'arrive à une réponse en 3 minutes environ sur les 20948 lignes.
C'est acceptable mais sur 117000 lignes pas sûr que cela passe.
Le calcul des formules sur les 20948 lignes est plus rapide mais nécessite de créer également tous les créneaux par formule aussi (automatique avec PowerQuery)
Si on veut ensuite filtrer le résultat, c'est instantané avec un résultat PowerQuery tandis qu'il y a un recalcul un peu pénible si formules.
Les 2 approches ont donc des avantages et inconvénients...
Chris
PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
----------------------------------------------------------------------------------------------
En cas de résolution, n'hésitez pas cliquer surc'est toujours apprécié...
Bonjour,
Voici dans le classeur joint un calcul de la présence par tranches horaires au moyen de formules mais avec une présentation des résultats sous forme de tableau à double entrée.
La feuille Tranches horaires se calcule en moins de 15 secondes.
Cordialement
Claude
RE
Belle illustrationEnvoyé par papouclo
. Cela complète mon exemple PQ.
Cependant...
Le cadre du tableau à double entrée est fait manuellement : (il exclut d'ailleurs 2017 alors que la source démarre en 2017). Cela allège les formules mais n'est pas dynamique.
Comme je l'ai mentionné, le calcul par formules est plus rapide mais... sauf à désactiver le calcul auto comme tu l'as fait, le filtre éventuel provoque le recalcul...
Chris
PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
----------------------------------------------------------------------------------------------
En cas de résolution, n'hésitez pas cliquer sur
Bonsoir Chris,
J'avais rempli la première colonne du tableau double entrée par formule en partant du minimum des dates d'admission jusqu'au maximum mais les résultats pour les deux lignes 2017 me semblaient tellement disproportionnés par rapport à l'année 2018 que j'ai tout recommencé à la main en me limitant à 2018.
Il y a un peu de travail pour passer d'une année à l'autre mais le gain de visibilité avec les résultats d'une même tranche horaire dans une seule colonne me semblait intéressant et j'ai rajouté un calcul de moyenne et une MFC pour renforcer ce point de vue.
Pour ce qui est de la suppression du calcul automatique, c'est peut-être un réflexe à avoir dès que l'on manipule des feuilles comportant un très grand nombre de lignes.
Mais mon choix comporte les désavantages que tu avais signalé pour l'emploi des formules.
Cordialement
Claude
Bonjour à tous,
Merci pour votre aide dans la résolution de ce problème. Merci de la découverte de la fonction PowerQry que je vais creuser désormais et merci pour la fonction NB.SI.ENS qui m'a rendu dingue!
Mon service IT m'a renvoyé le fichier avec les données traitées, voici la formule qu'ils ont utilisé:
Le fichier cible se situe sur une autre feuille que les données et est présenté comme ça:
Tranche min 0:00 1:00 02:00 03:00 .... Tranche max 0:59 1:59 02:59 03:59 .... Lundi 255 354 458 589... Mardi ... Mercredi . . .
Dans la cellule du tableau, la formule utilisée est la suivante :
=NB.SI.ENS(DATA2018!$AF:$AF;$A$2;DATA2018!$AG:$AG;$B5;DATA2018!$AH:$AH;$B5;DATA2018!$AC:$AC;"<=" & presence2018!D$4;DATA2018!$AE:$AE;">=" & presence2018!D$3)+NB.SI.ENS(DATA2018!$AF:$AF;$A$2;DATA2018!$AG:$AG;$B5;DATA2018!$AC:$AC;"<=" & D$4;DATA2018!$AH:$AH;SI($B5=7;1;$B5+1))+NB.SI.ENS(DATA2018!$AF:$AF;$A$2;DATA2018!$AG:$AG;"=" & SI($B5=1;7;$B5-1);DATA2018!$AH:$AH;$B5;DATA2018!$AE:$AE;">="&D$3)
AB = Date entrée (format date)
AC = Heure entrée (format hh:mm:ss)
AD = Date sortie (format date)
AE = h sortie (hh:mm:ss)
AF = Identifiant localisation
AG = Jour de la semaine adm
AH = jour de la semaine sortie
Donc merci à tous, c'est vmpt chouette de savoir que des experts peuvent encore aider un gros noob comme moi
PS : La requête powerqry a fonctionné, un jour et c'était bon!!
Excellente journée à vous!
Meg1
RE
A noter qu'on pouvait aussi exploiter la requête PowerQuery par un tableau croisé dynamique et obtenir un tableau similaire au tableau à double entrée de Papouclo...
Fil intéressant qui a permis de comparer des solutions sur une volume conséquent avec les avantages et inconvénients de chacune
Chris
PowerQuery existe depuis plus de 13 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
----------------------------------------------------------------------------------------------
En cas de résolution, n'hésitez pas cliquer sur
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Répondre avec citation
Partager