Как сделать сводный отчет в excel. Почему именно новые версии? Убираем данные из выдачи.
Сводные таблицы считаются одним из самых мощных инструментов Excel для работы с данными. Они существуют для того, чтобы упростить сложную и громоздкую таблицу, а результаты вычислений сделать простыми, понятными и доступными. Но! Далеко не из каждой таблицы можно сделать сводную.
Таблица должна быть в виде обычного списка, то есть заголовки столбцов могут находиться только в первой строке. Пример такой таблицы я привела на рис. П1.1.
Подготовка данных сводной таблицы
В этом руководстве вы получите краткий обзор создания сводной таблицы. Для получения более подробного руководства перейдите на страницу. В этом примере исходные данные содержат информацию о продажах продуктов питания в двух регионах. После подготовки исходных данных вы можете создать сводную таблицу.
Точных вам прогнозов!
В вашей книге, на новом листе, в выбранном макете создается сводная таблица. Когда вы выбираете ячейку в сводной таблице, появляется список полей сводной таблицы справа от рабочего листа. Вы можете изменить макет сводной таблицы после того, как она была создана.
Если у вас в таблице есть какие-нибудь промежуточные заголовки или промежуточные итоги, то их нужно удалить. Чтобы не объяснять словами всю пользу сводной таблицы, я покажу это на примере. Жмем кнопку Сводная таблица в группе Таблицы меню Вставка (рис. П1.2).
Проверка интерактивной сводной таблицы
Ваш браузер не может показывать этот фрейм. Посмотрите это видео, чтобы увидеть шаги, и письменные инструкции находятся ниже видео. В этом примере есть 3 сводные таблицы. Сводные таблицы настраиваются с использованием согласованных имен и местоположений.
Обобщите листы
Итак, в этом случае формула будет. Возможность быстрого анализа данных может помочь вам принять более эффективные бизнес-решения. Но иногда трудно понять, с чего начать, особенно когда у вас много данных. Сводные таблицы отличаются высокой гибкостью и могут быть быстро скорректированы в зависимости от того, как вам нужно отображать результаты.
Откроется окно. В нем нужно сначала выбрать таблицу или диапазон таблицы, для которого составляется сводная таблица, и указать, куда ее поместить. Можно расположить ее на новом листе, а можно - рядом с исходной таблицей, на тот же лист, как вам будет удобнее (рис. П1.3).
Например, здесь приведен простой список домашних расходов и сводная таблица на основе этого. Примечание. Если у вас другая версия, ваш взгляд может несколько отличаться, но если не указано иное, функциональность такая же. Ваши данные должны быть организованы в табличном формате и не иметь пустых строк или столбцов.
- Типы данных в столбцах должны быть одинаковыми.
- Например, вы не должны смешивать даты и текст в одном столбце.
Я вставила сводную таблицу на тот же лист. В правой части окна появилась панель-конструктор для создания сводной таблицы, а сверху новая группа вкладок - Работа со сводными таблицами.
Как делается сводная таблица?
В правой части панели, которая называется Список полей сводной таблицы, вы видите список заголовков столбцов из таблицы, показанной на рис. П1.1. Из этих полей вы теперь, как из конструктора, можете скомпоновать новую таблицу. Для этого нужно мышкой перетащить название поля в необходимую область. Я решила, что названия месяцев у меня будут в столбцах сводной таблицы, а фамилии - в строках, а поле Значения я заполню значениями из столбца Получено. То есть в сводную таблицу войдут только данные о полученных деньгах. Результат показан на рис. П1.4.
Работа с списком сводных таблиц
Это помогает дать вам отправную точку для дополнительных экспериментов. После создания рекомендуемой сводной таблицы вы можете исследовать различные ориентации и перегруппировать поля для достижения конкретных результатов. В области «Имя поля» вверху установите флажок для любого поля, которое вы хотите добавить в сводную таблицу. По умолчанию в область строки добавляются нечисловые поля, поля «Дата» и «Время» добавляются в область «Столбец», а числовые поля добавляются в область «Значения». Вы также можете вручную перетаскивать любой доступный элемент в любой из полей сводной таблицы или если вы больше не хотите элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок.
Сводная таблица называется именно так потому, что сводит все ваши результаты в простую таблицу, подводя общие итоги. В данном случае таблица суммирует данные (в графе Значения написано Сумма по полю Получено). Кстати, откройте на вкладке Параметры список кнопки Вычисления (рис. П1.5).
Если вы добавляете новые данные в источник данных сводной таблицы, все сводные таблицы, которые были построены на этом источнике данных, должны быть обновлены. 
Если вы создали сводную таблицу и решили, что ее больше не хотите, вы можете просто выбрать весь диапазон сводной таблицы и нажать «Удалить». Он не будет влиять на другие данные или сводные таблицы или диаграммы вокруг него. Если сводная таблица находится на отдельном листе, который не имеет других данных, которые вы хотите сохранить.
Я выставила итоги по среднему значению, и, как видите на рис. П1.5, теперь сводная таблица считает не сумму по месяцам и фамилиям, а среднее значение: среднюю зарплату по месяцам и среднее значение по работнику. Кроме того, вы можете по значениям сводной таблицы составить сводную диаграмму (рис. П1.6).
Убираем данные из выдачи
Прежде чем создавать сводную таблицу, вы должны создать список данных с помеченными столбцами. В противном случае вы увидите следующее сообщение. Вы можете использовать сводную таблицу, чтобы суммировать данные, которые вы добавили в таблицу. Таблица может быть слишком большой, чтобы вы могли анализировать определенные части. Лучший способ узнать о сводной таблице - посмотреть, как ее создать.
Мы создали таблицу, показанную ниже. В таблицах столбцы представляют собой поля и строки - это записи. Мы хотим, чтобы сводная таблица показывала нам, сколько телефонных номеров находится в файле для каждого сотрудника. Будет открыт новый лист, и вы увидите сводную таблицу, сводную диаграмму и список сводных таблиц в качестве боковой панели в правой части экрана. Последний представляет собой список всех полей вашей таблицы.
При этом появляется группа вкладок Работа со сводными диаграммами, в которой для вас нет ничего нового. Мы все это рассматривали, когда разбирали работу обычных диаграмм. Кстати, обратите внимание: я поменяла местами строки и столбцы, поэтому итоги считаются теперь по значению столбца Остаток (см. рис. П1.6). Я сделала это просто так, чтобы вы знали, что значения столбцов, строк и поле значений можно тасовать так, как вам удобно.
Существует три вида полей. Поля данных - это поля, содержащие данные, которые вы можете добавлять, вычитать, умножать или делить.
- Категории полей - это поля, которые вы можете группировать.
- Произвольные поля - это поля, которые не являются ни данными, ни категориями.
- Поле имени в нашей таблице будет произвольным.
Просто перетащите из верхней части списка полей в нижнюю часть и поместите его в категорию: «Фильтры», «Столбцы», «Строки» и «Значения». Ваша сводная таблица появится в вашей таблице, как вы это делаете. Вы можете всегда перетаскивать в другой раздел, если хотите.
А еще в сводную таблицу можно вставить срез. Это дополнительный фильтр, который позволяет сделать результат еще нагляднее (рис. П1.7).
В группе Сортировка и фильтр вкладки Параметры нужно нажать кнопку Вставить срез и выбрать параметр, по которому вы хотите отфильтровать данные. Я указала месяц. Теперь вы сможете в окошке среза выбрать конкретный месяц, и в сводной таблице будут отображаться только данные, относящиеся к этому месяцу (см. рис. П1.6). В вашем распоряжении также появится целая вкладка - Инструменты для среза. Кстати, вы можете вставить в таблицу не один срез, а несколько.
Вот что мы сделали в списке полей справа. Теперь мы можем видеть, сколько телефонных номеров у нас было для каждой фамилии в нашей электронной таблице. Мы также можем использовать фильтр, который мы создали в верхней части, чтобы выбрать номер телефона, чтобы узнать, кому он принадлежит. Вы можете добавить фильтр для любой категории: имя, фамилия или номер телефона.
Вот лучший пример, потому что он показывает вам, что сводная таблица может делать с вашими данными. Давайте создадим сводную таблицу, используя таблицу выше. Мы хотим, чтобы значения, показанные в таблице, были зарплатой, поэтому мы перетаскиваем это значение в значения. Теперь мы можем видеть зарплату по департаменту и по описанию работы. Если бы у нас было более одной зарплаты за описание должностных обязанностей, это означало бы оклады для нас.
Я вам рассказала самые простые приемы работы со сводными таблицами. Если разберетесь с этим, сможете понять и все остальное. Только не забывайте о том, что прежде, чем создавать сводную таблицу, исходную таблицу нужно к этому подготовить, то есть сделать так, чтобы она не содержала никаких промежуточных заголовков и итогов. Ну а если что-то все-таки непонятно или вы хотите более подробно изучить возможности сводных таблиц, то рекомендую обратиться к материалам специального сайта Сводные таблицы Excel 2010 , который целиком и полностью посвящен методам работы с данными в сводных таблицах Excel 2010.
Суммы в сводной таблице отражают обе зарплаты. Сводные таблицы с помощью быстрого анализа. Более быстрый способ создания сводной таблицы - использовать инструмент быстрого анализа. Для этого выберите данные в таблице, которую вы хотите использовать для создания сводной таблицы.
Изменение порядка сортировки
Кнопка быстрого анализа отображается внизу справа, как показано ниже. Вы можете отформатировать сводную таблицу так же, как и любую другую таблицу. Вы можете выбрать макет для таблицы, а также стиль. Когда мы создали сводную таблицу, мы добавили фильтр при создании таблицы. Однако мы также можем добавить в таблицу слайсер. Слайсер также будет фильтровать ваши данные.
Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).
Сводная таблица в Excel 2010 используется для:
- выявления взаимосвязей в большом наборе данных;
- группировки данных по различным признакам и отслеживания тенденции изменений в группах;
- нахождения повторяющихся элементов, детализации и т.п.;
- создания удобных для чтения отчетов, что является самым главным.
Создавать сводные таблицы можно двумя способами. Рассмотрим каждый из них.
Давайте покажем вам, что мы имеем в виду, используя нашу первую таблицу в качестве примера. Перейдите на вкладку «Анализ инструмента сводной таблицы», затем перейдите в группу «Фильтр», как показано на рисунке ниже. Как вы можете видеть выше, мы выбрали имя.
Вы можете перемещать и форматировать его так же, как и любой другой графический объект. Вы также можете создать сводную диаграмму из таблицы. Выберите, если вы хотите создать только сводную диаграмму - или сводную диаграмму и таблицу. Мы собираемся создать сводную диаграмму.
Затем выберите, хотите ли вы его на новом или существующем листе. Теперь вы можете организовать свою сводную диаграмму так же, как и сводную таблицу. Единственное отличие состоит в том, что у вас две разные категории: ось и легенда. Изменение настроек поля для сводных таблиц и сводных диаграмм.
Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»
Перед тем как создавать отчет сводной таблицы, определимся, что будет использоваться в качестве источника данных. Рассмотрим вариант с источником, находящимся в этом же документе.
1. Для начала создайте простую таблицу с перечислением элементов, которые вам нужно использовать в отчете. Верхняя строка обязательно должна содержать заголовки столбцов.
При создании сводных диаграмм и таблиц вы можете изменить поля, перечисленные в списке полей справа, чтобы отображаемые данные отображались в сводной диаграмме или сводной таблице. Чтобы показать вам, как это сделать, давайте вернемся к нашей сводной диаграмме в последнем разделе. В разделе «Значения» в списке «Поле» мы замечаем, что он показывает сумму за каждый месяц, но мы хотим видеть средние значения.
Вы можете построить сводную таблицу примерно за одну минуту
Вы можете увидеть список полей справа. Итак, в определенном порядке, вот некоторые вещи, которые вы должны знать о них. У многих людей есть идея, что создание сводной таблицы является сложной и отнимающей много времени, но это просто неправда. По сравнению с тем временем, когда вы планируете создавать эквивалентный отчет вручную, сводные таблицы невероятно быстра. -организованные исходные данные, вы можете создать сводную таблицу меньше минуты.
2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».
Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажмите на стрелку в нижнем правом углу значка «Сводная таблица» и выберите пункт «Сводная диаграмма».
3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область.
Выберите любую ячейку для запуска данных. Очень простая сводная таблица за 30 секунд. Чтобы свести к минимуму проблемы в будущем, всегда используйте качественные исходные данные, организованные в виде таблиц. «Исключительные» исходные данные не будут содержать пустых строк или столбцов, а не промежуточные итоги. Каждый столбец будет иметь уникальное имя, каждое поле будет иметь значение в каждой строке, а столбцы не будут содержать повторяющиеся группы данных.
Когда вы сначала создаете сводную таблицу, используйте ее, чтобы сгенерировать простой счет сначала, чтобы убедиться, что сводная таблица обрабатывает данные, как вы ожидаете. Для этого просто добавьте любое текстовое поле в поле Значение. Вы увидите очень маленькую сводную таблицу, которая отображает общее количество записей, то есть общее количество строк в ваших данных. Если это число имеет смысл для вас, вам хорошо идти. Если число не имеет для вас смысла, возможно, что сводная таблица не правильно считывает данные или что данные не определены правильно.
В качестве данных для анализа можно указать внешний источник: установите переключатель в соответствующее поле и выберите нужное подключение из списка доступных.
5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.
В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.
Добавление новых полей
Через час это не так Так весело. Прежде чем вы начнете строить, напишите, что вы пытаетесь измерить или понять, и нарисуйте несколько простых отчетов в блокноте. Эти простые заметки помогут вам пройти через огромное количество вариантов, которые у вас есть в вашем распоряжении.
Используйте таблицу для ваших данных для создания «динамического диапазона»
Держите вещи простыми и сосредоточьтесь на вопросах, которые вам нужно ответить. Динамический диапазон автоматически расширяет и сокращает таблицу при добавлении или удалении данных, поэтому вам не придется беспокоиться о том, что в сводной таблице отсутствуют последние данные.
Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.
Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополнительных функций.
Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»
Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» — «Параметры» — «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.
Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.
1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:
- «в списке или базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется;
- «во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
- «в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников;
- «данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.
2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».
- Если в качестве источника выбран текущий документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область. Далее выберите место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
- Если же необходимо консолидировать данные из нескольких источников, поставьте переключатель в соответствующую область и выберите тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.
При выборе «Создать поля страницы» прежде всего придется указать диапазоны источников данных: выделите первый диапазон, нажмите «Добавить», потом следующий и т.д.
Для удобства диапазонам можно присваивать имена. Для этого выделите один из них в списке и укажите число создаваемых для него полей страницы, потом задайте каждому полю имя (метку). После этого выделите следующий диапазон и т.д.
- При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект поставки Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».
- Если в документе уже присутствует отчет сводной таблицы или сводная диаграмма — в качестве источника можно использовать их. Для этого достаточно указать их расположение и выбрать нужный диапазон данных, после чего будет создана новая сводная таблица.
Вам понравился материал?
Поделитeсь:
4,75
из 5, оценок: 4
)
