Excel OFFSET функция (Съдържание)

  • OFFSET в Excel
  • OFFSET Формула в Excel
  • Как да използвате функцията OFFSET в Excel?

OFFSET в Excel

OFFSET Функцията в Excel помага на потребителя да върне клетка или диапазон от клетки, което е зададено no. от редове и колони от клетка. Можем да определим не. на редове и не. от колоните, които трябва да бъдат върнати.

По-долу е формулата OFFSET в Excel:

Функцията OFFSET в Excel се състои от следните аргументи:

  • Справка: Това е аргументът, от който искаме да основаваме компенсирането. Това може да бъде референция за клетка или диапазон от съседни клетки, ако не, Offset връща #VALUE! Стойност за грешка.
  • Редове: Това е не. от редове за компенсиране Ако използваме положително число, то то се компенсира до редовете отдолу, а ако се използва отрицателно число, то измества редовете по-горе.
  • Колони: Тя е за не. от колони за компенсиране. Така че, това е същата концепция като редовете, ако използваме положително число, тогава то се измества в колоните вдясно и ако се използва отрицателно число, то нейните компенсации към колоните вляво.
  • Височина: Този аргумент е незадължителен. Трябва да е положително число. Това е число, което представлява броя на редовете във върнатата справка.
  • Ширина: Това също е незадължителен аргумент. Трябва да е положително число. Това е число, което представлява броя на колоните във върнатата справка.

Забележки:

  • Функцията OFFSET в Excel връща #REF! стойност на грешката, ако редовете и колоните компенсират референцията над ръба на работния лист.
  • При OFFSET функцията се предполага, че тя е същата височина или ширина като еталон, ако височината или ширината са пропуснати.
  • OFFSET връща референция, тя всъщност не мести клетки или диапазон от клетки или не променя селекцията. Може да се използва с всяка функция, която очаква референтен аргумент.

Как да използвате функцията OFFSET в Excel?

OFFSET Функцията в Excel е много проста и лесна за използване. Нека да разберем работата на OFFSET функцията в Excel чрез пример на формула OFFSET.

Можете да изтеглите този шаблон на OFFSET Функция Excel тук - OFFSET Функция шаблон на Excel

Пример №1

Формулата Offset връща справка за клетка въз основа на начална точка, редове и колони, които посочваме. Можем да го видим в дадения по-долу пример:

= OFFSET (A1, 3, 1)

Формулата казва на Excel да разгледа клетка А1 за начална точка (референция), след това премести 3 реда надолу (редове) и 1 колона наляво (аргумент на колони). След това формулата OFFSET връща стойността в клетка B4.

Снимката вляво показва, че маршрутът на функцията и екранната снимка вдясно демонстрира как можем да използваме формулата OFFSET на база данни в реално време. Разликата между двете формули е, че втората (вдясно) включва препратка към клетка (D1) в аргумента на реда. Но тъй като клетка D1 съдържа номер 1 и точно същото число се появява в аргумента на редовете от първата формула, и двете биха върнали идентичен резултат - стойността в B2.

Пример №2 Използване на OFFSET функция за сума

Както знаем, че OFFSET функцията може да се използва и с другата функция на Excel, затова в този пример ще видим с функцията SUM.

Предполагам, че в горната снимка трябва да открием сумата от марки, след което можем да използваме и OFFSET функцията.

Формулата е по-долу:

Така че, по-горе формула D5 е първата клетка, от която започват данните, която е референция като начална клетка. След това стойността на редовете и колоните е 0, така че можем да го поставим като 0, 0. Тогава има 4, което означава 4 реда под референтната, за която Excel трябва да изчисли сумата и после, най-накрая, е 1, това означава, че има 1 колона като ширина.

Можем да отнесем горната снимка, за да свържем обяснения пример.

И по-долу е резултатът:

Просто трябва да натиснем Enter след въвеждане на формула в необходимата клетка и тогава имаме резултат.

