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

В следующем подходе используется обходной путь, описанный здесь и здесь, чтобы включить функцию рабочего листа, определенную в 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

Предположим, вы хотите, чтобы текст "Текст 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 рабочая книга должна быть сохранена как рабочая книга с поддержкой макросов, а пользователь должен разрешить использование макросов или сделать файл доверенным файлом.

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

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

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

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

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

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

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

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

При использовании функции ЕСЛИ Vineet хочет сохранить старое значение в ячейке, если условие ложно. Другими словами, значение в ячейке, где используется функция ЕСЛИ, должно меняться только в том случае, если условие, проверяемое функцией ЕСЛИ, истинно. Однако по умолчанию функция ЕСЛИ принимает значение 0, если условие ложно.

Функция ЕСЛИ может принимать до трех параметров. Первый параметр — это сравнение, которое необходимо выполнить, второй параметр — то, что должно быть возвращено, если сравнение истинно, а третий — то, что должно быть возвращено, если сравнение ложно. Последний параметр можно пропустить, но если вы это сделаете, Excel вернет значение 0, если сравнение окажется ложным. (Это то, что Винит видит в результате использования функции ЕСЛИ.)

Таким образом, очевидное решение — убедиться, что вы предоставили функции ЕСЛИ что-то, что должно быть возвращено, если сравнение окажется ложным. Например, предположим, что ваша формула находится в ячейке B1, и вы сравниваете что-то в ячейке A1. Используемая вами формула может выглядеть следующим образом:

=ЕСЛИ(A1<10,"меньше десяти",B1) Обратите внимание, что слова "меньше десяти" возвращаются, если значение в A1 меньше 10. Если это условие не выполняется, то возвращается значение в B1. Поскольку эта формула находится в ячейке B1, это означает, что предыдущее значение в ячейке возвращается, если условие ложно.

Это также означает, что формула содержит циклическую ссылку. Чтобы циклические ссылки работали нормально, вам необходимо сообщить Excel, что их появление на вашем листе разрешено. Выполните следующие действия, если вы используете Excel 2010 или более позднюю версию:

1. Откройте вкладку «Файл» на ленте и нажмите «Параметры». Excel отображает диалоговое окно «Параметры Excel». 2. В левой части диалогового окна выберите «Формулы». Убедитесь, что установлен флажок «Включить итеративные вычисления». 3. Нажмите ОК.

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

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