Анализ данных в Excel 2003

В этой главе вы познакомитесь cq следующими вопросами, связанными с анализом данных:

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

Сортировка данных

Excel позволяет упорядочить данные, приведенные в таблице, в алфавитно-цифровом порядке по возрастанию или убывания значений. В зависимости от выполняемой работы требуется сортировка различных данных. Например, при работе со списком товаров желательно отсортировать их по названиям, при выборе товаров в определенном ценовом диапазоне — в порядке возрастания или убывания их цены. Числа сортируются от наименьшего отрицательного до наибольшего положительного числа. При сортировке алфавитно-цифрового текста Excel сравнивает значения посимвольно слева направо. Например, если ячейка содержит текст « И100», Excel поместит ее после ячейки, содержащей запись «И1», и перед ячейкой, содержащей запись « ИИ».
Текст, в котором есть числа, сортируется в следующем порядке: 0123456789 (пробел) !«#$%&()*,./:;?@[\]^_'{|}~ + < = >А ВСDЕFGНIJKLMNОРQRSTUVWXYАБВГДЕЕЖЗИЙКЛ МНОПРСТУФХЦ Ч Ш Щ Ъ Ы Ь Э Ю Я. Пустые значения всегда ставятся в конец вне зависимости от направления сортировки.

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

Сортировка денных по нескольким полям

Стандартные средства Excel позволяют одновременно сортировать записи по трем полям.
Если сортируемый список окружен со всех сторон пустыми ячейками, то достаточно установить курсор в одну из ячеек. Последовательность сортировки полей выбирается в диалоговом окне Сортировка диапазона в раскрывающихся списках Сортировать по (Sort by), Затем по (Then by), В последнюю очередь, по (Then by) (рис. 18.1). Расположенные рядом с каждым списком переключатели по возрастанию (Ascending) , пo убыванию (Descending) позволяют задать направление сортировки.
Переключатель Идентифицировать поля по (My list has) можно установить в следующие положения:

 подписям (No header row) — исключает первую строку с названиями столбцов из сортировки и позволяет работать с полями по их названиям;  обозначениям столбцов листа (Header row) — если в сортируемом диапазоне первая .строка не содержит названий столбцов.

Рис. 18.1 Задание условий сортировки

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

Использование списков в качестве баз данных

Набор строк таблицы, содержащий связанные данные, образует список. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов. Можно выбрать диапазон ячеек и определить его в качестве списка.
Списки на листе Excel позволяют группировать данные и выполнять различные действия над связанными данными. Управление данными в нем и их анализ производится независимо от данных за пределами списка. К каждому
столбцу списка по умолчанию применяется Автофильтр, кнопки которого расположены в строке заголовков, что позволяет быстро фильтровать и сортировать данные. В список можно добавить строку итогов. Щелкнув ячейку в строке итогов, можно выбрать из раскрывающегося списка нужную обобщенную функцию. Путем перемещения метки изменения размера, находящейся в правом нижнем углу рамки списка, можно изменить размер списка. В качестве примера, составим список студентов группы. В ячейку А1 введем Список студентов, в А2 — № п/п, В2 — Фамилия, С2 — Имя, D2 — Год рождения и заполним первую строку списка, не указывая № п/п, например, Андреев Петр 1978 (рис. 18.2).

Рис. 18.2 Пострoение базы данных в Excel

Ha экране отобразится диалоговое окно с именем листа, на котором расположен список (рис. 18.3). Назначение кнопок в этом окне:

 Дoбaвumь(New) — позволяет создать новую запись. После нажатия клавиши Enter запись будет добавлена в конец списка. Поля формы очистятся и в них можно будет ввести новую запись.  Удалить (Delete) — удаляет текущую запись из списка.  Вернуть (Restore) — отменяет все изменения, внесенные в текущую запись. Нажатие этой кнопки не восстанавливает удаленную запись.  Далее (Find Next), Назад (Find Prev) — позволяет перейти к следующей или к предыдущей записи списка.  Критерии (Criteria) — позволяет производить поиск записей, основанных на заданном условии поиска (поиск данных по заданным условиям рассмотрен ниже).  Закрыть (Close) — добавляет запись и закрывает диалоговое окно.

