Преузмите пример радне свеске
Овај водич показује како се користи Екцел ОФФСЕТ функција у Екцелу за креирање референтног помака од почетне ћелије.
ОФФСЕТ Преглед функције
Функција ОФФСЕТ Почиње са дефинисаном референцом ћелије и враћа референцу ћелије одређени број редова и колона померених од оригиналног референци. Референце могу бити једна ћелија или низ ћелија. Одступање вам такође омогућава да промените величину референце одређеном броју редова/колона.
(Обратите пажњу на то како се појављују уноси формуле)
Синтакса и улази функције ИФЕРРОР:
| 1 | = ОФФСЕТ (референца, редови, колоне, висина, ширина) |
референца - Почетна референца ћелије од које желите да се помакнете.
редове - Број редова за помак.
цолс - Број ступаца за помак.
висина - ОПЦИОНАЛНО: Подесите број редова у референци.
ширина - ОПЦИОНАЛНО: Подесите број ступаца у референци.
Шта је функција ОФФСЕТ?
Функција ОФФСЕТ једна је од моћнијих функција прорачунске таблице јер може бити прилично свестрана у ономе што ствара. Кориснику даје могућност да дефинише ћелију или опсег у различитим положајима и величинама.
ОПРЕЗ: ОФФСЕТ функција је једна од нестабилних функција. Већину времена док радите у табели, рачунар ће поново израчунати формулу само ако су уноси променили своје вредности. Променљива функција се, међутим, прерачунава сваки време када промените било коју ћелију. Треба бити опрезан како не би дошло до великог времена поновног израчунавања због прекомерне употребе испарљиве функције или због тога што многе ћелије зависе од резултата испарљиве функције.
Основни примери редова
У свакој употреби функције ОФФСЕТ морате дати почетну тачку или сидро. Погледајмо ову табелу да бисмо лакше разумели ово:
Користићемо „Боб“ у ћелији Б3 као нашу сидришну тачку. Да желимо да зграбимо вредност испод (Чарли), рекли бисмо да желимо да померимо ред за 1. Наша формула би изгледала као
| 1 | = ОФФСЕТ (Б3, 1) |
Ако желимо да се померимо горе, то би био негативан помак. Ово можете замислити како се број реда смањује, па морамо одузети. Дакле, да бисмо добили горњу вредност (Адам), написали бисмо
| 1 | = ОФФСЕТ (Б2, -1) |
Примери основних колона
Настављајући идеју из претходног примера, додаћемо још једну колону у нашу табелу.
Да желимо да ухватимо учитеља за Боба, могли бисмо да користимо формулу
| 1 | = ОФФСЕТ (Б2, 0, 1) |
У овом случају, рекли смо да желимо да померимо нулу редова (ака остати у истом реду), али желимо да померимо 1 колону. За колоне позитиван број значи помак удесно, а негативан број помак улијево.
ОФФСЕТ и МАТЦХ
Претпоставимо да имате неколико колона података и желите да дате кориснику могућност избора из које колоне ће доносити резултате. Можете користити ИНДЕКС функцију или ОФФСЕТ. Пошто ће МАТЦХ вратити релативни положај вредности, мораћемо да се уверимо да је тачка сидра лево од наше прве могуће вредности. Размотрите следећи распоред:
У Б2 ћемо написати ову формулу:
| 1 | = ОФФСЕТ (Б2, 0, МАТЦХ (А2, $ Ц $ 1: $ Ф $ 1, 0)) |
МАТЦХ ће изгледати „Феб“ у опсегу Ц1: Ф1 и наћи ће се у 2нд мобилни. ОФФСЕТ ће тада померити 1 колону десно од Б2 и ухватити жељену вредност 9. Имајте на уму да ОФФСЕТ нема проблема да користи исту ћелију која садржи формулу као тачку сидрења.
НАПОМЕНА: Ова техника се може користити као замена за ВЛООКУП или ХЛООКУП када желите да вратите вредност са леве стране/изнад опсега претраге. То је зато што ОФФСЕТ може направити негативне помаке.
ОФФСЕТ да бисте добили опсег
Можете користити 4тх и 5тх аргументи у функцији ОФФСЕТ за враћање опсега, а не само једне ћелије. Претпоставимо да желите да збројите 3 колоне у овој табели.
| 1 | = ПРОСЈЕЧНО (ОДМЈЕР (А1, МАТЦХ (Ф2, А2: А5,0), 1,1,3)) |
У Ф2 смо изабрали име ученика за које желимо да добијемо њихове просечне резултате теста. Да бисмо то урадили, користићемо формулу
| 1 | = ПРОСЈЕЧНО (ОДМЈЕР (А1, МАТЦХ (Ф2, А2: А5,0), 1,1,3)) |
МАТЦХ ће претражити кроз колону А наше име и вратити релативну позицију, која је 3 у нашем примеру. Да видимо како ће се то оценити. Прво ће кренути ОФФСЕТ доле 3 реда од А1 и 1 колона до јел тако од А1. Ово нас ставља у ћелију Б3.
| 1 | = ПРОСЈЕЧНО (ОДМЈЕР (А1, 3, 1, 1, 3)) |
Затим ћемо променити распон. Нови распон ће имати Б3 као горњу леву ћелију. Биће висок 1 ред и 3 колоне, што нам даје распон Б4: Д4.
| 1 | = ПРОСЈЕЧНО (ОДМЈЕР (А1,3, 1, 1, 3)) |
Имајте на уму да иако можете легитимно ставити негативне вредности у аргументе офсет, можете користити само негативне вредности у аргументима величине.
На крају, наша функција АВЕРАГЕ види:
| 1 | = ПРОСЕЧНО (Б4: Д4) |
Тако добијамо решење 86.67
ОФФСЕТ са динамичким ЗБОРОМ
Будући да се ОФФСЕТ користи за проналажење референце, а не за директно упућивање на ћелију, најкорисније је када се бавите подацима који имају додане или избрисане редове. Размотрите следећу табелу са укупним бројем на дну
| 1 | = ЗБИР (Б2: Б4) |
Да смо овде користили основну формулу СУМ од „= СУМ (Б2: Б4)“, а затим уметнули нови ред да бисмо додали запис за Билла, имали бисмо погрешан одговор
Уместо тога, размислимо како да то решимо са гледишта Тотал -а. Заиста желимо да зграбимо све од ћелије Б2 до ћелије мало изнад нашег укупног износа. Начин на који ово можемо записати у формулу је да направимо помак реда -1. Дакле, ово користимо као формулу за наш укупни износ у ћелији Б5:
| 1 | = ЗБИР (Б2: ОФФСЕТ (Б5, -1,0)) |
Ова формула ради оно што смо управо описали: почните од Б2 и идите до 1 ћелије изнад наше укупне ћелије. Можете видети како се након додавања Биллових података наш укупан број исправно ажурира.
ОФФСЕТ да бисте добили последњих Н ставки
Рецимо да бележите месечну продају, али желите да можете да погледате последња 3 месеца. Уместо да морате ручно да ажурирате формуле да бисте наставили да се прилагођавате како се додају нови подаци, можете да користите функцију ОФФСЕТ са ЦОУНТ.
Већ смо показали како можете користити ОФФСЕТ за хватање низа ћелија. Да бисмо утврдили колико ћелија треба да преместимо, користићемо ЦОУНТ да бисмо пронашли колико бројеви су у колони Б. Погледајмо нашу табелу узорака.
| 1 | = ЗБИР (ОФФСЕТ ($ Б $ 1, ЦОУНТ (Б: Б)-$ Е $ 1+1,0, $ Е $ 1,1)) |
Да смо почели од Б1 и померили 4 реда (број бројева у колони Б), завршили бисмо на дну нашег опсега, Б5. Међутим, пошто ОФФСЕТ не може да промени величину са негативном вредношћу, морамо да извршимо нека прилагођавања како бисмо завршили у Б3. Општа једначина за ово ће бити тачна
| 1 | ЦОУНТ (…) - Н + 1 |
Узимамо пребројавање целе колоне, одузимамо колико год желимо да вратимо (пошто ћемо променити величину да бисмо их ухватили), а затим додамо 1 (пошто у основи започињемо одступање на позицији нула).
Овде можете видети да смо подесили опсег за добијање збира, просека и максимума у последњих Н месеци. У Е1 смо унели вредност 3. У Е2, наша формула је
| 1 | = ЗБИР (ОФФСЕТ ($ Б $ 1, ЦОУНТ (Б: Б)-$ Е $ 1+1,0, $ Е $ 1,1)) |
Истакнути одељак је наша општа једначина о којој смо управо говорили. Не морамо да померамо ниједну колону. Затим ћемо променити величину опсега тако да буде 3 ћелије висок (одређен вредношћу у Е1) и широк 1 ступац. Наш збир тада узима овај распон и даје резултат од 1.850 долара. Такође смо показали да можете израчунати просек макс у овом истом опсегу једноставним пребацивањем спољне функције са СУМ на све што ситуација захтева.
ОФФСЕТ динамичке листе валидације
Користећи технику приказану у последњем примеру, можемо такође изградити именоване опсеге који се могу користити у провери података или графиконима. Ово може бити корисно када желите да поставите табелу, али очекујете да ће се наше листе/подаци променити у величини. Рецимо да наша продавница почиње да продаје воће, а тренутно имамо 3 избора.
Да бисмо направили падајући мени Валидација података који можемо да користимо на другом месту, дефинисаћемо именовани опсег МиФруит као
| 1 | = $ А $ 2: ОФФСЕТ ($ А $ 1, ЦОУНТА ($ А: $ А) -1, 0) |
Уместо ЦОУНТ, ми користимо ЦОУНТА јер се бавимо текстуалним вредностима. Због овога ће, међутим, наша ЦОУНТА бити једна већа јер ће пребројавати ћелију заглавља у А1 и дати вредност 4. Ако ипак одступимо за 4 реда, завршили бисмо у ћелији А5 која је празна. Да бисмо то прилагодили, одузимамо 1.
Сада када имамо постављено именовање распона, можемо поставити неке провјере података у ћелију Ц4 користећи тип листе, са извором:
| 1 | = МиФруит |
Имајте на уму да падајући мени приказује само наше три тренутне ставке. Ако затим додамо још ставки на нашу листу и вратимо се на падајући мени, листа приказује све нове ставке без потребе да мењамо било коју формулу.
Опрез при коришћењу ОФФСЕТ -а
Као што је споменуто на почетку овог чланка, ОФФСЕТ је нестабилна функција. Ово нећете приметити ако га користите у само неколико ћелија, али ако почнете да га укључујете у стотине прорачуна, брзо ћете приметити да ваш рачунар троши приметно много времена на прерачунавање сваки пут када унесете било какве промене .
Осим тога, будући да ОФФСЕТ не именује директно ћелије које гледа, други корисници теже долазе касније и мењају формуле ако је потребно.
Уместо тога, било би упутно користити Табеле (уведене у Оффице 2007) које дозвољавају структурне референце. То је помогло корисницима да дају једну референцу која се аутоматски прилагођава величином при додавању или брисању нових података.
Друга опција уместо ОФФСЕТ -а је моћна ИНДЕКС функција. ИНДЕКС вам омогућава да изградите све динамичке опсеге које смо видели у овом чланку без питања да је то променљива функција.
Додатне напомене
Користите функцију ОФФСЕТ да бисте вратили вредност ћелије (или опсег ћелија) померањем датог броја редова и колона од почетне референце. Када траже само једну ћелију, ОФФСЕТ формуле постижу исту сврху као ИНДЕКС формуле, користећи нешто другачију технику. Права моћ ОФФСЕТ функције лежи у њеној способности да изабере опсег ћелија које ће се користити у другој формули.
Када користите функцију ОФФСЕТ, дефинирате почетну почетну ћелију или распон ћелија. Затим означите број редова и ступаца за помак од те почетне ћелије. Такође можете променити величину опсега; додавање или одузимање редова или колона.
Повратак на листу свих функција у програму Екцел
ОФФСЕТ у Гоогле табелама
Функција ОФФСЕТ ради потпуно исто у Гоогле таблицама као и у Екцелу:
