Л.С. Таганов Решение численных задач средствами MS Excel

40

Продолжение таблицы 2.1

	№			Функция f(x)			a			b		№		Функция f(x)			a			b
	п/п											п/п
	11			Ln2(x) – Cos(x + 1)		4			5			28		Cos(Ln(1 + x))ex		2			4
	12			Sin(Ln(1 + x))ex		1			2			29		35Cos(4x) + 20		1			2
	13			Ln(πx)xe-x		1			3			30		Cos(πx/2)/(1 – x3)		5			7
	14			e(1- x)Ln(1 + x2)		1			2			31		(1 – 1/x2)e-x		1			2
	15			3 + 4Cos(2x) - 7		5			6			32		10Ln(1 + x)Sin(πx)		1			3
	16			Cos(πx/2)/(1 – x)		0			2			33		5Cos(3x) + 3Cos(5x)		1,5			2,5
	17			Sin(πx)/x(1 + x)		0			1			34		Ln(1 + x)x/(ex – 1)		1			4

ЛАБОРАТОРНАЯ РАБОТА №5

Автоматизированные базы данных

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

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

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

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

41

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

-список состоит из строк, называемых записями;

-верхняя строка списка, называемая заголовком, должна содержать

-метки (имена) соответствующих полей (столбцов), её формат

(шрифт и цвет фона) должен отличаться от формата записей;

-столбцы списка, называемые полями, должны содержать однородную (однотипную) информацию;

-внутри списка не должно быть пустых строк и столбцов;

-всякая другая информация кроме списка должна располагаться на рабочем листе отдельно от списка.

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

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

1. СОЗДАНИЕ СПИСКА (ТАБЛИЦЫ БАЗЫ ДАННЫХ)

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

(текущая дата – дата рождения (выпуска или изготовления))/365,

где: - текущая дата – это функция СЕГОДНЯ(); - дата рождения (выпуска) – это адрес ячейки, в которой содержится дата соответствующей записи.

Пример: =(СЕГОДНЯ() – D7)/365

42

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

-ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными;

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

то есть оформляется заголовок списка или “шапка” таблицы.

Пример структуры списка:

№	Фамилия И. О.	Специальность	Стаж	Дата	Возраст
записи			работы	рождения

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

2.ЗАДАНИЕ НА ВЫПОЛНЕНИЕ РАБОТЫ

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

тестовое, числовое, дата и время, вычисляемое.

2.Заполнить список реальным (правдоподобным) содержанием, состоящим не менее чем из 15 записей.

3..Сформулировать и реализовать 5 запросов на поиск и отбор

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

43

Примерные варианты списков

1.Страны: название, столица, площадь, население, год образования , возраст.

2.Столицы: название, страна, население, год образования, возраст.

3.Предприятия города: название, тип собственности, дата рождения, возраст, экономическая эффективность.

4.Список трудового коллектива: фамилия, имя, отчество, профессия, дата рождения, возраст.

5.Легковые автомобили: модель (марка), страна-производитель, мощность двигателя, дата выпуска, возраст, цена в условных единицах, цена в рублях.

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

7.Ваши преподаватели: фамилия, имя, отчество, дата рождения, возраст, учёная степень, учёное звание.

8.Учебные дисциплины: название, семестр, количество часов в семестре, количество часов в неделю, дата сдачи экзамена.

9.Список учебной группы: фамилия, имя, отчество, пол, номер зачётной книжки, дата рождения, возраст, успеваемость.

10.Домашние животные (кошки): порода, цвет шерсти, цвет глаз, дата рождения, возраст, цена, телефон хозяина.

11.Домашние животные (собаки): порода, цвет шерсти, дата рождения, возраст, цена, телефон хозяина.

12.Квартиры: планировка, площадь, число комнат, площадь кухни этаж, район города, цена.

13.Продукты питания: наименование, производитель, дата выпуска, срок реализации, время до срока реализации, цена.

3.ОБРАБОТКА СПИСКА

Косновным действиям по обработке списков относятся:

добавление, удаление, редактирование, просмотр, сортировка и поиск записей.

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

44