Рис. 18.3 Форма для ввода и редактирования новых записей

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

Поиск ценных по заданным условиям

С помощью формы можно производить поиск данных, приведенных в списке, по определенному критерию. Чтобы провести такой поиск, нажмите кнопку Критерии (Criteria) (рис. 18.4). В качестве условия могут выступать как последовательность символов, например, конкретная фамилия, так и выражение. Введем в поле Год рождения «>1978» и после нажатия кнопки Далее (Find Next) или Назад (Find Prev) мы увидим записи, удовлетворяющие заданному критерию.

Рис. 18.4 Задание критерия поиска

Автофильтр

В ряде случаев нам необходимо найти только те строки списка, которые отвечают заданному условию, например, в списке цен на продукты выбрать только строки, касающиеся шоколада, или в списке студентов, выбрать тех, кто родился после определенной даты. Скрыть строки, не отвечающие заданному условию, позволяет функция Автофильтр.
В верхней строке выделенного диапазона в каждом столбце появится раскрывающийся список (рис. 18.5), содержащий перечень возможных вариантов фильтрации (таблица 18.1). Для отмены использования автофильтра повторив выберите в меню Данные (Data) команды Фильтр (Filter), Автофильтр (AutoFUter).

Рис. 18.5 Выбор условия фильтрации с помощью функции Автофильтр

Таблица 18.1. Назначение различных элементов раскрывающегося списка, созданного командами Фильтр (Filter), Автофильтр (AutoFilter) в меню Данные (Data)

Элемент раскрывающегося списка, созданного командой Автофильтр	Назначение элемента раскрывающегося списка Автофильтра
Сортировка по возрастанию	Сортирует список по возрастанию (с первых букв алфавита)
Сортировка по убыванию	Сортирует список по убыванию (с последних букв алфавита)
Все (All)	Отключает фильтрацию, отображает все поля
Первые 10 (Тор 10)	Отображает диалоговое окно, позволяющее выбрать заданное количество наибольших или наименьших значений в столбце с числовыми данными (см. ниже раздел «Автофильтрация «Первые 10»»)
Условие (Custom)	Отображает диалоговое окно, позволяющее задать условия отбора «см. ниже раздел «Пользовательский автофильтр»»

Автофильтрация "Первые 10"

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

Рис. 18.6 Диалоговое окно Наложение условия по списку

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

Пользовательский автофильтр

Пользовательский автофильтр позволяет использовать операторы сравнения при фильтрации данных выбранного столбца. После выбора элемента Условие (Custom) в раскрывающемся списке, созданном командой Автофильтр (AutoFilter), отображается диалоговое окно Пользовательский автофильтр (Custom AutoFilter).
В группе параметров раскрывающегося списка слева выберите операцию фильтрации, а затем в поле справа введите значение с листа, с которым будет производиться сравнение. Для включения другого набора условий фильтрации выберите нужное положение переключателя И (And), Или (Or) и задайте соответствующие условия в расположенных ниже полях.
Например, вы можете выбрать из списка элемент начинается с (begins with) и указать букву К. В результате будут выбраны только те фамилии, которые начинаются с этой буквы.

Прогнозирование линейной или экспоненциальной зависимости

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

 выделите диапазон ячеек, содержащий исходные значения;  выберите в меню Правка (Edit) команду Заполнить (Fill), Прогрессия (Series);  в диалоговом окне Прогрессия (Series) установите флажок Автоматическое определение шага (Trend ) (см. рис. 14.9);  положением переключателя Расположение укажите, заполняется ли ряд строк или столбцов. Содержимое первой ячейки или ячеек этой строки или столбца будет использовано как начальное значение ряда;  положением переключателя Тип (Туре) выберите, какую прогрессию вы хотите использовать при аппроксимации.

Шаг прогрессии определяется автоматически, на основе анализа выделенных значений. При этом исходные значения ячеек заменяются значениями ряда.

