Ця стаття — частина матеріалу, присвяченого опануваннюExcel. Посилання на інші статті ви знайдете нижче.

Зазвичай користувачі вважають, що формула в Excel видає один результат лише для конкретної клітинки в таблиці.

Але завдяки динамічним масивам Excel одна формула може заповнювати багато клітинок — і кожна матиме унікальний розрахунок. Це спрощує роботу над великими файлами й мінімізує помилки. Використовуйте ці чарівні формули скрізь, де треба повторювати обчислення для масиву даних.

Ми наведемо два приклади, що демонструють, як можна заощадити час і зусилля, користуючись динамічними даними.

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

Перше, що вам спадає на думку, — ввести формулу =C4+B5 у клітинку C5, потім скопіювати та вставити цю клітинку кілька разів, щоб заповнити увесь рядок, а потім скопіювати рядок і вставити його в решту рядків у таблиці. Натомість використайте динамічні масиви та створіть одну формулу, що розрахує значення кожної клітинки.

The dynamic way: Create a single formula that references the entire range of cells and calculates every value in one fell swoop.

How to do it: Using the table below as an example, enter in the first answer cell =C4:J4+B5:B18, where C4:J4 represents the range of cells in the first row and B5:B18 represents those in the first column. Press Enter and the entire table populates!

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

Щоб узагальнити ці дані, спочатку треба отримати перелік продавців, виключивши записи, що повторюються. Це найпростіше зробити з новою функцією UNIQUE(). У пустій ділянці книги введіть =UNIQUE(B3:B12).

Найпростіший спосіб отримати загальний обсяг продажів кожної особи — використати функцію =SUMIF(), яка додає всі значення за заданим критерієм (у цьому випадку — ім’я людини). Але ваша керівниця додаватиме нового переможця щотижня, а ви не хочете щоразу оновлювати таблицю вручну. Динамічні масиви дають змогу налаштувати формулу для обчислення таблиці, що постійно доповнюється.

У стовпчику біля імені Мурата введіть формулу =SUMIF(B3:B12,E3#,C3:C12), де B3:B12 — це поточний список переможців, а C3:C12 — поточний список тижневих продажів. Символ ґратки (#) тут виконує магічну функцію: вказує Excel посилатися на весь діапазон динамічних даних — інакше кажучи, враховувати всі нові рядки. Натисніть Enter — й Excel підсумує продажі кожного працівника.

To get the total sales for each person, you’ll use a special version of the =SUMIF() function: In the column next to Murat’s name, enter the formula =SUMIF(B3:B12,E3#,C3:C12), where B3:B12 is the current list of winners and C3:C12 is the current list of weekly sales.

The number sign (#) is the magical bit here: It tells Excel to reference the entire range of the dynamic array—in other words, to account for any new rows. Press enter and Excel summarizes the sales for each person:

Тепер, коли ваша керівниця додасть Ешлі як переможницю 11-го тижня, дані буде внесено в підсумковий список автоматично:

Excel пропонує чимало способів використовувати динамічні масиви, зокрема з функціями FILTER(), SORT(), SORTBY(), SEQUENCE() та RANDARRAY(). Оскільки достатньо використати кожну з них лише раз, щоб заповнювалася необмежена кількість клітинок, динамічні масиви можуть заощадити вам чимало часу.