text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как с помощью Excel подготовить выборку из данных с частично совпадающими названиями

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

Для этого потребуется:

Какие данные потребуются для подготовки выборки по неточному совпадению в Excel

Для сортировки по неточному совпадению в Excel понадобятся два вида исходных данных:

Допустим, из смет двух предприятий группы компаний необходимо составить отчет о затратах на материалы по статьям затрат (см. рисунок 1). В сметах названия материалов не совпадают, как например, один и тот же щебень может быть указан как «Щебень в а/бетон» и «Щебень в основание».

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

Как задать формулу сортировки данных по неточному совпадению в Excel

Чтобы задать формулу сортировки данных по неточному совпадению в Excel, предстоит к таблицам с исходными данными добавить дополнительный столбец «Группировка по статьям», в котором автоматом будут проставляться статьи (см. рисунок 4). В его первой ячейке (см. на рисунке 4 ячейку Н4) нужно указать формулу сортировки, которая автоматически выберет из справочника статей затрат нужную. Сформировать эту формулу можно, если:

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

Определить алгоритм выборки. Чтобы настроить автоматическую выборку (поиск заданных наименований), необходимо итоговую формулу настроить так, чтобы она искала в двух исходных таблицах (см. рисунок 1) в каждой строке по столбцу «Наименование работ, материалов» фрагмент текста, равный значению из справочника (см. рисунок 2). Далее при положительном исходе поиска в дополнительном столбце автоматически должно появляться соответствующее значение справочника, при отрицательном исходе – пустая ячейка. Группировку статей в таблицах по новому столбцу можно сделать как по материалу, так и по статье затрат (см. рисунок 3).

Составить итоговую формулу. Для реализации желаемого алгоритма выборки нужно ввести следующую формулу поиска по неточному совпадению по статьям затрат (см.  рисунок 4):

{ЕСЛИ(ЕНД(ИНДЕКС(Статья;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Материал;B4));0)));"";
ИНДЕКС(Статья;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(Материал;B4));0)))}, где:
– ЕСЛИ () – функция, позволяющая при отсутствии искомого наименования (обнаружении статей, несопоставимых друг с другом) отражать пустую ячейку вместо ошибки поиска «#Н/Д»;
– ЕНД () – функция, которая уточняет, доступно значение в искомой в ячейке или нет. Если не доступно («#Н/Д»), то отражается значение «ИСТИНА», иначе – «ЛОЖЬ»;
– ИНДЕКС () – отражает любое значение в искомой ячейке, находящееся на пересечении строки и столбца. Если значения там нет, формула выдает ошибку «#Н/Д»;
– ПОИСКПОЗ () – эта функция позволяет найти в массиве искомое числовое значение по точному совпадению. Если значение не обнаружено, формула выдаст ошибку «#Н/Д».

– ПОИСКПОЗ (ИСТИНА; ЕЧИСЛО (ПОИСК (Материал;B4));0) – фильтрует результаты поиска, равные «ИСТИНА»;
– ЕЧИСЛО () – эта функция проверяет содержимое ячейки на предмет наличия или отсутствия в нем числа. Результат такой проверки отражает: «ИСТИНА» или «ЛОЖЬ». Использование ЕЧИСЛО (ПОИСК (Материал;B4)) позволяет нейтрализовать в поиске варианты «#ЗНАЧ!»;
– ПОИСК (Материал;B4) – эта функция ищет в тексте ячейки похожие слова из справочника статей (см. рисунок 2). Поиск ведется по диапазону, которому было присвоено имя «Материал». Результат поиска  – числовое значение, которое указывает первую позицию ячейки, где расположено найденное слово. Если текста не обнаружено, результат отображается как «#ЗНАЧ!»;
– ИНДЕКС (Статья; ПОИСКПОЗ (ИСТИНА; ЧИСЛО (ПОИСК(Материал;B4));0)) – выдает искомое значение статьи затрат из справочника.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

Вы получите доступ к уникальному контенту, который закрыт для незарегистрированных пользователей!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

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