XL 2016 UTILISATION SOMME SI ENS par code VBA EXCEL

Panda Tsoa

XLDnaute Nouveau
Bonjour à tous,
J’ai un petit problème sur VBA. Un fichier en attaché contient les détails des problèmes et la solution attendue car je suis nul en VBA.
Merci pour ceux qui peut consacrer leur temps pour moi.
 

Fichiers joints

James007

XLDnaute Barbatruc
Bonjour,

Dans ton fichier test ... pas de chiffres ... mais, si mes yeux ne me trompent pas ... pas de calculs ( ni somme, ni autre chose) entre tes deux feuilles ...

On dirait qu'il ne s'agit que de présentation ... donc purement cosmétique ...

Me trompe-je ?
 

Dudu2

XLDnaute Occasionnel
Bonjour,
Si je peux me permettre... et surtout tu n'indiques pas quelle donnée(s) de Données sources qui a plusieurs colonnes par date doit / doivent être copiée(s) dans Suivi. qui n'a qu'une colonne par date.
 

job75

XLDnaute Barbatruc
Bonjour Panda Tsoa, James007, Dudu2,

Voyez le fichier joint et cette macro dans le code de la 1ère feuille (clic droit sur l'onglet et Visualiser le code) :
VB:
Private Sub Worksheet_Change(ByVal R As Range)
Dim P As Range, F As Worksheet, Q As Range, lig As Variant, col As Variant
Set P = [C5].CurrentRegion
Set R = Intersect(R, P)
If R Is Nothing Then Exit Sub
Set F = Sheets("Suivi")
For Each R In R.EntireRow 'si entrées/effacements multiples
    Set Q = Intersect(R, P)
    lig = Application.Match(Q(3), F.Columns(2), 0)
    col = Application.Match(Q(1), F.Rows(2), 0)
    If IsNumeric(lig) And IsNumeric(col) Then F.Cells(lig, col) = Application.SumIfs(P.Columns(9), P.Columns(1), Q(1), P.Columns(3), Q(3))
Next
End Sub
La macro s'exécute quand on valide ou modifie des cellules du tableau source.

Pour renseigner entièrement la 2ème feuille copier-coller une colonne entière de ce tableau sur elle-même.

Edit : j'ai testé sur les 366 jours de 2020, le copier-coller d'une colonne entière prend 17 secondes chez moi, c'est acceptable.

A+
 

Fichiers joints

Dernière édition:

job75

XLDnaute Barbatruc
Tu as parfaitement raison James007 puisque normalement un employé ne pointe qu'une fois par jour.

Donc il est inutile d'utiliser SOMME.SI.ENS (SumIfs dans ma macro).

Mais les INDEX/EQUIV dans toutes les cellules de la 2ème feuille prendront quand même trop de temps de calcul sur 366 jours, donc utilisez cette macro du fichier (2) :
VB:
Private Sub Worksheet_Change(ByVal R As Range)
Dim P As Range, F As Worksheet, Q As Range, lig As Variant, col As Variant
Set P = [C5].CurrentRegion
Set R = Intersect(R, P)
If R Is Nothing Then Exit Sub
Set F = Sheets("Suivi")
For Each R In R.EntireRow 'si entrées/effacements multiples
    Set Q = Intersect(R, P)
    lig = Application.Match(Q(3), F.Columns(2), 0)
    col = Application.Match(Q(1), F.Rows(2), 0)
    If IsNumeric(lig) And IsNumeric(col) Then F.Cells(lig, col) = Q(9)
Next
End Sub
Avec 366 jours le copier-coller d'une colonne prend environ 1 seconde.
 

Fichiers joints

job75

XLDnaute Barbatruc
Si l'on veut une solution par formules on entrera en D4 de la 2ème feuille :
Code:
=RECHERCHEV($B4;DECALER('Données sources'!$E$5;EQUIV(D$2;'Données sources'!$C$6:$C$16841;0);;NB.SI('Données sources'!$C$6:$C$16841;D$2);7);7;0)
Avec 366 jours la validation d'une cellule dans l'une des feuilles prend de 3 à 4 secondes.
 

Panda Tsoa

XLDnaute Nouveau
job75, James007,Dudu2, bonjour,
Je vous remercie pour votre assistance. Vous avez raison sur l'utilisation de la formule index-equiv, Décaler.
J'ai un petit soucis car si dans l'onglet 'Suivi" on rempli des formules par employé jusqu'au 31 Décembre 2020, le traitement dans le fichier lorsqu'on saisie dans l'onglet "Données sources" devient lent et c'est ça mon soucis.
Je suis désolé car je suis presque débutant en excel les amis!
Merci à vous!
 

James007

XLDnaute Barbatruc
Bonjour à tous,

Au risque de me répéter ... pas de calculs ( ni somme, ni autre chose) entre tes deux feuilles ...
On dirait qu'il ne s'agit que d'une mise en forme ... donc purement cosmétique ...

As-tu vraiment de cette seconde feuille ?

Si tu veux éviter toutes les lourdeurs et si tu as le souci de la rapidité ...
As-tu insérer un Tableau Croisé Dynamique ?
 

job75

XLDnaute Barbatruc
Ah quoi joue-t-on ici ?

Au post #10 j'ai donné une solution VBA qui est rapide même avec 366 jours !

Il faut tester les fichiers que je dépose !
 

Panda Tsoa

XLDnaute Nouveau
Job75,
Je vous remercie pour le fichier avec macro!
J'ai encore une petite question mais je ose pour mon niveau.
Je vais réfléchir un peu et je reviendrai.
Merci beaucoup!
 

Panda Tsoa

XLDnaute Nouveau
Job75,
Ta solution marche bien. Une petite question: si on veut vider les données pour une nouvelle année, on supprime mais l'onglet "Suivi" n'est pas mis à jour.
Comment on fait?
Merci
 

job75

XLDnaute Barbatruc
Il suffit de modifier manuellement les dates en ligne 2 (vous pouvez y mettre des formules pour aller plus vite).

Pour mettre le tableau à jour je répète : copier-coller une colonne sur elle-même en 1ère feuille.
 

Panda Tsoa

XLDnaute Nouveau
Bonjour job75,
Je t'envoie ci-joint le fichier, j'ai supprimée les heures saisies dans l'onglet "Données de bases" et les données résultats dans l'onglet " Suivi" restent encore visibles.
Merci pour votre compréhension
 

Fichiers joints

job75

XLDnaute Barbatruc
Je vais répéter ça combien de fois ???
Pour mettre le tableau à jour je répète : copier-coller une colonne sur elle-même en 1ère feuille.
Dans la 1ère feuille sélectionnez la colonne C, clic droit => Copier, clic droit => Coller.

Dans le tableau de la 2ème feuille il n'y a plus que des valeurs zéro.
 

job75

XLDnaute Barbatruc
Une petite anomalie chez moi : en D4 et E4 de la 2ème feuille les tirets du format Comptabilité n'apparaissent pas.

Pour y remédier il suffit de copier la plage D5:E5 et de la coller sur D4:E4.

Je ne sais pas pourquoi il y a cette anomalie chez moi sur Excel 2019.
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas