Отчеты в 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
Что такое отчет?
На мой взгляд, одним из лучших определений для отчетности является документ, используемый для отображения результатов для данного вида деятельности. То есть независимо от того, что вы делаете, вы можете создать отчет в Excel для представления или даже сохранения прогресса в деятельности, которую вы разрабатываете.
Такое раскрытие может быть устным, письменным или оцифрованным. И поскольку каждый день у нас нет времени терять, это значительно ускоряет вашу жизнь, чтобы знать, как создавать отчеты непосредственно из вашего источника данных, и, как мы уже знаем, Excel — отличный инструмент для манипулирования данными.
Как сделать отчет в Excel
Правда в том, что у вас есть несколько способов предоставить информацию в Excel. Я разделил сводную таблицу, сценарий, отчеты для печати и отчеты вручную. Я вижу, как создавать основные отчеты в Excel:
1 — сводный отчет
Прежде всего, он понимает, что такое сводная таблица. В принципе, это таблица, которая может быть организована динамически (очевидное среднее право?!). Но давайте сделаем это еще на один шаг . что позволяет этот тип сводной таблицы — создавать гибкие отчеты, где, согласно выбранным вами выборам, у вас уже есть результат перед тем, как вы суммированы и готовы.
Давайте посмотрим, как создать свой первый отчет сводной таблицы. Чтобы начать работу, вам необходимо иметь источник данных (таблицу с информацией), как в приведенном ниже примере таблицы продаж:
Теперь вам просто нужно преобразовать источник данных в таблицу. Для этого выберите все данные, перейдите на вкладку вставки и добавьте таблицу. После преобразования источника данных в таблицу добавьте Сводную таблицу. Процесс аналогичен, на той же вкладке вкладки выберите параметр сводной таблицы в левом углу. После этого откроется окно для создания отчета сводной таблицы:
Здесь просто выберите правильный диапазон или таблицу (обратите внимание, что в нашем примере интервал представляет собой таблицу 1) и нажмите кнопку OK. После этого вы откроете отчет сводной таблицы в Excel на новой вкладке:
Посмотрите, что в правом углу у вас есть часть управления отчета сводной таблицы, где у вас есть поля (столбцы таблицы) и области, где именно вы динамически управляете таблицей. Обратите внимание, что перетаскивая поля «Продукт и регион продаж» в область «Столбцы» и поля «Имя продавца» и «Имя клиента» в область «Строки», мы получаем очень большую таблицу. Это было создание нашего первого доклада. Теперь, если мы хотим, мы можем дальше манипулировать информацией, чтобы иметь более простые представления результата.
В этом случае мы берем поля 2 из областей строк и столбцов, чтобы упростить анализ сводного отчета. Я считаю это одним из самых мощных инструментов в Excel, потому что с его помощью вы можете увидеть в нескольких кликах много важной информации, которая станет основой для принятия решений вашей компанией или областью.
2 — Отчет о сценарии
Диспетчер сценариев — это инструмент данных Excel, который находится внутри кнопки «Тест гипотезы» на вкладке «Данные»:
Нажимая на вариант сценариев, вы уже открываете окно диспетчера сценариев, где вы можете сделать первый шаг для создания отчета. Смотрите, что у нас уже есть сценарии 2. Чтобы сделать отчет о сценарии в Excel, обязательно, что вы уже добавили некоторые возможности, иначе ничего не произойдет.
Создав сценарии, нажмите кнопку возобновления:
Следующее окно — это уже последний шаг для создания отчета о сценарии, который состоит из новой вкладки с различной информацией о сценариях, которые вы создали. Посмотрите, что важно определить, какова будет ячейка анализа. В нашем случае это ячейка O6.
При нажатии OK откроется новая вкладка со всеми возможностями изменения результата в ячейке O6 в соответствии с созданными сценариями. Обратите внимание, что сценарий оптимистичных яблок имеет лучший результат (2730), намного лучше, чем результаты 1870 и 2070 других сценариев.
Без возможности представления сценариев было бы сложнее и трудоемко оценивать наилучший сценарий.
3 — Распечатать отчет
Еще один очень важный отчет, когда дело доходит до Excel, — это отчет о печати. Именно в этом вы будете перечислять всю информацию из своей таблицы на бумаге. Это отличный способ представить материал кому-то, но важно, чтобы вы проявили некоторую осторожность.
Например, в нашей Рабочий лист денежного потока, мы создали специальную вкладку для отчета печати в Excel:
На этой вкладке мы организуем все данные, и, когда мы хотим сгенерировать печатный файл, нажмите CTRL + P, чтобы перейти к опции предварительного просмотра печати. Посмотрите, что впечатление от отчета идеально.
Если это не так, это можно отрегулировать с помощью инструментов конфигурации левой стороны или с помощью функции предварительного просмотра разрыва страницы (на вкладке «Экран»). В этом случае просто нажмите и перетащите синие линии, когда вы автоматически настроите отчет печати:
Эти синие линии демаркируются в соответствии с настройками, которые вы сделали, а также в соответствии с выбранной вами областью печати. Чтобы определить область печати, просто выберите нужный диапазон, перейдите на вкладку «Макет страницы» и нажмите на область области печати, чтобы выбрать параметр настройки.
4 — ручной отчет
Наконец, это не настоящий отчет Excel, но если вы знаете, как возиться с этим фантастическим инструментом, вы сможете создавать таблицы, которые будут служить в качестве отчетов, суммирующих ключевые данные в вашей электронной таблице. Вот пример нашей денежных потоков таблицу:
Обратите внимание, что в отчете функции SOMASES чтобы суммировать данные, которые находятся на нескольких других вкладках рабочего листа.
Создание отчетов в Excel
Теперь ваша очередь положить руку в тесто. Создавайте собственные отчеты в Excel. Если у вас есть какие-либо трудности, сообщите нам, какой из них поможет вам собрать лучшее резюме всего! Если вам нужна рука, мы рекомендуем Курсы Excel Beginner / Intermediate и Advanced Excel.