Яка функція символу @ в Excel та неявного перетину?

Інформатек Діджитал » Ресурси » Яка функція символу @ в Excel та неявного перетину?

Якщо ви щодня користуєтеся Excel і раптом почали бачити символ @ у своїх формулах , не панікуйте: це не помилка чи щось незвичайне, це неявний оператор перетину. Цей маленький символ став ключовим, особливо після того, як динамічні масиви були представлені в Excel 365 та Excel 2021.

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

Що таке неявний перетин в Excel?

Так званий неявний перетин – це правило, яке Excel використовує для переходу від «багатьох можливих значень» до «лише одного», коли формула посилається на діапазон або масив, але клітинка може відображати лише один результат. Іншими словами, Excel має вирішити, яке конкретне значення використовувати з усіх, запропонованих діапазоном або масивом.

Роками це рішення приймалося без жодної особливої ​​​​помітки користувача: ви писали формулу, а Excel виконував власні обчислення, застосовуючи неявний перетин за потреби. З появою динамічних масивів цю поведінку довелося уточнити, і саме тут з'являється оператор @ як індикатор неявного перетину.

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

У сучасних версіях, таких як Microsoft 365, формули можуть «переповнюватися» та заповнювати кілька комірок одночасно, але Excel все ще потребує способу визначити, коли нам потрібна така динамічна поведінка, а коли ні. Саме тому символ @ використовується для примусового повернення одного значення , зберігаючи логіку неявного перетину, коли це необхідно.

Як працював неявний перетин в Excel 2019 та попередніх версіях

У версіях, що передували динамічним масивам (наприклад, Excel 2019 та раніших), неявний перетин був стандартною поведінкою . Він застосовувався послідовно, навіть якщо ви не робили нічого особливого. Щоразу, коли формула працювала з діапазонами або масивами, Excel вирішував, яке значення використовувати, відповідно до дуже специфічних внутрішніх правил.

Ось основні правила , які Excel застосовував, не повідомляючи користувача:

• Якщо результатом було одне значення, це значення поверталося безпосередньо, без потреби у спеціальному перетині.
• Якщо формула посилалася на діапазон (наприклад, A1:A10), Excel використав значення клітинки в діапазоні, що збігалося з рядком (або стовпцем), де знаходилася формула.
• Якщо використовувалася матриця З кількох значень Excel взяв як результат елемент, розташований у верхньому лівому куті цього масиву.

Уявіть, що ви пишете формулу типу =A1:A10 у клітинці в рядку 5. У тих «старих» версіях Excel ви б не побачили нічого незвичайного : значення A5 просто відображалося б, оскільки це перетин між діапазоном A1:A10 та рядком, де розташована формула . Це був саме той неявний перетин у дії.

Ще один класичний приклад: якщо ввести =B2:B5*C2:C5 у клітинку, Excel використовуватиме лише значення, що відповідають тому ж рядку, що й формула . Ви отримаєте один результат, а щоб отримати інші, потрібно перетягнути формулу вниз. За замовчуванням програма не «розгортає» всі можливі результати на інші клітинки.

Коли ви хотіли піти далі та налаштувати Excel на роботу з усім діапазоном одразу, вам доводилося вдаватися до класичних формул масивів: ви вибирали вихідний діапазон, писали формулу та підтверджували її за допомогою Ctrl + Shift + Enter . Ця комбінація вказувала Excel не застосовувати неявний перетин, а обчислювати вираз як повний масив і повертати кілька результатів у кількох клітинках.

Неявний перетин в Excel 365 та версіях з динамічними масивами

З появою динамічних масивів у Microsoft 365 та Excel 2021 , обчислювальний механізм змінив свою філософію. Тепер, коли формула може повертати кілька результатів, правило діє навпаки: Excel «випускає їх» і автоматично розподіляє по кількох клітинках.

Така поведінка відома як розлив . Якщо ви напишете формулу, яка повертає масив із кількох елементів, ви побачите, як блок комірок заповнюється, а уражена область візуально виділяється синьою рамкою. Вам більше не потрібно вдаватися до старої комбінації клавіш Ctrl+Shift+Enter, щоб отримати кілька результатів.

