Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 1

Наш справочник магазинов находится на другом листе рабочей книги. Зато у него есть своё название. Переходим на вкладку со справочником магазинов «Маг». Выделяем всю таблицу. В строке формул в качестве второго аргумента появляется название таблицы (рис. 5.32).

Рис. 5.32. Второй аргумент — справочник

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

Рис. 5.33. Третий аргумент — номер столбца

Вот мы и сформировали вызов функции поиска и подстановки. Закрываем круглую скобку и нажимаем клавишу Enter. Мы вернулись на страницу с таблицей транзакций. Таблица автоматически заполнила всю колонку названиями магазинов. В формуле используются названия столбца и справочника, а не адреса конкретной ячейки и диапазона ячеек (рис. 5.34). Это довольно удобно.

Рис. 5.34. Подстановка из справочника

Задание. Сформируйте вызов функции VLOOKUPи заполните колонку названий магазинов в таблице транзакций.

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

Щёлкаем по ячейке в колонке «Город» и нажимаем кнопку вызова Мастера функций Insert Functionслева от строки формул (рис.5.35).

Рис. 5.35. Вставка функции

Появляется диалоговое окно Insert Function.

Для начала попробуем найти функцию по названию. Вводим название LOOKUPв строке поиска Search for a function(рис. 5.36). Выясняется, что у нас есть большой выбор. Целых четыре функции для поиска чего-нибудь разными способами. В том числе и горизонтальный поиск, то есть поиск в строке.

Рис. 5.36. Поиск функции по названию

Задание. Вызовите Мастера функций и ознакомьтесь с функциями поиска значений.

Теперь представим себе, что мы не помним название функции, а только очень смутно представляем её предназначение. Открываем выпадающий список Or select a category. Выбираем в выпадающем списке раздел Lookup & Reference(рис. 5.37). Листаем полученный список функций в этой категории и находим VLOOKUP. Щёлкаем по названию функции и читаем краткое описание в нижней части окна. Это она — та самая функция, которую мы так долго искали.

Здесь же нам предлагают получить справку — подробное описание функции. Нажимаем ссылку Help on this functionв нижней части окна. В браузере открывается страничка службы поддержки. Здесь есть что почитать и есть что посмотреть.

Рис. 5.37. Выбор функции

Задание. Найдите функцию VLOOKUPв списке функций, вызовите справку и просмотрите видеоролик, демонстрирующий работу с данной функцией.

Мы ознакомились с описанием функции и нас оно устраивает. Нажимаем ОК. Появляется диалоговое окно выбора аргументов функции Function Arguments(рис. 5.38). Курсор уже указывает на первый аргумент и предлагает нам его указать. Щёлкаем по ячейке в колонке «ИД магазина». В окне выбора аргументов и в строке формул появляется ссылка на выбранный столбец.

Рис. 5.38. Выбор первого аргумента функции

Переходим к выбору второго аргумента. Щёлкаем по строке Table_array, переходим на вкладку со справочником магазинов и выделяем всю таблицу. Мастер функций указывает в качестве второго аргумента название таблицы «Магазины» (рис. 5.39).

Рис. 5.39. Выбор второго аргумента

Третий аргумент вводим вручную. Щёлкаем по третьей строчке Мастера функций Col_index_num. Названия городов у нас находятся в третьем столбце справочника магазинов. Вводим число 3 (рис. 5.40).

Кстати, при выборе аргумента внизу окна Мастера функций выводится краткая справка для каждого аргумента.

Рис. 5.40. Ввод третьего аргумента

Мы выбрали минимальный набор аргументов. Нажимаем ОК. Столбец названий городов заполнен (рис. 5.41). В строке формул видим наш вызов функции с указанием названий столбца и справочника. Смотрим на таблицу транзакций. Мы действительно выбрали города для вставки. Пока всё работает правильно.

Рис. 5.41. Названия городов

Задание. Вызовите функцию VLOOKUPс помощью Мастера функций и заполните столбец названий городов.

Точно таким же способом мы сгенерируем случайные целые числа для колонки «ИД товара». Вставляем пару вспомогательных столбцов после колонки «Город». Смотрим справочник товаров — их в нулевом варианте шесть штук. Вызываем генератор из надстройки «Анализ данных». Указываем диапазон от 1 до 6. Не забываем установить новое начальное состояние — чтобы оно было не такое же, как в предыдущем вызове генератора. Округляем, копируем, вставляем как значения. Удаляем вспомогательные столбцы. Получаем заполненную колонку «ИД товара» (рис. 5.42). Убеждаемся, что здесь в ячейках таблицы появились числа, а не ссылки.

Рис. 5.42. Заполняем колонку «ИД товара»

Задание. Заполните колонку «ИД товара» случайными целыми числами.

Теперь заполняем колонки «Название товара» и «Цена товара руб/кг». Снова обращаемся к функции VLOOKUPи используем справочник товаров. Смотрим на справочник товаров и обнаруживаем, что мы оставили за кадром категорию товара. Вставляем столбец вида товара в таблицу транзакций. Теперь все сведения на месте (рис. 5.43).

Рис. 5.43. Сведения из справочника товаров

Задание. Заполните таблицу транзакций сведениями из справочника товаров.

Далее сгенерируем количество товара в килограммах. Пусть это будут случайные числа от 200 г до 5,5 кг. И пусть весы работают с точностью до 1 грамма. Такое уже встречается в наших магазинах. Стало быть, округлять будем до 3 знаков после запятой. Создаём пару вспомогательных столбцов. Генерируем случайные числа с НОВЫМ начальным состоянием, округляем, копируем и вставляем как значения. Удаляем вспомогательные столбцы. Устанавливаем формат вывода — число, 3 знака после запятой:

Format Cells — Number — Category — Number — Decimal places — 3.

Количество товара заполнено, осталось посчитать стоимость.

Умножаем цену на количество (рис. 5.44).

Таблица транзакций почти готова.

Рис. 5.44. Количество и стоимость товара

Задание. Сгенерируйте количество товара и вычислите его стоимость.

В некоторых колонках ещё остались формулы. Постоянный пересчёт формул может замедлять работу программы. Поэтому выделим всю таблицу Ctrl+Shift+Endи вставим её как значения. Убеждаемся, что вместо формул у нас в таблице транзакций остались только значения (рис. 5.45).

Рис. 5.45. Вставка значений

Задание. Вставьте таблицу транзакций как значения и убедитесь, что формул в таблице не осталось.