Подстановка в excel

Создание формулы подстановки с помощью мастера подстановок

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Обратите внимание для пользователей Office 2003 Чтобы продолжить получать обновления для системы безопасности для Office, убедитесь, что вы используете Office 2003 с пакетом обновления 3 (SP3). Поддержка Office 2003 заканчивается 8 апреля 2014 г. Если вы используете версию Office 2003 после окончание поддержки, для получения важных обновлений для Office, необходимо выполнить обновление до более поздней версии, например Office 365 или Office 2013. Дополнительные сведения читайте в статье прекращение поддержки Office 2003.

В выпусках Excel 2007 и Excel 2003 мастер подстановок создает формулы подстановки на основе данных листа с подписями строк и столбцов. Мастер подстановок позволяет находить остальные значения в строке, если известно значение в одном столбце, и наоборот. В формулах, создаваемых мастером подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

Мастер больше не учитываются в Excel 2010. Он был заменен мастером функций и доступны функции ссылки и поиска (Справка).

Использование мастера подстановок в Excel 2007

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решений нажмите кнопку Подстановка .
Если недоступна команда ” Просмотр “, выполните следующие действия, чтобы загрузить надстройку мастера подстановок.

Нажмите кнопку Microsoft Office , выберите пункт Параметры Excelи выберите категорию надстройки .

В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.

В области Доступные надстройки установите флажок рядом с пунктом Мастер подстановок и нажмите кнопку ОК.

Следуйте указаниям мастера.

К началу страницы

Использование мастера подстановок в Excel 2003

В меню Сервис выберите пункт Надстройки, щелкните поле Мастер подстановок, а затем нажмите кнопку ОК.

Щелкните ячейку в диапазоне.

В меню Сервис выберите пункт Подстановка.

Следуйте инструкциям мастера.

Что произошло с мастером подстановок в Excel 2010?

Мастер больше не учитываются в Excel 2010. Эта функция был заменен мастером функций и доступны функции ссылки и поиска (Справка).

Формулы, созданные с помощью этого мастера, будут действовать в Excel 2010. Их можно изменять другими способами.

Источник: support.office.com

Таблицы подстановки в Excel

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул. Возможные значения одного или двух аргументов функции представляют в виде списка или таблицы. При использовании одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в функцию (формулу), заданную пользователем, а затем выстраивает результаты также соответственно в строку или в столбец.

Изменения двух исходных значений, просматривая результаты только одной формулы. При использовании таблицы с двумя переменными значениями одно из них располагается в столбце, другое — в строке; результат вычислений получают на пересечении строки и столбца.

На конкретном примере

Задание: использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами. Рассчитать ежемесячные выплаты по займу и платежи по процентам. Исходные данные приведены на рисунке ниже.

Порядок работы

  • Запустите MicrosoftExcel и создайте новую электронную книгу.
  • Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

