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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

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

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

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

Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать.

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец.

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

Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel.

Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов - просто наведите мышь, нажмите левую кнопку и тяните.

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

В параметрах полей значений вы найдете множество вариантов для анализа.

Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».

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

Также можно выбрать и значения для отдела.

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

Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки.

Обновление данных в сводной таблице в Excel

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».

Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра.

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».

Выберите все команды.

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.

Щелкайте «Далее» и создавайте на новом листе.

После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях.

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.

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

Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».

Вам откроются на выбор три типа для структуризации информации:

Сжатая форма

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

Структурированная форма

Все показатели подаются иерархично: от малого к большому.

Табличная форма

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Отличного Вам дня!

В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?

Совет 1. Автоматическое обновление сводных таблиц

Иногда требуется, чтобы сводные таблицы обновлялись автоматически. Предположим, вы создали сводную таблицу для менеджера. Вряд ли вы сможете регулярно обновлять ее, разве что менеджер допустит вас к своему ноутбуку. Можно включить автоматическое обновление сводной таблицы, которое будет выполняться всякий раз при открытии книги:

Щелкните правой кнопкой мыши на сводной таблице и в контекстном меню выберите пункт Параметры сводной таблицы .
В появившемся диалоговом окне Параметры сводной таблицы выберите вкладку Данные .
Установите флажок Обновить при открытии файла .

Рис. 1. Включите опцию Обновить при открытии файла

Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.

Скачать заметку в формате или , примеры в формате (файл содержит код VBA).

Совет 2. Одновременное обновление всех сводных таблиц книги

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

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

Способ 3. Воспользуйтесь кодом VBA для обновления всех сводных таблиц в рабочей книге по требованию. Данный подход предусматривает использование метода RefreshAll объекта Workbook. Для использования этой методики создайте новый модуль и введите следующий код:

Sub Refresh_All()

ThisWorkbook.RefreshAll

Совет 3. Сортировка элементов данных в произвольном порядке

На рис. 2 показан заданный по умолчанию порядок отображения регионов в сводной таблице. Регионы отсортированы в алфавитном порядке: Запад, Север, Средний Запад и Юг. Если ваши корпоративные правила требуют, чтобы сначала отображался регион Запад, а затем - регионы Средний Запад, Север и Юг, выполните ручную сортировку. Просто введите Средний Запад в ячейку С4 и нажмите клавишу Enter . Порядок сортировки регионов изменится.

Совет 4. Преобразование сводной таблицы в жестко заданные значения

Цель создания сводной таблицы - суммирование и отображение данных в подходящем формате. Исходные данные для сводной таблицы хранятся отдельно, в связи с чем возникают определенные «накладные расходы». Преобразование сводной таблицы в значения позволит использовать полученные в ней результаты без обращения к исходным данным либо кешу сводной таблицы. Способ преобразования сводной таблицы зависит от того, затрагивается ли вся таблица или только ее часть.

Для преобразования части сводной таблицы выполните следующие действия:

Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).

Если нужно преобразовать всю сводную таблицу, выполните следующие действия:

Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать . Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка .
Выберите параметр Значения и щелкните ОК .

Перед преобразованием сводной таблицы целесообразно удалить промежуточные итоги, поскольку они не слишком нужны в автономном наборе данных. Чтобы удалить все промежуточные итоги пройдите по меню Конструктор -> Промежуточные итоги -> Не показывать промежуточные итоги. Для удаления конкретных промежуточных итогов щелкните правой кнопкой мыши на ячейке, в которой эти итоги вычисляются. Выберите в контекстном меню пункт Параметры поля и в диалоговом окне Параметры поля в разделе Итоги выберите переключатель Нет . После щелчка на кнопке ОК промежуточные итоги будут удалены.

Совет 5. Заполнение пустых ячеек в полях СТРОКИ

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

Рис. 3. Использовать эту преобразованную сводную таблицу без заполнения пустых ячеек в левой части проблематично