У цій новій моделі, якщо ви хочете відтворити старий спосіб роботи (обчислення лише одного значення на рядок або стовпець), вам потрібно явно вказати це Excel. Це досягається за допомогою оператора @, який активує неявний перетин для цієї частини формули.

Іншими словами: раніше вам доводилося «змушувати» Excel обчислювати з цілими масивами; тепер вам доводиться «змушувати» Excel поводитися традиційним чином, коли вираз генерує більше одного результату. Символ @ обробляє цю зміну : якщо ви його додаєте, ви отримуєте лише одне значення; якщо ви його видаляєте, формула може переповнитися.

Важливою деталлю є те, що формули, написані в старіших версіях і відкриті в Excel 365, можуть відображатися з символами (@), які ви ніколи не вводили. Це не тому, що поведінка формули змінилася; радше, Excel тепер візуально показує, де відбувався цей неявний перетин, який раніше був прихований.

Роль символу @ як неявного оператора перетину

Символ @, технічно, є неявним оператором перетину в новій мові формул Excel. Він був введений разом із динамічними масивами, щоб чітко вказати точки, в яких Excel зменшує діапазон або масив до одного значення.

Типовий синтаксис дуже простий: знак @ ставиться безпосередньо перед діапазоном, масивом або функцією, яка може повертати кілька значень . Наприклад, ви можете знайти формули типу =@A1:A10 або =@INDEX(A1:A10,B1). В обох випадках @ вказує на те, що, хоча потенційним результатом є колекція значень, Excel має повертати лише значення, що відповідає перетину (або верхньому лівому елементу, якщо це чистий масив).

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

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

Крім того, під час створення нових формул в Excel 365 ви можете свідомо вирішити, де потрібно використовувати поведінку масиву, а де ви бажаєте продовжувати використовувати класичну логіку з одним результатом , просто додаючи або видаляючи символ @ у ключових точках.

Практичні приклади використання оператора @ у формулах

Щоб краще зрозуміти роль символу (@), корисно переглянути, як деякі типові формули трансформуються під час відкриття в Excel з динамічними масивами. Багато з них залишаються незмінними; інші ж відображаються з неявним символом перетину перед ними.

Ось деякі вагомі приклади :

• =СУМ(A1:A10) → =СУМ(A1:A10)
  Змін немає, оскільки функція SUM розроблена для роботи з діапазонами та масивами. Застосування неявного перетину до її аргументу є безглуздим: функція вже очікує кілька значень.
• =A1+A2 → =A1+A2
  Тут також немає символу «at», оскільки формула пов'язує лише окремі значення, а не цілі діапазони. Неявний перетин неможливий.
• =A1:A10 → =@A1:A10
  У цьому випадку оператор дійсно відображається. Excel розуміє, що може виникнути неявний перетин, і що він повинен повернути значення діапазону, що відповідає рядку, де розташована формула.
• =ÍNDICE(A1:A10;B1) → =@ÍNDICE(A1:A10;B1)
  Функція INDEX може повернути цілий діапазон або масив, якщо використовуються певні аргументи (наприклад, якщо в рядку або стовпці вказано 0). Тому Excel позначає можливість неявного перетину для потенційного виводу масиву.
• =DESREF(A1:A2;1;1) → =@DESREF(A1:A2;1;1)
  OFFSET може створювати діапазон із кількох комірок. Якщо це трапляється, символ @ гарантує, що Excel поверне одне значення, активуючи неявний перетин у повернутому діапазоні.
• =МОЙУДФ() → =@МОЙУДФ()
  Користувацькі функції (UDF) можна запрограмувати для повернення масивів. Коли це трапляється, Excel використовує символ @, щоб вказати, що вихідна формула спиралася на неявний перетин, щоб отримати лише один результат.

Як бачите, присутність символу @ не означає, що логіка формули змінюється за одну ніч; вона просто робить видимою поведінку, яка раніше відбувалася за лаштунками без вашого відома . Це допомагає вам краще зрозуміти, чому формула повертає лише одне значення навіть під час роботи з досить великими діапазонами.

Чи можна видалити символ @ з формули?

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

Основні можливості такі:

• Якщо вираз повертає одне значення Звичайно, видалення символу @ не вплине на результат. У такому разі, неявний перетин нічого не додає, оскільки немає чого "зменшувати".
• Якщо вираз повертає діапазон або масивВидалення символу @ може призвести до переповнення формули, заповнення сусідніх комірок усіма значеннями з результату масиву.

