Discussion :

[BenjGe]

Au cours de manipulations je me suis rendu compte qu'excel limitait le nbre de formules dans la feuille.

J'ai appliqué une technique dite de "vue" sur une feuille de données importée depuis un logiciel métier: on réalise une feuille en superposition de la première avec des colonnes de formule de calcul destinées à nettoyer les colonnes en vis à vis sur l'aute feuille (suppression d'espace et autre);

Le listing importé comprenant un très grand nombre de ligne j'ai tenté une recopie de formules sur les 65000 lignes (c'était inutile). Et là suprise Excel ne recopie pas les formules jusqu'au bout. Plus fort, une fois ceci fait impossible de recopier d'autres formules dans la feuille. La recopie des formules redevient possible après suppression d'un certain nombre de lignes.

il ne m'est pas possible de fournir le classeur initial mais je peut peut être tenter de reproduire ça sur un exemple si certains le veulent.

Est ce que quelqu'un a déjà rencontré ce comportement d'Excel? Quelle est la limitation d'une feuille en nombre de formules, ou comment peut on la calculer?
Quels sont les facteurs jouant sur celle-ci (ram, type de formules, type de données,...)

Merci par avance.

[larryj]

Je n'ai pas vraiment d'idée sur la question, la réponse m'intéresse donc aussi.