Подбор параметра, обеспечивающего получение требуемого результата

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

Таблица 18.2. Исходные данные по затратам на выпуск 3000 книг

Параметр	Значение	Ячейка	Расчетная формула
Тираж	3000	В1
Затраты на печатание книг	3180000	В2	В1* ВЗ
Затраты на печатание одной книги	120	ВЗ
Затраты на зарплату	70000	В4
Накладные расходы	48000	В5
Затраты на аренду	20000	В18
Общие затраты	498000	В7	В2+ В4+ В5+ В 18
Себестоимость одной книги	11818	В8	В7/В1
Доход	10000	В9	(В10- В8)* В1
Оптовая цена книги	190	В10

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

 Выделите ячейку В9 с рассчитываемым параметром Доход (рис. 18.7).  Выберете в меню Сервис (Tools) команду Подбор параметра (Goal Seek).

Рис. 18.7 Затраты на печатание тиража книг

 На экране отобразится диалоговое окно Подбор параметра (рис. 18.8). -В поле Установить в ячейки (Set cell) будет видна ссылка на ячейку В9, содержащую формулу, для которой следует подобрать параметр. (Она была выделена до выбора команды.) Кнопка свертывания диалогового окна, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует снова нажать кнопку для вывода на экран диалогового окна.

Рис. 18.8 Диалоговое окно Подбор параметра

 В поле Значение (То value) введите искомое число дохода 90000.  В поле Изменение значения параметра (By changing cell) укажите ссылку на 'ячейку, содержащую Параметр, значение которого требуется подобрать для получения требуемого результата, в данном примере меняется тираж книги, величина которого задается в ячейке В1. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке (Set cell).

Рис. 18.9 Диалоговое окно Результат подбора параметра

 Нажмите кнопку ОК . Откроется диалоговое окно Результат подбора параметра (Goal Seek Status) (рис. 18.9). Если подбор параметра требует много времени, то кнопка Пауза (Pause) в этом окне позволяет выполнить пошаговый процесс поиска. Кнопка Продолжить (Continue) позволяет возобновить автоматизированный подбор.

Визуальный подбор параметра с помощью диаграммы

Аргумент, обеспечивающий необходимое значение параметра, можно найти с помощью гистограммы, линейчатой диаграммы, графика. На рис. 18.10 показана гистограмма затрат на тираж. Для визуального подбора параметра, установите указатель мыши ниже верхнего края столбца тиража, и выполните два одиночных щелчка мышью. Перетащите верхний средний квадратик в требуемое положение: 90000. После того как вы отпустите кнопку мыши, на экране отобразится диалоговое окно Подбор параметра с заполненными полями Установить в ячейке (Set cell) и Значение (То value) . Введите ссылку на ячейку В1 в поле Изменяя значение параметра (By changing cell). Нажмите кнопку ОК . Откроется диалоговое окно Результат подбора параметра (Goal Seek Status) , рассмотренное выше.

Рис. 18.10 Bспользование uистограммы для dыбора заданного значения параметра

Решение уравнений

Для решения задач с несколькими неизвестными и набором ограничений следует использовать надстройку Поиск решения (Solver) .
В качестве примера решим уравнение
2х3-4х2+3х=27
Для решения уравнения выполните следующие действия:

 Запишите в ячейку А1 число 0, а уравнение в ячейку В1: =2*АГЗ-4*А1 Л2+3*А1-27+А1.  Выберите команду Подбор параметра (Goal Seek) в меню Сервис (Tools) в меню Сервис .  В поле Установить в ячейке (Set cell) введите В1, в поле Значение (То value) — 1, в поле Изменяя значение параметра (By changing cell) дайте ссылку на ячейку А1.  Нажмите кнопку ОК . Откроется диалоговое окно Результат подбора параметра (Goal Seek Status) .  Нажмите кнопку ОК . В ячейке А1 будет приведено значение 2,939, т.е. х=3.

