Discussion :

[Gandorf]

Bonjour à tous.
Voila, j'ai un fichier (xlsm) qui comporte plusieurs formules sur certaines colonnes (sur plusieurs onglets également).
Ces formules sont "étirées" vers le bas, afin qu'elles fonctionnent en relation avec les cellules correspondantes en fonction de la longueur du fichier (nombre de lignes) qui est variable.
Le problème, c'est que cela alourdi considérablement le fichier (c'est peu dire) de "tirer" ces formules sur 2500 lignes ou plus.
L'idée serait donc de remplacer ces formules dans les cellules par un code dans les feuilles. Dans mon fichier, j'ai une colonne qui est déjà traitée de cette façon, mais je suis bien incapable de l'adapter aux autres colonnes.
Comme il est d'usage sur ce forum, j'attends une sollicitation éventuelle pour poster mon fichier exemple, qui serait sans doute plus parlant.
Merci de votre intérêt,

Cordialement,

[Philippe Tulliez]

Bonjour,

L'idée serait donc de remplacer ces formules dans les cellules par un code dans les feuilles. Dans mon fichier, j'ai une colonne qui est déjà traitée de cette façon, mais je suis bien incapable de l'adapter aux autres colonnes.

Ce qui serait plus parlant, ce serait de publier le code que tu as déjà réaliser puisque tu dis avoir déjà réaliser cela sur une feuille. Ensuite tu peux expliquer pourquoi tu ne sais pas l'adapter pour les autres feuilles.
Ceci dit tirer les formules sur 2500 lignes, ne dois pas être insurmontable.
As-tu essayé le double clic où connais-tu l'outil atteindre (touche F5) ?
Exemple :
Tu te places sur C2, tu appuies sur F5, dans le TextBox référence de la boite de dialogue Atteindre, tu tapes C2501 et tu cliques sur OK en gardant le doigt sur la touche Maj/Shift la sélection est faites sur les 2500 lignes.
Ensuite tu tapes la formule et tu confirmes par Ctrl + Enter. La formule est copiée sur toutes les lignes sélectionnées.

[Gandorf]

Bonjour à tous, bonjour corona et merci de ton intérêt.
Voici le code (qui ne vient pas de moi, merci mapomme et JCGL) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangedCells, xcell As Range
 
Application.EnableEvents = False
Application.ScreenUpdating = False
'  Mettre en colonne A le jour (jjj) si changement(s) en colonne B
If Not Intersect(Target, Range("B:B")) Is Nothing Then
   Set ChangedCells = Intersect(Target, Range("B:B"))
   For Each xcell In ChangedCells
 
      xcell.Offset(0, -1) = Left(Format(xcell, "ddd"), 3)
   Next xcell
End If
 
Application.EnableEvents = True
End Sub

Sinon, j'ai dû mal m'expliquer. Je sais bien sûr "étirer" une formule, mon souhait est de ne pas avoir à le faire pour 2 raisons:
Le fichier étant destiné à servir à d'autres personnes, il ne faut pas qu'elles aient elles-même à tirer ces formules.
Si je les étire "à l'avance", la taille du fichier explose (il passe de 400 ko à 7 Mo).
Un exemple d'une de mes formules "à traiter" (j'en ai 38 en tout !!) :

Code :

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

=SIERREUR(GAUCHE(INDEX('échantillon daté'!$K$1:$AD$1;EQUIV($A2;DECALER('échantillon daté'!$K$1:$AD$1;EQUIV($D2;'échantillon daté'!$C$2:$C$10999;0););0));1)&(NBVAL(DECALER('échantillon daté'!$K$1:$AD$1;EQUIV(Plannings!$D2;'échantillon daté'!$C$2:$C$10999;0);)));"")

[Philippe Tulliez]

Bonjour,

Le fichier étant destiné à servir à d'autres personnes, il ne faut pas qu'elles aient elles-même à tirer ces formules.

