Використання формул масивів в MS Excel

Лабораторна робота №8

Використання формул масивів в MS Excel

Мета роботи: Ознайомитися з особливостями роботи з формулами масивів в Excel, їх призначенням та використанням

Програма виконання роботи

1. Завантажити табличний процесор Excel.

2. Ввести значення двох масивів із завдання.

3. За допомогою формул масивів обчислити добуток двох масивів та знайти їх суму (див. приклади, що відносяться до рис.2, 3).

4. Обчислити суму сум квадратів відповідних елементів наведених масивів.

5. Обчислити суму квадратів різниці відповідних значень двох масивів.

6. Обчислити суму квадратів відповідних елементів двох масивів.

7. Обчислити суму добутків відповідних елементів двох масивів.

8. Обчислити суму різниць квадратів відповідних елементів двох масивів.

9. Зберегти створений документ у власній папці.

10. Завершити роботу з Excel.

Завдання на лабораторну роботу

 

№ п.п.	Масив Х	Масив У
	5,4	21,9
	7,3	20,2
	9,8	19,8
	11,4	18,7
	13,5	17,8
	14,2	17,1
7	15,6	16,2

 

Теоретичні відомості

Одна з найбільш цікавих та потужних можливостей в Excel – використання масивів у формулах.

Масив – це набір елементів, що може оброблятися як єдина група або кожен окремо. В Excel масиви можуть бути одно - або двовимірними. Виміри масивів безпосередньо відповідають рядкам та стовпчикам таблиці Excel. Наприклад, одновимірний масив може бути групою комірок, розміщених у одному рядку (горизонтальний масив) або у одному стовпчику (вертикальний масив). Двовимірний масив розміщується у декількох рядках та стовпчиках. Тривимірні масиви Excel не підтримує.

Робота з формулами масивів

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

Для введення формули масиву у комірку або у діапазон комірок виконують певну процедуру, яка дозволяє програмним засобам Excel ідентифікувати введений вираз, як формулу масиву, на відміну від звичайної формули. А саме, закінчення набору формули обов’язково супроводжується одночасним натисканням комбінації клавіш Ctrl+Shift+Enter, після чого система автоматично розміщує формулу у фігурних дужках.

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

Виокремлення діапазону комірок, в якому розміщено масив, виконують одним із способів:

§ розміщують курсор в одній із комірок масиву;

§ виконують дії: Правка Þ Перейти, або натискають клавішу F5;

§ у діалоговому вікні Переход, що відкриється, натискають кнопку Выделить;

§ у наступному діалоговому вікні Выделение группы ячеек вибирають перемикач Текущий массив;

§ натискають кнопку ОК, щоб закрити діалогове вікно;

або:

§ розміщують курсор в одній із комірок масиву;

§ натискають комбінацію клавіш Ctrl+/.

Редагування формули масиву

Якщо формула масиву знаходиться у декількох комірках, редагування здійснюють в усіх комірках діапазону як в одній.

При редагуванні формул масивів дотримуються таких правил:

§ не можна змінювати вміст жодної з комірок, значення яких одержано шляхом розрахунків за формулою масиву – при спробі це зробити, Excel видасть повідомлення “ Нельзя изменить часть массива”;

§ не можна переміщувати значення окремих комірок, на які розповсюджується формула масиву (переміщують всі комірки з формулою масиву разом);

§ не можна вилучати окремі комірки, на які розповсюджується формула масиву (вилучають лише весь масив повністю);

§ не можна вставляти нові комірки у масив (це правило відноситься також до вставки нових рядків та стовпчиків).

Щоб відредагувати формулу масиву:

§ виокремлюють всі комірки масиву;

§ переводять курсор у рядок формул, або натискають кнопку F2;

§ фігурні дужки при цьому вилучаються;

§ вносять потрібні зміни у формулу масиву;

§ після закінчення редагування формули натискають Ctrl+Shift+Enter, щоб підтвердити внесення змін.

Після виконання зазначених дій вміст всіх комірок масиву зміниться у відповідності з внесеними змінами.

Види формул масивів

Окрім наведених прикладів для роботи з масивами використовують вбудовані функції масивів, що належать до категорії Математические:

СУММСУММКВ(масcив_x; масcив_y) – сума сум квадратів відповідних елементів двох масивів.

Масcив_х – діапазон комірок зі значеннями першої змінної, наприклад А1:А10;

Масcив_у – діапазон комірок зі значеннями другої змінної, наприклад В1:В10.

