ПРАКТИЧНА РОБОТА
Тема: Робота з масивами. Розв'язання системи лінійних рівнянь.
Ціль: Ознайомитися із роботою з масивами в MS Excel і навчитися вирішувати системи лінійних рівнянь.
МЕТОДИЧНІ ВКАЗІВКИ.
5.1 Масиви
Іноді при роботі з таблицями виникає необхідність застосувати ту саму операцію до цілого діапазону комірок або зробити розрахунки по формулах, що залежать від великого масиву даних. Під масивом в MS Excel розуміється прямокутний діапазон формул або значень, які програма обробляє як єдину групу. Для розв'язання задач із масивами MS Excel надає простий засіб - формулу масиву.
5.2. Розрахунок ПДВ, як приклад множення елементів масиву на число
Як приклад використання формули масиву приведемо розрахунок цін групи товарів з урахуванням ПДВ. Наприклад, у діапазоні В2:В4 дані ціни групи товарів без обліку ПДВ. Необхідно знайти ціну кожного товару з урахуванням ПДВ (який будемо вважати рівним 25%). Таким чином, необхідно помножити масив елементів В2:В4 на 125%. Результат треба розмістити в комірках діапазону С2:С4.
1. Виберіть діапазон, наприклад С2:С4, у якому буде розміщений результат множення початкового масиву на число. Від діапазону, у якому вводиться результат, вимагається, щоб він мав той же розмір, що й вихідний діапазон.
2. Введіть формулу
=В2:В4*125%
3. Завершіть введення формули натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>. Таким чином, ви повідомите MS Excel, що необхідно виконати операцію над масивом, і створити формулу масиву. Після цього MS Excel автоматично візьме формулу у фігурні дужки.
{=В2:В4*125%}
При виборі діапазону, у який буде введена формула масиву, варто стежити за розміром: якщо вибрати занадто малий діапазон, то неможливо буде одержати всі результати; якщо вибрати занадто великий, то у використовуваних комірках відобразиться повідомлення про помилку #Н/Д.
Мал. 5.1. Поява помилки вибору діапазону
Мал. 5.2. Результат множення масиву на число
5.3. Виправлення формули масиву
Формули масивів діють на всі комірки масиву. Не можна змінювати окремі комірки в операндах формули. Найпростіший спосіб зміни формули масиву – виділити весь діапазон, у який введена формула масиву, і видалити її, нажавши клавішу <Delete>. Після чого ввести формулу масиву коректно. Такий прямий спосіб підходить, коли формула проста. А якщо вона складна, а в ній просто потрібно зробити невелику корекцію? У цьому випадку можливі дві ситуації.
Якщо формула масиву введена в правильний діапазон, треба виділити цей діапазон. У рядку формул зробити необхідні зміни й натиснути <Ctrl>+<Shift>+<Enter>.
Якщо формула масиву введена в неправильний діапазон - треба виділити цей діапазон, у рядку формул виділити формулу й скопіювати її в буфер обміну. Потім повторно вибрати діапазон і видалити з нього формулу масиву. Після цього треба виділити коректний діапазон, розташувати в рядку формул курсор і вставити в нього формулу з буфера обміну. Зробити, якщо потрібно, необхідне виправлення формули й завершити введення натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>.
5.4. Поелементні арифметичні операції із двома масивами
Виконаємо операцію поелементного додавання двох масивів. Нехай, наприклад доданками будуть масиви, що містяться в діапазонах А1:В2 й D1:E2. Далі:
1. Виберіть на робочому листі діапазон, наприклад С1:H2, у який буде поміщений результат поелементного додавання двох масивів. Від даного діапазону вимагається, щоб він мав той же розмір, що й масиви-доданки.
2. Введіть формулу
=A1:B2+D1:E2
та завершіть введення натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>. Excel візьме формулу в рядку формул у фігурні дужки й зробить необхідні обчислення {=A1:B2+D1:E2}
Аналогічно обчислюється поелементні різниця, добуток і ділення масивів.
5.5. Обчислення функції, що залежить від елементів масиву
На робочому листі припустимо створювати формули масиву, кожен елемент якого зв'язаний за допомогою деякої функції з відповідним елементом початкового масиву.
Наприклад, нехай у діапазоні А1:В2 є деякий масив даних. Потрібно знайти масив, елементи якого дорівнюють значенням функції SIN від відповідних елементів шуканого масиву. Для цього:
Мал. 5.3. Поелементне додавання двох масивів
1. Виберіть у робочому листі діапазон, наприклад А1:В2, у якому буде розміщений результат. Від даного діапазону вимагається, щоб він мав той же розмір, що й вихідний діапазон.
2. Введіть формулу =SIN(A1:B2), та завершіть введення натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>. MS Excel візьме формулу в рядку формул у фігурні дужки й проведе необхідні обчислення з елементами масиву.
5.6. Функції робочого листа для роботи з матрицями
В MS Excel є функції робочого листа для роботи з матрицями:
1) МОБР (масив) - повертає зворотну матрицю.
2) МОПРЕД (масив) - повертає визначник матриці.
3) МУМНОЖ(масив1; масив2) - повертає матричний добуток двох матриць.
4) ТРАНСП (масив) - повертає транспоновану матрицю
При роботі з матрицями, перед введенням формули, треба виділити область на робочому листі, куди буде поміщений результат обчислень, а введення формули завершувати натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>.
5.7. Розв'язання системи лінійних рівнянь
Вирішимо як приклад систему лінійних рівнянь із двома невідомими, матриця коефіцієнтів якої записана в комірки А2:В3, а вільні члени - в комірки D2:D3.
Рішення лінійної системи АХ = В, де А - матриця коефіцієнтів, В - стовпець (вектор) вільних членів, X - стовпець (вектор) невідомих, має вигляд X = А-1В , де А-1 - зворотна матриця до А. У нашому випадку для рішення системи рівнянь:
, маємо: А=
, В=
.
1. Виберіть той діапазон, у який буде введене рішення. Наприклад, G2:G3.
2. Введіть у нього формулу =МУМНОЖ(МОБР(А2:В3); D2:D3) та завершіть введення натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>.
3. У діапазоні G2:G3 з'явиться вектор, що є рішенням системи рівнянь.
X = , тобто x = 0,44; y = 0,16
Мал. 5.4. Розв'язання системи лінійних рівнянь
ПРАКТИЧНЕ ЗАВДАННЯ.
5.1. Вирішити систему рівнянь із двома невідомими відповідно до варіанта:
Варіант 1: ;
Варіант 2: ;
Варіант 3: ;
Варіант 4: ;
Варіант 5: ;
Варіант 6: ;
Варіант 7: ;
Варіант 8: ;
Варіант 9: ;
Варіант 10: .
КОНТРОЛЬНІ ПИТАННЯ ДЛЯ САМОПЕРЕВІРКИ .