Функцията OFFSET() в Excel

• by
• in Excel
• posted January 7, 2013

Сигурно си мислите, че функцията OFFSET() е висш пилотаж в Excel… Всъщност е проста и само звучи много сложно. Ще се опитам да обясня как работи и се надявам, че след като прочетете ще можете да впечатлявате колегите ви, които още не са попаднали на тази статия. 🙂

С OFFSET() може да правите динамични графики в Excel:

…или пък да свържете две таблици, ако в едната данните са в отделни редове, а в другата в колони:

Функцията OFFSET() в Excel дава съдържанието на една или повече клетки. (точка!)

Резултатът от нея ще е подобен на това в клетка А1 да напишете =А2. Така първата клетка ще показва съдържанието на втората.

Разликата обаче е, че с OFFSET() имате по-голяма гъвкавост и Excel може да се съобрази с определени условия при намирането на клетката, чието съдържание искате да използвате.

Основното предимство на функцията OFFSET() е, че тя не съдържа адрес на клетка, а „упътване” колко реда на надолу и колко колони надясно се намира търсената от нас клетка спрямо някаква отправна точка. Много лесно броят на редовете и колоните може да бъде заменен с някаква логическа формула и Excel винаги ще дава съдържанието на клетката, което отговаря на новото логическо условие.

В най-простия вид на формулата ние казваме на Excel: “Виждаш ли я ей тази клетка, слез 1 ред надолу от нея, мини една колона в дясно и ми дай съдържанието на клетката, която се намира там.”

Ето го и синтаксисът, с който записваме функцията OFFSET().

=OFFSET(reference,rows,cols,height,width)

• reference – адрес на клетката, който ще служи за отправна точка за местоположението на резултатът, който се търси от формулата. Обикновенно за референция се посочва най-горната, най-лява клетка от таблицата, в която се намира търсената клетка.
• rows – число, което показва колко редове надолу (+) или нагоре (-) от отправната точка (reference) се намира търсената от нас клетка.
• cols – число, което показва колко колони надясно (+) или наляво (-) от отправната точка (reference) се намира търсената от нас клетка.
• height – не е задължително да се попълва. Ако се остави празно, Excel приема, че търсим област от клетки с „височина” 1 – т.е. област от клетки, която се намира на един ред.
• width – не е задължително да се попълва. Ако се остави празно, Excel приема, че търсим област от клетки с „ширина” 1 – т.е. област от клетки, която се намира в една колона.

Height и width оставени празни или записани със стойност 1 означават, че резултатът е 1 клетка (област намираща се само на един ред и същевременно само в една колкона).

Например, ако в клетка B2 искаме да запишем дневния прием на калории за 01/01/2013 като използваме OFFSET() за да намерим клетката с тази стойност от допълнителна таблица, ето как би изглеждала формулата:

Записвайки $G$1 (Date) за Reference означава, че това ще е отправната точка, от която Excel ще започне намирането на клетката. В полето Rows записваме 1, което означава, че търсената клетка се намира един ред надолу от отправната, а единицата в полето Cols означава, че тя е и една колона на дясно от отправната клетка. Резултатът в този случай ще бъде H2 (1940).

На пръв поглед изглежда, че безмислено си усложняваме живота с очевидни неща – един ред надолу, една колкона на дясно … бла-бла. Истински полезното в случая е, че вместо да записваме твърдо число за ред/колкона, можем да заменим числото с формула, кочто дава различно число при различни условия.
Например IF(), разултатът от който, ако условието е изпълнено да е 1, а ако не е изпълнено – 2. И ако запишете тази IF() формула вместо cols в OFFSET()-а ще имате динамичен резултат.

Вместо IF() може да използвате някоя от опциите “Form controls” – радио бутони, scroll bar и подобни, които свързани към клетка в Excel дават променящо се число.

Ето и пример как да направите динамична графика, в която данните се “превъртат” със scroll bar:

А, ето го и крайният резултат – динамична графика в Ексел:

Може да свалите примерния файл от тук: OFFSET функция в Excel.

Успех!
by Magdalena Petrova