Excel подставить значение в ячейку зависимости от значения в другой ячейке
Исходный_текст — текст или ссылка на ячейку, содержащую текст, в котором заменяется часть строки. Старый_текст — заменяемый текст. Новый_текст — текст, на который заменяется старый_текст . Номер_вхождения — определяет, какое вхождение фрагмента Старый_текст нужно заменить фрагментом. Если этот аргумент определен, то заменяется только заданное вхождение фрагмента Старый_текст . В противном случае все вхождения фрагмента Старый_текст в текстовой строке заменяются фрагментом Новый_текст .
Примеры
Пусть в ячейке А6 введена строка Продажи (январь) , прибыль (январь) .
Чтобы заменить оба слова январь , на февраль , запишем формулу (см. файл примера внизу статьи ):
Функция ПОДСТАВИТЬ() может заменить только первое, только второе и т.д. вхождение слова "январь". Записав формулу =ПОДСТАВИТЬ(A2; "январь";"февраль";2) получим строку Продажи (январь), прибыль (февраль) .
Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру . Записав =ПОДСТАВИТЬ(A2; "ЯНВАРЬ";"февраль") получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() "ЯНВАРЬ" не тоже самое, что "январь".
Если строка содержит слова в разных регистрах, например, Продажи (январь), прибыль (ЯНВАРЬ) , то для замены слова январь на февраль можно порекомендовать формулу =ПОДСТАВИТЬ(СТРОЧН(A2); "январь";"февраль") В результате получим продажи ( февраль ), прибыль ( февраль ) .
Функция ПОДСТАВИТЬ() vs ЗАМЕНИТЬ()
Пусть в ячейке А2 введена строка Продажи (январь) . Чтобы заменить слово январь , на февраль , запишем формулы:
=ЗАМЕНИТЬ(A2;10;6;"февраль") =ПОДСТАВИТЬ(A2; "январь";"февраль")
т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.
Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д. Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь) . Запишем формулы: =ЗАМЕНИТЬ(A2;10;6;"февраль") =ПОДСТАВИТЬ(A2; "январь";"февраль") получим в первом случае строку Продажи (февраль), прибыль (январь) , во втором - Продажи (февраль), прибыль (февраль) . Записав формулу =ПОДСТАВИТЬ(A2; "январь";"февраль";2) получим строку Продажи (январь), прибыль (февраль) .
Кроме того, функция ПОДСТАВИТЬ() может работает с учетом регистра, а ЗАМЕНИТЬ() по понятным причинам не может.
Подстановка значений в Microsoft Excel подразумевает замену одной части текста на другую с выводом результата в отдельной ячейке. Это избавит вас от необходимости самостоятельно вносить одни и те же коррективы в большое количество полей. Осуществляется подобная манипуляция с использованием всего одной функции, но далее я расскажу еще и о ее модернизации для тех, кто исправляет ошибочный импорт, переводя текстовые значения в числовые.
Когда это может понадобиться?
Отвечу на самый главный вопрос по теме: «Когда может понадобиться подстановка значений?». Выполнение этой операции в первую очередь подразумевает замену любых символов. Например, вы импортировали диапазон значений из другой программы, где для разделения дробной части вместо запятой используется точка. Соответственно, Excel подумает, что это текстовые значения, и откажется использовать их при создании функций.
Использование функции ПОДСТАВИТЬ
Для начала разберемся с тем, как работает синтаксис основной функции – ПОДСТАВИТЬ. Объявим ее и рассмотрим каждый аргумент, чтобы при дальнейшем использовании у вас не возникло никаких проблем.
Для начала выберите необходимую клетку, нажмите по ней левой кнопкой мыши для активации и объявите функцию =ПОДСТАВИТЬ().
В скобках напишите номер ячейки, с которой нужно взять исходный текст.
Поставьте точку с запятой для отделения аргументов, в кавычках напишите символ или несколько символов, от которых хотите избавиться.
Снова отделите аргумент и в кавычках напишите новый символ для замены.
Это вся формула, поэтому можете нажать Enter и посмотреть, что получилось в итоге.
Для наглядности оставляю используемую мной формулу целиком, чтобы вы могли редактировать ее под себя и не вводить каждый символ вручную. Это поможет значительно сэкономить время.
Добавление функции ЗНАЧЕН
Теперь модернизируем формулу, чтобы в итоге получить числовое значение, а не текст, как в исходном варианте. Для этого понадобится функция ЗНАЧЕН, которую просто нужно вписать в строку, поставив дополнительные кавычки.
Выполните редактирование, чтобы в итоге получить примерно такой результат: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;".";",")).
Нажмите Enter для применения изменений и обратите внимание на то, что теперь числа отображаются справа в ячейке, что говорит об успешной смене формата с текстового на числовой.
Остается только растянуть формулу на все остальные клетки, чтобы завершить замену в массиве данных. Зажмите правый нижний угол ячейки и потяните вниз.
Посмотрите на таблицу и удалите лишнюю информацию, которая после преобразования больше не понадобится.
Работа с массивами данных в Microsoft Excel часто заставляет юзера вносить правки в текст, что особенно актуально при импорте дробных чисел. Теперь вы узнали, что решить проблему можно при помощи всего одной формулы, состоящей из двух функций. Используйте это, чтобы оптимизировать взаимодействие с электронной таблицей.
Функция ПОДСТАВИТЬ в Excel выполняет динамическую замену определенной части строки на указанное новое значение и возвращает новую строку, содержащую замененную часть текста. Благодаря этой функции можно подставлять значения из другой ячейки. Рассмотрим возможности функции на конкретных примерах в Excel.
Функция ПОДСТАВИТЬ при условии подставляет значение
Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.
Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:
Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:
- B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
- “NaN” – фрагмент текста, который будет заменен;
- 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.
Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:
Таким же образом функция подставляет значения и другой таблицы при определенном условии.
Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ
Пример 2. Провайдер домашнего интернета хранит данные о своих абонентах в таблице Excel. Предположим, улица Садовая была переименована в Никольскую. Необходимо быстро произвести замену названия улицы в строке данных об адресе проживания каждого клиента.
Для выполнения заданного условия используем формулу:
Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве Ctrl+Shift+Enter.
В результате получим:
Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ
В данном случае для поиска числовых значений номера дома воспользоваться встроенными функциями не удастся. Рациональнее всего использовать регулярные выражения. По умолчанию, в Excel отсутствует функция для работы с регулярными выражениями, однако ее можно добавить следующим способом:
- Открыть редактор макросов (Ctrl+F11).
- Вставить исходный код функции (приведен ниже).
- Выполнить данный макрос и закрыть редактор кода.
Public Function RegExpExtract(Text As String , Pattern As String , Optional Item As Integer = 1) As String
On Error GoTo ErrHandl
Set regex = CreateObject( "VBScript.RegExp" )
regex.Pattern = Pattern
regex. Global = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
RegExpExtract = matches.Item(Item - 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function
Для того, чтобы воспользоваться этой формулой, в любой ячейке необходимо ввести следующее:
Регулярные выражения могут быть различными. Например, для выделения любого символа из текстовой строки в качестве второго аргумента необходимо передать значение «\w», а цифры – «\d».
Для решения задачи данного Примера 3 используем следующую запись:
Данный способ может показаться громоздким, однако он весьма удобен для работы с таблицами, содержащими большое количество данных.
Обычный Выпадающий (раскрывающийся) список отображает только один перечень элементов. Связанный список – это такой выпадающий список , который может отображать разные перечни элементов, в зависимости от значения другой ячейки. Потребность в создании связанных списков (другие названия: связанные диапазоны , динамические списки ) появляется при моделировании иерархических структур данных. Например:
- Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
- Город – Улица – Номер дома . При заполнении адреса проживания можно из списка выбрать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).
В этой статье рассмотрен только двухуровневый связанный список . Многоуровневый связанный список рассмотрен в одноименной статье Многоуровневый связанный список . Создание иерархических структур данных позволяет избежать неудобств выпадающих списков связанных со слишком большим количеством элементов. Связанный список можно реализовать в EXCEL, с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список (пример создания приведен в данной статье) или с помощью элемента управления формы Список (см. статью Связанный список на основе элемента управления формы ).
Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.
Задача : Имеется перечень Регионов , состоящий из названий четырех регионов. Для каждого Региона имеется свой перечень Стран . Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего списка нужную ему Страну из этого Региона .
Таблицу, в которую будут заноситься данные с помощью Связанного списка , разместим на листе Таблица . См. файл примера Связанный_список.xlsx
Список регионов и перечни стран разместим на листе Списки .
Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран ( В1:Е1 ).
Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны ). Быстрее всего это сделать так:
- выделитьячейки А1:Е6 на листе Списки (т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран );
- нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
- Убедиться, что стоит только галочка «В строке выше»;
- Нажать ОК.
Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.
Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)
На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .
Теперь сформируем выпадающий список для столбца Страна (это как раз и будет желанный Связанный список ).
Важно, чтобы при создании правила Проверки данных активной ячейкой была B5 , т.к. мы используем относительную адресацию .
Тестируем. Выбираем с помощью выпадающего списка в ячейке A 5 Регион – Америка , вызываем связанный список в ячейке B 5 и балдеем – появился список стран для Региона Америка : США, Мексика …
Теперь заполняем следующую строку. Выбираем в ячейке A 6 Регион – Азия , вызываем связанный список в ячейке B 6 и опять балдеем: Китай, Индия …
Необходимо помнить, что в именах нельзя использовать символ пробела. Поэтому, при создании имен, вышеуказанным способом, он будет автоматически заменен на нижнее подчеркивание «_». Например, если вместо Америка (ячейка В1 ) ввести « Северная Америка » (соответственно подкорректировав ячейку А2 ), то после нажатия кнопки Создать из выделенного фрагмента будет создано имя «Северная_Америка». В этом случае формула =ДВССЫЛ(A5) работать не будет, т.к. при выборе региона « Северная Америка » функция ДВССЫЛ() не найдет соответствующего имени. Поэтому формулу можно подкорректировать, чтобы она работала при наличии пробелов в названиях Регионов : =ДВССЫЛ(ПОДСТАВИТЬ(A5;" ";"_")) .
Теперь о недостатках . При создании имен с помощью кнопки меню Создать из выделенного фрагмента, все именованные диапазоны для перечней Стран были созданы одинаковой длины (равной максимальной длине списка для региона Европа (5 значений)). Это привело к тому, что связанные списки для других регионов содержали пустые строки.
Конечно, можно вручную откорректировать диапазоны или даже вместо Именованных диапазонов создать Динамические диапазоны . Но, при большом количестве имен делать это будет достаточно трудоемко. Кроме того, при добавлении новых Регионов придется вручную создавать именованные диапазоны для их Стран .
Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка . Рассмотрим этот подход в другой статье: Расширяемый Связанный список .
При заполнении таблиц часто приходится вводить повторяющиеся или похожие значения.
Предположим, что пользователь заполняет таблицу доходов семьи.
Пусть ячейка В5 содержит значение « прибавка за выходные » . Предположим, что в ячейку В6 также необходимо ввести значение « прибавка за выходные» . Можно, конечно, использовать мышь и Маркер заполнения , и с его помощью скопировать значение из ячейки B5 . Но если обе руки заняты на клавиатуре, то использование мыши может замедлить работу.
Для исключения повторного набора текста можно воспользоваться механизмом автозаполнения ячеек. Например, если в ячейку B 6 начать вводить текст «зарплата» , то после ввода первой буквы «з» EXCEL автоматически предложит заполнить ячейку подходящим текстом из ячеек выше (находящихся в той же Текущей области ), и нам останется только нажать ENTER . Если использовать автозавершение записей не требуется, эту функцию можно отключить (Кнопка Microsoft Office/ Параметры Excel , затем щелкните вкладку Подробнее , и затем в меню Параметры правки снимите флажок Автозавершение значений ячеек ).
Но, в нашем случае (мы вводим значение « прибавка за выходные » ) этим механизмом нельзя воспользоваться пока в ячейку B 6 не будет введено хотя бы « прибавка за в » , так как в этом столбце (в ячейке B 4 ) существует запись похожего содержания «прибавка за доп.работу», и EXCEL, естественно, не знает какое из двух значений нужно предложить пользователю.
Решение: Если, как в нашем случае, нужное значение содержится в соседней верхней ячейке, то для быстрого копирования значения можно использовать комбинацию клавиш CTRL + D (или через меню Главная/ Редактирование/ Заполнить/ Вниз ). Запомнить несложно – D означает первую букву английского слова Down (вниз).
Если нужно скопировать вышестоящую строку, например, состоящую из 3-х полей (столбцов), то выделив сразу 3 ячейки и нажав CTRL + D ,
получим копию строки сверху.
Аналогично можно заполнить ячейку значением соседней ячейки слева. Для этого используйте комбинацию клавиш CTRL + R .
Читайте также: