Функция Excel OFFSET - Примери за формули + БЕЗПЛАТНО видео

Функция Excel OFFSET (пример + видео)

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

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

Какво връща

Той връща препратката, към която сочи функцията OFFSET.

Синтаксис

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

Аргументи за въвеждане

• справка - Референцията, от която искате да компенсирате. Това може да бъде референтна клетка или диапазон от съседни клетки.
• редове - броят на редовете за изместване. Ако използвате положително число, то се измества към редовете по -долу, а ако се използва отрицателно число, то се измества към редовете по -горе.
• cols - броя на колоните за компенсиране. Ако използвате положително число, то се измества към колоните вдясно, а ако се използва отрицателно число, то се измества към колоните вляво.
• [височина] - това е число, което представлява броя редове в върнатата препратка.
• [ширина] - това е число, което представлява броя на колоните в върнатата препратка.

Разбиране на основите на функцията Excel OFFSET

Функцията Excel OFFSET е може би една от най -объркващите функции в Excel.

Нека вземем прост пример за игра на шах. В Шах има едно парче, наречено „Топ“ (известно също като кула, маркиз или ректор).

[С любезното съдействие на изображението: Прекрасна Уикипедия]

Сега, както и всички останали фигури в шах, една топка има фиксиран път, по който може да се движи по дъската. Тя може да върви направо (надясно/наляво или нагоре/надолу по дъската), но не може да върви по диагонал.

Да предположим например, че имаме шахматна дъска в Excel (както е показано по -долу), в дадена кутия (D5 в този случай), Ладаят може да върви само в четирите маркирани посоки.

Сега, ако ви помоля да вземете Ладаша от текущото му положение до това, подчертано в жълто, какво ще кажете на топа?

Ще го помолите да направи две стъпки надолу и две стъпки надясно … нали?

И това е близо до това как функционира функцията OFFSET.

Сега нека видим какво означава това в Excel. Искам да започна с клетката D5 (която е мястото, където е Rook) и след това да отида два реда надолу и две колони вдясно и да извлека стойността от клетката там.

Формулата ще бъде:
= OFFSET (откъде да започнете, колко реда надолу, колко колони вдясно)

Както можете да видите, формулата в горния пример в клетка J1 е = OFFSET (D5,2,2).

Той започна от D5, след което се спусна с два реда надолу и две колони вдясно и стигна до клетката F7. След това връща стойността в клетка F7.

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

Нека разгледаме един прост пример тук:

Да предположим, че искате да използвате препратката към клетка A1 (в жълто) и искате да се позовавате на целия диапазон, подчертан със синьо (C2: E4) във формула.

Как бихте направили това с помощта на клавиатура? Първо ще отидете в клетка C2 и след това изберете всички клетки в C2: E4.

Нека сега да видим как да направим това, използвайки формулата OFFSET:

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

Ако използвате тази формула в клетка, тя ще върне #VALUE! грешка, но ако влезете в режим на редактиране и изберете формулата и натиснете F9, ще видите, че тя връща всички стойности, които са маркирани в синьо.

Нека сега да видим как работи тази формула:

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

• Първият аргумент е клетката, от която трябва да започне.
• Вторият аргумент е 1, който казва на Excel да върне препратка, която е била OFFSET от 1 ред.
• Третият аргумент е 2, който казва на Excel да върне препратка, която е била OFFSET от 2 колони.
• Четвъртият аргумент е 3. Това уточнява, че препратката трябва да обхваща 3 реда. Това се нарича аргумент за височина.
• Петият аргумент е 3. Това уточнява, че препратката трябва да обхваща 3 колони. Това се нарича аргумент за ширината.

Сега, когато имате препратка към диапазон от клетки (C2: E4), можете да го използвате в друга функция (като SUM, COUNT, MAX, AVERAGE).

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

Функция Excel OFFSET - Примери

Ето два примера за използване на функцията Excel OFFSET.

Пример 1 - Намиране на последната попълнена клетка в колоната

Да предположим, че имате някои данни в колона, както е показано по -долу:

За да намерите последната клетка в колоната, използвайте следната формула:

= OFFSET (A1, COUNT (A: A) -1,0)

Тази формула приема, че няма стойности освен показаните и тези клетки нямат празна клетка в нея. Той работи, като брои общия брой клетки, които са запълнени, и съответно компенсира клетката A1.

Например, в този случай има 8 стойности, така че COUNT (A: A) връща 8. Ние компенсираме клетката A1 с 7, за да получим последната стойност.

Пример 2 - Създаване на динамично падащо меню

Можете да разширите концептуалните шоута в Пример 1, за да създадете динамични именувани диапазони. Те биха могли да бъдат полезни, ако използвате наименуваните диапазони във формули или при създаване на падащи менюта и е вероятно да добавяте/изтривате данни от препратката, която прави посочения диапазон.

Ето един пример:

Имайте предвид, че падащото меню се настройва автоматично, когато годината се добави или премахне.

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

Ето как да направите това:

• Изберете клетката, в която искате да вмъкнете падащото меню.
• Отидете на Данни -> Инструменти за данни -> Проверка на данни.
• В диалоговия прозорец Проверка на данни в раздела Настройки изберете Списък от падащото меню.
• В източника въведете следната формула: = OFFSET (A1,0,0, COUNT (A: A), 1)
• Щракнете върху OK.

Нека видим как работи тази формула:

• Първите три аргумента на функцията OFFSET са A1, 0 и 0. Това по същество означава, че тя ще върне същата референтна клетка (която е A1).
• Четвъртият аргумент е за височина и тук функцията COUNT връща общия брой елементи в списъка. Предполага, че в списъка няма празни места.
• Петият аргумент е 1, което показва, че ширината на колоната трябва да бъде една.

Допълнителни бележки:

• OFFSET е нестабилна функция (използвайте с повишено внимание).
  • Той преизчислява винаги, когато работната книга на Excel е отворена или когато се задейства изчисление в работния лист. Това може да увеличи времето за обработка и да забави работната ви книга.
• Ако стойността на височината или ширината е пропусната, тя се приема като тази на препратката.
• Ако редове и cols са отрицателни числа, тогава посоката на отместване е обърната.

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

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

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

Функция Excel OFFSET - Видео урок

• Функция Excel VLOOKUP.
• Функция Excel HLOOKUP.
• Функция Excel INDEX.
• Функция Excel INDIRECT.
• Функция Excel MATCH.