Сводные таблицы в excel что это и для чего
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Создание сводной таблицы в Excel
Сводные таблицы в программе Excel предназначены для быстрой группировки больших объемов данных для дальнейшей аналитики и формирования отчетности. Инструмент работает по принципу конструктора, когда из имеющейся таблицы могут быть получены те же данные под альтернативным углом зрения и в разных “разрезах” в другой таблице. Итак, давайте разберемся, каким образом строятся сводные таблицы в Эксель.
Классический способ составления сводной таблицы
В качестве примера рассмотрим алгоритм действий для составления сводных таблиц в Microsoft Excel 2019. Эта же логика может применяться и в более ранних версиях программы.
В качестве исходной (базовой) таблицы возьмем данные по продажам по продажам. В таблице присутствует информация о наименовании товара, поле, виде спорта, а также, основных экономических показателях, таких как количество проданных единиц, цена и сумма в руб. При этом, если по одному и тому же наименованию товара было несколько продаж, то и строчек будет несколько.
Для корректного формирования сводной таблицы всегда очень важно представлять себе, как должен выглядеть конечный результат. Другими словами, нужно понимать, какую информацию мы хотим видеть в обработанном виде.
В рассматриваемом нами примере цель сводной таблицы – понять, сколько женской обуви для тенниса с детализацией по наименованиям товара мы продали. Также сводная таблица должна отражать общую сумму по тому или иному наименованию. Причем если, например, кроссовки того или иного размера продавались не раз и данные по ним разбросаны по нескольким строкам, в итоге нам необходимо увидеть общую сумму одной строке.
Шаг 1. Создаем “умную таблицу”
Как только мы сделаем нашу базовую таблицу “умной” (или динамической, то есть способной автоматически изменяться), при добавлении в нее новых строк с данными, они в автоматическом режиме будут отображаться и в сводной таблице.
Примечание: Преобразование простой таблицы в “умную” не является обязательным требованием, и этот шаг, при желании, можно пропустить. В этом случае, после каждой корректировки исходной таблицы сводную придется формировать заново либо обновлять, пользуясь методом, который описан в конце статьи.
Шаг 2. Формируем сводную таблицу
Шаг 3. Применяем фильтры и другие настройки
Теперь вернемся к нашей задаче. Нам нужно отфильтровать данные по полу (женский) и оставить только строки, которые относятся к виду спорта “теннис”.
Различные варианты сводной таблицы
В рассмотренном примере был показан только один вариант построения сводной таблицы.
Чтобы сконструировать отличную от предыдущей форму, снова воспользуемся окном списка полей (справа). Если вы специально или случайно закрыли это окно, чтобы вернуть его, кликаем правой кнопкой мыши по любой ячейке внутри таблицы и в открывшемся контекстном меню выбираем пункт “Показать список полей”.
Давайте теперь попробуем перетащить в пустую область “Столбцы” поле “Цена, руб.”, предварительно убрав ранее заданные фильтры, чтобы увидеть, как именно преобразится таблица в ее начальном виде.
Благодаря нашим стараниям таблица изменила свой вид. Теперь есть возможность увидеть разбивку суммы по ценам за единицу товара. При желании и необходимости снова можно задействовать фильтры по полу или виду спорта.
Также можно попробовать добавить в таблицу еще больше информации. Для этого снова открываем список полей и перетаскиваем поле “Продано, шт.” в область “Строки”.
Данное действие позволит выявить, встречались ли в базовой таблице продажи под одинаковому наименованию товара в разных строках. Как видим, для беговых кроссовок 35 размера так и есть.
Помимо этого, в программе есть возможность представить числовые данные в виде гистограммы. Отмечаем необходимую ячейку, переключаемся в главную вкладку, нажимаем на функцию “Условное форматирование”, далее в открывшемся перечне кликаем по кнопке “Гистограммы” и щелкаем по варианту, который понравился больше всего.
Гистограмма настроена для выделенной ячейки. Осталось только нажать на кнопку справа от ячейки и выбирать одну из опций “Применить правило форматирования ко всем ячейкам…”.
Видоизмененная таблица стала более наглядной и привлекательной.
Использование Мастера сводных таблиц
Сводная таблица может быть создана с помощью инструмента под названием “Мастер сводных таблиц”. Но предварительно нужно вынести значок Мастера на Панель быстрого доступа. Для этого выполняем следующую цепочку действий:
Важные моменты, которые нужно учитывать при формировании сводных таблиц
Заключение
Итак, сводную таблицу в Excel можно создать, пользуясь двумя методами: классическим и с помощью Мастера сводных таблиц. Последний охватывает более широкий спектр альтернатив на этапе выбора источников данных, однако, в большинстве случаев первый способ более чем достаточен для для конструирования сводной таблицы.
Как сводные таблицы Excel помогут сэкономить ваше время?
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Порой у многих специалистов в экономике, бухгалтерии, анализе и финансах встает вопрос: «Как работать с таблицами, в которых десятки или сотни строк?» или «Как структурировать и анализировать большие таблицы?».
На помощь профессионалам пришел очень полезный инструмент — Сводные таблицы. Благодаря ему можно придавать презентабельный вид любым таблицам с нескончаемыми столбцами и строками: группировать, перемещать, показывать нужные и скрывать те, что не пригодятся, делать различные расчеты по встроенным и созданным вами формулам, фильтровать, — в общем, смотреть на данные с разных сторон в очень презентабельном виде.
Именно благодаря своим широким возможностям Сводные таблицы — просто незаменимый инструмент для людей, работающих с большим количеством информации, которую периодически нужно структурировать и анализировать. Рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel», если хотите узнать о других полезных возможностях программы.
В чем полезность сводных таблиц
Многие специалисты используют Microsoft Excel для вышеперечисленных целей, потому что программа сильно экономит время профессионала, забирая всю трудоемкую работу с данными на себя.
Еще отличительной чертой, из-за которой Excel стал популярным — создание достаточно нетривиальных средств анализа данных (Сводные таблицы входят в их число) не требует особых знаний по программированию.
Но перед тем, как начать пользоваться всеми «профитами» от Сводных таблиц Excel, нужно приложить немало усилий, чтобы хорошо освоить довольно сложную технику создания таблиц и работы с ними. Но в итоге, эффект от приобретения нового навыка, вот увидите, окупит потраченное время, энергию и силы.
В этой статье мы решили рассказать об основных технологиях создания Сводных таблиц в Excel и отметить некоторые особенности методов, которые могут Вам пригодиться в будущей работе.
Сначала предлагаем разобраться в том, что же такое Сводная таблица и «с чем ее едят».
Сводные таблицы — это перечень, содержащий выборочные данные из каких-либо источников (Базы Данных, таблицы Excel и другие), который нужен специалисту для дальнейшего анализа данных, изменения структуры таблицы и так далее.
Сводные таблицы позволяют менять визуальное представление данных, представляя их с новой точки зрения, не меняя сам источник.
Создание сводной таблицы в Excel
Прежде чем создавать Сводную таблицу необходимо подготовить таблицу с исходными данными для дальнейшего анализа:
Требования очень похожи на те, что выдвигают при проектировании реляционных баз данных, поэтому специалистам (или хотя бы тем, кто имеет базовое представление) в этой области не сложно будет провести параллель и освоить новый программный инструмент.
Вот мы и добрались до главного вопроса, который нас интересовал – создание Сводной таблицы в Excel. Начнем разбираться.
В статье мы будем приводить примеры на учебной таблице, где отображена информация о закупках товара, предположим, магазином одежды.
Сначала откроем таблицу, убедимся, что она удовлетворяет всем требованиям и со спокойной душой перейдем к следующему шагу. Найдем вкладку «Вставка» на панели быстрого доступа. Перед нами появится лента с доступным функционалом. Мы создаем Сводную таблицу, поэтому ищем одноименную кнопку. Она будет самой первой на панели.
«Кликнем» на значок, и перед нами появится диалоговое окно с перечнем необходимых параметров:
Выбрав все параметры, нажимаем ОК, и на новом листе выделяется область, где будет располагаться Сводная таблица, а справа появится панель управления, с помощью которой мы сможем превратить обычную таблицу в информативный отчет.
Полдела сделано — Сводная таблица создана, осталось заполнить ее данными. Для этого обратимся к окошку справа «Поля сводной таблицы».
Сверху мы видим область, где перечислены все атрибуты исходной таблицы: «Дата поступления», «Наименование товара», «Отдел» и так далее.
Теперь можно крутить, вертеть данные как угодно, придумывая концепции для Сводной таблицы.
Для начала попробуем объединить данные по отделам и высчитать суммарную стоимость товаров, пришедших в каждый отдел.
Для добавления поля в таблицу, нужно поставить галочку напротив названия атрибутов в списке полей в верхнем правом углу. В нашем случае — «Отдел» и «Стоимость товара в партии».
В окошке, отведенном под Сводную таблицу, появились три строки с названиями отделов (левый столбик) и стоимостью товара по каждому отделу. Помимо суммы можно считать максимум, минимум, среднее значение и делать другие математические операции. Итоговый результат по всем трем отделам указывается в самом низу таблицы.
Посмотрите, какие места заняли данные в нижней части панели настроек: поле «Отдел», содержащее текстовые значения, переместилось в область «Строки», а численные данные — в «Значения».
То же самое произойдет, если мы добавим в таблицу любое числовое поле (например, «Количество»): у текущей таблицы появится столбец, и в области «Значения» — «Сумма по полю Количество».
Теперь в нашей таблице отображается информация не только о том, на какую сумму пришло товара в каждый отдел, но и в каком количестве.
Если нас не устраивает такая ориентация таблицы, то из области «Строки» можно перетащить «Отдел» в область «Столбцы»: передвиньте курсор, удерживая кнопку мышки в нажатом положении.
Таблица стала намного компактнее: в строчке отображается результат вычислений, а по столбцам – отделы, которым соответствуют данные.
Такое расположение может быть удобным при маленьком количестве отделов (как в примере), но если их много, то мы получим очень вытянутую таблицу, которую невозможно будет охватить взглядом и в которой трудно будет найти необходимые данные. Такая таблица нам точно не нужна.
Как мы уже отметили, сумма — это далеко не единственная операция, применимая к данным. Посмотреть, какие формулы нам доступны, можно, нажав на название поля в области «Значения» и выбрав «Параметры полей значений».
Появится окно со списком операций, которые можно применять к данным. Среди них: количество, среднее, максимум, минимум, отклонение, дисперсия и другие, — все, что может понадобиться для качественного анализа.
Для каждой строчки в поле «Значения» можно применять разные формулы. Например, добавим в таблицу поле «Цена» и отобразим максимальные значения по каждому отделу.
Итак, какие выводы мы можем сделать по получившейся таблице? В магазин больше всего закупили Аксессуаров (на сумму 267660 руб.), причем самый дорогой товар стоил 2700 руб.
Как Вы могли заметить, мы еще ни разу не пользовались областью «Фильтры» на панели параметров. Этот раздел нужен для того, чтобы можно было сортировать данные по заданным критериям.
К примеру, добавим в Сводную таблицу данные, чтобы можно было отфильтровать записи по «Дате поступления». Для этого поставим напротив поля «Дата поступления» галочку. Это поле сразу переместиться в область «Строки».
Данные добавили, теперь нужно создать фильтр. Для этого просто перетаскиваем «Дату поступления» в область «Фильтры», как мы это делали раньше со столбцами и строками. Получим вот что:
Над всеми строками появилось еще одно поле — «Дата поступления», в котором мы можем выбрать дату, нажав на треугольник в конце строчки (Все).
Теперь легко узнать на какую суммарную стоимость пришел товар, сколько стоил самый дорогой в определенный день. Например, за 04.05.2017.
Помимо выставления определенной даты, можно выбирать название отделов. То есть можно сделать Сводную таблицу, где будет содержаться информация о том, на какую сумму 04.05.2017 поступило Аксессуаров и какова цена самого дорогостоящего товара.
Как обновлять таблицу
У многих пользователей, научившихся создавать Сводные таблицы по известному алгоритму, часто появляется затруднение: они меняют данные в источнике (у нас таблица Excel), а в сводной таблице при этом никаких модификаций не происходит.
Решение этой проблемы занимает не больше пары секунд: достаточно нажать на одну кнопку, но не все знают о ее существовании и месторасположении. Поэтому мы считаем нужным рассказать о том, как обновлять данные в Сводных таблицах.
Если изменения происходят с данными только в одном столбце, то достаточно нажать правой кнопкой мыши на ячейки в этом столбце и найти функцию «Обновить». После того, как Вы нажмете «Обновить», таблица преобразуется.
Если же изменения произошли больше, чем в одном столбце, то выделим произвольную зону таблицы и на панели зайдем в раздел «Анализ», в котором тоже есть функция «Обновить». Можно выбрать «Обновить», тогда функция применится к выделенной зоне или «Обновить все». Выбирайте то, что Вам нужно, и Сводная таблица сразу изменится.
Удаление Сводной таблицы
Раз уж мы поняли, как создавать и изменять Сводные таблице, давайте посмотрим, как их удалять.
Эта операция тоже не требует большой последовательности действия: выделим область, где находится таблица (чтобы сэкономить немного времени воспользуемся клавишами Ctrl + A — выделить все), и нажмем delete (или на правую кнопку мыши, а затем на «Удалить» в появившемся списке)
Если нужно удалить столбик в Сводной таблице, то достаточно убрать галочку напротив названия в списке полей таблицы.
Добавление новых столбцов/таблиц
Выше мы описали, как обновлять данные в Сводной таблице при том условии, что эти данные находятся в указанном при создании таблицы диапазоне.
А что делать, когда в Сводную таблицу требуется добавить дополнительную колонку с данными?
Для начала новый столбец нужно вставить в исходную таблицу (источник данных), а потом увеличить диапазон для Сводной таблицы. Если нужно добавлять целую таблицу, то сперва ее нужно объединить с исходной, и потом изменять диапазон.
Вот мы добавили к источнику столбик «Цена с НДС». Теперь заходим в раздел «Анализ» и открываем «Источник данных».
Программа сама переведет Вас на тот лист, где находится источник, и в диалоговом окне попросит указать диапазон значений. Выделяем пополненную данными исходную таблицу, жмем ОК. После этого необходимо обновить Сводную таблицу, как мы показывали выше.
После того, как Вы обновите таблицу, в перечне полей появится новое — «Цена с НДС». Теперь мы сможем использовать значения этого поля в анализе для изучения ситуации с новых ракурсов.
Заключение
Как вы видите, работать со Сводными таблицами не сложно и даже интересно. Достаточно понять технологию создания и основные принципы.
Поэтому, прочитав нашу статью и повторив все действия со своими данными, вы уже сможете записать умение работать со Сводными таблицами Excel в свое CV.
И действительно, Сводные таблицы зачастую являются достойным аналогом использования реляционных баз данных. Операции, которые в Excel можно делать простым «пертаскиванием», в программировании реализуют с помощью сложных запросов.
Конечно, полезная информация по этой теме на нашей статье не заканчивается. Есть еще очень много нюансов, которые невозможно изложить в одной статье.
Но нашей задачей было ввести в курс дела тех, кто еще не знаком с таким мощным инструментом анализа, как Сводные таблицы, рассказать о многочисленных возможностях, которые предоставляет Microsoft Excel, напомнить опытным пользователем моменты, которые могли забыться. И кажется, нам это удалось.
Рекомендуем скачать бесплатный гайд с горячими клавишами, чтобы ваша работа в Excel стала еще продуктивнее!
Если осталось что-то, на чем мы не заострили внимание, а вам бы очень хотелось это узнать, пишите нам об этом, и мы не оставим ваш интерес неудовлетворенным.
Научитесь использовать все прикладные инструменты из функционала MS Excel.