Сводные таблицы в Excel

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

Сводные таблицы

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

Таблица для выполнения вместе с преподавателем: Google-таблица

Вместо этого инструмента можно использовать функции SUMIF COUNTIF — то есть подсчитать сумму, если данные удовлетворяют определенным условиям. Однако это займет очень много времени.

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

Например, у нас есть такие данные: город, выручка, количество покупок и месяц, за который были произведены эти покупки. К нам приходит руководитель и спрашивает: «А сколько суммарно товаров в Москве мы продали за последние два месяца?»

Да, можно сделать это с помощью небольшой формулы. Однако если нам надо выбрать такие данные по 250 городам, задача станет сложнее. Вот как это выглядит в Google Spreadsheets:

Задание для самостоятельной работы

Эти задания помогут закрепить теорию. Выполнять их необязательно, но очень желательно — только практика делает знание настоящим. Задания для самостоятельной работы не проверяются, но вы можете обсуждать их с опытными знакомыми и коллегами, а также в закрытом сообществе в Slack.

Мы подготовили учебную Google-таблицу с данными про объявления московской недвижимости. С помощью этой таблицы вы сможете закрепить знания: подготовить данные, соединить таблицы, получить основные инсайты о рынке московской недвижимости.

Каждая запись в таблице — объявления о продаже апартаментов. В них содержатся следующие характеристики:

• возраст дома, в котором расположена квартира;
• расстояние до метро;
• расстояние до центра города;
• количество комнат;
• размер квартиры;
• этаж;
• наличие первого жилого этажа в доме;
• городской округ;
• тип дома;
• сервис, в котором объявление было выставлено;
• стоимость квартиры.

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

Вопросы, на которые надо ответить — от простого к сложному:

1. Где средняя цен на однокомнатную квартиру самая низкая?
2. В каком округе больше всего объявлений?
3. В каком округе самый старый дом?
4. В каком типе дома двухкомнатная квартира стоит дороже?
5. Сколько % от всех объявлений выставлены на Яндексе?
6. Можно ли сказать, что источник объявления и средняя стоимость квартиры связаны? Если да, то в каком сервисе (Яндекс, ЦИАН и т.д.) в среднем квартиры дороже?
7. Какова максимальная цена квартиры, выставленной на ЦИАНе, при условии, что квартира находится в западном административном округе, в доме с количеством этажей от 5 до 10 и первым этажом, а площадь квартиры больше 50 квадратных метров?