Изтеглете примерната работна книга
В други статии сме обсъждали как има функции като OFFSET и INDIRECT, които са нестабилни. Ако започнете да използвате много от тях в електронна таблица или имате много клетки, зависещи от променлива функция, можете да накарате компютъра си да прекарва забележимо време в преизчисляване всеки път, когато се опитате да промените клетка. Вместо да се разочаровате от това как компютърът ви не е достатъчно бърз, тази статия ще изследва алтернативни начини за решаване на често срещаните ситуации, които хората използват OFFSET и INDIRECT.
Замяна на OFFSET за създаване на динамичен списък
След като научихте за функцията OFFSET, е често срещано погрешно схващане, че това е единственият начин да върнете резултат с динамичен размер, използвайки последните няколко аргумента. Нека да разгледаме списък в колона А, където нашият потребител може да реши по -късно да добави допълнителни елементи.
За да направите падащо меню в клетка C2, можете да определите именован диапазон с променлива формула като
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
С текущата настройка това със сигурност ще върне препратка към диапазона A2: A5. Има обаче друг начин за използване на енергонезависимия INDEX. За да направите това, помислете за това, че пишем препратка към диапазона от А2 до А5. Когато пишете „A2: A5“, не мислете за това като за единична информация, а по -скоро за „StartingPoint“ и „EndingPoint“, разделени с двоеточие (напр. StartingPoint: EndingPoint). Във формула и StartingPoint, и EndingPoint могат да бъдат резултати от други функции.
Ето формулата, която ще използваме за създаване на динамичен диапазон, използвайки функцията INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Обърнете внимание, че сме заявили, че началната точка за този диапазон винаги ще бъде A2. От другата страна на дебелото черво използваме INDEX, за да определим къде ще бъде EndingPoint. COUNTA ще определи, че има 5 клетки с данни в колона А и затова нашият INDEX ще създаде препратка към A5. Така формулата се оценява така:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Използвайки тази техника, можете динамично да изграждате препратка към всеки списък или дори двуизмерна таблица, използвайки функцията INDEX. В електронна таблица с изобилие от OFFSET функции, замяната на OFFSET с INDEX ще позволи на компютъра ви да започне да работи много по -бързо.
Замяна на INDIRECT за имена на листове
Функцията INDIRECT често се извиква, когато работните книги са проектирани с данни, разпръснати в множество работни листове. Ако не можете да получите всички данни на един лист, но не искате да използвате променлива функция, може да използвате CHOOSE.
Помислете за следното оформление, където имаме данни за продажбите в 3 различни работни листа. В нашия обобщен лист сме избрали от кое тримесечие бихме искали да прегледаме данните.
Нашата формула в B3 е:
= ИЗБЕРЕТЕ (МАТЧ (B2, D2: D4, 0), есен! A2, зима! A2, пролет! A2)
В тази формула функцията MATCH ще определи коя област искаме да върнем. След това се казва на функцията CHOOSE кой от следните диапазони да се върне като резултат.
Можете също да използвате функцията ИЗБОР, за да върнете по -голям диапазон. В този пример имаме таблица с данни за продажбите за всеки от трите ни работни листа.
Вместо да напишете INDIRECT функция за изграждане на името на листа, можете да позволите на CHOOSE да определи в коя таблица да се търси. В моя пример вече съм кръстил трите таблици tbFall, tbWinter и tbSpring. Формулата в B4 е:
= VLOOKUP (B3, ИЗБОР (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
В тази формула MATCH ще определи, че искаме 2nd артикул от нашия списък. След това CHOOSE ще вземе това 2 и ще върне препратката към tbWinter. И накрая, нашият VLOOKUP ще може да завърши търсенето в дадената таблица и ще установи, че общите продажби за Banana през зимата са били $ 6000.
= VLOOKUP (B3, ИЗБОР (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Тази техника е ограничена от факта, че трябва да попълните функцията CHOOSE с всички области, от които може да искате да извлечете стойност, но тя ви дава предимството да избегнете променлива формула. В зависимост от това колко изчисления трябва да завършите, тази способност може да се окаже доста ценна.
