Функція OFFSET в Excel уповільнює обчислення вашої книги. Існує краща альтернатива: незвичний синтаксис INDEX.
Це нішевий наконечник. Існує надзвичайно гнучка функція, яка називається OFFSET. Він гнучкий, оскільки може вказувати на діапазон різного розміру, який розраховується на льоту. На зображенні нижче, якщо хтось змінює випадаючий список # Qtrs у H1 з 3 на 4, четвертий аргумент OFFSET забезпечить розширення діапазону до чотирьох стовпців.
Гуру електронних таблиць ненавидять OFFSET, оскільки це нестабільна функція. Якщо ви перейдете до абсолютно не пов’язаної комірки та введете число, всі функції OFFSET обчисляться. Навіть якщо ця клітина не має нічого спільного з H1 або B2. Здебільшого Excel дуже обережно витрачає лише час на обчислення комірок, які потрібно обчислити. Але як тільки ви вводите OFFSET, усі комірки OFFSET, а також усе нижче від OFFSET починають обчислювати після кожної зміни на робочому аркуші.
Я судив фінал 2013 року у Нью-Йорку, коли кілька моїх друзів з Австралії вказали на химерне обхідне рішення. У формулі нижче є двокрапка перед функцією INDEX. Зазвичай функція INDEX, показана нижче, повертає 1403 з комірки D2. Але коли ви ставите двокрапку з обох сторін функції INDEX, вона починає повертати адресу комірки D2 замість вмісту D2. Це дико, що це працює.
Чому це важливо? INDEX не є мінливим. Ви отримуєте всю гнучку користь OFFSET без повторних перерахунків, що затягують час.
Вперше я дізнався цю підказку від Дана Майо у Fintega. Дякую Access Analytic за пропозицію цієї функції.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2048 -: INDEX замінить летюче ЗМІСТЕННЯ!
Гей, я підкастую всі мої поради з цієї книги, клацніть на “i” у верхньому правому куті, щоб перейти до списку відтворення!
Добре, OFFSET - це дивовижна функція! OFFSET дозволяє нам вказати верхню ліву кутову комірку, а потім за допомогою змінних визначити звідти, скільки рядків вниз, скільки рядків над, а потім визначити фігуру, добре. Отже, якщо я хочу скласти або зробити середнє значення, скажімо 3/4, ця формула тут погляне на формулу. OFFSET говорить, що ми починаємо з B2, починаючи з Q1, ми опускаємося 0, більше 0, фігура буде 1 рядок заввишки, і це буде H1, іншими словами 3 клітинки в ширину, добре. Отже, у цьому випадку все, що ми дійсно робимо, це зміна кількості стільників, які ми додаємо, ми завжди починаємо з В2, або В3 або В4, коли я копіюю, а потім вирішує, скільки комірок шириною. Добре, OFFSET - це крута штука, вона виконує всі дивовижні функції, але ось клопоти, це нестабільно!Це означає, що навіть якщо щось не входить в ланцюг обчислень, Excel, знадобиться час, щоб перерахувати це, що призведе до уповільнення ситуації, добре.
Ця дивовижна версія INDEX, вірно, як правило, якщо я попрошу INDEX цих 4 клітинок, 3, вона поверне число 1403. Однак, коли я ставлю двокрапку до або після INDEX, відбувається щось зовсім інше, ми поглянемо на цю формулу тут. Отже, індекс 4-х клітинок, яку мені потрібно, я хочу 3-ю, але ви бачите, що тут є:. Отже, ми завжди будемо переходити з B2: E2, і я покажу тобі, якщо ми перейдемо до Формули, Оціни Формулу, добре, так ось тут вона буде обчислювати число 3. І тут, ІНДЕКС із: next до нього, замість того, щоб сказати нам 1403, як зазвичай розраховує INDEX, він поверне $ D2, і тоді AVERAGE зробить середнє з цих 3. Абсолютно дивно, як це працює, і додаткова вигода це не мінливо, добре,і це навіть може бути використано для заміни неймовірно складного OFFSET.
Отже, тут із цим OFFSET ми базуємось на цих значеннях. Якщо я виберу Q2 та Central, добре, ці формули використовують MATCH і COUNTIF, щоб зрозуміти, скільки рядків вниз, скільки стовпців, як високий, як широкий. І тоді OFFSET тут використовує всі ці значення для з’ясування медіани. Ми просто проведемо тест, щоб переконатися, що він працює, так що = MEDIAN того синього діапазону там, краще буде 71, добре, і ми оберемо щось інше тут, Q3 та West, так. Натисніть F2, я просто перетягну це і зменшу його розмір, щоб переписати цю формулу, натисніть Enter, і це працює з OFFSET.
Ну ось, використовуючи ІНДЕКС, я насправді маю двокрапку посередині з ІНДЕКСОМ з лівого боку та ІНДЕКСОМ з правого боку, спостерігайте, як ця річ обчислюється у формулі оцінки. Отже, все починається, буде Evaluate, Evaluate, і ось тут, що INDEX збирається перетворити його на адресу комірки. Отже, H16 - це 1-е, західне, Q3, а праворуч оцінить, ще пару, а потім праворуч це зміниться на I20. Тож прохолодний, прохолодний енергонезалежний, щоб замінити OFFSET за допомогою функції INDEX. Добре, ця порада та багато іншого в цій книзі, клацніть на “i” у верхньому правому куті, щоб придбати книгу.
Добре підсумовуємо: OFFSET, дивовижна, гнучка функція, як тільки ви освоїте, ви зможете робити всілякі речі. Вкажіть на змінну верхню ліву клітинку, вкажіть на діапазон, який має змінну форму, але він нестабільний, і тому вони обчислюють при кожному обчисленні. Excel зазвичай робить розумне обчислення або перераховує клітинки, які потрібно обчислити, але за допомогою OFFSET він завжди отримуватиме обчислення. Замість OFFSET ви можете використовувати INDEX: або: INDEX або навіть INDEX: INDEX, у будь-який час, коли INDEX має двокрапку поруч, він поверне адресу комірки замість значення цієї комірки. І перевага в тому, що INDEX не є мінливою!
Добре, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2048.xlsm
