XL 2013 Simplifier formule qui consomme trop de ressources

conilog

XLDnaute Nouveau
Bonjour le forum,

J'ai une formule qui consomme énormément de ressources. Quand je valide cela met prêt de 5mn à calculer (pourtant Excel est installé sur un Windows serveur 2012 R2 avec 24Go de RAM)

Le but de la formule est de connaître le nombre d'occurence différentes dans une plage en fonction d'un critère défini.

Voici la formule actuelle :

=SOMMEPROD((DETAILS!$B$2:$B$10316='Synt marges'!A21)*(1/NB.SI(DETAILS!$D$2:$D$10316;DETAILS!$D$2:$D$10316)))

Il n'y aurait pas un moyen de simplifier tout ça pour prendre moins de ressources (surtout que là je ne l'ai mis que sur une ligne mais je dois la reproduire 30 fois... Je n'ose même pas essayer :eek:

Merci de votre aide.

Cordialement,
 

pierrejean

XLDnaute Barbatruc
Re
Un essai un peu moins chronophage (chez moi 10 s contre 42 s)
Les résultats sont un peu différents parce que la colonne Date contenait des valeurs erronées ( j'ai tout passé en nombre standard)
Je crois que je vais passer la main a mon ami job75 qui excelle pour ce genre de pb
 

Pièces jointes

  • FichierZv1.xlsm
    251.1 KB · Affichages: 44

job75

XLDnaute Barbatruc
Bonsoir conilog, Pierre,

Voici une méthode simple et rapide, sans VBA.

1) Commencez par effacer vos formules et mettez le fichier en mode de calcul Automatique.

2) Dans la 1ère feuille créez une colonne auxiliaire avec cette formule en E2 à tirer vers le bas :
Code:
=SI(NB.SI(E$1:E1;B2&D2);"";B2&D2)
Elle concatène et élimine les doublons.

3) Formule en B2 de la 2ème feuille, à tirer vers le bas :
Code:
=NB.SI(DETAILS!E$2:E$8426;A2&"*")
4) Le tableau des résultats est organisé en tableau Excel avec l'option "Ligne des totaux" cochée.

Fichier joint.

Bonne fin de soirée.
 

Pièces jointes

  • FichierZ(1).xlsx
    415.2 KB · Affichages: 47

job75

XLDnaute Barbatruc
Re,

Pour mesurer les durées des calculs j'ai créé cette macro :
Code:
Sub Test()
Dim t
With Sheets("DETAILS")
  t = Timer
  .[B2] = .[B2]
  MsgBox "Validation de DETAILS!B2 => " & Format(Timer - t, "0.00 \s"), , "Durées"
  t = Timer
  .[B4200] = .[B4200]
  MsgBox "Validation de DETAILS!B4200 => " & Format(Timer - t, "0.00 \s"), , "Durées"
  t = Timer
  .[B6300] = .[B6300]
  MsgBox "Validation de DETAILS!B6300 => " & Format(Timer - t, "0.00 \s"), , "Durées"
  t = Timer
  .[B2:B8426] = .[B2:B8426].Value
  MsgBox "Copie des valeurs sur DETAILS!B2:B8426 => " & Format(Timer - t, "0.00 \s"), , "Durées"
End With
End Sub
Fichier de test joint.

Bonne nuit.
 

Pièces jointes

  • FichierZ Test durées(1).xlsm
    422.9 KB · Affichages: 87

job75

XLDnaute Barbatruc
Bonjour conilog, Pierre, le forum,

Avec cette macro utilisant le Dictionary c'est beaucoup plus rapide :
Code:
Private Sub Worksheet_Activate()
Dim t#, tablo, d As Object, i&, a, b, n&, c()
t = Timer
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
tablo = Sheets("DETAILS").[A1].CurrentRegion.Resize(, 4) 'matrice, plus rapide
With ListObjects(1) 'tableau Excel
  .ShowTotals = False
  If .ListRows.Count Then .DataBodyRange.Delete xlUp 'RAZ
  '---liste concaténée sans doublons---
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = vbTextCompare 'la casse est ignorée
  For i = 2 To UBound(tablo)
    d(tablo(i, 2) & tablo(i, 4)) = tablo(i, 2)
  Next i
  If d.Count = 0 Then Exit Sub 'si le tableau est vide
  '---comptage des items---
  a = d.items
  d.RemoveAll 'RAZ
  For i = 0 To UBound(a)
    d(a(i)) = d(a(i)) + 1
  Next
  a = d.keys: b = d.items: n = UBound(a)
  '---transposition---
  ReDim c(0 To n, 0 To 1)
  For i = 0 To n
    c(i, 0) = a(i): c(i, 1) = b(i)
  Next
  '---restitution---
  With .Range.Rows(2).Resize(n + 1)
    .Value = c
    .Sort .Columns(1), xlAscending, Header:=xlYes
  End With
  .ShowTotals = True 'option "Ligne des totaux" cochée
End With
MsgBox "Tableau mis à jour en " & Format(Timer - t, "0.00 \s") 'facultatif bien sûr
End Sub
La macro est à placer dans le code de "Synt marges" et s'exécute quand on active cette feuille.

Le Dictionary est utilisé pour 2 tâches différentes.

Fichier joint, notez que vous n'aviez pas répertorié le compte client "FIN".

Bonne journée.
 

Pièces jointes

  • FichierZ par VBA(1).xlsm
    225.7 KB · Affichages: 48
Dernière édition:

conilog

XLDnaute Nouveau
Bonjour Job75 et Pierre,


Merci beaucoup pour ces infos, et les macros (je conserve le timer qui me servira dans bien des cas, car je suis le spécialiste de la formule qui plante le serveur pendant 10 mn...)

Je vais voir quelle méthode je privilégie.

Merci encore

Bonne journée.
 

job75

XLDnaute Barbatruc
Re,

En colonne D de la feuille "DETAILS" il y a des références avec des espaces.

Cela peut provoquer des erreurs de comptage s'il y a des espaces superflus ou manquants.

Pour éliminer les erreurs il suffit de supprimer les espaces avec :
Code:
    d(Replace(tablo(i, 2) & tablo(i, 4), " ", "")) = Replace(tablo(i, 2), " ", "") 'évite les espaces parasites
Fichier joint, on voit que le Total est diminué de 3 unités, cela est dû aux références suivantes :

- ATA1401/ATA 1401 en lignes 2818-2819-2820-2821

- NOGAPUDI BL2998/NOGA PUDI BL2998 en lignes 5236-5238-5261-5262-5263

- APE100038927/APE 100038927 en lignes 2830-6233-6307.

Pour trouver ces 3 erreurs j'ai dû étudier les "keys" du 1er Dictionary.

Edit : je joins aussi le fichier par formules .xlsx.

A+
 

Pièces jointes

  • FichierZ sans espaces parasites(1).xlsx
    398.4 KB · Affichages: 65
  • FichierZ par VBA sans espaces parasites(1).xlsm
    226 KB · Affichages: 40
Dernière édition:

Statistiques des forums

Discussions
311 710
Messages
2 081 781
Membres
101 817
dernier inscrit
carvajal