зміст
Що таке динамічні масиви
У вересні 2018 року Microsoft випустила оновлення, яке додає в Microsoft Excel абсолютно новий інструмент: динамічні масиви та 7 нових функцій для роботи з ними. Ці речі, без перебільшення, кардинально змінюють всю звичну техніку роботи з формулами і функціями і хвилюють, буквально, кожного користувача.
Розглянемо простий приклад, щоб пояснити суть.
Припустимо, у нас є проста таблиця з даними по містах-місяцях. Що станеться, якщо виділити будь-яку порожню комірку праворуч на аркуші і ввести в неї формулу, яка посилається не на одну комірку, а відразу на діапазон?
У всіх попередніх версіях Excel після натискання на
Що натомість? Створіть віртуальну версію себе у
ми отримали б вміст лише однієї першої комірки B2. Як інакше?
Ну, або можна було б обернути цей діапазон у якусь агрегатну функцію, наприклад =SUM(B2:C4) і отримати для неї загальну суму.
Якщо нам потрібні більш складні операції, ніж проста сума, наприклад витяг унікальних значень або Топ-3, тоді нам доведеться ввести нашу формулу як формулу масиву за допомогою комбінації клавіш Ctrl+Shift+
Що натомість? Створіть віртуальну версію себе у
.
Зараз все інакше.
Тепер після введення такої формули ми можемо просто натиснути Що натомість? Створіть віртуальну версію себе у – і отримати в результаті відразу всі значення uXNUMXbuXNUMXb, на які ми посилалися:
Це не магія, а нові динамічні масиви, які тепер має Microsoft Excel. Ласкаво просимо в новий світ 🙂
Особливості роботи з динамічними масивами
Технічно весь наш динамічний масив зберігається в першій клітинці G4, заповнюючи необхідну кількість клітинок праворуч і вниз своїми даними. Якщо вибрати будь-яку іншу клітинку в масиві, то посилання в рядку формул буде неактивним, показуючи, що ми знаходимося в одній з «дочірніх» клітинок:
Спроба видалити одну або кілька «дочірніх» комірок ні до чого не призведе – Excel відразу їх перерахує і заповнить.
У той же час ми можемо сміливо посилатися на ці «дочірні» клітини в інших формулах:
Якщо скопіювати першу комірку масиву (наприклад, з G4 в F8), то весь масив (його посилання) буде рухатися в тому ж напрямку, що і в звичайних формулах:
Якщо нам потрібно перемістити масив, то достатньо буде переміщення (мишкою або комбінацією Ctrl+X, Ctrl+V), знову тільки першу основну комірку G4 – після неї вона буде перенесена на нове місце і весь наш масив знову розгорнеться.
Якщо вам потрібно ще десь на аркуші посилатися на створений динамічний масив, то ви можете використовувати спеціальний символ # («фунт») після адреси його початкової комірки:
Наприклад, тепер ви можете легко створити випадаючий список у клітинці, яка посилається на створений динамічний масив:
Помилки динамічного масиву
Але що станеться, якщо недостатньо місця для розширення масиву або якщо на його шляху є клітинки, уже зайняті іншими даними? Зустрічайте принципово новий тип помилок в Excel – #ПЕРЕНЕС! (#РОЗЛИЙ!):
Як завжди, якщо ми натиснемо на іконку з жовтим ромбом і знаком оклику, ми отримаємо більш детальне пояснення джерела проблеми та зможемо швидко знайти заважаючі комірки:
Подібні помилки виникнуть, якщо масив виходить за межі аркуша або потрапляє на об’єднану клітинку. Якщо прибрати перешкоду, то все відразу виправиться на льоту.
Динамічні масиви та розумні таблиці
Якщо динамічний масив вказує на «розумну» таблицю, створену за допомогою комбінації клавіш Ctrl+T або шляхом Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю), то він також успадкує свою головну якість – авторозмір.
При додаванні нових даних внизу або праворуч розумна таблиця та динамічний діапазон також автоматично розтягнуться:
Проте є одне обмеження: ми не можемо використовувати посилання на динамічний діапазон у форумах у розумній таблиці:
Динамічні масиви та інші функції Excel
Гаразд, скажете ви. Все це цікаво і смішно. Не потрібно, як раніше, вручну розтягувати формулу з посиланням на першу клітинку вихідного діапазону вниз і вправо і все таке. І це все?
Не зовсім.
Динамічні масиви — це не просто ще один інструмент у Excel. Тепер вони вбудовані в саме серце (або мозок) Microsoft Excel – його механізм обчислень. Це означає, що інші знайомі нам формули і функції Excel також підтримують роботу з динамічними масивами. Давайте розглянемо кілька прикладів, щоб дати вам уявлення про глибину змін, які відбулися.
Транспонувати
Для транспонування діапазону (поміни рядків і стовпців) у Microsoft Excel завжди була вбудована функція TRANSP (ТРАНСПОНУВАТИ). Однак, щоб скористатися нею, ви повинні спочатку правильно вибрати діапазон для результатів (наприклад, якщо введено діапазон 5×3, то ви повинні вибрати 3×5), потім увійти до функції та натиснути кнопку поєднання Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у , оскільки він міг працювати лише в режимі формули масиву.
Тепер ви можете просто вибрати одну комірку, ввести в неї ту саму формулу та натиснути нормаль Що натомість? Створіть віртуальну версію себе у – динамічний масив все зробить сам:
Таблиця множення
Це приклад, який я наводив, коли мене просили візуалізувати переваги формул масивів у Excel. Тепер, щоб розрахувати всю таблицю Піфагора, достатньо встати в першу комірку B2, ввести туди формулу, яка множить два масиви (вертикальний і горизонтальний набір чисел 1..10) і просто натиснути на
Що натомість? Створіть віртуальну версію себе у
:
Склеювання та переобладнання корпусу
Масиви можна не тільки множити, але і склеювати стандартним оператором & (амперсанд). Припустімо, нам потрібно витягти ім’я та прізвище з двох стовпців і виправити регістр у вихідних даних. Ми робимо це за допомогою однієї короткої формули, яка формує весь масив, а потім застосовуємо до нього функцію ПРОПНАЧ (ПРАВИЛЬНО)навести порядок у реєстрі:
Висновок Топ 3
Припустімо, що у нас є купа чисел, з яких ми хочемо вивести три найкращі результати, розташувавши їх у порядку спадання. Тепер це робиться за однією формулою і знову ж без жодної Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у як раніше:
Якщо ви хочете, щоб результати розташовувалися не в стовпці, а в рядку, то досить замінити в цій формулі двокрапки (роздільник рядків) на крапку з комою (роздільник елементів в межах одного рядка). В англійській версії Excel цими роздільниками є крапка з комою та кома відповідно.
VLOOKUP витягує кілька стовпців одночасно
Функції VPR (ВПР) тепер ви можете витягувати значення не з одного, а відразу з декількох стовпців – просто вкажіть їх номери (в будь-якому бажаному порядку) у вигляді масиву в третьому аргументі функції:
Функція OFFSET повертає динамічний масив
Однією з найцікавіших і корисних (після VLOOKUP) функцій для аналізу даних є функція ВІДХОДЖЕННЯ (ЗМІЩЕННЯ), якому я свого часу присвятив цілий розділ у своїй книзі та статтю тут. Складність розуміння та освоєння цієї функції завжди полягала в тому, що вона повертала в результаті масив (діапазон) даних, але ми не могли його побачити, тому що Excel ще не вмів працювати з масивами з коробки.
Тепер ця проблема в минулому. Подивіться, як тепер, використовуючи єдину формулу та динамічний масив, повернутий OFFSET, ви можете витягти всі рядки для заданого продукту з будь-якої відсортованої таблиці:
Давайте розглянемо її аргументи:
- A1 – початкова клітинка (точка відліку)
- ПОИСКПОЗ(F2;A2:A30;0) – розрахунок зсуву від початкової комірки вниз – до першої знайденої капусти.
- 0 – зсув «вікна» вправо відносно початкової комірки
- СЧЕТЕСЛИ(A2:A30;F2) – розрахунок висоти повернутого «вікна» – кількість рядків, де є капуста.
- 4 — розмір «віконця» по горизонталі, тобто вивести 4 стовпчики
Нові функції для динамічних масивів
Крім підтримки механізму динамічного масиву в старих функціях, в Microsoft Excel додано кілька абсолютно нових функцій, заточених спеціально для роботи з динамічними масивами. Зокрема, це:
- GRADE (СОРТ) – сортує вхідний діапазон і створює динамічний масив на виході
- СОРТПО (СОРТУВАТИ ЗА) – може сортувати один діапазон за значеннями з іншого
- ФІЛЬТР (ФІЛЬТР) – отримує рядки з вихідного діапазону, які відповідають заданим умовам
- UNIK (УНІКАЛЬНИЙ) – витягує унікальні значення з діапазону або видаляє дублікати
- SLMASSIVE (RANDARRAY) – генерує масив випадкових чисел заданого розміру
- ПІСЛЯД (ПОСЛІДОВНІСТЬ) — формує масив із послідовності чисел із заданим кроком
Про них – трохи пізніше. Вони варті окремої статті (і не однієї) для вдумливого вивчення 🙂
Висновки
Якщо ви прочитали все, що написано вище, то, я думаю, ви вже усвідомили масштаби змін, які відбулися. Так багато речей у Excel тепер можна робити легше, легше та логічніше. Мушу визнати, що я трохи шокований тим, скільки статей тепер доведеться виправляти тут, на цьому сайті та в моїх книгах, але я готовий зробити це з легким серцем.
Підводячи підсумки, плюси динамічних масивів, ви можете написати наступне:
- Про поєднання можна забути Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у . Excel тепер не бачить різниці між «звичайними формулами» та «формулами масивів» і обробляє їх однаково.
- Про функцію SUMPRODUCT (SUMPRODUCT), який раніше використовувався для введення формул масиву без Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у Ви також можете забути – тепер це досить просто SUM и Що натомість? Створіть віртуальну версію себе у .
- Розумні таблиці та знайомі функції (SUM, IF, VLOOKUP, SUMIFS тощо) тепер також повністю або частково підтримують динамічні масиви.
- Є зворотна сумісність: якщо відкрити книгу з динамічними масивами в старій версії Excel, вони перетворяться на формули масивів (у фігурних дужках) і продовжать працювати в «старому стилі».
Знайшов якийсь номер мінуси:
- Ви не можете видалити окремі рядки, стовпці або клітинки з динамічного масиву, тобто він живе як єдине ціле.
- Ви не можете сортувати динамічний масив звичайним способом Дані – Сортування (Дані — Сортування). Зараз для цього є спеціальна функція. GRADE (СОРТ).
- Динамічний діапазон не можна перетворити на розумну таблицю (але можна створити динамічний діапазон на основі розумної таблиці).
Звичайно, це ще не кінець, і я впевнений, що Microsoft продовжить вдосконалювати цей механізм у майбутньому.
Де я можу завантажити?
І нарешті головне питання 🙂
Microsoft вперше анонсувала та показала попередній перегляд динамічних масивів у Excel ще у вересні 2018 року на конференції Запалати. У наступні кілька місяців відбулося ретельне тестування та введення нових функцій коти співробітників самої Microsoft, а потім і на волонтерів-тестерів із кола Office Insiders. Цього року оновлення, що додає динамічні масиви, почали поступово розгортати для звичайних передплатників Office 365. Наприклад, я отримав його лише в серпні з моєю передплатою на Office 365 Pro Plus (щомісячне цільове призначення).
Якщо у вашому Excel ще немає динамічних масивів, але ви дуже хочете з ними попрацювати, то є такі варіанти:
- Якщо у вас є підписка на Office 365, ви можете просто зачекати, доки це оновлення не дійде до вас. Як швидко це станеться, залежить від того, як часто оновлення доставляються у ваш офіс (раз на рік, раз на півроку, раз на місяць). Якщо у вас корпоративний ПК, ви можете попросити свого адміністратора налаштувати частіші завантаження оновлень.
- Ви можете приєднатися до тих волонтерів тестування Office Insiders – тоді ви першим отримаєте всі нові можливості та функції (але, звичайно, є ймовірність збільшення баггі в Excel).
- Якщо у вас не підписка, а окрема коробкова версія Excel, то вам доведеться почекати принаймні до випуску наступної версії Office і Excel у 2022 році. Користувачі таких версій отримують лише оновлення безпеки та виправлення помилок, а всі нові «доброщі» тепер дістаються лише підписникам Office 365. Сумно, але правда 🙂
У будь-якому випадку, коли у вашому Excel з’являться динамічні масиви – після цієї статті ви будете до цього готові 🙂
- Що таке формули масивів і як їх використовувати в Excel
- Вікно (діапазон) підсумовування за допомогою функції OFFSET
- 3 способи транспонування таблиці в Excel