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

Именованный диапазон Excel

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

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

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

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

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

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

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

Рассмотрим создание сводной таблицы в Экселе на конкретном примере.

У нас есть таблица, куда занесены следующие данные: артикул, наименование товара, ID магазина, дата продажи, количество, цена.

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

Задача

На какую сумму продано товара за весь период в магазине с ID #11?

Примените команду: Верхнее меню → Вставка → Сводная таблица. В открывшемся окне задайте параметры новой сводной таблицы и нажмите ОК. Лучше всего будет разместить её на новом листе.

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

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

Ответ: 6230.

Если вы хотите скрыть ненужные строки и оставить, например, только итог по ID #11, проделайте следующее.

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

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

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

Способ 1. Использования опции «Размер таблицы»

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

1. Выделить все ячейки табличного массива, зажав левую клавишу манипулятора.
2. Перейти в раздел «Вставка», находящийся в верхней панели главного меню программы.
3. Развернуть подраздел «Таблицы», кликнув ЛКМ по стрелочке снизу.
4. Выбрать нужный вариант создания таблицы и нажать по нему.

Выбор варианта создания таблицы в Excel

1. В открывшемся окошке будет задан диапазон выделенных ранее ячеек. Здесь пользователю необходимо поставить галочку напротив строчки «Таблица с заголовками» и щелкнуть по «ОК».

Установка галочки в поле «Таблица с заголовками»

1. В верхней панели опций MS Excel найти слово «Конструктор» и кликнуть по нему.
2. Щелкнуть по кнопке «Размер таблицы».
3. В появившемся меню нажать на стрелку, находящуюся справа от строчки «Выберите новый диапазон данных для таблицы». Система предложить задать новый диапазон ячеек.

Указание диапазона для продления табличного массива в MS Excel

1. Выделить ЛКМ исходную таблицу и нужное количество строк под ней, которые необходимо добавить к массиву.
2. Отпустить левую клавишу манипулятора, в окне «Изменение размеров таблицы» нажать по «ОК» и проверить результат. Изначальный табличный массив должен расшириться на заданное количество ячеек.

Финальный результат расширения массива

Способ 2. Расширение массива через контекстное меню

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

1. Правой клавишей мышки нажать по любой строчки исходного табличного массива.
2. В окне контекстного типа нажать по слову «Вставить».

Вставка дополнительной строчки в табличку Эксель

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

Результат вставки элементов

Способ 3. Добавление новых элементов в таблицу через меню «Ячейки»

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

1. Войти во вкладку «Главная» в верхней области программы.
2. В отобразившейся панели инструментов найти кнопку «Ячейки», которая располагается в конце списка опций, и кликнуть по ней ЛКМ.

Кнопка «Ячейки» в верхней панели инструментов программы Excel

1. После выполнения предыдущей манипуляции развернется дополнительное окно, в котором будет представлено несколько вариантов работы с ячейками. Здесь пользователю нужно развернуть подраздел «Вставить», кликнув по соответствующему слову.
2. В контекстном меню выбрать один из представленных вариантов вставки ячеек. В данной ситуации целесообразно выбрать либо пункт «Вставить строки на лист», либо «Вставить столбцы на лист».

Выбор нужного варианта вставки

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

Способ 4. Расширение массива путем добавления сразу нескольких строчек или столбиков

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

1. В исходном табличном массиве выделить нужное количество строк левой клавишей манипулятора. Впоследствии к табличке добавится столько строчек, сколько было выделено изначально.
2. По любому месту выделенной области щелкнуть правой кнопкой мышки.
3. В меню контекстного типа нажать ЛКМ по варианту «Вставить…».

Действия по добавлению в таблицу Excel нужного числа строчек

1. В небольшом открывшемся окошке поставить тумблер рядом с параметром «Строку» и кликнуть по «ОК».
2. Проверить результат. Теперь к таблице под выделенными строками добавится такое же количество пустых строчек, что приведет к увеличению массива. Аналогичным образом к таблице добавляются столбцы.

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2016 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать «Параметры … » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

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

Думаю, стало даже лучше.

Как избавиться от заголовков «Метки строк» ​​и «Метки столбцов».

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

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

И вот что мы получим в результате.

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

Другое решение — перейти на вкладку «Анализ», нажать кнопку «Заголовки полей», выключить их. Однако это удалит не только все заголовки, а также выпадающие фильтры и возможность сортировки. А для анализа данных отсутствие фильтров – это чаще всего нехорошо.

