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

Обобщение

Функцията Excel OFFSET връща препратка към диапазон, изграден с пет входа: (1) начална точка, (2) отместване на редове, (3) отместване на колона, (4) височина в редове, (5) ширина в колони. OFFSET е удобен за формули, които изискват динамичен обхват.

Предназначение

Създайте референтно отместване от дадената начална точка

Върната стойност

Препратка към клетка.

Синтаксис

= OFFSET (справка, редове, колони, (височина), (ширина))

Аргументи

  • справка - началната точка, предоставена като препратка към клетка или диапазон.
  • редове - Броят на редовете, които трябва да се компенсират под началната препратка.
  • cols - Броят на колоните, които трябва да се изместят вдясно от началната препратка.
  • височина - (по избор) Височината в редове на върнатата препратка.
  • width - (по избор) Ширината в колони на върнатата препратка.

Версия

Excel 2003

Бележки за употреба

Функцията Excel OFFSET връща динамичен диапазон, изграден с пет входа: (1) начална точка, (2) отместване на редове, (3) изместване на колона, (4) височина в редове, (5) ширина в колони.

Началната точка ( референтният аргумент) може да бъде една клетка или диапазон от клетки. Аргументите за редовете и колоните са броят на клетките, които трябва да се „компенсират“ от началната точка. Аргументите за височина и ширина не са задължителни и определят размера на създадения диапазон. Когато височината и ширината са пропуснати, те по подразбиране са височина и ширина на препратката .

Например, за препратка C5, започваща от A1, препратката е A1, редовете са 4 и cols е 2:

=OFFSET(A1,4,2) // returns reference to C5

За препратка C1: C5 от A1, референцията е A1, редовете са 0, cols е 2, височината е 5 и ширината е 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Забележка: ширината може да бъде пропусната, тъй като ще е по подразбиране 1.

Често се вижда OFFSET, увит в друга функция, която очаква обхват. Например към SUM C1: C5, започвайки от A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

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

Забележка: Документацията на Excel посочва, че височината и ширината не могат да бъдат отрицателни, но отрицателните стойности изглежда са работили добре от началото на 90-те години. Функцията OFFSET в Google Sheets няма да позволи отрицателна стойност за аргументи за височина или ширина.

Примери

Примерите по-долу показват как OFFSET може да бъде конфигуриран да връща различни видове диапазони. Тези екрани са направени с Excel 365, така че OFFSET връща динамичен масив, когато резултатът е повече от една клетка. В по-старите версии на Excel можете да използвате клавиша F9, за да проверите резултатите, върнати от OFFSET.

Пример # 1

На екрана по-долу използваме OFFSET, за да върнем третата стойност (март) във втората колона (запад). Формулата в Н4 е:

=OFFSET(B3,3,2) // returns D6

Пример # 2

На екрана по-долу използваме OFFSET, за да върнем последната стойност (юни) в третата колона (север). Формулата в Н4 е:

=OFFSET(B3,6,3) // returns E9

Пример # 3

По-долу използваме OFFSET, за да върнем всички стойности в третата колона (север). Формулата в Н4 е:

=OFFSET(B3,1,3,6) // returns E4:E9

Пример # 4

По-долу използваме OFFSET, за да върнем всички стойности за май (пети ред). Формулата в Н4 е:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Пример # 5

По-долу използваме OFFSET за връщане на стойностите за април, май и юни за Западния регион. Формулата в Н4 е:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Пример # 6

По-долу използваме OFFSET, за да върнем стойностите за април, май и юни за Запад и Север. Формулата в Н4 е:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Бележки

  • OFFSET връща само препратка, не се преместват клетки.
  • И двата реда и колони могат да се доставят и отрицателни числа да обърне нормалното им компенсира посока - отрицателни колони компенсирани от ляво, така и отрицателни редове компенсират по-горе.
  • OFFSET е „летлива функция“ - тя ще се преизчислява при всяка промяна на работния лист. Летливите функции могат да накарат по-големите и сложни работни книги да работят бавно.
  • OFFSET ще покаже #REF! стойност на грешката, ако изместването е извън ръба на работния лист.
  • Когато височината или ширината са пропуснати, се използват височината и ширината на референцията .
  • OFFSET може да се използва с всяка друга функция, която очаква да получи референция.
  • Документацията на Excel казва, че височината и ширината не могат да бъдат отрицателни, но отрицателните стойности работят.

Подобни видеа

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

Интересни статии...