Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
Пользовательское имя [Custom Name] можно ввести имя с клавиатуры, отличное от уже существующих имен полей. Для форматирования значений поля выбрать Числовой формат [Number Format]. На вкладке Дополнительные вычисления [Show values as] выбрать способ обработки значений в области данных отчета сводной таблицы, используя значения других областей данных. Доступны следующие функции: Дополнительные вычисления Show values as Результат Без вычислений Normal Выключение настраиваемого вычисления % от общей суммы % of Grand Total Отображение значений в процентах от общей суммы значений или элементов данных в отчете % от суммы по столбцу % of Column Total Отображение всех значений в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду % от суммы по строке % of Row Total Отображение значений в каждой строке или категории в процентах от итогового значения по этой строке или категории Доля… Percent Of Отображение доли в процентах от значения элемента в поле % от суммы по родительской сроке % of Parent Row Total Отображение значения в виде процента по отношению к промежуточному итогу по строке % от суммы по родительскому столбцу % of Parent Column Total Отображение значения в виде процента по отношению к промежуточному итогу по столбцу % от родительской суммы… % of Parent Total Отображение значения в виде процента по отношению к промежуточному итогу по строке Отличие… The Difference From Отображение значения в виде разницы по отношению к значению элемента в поле Приведенное отличие… Percent Difference From Отображение значений в виде разницы в процентах по отношению к значению элемента в поле С нарастающим итогом в поле… Running Total In Отображение значений в виде нарастающего итога для последовательных элементов в поле % от суммы с нарастающим итогом в поле % Running Total In Отображение значений в виде нарастающего процента по нарастающему итогу для последовательных элементов в поле Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 66 Дополнительные вычисления Show values as Результат Сортировка от минимального к максимальному Sort Smallest to Largest Определение порядкового номера значения элемента (ранг) по отношению к значениям элементов поля от минимального (1-я позиция) к максимальному значению Сортировка от максимального к минимальному Sort Largest to Smallest Определение порядкового номера значения элемента (ранг) по отношению к значениям элементов поля от максимального (1-я позиция) к минимальному значению Индекс Index Вычисление значений по формуле: Значение_в_ячейке ∙ Общий_итог Итог_строки ∙ Итог_столбца 2-й способ: щелкнуть правой кнопкой мыши по значениям нужного поля в области Σ Значения [Σ Values] и выбрать: Итоги по [Summarize Values By], далее выбрать Сумма [Sum], Количество [Count], Среднее [Average]. Максимум [Max], Минимум [Min], Произведение [Product of Values] или Дополнительно [More], чтобы выбрать другую функцию из предлагаемого списка, настроить формат поля и изменить имя поля. Дополнительные вычисления [Show values as], далее выбрать нужный вариант расчета. Повторное использование исходного поля в отчете сводной таблицы По одному и тому же исходному полю можно делать несколько вычислений. Для этого необходимо исходного поле из списка полей сводной таблицы добавить в область значений Σ Значения [Σ Values] несколько раз, затем настроить параметры поля. Это позволит одновременно видеть как само вычисление, так и вычисление, выполненное на его основе. Например, рассчитать суммы продаж Клиентам и вычислить Рейтинг суммы продаж каждого клиента от максимального (рейтинг 1) к минимальному значению (рейтинг 8). Добавление вычисляемых полей в сводную таблицу В отчет сводной таблицы нельзя добавить новые строки или столбцы для вставки формул или дополнительных расчетов, но можно добавить вычисляемое поле, с помощью которых можно вводить формулы для расчета новых данных на основании существующих. Вставка вычисляемого поля непосредственно в сводную таблицу – это наилучшее решение. Не потребуется управлять формулами и беспокоиться о расширяемости таблицы при росте или редактировании источника данных, причем сводная таблица останется достаточно гибкой при изменении определений полей. Еще одно огромное преимущество этого метода заключается в том, что можно изменять структуру сводной таблицы и даже просчитывать другие поля данных для вычисляемых полей, не беспокоясь об ошибках в формулах или нарушении ссылок на ячейки. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 67 Таким образом, при добавлении собственных вычисляемых полей в сводную таблицу очевидны преимущества: Исключение потенциальных ошибок в формулах и ссылках на ячейки. Возможность добавления и удаления данных из сводной таблицы без изменения исходных вычислений. Возможность автоматического перерасчета данных при изменении или обновлении сводной таблицы. Обеспечение гибкости изменения вычислений при изменении определений элементов. Возможность эффективного управления вычислениями. Создание вычисляемого поля: 1. Выделить ячейку сводной таблицы; 2. На вкладке Параметры [Options], в группе Вычисления [Calculations], раскрыть список Поля, элементы и наборы [Fields, Items, &Sort] и выбрать Вычисляемое поле [Calculated Field]. 3. В окне Вставка вычисляемого поля [Insert Calculated Field]: В поле Имя [Name] ввести имя нового поля. В поле Формула [Formula] составить формулу, начиная со знака = и выбирая доступные поля в списке Поля [Fields] (для вставки можно использовать двойной щелчок по полю или выделить поле, затем нажать кнопку Добавить поле [Insert Field]). Нажать Добавить [Add]. 4. ОК. Настройка внешнего вида вычисляемого поля, как и любого другого поля, происходит в окне Параметры полей значений [Value Field Settings]. Редактирование вычисляемого поля 1. На вкладке Параметры [Options], в группе Вычисления [Calculations], раскрыть список Поля, элементы и наборы [Fields, Items, &Sort], выбрать Вычисляемое поле [Calculated Field]. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 68 2. В окне Вставка вычисляемого поля [Insert Calculated Field] выбрать в списке Имя нужное имя вычисляемого поля, затем: При редактировании изменить Имя [Name], Формулу [Formula] и нажать Изменить [Modify]. При удалении нажать Удалить [Delete]. Форматирование сводной таблицы Для оформления сводной таблицы, в программе предусмотрены встроенные стили, а так же гибкий подход для настройки нужного форматирования. 1. Щелкнуть в ячейку отчета. 2. На вкладке Конструктор [Design] выбрать нужные параметры: В группе Стили сводной таблицы [PivotTable Styles] выбрать нужное форматирование. В группе Параметры стилей сводной таблицы [PivotTable Styles Options] можно настроить отличие в оформлении, выбирая Заголовки строк [Row Headers], Заголовки столбцов [column Headers], Чередующиеся строки [Banded Rows], Чередующиеся столбцы [Banded Columns]. В группе Макет [Layout] можно выбрать: Пустые строки [Blank Rows]– вставить или удалить пустую строку после каждого элемента. Макет отчета [Report Layout]– в сжатом виде [Compact Form], в форме структуры [Outline Form] или в табличном виде [Tabular Form]. Если макет выбран в табличном виде или в форме структуры, то можно повторять все подписи элементов Общие итоги [Grand Totals]– выбрать расположение итогов: в строках и/или столбцах. Промежуточные итоги [Subtotals] – наличие и место отображения промежуточных итогов Обновление сводных таблиц и сводных диаграмм Обновление сводных таблиц не происходит автоматически. Если данные в исходной таблице были изменены, то требуется принудительно обновить отчет сводной таблицы. Для обновления данных отчета, щелкнуть правой кнопкой мыши по ячейке сводной таблицы и выбрать Обновить [Refresh Data] или на вкладке Параметры [Options] в группе Данные [Data] нажать кнопку Обновить [Refresh]. Для настройки автоматического обновления при открытии файла: Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 69 1. Щелкнуть правой кнопкой мыши в область отчета сводной таблицы и выбрать Параметры сводной таблицы [PivotTable Options] или на вкладке Параметры в группе Сводная таблица [PivotTable] нажать Параметры [Options]. 2. В диалоговом окне Параметры сводной таблицы [PivotTable Options] перейти на вкладку Данные [Data], выбрать Обновить при открытии файла [Refresh data when opening the file], ОК. Чтобы посмотреть и при необходимости изменить диапазон исходных данных, надо на вкладке Параметры [Option], в группе Данные [Data], выбрать Источник данных [Data Source]. Если исходные данные постоянно изменяются, то для определения автоматического размера исходной таблицы, необходимо исходную таблицу преобразовать в список: 1. Выделить любую ячейку таблицы данных. 2. На вкладке Вставка [Insert], выбрать Таблица [Table], ОК. При выполнении команды Обновить все добавляемые строки/столбцы будут автоматически включены в источник. Группировка полей в сводных таблицах В сводных таблицах можно группировать поля, которые расположены в области Названия строк [Row Labels] или Названия столбцов [Row Labels]. Группировка поля типа Дата/Время 1. Щелкнуть правой кнопкой мыши по любой ячейке нужного поля и выбрать Группировать [Group]. 2. Выбрать диапазон группировки данных: начиная с [Starting at] и по [Ending at], выделить требуемый шаг группировки в поле с шагом [By]: Дни [Days], Месяцы [Months], Кварталы [Quarters], Годы [Years]. Результат группировки даты по годам: Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 70 Группировка числового поля 1. Щелкнуть правой кнопкой мыши по любой ячейке нужного поля и выбрать Группировать [Group]. 2. Выбрать диапазон группировки данных: начиная с [Starting at] и по [Ending at], ввести требуемый шаг группировки в поле с шагом [By] Результат группировки по числовому полю с шагом 5: Группировка текстового поля 1. Выделить в области Названия строк [Row Labels] или Названия столбцов [Row Labels] нужные элементы: Выделить нужные ячейки поля с нажатой клавишей Ctrl Установить фильтр для отбора нужных записей, затем выделить результат отбора. 2. Щелкнуть правой кнопкой мыши по любой выделенной ячейке и выбрать Группировать [Group]. 3. Ввести имя группы с клавиатуры вместо имени Группа 1 [Group1]. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 71 Чтобы изменить имя полученного поля, надо щелкнуть правой кнопкой мыши по его ячейке и выбрать Параметры поля [Field Settings], затем ввести с клавиатуры Пользовательское имя [Custom name] Для быстрой работы с группами данных, можно по ячейке в области Названия строк [Row Labels] или Названия столбцов [Row Labels] щелкнуть правой кнопкой мыши и выбрать Развернуть/свернуть [Exspande/Collapse], а затем нужный вариант: Развернуть [Expand] – текущий элемент. Свернуть [Collapse] – текущий элемент. Развернуть все поле [Expand Entire Field] – развернуть все до уровня исходного поля. Свернуть все поле [Collapse Entire Field] – свернуть все до уровня поля-группировки. Свернуть до «Имя поля-группировки» [Collapse to «Имя поля-группировки»] Развернуть до «Имя исходного поля» [Expand to «Имя исходного поля»] Сводные диаграммы Существует два способа построения сводных диаграмм: построение на основе диапазона исходных данных (автоматически строится макет сводной таблицы вместе со сводной диаграммой) или сводная таблица строится на основе построенной сводной таблицы. Второй вариант используется значительно чаще. Построение сводной диаграммы на основе построенной сводной таблицы: 1. Выделить ячейку сводной таблицы. 2. На вкладе Параметры [Options], в группе Сервис [Options], выбрать Сводная диаграмма [PivotChart]. 3. Выбрать тип диаграммы, нажать ОК. Для фильтрации данных сводной таблицы можно использовать отчет сводной таблицы или удобно выполнять отбор данных на сводной диаграмме, используя фильтры: Фильтр отчета [Report Filter] Поля осей (категории) [Axis Fields (Categories)] – соответствуют области Названия строк [Row Labels] в макете сводной таблицы – соответствуют области Названия столбцов [Column Labels] в макете сводной таблицы. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 72 Поля легенды (ряды) [Legend Fields (Series)] Изменения, сделанные в отчете сводной таблицы, автоматически отображаются на сводной диаграмме и наоборот. Работа со сводными таблицами аналогична работе с обычными диаграммами. При выделении сводной диаграммы появляются вкладки для работы со сводными диаграммами. ПРАКТИКУМ: Создание Открыть файл 03 Сводные таблицы из текущей папки. По данным таблицы с листа Продажи Наборы построить сводную таблицу, позволяющую проанализировать суммы продаж каждого наименования по клиентам в зависимости от выбранного города. Создать отчет на новом листе. Расположить данные по клиентам в названия строк, наименования – в названия столбцов, суммы – в Σ Значения, а город – в фильтр отчета. Переименовать лист в Отчет Наборы. Изменение макета На листе Отчет Наборы, сделать следующие изменения макета: Переместить поле Наименование из области названия столбцов в названия строк. Изменить расположение полей в области названия строк, чтобы просматривать данные по клиентам в зависимости от наименования. Переместить поле Город из области фильтр отчета в область названия столбцов. Включить флажок Отложить обновление макета и сделать преобразование макета отчета: Удалить поле Наименование из отчета. Переместить поле Город из области названия столбцов в названия строк. Добавить поле Количество в область Σ Значения. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 73 Выполнить обновление макета. Снять флажок Отложить обновление макета и добавить в отчет поле Дата, разместив его в фильтре отчета. Фильтры и срезы На листе Отчет Наборы предоставить данные для анализа в период с 23 по 25 марта 2006 г только клиентами, в названии которых есть слово компания. Очистить условия фильтрации. Удалить поле Город из отчета сводной таблицы. Вставить срезы по полям Город и Наименование. Применить к срезам разные стили оформления. Отобразить данные только по Москве, затем только по Казани. Очистить фильтр. Отобразить данные продаж в городе Санкт-Петербурге наименований набор "Доброта", набор "Мечта" и набор "Радость". Очистить все фильтры. Удалить срез Город и Наименование. Настройка полей На листе Отчет Наборы настроить параметры полей: Для Суммы – операция Сумма, имя Сумма RUB, числовой формат денежный. Для Количества – операция Сумма, имя Кол-во. Удалить из отчета поле Клиент, расположив вместо него поле Наименование. Добавить в отчет данные для анализа общих продаж по всем наименованиям: Вычисление доли суммы продаж каждого наименования от общей суммы продажи. Назвать поле Доля продаж. Рейтинга продаж каждого наименования. Максимальная сумма продажи должна соответствовать позиции 1. Назвать поле Рейтинг. Используя срез по полю Клиент, просмотреть распределение продаж наборов по конкретным клиентам. Очистить условия фильтрации. Результат вычислений можно сравнить с образцом: Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 74 Вычисляемые поля На листе Отчет Наборы добавить в отчет вычисляемые поля на основе существующего поля Сумма, руб: СуммаEUR – при курсе 1€=40 р. СуммаUSD – при курсе 1$=30 р. Настроить параметры вычисляемых полей с соответствующими денежными знаками. Изменить формулу поля |