Pour palier ce problème, il suffit que tu transformes tes cellules en tableau (Commande Tableau de l'onglet [Insertion] du groupe Tableaux).
Ainsi dès que l'on entre une valeur dans une cellule d'une nouvelle ligne, toutes les formules de cette ligne sont copiées.

[Gandorf]

Rebonjour corona, merci de ton attention.
Je dois rater quelque chose, quand je converti en tableau, outre le fait que cela me supprime toutes mes MFC, ce qui est très génant par ailleurs, et bien les formules ne s'appliquent pas lorsque je rajoute des données sur des lignes supplémentaires. J'ai essayé en définissant le tableau sur les colonnes complètes avec les formules saisies uniquement sur les lignes 2 et 3 (1 étant les entêtes), sans succès, j'ai essayé ensuite en définissant le tableau sur les seules 3 premières lignes, sans plus de succès.
Les seuls cas ou cela "fonctionne", c'est soit quand j'insère des lignes au milieu du tableau défini, ce qui ne me sert à rien, je n'insère pas, je "colle" ou vais chercher (par macro). Ou alors, si les formules sont également tirées à l'intérieur du tableau jusqu'en bas (2500 à 3000 lignes), ce qui finalement revient au même que la version initiale, donc inutile.
Toutefois, j'ai entendu parler de la fonction "DECALER", sans la maitriser, je me dit que je peux peut-être définir la taille de mes tableaux avec cette fonction ?
Si vous me dites que c'est une piste à explorer, je creuserais en ce sens sur le net...
Sinon, je rate peut-être une simple manipulation....
Merci encore !

[Philippe Tulliez]

Bonsoir,
C'est très curieux ce que tu décris parce-que je te confirme que cela fonctionne bien tel que je te l'ai signalé.
Est-tu passé par l'onglet [Insertion] groupe Tableaux Tableau ?
Ensuite tu as la boîte de dialogue Créer un tableau qui apparait et t'invite à confirmer la plage de cellules concernée.

[Gandorf]

Bonsoir corona. Effectivement, je pense rater quelque chose, mais quoi ?
Voici le détail point par point de ce que j'ai tenté (3 "méthodes") sur l'onglet "Plannings" de mon fichier, à savoir que les données générées par macro s'incrémentent dans les colonnes A à K, et que toutes mes formules sont placées de M à AG :
Méthode 1 :
- J'efface le contenu de toutes les lignes au delà de la ligne 3. Il me reste donc la ligne d'entêtes, puis 2 lignes avec mes formules (de M à AG).
- Je me positionne en A1.
- Je clique sur insertion- Tableau.
- Il me propose par défaut la plage $A$1:$AG$3.
- Je valide.
- Ensuite, je saisi en ligne 4 (de A4 à K4) des nouvelles données.
- Le résultat, aucun calcul, aucune formule dans les cellules M4 à AG4.

Méthode 2 :
- Exactement la même que la méthode 1, à la différence prêt que pour la définition de la plage, je rentre $A$1:$AG$11000.
- Même résultat (aucun).

Méthode 3 :
- Exactement la même que la méthode 1, à la différence prêt que pour la définition de la plage, je rentre $A:$AG
- Même résultat (aucun).

Je pense que ce détail permettra de voir ce qui cloche dans ma procédure, c'est sans doute évident pour des habitués, mais je ne vois pas....

Et de toute façon, même si cela fonctionnait, un inconvénient majeur subsisterait ; La conversion en tableau semble me faire perdre toutes mes MFC, ce qui est rédhibitoire pour moi...

En tous cas, merci pour l'attention portée....
Ne voyant pas comment avancer avec cette technique, je suis en train d'essayer (avec mon petit niveau) la méthode suivante :
Pour les formules de l'onglet "Planning" par exemple :
- Je crée un onglet (que je masquerais ensuite) avec en ligne 2 une copie de toutes mes formules.
- Je nomme la plage contenant ces formules (de M2 à AG2).
- J'essaye (j'en suis là), dans le code de la page "Plannings", de programmer la recopie de la formule (de M à AG) dès lors que des données sont saisies sur une ligne (de A à K). Mais j'ai du mal pour l'instant...
Pensez-vous que c'est le bon angle d'attaque ? Dois-je persévérer en ce sens ?
Mon fichier est bien sûr prêt à être posté dès lors qu'il est sollicité, conformément à la charte...
Merci de votre attention en tous cas.
Cordialement,

[Philippe Tulliez]

