Сводные отчеты excel. Управленческая отчетность образец в excel

Я работаю обычным аналитиком и, так получилось, что летом 2014 года, участвуя в одном e-commerce проекте, на коленке за 3 недели сделал управленческий учет в MS Excel. Давно планировал и наконец-то решил выложить на Хабр. Думаю, будет полезно малым предпринимателям, понимающим важность управления финансовыми потоками, но не желающим тратить значительное количество времени и средств на ведение управленческого учета. Не претендую на истину в последней инстанции и буду рад иным решениям, предложенным участниками сообщества.

Бизнес, к которому я летом имел отношение, был обычным интернет-магазином одежды премиум и выше сегмента с оборотом около 1 млн рублей в месяц. Бизнес работал, не сказать, чтобы очень успешно, но работал и продолжает работать. Собственник понимал необходимость ведения управленческого учета и, с этим пониманием, взял меня в качестве финансового директора (аналитика/менеджера...), так как предыдущий ушел из бизнеса за 3 месяца до моего прихода. Собственно, дыра такой же продолжительности была и в ведении управленческого учета. Забегая вперед скажу, что дыру не устранил (решили не ворошить прошлое), но создал систему, которая успешно работает при минимальных трудозатратах и по сей день.

Мой предшественник вёл управленку в Финграде , который оказался весьма мощным инструментом. Например, он позволял автоматически грузить информацию из 1С и выписок разных банк-клиентов, создавая проводки по заранее сформулированным правилам. Вещь, безусловно полезная, однако, при соблюдении системы двойной записи увеличивала время работы в разы. Чтобы избежать увеличения работы этот инструмент позволял генерировать «зависимые проводки». В создании этих дополнительных проводок и была зарыта собака. И тут выяснилось, что за всей мощью Финграда крылась уникальность, обусловившая полное отсутствие экспертизы в свободном доступе. Обычным пользователям (платившим, кстати, 3000 рублей в месяц за доступ к системе) были доступны лишь «Руководство пользователя» на официальном сайте, да 6 видео-уроков там же. Youtube, дававший доступ к ещё паре десятков видеоуроков, также не сильно помогал. Форумов с информацией «how to...» не было в принципе. Поддержка, на конкретные вопросы о правилах создания «зависимых проводок» и просьбах помочь именно в моем случае - морозилась фразами «у нас с вами не заключен договор на поддержку, поэтому на такие специфические вопросы мы не готовы отвечать». Хотя казалось бы - чего специфического в таких просьбах, да ещё и со скриншотами с моей стороны? Понятно, что все можно бить руками, но спрашивается, а зачем тогда вообще платить за инструмент, который сильно увеличивает время, необходимое на ведение управленки и не дает никаких преимуществ для малого бизнеса?

Убедив собственника в нецелесообразности использования «Финграда» при таких объемах бизнеса и выгрузив всю информацию из системы, я поставил на нем БОЛЬШОЙ и жирный крест. При этом решение уйти именно в MS Excel было не спонтанным. Хорошенько загуглив на тему ведения управленческого учета находил монстров, похожих на «Финград» , либо ссылки на веб-приложения для ведения личных финансов, в то время как основными требованиями к системе были:

Возможность ведения БДДС и БДР на основе изменяемого плана счетов;
- простота в дальнейшем ведении управленческого учета (в том числе силами «финансово-неграмотных» пользователей);
- гибкость (возможность на ходу расширять/убирать функционал);
- отсутствие перегруженности инструмента/интерфейса.

Для начала проясним термины: будучи не финансистом, под БДДС понимаю «Баланс Движения Денежных Средств», БДР - «Бюджет Доходов и Расходов». БДДС считаем кассовым методом (днем совершения операции - колонка «Дата операции») и используем для операционного day-to-day планирования, а БДР методом начисления (колонка «Период начисления») для стратегического, в рамках года и более.

Итак, как все устроено и как оно работает (в идеале):

1. Управленческий учет собирается на основе информации вводимой конечными пользователями при помощи формы в Google Docs. Красным помечены названия полей и кодировки вариантов в конечном файле управленческого учета - своего рода мапинг полей.

2. В итоге выглядит оно так (зеленым залито то, что перенесено в итоговый файл управленки).

3. Управленческий учет построен на базе.xls выгрузки из Финграда (отсюда странные для сторонних пользователей названия и, в целом избыточное количество колонок). Убедительная просьба не воспринимать всерьез значения колонок «Приход», «Расход» - многое рандомно изменено.

