23.03.2009
Заповнення реєстру за допомогою функції «ВПР»
Шановна редакціє! Часто доводиться формувати різні реєстри в Excel. Коли значення в даних відрізняються, спрацьовує автозаповнення за першою літерою. Але в моєму випадку дані схожі (номери рахунків, перші символи в назві підприємств тощо). У такій ситуації автозаповнення Excel не допомагає. Підкажіть, як вирішити проблему? Спасибі.
А. Васильєв, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Наскільки можна зрозуміти із запитання, ідеться про заповнення бази даних. Дійсно, Excel пропонує декілька способів, які добре себе зарекомендували за наявності чітко вираженої
відмітної ознаки в різних елементах даних. Коли такої ознаки немає, доведеться її запровадити. І зробити автозаповнення на основі формул. Я покажу це на прикладі зі своєї практики.Мені знадобилося створити реєстр договорів з контрагентами, зображений на рис. 1. База даних була велика, але вся інформація стосувалася двох десятків контрагентів. Друкувати всі дані, звісно, не хотілося. Я скористався довідником. Для цього створив лист. У прикладі на рис. 1 він називається «
Спр». На ньому підготував дані, як показано на рис. 2.
Важливо!
Довідник «Спр» має бути відсортовано за збільшенням поля «№».Зовні ці дві таблиці дуже схожі, але між ними є принципова відмінність. Перша — це база даних. У ній багато рядків, кожен рядок описує дані конкретного договору. У різних договорах з одним підприємством інформація про контрагента повторюється. А ось сума договору та його номер (колонка «№ дог») унікальні. На листі «Спр» записано довідник із даними про контрагентів. Жодних повторень тут немає. Один рядок — один контрагент. Поля «№ дог», «Дата» та «Сумма договора» у довіднику відсутні.
Для зв’язку між таблицями я ввів робоче поле «№». Воно вказує номер контрагента на листі «Спр». Залишилося зробити автоматичне заповнення полів «База» для контрагента з номером у полі «№». Для цього скористаємося функцією «ВПР()». Роботу функції докладно не описуватиму, відразу наведу формули (див. табл. на с. 22).
| Адреса комірки | Формула | Що означає |
| 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» на листі «Спр».Важливо!
Абсолютна адресація колонок є обов’язковою, оскільки ми копіюватимемо формулу вправо.Для функції «
ВПР» усі колонкиТепер як користуватися таблицею. Я вчинив старим дідівським методом. Надрукував довідник. Прикріпив скотчем його унизу до корпуса екрана монітора, щоб він завжди був перед очима. Тепер, заповнюючи базу, мені досить ввести номер контрагента, дату, суму та номер договору. Решту полів 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 .