Заглавная страница Избранные статьи Случайная статья Познавательные статьи Новые добавления Обратная связь КАТЕГОРИИ:  Археология Биология Генетика География Информатика История Логика Маркетинг Математика Менеджмент Механика Педагогика Религия Социология Технологии Физика Философия Финансы Химия ЭкологияТОП 10 на сайте Приготовление дезинфицирующих растворов различной концентрацииТехника нижней прямой подачи мяча. Франко-прусская война (причины и последствия) Организация работы процедурного кабинета Смысловое и механическое запоминание, их место и роль в усвоении знаний Коммуникативные барьеры и пути их преодоления Обработка изделий медицинского назначения многократного применения Образцы текста публицистического стиля Четыре типа изменения баланса Задачи с ответами для Всероссийской олимпиады по праву 
Мы поможем в написании ваших работ! ЗНАЕТЕ ЛИ ВЫ?
Влияние общества на человека
Приготовление дезинфицирующих растворов различной концентрации Практические работы по географии для 6 класса Организация работы процедурного кабинета Изменения в неживой природе осенью Уборка процедурного кабинета Сольфеджио. Все правила по сольфеджио Балочные системы. Определение реакций опор и моментов защемления |
Кафедра «Высшая математика и информатика»Стр 1 из 3Следующая ⇒
Кафедра «Высшая математика и информатика» Абрамченко Н.В. Мещеряков Е.А. Мещерякова Н.А., Ультан А.Е. Реализация математических методов и моделей в MS Excel. Компьютерный практикум Учебное пособие
Омск 2018 УДК 004.942 ББК 32.972.13
Рецензенты: д. пед н., профессор, зав. кафедрой «Информационная безопасность» Сибирской автомобильно-дорожной академии Семенова З.В. к. пед. н., доцент, доцент кафедры «Информационная безопасность» Сибирской автомобильно-дорожной академии Анацкая А.Г.
Абрамченко Н.В., Мещеряков Е.А., Мещерякова Н.А., Ультан А.Е. Реализация математических методов и моделей в MS Excel. Компьютерный практикум: Учебное пособие – Омск: Издательский центр КАН, 2018. – 85 c.
ISBN
Учебное пособие предназначено для студентов направлений подготовки "Менеджмент" и "Экономика", обучающихся по программам бакалавриата в рамках изучения дисциплин "Математика", "Компьютерный практикум". В пособии рассматриваются вопросы использования MS Excel для решения математических задач, нацеленные на формирование у студентов с помощью компьютерных вычислений практических навыков по реализации математических методов и моделей, применяемых в профессиональных задачах. Пособие может быть полезно всем желающим использовать компьютерные модели для решения аналитических, расчетных и финансовых задач в практической деятельности. Рекомендовано Учебно-методическим советом Омского филиала Финуниверситета в качестве учебного пособия по дисциплинам "Математика", "Компьютерный практикум", для студентов, обучающихся по направлениям 38.03.02 "Менеджмент", 38.03.01 "Экономика", (программы подготовки бакалавров). Протокол от 16 ноября 2017 г. № 9.
УДК 004.942 ББК 32.972.13 ISBN
© Абрамченко Н.В., 2018 © Мещеряков Е.А., 2018 © Мещерякова Н.А., 2018 © Ультан А.АЕ, 2018 СОДЕРЖАНИЕ Введение. 4 Тема 1.............................................. Адресация. Ввод формул (2 часа) 6 Тема 2..................................................... Встроенные функции (2 часа) 14 Тема 3.................... Встроенные функции (продолжение) (4 часа) 20 Тема 4. Встроенные функции (продолжение) Решение систем линейных уравнений (4 часа). 30 Тема 5...... Вычисление значений функции. Средство анализа «Подбор параметра» (2 часа) 42
Тема 6................................ построение графиков функции (2 часа) 47 Тема 7. исследование числовых характеристик функции (4 часа) 50 Тема 8................ Моделирование числовых и функциональных последовательностей. вычисление пределов последовательносте (2 часа) 55 Тема 9...................... Графическое построение асимптот (4 часа) 59 Тема 10. Средство анализа «Поиск решения» (4 часа) 66 Тема 11. Приближенное вычисление производной функции (2 часа) 74 Тема 12. Полное Исследование функции (4 часа) 78 СПИСОК ЛИТЕРАТУРЫ И ССЫЛКИ НА РЕСУРСЫ ИНТЕРНЕТ. 86 Введение Учебное пособие предназначено для формирования у студентов знаний о вычислительных методах реализации математических объектов и моделей, используемых в экономике и финансах, а также о средствах визуализации математических результатов исследований. Рассматриваются вопросы формирования у студентов практических навыков по использованию компьютерных технологий в вычислительных задачах экономики и финансов. Экономическую информацию удобно представлять в виде таблиц (накладная, начисление заработной платы, график погашения кредита и т.д.) для быстрого выполнения различных расчетов при изменении исходных данных. Сводные экономические показатели вычисляются функционально на основе других экономических показателей. В общем случае функция может быть задана аналитически, таблично или в виде графика. Аналитический способ задания функции заключается в задании связи между аргументом в виде формулы или системы формул, например Y= Х2. Табличный способ задания функций имеет широкое распространение в различных областях знаний и приложениях: экспериментальных измерениях, таблицах бухгалтерской отчетности и банковской деятельности, статистических данных и т.п. Каждому значению независимой переменной Х соответствует значение функции Y, записанное в той же строке таблицы. Графическое представление функции позволяет наглядно представить характер поведения функции. Таблица – это совокупность данных, упорядоченных по строкам и столбцам. Для решения задач, представляемых в виде таблиц, разработаны специальные пакеты программ, которые называют электронными таблицами или табличными процессорами.
Наиболее популярным программным продуктом этого класса является табличный процессор MS Excel, приложение из пакета MS Office системы Windows. Процессор включает широкий набор вычислений, графическую интерпретацию данных, автоматическое обобщение и анализ содержащихся в них данных. Любая функция в MS Excel может быть задана таблично с использованием формул и в виде графика. Выделим следующие основные функции Excel: • решение экономических задач (создание бланков, планирование производства, расчет налогов и заработной платы, учет кадров и затрат); • решение инженерных задач; • решение математических задач; • построение графиков и диаграмм. Табличный процессор Ехсеl позволяет: • использовать для хранения таблиц рабочую книгу, состоящую из отдельных листов, в ячейках которых можно применять различные средства корректировки и обработки данных; • оформлять таблицы, применяя самые разнообразные приемы редактирования и форматирования на уровне символов, ячеек, строк и столбцов и др.; • производить вычисления, используя не только создаваемые самим пользователем формулы, но и большой набор готовых встроенных функций; • осуществлять математическое моделирование и численное экспериментирование с данными в таблицах (типа что будет, если); • осуществлять статистический анализ, прогнозирование и оптимизацию результатов вычислений (поддержку принятия решений); • наглядно представлять зависимости между данными в виде диаграмм; • реализовать функции базы данных при больших объемах хранимых записей; • обеспечивать многоступенчатую защиту хранимой информации; • осуществлять обмен информацией с другими программными средами. Бесспорным достоинством электронных таблиц для профессиональных экономистов является практически мгновенный пересчет всех содержащихся в таблице данных, которые связаны между собой формулами, при изменении значения любого из данных! Тема 1. Адресация. Ввод формул (2 часа) Адресация ячеек Ячейка – минимальная структурная единица на рабочем листе. Каждая ячейка имеет свой адрес, состоящий из имени столбца и имени строки, например, B25, WA5000. Ячейка, в которой в данный момент времени находится курсор, считается активной. Она выделяется жирным контуром. В активную ячейку можно вводить данные и производить над ними различные операции.
Рис. 1. Интерфейс системы MS Excel
Рис. 2. Поле адреса и строка формул MS Excel Если установить курсор в строку формул, то в поле адреса станет активным список доступных встроенных в MS Excel функций.
Адреса ячеек (ссылки на ячейки) используются в формулах и функциях в качестве аргументов. При копировании формул в другие ячейки адреса ячеек изменяются (наращиваются при копировании вниз и вправо и уменьшаются при копировании вверх и влево). В ячейки рабочего листа вводятся два вида данных: постоянные значения и формулы. В качестве постоянных значений могут выступать текст, числа, символы, даты. Однако есть случаи, когда в качестве одного из аргументов функции, которую предполагается копировать в другие ячейки, выступает некоторая константа, например, процентная ставка в финансовой операции. Для того чтобы отменить автоматическое изменение адреса ячейки при копировании, необходимо назначить ей абсолютный (или смешанный) адрес. Для этого нужно поставить перед номером столбца и (или) перед номером строки знак доллара «$». Например, в адресе $А5 при копировании формулы вправо не будет меняться номер столбца, в адресе В$7 при копировании формулы вниз не будет меняться номер строки, а в адресе $Н$5 при любом копировании формулы не будет меняться ни тот, ни другой номер. В первых двух случаях адрес называется смешанным, в последнем – абсолютным. Для быстрейшего проставления знак доллара «$» в нужное место можно воспользоваться функциональной клавишей F 4, последовательное нажатие которой дает четыре возможных варианта адресации: абсолютную, две смешанные, относительную. Любая ячейка электронной таблицы может быть заполнена данными. MS Excel поддерживает следующие типы данных: общий (отображает числовые и текстовые данные произвольного типа); числовой (включает цифры, десятичную точку, процент и др.)); денежный (для отображения денежных величин, включая количество десятичных знаков, обозначение, например " руб. " и др.); дата/время (отображает дату и время в выбранном формате); процентный (для вывода чисел, предварительно умноженных на 100, с символом процента); дробный (для вывода дробных чисел); экспоненциальный (для вывода чисел в экспоненциальном формате); текстовый (содержит буквы, цифры, специальные знаки); дополнительный (содержит нестандартные форматы (номер телефона, почтовый индекс и др.)). Ячейки и диапазоны ячеек могут иметь имена, которые также могут использоваться в формулах и в функциях. В случае использования имен для ячеек или диапазонов в формулах пропадает необходимость использования абсолютных ссылок при копировании формул.
Ввод формул. Автозаполнение Формула – это указание для Excel выполнить определенные вычисления. Основное правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко выполнить в «уме»! Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений. Формула всегда начинается со знака "="! Формула состоит из операндов, соединенных между собой арифметическими или логическими операторами: "+", "-", "*" (умножить), "/" (разделить), "^" (возвести в степень), И (логическое), ИЛИ (логическое), "=", ">", ">=", "<", "<=", "<>" (логическое сравнение "не равно"), % (процент). В качестве операндов используются: · числа или строки; · ссылки на ячейки; · стандартные встроенные функции (СУММ (диапазон), COS (число), МАКС (диапазон) и т.д.) и их аргументы. Чтобы в формуле сослаться на ячейку, корректнее кликнуть мышью по этой ячейке, а не вводить адрес вручную. Завершается Арифметические действия в формулах можно производить лишь в том случае, если содержимое ячеек, в которых располагаются операнды, имеет числовые значения или даты! В противном случае в ячейке появится выражение #ЗНАЧ! Формулы можно копировать из одной ячейки в другие, соседние ячейки маркером Автозаполнение, в несмежные – по правилам копирования. Правила копирования подразумевают копирование в буфер обмена и вставку из буфера обмена, использовав контекстное меню либо сочетание клавиш Ctrl +С (копировать), Ctrl +Х (вырезать), Ctrl + V (вставить), а также можно «отбуксировать» содержимое ячейки, удерживая нажатой клавишу Ctrl, при этом курсор должен приобрести вид крестика со стрелочками на концах (обычный вид курсора – объемный белый крестик). Чтобы получить маркер Автозаполнение, курсор подводят к правому нижнему углу ячейки, он должен принять вид тонкого черного крестика, и далее формулу "протягивают" вниз или вправо на необходимое число ячеек. При этом меняются адреса ячеек, входящих в формулу! В основном при вычислениях это и требуется по условию задачи, т.е. в каждой последующей ячейке получают значение функции от нового аргумента, например, вычисляют наращенную сумму для каждого последующего периода финансовой операции. Задание 1.1. Вычислите, используя абсолютный адрес ячейки. Постройте столбчатую гистограмму по первому и последнему столбцу. Таблица 3.
Задание 1.2. Транспортная фирма выполняет перевозки грузов по тарифу 100 руб. за 1 тонно-километр. Разработайте таблицу расценок для перевозки грузов на различные расстояния. Предусмотрите возможность простого и быстрого изменения тарифа. Таблица 4.
Задание 1.3. Создайте таблицу согласно образцу. Автоматически пронумеруйте строки. Таблица 5. Номер п/п |
Показатели |
Год |
Итого за год | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Кв. |
Кв. |
Кв. |
Кв. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Продано единиц | 3592 | 4390 | 3192 | 4789 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Торговые доходы | 143662 | 175587 | 127700 | 191549 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Торговые расходы | 89789 | 109742 | 79812 | 119712 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Валовая прибыль |
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Расходы на зарплату | 8000 | 8000 | 9000 | 9000 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Расходы на рекламу | 10000 | 10000 | 10000 | 10000 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Накладные расходы фирмы | 21549 | 26338 | 19155 | 28732 |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Общие затраты |
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Производственная прибыль |
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Удельная валовая прибыль |
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ввод формулы нажатием клавиши Enter или кнопкой, которая находится левее строки формул.
1). Выполните необходимые расчеты:
- валовую прибыль рассчитать как разность между торговыми доходами и торговыми расходами;
- общие затраты состоят из суммы трех предыдущих строк;
- производственная прибыль рассчитывается как разность между валовой прибылью и общими затратами;
- удельная валовая прибыль рассчитывается делением производственной прибыли на торговые доходы
- колонка «Итого» рассчитывается суммированием квартальных данных.
(В последней строке должны получиться следующие результаты: 1 кв. – 10%, 2 кв. – 12,2%, 3 кв. – 7,6%, 4 кв. – 12,6%, итого за год – 10,9%);
2). Постройте гистограмму по строке Валовая прибыль для четырех кварталов года.
3). На отдельном листе постройте нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы. Вид диаграммы приведен ниже.
Рис. 6. Общий вид нормированной гистограммы
Тема 2. Встроенные функции (2 часа)
Помимо формул для вычислений в MS Excel используют большой класс встроенных функций. Функции позволяют производить вычисления, которые невозможно выполнить с помощью формул (например, для расчета приближенного числа дней ссуды). Чаще функции используют для упрощения и ускорения вычислений в формулах, например, для нахождения среднего значения содержимого двадцати ячеек необходимо сначала просуммировать эти двадцать значений, а затем полученную сумму поделить на 20, тогда как можно использовать одну встроенную функцию СРЗНАЧ(диапазон). Также использование встроенных функций управления позволяет накладывать условия на выполнение формул, что позволяет вычислять альтернативные варианты и способствует поддержке в принятии решений.
Как вы уже заметили выше, встроенные функции имеют аргументы, которые записываются в скобках после имени функции через точку с запятой, и являются ссылками на ячейки, диапазонами ячеек, именами диапазонов, массивами, другими функциями, математическими выражениями или текстовыми строками. Есть функции и без аргументов, например функция СЕГОДНЯ (), которая возвращает значение текущей даты. Есть аргументы, которые вводить обязательно, а есть и необязательные аргументы, например, у встроенных финансовых функций (будут рассмотрены ниже в отдельном параграфе) не все аргументы являются обязательными. Функции добавляются в формулы кнопкой fx левее строки формул (рис. 1) или через пункт меню Формулы / Вставить функцию.
Задание 2.1.
Рассчитайте средний балл студентов за сессию. Определите количество студентов в группе, средний балл которых больше среднего по группе (используйте функции СРЗНАЧ и СЧЕТЕСЛИ)
Таблица 8.
| Фамилия | История | Математика | Философия | Информатика | Средний балл |
| Петров | 4 | 3 | 5 | 5 |
|
| Иванов | 5 | 5 | 4 | 5 |
|
| Сидоров | 3 | 4 | 4 | 4 |
|
| Иванцов | 5 | 4 | 4 | 4 |
|
| Мишин | 3 | 3 | 4 | 4 |
|
| Лавров | 4 | 4 | 5 | 3 |
|
| Кубиков | 3 | 3 | 3 | 3 |
|
| Средний балл по группе: |
| ||||
| Количество студентов со средним балом выше среднего по группе: |
| ||||
Задание 2.2. Определите место каждой команды
Таблица 9.
| Команда | Очки | Сумма | Место | ||
| Метеор | 10 |
| 12 |
|
|
| Ландыш |
| 12 | 20 |
|
|
| Звезда | 12 | 11 |
|
|
|
| Прогресс | 10 | 11 | 12 |
|
|
| Заря | 15 | 12 |
|
|
|
Задание 2.3.
В примере 2.4. добавьте справа столбец «Фамилия И.О.» и заполните ее данными, например, «Авдеев П.В.» (дополнительно используйте функцию ЛЕВСИМВ).
Задание 2.4.
Рассчитайте количество магазинов, имеющих суммарную выручку до 1000 тыс. руб., от 1000 тыс. руб. до 1500 тыс. руб., от 1500 тыс. руб. до 2000 тыс. руб. и свыше 2000 тыс. руб.
Таблица 10
| Магазин | Суммарная выручка | Диапазон выручки | Кол-во магазинов | |
| магазин 1 | 1293 | 1000 |
| |
| магазин 2 | 1434 | 1500 |
| |
| магазин 3 | 1606 | 2000 |
| |
| магазин 4 | 2108 |
|
| |
| магазин 5 | 2146 |
|
| |
| магазин 6 | 2080 |
|
| |
| Итого | 10667 |
|
|
Тема 3. Встроенные функции (продолжение) (4 часа)
Задание 3.1.
В задании 1.2 предусмотрите колонку "Машина" для оптимального выбора машины в зависимости от веса груза (если вес груза < 1,5 тонны, то выбрать Газель, Если вес груза < 5 тонн, то выбрать ЗИЛ, иначе выбрать КАМАЗ).
Таблица 13.
| Расстояние | Вес груза | Стоимость перевозки | Машина |
| 5 | 0,5 | ||
| 10 | 1 | ||
| 25 | 4,5 | ||
| 50 | 2 | ||
| 32 | 5 | ||
| 40 | 4 | ||
| 25 | 1,5 | ||
| 20 | 5,8 | ||
| 55 | 3 |
Задание 3.2.
Рассчитайте размер пособия зависит от стажа и от количества рабочих дней, пропущенных по болезни.
• Если стаж до 5 лет, то пособие определить: Оклад / Кол. раб. дней*60% * Кол. дней, пропущенных по болезни;
• Если стаж от 5 до 8 лет – Оклад / Кол. раб. дней*80% * Кол. дней, пропущенных по болезни;
• Если стаж 8 лет или свыше – Оклад / Кол. раб. дней * Кол. дней, пропущенных по болезни.
Рис. 20. Компьютерная модель задания 3.3
Задание 3.3. Рассчитайте средний балл каждого студента и определите, кто из студентов допущен к экзамену, используя встроенные функции И и ЕСЛИ (допущен, если каждая оценка >= 3).
Таблица 14.
| Фамилия | Задание 1 | Задание 2 | Задание 3 | Задание 4 | Средний балл | Допуск |
| Петров | 4 | 3 | 4 | 5 |
|
|
| Иванов | 3 | 3 | 4 | 5 |
|
|
| Сидоров | 3 | 4 | 2 | 4 |
|
|
| Иванцов | 5 | 1 | 2 | 2 |
|
|
| Мишин | 3 | 3 | 4 | 4 |
|
|
| Лавров | 4 | 2 | 2 | 3 |
|
|
| Кубиков | 3 | 2 | 3 | 3 |
|
|
Задание 3.4. Определите отклонение от плана, суммарные плановые и фактические расходы, а также отклонение суммарных фактических расходов от плановых. При превышении бюджета назначить штраф: если превышение бюджета по позиции больше, чем суммарное превышение, то размер штрафа 100 000 руб., иначе – 10 000 руб.
Таблица 15.
| № п/п | Фактические расходы (тыс. руб.) | Плановые расходы (тыс. руб.) | Превышение бюджета | Штраф |
| 1 | 1200 | 900 |
|
|
| 2 | 500 | 700 |
|
|
| 3 | 975 | 1000 |
|
|
| 4 | 800 | 725 |
|
|
Задание 3.5. Назначьте буквенную категорию указанным баллам, считая, что 89-100 – "А", 79-88 – "В", 69-78 – "С", 59-68 – "D", менее 59 – "Е" (используйте функцию ПРОСМОТР)
Таблица 16.
| Фамилия | Средний балл | Значение |
| Петров | 77 |
|
| Иванов | 18 |
|
| Сидоров | 80 |
|
| Иванцов | 95 |
|
| Мишин | 64 |
|
| Лавров | 58 |
|
| Кубиков | 69 |
|
Задание 3.6. В ячейку H2 введено выражение:
ЕСЛИ(И(A2<>1;C2<2);G2*СУММ(B2:G2);ЕСЛИ(И(A2=1;C2=7);5+G2;КОРЕНЬ(B2+C2))).
Что отобразится в ячейке Н2, если в следующие ячейки введены значения: А2=1 В2=7 С2=7 D2=9 E2=2 F2=2 G2=5?
Тема 4. Встроенные функции (продолжение) Решение систем линейных уравнений (4 часа).
Матричные функции
Матричные функции используют для решения систем уравнений размерности n x n, т.е. когда число уравнений в системе равно числу неизвестных:
функция МОПРЕД(массив) возвращает определитель матрицы;
функция МОБР(массив) служит для нахождения обратной матрицы;
функция МУМНОЖ(массив1; массив2) возвращает произведение матриц.
Для нахождения определителя матрицы выделите свободную ячейку, вызовите встроенную функцию МОПРЕД, где в качестве массива укажите диапазон ячеек с исходной матрицей, нажмите ОК. Результат вычисления появится в выделенной ячейке.
Для нахождения обратной матрицы выделите пустой диапазон такого же размера, как и исходная матрица, вызовите функцию МОБР, где в качестве массива укажите диапазон ячеек с исходной матрицей, нажмите сочетание клавиш Shift+Ctrl+Enter. В выделенном диапазоне отобразится обратная матрица.
Нахождение произведения матрицы А на матрицу В возможно, если число столбцов матрицы А равно числу строк матрицы В. Для нахождения произведения матриц выделите пустой диапазон такого же размера, как и исходная матрица В, вызовите функцию МУМНОЖ, где в качестве массива1 укажите диапазон ячеек с исходной матрицей А, в качестве массива2 – диапазон ячеек с матрицей В и нажмите сочетание клавиш Shift+Ctrl+Enter. В выделенном диапазоне отобразится результат умножения матриц.
Над диапазонами, также как и над числами, можно производить арифметические операции, только в этом случае завершаться операция должна не нажатием клавиши Enter, а нажатием сочетания клавиш Shift+Ctrl+Enter!
Пример 4.3.
Используя матричные функции, решите систему уравнений:
2х1 – 2х2 + х3 = 2
х1 + 2х2 + 3х3 = 4
2х1 – 4х2 + 6х3 = 8
Решение. Для решения задачи на листе Excel введем матрицу А и вектор В (рис. 22). В некоторой пустой ячейке (С6) найдем определитель матрицы А, используя встроенную функцию МОПРЕД.
Рис. 22. Исходные данные задачи и определитель матрицы
Если определитель матрицы отличен от 0, то система имеет решение. В нашем случае определитель равен 40, т.е. система решение имеет. Найдем это решение. Выделим на листе диапазон пустых ячеек размерности 3х3, например, В8: D 10, вызовем встроенную функцию МОБР, где в качестве массива укажем диапазон ячеек В2: D 4, нажмем сочетание клавиш Shift+Ctrl+Enter.
В выделенном диапазоне отобразится обратная матрица. Если Вы не увидели в диапазоне желаемого результата (значение появилось только в одной ячейке, вообще нигде не появилось и др.), нажмите клавишу F2 (в первой ячейке диапазона отобразится =МОБР(B2:D4)), затем Shift+Ctrl+Enter.
Если результат опять Вас не устраивает, значит, Вы не выделили диапазон, равный по размерности обратной матрице!
Рис. 23. Решение системы линейных уравнений
Для нахождения вектора Х выделим пустой диапазон ячеек размерности 1х3, например, F 8: F 10, вызовем встроенную функцию МУМНОЖ, где в качестве массива1 укажем диапазон ячеек с обратной матрицей В8: D 10, в качестве массива2 – диапазон ячеек F 2: F 4 с вектором В и нажмем сочетание клавиш Shift+Ctrl+Enter. Результат выполнения задания приведен на рисунке 23. В ячейке F9 отображается число 0 в экспоненциальном формате, указывающем на некоторую погрешность вычислений.
Пример 4.4.
Решите систему уравнений методом Крамера
2х1 – 2х2 + х3 = 2
х1 + 2х2 + 3х3 = 4
2х1 – 4х2 + 6х3 = 8
Решение. Решим ту же систему, что и в предыдущем примере, методом Крамера, и сравним полученные решения. Для этого на листе Excel скопируем и вставим в свободные диапазоны три раза матрицу А (рис. 24). Последовательно заменим столбцы этих матриц на вектор свободных членов: в первой матрице – первый столбец, во второй матрице – второй столбец, в третьей – третий. В ячейках К12:К14 найдем определители полученных матриц, используя встроенную функцию МОПРЕД. Далее в ячейках G 8: G 10 найдем отношения частных определителей к общему (рис. 24). Полученные разными способами решения совпадают.
Рис. 24. Исходные данные задачи и определитель матрицы
Пример 4.7.
В таблице приведены данные о дневной производительности пяти предприятий холдинга, выпускающих четыре вида продукции с использованием трех видов сырья, а также продолжительность работы каждого предприятия в году и цена каждого вида сырья.
Таблица 17.
| Вид изделия № | Производительность предприятия (изд. / день) | Затраты видов сырья (ед. веса / изд.) | |||||||
| 1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | ||
| 1 | 4 | 5 | 3 | 6 | 7 | 1 | 2 | 3 | |
| 2 | 0 | 2 | 4 | 3 | 0 | 3 | 5 | 6 | |
| 3 | 8 | 15 | 0 | 4 | 6 | 4 | 4 | 5 | |
| 4 | 3 | 10 | 7 | 5 | 4 | 5 | 8 | 6 | |
| Количество рабочих дней в году | Цены видов сырья (ден. ед. / ед. веса) | ||||||||
| 1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | ||
| 200 | 150 | 170 | 120 | 140 | 40 | 50 | 60 | ||
Требуется определить: годовую производительность каждого предприятия по каждому виду изделий; годовую потребность каждого предприятия в каждом виде сырья; годовую сумму финансирования каждого предприятия холдинга для закупки сырья, необходимого для выпуска продукции.
Решение. Перенесем исходные данные на лист Excel. Компьютерная модель задачи имеет вид (рис. 29).
Для расчета годовой производительности каждого предприятия необходимо найти произведение количества произведенных изделий в день каждым предприятием на количество рабочих дней в году каждого предприятия. Для этого выделим весь диапазон В16: F 19 и введем формулу: =B4:F7*B11:F11, закончив ее ввод сочетанием клавиш Shift+Ctrl+Enter.
Аналогично рассчитаем матрицу финансовых затрат на каждый вид сырья для каждого изделия (диапазон (K 4:М7): {=G4:I7*G11:I11}).
Для расчета годовой потребности каждого предприятия в каждом виде сырья необходимо найти сумму произведений годового выпуска каждого изделия на затраты каждого вида сырья на единицу изделия. Для этого транспонируем матрицу затрат видов сырья. Для первого изделия в диапазоне В24:В26 найдем произведение полученной транспонированной матрицы (диапазон I 23: L 25) на вектор годовой производительности первого предприятия (диапазон В16:В19). Формулу скопируем вправо для расчета годовой потребности в сырье других предприятий (в диалоговом окне функции МУМНОЖ не забудьте закрепить общий для всех диапазон I 23: L 25!).
Рис. 29. Компьютерная модель примера 5.5.
