[thanatosX]

Exemple de ma feuille Excel
(la photo est grosse, donc je mets un lien vers l'image plutôt que de poster une image qui prend tout l’écran)

Bonjour, je recherche le moyen dans une formule de ne PAS utiliser la référence fixe d'une cellule.
Dans ma cellule E2, j'ai "=SI(A2=0;0;1)" => Si A2 est égale à 0 alors 0, sinon 1.
Et je faire en sorte que dans E2 j'ai Si la 4eme cellule à gauche est égale à 0 alors 0, sinon 1.

Oui, je sais recopier une formule vers le bas, mais ça n'est pas ça que je veux.
Sauf que si je fait ça, je suis obligée de faire ça sur 12 onglet différents et sur 39890 lignes !
Alors que si j'arrive a faire ce que je veux, je crée une formule dans E2 et dans toute les autre je met que c'est égale à E2.
Il me suffit de changer E2 pour que ça change mes 39889 autre cellules.

[~Boumy~]

Te casses pas la tête à essayer de faire ca.

Change la formule dans la première case, puis double clique dans le coin en bas à droite (le petit carré noir) et ca va appliquer la nouvelle formule à toutes les lignes.
Répéter ca 12 fois ne devrait pas être trop compliqué.

[Lithiye]

Si la position de ta formule dans tous tes onglets est strictement la même tu peux appliquer ce que tu peux fais au premier à tous tes onglets en même temps déjà.

[aziraphale]

A ma connaissance c'est impossible de dire à des cellules qu'elles sont égales à une autre et que ça soit la formule à l'intérieur de la cellule qui soit copiée et pas la valeur.
Surtout qu'au final pour dire à toutes tes cellules qu'elles sont "égales à E2" ça va te prendre le même temps puisque tu vas devoir faire exactement la même chose que pour leur dire qu'elles doivent appliquer la fonction.

Tu peux automatiser ton truc en créant une macro fera le boulot de remplissage des cellules concernées à ta place mais je ne vois pas trop l'intérêt vu que ça va te prendre bien plus de temps que de tirer une cellule vers le bas pour copier la formule partout automatiquement.

Après, si ton problème c'est que dans un onglet c'est la colonne E qui va vérifier A et dans un autre c'est G qui va vérifier C, c'est faisable de créer une fonction personnalisée toto() qui récupère la valeur de la 4eme colonne à gauche et fasse ton calcul.
Ca ne change rien au fait qu'une fois que tu auras mis dans la cellule [E2] "=toto()" il faudra copier cette formule dans toutes les cellules où tu en as besoin.

[cricri]

pas bien comprit ce que tu veux

sinon =SI(INDIRECT("E2";1)=1;4;6)

si toute tes 30000 lignes =e2 elle sont toute identique je vois pas l interet

[~Boumy~]

Citation :

Publié par cricri

pas bien comprit ce que tu veux

sinon =SI(INDIRECT("E2";1)=1;4;6)

si toute tes 30000 lignes =e2 elle sont toute identique je vois pas l interet

De ce que j'ai compris, il veut avoir une cellule avec une formule mathématique (A+B) qu'il applique sur une colonne entière (A1+B1, A2+B2,...). Puis il veut pouvoir changer la formule (genre A-B) et que cela se répercute sur l'ensemble de la colonne.
Pour moi, c'est se compliquer la vie pour rien.

[cricri]

oui suffit d ettirer la formule

[thanatosX]

Je cherche a faire en sorte que quant je change la formule dans E2, tout le reste des colonnes E (dans tous les onglets) soit changé. E2 utilise A2/C2, mais E3 devra utiliser A3/E3 et non pas A2/C2.
Encore une fois, OUI, JE SAIS qu'on peut étirer la formule vers le bas, mais ça n'est pas ce que je souhaite faire 40000 fois sur 12 onglets. Si ma méthode est possible, je change E2 et tout est changé en une fois et sans manipulations.

Pour le noms de mes colonnes, c'est strictement égale, donc c'est simple.
Mais là, A recherche B dans H et affiche 0 ou 1 en fonction de la présence de B dans H.

[aziraphale]

Le plus simple c'est de te créer une fonction personnalisée dans ce cas.
Toutes tes cellules des colonnes E appelleront cette fonction, donc il te suffira de changer le calcul dans la fonction pour que toutes les valeurs changent d'un seul coup.

[thanatosX]

Comment j'utilise des références relatives ?

[aziraphale]

Une fonction tu peux lui passer des arguments.
Si tu définis une fonction TOTO ( X ), tu n'auras plus qu'a écrire dans [E2] "=TOTO(A2)" et ça fera le calcul que tu as prévu.

La première fois il faudra bien évidemment que tu fasse le boulot de copier la formule dans toutes tes cellules E de toutes tes pages, mais ensuite il te suffira d'aller modifier le calcul fait par TOTO(X).


Cherche un petit tuto création de fonction Excel en VBA tu verras que c'est assez simple.
Le plus compliqué c'est souvent de trouver le nom en anglais des fonction EXCEL qu'on veut utiliser dans sa fonction quand on a l'habitude du français.

[Quild]

Tu sais que tu peux mettre ton fichier excel en pièce-jointe sur jol plutôt qu'une capture d'écran ?

Si tu crées un vrai tableau excel (selectionne ton tableau, onglet insertion, "tableau"), les formules s'entendront automatiquement à toute la colonne. Mais ça crée plein d'autres problèmes et ça ne résouds pas le fait que tu veux l'étendre à d'autres onglets.


Tu peux donc télécharger MOREFUNC qui contient la fonction "EVAL()".

Ensuite sous excel, tu fais "fichier/options/formules/"style de réference L1C1"

Tu rentres quelque part "SI(LC(-3)=0;0;1)"
Sans les guillemets bien sûr.

Il te reste ensuite à mettre dans toutes tes colonnes E la formule =EVAL(làoùtuasrentrétaformule).

Quand tu changeras ta formule (et je vois pas trop ce que tu voudrais lui changer, mais bon), ça recalculera automatiquement.


Tu peux créer une macro plutôt que d'utiliser MOREFUNC et ça aura l'avantage que la macro suive ton fichier plutôt que de demander d'installer MOREFUNC sur tout ordi où tu voudras l'utiliser, mais je ne suis pas sûr que tu saches faire.

[I3aldou]

Citation :

Publié par cricri

...SI(INDIRECT("E2";1)=1;4;6)

si toute tes 30000 lignes =e2 elle sont toute identique je vois pas l interet

Tu n’as rien compris au besoin de l’OP. La fonction INDIRECT n’est ni faite pour ça, ni ne répond au besoin.

Citation :

Publié par cricri

oui suffit d ettirer la formule

Tu as lu la demande de l’OP ?

Citation :

Publié par ~Boumy~

Te casses pas la tête à essayer de faire ca.

Change la formule dans la première case, puis double clique dans le coin en bas à droite (le petit carré noir) et ca va appliquer la nouvelle formule à toutes les lignes.
Répéter ca 12 fois ne devrait pas être trop compliqué.

Jusqu’à present cela semble etre la meilleure solution en termes de simplicité / temps à y passer. Le seul inconvénient est que le truc s’arrête si il y a des
lignes vides.

Citation :

Publié par Quild

Tu peux créer une macro plutôt que d'utiliser MOREFUNCTION..... pas sûr que tu saches faire.

Macros et morefunction seraient des solutions possibles mais tu le soulignes parfaitement : cela demande un minimum de connaissance et de temps. A l’Op de voir si l’investissement apprentissage + temps de mise en œuvre est rentable pour lui.

Sinon il y a une autre solution possible pour changer une formule dans de nombreuses cellules mais c’est limité à certains changements comme changer un + en - ou un SIN par COS. Il suffit de sélectionner toutes les cellules à modifier, de passer en affichage «*formules*» et de faire un search/replace. Pas sûr que cette méthode soit plus efficace/rapide que d’autres (dont celle de Boumy) pour l’OP mais elle m’a déjà fait gagner beaucoup de temps dans des situations similaires. L’inconvénient est qu’elle n’est pas très appicable pour changer massivement des références de cellules.

[Quild]

Citation :

Publié par I3aldou

Sinon il y a une autre solution possible pour changer une formule dans de nombreuses cellules mais c’est limité à certains changements comme changer un + en - ou un SIN par COS. Il suffit de sélectionner toutes les cellules à modifier, de passer en affichage «*formules*» et de faire un search/replace. Pas sûr que cette méthode soit plus efficace/rapide que d’autres (dont celle de Boumy) pour l’OP mais elle m’a déjà fait gagner beaucoup de temps dans des situations similaires. L’inconvénient est qu’elle n’est pas très appicable pour changer massivement des références de cellules.

T'es pas gonflé de critiquer les solutions des autres (que moi, j'entends), pour pas proposer mieux.