Обратите внимание на то, что поля Регион и Рынок сбыта сохраняет ту же структуру строк, которая присуща при нахождении этих данных в области СТРОКИ сводной таблицы. В Excel 2013 существует быстрый способ заполнения ячеек в области СТРОКИ значениями. Кликните в области сводной таблицы, после чего пройдите по меню Конструктор -> Макет отчета -> (рис. 4). После этого можно преобразовать сводную таблицу в значения, в результате чего вы получите таблицу данных без пробелов.

Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки

Совет 6. Ранжирование числовых полей сводной таблицы

В процессе сортировки и ранжирования полей, содержащих большое количество элементов данных, не всегда легко определить числовой ранг анализируемого элемента данных. Более того, если сводная таблица будет преобразована в значения, назначенный каждому элементу данных числовой ранг, отображенный в целочисленном поле, значительно облегчит анализ созданного набора данных. Откройте сводную таблицу, подобную показанной на рис. 5. Обратите внимание на то, что один и тот же показатель - Сумма по полю Объем продаж - отображается дважды. Щелкните правой кнопкой мыши на втором экземпляре показателя и в контекстном меню выберите команду Дополнительные вычисления -> Сортировка от максимального к минимальному (рис. 6.)

После создания ранга можно настроить подписи полей и форматирование (рис. 14.9). В результате будет получен красивый ранжированный отчет.

Совет 7. Уменьшение размера отчета сводной таблицы

При формировании отчета сводной таблицы Excel создает снимок данных и сохраняет его в кеше сводной таблицы. Кеш сводной таблицы представляет собой специальную область памяти, в которой хранится копия источника данных для ускорения доступа. Другими словами, Excel создает копию данных, а затем хранит ее в кеше, связанном с рабочей книгой. Кеш сводной таблицы обеспечивает оптимизацию рабочего процесса. Любые изменения, внесенные в сводную таблицу, такие как изменение расположения полей, добавление новых полей либо сокрытие каких-либо элементов, выполняются быстрее, а требования к системным ресурсам оказываются гораздо скромнее. Основной недостаток кеша сводной таблицы заключается в том, что в результате его применения практически вдвое увеличивается размер файла рабочей книги при каждом создании сводной таблицы «с нуля».

Удаляйте исходные данные. Если рабочая книга содержит исходный набор данных и сводную таблицу, размер ее файла увеличивается вдвое. Поэтому можете спокойно удалить исходные данные, и это совершенно не отразится на функциональности вашей сводной таблицы. После удаления исходных данных не забудьте сохранить сжатую версию файла рабочей книги. После удаления исходных данных можно использовать сводную таблицу в обычном режиме. Единственная проблема заключается в невозможности обновления сводной таблицы из-за отсутствия исходных данных. Если же вам понадобятся исходные данные, щелкните дважды на пересечении строки и столбца в области общих итогов (на рис. 7 это ячейка В18). При этом Excel выгружает содержимое кеша сводных таблиц на новый рабочий лист.

Совет 8. Создание автоматически развертываемого диапазона данных

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

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

Для реализации описанной методики выделите исходные данные, а затем щелкните на значке таблицы, находящемся на вкладке Вставка (рис. 8) или нажмите Ctrl+T (Т английское). Щелкните ОК в открывшемся окне. Обратите внимание на то, что, хотя диапазон исходных данных в сводной таблице переопределять не нужно, но при добавлении исходных данных в диапазон в сводной таблице все равно придется щелкнуть на кнопке Обновить .

Совет 9. Сравнение обычных таблиц с помощью сводной таблицы

Если вы выполняете сравнительный анализ двух различных таблиц, удобно воспользоваться сводной таблицей, что существенно сэкономит время. Предположим, имеются две таблицы, в которых отображаются сведения о заказчиках за 2011 и 2012 годы (рис. 9). Небольшие размеры этих таблиц приведены здесь исключительно в качестве примеров. На практике используются таблицы, имеющие гораздо большие размеры.

