Завантажте зразок робочої книги
Ми обговорювали в інших статтях про те, як існують такі функції, як OFFSET та INDIRECT, які є мінливими. Якщо ви починаєте використовувати багато з них у таблиці або у вас багато клітинок залежать від мінливої функції, ви можете змусити комп’ютер витрачати помітний час на перерахунок щоразу, коли ви намагаєтесь змінити клітинку. Замість того, щоб розчаровуватися в тому, що ваш комп’ютер недостатньо швидкий, ця стаття досліджуватиме альтернативні способи вирішення типових ситуацій, коли люди використовують OFFSET та INDIRECT.
Заміна OFFSET для створення динамічного списку
Дізнавшись про функцію OFFSET, поширена помилка, що це єдиний спосіб повернути результат з динамічним розміром, використовуючи останні пару аргументів. Давайте розглянемо список у стовпці А, де наш користувач може пізніше вирішити додати додаткові елементи.
Щоб створити спадне меню у клітинці С2, ви можете визначити Іменований діапазон із летючою формулою, наприклад
= ЗМІЩЕННЯ ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
З поточною установкою це, безумовно, поверне посилання на діапазон A2: A5. Однак існує інший спосіб використання енергонезалежного ІНДЕКСУ. Для цього подумайте над тим, щоб ми написали посилання на діапазон від А2 до А5. Коли ви пишете “A2: A5”, не думайте про це як про окрему частину даних, а скоріше як про “StartingPoint” та “EndingPoint”, розділених двокрапкою (наприклад, StartingPoint: EndingPoint). У формулі і StartingPoint, і EndingPoint можуть бути результатами інших функцій.
Ось формула, яку ми будемо використовувати для створення динамічного діапазону за допомогою функції INDEX:
= $ A $ 2: ІНДЕКС ($ A: $ A, COUNTA ($ A: $ A))
Зауважте, що ми заявили, що початковою точкою для цього діапазону завжди буде А2. З іншого боку товстої кишки ми використовуємо 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, ВИБІРИ (МАТЧ (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
У цій формулі MATCH збирається визначити, що ми хочемо 2nd пункт з нашого списку. Потім CHOOSE візьме це 2 і поверне посилання на tbWinter. Нарешті, наш VLOOKUP зможе завершити пошук у наведеній таблиці, і він виявить, що загальний обсяг продажів бананів взимку склав 6000 доларів.
= VLOOKUP (B3, CHOOSE (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
Ця техніка обмежена тим, що ви повинні заповнити функцію ВИБІР усіма областями, з яких ви можете отримати значення, але це дає вам перевагу уникнути нестабільної формули. Залежно від того, скільки обчислень вам потрібно виконати, ця здатність може виявитися досить цінною.
