Лекция 12

Microsoft Office 2007. Методы работы с базами данных в Excel

Тема №3.Основные продукты, обеспечивающие  технологию автоматизированного офиса.

1.  Обработка информации в списках. Методы работы с базами данных в Excel

1.1.  Оформление базы данных 1.2. Структура

1.3.  Сортировка элементов БД

1.4.  Фильтрация  БД

1.1.1.  Автофильтр

1.1.2.  Расширенный фильтр

1.5.  Промежуточные итоги в БД

1.6.  Работа в режиме структуры

1.7.  Функции для работы с Базой данных

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

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

2.1.  Исходные данные сводной таблицы и сводной диаграммы

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

2.3.  Отчеты сводных диаграмм

3.  Консолидация

3.1.  Консолидация

3.2.  Изменение итоговой таблицы консолидации данных

1. Обработка информации в списках. Методы работы с базами данных в Excel

Определения.

Список – таблица с данными, разделенными на столбцы-поля  и строки-записи, содержащие однородные данные.

Например, база данных счетов или набор адресов и телефонов клиентов. Первая строка в этом списке содержит заголовки столбцов.

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

В базе данных Excel информация группируется по строкам. Каждая строка представляет собой запись - сведения об объекте, разнесенные по отдельным полям. 

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

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

1.1. Оформление базы данных

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

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

Рекомендации по созданию списка на листе книги

1.    ОРГАНИЗАЦИЯ СПИСКА

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

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

1.3.       Столбцы списка должны содержать однородную информацию (например, только даты, только текстовые строки).

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

1.5.       Располагать важные данные сверху или снизу от списка. Избегать размещения важных данных слева или справа от списка; данные могут быть скрыты при фильтрации списка.

1.6.       Отображение строк и столбцов. Перед внесением изменений в список убедитесь в том, что все скрытые строки и столбцы отображены. Если строки или столбцы списка скрыты, данные могут быть непреднамеренно удалены.

2.    ФОРМАТ СПИСКА

2.1.       Использование форматированных подписей столбцов. Создать подписи столбцов в первой строке списка. Microsoft Excel использует подписи при создании отчетов, поиске и оформлении данных. Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных. Перед вводом подписей столбцов ячейкам должен быть присвоен текстовый формат.

2.2.       Использование границ ячеек. Для отделения подписей от данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.

2.3.       Отсутствие пустых строк и столбцов. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

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

Создание БД:

1.    Создать заголовки для каждого поля в списке, задать выравнивание и отформатировать жирным шрифтом.

2.    Отформатировать находящиеся под заголовками ячейки в соответствии с данными, которые в них должны храниться (наложение числовых форматов – денежных или даты и т.п.).

3.    Ввести данные в строках под заголовками полей.

Примечание 1. Для дальнейшей работы со списком ему удобно дать зарезервированное имя База_данных.

1                    способ. Выделить диапазон, в контекстном меню выбрать Имя диапазона, в диалоговом коне задать, имя, область видимости, диапазон. 

2                    способ. Или в строке формул задать имя в одноименном поле. 3 способ. На Линейке Формулы группа Определенные имена.

Примечание. 

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

Office 2007.Для закрепления «шапки»  (заголовки столбцов) и «боковины» (определяющий столбец) надо:

1.    На линейке Вид в группе Окно  выбрать Разделить.

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

3.    Выбрать в списке Закрепить области одноименную команду.

Чтобы изменить или удалить закрепление надо в списке Закрепить области  выбрать команду  Снять закрепление областей.

Проверка данных при вводе

Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. Например, можно потребовать, чтобы в столбец Месяц можно было ввести только январские и февральские даты или чтобы сумма в долларах в столбце Объем продаж входила в определенный диапазон (скажем $0-5000). В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода.

Критерий правильности ввода для определенного диапазона ячеек задается следующим образом:

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

2.    На линейке Данные в группе Работа с данными команда Проверка данных.             

3.    На вкладке Параметры в области Условие проверки необходимо выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу).  

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

(рис.3).

                                                           Рис.3                                                                                 Рис.4

4.    На вкладке Сообщение для ввода (рис.4) можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.

5.    На вкладке Сообщение об ошибке (рис.5) можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое должно появляться при вводе в ячейку недопустимого значения. Из раскрывающегося списка Вид выбрать нужное значение: 

         Останов для блокировки ввода,          

Предупреждение  для выдачи предупреждения с

         возможностью продолжить ввод;         

Сообщение для выдачи сообщения с продолжением     Рис.5 ввода.

Примечание. Для визуального выделения неверных данных можно в команде Проверка данных выбрать команду Обвести неверные данные.

1.2. Структура

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

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

Например, в списке есть 3 столбца (А,В,С): Фамилия, Имя и Отчество. Предположим, что надо убирать с экрана и вновь восстанавливать столбцы с именем и отчеством (В и С). 

Для этого надо:

1.    Выделить столбцы В и С.

2.    Выполнить команду         

Office 2007 На линейке Данные в группе Структура команды Группировать.  

3.    Над столбцами появятся символы структуры. Щелкая по кнопкам с плюсом/минусом, можно скрывать и отображать столбцы В и С.

Примечания.

Убрать структуру можно командами         

Office 2007 На линейке Данные в группе Структура команды Разгруппировать.  

1.3. Сортировка элементов БД

Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов (cnhjr). Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).

1.    Выделить ячейку в списке, который требуется отсортировать. 

2.    Выполнить команду На линейке Данные в группе Сортировка и Фильтр команды Сортировка.  

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

Примечание 1. При необходимости можно добавить или убрать уровни сортировки. 

Примечание 2. Выбор порядка позволяет настраиваемый задать нестандартный порядок сортировки, например, по Север, Запад, Юг, Восток.

                                                                   Рис.6                                                                                 Рис.7

1.4. Фильтрация  БД Фильтрация - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке.

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

Фильтр - средство отображения только тех строк списка, которые соответствуют заданным условиям.

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

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

Microsoft Excel предоставляет две команды для фильтрации списков: 

§ Автофильтр, включая фильтр по выделенному, для простых условий отбора;  § Расширенный фильтр для более сложных условий отбора. 

1.4.1. Автофильтр

1.    На линейке  Данные  в группе  Сортировка и фильтр команда Фильтр.

2.    Нажать кнопку со стрелкой возле названия поля и выбрать значение или способ фильтрации. На экране появятся только те записи, которые содержат выбранный пункт.

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

§  имени поля (атрибута), 

§  варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); § слова или числа для сравнения. 

Сложное условие состоит из двух простых соединенных союзами И / ИЛИ (рис.9-10).

                                                      Рис.9                                                                                 Рис.10

1.4.2. Расширенный фильтр

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

Расширенный фильтр позволяет:

§  сразу копировать отфильтрованные записи в другое место того же самого рабочего листа;

§  сохранять критерий отбора для дальнейшего использования;

§  показывать в отфильтрованных записях не все столбцы, а только указанные;

§  объединять оператором ИЛИ условия для разных столбцов;

§  для одного столбца объединять операторами И,ИЛИ более двух условий; § создавать вычисляемые критерии; § выводить только уникальные значения.

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

                                                                        Рис.11                                                       Рис.12

2.    Ввести в строки под заголовками столбцов требуемые критерии отбора (рис.12).            

Например, При вводе условия Кот* будут отображены все слова начинающиеся на Кот, а при вводе условия >=2000 все числа, которые больше или равны 2000.

§  При составлении условий можно использовать подстановочные знаки (?,*,~). § Диапазону-критерию лучше всего дать какое-нибудь имя, например, Кр1.

3.    Выбрать одну из ячеек списка.

4.    На линейке Данные в группе Сортировка и Фильтр команда  Дополнительно (рис.13).

                                                   Рис.13                                                                                ^Рис.14                       

5.    В появившемся окне установить переключатель Обработка в одно из положений:

§  Фильтровать список на месте, если надо показать результат фильтрации, скрыв ненужные строки;

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

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

Примечание. Чтобы удалить расширенный фильтр необходимо выполнить команду Данные \ Фильтр \ Отобразить все.

Примеры сложных условий для расширенного фильтра

ПРАВИЛО: При записи составного условия простые условия записываются в одной строке, если они связаны логической операцией И и в разных, если ИЛИ.

Задачи №1. Составить список продавцов (без повторений).

Решение. 

Необходимо выбрать записи по критерию Продавец любой.

1.    В отдельной ячейке (например, А13) создать заголовок Продавец для отфильтрованного списка.

2.    Вызвать окно Расширенный фильтр, где выбрать параметры:  Обработка: Скопировать результаты в другое место  Диапазон условий: $В$1:$В$2. 

      Поместить результат в диапазон:  $А$13          

Включить: Только уникальные записи

Решение. 

1.    В отдельной ячейке (например, А13) создать заголовок Продавец для отфильтрованного списка.

2.    В ячейке А2 указать наименование товара, .

3.    Вызвать окно Расширенный фильтр, где выбрать параметры:          Обработка: Скопировать результаты в другое место 

§  ! Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое в дальнейшем будет указано в раскрывающемся списке При каждом изменении в.

§  Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду промежуточные итоги , а затем щелкнуть по кнопке Убрать все.

1.6. Работа в режиме структуры

Режим структуры, в котором оказывается список после выполнения команды Итоги, напоминает режим структуры в Word и позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис.25).

Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками + и -  предназначены для свертывания \ развертывания отельных групп.

                                                                            Рис.25                                                                                 Рис.26

Помимо группировки строк можно группировать столбцы (рис.26).

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

1.7. Функции для работы с Базой данных

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

Набор аргументов функций работы с БД одинаков:

База_данных - интервал ячеек, формирующих список или базу данных. 

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

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

Рассмотрим некоторые функции работы с БД:

БДПРОИЗВЕД (База_данных; Поле; Критерий) – перемножает числа в указанном столбце списка или базы данных, которые удовлетворяют заданным условиям.

БДСУММ (База_данных; Поле; Критерий) – суммирует числа в указанном столбце списка или базы данных, которые удовлетворяют заданным условиям.

БИЗВЛЕЧЬ (База_данных; Поле; Критерий) – Извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. 

Примечание. Если ни одна из записей не удовлетворяет критерию, то функция возвращает значение ошибки #ЗНАЧ!. Если более чем одна запись удовлетворяет критерию, то функция возвращает значение ошибки #ЧИСЛО!.

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

В большинстве случаев, когда речь идет об анализе списков, количество обрабатываемых значений (записей) известно заранее. Однако в ряде случаев количество обрабатываемых величин заранее неизвестно. Как подготовить лист для решения задач в таких случаях? 

Задача. Составить таблицу «Выплата премии сотрудникам» (см. рис). Премия начисляется как 20% от оклада. Максимальное количество сотрудников известно (25 человек).

Требования к оформлению:

§  Порядковые номера должны появляться автоматически после  введения фамилии в соответствующую ячейку той же строки. 

§  Фраза «Общая сумма» в ячейке С2 и значение общей суммы в D2 должны появляться только в том случае, если в списке есть хотя бы 1 запись.

§  Ячейки с начисленной премией в столбце D также заполняются автоматически как только будет введено очередное значение оклада в соответствующую ячейку столбца С.

§  Все ячейки с данными должны иметь границу черного цвета. 

Решение.

1.         Прежде всего предусмотреть ячейку для ввода количества величин (например, к-во сотрудников, кво единиц груза и т.п.).

2.         В некотором столбце, находящимся правее основной таблицы, например, в М, начиная с ячейки М6, записать порядковые номера сотрудников: 1,2,… до указанного максимального значения количества сотрудников (в ячейке D1).

3.         В ячейку А6 ввести формулу:              

=ЕСЛИ(И(ЕТЕКСТ(B6));M6>0;M6<=$D$1);M6;"")

Примечание. Можно было обойтись и без дополнительного столбца М. В этом случае надо в ячейку А6 записать формулу: =ЕСЛИ(ЕТЕКСТ(B6));1;"")

          а в А7 формулу:                                =ЕСЛИ(И(ЕЧИСЛО(A6);A6<$D$1;ЕТЕКСТ(B7)));A6+1;"")