У сучасних версіях таке переповнення є нормальним і часто бажаним. Але будьте обережні: якщо ви потім відкриєте файл у версії Excel, яка не підтримує динамічні масиви, формула, яка раніше використовувала символ @, але більше його не використовує, може стати застарілою формулою масиву , укладеною у фігурні дужки {} для забезпечення узгодженості обчислення.

Коли Excel перетворює цю формулу на старішу версію, він робить це саме для того, щоб запобігти повторному спрацьовуванню неявного перетину та зміні результату. Це зберігає початкову поведінку, яку вона мала в Excel з динамічними масивами.

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

Коли Excel автоматично додає символ @?

Excel автоматично розміщує неявний оператор перетину переважно тоді, коли виявляє застарілі формули, відкриті в середовищі з динамічними масивами . Мета полягає в тому, щоб явно показати, де ці невидимі перетини відбувалися в старій моделі.

Це трапляється особливо з функціями, здатними повертати діапазони або масиви з кількох комірок, наприклад:

• INDEX, особливо коли один з їхніх аргументів позиції дорівнює 0.
• ЗМІЩЕННЯ, який зазвичай повертає зміщені діапазони з кількома клітинками.
• Користувацькі функції (UDF) призначений для повернення матриць.

У багатьох із цих випадків оригінальна формула була написана з урахуванням класичної моделі, де кожна клітинка могла містити лише одне значення . Excel вирішив потенційний конфлікт за допомогою неявного перетину, не повідомляючи вас про це. Коли ви відкриваєте той самий файл у версії з динамічними масивами, програма «виявляє» цей перетин, додаючи символ @ у потрібних точках.

Однак є й явні винятки. Наприклад, якщо формула використовує функцію, яка приймає масиви або діапазони як вхідні дані, такі як SUM або AVERAGE, Excel не потрібно ставити символ @ перед цими аргументами: функція вже очікує повні діапазони та обробляє їх правильно без необхідності додаткових перетинів.

Коротше кажучи, якщо ви бачите символ «at», який ви не пам’ятаєте, щоб вводили, найімовірніше, Excel додав його, коли ви відкривали старішу книгу, щоб допомогти вам зрозуміти, де саме відбувалося це зменшення значень на рівні рядка або стовпця.

Використання оператора @ у нових та змішаних формулах

Коли ви починаєте створювати нові формули в Excel з динамічними масивами, ви можете поєднувати обчислення "чистих" масивів з неявними перетинами . Однак старіші версії Excel не обробляли ці типи змішаних формул, що вимагало використання механізму сумісності.

Якщо ви пишете щось на кшталт =A1:A10+@A1:A10, ви змішуєте частину, яка обчислюється як масив (A1:A10), з іншою частиною, яка примусово використовує неявний перетин (@A1:A10). Excel 365 виявляє це та може запропонувати альтернативну версію формули, яка використовує лише неявний перетин або лише обчислення масиву, уникаючи комбінацій, які не розуміються в старіших версіях Excel.

Якщо ви вирішите проігнорувати рекомендацію та дотримуватися змішаної формули, нічого не станеться, поки ви продовжуватимете використовувати сучасну версію. Однак, якщо ви пізніше відкриєте цю книгу у виданні, яке передує динамічним масивам, ви, ймовірно, побачите щось на кшталт =A1:A10+_xlfn.SINGLE(A1:A10) , де оператор @ стає викликом функції маркера сумісності SINGLE.

У старіших версіях Excel під час обчислення формули за допомогою _xlfn.SINGLE() зазвичай виникають помилки типу #NAME? або #VALUE!, оскільки програма не розпізнає ні функцію, ні змішаний підхід. Це чітко вказує на те, що ви використовуєте конструкції, розроблені для нового покоління динамічних масивів.

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

Символ @ у таблицях Excel та структурованих посиланнях

Задовго до появи динамічних масивів символ @ вже використовувався у структурованих посиланнях у таблицях Excel . Якщо ви працювали з таблицями (Вставка > Таблиця), ви, ймовірно, бачили формули, які виглядають приблизно так.

