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

Рис. 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. Вставка значений

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

Некоторые итоговые, обобщённые значения показателей можно получить сразу же, не покидая исходную таблицу транзакций. Выделим столбец «Количество товара».

В нижней части окна Excel находится так называемая строка состояния (рис. 5.46). После выделения столбца чисел нам сразу же подсчитали следующие показатели:

— Average — Среднее значение;