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

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

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

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

=SetCellValue(target_cell, value)

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

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

Требуется три куска кода:

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

Код функции SetCellValue Function

Этот код необходимо вставить в стандартный модуль, вставляемый в рабочую книгу. Модуль может быть вставлен через меню редактора 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 Function

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

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

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

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

Ячейка C5 теперь будет показывать “Текст А” только в том случае, если 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

Условия и расположение вывода - это, конечно, только пример, и его нужно подогнать под ваши требования.

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

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

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

Чтобы проверить, выполняется ли условие, напишите формулу IF в какой-нибудь ячейке, чтобы обновить “определенную строку текста в другой ячейке”. Ячейка результата будет содержать только значение формулы, а не формулу, которая генерировала это значение:

Рабочая ячейка (например, J5) =IF(A1="yes","Specific line of text","") Ячейка результата (например, B5) =J5

Таким образом, если условие выполнено (A1=“да”), B5 будет содержать “определенную строку текста”. В противном случае она останется пустой.

Notes: Ячейки, которые имеют значения переменных, как правило, будут содержать какую-то формулу для продолжения обновления своих значений.

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

Чтобы скрыть формулу, которая создала значение, или ячейку, на которую ссылается B5 (чтобы продолжать проверять, нужно ли обновлять значение), обратитесь к: https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Другие функции, которые могут достичь того же эффекта, включают CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.

= значение(ячейка)

Прямая формула для использования, имела ту же проблему и работала.