А.Г. Пимонов Информационные системы в экономике (Часть I). Министерство образования российской федерации государственное образовательное учреждение высшего профессионального образования
 Скачать 0.49 Mb.
|
МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ «Кузбасский государственный технический университет» Кафедра вычислительной техники и информационных технологий ИНФОРМАЦИОННЫЕ СИСТЕМЫ В ЭКОНОМИКЕ (ЧАСТЬ I) Программа дисциплины, методические указания и контрольное задание для студентов экономических специальностей заочной формы обучения (ускоренная подготовка на базе среднего специального образования) Составители А.Г. Пимонов И.В. Кандинская Ю.В. Бегаева В.Г. Левин Утверждены на заседании кафедры Протокол № 6 от 29 января 2003 г. Рекомендованы к печати учебно-методической комиссией специальности 060400 Протокол № 6 от 25 февраля 2003 г. Электронная копия хранится в библиотеке главного корпуса ГУ КузГТУ КЕМЕРОВО 2003 1 1. ПРОГРАММА 1.1. Цели и задачи курса Цели курса. Формирование у студентов представлений об общих принципах построения, функционирования и развития информационных систем, о роли информационных систем и средств вычислительной техники в решении задач сбора, передачи, хранения и обработки экономической ин- формации, а также овладение приёмами работы с современными программ- ными средствами, обеспечивающими широкие возможности обработки не только экономической, но и любой другой информации. Задачи курса. Обучение студентов основам проектирования автомати- зированных информационных систем, базирующихся на современных тех- нических и программных средствах. В рамках курса изучаются возможности технологии баз данных при разработке автоматизированных информацион- ных систем в экономике, возможности реализации баз данных средствами табличных процессоров (электронных таблиц), назначение и основные воз- можности систем управления базами данных. 1.2. Содержание курса В результате изучения дисциплины студент должен свободно ориен- тироваться во всех темах предлагаемого курса. Введение Предмет и задачи дисциплины «Информационные системы в экономи- ке». Определение информационных систем, систем обработки данных, сис- тем управления. Информационные системы и технологии. Понятие автома- тизированных и автоматических систем и процессов. Тема 1. Информационные ресурсы и виды информационных сис- тем Информационные ресурсы общества и предприятия. Виды и формы информационного обеспечения. Классификация информационных систем. Фактографические, документальные, документально-фактографические ин- 2 формационные системы. Системы обработки данных и автоматизированные информационные системы. Методология и технология разработки информа- ционных систем. Жизненный цикл информационных систем. Принципы по- строения автоматизированных систем управления. Информационно- поисковые системы и информационно-поисковые языки. Информационные системы специального назначения. Поисковые системы Internet. Интегриро- ванные информационные системы. Тема 2. Основы технологии автоматизированных баз данных Понятие автоматизированной базы данных (АБД). Система баз дан- ных. Предметная область АБД. Этапы проектирования и создания АБД. Мо- дели данных: иерархическая, сетевая, реляционная. Нормализация отноше- ний. Первая, вторая и третья нормальные формы. Системы управления ба- зами данных (СУБД). Функции СУБД. Язык структурированных запросов SQL (Structured Query Language). Виды СУБД: реляционные, дедуктивные, экспертные, расширяемые, объектно-ориентированные, семантические, уни- версальные реляционные. Архитектура СУБД: внешний, концептуальный, внутренний уровни. Архитектура клиент/сервер и распределённая обработка данных. Серверы баз данных. Администрирование базами данных. История и перспективы развития, обзор рынка СУБД. Примеры реляционных СУБД (dBase, FoxPro, Paradox, Oracle, Access и т.д.). Тема 3. Списки (базы данных) Excel Определение списка (базы данных) как таблицы (отношения) Excel. Требования к структуре списка. Ограничения на заголовки списка. Имено- вание списка. Записи-строки (кортежи), поля-столбцы (атрибуты или доме- ны). Вычисляемые поля. Создание базы данных. Импорт-экспорт файлов баз данных FoxPro и таблиц Access. Редактирование (изменение, добавление, удаление записей) списков с помощью стандартной экранной формы. Сор- тировка списков. Использование стандартной формы для поиска данных. Анализ списков с помощью фильтров. Автофильтр. Использование симво- лов шаблона. Расширенный (усиленный) фильтр. Диапазон условий (интер- вал критериев). Критерии с тремя и большим количеством условий. Тексто- 3 вые и вычисляемые критерии. Подведение итогов. Сводные таблицы. Функ- ции рабочего листа для анализа списков. Тема 4. Базы данных Access Структура базы данных в Microsoft Access. Нормализация данных и определение связи между полученными в результате нормализации инфор- мационными объектами (реляционными таблицами). Типы связи информа- ционных объектов (одно – однозначные (1:1), одно – многозначные (1:М), много – многозначные (M:N)). Создание новой базы данных в Microsoft Access. Режимы создания таблиц (режим таблицы, конструктор, мастер таб- лиц). Создание таблиц в режиме конструктора. Типы и свойства полей таб- лицы. Подстановка. Виды подстановки. Определение первичного ключа. Непосредственный ввод данных в таблицу. Схема данных в Access и созда- ние взаимосвязи таблиц. Обеспечение целостности данных. Автоматизиро- ванная нормализация таблиц с помощью мастера анализа таблиц. Форма как один из основных инструментов для просмотра, корректировки и ввода дан- ных. Виды форм (однотабличные и многотабличные) и способы их создания (конструктор, мастер форм, автоформы). Использование мастера форм для создания многотабличных форм с включением подчиненных и связанных форм и конструктора для доработки ранее созданных форм. Поиск, сорти- ровка и отбор записей с помощью фильтра. Запросы. Назначение и виды за- просов. Способы создания запросов. Вычисляемые поля и использование групповых операций. Разработка отчетов. Создание одно- и многотаблич- ных отчетов с помощью мастера и их редактирование с помощью конструк- тора. Разработка, корректировка и включение подчиненного отчета в много- табличный отчет. Включение вычисляемых полей. Просмотр и печать отче- тов. 2. ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ И СИСТЕМЫ Информационные технологии (ИТ) – это сочетание принципиально новых средств и методов (алгоритмов), обеспечивающих функции сбора, передачи, накопления, обработки и анализа информации в организационной 4 структуре с использованием всего многообразия средств вычислительной техники и связи. Любая система – это, прежде всего, средство достижения цели. Существуют различные точки зрения на определение информацион- ных систем (ИС). ИС – это совокупность программных средств, с помощью которых осуществляются функции сбора, передачи, накопления, обработки и анализа информации с целью реализации управленческих функций. Соз- дание ИС без использования ИТ немыслимо. Информационная технология базируется на техническом, программ- ном, информационном, методическом и организационном обеспечении (рис.1). В качестве критериев классификации ИТ можно использовать поль- зовательский интерфейс (совокупность способов взаимодействия с компью- тером), реализованный в операционной системе, тип обрабатываемой ин- формации. Существуют различные подходы и к классификации информаци- онных систем. По сфере применения ИС классифицируются следующим об- разом: 1) ИС для научных исследований; 2) ИС автоматизированного проектирования; 3) ИС организационного управления (офисные ИС); 4) ИС управления технологическими процессами; 5) информационно–справочные системы. По масштабу ИС можно разделить на следующие группы: 1) одиночные; 2) групповые; 3) корпоративные. Среди групповых и корпоративных систем по способу организации различают следующие: 1) ИС на основе архитектуры файл–сервер; 2) ИС на основе архитектуры клиент–сервер; 3) ИС на основе многоуровневой архитектуры; 4) ИС на основе интернет/интранет технологий. 5 Предметная область Функциональные процессы Промышленное предприятие, его подразделения, службы и т.п. Производство товаров, приобретение сырья, прием-увольнение сотрудников и т.п. Модели предметной области ИНФОРМАЦИОННАЯ ТЕХНОЛОГИЯ Опорная технология База знаний Аппаратные средства Системное инструментальное ПО БАЗА ДАННЫХ Пользовательский интерфейс Информационно- технологические процессы Рис 1 Структура автоматизированной информационной технологии 3. МЕТОДИЧЕСКИЕ УКАЗАНИЯ К КОНТРОЛЬНОЙ РАБОТЕ «БАЗЫ ДАННЫХ EXCEL» Технология автоматизированных баз данных (АБД) – неотъемлемая составляющая любой ИТ (рис. 1), а собственно АБД входит в состав любой ИС. Классическим определением автоматизированной базы данных является следующее. АБД по своей физической сущности есть не что иное, как сово- купность взаимосвязанных файлов, содержащих структурированную ин- формацию о той или иной предметной области деятельности человека. В за- висимости от модели данных, используемой для хранения информации в ба- зе, принято различать иерархические, сетевые и реляционные БД. Получив- шие наибольшее распространение реляционные базы данных представляют 6 собой совокупность таблиц, которые могут храниться в виде отдельных файлов. Таблицы естественны для Excel – программы, предназначенной для решения задач, информация (входная и выходная) для которых представима в табличном виде. Excel располагает встроенными средствами поиска, отбо- ра и сортировки данных в таблицах. Поэтому при небольших объемах дан- ных, когда нет необходимости в сложных запросах, требующих одновре- менной работы с несколькими таблицами, работать с базами в Excel просто, быстро и удобно. Базы данных в Excel принято называть списками. 3.1. Списки Excel как база данных Списком Excel называется таблица, оформление которой отвечает сле- дующим требованиям: 1) список состоит из строк, называемых записями; 2) столбцы списка, называемые полями, должны содержать однородную (однотипную) информацию; 3) верхняя строка списка, называемая заголовком, должна содержать мет- ки (имена) соответствующих полей, и ее формат (шрифт, цвет фона и т.п.) должен отличаться от формата записей; 4) внутри списка не должно быть пустых строк и столбцов, которыми список отделяется от остальной части рабочего листа; 5) не рекомендуется на рабочем листе располагать еще что-либо, кроме списка, но если что-то и присутствует, то должно быть расположено либо выше, либо ниже списка, и рабочий лист рекомендуется именовать назва- нием списка. В общем случае база данных может состоять из нескольких списков, количество которых сверху ограничено только здравым смыслом и опытом разработчика. 3.2. Создание списков Первый, наиболее важный шаг при создании базы данных – это разра- ботка хорошо продуманной структуры (организации хранения данных), ко- 7 торая и определяет возможности будущей обработки информации. Структу- ра списка определяется структурой одинаково организованных записей, его составляющих. Под структурой записи принято понимать совокупность ее полей (их имена, типы, назначение). Пусть, например, мы хотим создать список, содержащий информацию о студентах, обучающихся в Междуре- ченском филиале ГУ КузГТУ. Структура такого списка может быть доста- точно простой (табл. 1). Таблица 1 Структура списка студентов В ячейках списка чаще всего хранятся константы соответствующих основных типов: числовые, текстовые, даты и времени. Кроме того, допус- тимо использование так называемых вычисляемых полей, содержащих фор- мулы. В нашем примере (рис. 2) это возраст. В качестве вычисляемого поля № п/п Имя поля Тип поля Назначение Комментарий 1 ФАМИЛИЯ Текстовое 2 ИМЯ Текстовое 3 ОТЧЕСТВО Текстовое Эти поля предназначе- ны для хра- нения Ф.И.О. студента Строки состоят из букв кирил- лицы без ведущих и хвостовых пробелов, первый символ – про- писная буква 4 ГРУППА Текстовое Название группы Пятисимвольная строка: первые два символа – специальность, третий – форма обучения, по- следние два – год поступления 5 СТУДЕН- ЧЕСКИЙ Числовое Номер сту- денческого билета Шестизначное число: первая цифра – номер филиала, две следующих – год поступления, три последних – собственно но- мер 6 ПОЛ Текстовое Пол студента Односимвольная строка: буквы «м» или «ж» 7 ДАТА РОЖ- ДЕНИЯ Дата Дата рожде- ния студента Например «23.11.59» 8 ВОЗРАСТ Вычис- ляемое Возраст в го- дах Вычисляется по формуле: (текущая дата–дата рожде- ния)/365, т.е. =(СЕГОДНЯ()– G2)/365 8 могут выступать в зависимости от предметной области, например, стои- мость партии товара, время до истечения срока годности продукта, стои- мость услуги в у.е. и т.п. При формировании вычисляемых полей необходи- мо придерживаться двух основных правил: 1) ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными; 2) ссылки на ячейки вне списка должны быть абсолютными. Заполняется информацией список, как правило, вручную. Кроме того, основные действия со списком позволяет выполнять стандартная экранная форма (рис. 3), которая активизируется с помощью пункта меню Дан- ные/Форма. К основным действиям по обработке списков относятся сле- Рис .2. Список студентов Междуреченского филиала ГУ КузГТУ 9 дующие: добавление, удаление, редактирование, просмотр и поиск записей. При работе со списком перед обращением к команде меню Данные в обяза- тельном порядке необходимо активизиро- вать любую ячейку внутри списка. В этом случае Excel автоматически распознает интервал списка. Таблицы баз данных Ac- cess на рабочие листы Excel можно пере- нести и через буфер обмена. Вместе с тем Excel имеет специальные достаточно раз- витые возможности экспорта и импорта файлов баз данных, созданных другими средствами. Доступ к этим возможностям реализуется с помощью команд меню Файл/Сохранить как (Открыть), ли- бо Данные/Внешние данные. При этом драйверы Microsoft Office позволяют получать сведения из следующих источников данных: 1) Microsoft SQL Server OLAP Services (поставщик данных OLAP); 2) Microsoft Access; 3) dBASE; 4) Microsoft FoxPro; 5) Oracle; 6) Paradox; 7) SQL Server; 8) текстовые файлы баз данных. Внешние данные могут быть получены из баз данных с помощью Mi- crosoft Visual Basic, Web-запросов и Microsoft Query. Чаще всего настройку источников данных для Excel осуществляют посредством Microsoft Query – инструмента, предназначенного для установления подключения к внешним базам данных и создания запросов для извлечения данных. Microsoft Query входит в состав дополнительных компонентов Microsoft Office. Рис. 3. Стандартная экранная форма 10 3.3. Сортировка списков Под сортировкой списка, как и любого другого набора объектов, при- нято понимать расположение его записей в определенном порядке. Записи можно располагать в порядке возрастания-убывания числовых полей, в ал- фавитном (обратном алфавитному) порядке текстовых полей, в хронологи- ческом порядке полей типа «дата и время». Поле, по которому производится сортировка, называется ключевым полем, или ключом сортировки. Возмож- ности сортировки реализуются с помощью кнопок Сортировка по возрас- танию и Сортировка по убыванию панели инструментов Стандарт- ная, либо через команду меню Данные/Сортировка, которая позволяет от- сортировать список за один прием максимум по трем полям (первичный ключ, вторичный и т.д.). В случае необходимости можно произвести сорти- ровку и более, чем по трем столбцам. В этой ситуации список сортируется последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно ее выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки по каким- либо причинам вас не устраивают, это действие необходимо незамедлитель- но отменить с помощью кнопки Отменить панели инструментов Стандартная. 3.4. Анализ списков с помощью фильтров В конечном итоге основное назначение любой базы данных – это опе- ративный поиск необходимой информации по какому-либо запросу. Под за- просами принято понимать задачи на поиск информации в базе данных. При этом часть базы данных, удовлетворяющая запросу, называется выборкой. В Excel запросы реализованы с помощью фильтров. Фильтрация списка – это процесс, в результате которого в списке скрываются все строки, не удовле- творяющие критериям фильтрации, а остаются видимыми только те (остает- ся выборка), которые соответствуют условиям запроса. 11 Excel располагает двумя командами фильтрации, которые становятся доступными через пункт меню Данные/Фильтр: Автофильтр и Расширен- ный фильтр. С помощью автофильтра реализуются простые запросы, со- держащие не более двух условий поиска. Расширенный (усиленный) фильтр позволяет выполнять запросы практически любой сложности. Для установки автофильтра на все поля списка достаточно обратиться к пункту меню Данные/Фильтр/Автофильтр. Можно установить авто- фильтр и для одного поля. Для этого достаточно его предвари- тельно выделить: активизировав заголовок соответствующего столбца, нажать комбинацию клавиш Shift, Ctrl+ ↓, после чего справа от заголовка появится кнопка , щелчок по ко- торой раскрывает список значе- ний данного столбца. Эти значения можно использовать для фильтрации. Кроме того, можно на- строить авто- фильтр, вы- брав из этого списка эле- мент (Условие…), после чего можно создать критерий (настроить пользова- тельский автофильтр), состоящий не более чем из двух условий, соединен- ных знаками операций И, ИЛИ. Каждое из этих условий представляет собой выражение логического типа, содержащее любые операции отношения (<, <=, =, <>, >, >=). Пусть, например, нам необходим список студентов, ро- дившихся в 1971 году. Пользовательский автофильтр для решения этой за- дачи приведен на рис. 4, а результаты фильтрации – на рис. 5. Рис.4. Критерий для выборки студентов, родившихся в 1971 году Рис. 5. Выборка студентов, родившихся в 1971 году 12 При создании текстовых критериев можно использовать символы шаб- лона: «*» – для обозначения последовательности любых символов произ- вольной длины, и «?» – для обозначения единичного символа, стоящего на определенном месте. Для включения символов шаблона в критерий в каче- стве обычных символов перед ними надо ставить тильду «». Пусть, напри- мер, нам необходим список студентов, чьи имена начинаются с буквы «А» и заканчиваются буквой «а», или имена состоят из восьми любых букв. Один из возможных вариантов пользовательского автофильтра для решения этой задачи приведен на рис. 6, а результаты фильтрации – на рис.7. Расширенный фильтр по сравнению с автофильтром обладает сле- дующими преимуществами: 1) позволяет создавать критерии с условиями по нескольким полям; 2) позволяет создавать критерии с тремя и более условиями; 3) позволяет создавать вычисляемые критерии; 4) позволяет копию полученной в ре- зультате фильтра- ции выборки поме- щать в другое место рабочего листа. При работе с расширенным фильтром необходимо опреде- лить три области (рис. 8): 1) исходный диапазон (интер- вал списка) – область базы данных ($A$1:$H$26); 2) диапазон условий (интервал критериев) – область, содер- жащая критерии фильтрации, которые могут находиться и на отдельном листе (Критерии!$A$2:$D$4); Рис.6. Критерий с использованием символов шаблона Рис.7. Результаты фильтрации по критерию рис.6 13 3) диапазон результата (интервал извлечения) – область, в которую необ- ходимо скопировать выборку (можно указать только адрес левого верхне- го угла), она не задается в случае фильтрации списка на том же самом месте. Назначение флажка Только уникальные записи (рис. 8) очевидно. Установка этого флажка при копи- ровании выборки в интервал извле- чения позволяет убрать из нее все повторяющиеся записи. При отсут- ствии диапазона условий с помощью этого флажка можно избавиться от повторяющихся записей в исходном списке. При создании интервала крите- риев (рис. 9) необходимо пом- нить о следующих соглашениях: 1) диапазон условий должен состоять не менее чем из двух строк (первая строка – заго- ловки, которые рекомендуется просто копировать из заголовков столбцов списка, последующие – соответствующие критерии); 2) если условия располагаются в одной строке, то это означает одновре- менность их выполнения, т.е. считается, что между ними поставлена ло- гическая операция И; 3) для истинности критерия, состоящего из условий, располагающихся в разных строках, требуется выполнение хотя бы одного из них, т.е. счита- ется, что они соединены логической операцией ИЛИ; 4) интервал критериев должен располагаться выше или ниже списка, ли- бо на другом рабочем листе; 5) в интервале критериев не должно быть пустых строк. Рис. 9. Пример интервала критериев Рис. 8. Окно диалога Расширенный фильтр 14 При формировании текстовых критериев необходимо помнить о том, что: 1) если в ячейке содержится только один символ (рис. 10 – колонка A), то такому условию удовлетворяют любые тексты, начинающиеся с этого символа; 2) если содержимое ячейки представляет собой текстовую константу вида ”>БУКВА” или ”<БУКВА”, то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ, или начинающийся с предшествующих ей БУКВ; 3) для поиска текста на полное совпадение содержимое ячейки с крите- рием должно иметь вид =”=ТЕКСТ”; 4) в текстовых критериях можно использовать символы шаблона. Вычисляемый критерий представляет собой формулу (рис. 10), в кото- рой обязательно имеется ссылка (для реализации каких-либо вычислений) на соответствующую ячейку первой строки списка. Так как эта формула яв- ляется логическим выражением, то в ячейке, ее содержащей, отображается результат вычисления (ИСТИНА либо ЛОЖЬ) для первой записи списка (рис.9). А в результате процесса фильтрации в списке будут скрыты те запи- си, для которых при вычислении формулы получается значение ЛОЖЬ. При создании вычисляемых критериев необходимо помнить о следующих пра- вилах: 1) заголовок столбца над вычисляемым критерием не должен совпадать ни с каким из имен полей списка, он может быть либо пустым, либо со- держать текст, поясняющий назначение условия; 2) в самом условии ссылки на ячейки внутри списка должны быть запи- саны в относительной форме; 3) ссылки на ячейки вне списка должны быть абсолютными. Рис. 10. Содержимое интервала критериев рис. 9 15 Пусть, например, перед нами стоит следующая задача. Необходимо выдать список студентов мужского пола, родившихся летом, или же студен- ток, год рождения которых является високосным. Содержимое интервала критериев, созданного для реализации этого запроса, приведено на рис. 10. Верхняя строка интервала содержит заголовки критериев, первый из кото- рых совпадает с заголовком поля, а оставшиеся три уникальны. В ячейки B3, C3, D4 записаны формулы (вычисляемые критерии). Выборка, полученная в результате фильтрации по критериям рис. 10, приведена на рис. 11. Расширенный фильтр с вычисляемыми критериями позволяет реализо- вать запрос практически любой сложности. Пусть, например, перед нами стоит следующая задача. Необходимо сформировать выборку, в которую бы входили студенты: 1) названные в честь отца; 2) самые младшие по возрасту; 3) самые старшие. Интервал критериев для реализации этого запроса приведен на рис. 12, а полученная выборка – на рис. 13. Однако для реализации такого рода сложных запросов необходимо оз- накомиться хотя бы с самыми распространенными функциями рабочего лис- та Excel: математическими, текстовыми, даты и времени. Кроме того, суще- ствует еще специальный класс функций, предназначенных для анализа спи- сков. Рис. 11. Выборка, соответствующая критериям рис. 10 16 3.5. Использование текстовых функций при формировании вычисляемых критериев Текстовые функции дают возможность выполнять самые разнообраз- ные преобразования текстовых данных. К наиболее часто используемым из них относятся следующие: 1. ЗНАЧЕН(текст) − преобразует числовые данные, введенные на рабочем листе в текстовом формате (т.е. заключенные в кавычки) в числовые значения. 2. ТЕКСТ(значение; формат) − преобразует число в текст по задан- ному формату. 3. ДЛСТР(текст) − возвращает длину строки (рис. 12) − количество символов в параметре текст, включая пробелы между словами. 4. ПРАВСИМВ(текст; колич_симв) − извлекает заданное количест- во колич_симв последних (самых правых) символов из текстовой строки текст. 5. ЛЕВСИМВ(текст; колич_симв) − извлекает заданное количество колич_симв первых (самых левых)символов (рис. 12) из текстовой строки текст. Рис. 12. Критерии для реализации сложного запроса Рис. 13. Выборка, соответствующая критериям рис. 12 17 Рис. 14. Критерии для реализации запроса Рис. 15. Выборка, соответствующая критериям рис. 14 6. ПСТР(текст; нач_позиция; колич_симв) − извлекает из исход- ной строки текст, начиная с указанной позиции нач_позиция, подстроку заданной длины колич_симв. Пусть, например, необходимо выбрать студентов, у которых в студен- ческом билете среди трех последних имеются хотя бы две подряд идущие одинаковые цифры. Интервал критериев для реализации данного запроса представлен на рис. 14. Выборка, полученная в результате фильтрации по критериям рис. 14, приведена на рис. 15. 7. СЖПРОБЕЛЫ(текст) − удаляет из текста все начальные и хво- стовые пробелы, из внутренних же удаляются все пробелы, за ис- ключением одиночных. 8. НАЙТИ(иск_текст; просм_текст; нач_позиция) и ПОИСК(иск_текст; просм_текст; нач_позиц) − осуществ- ляют поиск внутри строки некоторого образца, т.е. заданной под- строки, и возвращают порядковый номер символа строки, с которо- го начинается найденный образец. При подсчете всегда учитывают- ся все символы, включая пробелы и знаки препинания. Обе функ- ции выполняют сходные действия, различие заключается в том, что функция НАЙТИ() учитывает регистр, а функция ПОИСК() допус- кает символы шаблона. 18 Пусть, например, нам необходим список студентов, у которых в фами- лии имеется хотя бы одна буква «С», независимо от регистра (маленькая или большая). Критерий для реализации данного запроса представлен на рис. 16. Результаты фильтрации показаны на рис. 17. 9. ЗАМЕНИТЬ(старый_текст; нач_позиция; колич_симв; новый_текст) − заменяет заданный фрагмент текста старый_текст другой строкой новый_текст. 10. СЦЕПИТЬ(текст1; текст2; …) − соединяет несколько строк в одну (конкатенация). 3.6. Использование функций даты и времени при формировании вычисляемых критериев Рассмотрим основные функции, предназначенные для работы с датами и временем: 1. ДАТА(год; месяц; день) − возвращает дату, заданную параметра- ми, в числовом формате. 2. СЕГОДНЯ() − возвращает числовое значение текущей даты. В на- шем списке эта функция используется при вычислении возраста студентов (табл. 1). 3. ДЕНЬНЕД(дата; тип) − вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Необязательный вто- рой аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутству- ет, то первым днем недели считается воскресенье, а последним − Рис. 17. Выборка, соответствующая критерию 16 Рис. 16. Критерий для реализации запроса 19 суббота. Если тип равен 2, то первым днем недели считается понедельник, а последним − вос- кресенье. Пусть, например, необходимо найти студентов, родившихся в пятницу или 13-го числа. Критерии для поиска таких студентов представлены на рис.18. Выборка, полученная в результате фильтрации по критериям рис.18, представлена на рис. 19. 4. ГОД(дата) − возвращает значение года (от 1900 до 9999) для дан- ной даты (рис. 10). 5. МЕСЯЦ(дата) − возвращает номер месяца (от 1 до 12) для данной даты (рис. 10). 6. ДЕНЬ(дата) − возвращает номер дня в месяце (от 1 до 31) для данной даты. 7. ДАТАЗНАЧ(дата_как_текст) − преобразует в числовой формат дату, заданную в текстовом формате. Пусть, например, Рис. 18. Содержимое интервала критериев Рис. 20. Интервал критериев для реализации запроса Рис. 19. Выборка, соответствующая критериям рис. 18 Рис. 21. Выборка, соответствующая критериям рис. 20 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. 3. БДПРОИЗВЕД(база_данных; поле; критерий) − перемножает значения определенных полей записей базы данных, удовлетво- ряющих критерию. 4. БСЧЕТ(база_данных; поле; критерий) − подсчитывает количе- ство числовых ячеек в полях записей базы данных, отвечающих за- данному критерию. 5. ДМАКС(база_данных; поле; критерий) − возвращает макси- мальное значение поля среди выделенных записей базы данных. Рис. 22. Пример использования функции баз данных Рис.23. Результат вычислений по формуле рис. 22 Рис. 24 .Пример использования функций баз данных Рис. 25. Результаты вычислений по формулам рис. 24 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 А |