Excel. Използване на функциите OFFSET и INDIRECT за търсене

Функциите OFFSET и INDIRECT са доста сложни за разбиране. Тази бележка и примерите, които съдържа, трябва да ви позволят да овладеете тези функции. Вижте също връзки към допълнителни материали в края на бележката.

Сумиране в диапазон, чийто размер се изчислява в клетка. Проблем: Трябва да сумирам стойностите в диапазон, започващ в клетка A5, а броят на редовете на сумиращия диапазон е зададен в клетка C5 (Фигура 7.1).

Стратегия: използвайте гъвкава (гъвкава) функция OFFSET, която ви позволява да:

  • Започнете да броите от конкретна клетка или диапазон от клетки;
  • Преместване на произхода с посочения брой редове и колони;
  • Посочете броя на редовете и колоните за диапазона, като броите от новия произход.

функциите

Ориз. 7.1. Тази формула сумира стойностите в диапазона A5:A9

Изтеглете бележка във формат Word или pdf, примери във формат Excel

Първият аргумент на функцията SNAP указва произхода. В нашия пример клетка A5. Вторият и третият аргумент ви позволяват да промените произхода. Това е полезно, когато стойностите на тези аргументи са взети от клетки, в които се извършват някои изчисления. В нашия пример началото не е изместено (виждаме две нули). Можем да посочим 2 като втори аргумент и след това началото ще се измести към A7 или -2 и след това ще започнем от клетка A3.Четвъртият и петият аргумент задават размера на диапазона. Те не са задължителни. Ако тези аргументи липсват, тогава височината и ширината на върнатия диапазон са същите като за оригиналния диапазон в първия аргумент. В нашия пример това са пет реда и една колона.

Като цяло функцията OFFSET връща препратка към диапазон или, с други думи, масив от стойности, съхранени в този диапазон (като запазва форматадиапазон, т.е. броят на редовете и колоните). Ако използвате OFFSET самостоятелно, в повечето случаи ще върне неправилен резултат (една от стойностите на масива) или #VALUE! Следователно OFFSET обикновено се използва като аргумент на друга функция. В нашия пример SUM. Само ако OFFSET върне препратка към една клетка, то само по себе си ще върне правилния резултат - стойността в тази клетка.

Променете стойността в C5 от 5 на 3 и формулата ще върне сумата от стойностите в диапазона A5:A7 (фиг. 7.2). В същото време самата формула в клетка C7 не се е променила.

offset

Ориз. 7.2. Променете стойността в C5 на 3 и обхватът на сумиране ще се промени

OFFSET може да се използва за насочване към клетка над текущата. Защо отивате толкова много, когато една проста формула прави същото?

функциите

Но какво се случва, ако ред 4 бъде изтрит? Една проста формула в колона B ще даде #REF! Формулата OFFSET продължава да работи (фиг. 7.4).

offset

Ориз. 7.4. Ако приемем, че някой ред може да бъде изтрит, тогава функцията OFFSET е незаменима

Допълнителна информация: Началният диапазон може да бъде повече от една клетка. В следващия пример началният диапазон е A4:A13. Изчислява се третият аргумент на функцията OFFSET - МЕСЕЦ(A1). Той измества обхвата на сумиране с 5 колони надясно. Формулата =SUM(OFFSET(A4:A13,0,MONTH(A1))) сумира колоната, съответстваща на датата в клетка A1.

excel

Ориз. 7.5. Използвайте OFFSET, за да преместите диапазона на сумата N колони надясно

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

Можете да използвате INDEX вместо OFFSET (Фигура 7.6). По принцип синтаксисът на функцията =INDEX(масив; номер_на_ред; [номер_на_колона]) е проектиран да връща стойността на една клетка от диапазон. Ако обаче пропуснете втория аргумент, Excel ще върне всички редове, т.е. препратка към диапазона F4:F13 (или масив) Формула =SUM(INDEX(B4:H13;;MONTH(A1))) ще върне резултат, еквивалентен на формулата на фиг. 7.5.

използване

Ориз. 7.6. INDEX с пропуснат номер на ред ще върне всички редове в диапазона, т.е. цяла колона

Какво се случи с функцията @@? Lotus 1-2-3 имаше функция @@. Ако сте използвали @@(A3), Lotus ще премине към A3. A3 трябваше да съдържа препратка към клетка. Да кажем, че A3 съдържа текст C5. Функцията @@ върна стойността от клетка C5.

excel

Ориз. 7.7. F2 казва поглед в клетка C1

В следващия пример (Фигура 7.8) функцията VLOOKUP сочи към различен лист въз основа на номера на четвърт в колона B. INDIRECT използва конкатенацията (&), приложена за свързване на текстови низове, за да създаде името на листа в работна книга на Excel в движение.

offset

Ориз. 7.8. Име на листа Q2! изчислено в движение във функцията INDIRECT

Ако използвате имена на диапазони, стойността вътре в INDIRECT може да сочи към името на диапазона. Това създава някои интересни възможности за търсене. Първо, задайте имена на диапазоните. За да направите това, изберете целия диапазон (фиг. 7.9). В раздела ФОРМУЛА изберете Създаване от селекция (или натиснете Ctrl+Shift+F3).

използване

Ориз. 7.9. Изберете диапазон за заместване, включително заглавия

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

excel

Ориз. 7.10. Опции за настройка в прозореца Генериране на имена от избрания диапазон

Имената за редове и колони ще бъдат генерирани автоматично. Например диапазонът $B$3:$I$3 ще бъде наречен Prol2, а диапазонът $E$2:$E$8 ще бъде наречен Apr. Всички имена на диапазони могат да се видят, като отидете на FORMULA –> Мениджър на имена (фиг. 7.11).

използване

Ориз. 7.11. Автоматично генерирани имена на диапазони в прозореца на диспечера на имена

Вече имате седем именувани диапазона въз основа на етикетите (имена) в колона A и осем именувани диапазона въз основа на заглавията на колоните.

Формулата =SUM(INDIRECT(J10) INDIRECT(J11)) първо ще изчисли INDIRECT(J10) = Prod4 и INDIRECT(J11) = Apr. В този случай функцията SUM ще възприеме стойностите на Prod4 и Apr като имена на диапазони. Тъй като се използва интервален оператор, функцията SUM ще върне стойността в пресечната точка на диапазоните Prod4 и Apr, т.е. стойност от клетка E5.

използване

Ориз. 7.12. Необичаен начин за търсене в две посоки