Convertir formules en codes.

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,

Bonjour,

Citation:

---

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.

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:

---

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:

---

=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);)));"")

---

Bonjour,

Citation:

---

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.

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 !

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.

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,

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.

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

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:

---

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

---

=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

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:

---

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

Bonjour,

Citation:

---

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

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 ??

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

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

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.

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 ??

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.

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.

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 !

Bonjour,
On y arrive toujours finalement.
On peux bien sûr par du code VBA vérifier à l'ouverture du classeur si des options sont cochées mais le problème pourrait se poser aussi que les macros soient désactivées sur la machine d'une personne qui doit encoder et même si un message apparaît signalant que des macros sont désactivées, l'utilisateur non averti ne le voit pas toujours.
Depuis que j'utilise les listes et tableaux, c'est la première fois que j'étais confronté à ce problème d'où mon ignorance sur ce sujet. Par défaut ces options sont cochées.

Re bonjour corona !
Je te renouvèle mes remerciements.
J'ai réussi avec l'enregistreur à faire une macro qui active ces options si décochées, il me reste à trouver où là placer pour qu'elle se lance à l'ouverture (si macro activées, bien sûr), je devrais trouver sans problème...
Me reste un petit soucis de format des entêtes, et d'ajustement des colonnes à résoudre (ma macro lançait un ajustement auto, mais les tailles ne sont pas les mêmes lorsque l'on passe sous tableau, du fait des filtres je pense, et cela bouzille). En creusant, je devrais trouver j'espère. Sinon, je reviendrais !
Je passe donc en résolu d'ici peu...
Ah oui, tu me parlais d'un soucis avec les formules des MFC... Qu''est-ce à dire ? Elle sont trop lourdes et pourraient être simplifiées ? Si c'est le cas, j'avoue ne pas savoir ou chercher des infos sur l'optimisation de cette partie, j'ai procédé par tâtonnement jusqu'à obtention des résultats souhaités...
Merci encore !!!
Je passe donc en résolu...

Bonjour Gandorf,

Citation:

---

J'ai réussi avec l'enregistreur à faire une macro qui active ces options si décochées, il me reste à trouver où là placer pour qu'elle se lance à l'ouverture (si macro activées, bien sûr), je devrais trouver sans problème...

---

Il faut placer le code dans la procédure événementielle OPEN de l'objet ThisWorkbook

Citation:

---

Ah oui, tu me parlais d'un soucis avec les formules des MFC... Qu''est-ce à dire ? Elle sont trop lourdes et pourraient être simplifiées ? Si c'est le cas, j'avoue ne pas savoir ou chercher des infos sur l'optimisation de cette partie, j'ai procédé par tâtonnement jusqu'à obtention des résultats souhaités...

---

Je pense que j'ai vu que la mise en forme conditionnelle faisait référence à des cellules qui n'étaient pas dans la plage du tableau ce qui provoquait un message d'erreur lors de la suppression des lignes débordant du tableau.

Bonsoir corona, et merci encore !
J'ai enfin réussi à faire ce que je voulais avec ce fichier !
Reste pour moi à le rajouter aux autres, pour parvenir à mon outil final qui est maintenant proche d'être bouclé !
Pour info, à partir de l'"échantillon daté", le fichier final sort les plannings, les visuels, les détails d'heures (normales, majorées, d'attente,...), et d'autres petits détails.
Un travail de longue haleine qui touche à sa fin, pour lequel j'ai été beaucoup aidé (c'est peu dire), et pour lequel tu as activement participé !
Merci encore !
:ccool: