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

Постановка задачи

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

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

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

Таблица с данными о поставках Таблица с данными о поставках

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

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

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

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

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

Решение задачи формулами

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

Так, например, чтобы определить город, приносивший максимальный доход, нужно просто сложить сумму всех доставок по каждому из городов. Это можно сделать с помощью функции СУММЕСЛИ.

То есть нам предстоит создать отдельную таблицу, которая с помощью функции СУММЕСЛИ суммирует данные по каждому из городов.

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

Удалить дубликаты Удалить дубликаты

Теперь воспользуемся функцией СУММЕСЛИ.

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

Доход по городам с использованием функции СУММЕСЛИ Доход по городам с использованием функции СУММЕСЛИ

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

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

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

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

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

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

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

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

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

Строка заголовка Строка заголовка

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

Создаем сводную таблицу Создаем сводную таблицу

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

Абсолютная привязка площади Абсолютная привязка площади

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

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

Делается это очень просто – также устанавливаем курсор таблицы на любую ячейку диапазона и либо выбираем Таблица на вкладке Вставка, либо просто нажимаем комбинацию клавиш Ctrl+T.

Создание смарт-таблицы Создание смарт-таблицы

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

Оставьте флажок «Таблица заголовков», снимите флажок «Таблица заголовков

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

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

Вставьте сводную таблицу на новый лист.

Сводная таблица строится на основе данных Таблицы 1 и размещается на новом листе Сводная таблица строится на основе данных Таблицы 1 и размещается на новом листе.

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

Совет 1 и боковая панель для создания сводной таблицы 2. Совет 1 и боковая панель для создания сводной таблицы 2.

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

Поля сводной таблицы соответствуют заголовкам смарт-таблиц Поля сводных таблиц соответствуют заголовкам смарт-таблиц

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

Области, образующие сводную таблицу Области, формирующие сводную таблицу

Например, нам нужно ответить на вопрос — поставка товаров в какой город позволила нам получить максимальный доход?

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

Поле «Город» генерирует список уникальных названий городов из соответствующего столбца смарт-таблицы. Поле «Город» генерирует список уникальных названий городов из соответствующего столбца смарт-таблицы

То есть область Rows позволяет размещать данные в строках.

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

Поле в области «Столбцы» создает заголовки столбцов сводной таблицы. Поле в области «Столбцы» создает заголовки столбцов сводной таблицы

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

Сводная таблица и таблица, полученные с помощью функции СУММЕСЛИМН Сводная таблица и таблица, полученные с помощью функции СУММЕСЛИМН

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

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

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

Возможные операции в диапазоне значений Возможные операции в диапазоне значений

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

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

Количество доставок в каждом городе Количество доставок в каждом городе

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

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

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

Доходы от отдельных сетей магазиновДоходы от отдельных сетей магазинов

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

Действие по аналогии.

Общий оборот по категориям продуктов Общий доход по категориям продуктов

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

Вложенное поле Товар Вложенное поле Товар

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

Вложенное поле Категория продукта Вложенное поле Категория продукта

В данном случае крайне неинформативно, поэтому верну все как было.

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

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

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

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

Форматирования сводной таблицы

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

Заголовок меняется самым обычным способом — кликаем по ячейке с ним и потом меняем текст.

Изменить заголовки полей в диапазоне значений Изменить заголовки полей в диапазоне значений

Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.

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

Имя заголовка столбца не может совпадать с именем соответствующего поля. Заголовок столбца не может совпадать с именем соответствующего поля

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

Я просто хочу переименовать поля в «Количество заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».

Сводная таблица с измененными заголовками столбцов Сводная таблица с измененными заголовками столбцов

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

Изменить формат числа в ячейке Изменить формат числа в ячейке

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

Изменить числовой формат для всего столбца Изменить числовой формат для всего столбца

Затем в появившемся окне укажите нужный формат и задайте параметры.

Параметры формата номера столбца. Параметры формата номера столбца

Форматирование будет применено ко всем столбцам сразу.

Формат номера валюты, применяемый ко всему столбцу Формат номера валюты, применяемый ко всему столбцу

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

Стиль сводной таблицы Стиль сводной таблицы

Общие и промежуточные итоги

