Матричный способ решения СЛАУ (7.6) достаточно прост. Обе части матричного равенства (7.2) умножим слева на обратную матрицу А -1. Получим A -1´ A ´ X = A -1´ B. Т.к. A -1´ A = E, где E – единичная матрица (диагональная матрица, у которой по главной диагонали расположены единицы). Тогда решение системы (7.2) запишется в следующем виде
| X = A -1 ´ B. | (7.11) |
Т.е. для решения системы (7.2) (вычисления вектора-столбца X (7.4)) необходимо найти для матрицы A (7.3) обратную A -1 и умножить ее справа на вектор-столбец B (7.5) свободных членов. Для чего, воспользовавшись функциями Excel МУМНОЖ(матрица1;матрица2) и МОБР(матрица), введем в интервал B7:B9 следующего рабочего листа (Лист2) табличную, т.е. используя для ввода комбинацию Ctrl+Shift+Enter, мегаформулу МУМНОЖ(МОБР(A3:C5);D3:D5). После чего в строке формул увидим {=МУМНОЖ(МОБР(A3:C5);D3:D5)}, а в интервале B7:B9 – решение, точно такое же, как и в предыдущем случае (рис. 7.6).
Поиск решения
Широкий класс экономических задач составляют задачи оптимизации. Задачи оптимизации предполагают поиск значений аргументов, доставляющих функции, которую называют целевой, минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. MS Excel располагает мощным средством для решения оптимизационных задач. Это инструмент-надстройка, который называется Поиск решения (Solver). Поиск решения доступен через меню Сервис/Поиск р ешения….
Задачу решения СЛАУ (7.1) можно свести к оптимизационной задаче. Для чего одно из уравнений (например, первое) взять в качестве целевой функции, а оставшиеся n -1 рассматривать в качестве ограничений. Запишем систему (7.1) в виде
| (7.12) |
Тогда задача оптимизации для Поиска решения может звучать следующим образом. Найти значения X = (x1, x2, …, xn) T, доставляющие нуль функции, стоящей слева в первом уравнении системы (7.12), при n -1 ограничениях, представленных оставшимися уравнениями.
Для решения этой задачи необходимо записать выражения (формулы) для вычисления значений функций, стоящих слева в уравнениях системы (7.12). Отведем под эти формулы интервал C7:C9 текущего рабочего листа (Лист3). В ячейку C7 введем формулу =A3*$B$7+B3*$B$8+C3*$B$9-D3 и скопируем ее в оставшиеся C8 и C9. В них появятся соответственно =A4*$B$7+B4*$B$8+C4*$B$9-D4
и =A5*$B$7+B5*$B$8+C5*$B$9-D5. Осталось, обратившись к пункту меню С е рвис/Поиск р ешения…, в окне диалога (рис. 7.7) задать параметры поиска (установить целевую ячейку C7 равной нулю, решение в изменяемых ячейках B7:B9, ограничения заданы формулами в ячейках C8 и С9). После щелчка по кнопке Выполнить в интервале B7:B9 получим результат (рис. 7.8) – решение СЛАУ (7.7).
В завершение работы можно защитить ячейки созданных таблиц от несанкционированного, часто случайного, изменения и скрыть формулы, по которым находится решение СЛАУ. Для этого существует стандартное средство Excel – пункт меню С е рвис/За щ ита/Защитить л ист…. Перед этим необходимо снять защиту с ячеек, содержащих исходные данные (A3:C5 – элементы матрицы A (7.8), и D3:D5 – элементы вектора B), выделив эти интервалы, выбрав меню Фор м ат/ Я чейки… вкладка Защита и сбросив флажок За щ ищаемая ячейка. Для ячеек же, содержащих формулы, надо в этом диалоге (Формат ячеек) установить флажок Скрыть формулы. Надо знать, что после такой защиты невозможно будет воспользоваться средством Поиск решения. Поэтому защитить ячейки и скрыть формулы можно на первом и втором листах. В случае необходимости можно скрыть и отображаемую в ячейках информацию, поставив в соответствие этим ячейкам пользовательский формат;;; (три точки с запятой).
Варианты задания
В соответствии с номером варианта выберите из приведенных ниже систему линейных алгебраических уравнений четвертого (n =4) порядка. Приведите ее к нормальному виду (7.1). Разработайте таблицы Excel для решения выбранной СЛАУ тремя различными способами:
1) методом Крамера,
2) матричным способом,
3) используя Поиск решения.
Варианты систем линейных алгебраических уравнений приведены ниже.
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
| 
|
Правильность полученных решений легко проверить. В результате решения выбранной СЛАУ тремя различными способами должны получиться три одинаковых решения. Однако, на всякий случай, приведем решения систем 1)-36):
| 1)-6) X = (-5,1,-3,4) T; | 7)-12) X = (1,-3,-1,0) T; | 13)-18) X = (1,0,-2,3) T; |
| 19)-24) X = (1,0,0,1) T; | 25)-30) X = (0,1,1,0) T; | 30)-36) X = (5,0,-5,0) T. |
8. ЛАБОРАТОРНАЯ РАБОТА № 7
«ПРОСТЕЙШАЯ БАЗА ДАННЫХ»
Цель работы: овладеть навыками по созданию базы данных и приемами обработки информации, содержащейся в ней.
Таблицы естественны для программы MS Excel, предназначенной для решения задач обработки информации, представленной в табличном виде. Excel располагает встроенными средствами сортировки, поиска и отбора данных в таблицах. Поэтому при небольших объёмах информации, когда нет необходимости в сложных запросах, работать с базами данных в Excel просто, быстро и удобно. Базы данных в Excel принято называть списками.
Списком Excel называется таблица, оформление которой должно отвечать следующим требованиям:
1) список состоит из строк, называемых записями;
2) верхняя строка списка, называемая заголовком, должна содержать метки (имена) соответствующих столбцов (полей), её формат (шрифт и цвет фона) должен отличаться от формата записей;
3) поля должны содержать однородную (однотипную) информацию;
4) внутри списка не должно быть пустых строк (записей) и столбцов (полей);
5) всякая другая информация на этом рабочем листе, не имеющая отношения к списку, должна располагаться отдельно от него;
6) рабочий лист рекомендуется именовать названием списка.
Создание списка
Первый, наиболее важный шаг при создании базы данных – это разработка хорошо продуманной структуры, которая и определяет возможности будущей обработки информации. Структура списка определяется структурой одинаково организованных записей. Под структурой записи понимается совокупность её полей (их имена, типы, назначение). В ячейках списка хранятся данные соответствующих типов: числовые, текстовые, даты и времени. Кроме того, могут быть поля, в ячейках которых содержатся вычисляемые по формулам значения (числа, тексты, дата и время). Примерами вычисляемых полей могут служить в зависимости от предметной области, например, стоимость в условных единицах и в рублях, время до истечения срока годности продукта, возраст, срок эксплуатации изделия и другие. Для вычисления возраста, срока эксплуатации изделия и времени до истечения срока годности продукта можно использовать формулу:
(текущая дата-дата рождения (выпуска или изготовления))/365, где:
текущая дата – это результат, возвращаемый функцией СЕГОДНЯ();
дата рождения (выпуска) – это адрес ячейки, в которой содержится соответствующая дата.
Пример: =(СЕГОДНЯ()-D7)/365
Если результат вычисления должен быть целым числом, то ячейки этого столбца (поля) необходимо отформатировать так, чтобы в дробной части было нуль цифр.
При формировании вычисляемых полей необходимо руководствоваться следующими правилами:
1) ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными;
2) ссылки на ячейки вне списка должны быть абсолютными.
Первоначально на рабочем листе формируется структура списка, то есть оформляется заголовок списка или “шапка” таблицы.
Пример структуры списка
| № записи | Фамилия И.О. | Дата рождения | Возраст (лет) | Специальность |
Заполнение списка информацией о реальных объектах может осуществляться вручную (непосредственным вводом значений в соответствующие ячейки) или с использованием стандартной экранной формы, которая активизируется через меню Д анные, затем выбирается строка меню Фор м а….
Содержание работы
1) Спроектировать базу данных, состоящую из одного списка, для автоматизации любой предметной области. Каждая запись списка должна состоять не менее чем из пяти полей основных типов: текстовое, числовое, дата и время, вычисляемое.
2) Заполнить список реальным (правдоподобным) содержимым, состоящим не менее чем из 15 записей.
3) Сформулировать 2 задачи на сортировку созданного списка. Эти задачи реализовать с помощью пункта Сортировка… меню Д анные по текстовому, числовому полям или по полю дата.
4) Сформулировать 2 запроса (задачи) на поиск и отбор информации из созданного списка. Эти задачи реализовать с помощью пункта А втофильтр меню Д анные/ Ф ильтр по текстовому, числовому полям или по полю дата.
Примечание: таблицы с результатами сортировок и выборками, полученными по запросам, расположить последовательно ниже исходной таблицы. Для каждой таблицы оформить заголовок с указанием условия сортировки или запроса.
Варианты задания
1) Страны: название, столица, площадь (тыс.кв. км), население (млн. чел.), плотность населения, дата образования.
2) Столицы: название, страна, население, дата образования, возраст.
3) Предприятия города: название, тип собственности, дата создания, возраст, экономическая эффективность.
4) Список сотрудников трудового коллектива: фамилия, имя, отчество, дата рождения, возраст, профессия.
5) Легковые автомобили: модель (марка), страна-производитель, мощность двигателя, дата выпуска, возраст, цена в условных единицах, цена в рублях.
6) Книги: автор, название, издательство, год выпуска, число томов, цена одного тома, цена издания, дата приобретения.
7) Список студентов учебной группы: фамилия, имя, отчество, дата рождения, возраст, пол, номер зачётной книжки, успеваемость.
8) Домашние животные (кошки): порода, дата рождения, возраст, цена, телефон хозяина.
9) Домашние животные (собаки): порода, дата рождения, возраст, цена, телефон хозяина.
10) Квартиры: планировка, площадь, число комнат, площадь кухни, этаж, район города, цена.
11) Продукты питания: наименование, производитель, дата выпуска, срок реализации, время до окончания срока реализации, цена.
12) Мобильные телефоны: модель, страна-производитель, цена в у. е., цена в рублях, дата покупки.
13) Компьютеры: модель, ёмкость жёсткого диска, ёмкость оперативной памяти, тактовая частота, объём видеопамяти, цена в у. е., цена в рублях, дата покупки.
Примечание: номер варианта соответствует номеру студента в списке подгруппы. По согласованию с преподавателем допустимо создание любой другой базы, отличающейся от предложенных.
Обработка списка
К основным действиям по обработке списков относятся: добавление, удаление, редактирование, просмотр, сортировка и поиск записей.
Перед выполнением какого-либо действия по обработке списка в обязательном порядке необходимо активизировать одну из ячеек с именем поля или любую другую ячейку внутри списка.
Сортировка
Под сортировкой списка принято понимать расположение его записей в определённом порядке. Записи можно располагать в порядке возрастания-убывания числовых полей, в алфавитном (обратном алфавитному) порядке текстовых полей, в хронологическом порядке полей типа дата и время.
Поле, по которому производится сортировка, называется ключевым полем (ключом сортировки).
Реализация сортировки возможна двумя способами:
1) с помощью кнопок Сортировка по возрастанию и
Сортировка по убыванию панели инструментов
Стандартная;
2) через команду меню Д анные/С о ртировка….
Применение: команда меню Д анные/С о ртировка… позволяет отсортировать список за один приём максимум по трём полям (первый ключ, второй, третий ключ). Если необходимо произвести сортировку более чем по трём полям, то сортировка должна производиться последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно её выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки не соответствуют ожидаемым, действие по сортировке необходимо незамедлительно отменить с помощью кнопки Отменить панели инструментов Стандартная и повторить сортировку.
Запросы и фильтрация
Основное назначение любой базы данных – это оперативный поиск необходимой информации по какому-либо запросу. При этом часть базы данных, удовлетворяющая запросу, называется выборкой.
Запросы в Excel реализованы с помощью фильтров. Фильтрация списка – это процесс, в результате которого в списке скрываются все строки, не удовлетворяющие критериям фильтрации, а видимыми остаются только те, которые соответствуют условиям запроса (остаётся выборка).
Excel располагает двумя инструментами фильтрации: автофильтром и расширенным фильтром. С помощью автофильтра реализуются простые запросы, содержащие не более двух условий поиска. Расширенный фильтр позволяет выполнять запросы практически любой сложности.
Автофильтр
Для установки автофильтра на все поля необходимо выполнить следующие действия:
1) активизировать любую ячейку списка (базы данных), щёлкнув мышью по этой ячейке;
2) щёлкнуть мышью по кнопке меню Д анные;
3) в раскрывшемся меню щёлкнуть мышью по строке Ф ильтр;
4) в появившемся справа подменю установить флажок на строке
А втофильтр, щёлкнув по ней мышью.
Для установки автофильтра на одно поле необходимо выполнить следующие действия:
1) активизировать заголовок нужного поля, щёлкнув мышью по нему;
2) нажать комбинацию клавиш Sift+Ctrl+¯.
После установки автофильтра в ячейках с именами всех полей или одного поля появляется кнопка в виде треугольника вершиной вниз.
Отмена установки автофильтра осуществляется в той же последовательности команд, как и при установке, за исключением последней команды. Вместо установки флажка на строке А втофильтр надо снять его щелчком мыши.
При использовании автофильтра за один приём можно сформировать запрос только по одному полю. Для этого необходимо щёлкнуть по кнопке автофильтра в заголовке этого поля. В раскрывшемся списке поля (столбца) содержатся строки, позволяющие провести настройку автофильтра.
1) (Все) – для выбора содержимого всего списка или отображения всего списка после фильтрации;
2) (Первые 10…) – для формирования запросов только по числовым полям. После щелчка мышью по этой строке появляется диалоговое окно Наложение условия по списку. В данном окне устанавливаются условия выборки: количество записей, наибольших или наименьших, элементов списка или % от количества элементов;
3) (Условие…) – для установки критериев фильтрации. После щелчка мышью по этой строке появляется диалоговое окно
Пользовательский автофильтр, в котором можно задать критерий фильтрации. Критерий может состоять не более чем из двух условий, соединённых операциями И, ИЛИ. Каждое из условий представляет собой выражение логического типа, содержащее любые операции отношения (<, <=, =, <>, >, >=). В пользовательском автофильтре эти операции представлены в виде текста и предназначены для создания условий преимущественно по числовым полям и полям типа дата и время. Для создания условий по текстовым полям предназначены ограничения начинается с, не начинается с,
заканчивается на, не заканчивается на, содержит, не содержит.
Кроме того, при создании текстовых критериев можно использовать символы шаблона:
1) “*” – для обозначения последовательности произвольной длины, состоящей из любых символов;
2) “?” – для обозначения одного символа, стоящего на определённом месте.
При включении символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду “ ~ ”.
СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ
1. Рудикова Л.В. Microsoft Excel для студента.- СПб: БХВ-Петербург, 2005.- 368 с.
2. Вержбицкий В.М. Основы численных методов: Учебник для вузов.- 2-е изд., перераб.- М.: Высшая школа, 2005.- 840 с.
3. Сингаевская Г.И. Функции в Excel. Решение практических задач.- М.: Диалектика, 2005.- 880 с.
4. Тынкевич М.А. Численные методы анализа: Учебное пособие.- Кемерово, 1997.-123 с.
5. Попов А.А. Excel: практическое руководство: Учебное пособие для вузов.- М.: Десс, 2004.- 301 с.
6. Мудров А.Е. Численные методы на ПЭВМна языках Бейсик, Фортран и Паскаль.- Томск: МП «Раско», 1992.- 272 с.
7. Гельман В.Я. Решение математических задач средствами Excel: Практикум.- СПб.: Питер, 2003.- 237 с.
8. Турчак Л.И. Основы численных методов: Учебное пособие для вузов.- М.: Наука, 1987.- 320 с.
9. Лавренов С.М. Excel: Сборник примеров и задач.- М.: Финансы и статистика, 2002.- 336 с.
10. Холи Р. Excel. Трюки/ Р. Холи, Д. Холи.- СПб.: Питер, 2005.-287 с.
11. Гладкий А.А. Excel. Трюки и эффекты/ А.А. Гладкий, А.А. Чиртик.- СПб.: Питер, 2006.- 368 с.
ПРИЛОЖЕНИЕ 1
Пример оформления рабочего листа в лабораторной №1
ПРИЛОЖЕНИЕ 2
Пример оформления рабочего листа в лабораторной №2
ПРИЛОЖЕНИЕ 3
Пример оформления рабочего листа в лабораторной №3
ПРИЛОЖЕНИЕ 4
Пример оформления рабочего листа в лабораторной №4
ПРИЛОЖЕНИЕ 5
Пример оформления рабочего листа в лабораторной №5
Составители
Леонид Степанович Таганов
Александр Григорьевич Пимонов
Владимир Григорьевич Левин
ИНФОРМАТИКА