Le principal problème de l'OP c'est d'appliquer sa solution à 12 onglets. Ta solution n'est pas mieux qu'étirer la formule vers le bas.

Etirer une formule vers le bas peut se faire de 3 façons :
Double clic en bas à droite de la première cellule : mais s'arrête en cas de cellule vide
Tirer la formule vers le bas : mais chiant si beaucoup de lignes
Faire un vrai "tableau" sous excel : automatique et aide peut-être le problème de l'OP parce que la référence est celle d'une colonne du tableau et non pas d'une cellule. Mais les tableaux peuvent poser des problèmes de manipulation.


OP : Je vois pas pourquoi tu veux faire ça ceci dit. Ta formule permet de savoir si tu as un équipement ou pas. Pourquoi penses-tu devoir la changer ? Et assez souvent pour que ça soit embêtant ?

[thanatosX]

Pour la pièce jointe, le fichier fait déjà 2,4 Mo ça prend 5-6 secondes rien que pour l'ouvrir en local. Je suis issue de l’ère des modems. Le fichier est disponible là :

• Version avec macro => http://eqtoonx.free.fr/fichiers/Trad...-300-v2.0.xlsm - 2446 Ko<=
• Version sans macro => http://eqtoonx.free.fr/fichiers/Trad...ver%20300.xlsx 2443 Ko<=
  