3.1. Сортировка списка

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

Поле, по которому производится сортировка, называется ключевым полем (ключом сортировки).

Реализация сортировки возможна двумя способами:

-с помощью кнопок <Сортировка по возрастанию> и <Сортировка по убыванию> панели инструментов <Стандартная>;

-через команду меню <Данные/Сортировка>.

Применение команды меню <Данные/Сортировка> позволяет отсортировать список за один приём максимум по трём полям (первый ключ, второй, третий ключ). Если необходимо произвести сортировку более чем по трём полям, то сортировка должна производиться последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно её выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки не соответствуют ожидаемым, действие по сортировке необходимо незамедлительно отменить с помощью кнопки

<Отменить> панели инструментов <Стандартная>.

3.2. Формирование запросов

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

Запросы в Excel реализованы с помощью фильтров. Фильтрация списка – это процесс, в результате которого в списке скрываются все строки, не удовлетворяющие критериям фильтрации, а видимыми остаются только те, которые соответствуют условиям запроса (остаётся

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

45

Расширенный (усиленный) фильтр позволяет выполнять запросы практически любой сложности.

3.2.1. Формирование запросов с помощью автофильтра

Для установки автофильтра на все поля необходимо выполнить: - активизировать любую ячейку списка (базы данных), щёлкнув мышкой по этой ячейке;

-щёлкнуть мышкой по кнопке меню <Данные>;

-в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>;

-в появившемся справа подменю установить флажок на строке <Автофильтр>, щёлкнув мышкой по строке.

Для установки автофильтра на одно поле необходимо выполнить:

-активизировать заголовок нужного поля, щёлкнув мышкой по нему;

-нажать комбинацию клавиш Sift+Ctrl+↓.

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

Отмена установки автофильтра осуществляется той же последовательностью команд, что и при установке, за исключением последней команды. Вместо установки флажка на строке <Автофильтр> надо снять флажок щелчком мышки по нему.

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

-(Все) – для выбора содержимого всего списка или возврата всего списка после фильтрации;

-(Первые 10 …) – для формирования запросов только по числовым полям. После щелчка мышкой по этой строке появляется диалоговое окно <Наложение условия по списку>. В данном окне устанавливаются условия выборки: количество записей, наибольших или наименьших, элементов списка или % от количества элементов;

-(Условие …) - для установки критериев выборки. После щелчка мышкой по этой строке появляется диалоговое окно

<Пользовательский автофильтр>, который позволяет создать критерий выборки. Критерий может состоять не более чем из двух условий, соединённых операциями <И>, <ИЛИ>. Каждое из условий

46

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

-“*” – для обозначения последовательности произвольной длины, состоящей из любых символов;

-“?” – для обозначения символа, стоящего на определённом

месте.

При включении символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду “~”.

3.2.2. Формирование запросов с помощью расширенного фильтра

Расширенный фильтр по сравнению с автофильтром обладает следующими преимуществами:

-позволяет создавать критерии с условиями по нескольким полям;

-позволяет создавать критерии с тремя и более условиями;

-позволяет создавать вычисляемые критерии;

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

Для установки расширенного фильтра необходимо выполнить:

- активизировать любую ячейку списка (базы данных), щёлкнув мышкой по этой ячейке;

-щёлкнуть мышкой по кнопке меню <Данные>;

-в раскрывшемся меню щёлкнуть мышкой по строке <Фильтр>;

-в появившемся справа подменю установить флажок на

строке <Расширенный фильтр>, щёлкнув мышкой по строке. При работе с расширенным фильтром необходимо определить три

области:

-исходный диапазон – вся область базы данных, например, $A$1:$H$26;

-диапазон условий – область, содержащая критерии фильтрации,

47

например, Критерии! $A$28:$C$30;

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

Назначение флажка <Только уникальные записи> в окне диалога <Расширенный фильтр> очевидно. Установка этого флажка при копировании выборки позволяет убрать из неё все повторяющиеся записи. При отсутствии диапазона условий с помощью этого флажка можно избавиться от повторяющихся записей в исходном списке.

При создании интервала критериев необходимо руководствоваться следующими правилами:

- диапазон условий должен состоять не менее чем из двух строк: первая строка – строка заголовков, последующие строки – соответствующие критерии;

-интервал критериев должен располагаться вне списка или на другом листе;

-в интервале критериев не должно быть пустых строк;

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

между ними поставлена логическая операция <И>;

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

При формировании текстовых критериев необходимо помнить о следующих правилах:

-если в ячейке содержится только один символ, то такому условию удовлетворяют любые тексты, начинающиеся с этого символа;

-если содержимое ячейки представляет собой текстовую константу вида “>БУКВА” или “<БУКВА”, то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ, или начинающийся с предшествующих ей БУКВ;

-для поиска текста на полное совпадение содержимое ячейки с критерием должно иметь вид =”=ТЕКСТ”;

-в текстовых критериях можно использовать символы шаблона.

48

Вычисляемый критерий представляет собой формулу, в которой обязательно имеется ссылка на соответствующую ячейку первой строки списка. Так как эта формула является логическим выражением, то в ячейке, её содержащей, отображается результат вычисления (ИСТИНА или ЛОЖЬ) для первой записи списка. А после фильтрации в списке будут скрыты те записи, для которых при вычислении формулы получается значение ЛОЖЬ.

При формировании вычисляемых критериев необходимо руководствоваться следующими правилами:

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

-ссылки на ячейки внутри списка, задаваемые в условии, должны быть относительными;

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

формировании вычисляемых критериев.

а) Текстовые функции:

-ДЛСТР(текст) – возвращает длину строки, то есть количество символов в параметре текст, включая пробелы между словами;

-ТЕКСТ(значение; формат) – преобразует число в текст

-ПРАВСИМВ(текст; колич_симв) – извлекает заданное по заданному формату;

-ЗНАЧЕН(текст) – преобразует число, представленное в текстовом формате, в числовой формат; количество символов из конца строки текст;

-ЛЕВСИМВ(текст; колич_симв) – извлекает заданное

количество символов из начала строки текст;

-ПСТР(текст; нач_позиция; колич_симв) – извлекает из исходной строки текст, начиная с указанной позиции, подстроку заданной длины;

-СЖПРОЕЛЫ(текст) – удаляет все пробелы из начала и конца строки текст, а из внутренней части строки все кроме одиночных;

49

-НАЙТИ(исх_текст; просм_текст; нач_позиц) – ведёт поиск заданной подстроки внутри исходной строки и возвращает порядковый номер символа исходной строки, с которого начинается найденный образец. При подсчёте учитываются все символы, включая пробелы и знаки препинания, а также учитывается регистр;

-ПОИСК(исх_текст; просм_текст; нач_позиц) – ведёт поиск заданной подстроки внутри исходной строки и возвращает порядковый номер символа исходной строки, с которого начинается найденный образец. При подсчёте учитываются все символы, включая пробелы и знаки препинания, однако не учитывается регистр, допускаются символы шаблона.

б) Функции даты и времени:

-ДАТА(год; месяц; день) – возвращает дату в числовом формате (параметры функции также задаются цифрами);

-СЕГОДНЯ() – возвращает числовое значение текущей даты;

-ДЕНЬНЕД(дата; тип) – вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Параметр тип определяет начало отсчёта (воскресенье или понедельник). Если параметр задан равный 1 или отсутствует, то началом отсчёта будет воскресенье, а если равен 2, то начало отсчёта будет понедельник;

-ГОД(дата) – возвращает значение года (от 1900 до 9999) для данной даты;

-МЕСЯЦ(дата) – возвращает номер месяца (от 1 до 12) для данной даты;

-ДЕНЬ(дата) – возвращает номер дня в месяце (от 1 до 31) для данной даты;

-ДАТАЗНАЧ(дата_как_текст) – преобразует дату, заданную в текстовом формате, в числовой формат.

в) Функции для анализа списков:

-СЧЁТЕСЛИ(интервал; критерий) – возвращает количество ячеек в интервале, которые удовлетворяют критерию, например, =СЧЁТЕСЛИ(F2:F26;”Ж”);