Виправлення формули масиву

1. Для чого використовується майстер діаграм?

2. Навіщо в діаграмі потрібна легенда?

3. Як розташувати діаграму автоматично на окремому листі під назвою «Лист диаграмм»?

4. Які особливості побудови графіку функції з двома умовами?

5. Навіщо використовується функція ЯКЩО (ЕСЛИ, IF)?

6. Які логічні функції MS Excel 2000/XP ви знаєте? Опишіть їх аргументи.

7. Опишіть етапи знаходженя корінь нелінейного рівняння методом підбору параметра.

8. Як задати кількість ітерацій та погрішність у методі підбору параметра?

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

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

Ціль: Ознайомитися із роботою з масивами в MS Excel і навчитися вирішувати системи лінійних рівнянь.

МЕТОДИЧНІ ВКАЗІВКИ.

Масиви

Іноді при роботі з таблицями виникає необхідність застосувати ту саму операцію до цілого діапазону комірок або зробити розрахунки по формулах, що залежать від великого масиву даних. Під масивом в 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. Результат множення масиву на число

Виправлення формули масиву

Формули масивів діють на всі комірки масиву. Не можна змінювати окремі комірки в операндах формули. Найпростіший спосіб зміни формули масиву – виділити весь діапазон, у який введена формула масиву, і видалити її, нажавши клавішу <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. Розв'язання системи лінійних рівнянь