Je suis en mode édition, donc je peux êtres amener à changer souvent certaines formules.
Il est prévu que ce fichier soit partagé, donc j’essaie d'éviter l'utilisation ou l'addition de choses pour les utilisateurs final. Merci beaucoup pour votre patience et votre inestimable aide.

Changer le mode de référence n'aide pas non plus.
Options d'Excel => Formules => Manipulation de formules (Style de référence L1C1)

PS : C'est surtout pour me simplifier la vie, si ça n'est pas possible, c'est pas grave, je ferais avec.

[Chernish]

Utilise les fonctions INDEX et ROW (en anglais, je ne les connais pas en français).
INDEX pour fixer la colonne, et ROW pour faire varier la ligne.

La formule
"=IF(A2 = 0, 0, 1)"
devient:
"=IF(INDEX(A:A, ROW()) = 0, 0, 1)"

[thanatosX]

Citation :

Publié par Chernish

Utilise les fonctions INDEX et ROW (en anglais, je ne les connais pas en français).
INDEX pour fixer la colonne, et ROW pour faire varier la ligne.

La formule
"=IF(A2 = 0, 0, 1)"
devient:
"=IF(INDEX(A:A, ROW()) = 0, 0, 1)"

=LIGNE() me renvois le numéro de la ligne où est la formule (ROW)
=COLONNE() renvois le numéro de la colonne (A=1, B=2, etc...) (COL)
=INDEX(XX:YY) permet de définir le contenu de la cellule XX:YY

Je vais voir si je peut utiliser ça dans mes formule. Par contre si je me réfère à cette cellule, il me donne le même résultats sur toutes mes cellules. Mais, ça avance.

[Chernish]

Voilà deux autre possibilités, avec la fonction OFFSET (DECALER en français). Ca correspond probablement mieux à ce que tu cherches, car on peut y utiliser des positions relatives complètes.

Ou encore tu peux modifier le style de référence dans les Options (Options - Formules -> Style de référence R1C1). Là tu peux utiliser par exemple la formule "=RC[-1]" pour référencer la cellule de la même ligne et de la colonne précédente. Les formules sont "traduite" en stye A1 lorsque tu changes les options.
Mais évidemment si tu dois changer de style souvent c'est lourd de passer par les options à chaque fois.

<rant> Si ça ne tenait qu'à moi je serais toujours en stlye R1C1, parce que que c'est quand-même plus logique que ce p**ain d'alphabet, mais je dois partager mes templates et certains de mes collègues bavent de terreur lorsqu'ils sortent de leurs chers sentiers battus. Alors c'est INDEX ou OFFSET... </rant>

