Рис. 5.41. Названия городов
Задание. Вызовите функцию VLOOKUP с помощью Мастера функций и заполните столбец названий городов.
Точно таким же способом мы сгенерируем случайные целые числа для колонки «ИД товара». Вставляем пару вспомогательных столбцов после колонки «Город». Смотрим справочник товаров их в нулевом варианте шесть штук. Вызываем генератор из надстройки «Анализ данных». Указываем диапазон от 1 до 6. Не забываем установить новое начальное состояние чтобы оно было не такое же, как в предыдущем вызове генератора. Округляем, копируем, вставляем как значения. Удаляем вспомогательные столбцы. Получаем заполненную колонку «ИД товара» (рис. 5.42). Убеждаемся, что здесь в ячейках таблицы появились числа, а не ссылки.
Рис. 5.42. Заполняем колонку «ИД товара»
Задание. Заполните колонку «ИД товара» случайными целыми числами.
Теперь заполняем колонки «Название товара» и «Цена товара руб/кг». Снова обращаемся к функции VLOOKUP и используем справочник товаров. Смотрим на справочник товаров и обнаруживаем, что мы оставили за кадром категорию товара. Вставляем столбец вида товара в таблицу транзакций. Теперь все сведения на месте (рис. 5.43).
Рис. 5.43. Сведения из справочника товаров
Задание. Заполните таблицу транзакций сведениями из справочника товаров.
Далее сгенерируем количество товара в килограммах. Пусть это будут случайные числа от 200 г до 5,5 кг. И пусть весы работают с точностью до 1 грамма. Такое уже встречается в наших магазинах. Стало быть, округлять будем до 3 знаков после запятой. Создаём пару вспомогательных столбцов. Генерируем случайные числа с НОВЫМ начальным состоянием, округляем, копируем и вставляем как значения. Удаляем вспомогательные столбцы. Устанавливаем формат вывода число, 3 знака после запятой:
Далее сгенерируем количество товара в килограммах. Пусть это будут случайные числа от 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 Среднее значение;
Count Количество заполненных (непустых) ячеек;
Sum Сумма значений.
Рис. 5.46. Итоговые показатели в строке состояния
Задание. Выделите столбец количества товара в таблице транзакций и обратите внимание на показатели в строке состояния.
Нужные показатели в строке состояния можно выбрать из довольно большого списка Customize Status Bar. Для этого щёлкнем по строке состояния правой кнопкой мыши. В длинном списке имеется раздел сводных показателей (рис. 5.47). Для уже упомянутых трёх показателей здесь даны их числовые значения. Кроме того, здесь есть
Numerical Count Количество ячеек с числовыми значениями;
Minimum Минимальное значение;
Maximum Максимальное значение.
Рис. 5.47. Настройка строки состояния
Задание. Настройте строку состояния так, чтобы вывести все возможные сводные показатели. На новом листе создайте небольшую таблицу и проверьте, как работают эти показатели. Для этого заполните ячейки числами, словами и оставьте несколько пустых ячеек. Обратите внимание на итоговые показатели.
6. Создание сводной таблицы
Сводные таблицы (английское название Pivot Tables) это инструмент электронных таблиц для сводки и группировки данных. В сводной таблице выводятся обобщенные показатели. Такая таблица гораздо меньше, чем исходная. Это всего лишь десятки или сотни строк, а не миллионы или миллиарды как в учётной базе данных.
Имеется более десяти способов обобщения (агрегирования) данных. Это, прежде всего, сумма. Наверное, самый частый вопрос звучит так: «Сколько всего?» Сколько всего рублей составила наша выручка за 2018 год? Сколько всего килограммов овощей продано за февраль 2017 года?
Можно считать, что сводная таблица это экранное представление многомерного OLAP-куба. Название OLAP OnLine Analytical Processing можно перевести как «оперативный анализ данных» или «интерактивная аналитическая обработка».