Механизм заполнения прост: аккуратно переносим во вкладку «Общая книга» из формы Google Docs и банковских выписок. Красным выделены строки, используемые для формирования БДР, зеленым - БДДС., которые представляют собой сводные таблицы и строятся на основе промежуточных вкладок с говорящими названиями. Единственные колонки, информация в которых не связана с иными источниками: «Исходный ID» (уникальные значения строк) и «Дата создания» (=ТДАТА(), а затем копируем и вставляем как значение)

4. Статьи ДДС (движения денежных средств) располагаются на отдельной вкладке «ПС_служебный» и вполне могут регулярно пересматриваться в зависимости от конкретных потребностей (не забываем обновлять формулы на листах «Данные_БДДС», «Данные_БДР»).

5. На картинке образец БДДС, в формате по умолчанию, свернутый до понедельной «актуальности».

6. Образец БДС (помесячный). Обратите внимание на уже упоминавшийся выше тезис об использовании строк из «Общей книги»: Бюджет и Факт для БДР, План и Факт - для БДДС.

7. Работа с БДДС подразумевает поддержание строк «План» в максимально актуальном состоянии. Я достаточно педантичен в работе с первичной информацией и комментарии сделанные мной сохраняли всю историю изменений. Как будет у Вас - вопрос к Вам. Мой подход позволил мне отлавливать примерно 1 существенную ошибку в неделю, грозившую расхождениями на десятки-сотни тысяч рублей. Время, кстати, съедалось немного.

8. Собственно сам

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

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

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

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

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

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

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

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


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

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


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


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

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


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


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

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


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


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


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

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

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

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


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


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


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

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


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

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


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

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

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

Термины многомерного анализа данных

Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.

Многомерные данные, измерения

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

• виды или группы товаров;
• бренды или категории товаров;
• периоды (месяц, квартал, год);
• покупатели или группы покупателей;
• регионы продаж
• и т.п.

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

• объем продаж;
• цена продажи;
• индивидуальная скидка
• и т.п.

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

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

OLAP

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта. По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server. Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.

Виртуальный куб данных

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

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

«Сводный отчет» (сводная таблица, Pivot Table) - это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок. Обновление отчета производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Интерфейс сводных таблиц Excel является, пожалуй, самым популярным программным продуктом для работы с многомерными данными. Он поддерживает в качестве источника данных как внешние источники данных (OLAP-кубам и реляционным базам данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).

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

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

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

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

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

Подготовка многомерных данных

Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период. Для простоты примера будем анализировать единственный числовой показатель – объем продажи в кг. Имеются следующие ключевые измерения данных: продукция, покупатель и перевозчик (транспортная компания). Кроме того, имеются несколько дополнительных измерений данных, являющихся признаками продукта: тип, бренд, категория, поставщик, а также покупателя: тип. Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.

Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам). Кроме того, используя автофильтр можно попробовать просуммировать данные по сочетаниям одного или нескольких ключевых критериев. Совершенно отсутствует информация о брендах, категориях и типах. Нет возможности сгруппировать данные с автоматическим суммированием по определенному ключу (например, по покупателям). Кроме того, набор дат зафиксирован, и просмотреть итоговую информацию за определенный период, например, 3 дня, автоматическими средствами не удастся.

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

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

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

От автофильтра к сводному отчету

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

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

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

Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица (в Excel 2000-2003 меню Данные \ Сводная таблица ).

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

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

Свойства и форматирование

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

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

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

Доступ к внешним данным

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

Excel поддерживает множество типов источников внешних данных:

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

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

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

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

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

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

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

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

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

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

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

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

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


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


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

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

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

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


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


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


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


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


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


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

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


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


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


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


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


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


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


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


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


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


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

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

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

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


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


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

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

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


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


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


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

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

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

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


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


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


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


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


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


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

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

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


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


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


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

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

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

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

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

• Сжатая форма

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

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

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

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

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

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

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

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

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

1. В окне открытого листа выделите любую ячейку в диапазоне данных.
2. Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица».
3. В списке команд выберите пункт «Сводная таблица» (рис. 5.59).
4. В окне «Создание сводной таблицы» (рис. 5.60) в графе «Таблица или диапазон» отобразится адрес выбранного диапазона анализируемых данных.






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

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

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

6. Закройте окно кнопкой «ОК».
7. На листе (новом или существующем) появится пустая сводная таблица в виде контура макета, а справа листа откроется область «Список полей сводной таблицы» для создания отчета (рис. 5.61). В нем можно вставлять в сводную таблицу новые поля, создавать макет и настраивать отчет.



8. Отметьте в окне «Список полей» имена столбцов для создания сводной таблицы.

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