Алгоритм обчислення за даною формулою масиву відбувається у такому порядку:

§ спочатку обчислюються квадрати значень, що знаходяться у комірках масивів, наприклад, =(А1)^2; =(B1)^2; =(А2)^2; =(B2)^2;...=(А10)^2; =(B10)^2;

§ визначається сума квадратів пар значень: =(А2)^2 +(B2)^2;... =(А12)^2+(B10)^2;

§ визначається сума пар сум квадратів: =((А2)^2 +(B2)^2)+... +((А12)^2+(B10)^2).

За формулою масиву СУММСУММКВ(масcив_x; масcив_y) зазначена послідовність розрахунків реалізується одночасно.

Для введення даної формули масиву виконують дії:

§ вибирають комірку для введення формули (клацають по ній мишею);

§ ВставкаÞФункция;

§ у діалоговому вікні, що відкриється, вибирають категорію функцій Математические;

§ із списку вибирають функцію СУММСУММКВ;

§ у діалоговому вікні, що відкриється, у полі масив х вказують діапазон комірок зі значеннями першого аргументу, наприклад А1:А10;

§ у рядку масив у – діапазон комірок зі значеннями другого аргументу, наприклад В1:В10;

§ натискують комбінацію клавіш Ctrl+Shift+Enter.

СУММКВРАЗН(масcив_х;масcив_у) – сума квадратів різниці відповідних значень двох масивів.

Алгоритм обчислень за наведеною формулою масиву відбувається у такому порядку:

§ спершу віднімають відповідні значення, що знаходяться у комірках масивів, наприклад, =А1−B1; =А2−B2;...=А10−B10;

§ визначають квадрат різниці відповідних пар значень: =(А2−B2)^2;... =(А10−B10)^2;

§ визначають суму квадратів різниці відповідних пар значень: =(А2−B2)^2 +... +(А10−B10)^2).

Окрім розглянутих формул масивів в Excel є ще такі вбудовані функції (формули) масивів:

СУММКВ(масcив_х; масcив_у) – сума квадратів відповідних елементів двох масивів.

СУММПРОИЗВ(масcив_х; масcив_у) – сума добутків відповідних елементів двох масивів.

СУММРАЗНКВ(масcив_х;масcив_у) – сума різниць квадратів відповідних елементів двох масивів.

Введення формул масивів, алгоритм обчислення за ними і виведення результатів розрахунків відбуваються аналогічно наведеним прикладам.

Контрольні запитання

1. Що називається масивом в Excel?

2. Які види масивів підтримує Excel?

3. Які особливості створення формул масивів?

4. Як відредагувати формулу масиву?

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

6. Як ввести формулу масиву для виведення результату у одну комірку?

7. Як ввести формулу масиву у діапазон комірок?

8. Які види обчислень замінює формула масиву СУММСУММКВ?

9. Які види обчислень замінює введення формули масиву СУММКВРАЗН?

10. Які обчислення замінює введення формули масиву СУММКВ?

 

 

Лабораторна робота №9

Застосування формул масивів для виконання дій над матрицями в MS Excel

Мета роботи: Ознайомитися з можливостями застосування формул масивів для виконання дій над матрицями

Програма виконання роботи

1. Завантажити табличний процесор Excel.

2. Знайти матрицю С=АВ-ВА, якщо

а) ; б)

 

3. Піднести матриці до степеня, використовуючи послідовно функцію МУМНОЖ.

а) б) в)

4. Знайти матрицю, обернену до даної, використовуючи функцію МОБР. Задати матрицю а), як діапазон комірок; б) у вигляді числового масиву.

5. Знайти визначники матриць, використовуючи функцію МОПРЕД. Задати матриці а), як діапазон комірок; б) у вигляді числового масиву.

6. Розв’язати системи рівнянь за формулами Крамера та матричним методом, використовуючи функції МОПРЕД, МОБР, МУМНОЖ.

а) б)

7. Зберегти створений документ у власній папці.

8. Завершити роботу з Excel.

Теоретичні відомості

Дії над матрицями

Сумою двох матриць А та В однакової вимірності називається матриця С=А+В тієї ж вимірності, де кожен елемент c_ij матриці С визначається: с_ij=a_ij+b_ij.

Добутком скаляра λ на матрицю А є матриця С= λА тієї ж вимірності, кожен елемент якої є добутком λ на відповідний елемент матриці А.

