Використання формули масивів для діапазону комірок

На рис. представлено робочий аркуш документа Excel з розрахунками обсягів продажу добрив. Щоб обчислити вартість продажу кожного виду добрива (значення у стовпчику D) зазвичай використовують формулу множення кількості товару (стовпчик В) на ціну товару (стовпчик С). Наприклад, значення комірки D2 може бути обчислене за формулою: = В2 * С2, яка потім розповсюджується на діапазон комірок, що відповідає коміркам зі значеннями аргументів. В даному випадку отримаємо п’ять окремих формул у стовпчику D.

Другий спосіб обчислення п’яти значень у стовпчику D – використання однієї формули масиву. Ця формула буде розміщена в діапазоні комірок D2:D6, результатом розрахунку за якою буде п’ять шуканих значень.

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

§ виокремлюють діапазон комірок, в якому повинен знаходитись результат розрахунків за формулою масиву (в даному випадку це – D2:D6);

§ вводять формулу =В2:В6*С2:С6;

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

 

 

Рис.2. Приклад застосування формули масиву для діапазону комірок

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

Формули масивів також можуть повертати результат у одну комірку. На рис. у комірку D8 введено формулу масиву {=СУММ(В2:В6*С2:С6)}. Ця формула обчислює загальну суму продажу і дозволяє визначити суму добутків чисел, розташованих у комірках В2 і С2, В3 і С3,... В6 і С6. Тобто, спочатку знаходяться добутки відповідних пар чисел масиву, а потім ці результати додаються.

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

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

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

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

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

§ у відповідному діалоговому вікні, що відкриється у рядку масив 1 вводять формулу =В2:В6*С2:С6;

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

 

Рис.3. Приклад введення формули масиву у одну комірку

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

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

СУММСУММКВ(мас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