А.Г. Пимонов Информационные системы в экономике (Часть I)

20

необходимо выдать список студентов, родившихся под знаком зодиака «Водолей» (с 21.01 по 19.02). Интервал критериев для реализации этого запроса приведен на рис. 20, а полученная выборка − на рис. 21.

8.ДНЕЙ360(нач_дата; кон_дата; метод) − вычисляет количество дней между двумя датами на основе 360-дневного года (12 месяцев по 30 дней).

3.7.Функции баз данных

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

1.СЧЁТЕСЛИ(интервал; критерий) − возвращает количество яче-

ек в интервале, которые удовлетворяют критерию.

Например, подсчитать число студенток в списке можно по формуле

=СЧЁТЕСЛИ(F2:F26; “Ж”).

2.СУММЕСЛИ(интервал; критерий; интервал_суммирования)

− возвращает сумму значений в ячейках из интервала суммирования, отфильтрованных в соответствии с критерием, применяемым к

интервалу.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

	Excel изменяет разметку списка, что по-
	зволяет отображать и скрывать строки
	каждого промежуточного итога.
	Перед вставкой промежуточных
	итогов с целью группировки строк, для
	которых планируется подвести итоги,
	список необходимо отсортировать (см.
	п. 3.3) по соответствующему полю.
	Для вставки итогов выбирается
	команда меню Данные/Итоги. В поя-
Рис. 26. Диалоговое окно	вившемся диалоговом окне (рис. 26) не-
вставки Промежуточных итогов	обходимо выбрать:

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

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

23

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

24

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

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

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

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

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

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

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

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

25

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

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

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

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

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

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

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

4. ЗАДАНИЕ ДЛЯ ВЫПОЛНЕНИЯ КОНТРОЛЬНОЙ РАБОТЫ

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

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

26

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

Отчет по контрольной работе должен состоять из следующих разделов:

1)описание предметной области и структура списка (см. п.3.2, табл. 1);

2)собственно список Excel (см. п.3.2, рис. 2);

3)описание реализованного с помощью автофильтра запроса:

) постановка задачи (запрос) на поиск информации (см. п.3.4); ) результаты фильтрации (см. рис. 5, 7);

4)описание реализованного с помощью расширенного фильтра запроса: ) постановка задачи (запрос) на поиск информации (см. п.3.4–3.6); ) критерии отбора информации (см. рис. 10, 12, 14, 16, 18, 20); ) результаты фильтрации (см. рис. 11, 13, 15, 17, 19, 21);

5)описание задачи, решенной с использованием функции баз данных:

) постановка задачи (см. п.3.7); ) интервал критериев и формула для решения задачи (см. рис.22, 24);

) результат вычислений (см. рис. 23, 25); 6) описание задачи, для которой были использованы промежуточные итоги:

) постановка задачи (см. п.3.8); ) таблица с итоговыми данными (см. рис. 27, 28).

Отчет должен быть подготовлен с помощью текстового процессора Word и представлен для проверки до сессии в традиционной форме (в виде твердой копии) на листах бумаги. Дополнительно отчет может быть представлен на электронном носителе информации (дискете или переслан по электронной почте) в виде двух файлов: документа Word и рабочей книги Excel, содержащей подготовленный список, критерии и формулы для реали-

27

зации запросов и решения задач, выборки, итоговые таблицы.

СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

1.Петров В.Н. Информационные системы.– СПб.: Питер, 2002.– 688 с.

2.Проектирование экономических информационных систем: Учеб./ Г.Н. Смирнова, А.А. Сорокин, Ю.Ф. Тельнов; Под ред. Ю.Ф. Тельнова.– М.: Финансы и статистика, 2002.– 512 с.

3.Информационные технологии (для экономиста): Учеб. пособие/ Под общ. ред. А.К. Волкова.– М.: ИНФРА–М, 2001.– 310 с.

4.Карпова Т. Базы данных: модели, разработка, реализация.– СПб.:

Питер, 2001.– 304 с.

5.Дейт К.Дж. Введение в системы баз данных.– 6-е изд.: Пер. с англ.– Киев, М., СПб.: Издательский дом «Вильямс», 1999.– 848 с.

6.Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах.– СПб.: BHV – Санкт-Петербург, 2001.– 816 с.

7.Биллиг В.А. VBA и Office 97. Офисное программирование/ В.А. Биллиг, М.И. Дехтярь.– М.: Издательский отдел «Русская Редакция» ТОО «Channel Trading Ltd.», 1998.– 720 с.

8.Робинсон С. Microsoft Access 2000: учебный курс.– СПб.: Питер, 2001.– 512 с.

WEB–РЕСУРСЫ

1.http://www.citforum.ru − сервер информационных технологий, содержащий «море(!)» аналитической информации.

2.http://www.microsoft.com/rus/office − информация обо всех компонентах пакета Microsoft Office.

3.http://www.firststeps.ru/msoffice/excel1.html − первые шаги − знаком-

ство с основными возможностями MS Excel.

4.http://www.vtit.kuzstu.ru – сервер кафедры вычислительной техники

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

Составители Александр Григорьевич Пимонов Ирина Владимировна Кандинская

Юлия Викторовна Бегаева Владимир Григорьевич Левин

ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ (Часть I)

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

Редактор Е.Л. Наркевич

Подписано в печать 01.02.03.

Формат 60×84/16. Бумага офсетная. Отпечатано на ризографе. Уч.-изд. л. 1,65. Тираж 1400 экз. Заказ .

ГУ КузГТУ. 650026, Кемерово, ул. Весенняя, 28.

Типография ГУ КузГТУ. 650099, Кемерово, ул. Д. Бедного, 4А.