Как обновлять данные в сводной таблице

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

13

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

14

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

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

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

1. Перейти в раздел меню «Данные» и выбрать соответствующий пункт.

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

1. После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».

1. Для завершения настроек жмем на «Готово».

1. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

1. К примеру, может получиться вот такой результат.

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

Как создать дашборд в Excel

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

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

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

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

Кроме всего прочего, умение работать с дашбордами говорит о профессионализме сотрудника. Такой навык сразу поднимает его на голову выше в глазах руководства.

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

1. Фигуры и объекты Word Art. Они позволяют рисовать все, что угодно, вплоть до инженерных чертежей. Кроме этого, есть множество текстовых меток, которые позволяют описать любую составную часть дашборда.
2. Использование сводных таблиц.
3. Графики, которые могут в качестве данных также использовать исходный диапазон.

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

Настройка сводной таблицы

Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method (Способ оплаты). Просто перетащите заголовок Payment Method в область Column Labels (Колонны):

Получим результат:

Выглядит очень круто!

Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим…

Перетащите заголовок Package (Комплекс) в область Report Filter (Фильтры):

Заметьте, где он оказался…

Это даёт нам возможность отфильтровать отчёт по признаку “Какой комплекс отдыха был оплачен”. Например, мы можем видеть разбивку по продавцам и по способам оплаты для всех комплексов или за пару щелчков мышью изменить вид сводной таблицы и показать такую же разбивку только для заказавших комплекс Sunseekers.

Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Продолжим настраивать…

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

Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из нее.

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

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

1. Для начала ее необходимо полностью выделить.

1. Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».

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

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

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

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

1. Для завершения настроек нужно нажать на кнопку «OK».

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

1. На этом этапе необходимо указать, какое поле будет:
   1. столбцом;
   2. строкой;
   3. значением для анализа.

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

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

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

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

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

1. Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.

1. Снова закрываем помощник для построения.

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

Сводная таблица в Excel средствами Power Query

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

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

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

В этом листе перейти во вкладку «Данные».

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

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

В появившемся окне надо указать книгу, откуда программа должна взять данные и нажать кнопку «Импорт».

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

Полезный материал про работу в Excel!

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

В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.

После этого источник данных не будет содержать лишней информации.

Дальше необходимо отредактировать данные в разделе «Параметры запроса».

Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».

Далее надо выбрать только нужную информацию. В контекстном меню колонки «Data» выбрать «Удалить другие столбцы».

Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.

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

Далее необходимо убрать лишние заголовки – «шапки». Для этого надо нажать «Использовать первую строку в качестве заголовков».

Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.

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

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

Далее, чтобы построить сводную таблицу, нужно во вкладке «Вставка» нажать кнопку «Сводная таблица».

В появившемся окне установить переключатель в положение «Использовать внешний источник данных» и нажать кнопку «Выбрать подключение».

В появившемся окне выбрать имя сформированного запроса, в нашем случае – «сводная» и нажать кнопку «Открыть».

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

Где применять

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

Расчет KPI

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

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

Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.

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

Отчет по персоналу

Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.

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

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

Сортировка значений

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

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

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

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

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

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

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

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

Создание сводной таблицы вручную

Иногда ни одна из сводных таблиц, предлагаемых инструментами быстрого анализа или кнопкой Рекомендуемые таблицы, не подходит. В подобных случаях можно либо выбрать готовый образец сводной таблицы, макет которого наиболее близок к требуемой вам сводной таблице, либо создать нужную таблицу “с нуля”. Создать сводную таблицу вручную не так уж и трудно, как может показаться на первый взгляд.

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

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

Если источник данных сводной таблицы представляет собой внешнюю базу данных, созданную в другой программе, такой как Access, установите переключатель Использовать внешний источник данных. Потом щелкните на кнопке Выбрать подключение, а затем в открывшемся диалоговом окне выберите требуемое подключение. Кроме того, Excel поддерживает анализ данных для нескольких связанных таблиц листа (так называемая “модель данных”). Если данные новой сводной таблицы будут анализироваться наряду с данными существующей сводной таблицы, то установите флажок Добавить эти данные в модель данных.

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

• ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
• СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
• СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
• ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

• Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
• +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
• Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.

Изменение итоговой функции сводной таблицы

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

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

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

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

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

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

Вот и всё! Теперь вы умеете создавать сводные таблицы в Excel, форматировать, сортировать и фильтровать данные.