В процессе сравнения создается одна таблица, на основе которой создается сводная таблица. Убедитесь в том, что у вас имеется способ пометить данные, относящиеся к этим таблицам. В рассматриваемом примере для этого используется столбец Фискальный год (рис. 10). После объединения двух таблиц воспользуйтесь полученным комбинированным набором данных для создания новой сводной таблицы. Отформатируйте сводную таблицу таким образом, чтобы в качестве тега таблицы (идентификатор, указывающий на происхождение таблицы) использовалась область столбцов сводной таблицы. Как показано на рис. 11, годы находятся в области столбцов, а сведения о заказчиках - в области строк. В области данных содержатся объемы продаж для каждого заказчика.

Совет 10. Автоматическая фильтрация сводной таблицы

Как известно, в сводных таблицах нельзя применять автофильтры. Тем не менее существует трюк, позволяющий включить автофильтры в сводную таблицу. Принцип использования этой методики заключается в том, чтобы поместить указатель мыши справа от последнего заголовка сводной таблицы (ячейка D3 на рис. 12), а затем перейдите на ленту и выбрать команду Данные -> Фильтр . Начиная с этого момента в вашей сводной таблице появляется автофильтр! Например, вы сможете выбрать всех заказчиков с уровнем транзакций выше среднего. С помощью автофильтров в сводную таблицу добавляется дополнительный уровень аналитики.

Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах

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

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

Шаг 1. Объединение всех полей, не относящихся к области столбцов, в один столбец. Для создания сводных таблиц с несколькими консолидированными диапазонами следует создать единственный столбец размерности. В рассматриваемом примере все, что не относится к полю месяца, рассматривается как размерность. Поэтому поля Рынок сбыта и Описание услуги следует объединить в один столбец. Для объединения полей в один столбец просто введите формулу, которая выполняет конкатенацию этих двух полей, используя точку с запятой в качестве разделителя. Присвойте новому столбцу имя. Введенная формула отображается в строке формул (рис. 14).

Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги

После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения . Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).

Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги

Шаг 2. Создание сводной таблицы с несколькими диапазонами консолидации. Теперь нужно вызвать знакомый многим пользователям по предыдущим версиям Excel мастер сводных таблиц и диаграмм. Для вызова этого мастера нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. . После запуска мастера установите переключатель В нескольких диапазонах консолидации . Кликните Далее . Установите переключатель Создать поля страницы и щелкните Далее . Определите рабочий диапазон и кликните Готово (подробнее см. ). Вы создадите сводную таблицу (рис. 16).

Шаг 3. Дважды щелкните на пересечении строки и столбца в строке общих итогов. На этом этапе в вашем распоряжении окажется сводная таблица (рис. 16), включающая несколько диапазонов консолидации, которая является практически бесполезной. Выберите ячейку, находящуюся на пересечении строки и столбца общих итогов, и дважды щелкните на ней (в нашем примере это ячейка N88). Вы получите новый лист, структура которого напоминает структуру, показанную на рис. 17. Фактически этот лист представляет собой транспонированную версию исходных данных.

Шаг 4. Разбиение столбца Строка на отдельные поля. Осталось разбить столбец Строка на отдельные поля (вернуться к изначальной структуре). Добавьте один пустой столбец сразу же после столбца Строка . Выделите столбец А, а затем перейдите на вкладку ленты Данные и щелкните на кнопке Текст по столбцам . На экране появится диалоговое окно Мастер распределения текстов по столбцам . На первом шаге выберите переключатель С разделителями и щелкните на кнопке Далее. В следующем шаге выберите переключатель точка с запятой и щелкните Готово . Отформатируйте текст, добавьте заголовок и превратите исходные данные в таблицу путем нажатия Ctrl+T (рис. 18).

Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)

Совет 12. Включение двух числовых форматов в сводную таблицу

А теперь рассмотрим ситуацию, когда нормализованный набор данных затрудняет построение удобной для анализа сводной таблицы. Примером может служить показанная на рис. 19 таблица, которая включает два разных показателя для каждого рынка сбыта. Обратите внимание на столбец D, который идентифицирует показатель.

Несмотря на то что эта таблица может служить примером неплохого форматирования, не все так хорошо. Обратите внимание на то, что одни показатели должны отображаться в числовом формате, а другие - в процентном. Но в исходной базе данных поле Значение имеет тип Double. При создании сводной таблицы на основе набора данных невозможно присвоить два разных числовых формата одному полю Значение . Здесь действует простое правило: одно поле соответствует одному числовому формату. Попытка назначить числовой формат полю, которому был присвоен процентный формат, приведет к тому, что процентные значения превратятся в обычные числа, которые завершаются знаком процента (рис. 20).

