Решим одну из таких задач.
2.5.Продовольственная база снабжает сеть из 11 магазинов. В течение квартала в магазины осуществляется завоз набора основных продуктов. График завоза продуктов приведен в таблице 1. Набор основных требуемых продуктов по магазинам приведен в таблице 2. Необходимо определить общее количество продуктов, требуемое ежемесячно и в течение квартала. Результат поместить в таблицу 3.
Откройте новый рабочий лист. Присвойте листу новое имя - Задача. Наберите и оформите на этом листе представленные выше таблицы 1 и 2.
Таблица 1. План завоза продуктов по магазинам.
| Месяцы | Номера магазинов | ||||||||||
| N10 | N12 | N15 | N18 | N19 | N20 | N22 | N25 | N30 | N32 | N34 | |
| Январь | |||||||||||
| Февраль | |||||||||||
| Март | |||||||||||
| Итого |
В строке Итого необходимо просуммировать значения в соответствующих столбцах.
Таблица 2. Объем поступлений продуктов при каждом завозе по магазинам.
| N магазина | Консервы рыб., шт. | Мясо, т. | Колбасн. изд, кг | Сахар, ц. | Крупы, ц. | Макарон. издел., ц. |
| N10 | ||||||
| N12 | ||||||
| N15 | ||||||
| N18 | ||||||
| N19 | ||||||
| N20 | ||||||
| N22 | ||||||
| N25 | ||||||
| N30 | ||||||
| N32 | ||||||
| N34 |
Данные, представленные в этих таблицах, образуют две матрицы размера 4х11 и 11х6. Решение задачи получается умножением соответствующих элементов 1-й и 2 таблиц, т.е. обычным умножением матриц. Результирующая матрица, образующая Таблицу 3, будет иметь размер 4х6.
Подготовьте форму таблицы 3. Ее верхняя строка заголовков получается копированием верхней строки с заголовками Таблицы 2, а левые заголовки получаются копированием левой колонки с заголовками Таблицы 1.
Таблица 3. Требуемое количество продуктов на 1-й квартал.
| Консервы рыб., шт. | Мясо, т. | Колбасн.изд, кг | Сахар, ц. | Крупы, ц. | Макарон. издел., ц. | |
| Месяцы | ||||||
| Январь | ||||||
| Февраль | ||||||
| Март | ||||||
| Итого |
Результаты внутри Таблицы 3 получаются как результат умножения данных Таблицы 1 (матрица А) на данные Таблицы 2 (матрица В) аналогично выполненному ранее умножению матриц.
2.6. Для решения системы линейных уравнений откройте новый рабочий лист, присвойте ему имя Система уравнений.
Система линейных уравнений в матричной форме имеет вид: АХ=В, где А - матрица, составленная из коэффициентов при неизвестных в уравнениях, Х - вектор из неизвестных,В - вектор, составленный из правых частей уравнений. Решение системы линейных уравнений в матричной форме имеет вид: Х=А-1•В, т.е. для нахождения решения системы Х необходимо:
а) найти матрицу, обратную к исходной матрице А;
б) умножить получившуюся обратную матрицу на вектор свободных членов уравнений В.
Подготовьте на листе необходимые заголовки для решения системы: Исходная матрица, Правая часть системы уравнений, Обратная матрица, Решение.
Введите в соответствующие ячейки ниже заголовков матрицу из коэффициентов при неизвестных в уравнениях и правые части системы уравнений.
С помощью функции вычисления обратной матрицы вычислите обратную матрицу и поместите ее в соответствующей части рабочего листа.
С помощью функции умножения матриц умножьте обратную матрицу на вектор свободных членов и результат поместите в соответствующей части рабочего листа.
Проверьте полученное решение системы линейных уравнений. Для этого надо умножить исходную матрицу на полученное решение. В результате должен получиться вектор свободных членов.
3. Решение уравнений.Для решения алгебраических уравнений до 3 степени включительно существуют формульные выражения, позволяющие через коэффициенты при неизвестных в уравнении находит корни уравнения (например формула Виетта). Уравнения, начиная с 4-й степени и выше, а также трансцендентные уравнения обычно в радикалах не решаются. Для нахождения корней таких уравнений обычно используются приближенные методы решения, позволяющие по начальному приближению вычислять корни уравнения с задаваемой точностью. Наиболее часто используются метод касательных и метод хорд. Для применения этих методов необходимо выделить интервал, на котором корень уравнения будет единственным. На концах интервала функция должна принимать противоположные по знаку значения. На этом интервале функция должна быть непрерывной, монотонно возрастающей или убывающей (знак производной постоянен) и не иметь точек перегиба. Тогда решение уравнения может быть найдено приближенными методами. Эти методы реализованы в программе Excel в виде команды меню Сервис/Поиск решения. С помощью этой команды можно также находить точки экстремума и вычислять экстремальные значения функции (минимум/максимум).
3.1.Найти корни уравнения: 3x5 - 5x2sin(2x) + 2x -7 = 0 и найти минимум функции на интервале (-2; 3). Для решения задачи:
а) в ячейке А1 введите заголовок Решение уравнения, в ячейке В1 введите заголовок Функция, в ячейке С1 - Точка экстремума,в ячейке D1 - заголовок Минимум функции;
б) в ячейках А2 и С2 наберите начальное приближение - 0;
в) в ячейке В2 с помощью Мастера функций введите функцию, находящуюся в левой части уравнения (в качествех будет ячейка А2): =3*A2^5-A2^2*SIN(2*A2)+2*A2-7;
г) в ячейке D2 также наберите нашу функцию, только вместо А2 необходимо набрать С2;
д) вызовите команду меню Сервис/Поиск решения.В открывшемся окне команды необходимоустановить целевую ячейку В2 равной значению0. В опции команды Изменяя ячейкиуказать ячейку, в которой мы ищем решение (А2). Приближенный анализ уравнения показывает, что решение будет больше, чем 1,2. Введем ограничение (нажмите опцию Добавить): A2>1,2. Далее кнопкой параметры можно установить максимальное число итераций, точность расчета и другие параметры поиска решения. После ввода всех необходимых для поиска решения данных нажмите кнопку Выполнить.В результате в ячейке А2 вы получите решение уравнения, Значение функции в ячейке В2 будет приблизительно (с заданной вами точностью) равно 0. Если в результате поиска решение не может быть найдено (об этом на экране будет выведено соответствующее сообщение), это означает,что заданный вами интервал поиска или начальное приближение подобраны неудачно и вам необходимо изменить интервал поиска, задаваемый ограничением;
е) для нахождения минимального значения функции необходимо вызвать команду Сервис/Поиск решения и в ее окне указать целевую ячейку D2 равной минимальному значению, изменяемая ячейка - С2, а ограничения поиска минимума (интервал (-2;3)), задается ограничениями C2>-2 и C2<3. В результате выполнения поиска решения в ячейке С2 вы получите точку экстремума, а в ячейке D2 - минимальное значение функции на интервале.
3.2.Найдите корни уравнения: x3 + arctg(x) - 2 = 0 и вычислите максимальное значение функции на интервале (-3;-1).
4. Сохранить полученные результаты на гибком диске в каталоге Excelпод именем Excel-5.xls.
Лабораторно-практическое занятие N5. Обработка базы данных: использование Excel для выборки записей из базы данных по заданным критериям
Задание:научиться использовать программу Excel для работы с базами данных, представленными в табличной форме и осуществлять поиск данных в таблицах по задаваемым критериям с использованием Автофильтраи Усиленного фильтра.
Результат:пользовательсможет с помощью программы Excel отбирать в таблицах данные по вводимым критериям отбора
Решение:рассмотрим возможности программы Excel по отбору данных из таблицы на примере решения следующего задания.
Задание 1.Провести выборку записей из базы данных, представленной в файле Y:\01. Информатика (общий курс)\04. Ивасюк Ю.Д\Задачи Excel\tr14.xls в форме таблицы 1. Записи, подлежащие выборке, должны удовлетворять следующим критериям: 1/ фамилии клиентов должны начинаться на буквы П, Л, З, 2/ размер ссуды должен быть в пределах от 98 до 200 тысяч рублей, 3/ число взносов за весь срок не должен превышать 100.