Екцел ОФФСЕТ функција (Пример + видео)
Када користити Екцел ОФФСЕТ функцију
Екцел ОФФСЕТ функција може се користити када желите да добијете референцу која помера одређени број редова и колона од почетне тачке.
Шта враћа
Враћа референцу на коју указује функција ОФФСЕТ.
Синтакса
= ОФФСЕТ (референца, редови, ступци, [висина], [ширина])
Улазни аргументи
- референца - Референца од које желите да се померите. Ово може бити референца ћелије или низ суседних ћелија.
- редови - број редова за помак. Ако користите позитиван број, он се помера у доње редове, а ако се користи негативан број, он се помера у горње редове.
- цолс - број ступаца за одступање. Ако користите позитиван број, он се помера у колоне са десне стране, а ако се користи негативан број, онда се помера у колоне са леве стране.
- [висина] - ово је број који представља број редова у враћеној референци.
- [ширина] - ово је број који представља број колона у враћеној референци.
Разумевање основа Екцел ОФФСЕТ функције
Екцел ОФФСЕТ функција је вероватно једна од најзбуњујућих функција у Екцелу.
Узмимо једноставан пример шаховске игре. У шаху постоји комад који се зове Роок (такође познат и као кула, маркиз или ректор).
[Љубазношћу слике: Дивна Википедија]
Сада, као и све остале фигуре шаха, и топа има фиксну путању по којој се може кретати по табли. Може ићи равно (десно/лево или горе/доле), али не може дијагонално.
На пример, претпоставимо да имамо шаховску таблу у Екцелу (као што је приказано доле), у датом оквиру (у овом случају Д5), топа може да иде само у четири истакнута правца.
Ако вас замолим да одведете Лауна са њеног тренутног положаја на позицију истакнуту жутом бојом, шта ћете рећи топу?
Замолићете га да направи два корака надоле и два корака удесно … зар не?
И то је приближан начин на који функционише функција ОФФСЕТ.
Хајде сада да видимо шта то значи у Екцелу. Желим да почнем са ћелијом Д5 (где се налази Роок), а затим да идем два реда надоле и две колоне удесно и дохватим вредност из ћелије тамо.
Формула би била следећа:
= ОФФСЕТ (одакле почети, колико редова надоле, колико колона десно)
Као што видите, формула у горњем примеру у ћелији Ј1 је = ОФФСЕТ (Д5,2,2).
Почело је од Д5, а затим се спустило два реда надоле и две колоне десно и стигло до ћелије Ф7. Затим је вратила вредност у ћелију Ф7.
У горњем примеру смо погледали функцију ОФФСЕТ са 3 аргумента. Али постоје још два изборна аргумента која се могу користити.
Погледајмо једноставан пример овде:
Претпоставимо да желите да користите референцу на ћелију А1 (у жутој боји) и желите да се позовете на цео опсег означен плавом бојом (Ц2: Е4) у формули.
Како бисте то урадили користећи тастатуру? Прво бисте отишли у ћелију Ц2, а затим изабрали све ћелије у Ц2: Е4.
Хајде сада да видимо како то да урадимо помоћу ОФФСЕТ формуле:
= ОФФСЕТ (А1,1,2,3,3)
Ако користите ову формулу у ћелији, она ће вратити #ВАЛУЕ! грешка, али ако уђете у режим уређивања, изаберете формулу и притиснете Ф9, видећете да враћа све вредности означене плавом бојом.
Погледајмо сада како функционише ова формула:
= ОФФСЕТ (А1,1,2,3,3)
- Први аргумент је ћелија одакле би требало да почне.
- Други аргумент је 1, који говори Екцелу да врати референцу која је ОФФСЕТ за 1 ред.
- Трећи аргумент је 2, који говори Екцелу да врати референцу која је ОФФСЕТ за 2 колоне.
- Четврти аргумент је 3. Ово специфицира да референца треба покрити 3 реда. Ово се назива аргумент висине.
- Пети аргумент је 3. Ово специфицира да референца треба покрити 3 колоне. Ово се назива аргумент ширине.
Сада када имате референцу на распон ћелија (Ц2: Е4), можете је користити у оквиру других функција (као што су СУМ, ЦОУНТ, МАКС, АВЕРАГЕ).
Надајмо се да имате добро основно разумевање коришћења Екцел ОФФСЕТ функције. Хајде сада да погледамо неке практичне примере коришћења функције ОФФСЕТ.
Екцел ОФФСЕТ функција - Примери
Ево два примера коришћења Екцел ОФФСЕТ функције.
Пример 1 - Проналажење последње попуњене ћелије у колони
Претпоставимо да имате неке податке у колони као што је приказано испод:
Да бисте пронашли последњу ћелију у колони, користите следећу формулу:
= ОФФСЕТ (А1, ЦОУНТ (А: А) -1,0)
Ова формула претпоставља да не постоје вредности осим приказаних и да ове ћелије немају празну ћелију у себи. Функционише тако што броји укупан број ћелија које су испуњене и према томе помера ћелију А1.
На пример, у овом случају постоји 8 вредности, па ЦОУНТ (А: А) враћа 8. Померамо ћелију А1 за 7 да бисмо добили последњу вредност.
Пример 2 - Креирање динамичког падајућег менија
Можете проширити концептне концепте у Примеру 1 да бисте креирали динамичке именоване опсеге. Ово би могло бити корисно ако користите именоване опсеге у формулама или креирате падајуће меније и вероватно ћете додати/избрисати податке из референце која чини именовани опсег.
Ево примера:
Имајте на уму да се падајући мени аутоматски прилагођава када се година дода или уклони.
То се дешава јер је формула која се користи за креирање падајућег менија динамична и идентификује свако додавање или брисање и према томе прилагођава опсег.
Ево како то учинити:
- Изаберите ћелију у коју желите да уметнете падајући мени.
- Идите на Подаци -> Алати за податке -> Валидација података.
- У дијалогу Валидација података на картици Поставке са падајућег менија изаберите Листа.
- У извор унесите следећу формулу: = ОФФСЕТ (А1,0,0, ЦОУНТ (А: А), 1)
- Притисните ОК.
Да видимо како функционише ова формула:
- Прва три аргумента функције ОФФСЕТ су А1, 0 и 0. То у суштини значи да би вратила исту референтну ћелију (која је А1).
- Четврти аргумент је за висину и овде функција ЦОУНТ враћа укупан број ставки на листи. Претпоставља се да на листи нема празних места.
- Пети аргумент је 1, што указује на то да ширина колоне треба да буде једна.
Додатне напомене:
- ОФФСЕТ је нестабилна функција (користите је опрезно).
- Поново израчунава сваки пут када је Екцел радна свеска отворена или кад год се покрене прорачун на радном листу. Ово би могло продужити време обраде и успорити радну свеску.
- Ако је вредност висине или ширине изостављена, узима се као вредност референце.
- Ако редове и цолс су негативни бројеви, тада је смер померања обрнут.
Алтернативе функције Екцел ОФФСЕТ
Због неких ограничења функције Екцел ОФФСЕТ, многи желите да размотрите њене алтернативе:
- ИНДЕКС функција: ИНДЕКС функција се такође може користити за враћање референце ћелије. Кликните овде да видите пример како да креирате динамички именовани опсег помоћу функције ИНДЕКС.
- Екцел табела: Ако у Екцел табели користите структуриране референце, не морате бринути о додавању нових података и потреби прилагођавања формула.
Екцел ОФФСЕТ функција - видео водич
- Екцел ВЛООКУП функција.
- Екцел ХЛООКУП функција.
- Екцел ИНДЕКС функција.
- Екцел ИНДИРЕКТНА функција.
- Екцел МАТЦХ функција.
