Промяна на offset при по-нови версии на Excel

Здравейте, Сарко, Вие сте забелязали една от най-важните промени в модерния Excel – въвеждането на Dynamic Arrays.

Накратко, в по-новите версии (като Excel 365 и Excel 2021) софтуерът автоматично „разлива“ (spill) резултатите от функции, които връщат повече от една стойност, вместо да изисква специални клавишни комбинации или да показва грешка. Тъй като Вашата формула дефинира област с височина 3 и ширина 3, Excel автоматично запълва съответния брой клетки под и вдясно от началната клетка.

Обяснение на поведението в новите версии

Ето как работи процесът на ниско ниво в новия Calculation Engine:

1. Дефиниране на масива: Функцията =OFFSET(A2;2;1;3;3) казва на Excel да отиде 2 реда надолу и 1 колона надясно от клетка A2, след което да маркира област с размер 3x3 клетки.
2. Spilling (Разливане): В по-старите версии на Excel, ако напишете това в една клетка, ще получите грешка #VALUE!, освен ако не използвате CTRL + SHIFT + ENTER. В новите версии се активира функцията Spill. Excel автоматично открива празните клетки около Вашата формула и поставя резултатите в тях.
3. Spill Range: Областта, която се запълва автоматично, се нарича Spill Range. Ако кликнете върху някоя от клетките с резултат (различна от първата), ще забележите, че формулата в лентата за формули (Formula Bar) е оцветена в сиво, което показва, че тя е част от динамичен масив.
4. Грешка #SPILL!: Ако по пътя на „разливането“ има други данни, които пречат на масива да се покаже изцяло, Excel ще върне грешката #SPILL!. Трябва просто да почистите тези клетки, за да се покажат данните.

Как да ограничите функцията, ако не желаете масив?

Ако искате Excel да се държи по стария начин и да върне само една стойност (тази, която се намира в същия ред като формулата), можете да използвате оператора за Implicit Intersection (знакът @): * Пример: = @OFFSET(A2;2;1;3;3)

Бяхте много изчерпателен. Благодаря!