Обчислити визначник третього порядку 3 страница
8.3. На решті листів ввести такі ж 6 записів.
9. Пошук записів по критерію: Данные ð Форма ð Критерии
9.1. Переглянути через вікно Форма записи: службовці, ФАМИЛИЯ у яких починається на букву В і НАЧИСЛЕНО >100
9.2. Переглянути через вікно Форма записи: службовці, ФАМИЛИЯ у яких починається на букву В, НАЧИСЛЕНО >100 і К ВЫДАЧЕ <800
9.3. Переглянути через вікно Форма записи: службовці, у яких ПРЕМИЯ >50
9.4. Переглянути через вікно Форма записи: службовці з відділу САПР, у яких
К ВЫДАЧЕ < 200
10. Сортування записів: Данные ð Сортировка
10.1. Відсортувати записи по ФАМИЛИИ по возрастанию, а по НАЧИСЛЕНО по убыванию
10.2. Повернути назад, тобто відсортувати по № П/П
11. Написати макрос на сортування по ОТДЕЛУ по возрастанию, по ПРЕМИИ по убыванию і перевірити його роботу.
12. Створити зведену таблицю для обчислення середнього значення премії в січні, слідуючи вказівкам Майстра зведених таблиць, для чого,
12.1. на першому кроці вибрати В списке или базе данных ðДалее ð
12.2. на 2 кроці на листі Январь, виділити таблицю нарахування зарплати за виключенням №п /п, тобто стовпця АðДалее ð
12.3. на 3 кроці включіть кнопку Макет ð в рядок потягніть – Отдел а в дані – Премия ðподвійне клацання по Премия в розділі Данные ðвиберіть середнє значенняð ОКðОКð
12.4. виберіть новий лист і ОК.
13. По отриманій зведеній таблиці побудуйте гістограму і відформатуйте її.
14. Збережіть книгу Зарплата.
Практична робота № 19.
Тема: Робота в базі даних. Фільтр, розширений фільтр. Підсумки в базі даних.
Мета роботи: Навчитися працювати в базі даних: фільтрувати записи, обчислювати підсумки.
Хід роботи.
1.Запустити програму Excel і відкрити книгу«Зарплата».
2. Робота з фільтром: ДанныеÞФильтрÞАвтофильтр.Кожного разу при зміні умов фільтрації вибирати в списку ВСЕ.
Ø Відібрати тих, у яких ФАМИЛИЯ починаються на В або К
Ø Відібрати тих, у яких НАЧИСЛЕНО більше 100 і менше 500
Ø Відібрати 7 з найбільшою сумою К ВЫДАЧЕ і ПН ЗА МЕСЯЦ більше 200 (непотрібні стовпці приховати).
Ø Відібрати співробітників відділу САПР з найбільшою ПРЕМИЕЙ
Ø Показати тільки записи відділу ОАСУП, в яких НАЧИСЛЕНО більше 500, але менше 1000.
Ø Вихід з фільтру Данные Þ Фильтр Þ Автофильтр.
3. Написати макрос на фільтр по відділу ОАСУП
4. Написати макрос на фільтрНАЧИСЛЕНО більше 100 і менше 500
5. Написати макрос на фільтрК ВЫДАЧЕ більше 300, але менше 1000.
6. Робота з розширеним фільтром: ДанныеÞФильтрÞРасширенный Фильтр:
6.1. Відібрати записи, що містять Фамилию, Отдел і К выдаче тих, хто має Фамилию, що починається на букву В і К выдаче < 500.
6.2. Порядок дій :
6.2.1. Для завдання области критерияскопіювати назви полів Фамилия та К выдаче за базою даних (вниз)
6.2.2. В наступному рядку записати критеріїВ* і >500
| Фамилия | К выдаче |
| В* | <500 |
6.2.3. Для завдання целевой области скопіювати назви полів
| Фамилия | Отдел | К выдаче |
6.2.4. Встати на будь-яку комірку бази даних і Данные Þ Фильтр Þ Расширенный Фильтр
6.2.5. Заповнити діалогове вікно: перший рядок буде заповнений, на другому – виділіть мишею область критерия, щоб був активним третій рядок, включіть опцію Скопировать результат в другое место і відзначте мишею целевую область і ОК.
6.2.6. Включіть ще раз цей же фільтр, тільки зніміть опцію Скопировать результат в другое место. Подивіться результат і зніміть фільтр Данные Þ Фильтр Þ Отобразить все.
6.3. Відібрати записи, що містять Фамилия, Отдел і К выдаче тих, хто має Фамилия, що починається на букву В ИЛИ К выдаче >500. Порядок дій:
6.3.1. Для завдання области критерия скопіювати назви полів Фамилия К выдаче за базою даних (внизу)
6.3.2. В наступному рядку записати критерий В*, а на рядок нижче >500
| Фамилия | К выдаче |
| В* | |
| >500 |
6.3.3. Для завдання целевой области скопіювати назви полів Фамилия Отдел К выдаче
| Фамилия | Отдел | К выдаче |
6.3.3.1. Встати на будь-яку комірку бази даних і Данные Þ Фильтр Þ Расширенный Фильтр
6.3.3.2. Заповнити діалогове вікно: перший рядок буде заповнений, на другій – виділите Мишею область критерия, щоб був активним третій рядок, включіть опцію целевую область і відзначте мишею целевую область і ОК.
6.3.3.3. Включіть ще раз цей же фільтр, тільки зніміть опцію целевую область. Подивіться результат і зніміть фільтр Данные Þ Фильтр Þ Отобразить все.
7. Написати макрос на розширений фільтр: Відібрати записи, що містять Фамилия, Отдел і К выдаче тих, хто має Фамилия, щопочинається на букву С ИЛИК выдаче >300.
8. Итоги: ДанныеÞИтоги
8.1. Обчислити Промежуточные итоги, чому дорівнюють сумарні НАЧИСЛЕНО і К ВЫДАЧЕ при зміні ФАМИЛИИ, не забудьте встати на комірку списку і відсортувати його по ФАМИЛИИ.
8.2. Переглянути кожний з 3-х рівнів структури
8.3. Вийти з ИТОГОВ: Данные Þ Итоги Þ Убрать все
9. Написати макрос наотримання Промежуточных итогов по НАЧИСЛЕНО і ПРЕМИЯ при зміні ОТДЕЛА.
10.Збережіть книгуЗарплата.
Практична робота №20.
Тема: Рішення задач оптимізації. Лінійна оптимізація.
Мета роботи: Навчитися застосовувати надбудову «Пошук рішення» в задачах знаходження найменшого і найбільшого значення (лінійна оптимізація).
Хід роботи.
1. Задача 1: Фірма робить дві моделі А і В книжкових полиць. Їх виробництво обмежене наявністю сировини (високоякісних матеріалів) і часом машинної обробки. Для кожного виробу моделі А потрібно 3м2 дошки, а для виробу моделі В 4м2. Фірма може отримувати від своїх постачальників до 1700м2 дошки в тиждень. Для кожного виробу моделі А потрібно 12 хвилин машинного часу, а для виробу моделі В - 30 хв. В тиждень можна використовувати 160 годин машинного часу. Скільки виробів кожної моделі слід випускати фірмі в тиждень, якщо кожний виріб моделі А дає 2$ прибутку, а кожний виріб моделі В - 4$ прибутку.
1.1. Розв’язування:Побудуємо математичну модель. Позначимо х - кількість виробів моделі А, y - кількість виробів моделі В. Прибуток від цих виробів дорівнює: 2х+4y($). Цей прибуток потрібно максимізувати. Функція, для якої шукається екстремум (максимум або мінімум), носить назву цільової функції. Безмежному збільшенню кількості виробів перешкоджають обмеження. Обмежена кількість матеріалу для полиць, звідси нерівність 3х+4y≤1700. Обмежений машинний час на виготовлення полиць. На виріб А йде 0.2 години, на виріб В — 0.5 годин, а усього не більше 160 годин, тому 0.2х + 0.5у≤160. Крім того, кількість виробів додатне число, тому х > 0, у> 0.
Формально задача оптимізації записується так:
2х + 4у → max
3х+4y≤1700
0.2х + 0.5у≤160
х>0, y>0
1.2.Робочому листу1 дайте ім’я: Задача1 і коміркам В2 і В3 присвойте імена х і у. В комірках В6, В9 і В10 внесіть потрібні формули, їх вигляд записаний в комірках С6,С9, С10:
1.3.Цільова комірка В6. Станьте в В6 ðСервис ðПоиск Решенияðв діалоговому вікні Поиск решения (назвемо його Вирішувач) перевірте адресу в полі вводу: «Установить целевую ячейку», перемикач - максимальное значение, кнопка Предположитьі в полі вводу з’явиться адреса $B$2:$B$3. Для вводу обмежень клацніть на кнопку Добавить і в діалоговому вікні «Добавление ограничений», у полі вводу «Ссылка на ячейку:» укажіть $В$9. Правіше розташований список з умовними операторами. Виберіть умову <=. У полі вводу «Ограничение:» введіть число 1700, не виходячи з цього вікна, клацніть кнопку «Добавить» и введіть обмеження $В$10<=160 ðОК. Ви знову у вікні «Поиск решения». Ви побачите введені обмеження $В$10<=160 і $В$9<=1700.
1.4.Кнопка Параметрыðустановіть 2 прапорці: Линейная модельі Неотрицательные значения
1.5.ðОк ðкнопка Выполнить.
1.6.У вікні «Результаты поиска решения»виберіть усі 3 варіанти Тип отчета: результаты, устойчивость, пределы.Перемикач «Сохранить найденное решение» ðОк.
1.7.Відповідь: потрібно випускати: изделия А – 300 штук, изделия В – 200 штук, прибуток – 1400$.
2. Задача 2: Є три сплави. Перший сплав містить 70% олова і 30% свинцю, другий - 80% олова і 20% цинку, третій - 50% олова, 10% свинцю і 40% цинку. З них необхідно виготовити новий сплав, що містить 15% свинцю. Який найбільший і найменший відсотковий вміст олова може бути в цьому сплаві?
2.1. Розв’язання:Нехай u — кількість першого сплаву, v — кількість другого сплаву, w — кількість третього сплаву, взяті для виготовлення нового сплаву. Оскільки в сплаві повинно бути 15% свинцю, отримуємо рівняння:
Кількість олова в новому сплаві:
Для цієї функції трьох додатних змінних треба знайти найбільше і найменше значення. Доречно перейти до нових змінних:
Тоді ми отримаємо обмеження: 0.3a + 0.1z-0.15 = 0 і a+b+z-l=0, причому змінні a, b, z додатні. Цільова функція має вигляд: 0.7a + 0.8b + 0.5z .
2.2.Заповнимо робочий лист 2. Дайте йому ім’я: Задача2.
2.3.У стовпці С показані формули із стовпця В. Коміркам (В2:В4) дайте ім’я: a, b, z - відповідно. На комірку В7 накладіть процентний формат. Виділить комірку В7 і викличте Поиск решения. Звичайним порядком задайте комірку з цільовою функцією (В7), Изменяя значения (В2:В4) і ограничения (В10:В11=0).
2.4.Клацнувши кнопку "Параметры", встановіть прапорці "Линейная модель" і "Неотрицательные значения". Спочатку вкажіть, що треба шукати максимальне значення. Після виконання розрахунків отримаєте вже знайоме діалогове вікно "Результаты поиска решения". Запишіть в звіт з практичної роботи максимальне значення відсоткового вмісту олова в цьому сплаві.
2.5.Клацніть кнопку "Сохранить сценарий". З'явиться діалогове вікно "Сохранение сценария". Введіть назву сценарію "max".
2.6.Знову з'явиться вікно "Результаты поиска решения". Установіть перемикач "Восстановить исходные значения" і закрийте це вікно.
2.7.Знову викличте Поиск решения і знайдіть мінімальне значення цільової функції, дайте сценарію назву "min". Запишіть в звіт з практичної роботи мінімальне значення відсоткового вмісту олова в цьому сплаві.
2.8. Робота зі сценаріями. Виконайте команду меню: Сервис ð Сценарии. З'явиться діалогове вікно Диспетчер сценариев. У ньому перераховані сценарії поточного робочого листа: min і max. Нижче вказані адреси змінюваних комірок: $В$2:$В$4.
2.9.Виділіть в списку імен сценаріїв рядок max і клацніть кнопку Вывести ð Закрыть. На рабочому листі з'являться числа
Увага, якщо у формулі цільової функції стоїть #Знач!, то треба змінити у вікні Настройка региональных параметров Разделитель целой и дробной части на точку.
2.10.Знову визвіть: Сервис ð Сценарии. Виділіть в списку імен сценаріїв рядок min і клацніть кнопку Вывести ð Закрыть. На робочому листі з'являться числа:
Отже, сценарій - це набір значень для змінюваних комірок, і цьому набору дано ім'я. Завдяки цьому засобу користувач таблиці може зберігати в ній декілька варіантів розрахунків і звертатися до них при необхідності.
2.11.Для того, щоб побачити всі сценарії разом Диспетчер сценариев має командну кнопку Отчет. Викличте Диспетчер сценариеві клацніть накнопку Отчет. З'явиться діалогове вікно Отчет по сценарию. На вибір пропонується тип звіту: структура або сводная таблица. Установіть перемикач: структура. З'явиться новий робочий лист: Структура сценария.
2.12.Тепер подивимося, як виглядає звіт у формі Сводной таблицы. Викличте Диспетчер сценариев: Сервис ð Сценарии ð кнопка Отчет, Установіть перемикач сводная таблица. З'явиться новий робочий лист: Сводная таблица по сценарию.
2.13.Щоб включити в таблицю значення В2:В4, викличте Диспетчер сценариев: Сервис ð Сценарии ð кнопка Отчет ðу вікні Отчет по сценарию додайте у поле введення Ячейки результата: адресу блоку В2:В4 перед адресами комірок В7, В10, В11.
2.14.Тепер у зведеній таблиці представлені значення не лише результуючих, але і початкових комірок.
2.15.Змініть формат у цільовій комірці на процентний:
3. Самостійно розв’яжіть наступні задачі:
Задача 3.Фірма робить три види продукції (А, В, С), для випуску кожного вимагається певний час обробки на усіх чотирьох пристроях I, II, Ш, IV.
| Вид продукції | Час обробки | Прибуток | |||
| I | II | Ш | IV | ||
| А | |||||
| В | |||||
| С |
Нехай час обробки на пристроях відповідно: 84, 42, 21, 42 години. Знайти, яку продукцію і в якій кількості треба випускати, щоб прибуток був максимальний.
Задача 4.Фірма робить два продукти А и В, ринок збуту яких необмежений. Кожен продукт має бути оброблений кожною машиною I, II, III. Час обробки в годинах для кожного з виробів А и В наведено нижче
| I | II | Ш | |
| А | 0,5 | 0,4 | 0,2 |
| В | 0,25 | 0,3 | 0,4 |
Час роботи машин I, II, III відповідно 40, 36 і 36 годин на тиждень. Прибуток від виробу продуктів А і В складає відповідно 5 і 3 долари. Фірмі потрібно визначити тижневі норми випуску виробів А і В, щоб максимізувати прибуток.
Задача 5.Фірма займається складанням дієти, що містить принаймні 20 одиниць білків, 30 одиниць вуглеводів, 10 одиниць жирів і 40 одиниць вітамінів. Як найдешевше досягти цього при вказаних цінах (у рублях) на 1 кг (чи 1 л) п'яти наявних продуктів?
| Хліб | Соя | Сушена риба | Фрукти | Молоко | |
| Білки | |||||
| Вуглеводи | |||||
| Жири | |||||
| Вітаміни | |||||
| Ціна |
4. Зберегти таблицю з ім'ям «Лінійна_оптим».
Тема: Розв’язання систем лінійних рівнянь.
Мета роботи: Оволодіти технологією розв’язання систем лінійних рівнянь засобами MS Excel.
Хід роботи.
Умова задачі:Розв’язати систему лінійних рівнянь:
1. Метод Крамера.
1.1. За методом Крамера потрібно знайти визначники матриць :
det A = 
; det A1 = 
; det A2 = 
; det A3 = 
Матриці A1, A2, A3 утворюються з матриці А заміною i –го стовпця стовпцем свобідних членів (права частина рівняння).
Розв’язання 
= 
1.2. На робочому листі запишіть задану систему в даному вигляді: 
1.3. Для розв’язання виділимо діапазон: В7:В9.
1.4. Для матриць А1, А2, А3 виділимо діапазони: В12:D14. F12:H14, J12:L14 - відповідно, причому перший рядок кожної матриці введемо за допомогою формул і скопіюємо на 2 останніх рядка.
|
1.5. Далі використаємо функцію МОПРЕД(матриця), у комірках С17:С19 обчислимо визначники усіх матриць, відповідно:
1.6. У комірці F8 обчислимо визначник матриці А:
1.7. Далі знайдемо розв’язання у комірках В7:В9 за формулами Крамера таким чином: запишимо формулу у комірці В7 і скопіюємо її у комірки В8, В9.
1.8. Відповідь повинна бути:
2. Матричний спосіб. Систему рівнянь записуємо в матричному вигляді: А-1 Х = В, де матриця А складається із коефіцієнтів при невідомих
А = 
, Х = 
, В = 
Матрицю А-1 потрібно обчислити за допомогою функції: МОБР(матриця),
Далі потрібно помножити матрицю А-1 на стовпець В за допомогою функції: МУМНОЖ(матрица1;матрица2).
2.1. На лист 2 скопіюйте діапазон А1:E4 з першого листа.
2.2. Обчисліть А-1: виділіть діапазон 3´3, викличте функцію і натисніть 3 клавіші Ctrl+Shift+Enter одночасно.
2.3. Виконайте множення матриці А-1на В. Виділіть діапазон С11:С13 ð викличте функцію МУМНОЖ ð натисніть 3 клавіші Ctrl+Shift+Enter одночасно
2.4. Відповідь повинна бути:
3. Застосування надбудови «Поиск решения». Для цього систему потрібно переписати у вигляді:
Завдання розв’язання системи лінійних рівнянь можна звести до оптимізаційного завдання. Для чого одно з рівнянь(наприклад, перше) взяти як цільову функцію, а решта n - 1 розглядати в якості обмежень. Тоді завдання оптимізації для Пошуку рішення може звучати таким чином: знайти значення X = (x1, x2 ., xn) T, , що доставляють нуль функції, яка стоїть ліворуч в першому рівнянні системи при n - 1 обмеженнях, представлених рівняннями, що залишилися
3.1. На лист 3 в діапазон В2:D4 скопіюємо матрицю А з листа 1.
3.2. В Е2:Е4 скопіюємо коефіцієнти зі стовпця В листа 1.
3.3. Діапазон В6:В8 залишаємо для отримання результату розв’язання.
3.4. В діапазоні С6:С8 запишемо формули лівої частини рівнянь, в С6 запишемо формулу і скопіюємо її в С7:С8.
3.5. Далі потрібно стати у комірку С6 ðвикликати Поиск решения у полі Равной вмикнути перемикач значению (повинно бути 0) ðстати в поле Изменяя ячейки і виділити діапазон В6:В8, де буде результат розв’язання
3.6. ð у полі Ограничениянатиснути кнопку Добавитьу вікні, що з’явилось у полі Ссылка на ячейку клацнути на комірку С7 ð вибрати знак = ðі у наступному полі написати 0ð
3.7. ðзнову натиснути кнопку Добавитьð те ж саме зробити з коміркою С8ðОК
3.8. Натиснути кнопку Выполнить.
3.9. ð кнопка ОК.
3.10. В комірках В6:В8 отримали результат,
4. Відповідно до номера варіанту виберіть з приведених нижче систему лінійних алгебраїчних рівнянь четвертого(n=4) порядку. Приведіть її до нормального виду. Розробіть таблиці Excel для вирішення системи трьома різними способами:
Ø Методом Крамера
Ø Матричним способом
Ø Використовуючи Поиск решения.
5. Варіанти систем лінійних рівнянь приведені нижче.
6. Перевірте рішення:
Розділ 6. Системи комп’ютерної математики.
Практична робота №21
Тема: Знайомство з середовищем MathCad
Мета роботи:отримати найпростіші навички роботи в середовищі MathCad
Хід роботи.
1. Завантажте середовище MathCad.
2. Виконайте просте обчислення, для чого введіть простий вираз
15-8/104.5=
Після натиснення знака = має з'яватися результат.
3. Задайте значення змінних через знак присвоєння :=
g := -9.8
t := 10
Знак := необхідно вводити з панелі Калькулятор, або натиснувши одночасно клавіші Shift - Ж
4. Задайте функцію
Після набраної функції натисніть =
Має з'явитися результат.
5. Наберіть у верхньому рядку текст
ЛАБОРАТОРНА РОБОТА № 1
Спробуйте його перемістити, для чого виділіть набраний текст рамкою; підведіть курсор, так щоб утворилась рука та методом перетаскування перенесіть текст праворуч.
6. Обчислити вираз для діапазону значень t від 10 до 20 с кроком 1. Для цього вираз t := 10 змініть на t := 10, 11 .. 20 ( щоб ввести .. необхідно натиснути кнопку Ж
Продивіться, щоб результат обчислення функції став у вигляді таблиці значень.
7. Обчислити вираз
Щоб з'явився результат необхідно після виразу натиснути знак =
8. Набрати вираз та отримати результат
9. Самостійно набрати вирази та отримати результати 
Ø x = 1.825 y = 18.225 z = -3.298
Ø a = 1.5 b = 15.5 x = -2.9