Bonsoir,
Par acquit de conscience, je viens de tester la mise en forme conditionnelle sur un tableau et je confirme qu'il n'y a pas de problème non plus.
Je te suggère de déposer ton classeur parce-que j'avoue ne pas trop comprendre et puis mes tests ont portés sur un tableau d'une vingtaine de colonnes et un centaine de lignes.
Peut-être qu'en manipulant tes données on y verra plus clair.

[Gandorf]

Rebonsoir corona !
Voici donc mon fichier.
Merci encore de ton intérêt !

[Philippe Tulliez]

Bonsoir,
Dans la feuille [Planning], j'ai supprimé toutes les lignes après la 36ème et j'ai insérer un tableau.
Mis à part cette formule en colonne P que je n'ai pas décortiquée mais qu'il serait sûrement possible de simplifier et qui évidemment à posé un problème dès que j'ai supprimé les lignes 36 à 45 parce-qu'elle fait référence à une ligne qui suit, je n'ai pas de problème à insérer cette plage en tableau et de voir toutes les formules apparaître dès que j'entre une valeur en A37
Lors de la supression des lignes la formule de la colonne P renvoie et c'est normal une erreur dans la référence à A37
Par contre si je modifie cette formule avant d'insérer le tableau en refaisant référence à A37, les formules ne se recopient pas.

Code :

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

=SI(A37<>A36;SOMME(SOMME.SI(A:A;A36;N:N);SOMME.SI(A:A;A36;O:O));"")

Il faut donc revoir cette formule pour que cela fonctionne parfaitement. Quel est son but ?
Il y a lieu aussi de repenser les formules de la mise en forme conditionnelle

Bonsoir,
J'ai analysé ta formule de la colonne P et tu peux la remplacer par celle-ci.

Code :

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

=SI(LIGNE() = EQUIV(A2;$A$2:$A$36;0) + NB.SI($A$2:$A$36;A2);SOMME(SOMME.SI($A$2:$A$36;A2;$N$2:$N$36);SOMME.SI($A$2:$A$36;A2;$O$2:$O$36));"")

Pas le temps de la commenter mais si tu as un problème, n'hésite pas à demander.
Il faut bien entendu que la colonne A soient triées ascendant

[Gandorf]

Rebonsoir corona !
Pour expliquer la colonne P, une brêve description du fichier s'impose : Il s'agit de plannings de travail (dans des bus) et la colonne A (Planning) est un identifiant de personne (enquêteur). Les données se "collent" via macro dans les colonnes A à K, sont triées par Planning, puis date, puis heure de départ.
La colonne P doit renvoyer, sur la dernière ligne d'un N° de planning donné et à cet endroit seulement, la somme des colonnes N à O (Travail + Attente) relatives à ce N° de planning.... D'où la référence à la ligne suivante :

Code :

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

