Excel OFFSET функция

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

Синтаксис

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

Аргументи

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

Функцията OFFSET ще върне референтно отместване на клетка от дадена начална точка.

Бележки за функциите

1. #СТОЙНОСТ! стойността за грешка ще се върне, когато препратката се отнася до диапазон от прекъснати клетки.
2. #REF! стойността за грешка ще се върне, когато редовете и колоните изместят препратката над ръба на работния лист.

Примери

Пример 1: Основна употреба за функцията OFFSET

Върнете препратка към клетка с формулата по-долу:
=OFFSET(B2,3,1)

В този случай B2 е началната точка, номер 3 и 1 означават, че трябва да преместите 3 реда надолу и 1 колона надясно от клетка B2 и накрая да върнете стойността в C5. Вижте екранна снимка:

Върнете препратка към диапазон от клетки с формулата по-долу:
=OFFSET(B2,3,1,2,2)

В този случай ще получите резултатите от диапазон 2 x 2, който е 3 реда по-долу и 1 колона вдясно от клетка B2.

Забележка: #СТОЙНОСТ! Ще възникне грешка, когато изберете само една клетка, за да приложите функцията OFFSET за връщане на диапазон от клетки. Трябва да изберете диапазон 2 x 2 (казва 4 празни клетки), да въведете формулата и да натиснете Ctrl + Превключване + Въведете ключове, за да получите резултатите.

Пример 2: Използвайте функцията OFFSET, за да сумирате диапазон от стойности

Както споменахме по-горе, ако се опитате да използвате функцията OFFSET като напр =OFFSET(B2,3,1,2,2) сам по себе си в една клетка, той ще върне #VALUE! Грешка. Въпреки това, ако комбинирате функцията SUM и OFFSET, както е показано на екранната снимка по-долу, тя ще върне директно сумата от стойности в диапазона C5:D6.

1. Изберете празна клетка, копирайте формулата по-долу в нея и натиснете Въведете ключ за получаване на резултата.
=SUM(OFFSET(B2,3,1,2,2)))

Пример 3: Сумиране на колони въз основа на определени критерии

Както е показано на екранната снимка по-долу, как да получите общите продажби на Mango от неделя до вторник? Моля, опитайте както по-долу.

1. Изберете празна клетка, копирайте формулата по-долу в нея и натиснете Въведете ключ за получаване на резултата.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

бележки:

1. В горната формула, MATCH(G4,B3:B8) търси Mango и връща позицията си в обсега B3: B8. Да видим, Mango намира 5 реда под клетка B2, така че връща числото 5;
2. Тъй като резултатът MATCH е 5, а последната запетая тук представлява 0, функцията OFFSET сега се показва като OFFSET(C2:E2,5,0), което означава, че отместването започва от диапазон C2:E2, преместете 5 реда надолу и 0 колона надясно, за да получите препратка към диапазон C7:E7;
3. Тогава функцията SUM се показва като SUM(C7:E7)и накрая връща сумата от стойности в диапазон C7:E7.

Още примери

Как да сумирам всеки n реда надолу в Excel?

Как да създадете динамичен именуван диапазон в Excel?

Как да върна стойността на клетка на всеки пет или n-ти ред в Excel?