Загрузите образец книги
В других статьях мы обсуждали, что существуют такие функции, как OFFSET и INDIRECT, которые являются нестабильными. Если вы начнете использовать многие из них в электронной таблице или у вас будет много ячеек, зависящих от изменчивой функции, вы можете заставить свой компьютер тратить заметное время на пересчеты каждый раз, когда вы пытаетесь изменить ячейку. Вместо того чтобы расстраиваться из-за того, что ваш компьютер недостаточно быстр, в этой статье будут рассмотрены альтернативные способы решения типичных ситуаций, в которых люди используют СМЕЩЕНИЕ и КОСВЕННОСТЬ.
Замена OFFSET для создания динамического списка
После изучения функции СМЕЩЕНИЕ возникает распространенное заблуждение, что это единственный способ вернуть результат с динамическим размером, используя последнюю пару аргументов. Давайте посмотрим на список в столбце A, где наш пользователь может позже добавить дополнительные элементы.
Чтобы создать раскрывающийся список в ячейке C2, вы можете определить именованный диапазон с изменчивой формулой, например
= СМЕЩЕНИЕ ($ A $ 2; 0; 0; COUNTA ($ A: $ A) -1; 1)
При текущей настройке это обязательно вернет ссылку на диапазон A2: A5. Однако есть другой способ использования энергонезависимого ИНДЕКСА. Для этого подумайте о том, чтобы написать ссылку на диапазон, идущий от A2 до A5. Когда вы пишете «A2: A5», не думайте об этом как об отдельном фрагменте данных, а скорее как о «Начальная точка» и «Конечная точка», разделенные двоеточием (например, Начальная точка: Конечная точка). В формуле и начальная точка, и конечная точка могут быть результатами других функций.
Вот формула, которую мы будем использовать для создания динамического диапазона с помощью функции ИНДЕКС:
= $ A $ 2: ИНДЕКС ($ A: $ A, COUNTA ($ A: $ A))
Обратите внимание, что мы заявили, что начальная точка для этого диапазона всегда будет A2. С другой стороны от двоеточия мы используем ИНДЕКС, чтобы определить, где должна быть конечная точка. COUNTA определит, что есть 5 ячеек с данными в столбце A, и поэтому наш ИНДЕКС создаст ссылку на A5. Таким образом, формула оценивается так:
= $ A $ 2: ИНДЕКС ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: ИНДЕКС ($ A: $ A, 5) = $ A $ 2: $ A5
Используя эту технику, вы можете динамически создавать ссылку на любой список или даже на двумерную таблицу с помощью функции ИНДЕКС. В электронной таблице с множеством функций СМЕЩЕНИЕ замена СМЕЩЕНИЯ на ИНДЕКС позволит вашему компьютеру начать работать намного быстрее.
Замена INDIRECT для имен листов
Функция ДВССЫЛ часто вызывается, когда книги были разработаны с данными, разбросанными по нескольким листам. Если вы не можете собрать все данные на одном листе, но не хотите использовать изменчивую функцию, вы можете использовать CHOOSE.
Рассмотрим следующий макет, где у нас есть данные о продажах на 3 разных листах. На нашем сводном листе мы выбрали, за какой квартал хотим просмотреть данные.
Наша формула в B3:
= ВЫБРАТЬ (МАТЧ (B2; D2: D4; 0); Осень! A2; Зима! A2; Весна! A2)
В этой формуле функция ПОИСКПОЗ будет определять, какую область мы хотим вернуть. Затем это сообщает функции CHOOSE, какой из следующих диапазонов вернуть в качестве результата.
Вы также можете использовать функцию ВЫБРАТЬ, чтобы вернуть больший диапазон. В этом примере у нас есть таблица данных о продажах на каждом из трех рабочих листов.
Вместо того, чтобы писать КОСВЕННУЮ функцию для построения имени листа, вы можете позволить ВЫБОРУ определять, по какой таблице выполнять поиск. В моем примере я уже назвал три таблицы tbFall, tbWinter и tbSpring. Формула в B4:
= ВПР (B3; ВЫБРАТЬ (ПОИСКПОЗ (B2; D2: D4; 0); tbFall; tbWinter; tbSpring); 2; 0)
В этой формуле MATCH будет определять, что мы хотим, чтобы 2nd пункт из нашего списка. Затем CHOOSE возьмет эти 2 и вернет ссылку на tbWinter. Наконец, наша ВПР сможет завершить поиск в данной таблице и обнаружит, что общие продажи банана зимой составили 6000 долларов.
= ВПР (B3, ВЫБРАТЬ (ПОИСКПОЗ (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = ВПР (B3, ВЫБРАТЬ (2, tbFall, tbWinter, tbSpring), 2, 0) = ВПР (B3, tbWinter, 2, 0) = 6000
Этот метод ограничен тем фактом, что вы должны заполнить функцию CHOOSE всеми областями, из которых вы, возможно, захотите получить значение, но он дает вам преимущество в том, что вы избегаете изменчивой формулы. В зависимости от того, сколько вычислений вам нужно выполнить, эта способность может оказаться весьма полезной.
