Discussion :

[villegente]

Bonjour,

J'essai de produire des statistiques sur l'utilisation d'une salle informatique, mais mes compétences sont pour le moment un peu trop juste.
J'ai regardé du côté de Excel et Google Data Studio, sans succès.

Mon besoin est de faire ressortir combien de fois une classe a utilisée la salle informatique de son site sur des périodes définies.

Pour ce faire, j'ai les informations ci-dessous :

un fichier format Excel avec :
- 1 onglet "logs" contenant id, computername, username, datein, dateout,duree,ecole, type.
- 1 onglet periode" contenant la periode de date (nom periode, date debut, date fin et nombre de creneaux theorique disponible).
Ce fichier est mi à jour une à plusieurs fois par mois afin d'intégrer les nouvelles utiliations des ordinateurs.

La règle établie consiste à considérer que la salle informatique est utilisée une fois par une classe si au moins 5 sessions sont ouvertes avec le même compte, sur la même heure, sur les ordinateurs appartenant à "salle info" avec une durée de session d'au moins 10 minutes.

Pour le moment, je ne parviens pas à retranscrire cette donnée sous Excel. Pouvez-vous m'aider ?
Ci-joint un fichier avec un jeu de données pour être concret. SAMPLE_LOGS.xlsx

Merci,

[QuestVba]

Salut,
Je ne pense pas avoir tout compris :

La règle établie consiste à considérer que la salle informatique est utilisée une fois par une classe si au moins 5 sessions sont ouvertes avec le même compte, sur la même heure, sur les ordinateurs appartenant à "salle info" avec une durée de session d'au moins 10 minutes.

Mes questions :

• Utilisation par une classe : dans toutes les données que tu mentionnes, jamais n'apparaît la classe ? Comment la détecter ?
• Avec le même compte : c'est l'ID ou l'USERNAME ?
• Sur la même heure : de 00' à 59' ? Elles débutent toujours à 00' ? La même journée ? Et si la session n'est pas fermée ?

[villegente]

Merci pour ce premier retour aussi rapide. Effectivement, j'ai manqué de précision.

La classe peu être identifiée par le username.
La classe de CE1 se connecte avec le username ce1, la classe CE2a avec le username ce2a, ... De fait, si 8 élèves de la classe CE1 se connectent à 8 ordinateurs de la salle informatique, le fichier de logs montrera 8 ouvertures de sessions avec le username "ce1", sur 8 ordinateurs différents dans la même heure.

La colonne ID correspond au numéro de l'enregistrement dans le fichier de logs. Ce numéro est unique.

Pour le moment, une séance en salle informatique dure généralement 45mn. On considère 1h afin de prendre en compte l'entrée des élèves, le démarrage de la séance et la fin de séance.

Les élèves et enseignants sont invités à fermer leurs sessions en partant. Par sécurité, une GPO ferme les sessions inactive depuis plus de 45mn dans la salle informatique.

[Pierre Dumas]

Bonjour

Je vous propose en [ATTACH]PJ le classeur modifié[/ATTACH].

Dans votre tableau, j'ai rajouté quelques colonnes (mais je pense que l'on peut faire mieux ; je ne me sens pas en forme aujourd'hui) :
- "durée2" pour avoir la valeur de la colonne durée en numérique
- "jour in" pour extraire le jour de début (même chose pour "jour out")
- "dec in" pour extraire la partie décimale correspondant à l'heure pour le début (même chose avec "dec out")
- "test" qui compte combien de fois on trouve le username de cette ligne parmi les lignes, pour le type "SALLE INFO", avec le même "jour out" et le même "jour in", avec un "dec in" inférieur au "dec out" et un "dec out" supérieur au "dec in"

Voici l'image correspondante à ce code : =NB.SI.ENS([username];[@username];[type];"SALLE INFO";[durée2];">=10";[Jour in];[@[Jour in]];[Jour out];[@[Jour out]]; [dec in];"<="&[@[dec out]];[dec out];">="&[@[dec in]])



On peut faire un TCD sur ce tableau structuré, mettre en ligne les username, en colonne les "jour in" et en valeurs il faut compter les tests.

On peut modifier encore un peu la colonne test pour améliorer la lisibilité.

En espérant que cela convienne.

Bonne journée

Pierre Dumas

[villegente]

Merci pour ce bon coup de pouce.

