Існує гнучка функція, яка називається OFFSET. Він може вказувати на діапазон різного розміру, який обчислюється на льоту. На зображенні нижче, якщо хтось змінює випадаючий список # Qtrs у H1 з 3 на 4, четвертий аргумент OFFSET забезпечить розширення діапазону до чотирьох стовпців.
![](https://cdn.wiki-base.com/1232659/excel_2020_use_a2_index_as_a_non-volatile_offset_-_excel_tips.png.webp)
Гуру електронних таблиць ненавидять OFFSET, оскільки це нестабільна функція. Якщо ви перейдете до абсолютно не пов’язаної комірки і введете число, всі функції OFFSET обчисляться - навіть якщо ця комірка не має нічого спільного з H1 або B2. Здебільшого Excel дуже обережно витрачає час лише на обчислення клітинок, які потрібно обчислити. Але як тільки ви вводите OFFSET, усі комірки OFFSET, а також усе нижче від OFFSET починають обчислювати після кожної зміни на робочому аркуші.
У формулі нижче є двокрапка перед функцією INDEX. Зазвичай функція INDEX, показана нижче, повертає 1403 з комірки D2. Але коли ви ставите двокрапку з обох сторін функції INDEX, вона починає повертати адресу комірки D2 замість вмісту D2. Диво, що це працює.
![](https://cdn.wiki-base.com/1232659/excel_2020_use_a2_index_as_a_non-volatile_offset_-_excel_tips_2.png.webp)
Чому це важливо? INDEX не є мінливим. Ви отримуєте всю гнучку користь OFFSET без повторних перерахунків, що затягують час.
Вперше я дізнався цю підказку від Дана Майо у Fintega. Дякую Access Analytic за пропозицію цієї функції.