И.В. Кандинская Информационные системы в экономике

20

только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база_данных; поле; критерий). Необходимо обратить внимание на правила обращения к функциям баз данных:

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

2)второй аргумент задает столбец, элементы которого необходимо просуммировать, усреднить и т.п.;

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

4)третий аргумент задает интервал критериев аналогично интервалу критериев расширенного фильтра.

Вот некоторые из этих функций:

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

2.ДСРЗНАЧ(база_данных; поле; критерий) − возвращает среднее

значение выбранных фрагментов базы данных.

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

Рис. 22. Пример использования функции баз данных

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

4.БСЧЕТ(база_данных; поле; критерий) − подсчитывает количе-

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

Рис. 23. Результат вычислений по формуле рис. 22

21

5.ДМАКС(база_данных; поле; критерий) − возвращает макси-

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

6.ДМИН(база_данных; поле; критерий) − возвращает минималь-

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

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

Рис. 24. Пример использования функций баз данных

Рис. 25. Результаты вычислений по формулам рис. 24

3.8. Промежуточные итоги

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

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

Для вставки итогов выбирается команда меню Данные/Итоги. В появившемся диалоговом окне (рис. 26) необходимо выбрать:

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

22

столбце подсчет итогов инициируется вновь (это столбец, по которому проводилась сортировка);

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

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

–чтобы итоги отображались над строками данных, а не под ними,

снять флажок Итоги под данными;

–нажать кнопку .

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

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

Для удаления итогов необходимо:

–установив курсор внутрь списка, содержащего итоги, выбрать ко-

манду меню Данные/Итоги;

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

23

Рассмотрим использование Промежуточных итогов на примере подсчета количества студентов, обучающихся в различных группах, и среднего по группам возраста. Исходные данные – список на рабочем листе Excel, представленный на рис. 2. Для решения данной задачи необходимо выполнить следующие действия:

1)отсортировать список по полю ГРУППА;

2)выбрать команду меню Данные/Итоги. В результате на экране появится окно диалога (рис. 26). В раскрывающихся списках выбрать:

–При каждом изменении в – поле ГРУППА,

–Операция – Количество,

–Добавить итоги по – поле ГРУППА;

3)нажать кнопку .

Врезультате выполненных действий исходный список студентов на рабочем листе Excel примет вид, представленный на рис. 27.

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

24

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

–При каждом изменении в – поле ГРУППА;

–Операция – Среднее;

–Добавить итоги по – поле ВОЗРАСТ;

–снять флажок Заменить текущие итоги;

–нажать кнопку .

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

с этой копией. Обратите вни-

мание на то, что одни и те же задачи можно

решать различными

Рис. 28. Рабочий лист с итоговыми данными средствами. Посчи-

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

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

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

25

щью промежуточных итогов.

Так же, как и в промежуточных итогах, в сводных таблицах возможно получение автоматически вычисленных значений по группе элементов списка. В сводной таблице тип вычислительной операции называется Вычисление поля сводной таблицы (рис. 33), по сути – это полная аналогия с полем Операция в промежуточных итогах (рис. 26). Но в отличие от промежуточных итогов в сводной таблице нет необходимости в предварительной сортировке данных. А самое существенное отличие состоит в том, что сгруппированные данные могут располагаться не только по строкам, как у промежуточных итогов, но и по столбцам.

Для создания сводной таблицы в Excel предусмотрен Мастер сводных таблиц. Вызов Мастера сводных таблиц осуществляется вызовом команды меню Данные/Сводная таблица.

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

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

• использование списка (базы данных

Excel). Можно ис-

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

подписи столбцов, ос-

Рис. 29. Шаг 1 Мастера сводных таблиц тальные строки долж-

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

26

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

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

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

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

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

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

27

[имя_книги]имя_листа!диапазон_ячеек. Если предварительно установить курсор в ячейку списка, для которого строится сводная таблица, интервал ячеек будет опознан автоматически. Для ссылки на интервал другой (закрытой) рабочей книги нажимается кнопка Обзор, в одноименном диалоговом окне выбирается диск, каталог и файл закрытой рабочей книги, вводится имя рабочего листа и диапазон ячеек либо имя блока ячеек.

		Этап		3.
	Выбор		области
	для		вставки
	сводной		табли-
	цы. На		данном
	этапе	решается
	вопрос		о	том,
Рис. 31. Шаг 3 Мастера сводных таблиц	куда	поместить
	будущую			свод-

ную таблицу: на новый лист или уже существующий (рис. 31).

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

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

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

28

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

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

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

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

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

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

29

логового окна

Вычисление поля сводной таблицы

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

нительно вызы-

вает диалоговое

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

окно Дополнительные вычисле-

ния (рис. 34) для выбора функций, список которых приведен в табл. 2. При использовании функции сравне-

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