учебник информатика

Глава 3. Программное обеспечение

Копирование формул – характерный прием в электронных таблицах, который позволяет выполнить одни и те же операции с группой данных (в некоторой степени – аналог циклов при работе с массивами в программах на алгоритмических языках). Именно в операциях копирования большое значение имеет правильное использование абсолютных (не изменяются при копировании) и относительных ссылок (изменяются в соответствии с изменением позиции формулы). Ссылка на ячейку в формуле состоит из двух частей – обозначение колонки и строки, абсолютную адресацию можно использовать для одного из этих компонентов. Пример использования смешанных ссылок – расчет Z для поверхности вида Z = F(X, Y), где формула расчета написана один раз для Z1 = F(X1, Y1), а затем скопирована для всех остальных значений X и Y, т. е. вниз и вправо (см. рису-

нок 3.46).

=(B6-B5)/(B7-1)

=$B$3*$A12^2+$B$4*B$11^2+$D$3*$A12*B$11+$D$4

=$B$3*$A18^2+$B$4*D$11^2+$D$3*$A18*D$11+$D$4

Рисунок 3.46. Использование различных типов ссылок в формулах

231

Глава 3. Программное обеспечение

Система Excel имеет достаточно большой набор функций (см. ленту Формулы на рисунке 3.47).

Рисунок 3.47. Вкладка ленты Формулы Excel 2007 с установленными надстройками Поиск решения, Мастер суммирования, Пересчет в евро

Функции сгруппированы по типу обрабатываемых ими данных:

–текстовые (ЛЕВСИМВ, ПРАВСИМВ, ПСТР…);

–математические (ABS, ACOS, LN, LOG10…);

–в том числе матричные МОБР, МОПРЕД, МУМНОЖ;

–финансовые (БС, ВСД, КПЕР…);

–статистические (ДИСП, КВАДРОТКЛ, КОРРЕЛ…);

–работы с датой и временем (СЕГОДНЯ, ТДАТА, МИНУТЫ…).

Названия функций приведены для русской версии Office, в других

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

Кроме основных функций с системой Microsoft Office поставляются надстройки для Excel – вспомогательные программы, служащие для использования дополнительных возможностей (таблица 3.12), они устанавливаются из окна Параметры Excel, раздел Надстройки.

В частности, пакет анализа (см. рисунок 3.48) имеет следующие компоненты:

–описательная статистика;

–дисперсионный анализ:: однофакторный; двухфакторный с повторениями; двухфакторный без повторений;

–корреляционный анализ;

–регрессия;

–ковариационный анализ;

–экспоненциальное сглаживание;

–двухвыборочный f-тест для дисперсии;

–анализ Фурье;

–гистограмма;

232

Глава 3. Программное обеспечение

–скользящее среднее;

–генерация случайных чисел;

–ранг и персентиль;

–выборка;

–t-тест;

–z-тест.

Рисунок 3.48. Excel 2007 с установленной надстройкой Пакет анализа

Таблица 3.12. Надстройки Microsoft Office Excel

Надстройка	Описание
Пакет анализа	Статистические, финансовые и инженерные функции
Пакет анализа	Позволяет разработчикам использовать синтаксис пакета
VBA	анализа данных
Мастер сумми-	Создает формулу суммирования с условием
рования
Пересчет в евро	функция EUROCONVERT для пересчета
Помощник по	Позволяет размещать данные Microsoft Excel в Интерне-
Интернету	те, используя синтаксис помощника по Интернету.
Мастер подста-	Создает формулу для поиска данных в диапазоне по дру-
новок	гому значению в этом же диапазоне.
Поиск решения	Вычисляет решения для сценариев «что-если» на основе
	ячеек перебора и ячеек ограничений.
	233

Глава 3. Программное обеспечение

Описание использования этих компонентов присутствует в справке Excel 2003, которую можно вызвать из окна Анализ данных. В Приложении 5 показаны примеры использования пакета анализа при статистической обработке результатов эксперимента – для определения погрешности результата измерения и дисперсионного анализа. Нахождение уравнений регрессии может быть выполнено путем построения линии тренда, проверка адекватности – использованием компонента Регрессия пакета анализа.

Методика построения диаграмм в Excel 2003 отличается от Word

наличием Мастера диаграмм, в Excel 2007 Мастер отсутствует, но есть множество Экспресс-макетов и Экспресс-стилей. Для построения диа-

граммы рекомендуется предварительно выбрать группу ячеек – источник данных, далее дать команду Вставка ► Диаграмма (или для Excel 2003 нажать кнопку Мастер диаграмм на панели инструментов Стандартная).

Мастер диаграмм позволяет пошагово задать следующие основные свойства диаграммы:

1)тип диаграммы

a)Точечная – позволяет показать зависимость Y = F(X), даже если точкт по оси X заданы с неравномерным шагом (один из рядов – значения X, другие – Y1, Y2…), причем есть возможность показать кривую, проходящую по точкам, с ее сглаживанием или линию тренда (аппроксимация данных экспоненциальной, линейной, логарифмической, полиномиальной или степенной функциями);

b)Поверхность – зависимость Z = F(X, Y) (см. рисунок 3.48);

c)Гистограмма, Линейчатая, График – диаграммы с равно-

мерным распределением данных по оси X (ось категорий), все ряды – значения по оси Y (ось значений).

2)источник данных диаграммы – если заранее выбран диапазон данных, при построении будут использованы ссылки на эти данные, при этом следует учитывать следующее:

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

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

3)параметры диаграммы – название, подписи по осям, показ линий сетки и пр.

4)размещение диаграммы – на текущем листе или на новом.

Без использования Мастера те же свойства можно определить в свойствах объекта Диаграмма в Excel 2007 на контекстной ленте Работа с