4.         Скопировать (распространить) введенную формулу вниз на остальные ячейки столбца А (общее число ячеек с формулой должно быть равно максимально возможному количеству сотрудников).

5.         В ячейку С2 ввести формулу: 

=ЕСЛИ(ЕЧИСЛО(D6);"Общая сумма";"")

6.         В ячейку D2 ввести формулу, суммирующую все значения, которые могут быть указаны в столбце D в графе Премия. 

=ЕСЛИ(ЕЧИСЛО(D6);СУММ(D6:D30);"")

7.         В ячейку D6 ввести формулу: 

=ЕСЛИ(ЕЧИСЛО(C6);C6*20%;"")

8.         Скопировать      (распространить)      введенную формулу вниз на остальные ячейки столбца D (общее число ячеек с формулой должно быть равно максимально возможному количеству сотрудников).

9.         Спрятать столбец М. 

10.     Выделить ячейки А5:D30. Задать условное форматирование, указав в качестве условия:

Формула =А5<>””  

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

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

Отчет сводной таблицы – интерактивная таблица, с помощью которой можно быстро объединять и сравнивать большие объемы данных. Отчет содержит итоговые данные и выполняет анализ таких данных, как записи базы данных из разных источников, в том числе внешних по отношению к Microsoft Excel.

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

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

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

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

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

Отчеты сводной таблицы содержат поля строк, столбцов, страниц и данных. 

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

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

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

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

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

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

Способ организации данных

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

В приведенном примере столбец «Спорт» становится полем «Спорт», а все записи «Футбол» суммируются в одном элементе «Футбол».

Поле данных, например, поле суммарных объемов продаж, содержит суммируемые значения. Ячейка F3 на приведенном выше примере содержит сумму значений по столбцу

«Продажи» для строк исходных данных, которые содержат в  столбце «Спорт» значение «Футбол», а в столбце «Квар-

тал» — значение «Кв3».

2.1. Исходные данные сводной таблицы и сводной диаграммы

При создании сводной таблицы или отчета сводной диаграммы можно использовать различные типы исходных данных:

1. Списки или базы данных Microsoft Excel.         

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

Примечание. 

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

§  Чтобы отчет было легче обновлять, надо присвоить имя исходному диапазону и использовать его имя при создании отчета. 

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

§  Чтобы создать отчет, содержащий только фильтрованные данные, надо использовать  команду Расширенный фильтр для извлечения данных в другое место листа, а затем создать  отчет. Обычный Фильтр Excel игнорирует.

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

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

Примечание. С помощью Мастера сводных таблиц можно получать любые данные, а также создавать источники данных, запускать имеющиеся запросы и создавать новые. 3. Несколько диапазонов консолидации. 

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

Примечание.

§  ?? При запуске мастера сводных таблиц установить переключатель в положение в нескольких диапазонах консолидации.

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

§  Каждому исходному диапазону желательно присвоить имя и использовать его при создании отчета.

§  При консолидации используются дополнительные поля страниц, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, если консолидируются бухгалтерские данные по нескольким отделам («Маркетинг», «Сбыт» и «Производство»), в поле страницы могут находиться элементы, представляющие каждый отдел и их комбинацию. 4. Другой отчет сводной таблицы. 

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

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

§  Исходная таблица не может содержать поля страниц, в которых устанавливается запрос к внешним данным при выборе элемента поля.

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

§  Отчет сводной таблицы или сводной диаграммы можно создать на основе отчета другого отчета сводной таблицы, но не на основе другого отчета сводной диаграммы. Тем не менее, связанный отчет сводной таблицы создается из тех же данных, что и отчет сводной диаграммы, поэтому новый отчет можно создавать на основе связанного отчета. Изменения в отчете сводной диаграммы влияют на связанный отчет сводной таблицы, и наоборот.

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

1.    Открыть книгу, в которой требуется создать отчет сводной таблицы. 

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

3.    На шаге 1 выполнения мастера сводных таблиц и диаграмм установить переключатель Вид создаваемого отчета в положение Сводная таблица. 

4.    На следующих этапах (2-3 шаг) необходимо следовать  инструкциям мастера. 