En revanche, dans le cas présenté ici, peut-être que passé par un champ nommé pourrait résoudre le problème (je n'en suis pas sûr).

Faire un champ nommé faisant référence à la colonne d'origine (données stockées avec espaces, etc.) et mettre la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SUPPRESPACE(Feuil1!A1)

Ensuite, dans la colonne où l'on voulait les données "propres", il n'y aurait plus qu'à mettre le nom du champ nommé.

[Invité]

Bonsoir,

Quelle est la version d'Excel que vous utilisez. Pour les versions antérieures à 2007, vous ne pouvez pas utiliser plus de 65536 lignes.

Avez-vous vérifié que cette limite n'était pas atteinte ?

Si vous faites "Ctrl+fin" quelle est le numéro de la dernière ligne ?

Sinon, bien évidemment, il y a toujours des limites, mais cela dépend surtout de la configuration de votre PC.

[Invité]

Bonjour

Ce n'est pas seulement une limite sur les formules, mais souvent c'est un tout, nombre de feuilles, nombre de formatages différents, etc..., et le cumul de l'ensemble peut bloquer la réalisation d'ajouts.

Peux-tu nous donner également la taille de ton fichier.

Philippe

[BenjGe]

je vais tenter de reproduire un exemple et le poster lundi.
La version d'excel utilisée était alors 2003 mais je suis convaincu qu'en 2007 c'est pareil. Le blocage en recopie se faisait autour de la ligne 10000, bloquant toute autre recopie dans la feuille. Le fichier faisait il me semble quelques Mo.

[Didier Gonard]

Bonjour,

je pencherais pour le fait que ce soit lié à la mémoire disponible (ou a la technique employée), il n'y a rien à ce niveau dans les spécifications d'Excel 2003 :

La longueur des formules est limité à 1024 caractères.
La seule allusion au nombres de formules est le Nombre maximal de formules d'éléments calculés par tableau croisé dynamique qui est limité par la quantité de mémoire disponible..

Vu que nous sommes sur le forum Excel, tu fais comment pour recopier tes formules ?

Si ce n'est le cas, fait un test en vba genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub testreco()
Dim i
For i = 2 To 50000
   Cells(i, 4) = Cells(1, 4).Formula
Next i
End Sub

voir ce que ça donne ?

cordialement,

Didier

[BenjGe]

pour la recopie j'ai utilisé la poigne de recopie. Je pense que le vba n'apportera rien de plus.

Concernant le tcd, celui-ci dispose d'un cache (une sorte de cube), et ses possibilités sont donc effectivement limitées par la seule ram, mais concernant la gestion de la feuille et de ses formules je n'ai aucune info.

Comme toi je pense que la technique employée importe (formules matricielles, recherche en masse, utilisation de références nommées), mais je ne connais aucune doc donnant des détails là dessus. Est ce lié à sa gestion de la mémoire? Sûrement puisque de temps en temps il peut afficher un message d'erreur "impossible de terminer l'opération mémoire disponible insuffisante". Mais ce n'est sûrement pas le seul critère. Le nombre de prédécesseurs et de successeurs à une formule est peut être un élément important; j'imagine que c'est lui qui va déterminer la quantité d'espace mémoire à réserver pour un calcul. Mais là encore on manque d'infos sur la gestion des calculs par Excel.

Je vais m'atteler au classeur test cet après midi (ça va me prendre du temps car je dois anonymiser la bdd). Je testerai ton code de recopie à ce moment là. Une remarque, ta procédure est équivalente à ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub testreco()
   range("D2:D50000").value = Cells(1, 4).Formula
End Sub

pas besoin de boucle et c'est plus rapide vis à vis d'Excel.

[Didier Gonard]

Bonjour,

le sujet n'est pas un concours d'optimisation, mais de trouver la raison (à cet instant inexpliquée) d'un problème donné.

Donc la piste vba proposée, n'est pas une solution, mais une piste pour tester si la réaction est différente

Une remarque, ta procédure est équivalente à ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub testreco()
   range("D2:D50000").value = Cells(1, 4).Formula
End Sub

pas besoin de boucle et c'est plus rapide vis à vis d'Excel.

Voir remarque ci-dessus, l'intérêt d'une boucle dans une procédure de débogage (c'est ce dont il s'agit), est que si elle bloque, en regardant l'indice à ce moment là, on a immédiatement la donnée butoire.

Si c'est un plantage global, il suffit de faire des sauts en pas à pas.

Une fois la solution trouvée (si solution il y a), il est temps de se soucier de l'optimisation.


sinon :

puisque de temps en temps il peut afficher un message d'erreur "impossible de terminer l'opération mémoire disponible insuffisante"

pourquoi ne pas l'avoir indiqué précédemment pour optimiser les réponses ?

c'est tout pour moi.

cordialement,

Didier

[BenjGe]

autant pour moi je n'avais pas saisi l'intérêt de la boucle. J'ai parlé trop vite.

Concernant le message d'erreur "impossible de terminer l'opération car la mémoire disponible est insuffisante" il n'apparaît pas pour le classeur que je cite. Je le donnais en exemple pour appuyer le fait qu'Excel fait un usage intensif de la ram dans certains cas de figure.

Enfin j'ai obtenu une réponse à ma question gràce à Excel 2010 qui m'a indiqué lors de la recopie sur ce classeur que les fichiers 97-2003 ne supporte pas plus de 65472 formules matricielles dans une feuille. Pas testé en format 2007.

Je joint le fichier par le biais de skydrive car la pièce jointe est trop grosse pour développez.
Pour reproduire le problème, rendez vous sur la feuille stats en bas de la colonne B, tentez une recopie. Ou bien tentez de recopier vers la droite les formules matricielles.

Limite en nombre de formules

Compressé avec 7-zip.

Quelqu'un connaît il d'autres limites de ce type outre les classiques nbre de lignes, colonnes, cellules.

[Invité]

Bonjour,

Vous pouvez trouver des réponses à vos questions ici :

-pour 2003 :
http://office.microsoft.com/fr-fr/ex...992911036.aspx

-pour 2007 :
http://office.microsoft.com/fr-fr/ex...738491036.aspx

[BenjGe]

merci pour votre réponse, je n'avais pas connaissance de ces documents qui contiennent des infos très intéressantes sur les limitations d'Excel.

Cependant je n'ai rien vu sur un nombre limite de formules.

Par contre pour 2007 le document parle de 4 milliards de formules dépendant d'une cellule (sic!)

[BenjGe]

merci pour vos réponses et le temps que vous avez accordé à ce problème.

merci à ormonth. Mon souhait n'était nullement de vous froisser. Ma prétention a parlé avant mon cerveau.

merci à jaques_jean pour les documents sur les limitations d'excel, très instructif.

Si quelqu'un a d'autres infos je suis preneur.

Resolu.