234

Глава 3. Программное обеспечение

диаграммами, которая имеет 3 вкладки: Конструктор, Макет и Формат).

После создания диаграммы все ее элементы дополнительно можно отредактировать на самой диаграмме или настроить с помощью контекстной ленты Работа с диаграммами. На диаграмму можно добавить новые графические объекты ленты Вставка или в Excel 2003 панели Рисование.

Векторные фигуры (ленты Вставка ► Иллюстрации ► Фигуры)

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

Прямоуголь-
ник со ссылкой		Прямоуголь-
=$G$6		ник со ссылкой
		=$G$7

Рисунок 3.49. Использование ссылок в графических объектах

На рисунке 3.50 показан пример использования матричных функций

и логической функции ЕСЛИ(логическое_выражение; значение1; зна-

235

Глава 3. Программное обеспечение

чение2), которая возвращает (показывает в ячейке, где она находится)

значение1, если логическое_выражение истинно или значение2, если логическое_выражение ложно.

Рисунок 3.50. Использование матричных функций и функции ЕСЛИ

Впримере функция ЕСЛИ(E5<0;"-";"+"), записанная в ячейке D5 показывает в этой ячейке минус или плюс в зависимости от того, отрицательное или нет число записано в соседней справа клетке E5 (для числа задан формат без знака).

Вэтом же примере показано использование двух матричных функций: вычисление обратной матрицы МОБР(M3:O5) и умножение матриц МУМНОЖ(M8:O10;K3:K5). Особенность этих функций – результат вычисле-

ния имеет вид матриц (в первом случае двухмерная, во втором – вектор). Матричная функция записывается в одну клетку, затем решение разворачивается на группу клеток, как это описано на рисунке 3.50 с использованием сочетания клавиш CTRL+SHIFT+ENTER.

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

236

Глава 3. Программное обеспечение

следует снять пометку Защищаемая ячейка или на ленте Главная ► группа Ячейки ► список Формат ► выключить пункт Блокировать ячейку). Затем для Excel 2003 выбрать пункт меню Сервис ► Защита, для Excel 2007 в том же списке Формат, что и ранее для блокировки ячеек, выбрать команду Защитить лист… и в окне Защита листа оставить га-

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

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

ется в окне Параметры страницы или на вкладке Разметка страницы

Excel 2007 (см. рисунок 3.51).

Рисунок 3.51. Настройка параметров страницы и масштаба печати

Например, для документа, занимающего лист формата А4 при масштабе 100%, можно задать масштаб 200%, при этом документ будет напечатан на четырех листах формата А4, все изображение соответственно увеличится в 2 раза. Этот прием может быть использован для печати небольших плакатов с расчетными данными. И наоборот, если документ не помещается в необходимый формат, можно задать, на скольких листах бумаги нужно его разместить при печати, при этом масштаб изображения будет автоматически уменьшен.

3.3.4 Работа с базами данных в системе Microsoft Office Access

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

Система Microsoft Office Access имеет достаточно большие возможности по работе с базами данных, развитые средства создания баз данных

237

Глава 3. Программное обеспечение

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

Некоторые термины и определения, которые необходимо знать при работе с базами данных

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

База данных (БД, database) поименованная совокупность структурированные данных, относящихся к определенной предметной области.

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

Система управления базами данных (СУБД) – комплекс про-

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

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

Таблица базы данных (table) – регулярная структура, состоящая из однотипных строк (записей, records), разбитых на столбцы (поля, fields).

Втеории реляционных баз данных синоним таблицы – отношение (relation), строка называется кортежем, столбец отношения называется атрибутом.

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

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

238

Глава 3. Программное обеспечение

Вреляционной теории и концептуальной модели понятие «ключ» применяется для атрибутов отношения или сущности.

Первичный ключ (primary key) – главный ключевой элемент, однозначно идентифицирующий строку в таблице. Могут также существовать альтернативный (candidate key) и уникальный (unique key) ключи, служащие также для идентификации строк в таблице.

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

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

Связь (relation) – функциональная зависимость между объектами. В реляционных базах данных между таблицами устанавливаются связи по ключам, один из которых (в главной таблице) – первичный, второй может быть тоже первичным для связи «один к одному» или не первичный (внешний, foreign) – связь «один ко многим». Информация о связях сохраняется в базе данных.

Хранимые процедуры (stored procedures) – программные модули,

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

Ссылочная целостность данных (referential integrity) – набор пра-

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

Триггеры (triggers) – хранимые процедур, обеспечивающие соблюдение ссылочной целостности данных в следующих операциях:

1)добавление записи или изменение внешнего ключа в дочерних (child) таблицах – проверяется наличие соответствующего первичного ключа в родительской (parent) таблице;

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

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

Объект (object) – элемент информационной системы, обладающий

определенными свойствами (properties) и определенным образом реагирующий на внешние события (events).

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

239

Глава 3. Программное обеспечение

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

Язык SQL (Structured Query Language) – универсальный язык рабо-

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

3.3.4.1 Общие принципы разработки информационной системы

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

База данных – важнейший компонент ИС. Хорошо спроектированная база данных позволяет не только безпроблемно эксплуатировать систему и выполнять ее текущее обслуживание, но и модифицировать и развивать ее при развитии предприятия и изменении информационных потоков, законодательства и форм отчетности.

В настоящее время в эксплуатации на крупных предприятиях находятся комплексные ИС управления предприятиями (КИС, корпоративные системы, ERP-системы), такие, как R/3 фирмы SAP, Oracle E-Business Suite, InforERP. Среди российских разработок приближаются к системам класса ERP Галактика, Флагман, Парус.

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

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

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

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

240