2013-05-31 15:52:45 +0000 2013-05-31 15:52:45 +0000
8
8

Comment écrire une formule Excel qui collera une valeur spécifique dans une autre cellule ?

Donc, en gros, je veux écrire une formule qui vérifiera une condition, et si cette condition est remplie, alors je veux coller une ligne de texte spécifique dans une cellule différente. Je dois noter que je ne veux pas que la formule existe dans la cellule dans laquelle je veux coller une valeur. Ainsi, par exemple, si je veux coller une valeur dans B5, je ne veux pas que la formule soit dans la cellule B5…

Réponses (4)

7
7
7
2013-06-02 23:43:38 +0000

L'approche suivante utilise une solution de contournement décrite ici et ici pour permettre à une fonction de feuille de calcul définie en VBA de fixer la valeur d'une autre cellule.

La fonction personnalisée stocke dans des variables globales l'adresse de la cellule cible et la valeur à laquelle cette cellule doit être définie. Ensuite, une macro qui est déclenchée lorsque la feuille de calcul recalcule lit les variables globales et fixe la cellule cible à la valeur spécifiée.

L'utilisation de la fonction personnalisée est simple :

=SetCellValue(target_cell, value)

target_cell est une chaîne de référence à une cellule de la feuille de calcul (par exemple, “A1”) ou une expression qui évalue une telle référence. Cela inclut une expression telle que =B14 où la valeur de B14 est “A1”. La fonction peut être utilisée dans n'importe quelle expression valide.

SetCellValue renvoie 1 si la valeur est écrite avec succès dans la cellule cible, et 0 sinon. Tout contenu précédent de la cellule cible est écrasé.

Trois morceaux de code sont nécessaires :

  • le code définissant SetCellValue lui-même
  • la macro qui est déclenchée par l'événement de calcul de la feuille de calcul ; et
  • une fonction utilitaire IsCellAddress pour garantir que target_cell est une adresse de cellule valide.

Code pour la fonction SetCellValue

Ce code doit être collé dans un module standard inséré dans le classeur. Le module peut être inséré via le menu de l'éditeur Visual Basic, auquel on accède en sélectionnant Visual Basic dans l'onglet Developer du ruban.

Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

Feuille de travail_Calculer le code macro

Ce code doit être inclus dans le code spécifique à la feuille de travail dans laquelle vous utiliserez SetCellValue. Le plus simple est de cliquer avec le bouton droit de la souris sur l'onglet de la feuille de calcul dans la vue Home, de sélectionner View Code, puis de coller le code dans le volet de l'éditeur qui apparaît.

Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

Code pour la fonction IsCellAddress

Ce code peut être collé dans le même module que le code SetCellValue.

Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range ' Input is valid cell reference if it can be
      On Error GoTo GetOut ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long 'convert single cell "range" address to
      colonPos = InStr(aValue, ":") 'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If 'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function
``` ### Code pour la fonction IsCellAddress
2
2
2
2013-06-02 05:30:07 +0000

Supposons que vous vouliez que le texte “Texte A” apparaisse dans la cellule C5 si la cellule B5 contient la valeur “vert”.

Vous pouvez utiliser une approche par formule, mais comme les formules ne peuvent pas changer les valeurs dans les autres cellules, la formule devra être entrée dans la cellule C5.

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

La cellule C5 n'affichera désormais le “Texte A” que si B5 contient le mot “vert”.

Une formule comme celle-ci peut être construite pour fonctionner dans de nombreuses conditions. Vous devez définir vos besoins afin d'obtenir de l'aide pour votre situation spécifique.

Si vous ne voulez pas que C5 ait une formule, vous pouvez également utiliser une approche VBA. Vous pouvez lancer un événement de modification de la feuille de calcul qui s'exécutera chaque fois que la cellule B5 sera modifiée, soit en modifiant manuellement la valeur, soit en y collant quelque chose.

Un exemple pour une telle macro pourrait être

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

Les conditions et le placement de la sortie ne sont bien sûr qu'un exemple et doivent être adaptés à vos besoins.

La différence entre la formule et l'approche macro est

  • avec l'approche par formule, la cellule C5 contiendra une formule. Si l'utilisateur supprime accidentellement la formule, la fonctionnalité qu'elle fournit sera également supprimée. (Il existe cependant des moyens de gérer cela)
  • avec la macro VBA, la cellule C5 n'affichera aucune formule et aura le texte intégral comme valeur, mais la modification de la configuration nécessite la connaissance d'Excel VBA. En outre, avec une approche VBA, le classeur doit être enregistré comme un classeur avec macros et l'utilisateur doit autoriser les macros ou faire du fichier un fichier de confiance.

Note : ce qui précède n'est qu'un exemple. Vous devez définir vos besoins, que ce soit pour évaluer des chiffres ou du texte, si l'évaluation est sensible à la casse, quelles sont les règles d'évaluation, où placer le résultat, etc.

1
1
1
2019-10-06 11:03:17 +0000

Pour vérifier si une condition est remplie, inscrivez une formule IF dans une cellule pour mettre à jour “une ligne de texte spécifique dans une autre cellule”. La cellule de résultat ne contiendra que la valeur de la formule, et NON la formule qui a généré cette valeur, comme c'est le cas :

Cellule de travail (ex. J5) =IF(A1="yes","Specific line of text","") Cellule de résultat (ex. B5) =J5

Donc si la condition est remplie (A1=“oui”), B5 contiendra “une ligne de texte spécifique”. Sinon, elle reste vide.

Notes: Les cellules qui ont des valeurs variables contiennent normalement une sorte de formule pour continuer à mettre à jour leurs valeurs.

Si vous voulez que personne ne connaisse la formule réelle qui crée la valeur, la formule peut aller dans une autre cellule pour générer la valeur, qui sera ensuite copiée dans la cellule de résultat, par exemple B5.

Pour cacher la formule qui a généré la valeur ou pour cacher la cellule à laquelle B5 se réfère (pour continuer à vérifier si la valeur doit être mise à jour), reportez-vous à https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Les autres fonctions qui peuvent obtenir le même effet comprennent CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.

-1
-1
-1
2020-01-17 07:27:23 +0000

=valeur(cellule)

Formule simple à utiliser, avait le même problème et ça marche.