Сумиране по - прозорец - на листа с функция OFFSET
Има ситуации, когато не е известно предварително кои клетки на листа трябва да бъдат преброени. Например, нека си представим, че трябва да внедрим малък транспортен калкулатор в Excel, за да изчислим разстоянието за пътуване от една дадена станция до друга:
В падащите списъци в жълтите клетки F3 и F5 потребителят избира началната и крайната гара, а в зелената клетка F7 трябва да се изчисли сумата от всички клетки в посочения "прозорец" на листа. За да пътувате от Останкино до Ховрино, както е на фигурата, например, ще трябва да сумирате всички клетки в диапазона, ограден със зелени пунктирани линии.
Как да изчислим сумата е разбираемо, но как да определим диапазона от клетки, които трябва да бъдат сумирани? В края на краищата, когато избирате станции, тя постоянно ще се трансформира?
В такава ситуация може да помогне функциятаOFFSET), която може да даде връзка към "плаващ прозорец" - диапазон с даден размер, разположен на определено място в листа. Синтаксисът на функцията е както следва:
=ИЗМЕСТ(Референтна_точка; Shift_надолу; Shift_надясно; Височина; Ширина)
На изхода тази функция дава препратка към диапазон, изместен спрямо определена начална клетка (Reference_Point) с определен брой редове надолу и колони вдясно. Освен това, размерът на диапазона ("прозорец") може също да бъде зададен чрез параметритеHeightиWidth.
В нашия случай, ако вземем клетка A1 като референтна точка, тогава:
- Референтна точка = A1
- Shift_down = 4
- Shift_надясно = 2
- Височина = 4
- Ширина = 1
За да изчислим аргументите, необходими заOFFSET, нека първо приложим функциятаMATCH, която вече разгледахме къмизчисляване на позициите на отправната и крайната гара:
И накрая, използваме функциятаOFFSET, за да получим връзка към желания "прозорец" на листа и да сумираме всички клетки от него:
Това е, проблемът е решен :)
За разлика от повечето други функции на Excel,OFFSETе непостоянна или, както се казва, „променлива“ функция. Обикновените функции се преизчисляват само ако клетките с техните аргументи се променят. Променливите се преизчисляват всеки път, когато променитекоято и да еклетка. Разбира се, това се отразява негативно на производителността. При големи тежки маси разликата в скоростта на книгата може да бъде много забележима (на моменти). В някои случаи се оказва по-бързо да замените бавнияSHIFTс енергонезависимияINDEXили други аналози.