А если говорить о контекстной вкладке Конструктор, то сразу следует сказать о настройках сводной таблицы, связанных с макетом.

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

Общие итоги и промежуточные итоги в сводной таблицеГлобальные и промежуточные итоги в сводной таблице

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

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

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

Промежуточные результаты Промежуточные результаты

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

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

Деактивировать промежуточные итоги Деактивировать промежуточные итоги

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

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

Итоги внизу группы Итоги внизу группы

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

Управление отображением итогов Управление отображением итогов

Макет сводной таблицы

Что ж, выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.

Выбор макета Выбор макета

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

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

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

Форма таблицы аналогична форме структуры, но промежуточные итоги из строки с названием категории смещены вниз.

Форма таблицы Форма таблицы

В этом же меню есть еще одна настройка, позволяющая повторять или не повторять метки элементов.

Ярлыки элементов Ярлыки элементов

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

Дублирующиеся товары — название категории в каждой строке Дублирующиеся товары — название категории в каждой строке

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

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

Вставить пустые строки между элементами Вставить пустые строки между элементами

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

Развернуть или скрыть поле или все поля сразу Развернуть или скрыть поле или все поля сразу

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

Скрыть кнопки скрыть кнопки

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

Готовый отчет Готовый отчет

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

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

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

1. Организуйте свои исходные данные

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

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

Полезные подсказки:

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

2. Создаем и размещаем макет

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

Откроется окно «Создать…». Убедитесь, что в поле «Диапазон» указан правильный источник данных. Затем выберите место для хранилища:

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

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

Полезные подсказки:

• В большинстве случаев имеет смысл разместить их на отдельной таблице. Это особенно рекомендуется для начинающих.
• Если вы извлекаете информацию из другого листа или книги, укажите их имена, используя следующий синтаксис: имя_рабочей_книгиимя_листа!Диапазон. Например, Book1.xlsx Sheet1!$A$1:$E$50. Конечно, вы не можете написать все вручную, а просто выделить диапазон ячеек в другой книге с помощью мыши.
• Может быть полезно построить таблицу и диаграмму одновременно. Для этого в Excel 2016 и 2013 перейдите на вкладку «Вставка», щелкните стрелку под кнопкой «Сводная диаграмма», а затем щелкните «Диаграмма и таблица». В версиях 2010 и 2007 щелкните стрелку под сводной таблицей, а затем щелкните Сводная диаграмма.

1. Организация макета.

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

• Раздел «Поля» содержит названия индикаторов, которые вы можете добавить. Они соответствуют именам исходных столбцов данных.
• Раздел «Макет» содержит фильтры, столбцы, строки и значения. Здесь вы можете расположить поля в нужном порядке.

Изменения, которые вы вносите в эти разделы, немедленно применяются к вашей электронной таблице.

3. Как добавить поле

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

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

• В область строк добавлены нечисловые значения;
• К диапазону добавляются числовые значения;
• Дата и время добавляются в область столбца.

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

Чтобы удалить любое поле, вы можете сделать следующее:

• Снимите флажок рядом с тем, который вы ранее отметили.
• Щелкните правой кнопкой мыши поле и выберите «Удалить……».

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

5. Как упорядочить поля?

Изменить расположение индикаторов можно тремя способами:

1. Перетащите поле между 4 областями раздела с помощью мыши. Кроме того, вы можете нажать и удерживать имя в разделе «Поле», а затем перетащить его в нужную область в разделе «Макет». Это удалит его из текущей области и поместит в новое место.

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

1. Щелкните поле в разделе «Макет», чтобы выбрать его. Это немедленно отобразит доступные параметры:

Любые внесенные вами изменения применяются немедленно.

Ну а если вы поняли, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав нужную клавишу).

6. Выберите функцию для значений (необязательно)

По умолчанию Microsoft Excel использует функцию суммы для чисел, которые вы вводите в диапазоне значений. Когда вы помещаете в эту область нечисловые (текстовые, даты или логические) или нулевые значения, к ним применяется функция Count».

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

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

Здесь же вы можете изменить имя на что-то более удобное и понятное для вас. Ведь он фигурирует в таблице, а значит и должен выглядеть соответственно.

В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте — на вкладке «Параметры», в группе «Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

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

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