Добутком матриці А вимірності (m×n) на матрицю В вимірності (n×r) називається матриця С = А · В =(с_ij)_m×r кожен елемент с_ij якої є добутком і -ого рядка матриці А на j -ий стовпець матриці В.

Квадратна матриця В називається оберненою до деякої матриці А, якщо АВ = ВА = Е. Позначення квадратної матриці: В = А^{- 1}, причому
АА^{- 1}= А^{- 1} А = Е.

 

Знаходження суми матриць

Щоб додати матриці однакової вимірності, виконують дії:

§ вводять матриці однакової вимірності у вигляді масивів або у вигляді діапазонів комірок;

§ виокремлюють діапазон комірок такої вимірності що і матриці-доданки;

§ вводять формулу суми відповідних масивів;

§ натискають комбінацію клавіш Ctrl+Shift+Enter.

На рис. 7 наведено приклад додавання матриць вимірністю 2×2 заданих діапазоном комірок за допомогою формул масивів.

 

Рис. 7. Приклад додавання матриць (двовимірних масивів)

Знаходження добутку матриць

Для знаходження добутку матриць в Excel застосовують вбудовану функцію МУМНОЖ категорії Математические. Результатом використання функції є масив (матриця) з такою самою кількістю рядків, як масив (матриця) 1 і з такою ж кількістю стовпчиків, як масив (матриця) 2.

При знаходженні добутку матриць дотримуються таких обмежень:

§ кількість стовпчиків масиву (матриці) 1 повинна бути такою самою як кількість рядків масиву (матриці) 2;

§ матриці вводять як формули масивів.

Алгоритм знаходження добутку матриць з використанням вбудованої функції МУМНОЖ розглянемо на прикладі:

Для знаходження добутку матриць виконують дії:

§ вводять матрицю (масив) 1, для цього:

- виокремлюють діапазон комірок, що відповідає розмірам матриці А, наприклад В2:D4 (див. введення двовимірного масиву);

- у рядку формул вводять ={1;2;4:3;2;0:−1;4;2};

- натискають комбінацію клавіш Ctrl+Shift+Enter;

§ Вводять матрицю (масив) 2:

- виокремлюють діапазон комірок, що відповідає розмірам матриці D, наприклад F2:F4 (див. введення одновимірного вертикального масиву);

- у рядку формул вводять: ={1:2:3};

- натискають комбінацію клавіш Ctrl+Shift+Enter.

Для знаходження результату добутку двох матриць відповідну формулу вводять як формулу масиву. З цією метою:

§ виокремлюють діапазон комірок, що відповідає кількості рядків масиву 1 і кількості стовпчиків масиву 2;

§ переходять у рядок формул;

§ виконують дії: ВставкаÞФункция;

§ у діалоговому вікні, що відкриється, вибирають категорію функцій Математические;

§ у даній категорії вибирають із списку функцію МУМНОЖ;

§ у відповідному діалоговому вікні, що відкриється, у поле масив х вводять діапазон комірок з елементами першої матриці (наприклад, В2:D4);

§ у поле масив у вводять діапазон комірок з елементами другої матриці (наприклад, F2:F4 );

§ натискають комбінацію клавіш Ctrl+Shift+Enter.

або:

§ вводять = МУМНОЖ(В2:D4; F2:F4);

§ натискають комбінацію клавіш Ctrl+Shift+Enter.

Результат добутку матриць буде виведений у вигляді масиву (рис. 8).

 

Рис. 8. Приклад знаходження добуту матриць за допомогою формул масиву

 

Результат множення матриць, наведених у прикладі, відповідає математичному виразу:

Лабораторна робота №8

Використання формул масивів в MS Excel

Мета роботи: Ознайомитися з особливостями роботи з формулами масивів в Excel, їх призначенням та використанням

Програма виконання роботи

1. Завантажити табличний процесор Excel.

2. Ввести значення двох масивів із завдання.

3. За допомогою формул масивів обчислити добуток двох масивів та знайти їх суму (див. приклади, що відносяться до рис.2, 3).

4. Обчислити суму сум квадратів відповідних елементів наведених масивів.

5. Обчислити суму квадратів різниці відповідних значень двох масивів.

6. Обчислити суму квадратів відповідних елементів двох масивів.

7. Обчислити суму добутків відповідних елементів двох масивів.

8. Обчислити суму різниць квадратів відповідних елементів двох масивів.

9. Зберегти створений документ у власній папці.

10. Завершити роботу з Excel.