Создание сводных таблиц в excel презентация
Обновлено: 04.07.2024
является чрезвычайно мощным и удобным средством анализа и трансформации данных. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным.
Сводная таблица во многом объединяет
возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация .
Возможности Сводных таблиц
С помощью сводной таблицы можно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным образом, а также получить все необходимые промежуточные итоги и сортировки. Организацию сводной таблицы иллюстрирует рисунок.
Пользователь может расположить свои данные по любой из трех осей куба.
Видимые данные должны находиться на оси столбцов и строк. Данные, не представленные сейчас, но доступные в любой момент для включения в сводную таблицу, располагаются на оси страниц.
Положение всех элементов сводной таблицы в процессе анализа можно изменять, располагать их по осям в желаемом порядке.
Формирование сводных таблиц
Для формирования сводных таблиц предназначена команда
Вставка, Сводная таблица,
которая вызывает Мастера сводных таблиц, позволяющий
в наглядной форме сформировать нужную сводку, которую
в дальнейшем, при желании, можно неограниченно трансформировать и «улучшать».
Сначала выбирают поля для добавления в отчет,
При этом автоматически эти поля фиксируются в нижнем окне,
И формируется сводная таблица
Этап 1
Указание вида источника данных: в качестве источника могут быть использованы
• списки (базы данных Excel);
• внешний источник данных;
• несколько диапазонов консолидации;
• данные из другой сводной таблицы.
В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим наиболее распространенный случай использования списков собственной таблицы Excel.
Этап 2
Указание адреса (диапазона ячеек, содержащего исходные данные) исходных данных. Список (база данных Excel) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде [имя_книги]имя_листа!диапазон ячеек Запрос появляется при нажатии кнопки Далее .
Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопка 0бзор , в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.
Построение структуры сводной таблицы.
После нажатия кнопки Далее появляется следующее окно Мастера, в котором нужно кнопку Макет .
Структура сводной таблицы состоит из следующих областей, определяемых в макете:
страница — на ней размещаются поля, значения которых обеспечивают отбор записей на первом уровне; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи — сверху вниз; страницу определять необязательно;
столбец — поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно;
строка — поля размещаются сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку необязательно;
данные — поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно.
В правой части окна отображены кнопки-названия всех полей, участвующих в сводке, которые с помощью мыши можно перетаскивать в свободные сейчас области структуры макета документа. В области Столбец и Строка переносятся поля, сведения о которых будут находиться на соответствующих осях формируемой таблицы
Каждое поле размещается только один раз в областях:
страница, строка или столбец. По этим полям можно
формировать группы и получать итоговые значения в области данные — группированные поля. В области
данные могут находиться поля произвольных типов,
одно и то же поле может многократно размещаться в области данные. Для каждого такого поля задается вид
функции и выполняется необходимая настройка.
Этап 3
Этап 4
Указание местоположения готовой сводной таблицы . Нужно указать, куда поместить
таблицу, на новый лист или на существующий Ее местоположение, в общем, безразлично (указать ячейку, например, А13),
Слайды и текст этой презентации
1. Создание сводной таблицы
2. Структура и редактирование сводной таблицы
1. Создание сводной таблицы
Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации.
Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных.
Сводная таблица используется для:
выявления взаимосвязей в большом наборе данных;
группировки данных по различным признакам и отслеживания тенденции изменений в группах;
нахождения повторяющихся элементов, детализации и т.п.;
создания удобных для чтения отчетов
Перед тем как создавать отчет сводной таблицы, определимся, что будет использоваться в качестве источника данных.
Для начала следует создать простую таблицу с перечислением элементов, которые нужно использовать в отчете.
Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»
Верхняя строка обязательно должна содержать заголовки столбцов.
2. На вкладке «Вставка» выбрать из раздела «Таблицы» инструмент «Сводная таблица».
Если вместе со сводной таблицей нужно создать и сводную диаграмму – следует нажать на стрелку в нижнем правом углу значка «Сводная таблица» и выбрать пункт «Сводная диаграмма».
3. В открывшемся диалоговом окне «Создание сводной таблицы» выбрать только что созданную таблицу с данными или ее диапазон. Для этого выделить нужную область.
В качестве данных для анализа можно указать внешний источник: установить переключатель в соответствующее поле и выбрать нужное подключение из списка доступных.
4. Далее нужно указать, где размещать отчет сводной таблицы. Удобнее всего это делать на новом листе.
5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета.
В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет».
Следует отметить необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.
Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке.
Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр».
В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.
Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополнительных функций.
Чтобы применить этот способ, следует сделать доступным инструмент, который по умолчанию на ленте не отображается:
1. выполнить команду
«Файл» — «Параметры» — «Панель быстрого доступа».
2. в списке «Выбрать команды из» отметить пункт «Команды на ленте».
3. Выбрать «Мастер сводных таблиц и диаграмм».
4. Нажать кнопку «Добавить».
Иконка мастера появится вверху, на панели быстрого доступа.
Способ 2. Создание сводной таблицы с использованием инструмента
«Мастер сводных таблиц и диаграмм»
Кликниуть по иконке мастера в панели быстрого допуска.
В диалоговом окне поставить переключатель на нужный пункт списка источников данных:
«в списке или базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется;
«во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных;
«в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников;
«данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.
2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».
Если в качестве источника выбран текущий документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область.
Далее выбирается место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
Если же необходимо консолидировать данные из нескольких источников, надо установить переключатель в соответствующую область и выбрать тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.
При выборе «Создать поля страницы» прежде всего придется указать диапазоны источников данных: выделите первый диапазон, нажмите «Добавить», потом следующий и т.д.
Для удобства диапазонам можно присваивать имена. Для этого выделить один из них в списке и указать число создаваемых для него полей страницы, потом можно задать каждому полю имя (метку).
После этого выделить следующий диапазон и т.д.
После завершения нажать кнопку «Далее», выбрать месторасположение будущей сводной таблицы – на текущем листе или на другом, нажать «Готово» и отчет, собранный из нескольких источников, будет создан.
2. Структура и редактирование сводной таблицы
Сводная таблица состоит из четырех областей.
В зависимости от того, куда будут помещены данные, внешний вид сводной таблицы будет меняться.
В этой области происходят все расчеты исходных данных.
Как правило, в это поле перетаскиваются данные, которые необходимо рассчитать – итоговая площадь территории, средний доход на душу населения и т.д.
Исходные данные перенесенные в это поле, размешаются в левой части сводной таблицы и представляют из себя уникальные значения этого поля.
Как правило область строк имеет хотя бы одно поле, хотя возможно его наличие без полей вовсе.
Сюда обычно помещают данные, которые необходимо сгруппировать и категорировать, например, название округа или продуктов.
Область столбцов содержит заголовки, которые находятся в верхней части сводной таблицы
Область столбцов идеально подходит для создания матрицы данных или указания временного тренда.
В верхней части сводной таблицы находится необязательная область фильтров с одним или более полем.
В зависимости от выбора фильтра меняется внешний вид сводной таблицы.
Если необходимо изолировать, или наоборот, сконцентрироваться на конкретных данных, то необходимо поместить данные в это поле.
Можно добавлять необходимые поля в сводную таблицу простым перетаскиванием диапазонов с названиями в одну из четырех областей сводной таблицы.
Если диалоговое окно Поля сводной таблицы не появилось, следует щелкнуть правой кнопкой мыши на любом месте сводной таблицы и выбрать
Показать список полей.
*Если ставить галочки напротив полей с текстовыми значениями, Еxcel по умолчанию помещает эти значения в область строк, с числовыми значениями – в область значений.
Использование фильтров в сводной таблице
Часто возникает необходимость создать отчет для разных типов данных (к примеру, проанализировать только конкретные округи).
Вместо того, чтобы тратить время на изменения исходных данных, следует воспользоваться областью Фильтры.
Перетаскивая необходимые поля в область Фильтры можно менять внешний вид сводной таблицы.
Со временем исходные данные изменяются, к ним добавляются новые строки и колонки.
Для обновления сводной таблицы надо нажать правой кнопкой мыши на любом месте таблицы и выбрать Обновить.
Обновление сводной таблицы
Бывают ситуации, когда структура исходных данных меняется, к примеру, вам необходимо добавить новые строки в таблицу с данными.
Этот тип изменений повлияет на диапазон источника данных, и об этом необходимо сообщить сводной таблице.
Простое обновление не сработает, так как в данном случае необходимо расширить диапазон источника данных.
Щелкаем левой кнопкой мыши в любом месте сводной таблицы.
Во вкладке Работа со сводными таблицами
-> Анализ –> Источник данных.
В появившемся диалоговом окне
Изменить источник данных сводной таблицы
следует задать изменившийся диапазон данных.
Сводные таблицы. Сводная таблица – это средство, позволяющие по-разному группировать, объединять большие объемы данных и быстро получать итоги для последующего анализа. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные. Сводная таблица создается Мастером сводных таблиц, используемым для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, выберите команду Данные/ Сводная таблица.
Слайд 57 из презентации «Табличный процессор Excel»
Excel
«Знакомство с Excel» - Назначение. Имя файла. Cтолбцы. Запуск электронных таблиц. Отрицательное значение. Табличный процессор. Текстовые данные. Строка. Адреса ячеек. Тип даты. Знакомство с Excel. Основной элемент. Ячейка. Числовой формат. Принцип относительной адресации. Формулы. Функции обработки диапазона. Операции манипулирования.
«Таблица данных в Excel» - График квадратичной функции. Познакомить с понятием «статистика». График регрессионной модели. Графики. Метод наименьших квадратов. Статистические данные. Линейный график. Математическая модель. Параметры функций. Усредненный характер. Данные медицинской статистики. Построим таблицу. Практическое использование.
«Функции Excel» - Структура книги. Через функции реализована большая часть возможностей данной программы. Программа занятий. Освоение технологии создания отчетных и иллюстративных материалов средствами Microsoft Excel: Копирование и перемещение данных. Возврат к меню. Примеры отчетных и иллюстративных материалов. В программу MS Excel встроено более 200 функций.
«Программа Excel» - Назначение. Абсолютная ссылка. Диаграмма. Информатика — серьёзная наука. Пиктограмма позволит вам загрузить программу и начать с ней работу. Маркер заполнения. Смешанные ссылки. Ситуация. Данные в ячейках имеют формат. Некоторые форматы находятся на панели инструментов. План урока. Задача. Все вроде для тебя знакомо, но появилась новая строка.
«Работа в Excel» - Панель быстрого доступа. Вид режима просмотра. Что произошло с прежними сочетаниями клавиш. Преимущества нового формата файлов. Поля страницы. Файл. Старые файлы. Обзор. Что изменилось и почему. Форматирование. Работа с файлами предыдущих версий. Заголовки. Книги предыдущих версий. Непосредственное представление.
«Информатика Excel» - Использование электронных таблиц MS Excel обусловлено: Аннотация. Что можно добавить? Почему именно в электронных таблицах? При проведении компьютерного эксперимента: Задачи курса: Данный курс рассчитан на проведении в течении 12 уроков третьей четверти. Учебный план курса. Типология курса: исследовательская, межпредметная.
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Работа с помощью стеблей немного отличается в зависимости от того, какую платформу вы используете для Excel.
Создание в Excel для Windows
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.
На вкладке Вставка нажмите кнопку Сводная таблица.
В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.
В поле Таблица или диапазон проверьте диапазон ячеек.
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
Настройка сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае вам потребуется либо изменить исходные данные для pivotTable,либо использовать формулу динамического именоваемого диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите на вкладку > рекомендуемая ст.
3. Excel данные анализируются и представлены несколько вариантов, например в этом примере с использованием данных о расходах семьи.
4. Выберите наиболее подбираемую для вас сетовую и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в > Вставить.
Если вы используете Excel для Mac 2011 или более ранней версии, кнопка "Сводная таблица" находится на вкладке Данные в группе Анализ.
3. Excel отобразит диалоговое окно Создание таблицы с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу "таблица_СемейныеРасходы".
4. В разделе Выберите, куда следует поместить отчет таблицы выберите новый или существующий. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
5. Нажмитекнопку ОК, Excel создаст пустую стебли и отобразит список полей.
Список полей сводной таблицы
В области Имя поля в верхней части выберите любое поле, которое вы хотите добавить в свою сетную. По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбец, а числовые — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Список полей сводной таблицы
Суммировать по
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля и выбрав Параметры полей.
Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню "Изменить"), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали и решили, что вам больше не нужна, вы можете просто выбрать весь диапазон, а затем нажать кнопку УДАЛИТЬ. Она не влияет на другие данные, с ее стебли или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Примечание: Мы постоянно работаем над улучшением работы с Excel для Интернета. Новые изменения внося постепенно, поэтому если действия, которые вы в этой статье, возможно, не полностью соответствуют вашему опыту. Все обновления в конечном итоге будут обновлены.
Выберите таблицу или диапазон данных на листе, а затем выберите Вставить >, чтобы открыть область Вставка таблицы.
Вы можете вручную создать собственную или рекомендованную советку. Выполните одно из следующих действий:
Примечание: Рекомендуемые стеблицы доступны только Microsoft 365 подписчикам.
На карточке Создание собственной таблицы выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
В рекомендуемой pivottable выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
При необходимости перед созданием можно изменить источник данных в pivottable.
В области Вставка pivotTable выберите текстовое поле в поле Источник. При изменении источника карточки в области будут недоступны.
Вы можете выбрать данные в сетке или ввести диапазон в текстовое поле.
Нажмите клавишу ВВОД или кнопку, чтобы подтвердить выбор. В области по-новому будут обновлены рекомендуемые с помощью новых источников данных.
В верхней части области Поля pivotTable (Поля) в верхней части выберите любое поле, добавляемое в нее.
По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбцы, а числовые — в область значения.
Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Соответствующие поля в pivotTable:
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля, а затем выберите Параметры значения.
Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, "Сумма по полю имя_поля"), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.
Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.
Читайте также: