Décaler le contenu d'une cellule

[gerald.lebret]

Bonjour à tous,

Je travaille sur un fichier qui va puiser ces données dans 2 ou 3 autre fichiers.
Il s'agit de donner concernant des charges et du CA de 7 points de vente avec 7 types de clients différents.

Je dois synthétiser toutes ces données et pour celui j'utilise énormément la fonction OFFSET.

En fait, ce que j'aimerais faire, c'est indiqué une fois ma formule qui va puiser dans une autre feuille et ensuite, j'aimerais pouvoir faire un offset du contenu de cette cellule sans être obligé de retaper la formule.

Par exemple, si j'ai en b2, la formule='Sheet2!b4+'Sheet2!b5, au lieu de devoir faire offset('sheet2!b4;20;0)+offset('sheet2!b5), j'aimerais pouvoir simplement faire offset(le contenu de b2;23;0).

J'ai essayé avec la fonction INDIRECT mais je n'arrive pas à "offseter" le contenu, excel va "offseter" depuis la valeur situé en b2 et non pas la formule.

Je ne sais pas si une macro ou autre chose est possible mais si quelqu'un pouvait me filer un coup de main ce serait génial.

Merci beaucoup.

 

[job75]

Re : Décaler le contenu d'une cellule

Bonjour gerald.lebret, bienvenue sur le forum,

Avec un fichier explicatif on pourrait peut-être comprendre ce que vous voulez 😕

Notez d'abord que la formule en B2 peut s'écrire (1) :

=SUM(Sheet2!B4:B5)

Ensuite de 2 choses l'une :

- ou bien vous voulez décaler la plage à sommer vers le bas, alors écrivez en B2 (2) :

=SUM(OFFSET(Sheet2!B4:B5;20;))

- ou bien vous voulez renseigner la cellule B22, auquel cas vous y entrez la même formule qu'en B2 (1) ou celle-ci :

=B2

A+

 

[gerald.lebret]

Re : Décaler le contenu d'une cellule

Bonjour Job75,

Tout d'abord merci de la réponse.
Je connaissais les réponses donner.

En fait, le sum est un exemple, j'aurais très bien pu prendre une multiplication.

Mon problème réside dans le fait que j'aimerais pouvoir offsetter le contenu d'une cellule sans avoir besoin d'en retaper le contenu.

Dans l'exemple, en fait, je vais taper ma formule en F14. JE fais référence à une cellule d'un autre fichier. En F40, en fait, je fais référence au même fichier mais à une cellule qui se trouve 23 lignes plus bas et en F66, à ce même fichier mais à un cellule qui se trouve 46 lignes plus bas. (en fait, j'offset de 23 lignes à chaque fois.

En fait, ce que j'aimerais pouvoir faire, c'est éventuellement inscrire comme je l'ai fait en colonne S, le nombre de lignes à offseter pour ensuite avoir simplement à taper une formule du genre: =offset(contenu de F$14;$s40;0).

Donc je souhaite vraiment pourvoir offseter le contenu d'une cellul sans être obligé de le retaper.

SI je pouvais même avoir une macro qui par exemple m'ouvrirait une boite demande la cellule/formule de base, les cellules de destination et le nombre de lignes à offseter, ce serait encore mieux 🙂

Je sais pas si tout ça est faisable mais dans tous les cas merci d'avance pour l'aide

 

[job75]

Re : Décaler le contenu d'une cellule

Bonjour gerald,

En fait vous voulez tout simplement alléger vos formules.

Alors sur Excel 2007/2010 : onglet Formules => Gestionnaire de noms et définir le nom CELLUL avec cette formule :

='X:\Cost Control\Budget\2013\[Budget 2013 bis.xlsx]Ventilation Paiements'!$BC$7

Ensuite vous pouvez entrer les formules :

en F14 :

=CELLUL

en F40 :

=SI(OU(DECALER(CELLUL;$S40;0)=0;DECALER(CELLUL;$S40;0)="");"";DECALER(CELLUL;$S40;0))

en F66 :

=SI(OU(DECALER(CELLUL;$S66;0)=0;DECALER(CELLUL;$S66;0)="");"";DECALER(CELLUL;$S66;0))

C'est toujours une cellule qui est décalée, "décaler un contenu" ne veut strictement rien dire 🙂

Bien sûr sur votre version Excel remplacez OU par OR et DECALER par OFFSET.

Edit : surtout ne pas oublier les 2 signes $ dans la définition de CELLUL...

A+

 

[JNP]

Re : Décaler le contenu d'une cellule

Bonjour le fil 🙂,
@ Job : N'est-il pas imprudent de nommer une zone CELLULE alors que c'est une formule existante 🙄 ?

Description
La fonction CELLULE renvoie des informations sur la mise en forme, l’emplacement ou le contenu d’une cellule. Par exemple, si vous voulez vérifier qu’une cellule contient bien une valeur numérique et non du texte avant de l’inclure dans un calcul, vous pouvez utiliser la formule suivante :
=SI(CELLULE("type"; A1) = "v"; A1 * 2; 0)
Cette formule calcule A1*2 uniquement si la cellule A1 contient une valeur numérique, et renvoie 0 si la cellule A1 contient du texte ou si elle est vide.

Bonne journée 😎
Ajout : Trop rapide pour moi, Job 😛...

 

[job75]

Re : Décaler le contenu d'une cellule

Salut Jean-Noël 🙂

C'est pour cela que j'ai mis CELLUL au lieu de CELLULE.

En fait pas sûr que ce soit nécessaire.

En tout cas pas sur version anglaise car la fonction CELLULE() se traduit par CELL().

A+

 

[JNP]

Re : Décaler le contenu d'une cellule

Re 🙂,
Si je ne m'abuse, les fonctions en anglais d'une version anglaise se traduisent automatiquement en français avec une version française, alors que le nom, lui, ne sera pas traduit 🙄...
Après, c'est comme en VBA, tu peux utiliser des variables avec des noms de fonctions, mais tu peux parfois avoir des surprises 😛...
Alors, autant ne pas tenter le diable 😉...
Bonne journée 😎

 

[gerald.lebret]

Re : Décaler le contenu d'une cellule

Re, 🙂
Tout d'abord, merci beaucoup pour toutes vos réponses.

Je travaille avec un excel en anglais donc aucun problème de traduction.

Maintenant, par rapport à la solution, ça fonctionne très bien si on fait référence à une seule cellule.
Quid si je dois faire référence à plein de cellule différente.
Donc dans mon exemple je faisais référence à BC7 d'un autre fichier, mais dans d'autres cellules, je fais référence à d'autre cellule et d'autres fichiers.

Merci beaucoup pour toute votre aide.

 

[JNP]

Re : Décaler le contenu d'une cellule

Re 🙂,

Donc dans mon exemple je faisais référence à BC7 d'un autre fichier, mais dans d'autres cellules, je fais référence à d'autre cellule et d'autres fichiers.

Job n'as plus l'air en ligne 🙄...
J'ai pas tout suivi de la discussion, mais je dirais qu'il te suffit d'avoir un nom pour chaque fichier/feuille/cellule 😛
C'est sûr que s'il y en a "plein", ça va vite devenir lourd 😱...
Bon courage 😎

 

[gerald.lebret]

Re : Décaler le contenu d'une cellule

Re, Merci JNO mais comme tu le mentionnes, il y a plein de cellule et donc c'est vitre très lourd.

Any help??

En fait, je pensais vraiment à un truc genre une macro/un userform qui quand tu l'appliques te demandes la cellule de référence ou peut-être la formule de référence, le nombre de lignes à décaler et enfin la/les cellules de destination.
...sauf que je sais pas faire. 🙁

 

[JNP]

Re : Décaler le contenu d'une cellule

Re 🙂,
S'amuser à ouvrir des fichiers par macro pour aller récupérer des valeurs puis les ramener, ça va pas être non plus très souple et rapide 😱...
A mon avis (mais ça n'engage que moi 🙄), il vaudrait mieux rationaliser les fichiers source pour toujours aller chercher les valeurs au même endroit 😉...
Sinon, avoir tous les liens dans une 2ème feuille de ton classeur et aller les chercher à fait 😛...
Bon courage 😎

 

[gerald.lebret]

Re : Décaler le contenu d'une cellule

Re,

En fait, tout est sur le même fichier sauf que pour être plus précis, et en reprenant mon exemple, ma cellule F14, fait référence à la cellule BC7 d'une autre feuille, qui elle même fait référence à la cellule d'un autre fichier.😉

J'ai à la base un fichier CA par mois.

Je veux préparer mon budget pour l'année à venir et donc j'ai regroupé sur une seule feuille le récapitulatif CA par mois. J'ai donc sur une feuille 12 tableaux qui font appel à 12 fichiers différents.

Ensuite j'ai une autre feuille sur laquelle j'analyse ce CA par type de clients (environ 7 différents) et je dois donc faire des calculs avec des proportions... et pour ça, il faut bien que je mette des liens avec ma feuille de CA.

Et donc je ne vois pas du tout comment je pourrais simplifier la chose....😕

De plus, j'ai plus ou moins la même chose pour mes charges.

(à titre indicatif, le fichier budget pour 2012 que j'essaie d'améliorer faisait 30MB)

Merci encore pour votre aide

 

[job75]

Re : Décaler le contenu d'une cellule

Re,

Bien trop compliqué un UserForm, une simple fonction macro suffit, à placer dans un Module standard :

Function DECAL(Cellule As Range, Nlig As Long, Ncol As Long) As Range
Set DECAL = Evaluate(Cellule.Formula).Offset(Nlig, Ncol)
End Function

Mais il y a un hic : le classeur source doit être OUVERT.

Par exemple dans votre fichier, il y a en F14 la formule :

='X:\Cost Control\Budget\2013\[Budget 2013 bis.xlsx]Ventilation Paiements'!BC7

Il faut ouvrir le classeur Budget 2013 bis.xlsx alors la formule en F14 devient :

='[Budget 2013 bis.xlsx]Ventilation Paiements'!BC7

et cette formule en F40 fonctionnera :

=SI(OU(DECAL($F$14;$S40;0)=0;DECAL($F$14;$S40;0)="");"";DECAL($F$14;$S40;0))

Copiée en F66 elle devient bien sûr :

=SI(OU(DECAL($F$14;$S66;0)=0;DECAL($F$14;$S66;0)="");"";DECAL($F$14;$S66;0))

A+

 

[gerald.lebret]

Re : Décaler le contenu d'une cellule

Re,

Ca semble exactement ce que j'ai besoin.

Mais je n'arrive pas à le faire fonctionner.

N'étant pas (ou plutôt pas encore, j'espère le devenir un jou 🙂)un expert dans les macros, j'aimerais juste comprendre la macro.
C'est à quel moment que je dis qu'il doit décaler avec la valeur indiquée en S40.

En fait, est-ce que ça t'embête de m'expliquer la macro en détail parce qu'ai j'ai fait un bête copier-coller du code et ça ne fonctionne pas?

Merci encore.

 

[job75]

Re : Décaler le contenu d'une cellule

Re,

Non ça ne m'embête pas.

Function DECAL(Cellule As Range, Nlig As Long, Ncol As Long) As Range

Déclare la fonction et ses 3 arguments.

Set DECAL = Evaluate(Cellule.Formula).Offset(Nlig, Ncol)

- Evaluate(Cellule.Formula) => prend la formule (en notation A1) dans Cellule et l'évalue => c'est un objet Range qui est renvoyé

- .Offset(Nlig, Ncol) décale l'objet Range obtenu

- Set DECAL : il faut toujours utiliser Set quand on définit un objet.

A+