XL 2016 Inclure une formule dans macro

Thieum-novice

XLDnaute Nouveau
Bonjour,
Je souhaiterai inclure une formule (fournit par un membre de ce forum) dans ma macro mais quand je la lance elle me renvoie '1004' Erreur définie par l'application...
J'ai, sur deux onglets nommés "Début" et "fin", des adresses horodatées (en jour et en heure). La formule ci dessous me permet de récupérer l'heure de fin et la mettre à côté de l'heure de début lorsque l'adresse est identique.

La formule : =SIERREUR(INDEX(T_Fins[Heure_D];AGREGAT(15;6;(LIGNE(T_Fins[Heure_D])-1)/(T_Fins[Adresses]=T_Débuts[@Adresses])/(T_Fins[Date]=T_Débuts[@Date]);1));0)

Je ne sais pas comment déclarer les variables qui lui sont liées. Merci pour votre aide. Je joins le fichier.

L'objectif serait d'avoir l'adresse avec son heure de début et de fin avec la même date et calculer son écart.

Je ne sais pas si c'est très clair mais avec le fichier ça rendra mon texte un peu plus lisible ! 🙂

Merci
 

Pièces jointes

  • ThieummacroTest.xlsm
    30.7 KB · Affichages: 31

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Thieum-novice,
Votre fichier me pose problème.
Votre formule semble adresser un tableau structuré ( T_Fins[Heure_D] Tableau T_Fins, Colonne Heure_D ), or votre fichier n'a que des tableaux "normaux".
On reconnait le type de tableau dans le Gestionnaire de noms par le symbole de tableau :
1619853044321.png

Dans votre fichier les tableaux sont déclarés non structurés :
1619853091146.png


Impossible d'aller plus loin, mon vieux 2007 n'a pas la fonction AGREGAT. :)
La solution est peut être de mettre vos tableaux en structurés.
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

De plus il n'y a qu'une 'Adresse' de début qui corresponde à une 'Adresse' de fin (fac162ba5ba53804197f620f7b954f9d) et ce sans correspondance de date.
Les formules ne retournerons rien.

Puisque sous 2016, je préparais le traitement par power query des données brutes, afin que vous puissiez vous passer de macro.

Cordialement
 

Thieum-novice

XLDnaute Nouveau
Bonjour,

De plus il n'y a qu'une 'Adresse' de début qui corresponde à une 'Adresse' de fin (fac162ba5ba53804197f620f7b954f9d) et ce sans correspondance de date.
Les formules ne retournerons rien.

Puisque sous 2016, je préparais le traitement par power query des données brutes, afin que vous puissiez vous passer de macro.

Cordialement
Bonjour Roblochon,

Oui, merci du retour, effectivement je m'aperçois que dans mon exemple il n'y a qu'une adresse qui est commun dans les deux onglets et en plus sans correspondance de date.... Désolé, j'ai voulu refaire au "propre" un tableau pour le reposter dans un nouveau message.
Je mets ici le même classeur modifié prenant en compte des adresses communes dans les deux onglets avec jour identique.
En effet, lors de ma première demande je pensais qu'avec une formule plus simple j'allais m’en sortir pour l'inclure dans ma macro mais avec ta proposition je crois être dépassé. Il faut absolument que j'automatise cette procédure car j'ai bcp de fichiers à exploiter.
Bien cordialement,
 

Pièces jointes

  • ThieummacroTest.xlsm
    30.8 KB · Affichages: 4

Thieum-novice

XLDnaute Nouveau
Bonjour Thieum-novice,
Votre fichier me pose problème.
Votre formule semble adresser un tableau structuré ( T_Fins[Heure_D] Tableau T_Fins, Colonne Heure_D ), or votre fichier n'a que des tableaux "normaux".
On reconnait le type de tableau dans le Gestionnaire de noms par le symbole de tableau :
Regarde la pièce jointe 1104152
Dans votre fichier les tableaux sont déclarés non structurés :
Regarde la pièce jointe 1104153

Impossible d'aller plus loin, mon vieux 2007 n'a pas la fonction AGREGAT. :)
La solution est peut être de mettre vos tableaux en structurés.
Bonjour Sylvanu et merci de ton intérêt pour mon problème.
Effectivement, Roblochon m'avait informé qu'il avait dû définir un tableau Structuré. Je comprends que dans ma Macro lorsque je mets :

Range("B2:E1048576").Name = "T_Débuts" 'défini le nom de la plage pour la formule
Range("B2:E1048576").Name = "T_Fins" 'défini le nom de la plage pour la formule

ça ne définit pas la "zone" comme tableau structuré comme tu me l'as montré.Je ne connaissais pas cette nuance....

PS : les autres noms "TABL" et "Tableau_Rech" c'étaient des tests !
Bien cordialement,
 

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

Vos adresses MAC sont - elles issues des colonnes 'Données brutes' ? Si oui, ligne 13 de Fin, pourquoi ne correspond-elle pas à la donnée brute correspondantes ?

Si je vous pose cette question, c'est pour vous préparer une solution automatique par Power Query qui pourrait même aller chercher vos données dans les différents fichiers (.csv je suppose) et vous pondre un tableau tout prêt. Mais pour ça il faut être rigoureux sur les données.

