Разоблачение Excel: Работа со справочниками

Для меня слово справочник прочно асcоциировано со школьными таблицами Брадиса (кто-нибудь еще помнит такие или я последний из могикан?) и это воспоминание непременно вызывает улыбку и светлые чувства.
Но могу предположить, в том числе и из собственного опыта, что тестирование задач завязанное на справочную информацию (особенно большого объема) вызывает прямо противоположные чувства. Поэтому, если данная статья поможет высушить хотя бы одну слезинку тестировщика - я буду считать, что цель достигнута :)


1. Введение

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

2. Справочники и с чем их едят

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

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



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

Если начать копать глубже в сторону БД, что мы увидим, что там без справочников просто нельзя и шагу ступить. Но тема нашей статьи не теория реляционных БД, а практика работы с Eхcel, поэтому предлагаю в очередной раз посмотреть, что же мы можем сделать, имея в руках только молоток и гвозди Microsoft Office и светлую голову.

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

Как я говорил в своей предыдущей статье Excel – это конечно не полноценная БД, но все-таки некоторые “базовские” функции присутствуют. Для работы со справочниками есть целый набор функций, доступный для выбранной категории “Lookup & Reference”



Нас с вами будут интересовать 2-е из них – VLOOKUP и HLOOKUP. Эти функции в целом схожи и различаются лишь направлением поиска (вертикальным и горизонтальным соответственно). Для большинства из нас более естественным является горизонтальное расположение строк и вертикальное – столбцов, поэтому все примеры будут основаны на использовании функции VLOOKUP, реализующей работу именно с таким вариантом.

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

Для начала обрисуем себе цель нашей авантюры, то есть что же мы хотим получить в обмен на наши мучения.



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

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

4.1. Попроще

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



Для удобства работы со списком выносим его на отдельную страницу и помечаем как именованный диапазон



После этого единственное, что нам остается сделать – это определить откуда мы берем код для поиска и что мы хотим видеть взамен этого кода. В итоге получим следующее выражение:

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

B2 – это ячейка с кодом
Список_Штатов – это наш справочник, в котором нам интересна 2-ая колонка с полным названием штата

Будьте внимательны с последним параметром!!!
Последний параметр (FALSЕ) определяет правило поиска кода:
- Если он равен TRUE или пропущен – ищется ближайший вариант
- Если он равен FALSE – поиск ведется на полное соответствие

Попробуйте поиграть с этим параметром, чтобы, так сказать, почувствовать разницу.

Кстати, никто не обратил внимание, чего еще не хватает во входных параметрах функции VLOOKUP для полноты картины? А откуда она узнает в каком столбце нашего справочника содержится имено код?
А вот это уже ваша прямая обязаннность – обеспечить чтобы столбец с кодами был первым столбцом нашего справочника

4.2. Посложнее

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



Для расчета вручную информации достаточно, и затруднений сам расчет не вызывает. Но для использования в качестве входа для автоматического расчета нам потребуется немного преобразовать данную таблицу.

Правило преобразования несложное и основывается на том, что при поиске подходящего значения (в данном случае уже имеется ввиду поиск приближенного значения) ищется ближайшее меньшее или равное искомому коду. А если по-русски и без заумных фраз, то
• для каждой пары вида ”A – B” должно остатья первое значение – ”A”
• выражения вида >A следут заменить на величину A+1
• выражения вида <A следут заменить на Min(A), то есть минимально допустимое значение

В итоге получаем следующую таблицу:



И формула для расчета ”возрастной добавки” будет выглядеть следующим образом

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

D10 – это ячейка с кодом
Возраст_автомбиля – наш доведенный до ума справочник

И обратите внимание на последний параметр – он теперь имеет значение TRUE, что указывает на правило поиска приближенного, а не точного значения.

4.3. Высший пилотаж

Изложение материала в предыдущих параграфах начиналось словами ”каждому коду … или каждому диапазону …”. То есть основное предположение состоит в том, что у нас есть уникальное значение с одной стороны и набор дополнительной информации для него с другой.

Рассмотрим теперь ситуацию, когда мы не сможем опираться на такой удобный посыл. Предположим, что выбор искомой информации зависит от набора ключевых значений, например сильно упрощенная схема расчета страховой премии на страхование автомобиля в США зависит от следующих параметров:
- возраст автомобиля: 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. Хитрости и трюки

В принцпипе, информации полученной из предыдущих примеров достаточно, чтобы покрыть большинство случаев использования справочников. Но в качестве дополнительного бонуса (на сладкое так сказать) хотел бы предложить найденные в ходе работы интересности, связанные с изложенным материалом.

5.1. Использование столбца кода для валидации

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

Если бы не одно но – при организации списка валидации нам необходимо указать источник для наполнения выпадающего списка и этот источник не может быть 2-х мерной таблицей коим является наш справочник Список_Штатов. О чем вам незамедлительно сообщит Excel



Казалось бы единственным правильным вариантом будет дополнительно определить именованный диапазон только для столбца кодов, но с точки зрения правильного лентяя хотелось бы найти более элегантный вариант. И он есть :)
В поле источника данных следует указать такое выражение:

=INDEX(Список_Штатов,0,1)

И “золотой ключик у вас в кармане”.

5.2. Связанные справочники

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

В нашем сакраментальном примере с адресом помимо штата присутсвует также поле Город. И было бы достаточно интересно реализовать выпадающий список городов в зависимости от выбранного штата. Задача не совсем для Excel, но тем не менее поддается решению без привлечения тяжелой артилериии в лице VBA.

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

В итоге выражение для валидации будет выглядеть следующим образом:

=INDEX(INDIRECT("Города_"&B2);0;2)

B2 – адрес ячейки с выбранным кодом штата
И в реальной жизни выглядеть это будет вот так:





6. Выводы

Коллеги, я постарался изложить в статье все наработки, связанные с тестированием алгоритмов, основанных на справочных таблицах. Нам данная техника очень помогла при тестировании весьма непростых систем расчета страховых взносов. Очень надеюсь, что и вам эта информация в чем-то окажется полезной.

Понимая, что теория без практики плохо осаждается даже в светлых головах, вместе с данным материалом вы найдете Excel-файл со всеми реализованными в статье примерами.