Практична робота 5:"Табличний процесор Excel"

ПРАКТИЧНА РОБОТА № 5.

 

 Тема: Робота з масивами. Розв'язання системи лінійних рівнянь.

 Ціль: Ознайомитися із роботою з масивами в 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: .

 

 

КОНТРОЛЬНІ ПИТАННЯ.

 

1. Що називається масивом у MS Excel?
2. Чим відрізняється формула масиву від звичайних формул?
3. Як закінчити введення формули масиву?
4. Наведіть приклад використання формули масиву.
5. Від чого залежить величина (розмір) діапазону, у якому буде введена формула масиву?
6. Як виправляються помилки у формулах масиву?
7. Опишіть виконання поелементних арифметичних операцій із двома масивами.
8. Як обчислити функцію, яка залежить від елементів масиву?
9. Які ви знаєте функції MS Excel для роботи з матрицями?
10. Опишіть процес розв’язання системи двох лінійних рівнянь.