Цель работы: научиться создавать и применять сводные таблицы

при работе с данными

1 Мастер сводных таблиц

2 Построение макета сводной таблицы

3 Работа со сводной таблицей

Мастер сводных таблиц

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

Пример 1 Дан список "Экзаменационная ведомость" (рис.11.1).

Рисунок 11.1 Список "Экзаменационная ведомость"

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

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

В окне Создание сводной таблицы (рис. 11.2) нужно ввести следующую информацию:

1Указание вида источника сводной таблицы:

использование списка (базы данных Excel);

использование внешнего источника данных;

использование нескольких диапазонов консолидации;

использование данных из другой сводной таблицы.

Рисунок 11.2 Составление сводной таблицы

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

2Указание диапазона ячеек, содержащего исходные данные. Список (база данных Ехсеl) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде:

имя_книги] имя_листа! диапазон ячеек

(параметр в квадратных скобках необязателен)

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

3 Выбор места расположения и параметров сводной таблицы.

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

2 Построение макета сводной таблицы.

После нажатия кнопки ОК будет сформирована сводная таблица со стандартным именем СводнаяТаблица1 и список её полей (рис. 11.3). Если список полей не вывелся, нужно щелкнуть правой мышью по имени СводнаяТаблица1 и выбрать команду Показать список полей.

Структура сводной таблицы состоит из следующих полей и областей, определяемых в макете (рис.11.3):

Поля для добавления в отчёт – для размещения полей, значения которых обеспечивают отбор записей в сводную таблицу. На странице может быть размещено несколько полей, между которыми устанавливается иерархия связи - сверху вниз, определять страницу необязательно. Значок списка в правом верхнем углу позволяет задать расположение полей и областей в столбик (как на рис. 11.3), рядом, только поля, только области и др.

Рисунок 11.3 Макет отчёта сводной таблицы

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

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

Область Названия строк - поля размещаются сверху вниз, обеспечивая

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

Область ∑ Значения по умолчанию появляется вариант Сумма по полю Оценка. При необходимости использования другой функции в качестве подведения итогов свода нужно щелкнуть мышью по строке Сумма по полю Оценка и в раскрывающемся меню выбрать опцию Параметры полей значений. В результате появится окно (рис. 11.4), во вкладке Операция можно выбрать одну из 12 функций (количество, среднее, максимум, произведение, …, дисперсия), во вкладке Дополнительные вычисления – задать доли, нарастающий итог и пр. – см. табл. 1. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элемент содержит значения поля, участвующего в пользовательском вычислении.

Таблица 1Виды дополнительных функций в полях области данных

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

В окне можно также задать числовой формат – общий, денежный, процентный и др.

Рисунок 11.4 Окно Параметры полей значений

Для выполнения задания примера 1 в окне списка полей для добавления в отчёт нужно поставить галочки во всех полях, затем перетащить мышью поля № группы, Фамилия И.О. и № зач. кн. в раздел Названия строк, поле Код преп – в раздел Названия столбцов, а поле Оценки – в Фильтр отчёта. В области ∑ Значения по умолчанию стоит вариант Сумма по полю Оценка, поэтому нужно щелкнуть мышью по этой строке и в появившемся окне во вкладке Операции заменить Сумма на Среднее (рис. 11.4).

Как видно из рис. 11.5, сводная таблица содержит три раздела, название каждого раздела (№ группы), средний бал по каждому преподавателю и общий итог по разделу приводятся в его первой строке.

В последней строке таблицы приводится общий итог по всем разделам свода.

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

В областях строка или столбец каждое поле размещается только один раз. Поля, помещённые в область для столбцов, остаются столбцами, а поля, помещённые в область для строк, разворачиваются в строку. На их пересечении формируются группы и получаются итоговые (сводные) значения в области для данных- группировочные поля. В области Фильтр отчёта могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данных. Для каждого такого поля в области ∑ Значения задается вид функции и выполняется необходимая настройка.

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

Рисунок 11.5 Исходная таблица, сводная таблица и список её полей.

После завершения построения сводной таблицы могут измениться исходные данные. Для обновления данных сводкой таблицы в этом случае следует выполнить командуДанные\ Обновить все .

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

Контрольные вопросы

1 Что называется сводной таблицей, сводом?

2 Для чего предназначены сводные таблицы?

3 Что помещается в областях для строк, столбцов, данных макета?

4 Как задать в сводной таблице вид вычислительного итога Сумма, Максимум, Произведение?

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

6 Как изменить структуру сводной таблицы (добавить или изменить поля строк, столбцов, данных?

Задание

Для таблицы своего варианта из лабораторной работы №9 "Списки в MS Excel. Сортировка и фильтрация данных" построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.

1 Название работы

2 Цель работы

4 Задание и своды (на носителе) – две таблицы.

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

6 Выводы по работе

Лабораторная работа №12

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

С их помощью можно легко обобщить некоторые однотипные данные.

В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.

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

Анализ данных документа способствует более быстрому и правильному решению поставленных задач.

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

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

• С помощью вкладки «вставка», которая находится в главном меню панели инструментов программы, выберите клавишу создания рекомендуемых сводных таблиц;

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

Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт ».

• Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
• Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
  Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
  Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);

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

В пустой шаблон необходимо добавить поля, формулы для расчета, фильтры.

Пустая форма заполняется путем перетаскивания на отдельные области необходимых элементов данных.

Также можно создавать связанные таблицы-сводки на нескольких листах документа одновременны.

Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.

Проводить анализ внешних данных тоже можно с помощью сводных таблиц.

Сводные расчеты в Microsoft Excel - Формулы

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

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

Все формулы в столбики и строки добавляются с помощью поля «Вставка».

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

Еще интересный материал на тему:

Функции для сводных таблиц в Экселе - Виды

Сводные таблицы в табличном процессоре могут выполнять следующие функции:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для создания Сводной таблицы на вкладке «Вставка » в группе «Таблицы » нажмите кнопку «Сводная таблица » (Рис.1).

Рис. 1 Создание сводной таблицы.

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

Рис. 2 Выбор диапазона сводной таблицы.

Теперь у вас открылся лист, в котором формируется сводная таблица. Справа от сводной таблицы находится список полей (Рис. 3). Выбирая поля, мы формируем сводную таблицу, причем порядок выбора важен.

Рис. 3 Новая сводная таблица.

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

Рис. 4 Сформированная сводная таблица.

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

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