Для решения этой проблемы применяется пользовательский числовой формат, который любое значение, большее 1,5, форматирует как число. Если же значение меньше 1,5, оно форматируется как процент. В диалоговом окне Формат ячеек выберите вкладку (все форматы) и в поле Тип введите следующую форматирующую строку (рис. 21): [>=1,5]$# ##0; [<1,5]0,0%

Рис. 21. Примените пользовательский числовой формат, в котором любые числа, меньшие 1,5, форматируются как проценты

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

Совет 13. Создание частотного распределения для сводной таблицы

Если вы когда-либо создавали частотные распределения с помощью функции ExcelЧастота , то, наверное, знаете, что это весьма непростая задача. Более того, после изменений диапазонов данных все приходится начинать сначала. В этом разделе вы научитесь создавать простые частотные распределения с помощью обычной сводной таблицы. Вначале создайте сводную таблицу, в области строк которой находятся данные. Обратите внимание на рис. 23, где в области строк находится поле Объем продаж .

Щелкните правой кнопкой мыши на любом значении в области строк и в контекстном меню выберите параметр Группировать . В диалоговом окне Группирование (рис. 24) определите значения параметров, определяющих начало, конец и шаг частотного распределения. Щелкните ОК.

Рис. 24. В диалоговом окне Группирование настройте параметры частотного распределения

Если в сводную таблицу добавить поле Заказчик (рис. 25), получим частотное распределение транзакций заказчиков относительно размера заказов (в долларах).

Рис. 25. Теперь в вашем распоряжении оказалось распределение транзакций заказчиков в соответствии с размерами заказов (в долларах)

Преимущество описанной методики заключается в том, что фильтр отчета сводной таблицы может применяться для интерактивной фильтрации данных, основанных на других столбцах, таких как Регион и Рынок сбыта . У пользователя также имеется возможность быстрой настройки интервалов частотного распределения путем щелчка правой кнопкой мыши на любом числе в области строк с последующим выбором параметра Группировать . Для наглядности представления может быть добавлена сводная диаграмма (рис. 26).

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

Аналитикам часто приходится создавать различные отчеты сводных таблиц для каждого региона, рынка сбыта, менеджера и т.п. Выполнение этой задачи обычно подразумевает длительный процесс копирования сводной таблицы на новый лист и последующее изменение поля фильтра с учетом соответствующего региона и менеджера. Этот процесс выполняется вручную и повторяется для каждого вида анализа. Но вообще-то создание отдельных сводных таблиц можно поручить Excel. В результате применения параметра автоматически создается отдельная сводная таблица для каждого элемента, находящегося в области полей фильтра. Для использования этой функции просто создайте сводную таблицу, включающую поле фильтра (рис. 27). Поместите курсор в любом месте сводной таблицы и на вкладке Анализ в группе команд Сводная таблица щелкните на раскрывающемся списке Параметры (рис. 28). Затем щелкните на кнопке Отобразить страницы фильтра отчета .

Рис. 28. Щелкните на кнопке Отобразить страницы фильтра отчета

В появившемся диалоговом окне (рис. 29) можно выбрать поле фильтра, для которого будут созданы отдельные сводные таблицы. Выберите подходящее поле фильтра и щелкните ОК .

Рис. 29. Диалоговое окно Отображение страниц фильтра отчета

Для каждого элемента поля фильтра будет создана сводная таблица, помещенная на отдельный лист (рис. 30). Обратите внимание на то, что ярлычки листов называются так же, как и элементы поля фильтра. Учтите, что параметр Отобразить страницы фильтра может применяться к полям фильтра поочередно.

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

