Приклади створення і використання формул-масивів в ЕТ

Створення формули масиву для кількох клітинок

Відкрийте нову пусту книгу.

Скопіюйте дані у прикладі та вставте їх у нову книгу, починаючи із клітинки A1.

Щоб зберегти параметри форматування, скористайтеся кнопкою Параметри вставлення , яка з’явиться поруч.

Щоб перемножити значення в масиві (діапазон клітинок C2 – D11), виділіть діапазон клітинок E2 – E11 і в рядок формул введіть таку формулу:

=C2:C11*D2:D11

Натисніть сполучення клавіш Ctrl+Shift+Enter.

Формула береться у фігурні дужки ({ }) і її копія додається до кожної клітинки виділеного діапазону. Це відбувається дуже швидко, і у стовпці Е ви отримуєте загальний обсяг збуту кожного типу машини для кожного продавця

Створення формули масиву для однієї клітинки

У клітинці A13 введіть Загальний обсяг збуту.

У клітинці B13 введіть наведену нижче формулу та натисніть сполучення клавіш Ctrl+Shift+Enter.

=SUM(C2:C11*D2:D11)

У цьому випадку програма Excel перемножує значення масиву (діапазон клітинок C2 – D11), а потім за допомогою функції SUM підсумовує результати. Загальний обсяг збуту становить 111 800 $. Цей приклад демонструє, наскільки корисною може бути така формула. Наприклад, у вас 15 000 рядків даних. Усі ці дані або їх частину можна підсумувати, створивши формулу масиву в одній клітинці.

Також зверніть увагу на те, що формула для однієї клітинки (у клітинці B13) не залежить від формули для кількох клітинок (у клітинках E2 – E11). Це вказує на ще одну перевагу формул масивів – гнучкість. Можна виконати будь-яку кількість дій, наприклад змінити формули у стовпці Е або взагалі видалити цей стовпець, і це зовсім не вплине на формулу для однієї клітинки.

Формули масивів мають також такі переваги:

Послідовність. Якщо клацнути будь-яку клітинку в діапазоні під клітинкою Е2, відобразиться та сама формула. Така послідовність допомагає досягти більшої точності.

Безпека. Компонент формули масиву для кількох клітинок не можна змінити. Наприклад, клацніть клітинку E3 і натисніть клавішу Delete. Потрібно вибрати весь діапазон клітинок (Е2 – Е11) і змінити формулу для всього масиву або залишити його без змін. Сполучення клавіш Ctrl+Shift+Enter, яке використовується для підтвердження змінення формули, виступає в ролі додаткового заходу безпеки.

Файли меншого розміру. Часто замість кількох проміжних формул можна використати одну формулу масиву. Наприклад, у книзі, яку ви створили під час виконання цієї вправи, для обчислення результатів у стовпці Е використовується одна формула масиву. Якби для обчислення тих самих результатів ви би використовували стандартні формули (наприклад, =C2*D2), вам би довелося задіяти 11 різних формул.

Синтаксис формули масиву

Загалом у формулах масиву використовується стандартний синтаксис формул. Усі вони починаються зі знака рівності (=) і підтримують усі вбудовані функції програми Excel. Основна відмінність полягає в тому, що для введення формули масиву потрібно натиснути сполучення клавіш Ctrl+Shift+Enter. Після цього програма Excel бере формулу масиву у фігурні дужки. Якщо такі дужки ввести самостійно, формула перетвориться на текстовий рядок і не працюватиме.

Також слід розуміти, що функції масивів – це свого роду стенографічні записи. Наприклад, замість функції для кількох клітинок, яку ви використовували раніше, можна використати такі формули:

=C2*D2

=C3*D3

Формула для однієї клітинки у клітинці B13 містить у собі всі операції множення, а також арифметичні дії, потрібні для отримання загального результату із проміжних підсумків: =E2+E3+E4 тощо.

Правила введення та змінення формул масивів

Ще раз повторимо основне правило створення формули масиву: натискайте сполучення клавіш Ctrl+Shift+Enter щоразу, коли потрібно ввести або змінити формулу масиву. Це правило стосується формул для однієї та кількох клітинок.

Працюючи з формулами для кількох клітинок, також слід пам’ятати про такі правила:

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

Вміст окремої клітинки у формулі масиву змінити не можна. Щоб переконатися в цьому, виділіть клітинку Е3 у книзі зі зразком і натисніть клавішу Delete.

Усю формулу масиву переміщати та видаляти можна, а певну її частину – ні. Іншими словами, щоб зменшити формулу масиву, потрібно видалити наявну формулу та ввести нову.

Порада Щоб видалити формулу масиву, виділіть усю формулу (наприклад, =C2:C11*D2:D11), натисніть клавішу Delete, а потім – сполучення клавіш Ctrl+Shift+Enter.

У формулу масиву для кількох клітинок не можна вставляти пусті клітинки. З неї також не можна видаляти клітинки.

Розширення формули масиву

Інколи потрібно розширити формулу масиву (зменшити формулу масиву не можна). Цей процес нескладний, але слід пам’ятати правила з попереднього розділу.

У книзі зі зразком видаліть будь-який текст і формули для однієї клітинки, які розміщено під основною таблицею.

Вставте наведені нижче додаткові рядки даних у книгу, починаючи із клітинки A12. Щоб зберегти параметри форматування, скористайтеся кнопкою Параметри вставлення , яка з’явиться поруч.Пузій Седан 6 2500

Купе 7 1900

Торенко Седан 4 2200

Купе 3 2000

Іваненко Седан 8 2300

Купе 8 2100

Виділіть діапазон клітинок, який містить поточну формулу масиву (E2:E11), а також пусті клітинки (E12:E17), розташовані поруч із новими даними. Іншими словами, виділіть діапазон клітинок E2:E17.

Натисніть клавішу F2, щоб активувати режим редагування.

У рядку формул замініть C11 на C17, а D11 – на D17, а потім натисніть сполучення клавіш Ctrl+Shift+Enter. Програма Excel оновить формулу в діапазоні клітинок E2 – E11 і додасть копію формули до нових клітинок (E12 – E17).

Недоліки формул масивів

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

Користувачі час від часу забувають натискати сполучення клавіш Ctrl+Shift+Enter. Щоб ввести або змінити формулу масиву, потрібно натиснути це сполучення. Пам’ятайте про це.

Інші користувачі можуть не розуміти цих формул. На практиці формули масивів зазвичай не документуються на аркуші, тому якщо іншим користувачам доведеться працювати з вашими книгами, не використовуйте формули масивів, або переконайтеся, що ці користувачі розуміють, як їх змінювати.

Залежно від швидкості обробки даних і обсягу пам’яті комп’ютера великі формули масивів можуть обчислюватися повільно.