Ключ к разгадке. Функция «Несколько метрик» может быть особенно полезна, когда вы добавляете одно и то же поле более одного раза и, как в нашем примере, одновременно показываете общий объем продаж и объем продаж в процентах от общего объема. Согласитесь, создавать такую ​​таблицу с обычными формулами придется долго. А тут — пара минут работы!

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

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

Из отчета (см выше) мы видим, что продано ТОЛЬКО 30 видеокарт. Чтобы узнать, какие данные использовались для получения этого значения, дважды щелкните по числу «30». Получаем подробный отчет:

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

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

Обновление данных:

Курсор должен находиться в любой ячейке сводного отчета.

Или же:

Правая кнопка мыши — обновить.

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

1. Курсор находится в любом месте отчета. Работа со сводными таблицами — Параметры — Сводная таблица.
2. Параметры.
3. В открывшемся диалоге — Данные — Обновлять при открытии файла — ОК.

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

Добавьте новые поля в сводную таблицу:

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

Если бы мы добавили столбцы в исходную таблицу, было бы достаточно обновить сводную таблицу.

После изменения выбора в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

Инструкции по добавлению настраиваемого поля:

1. Решаем, какие функции будет выполнять виртуальная колонка. К каким данным сводной таблицы должно относиться вычисляемое поле. Допустим, нам нужны остатки по группам товаров.
2. Работа со сводными таблицами — Параметры — Формулы — Вычисляемое поле.
3. Введите название поля в открывшемся меню. Поместите курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на области. Поэтому нет смысла выделять ячейки в сводной таблице. Из предложенного списка выберите категории, необходимые при расчете. Выберите «Добавить поле». Дополняем формулу необходимыми арифметическими действиями.
4. Нажмите «ОК». Появились остатки.

Группировка данных в сводном отчете

Например, рассмотрим себестоимость проданных товаров в разные годы. Сколько денег было потрачено в 2012, 2013, 2014 и 2015 годах. Группировка по дате в сводной таблице Excel делается следующим образом. Например, создадим простую сводку по дате и сумме доставки.

Щелкните правой кнопкой мыши любую дату. Выберите команду «Группировать».

Введите параметры группировки в открывшемся диалоговом окне. Дата начала и окончания диапазона отображается автоматически. Выберите шаг — «Год».

Мы получаем заказы в год.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пометьте каждый столбец заранее

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь мы видим, что в отдел «Фурнитура» поступил товар на сумму 267 660 рублей, а самый дорогой имеет цену 2 700 рублей.
Область «Фильтр» позволяет задать критерии отбора записей. Добавим поле «Дата получения», поставив напротив него галочку.

Теперь мы видим, что в отдел «Фурнитура» поступил товар на сумму 267 660 рублей, а самый дорогой имеет цену 2 700 рублей.
Область «Фильтр» позволяет задать критерии отбора записей. Добавим поле «Дата получения», поставив напротив него галочку.

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

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

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

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

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

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

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

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

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

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

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

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

Если сводная таблица в Excel не нужна, следует узнать, как ее удалить. Это не будет иметь большого значения. Выделите все компоненты вручную или используйте сочетание клавиш «CTRL+A». Затем нажмите клавишу «УДАЛИТЬ», и поле будет очищено.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видите, у нас есть одно значение в соответствующем диапазоне.

Анализ

Как только вы добавите (неважно как) сводную таблицу, вы увидите новую вкладку «Анализ» на панели инструментов. Содержит большое количество различных инструментов и функций.

Рассмотрим каждый из них подробнее.

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

Нажав на кнопку, отмеченную на скриншоте, вы можете сделать следующее:

• сменить имя;
  
• вызвать окно настроек.
  

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

Активное поле

С помощью этого инструмента вы можете сделать следующее:

1. Сначала нужно выделить ячейку. Затем нажмите кнопку «Активное поле». В появившемся меню нажмите «Параметры поля».
   
2. Сразу после этого вы увидите следующее окно. Здесь вы можете указать тип операции, которая будет использоваться для суммирования данных в выбранном поле.
   
3. Кроме того, вы можете настроить числовой формат. Для этого нажмите на соответствующую кнопку.
   
4. Откроется окно «Формат ячеек».
   

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

Группировать

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

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. В этом случае ничего сложного делать не нужно.

