Поэтому в основном я хочу написать формулу, которая будет проверять условие, и если условие выполнено, то я хочу вставить определенную строку текста в другую ячейку. Следует отметить, что я не хочу, чтобы формула существовала в ячейке, в которую я хочу вставить значение. Например, если я хочу вставить значение в B5, то я не хочу, чтобы формула существовала в ячейке B5….
Следующий подход использует обходной путь, описанный здесь и здесь для того, чтобы функция рабочего листа, определенная в VBA, могла установить значение другой ячейки.
Настраиваемая функция хранит в глобальных переменных адрес целевой ячейки и значение, на которое эта ячейка должна быть установлена. Затем, макрос, который запускается, когда рабочий лист заново вычисляет прочитанные глобальные переменные и устанавливает целевую ячейку на указанное значение.
Использовать пользовательскую функцию просто:
=SetCellValue(target_cell, value)
где target_cell - это строковая ссылка на ячейку в рабочем листе (например, “A1”) или выражение, которое вычисляет такую ссылку. Это включает в себя выражение, такое как =B14, где значение B14 равно “A1”. Функция может быть использована в любом корректном выражении.
SetCellValue возвращает 1, если значение успешно записано в целевую ячейку, и 0 в противном случае. Любое предыдущее содержимое целевой ячейки перезаписывается.
Требуется три куска кода:
SetCellValue IsCellAddress, гарантирующая, что target_cell является действительным адресом ячейки. Этот код необходимо вставить в стандартный модуль, вставляемый в рабочую книгу. Модуль может быть вставлен через меню редактора 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
Этот код должен быть включен в код, специфичный для рабочего листа, в котором вы будете использовать 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
Этот код можно вставить в тот же модуль, что и код 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
Условия и расположение вывода - это, конечно, только пример, и его нужно подогнать под ваши требования.
Разница между формулой и макро-подходом составляет
Примечание: вышесказанное лишь пример. Вам нужно определить ваши требования, оценивать ли числа или текст, чувствительна ли оценка к регистру, каковы правила оценки, куда помещать результат и т.д.
Чтобы проверить, выполняется ли условие, напишите формулу 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.
= значение(ячейка)
Прямая формула для использования, имела ту же проблему и работала.