Неща, които трябва да запомните за функцията OFFSET в Excel

  • Функцията OFFSET в Excel просто връща референция, не премества никакви клетки или обхват от клетки в действителност.
  • Ако OFFSET Formula върне диапазон от клетки, редовете и колоните винаги се отнасят до горната лява клетка във върнатия диапазон.
  • Референцията трябва да включва клетка или диапазон от клетки, в противен случай формулата ще върне грешката #VALUE.
  • Ако редовете и колоните, посочени над ръба на електронната таблица, формулата Offset в Excel ще върне #REF! грешка.
  • Функцията OFFSET може да се използва във всяка друга функция на Excel, която приема в своите аргументи референция за клетка или диапазон.

Например, ако използваме формулата = OFFSET (A1, 3, 1, 1, 3) самостоятелно, тя ще даде # VALUE! Грешка, тъй като диапазон на връщане (1 ред, 3 колони) не се побира в клетка. Ако обаче го съберем във функцията SUM, както по-долу:

= SUM (OFFSET (A1, 3, 1, 1, 3))

Формулата Offset връща сумата от стойности в 1 ред чрез диапазон от 3 колони, който е 3 реда отдолу и 1 колона вдясно от клетка A1, т.е. общата стойност в клетки B4: D4.

Както можем да видим, че общо 36 + 63 + 82 от маркиран Мар е равен на 181, което се получи в резултат на G2 след прилагане на сумата и офсетната формула. (подчертано в жълто)

Използване на OFFSET функция в Excel

Тъй като видяхме, че това, което функцията OFFSET всъщност прави с помощта на горния пример, може да имаме въпроси, които Защо да се притесняваме да изпеем формулата на OFFSET, която е малко сложна, защо просто не използваме директна референция като сума или B4: D4?

Функцията OFFSET в Excel е много добра за по-долу:

  • Получаване на диапазона от стартовата клетка:

Понякога не знаем точния или действителния адрес на диапазона, знаем само откъде започва от определена клетка. В този случай можем да използваме OFFSET функцията, която е лесна за преминаване.

  • Създаване на динамичен диапазон:

Позоваванията като горния пример B1: C4 са статични, това означава, че винаги се отнасят за даден или фиксиран диапазон. Но в някои случаи задачите са по-лесни за изпълнение с динамични диапазони. Особено помага, когато работим с променящи се данни. Например, бихме могли да имаме електронна таблица, в която да се вмъкват или добавят нов ред или колона всеки ден / седмица / месец, в този случай функцията OFFSET ще помогне.

Ограничения и алтернативи на OFFSET функцията в Excel

Тъй като всяка формула в Excel има свои собствени ограничения и алтернативи, видяхме как и кога да използваме OFFSET функцията в Excel.

По-долу са критичните ограничения на функцията OFFSET в Excel:

  • Гладна за ресурс функция:

Това означава, че винаги, когато има промяна в изходните данни, формулата ще бъде преизчислена за целия набор, което държи Excel зает за по-дълго време. Ако има малка електронна таблица, това няма да повлияе толкова много, но ако има редица електронни таблици, тогава Excel може да отнеме време за преизчисляване.

  • Трудност при преглед:

Както знаем препратките, върнати от функцията Offset, са динамични или променящи се и могат да съдържат голяма формула, която може да се окаже трудна за коригиране или редактиране според изискването.

Алтернативи на OFFSET функцията в Excel:

  • Функция INDEX:

Функцията INDEX в Excel може също да се използва за създаване на динамичен диапазон от референции. Не е точно като OFFSET. Но като OFFSET функцията INDEX не е толкова променлива, следователно няма да забави Excel, което е ограничение за OFFSET.

  • Непряка функция:

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

Препоръчителни статии

Това е ръководство за OFFSET в Excel. Тук обсъждаме формулата OFFSET в Excel и как да използваме OFFSET функцията в Excel, заедно с практически примери и шаблон за Excel за сваляне. Можете да разгледате и другите ни предложени статии -

  1. MS Excel: Функция LOOKUP
  2. Най-добрият пример за Excel XIRR функция
  3. Невероятно ръководство за НЕ функция в Excel
  4. IPMT в Excel с примери

Категория:

Excel Съвети