[Quild]

Citation :

Publié par Chernish

<rant> Si ça ne tenait qu'à moi je serais toujours en stlye R1C1, parce que que c'est quand-même plus logique que ce p**ain d'alphabet, mais je dois partager mes templates et certains de mes collègues bavent de terreur lorsqu'ils sortent de leurs chers sentiers battus. Alors c'est INDEX ou OFFSET... </rant>

Quand tu travailles sur des tableurs de dizaines de colonnes c'est un peu lourd de te dire "hmmm, cette colonne fait référence à 10 colonnes avant, comptons de laquelle il s'agit".

[thanatosX]

Bon, j'arrive a utilise des références relatives avec DECALER, cà me pose donc le problème de faire référence à la cellule où est la formule. Mais surtout, si la cellule en dessous est égale à la cellule de référence, ça me copie la valeur et non la même formule.

[Chernish]

A Quild: Quand tu écris ta formule dans Excel en style r1c1, tu peux cliquer sur la cellule et ça te donnera la ref en style r1c1...
Non vraiment c'est juste des habitudes.

[aziraphale]

Citation :

Publié par thanatosX

Bon, j'arrive a utilise des références relatives avec DECALER, cà me pose donc le problème de faire référence à la cellule où est la formule. Mais surtout, si la cellule en dessous est égale à la cellule de référence, ça me copie la valeur et non la même formule.

C'est le principe de base du tableur.
Tu ne pourras jamais dire que E3 = E2 et que ça copie la formule de calcul qui est dans E2 dans E3.

Si c'est l'opération de copie de la formule qui t'emmerde (au pif, pour 12 onglets ça doit prendre 1 minute à tout casser) tu peux automatiser le truc avec une macro que tu associes à un gros bouton dans le premier onglet comme ça une fois la formule dans E2 modifiée en 1 clic le boulot sera fait (mais ça pendra plus du temps quand même).

C'est bien pour ça que je t'ai conseillé de créer une fonction : l'opération de copie tu la fais une fois au moment de la création, après c'est juste la modification du calcul dans la fonction qui est nécessaire, rien à changer dans les cellules.

[thanatosX]

Citation :

Publié par aziraphale

pour 12 onglets ça doit prendre 1 minute à tout casser

Pas sur 39000 lignes...

Citation :

Publié par aziraphale

tu peux automatiser le truc avec une macro que tu associes à un gros bouton dans le premier onglet comme ça une fois la formule dans E2 modifiée en 1 clic le boulot sera fait

Ca, je prend ![/QUOTE]

[Quild]

Citation :

Publié par thanatosX

Pas sur 39000 lignes...

Ben le double clic en bas à droite va te dérouler la formule jusqu'en bas si tu n'as pas de cellule vide.

Sinon tu peux aussi faire :
Sélectionner la cellule avec la nouvelle formule.
CTRL+C
CTRL+SHIFT+BAS en restant appuyé sur les deux premiers et répétant BAS jusqu'à arriver à la fin de ton tableau ou en faisant HAUT une fois à la fin si tu es arrivé tout en bas de la feuille excel
CTRL+V
Ce qui te copiera la formule très rapidement.

Ou sélectionner la cellule avec la nouvelle formule, faire la même manip qu'au dessus pour sélectionner toute la colonne et faire CTRL+B.

Ou comme je le disais faire un vrai tableau qui mettra automatiquement la formule sur toute la colonne (mais pose problème si tu as plusieurs formules différentes dans une colonne).


Le nombre de lignes est franchement secondaire. Ton nombre d'onglets ne paraît pas assez élevé pour nécessiter une solution complexe sauf à ce que tu veuilles procéder à des ajustements par tâtonnement. Mais vu ton tableur, j'en doute.

[Glor]

Par contre si y'a trop de cases vides (peu probable vu la gueule du truc, mais on sait pas trop...), CTRL+C, ascenseur jusqu'en bas de la feuille, clic gauche + SHIFT sur la dernière cellule et CTRL+V (ou CTRL+ALT+V->U->ENTER si il a des histoires de formats à gérer différemment, mais encore une fois j'en doute sur 39000 lignes).