IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Formula - passer de référence absolue à relative


Sujet :

Excel

  1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut Formula - passer de référence absolue à relative
    Bonjour,

    Dans la formule suivante, je cherche à rendre la colonne B relative, de façon à ce que cette formule puisse être copiée dans les colonnes suivantes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"
    La formule dans la colonne B2 est maintenant :

    Maintenant il ne reste qu'à déverrouiller la colonne afin de pouvoir copier la formule dans les autres colonnes et qu'elle s'adapte :
    Code formule : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    =(B$5-B$27)
    =(C$5-C$27)

    Merci en avance pour votre aide.

    Olivier

  2. #2
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Si tu écris une formule dans une plage de cellules en une seule instruction VBA et que tu références les cellules en relatif, absolue ou mixte, c'est exactement comme on le fait manuellement en trois étapes
    • Sélection de la plage (pour l'exemple C4:K12)
    • Insertion de la formule dans la cellule active =$B4 * C$3
    • et raccourci clavier Ctrl + Enter


    Exemple de la table de multiplication que je montre lors de mes formations dans la partie "Trucs et astuces"

    Nom : 200401 - Trucs et astuces.png
Affichages : 269
Taille : 15,4 Ko

    En VBA, une ligne de code
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("C4:K12").Formula = "=$B4*C$3"
    La base d'excel est la maîtrise du référencement des cellules

    A lire éventuellement Excel: Les références absolues et relatives dans vos classeurs Excel
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut
    Bonjour,

    Directement dans Excel, c'est quelque chose d'assez simple je comprend.

    Mais si tu regardes ma formule, les 2 lignes qui sont utilisées ne sont pas fixes et c'est ce qui me bug. Avec la formule que j'utilise avec le find et le offset, le retour me donne une référence absolue et non une référence relative...

  4. #4
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    Ce que tu affiches ressemble à une formule matricielle mais ce n'est pas ta question

    Je t'ai montré comment écrire une formule simple dans Excel avec du VBA

    Je te conseille la lecture de ces billets pour comprendre comment traduire facilement en VBA la syntaxe d'une formule


    Pour obtenir une adresse relative, avec la propriété Address, il faut modifier la valeur des arguments RowAbsolute et ColumnAbsolute qui sont pas défaut à True

    Exemple pour obtenir $A2
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    MsgBox Range("A2").Address(RowAbsolute:=False)
    et pour obtenir en plus la parentalité complète de la cellule
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
     MsgBox Range("A2").Address(RowAbsolute:=False, External:=True)
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  5. #5
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par Olivier8510 Voir le message
    Dans la formule suivante, je cherche à rendre la colonne B relative, de façon à ce que cette formule puisse être copiée dans les colonnes suivantes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"
    La réponse est dans l'aide VBA de la propriété Address : https://msdn.microsoft.com/fr-fr/lib...5(v=office.15)
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Salut


    Citation Envoyé par Olivier8510 Voir le message
    [...]
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Range("B2").Formula = "=(" & Range("A:A").Find("Ventes").Offset(0, 1).Address & "-" & Range("A:A").Find("CMV").Offset(0, 1).Address & ")"
    [...]
    Pourrais-tu nous donner EN EXCEL la formule que tu souhaites utiliser? J'ai l'impression que tu te compliques drôlement la vie avec ta "formule" recomposée par concaténation...

    Et tant qu'à faire, si tu pouvais nous expliquer, en français et donc sans jargon Excel ou VBA ce que tu souhaites obtenir, ça nous aiderait à mieux t'aider
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut
    Je viens d'appliquer le row/column absolute de address et cela répond exactement à ce que je m'attendais comme résultat. Merci à Philippe et Manhir pour votre temps!

    Pour Pierre, je suis convaincu qu'effectivement la formule pourrait être améliorée. Essntiellement je veux calculer une marge brute sur vente à partir de (1) les ventes - (2) le coût des marchandises vendues. Toutefois, ce qui présente un dédi vient du fait que les lignes des ventes et coût des marchandises vendues ne sont pas fixes dans la feuille. C'est pourquoi j'utilise le find dans la formule (pour le moment). Si vous avez une solution pour simplifier cette formule, je suis à l'écoute

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Sans avoir a minima la structure de tes feuilles en copie d'écran, c'est un peu jouer à Irma la voyante...

    J'ai de très gros doutes qu'il faille passer par du VBA et des FIND (Beurk, au passage) pour réaliser ce que tu souhaites obtenir;
    • La plupart des questions posées le sont suite à une mauvaise conception de classeur (ce n'est pas une critique mais un simple constat);
    • 95% du vba qui est pondu sur ce forum est inutile lorsque l'on maîtrise correctement Excel;
    • Le VBA ne devrait être là que pour automatiser ce que l'on réalise à la main;
    • il est souvent plus utile de regarder ce qui se cache derrière la question plutôt que de répondre sur un problème de ; ou de $ mal placé.



    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour Pierre,
    95% du vba qui est pondu sur ce forum est inutile lorsque l'on maîtrise correctement Excel,

    C'est un point sur lequel j'insiste lors de mes formations VBA.
    Plus on maîtrise Excel, mieux on programme en VBA pour excel et dans un projet, un gros pourcentage peut être construit avec ce qui est nativement présent dans l'application et surtout utiliser les méthodes des fonctionnalités natives d'excel.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  10. #10
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut
    Voici mon fichier Excel en pièce jointe.
    Fichiers attachés Fichiers attachés

  11. #11
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Voilà TOUT ce qu'il y a dans ton classeur...

    Nom : 2020-04-02_160420.png
Affichages : 270
Taille : 8,5 Ko

    Oserais-je dire que je suis perplexe? C'est un poisson d'avril à retardement? Où est la caméra cachée?

    Sérieusement, que veux-tu que l'on fasse avec ça et comment veux-tu que l'on comprenne ton besoin?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  12. #12
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut
    En fait j'ai simplifié la feuille pour que ce soit plus visuel, car il y a plus de 300 lignes dans l'originale.

    Pour résumer, il y a plusieurs feuilles dans le classeur dont une qui se nomme disons "FT Master". Cette feuille contient beaucoup de lignes et constitue la case départ pour d'autres feuilles qui seront éventuellement créées.

    Ici, la feuille "FT Master" a été copiée dans une nouvelle feuille qui se nomme "FT État des résultats", dans laquelle on voudra calculer la "marge brute", et ce à partir des lignes "Ventes" et "Coût des marchandises vendues".

    "Ventes" et "Coût des marchandises vendues" provenant de la "FT Master" qui a été copiée, elles ne se situent donc pas toujours sur les même lignes de la feuille "FT État des résultats".

    Je recherche donc la façon la plus simple d'identifier les lignes "Ventes" et "Coût des marchandises vendues" dans la colonne A de la feuille "FT État des résultats" pour ensuite utiliser les montants de leur colonne B respective et ainsi calculer la "Marge brute".

    Voilà. En espérant que cela puisse vous éclairer!

  13. #13
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Citation Envoyé par Olivier8510 Voir le message
    En fait j'ai simplifié la feuille pour que ce soit plus visuel, car il y a plus de 300 lignes dans l'originale.[...]
    La simplifier en deçà de trois cellules, tu reconnaitrais que c'est difficile. Pour avoir simplifié, tu as simplifié...

    Donne-nous des copies d'écran de ce que tu as pour que l'on puisse imaginer comment tu as organisé tes données. Comment veux-tu que l'on puisse s'imaginer la conception de ton classeur sur base de ce que tu dis?

    Un classeur de ce type s'articule autour des tableaux structurés (voir mon tuto). C'est comme cela qu'Excel, qui je le rappelle est un outil d'analyse, travaille le mieux.

    Sans visuel sur la conception de tes feuilles, on perd juste son temps. Tu risques juste de te retrouver avec autant de propositions qu'il y aura de personnes qui te répondent, chacune ayant compris à sa sauce ce que tu as essayé d'expliquer.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  14. #14
    Nouveau Candidat au Club
    Homme Profil pro
    Collégien
    Inscrit en
    Avril 2020
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Collégien

    Informations forums :
    Inscription : Avril 2020
    Messages : 6
    Points : 1
    Points
    1
    Par défaut
    Je suis nouveau dans cette communauté. Ne connaissant pas encore les faits et aboutissants, je tentais simplement de vous simplifier la vie. Vous trouverez ci-joint le fichier Excel, encore en version simplifié, mais qui je crois vous permettra de comprendre l'idée de la structure. Merci encore une fois pour votre temps et votre patience!
    Fichiers attachés Fichiers attachés

  15. #15
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    Pas de soucis d'être nouveau, juste prendre le temps de te mettre à la place de ceux qui prennent du temps pour t'aider et, avant de poster, de relire ton message pour voir s'il est compréhensible...

    Je vais te donner deux réponses:

    1. Réponse technique au problème posé. En colonne D, les formules utilisées en B

    Nom : 2020-04-02_184900.png
Affichages : 241
Taille : 12,4 Ko


    2. Evidemment, cette réponse ne me convient pas, car c'est du truc de bourrin et ça ne tient pas la route.

    1. Questions:
    2. Pourquoi les données sont-elles organisées ainsi en FT Master? C'est une façon d'organiser les données qui permet très peu, voire pas du tout, d'utiliser les outils d'Excel
    3. Comme je suppose que ce n'est pas sur une seule feuille que tu vas travailler... Comment les infos arrivent-elles dans la feuille FT Master?
    4. Devras-tu compiler les infos reçues sur chaque FT Master dans une seule et même feuille?


    J'en ai probablement d'autres, mais pour l'instant, vois déjà tes données et donne-nous les réponses à mes questions. En français et sans jargon Excel et encore moins VBA, explique-nous ta démarche, la source de tes données, ton besoin, un peu comme si tu remplissais un cahier des charges pour un programmeur qui devrait te développer le truc.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. [XL-2019] Ligne Absolue ou référence semi-relative Vba
    Par iliesss dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 19/12/2019, 11h27
  2. Réponses: 4
    Dernier message: 24/02/2008, 22h16
  3. Réponses: 17
    Dernier message: 24/11/2006, 19h25
  4. url absolue / relative
    Par enzo21 dans le forum Flash
    Réponses: 9
    Dernier message: 30/08/2006, 19h23
  5. [C#] Comment passer la référence d'une fenêtre à une autre ?
    Par L`artiste dans le forum Windows Forms
    Réponses: 6
    Dernier message: 08/01/2006, 17h26

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo