Викриття excel робота з довідниками

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

2. Довідники та з чим їх їдять

Кожен, навіть людям далеким від IT сфери доводиться щодня стикатися з цією функціональністю щодня. Не вірите - тоді невеличкий тест. Чи є у вас труднощі з розумінням наступних фраз?
- Основна валюта USA - це USD
- КДБ і ДМБ хоч і звучать схоже, але страшно далекі один від одного
- DE, FR, IT, AU - хтось тут не з EU

Упевнений, що проблем з прочитанням і розумінням ні у кого не виникло. Що власне і говорить про те, що довідники міцно увійшли в наше повсякденне життя, вийшовши за рамки вузькоспеціалізованої області. Якщо ж все-таки труднощі виникли, то вони легко розв'язні за допомогою такої таблиці:

яка власне і є типовим представником сімейства "справочнікових".
Тобто в загальному випадку довідник - це таблиця, з якої за значенням виділеного стовпця (код) можна однозначно витягти відповідні йому дані.

Якщо почати копати глибше в сторону БД, що ми побачимо, що там без довідників просто не можна й кроку ступити. Але тема нашої статті не теорія реляційних БД, а практика роботи з Eхcel, тому пропоную в черговий раз подивитися, що ж ми можемо зробити, маючи в руках лише молоток і цвяхи Microsoft Office і світлу голову.

3. Чим може порадувати Excel?

Нас з вами будуть цікавити 2-е з них - VLOOKUP і HLOOKUP. Ці функції в цілому схожі і розрізняються лише напрямком пошуку (вертикальних і горизонтальних відповідно). Для більшості з нас більш природним є горизонтальне розташування рядків і вертикальне - стовпців, тому всі приклади будуть засновані на використанні функції VLOOKUP, що реалізує роботу саме з таким варіантом.

4. Варіанти реалізації

Для початку обрисуємо собі за мету нашої авантюри, тобто що ж ми хочемо отримати в обмін на наші муки.

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

Отже, більш простий варіант використання передбачає, що кожному коду відповідає одна унікальна запис в таблиці. Я, як ви вже зрозуміли, для допомоги взяв список Американських штатів (прикольні у них назви і в інтернеті повно інформації в табличному вигляді):

Для зручності роботи зі списком виносимо його на окрему сторінку і помічаємо як іменований діапазон

Після цього єдине, що нам залишається зробити - це визначити звідки ми беремо код для пошуку і що ми хочемо бачити замість цього коду. В результаті отримаємо такий вираз:

= VLOOKUP (B2; Спісок_Штатов; 2; FALSE)

B2 - це осередок з кодом
Спісок_Штатов - це наш довідник, в якому нам цікава 2-а колонка з повною назвою штату

Будьте уважні з останнім параметром.
Останній параметр (FALSЕ) визначає правило пошуку коду:
- Якщо він дорівнює TRUE або пропущений - шукається найближчий варіант
- Якщо він дорівнює FALSE - пошук ведеться на повну відповідність

Спробуйте пограти з цим параметром, щоб, так би мовити, відчути різницю.

До речі, ніхто не звернув увагу, чого ще не вистачає у вхідних параметрах функції VLOOKUP для повноти картини? А звідки вона дізнається в якому стовпці нашого довідника міститься имено код?
А ось це вже ваша пряма обязаннность - забезпечити щоб стовпець з кодами був першим стовпцем нашого довідника

В ускладненому варіанті замість коду вже з'являється таке поняття як діапазон значень і вже кожному діапазону відповідає будь-який запис.
Типовим прикладом може служити таблиця значень підвищує коефіцієнт для розрахунку страхових внесків залежно від віку автомобіля.

Для розрахунку вручну інформації достатньо, і труднощів сам розрахунок не викликає. Але для використання в якості входу для автоматичного розрахунку нам потрібно трохи перетворити цю таблицю.

В результаті отримуємо наступну таблицю:

І формула для розрахунку "вікової добавки" буде виглядати наступним чином

= VLOOKUP (D10; Возраст_автомобіля; 2; TRUE)

D10 - це осередок з кодом
Возраст_автомбіля - наш доведений до розуму довідник

І зверніть увагу на останній параметр - він тепер має значення TRUE, що вказує на правило пошуку наближеного, а не точного значення.

Виклад матеріалу в попередніх параграфах починалося словами "кожному коду ... або кожному діапазону ...". Тобто основне припущення полягає в тому, що у нас є унікальне значення з одного боку і набір додаткової інформації для нього з іншого.

Розглянемо тепер ситуацію, коли ми не зможемо спиратися на такий зручний посил. Припустимо, що вибір шуканої інформації залежить від набору ключових значень, наприклад сильно спрощена схема розрахунку страхової премії на страхування автомобіля в США залежить від наступних параметрів:
- вік автомобіля: 1-3, 4-7, 8-10,> 10
- стать водія (плювати вони хотіли на гендерну рівність): М, Ж
- стаж водія: <2, 2-5,>5

Повний набір всіх варіантів навіть в такому стиснутому варіанті містить 24 запису, для кожного з яких є соотвествующий коефіцієнт для розрахунку страхової премії. Як же в цьому випадку допоможе відома нам функція VLOOKUP, яка не працює з набором ключів, а приймає на вхід лише одиночний код.

Відповідь - ніяк ... без попередньої підгонки. Чим ми з вами і займемося трохи далі.

Підгонка полягатиме в створенні додаткового поля з кодом, унікально визначає кожен набір, наприклад
1-3, M, <2 преобразуется в 1M0
4-7, М, 2-5 перетворюється в 11Ж2

У прінцпіпе, інформації отриманої з попередніх прикладів досить, щоб покрити більшість випадків використання довідників. Але в якості додаткового бонусу (на солодке так сказати) хотів би запропонувати знайдені в ході роботи цікавинки, пов'язані з викладеним матеріалом.

5.1. Використання стовпчика коду для валідації

У нашому першому прикладі зі штатами ми у відповідь на введений код штату хочемо отримати його повна назва. Естесвенно бажанням було б убезпечити себе від неправильно введеного коду, тим більше що всі коди у нас присутні в нашому довіднику і у нас є необхідні знання (згадуємо тут), щоб організувати випадає для кодів.

Якби не одне але - при організації списку валідації нам необхідно вказати джерело для наповнення списку і це джерело не може бути 2-х мірної таблицею яким є наш довідник Спісок_Штатов. Про що вам негайно повідомить Excel

Здавалося б єдиним правильним варіантом буде додатково визначити іменований діапазон тільки для стовпця кодів, але з точки зору правильного ледаря хотілося б знайти більш елегантний варіант. І він є :)
В поле джерела даних слід вказати такий вислів:

І "золотий ключик у вас в кишені".

5.2. пов'язані довідники

Прийом більше підходить до розділу трюків, ніж хитрощів, але тим не менше іноді корисний (знаю особисто один такий випадок).

Все, що нам треба зробити - це підготувати іменовані списки міст включають в себе код штату, наприклад
Города_FL для Флориди,
Города_TX для Техасу
І познайомиться з ще однією чудовою фукцией INDIRECT. що дозволяє формувати стандартну Excel-посилання з строкового значення.

В результаті вираз для валідації буде виглядати наступним чином:

Колеги, я постарався викласти в статті всі напрацювання, пов'язані з тестуванням алгоритмів, заснованих на довідкових таблицях. Нам дана техніка дуже допомогла при тестуванні досить непростих систем розрахунку страхових внесків. Маю велику надію, що і вам ця інформація в чомусь виявиться корисною.

Розуміючи, що теорія без практики погано осідає навіть у світлих головах, разом з цим матеріалом ви знайдете Excel-файл з усіма реалізованими в статті прикладами.