В совете 14 мы воспользовались специальной опцией для разделения сводных таблиц по рынкам сбыта на разных листах рабочей книги. Если же вам нужно разделить исходные данные по разным рынкам сбыта в отдельных книгах, можно воспользоваться небольшим кодом VBA. Для начала поместите поле, на основе которого будет выполняться фильтрация, в область полей фильтра. Поместите поле Объем продаж в область значений (рис. 31). Предлагаемый код VBA поочередно выбирает каждый элемент ФИЛЬТРА и вызывает функцию Показать детали , создавая новый лист с данными. Затем этот лист сохраняется в новой рабочей книге

Код VBA.

Sub ExplodeTable()

Dim PvtItem As PivotItem

Dim PvtTable As PivotTable

Dim strfield As PivotField

‘Изменение переменных в соответствии со сценарием

ConststrFieldName = " Рынок сбыта " ‘<—Изменение имени поля

Const strTriggerRange = " A4 " ‘<—Изменение диапазона триггера

‘Изменение названия сводной таблицы (при необходимости)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") ‘<—Изменение названия сводной

‘Циклический обход каждого элемента выделенного поля

For Each PvtItem In PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

Range(strTriggerRange).ShowDetail = True

‘Присваивание имени временному листу

ActiveSheet.Name = " TempSheet "

‘Копирование данных в новую книгу и удаление временного листа

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs _

Filename:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook.Close

Sheets(" Tempsheet ").Delete

Application.DisplayAlerts = True

Введите этот код в новый модуль VBA. Проверьте значения следующих констант и переменных и в случае необходимости измените их:

Const strFieldName. Имя поля, используемого для разделения данных. Другими словами, это поле, которое помещается в область фильтра/страниц сводной таблицы.
Const strTriggerRange. Ячейка триггера, в котором хранится единственное число из области данных сводной таблицы. В нашем случае ячейкой триггера является А4 (см. рис. 31).

В результате выполнения кода VBA данные для каждого рынка сбыта будут сохранены в отдельной книге.

Заметка написана на основе книги Джелен, Александер. . Глава 14.

Дата: 16 марта 2017 Категория:

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

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

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

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

данных в сводной таблице
одним из известных способов. Например, нажмите Ctrl+C на клавиатуре
Установите курсор в ячейку, где должен располагаться верхний левый угол вставляемого диапазона
Выполните на ленте Главная – Буфер обмена – Вставить – Значения .

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

Как получить детальные данные из сводной таблицы

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

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

Представим, что по какой-то причине нас заинтересовали подневные продажи метизов у Романа. Выделим ячейку на пересечении строки «Роман» и столбца «Метизы». Жмем на нем правой кнопкой мыши и выбираем «Показать детали». Программа создаст новый лист и отобразит на нем выборку из исходной таблицы, т.е. все продажи Романа метизов.

А можно просто сделать двойной клик по нужной величине. Это альтернативный, и более простой способ.

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

Дополнительные вычисления в сводной таблице

Если стандартных функций вычисления в сводной таблице (сумма, количество, отклонение и т.п.) Вам оказалось мало, инструмент имеет дополнительные вычислительные возможности. Прежде всего, нажмите правой кнопкой мыши в любой ячейке столбца с вычислениями и выберите «Дополнительные вычисления». В списке вычислений будет много интересных вариантов. К примеру. Мы хотим знать какую долю продаж от всего количества выполняет каждый менеджер из примера. Выполним дополнительные вычисления в столбце, где суммируются продажи. Параметром выберем «% от суммы по столбцу». Посмотрите, в 2 клика мы перешли от натуральных величин к процентам, теперь легче сравнивать показатели, или скрыть их, когда не рекомендуется отражать в отчете конкретные цифры в денежных единицах.

Просмотрите все возможные опции расчетов, и Вы наверняка найдете то, что можно использовать уже сейчас.

Вычисляемые поля и объекты

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

Вычисляемые поля

Эти объекты нужны, чтобы вставить в таблицу новые столбцы без вставки их в исходный массив данных. К примеру, у нас есть сумма продаж менеджеров и количество чеков. Рассчитаем в отдельном столбце средний чек.

Выполняем такую последовательность действий:

Установим курсор в одну из ячеек, содержащих значения
На ленте нажимаем: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемое поле
В открывшемся окне в поле «Имя» запишем «Средний чек»
Теперь вводим формулу, нам нужно поделить сумму продаж на количество чеков. Всписке полей дважды кликнем на «Сумма продаж», пишем на клавиатуре знак деления «/» и дважды щелкаем на «Количество чеков. Должна получиться такая формула:

Жмем Ок и смотрим, что получилось.

Теперь у нас появился еще один столбец, в котором посчитана средняя сумма в чеке для каждого менеджера. Его название появилось в области «Значения» панели настройки сводной таблицы.

Вычисляемые объекты

Похожий функционал предоставляют вычисляемые объекты. Но они вставляют в таблицу не столбцы, а строки. К примеру, у нас есть сумма продаж менеджеров, а нас интересует сколько составит НДС (18%) от этих продаж и общая сумма с НДС. Создаем вычисляемый объект:

Ставим курсор в любую строчку первого столбца или любой столбец первой строки
Жмем на ленте: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемый объект . Откроется окно вставки:

В поле «Имя» запишем «НДС», в списке «Поля» выбираем «Менеджер»
Кликая дважды на имя каждого менеджера, запишем формулу: =(Алексей+Анна +Виктор +Виктория +Виталий +Денис +Егор +Роман +Светлана)*0,18
Удалим ненужные поля, жмем Ок. Получаем еще одно поле, в котором будет посчитана НДС. Значение в этом поле будет добавлено к общей сумме.

Группировка данных в сводной таблице

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

Группировка с шагом

Если Вам нужно объединить числовые данные в несколько интервалов для минимизации отчета – используйте этот инструмент. Например, в нашей таблице есть данные о продажах по дням. Нам нужно сгруппировать эти данные по месяцам. В сводной таблице это можно сделать очень просто. Делаем по шагам:

Создаем сводную таблицу, где в строках будут дни, а в значениях – продажи. Если Вы не знаете, как создать сводную таблицу – сначала прочтите ;
Кликните правой кнопкой мыши по любой из дат в сводной таблице и в контекстном меню выберите «Группировать». Откроется окно настройки группировки;

В полях «Начиная с» и «по» автоматически установятся минимальная и максимальная даты в списке. Можете, при необходимости, указать здесь более узкий период для группировки
В списке «С шагом» выберите эталонный интервал времени. У нас это «Месяцы». Можно выбрать сразу несколько пунктов в этом списке. Давайте попробуем построить по кварталам и месяцам, отмечаем их;
Жмем «Ок» и сразу же получаем результат. Взгляните, что получилось:

Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:

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

Кликнем правой кнопкой в любо строке первого столбца (суммы продаж) и выберем «Группировать». В открывшемся окне задаем минимальное и максимальное числа для группировки, а так же, шаг. У нас это 1000. Вместо огромной таблицы, получаем компактную, из десяти строк. В каждой строке – интервал сумм и количество чеков в этом интервале.

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

Конечно, эта задача упрощенная, он отличается от тех, которые будете решать Вы. Но главное – понять механизм работы, тогда сможете применить его в своих расчетах.

Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному .

Кстати, чтобы отменить группировку – кликните правой кнопкой мыши по группированному столбцу и выберите «Разгруппировать».

Фильтрация сводных таблиц с помощью срезов

Если Вы мало знаете о срезах, или вообще с ними незнакомы – вот вам моя . Вкратце, срезы – это инструменты, позволяющие фильтровать данные благодаря выносным кнопкам. Это прекрасный способ создать пользовательский интерфейс, ведь в реальности не каждый пользователь Эксель продвинут, как Вы и не все умеют пользоваться автофильтром, поиском или фильтрами сводных таблиц.

А выглядит это вот так:

Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:

В окне «Дата» ищем и выбираем 01.04.2016;
В окне «Менеджер» выбираем «Роман»;
В окне «Группа товара» кликаем «Метизы»

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

По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез . На экране появится окошко, где нужно галочками отметить те поля сводной таблицы, по которым можно будет делать срезы. Каждому полю будет соответствовать свое окошко со списком. Выбирайте, жмите Ок и все, заработало!

Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу . После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.

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