Если уравнение имеет несколько корней, то измените число 0, выбранное в качестве начального приближения, например, на 0,5 или 2.

Аппроксимация данных

Напомним, что регрессионный анализ это вид статистического анализа, используемый для прогнозирования. Регрессионный анализ позволяет оценить степень связи между переменными, предлагая механизм вычисления предполагаемого значения переменной из нескольких уже известных значений.
Линиями тренда можно дополнить ряды данных, представленные на ненормированных плоских диаграммах с областями, линейчатых диаграммах, гистограммах, графиках, биржевых, точечных и пузырьковых диаграммах. Использование линии тренда того или иного вида определяется типом данных. Нельзя дополнить линиями тренда ряды данных на объемных диаграммах, нормированных диаграммах, лепестковых диаграммах, круговых и кольцевых диаграммах.
Более ясно закономерность в развитии данных показывает сглаженная кривая. Она строится по точкам скользящего среднего, где под скользящим средним подразумевается последовательность средних чисел, каждое из которых вычислено по некоторому подмножеству ряда данных.

Добавление линии тренда или скользящего среднего к рядам данных

В Excel используются шесть различных видов линий тренда (аппроксимация и сглаживание), которые могут быть добавлены в диаграмму (рис. 18.11): 1) Линейная аппроксимация (Linear) — это прямая линия, наилучшим образом описывающая набор данных. Уравнение прямой у=ах+Ь, где а — тангенс угла наклона, b — точка пересечения прямой с осью у. Линейная аппроксимация применяется для переменных, которые увеличиваются или убывают с постоянной скоростью.
2) Логарифмическая аппроксимация (Logarithmic) хорошо описывает положительные, так и отрицательные величины, которые вначале быстро растут или убывают, а затем постепенно стабилизируется. Логарифмическая аппроксимация использует уравнение у=с* lnx+Ь, где с и b константы, In — натуральный логарифм.
3) Полиномиальная аппроксимация (Polynomial) используется для описания величин, попеременно возрастающих и убывающих. Ее целесообразно применять для анализа большого набора данных нестабильной величины. Степень полинома определяется количеством экстремумов (максимумов и минимумов) кривой. Полином второй степени может описать только один максимум или минимум. Полином третьей степени имеет один или два экстремума. Полином четвертой степени может иметь не более трех экстремумов. Полиномиальная аппроксимация описывается уравнением y=a+ciXi+C2X2++Cigx18, где a, Cj—Cjg — константы. Требуемая степень полинома задается в поле Степень (рис.). Максимальная величина степени — 18.
4) Степенная аппроксимация (Power) дает хорошие результаты, если зависимость, которая содержится в данных, характеризуется постоянной скоростью роста. Примером такой зависимости может служить график ускорения автомобиля. Если в данных имеются нулевые или отрицательные значения, использование степенного приближения невозможно. Степенная аппроксимация описывается уравнением у=а * хn, где а и n — константы.
5) Экспоненциальную аппроксимацию (Exponential) следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, этот вид приближения неприменим. Экспоненциальная аппроксимация описывается уравнением у= а • ebx, где а и b — константы.
6) Линейная фильтрация (Moving average) позволяет сгладить колебания данных и таким образом более наглядно показать характер зависимости. Такая линия тренда строится по определенному числу точек (она задается параметром Тонки (Period). Элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если параметр Тонки равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка — как среднее следующих двух элементов и так далее. Для расчета скользящего среднего используется уравнение у= (Aj+Aj_i++Aj_n+i)/n.

Добавление линии тренда к рядам данных

Для добавления линии тренда к рядам данных выполните следующие действия:

 выделите ряд данных, к которому нужно добавить линию тренда или скользящее среднее;  выберите команду Добавить линию тренда (Add Trendline ) в меню Диаграмма (Chart). На вкладке Тип (Туре) выберите нужный тип регрессионной линии тренда или линии скользящего среднего (рис. 18.11);

Рис. 18.11 Выбор линии тренда

Рис. 18.12 Диаграмма с нанесенной линией тренда

 при выборе типа Полиномиальная (Polynomial) введите в поле Степень (Order) наибольшую степень для независимой переменной;  при выборе типа Скользящее среднее (Moving Average) введите в поле Точки (Period) число точек, используемых для расчета скользящего среднего.

Все ряды данных диаграммы, поддерживающей линии тренда перечислены в поле Построен на ряде (Based On Series) . Для добавления линии тренда к другим рядам выберите нужное имя в поле, а затем выберите нужные параметры. Если вариант Скользящее среднее (Moving Average) выбран для точечной диаграммы, результат будет зависеть от порядка расположения значений X во входном диапазоне. Чтобы получить правильный результат, необходимо отсортировать значения X перед построением линии скользящего среднего.

Надежность линии тренда

Оценка надежности линии тренда к фактическим данным выполняется по показателю определенности или величине R в квадрате. R может изменяться от 0 до 1. Чем больше величина этого показателя, тем достовернее линия тренда. Значение R2 автоматически рассчитывается Excel при подборе линии тренда к данным. Это значение можно отобразить на диаграмме.
Для вывода значения R-квадрат для линии тренда щелкните эту линию, и выберите команду Выделенная линия тренда (Selected Trendline) в меню Формат (Format) . На вкладке Параметры (Options) установите флажок поместить на диаграмму величину достоверности аппроксимации (R*2) (Display R-squared value on chart) (рис. 18.13). Отметим, что для скользящего среднего значение R-квадрат не может быть отображено.

Рис. 18.13 вывод значения R-квадрат для линии тренда

Консолидация данных

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

Рис. 18.14 Пример таблицы исходных данных для консолидации

Консолидация данных по расположению

Диапазоны, данные которых консолидируются и помещаются в указанный конечный диапазон, называются исходными областями. Исходные области могут располагаться на любом листе или книге, на других открытых листах или книгах, а также на листах Lotus 1-2-3.
Если во всех ведомостях количество упаковок Рыбные продукты приводится в ячейке F3, то при консолидации в итоговой ведомости будут обобщены значения, хранящиеся в этой ячейке во всех ведомостях. Области консолидации

Рис. 18.15 Диалоговое окно Консолидация можно задать либо трехмерными формулами, либо в поле Ссылка (Reference) (рис. 18.15).-

В диалоговом окне Консолидация в раскрывающемся списке Функция (Function) выберите итоговую функцию, которую следует использовать для обработки данных, например, Сумма (Sum) , если будут суммироваться значения. Описание функций, приведенных в списке, дано в таблице 18.3.

Таблица 18.3. Итоговые функции, используемые при построении сводных таблиц и сводных диаграмм

Операция	Назначение
Сумма (Sum)	Вычисление суммы чисел, хранящихся в исходных ячейках. Эта операция используется по умолчанию для подведения итогов по числовым полям
Количество (Count)	Количество записей или строк данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям
Среднее (Average)	Вычисление среднего числа по данным, хранящимся в исходных ячейках
Максимум (Мах)	Определение максимального числа по данным, хранящимся в исходных ячейках
Минимум (Min)	Определение минимального числа по данным, хранящимся вч исходных ячейках
Произведение (Product)	Вычисление произведение чисел, хранящихся в исходных ячейках
Количество чисел (Count Nums)	Количество записей или строк, содержащих числа
Смещенное отклонение (StdDev)	Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных
Несмещенное отклонение (StdDevp)	Вычисление стандартного отклонения генеральной совокупности по выборке данных, хранящихся в исходных ячейках
Смещенная дисперсия (Var)	Смещенная оценка дисперсии генеральной совокупности по выборке данных
Несмещенная дисперсия (Varp)	Несмещенная оценка дисперсии генеральной совокупности по выборке данных

После установки курсора в поле Ссылка (Reference) введите ссылку на первый диапазон данных, который консолидируется в указанный конечный диапазон. Ссылку введите вручную или, если лист, содержащий новую исходную область, является текущим, выделите на нем исходную область. Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Чтобы убрать диалоговое окно Консолидация (Consolidate) на время выбора исходной области, нажмите кнопку Свернуть диалоговое окно (Collapse dialog) в правой части поля. Повторное нажатие на эту кнопку восстанавливает окно.
Если исходные данные находятся в другой книге, которая в данный момент закрыта, нажмите кнопку Обзор (Browse) и выберите книгу. Путь к выбранной ссылке отобразится в поле Ссылка (Reference). Затем добавьте ссылку.
Нажмите кнопку Дoбaвumъ(Add ). Введенная ссылка отобразится в окне Список диапазонов (All references). Повторите эту операцию для всех консолидируемых исходных областей.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.
При консолидации данных по расположению заголовки категорий исходных областей не копируются автоматически в область назначения. Если в области назначения требуется разместить заголовки, скопируйте или введите их вручную. .

Консолидация данных по категориям

Выберите из раскрывающегося списка Функция (Function) функцию, которую следует использовать для обработки данных (рис. 18.15). Используемые итоговые функции приведены в таблице 18.3. Введите исходную область консолидируемых данных в поле Ссылка (Reference) . Убедитесь, что исходная область имеет заголовок.
Нажмите кнопку Добавить (Add) для добавления диапазона к списку исходных диапазонов консолидации. Повторите эту операцию для всех консолидируемых исходных областей. В наборе флажков Использовать в качестве имен (Use labels in) установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.
Чтобы автоматически обновлять итоговую таблицу при изменении источников данных, установите флажок Создавать связи с исходными данными (Create links to source data). Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже входящие в консолидацию.

Сводные таблицы

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

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

В качестве примера рассмотрим создание сводной таблицы, позволяющей на основе таблиц с исходными данными выполнить анализ продажи определенных товаров в различных городах России. В книге, приведенной на рис. 18.16, показана продажа нескольких моделей автомобилей: Волга, Жигули, Ока в разных городах России: в Москве, Саратове и Туле. Каждый город показан на отдельном листе. Предполагается, что сводные таблицы составляются по четырем месяцам: январь, февраль, март и апрель. Таблицы отформатированы с использованием команды Автоформат (AutoFormat) в меню Формат (Format) . Выбран образец с подписью Простой (Simple) .

Рис. 18.16 Исходный список для составления сводной таблицы

Создание сводной таблицы желательно начать с выделения ячейки внутри используемого списка (это позволяет автоматически выделить диапазон, содержащий исходные данные).
Положением переключателя в группе Создать таблицу на основе данных, находящихся: (Create Pivot table from data in:) установите переключатель в положение: в нескольких диапазонах консолидации (Multiple consolidation ranges) , так как источники данных для создания сводной таблицы, расположены на разных листе Excel.
Назначение других положений переключателя:

 в списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or Database ) — позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных на одном листе Excel;  во внешнем источнике данных (External data source) — позволяет создать отчет сводной таблицы или сводной диаграммы по данным внешнего файла или базы данных, например, Microsoft Access, SQL Server, Paradox;.  в нескольких диапазонах консолидации — позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных в нескольких диапазонах консолидации;  в другой сводной таблице или сводной диаграмме (Another PivotTable report or Pivot Chart report) — используется для создания отчета сводной таблицы или сводной диаграммы по данным другого отчета сводной таблицы в активной книге.

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