5.    После чего выбрать способ создания отчета: вручную или с помощью мастера.  

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

1 способ: Создание отчета вручную

1.    Из окна Список полей сводной таблицы перетащить поля с данными, которые требуется отобразить в строках, в область перетаскивания с надписью Перетащите сюда поля строк.  

      Примечания.          

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

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

2.    Поля с данными, которые должны отображаться в столбцах, перетащить в область перетаскивания с надписью Перетащите сюда поля столбцов. 

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

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

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

4.    Поля, которые требуется использовать как поля страниц, перетащить в область с надписью Перетащите сюда поля страниц. 

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

2 способ: Создание отчета с помощью мастера

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

1.      На 3 шаге мастера нажать кнопку Макет (в некоторых версиях появляется автоматически).

2.      Из группы кнопок полей, расположенной справа, перетащить нужные поля в области диаграммы СТРОКА и СТОЛБЕЦ. 

3.      Поля, содержащие данные, которые требуется обобщить, перетащить в область ДАННЫЕ. 

4.      Поля, которые требуется использовать как поля страниц, перетащить в область СТРАНИЦА.   Если требуется постраничное получение данных для работы с большим количеством исходных данных, надо дважды щелкнуть кнопкой мыши поле страницы и нажать кнопку Дополнительные, выбрать параметр Обновлять внешние данные при выборе каждого элемента, а затем дважды нажать (этот параметр недоступен для некоторых видов исходных данных). 

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

6.      Чтобы удалить поле, надо перетащите его за пределы диаграммы. 

7.      Получив нужный макет, нажать кнопку OK, а затем кнопку Готово. 

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

2.3. Отчеты сводных диаграмм

Отчет сводной диаграммы позволяет отображать данные в графическом представлении в отчете сводной таблицы. Макет и данные, отображаемые в отчете сводной диаграммы, можно изменять таким же образом, как и в отчете сводной таблицы.

Элементы отчета сводных диаграмм (термины и определения)

Аналогичные обычным диаграммам

1.    Ряд данных - набор связанных между собой элементов данных, соответствующий одному столбцу или одной строке таблицы данных. Каждому ряду данных на диаграмме соответствует отдельный цвет и/или способ обозначения. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.

2.    Значения.

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

4.    Ось. Линия, ограничивающая область построения диаграммы, используемая как система координат в выбранных единицах измерения. Ось Y обычно расположена вертикально, а вдоль нее строятся данные. Ось X обычно расположена горизонтально, а вдоль нее строятся категории

Специализированные элементы:

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

6.    Поле данных - поле из текущего источника данных, который предоставляет значения для сравнения или изменения. ((например,  «Сумма продаж» — это поле данных, которое обобщает данные по квартальным продажам в каждом регионе по каждому виду спорта. Маркер данных первой категории («Кв.1») почти достигает отметки 250 на оси значений (y). Это значение является суммой продаж товаров для занятий теннисом, сафари и гольфом в первом квартале)). 

В зависимости от источника данных, используемых в отчете, можно изменять итоговую функцию на СРЕДНЕЕ, СЧЁТ, ПРОИЗВЕД или другие вычисления.

7.    Поле ряда - поле, которому присваивается ориентация ряда в отчете сводной диаграммы. Элементы в поле содержат отдельные ряды дан-

ных ((например,  «Спорт» является полем ряда, содержащим 2 элемента: «Футбол», «Баскетбол»)).

8.    Элемент - представляет уникальные значения в поле и появляется в раскрывающихся списках в полях страницы, категории и рядов. ((например, «Футбол», «Баскетбол» являются элементами поля ряда «Спорт»)).

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

Представление данных в отчете сводной диаграммы

Отчет сводной диаграммы всегда имеет связанный с ним отчет сводной таблицы. Оба типа отчетов имеют соответствующие друг другу поля. Изменение положения поля в одном отчете вызывает изменение положения соответствующего поля в другом отчете.

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

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

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

Большинство операций аналогичны операциям отчета сводной диаграммы. Однако существует и ряд отличий.

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

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