У цьому контексті символ (@) вказує на те, що формула обчислюється на рівні рядка . Тобто для вказаного стовпця формула використовуватиме значення з рядка, що містить формулу, а не з усього стовпця. Наприклад, вираз типу =* обчислює добуток лише для активного рядка, а потім Excel автоматично копіює логіку до решти рядків таблиці.

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

Коли ви пишете ці типи формул у сучасних версіях, Excel продовжує використовувати той самий синтаксис, оскільки він ідеально відповідає ідеї неявного перетину: йдеться про вибір значення з діапазону (стовпця таблиці), яке відповідає рядку, де розташована формула.

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

Неявний перетин проти розливу або поширення результатів

Щоб повністю зрозуміти мету @, корисно порівняти його з ефектом переливання. Це, певним чином, дві сторони однієї медалі: неявний перетин зменшує результати ; переливання, з іншого боку, розширює їх.

У сучасних версіях Excel правилом за замовчуванням є переливання : якщо формула генерує кілька значень, вони «розливаються» в сусідні комірки. Це значно полегшує роботу з новими функціями, такими як FILTER, SEQUENCE, RANDOMAR, SORT, SORTBY або UNIQUE, які розроблені саме для автоматичного заповнення цілих блоків комірок.

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

Якщо неправильно використовувати символ @ там, де потрібно, можуть виникнути помилки переповнення (#OVERFLOW!), коли результат формули не поміщається в доступні комірки. Однак, якщо правильно застосувати його, можна отримати найкраще з обох світів: розлив, коли потрібно заповнити цілі масиви, та неявний перетин, коли потрібно лише певне значення з більшого діапазону.

На практиці, навчитися грати з цими двома ідеями — переповненням матриць або їх скороченням — є ключем до оволодіння сучасним Excel та уникнення неприємних сюрпризів у ваших електронних таблицях.

Символ @ поза формулами: згадки в коментарях

У програмі Excel для Microsoft 365 та веб-версії символ @ також має більш «соціальне» використання, спрямоване на спільну роботу . Він використовується не лише у формулах, але й для згадування інших людей у ​​коментарях до книги.

Коли ви вставляєте коментар у клітинку та друкуєте символ @, а потім ім’я колеги, Excel показує список потенційних користувачів, щоб ви могли вибрати, кого згадати. Коли ви вибираєте їх, ця особа отримує електронний лист із прямим посиланням на коментар , що значно пришвидшує рецензування та командну роботу.

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

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

Дужки та порядок обчислення у формулах

Хоча символ @ є основною темою цієї теми, важливо пам'ятати, що Excel також значною мірою залежить від порядку, в якому обчислюються формули , в основному за допомогою дужок. Це безпосередньо впливає на те, як класичні операції поєднуються з діапазонами та масивами.

Наприклад, у простому виразі, такому як =5+2*3, Excel дотримується звичайного порядку операцій: спочатку множить, а потім додає, в результаті чого отримує 11. Якщо потрібно спочатку додавати, а потім множити, потрібно написати =(5+2)*3, що повертає 21. Дужки змушують Excel обчислювати цю частину перед рештою.

У складніших формулах, наприклад, =(B4+25)/SUM(D5:F5) чітко вказує на те, що Excel має підсумувати B4+25, потім обчислити суму діапазону D5:F5 і, нарешті, поділити обидва результати. Така структура допомагає уникнути плутанини та неправильних комбінацій діапазонів, масивів і скалярних значень.

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

Розуміння того, як пов'язані @, дужки та порядок обчислення, дає вам набагато кращу можливість створювати формули, які працюють однаково в різних контекстах і добре адаптуються до змін між старішими та новішими версіями Excel.

Вся ця поведінка, пов'язана з символом @, неявним перетином, динамічними масивами та порядком обчислень, робить Excel набагато потужнішим інструментом, але також трохи вимогливішим до розуміння того, що відбувається «під капотом». Оволодіння цими концепціями дозволить вам вирішити, коли ви хочете, щоб формула поводилася «як традиційні формули», а коли ви хочете, щоб вона повною мірою використовувала можливості масивів та спільної роботи поточних версій.

Пов'язана стаття:

Стовпець і рядок в Excel: як використовувати їх для створення ефективних електронних таблиць