Корпорація Майкрософт представила оператор неявного перетину в рамках суттєвого оновлення мови формул Excel для підтримки динамічних масивів. Динамічні масиви надають програмі Excel нові можливості та можливості обчислень.
Оновлена мова формул
Оновлена мова формул Excel майже ідентична старій, за винятком того, що в ній використовується оператор @ для позначення можливих неявних перетинів, тоді як стара мова робила це без оповіщення. Як наслідок, у деяких формулах динамічного масиву Excel можуть з'являтися символи @. Формули й надалі обчислюватимуться так само, як завжди.
Що таке неявний перетин?
Логіка неявного перетину зводить багато значень до одного. Так Excel змусив формулу повертати одне значення, оскільки клітинка може містити лише одне значення. Якщо формула повертає одне значення, неявний перетин нічого не робить (хоча технічно це відбувається у фоновому режимі). Логіка працює наступним чином:
- Якщо значення – один елемент, повернути елемент.
- Якщо значення – діапазон, повертає значення з клітинки в тому самому рядку або стовпці, що й формула.
- Якщо значення – масив, виберіть верхнє ліве значення.
З появою динамічних масивів програма Excel більше не обмежується поверненням окремих значень із формул, тому мовчазний неявний перетин більше не потрібен. Якщо стара формула могла непомітно викликати неявний перетин, Програма Excel із підтримкою динамічного масиву показує, де це могло статися з @.
Чому символ @?
Символ @ вже використовується в посиланнях на таблиці для позначення неявного перетину. Розглянемо таку формулу в таблиці =[@Column1]: Символ @ указує на те, що формула використовує неявний перетин, щоб отримати значення цього ж рядка з [Стовпця1].
Чи можна видалити символ @?
Часто це можливо. Це залежить від того, що повертає частина формули праворуч від символу @:
- Якщо функція повертає одне значення (найтиповіший випадок), після видалення символу @ зміни не зазнають.
- Якщо функція повертає діапазон або масив, то видалення символу @ спричиняє його розгортання в сусідні клітинки.
Якщо видалити автоматично додану формулу @, а потім відкрити книгу в попередній версії Excel, її буде відображено як застарілу формулу масиву (у фігурних дужках {}). Така поведінка гарантує, що старіша версія не ініціюватиме неявного перетину.
Коли ми додаємо символ @ до старих формул?
Зазвичай, функції, які повертають діапазони або масиви з кількох клітинок, мають префікс @, якщо їх створено в попередній версії програми Excel. Поведінка формули не зміниться – відобразиться лише раніше невидимий неявний перетин. До функцій, які повертають діапазони клітинок, належать INDEX, OFFSET і користувацькі функції (UDF). Зазвичай виняток становлять випадки, коли їх обгорнуто у функцію, яка приймає масив або діапазон (наприклад, SUM() або AVERAGE()).
Докладні відомості див. у функціях Excel, які повертають діапазони або масиви .
Приклади
| Вихідна формула | Як видно в динамічному масиві Excel | Пояснення |
|---|---|---|
| =SUM(A1:A10) | =SUM(A1:A10) | Без змін – неявного перетину не може бути, оскільки функція SUM очікує діапазони або масиви. |
| =A1+A2 | =A1+A2 | Без змін – неявного перетину не може бути. |
| =A1:A10 | =@A1:A10 | Буде показано неявний перетин, і програма Excel поверне значення, пов'язане з рядком, у якому розташовано формулу. |
| =INDEX(A1:A10;B1) | =@INDEX(A1:A10;B1) | Можливе неявне перетинання. Функція INDEX може повернути масив або діапазон, якщо другий або третій аргумент дорівнює 0. |
| =OFFSET(A1:A2;1;1) | =@OFFSET(A1:A2;1;1) | Можливе неявне перетинання. Функція OFFSET може повертати діапазон для кількох клітинок. Коли це відбувається, він запускає неявний перетин. |
| =MYUDF() | =@MYUDF() | Можливе неявне перетинання. Створення спеціальних функцій в Excel може повертати масиви. Коли вони зустрічаються, вихідна формула запускає неявний перетин. |
Використання оператора @ у нових формулах
Якщо ви створюєте або редагуєте формулу, яка містить оператор @, у динамічному масиві Excel, вона може відображатися як _xlfn. SINGLE() у додинамічному масиві Excel.
Така поведінка спостерігається під час затвердження змішаної формули. Мішана формула – це формула, яка використовує обчислення масиву та неявний перетин. Додинамічний масив Excel не підтримує таке об'єднання. Додинамічний масив Excel підтримує лише формули, які виконується i) обчислення неявного перетину або ii) масиву.
Якщо активовано динамічний масив, Excel виявляє змішану формулу, вона пропонує варіант формули, який використовує неявний перетин. Наприклад, якщо ввести, з'явиться =A1:A10+@A1:A10, таке діалогове вікно:
Якщо відхилити формулу, запропоновану в діалоговому вікні, буде затверджено змішану формулу =A1:A10+@A1:A10 . Якщо пізніше відкрити цю формулу в додинамічному масиві Excel, вона відображатиметься як =A1:A10+_xlfn.SINGLE(A1:A10) оператори @ у змішаній формулі Коли _xlfn.SINGLE(). Excel обчислить цей набір #NAME! .
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.