Как написать формулу Excel, которая вставит определенное значение в другую ячейку?

Question

Поэтому я хочу написать формулу, которая проверит условие, и если это условие будет выполнено, я хочу вставить определенную строку текста в другую ячейку. Следует отметить, что я не хочу, чтобы формула существовала в ячейке, в которую я хочу вставить значение. Например, если я хочу вставить значение в B5, я не хочу, чтобы формула находилась в ячейке B5 ...

Answer 1

В следующем подходе используется обходной путь, описанный здесь и здесь, чтобы включить функцию рабочего листа, определенную в VBA, для установки значения другой ячейки.

Пользовательская функция хранит в глобальных переменных адрес целевой ячейки и значение, в которое должна быть установлена эта ячейка. Затем макрос, запускаемый при пересчете листа, считывает глобальные переменные и устанавливает целевую ячейку на указанное значение.

Использовать пользовательскую функцию просто:

  =SetCellValue(target_cell, value)

где target_cell - это строковая ссылка на ячейку на рабочем листе (например, "A1") или выражение, которое оценивает такую ссылку. Это включает в себя выражение, такое как =B14 где значение B14 равно "A1". Функция может использоваться в любом допустимом выражении.

SetCellValue возвращает 1, если значение успешно записано в целевую ячейку, и 0 в противном случае. Любое предыдущее содержимое целевой ячейки перезаписывается.

Необходимы три куска кода:

• код, определяющий сам SetCellValue
• макрос, который запускается событием вычисления листа; а также
• служебная функция IsCellAddress чтобы убедиться, что target_cell является действительным адресом ячейки.

Код для функции SetCellValue

Этот код необходимо вставить в стандартный модуль, вставленный в рабочую книгу. Модуль можно вставить через меню редактора Visual Basic, к которому можно перейти, выбрав Visual Basic на вкладке « Developer » на ленте.

  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

Worksheet_Calculate Macro Code

Этот код должен быть включен в код, специфичный для рабочего листа, в котором вы будете использовать SetCellValue . Самый простой способ сделать это - щелкнуть правой кнопкой мыши вкладку листа в Home представлении, выбрать « View Code , а затем вставить код в появившуюся панель редактора.

  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

Код для функции IsCellAddress

Этот код может быть вставлен в тот же модуль, что и код 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

Answer 2

Предположим, вы хотите, чтобы текст "Текст A" отображался в ячейке C5, если ячейка B5 содержит значение "зеленый".

Вы можете использовать подход на основе формул, но поскольку формулы не могут изменять значения в других ячейках, формулу необходимо будет ввести в ячейку C5.

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

В ячейке C5 теперь будет отображаться "Текст A", только если B5 содержит слово "зеленый".

Такая формула может быть построена для работы со многими условиями. Вы должны определить свои требования, чтобы получить помощь в конкретной ситуации.

Если вы не хотите, чтобы в C5 была формула, вы также можете использовать подход VBA. Вы можете запустить событие изменения рабочего листа, которое будет запускаться при каждом изменении ячейки B5, либо вручную отредактировав значение, либо вставив в него что-нибудь.

Примером такого макроса может быть

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

Условия и размещение выходных данных являются, конечно, только примером и должны быть адаптированы к вашим требованиям.

Разница между формулой и макроподходом заключается в

• с формульным подходом ячейка C5 будет содержать формулу. Если пользователь случайно удалит формулу, ее функциональные возможности также будут удалены. (Есть способы справиться с этим, хотя)
• с макросом VBA ячейка C5 не будет показывать никакой формулы и будет иметь буквенный текст в качестве значения, но изменение настройки требует знания Excel VBA. Кроме того, при использовании подхода VBA рабочая книга должна быть сохранена как рабочая книга с поддержкой макросов, а пользователь должен разрешить использование макросов или сделать файл доверенным файлом.

Примечание: приведенное выше является лишь примером. Вам необходимо определить свои требования, оценивать ли цифры или текст, учитывает ли оценка регистр, каковы правила оценки, где разместить результат и т.д.