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