J'ai essayé de traduire la fin de la notion d'utilisation de la salle informatique, sans grand succès. A savoir, "... la salle informatique est utilisée une fois par une classe si au moins 5 sessions sont ouvertes ..."
Pour cela, j'ai ajouté la colonne "utilisation", qui contient la date et l'heure au format AAAAMMDD_HH lorsqu'il y'a eu plus de 5 session ouvertes avec le même compte à la même heure.

Voila se que cela donne.



On vois que la classe ce2cm1 a ouvert plus de 5 sessions en simultanée uniquement le 18/03. On considère donc que cette classe a utilisé 1 seul fois la salle informatique.
Je pensais pouvoir faire ressortir cela avec un TCD, mais sans succès. Le résultat que j'obtiens est systématiquement 13 (nombre d'ouvertures de session avec ce username sur la plage de date), au lieu de 1




J'ai aussi un petit problème avec la colonne "test".
Il est bien indiqué qu'il y'a eu 8 sessions ouvertes avec le même username sur la même heure, mais une des cellules indique 8 alors que cette ligne à une durée de session à 0 (moins d'1 minute).

[Pierre Dumas]

Bonjour

J'ai transpiré une bonne fois, mais je crois y être arrivé. Cf le fichier SAMPLE_LOGS.xlsx

Ce qui m'a surtout gêné, c'est votre dernière phrase, pour les lignes qui ont une durée inférieure à 10 et qui s'affichent quand même. D'ailleurs, on avait le même problème pour des lignes qui n'avaient pas la mention "SALLE INFO".

J'ai finalement changé le fusil d'épaule en faisant deux colonnes supplémentaires :
- une où l'on concatène Username, école et jour in (je n'ai pas tenu compte des heures car il m'a semblé qu'une classe ne venait jamais deux fois dans la même journée) UNIQUEMENT pour les SALLE INFO et les durées supérieures à 10. Les autres lignes sont misent à 0 : =SI(ET([@durée2]>10;[@type]="SALLE INFO");[@username]&[@ecole]&[@[Jour in]];0)
- une pour les lignes où si la concaténation est à zéro, on laisse à zéro, sinon on divise 1 par le nombre de valeurs de concaténation identique : =SI([@concat]=0;0;1/NB.SI.ENS([concat];[@concat]))
J'effectue cette division, pour qu'ensuite dans le TCD, je puisse additionner les cellules et tomber sur 1.



Il ne reste qu'à faire un TCD classique comme vous l'aviez fait en faisant une somme et non un compte.

En espérant que cela vous aide

Bonne journée

Pierre Dumas

[villegente]

Bonjour,

Il me semble que cela ne fonctionne pas, car la contrainte sur le nombre de sessions ouvertes n'est plus prise en compte.

Dans la capture ci-dessous, le rectangle mauve montre une utilisation que nous considérons comme une utilisation de la salle informatique.
Les rectangles rouge montrent des cas considéré comme une utilisation de la salle alors que cela ne devrait pas. Il n'y a pas au moins 5 sessions ouvertes avec le même username.




J'ai essayé de modifier la formule contenu de concat de la manière ci-dessous, mais j'obtiens une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ET([@durée2]>10;[@type]="SALLE INFO";(NB.SI.ENS([username];[@username];[ecole];[@ecole];[Jour in];[@[Jour in]];[Jour out];[@[Jour out]];[dec in];"<="&[@[dec out]];[dec out];">="&[@[dec in]]))&">=5");[@username]&[@ecole]&[@[Jour in]];0)

Merci de votre aide,

[Pierre Dumas]

Rebonjour

Effectivement, j'avais oublié cette contrainte
Pour la prendre en compte, je propose de ne pas toucher à la colonne de concaténation. Par contre, je vous propose de modifier la formule de la colonne test suivant ce code : =SI(OU([@concat]=0;NB.SI.ENS([concat];[@concat])<=5);0;1/NB.SI.ENS([concat];[@concat]))
Cela indique que si ou concat =0 ou le nombre de session est inférieur (ou égal à 5), alors on inscrit 0; sinon on divise 1 par le nombre de sessions.

En espérant que cela rapproche encore un peu plus du but

Belle journée

Pierre Dumas

[villegente]

Cette solution semble bien fonctionner.
Seul petit hic et que mon fichier de travail contient déjà un peu plus de 92 000 lignes. Du coup, le moindre filtre ou calcul prends du temps. Je vais chercher comment optimiser cela. Actuellement, toutes mes données proviennent d'une base MySQL. Je vais essayer de l'interroger en direct.