1. Выберите столбец. Затем нажмите на кнопку «Вставить диск».
2. В появившемся окне, например, выберите одно из предложенных полей (в дальнейшем их можно будет выбрать неограниченное количество). После того, как что-то выбрано, сразу активируется кнопка «ОК». Нажмите здесь.
   
3. В результате появится маленькое окошко, которое можно переместить куда угодно. Он предложит все возможные уникальные значения, которые есть в этом поле. Благодаря этому инструменту вы сможете снять сумму только в определенные месяцы (в данном случае). По умолчанию информация отображается за все время.
   
4. Вы можете нажать на любой из пунктов. Сразу после этого изменятся все значения в поле суммы.
   
5. Таким образом, можно будет выбрать любой период времени.
   
6. В любой момент все можно вернуть в первоначальный вид. Для этого нажмите на иконку в правом верхнем углу этого окна.

Читайте также: Ошибка в Excel при отправке команды в приложение: решаем проблему

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

Вставить временную шкалу

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

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

Далее необходимо построить сводную таблицу.

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

1. Затем нас просят выбрать диапазон значений.
   
2. Для этого достаточно выделить всю таблицу.
   
3. Сразу после этого адрес будет заменен автоматически. Здесь все очень просто, ведь он рассчитан на чайников. Нажмите кнопку OK, чтобы завершить сборку».
   
4. Редактор Excel предложит нам только один вариант, так как таблица очень простая (для примера больше и не нужно).
   
5. Попробуйте еще раз щелкнуть значок «Вставить временную шкалу» (на вкладке «Анализ»).
   
6. На этот раз ошибок не будет. Вам будет предложено выбрать поле для сортировки. Поставьте галочку и нажмите кнопку ОК».
   
7. Благодаря этому появится окно, в котором можно выбрать нужную дату с помощью ползунка.
   
8. Выбираем другой месяц, а данных нет, так как все расходы в таблице указаны только за март.
   

Обновить

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

Источник данных

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

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

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

Действия

С помощью этого инструмента вы сможете:

• снять со стола;
• выделять;
• подвинь это.
  

Вычисления

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

К ним относятся:

• вычисляемое поле;
  
• вычисляемый объект;
  
• порядок расчета (добавленные формулы появляются в списке);
  
• показать формулы (нет информации, так как формулы не добавлялись).
  

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

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

• каждый столбец должен иметь заголовок;

• значения в каждую колонку необходимо вводить только в одном формате (например, колонка «Дата поставки» должна содержать все значения только в формате даты; колонка «Поставщик» — название компании только в текстовом формате, или вы можно ввести код поставщика в числовом формате);

• таблица не должна содержать полностью пустых строк и столбцов;

• в ячейки необходимо вводить «атомарные» значения, т.е только те, которые нельзя разбить по разным столбцам. Например, нельзя ввести адрес в одну ячейку в формате: «Город, улица, номер дома». Необходимо создать 3 столбца с одинаковым названием, иначе сводная таблица будет работать неэффективно (на случай, если вам понадобится информация, например, в разрезе города);

• избегайте таблиц с «неправильной» структурой (см рисунок ниже).

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

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

.

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

создание таблицы в формате EXCEL 2007 добавляет новые возможности:

• когда вы добавляете в таблицу новые значения, в таблицу автоматически добавляются новые строки;

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

• таблице автоматически присваивается имя

  .

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

В таблице есть столбцы:

• Товар — наименование партии товара, например «апельсины

  »;

• Группа — группа товаров, например «Апельсины» входит в группу «Фрукты

  »;

• Поставщик — компания, поставляющая товары, поставщик может поставлять несколько групп товаров;

• Дата поставки — Дата поставки товара поставщиком;

• Регион продаж — регион, в котором была реализована партия товара;

• Продажа — Стоимость, по которой можно было продать партию товара;

• Продажи — период фактической реализации товара в регионе (в днях);

• Прибыль — показатель того, была ли получена прибыль от проданной партии товара.

Через обработку имен (формулы/определенные имена/обработка имен) мы скорректируем имя таблицы на «Исходная таблица

».

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

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

.

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

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

…

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

.

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

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

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

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

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

Сводная таблица будет выглядеть так.

Переключить столбцы

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