Функцията 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