Question Excel a manqué de ressources en essayant de calculer une ou plusieurs formules


J'ai reçu le message d'erreur "Excel était à court de ressources en essayant de calculer une ou plusieurs formules" sur UN de mes ordinateurs (sur 2).

Ma feuille de calcul contient: (Dans une seule feuille de calcul, pas dans un classeur, il existe des formules dans une autre feuille de calcul)

  • 1 000 000 de formules

  • une base de tableau croisé dynamique sur 900 000 lignes de données

Lorsque je lance Excel / VBA sur l'action "Calculer Feuille" (la feuille de calcul en cours uniquement), le programme affichera le message d'erreur suivant:

Excel ran out of resources while attempting to calculate one or more formulas

sur ni je "rafraîchir" un tableau croisé dynamique (Excel ou VBA) ou "Calculer la feuille" dans (Excel ou VBA)

J'ai 2 ordinateurs:

  • les deux exécutant Windows 7 64 bits,

  • tous deux exécutant Excel 2007 32Bit,

  • Je lance Excel juste après avoir démarré Windows,

  • mon PC de développement avec 2 Go de RAM peut fonctionner sans problème,

  • un autre PC avec 6 Go de RAM a montré le ran out of resources Message d'erreur

  • Exécution du même jeu de données, même fichier Excel

Je remarque également que sur mon PC de développement, il utilise ~ 1,2 G RAM, et que le PC non fonctionnel utilise 900 Mo de RAM avant de cliquer sur l'action "Refresh" / "Calculate".

MODIFIER

L'ordinateur non fonctionnel peut gérer des données dans les 100 000 lignes de données

Mes questions:

  1. Pourquoi cela fonctionne-t-il pour l’ordinateur avec moins de mémoire mais pas celui avec plus de mémoire? (question principale)
  2. Que puis-je faire pour réduire la mémoire utilisée par Excel? (sous-question) (Autre que la suppression de données)

Toute aide est appréciée, veuillez me diriger dans la bonne direction ou donner des indices.

MODIFIER: Je pense à supprimer les formules, et déplacer la logique dans vba, et le faire en mettant en cache les données par 10 000 lignes. Mais cela ne résoudra pas mon problème si le tableau croisé "actualiser" afficherait la même erreur.


3
2018-01-02 10:04


origine


Bizarre que l'ordinateur qui ne fonctionne pas a plus RAM. Pouvez-vous vérifier qu'il y a suffisamment de mémoire libre (à la fois physique et d'échange) sur l'ordinateur en erreur? Autre que cela, je dirais d’optimiser ou de diviser la feuille si possible. 1M de formules sonne un peu pour une feuille Excel? - lc.
D'accord. Alors pourquoi déplacer les lignes vers Excel? Vous pouvez configurer votre pivot pour interroger directement Oracle. Si l'indication de non-spécification est spécifiée, je la jouerais de cette façon. - Patrick Honorez
Ou mieux encore, si vous exécutez au moins Oracle 11g, il prend en charge les requêtes PIVOT en mode natif, le travail est alors effectué côté serveur plutôt que localement et tout problème de ressource doit être évité.
@Larry, j'ai rencontré le même problème à quelques reprises et je pense généralement à la version 32 bits d'Excel. Pour ce qui est de savoir pourquoi cela fonctionne sur une machine plutôt qu’une autre ... Je dirais que c’est simplement la façon dont le système d’exploitation / installation a alloué des ressources. Les deux seules solutions que j'ai trouvées pour cela sont les suivantes: 1) Rédiger des solutions .Net qui effectuent la plus grande partie du travail, puis demander à une macro Excel d'appeler le fichier .exe shell puis importez les données déjà calculées / effectuez un travail de nettoyage minimal. 2) BEAUCOUP de jouer avec mon code pour essayer de le rendre plus efficace / moins gourmand en mémoire ... Bonne chance !!!
2 choses sur le dessus de ma tête pour vérifier. 1) la machine 6 Go a-t-elle d'autres programmes utilisant plus de mémoire? 2) Les deux versions d'Excel sont-elles exactement les mêmes (mêmes mises à jour, etc. appliquées) - Pynner


Réponses:


La raison en est probablement la fragmentation de la mémoire 32 bits sur les 2 PC (il est souvent difficile d'utiliser tous les 2 Go théoriquement disponibles).
Vous pouvez réduire la quantité de mémoire utilisée par le tableau croisé dynamique en réduisant le nombre de colonnes et / ou en réduisant la mémoire requise par certaines des colonnes (les chaînes de texte sont un bon candidat).
(Vous pouvez mesurer la quantité de mémoire utilisée par le cache pivot avec VBA PivotCache.memoryUsed)
Je suppose que vous créez le cache pivot en lisant directement les données de la requête dans le cache pivot plutôt que de placer les données de requête sur une feuille de calcul et de baser le pivot sur la feuille de calcul, ce qui utiliserait beaucoup plus de mémoire.
Vous ne dites pas ce que sont les formules> 1000000, donc je n'ai aucune suggestion d'amélioration pour eux.
Si vous souhaitez utiliser de grandes quantités de données dans des pivots Excel, vous obtiendrez de meilleurs résultats avec la version 64 bits d'Excel 2010 qui ne dispose pas de la limite de 2 Go.


2
2018-01-02 16:00



Merci pour la suggestion, je suis en train d'essayer des choses, trouvé un bon lien add-ins.com/support/... - Larry
Sur la partie des formules, je n'utilise aucune fonction Volatile intégrée ou définie par l'utilisateur, mais je pense à utiliser Selection.Calculate et à ne sélectionner que quelques lignes à la fois. - Larry


merci pour toutes les suggestions, lectures et aide. J'ai résolu le problème en

  1. Changez toutes les formules en valeurs pas à pas par ~ 100 000 lignes --- Cela réduit la mémoire stockée par Excel de 10%
  2. Suppression de toutes les données / feuilles de calcul inutiles dans le classeur (les données brutes avant le massage des données) --- Cela réduit la mémoire stockée par Excel de 40%

Et la lecture http://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm suggérer pourquoi PC 6G RAM est confronté à ce problème plutôt que 2G RAM PC. Je pense que c’est parce que le PC 6G a beaucoup d’extensions Excel qui consomment aussi de la RAM.

Merci pour les aides.


1
2018-01-03 09:25





Un possible La raison pour laquelle il pourrait manquer de ressources sur une machine de 6 Go, et non sur une machine de 2 Go, est que Windows lui-même prend plus de ressources pour gérer 6 Go de mémoire que pour gérer 2 Go de mémoire.

C'était un problème connu dans Windows 32 bits (et un problème encore plus important dans Windows 16 bits). Il est supposé être résolu sur les systèmes 64 bits, mais ...

Dans tous les cas, il est possible de tester: désactiver la RAM supplémentaire et voir si le problème disparaît.


0
2018-01-03 12:29





ancien questionnaire mais toujours valable pour toutes les versions, je rencontre des problèmes similaires lorsque les formules que j'ai utilisées étaient trop volumineuses, même si la plupart des champs étaient vides, ex: DO_CALCULATION (D1: D100000) prendra beaucoup de temps et de mémoire même si seules les cellules D1: D10 ont une valeur. Alors soyez prudent en essayant de préparer les formules pour l'expansion des données, gardez les gammes petites :)


0
2017-07-02 11:40





Il semble que vous construisiez de très grandes feuilles Excel. Les applications 32 bits ne peuvent utiliser que 2 Go de RAM, sauf si elles peuvent être configurées pour utiliser jusqu'à 3,2 Go avec un "commutateur" spécial. Ainsi, Excel 32 bits est en train de manquer de mémoire après 2 Go.

Donc peu importe si vous avez même 16 ou 32 Go de RAM. Vous avez besoin de MS Office 64 bits pour pouvoir utiliser la RAM au-dessus de 2 Go. Alors seulement, votre RAM supplémentaire de 6 Go sera réellement utile pour Excel. Sinon, il est difficile de comprendre comment exécuter avec seulement 2 Go de RAM, et très probablement, va commencer à utiliser le fichier d'échange sur le disque dur.

Une possibilité serait d'augmenter la taille du fichier de page Windows. Je ne laisse jamais Windows gérer le fichier d'échange. Je le mets à la RAM physique 1.5X et règle la taille minimale et maximale du fichier d'échange. Cela empêche la fragmentation de la RAM. Donc, si vous avez 4 Go de RAM, réglez-le sur 6144/6144 Mo min / max.


0
2017-10-05 14:41





La réponse est de mettre à niveau vers une version 64 bits d'Excel. Il peut couvrir une plage de mémoire beaucoup plus grande et est généralement juste plus rapide et le bon outil pour le travail si vous avez une grande feuille de calcul.


0
2018-05-13 22:10