Si vous avez des fichiers csv disponibles, postez les.

Cordialement
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

voici une proposition Power query à partir de vos données telles qu'elles sont (Feuille Résultat), J'ai tout de même 'normalisé' les noms de colonnes.

Sachez qu'il est possible de traiter directement à partir des fichiers et de ne retenir que les colonnes dont vous auriez besoin.

Lorsque le contenu d'un ou de l'autre tableau change, click-droit dans une cellule du tableau Résultat et 'Actualiser'

Cordialement
 

Pièces jointes

  • ThieummacroTest.xlsm
    43.2 KB · Affichages: 5

Thieum-novice

XLDnaute Nouveau
Re,
oui les adresses viennent des données brutes.
Pour les tests j'ai fait des copier/coller d'adresses del'onglet début vers l'onglet fin sans prendre en compte les données brutes car c'est un échainement de plusieurs Macro.
- la 1ere je dézip mes fichiers
- la 2eme j'ouvre mes fichiers txt et je mets en forme mes données dans un fichier Excel avec les 2 onglets "Début" "Fin".
et la 3 ème je voulais faire l'analyse en créant un onglet analyse.

Je peux fournir qqles fichiers txt mais il y en a beaucoup pour mettre en cohérence une adresse début et fin...
Je ne connais pas P. Quéry il va falloir que je regarde cela.
PS : je n'ai pas encore regardé votre prosition.
D'avance merci.
 

Thieum-novice

XLDnaute Nouveau
exemple de fichier mais dézippé car le forum ne prend pas le format BZ2
PS : les info sont anonymisées 👍

sinon j'ai un peu regardé sur le net ce matin est ce que ce type de code pourrait (en l'adaptant bien sûr) me permettre de faire fonctionner votre formule dans la macro merci
extrait :
Dim TableauStruct AsListObject

Set TableauStrcut = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=T_Débuts, XlListObjectHasHeaders:=xlYes)

Sheets("Début").Activate
Range("B2:E1048576").Name = "T_Débuts"

Range("K2").FormulaLocal = "=SIERREUR ....etc....
 

Pièces jointes

  • exemple.txt
    961.7 KB · Affichages: 2

Hasco

XLDnaute Barbatruc
Repose en paix
inon j'ai un peu regardé sur le net ce matin est ce que ce type de code pourrait (en l'adaptant bien sûr)
Re,

Quel type de code ?
Si on va jusqu'au bout de Power Query, vous n'aurez plus besoin de vos macros ! Sauf celle qui dézippe vos fichier.

Votre fichier Exempe.txt est-il un fichier de début, de fin ou des deux ? Et dans ce dernier cas comment sépare-t-on les heures de début et de fin ?

Pour avancer il serait bon que vous mettiez plusieurs fichiers de données.

Cordialement
 

Thieum-novice

XLDnaute Nouveau
Re,
le fichier texte que je vous ai joint c'est un exemple de fichier avec les adresses de début. Pour info, j'ai 30 fichiers texte pour les adresses début et autant pour les adresses fin...
J'ai essayé de regarder P. Query, et je pense que je vais m'y interréssé car effectivement j'ai l'impression qu'on peut traiter des données avec tableaux complexes..
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Voilà qui est fait à partir des fichiers donnés plus haut.

J'ai fait selon ce que je comprenais de votre problème, notamment concernant les doublons sur 'Adresses MAC' et 'Date'

La requête de transformation de fichier supprime les doublons sur 'Adresse MAC' et 'DTZ' ( pour DateTimeZone Ou GMT ) ce qui conserve plus de lignes et donc produit peut-être des résultats différents.

Dans vos deux fichiers, vous avez beaucoup de lignes non correspondantes, et des lignes dont les heures de fin sont antérieures aux heures de début (voir à partir de la ligne 3570 de la feuille résultat ).

Alors si les résultats obtenus ne sont pas ceux que vous attendiez, réfléchissez à ces histoires de doublons et demandez vous quelles sont les données pertinentes pour ce que vous désirez.

Les tableaux 'Debut' et 'Fin' ne sont pas affichés dans des feuilles. Si vous voulez les voir, Dans le volet Requêtes et connexions, click-droit sur les noms RQ_Debut et RQ_Fin, chargez dans ... sélectionnez les options voulues dans la fenêtre qui s'ouvre.

Le fichier PQ_Requetes.xlsm traiter l'ensemble des fichiers d'un dossier (peut être long).

Le fichier PA_SimpleFichier.xlsm ne traite que les deux fichiers donnés plus haut.

Commencez par ouvrir et étudier ce dernier, dont les étapes sont plus simple à suivre.

Et enfin votre fichier en retour (zippé), mêlant PowerQuery et macro (pour rajouter la formule de récupération des heures de fins et la formule de calcul de la durée)

Je ne remets pas les deux fichiers textes.




Cordialement
 

Pièces jointes

  • PQ_SimpleFichier.xlsm
    543.9 KB · Affichages: 2
  • PQ_Requetes.xlsm
    631.8 KB · Affichages: 2
  • ThieummacroTest.zip
    888 KB · Affichages: 2
Dernière édition:

Discussions similaires