Л.С. Таганов Решение численных задач средствами MS Excel
.pdf40
Продолжение таблицы 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;”Ж”);