В разделе Вид создаваемого отчета (What kind of report do you want to create?) поставьте переключатель в положение сводная таблица (PivotTable) для создания только сводной таблицы и нажмите кнопку Далее (Next).
Если исходные данные расположены на нескольких листах, то в следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 (PivotTable and Pivot Chart Wizard — Step 2a of3) поставьте переключатель в положение Создать одно поле страницы (Create a single page field for те) , так как все листы аналогичны и отличаются только городом, в котором реализовывалась продукция. Нажмите кнопку Далее (Next) .
На экране отобразится диалоговое окно Мастер сводных таблиц и диаграмм — шаг 26 из 3 (PivotTable and PivotChart Wizard — Step 2b of 3) . Щелкните мышью в поле Диапазон (Range) (рис. 18.18). Выделите поочередно на всех листах ячейки с А1 по Е4, и нажмите кнопку Добавить (Add) после каждого выделения для добавления диапазона к списку исходных диапазонов. Ссылка на исходную область будет добавлена в список Все ссылки (All references). Кнопка свертывания окна справа от поля позволяет свертывать диалоговое окно для выделения каждого диапазона. Повторное нажатие на эту кнопку восстанавливает окно.
Можно сдвинуть диалоговое окно, чтобы был виден один из углов выделяемого диапазона. Пока идет выделение диапазона диалоговое окно автоматически свертывается.

