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

Отчеты в EXCEL

history 21 января 2013 г.
    Группы статей

  • Анализ таблиц и Сводные таблицы
  • Имена
  • Расширенный фильтр
  • Таблицы в формате EXCEL 2007

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

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

В таблице имеются столбцы:

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

Через Диспетчер имен откорректируем имя таблицы на « Исходная_таблица » (см. файл примера ).

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

Отчет №1 Суммарные продажи Товаров

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

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

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

  • Скопируйте полученный список на лист, в котором будет размещен отчет;
  • Отсортируйте перечень товаров ( Данные/ Сортировка и фильтр/ Сортировка от А до Я ).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1 ) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel .

Также можно легко подсчитать количество партий каждого Товара:

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B 8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил относительной адресации , теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8 , в Буфер обмена , затем вставить ее в диапазон С8: G 8 , нажав CTRL + V . В ячейки ниже формулу можно скопировать Маркером заполнения .

Отчет №3 Фильтрация Товаров по прибыльности

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

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

Суммарные продажи подсчитаем следующей формулой массива : =СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5="(Все)";1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL + SHIFT + ENTER .

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

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

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

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B .

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА() , которую нужно ввести как формулу массива :

Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .

Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ() : =СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]

Отчет №5 Статистика поставок Товаров

Теперь подготовим отчет о поставках Товаров за месяц. Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы: =МИН(Исходная_Таблица[Дата поставки])

Создадим перечень дат — первых дней месяцев , начиная с самой ранней даты поставки. Для этого воспользуемся формулой: =КОНМЕСЯЦА($C$5;-1)+1

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

Применив соответствующий формат ячеек, изменим отображение дат:

Формула для подсчета количества поставленных партий Товаров за месяц:

=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* (Исходная_Таблица[Дата поставки] ГОД() .

Теперь для вывода промежуточных итогов по годам создадим структуру через пункт меню Данные/ Структура/ Промежуточные итоги :

  • Выделите любую ячейку модифицированной таблицы;
  • Вызовите окно Промежуточные итоги через пункт меню Данные/ Структура/ Промежуточные итоги ;
  • Заполните поля как показано на рисунке:

После нажатия ОК, таблица будет изменена следующим образом:

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

Резюме :

Отчеты, аналогичные созданным, можно сделать, естественно, с помощью Сводных таблиц или с применением Фильтра к исходной таблице или с помощью других функций БДСУММ() , БИЗВЛЕЧЬ() , БСЧЁТ() и др. Выбор подхода зависит конкретной ситуации.

Excel для бизнеса: как строить сводные таблицы

Гайд от аналитика в Laba.

cover.exel-60896ee101699579267744.jpg

Галабурда

Если вам интересна эта статья, то вам точно понравится наш обширный список формул в Excel

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

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

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

Работа с этими таблицами интуитивно понятна.

Как должны выглядеть источники данных

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

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

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

На основании этих данных и будем строить сводный отчет.

Сначала убедитесь, что исходные данные правильно оформлены:

#1. Каждый столбец должен содержать единый тип данных — даты должны находиться в поле с датами и иметь формат дат. В поле «Наименование» не должно быть информации о городе покупки.

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

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

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

Выделите любую ячейку уже «умной» таблицы и дайте ей подходящее имя.

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

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

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

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

Пройдемся по пунктам:

#1. Выбор таблицы, на основании которой мы хотим построить сводный отчет.

#2. Использование внешних источников данных — это могут быть подключения через встроенную в Excel (начиная с версии 2013 года) среду Power Query или с помощью других надстроек. В нашем случае нет потребности использовать эту функцию.

#3. Выбираем лист, где будем создавать таблицу.

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

Нажимаем «ОК». Таблица создана.

Разбиваем макет на блоки и разбираемся, что где находится.

#1. «Анализ сводной таблицы». Панель инструментов, в которой можно найти дополнительные функции обработки таблицы и форматирования данных.

#2. «Конструктор». Здесь можно подобрать другой стиль таблицы или создать свой, настроить тип отображения данных, итоговые поля и другие полезные функции.

#3. Местоположение сводной таблицы.

#4. Настройки отображения списка полей таблицы.

#5. Быстрый поиск столбцов или расчетов.

#6. Столбцы и расчеты таблицы.

Ссылка на основную публикацию