Отчеты в MS EXCEL. Примеры и методы

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

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

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

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

https://www.youtube.com/watch?v=ytadvertiseru

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

=СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])

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

=СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)

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

Отчеты в MS EXCEL. Примеры и методы

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

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

Предлагаем ознакомиться  Как обезопасить себя при покупке дачи

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

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

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

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

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

https://www.youtube.com/watch?v=upload

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

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

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

Отчеты в MS EXCEL. Примеры и методы

=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)*ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица[Прибыль]=$B$5)))

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

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

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

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

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

=ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)

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

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

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

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

Отчеты в MS EXCEL. Примеры и методы

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

=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]{amp}gt;=B9)*(Исходная_Таблица[Дата поставки]{amp}lt;B10))

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

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

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

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

https://www.youtube.com/watch?v=ytpolicyandsafetyru

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

You May Also Like

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Adblock detector