=SI(A37<>A36;....

veut dire "si l'on change de N° de planning... Peut-être devrais-je remplacer, à la fin de cette formule, le "" par un 0, et mettre dans les options d'excel "ne pas afficher les zéros"?

Sinon, j'ai 2 questions :
- Pourquoi jusqu'à la ligne 36 ? La ligne 2 ou 3 n'est pas suffisante ?
- Quelle est la plage définie dans le tableau que tu insères ? $A$1:$AG$36 ?

Pour les MFC, je n'ai aucune idée de la façon de les optimiser... J'ai fait cela en tatonnant longuement... Peut-être certaines peuvent-elles être regroupées?

Nos messages se sont croisés, je n'avais pas vu ton dernier avant de poster..
Je vais donc tester avec cette formule...
Merci, je reviendrais, sans doute aurais-je d'autre soucis/ questions sur ce fichier, et voir pourquoi je n'arrive pas à définir correctement la plage du tableau et conserver les MFC (excel m'impose de choisir une mise en forme lors de la création du tableau...)

[Philippe Tulliez]

Bonjour,

Sinon, j'ai 2 questions :
- Pourquoi jusqu'à la ligne 36 ? La ligne 2 ou 3 n'est pas suffisante ?
- Quelle est la plage définie dans le tableau que tu insères ? $A$1:$AG$36 ?

Insérer un tableau dans une plage existante, ici $A$1:$AG$36, cela équivaut à convertir un plage de cellules en tableau.

Ensuite le tableau s'agrandit ou diminue en fonction des ajouts ou des suppressions de lignes en adaptant toutes les références aux cellules qui si trouvent et en copiant toutes les formules dans la ligne ajoutée. C'est son avantage.

L'opération inverse est la conversion du tableau en plage de cellules.
Onglet contextuel [Outils de tableau] onglet [Création] groupe Outils, commande convertir en plage

[Gandorf]

Bonjour corona,
Merci encore à toi pour le temps passé à essayer de m'expliquer.
Je ne comprends pas, il me semble pourtant suivre les instructions à la lettre, mais quand je saisi ou colle des données en A37:K37 ensuite, aucune formule ne s'affiche et le tableau ne "s'agrandit" toujours pas ...
Peut-être une "option" d'excel désactivée ??

[Philippe Tulliez]

Bonjour,
Il n'y a rien à coller et pas d'option Excel.
Quand on a une plage de cellules qui contient des constantes et des formules, on peut transformer cette plage en tableau par Insertion, tableau.
Ensuite chaque nouvelle ligne agrandit le tableau et les formules s'ajoutent automatiquement dans les cellules adéquates.
Pour t'en convaincre, crée un nouveau classeur où dans une feuille tu tapes
A1 : Désignation
B1 : Qté
C1 : PA
D1 : Valeur
A2 : Produit 1
B2 : 10
C2 : 100
D2 = =B2 * C2
Ensuite tu convertis cette plage en Tableau et ensuite tu tapes en A3 Produit 2

[Gandorf]

Rebonjour corona !
Mon problème n'est pas tant d'être convaincu, je pense surtout louper une étape bête. Cela ne fonctionne toujours pas (j'ai réalisé l'exemple que tu viens de me donner scrupuleusement dans le mini fichier joint)... Le tableau ne s'agrandit pas...

[Philippe Tulliez]

Bonjour,
Oui effectivement, je ne sais pas comment tu es arrivé à ne pas reproduire la ligne avec ce tableau.
Retape une valeur dans la cellule A3, par exemple Toto cela devrait fonctionner. C'est ce que j'ai fait.

[Gandorf]

C'est à s'arracher les cheveux (que j'ai rares en plus).
Non, ça ne fonctionne toujours pas chez moi.
Excel 2010.
Donc, si je comprend bien, comme cela fonctionne chez toi, c'est soit une option cachée activée/désactivée chez moi, soit mon excel qui bug ??

[Philippe Tulliez]

Bonjour,
J'ai testé sur Office 2010 et je n'ai pas de problème.
Sauf s'il y a une option que j'ignore et c'est possible, c'est effectivement un casse tête.
Je vais tout de même regardé s'il y a une option mais j'avoue que je n'ai jamais été confronté à ce type de situation.

[Philippe Tulliez]

Bonjour,
On en apprends tous les jours.
Dans les options d'Excel ([Fichier] Option d'excel) colonne de gauche de la boîte de dialogue Options d'Excel sélectionner à gauche Vérification
Dans le groupe Options de correction automatique cliquer sur le bouton Options de correction automatique...
Onglet [Mise en forme au cours de la frappe] de la boite de dialogue Correction automatique.
Vérifier si l'option Formules de remplissage dans les colonnes pour créer des colonnes calculées est bien cochée.
J'ai trouvé cela dans le didacticiel de silkyroad que je te conseille de lire.

[Gandorf]

Rebonjour corona !
Alléluia !!
C'était bien ici que ça se passait !
A la différence prêt que juste au dessus de cette option, las case "inclure de nouvelles lignes et colonnes dans le tableau automatiquement" était également décochée chez moi.
Je vais donc tout tester avec ces options activées, mais cela devrait fonctionner parfaitement je pense...
Ce qui risque de m'amener une autre question : Penses-tu que l'on puisse ajouter un code à l'ouverture du classeur qui active ces options si elles ne le sont pas ? (afin de pouvoir "diffuser" ce fichier sans que l'utilisateur ait à se poser des questions et que tout roule quoi qu'il arrive)?
En tout cas merci beaucoup, je pensais bien qu'il se pouvait qu'une option soit responsable du soucis, mais je n'aurais jamais trouvé.
Merci également pour le lien !