Функцията OFFSET на Excel ще забави изчисляването на вашата работна книга. Има по-добра алтернатива: необичаен синтаксис на INDEX.
Това е ниша. Има невероятно гъвкава функция, наречена OFFSET. Той е гъвкав, защото може да сочи към различен по размер диапазон, който се изчислява в движение. На изображението по-долу, ако някой промени падащото меню # Qtrs в H1 от 3 на 4, четвъртият аргумент на OFFSET ще гарантира, че обхватът се разширява, за да включва четири колони.
Гурутата за електронни таблици мразят OFFSET, защото това е променлива функция. Ако отидете до напълно несвързана клетка и въведете число, всички функции OFFSET ще изчислят. Дори ако тази клетка няма нищо общо с H1 или B2. По-голямата част от времето Excel много внимава да губи време само в изчисляване на клетките, които трябва да изчисли. Но след като въведете OFFSET, всички клетки OFFSET, плюс всичко надолу от OFFSET, започват да изчисляват след всяка промяна в работния лист.
Бях съдия на финала на ModelOff през 2013 г. в Ню Йорк, когато няколко мои приятели от Австралия посочиха странно заобикаляне. Във формулата по-долу има двоеточие преди функцията INDEX. Обикновено показаната по-долу функция INDEX ще върне 1403 от клетка D2. Но когато поставите двоеточие от двете страни на функцията INDEX, то започва да връща адреса на клетката D2 вместо съдържанието на D2. Това е диво, че работи.
Защо това има значение? INDEX не е променлив. Получавате цялата гъвкава доброта на OFFSET без повторно преизчисляване на времето за всмукване.
За първи път научих този съвет от Дан Майо в Fintega. Благодарим на Access Analytic, че предложи тази функция.
Гледам видео
Видео транскрипция
Научете Excel от подкаст, епизод 2048 -: INDEX ще замени летлив OFFSET!
Хей, подкастирам всички мои съвети от тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
Добре, OFFSET е невероятна функция! OFFSET ни позволява да посочим клетка в горния ляв ъгъл и след това да използваме променливи, за да дефинираме от там колко реда надолу, колко реда над и след това да дефинираме фигура, добре. Така че тук, ако искам да събера или да направя средно, да кажем 3/4, тази формула тук ще разгледа формулата. OFFSET казва, че започваме от B2, започвайки от Q1, слизаме 0, над 0, фигурата ще бъде висока 1 ред и ще бъде H1, с други думи 3 клетки широки, добре. Така че в този случай всичко, което всъщност правим, е да променим колко клетки събираме, винаги започваме обратно в B2, или B3 или B4, докато копирам, и след това решава колко клетки са широки. Добре, OFFSET е това страхотно нещо, изпълнява всякакви невероятни функции, но ето кавгата, тя е нестабилна!Което означава, че дори нещо да не е във веригата за изчисление, Excel, ще отдели време да го преизчисли, което ще забави нещата, добре така.
Тази невероятна версия на ИНДЕКС, нали, обикновено, ако поискам ИНДЕКСА на тези 4 клетки, 3, ще върне числото 1403. Когато обаче поставя дебело черво преди или след ИНДЕКСА, се случва нещо съвсем различно, ще разгледаме тази формула тук. И така, индекс на 4-те клетки, коя искам, искам третата, но виждате, че има: точно там. Така че винаги ще преминем от B2: E2 и ще ви покажа, ако отидем до Formulas, Evaluate Formula, добре, така че тук ще изчислим числото 3. И тук, INDEX с: next към него, вместо да ни каже 1403, което е как ИНДЕКС обикновено изчислява, ще върне $ D2 и тогава СРЕДНАТА ще направи средната стойност от тези 3. Абсолютно невероятно, начина, по който това работи, и допълнителната полза, не е летливо, добре,и това дори може да се използва за замяна на невероятно сложен OFFSET.
Така че тук с този OFFSET ние се основаваме на тези стойности. Ако избера Q2 и Central, добре, тези формули използват MATCH и COUNTIF, за да разберат колко реда надолу, колко колони над, колко високи, колко широки. И тогава OFFSET тук използва всички тези стойности, за да разбере медианата. Просто ще направим тест, за да се уверим, че работи, така че = MEDIAN от онази синя гама там, по-добре да бъде 71, добре, и ще изберем нещо друго тук, Q3 и West, така че. Натиснете F2, просто ще преместя това и ще го преоразмеря, за да пренапиша тази формула, натиснете Enter и това работи с OFFSET.
Е, тук, използвайки ИНДЕКС, всъщност имам дебело черво в средата с ИНДЕКС от лявата страна и ИНДЕКС от дясната страна, гледайте как това нещо се изчислява в Формула за оценка. Така че започва, ще Оцени, Оцени и точно тук INDEX е на път да го превърне в адрес на клетка. Така че H16 е 1-ви, Западен, Q3, а отдясно ще оцени, още няколко, а след това отдясно тя се променя на I20. Така че хладното, хладно енергонезависимо да замени OFFSET с помощта на функцията INDEX. Добре, този съвет и много други в тази книга щракнете върху „i“ в горния десен ъгъл, за да купите книгата.
Добре резюме: OFFSET, страхотна, гъвкава функция, след като овладеете, можете да правите всякакви неща. Посочете към променлива клетка в горния ляв ъгъл, насочете към диапазон, който има променлива форма, но е променлив и така те изчисляват при всяко изчисление. Excel обикновено прави интелигентно изчисление или преизчислява клетките, които трябва да бъдат изчислени, но с OFFSET винаги ще се изчисли. Вместо OFFSET можете да използвате INDEX: или: INDEX или дори INDEX: INDEX, всеки път, когато INDEX има двоеточие до него, той ще върне адрес на клетка вместо стойността на тази клетка. И ползата е, че INDEX не е променлива!
Добре, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2048.xlsm
