Как сделать сводный отчет в excel

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

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

1. Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
2. На вкладке Вставка нажмите кнопку Сводная таблица.
3. В разделе Выберите данные, которые нужно проанализировать установите переключатель Выбрать таблицу или диапазон.
4. В поле Таблица/диапазон проверьте диапазон ячеек.
5. В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
6. Нажмите кнопку ОК.
7. Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.

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

Хотите узнать больше?

Нужна дополнительная помощь?

Нужны дополнительные параметры?

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

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

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

3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.

Как при помощи запроса Power Query реализовать в сводной таблице вывод динамического «Топ-N + Другие».

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

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

Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?

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

Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями «многие-ко-многим», простых мер на DAX и допиливании входных данных с помощью Power Query.

Что такое средневзвешенное значение и чем оно отличается от обычного среднего арифметического. Как вычислить средневзвешенное в Excel с помощью формул и в сводной таблице (в том числе с помощью DAX и Power Pivot).

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними — добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

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

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

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

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

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

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

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

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

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

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

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

Как за несколько секунд из огромной таблицы в несколько тысяч строк сформировать компактный, наглядный и правильный отчет? Один из лучших инструментов во всем Excel!

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

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

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

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

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

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

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

Самое рациональное решение – это создание сводной таблицы в Excel:

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

Просто, быстро и качественно.

• Первая строка заданного для сведения данных диапазона должна быть заполнена.
• В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
• В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

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

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

Порядок создания сводной таблицы из нескольких листов такой же.

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

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
3. Следующий этап – «создать поля». «Далее».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

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

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

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

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

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

В открывшемся меню выбираем поле с данными, которые необходимо показать.

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

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

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

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

Как создать сводную таблицу? Давайте рассмотрим с примерами.

Допустим, вы – поставщик бакалейной продукции, овощей и фруктов для крупных компаний. И ваша сводная таблица по всем показателям выглядит так:

Вам нужно узнать о продажах каждого вида продукции в разных городах страны.

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

Давайте узнаем, как сделать это в несколько кликов.

Шаг 1. Важно проверить, чтобы ваша таблица быть плоской — без объединённых ячеек. И у каждого столбца должен быть заголовок.

Шаг 2. Выделяем всю таблицу и идём на вкладку «Вставка», выбираем «Сводная таблица» и нажимаем «Ок» (менять в настройках ничего не нужно).

Если вы пока не знаете, каким образом хотите сформировать таблицу, вам поможет кнопка «Рекомендуемые сводные таблицы», которая предложит вам разные варианты формирования отчётности. Но раз у нас есть понимание, какие показатели нам нужны, давайте создадим таблицу самостоятельно.

Шаг 3. В правой стороне документа у вас появляется меню «Поля сводной таблицы». Именно там мы продолжаем свою работу. В первой части отметьте галочками те показатели (названия колонок), которые хотите видеть в сводной таблице: Товар, Регион продажи и Продажи. Они отразятся в областях ниже.

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

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

Столбцы – показатели с качественными признаками. Можно сделать колонки по годам или регионам.

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

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

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

Шаг 5.Как итог – мы получаем сводную таблицу с необходимыми нам данными. Сверху мы можем выбрать определенный город и посмотреть на то, как в нём идут продажи.

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

И вот как теперь будет выглядеть наша таблица:

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

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