В этой части самоучителя подробно описано, как создать сводную таблицу в Excel. Данная статья написана для версии Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?

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

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

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

Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).

В результате сводная таблица примет вот такой вид:

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

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

К счастью, сравнительно недавно появился MS Office 2010-2013, который не только включает в себя ряд обновленных программ для обработки текстовых файлов, таблиц, баз данных и презентаций, но и позволяет работать с ними одновременно нескольким работникам, что в корпоративной среде просто бесценно.

Почему именно новые версии?

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

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

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

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

Что это такое и для чего она нужна?

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

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

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

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

Создаем нужный документ

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

• Время продажи.
• Проданный товар.
• Стоимость всего сбыта.

Таким образом, между всеми параметрами в каждом конкретном столбце образуется связь: предположим, что кроссовки были проданы в 9 часов утра, причем прибыль составила n-рублей.

Форматирование таблицы

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

• Если вы сразу же нажмете на кнопку «ОК», то сводная таблица Excel сразу же будет выведена на отдельный лист.
• Полностью настроить выведение.

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

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

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

Создание и группировка временных рядов

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

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

Используемые формулы

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

• В «Годовой» вставляем формулу вида: «=ГОД» (ссылаясь при этом на дату).
• Месяц нужно дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
• В третий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при этом на год и на месяц).

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

Как проводить по годам?

Снова зажимаем левой клавишей мыши пункт «Год», после чего тащим его в «Название столбцов», после чего сводная таблица начинает отображать результаты продажи конкретного вида товара по всем годам. А если необходимо так же проанализировать месячный сбыт? Точно так же зажимаем ЛКМ «Месяц», перетаскивая его под годовой столбец.

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

• Сумма продаж конкретного товара за весь год.
• Динамику продаж каждого из них по годам.

Убираем данные из выдачи

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

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

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

Рассчитываем прогнозы

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

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

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

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

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

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

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

Функция слияния в поздних версиях «Офиса» практически не изменилась в сравнении с Office 2003. Тем не менее запросы к базам данных для выбора источника данных могут выполняться с применением Microsoft Query. Чтобы получить данные сводной таблицы Excel, необходимо наличие двух объектов:

• Основной документ, содержащий нужную вам информацию, а также поля, указывающие программе Excel, какие данные следует вставлять в документ. Основная таблица является стандартным документом табличного процессора, однако необходимо указать в окне диалога Слияние (Mail Merge Helper), что именно он должен использоваться как основной.
• Источник данных, содержащий ту информацию, которая необходима для составления прогноза. Можно создать новую таблицу тем способом, который мы указывали выше.

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

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

Используемые категории

Также здесь можно использовать категорию ASK, что значит «запросить», и FILLIN, что значит «заполнить», NEXT и NEXTIF, ELSE. Также можно произвести настройку параметров тех пунктов прогноза, которые будут отображены в готовой таблице. Найти необходимую запись можно при помощи специального фильтра или же путем простой сортировки данных, о чем мы уже говорили выше.

Когда все готово для создания составного документа, окно диалога Merge предоставляет пользователю возможность выявить и устранить отдельные ошибки до их появления. Когда все проверено, то можно смело жать на кнопку «Объединить». Если же слияние должно производиться через электронную почту, то выйдет запрос на установку почтовой программы (если у вас она не установлена).

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

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

Термин «Сводная таблица» особо нам ничего не говорит. Простое определение может прозвучать так: Сводная таблица - это отчет, позволяющий просмотреть данные в более удобном и понятном виде. Чаще всего сводные таблицы используются для организации отображения данных из БД, но также есть возможность создавать сводные таблицы и с данных находящихся в книге. Важное требование, это структурно-организованная таблица (по типу реестра), где имеются поля и записи (строки). Например, таблица, перечисляющая сотрудников, имеет поля: Фамилия, Имя, Отчество, Должность, Дата рождения, Зарплата и т.д.

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

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

Открываем книгу на листе «Вариант 1»

Открываем «Данные – Сводная таблица… »

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

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

В видео-примере я указал диапазон "Вариант 1"!$A:$G

Это необходимо в том случае, если таблица постоянно дополняется данными, а постоянно строить таблицу может быть очень затруднительно или просто лень:) Таким образом, я указал диапазон столбцов, но диапазон строк ограничен только возможностями Excel (в 2003 это 65536 строк, в 2007-2010 более 1млн. строк). Но у такого способа есть небольшой недостаток, в таблицах появляется критерий «пусто» (увидите далее). Хотя мне он особо не мешает.

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

Жмем «Готово ».

Мы увидим следующую картину

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

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

Тип операции – тащим в левую зону

Поставщик – так же в левую, но немного правее Типа операции;

Наименование товара тащим в верхнюю зону;

Кол-во и Сумму тащим в самую большую зону поочередно;

Для разбивки по датам перетянем поле Дата в зону чуть выше области данных;

В результате получим такую таблицу:

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

Исправляем.

Для того чтоб изменить вариант расчета, необходимо навести на строку «Количество по полю Кол-во» и «Количество по полю Сумма» указатель мыши таким образом чтобы он принял вид черной стрелки:

Щелкнув один раз левой кнопкой мыши все строки группы «Количество по полю Кол-во» должны выделиться как на снимке выше.

Теперь жмем правой кнопкой мыши и в контексте выбираем пункт «Параметры поля »

В открывшемся окне параметров вычесления выберем «Сумма »

То же самое проделайте и для строк группы «Количество по полю Сумма»/

Теперь скроем излишние строки итогов. Для этого также выделяем группы:

И в контекстном меню выбираем пункт «Скрыть »

В результате должны получить таблицу следующего вида:

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

Получиться таблица вида:

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

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

Прикрепленный файл: svodnaya_excel.zip

Видео: Строим сводную таблицу в Excel

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

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

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

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

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

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

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

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

Я выставила итоги по среднему значению, и, как видите на рис. П1.5, теперь сводная таблица считает не сумму по месяцам и фамилиям, а среднее значение: среднюю зарплату по месяцам и среднее значение по работнику. Кроме того, вы можете по значениям сводной таблицы составить сводную диаграмму (рис. П1.6).

При этом появляется группа вкладок Работа со сводными диаграммами, в которой для вас нет ничего нового. Мы все это рассматривали, когда разбирали работу обычных диаграмм. Кстати, обратите внимание: я поменяла местами строки и столбцы, поэтому итоги считаются теперь по значению столбца Остаток (см. рис. П1.6). Я сделала это просто так, чтобы вы знали, что значения столбцов, строк и поле значений можно тасовать так, как вам удобно.

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

В группе Сортировка и фильтр вкладки Параметры нужно нажать кнопку Вставить срез и выбрать параметр, по которому вы хотите отфильтровать данные. Я указала месяц. Теперь вы сможете в окошке среза выбрать конкретный месяц, и в сводной таблице будут отображаться только данные, относящиеся к этому месяцу (см. рис. П1.6). В вашем распоряжении также появится целая вкладка - Инструменты для среза. Кстати, вы можете вставить в таблицу не один срез, а несколько.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Нажмите ОК.

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

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

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

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

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

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

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