Рис. 18.18 Выделение диапазонов таблиц, подлежащих консолидации

Если исходная таблица находится в другой книге, к ней можно перейти с помощью кнопки Обзор (Browse) . Закончив выбор данных для отчета сводной таблицы, нажмите кнопку Далее (Next).
На последнем шаге мастера сводных таблиц и диаграмм вам предложат положением переключателя задать место, где следует поместить сводную таблицу (рис. 18.19):

 новый лист (New worksheet) — отчет сводной таблицы будет размещен на новом листе той же книги;  существующий лист (Existing worksheet) — отчет сводной таблицы будет размещен на том же листе, где находится исходная таблица. Кнопка свертывания диалогового окна справа от поля ввода временно убирает диалоговое окно с экрана, что позволяет указать диапазон ячеек, где будет расположена сводная таблица, путем выделения ячеек на листе. После этого можно нажать эту кнопку еще раз для восстановления диалогового окна.

Кнопку Готово (Finish) целесообразно нажать прежде, чем кнопку Макет (Layout) в следующих случаях:

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

Рис. 18.19 Выбор места расположения сводной таблицы

 планируется создать несколько полей данных и нужно указать порядок их отображения.

Сводную таблицу (рис. 18,20) можно создать путем перетаскивания заголовков полей в требуемую зону листа. Для некоторых внешних источников данных, особенно для больших баз данных, .это может оказаться более удобным по сравнению с настройкой макета непосредственно на листе. Так, если отчет создается на основе данных куба (с помощью мастера создания куба в Microsoft Query), настройка макета в диалоговом окне может значительно сократить время, которое требуется для извлечения данных. Здесь же можно выбрать параметры для создания полей страниц, чтобы данные элементов извлекались по отдельности. Параметры полей страниц доступны только в том случае, если источником данных отчета не является куб.

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

