Подстановочное значение Excel в ячейке в зависимости от значения в другой ячейке
Обновлено: 23.11.2024
Всегда кажется невозможным, пока это не сделано [Нельсон Мандела]
Обратите внимание: эта статья предназначена только для экспериментального использования. Мы не призываем никого использовать эти открытия в реальных приложениях. (Кроме разработчиков Excel. )
Мы знаем, что пользовательские функции (UDF) не могут напрямую изменять значения/свойства в любой ячейке, листе или книге — они должны только возвращать значение, как и любая другая встроенная формула Excel. Ваш UDF просто выдаст ошибку и немедленно остановится, если вы введете в него что-нибудь запрещенное.
Это нормальный способ работы в соответствии с целью разработки Microsoft.
Однако есть некоторые обходные пути, чтобы обойти это ограничение.
Формула всегда вводится в ячейку или ячейки на листе, таким образом, вызывающий объект (который вызывает пользовательскую функцию, поэтому запускает ее) является сама клетка. Возможно, Excel следит только за тем, является ли вызывающий объект ячейкой или нет — похоже, код перестает работать, только если мы пытаемся изменить лист из пользовательской функции, вызываемой из ячейки. Поэтому мы должны попытаться найти другие методы для вызова UDF.
Способ 1 — переключение мыши
Способ 2 – Таймер Windows
Во втором случае, который мы нашли после поиска в Google цитируемой здесь темы, используется забавный трюк с таймером Windows:
Ответ на вопрос «Не удается записать данные VBA в ячейки в Excel 2007/2010 в рамках функции»
Способ 3 — оценка именованных формул
Делать невозможное [Уолт Дисней] очень весело
Мы начали проверять, как Excel оценивает эти формулы, и обнаружили некоторые странности.
Мы использовали эту тестовую функцию:
Функция d(rng)
Application.Volatile
Debug.Print Application.Caller.Address
rng.Value = ""
Конечная функция
Диалоговое окно "Вычислить формулу"
- В Excel 2010 (и 2003) при запуске оценки функция UDF вызывается активной ячейкой, но код прерывается на rng.Value = "", поэтому удаление не происходит.
- В Excel 2007 диалоговое окно "Оценить" работает иначе. UDF вызывается активной ячейкой, но выполняется, поэтому происходит удаление!
Вычислить именованную формулу из VBA
Мы обнаружили, что если мы ссылаемся на эту именованную формулу в VBA, код UDF запускается, и удаление происходит также в версиях 2007 и 2010!
Поэтому мы можем легко обойти ограничения: просто создайте именованную формулу с исходной пользовательской функцией и напишите вторую пользовательскую функцию для оценки этой именованной формулы. Нам нужно только использовать эту вторую формулу на рабочем листе, которая вызовет исходную пользовательскую функцию через оценку именованной формулы. Этот трюк работает в Excel, без событий Windows или Excel. Наш Пример 1 основан на этой технике.
Пример 1. Формула самоубийства v1
Вот небольшой пример самоудаляющейся формулы, которая очистит ячейку, если значение другой ячейки равно 0. Это может быть полезно для линейных диаграмм, где нам нужна пустая ячейка для разделения строк внутри серии.
Вот что у нас есть в модуле кода:
Функция d(rng) выполнит удаление, это то, что мы используем в именованной формуле с относительной ссылкой на активную ячейку — эта ячейка будет передана функции d как rng, и это ячейка будет удалена. Если выбран C1, имя будет выглядеть так:
В функции empty_f() происходит только оценка [del] с использованием варианта (v). На листе следует использовать только этот empty_f(), например, так:
Эта формула скопирует значение D2, если оно не равно нулю. Если D2 равно нулю, то формула удаляет себя.
(см. файл: Example_1.xlsm)
Вычислить по старой формуле EVALUATE()
Во всех версиях Excel можно использовать формулы макросов Excel 4.0 в именованных формулах (определенные имена). Таким образом, мы могли бы упростить упомянутую выше оценку на основе VBA, и нам потребуется только код определяемой пользователем функции.
Пример 2. Формула самоубийства v2
В приведенном выше примере мы использовали empty_f() только для запуска вычисления именованной формулы, где мы использовали «недопустимую» пользовательскую функцию. С помощью старой формулы EVALUATE Excel 4.0 можно заменить этот код и сделать модель более понятной. Ключевым элементом является определенная формула имени. В этом случае сначала необходимо выбрать C1, а затем создать именованную формулу следующим образом:
Ячейка, на которую ссылается это имя, всегда должна быть активной ячейкой, потому что формула CELL будет считывать адрес этой ячейки и передавать его UDF d() в качестве строкового параметра. Эта ячейка будет удалена (как своего рода харакири), когда формула EVALUATE инициирует запуск пользовательской функции.
Формула, используемая на листе, теперь упрощена с использованием имени «p»:
Эта модель работает так же, как и предыдущая, но мы обнаружили одно интересное отличие в отношении вызовов UDF. Подробности можно прочитать здесь.
(см. файл: Example_2.xlsm)
Вычислить именованную формулу как список проверки
Есть еще один интересный способ запуска оценки. Используя проверку со списком, установленным по имени, Excel оценивает имя, когда вы нажимаете маленькую стрелку, чтобы открыть раскрывающийся список. Соответственно, мы могли бы использовать небольшую хитрость с UDF при проверке, как это описано в примерах 1 и 2.
Пример 3. Проверка с памятью
Можно создать проверку (используя раскрывающийся список), которая скопирует старое значение в другую ячейку.
Нам нужен только этот UDF:
Установите PrintOldValue = [=Sheet1!$D$1:$D$4]
Application.Caller.Offset(0, -1).Value = "старое значение:" & Application.Caller.Value
В функции мы устанавливаем PrintOldValue для отражения диапазона, в котором хранятся элементы списка, а затем используем эту функцию в именованной формуле:
Это имя будет списком, который мы используем при проверке. А когда вы нажимаете на маленькую стрелочку, происходит волшебство: перед выбором нового значения UDF записывает старое значение в ячейку слева от активной ячейки — как это установлено Application.Caller.Offset(0, -1 ).Выражение значения. Помните, что Application.Caller — это ячейка, с которой мы начали проверку.
Можно сказать, что это возможно без ухищрений, используя событие Worksheet_change. Да, это правильно. Мы не хотим призывать кого-либо использовать этот «незаконный» UDF. (Но мы должны выделить одно очевидное преимущество: вы можете просто скопировать ячейку, техника UDF будет работать в любом месте вашего файла - не нужно изменять/настраивать новые события Worksheet_Change.)
(см. файл: Example_3. XLSM)
Вычислить именованную формулу диаграммы
В этом посте вы можете прочитать, что изменение ширины столбца и большинство изменений формата ячеек заставляют диаграммы вызывать именованные формулы для расчета (оценки). Мы поняли, что пользовательская функция, обернутая в именованную формулу, будет оцениваться вместе с именованной формулой.
Пример 4. Расчет триггера: изменение формата, изменение ширины столбца
Мы использовали эту функцию для создания пользовательской функции, которая запускает расчет листа. После некоторых экспериментов окончательный UDF выглядит так:
Функция MyCalc()
MyCalc = 0
ActiveSheet.Range("a1").Formula = ActiveSheet.Range("a1").Formula
Конечная функция
Вторая строка (вроде бы) меняет лист и в результате происходит расчет. Вы можете легко проверить это с помощью функции volatile RAND() на листе.
Мы создали это имя:
По нашим наблюдениям, не обязательно использовать EVALUATE в имени, но мы поняли, что некоторые версии Excel без него дают сбой, поэтому такое решение более надежно.
Третий необходимый элемент — это очень простая диаграмма, в которой имя «rnd» используется для значения ряда. Диаграмма должна быть видна на листе, если она скрыта, то не вызывает пересчет названной формулы.
Подробнее об трюке UDF с диаграммой можно прочитать: СУММ по цвету: вычислить при изменении цвета
Дальнейшие выводы
Кажется, есть способы обойти ограничения, но помните, что ограничения предназначены не для того, чтобы усложнить нашу жизнь, а для того, чтобы пользователи не теряли контроль над своей работой.
Важно помнить об этом, потому что мы также заметил некоторое странное поведение этой техники.
Отладка кода
Во-первых, тестирование пользовательских функций — непростая задача, потому что точки останова не могут работать в этих особых случаях, а код не останавливается на операторе Debug.Assert False. Во многих случаях единственный способ получить некоторую информацию о том, что происходит внутри кода, — это старый добрый способ ведения журнала: метод Debug.Print. Используя это, мы могли бы определить, кто является вызывающим абонентом (с помощью Debug.Print Application.Caller.Address, который отлично работает в большинстве случаев).
Excel дважды вычисляет именованную формулу, вызванную из VBA?
Используя эту технику, мы обнаруживаем, что вышеупомянутая функция d() была вызвана дважды в примере 1 - сначала из $A$1 (по крайней мере, это то, что сообщает Application.Caller.Address), а затем из ячейки, где мы ссылается на функцию empty_f().
Зная это, неудивительно, что наша формула Suicide v1 всегда будет удалять содержимое $A$1.
Если вы хотите избежать этого, добавьте оператор If в код d():
Если rng.Address <> "$A$1" Тогда
При использовании формулы оценки макроса Excel 4.0 (пример 2) эта проблема не возникает, формула вызывается только один раз и не касается A1, поэтому мы пришли к выводу, что это лучший способ сделать « незаконные» UDF работают.
Excel аварийно завершает работу
В примерах 1 и 2 мы поняли, что в Excel 2010, если вы используете щелчок мыши для подтверждения значения ячейки (то есть вы не нажимаете Enter, Tab или стрелки), Excel немедленно аварийно завершает работу. В Excel 2007 этого не было так явно, но в некоторых случаях эта версия тоже вылетала.
Некоторые исключения для смены листа из формулы
По приведенным ниже ссылкам вы можете прочитать, что некоторые свойства могут быть изменены с помощью UDF в исключительных случаях, особенно в отношении комментариев и фигур:
Страница электронной таблицы — определяемая пользователем функция не может изменить рабочий лист . Ах да?
Ежедневная доза Excel — изменение фигур (и диаграмм) с помощью пользовательских функций
Как отобразить значение по умолчанию на основе другой ячейки в Excel?
Предположим, что между двумя списками на листе существует однозначное соответствие, что означает, что при выборе одного значения из списка 1 соответствующее ему значение в списке 2 будет отображаться автоматически, как показано на снимке экрана ниже. Как можно быстро решить эту задачу в Excel?
Отображение значения по умолчанию на основе другой ячейки с функцией ВПР
В Excel можно использовать функцию ВПР для быстрого отображения значения на основе соответствующего значения.
Выберите ячейку, в которой будет отображаться значение по умолчанию, введите эту формулу
D1 — это значение, которое вы ищете на основе, A1: B7 — это диапазон данных, который вы ищете, 2 указывает, что нужно найти значение во втором столбце искомого диапазона. Нажмите клавишу Enter, отобразится значение.
Отображать значение по умолчанию на основе другой ячейки с зависимым раскрывающимся списком
Если вы хотите, чтобы отображалось только значение по умолчанию, когда выбрано соответствующее ему значение, вы можете создать зависимый раскрывающийся список для его обработки.
<р>1. Дайте имя списку, на основе которого вы хотите отображать значение.
<р>2. Назовите значение по умолчанию с соответствующим ему значением одно за другим.
<р>3. Теперь выберите ячейку, например D1, нажмите Данные > Проверка данных > Проверка данных.
<р>4. В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список в разделе «Разрешить», затем введите =Country в текстовом поле «Источник», Country — это имя, которое вы даете списку, на основе которого на шаге 1.
<р>6. Затем в диалоговом окне "Проверка данных" выберите "Список" в разделе "Разрешить", затем введите =ДВССЫЛ($D$1) в текстовом поле "Источник", где $D$1 — это ячейка, содержащая первый раскрывающийся список.
<р>7. Нажмите «ОК».Теперь, когда вы выбираете одно значение из первого раскрывающегося списка, только соответствующее ему значение может быть выбрано и отображено во втором раскрывающемся списке.
В учебнике объясняются функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ с примерами их использования. Узнайте, как использовать функцию ЗАМЕНИТЬ с текстовыми строками, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.
На прошлой неделе мы обсуждали различные способы использования функций НАЙТИ и ПОИСК на листах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке в зависимости от ее местоположения или замены одной текстовой строки другой в зависимости от содержимого. Как вы уже догадались, я говорю о функциях Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ.
Функция Excel ЗАМЕНИТЬ
Функция ЗАМЕНИТЬ в Excel позволяет заменить один или несколько символов в текстовой строке другим символом или набором символов.
Как видите, функция ЗАМЕНА в Excel имеет 4 аргумента, и все они обязательны.
- Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите заменить некоторые символы.
- Начальный_номер — позиция первого символа в старом_тексте, который вы хотите заменить.
- Num_chars — количество символов, которые вы хотите заменить.
- Новый_текст – замещающий текст.
Например, чтобы заменить слово "sun" на "son", можно использовать следующую формулу:
=ЗАМЕНИТЬ("солнце", 2, 1, "о")
И если вы поместите исходное слово в какую-либо ячейку, скажем, A2, вы можете указать ссылку на соответствующую ячейку в аргументе old_text:
=ЗАМЕНИТЬ(A2, 2, 1, "o")
Использование функции Excel REPLACE с числовыми значениями
Функция ЗАМЕНИТЬ в Excel предназначена для работы с текстовыми строками. Конечно, вы можете использовать его для замены цифровых символов, являющихся частью текстовой строки, например:
=REPLACE(A2, 7, 4, "2016")
Обратите внимание, что мы заключаем «2016» в двойные кавычки, как обычно делаем с текстовыми значениями.
Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например:
И снова нужно заключить значение замены в двойные кавычки ("6").
Примечание. Формула ЗАМЕНИТЬ в Excel всегда возвращает текстовую строку, а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.
Использование функции Excel REPLACE с датами
Как вы только что видели, функция ЗАМЕНИТЬ прекрасно работает с числами, за исключением того, что она возвращает текстовую строку :) Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены дат. . Результаты будут весьма неловкими.
Например, у вас есть дата в формате A2, скажем, 1-окт-14, и вы хотите изменить "октябрь" на "ноябрь". Итак, вы пишете формулу REPLACE(A2, 4, 3, "Nov"), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4-го символа… и получили следующий результат:
Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Таким образом, наша формула замены изменяет последние 3 цифры в приведенном выше серийном номере на "Nov" и возвращает текстовую строку "419Nov".
Чтобы заставить функцию Excel ЗАМЕНИТЬ правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ или любой другой метод, показанный в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")
Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает, только если вы не планируете использовать измененные даты в дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию DATEVALUE, чтобы преобразовать значения, возвращаемые функцией Excel REPLACE, обратно в даты:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь"))
Вложенные функции REPLACE для выполнения нескольких замен в ячейке
Довольно часто может потребоваться выполнить несколько замен в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции REPLACE, которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте "вложение" означает помещение одной функции в другую.
Рассмотрите следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить "123456789" в "123-456-789".
Вставить первый дефис очень просто. Вы пишете обычную формулу замены Excel, которая заменяет нулевые символы дефисом, т.е. добавляет дефис на 4-й позиции в ячейке:
Результат приведенной выше формулы замены выглядит следующим образом:
Хорошо, а теперь нам нужно вставить еще один дефис в 8-ю позицию. Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы вставляете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:
В результате вы получаете телефонные номера в нужном формате:
Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:
=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))
Кроме того, вы можете преобразовать текстовые строки в реальные даты, заключив приведенную выше формулу ЗАМЕНИТЬ в функцию ДАТАЗНАЧ:
И, естественно, вы не ограничены в количестве функций, которые вы можете вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать до 8192 символов и до 64 вложенных функций в формуле).
Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:
=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")
Замена строки, которая появляется в разных позициях в каждой ячейке
До сих пор во всех примерах мы имели дело со значениями схожего характера и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, который необходимо заменить. Следующий пример продемонстрирует то, о чем я говорю.
Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с "ABC" на, скажем, "BCA". Таким образом, вам необходимо соответствующим образом обновить адреса электронной почты всех клиентов.
Но проблема в том, что имена клиентов имеют разную длину, и поэтому вы не можете указать, где именно начинается название компании. Другими словами, вы не знаете, какое значение указать в аргументе start_num функции Excel REPLACE. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:
Затем укажите указанную выше функцию НАЙТИ в аргументе start_num формулы ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca")
Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты.Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.
И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)
И эта улучшенная формула отлично работает, не так ли?
Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалоговое окно Excel НАЙТИ и ЗАМЕНИТЬ.
Функция Excel ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.
Синтаксис функции ПОДСТАВИТЬ в Excel следующий:
Первые три аргумента являются обязательными, а последний необязательным.
- Текст — исходный текст, в котором вы хотите заменить символы. Может быть предоставлена в виде тестовой строки, ссылки на ячейку или результата другой формулы.
- Old_text — символы, которые вы хотите заменить.
- Новый_текст — новые символы для замены старого_текста.
- Instance_num – экземпляр old_text, который вы хотите заменить. Если его не указать, каждое вхождение старого текста будет заменено новым текстом.
Например, все приведенные ниже формулы заменяют "1" на "2" в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:
=SUBSTITUTE(A2, "1", "2", 1) — первое вхождение "1" заменяется на "2".
=SUBSTITUTE(A2, "1", "2", 2) — заменяет второе вхождение "1" на "2".
=SUBSTITUTE(A2, "1", "2") — заменяет все вхождения "1" на "2".
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру. Например, следующая формула заменяет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.
Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)
Как и в случае с функцией ЗАМЕНА Excel, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы выполнять несколько подстановок одновременно, т. е. заменять несколько символов или подстрок одной формулой.
Предположим, что у вас есть текстовая строка вида "PR1, ML1, T1" в ячейке A2, где "PR" означает "Проект", "ML" означает "Веха", а "T" означает "Задача". Вам нужно заменить три кода полными именами. Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:
=ПОДСТАВИТЬ(A2, "ML", "Веха")
=ПОДСТАВИТЬ(A2, "Т", "Задание")
А затем вложите их друг в друга:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")
Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.
ЗАМЕНА в Excel и ЗАМЕНА в Excel
Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга в том смысле, что обе предназначены для замены текстовых строк. Различия между этими двумя функциями заключаются в следующем:
- SUBSTITUTE заменяет один или несколько экземпляров заданного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию Excel ПОДСТАВИТЬ.
- REPLACE изменяет символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel REPLACE.
- Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (instance_num), указывающий, какое вхождение old_text следует заменить на new_text.
Вот как вы используете функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач. Я благодарю вас за чтение и надеюсь увидеть в нашем блоге на следующей неделе!
Microsoft Excel – одно из самых популярных приложений для корпоративных пользователей. Это уменьшает препятствия при работе со старыми бумагами прошлых лет. В этой статье мы обсудим, как заменить текст ячейки на основе условия в Excel. Большинство людей знают, как заменить текст в Excel, но мы покажем несколько хитрых способов замены текста в Excel в зависимости от условия.
Загрузить практическую рабочую тетрадь
Загрузите следующую рабочую тетрадь, чтобы тренироваться во время чтения этой статьи.
5 методов замены текста ячейки на основе условия в Excel
В этой статье мы обсудим некоторые методы замены текста ячейки в зависимости от условия.
1. Заменить любой текст с помощью инструмента «Найти и заменить» в Excel
Мы будем использовать параметр инструмента «Найти и заменить», чтобы заменить текст из ячейки в Excel. Этот инструмент можно использовать с ленты или с помощью ярлыков мыши.
Мы применим этот метод к приведенному ниже набору данных.
Шаг 1:
- Перейдите на вкладку "Главная".
- Нажмите "Редактировать".
- Затем выберите «Заменить» в инструменте «Найти и выбрать».
Или мы также можем нажать горячие клавиши Ctrl+H.
Шаг 2:
- Появится новое диалоговое окно с названием «Найти и заменить».
В этом окне мы можем заменить любой текст. Сначала нам нужно найти текст, а затем заменить этот текст. Мы можем использовать точные слова или подстановочные знаки в случае частичного совпадения.
Шаг 3:
- Теперь мы будем использовать вопросительный знак (?) в слове, чтобы найти и заменить это слово.
- Мы использовали App?e, чтобы найти Apple и заменить его на Al.
Шаг 4:
Здесь мы видим, что произведена одна замена и показан результат.
Теперь мы применим еще одну подстановочную звездочку (*).
Шаг 5:
- Мы применяем A*e, чтобы найти Apple и заменить его на Al.
- Это может быть любое слово, начинающееся с буквы А и заканчивающееся буквой е.
Шаг 6:
Мы видим, что было выполнено 6 замен. Он заменяет любое слово, начинающееся с А и заканчивающееся на е. Он не проверяет чувствительность к регистру.
Теперь мы применим точное слово, чтобы заменить его из набора данных.
Шаг 7:
Шаг 8:
Здесь происходит одна замена. Вот как мы используем инструмент «Найти и заменить» в этом разделе.
Примечание:
Учитывать регистр: установите этот флажок, если необходимо обеспечить учет регистра.
Внутри: в этом поле можно указать, где искать и заменять слово. Это может быть лист или рабочая книга.
Поиск: предлагает два варианта. Один поиск по строке, другой поиск по столбцу.
Посмотрите: устанавливает определенные типы данных. Например, формулы, значения и т. д.
2. Используйте функцию REPLACE с условием в ячейке
Функция ЗАМЕНА используется для замены определенного количества букв в слове и формирования нового слова в Excel. В этом разделе мы обсудим, как эта функция ЗАМЕНИТЬ с условием.
Мы предполагаем, что некоторые из наших данных в наборе данных неверны. Мы исправим эти данные с помощью функции замены.
Шаг 1:
- Здесь мы добавляем в набор данных новый столбец с именем «Модификация».
Шаг 2:
- Мы обнаружили, что данные ячейки B9 неверны. Мы обновим измененные данные на Cell E9.
- Запишите приведенную ниже формулу в ячейку E9:
Шаг 3:
Мы получаем замененные данные, применяя функцию REPLACE в ячейке E9.
3. Применение функции ПОДСТАВИТЬ для замены текста в ячейке
Функция ПОДСТАВИТЬ заменяет текст из текстовой строки номером экземпляра. Здесь мы применим эту функцию, чтобы заменить текст ячейки условием.
Мы снова рассмотрим условие, упомянутое в предыдущем методе.
Мы заменим данные ячейки B9.
Шаг 1:
- Перейдите в ячейку E9.
- Примените здесь функцию ПОДСТАВИТЬ. Напишите приведенную ниже формулу:
Шаг 2:
Таким образом, мы применяем функцию ПОДСТАВИТЬ и заменяем текст из ячейки.
Похожие материалы:
4. Использование функции ЕСЛИ для замены текста в ячейке
В этом разделе мы будем применять условия с помощью функции ЕСЛИ. Функция ЕСЛИ возвращает результат на основе условия.
Здесь мы проверим все данные столбца B.
Шаг 1:
- Примените следующую формулу в ячейке E5:
Шаг 2:
Шаг 3:
- Перетащите значок маркера заполнения к последней ячейке.
Здесь функция ЕСЛИ проверяет условие. Если условие выполнено, возвращается измененный результат. В противном случае данные остаются прежними.
5. Макрос VBA для замены текста из ячейки
Мы применим фрагмент макроса VBA для замены текста из ячейки в этом разделе.
Шаг 1:
- Сначала перейдите на вкладку "Разработчик".
- Нажмите "Записать макрос".
- Появится новое окно с названием «Запись макроса».
- Название: Replace_data.
Шаг 2:
- Примените следующий код VBA к командному модулю.
Шаг 3:
Здесь мы видим, что данные Apple изменяются на Mango после запуска кода VBA.
Изменение цвета ячейки может показаться достаточно простым, но что, если вы хотите, чтобы ячейки были окрашены в определенный цвет на основе определенных критериев? В этих случаях вы должны использовать условное форматирование, чтобы изменить цвет фона вашей ячейки.
Основное форматирование цвета ячейки
Знаете ли вы, что вы можете изменить цвет фона ячеек, зайдя в форматирование ячейки? Затем вы можете выбрать раздел «Заливка» и выбрать нужный цвет для заливки ячейки.
В приведенном выше примере цвет ячейки E3 был изменен с «Без заливки» на синий цвет. Обратите внимание, что значение в ячейке E3 равно 6, и если вы измените значение в этой ячейке с 6 на любое другое значение, ячейка цвет не изменится и всегда останется синим.
Что это значит?
Это означает, что цвет ячейки не зависит от значения ячейки, поэтому независимо от того, какое значение введено в E3, цвет ячейки всегда будет синим. Это можно назвать статическим форматированием ячейки E3.
Форматирование цвета ячейки в зависимости от значения ячейки
Что делать, если вы хотите изменить цвет ячейки в зависимости от значения ячейки? Предположим, вы хотите, чтобы цвет ячейки E3 менялся при изменении значения в ней. Скажем, вы хотите раскрасить ячейку E3 на основе следующих значений.
0–10: вы хотите, чтобы цвет ячейки был синим
11-20: Вы хотите, чтобы цвет ячейки был красным
21–30: вы хотите, чтобы цвет ячейки был желтым
Любое другое значение или пустое: без цвета или без заливки.
Этого можно добиться с помощью условного форматирования. На вкладке «Главная» в группе разделов стилей нажмите «Условное форматирование» —–> «Новое правило»
Примечание. Убедитесь, что выбрана ячейка, к которой вы хотите применить условное форматирование.
Затем выберите «Форматировать только те ячейки, которые содержат», затем в первом раскрывающемся списке выберите «Значение ячейки», а во втором — «Между».
Затем в первом поле введите 0, а во втором поле введите 10, затем нажмите кнопку «Формат» и перейдите на вкладку «Заливка», выберите синий цвет, нажмите «ОК» и еще раз нажмите «ОК».
Теперь введите значение от 0 до 10 в ячейку E3, и вы увидите, что цвет ячейки изменится на синий, а если есть какое-либо другое значение или значение отсутствует, цвет ячейки изменится на пустой.
Повторите тот же процесс для 11-20 и 21-30, и вы увидите, что это число изменяется в соответствии со значением ячейки.
В приведенном выше примере вы меняете цвет ячейки только на основе значения этой ячейки. Вы также можете изменить цвет ячейки на основе значения других ячеек. Предположим, вы хотите изменить цвет ячейки E3 на основе значения в D3. Для этого нужно использовать формулу в условном форматировании.
Теперь предположим, что вы хотите изменить цвет ячейки E3 на синий, если значение в ячейке D3 больше 3, на зеленый, если значение в ячейке D3 больше 5, и на красный, если значение больше чем 10.
Снова выполните ту же процедуру.
На вкладке "Главная" в группе разделов стилей нажмите "Условное форматирование" —-> "Новое правило"
Примечание. Убедитесь, что выбрана ячейка, к которой вы хотите применить условное форматирование
Теперь выберите Использовать формулу, чтобы определить, какие ячейки нужно отформатировать, и в поле используйте формулу D3>5, затем выберите форматирование, чтобы сделать цвет ячейки зеленым.
Теперь обратите внимание, что мы выбрали ячейку E3, а не D3, потому что мы хотим изменить цвет ячейки E3 на основе значения D3. Также см. формулу «=D3>5», если D3 больше 5, то значение E3 изменится на зеленый. Нажмите "ОК" и увидите, что цвет ячейки E3 изменится на зеленый, так как ячейка D3 сейчас содержит 6.
Теперь давайте применим условное форматирование к E3, если D3 больше 3. Это означает, что если D3>3, то цвет ячейки должен стать «Синим», а если D3>5, то цвет ячейки должен остаться зеленым, как мы делали это в предыдущем примере. шаг.
Теперь, если вы выполните описанные выше шаги, как и для зеленого цвета, вы увидите, что даже если значение ячейки равно 6, оно по-прежнему показывает синий цвет, а не зеленый.Это связано с тем, что он использует последнее условное форматирование, которое вы установили для этой ячейки, а поскольку 6 также больше 3, поэтому он показывает синий цвет, но должен отображать зеленый цвет.
Итак, вам нужно упорядочить правила, которые вы применили для каждой конкретной ячейки. Это можно сделать, перейдя в раздел «Управление правилами» условного форматирования.
Вы можете увидеть все правила, примененные к этой ячейке, и там вы можете упорядочить правила или установить их приоритет с помощью кнопок со стрелками. Число больше 5 также будет больше 3. Следовательно, правило «больше 5» будет иметь приоритет, и вам придется переместить его вверх с помощью кнопки со стрелкой вверх.
Теперь, когда вы вводите 6 в D3, цвет ячейки E3 станет зеленым, а когда вы введете 4, цвет ячейки станет синим.
Все еще нужна помощь с условным форматированием или есть другие вопросы об Excel? Свяжитесь с живым экспертом по Excel здесь, чтобы получить помощь один на один. Первый сеанс всегда бесплатный.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
Я создал правило условного форматирования для одной ячейки, чтобы изменить цвет, если введенное значение совпадает с другим ячейкой. Все ячейки, где исходная ячейка равна 0, другая ячейка уже изменила цвет. Как это исправить?
Нужна помощь с формулами. Я хочу, чтобы моя формула сопоставляла количество цветов в одной ячейке с другим значением в ячейке.
Здравствуйте, у меня есть лист Google, которым поделилась команда, я установил условное форматирование, строка будет окрашиваться в определенные цвета по мере обновления значений ячеек, следующее, что я хочу сделать, это переместить все строки, которые изменились к определенным цветам к другой электронной таблице, автоматически. Это достижимо?
Есть ли формула для изменения текста (жирного/цветного) ячейки, если значение в другой ячейке превышает определенное значение? Что-то вроде оператора if-then в программировании? С другой стороны, есть ли способ заморозить число в ячейке, в которой уже есть формула, если другая ячейка находится над определенным числом?
Читайте также: