Серия «Уроки Excel»

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

ВПР() , ГПР()

Да, дорогие друзья, это тот самый легендарный Вэпээр, о котором говорят с придыханием девочки из бухгалтерии. Ладно, давайте к серьезному

Для чего используется? Для поиска данных в массиве по условию.

Звучит крайне криво - как и все околонаучные определения - но в самом Экселе определение еще хуже, так что... Так что давайте традиционно разбирать на конкретных примерах, потому что без этого реально тяжело.

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

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

Давайте разберем, как именно пишется ВПР(). В ней четыре блока, разделенных точкой с запятой.

Первый блок - условие, то есть то, что мы будем искать. Обратите внимание, столбец с условием должен быть левее столбцов с теми значениями, которые мы будем подставлять - это крайне важно, потому что иначе формула просто не работает. Только слева-направо!

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

Третий блок - это номер столбца относительно начала массива, из которого будут подставляться данные. То есть в нашем примере "Диагональ экрана" - столбец D - это третий столбец массива, "Цвет корпуса" второй, "Модель" - первый.

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

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

Поясню все на том же примере - допустим, у вас есть ноутбук модели AS650HP, который кто-то не очень аккуратный занес в табличку дважды и с разными диагоналями. Такого не может быть - ну потому что модель одна, и второго размера экрана у нее не существует. Явная ошибка. Но - формула ВПР() об этом не знает. И она возьмет из таблицы то значение, которое находится выше. То есть в данном случае 16 дюймов, что (допустим) неправильно. Сколько бы ни было строчек с одинаковым условием - формула всегда берет верхнюю.

Помните СУММЕСЛИМН() формулу? Вот она адекватно реагирует на повторяющиеся условия, она собственно для этого и создана. ВПР() же создана для вставки одного конкретного значения. И тут мы подходим к основной функции этой формулы - так называемому приему мэппинга.

Что интересного можно сделать с этой формулой?

Итак, о мэппинге. Я так называю процесс простановки некоего соответствующего параметра из одной таблицы в другую, которую необходимо определенным образом упорядочить и обработать. Чаще всего это делается тогда, когда не хочется проставлять около каждой из 100500 строчек какой-то параметр вручную или же в теории он может измениться в будущем.

Давайте придумаем что-нибудь базовое - ну вот например, если дополнить табличку из примера выше пометкой "в наличии", то можно будет рассортировать список заказов на те, которые мы сможем выполнить, и те, которые не сможем. Но чаще всего подобное используется для создания более сложных классификаторов. Например, для переложения бухгалтерского учета в управленческий, если это не автоматизировано в учетной программе, можно написать мэппинг, в котором статье из бухгалтерского учета будет соответствать статья управленческого.

Мэппинги, естественно, можно строить как по одному условию, так и по нескольким. В этом вам поможет ранее изученная формула "СЦЕПИТЬ". Например, представим что покупатель говорит "я хочу красный ноутбук с диагональю 14 дюймов, есть у вас такие?" - и мы, забив в наш шаблон эти два параметра и пробив пару формул отвечаем "да, вот такая модель в наличии".

В качестве условия для ВПР() может служить совокупность любых ячеек, которым вы сможете поставить что-либо в соответствие. Главное, чтобы эта совокупность была уникальна.

Важно понимать, что в третьем блоке в качестве номера столбца может также быть заведена формула. Мне не хочется слишком сильно ломать вам голову, но все же - изучим еще одну небольшую формулу. Она называется ПОИСКПОЗ(). Работает следующим образом - выбираем в первом блоке что искать, во втором обозначаем массив, где искать, и далее указываем, как ищем (-1 - самое ранее, 0 - первое, 1 - последнее).

Соответственно в этом примере формула найдем там значение "Модель" в массиве из пяти ячеек, и оно будет равно 2 - потому что это вторая ясейка массива. Как думаете, как можно это применить, чтобы упростить себе задачу в шаблоне из нашего примера?

Да вот так - мы просто делаем наименования полей нашего "шаблона ценника" такими же, как наименования столбцов в исходной таблице и далее вставляем ПОИСКПОЗ() в формулу ВПР(), наводясь на (зафиксированный!) массив из пяти ячеек. В каждом случае номер ячейки, которую найдет ПОИСКПОЗ() будет соответствовать номеру столбца для ВПР().

Таким образом например можно формировать всякие заявления, в которых нужна ФИО, должность, табельный номер или еще что-то, если это не автоматизировано.

***

Ну а на этом я с вами прощаюсь. Задавайте вопросы, комментируйте, учитесь, тренируйтесь.

Привет. Я уже около 13 лет работаю с Excel и решил написать пару постов, посвященных этому полезному инструменту. Не хочу долгих вступлений, но уровень владения программой в последнее время падает - я это вижу по кандидатам, которые проходят собеседования. Мой профиль - экономика, у нас это основной инструмент. Так что будем повышать - по мере возможности. И сегодня я разберу пример того, как из очень плохой таблицы сделать нечто удобоваримое.

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

Уточнение - левая и верхняя границы у таблицы есть, просто я немного поленился копировать с захватом на +1 ячейку. В конце поста будет нормально.

Давайте для начала дорисуем контекст - таблица была направлена в письме с целью показать прирост стоимости некой услуги и согласовать этот прирост. Пусть для простоты это будет аренда склада. Общая стоимость (столбец "Стоимость Услуги") складывается из двух элементов, обозначенных в договоре.

Что же не так с представлением информации?

1. Повторяющаяся информация

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

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

2. Удобство сравнения данных

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

В чем же проблема? В данном случае - в вертикальном представлении.

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

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

В данном случае необходимо каждый блок (выделен красной заливкой) транспонировать - т.е., столбцы сделать строками. Также будет неплохой идеей заменить слово "прирост" словом "изменение" - потому что понятие "отрицательный прирост" это ужасно. Шутки-шутками, но указание в наименовании столбца на результат - идея плохая. Если хотите сделать выводы - делайте их отдельно, таблица должна просто визуализировать данные.

3. Форматирование и лишняя информация

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

Во-первых, это выравнивание текста в ячейках. А именно - по нижнему краю в "шапках" двух первых столбцов, а также в ячейках с процентами крайнего правого столбца. Очевидно, что все наименования в "шапке" должны быть выравнены одинаково. Что касается значений в теле таблицы, то здесь обычно применяется простое правило: текст по левому краю, числовые значения - по правому. Почему числовые значения именно так? Ну потому что проще на глаз сравнить 1 000 и 10 000 по числу символов, например, если у них правая граница выравнена.

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

В-третьих, это высота строк. Все строки, содержащие однородные данные, должны быть одинакового размера. Аналогично со столбцами. Если есть возможность сократить отдельные слова, чтобы сравнять ширину строк - лучше сделать это.

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

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

б) Используйте полужирный шрифт только для итогов и/или заголовков.

в) Не применяйте более двух (в крайнем случае - трех) цветов шрифтов на таблицу.

г) Не используйте без необходимости лишние символы - знаки после запятой, валюты и тп.

д) Используйте отступы, чтобы данные не сливались с границами таблиц

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

Согласитесь, это несколько удобнее и информативнее, чем то, что было в исходнике:

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

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