XL 2013 Simplifier formule qui consomme trop de ressources

[conilog]

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 😱

Merci de votre aide.

Cordialement,

 

[pierrejean]

Bonjour conilog
Il est fort possible que le problème soit soluble via Vba
Si tu n'y est pas hostile poste un petit fichier exemple avec des données non confidentielles et on pourra te faire une proposition

 

[conilog]

Bonjour pierrejean

Non aucun problème pour du VBA. Voici un petit fichier test.

Merci d'avance de ton aide

 

[pierrejean]

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

 

[job75]

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 :

=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 :

=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.

 

[job75]

Re,

Pour mesurer les durées des calculs j'ai créé cette macro :

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.

 

[job75]

Bonjour conilog, Pierre, le forum,

Avec cette macro utilisant le Dictionary c'est beaucoup plus rapide :

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.

 

[conilog]

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]

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 :

    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+

 

[conilog]

Re,

Merci Job75 et bien vu pour les espaces ! je modifie la macro avec ce nouveau paramètre.

bonne journée

 

[job75]

Bienvenue Johanna31DZRR mais vous n'avez pas lu la Charte du forum, ici on commence par dire BONJOUR.

 

[Johanna31DZRR]

Bienvenue Johanna31DZRR mais vous n'avez pas lu la Charte du forum, ici on commence par dire BONJOUR.

Excusez moi, j'ai dit bonjour! Je me suis juste trompée de discussion...