Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel OFFSET в Excel, за да създадете референтно отместване от начална клетка.
Преглед на функцията OFFSET
Функцията OFFSET започва с определена препратка към клетка и връща препратка към клетка определен брой редове и колони, изместени от оригиналния референтен. Препратките могат да бъдат една клетка или диапазон от клетки. Offset също ви позволява да преоразмерите препратката към даден брой редове/колони.
(Забележете как се появяват входовете на формулата)
Синтаксис и входове на функцията IFERROR:
| 1 | = OFFSET (справка, редове, cols, височина, ширина) |
справка - Началната препратка към клетката, от която искате да изместите.
редове - Броят редове за изместване.
cols - Броят на колоните за компенсиране.
височина - ОПЦИОНАЛНО: Регулирайте броя редове в препратката.
ширина - ОПЦИОНАЛНО: Регулирайте броя на колоните в препратката.
Какво представлява функцията OFFSET?
Функцията OFFSET е една от най -мощните функции за електронни таблици, тъй като може да бъде доста гъвкава в това, което създава. Тя дава на потребителя възможността да дефинира клетка или диапазон в различни позиции и размери.
ВНИМАНИЕ: Функцията OFFSET е една от летливите функции. През повечето време, когато работите в електронната си таблица, компютърът ще преизчисли формула само ако входовете са променили стойностите си. Нестабилна функция обаче се преизчислява всеки време да направите промяна в която и да е клетка. Трябва да се внимава, за да се гарантира, че не причинявате голямо време за преизчисляване поради прекомерно използване на летливи функции или наличието на много клетки в зависимост от резултата от летлива функция.
Основни примери за редове
При всяко използване на функцията OFFSET трябва да давате начална точка или котва. Нека разгледаме тази таблица, за да разберем това:
Ще използваме „Боб“ в клетка В3 като наша опорна точка. Ако искахме да грабнем стойността точно под (Чарли), бихме казали, че искаме да изместим реда с 1. Нашата формула ще изглежда така
| 1 | = OFFSET (B3, 1) |
Ако искаме да се изместим нагоре, това би било отрицателна промяна. Можете да мислите за това, тъй като номерът на реда намалява, така че трябва да извадим. По този начин, за да получим стойността по -горе (Адам), бихме написали
| 1 | = OFFSET (B2, -1) |
Примери за основни колони
Продължавайки идеята от предишния пример, ще добавим още една колона към нашата таблица.
Ако искахме да грабнем учителя за Боб, бихме могли да използваме формулата
| 1 | = OFFSET (B2, 0, 1) |
В този случай казахме, че искаме да изместим нулевите редове (известен също като оставане на същия ред), но искаме да изместим 1 колона. За колоните положителното число означава отместване вдясно, а отрицателните числа означава отместване вляво.
ОФСЕТ и МАТЧ
Да предположим, че имате няколко колони с данни и искате да дадете на потребителя възможност да избере от коя колона да извлича резултати. Можете да използвате функцията INDEX или OFFSET. Тъй като MATCH ще върне относителната позиция на стойност, ще трябва да се уверим, че точката на закрепване е вляво от първата ни възможна стойност. Помислете за следното оформление:
В B2 ще напишем тази формула:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH ще изглежда „Feb“ в диапазона C1: F1 и ще го намери в 2nd клетка. След това OFFSET ще измести 1 колона вдясно от B2 и ще вземе желаната стойност 9. Обърнете внимание, че OFFSET няма проблем да използва същата клетка, която съдържа формулата като точка на закрепване.
ЗАБЕЛЕЖКА: Тази техника може да се използва като заместител на VLOOKUP или HLOOKUP, когато искате да върнете стойност отляво/над обхвата на търсене. Това е така, защото OFFSET може да направи отрицателни отмествания.
OFFSET, за да получите диапазон
Можете да използвате 4th и 5th аргументи във функцията OFFSET за връщане на диапазон, а не само на една клетка. Да предположим, че искате да сумирате 3 колони в тази таблица.
| 1 | = СРЕДНО (ОТКЛЮЧВАНЕ (A1, МАТЧ (F2, A2: A5,0), 1,1,3)) |
Във F2 избрахме името на студент, за който искаме да извлечем средните им резултати от теста. За да направим това, ще използваме формулата
| 1 | = СРЕДНО (ОТКЛЮЧВАНЕ (A1, МАТЧ (F2, A2: A5,0), 1,1,3)) |
MATCH ще търси в колона A нашето име и ще върне относителната позиция, която е 3 в нашия пример. Да видим как това ще бъде оценено. Първо, OFFSET ще отиде надолу 3 реда от A1 и 1 колона до надясно от А1. Това ни поставя в клетка В3.
| 1 | = СРЕДНО (ОТКЛЮЧВАНЕ (A1, 3, 1, 1, 3)) |
След това ще променим размера на диапазона. Новият диапазон ще има B3 като горна лява клетка. Той ще бъде висок 1 ред и висок 3 колони, което ни дава диапазона B4: D4.
| 1 | = СРЕДНО (ОТКЛЮЧВАНЕ (A1,3, 1, 1, 3)) |
Обърнете внимание, че макар да можете законно да поставяте отрицателни стойности в аргументите за отместване, можете да използвате само неотрицателни стойности в аргументите за оразмеряване.
В края нашата функция AVERAGE вижда:
| 1 | = СРЕДНО (B4: D4) |
Така получаваме нашето решение от 86.67
ОФСЕТ с динамична сума
Тъй като OFFSET се използва за намиране на препратка, а не директно насочване към клетката, това е най -полезно, когато имате работа с данни, в които има добавени или изтрити редове. Помислете за следната таблица с общо в долната част
| 1 | = SUM (B2: B4) |
Ако бяхме използвали основна формула SUM тук „= SUM (B2: B4)“ и след това вмъкнахме нов ред, за да добавим запис за Bill, щяхме да имаме грешен отговор
Вместо това нека помислим как да решим това от гледна точка на Total. Наистина искаме да вземем всичко от клетка В2 до клетката малко над нашата сума. Начинът, по който можем да напишем това във формула, е да направим отместване на ред от -1. По този начин ние използваме това като формула за общата ни сума в клетка В5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Тази формула прави това, което току -що описахме: започнете от В2 и отидете до 1 клетка над общата ни клетка. Можете да видите как след добавяне на данните на Бил общата ни сума се актуализира правилно.
OFFSET, за да получите последните N артикула
Да речем, че записвате месечни продажби, но искате да можете да разгледате последните 3 месеца. Вместо да се налага ръчно да актуализирате формулите си, за да продължите да се коригирате при добавяне на нови данни, можете да използвате функцията OFFSET с COUNT.
Вече показахме как можете да използвате OFFSET, за да вземете диапазон от клетки. За да определим колко клетки трябва да изместим, ще използваме COUNT, за да намерим колко числа са в колона Б. Нека да разгледаме нашата примерна таблица.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Ако започнахме от B1 и изместихме 4 реда (броят на числата в колона B), щяхме да свършим в дъното на нашия диапазон, B5. Въпреки това, тъй като OFFSET не може да преоразмерява с отрицателна стойност, трябва да направим някои корекции, така че да се озовем на B3. Общото уравнение за това ще бъде направено
| 1 | БРОЙ (…) - N + 1 |
Взимаме броя на цялата колона, изваждаме колкото и много да искаме да върнем (тъй като ще преоразмеряваме, за да ги вземем) и след това добавяме 1 (тъй като по същество започваме изместването си на позиция нула).
Тук можете да видите, че сме настроили диапазон, за да получим сумата, средната стойност и максималната стойност за последните N месеца. В E1 сме въвели стойността на 3. В E2 нашата формула е
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Откроеният раздел е нашето общо уравнение, което току -що обсъдихме. Не е нужно да компенсираме никакви колони. След това ще променим размера на диапазона, за да бъде 3 клетки висок (определен от стойността в E1) и 1 ширина на колоната. След това нашата сума възприема този диапазон и ни дава резултат от 1 850 долара. Ние също така показахме, че можете да изчислите средната стойност на max от същия този диапазон, като просто превключите външната функция от SUM към всичко, което изисква ситуацията.
Списъци за динамично валидиране OFFSET
Използвайки техниката, показана в последния пример, можем също да изградим именовани диапазони, които могат да се използват при проверка на данни или диаграми. Това може да бъде полезно, когато искате да настроите електронна таблица, но очаквате нашите списъци/данни да променят размера си. Да кажем, че нашият магазин започва да продава плодове и в момента имаме 3 възможности за избор.
За да направим падащо меню за валидиране на данни, което можем да използваме другаде, ще определим именования диапазон MyFruit като
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Вместо COUNT, ние използваме COUNTA, тъй като се занимаваме с текстови стойности. Поради това обаче нашата COUNTA ще бъде с една по -висока, тъй като ще брои клетката на заглавката в A1 и ще даде стойност 4. Ако компенсираме с 4 реда, щяхме да се окажем в клетка A5, която е празна. За да коригираме това, тогава изваждаме 1.
Сега, когато имаме нашата настройка на именования диапазон, можем да настроим валидиране на данни в клетка C4, като използваме списък тип с източник:
| 1 | = MyFruit |
Обърнете внимание, че падащото меню показва само трите ни текущи елемента. Ако след това добавим още елементи към нашия списък и се върнем към падащото меню, списъкът показва всички нови елементи, без да се налага да променяме някоя от формулите.
Внимание при използване на OFFSET
Както бе споменато в началото на тази статия, OFFSET е нестабилна функция. Няма да забележите това, ако го използвате само в няколко клетки, но ако започнете да го включвате в стотици изчисления и бързо ще забележите, че компютърът ви изразходва значително време за преизчисляване всеки път, когато правите каквито и да било промени .
Освен това, тъй като OFFSET не назовава директно клетките, които разглежда, за други потребители е по -трудно да дойдат по -късно и да променят формулите ви, ако е необходимо.
Вместо това би било препоръчително да се използват таблици (въведени в Office 2007), които позволяват структурни препратки. Това помогна на потребителите да могат да дадат една препратка, която автоматично се коригира по размер при добавяне или изтриване на нови данни.
Другата възможност да използвате вместо OFFSET е мощната функция INDEX. INDEX ви позволява да изграждате всички динамични диапазони, които видяхме в тази статия, без проблем да бъде променлива функция.
допълнителни бележки
Използвайте функцията OFFSET, за да върнете стойност на клетка (или диапазон от клетки), като изместите даден брой редове и колони от начална препратка. Когато търсите само една клетка, формулите OFFSET постигат същата цел като формулите INDEX, като използват малко по -различна техника. Истинската сила на функцията OFFSET се крие в способността й да избира диапазон от клетки, които да се използват в друга формула.
Когато използвате функцията OFFSET, вие определяте начална начална клетка или диапазон от клетки. След това посочвате броя редове и колони, които да се изместят от тази начална клетка. Можете също да промените размера на диапазона; добавяне или изваждане на редове или колони.
Връщане към списъка с всички функции в Excel
ОФСЕТ в Google Таблици
Функцията OFFSET работи абсолютно същото в Google Sheets като в Excel:
