Как сделать сводную таблицу в Excel

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

Сводные таблицы созданы для того, чтобы быстро и с комфортом обрабатывать большое количество данных. Освоив эту функцию, вы сможете по праву считать себя мастером офисного пакета Excel.

Что такое сводная таблица?

Итак, сводная таблица — это инструмент для обобщения и обработки большого объема информации в Excel. Благодаря этому приему, пользователю проще воспринимать данные среди бесконечных столбцов и ячеек. Данная функция позволяет создавать отдельные таблицы на основе уже существующих и сортировать их по своему усмотрению, задавая различные фильтры и критерии.

Для примера, мы создадим сводную таблицу в Microsoft Office Excel 2007 и покажем, как с ней работать.

Как создать сводную таблицу?

В появившемся окне вы должны указать область или имя таблицы. Обычно оно уже указано по умолчанию. Также можно выбрать: создать сводный список на текущем листе или на новом. Для удобства откроем еще один лист.

На новом листе появится область для будущей таблицы и список полей. Среди них вы увидите заголовки столбцов с предыдущего листа.

Чтобы сформировать сводную таблицу, надо определить, по какому критерию мы будем ее собирать. В нашем случае фильтром отчета может быть Заказчик. Надо захватить этот заголовок и с помощью мыши перетащить его в соответствующую область.

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

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

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

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

Если выбрать Гену, в таблице будут указаны только те фрукты, которые купил он. Представьте теперь, что в списке не два, а сто заказчиков и несколько сотен товаров: в таком случае не обойтись без сводной таблицы.

Сортировать можно не только по фильтру отчета, но и наименованию строк и столбцов. Например, если нас интересует результат продажи определенных товаров, мы можем изменить сводную таблицу, выделив только те строки, которые хотим.

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

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

Мы также можем сортировать списки по строкам, столбцам и фильтру отчета. Например, если отфильтровать таблицу по дате, мы увидим только те продажи, которые произошли в выбранный день.

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

[expert_bq id=»1570″]1 Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 1) Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only . Если у вас много разрезов информации, то проще сделать таблицу и заполнить ее, используя функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и правила работы с относительными ссылками.

Как делать сводную таблицу в Excel

2. В открывшемся окне в соответствующих полях введите имя элемента и формулу. В формуле можно использовать элементы других полей, но не функции рабочего листа. В нашем примере именем элемента является Комиссия 1 кв., а формула имеет следующий вид:

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

Мое мнение – этот инструмент надо изучать самим и в этой статье я расскажу нюансы, об которые спотыкаются новички, которые сами хотят освоить этот инструмент.

СТ требует правильно построенных данных. Идеальный вид – одной операции в строчку соответствуют разные поля свойств записи.

Я сваял простую таблицу, которая возникает на любом хозяйственном складе. Склад отгружает 4-м магазинам в течение года стройматериалы и каждая операция отгрузки товара имеет свою запись.

Как видите, здесь нет никакой группировки или суммирования данных, я называю это простыней, т.к., хотя столбцов может быть много, но строк тут гораздо больше. Здесь нет разрывов, что тоже важно.

Дальше, из любого места этих данных вызываете команду на вкладке Вставка – Сводная таблица.

Excel предложит диапазон, поэтому просто кликайте Ок.

Дальше на новом листе конструктор предложит сформировать будущий каркас сводной таблицы:

Здесь 4 варианта: строки, столбцы, значения и фильтр отчета.

Остановимся поподробнее. Строки и столбцы в общем-то одинаковые по сути категории. Вы можете сюда поставить те поля, которые являются признаковыми – например, наименование, месяц, категорию. Можно поставить два в строки и ни одного в столбец, в этом случае ваша таблица будет иметь группировку в строках.

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

1) Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only . Если у вас много разрезов информации, то проще сделать таблицу и заполнить ее, используя функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и правила работы с относительными ссылками.

Формат функции прост («Значения»; «Сводная таблица»; «Поле1»; «Элемент1»; «Поле2»; «Элемент2» и т.д.)

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

2) Чтобы постоянно обновлять данные и не трогать макет, добавляйте данные внизу простыни. Тогда у вас ничего не поползет, а обновление произойдет одной кнопкой обновить по самой СТ. Плюс в источнике данных для СТ укажите или все столбцы простыни (ленивый вариант) или имя диапазона, размеры которого будете менять произвольно, не заходя в настройки СТ.

[expert_bq id=»1570″]Начав с простого примера и освоив эту функцию, вы сможете с легкостью оперировать большими объемами данных и быстро создавать отчеты по разным критериям. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Конечно, это совсем не та сводная, что на основе одного листа: нет толковых и понятных заголовков столбцов; набор функций ограничен; да и вообще функционал скуден по сравнению с обычной сводной(например, нет группировки дат и значений). Может быть поэтому в Microsoft посчитали правильным убрать данный пункт с глаз, чтобы не вводить в заблуждение.

Что такое сводная таблица?

Так же на данный момент есть еще одна возможность сбора данных с нескольких листов и файлов — при помощи бесплатной надстройки PowerQuery. Как это сделать можно почитать и посмотреть в статье: Собрать и просуммировать данные из разных файлов при помощи PowerQuery