Коллеги, добрый день. В этом видео мы начнем работать со сводными таблицами. Это простой и в то же время очень мощный инструмент для анализа данных, который позволяет рассчитывать сводную аналитику из сырых необработанных данных, например, быстро получить средний размер покупки по разным каналам продаж на основе выгрузки из админки интернет-магазина вашей учетной системы или другого источника. Сводная таблица — это динамический итоговый отчет, формирующийся на основе базы данных, которая в случае с Google таблицами будет представлена на одном из листов вашего документа. Это определение сводной от гуру Excel и автора книг Джона Уокенбаха. Сводная — это очень популярный инструмент Excel, который в таблицах имеет существенные преимущества. Сводные таблицы в них обновляются автоматически, а не вручную, как в Excel. Итак, давайте перейдем в наш документ — это статистика продаж книжного магазина, и поработаем со сводными на практике. Что мы видим в этой таблице? Одна строка у нас — это один товар, в данном случае книга. У нее есть количественный параметр — цена. И есть издательство. Это параметр, который повторяется. То есть у нас у разных книг разные издательства, но издательств меньше, чем товаров. Соответственно по этому параметру можно наши товары сгруппировать, например, посмотреть число книг по издательству. И есть год выхода — еще один параметр. И собственно продажи по месяцам в динамике. Такие данные хорошо подходят для сводной таблицы, потому что, как я отметил, есть у нас те столбцы, или поля, как их называют, по которым данные можно сводить, группировать. Например, это в данном случае издательство. Давайте попробуем это сделать. Что нужно, чтобы создать сводную? Выделяем нашу таблицу — это можно сделать быстро с помощью сочетания клавиш Ctrl + A. Заходим в меню «Данные», выбираем «Сводные таблицы». Есть два варианта: сводную можно разместить на новом листе либо на существующем. Как правило, предпочтительный вариант все-таки новый лист, чтобы не путались данные — исходник с аналитической таблицей, хотя в разных ситуациях может быть по-разному. Мы создали новый лист, он именуется «Сводная таблица» с номером по порядку, сколько сводных вы создавали в документе. Его, конечно, можно потом переименовать. Итак, пока мы видим пустой отчет в сводной таблице, его можно формировать, используя поля, то есть столбцы, из исходных данных. Давайте для начала посмотрим информацию, статистику по издательствам — сколько у нас книг каждого издательства имеется в продаже. Добавим в строки поле «Издательство» и увидим, что по строкам у нас появился список всех издательств, каждое встречается один раз. Соответственно данные будут группироваться по этому параметру, хотя в исходной таблице строк больше, чем здесь, потому что издательства повторяются и там много книг. Здесь мы видим уникальный список, пока он просто идет по алфавиту. Сортировку, кстати, здесь справа в «Редакторе сводной» можно поменять. Итак, теперь давайте добавим в значение какой-нибудь параметр. Если мы хотим просто посмотреть количество наименований — сколько у нас строк, в которых встречается каждое издательство, — достаточно выбрать любой параметр, где есть значение, например, «Книга». Хотя это текстовый столбец, текстовое поле и мы не получим никакой информации количественного характера, никаких средних или сумм, мы можем просто посчитать, сколько у нас книг каждого издательства присутствует в нашей исходной таблице. Обратите внимание, здесь используется функция COUNTA — по-русски СЧЕТЗ, то есть количество значений, соответствующих тому или иному издательству. Например, у «Альпины» здесь 193 книги в исходной таблице, у издательства «Азбука» — 25 и 1. Кстати, типичная ошибка, которую хотел показать: видимо, в одном из названий есть пробел, и видите, сводная уже обрабатывает их как разные. Это к слову про проверку данных, которую мы тоже отдельно обсуждаем в нашем курсе. Итак, здесь функция COUNTA — мы получили количество книг по издательствам. Мы пока даже не использовали количественные столбцы, например, «Средняя цена», но можем это сделать. Давайте добавим оптовую цену и здесь воспользуемся функцией AVERAGE, то есть среднее значение. В сводных таблицах, к сожалению, функции на английском, это изменить во всяком случае на данный момент в 2019 году невозможно в отличие от языка формул в ячейках. Итак, AVERAGE — это средняя цена, таким образом, мы получим среднюю цену по издательствам, такую вот сводную информацию. При этом заголовки сводной создаются из названия функции и исходного поля, AVERAGE из цены оптовой не очень, так скажем, благозвучный заголовок, всегда можно его перименовать, например, «Средняя оптовая цена» — это будет гораздо более понятно. И то же самое можно здесь — «Количество наименований», например. Таким образом, таблица станет более читаемая. Есть также такой раздел, как «Фильтры» в «Редакторе сводной таблицы». Туда можно добавить, например, год выхода. Поставить год выхода в фильтры, и таким образом, сам фильтр не отображается здесь в отчете сводной таблицы, но позволяет данные ограничивать, фильтровать. Например, как в обычном фильтре, выбрать только 2017 и 2018 год выхода, посмотреть новинки за два года. Наша таблица уменьшилась, то есть не у всех издательств, очевидно, были новинки, которые выходили в этот период, поэтому их стало меньше. Здесь возможна сортировка. Давайте уберем пока наш фильтр. То есть оставим возможность фильтрации здесь по году выхода, но снимем фильтр. Видите, показаны все объекты. И вернемся к нашей сводной, попробуем отсортировать. У нас сейчас сортировка идет по издательству, по возрастанию, то есть в алфавитном порядке. А мы с вами давайте сделаем сортировку по количеству наименований, по, например, убыванию. И таким образом у нас самое крупное издательство, у кого больше всего наименований в этой таблице, будет наверху. При этом, как я уже отмечал, в чем плюс Google таблиц — сводная будет обновляться автоматически при изменении исходных данных. В Excel, возможно, кто-то из вас помнит, нужно щелкнуть правой кнопкой и нажать «Обновить» в случае со сводными таблицам. Давайте теперь перейдем в другой документ, который у меня называется «Данные по городам», и поговорим про такую опцию, как «Рассчитываемое поле» в сводных таблицах — это аналог формул, который позволяет проводить арифметический расчет прямо в рамках сводной таблицы. Для начала построим сводную по нашим данным. Данные здесь тоже подходят для создания сводных, потому что у нас есть несколько полей, в которых повторяются какие-то параметры: месяц продажи, тип товара и город, в котором осуществлена продажа. И есть количественный столбец «Объем продаж». Очевидно, здесь можно группировать и по городам, и по типам товаров, и по месяцам в разных форматах. Выделяем таблицу и создаем на ее основе сводную, опять же, на новом листе. Давайте посмотрим на сочетание тип товара и, например, город. Что для этого можно сделать? Добавить, например, город в строки — вот появились все наши три города, а в столбцы добавить тип товара. И соответственно осталось посчитать на пересечении этих двух параметров информацию, например, сумму продаж. Добавим поле «Объем продаж» и здесь оставим функцию суммирования, то есть общая сумма на пересечении двух параметров. Например, в этой ячейке считаются все продажи по типу товара A в городе Томске. Если бы мы хотели, например, считать среднюю величину сделки, мы бы сделали AVERAGE. Если бы хотели максимальную или минимальную сделку, воспользовались бы функцией MAX или MIN соответственно. COUNTA — это количество сделок соответственно. Но мы оставим функцию SUM — суммирование по двум параметрам на их пересечении. И давайте теперь добавим рассчитываемое поле. В значение можно добавлять не только существующий столбец, существующее поле из исходной таблицы, но и так называемое рассчитываемое поле, в Excel оно называлось вычисляемым. Итак, в рассчитываемом поле нужно ввести формулу прямо в этом окошке. При этом поля обозначаются в апострофах, исходные поля таблицы. В данном случае что мы можем рассчитать? Давайте предположим, что мы выплачиваем комиссию 15 % от величины всех продаж. И мы хотим в этой таблице ее тоже наблюдать, а сколько, собственно, величина комиссии? Для этого нам нужно объем продаж умножить на 0,15, давайте объем продаж введем здесь как ссылку на поле. Напомню, вводится она вот так в апострофах, так как поле называется в исходной таблице, а далее можно производить арифметические операции: умножим на 0,15. И нажмем Enter, но, собственно говоря, уже все рассчиталось, нажмем Escape теперь, чтобы выйти из редактора сводной, и получим результат. Останется только озаботиться форматом ячеек, чтобы везде он был числовым. И также займем заголовками. Давайте переименуем созданный по умолчанию заголовок в более удобоваримый. Так, напишем «Объем продаж». Обратите внимание, что заголовок поменялся везде по всем типам товаров и в итоговом столбце. И в итоговом столбце по всем типам товаров и в каждом столбце тоже. И рассчитываемое поле 1 тоже переименуем, конечно, в «Комиссию», например, «Комиссия 15 %». Он тоже изменился везде. Итак, отмечу также, что сводные данные, то есть итоговые расчеты можно убрать, если снять вот эти галочки по строкам и по столбцам. Можно вернуть в любой момент только одно из них. То есть, например, здесь мы считаем «Итого по городам», можем это убрать и посчитать итого по типам товаров. А можно оставить и то, и другое. И коллеги, хочу напоследок обратить ваше внимание на следующую особенность сводных таблиц. Они не работают с открытыми диапазонами. Даже если вы укажете диапазон, например, A1:D в качестве источника, видите, он автоматически изменится до A1:D1000, то есть где 1000 — это номер последней существующей строки в документе. Соответственно, как только теоретически появятся новые строки в документе, в них появятся новые данные, сводная их учитывать уже не будет, они не попадут в расчет. Но диапазон, где указаны только столбцы, то есть начиная с первой строки, вот в таком формате A:D работать для сводных будет. Так что если ваши исходные данные начинаются с первой же строки листа и будут пополняться, просто используйте диапазон вообще без номеров строк. Итак, коллеги, в этом видео мы учились работать со сводными таблицами — очень удобным и несложным для применения инструментом анализа данных. Сводные таблицы позволяют решать многие задачи без использования формул: вычислять суммы, количества, средние значения и проводить другие расчеты по любым критериям: категориям товаров, филиалам, менеджерам и так далее.
