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

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

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

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

Предположим, вы хотите, чтобы текст «Text 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 

Условия и размещение выпуска – это, конечно, пример, и его необходимо скорректировать с учетом ваших требований.

Разница между формулой и макроподходом

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

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

  • Как я могу гиперссылки из PowerPoint в Excel, ссылки на конкретную вкладку и названный диапазон или ячейку?
  • Как переносить и группировать данные в Excel 2010?
  • Нужно объединить строки в excel, учитывая три идентификатора без ущерба исходным данным
  • Excel автоматически добавляет дополнительную строку с формулами для введенного количества лет
  • Цвета ячеек в зависимости от значения
  • Сделать формулу массива в excel вычислять только по активным строкам
  • Excel 2010 - Как записать макрос и воспроизвести его для всех ячеек в столбце?
  • Проверьте, является ли текст ячейки Excel числовым, используя только формулу
  • Скопируйте рабочий лист Excel и поддерживайте относительную ссылку на ячейки в формулах
  • Excel 2010 Удаление дубликатов
  • Окраска частичного текста внутри ячейки в Excel
  • Interesting Posts

    Переменная среды не прилипает

    Хранить и читать hash и массив в файлах в Perl

    Смещение CSS-поля

    Какую библиотеку websocket использовать с Node.js?

    Как отменить 2 Modal View Controllers в последовательности?

    Как использовать перехватчик для добавления заголовков в «Дооснащение 2.0»

    Есть ли способ предотвратить установку / обновление для моего жесткого диска с помощью загадочных папок?

    Существуют ли проблемы, которые невозможно записать с помощью хвостовой рекурсии?

    Регулярные выражения в примерах C:?

    Используется несколько интернет-подключений.

    Почему администраторы не могут удалять ключи реестра при запуске regedit в качестве администраторов?

    Как повторно использовать JSON / JAXB от Джерси для сериализации?

    Может ли файл Byte записываться в файл на C #?

    Событие OnClick для кнопки в пользовательском уведомлении

    Как сделать раскрывающееся меню Twitter Bootstrap, а не щелчком

    Давайте будем гением компьютера.