Заполнение реестра с помощью функции «ВПР»
Уважаемая редакция! Часто приходится формировать различные реестры в Excel. Когда значения в данных отличаются, срабатывает автозаполнение по первой букве. Но в моем случае данные похожи (номера счетов, первые символы в названии предприятий и т. п.). В такой ситуации автозаполнение Excel не помогает. Подскажите, как решить проблему? Спасибо.
А. Васильев, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Насколько понятно из вопроса, речь идет о заполнении базы данных. Действительно, Excel предлагает несколько способов, которые хорошо себя зарекомендовали при наличии четко выраженного
отличительного признака в разных элементах данных. Когда такого признака нет, придется его внедрить и сделать автозаполнение на основе формул. Я покажу это на примере из своей практики.Мне понадобилось создать реестр договоров с контрагентами, изображенный на рис. 1. База данных была большая, но вся информация касалась двух десятков контрагентов. Печатать все данные, конечно, не хотелось. Я воспользовался справочником и для этого создал лист. В примере на рис. 1 он называется «
Спр». На нем подготовил данные, как показано на рис. 2. 
Важно!
Справочник «Спр» должен быть отсортирован по возрастанию поля «№».Внешне эти две таблицы очень похожи, но между ними есть принципиальное отличие — это база данных. В ней много строк, каждая из них описывает данные конкретного договора. В разных договорах с одним предприятием информация о контрагенте повторяется, а вот сумма договора и его номер (колонка «№ дог») уникальны. На листе «Спр» записан справочник с данными о контрагентах. Никаких повторений здесь нет: одна строка — один контрагент. Поля «№ дог», «Дата» и «Сумма договора» в справочнике отсутствуют.
Для связи между таблицами я ввел рабочее поле «№». Оно указывает номер контрагента на листе «Спр». Осталось сделать автоматическое заполнение полей в «База» для контрагента с номером в поле «№». Для этого воспользуемся функцией «ВПР()». Описывать работу функции подробно не буду, поэтому сразу дам формулы. Они показаны в таблице.
| Адрес ячейки | Формула | Что означает |
| D2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;2)) | Подставляет из справочника наименование предприятия |
| Е2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;3)) | Подставляет адрес предприятия |
| F2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;4)) | Подставляет из справочника ОКПО |
| G2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;5)) | Подставляет номер расчетного счета |
| H2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;6) | Подставляет наименование банка |
| I2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;7) | Подставляет МФО |
| J2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;8) | Подставляет Ф. И. О. руководителя |
| L2 | =ЕСЛИ($A2=";";ВПР($A2;Спр!$A:$K;9) | Подставляет налоговый номер плательщика НДС |
| M2 | =ЕСЛИ($A2=" ";"";ВПР($A2;Спр!$A:$K;10) | Подставляет номер свидетельства плательщика НДС |
Поясню работу формул на примере ячейки «
D2». Мне нужно заполнить строку для контрагента «ЧП Омега». В справочнике «Спр» его номер «1». Печатаю в ячейку «A2» единицу. Смотрим, что сделает формула в «D2». Функция «Если()» проверяет условие «$A2=""».Важно!
Между кавычками в выражении «$A2=""» пробела нет. Абсолютный адрес для колонки нужен: мы будем копировать формулу вправо, но идентификатор столбца должен быть неизменным.Если в «
A2» записана пустая строка (нет номера контрагента), формула вернет пустое значение («""»), иначе начнет работать «ВПР ($A2;Спр!$A:$K;2)». Первый параметр функции — это искомое значение, т. е. номер контрагента в справочнике. Для «D2» он равен «1». Адрес справочника в «ВПР» указан вторым параметром. Это блок колонок «$A:$K» на листе «Спр».Важно!
Абсолютная адресация колонок обязательна, так как мы будем копировать формулу вправо.Для функции «
ВПР» все колонки справочника пронумерованы слева направо начиная со значения «1». Функция просматривает первую колонку, находит в ней номер контрагента (значение «1»). Результат поиска находится в «A2». В качестве результата функция возвращает значение из ячейки в той же строке со смещением «2», т. е. «B2». Там находится наименование предприятия. Остальные формулы работают по тому же принципу, отличается только величина смещения.Теперь о том, как пользоваться таблицей. Я поступил старым дедовским методом. Напечатал справочник. Скотчем прикрепил внизу к корпусу экрана монитора, чтобы он всегда был перед глазами. Теперь, заполняя базу, мне достаточно ввести номер контрагента, дату, сумму и номер договора. Остальные поля Excel заполнит автоматически.
Формулы в таблице можно усовершенствовать. Например, если в колонку «
№» напечатать «пробел», то функция поиска выдаст ошибку: она не сможет отыскать такого значения в первой колонке справочника. Решить проблему просто: нужно воспользоваться функцией «Епусто()» в сочетании с логический проверкой «ИЛИ». Формула будет выглядеть так: «=ЕСЛИ(ИЛИ(ЕПУСТО($A2);$A2=" ");"";ВПР($A2;Спр!$A:$K;2))». В ней выражение «ЕПУСТО($A2)» проверяет, чтобы в «A2» стояло не пустое значение, иначе функция вернет «Ложь». Выражение «$A2=" "» проверяет «A2» на пробел. Функция «ИЛИ()» объединяет оба результата. Если хотя бы один из них сработает, значение функции будет положительным, а результат проверки в «Если» вернет в ячейку пустую строку. Вариантов проверок можно придумать много. Вопрос в том, стоит ли это делать. Осталось сделать пару замечаний по работе с таблицей.Дополняя справочник новыми данными, следите, чтобы он оставался упорядоченным по колонке «
№». Если нужно, воспользуйтесь меню «Данные → Сортировка» и восстановите порядок.Когда база готова, формулы вам станут не нужны. Сохраните их в примечаниях, а базу превратите в значения. Для этого сделайте так:
— станьте на первое значение с формулой (в примере это ячейка «
D2» на листе «База»);— нажмите «
F2», чтобы перейти в режим редактирования формул;— в строке формул выделите в блок текст функции, скопируйте его в буфер обмена по «
Ctrl+C». (Копирование делайте в строке формул, а не на рабочем листе!);— нажмите «
Esc», чтобы вернуться на рабочий лист;— щелкните на «
D2» правой кнопкой и из контекстного меню выберите «Добавить примечание»;— в окне примечания вставьте содержимое буфера по «
Ctrl+V»;— выделите всю таблицу и скопируйте ее в буфер («
Ctrl+C»).Не снимая выделения, войдите в меню «
Правка → Специальная вставка». В предложенном окне установите переключатель в положение «Значения» и нажмите «ОК». Теперь формулы пропали, остались только значения. Но в комментарии к «D2» все сохранилось. В любой момент вы щелкните на «D2» правой кнопкой, укажете в меню «Изменить примечание», скопируете из примечания текст формулы, вставите в ячейку рабочего листа и скопируете на всю таблицу. Останется только поправить смещения — и все заработает. Надеюсь, что предложенный пример поможет справиться с проблемой. Успешной работы!
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