Создание диаграммы. Для создания обычной диаграммы в Microsoft Excel используется мастер диаграмм. Для создания отчета сводной диаграммы можно воспользоваться мастером диаграмм. Если уже имеется отчет сводной таблицы, служащий исходными данными для отчета сводной диаграммы можно воспользоваться мастером сводных таблиц и сводных диаграмм.

Исходные данные. Обычные диаграммы связаны непосредственно с ячейками листа. Сводные диаграммы могут быть основаны на нескольких различных типах данных, включая: списки Microsoft Excel; базы данных; данные, находящиеся в нескольких диапазонах консолидации; и внешние источники (например, базы данных Microsoft Access и базы данных OLAP).

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

Форматирование.   Некоторые параметры форматирования теряются после изменения макета или обновления отчета сводной диаграммы. ((Эти параметры форматирования включают линии тренда (Линия тренда.

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

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

Создание отчета сводной диаграммы

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

Поле строки - поле, которому в отчете сводной таблицы соответствуют строки. Элементы, связанные с полем строки, отображаются в виде подписей строк.)

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

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

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

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

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

3. Консолидация

Термины и определения

Консолидация- обобщение информации, при котором  объединяются значения из нескольких диапазонов, рабочих листов или книг одинаковых по структуре.

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

Трехмерная ссылка - ссылка на диапазон, включающий более одного листа книги.

Итоговая (весовая) функция -  тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных (например, СУММ, СЧЕТ, СРЕДНЕЕ и т.п.).

Консолидировать данные в Microsoft Excel можно несколькими способами:

1 способ. Использование трехмерных формул.

Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

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

Например, формула в ячейке A2 складывает три числа, расположенные в различных местах трех разных листов.

Если все исходные листы имеют одинаковую разметку, в трехмерных формулах можно использовать диапазон имен листов. 

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

2  способ. Консолидация по расположению.

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

3  способ. Консолидация по категории.

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

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

Другие способы объединения данных.

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

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

3.1. Консолидация

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

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

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

2.      Указать ячейку, в которую следует поместить данные консолидации. 

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

Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, надо ввести формулу: 

=СУММ(Лист2:Лист7!B3). 

Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, то ввести формулу в формате 

=СУММ(Лист3!B4; Лист4!A7; Лист5!C5). 

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

1.      Настроить данные для консолидации, т.е.:

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

§  разместить каждый диапазон на отдельном листе (на лист, на котором должна выполняться консолидация, диапазоны лучше не помещать);

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

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

2.      Щелкнуть левый верхний угол области, в которой требуется разместить консолидированные данные. 

3.      Выбрать команду Данные \ Консолидация. 

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

5.      Щелкнуть поле Ссылка, открыть лист, содержащий первый диапазон данных для консолидации, ввести имя этого диапазона и нажать кнопку Добавить. Аналогично ввести все другие диапазоны. 

6.      Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, и позже точно не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установить флажок Создавать связи с исходными данными. 

7.      Если консолидация выполняется по положению, оставить все поля в группе Использовать в качестве имен пустыми. В Microsoft Excel подписи исходных строк и столбцов не копируются в консолидированные данные. Если требуется скопировать подписи в консолидированные данные, надо сделать это вручную.  

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

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

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

Если была использована команда Данные \ Консолидация, то изменить консолидацию можно, выполнив следующие действия: 

1.      Щелкнуть верхнюю левую ячейку консолидированных данных. 

2.      Данные \ Консолидация.           

Консолидацию изменить невозможно, если был установлен флажок Создавать связи с исходными данными. Если флажок установлен, надо нажать кнопку Закрыть и создать новую консолидацию. 

3.      Выполнить одно или несколько следующих действий:

3.1. Добавить диапазон данных в консолидируемые данные. 

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

§   Щелкнуть поле Ссылка. 

§   Для консолидации щелкнуть вкладку листа первого диапазона. 

§   Ввести имя диапазона или выбрать диапазон из списка. 

§   Нажать кнопку Добавить. 

3.2. Изменить размер или формы диапазона данных

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

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

§   Нажать кнопку Добавить. 

3.3. Удалить диапазон данных и консолидации

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

§   Нажать кнопку Удалить. 

      Автоматически обновить консолидацию.        

Установить флажок Создавать связи с исходными данными.

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