Таблица — заготовка для решения

  • Расчет ежемесячных выплат по займу происходит с помощью функции ПЛТ (). В ячейку В5 введите формулу:
  • =ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

    • Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

    =ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

    Краткая справка. При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.

    • После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (смотрите рисунок ниже). В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки В9 и С9 содержат формулы). Слева расположить различные значения исходных данных, которые необходимо протестировать. Подготовку исходного диапазона данных — различных величин процентной ставки в рассматриваемом примере можно осуществить с помощью маркера автозаполнения.
    • Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом таблицы должен быть столбец исходных значений, а в самой верхней строкой должна быть строка анализируемых формул.

    Расчет платежей

    • Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

    Подстановка данных

    Результат

    Источник: abuzov.ru

    Поиск и подстановка по нескольким условиям

    Постановка задачи

    Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 – без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

    Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

    Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

    Способ 1. Дополнительный столбец с ключом поиска

    Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

    Читайте также:  Как посчитать сумму в эксель

    Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

    Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

    Плюсы : Простой способ, знакомая функция, работает с любыми данными.

    Минусы : Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице – допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

    Способ 2. Функция СУММЕСЛИМН

    Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

    Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

    Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

    Способ 3. Формула массива

    О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

    1. Выделите пустую зеленую ячейку, где должен быть результат.
    2. Введите в строке формул в нее следующую формулу:

  • Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.
  • Как это на самом деле работает:

    Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

    Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

    Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны “с запасом” или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

    Источник: www.planetaexcel.ru

    Программа подстановки данных из одного файла в другой (замена функции ВПР)

    Программа предназначена для сравнения и подстановки значений в таблицах Excel.

    Если вам надо сравнить 2 таблицы (по одному столбцу, или по нескольким),
    и для совпадающих строк скопировать значения выбранных столбцов из одной таблицы в другую,
    надстройка «Lookup» поможет сделать это нажатием одной кнопки.

    То же самое можно сделать при помощи формулы =ВПР(), но:

    • формулы могут тормозить работу с файлом при пересчёте, если объём данных большой (много строк или столбцов)
    • если источник данных или файл, в который подставляются данные, каждый раз новый, — требуется время на прописывание или редактирование формул
    • если с файлами работают люди, «далёкие» от Excel, – их проще обучить нажимать одну кнопку, чем объяснять им, как прописывать эти формулы
    • иногда нужны дополнительные возможности (не учитывать заданные слова и символы при сравнении, выделять цветом изменения, копировать недостающие строки, и т.д.)

    В настройках программы можно задать:

    • где искать сравниваемые файлы (использовать уже открытый файл, загружать файл по заданному пути, или же выводить диалоговое окно выбора файла)
    • с каких листов брать данные (варианты: активный лист, лист с заданным номером или названием)
    • какие столбцы сравнивать (можно задать несколько столбцов)
    • значения каких столбцов надо копировать в найденные строки (также можно указать несколько столбцов)
    • каким цветом подсвечивать совпавшие и ненайденные строки (для каждого из 2 файлов)
    • исключаемые при сравнении символы и фразы

    Как скачать и протестировать программу

    Для загрузки надстройки Lookup воспользуйтесь кнопкой Скачать программу

    Если не удаётся скачать надстройку, читайте инструкцию про антивирус

    Если скачали файл, но он не запускается, читайте почему не появляется панель инструментов

    Это полнофункциональная пробная (TRIAL) версия, у вас есть 15 дней бесплатного использования ,
    в течение которых вы можете протестировать работу программы.

    Этого вполне достаточно, чтобы всё настроить и проверить, используя раздел Справка по программе

    Если вам понравится, как работает программа, вы можете Купить лицензию

    Лицензия (для постоянного использования) стоит 1200 рублей .

    В эту стоимость входит активация на 2 компьютера (вы сможете пользоваться программой и на работе, и дома).
    Если нужны будут дополнительные активации, их можно будет в любой момент приобрести по 500 рублей за каждый дополнительный компьютер.

    • 244153 просмотра

    Комментарии

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

    Здравствуйте!
    По видео я понял, что отличающиеся строки выстраиваются (дополнительно) внизу таблицы. Если она большая – то листать вниз – очень неудобно. Есть ли возможность ОТЛИЧИЯ встраивать на отдельный лист: к примеру синенькое – из прайса поставщика ( у меня этого нет), зелененькое – из моего прайса ( у поставщика нет). Будет крайне наглядно. Спасибо!

    Юрий, вот теперь всё понятно.
    Нажатием одной кнопки в надстройке Lookup такое не сделать
    В 3 нажатия кнопок – легко (3 разных набора настроек)

    Первое нажатие подставляет данные в ТРЕТИЙ столбец (во втором остались ранее подставленные значения)
    Второе нажатие сравнивает второй и третий столбцы, помечая цветом различия
    Третье нажатие копирует третий столбец во второй, и затирает третий столбец

    Инструкция, как сделать 3 кнопки запуска с разными настройками на панели инструментов:
    https://excelvba.ru/programmes/Lookup/manuals/SettingSwitcher

    Игорь, добрый день!
    К примеру есть файл (товар откуда берем данные) состоящий из двух столбцов. Столбец 1, это наименование товара, столбец 2, это количество. Файл куда будем подставлять данные (товар куда вставляем данные) так же состоит из 2 столбцов с такими же названиями. Сравнивать будем файлы по первому столбцу и в случае совпадения значения подставляем данные из второго столбца файла (товар откуда берем данные) во второй столбец файла (товар куда вставляем данные).
    При первом сравнении в файле (товар куда вставляем данные) будут получены значения из файла (товар откуда берем данные).
    А теперь вопрос. Если в первом файле изменилось значение в столбце 2, то при следующем сравнении, это значение заменит во втором файле уже ранее полученное значение. Как выделить цветом или еще каким то образом ячейку с этим изменившимся значением? Важно понимать какие ячейки файла (товар куда вставляем данные), в столбце 2 поменяли значения и все.

    Читайте также:  Excel обновить сводную таблицу в excel

    Юрий, при такой формулировке задания — не смогу сделать.
    (что с чем сравниваться должно, что где как должно выделяться, — не понятно)

    Игорь, добрый день!
    Подскажите пожалуйста есть ли возможность реализовать в программе следующий функционал. В столбце, в котором меняются данные, выделить ячейки значение в которых было изменено?
    Можно цветом или другим иным способом, главное, что бы эти ячейки отличались от стальных изначально прописанных в таблице. Далее прайс будет обрабатываться программой для обработки прайсов в которой можно будет отфильтровать эти ячейки по этому отличию.

    Здравствуйте, Алексей.
    Программа на такое не рассчитана, но если поколдовать с настройками, и сделать в пару нажатий (с разными настройками), то теоретически можно

    Как-то можно подставить значения в столбец пропуская те которые уже там проставлены?

    У меня – точно нет (я делаю программы только под windows)

    Скажите, а под mac os аналоги есть?

    Добрый день, можно ли как-то доработать обработку чтобы настройки сохранялись как в обработчике прайсов, т.е. для сравнения таких 2х файлов сравнивать так, для других 2х уже можно было бы выбрать другие настройки?

    Напишите мне на почту, прикрепив XML файл с настройками программы (на форме настроек есть слева снизу кнопка «Экспортировать настройки в файл»)

    Здравствуйте. Планируется ли правка кода программы по последней проблематике (изменение значений только в ячейках где меняются данные не меняя остальные в этом же столбике). Спасибо.

    А галочку эту вы в настройках включали.
    Конечно включал и даже При такой галочке он вместо значения тянет ПО ВСЕМУ СТОЛБЦУ опять же формулу из которого значение состоит.

    А галочку эту вы в настройках включали?

    Так устроена программа.
    Тогда повременю с покупкой, меня такой вариант не устраивает.
    Как выйдет обновлению, с удовольствием приобрету.

    Так устроена программа
    В столбцах, куда подставляются данные, остаются только значения
    Если надо сохранить формулы, – подставляйте данные в другой столбец

    Почему программа Lookup меняет формулы на значения.
    Поясню. В одном столбце 1000 строк, во всех стоят формулы. При подстановке значений ВСЕ эти формулы меняются на значения, хотя в этот столбец по поиску вносится всего 100 подстановок. То есть 900 ячеек должны остаться не тронутыми с формулами как и было, а 100 ячеек как раз формулы заменятся значениями из подстановки

    Не должно такого быть
    Программа вносит изменения только в те столбцы, которые заданы как столбцы для подстановки

    Почему программа Lookup меняет формулы на значения в тех ячейках столбика, где ничего не подставляется ? Как раз этого делать и не нужно, из этого теряется вся суть данной программы.

    Дай бог тебе здоровья добрый человек. Второй раз меня выручаете!

    С касперским обычно проблем нет
    Только что проверил файл на их сайте, — пишет, что проблем не найдено:

    сегодня касперский стал определять как вирус и удалять
    Пишет – Trojan:O97M/Foretype.A!ml
    Эта опасная программа выполняет команды злоумышленника

    Здравствуйте, Виктор
    Код программы закрыт.
    Для вашего случая программа не подойдёт (она сравнивает только по полному совпадению)
    Переделать (доработать) программу можно, но доработка будет стоить недешево (около 1500 руб дополнительно к стоимости программы)

    Здравствуйте, подскажите после покупки, код программы будет виден, или можно ли как то переделать что бы например при нахождении двух данных в 1 книге ячейке A1 “1000,2000” B1 “Ок” и сопоставлении их во 2 книге A1, A2 проставлялись так же B1, B2 значением из 1 книги

    что то вроде
    1 книга
    A1 1000,2000 B1 OK

    2 книга
    A1 1000 B1 OK
    A2 2000 B2 OK

    Поиск в надстройке Lookup идет по полному совпадению ячеек (искомое значение равно найденному)
    А поиск, выполняемый вами вручную в Excel, идет по частичному совпадению (вхождению искомого текста в ячейку)

    В вашем случае, поиск по частичному совпадению выполнять нельзя, — будете искать APV3, а будет также найдена строка с APV31 (и потом кучу времени потратите на поиск ошибок, угадывая, что с чем могло еще так совпасть)

    После настройки и запуска надстройки оказалось, что он не может найти артикул в тексте и срабатывает только если удалить лишний текст в ячейке. На фото правая таблица содержит 35 000 строк и редактировать каждую ячейку займет колоссальное кол-во времени. При этом видно, что обычный поиск по документу всё находит. Возможно всё дело в неправильной настройке? Или лучшим решением будет заказать у вас макрос который справится с поставленной задачей? Спасибо! Очень жду ответа.

    Спасибо за подсказку. Покупаю надстройку. ))

    Источник: excelvba.ru

    Подстановка данных в excel функция ВПР

    • 22 Апрель, 2011 –
    • Уроки Excel –
    • Tags : таблицы excel, уроки excel, эксель данные
    • 75 Comments

    Довольно распространенная ситуация, когда данные из одной таблицы в excel надо подставить в другую.

    Рассмотрим на примере: допустим есть 2 таблицы — продажи и прайс-лист. Задача-подставить цены из таблицы прайс-лист в таблицу продажи, чтобы можно было в итоге посчитать общую сумму продаж.

    Предлагаю 2 варианта выполнения этой задачи.

    Вариант 1. Использовать функцию ВПР. скачать пример

    Функция ВПР ищет заданное значение в крайнем левом столбце указанной таблицы, двигаясь сверху вниз. В нашем примере функция ВПР будет выполнять поиск «корм для кошек» в таблице «прайс-лист» и найдя его, подставит цену корма для кошек в таблицу «продажи». Ровно так, как показано на рисунке выше. Сразу хочу отметить 2 важных условия для удачной работы формулы: 1-столбец «наименование товара» в обоих таблицах должен иметь одинаковый формат, 2-столбец «наименование товара» в таблице прайс-лист должен быть отсортирован по возрастанию.

    Все, вызываем функцию ВПР. Щелкаем в той ячейке, куда будет подставляться цена( С5 нашего примера), далее жмем значок fx на панели инструментов (либо Вставка-функция) и в открывшемся окошке выбираем ссылки и массивы и далее ВПР. Как показано на картинке.

    и жмем ОК. Откроется следующее окно, в котором и задаются параметры подстановки:

    искомое значение — щелкаем по той ячейке, в которой находится искомое значение — у нас это корм для кошек

    таблица — это таблица, из которой берутся данные. Щелкаем на квадратик с красной стрелкой и мышкой обводим нашу таблицу прайс-лист, жмем Enter

    номер столбца — здесь нужно указать именно порядковый номер столбца таблицы из которой будут браться цены. В нашем примере столбец номер один-наименование, столбец номер 2-цена. Таким образом, мы ставим цифру 2

    Читайте также:  Как в эксель сделать гиперссылку

    интервальный просмотр – здесь можно ввести либо ЛОЖЬ либо ИСТИНА. Других вариантов нет. Можно либо словами написать, либо ввести цифру 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если вводим ЛОЖЬ — выполняется поиск точного соответствия заданному параметру, если вы введете ИСТИНА, то таким образом Вы даете разрешение на поиск приблизительно соответствия, то есть поиск максимально похожего заданному параметру. Чтобы было меньше ошибок, лучше всегда указывать ЛОЖЬ, т.е. поиск точного соответствия.

    Все, нажимаем ОК и радуемся:)

    Для еще большего удобства работы с такой формулой, можно таблице прайс-лист присвоить имя, и потом в формуле указывать не диапазон поиска, и просто имя таблицы. Для этого выделяем таблицу прайс-лист и жмем правой кнопкой мыши, там находим «имя диапазона»

    В новом открывшемся окне пишем имя диапазона, например «прайс»

    И тогда в формуле ВПР можно просто впечатать имя диапазона


    И второй способ решения данной задачи — подстановка данных в excel через функцию СУММЕСЛИ

    Источник: excelru.ru

    Примеры функции ПОДСТАВИТЬ для замены текста в ячейке 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.

    В результате получим:

    Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

    Пример 3. При составлении таблицы из предыдущего примера была допущена ошибка: все номера домов на улице Никольская должны быть записаны как «№№-Н», где №№ – номер дома. Как быстро исправить ошибку?

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

    1. Открыть редактор макросов (Ctrl+F11).
    2. Вставить исходный код функции (приведен ниже).
    3. Выполнить данный макрос и закрыть редактор кода.

    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 используем следующую запись:

    1. Функция ЕСЛИОШИБКА используется для возврата исходной строки текста (B2), поскольку результатом выполнения функции RegExpExtract(B2;”Никольская”) будет код ошибки #ЗНАЧ!, если ей не удалось найти хотя бы одно вхождение подстроки «Никольская» в строке B2.
    2. Если результат выполнения сравнения значений RegExpExtract(B2;”Никольская”)=”Никольская” является ИСТИНА, будет выполнена функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;”d+”);RegExpExtract(B2;”d+”)&”-Н”), где:
    • a. B2 – исходный текст, содержащий полный адрес;
    • b. RegExpExtract(B2;”d+”) – формула, выделяющая номер дома из строки с полным адресом;
    • c. RegExpExtract(B2;”d+”)&”-Н” – новый номер, содержащий исходное значение и символы «-Н».

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

    Особенности использования функции ПОДСТАВИТЬ в Excel

    Функция ПОДСТАВИТЬ имеет следующую синтаксическую запись:

    • текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
    • стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
    • нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
    • [номер_вхождения] – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:
    1. Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
    2. В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».

    Примечания:

    1. Аргумент [номер_вхождения] должен быть задан из диапазон целых положительных чисел от 1 до n, где n определяется максимально допустимой длиной строки, содержащейся в объекте данных (например, в ячейке).
    2. Если в текстовой строке, представленной в качестве аргумента текст не содержится фрагмент, переданный в качестве аргумента стар_текст, функция ПОДСТАВИТЬ вернет строку текст без изменений.
    3. Если число вхождений заменяемого фрагмента в обрабатываемой строке меньше, чем числовое значение, переданное в качестве аргумент [номер_вхождения], функция ПОДСТАВИТЬ вернет текстовую строку в исходном виде. Например, аргументы функции («а 1 а 2 а 3»;«а»;«б»;4) вернут строку «а 1 а 2 а 3».
    4. Рассматриваемая функция чувствительная к регистру, то есть строки «Слово» и «слово» не являются тождественными.
    5. Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.

    Источник: exceltable.com