Столкнулся недавно с проблемой:

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

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

Сводные_

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

После создания сводной таблицы и настройки ее структуры выделите всю таблицу и скопируйте ее в буфер обмена. Далее перейдите на вкладку Главная (Ноте) и щелкните на кнопке Вставка (Insert), а затем выберите в раскрывающемся меню команду Вставить значения (Insert Values), как показано на рис. 6.19.

Тем самым вы удалите сводную таблицу и замените ее статическими значениями, полученными на основе последнего состояния сводной таблицы. Полученные значения становятся основой создаваемой впоследствии сводной таблицы.

Рис 6.19. Команда Вставить значения применяется для получения на основе сводной таблицы обычной таблицы со статическими значениями

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

Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

Создание сводной Эксель таблицы требует соблюдения определенных условий:

Данные вписываются в таблицу, где есть столбцы и списки с названиями.
Отсутствие незаполненных форм.
Отсутствие скрытых объектов.

Как сделать сводную таблицу в excel: пошаговая инструкция

Для создания сводной таблицы необходимо:

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

Помечаться они будут галочкой, и для удобства анализа вы будете их менять местами в табличных областях.

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

Выбираем конкретного продавца. Зажимаем мышку и переносим поле «Продавец» в «Фильтр отчета». Новое поле отмечается галочкой, и вид таблицы немного изменяется.

Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.

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

Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.

Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.

Указываем периоды даты и шаг. Подтверждаем выбор.

Видим такую таблицу.

Сделаем перенос поля «Сумма» к области «Значения».

Стало видно отображение чисел, а нам необходим именно числовой формат

Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».

Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».

Оформление сводной таблицы

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

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

Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».

Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.

Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.

Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.

Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».

На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.

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

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

Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.

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

Для примера используем таблицу реализации товара в разных торговых филиалах.

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

Самое рациональное решение – это создание сводной таблицы в Excel:

Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
В меню «Вставка» выбираем «Сводная таблица».
Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

Первая строка заданного для сведения данных диапазона должна быть заполнена.
В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

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

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

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
Следующий этап – «создать поля». «Далее».
Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

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

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

Покажем детали по конкретному продукту. На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».

В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

Проверка правильности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

Тариф * количество человек / показания счетчика / площадь

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

Скачать все примеры сводной таблицы

Если при расчете коммунальных платежей применяются льготы, их тоже можно внести в формулы. Информацию по начислениям требуйте в бухгалтерии своей обслуживающей организации. Когда меняются тарифы – просто измените данные в ячейках.

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

На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.

Для того чтобы реализовать возможность создания такого списка, мы воспользуемся инструментами сводной таблицы. Добавим кнопку Мастер сводных таблиц в панель быстрогодоступа, которая недоступна нам на ленте, но осталась как рудимент от более ранних версий Excel.

Перейдите по вкладке Файл -> Параметры . В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.

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

Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.

На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.

На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.

На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.

На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.

Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о редактировании полей строк и столбцов в сводной таблице я писал в предыдущей статье.

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

Дважды щелкните по этой ячейке. Excel создаст новый лист, который будет содержать таблицу со списком значений.

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

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах . Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы (Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: (CTRL+SHIFT+L ), ;
исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

каждый столбец должен иметь заголовок;
в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» - названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
в таблице должны отсутствовать полностью незаполненные строки и столбцы;
в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

Более детальные советы по построению таблиц изложены в одноименной статье .

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

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

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

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

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

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

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

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы.
Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице Итоги по/ Количество .

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.

Изменение формата числовых значений

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

В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов .

Добавление новых полей

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

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная таблица примет следующий вид.

Меняем столбцы местами

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

Удаление полей

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

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Сводной таблицы, нажмите пункт меню ;
Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

Также данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.

Несколько итогов для одного поля

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

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

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

Выделите одно значение Сводной таблицы в столбце Названия строк;
В меню выберите пункт Группировка по полю ;
Появившееся окно заполните, как показано на рисунке ниже;

Нажмите ОК.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

Выделите все ячейки содержащие значения продаж;
Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов ;
Нажмите ОК.

Как сделать из сводной таблицы обычную