Упражнения

1. Составьте список заказов книг для поставки в разные библиотеки нескольких городов. Проанализируйте суммарные заказы на конкретные книги по городам.
2. Составьте список работников вашей фирмы с основными анкетными данными, используя форму (рис. 18.4). Выполните поиск данных и их сортировку по заданным критериям.
3. Создайте сводную таблицу по продаже товаров трех наименований за четыре месяца: январь, февраль, март и апрель для трех городов: Тула, Орел и Пенза. Отформатируйте таблицу с помощью команды Автоформат (AutoFormat) в меню Формат (Format). Переименуйте ярлычки листов по названиям городов. Посчитайте выручку (в тысячах рублей) по месяцам и товарам в разных городах и общую выручку.

Рис. 18.21 Диалоговое окно, используемое для автоматического вычисления полей сводной таблицы

Таблицы с данными магазинов будут иметь вид:

Таблица №1 город Тула

	Январь	Февраль	Март	Апрель
Овощи	20	30	14	23
Фрукты	30	48	15	24
Ягоды	25	24	16	25

Таблица №2 город Орел

	Январь	Февраль	Март	Апрель
	31	21	31	25
Д)п\лкты	32	22	32	23
Ягоды	33	23	33	24

Таблица №3 город Пенза

	Январь	Февраль	Март	Апрель
Овощи	12	24	24	23
Фрукты	14	21	45	33
Ягоды	17	26	44	32

