Детализация данных Сводной таблицы
Лекция 12
Тема: Решение задач анализа данных и прогнозирования средствами электронных таблиц
Работа с таблицами формата список. Сортировка и фильтрация. Создание промежуточный итогов. Консолидация данных. Сводные таблицы. Таблица подстановки. Анализ «что, если » (Подбор параметров. Поиск решения).
Обработка и анализ данных
Набор строк в Excel, содержащий взаимосвязанные данные и определенную структуру, называется списком. Такой диапазон можно сортировать, группировать, фильтровать, производить в нем поиск и выполнять вычисления.
Рис. 16.1. Пример списка данных
Данные на рис. 16.1. структурированы и удовлетворяют следующим критериям:
· Ячейки каждого столбца (т. е. поля) содержат однотипную информацию, имеют одну размерность, поэтому, например, дата и время представлены в одном формате (ДД.ММ.ГГ и ЧЧ.ММ).
· Каждый столбец имеет уникальный заголовок, расположенный прямо над данными.
· Все ячейки в каждой строке образуют одну запись и занимают не более одной строки.
· Абсолютно незаполненные строки не могут считаться данными – создав запись, следует заполнить хотя бы одно поле.
Сортировка данных
Для осуществления сортировки необходимо сначала выделить диапазон ячеек, содержащих данные для сортировки, либо ячейку столбца, по которому будет произведена сортировка. После выделения можно применить команды (для сортировки по возрастанию значений) и (для сортировки по убыванию значений). Команду настраиваемой сортировки можно вызвать через Главная – Редактирование – Сортировка и фильтр, либо через Данные –Сортировка и фильтр – Сортировка. В появившемся окне Сортировка (рис. 16.2) нужно указать столбец, порядок и особенности сортировки списка данных.
|
|
|
Рис. 16.2. Диалоговое окно Сортировка
Для добавления еще одного критерия сортировки нужно использовать кнопку Добавить уровень. Чтобы данные первой строки списка не участвовали в сортировке, нужно поставить флажок «Мои данные содержат заголовки».
Чтобы задать сортировку не строк, а столбцов диапазона, нужно, нажав кнопку Параметры, указать «сортировать столбцы диапазона».
Чтобы отсортировать данные только в одном столбце списка (не изменяя порядок в других), нужно выделить требуемый столбец, вызвать команду сортировки и в появившемся окне «Обнаружены данные вне указанного диапазона» выбрать пункт «сортировать в пределах указанного выделения».
Замечание. Сортировка может быть произведена по настраиваемым пользовательским спискам, т. е. по определенному пользователем порядку сортировки. Для создания списка нужно внести элементы в ячейки, выделить их и занести в список по команде кнопка Office – Параметры Excel – Основные параметры работы с Excel – Создавать списки для сортировки и заполнения. После того, как список создан, данные могут быть отсортированы по возрастанию/убыванию элементов данного списка. Для этого в окне сортировке в графе Порядок нужно указать, что сортировка будет произведена по настраиваемому списку.
|
|
|
Структурирование данных
На листах Excel из данных можно создать в структуру, которая позволит группировать информацию по разделам, сворачивая/разворачивая ее при надобности. Структура может быть как горизонтальной, так и вертикальной и иметь до 8 уровней вложенности. Структуру можно создать вручную, либо автоматически.
При ручном структурировании сначала надо определить нужные элементы – выделить диапазоны ячеек, которые должны быть структурированы, затем применить команду Данные – Структура – Группировать – Группировать.
После выполнения команды, выделенные строки/столбцы становятся разделом.
Нажав на знак +, можно увидеть детали раздела, нажав на знак –, можно скрыть лишние детали.
Замечание. Если при сворачивании раздела определенные строки/столбцы должны быть видимыми, их не нужно выделять для группировки.
|
|
|
Автоматическое структурирование используется, когда на листе расположены строки или столбцы итоговых данных или промежуточных итогов. В диапазоне, предназначенном для структурирования, необходимо выделить хотя бы одну ячейку, а затем выполнить команду Данные – Структура – Группировать – Создание структуры. В результате будет создана структура, использующая итоговые данные как уровни разделов.
Удалить структурирование можно, используя команды Данные –Структура – Разгруппировать – Удаление структуры.
Фильтрация
В Excel 2007 существует возможность отфильтровать список, чтобы вывести только необходимые в данный момент строки. При этом строки, не удовлетворяющие заданным условиям, просто не будут отображаться на экране, а не будут удаляться из файла. Поэтому, отменив фильтрацию данных, можно видеть полностью весь список. Фильтрация может быть выполнена с помощью автофильтра и расширенного фильтра.
Замечание. При копировании данных из отфильтрованной таблицы будут скопированы только те данные, которые отображаются в данный момент в таблице. Также, если в таблице есть строки с итоговыми данными, они автоматически будут пересчитываться при каждой фильтрации.
|
|
|
Чтобы применить автофильтр, нужно выделить хотя бы одну ячейку списка данных и применить команду Главная – Редактирование – Сортировка и фильтр – Фильтр или Данные – Сортировка и фильтр – Фильтр. После этого в правом углу ячеек заголовков списка появятся стрелки, нажав на которые можно получить доступ к параметрам фильтрации (рис. 16.3).
Рис. 16.3. Выбор условий фильтрации
Можно наложить фильтр по цвету ячеек, по выбору определенных данных, а также задать различные параметры для фильтрации данных разных типов: числовых, текстовых, дат. Также в автофильтре можно задать два условия отбора данных в одном или нескольких столбцах. Для этого нужно активировать команду Настраиваемый фильтр в контекстном меню для конкретного типа фильтра. Удалить автофильтр можно, повторно нажав на копку вызова фильтра.
Расширенный фильтр предназначен для фильтрации списков тогда, когда возможностей автофильтра недостаточно. Хотя расширенный фильтр и несколько сложнее автофильтра, он имеет ряд преимуществ, основные из них:
- можно сохранять критерий отбора данных для дальнейшего использования;
- для одного столбца можно задать более двух критериев отбора;
- между столбцами можно задать несколько критериев сравнения;
- можно показывать в отфильтрованных записях не все столбцы, а только указанные;
- в критерии можно включать формулы.
Чтобы отфильтровать список с помощью расширенного фильтра, необходимо произвести некоторые подготовительные действия:
1) проверить, чтобы столбцы списка имели заголовки;
2) подготовить таблицу критериев отбора. Заголовки столбцов, для которых будут задаваться условия должны в точности совпадать с заголовками списка, поэтому имеет смысл их просто скопировать из списка. В следующих строках необходимо записать условия фильтрации. Эти строки будут использованы в качестве диапазона условий отбора.
Для объединения критериев с помощью условного оператора И следует указать критерии в одной и той же строке, а для объединения критериев с помощью условного оператора ИЛИ следует ввести критерии в разных строках.
Чтобы применить расширенный фильтр, нужно выделить диапазон фильтрации и вызвать команду Данные – Сортировка и фильтр –Дополнительно.
Рис. 16.4. Окно Расширенный фильтр
В диалоговом окне Расширенный фильтр (рис. 16.4) нужно указать место размещения результатов фильтрации (на этом же месте или скопировать в другое), исходный диапазон фильтруемых данных, критерии отбора (диапазон условий), отображение в результате только уникальных записей и нажать ОК.
После применения данной команды на листе в указанном месте будут отображены отфильтрованные данные.
Чтобы убрать расширенный фильтр, достаточно нажать кнопку Очистить панели Сортировка и фильтр.
Разделение данных на несколько столбцов
Excel предоставляет возможность разбивать данные, находящиеся в одном столбце, на несколько. Эта возможность может также использоваться при импорте данных из других приложений, если данные не разделены на нужное количество столбцов.
С помощью команды Данные – Работа с данными – Текст по столбцам вызывается мастер, позволяющий указать параметры разбивки. Можно выбрать разделитель для данных, имеющих одинаковые знаки-разделители, либо указать параметр «фиксированная ширина» (если в столбце данные имеют одинаковую длину) и указать точное местоположение разделителя.
После выполнения команды разделенные данные копируются в столбцы, находящиеся справа от исходного столбца, заменяя старые данные. Поэтому предварительно нужно добавить пустые столбцы справа, либо при выполнении разбиения указать адрес ячейки, куда будет помещен результат.
Консолидация данных
Если в разных листах книгах или даже разных книгах имеются однотипные значения, которые нужно разместить в одном месте или провести с ними однотипные операции, это можно сделать при помощи функции Консолидация. При ее использовании данные из указанных источников собираются в одном месте и любые общие значения будут объединяться, как указано. Это позволяет более наглядно анализировать итоговые данные.
Чтобы выполнить консолидацию, сначала нужно выделить первую ячейку места, в котором будут расположены консолидированные данные, затем выбрать команду Данные –Работа с данными – Консолидация .
Рис. 16.5. Диалоговое окно Консолидация
В диалоговом окне (рис. 16.5) необходимо выбрать диапазоны ячеек (поля ссылка, список диапазонов) и тип предстоящей консолидации (поле Функция), а также указать использование в качестве имен некоторых значений диапазона данных и установить связь с исходными данными (чтобы при изменении исходных данных производилось обновление консолидированных). После задания параметров итоговые данные будут размещены в указанных ячейках.
Создание промежуточных и общих итогов
При анализе данных в больших таблицах часто возникает необходимость вычислять промежуточные и полные итоги.
Промежуточные итоги – это удобный способ обобщения и анализа данных на рабочем листе. При подведении промежуточных итогов таблица разбивается на несколько групп строк с одинаковыми значениями одного поля и по каждой группе подводятся итоги, а затем – общий итог по всей таблице.
В качестве итога могут вычисляться максимальное или минимальное значения в группе, сумма, среднее, количество элементов, стандартное отклонение и т. п.
Подведение промежуточных итогов позволяет обойтись практически без ручного ввода формул. Excel автоматически создает формулу, добавляет строку (или строки) для записи промежуточных итогов и подставляет адреса ячеек данных. Результирующая таблица является наглядной и удобной для последующей обработки. После подведения промежуточных итогов таблица представляется структурой, то есть она разбивается на группы. Разбиение таблицы на группы позволяет управлять степенью детализации отображаемых данных. Excel может показывать только итог для каждой группы или полностью содержимое группы.
Перед подведением промежуточных итогов имеет смысл произвести сортировку по тем столбцам, по которым подводятся итоги, чтобы все записи с одинаковыми полями этих столбцов попали в одну группу.
Чтобы добавить итоги для списка данных нужно:
1. Выделить исходный диапазон ячеек.
2. Выбрать команду Данные – Структура – Промежуточные итоги для вызова соответствующего диалогового окна (рис. 16.5).
Рис. 16.6. Диалоговое окно Промежуточные итоги
3. В списке При каждом изменении в выберите поле, по значениям которого вы собираетесь группировать строки (каждый раз, когда такое изменение найдено, вставляется промежуточный итог).
4. В списке Операция выберите функцию, при помощи которой будут подводиться итоги.
5. В группе флажков Добавить итоги по укажите, по каким полям должны вычисляться итоги. Отметьте только те поля, которые вы хотите просуммировать.
6. Нажмите кнопку ОК. Excel вставит врабочий лист строки с промежуточными итогами. Общий итог по всей таблице появится в ее нижней части.
Размещение строк промежуточных итогов регулируется с помощью флажков в нижней части диалогового окна. По умолчанию все старые промежуточные итоги должны заменяться новыми (флажок Заменить текущие итоги), также можно разделить группы итогов на страницы (флажок Конец страницы между группами) и добавить общие итоги под таблицей данных (флажок Итоги под данными).
Чтобы удалить итоги с листа, нужно вызвать окно добавления промежуточных итогов и нажать кнопку Убрать все.
Использование сводных таблиц для анализа данных
Сводные таблицы обеспечивают очень удобный интерфейс к хранилищам данных различной сложности и разного объема. Сводная таблица – это динамическая таблица специального вида, построенная на базе одной или нескольких исходных таблиц и содержащая сводную информацию по этим таблицам. Базами данных для сводных таблиц могут быть списки, таблицы, расположенные на рабочих листах Excel, либо внешние источники данных (например, базы данных Access).
При создании сводной таблицы пользователь распределяет информацию, указывая, какие элементы и в каких полях сводной таблицы будут содержаться.
Поле – это некоторая совокупность данных, собранных по одному признаку.
Элемент – отдельное значение, содержащееся в поле. Сводная таблица является многомерной и всегда связана с источником данных. Сама она предназначена только для чтения, а изменения нужно вносить в исходные таблицы. При этом можно изменять форматирование сводной таблицы, выбирать различные параметры вычислений.
Для работы в Excel со сводными таблицами существует команда Вставка –Таблицы – Сводная таблица. После ее активизации в появившемся окне Создание сводной таблицы нужно указать исходные данные и размещение итогов сводной таблицы. После этого при помощи мастера Список полей сводной таблицы необходимо заполнить макет таблицы. В результате будет получена сводная таблица, после вставки которой на ленте меню появляется контекстный инструмент Работа со сводными таблицами, имеющий вкладки Параметры – для изменения структуры сводной таблицы, и Конструктор –для ее форматирования.
Использование макросов
Макрос представляет собой последовательность действий, записанную в виде программы на языке Visual Basic for Applications (VBA). При помощи макросов пользователь может зафиксировать в памяти компьютера часто повторяющиеся последовательности действий, что позволит избежать рутинных операций и ошибок, а также сэкономить время. Макрос может быть записан как с помощью команд языка программирования, так и автоматически программой записи макроса.
Чтобы получить доступ к командам записи макросов, нужно отобразить вкладку Разработчик на ленте главного меню. Для этого нужно выбрать кнопку Office – Параметры Excel – Показывать вкладку Разработчик на ленте. На панели Код (рис. 16.7) доступны основные инструменты для работы с макросами.
Рис. 16.7. Панель Код
Для записи макроса на Visual Basic используйте кнопку запуска редактора – Visual Basic. Для просмотра списка имеющихся и запуска нужного макроса используйте кнопку Макросы. Кнопка Запись макроса позволяет записать последовательность действий в макрос. Кнопка Относительные ссылки включает при записи макроса режим относительных ссылок. Кнопка Безопасность макросов позволяет устанавливать различные уровни безопасности для запуска макросов из разных источников.
Сводные таблицы
Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах. Рассмотрим процесс создания несложных Сводных таблиц.
Сводные таблицы (Вставка/ Таблицы/ Сводная таблица) могут пригодиться, если одновременно выполняются следующие условия:
· имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
· необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
· этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, расширенного фильтра;
· исходная таблица удовлетворяет определенным требованиям (см. ниже).
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
· каждый столбец должен иметь заголовок;
· в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» - названия компаний только в текстовом формате);
· в таблице должны отсутствовать незаполненные строки и столбцы;
· в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно;
· избегайте таблиц с «неправильной» структурой (рисунок 16.8).
Рис. 16.8
Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, …), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке 16.8 (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи, а не указывать для каждого региона суммарные продажи).
Несколько облегчит процесс построения Сводной таблицы, тот факт, если исходная таблица будет преобразована в формат Excel 2007 (Вставка/ Таблицы/ Таблица). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица. Все поля окна будут автоматически заполнены, нажмите ОК.
Рис. 16.9.
Создание таблицы в формате Excel 2007 добавляет новые возможности:
· при добавлении в таблицу новых значений новые строки автоматически добавляются к таблице;
· при создании таблицы к ней применяется форматирование, к заголовкам – фильтр, появляется возможность автоматически создать строку итогов, сортировать данные и пр.;
· таблице автоматически присваивается Имя.
В качестве исходной будем использовать таблицу в формате Excel 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте (рис. 16.10).
Рис. 16.10
В таблице имеются столбцы:
· Товар – наименование партии товара, например, «Апельсины»;
· Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
· Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
· Дата поставки – Дата поставки Товара Поставщиком;
· Регион продажи – Регион, в котором была реализована партия Товара;
· Продажи – Стоимость, по которой удалось реализовать партию Товара;
· Сбыт – срок фактической реализации Товара в Регионе (в днях);
· Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен) откорректируем Имя таблицы на «Исходная_таблица».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
Имея исходную таблицу в формате Excel 2007 , для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица (рис. 16.11)
Рис. 16.11
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе (рис. 16.12).
Рис. 16.12
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы) (рис. 16.13).
Рис. 16.13
Структура Сводной таблицы в общем виде может быть представлена так (рис 16.14):
Рис. 16.14
Заполним сначала раздел Названия строк. Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец – синонимы (рис. 16.15).
Рис. 16.15
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я.
Теперь поставим галочку в Списке полей у поля Продажи (рис.16.16).
Рис. 16. 16
Рис. 16. 16
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул
Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицы строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины» (рис 16.17).
Рис. 16.17
Обновление Сводной таблицы
Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице. Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить. Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы, вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить.
Удаление Сводной таблицы
Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.
Изменение функции итогов
При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы.
Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество (рис. 16.18).
Рис. 16.18
Изменение порядка сортировки
Теперь немного модифицируем наш Сводный отчет. Сначала изменим порядок сортировки названий Товаров: отсортируем их в обратном порядке от Я до А. Для этого через выпадающий список у заголовка столбца, содержащего наименования Товаров, войдем в меню и выберем Сортировка от Я до А (рис. 16.19).
Рис. 16.19
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками) (Рис. 16.20).
Рис. 16.20
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца (рис 16.21).
Рис. 16. 21
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Дата добавления: 2021-01-20; просмотров: 581; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!