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

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

Назовём вкладку «Транз». А над таблицей дадим полное название. Чтобы освободить место для заголовка таблицы, вставим строки. Щёлкаем правой кнопкой по заголовку первой строки (рис. 5.23). Выбираем в контекстном меню пункт Insert.

Рис. 5.23. Вставка строки

Задание. Вставьте две пустые строки и введите заголовок страницы.

Создадим в нашей таблице следующие столбцы:

ИД магазина

Название магазина

Город

ИД товара

Название товара

Колич товара кг

Цена товара руб/кг

Стоим товара руб

Для начала просто оформим заголовки указанных столбцов.

Задание. Создайте перечисленные столбцы.

Наша таблица занимает определённый диапазон ячеек. Но об этом знаем только мы. Для программы это просто диапазон ячеек. Оформим нашу таблицу как объект типа «Таблица Excel». Выделим первый столбец, нажав знакомую комбинацию клавиш:

Ctrl + Shift + Down.

Столбец выделен. Теперь нажимаем комбинацию

Ctrl + Shift + Right.

Теперь у нас выделена вся таблица — включая пока ещё не заполненные столбцы. У этих столбцов пока есть только заголовки.

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

Форматируем таблицу. Указываем название: «Транзакции» (рис. 5.24).

Рис. 5.24. Форматирование таблицы транзакций

Задание. Проведите форматирование таблицы транзакций и дайте ей название.

Мы создали три таблицы и дали им осмысленные названия. Список объектов Excel и их названия можно вывести на экран. Выбираем в верхнем меню следующий пункт (рис. 5.25):

Formulas — Defined Names — Name Manager.

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

Рис. 5.25. Имена объектов

Задание. Выведите список объектов и убедитесь, что в нём присутствуют имена всех трёх таблиц.

Сгенерируем ИД магазинов. В нулевом варианте это должны быть целые числа от 1 до 9. Чтобы получить случайные числа, придётся вызвать генератор случайных чисел, а потом ещё и округлить полученные значения. Поэтому слева от стобца «ИД магазина» добавим два новых вспомогательных столбца: «Сл числа» и «Округл».

Вызываем генератор случайных чисел (рис. 5.26). Указываем диапазон случайных чисел от 1 до 9. И не забываем ввести новое начальное состояние генератора Random Seed. Выходной диапазон — второй столбец таблицы.

Рис. 5.26. Настройки генератора

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

Форматирование таблицы облегчает нам дальнейшее её заполнение. Если ввести формулу в одну ячейку такой таблицы, то автоматически будет заполнен весь столбец. Поэтому такой объект иногда даже называют «умной таблицей».

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

=ROUND ([@ [Сл числа]],0).

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

Нажимаем Enter — и весь столбец заполняется автоматически.

Выделяем столбец, копируем в буфер и вставляем в колонку ИД магазина КАК ЗНАЧЕНИЯ (рис. 5.27). В строке формул можно видеть значения, а не формулы с округлением.

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

Задание. Заполните столбец ИД магазина и убедитесь, что были вставлены значения, а не формулы.

Теперь можно безбоязненно удалить два вспомогательных столбца. Они нам больше не понадобятся. Выделяем два заголовка столбцов рабочего листа Excel и выбираем Deleteв контекстном меню. Столбец «ИД магазина» не пострадал, потому что здесь только числа и нет ссылок на другие ячейки (рис. 5.28).

Рис. 5.28. Вид таблицы после удаления вспомогательных столбцов

Задание. Удалите вспомогательные столбцы и убедитесь, что после этого важные данные не пострадали.

Мы сгенерировали столбец идентификаторов «ИД магазина» как целые случайные числа. А ещё у нас уже есть справочник, чтобы найти по этому идентификатору сведения о каждом магазине. Нам нужно связать две таблицы, чтобы вставить данные о магазине в основную таблицу транзакций. Для этого будем использовать функцию подстановки значений VLOOKUP. Русский вариант названия ВПР.

Название функции — это сокращение от VERTICAL LOOKUP. Английское слово LOOKUP означает «поиск информации в справочнике». Получается, что это «вертикальный поиск» или «поиск по вертикали». То есть поиск производится внутри столбца. Русское название ВПРскорее всего означает «вертикальный поиск решения», хотя на странице фирменного описания функции об этом скромно умолчали.

Щёлкаем по ячейке С4и начинаем вводить формулу. Пишем =vl. При этом Excel предлагает нам список функций, название которых начинается на эти буквы (рис. 5.29). В нашем случае это всего один вариант VLOOKUP. Справа от названия функции выводится её краткое описание.

Рис. 5.29. Ввод функции

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

Рис. 5.30. Аргументы функции

Задание. Введите название функции VLOOKUPили ВПР, перейдите по ссылке и прочитайте описание.

Упрощённый формат вызова функции:

VLOOKUP (lookup_value, table_array, col_index_num).

Первый аргумент lookup_value — это искомое значение. Мы будем искать идентификатор магазина. Поэтому выбираем соседнюю ячейку в колонке «ИД магазина» (рис. 5.31).

Рис. 5.31. Первый аргумент — искомое значение

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

В строке формул ставим запятую. Это разделитель аргументов в английской версии Excel. В русском варианте программы аргументы разделяет другой символ — точка с запятой.