Работу выполните в следующем порядке:

 Щелкните мышью в ячейке А2. Введите текст Овощи. Аналогично в ячейки A3 и А4 введите соответственно Фрукты и Ягоды.  Выделите ячейки с А1 по Е4 и выберите команду Автоформат (AutoFonnat) в меню Формат (Format).  В списке форматов выберите Классический! (Classicl) и нажмите кнопку ОК.  Щелкните правой кнопкой мыши по ярлыку первого листа и выберите в контекстном меню команду Переместить/скопировать (Move or Copy) . и установите в появившемся диалоговом окне флажок Создать копию (Create a copy) .  Аналогичным образом создайте третью копию листа.  Щелкните правой кнопкой мыши по ярлыку Лист 1(3) (Sheet 1(3)). Выберите команду Переименовать (Rename). В поле с именем листа введите Тула. Аналогичным образом двум другим листам с таблицей присвойте названия городов Орел, Пенза.  Заполните данные по реализации продукции по каждому из трех городов, как показано в таблицах 1, 2 и 3 ниже.  Для построения сводной таблицы выберите в меню Данные (Data) команду Сводная таблица (Pivot Table and PivotChart Report). Установите переключатель в положение В нескольких диапазонах консолидации (Multiple consolidation ranges), так как данные расположены на нескольких листах книги и нажмите кнопку Далее.  В следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 (PivotTable and PivotChart Wizard — Step 2a of3) поставьте переключатель в положение Создать одно поле страницы (Create a single page field for me), так как все листы аналогичны и отличаются только городом, в котором реализовывалась продукция. Щелкните мышью в поле Диапазон (Range). Выделите поочередно на всех листах ячейки с Al no E4, и нажмите кнопку Добавить (Add) после каждого выделения. Кнопка свертывания окна справа от поля позволяет свертывать диалоговое окно для выделения каждого диапазона. Повторное нажатие на эту кнопку восстанавливает окно. Затем нажмите кнопку Далее (Next).  Поставьте переключатель в положение Поместить таблицу на новый лист (New worksheet) и нажмите кнопку Готово (Finish ).  В появившейся сводной таблице дважды щелкните по полю со словом Строка (Row) . Откроется диалоговое окно Вычисления сводной таблицы PivotTable Field) (рис. 18.21). Введите слово Товар вместо Строка и нажмите ОК. Аналогично Столбец поменяйте на Месяц, а страница на Город. После создания сводной таблицы значения доходов просуммированы по месяцам и названиям товаров. Для анализа доходов по различным городам откройте раскрывающийся список городов в ячейке В1, выделите тот город который вас интересует и нажмите кнопку ОК. Вы получите просуммированные данные по данному городу.  Щелкните по раскрывающемуся списку Товары и снимите галочки рядом с теми товарами, которые вас не интересует. В таблице отобразятся сводные данные без этого продукта.

Выводы

1. Чтобы упорядочить данные по нескольким полям, выделите диапазон ячеек, который необходимо отсортировать, и выберите команду Сортировка (Sort) в меню Данные (Data).
2. Чтобы упростить ввод и редактирование данных при составлении списков в Excel, установите курсор в одной из ячеек списка и выберите в меню Данные (Data) команду Форма (Form).
3. Для прогнозирования зависимости выделите диапазон ячеек, содержащий исходные значения, и используйте диалоговое окно, отображаемое после выбора в меню Правка (Edit) команды Заполнить (Fill), Прогрессия (Series) .
4. Найти аргумент, обеспечивающий задаваемый результат, позволяет команда Подбор параметра (Goal Seek ) в меню Сервис (Tools). Решение находится путем последовательных итераций.
5. Чтобы обобщить однородные данные, расположенные в нескольких областях таблицы или на разных листах, в одной таблице, укажите верхнюю левую ячейку конечной области, где должны быть помещены консолидированные данные, и выберите команду Консолидация (Consolidate) в меню Данные (Data) .
6. Чтобы создать сводную таблицу, выберите команду Сводная таблица (Pivot Table and PivotChart Report) в меню Данные (Data) . Мастер сводных таблиц облегчает обработку больших массивов данных и получение итоговых результатов в удобном виде.

---

---