Як поліпшити ВПР()
Шановні працівники «Б & К»! Моє запитання пов’язане з пошуком значень у таблицях Excel. Завдання, на перший погляд, просте. У програмі Excel є дуже зручна функція ВПР(). Вона дозволяє знайти в таблиці задане значення та підставити його у формулу або скопіювати на робочий лист. Однак у неї є суттєве обмеження. Функція ВПР() знаходить тільки перше входження елемента до списку, і на цьому завершує свою роботу. А як можна вибрати
всі значення зі списку? Чи можна в Excel 2003 вирішити таке завдання? Наперед завдячую.Валентин Півоваров, м. Полтава
Відповідає
Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
У програмі Excel є різні інструменти для вибору інформації з бази даних. Наприклад, автоматичний або розширений фільтр. Однак, судячи із запитання, ідеться про вибір даних за допомогою формул. Тут ситуація така. Для роботи з базами даних в Excel є декілька функцій. Наприклад, вибірку даних зі списку може зробити «
БИЗВЛЕЧЬ()». Функція «ВПР()» знаходить задане значення у списку. Проте обидві ці функції не дозволяють вибрати всі входження елемента, що нас цікавить. Вирішити таке завдання можна двома способами — звернутися до мови Visual Basic або скористатися масивом формул. Я пропоную зупинитися на другому варіанті. Він простіший, не потребує навичок програмування, і тому легко реалізовується на практиці. Роботу з масивом формул я покажу на прикладі таблиці, зображеної на рис. 1. У ній на листі «Тбл» зібрано відомості про рух грошових коштів у розрізі контрагентів (або фізичних осіб). У цій таблиці є такі поля: «Дата» — дата проводки, «СчД», «СчК» — рахунки дебету і кредиту проводки, «Д», «К» — суми за кредитом і дебетом проводки, «Контрагент/ФЛ» — назва організації чи П. І. Б. працівника. 
Наше завдання. Потрібно побудувати таблицю, яка працюватиме так. До окремого елемента цієї таблиці ми введемо дані для пошуку. Хай це буде назва контрагента. Потім ми повинні створити формули, які проаналізують вихідну базу, виберуть із неї всі дані щодо зазначеного контрагента та перенесуть їх до окремої області листа. Наприклад, ми хочемо для контрагента побачити звіт про рух грошовий коштів — дату, суму за дебетом і кредитом проводки.
Створюємо масив із формул
На першому етапі (щоб надмірно не ускладнювати формули) будуватимемо таблицю на одному листі з базою даних. Робимо так:
1) перед першим записом бази даних додаємо робочий рядок (рис. 2);
2) до комірки «
H1» вводимо заголовок «Выборка». До комірки «I1» нашої таблиці введемо дані для відбору значень;3) до комірок «
H2», «I2», «J2» вводимо заголовки: «Дата», «Д», «К». Нижче цих заголовків розташовуватимуться результати відбору значень із бази даних;4) у комірці «
I1» зазначаємо назву контрагента, для якого потрібно зробити вибірку інформації з бази даних. На рис. 2 — це «Петров В.И.»;5) до комірки «
H3» вводимо формулу: «=ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(A3:A65536)-2;””);СТРОКА()-2))», натискуємо «Enter»;6) виділяємо фрагмент робочого листа, куди потрібно записати відібрані значення. Наприклад блок «
H3:H65536» (з третього рядка і до кінця робочого листа);7) не знімаючи виділення (!), натискуємо «
F2» — уключаємо режим редагування комірки «H3»;8) натискуємо комбінацію «
Ctrl+Shift+Enter» — заповнюємо блок масивом формул;9) виділяємо колонку «
H», призначаємо їй формат дати;10) до комірки «
I3» вводимо формулу: «=ИНДЕКС($D$3:$D$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(D3:D65536)-2;””);СТРОКА()-2))», натискуємо «Enter»;11) виділяємо фрагмент «
I3:I65536»;12) не знімаючи виділення, натискуємо клавішу «
F2», потім «Ctrl+Shift+Enter»;13) до комірки «
J3» вводимо формулу: «=ИНДЕКС($E$3:$E$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА(E3:E65536)-2;””);СТРОКА()-2))», натискуємо «Enter»;14) виділяємо фрагмент «
J3:J65536», натискуємо «F2», потім комбінацію «Ctrl+Shift+Enter». Результат зображено на рис. 2.У колонці «
H» (комірки «H3:H8») з’явилися дати, де є операції з контрагентом «Петров В.И.». У колонках «I» і «J» видно суми за дебетом і кредитом проводок. Усього в блоці з результатами заповнено шість рядків. Починаючи з комірки «H9» і до кінця таблиці йдуть значення «#ЧИСЛО». Це означає, що інших відомостей щодо елемента «Петров В.И.» в базі даних немає.
Покращуємо результат
У цілому формула працює правильно. Але вона має один недолік — для порожніх комірок вона повертає значення «
#ЧИСЛО». Це легко виправити за допомогою функцій «ЯКЩО()» і «ЕОШ()». І в остаточному варіанті формули виглядатимуть, як показано в таблиці.
| Адрес | Формула |
| H3 | =ЕСЛИ(ЕОШ(ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА (A3:A65536)-2;"");СТРОКА()-2)));"";ИНДЕКС($A$3:$A$65536;НАИМЕНЬШИЙ (ЕСЛИ($I$1=F3:F65536; СТРОКА(A3:A65536)-2;"");СТРОКА()-2))) |
| I3 | =ЕСЛИ(ЕОШ(ИНДЕКС( $D$3:$D$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА (D3:D65536)-2;"");СТРОКА()-2)));"";ИНДЕКС($D$3:$D$65536;НАИМЕНЬШИЙ (ЕСЛИ($I$1=F3:F65536; СТРОКА(D3:D65536)-2;"");СТРОКА()-2))) |
| J3 | =ЕСЛИ(ЕОШ(ИНДЕКС( $E$3:$E$65536;НАИМЕНЬШИЙ(ЕСЛИ($I$1=F3:F65536;СТРОКА (E3:E65536)-2;"");СТРОКА()-2)));"";ИНДЕКС($E$3:$E$65536;НАИМЕНЬШИЙ (ЕСЛИ($I$1=F3:F65536; СТРОКА(E3:E65536)-2;"");СТРОКА()-2))) |
Формули для комірок «
H3», «I3», «J3» практично однакові. Відмінності між ними виділено напівжирним зображенням. Щоб отримати остаточний варіант таблиці, робимо так:1) до комірки «
H3», «I3», «J3» вводимо формули з таблиці;2) стаємо на комірку «
H3», виділяємо фрагмент «H3:H65536»;3) не знімаючи виділення, натискуємо «
F2» — входимо в режим редагування комірки «H3»;4) натискуємо комбінацію «
Ctrl+Shift+ Enter»;5) повторюємо ці дії для блоків «
I3:I65536», «J3:J65536». Результат не зміниться, але замість значень «#ЧИСЛО» у комірках будуть порожні рядки.Завдання ми вирішили. Вибір значень з бази даних працює. Але є один момент, який обов’язково потрібно враховувати при роботі з масивами формул.
Важливо! З області листа з масивом формул не можна видалити окреме значення. Усе, що можна зробити, — стерти всі елементи масиву цілком.
У прикладі на рис. 2 масив із формулами розташований на одному листі праворуч від основної бази даних (колонки «
H», «I», «J»). Цей вибір було зроблено не випадково — так простіше підставляти адреси при створенні формул. Однак працювати з таким варіантом таблиці незручно. Наприклад, ми не зможемо додати або видалити рядок до основної бази даних, адже при цьому ми додаватимемо рядки і в область масиву з формулами! А цього робити не можна.Порада Працюючи з масивами формул, намагайтеся розташовувати їх на окремому листі робочої книги.
Дотримуючись цієї поради, робимо так:
1) на листі «
Тбл» (рис. 2) виділяємо колонки «H», «I», «J»;2) вирізаємо дані в буфер обміну (меню «
Правка → Вырезать», можна скористатися іконкою «Вырезать» на панелі інструментів або контекстним меню);3) переходимо на новий робочий лист, я назвав його «
Отчет»;4) вставляємо дані з буфера обміну. Excel автоматично відкоригує адреси у формулах, проставить посилання на робочий лист з базою даних. А звіт набере вигляду, як наведено на рис. 3.
Усе готово до роботи. До комірки «
B1» вводимо параметр відбору значень (наприклад, текст «Петров В.И.»). На листі з’явиться звіт про рух грошових коштів щодо зазначеного контрагента.
Стисло про формулу
Я поясню логіку роботи формул на прикладі виразу з комірки «
H3» (рис. 2). У нашому прикладі база даних починається з третього рядка робочого листа. Отже, самі дані (не враховуючи рядок заголовків) розміщуються на два рядки нижче початку робочого листа. Для визначення номерів записів у базі ми застосували вираз «=СТРОКА(A3:A65536)-2». Тут значення «-2» вказує зсув бази даних на два рядки від початку листа. Вираз «ЕСЛИ($J$1=F3:F65536;СТРОКА (A3:A65536)-2;””)» порівнює шукане значення (комірка «$J$1») із даними у блоці «F3:F65536». При збігу значень результатом буде номер запису в базі даних (тобто «СТРОКА(A3:A65536)-2»). Якщо результат порівняння помилковий, вираз поверне порожній рядок («""»). Отже, для рядків, які відповідають умові пошуку, формула визначить їх порядкові номери в базі даних. Наприклад, для запису «Петров В.И.» від «01/03/11» формула поверне номер «2» — відповідний запис знаходиться в базі на другій позиції. Щоб зібрати всі знайдені значення в один блок, ми застосували функцію «НАИМЕНЬШИЙ()». Вона має два параметри: масив та номер найменшого елемента в цьому масиві. Номерами найменших елементів у формулі є значення «СТРОКА(A3:A65536)-2». Нагадаю: значення цього виразу — це номери записів у базі даних, починаючи з «1». Тоді вираз «НАИМЕНЬШИЙ (ЕСЛИ($J$1=F3:F65536;СТРОКА(A3:A65536)-2;””);СТРОКА()-2)» поверне як результат перший найменший елемент (значення «2»). При копіюванні формули вниз на один рядок вираз поверне як результат другий найменший елемент (значення «5») і так далі. Отже, у результаті роботи функції «НАИМЕНЬШИЙ(…)» нам буде відомий рядок і колонка в базі даних, де знаходяться всі шукані елементи. Підставити конкретні значення, розташовані на перетині рядка і колонки, дозволяє функція «ИНДЕКС()».
Як застосувати її до свого завдання
Щоб скористатися формулою, не обов’язково розуміти логіку її роботи в усіх деталях. Досить знати, що потрібно в ній відкоригувати і для чого. Сам текст формули ви можете набрати самостійно або скачати готовий приклад на сайті редакції. А далі робимо так:
1) копіюємо текст формули до елемента таблиці;
2) знаходимо зсув першого рядка бази даних від початку робочого листа, заносимо відповідне значення до формули. У прикладі на рис. 2 це значення дорівнює «
-2»;3) вибираємо блок даних, в межах якого Excel виконуватиме пошук значень. У прикладі на рис. 3 ми відбирали дані за колонкою «
Контрагент/ФЛ». Відповідно в нашому випадку блок даних для пошуку був «F3:F65536». Замість цього виразу поставте своє значення;4) визначте блок даних, з якого потрібно повернути результат. Наприклад, формула для комірки «
H3» повертає дату операції. Дати в основній базі знаходяться в колонці «A». Відповідно у формулі для «H3» проставлено посилання на блок «$A$3:$A$65536». У комірці «I3» формула має повернути суму за дебетом проводки. Ці значення записані в колонці «D», а відповідне посилання буде «$D$3:$D$65536». Відкоригуйте у формулах посилання, виходячи зі свого завдання. Усе, що залишається зробити, — створити масив формул та користуватися результатом.
Успішної роботи! Чекаю ваших запитань, зауважень та пропозицій на
bk@id.factor.ua або nictomkar@rambler.ru .
