Гипертекстовое учебное пособие «Сводный анализ в MS Excel » Буквы.Ру Научно-популярный портал<script async custom-element="amp-auto-ads" src="https://cdn.ampproject.org/v0/amp-auto-ads-0.1.js"> </script>

Гипертекстовое учебное пособие «Сводный анализ в MS Excel

<

110313 2214 1 Гипертекстовое учебное пособие «Сводный анализ в MS Excel1. КОНСОЛИДАЦИЯ ДАННЫХ

1.1 Консолидация по физическому расположению

Консолидация — агрегирование (объединение) данных, представленных в исходных областях – источниках [1, c. 236].

Результат консолидации находится в области-назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей-источников (см. рис. 1.1) [1, c. 239].

110313 2214 2 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

Рис. 1.1 Представление о консолидации данных

 

Существуют следующие варианты консолидации данных [1, c. 242]:

  • с помощью формул, где используются ссылки;
  • по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
  • по категориям (по заголовкам) для различающихся по своей структуре области данных;
  • с помощью сводной таблицы;
  • при помощи специальной вставки;
  • консолидация внешних данных.

    При консолидации данных с помощью формул используемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей-источника и области-назначения:

  • все области на одном листе — в ссылках указывается адрес блока ячеек, например D1.C8;
  • области на разных листах — в ссылках указывается название листа, диапазон, например
  • лист1!D1 :лист2!С8;
  • области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.

    При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные, Консолидация, выбирается вариант, и задаются условия консолидации.

    Например, на отдельных листах рабочей книги по каждой учебной группе хранятся сведения о среднем балле по фиксированному перечню предметов в разрезе видов занятий (рис. 1.2). Серым цветом показана консолидируемая область источников.

    110313 2214 3 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1.2 Пример областей-источников при консолидации данных по расположению

     

    1.2 Консолидация по заголовкам строк и столбцов

     

    При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов, (имена включаются в выделенные области-источники). Выполняется команда Данные, Консолидация, выбирается вариант, и задаются условия консолидации [1, c. 257].

    Например, ниже на рабочих листах представлена информация областей-источников в виде структуры на рис. 1.3. Число строк — переменное, состав предметов и виды занятий повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. Серым цветом показана консолидируемая область источников

    110313 2214 4 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1.3 Пример областей-источников при консолидации данных по категориям

     

    Условия консолидации задаются в диалоговом окне «Консолидация» (рис.1.4). В окне «Функция» выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка <Добавить>.

    При консолидации по категориям область имен входит в выделение, устанавливаются флажки подписи верхней строки или значения левого столбца (рис. 1.4). Excel автоматически переносит эти имена в область назначения.

    110313 2214 5 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1.4. Диалоговое окно «Консолидация» для задания условий консолидации

     

    Ссылка может иметь любую из следующих форм (табл. 1) [1, c. 262]:

    Таблица 1. Форм ссылок на данные при консолидации

     

    Вид ссылки 

    Расположение объектов (источник и назначение) 

    ссылки на ячейки 

    источники и назначение на одном листе 

    ссылки на лист и ячейки

    источники и назначение на разных листах 

    ссылки на книгу, лист и ячейки 

    источники и назначение в разных книгах 

    полный путь и все ссылки 

    источники и назначение в различных местах диска 

    имя поименованной области 

    область-источник поименована 

     

    1.3 Консолидация при помощи команды Правка — Специальная вставка

     

    Этот метод применим только в том случае, если все консолидируемые рабочие листы открыты. Недостатком этого метода является то, что консолидация получается нединамичной, т.е. если любые консолидированные данные изменятся, результат будет неправильным. Преимуществом данного метода является то, что при вставке данных из буфера обмена с помощью команды Специальная вставка можно выполнять математические операции [2, c. 350].

    Команда Правка | Специальная вставка — универсальный вариант команды Правка | Вставить. Чтобы эта команда стала доступной, необходимо скопировать содержимое ячейки или диапазона в буфер обмена (команда Правка | Копировать). Затем активизировать ячейку, в которую будет размещаться информация, и выбрать команду Правка | Специальная вставка. В результате на экране появится диалоговое окно Специальная вставка (рис. 1.5).

    110313 2214 6 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1.5 Диалоговое окно Специальная вставка

     

    В этом диалоговом окне содержатся следующие переключатели:

    Группа переключателей Вставить:

    Все — выбор этой опции эквивалентен использованию команды Правка | Вставить. При этом копируется содержимое ячейки, форматы и параметры функции проверки допустимости данных.

    Формулы — выбор этой опции позволяет вставить только формулы в том виде, в котором они вводились в строку формул.

    Значения — выбор данной опции позволяет скопировать результаты расчетов по формулам, причем диапазоном назначения может быть как новый диапазон, так и исходный. В последнем случае первоначальные формулы будут заменены полученными значениями.

    Форматы — если к ячейке или диапазону применены какие-либо атрибуты форматирования, то можно их скопировать, а затем вставить в другую ячейку или диапазон, используя данную опцию. Если нужно скопировать только примечания к ячейке или диапазону, то можно воспользоваться данной опцией. Эта опция не копирует содержимого ячейки или ее атрибутов форматирования.

    Условия на значения — если для конкретной ячейки был создан критерий допустимости данных (с помощью команды Данные | Проверка), то этот критерий можно скопировать в другую ячейку или диапазон, воспользовавшись данной опцией.

    Без рамки — часто возникает необходимость скопировать ячейку без рамки. Например, если есть таблица с рамкой, то при копировании граничной ячейки будет скопирована также и рамка. Чтобы избежать копирования рамки можно выбрать эту опцию.

    Ширины столбцов — можно скопировать информацию о ширине столбца из одного столбца в другой.

    Переключатели из группы Операция позволяют выполнять арифметические операции. Например, можно скопировать один диапазон в другой и выбрать операцию умножить. В результате Excel перемножит соответствующие значения исходного диапазона и диапазона назначения и полученными результатами заменит прежние значения диапазона назначения.

    На рисунке 1.6 показан пример выполнения математической операции с помощью диалогового окна Специальная вставка. Задача состоит в том, чтобы увеличить на 10% значения, находящиеся в диапазоне B4:B10 (не пользуясь формулами).

    110313 2214 7 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1.6 Пример использования диалогового окна Специальная вставка

    а) исходные данные; б) результат выполнения операции умножить

     

    Для реализации данного примера надо выполнить следующие действия:

    — скопировать содержимое ячейки В1 в буфер обмена.

    — выбрать диапазон В4:В10.

    — выполнить команду Правка | Специальная вставка.

    — выбрать опцию умножить и активизировать опцию значения, иначе к диапазону назначения будут применены атрибуты форматирования ячейки В1.

    В результате выполнения этой операции все значения в ячейках выбранного диапазона увеличились на 10%.

    Общий подход к применению специальной вставки при консолидации данных:

    — скопировать данные из первого диапазона исходного рабочего листа.

    — активизировать зависимую рабочую книгу и выбрать ячейку, в которую нужно поместить консолидированные данные.

    — выбрать команду Правка | Специальная вставка, установить переключатель Формула на Сложить, Умножить и т.п., и щелкнуть на кнопке OK.

     

    2. ПОНЯТИЕ СВОДНОЙ ТАБЛИЦЫ. ОСНОВНЫЕ ОПЕРАЦИИ СО СВОДНЫМИ ТАБЛИЦАМИ

     

    2.1 Создание сводной таблицы

     

    Команда Данные, Сводная таблица вызывает Мастера сводных таблиц для построения сводов — итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы MS Excel. Сводная таблица обеспечивает различные способы агрегирования информации [3, c. 412].

    Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:

    Этап 1. Указание вида источника сводной таблицы:

    — использование списка (базы данных Excel);

    — использование внешнего источника данных;

    — использование нескольких диапазонов консолидации;

    — использование данных из другой сводной таблицы.

    В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим наиболее распространенный случай использования списков при построении сводных таблиц.

    Этап 2. Указание диапазона ячеек, содержащего исходные данные. Список (база данных Excel) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде

     

    [имя_книги]имя_листа!диапазон ячеек

     

    Если предварительно установить курсор в список, для которого строится сводная таблица, интервал ячеек будет автоматически указан. Для ссылки на закрытый интервал другой рабочей книги нажимается кнопка <0бзор>, в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.

    Этап 3 . Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете (рис. 2.1):

    110313 2214 8 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 2.1 Схема макета сводной таблицы

     

    страница — на ней размещаются поля, значения которых обеспечивают отбор записей на первом уровне; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи — сверху вниз; страницу определять необязательно;

    столбец — поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно;

    строка — поля размещаются сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку необязательно;

    данные — поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно.

    Размещение полей выполняется путем их перетаскивания при нажатой левой кнопке мыши в определенную область макета. Каждое поле размещается только один раз в областях: страница, строка или столбец. По этим полям можно формировать группы и получать итоговые значения в области данные — группировочные поля. В области данные могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данные. Для каждого такого поля задается вид функции и выполняется необходимая настройка.

    Для изменения структуры сводной таблицы выполняется перемещение полей из одной области в другую (добавление новых, удаление существующих полей, изменение местонахождения поля). Для сводных таблиц существен порядок следования полей (слева направо, сверху вниз), изменяется порядок следования полей также путем их перемещения.

    В макете сводной таблицы можно выполнить настройку параметров полей, размещенных в области данных. Эта настройка полей осуществляется с помощью диалогового окна «Вычисление поля сводной таблицы» (рис, 2.2).

     

    110313 2214 9 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 2.2 Диалоговое окно «Вычисление поля сводной таблицы»

     

    Для этого следует установить курсор на настраиваемое поле и дважды нажать левую кнопку мыши для вызова диалогового окна «Вычисление поля сводной таблицы», в котором можно переименовать поле, изменить операцию, производимую с данными поля, или изменить формат представления числа.

    Кнопка «Дополнительно» вызывает панель Дополнительные вычисления для выбора функций, список которых приведен в табл. 2. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элемент содержит значения поля, участвующего в пользовательском вычислении.

     

    Таблица 2. Виды дополнительных функций над полем в области данных

    Функция

       

    Результат

       

    Отличие   

    Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент

    Доля 

    Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элемент

       

    Приведенное отличие   

    Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент, нормированной к значению этого элемента

       

    С нарастающим итогом в поле

    Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге 

    Доля от суммы по строке   

    Значения ячеек области данных отображаются в процентах от итога строки   

    Доля от суммы по столбцу   

    Значения ячеек области данных отображаются в процентах от итога столбца   

    Доля от общей суммы   

    Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы 

    Индекс   

    При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог)) / ((Итог строки) * (Итог столбца))   

     
     

    Этап 4. Выбор места расположения и параметров сводной таблицы. В появляющемся на четвертом шаге диалоговом окне (рис. 2.3) можно выбрать место расположения сводной таблицы, установив переключатель новый лист или существующий лист, для которого необходимо задать диапазон размещения. После нажатия кнопки <Готово> будет сформирована сводная таблица со стандартным именем.

     

    110313 2214 10 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 2.3 Диалоговое окно «Мастер сводных таблиц» на 4-м этапе

     

    Кнопка <Параметры> в диалоговом окне 4-го шага вызывает диалоговое окно «Параметры сводной таблицы», в котором устанавливается вариант вывода информации в сводной таблице:

    общая сумма по столбцам — внизу сводной таблицы выводятся, общие итоги по столбцам;

    общая сумма по строкам — в сводной таблице формируется итоговый столбец;

    сохранить данные вместе с таблицей — сохраняется не только макет, но и результат построения сводной таблицы, на который можно ссылаться из других таблиц;

    автоформат — позволяет форматировать сводную таблицу с помощью команды Формат, Автоформат и другие параметры.

     

    2.2 Изменение сводной таблицы: внешний вид, обновление, макет и форматирование

     

    После завершения построения сводной таблицы могут измениться исходные данные. Для обновления данных сводной таблицы в этом случае следует выполнить команду Данные, Обновить данные.

    Для изменения структуры уже построенной сводной таблицы курсор устанавливается в область сводной таблицы, повторно выполняется команда Данные, Сводная таблица, которая вызывает Мастера сводных таблиц, шаг 3.

    При этом необходимо заметить, что сводные таблицы предназначены только для чтения, данные в них нельзя изменять. Но зато пользователь может очень легко трансформировать сводную таблицу: добавлять новые строки и столбцы, менять их местами, изменять названия полей и уровень детализации отображаемых данных [3, c. 420].

    В каждой области таблицы может размещаться не одно, а несколько полей данных. Чтобы добавить неиспользованные поля запроса в сводную таблицу, можно просто перетащить их мышью в нужную область.

    Например, можно добавить в область фильтра поле «Клиенты. Название» (CompanyName), что позволит фильтровать данные не только по странам, но и по клиентам (см. рис. 2.4). Для этого необходимо перетащить поле «Клиенты. Название» (CompanyName) из списка полей в область фильтра и поместить его рядом с полем «Страна» (Country). Устанавливая флажки против нужных клиентов, можно будет получать сводные данные по счетам для каждого клиента.

    Чтобы удалить поле строки, столбца или фильтра из таблицы, необходимо перетащить его мышью за пределы сводной таблицы (при этом рядом с курсором появится изображение креста). Поле исчезнет из таблицы.

    Пользователь может легко поменять местами поля из области фильтра и из области столбцов или строки поменять местами со столбцами. Например, можно переместить поле «Клиенты.Название» (CompanyName) в область столбцов, а поле «Годы» (Year) — в область фильтра. После этого в столбцах таблицы будут отображаться данные по продажам для каждого клиента (рис. 2.4), а, используя поле «Дата размещения по месяцам» (Order Date By Month), можно фильтровать эти данные.

    110313 2214 11 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 2.4 Отображение в сводной таблице данных по клиентам

     

    При переносе полей следует обращать внимание на форму указателя мыши. Когда он попадает в одну из областей таблицы — строк, столбцов, данных или фильтра — его форма меняется, и по ней можно определить, когда следует отпускать кнопку мыши.

    Cводная таблица связана с исходными данными, но она не обновляется автоматически при изменении исходных данных. Чтобы обновить сводную таблицу , надо выделить в ней любую ячейку и затем в меню Данные выбрать кнопку Обновить данные или нажать одноименную кнопку на панели инструментов Сводные таблицы [3, c. 428].

    Чтобы Excel автоматически обновлял сводную таблицу при каждом открытие книги, в которой она находится, необходимо выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводная таблица. Затем в окне диалога Параметры сводной таблицы необходимо установить флажок Обновить при открытии [3, c. 431].

    Если одна сводная таблица служит источником данных для другой, то при обновлении любой из них пересчитываются обе таблицы.

    Для изменения внешнего вида ячеек сводной таблицы можно использовать стандартную технику форматирования. Excel сохранит форматы и после обновления или реорганизации таблицы при условии , что не снят флажок Сохранить форматирование в окне диалога Параметры сводной таблицы [3, c. 435].

    Чтобы примененные форматы не терялись при обновлении или реорганизации таблицы, надо выполнить следующие действия:

    1. Выделить в сводной таблице любую ячейку.

    2. Выбрать команду Параметры в меню Сводная таблица на панели инструментов Сводные таблицы.

    3. В окне диалога Параметры сводной таблицы необходимо установить флажок Сохранить форматирование.

     

    3. АНАЛИЗ ДАННЫХ

     

    3.1 Использование сводной таблицы для консолидации данных

     

    Рассмотрим на примере использование сводных таблиц для консолидации данных (см. приложение 2).

    На рисунке 3.1 показаны три рабочих листа, каждый из которых содержит данные за месяц о продажах товаров в одном из магазинов крупной торговой сети. Необходимо консолидировать эту информацию в одну сводную таблицу. В данном примере исходные данные содержатся в одной рабочей книге. Однако так бывает далеко не всегда. Данные, которые необходимо консолидировать, могут находиться в разных рабочих книгах.

    110313 2214 12 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.1. Рабочие листы, содержащие данные за месяц о продажах товаров

     

    Для создания сводной таблицы необходимо выполнить следующие действия.

    — добавить новый лист, можно назвать его Итоги.

    — выбрать команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.

    — в первом диалоговом окне мастера выбрать переключатель В нескольких диапазонах консолидации и щелкнуть на кнопке Далее.

    — в следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2 из 3 выбрать переключатель Создать одно поле страницы, после чего щелкнуть на кнопке Далее.

    Теперь необходимо определить диапазоны для консолидации. Первый диапазон — Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Необходимо щелкнуть на кнопке Добавить для добавления диапазона к списку Список диапазонов.

    Данные действия необходимо повторить для двух других диапазонов. После этого необходимо щелкнуть на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.

    В третьем диалоговом окне Мастер сводных таблиц и диаграмм надо щелкнуть на кнопке Готово.

    В результате сводная таблица будет иметь вид:

    110313 2214 13 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.2 Сводная таблица

     

    На четвертом шаге описанной процедуры в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 можно выбрать переключатель Создать поля страницы. Это позволит назначить имя каждому элементу в поле страницы.

     

    3.2 Группировка элементов

     

    Рассмотрим создание структур рабочего листа и группировку данных.

    <

    Прежде чем создавать структуру, нужно убедиться в том, что данные подходят для создания структуры и формулы заданы правильно. Данные должны иметь иерархическую организацию. Все итоговые формулы должны быть введены правильно и согласовано. Под согласованностью подразумевается их одинаковое относительное положение.

    Создать структуру можно одним из способов [6]:

    — автоматически;

    — вручную.

    Чтобы автоматически создать структуру для некоторого диапазона данных, надо выполнить следующее [6]:

    — поместить табличный курсор в любую ячейку диапазона.

    — выбрать команду Данные | Группа и структура | Создание структуры.

    Excel проанализирует формулы из выделенного диапазона и создаст структуру. В зависимости от формул будет создана горизонтальная, вертикальная или смешанная структура.

    Если у рабочего листа уже есть структура, то будет задан вопрос, не хочет ли пользователь изменить существующую структуру. Необходимо щелкнуть на кнопке Да, чтобы удалить старую и создать новую структуру.

    Если воспользоваться командой Данные | Итоги, то Excel создаст структуру автоматически, при этом автоматически будут вставлены формулы для расчета промежуточных итогов, если данные введены в виде списка.

    Excel определяет способ создания структуры на основании анализа формул, поэтому без формул автоматически создать структуру невозможно. Процесс создания структуры вручную подразумевает создание групп строк (для вертикальной структуры) или групп столбцов (для горизонтальной структуры).

    Чтобы создать группу строк, необходимо выделить полностью все строки, которые нужно включить в эту группу, кроме строки, содержащей формулы для подсчета итогов. Затем нужно выбрать команду Данные | Группа и структура | Группировать. По мере создания группы Excel будет отображать символы структуры.

    Если перед созданием группы будет выделен диапазон ячеек (а не строки или столбцы целиком), то Excel отобразит диалоговое окно с вопросом о том, что пользователь хочет сгруппировать. Затем программа сгруппирует целиком те строки или столбцы, ячейки которых содержатся в выбранном диапазоне [5, c. 377].

    Можно выбирать также группы групп. Это приведет к созданию многоуровневых структур. Создание таких структур следует начинать с внутренней группы и двигаться изнутри наружу. В случае ошибки при группировке можно произвести разгруппирование с помощью команды Данные | Группа и структура | Разгруппировать

    В Excel есть кнопки инструментов, с помощью которых можно ускорить процесс группировки и разгруппировки (рис. 3.3). Кроме того можно воспользоваться комбинацией клавиш Alt + Shift + 110313 2214 14 Гипертекстовое учебное пособие «Сводный анализ в MS Excelдля группировки выбранных строк или столбцов, или Alt + Shift + 110313 2214 15 Гипертекстовое учебное пособие «Сводный анализ в MS Excelдля осуществления операции разгруппирования.

    110313 2214 16 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.3 Инструменты структуризации

     

    Инструмент структуризации содержит следующие кнопки [6].

    Таблица 3. Кнопки панели инструментов Структура.

    Кнопка  

    Название кнопки  

    Назначение  

    110313 2214 17 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Символы структуры документа  

    Скрывает и отображает символы структуры документа

    110313 2214 18 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Группировать  

    Группировка выбранных строк и столбцов  

    110313 2214 19 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Разгруппировать  

    Разгруппировка выбранных строк и столбцов  

    110313 2214 20 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Отобразить детали  

    Показ деталей (т.е. соответствующих ячеек с данными) для выбранной ячейки с итогами

    110313 2214 21 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Скрыть детали  

    Сокрытие деталей (соответствующих ячеек с данными) для выбранной ячейки с итогами

    110313 2214 22 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Выделить видимые ячейки  

    Выделяет только видимые ячейки рабочего листа, оставляя скрытые ячейки с данными не выделенными  

     

    3.3 Сортировка данных и итоги сводной таблицы, итоговые функции для анализа данных

     

    Если данные представлены в виде списка, программа «Excel» позволяет упростить этот процесс путем сортировки и фильтрации данных.

    Сортировка — это упорядочение данных по возрастанию или по убыванию. Проще всего произвести такую сортировку, выбрав одну из ячеек и щелкнув на кнопке «Сортировка по возрастанию» или «Сортировка по убыванию» на панели инструментов [6].

    Параметры сортировки задают командой «Данные» > «Сортировка». При этом открывается диалоговое окно «Сортировка диапазона». В нем можно выбрать от одного до трех полей сортировки, а также задать порядок сортировки по каждому полю.

    Рассмотрим вычисление итогов на примере сводной таблицы (с использованием группировки данных). В Excel предусмотрено удобное средство, которое позволяет группировать определенные элементы поля. Например, если одно из полей базы данных состоит из дат, то для каждой даты в сводной таблице будет отведена отдельная строка или столбец. Иногда полезно объединить даты в месяцы или кварталы, а затем убрать с экрана слишком детальное их представление. На рис. 3.4 показана сводная таблица, созданная на основе базы данных Банк.

    В ней показан итоговый баланс для каждого типа счета (поле столбца) по каждому из отделений (поле строки). Требуется создать отчет, который сравнивал бы результаты деятельности западного отделения с двумя другими отделениями, вместе взятыми. Решение очень простое — нужно создать группу, состоящую из данных центрального и северного отделений.

    110313 2214 23 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.4 Пример сводной таблицы

     

    Чтобы создать группу, необходимо выделить ячейки, которые будут сгруппированы, в данном случае — А6:А7. Затем надо выбрать команду Данные | Группа и структура | Группировать. В результате Excel создаст новое поле и назовет его Отделение2. В этом поле находиться два элемента: Западное и Группа1 (рис. 3.5).

    110313 2214 24 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.5 Сводная таблица после группировки данных

     

    Теперь можно удалить исходное поле Отделение и переименовать названия полей и элементов. На рисунке 3.6 показана сводная таблица после этих изменений. Новое название поля не может совпадать с названием существующего поля. При несовпадении имен Excel просто добавляет новое поле к сводной таблице. Поэтому в рассмотренном примере нельзя переименовать Отделение2 в Отделение без удаления исходного поля.

    110313 2214 25 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.6 Сводная таблица после выполненных преобразований

     

    Если элементы, необходимые для группировки, расположены не подряд, то выделить их можно следующим образом: необходимо нажать Ctrl и отметить элементы, которые должны составлять группу.

    Если элементы поля содержат числа, даты или время, то можно разрешить программе сгруппировать их автоматически. На рисунке 3.7 показана часть другой сводной таблицы, которая создана на основе той же банковской базы данных. На этот раз в качестве поля строки используется поле Счет, а в качестве поля столбца — Тип. Область данных отображает количество счетов данного типа.

    110313 2214 26 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.7 Пример сводной таблицы

     

    Чтобы создать группу автоматически, нужно отметить любой элемент поля Счет. Затем необходимо выбрать команду Данные | Группа и структура | Группировать. Появится диалоговое окно Группирование, показанное на рисунке 3.8.

    110313 2214 27 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.8 Диалоговое окно Группирование

     

    По умолчанию в нем будут показаны наименьшее и наибольшее значения, которые можно изменить по своему усмотрению. Например, чтобы создать группу с шагом в 5 000, необходимо ввести 0 в поле Начиная с, 100 000 — в поле По и 5 000 — в поле С шагом. Затем требуется щелкнуть на кнопке OK, и Excel создаст указанные группы. На рисунке 3.9 показана результирующая сводная таблица.

    110313 2214 28 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3.9 Результирующая сводная таблица

     

    В Excel существуют итоговые функции – они используются для вычисления автоматических промежуточных итогов, для консолидации данных, а также в отчетах сводных таблиц и сводных диаграмм. Следующие итоговые функции доступны в отчетах сводных таблиц и сводных диаграмм для всех типов исходных данных кроме OLAP (табл. 4) [5, c. 398].

     

    Таблица 4. Итоговые функции.

    Функция 

    Результат 

    Сумма 

    Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям. 

    Количество значений

    Количество данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям. Операция «Кол-во значений» работает так же, как и функция СЧЁТЗ.

    Среднее 

    Среднее чисел. 

    Максимум 

    Максимум чисел 

    Минимум 

    Минимум чисел 

    Произведение 

    Произведение чисел. 

    Количество чисел

    Количество данных, являющихся числами. Операция «Кол-во чисел» работает так же, как и функция СЧЁТ. 

    Несмещенное отклонение 

    Несмещенная оценка стандартного отклонения для генеральной совокупности, где выборка является подмножеством генеральной совокупности.

    Смещенное отклонение 

    Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных. 

    Несмещенная дисперсия 

    Несмещенная оценка дисперсии для генеральной совокупности, где выборка является подмножеством генеральной совокупности.

    Смещенная дисперсия 

    Смещенная оценка дисперсии генеральной совокупности по выборке данных. 

     

    В ходе работы были внимательно проанализированы такие возможности и средства MS Excel, как консолидация данных и их дальнейшая обработка (использование сводных таблиц, применение функций анализа данных и т.п.).

    Были описаны основные моменты, которые требуется знать пользователю при работе с ними.

    Приведенных в тексте работы материалов достаточно для того, чтобы еще раз убедиться в преимуществах программа работы с электронными таблицами по отношению к ведению расчетов вручную, и, в частности, для того, чтобы склониться в пользу продукта от Microsoft при выборе ПО для работы.

    СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

     

  1. Додженков В.А., Колесников Ю.И. Microsoft Excel 2002. — СПб, БХВ-Петербург, 2003 г. — 1056с..
  2. Додж М., Стинсон К. Эффективная работа с Microsoft Excel 2002. – СПб: БХВ-Петербург, 2003 г. — 1072с.
  3. Мак Федриз П. и др. Microsoft Office 97. Энциклопедия пользователя. – Киев: «Диасофт», 2004 г. – 445 с.
  4. Основы экономической информатики. Учеб. Пособие / Под ред. А.Н. Морозевича. – Мн.: ООО «Новое знание», 2001 г. – 573 с.
  5. Симонович С.В. Информатика. Базовый курс. — СПб.: «Питер», 2000 г. – 687 с
  6. Справочная система MS Excel 2003.

     

    ПРИЛОЖЕНИЕ 1.

    Тест по теме

     

    № вопроса 

    Вопрос 

    Варианты ответов 

    Правильный ответ 

    1

    В каком меню находится команда «Сортировка»? 

    а) Формат

    б) Данные

    в) Сервис 

    б) 

    2

    Что необходимо сделать перед подведением промежуточных итогов? 

    а) внести требуемые ячейки в область

    б) отсортировать список

    в) отключить фильтры 

    б) 

    3

    Для каких данных можно подводить промежуточные итоги? 

    а) для любых

    б) для даты, числовых данных и времени

    в) для числовых данных 

    в) 

    4

    Какая функция м.б. использована для подведения промежуточных итогов по одному столбцу?

    а) Автосумма

    б) Счет

    в) Срзнач 

    а) 

    5

    Что такое функция?

    а) возвращающая значение формула

    б) выражение, которое возвращает одно единственное значение

    в) заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке

    в) 

    6

    Как вызвать список доступных функций? 

    а) с помощью клавиш Ctrl+F5

    б) с помощью клавиш Shift+F3

    в) с помощью клавиш Alt+F7

    б) 

    7

    Можно ли создать сводную таблицу на основы данных из базы MS Excel?

    а) нет

    б) только при отсортированной базе

    в) да 

    в) 

    8

    Какое меню включает команду «Сводная таблица»? 

    а) Сервис

    б) Данные

    в) Справка 

    б) 

    9

    По скольким полям (максимум) можно сортировать список? 

    а) 3

    б) 5

    в) любое 

    а) 

    10 

    Какая функция из приведенного списка не относится к итоговым?

    а) Минимум

    б) Произведение

    в) Срзнач 

    в)

    11 

    Что такое «консолидация»? 

    а) агрегирование (объединение) данных, представленных в исходных областях – источниках

    б) представление данных в виде сводных отчетов, таблиц и диаграмм

    в) фильтрация и группировка данных

    а)

    12 

    Сколько областей-источников (максимум) может участвовать в консолидации? 

    а) до 16

    б) до 128

    в) до 155

    г) до 255 

    г)

    13 

    Какое название носит консолидация данных при помощи формул? 

    а) векторный анализ

    б) трехмерные формулы

    в) анализ рядов

    г) итоговая группировка

    б)

    14 

    Как располагаются источники при консолидации данных по расположению? 

    а) в разных книгах

    б) на разных листах

    в) одинаково

    г) на одних и тех же листах 

    в)

    15 

    Что требуется для консолидации данных по категориям? 

    а) заголовки строк

    б) заголовки столбцов

    в) заголовки строк и столбцов 

    в)

    16 

    В каком диалоговом окне задаются условия консолидации? 

    а) Консолидация

    б) Группирование

    в) Функции

    г) Фильтр 

    а)

    17 

    Какие условия налагаются на ссылки на ячейки при задании расположения источников данных для консолидации?

    а) источники и назначение на одном листе

    б) источники и назначение в различных местах диска

    в) область-источник поименована

    а)

    18 

    В каком пункте меню находится команда «Сводная таблица»?

    а) Формат

    б) Данные

    в) Правка

    б)

    19 

    За сколько этапов выполняется построение сводной таблицы при помощи мастера?

    а) за 3

    б) за 4

    в) за 5

    г) за 6

    д) за 8 

    б)

    20 

    Что указывается на втором этапе работы мастера сводных таблиц? 

    а) Указание диапазона ячеек, содержащего исходные данные

    б) Указание параметров группировки

    в) Задание итоговой функции 

    а)

    21 

    Что указывается на четвертом этапе работы мастера сводных таблиц? 

    а) Задается расположение и параметры сводной таблицы

    б) Указание параметров группировки

    в) Задание итоговой функции 

    а)

    22 

    При помощи какой команды группируются элементы?

    а) Группировка

    б) Фильтрация

    в) Создание структуры

    в)

    23 

    Для чего используется кнопка 110313 2214 29 Гипертекстовое учебное пособие «Сводный анализ в MS Excel на панели инструментов структуры?

    а) Разгруппировать

    б) Группировать

    в) Отобразить детали

    г) Скрыть детали 

    б)

    24 

    Как можно сортировать данные в Excel?

    а) по возрастанию

    б) по убыванию

    в) по возрастанию и убыванию 

    в)

    25 

    Какая клавиша используется для выделения расположенных в разных частях листа данных, 

    а) Shift

    б) Alt

    в) Ctrl

    в)

    26

    Как можно сортировать данные в Excel?

    а) по одному полю

    б) не более, чем по 8 полям

    в) не более, чем по 255 полям

    г) по любому числу полей

    г)

    27

    Для чего используется функция «Количество значений»?

    а) Количество данных, являющихся числами

    б) Количество данных

    а)

    28

    К какой группе функций относится функция «Произведение»?

    а) агрегатные функции

    б) статистические функции

    в) математические функции

    г) итоговые функции

    г)

    29

    Какое средство используется для построения сводных диаграмм?

    а) Мастер сводных диаграмм

    б) Мастер сводных таблиц

    в) Мастер диаграмм

    г) Мастер отчетов

    б)

    30

    В каком пункте меню находится команда «Специальная вставка»?

    а) Формат

    б) Данные

    в) Правка

    в) 

     

    ПРИЛОЖЕНИЕ 2.

    Решение задач и практические рекомендации

     

    1. Рассмотрим на примере использование сводных таблиц для консолидации данных.

     

    На рисунке 1 показаны три рабочих листа, каждый из которых содержит данные за месяц о продажах товаров в одном из магазинов крупной торговой сети. Необходимо консолидировать эту информацию в одну сводную таблицу. В данном примере исходные данные содержатся в одной рабочей книге. Однако так бывает далеко не всегда. Данные, которые необходимо консолидировать, могут находиться в разных рабочих книгах.

    ris177 1 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 1. Рабочие листы, содержащие данные за месяц о продажах товаров

     

    Для создания сводной таблицы необходимо выполнить следующие действия.

    — добавить новый лист, можно назвать его Итоги.

    — выбрать команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.

    — в первом диалоговом окне мастера выбрать переключатель В нескольких диапазонах консолидации и щелкнуть на кнопке Далее.

    — в следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2 из 3 выбрать переключатель Создать одно поле страницы, после чего щелкнуть на кнопке Далее.

    Теперь необходимо определить диапазоны для консолидации. Первый диапазон — Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Необходимо щелкнуть на кнопке Добавить для добавления диапазона к списку Список диапазонов.

    Данные действия необходимо повторить для двух других диапазонов. После этого необходимо щелкнуть на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.

    В третьем диалоговом окне Мастер сводных таблиц и диаграмм надо щелкнуть на кнопке Готово.

    В результате сводная таблица будет иметь вид:

    ris177 2 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 2 Сводная таблица

     

    На четвертом шаге описанной процедуры в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 можно выбрать переключатель Создать поля страницы. Это позволит назначить имя каждому элементу в поле страницы (см. файл Приложение 3-1.xls).

     

    2.
    Фильтрация. Сортировка

    — сформировать список продаж компьютерных запчастей за определенный период (включить в список поля «Дата продажи», «Тип товара», «Количество единиц», «Стоимость единицы», «Общая стоимость», «Менеджер» и др.)

    110313 2214 30 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 3 Список

    — отобразить данные о продажах мониторов и принтеров.

    110313 2214 31 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 4 Сведения о продажах мониторов и принтеров

    — отобразить записи, для которых стоимость проданных товаров превышает 10000 руб.

    110313 2214 32 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 5 Фильтрация по стоимости проданных товаров

     

    — отобразить данные о продажах клавиатур, где количество единиц в заказе больше 20 (использовать фильтрацию данных при выполнении заданий).

    110313 2214 33 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 7 Фильтрация по количеству проданных клавиатур

     

    — отсортировать исходную таблицу по убыванию сумм заказов.

    110313 2214 34 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 8 Сортировка данных

     

    Образец выполненного задания находится в файле «Приложение 3-2.xls»)

     

    3.
    Создание сводных таблиц.

    — использовать книгу, сформированную во втором задании.

    — сформировать сводную таблицу по продажам товаров каждым менеджером.

    110313 2214 35 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

    Рис. 9 Сводная таблица

     

    Образец выполненного задания находится в файле «Приложение 3-3.xls».

     

     

    ПРИЛОЖЕНИЕ 3.

    Презентация в MS PowerPoint

     

    110313 2214 36 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

     

    110313 2214 37 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

     

    110313 2214 38 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

     

    110313 2214 39 Гипертекстовое учебное пособие «Сводный анализ в MS Excel

<

Комментирование закрыто.

WordPress: 23.75MB | MySQL:119 | 1,864sec