СМЕЩ() несовершенна
Обсуждая в статье "7 причин полюбить именованные диапазоны" способы создания именованных диапазонов, мы в "пятой причине" говорили о возможности создания ИД, который самостоятельно подстраивается под свои текущие размеры. Решение это достаточно известно и построено на формуле СМЕЩ (OFFSET). Данная формула является медленной или волатильной (volatile), так как она пересчитывается всякий раз, когда вы вводите данные в какую-либо ячейку какой-либо открытой книги Excel. Когда данных в книге мало, то вы это не заметите, однако, если таких формул у вас много и/или вы применяете их к большим диапазонам данных, то вы увидите, как ваша книга начнёт буквально спотыкаться на каждом шагу, реагируя на любые ваши действия чувствительными подвисаниями. Есть достаточно серьёзные причины, по которым эта и подобные ей формулы (например, ДВССЫЛ(), СЕГОДНЯ(), ТДАТА(), СЛУЧМЕЖДУ(), СЛЧИС()) пересчитываются именно так, но мы не будем сейчас в это углубляться. Нас сейчас занимает вопрос, а есть ли способ динамически определить именованный диапазон, не пользуясь волатильными формулами?
Есть такая формула!
Оказывается, это можно сделать при помощи формулы ИНДЕКС (INDEX). Рассмотрим сначала более простой случай, когда ваш диапазон состоит из одного столбца и меняться у него может только количество строк. В этом случае формула, динамически определяющая этот диапазон, будет выглядеть так:
=Single!$A$2:ИНДЕКС( Single!$A:$A; СЧЁТЗ(Single!$A:$A) )
Single!$A$2 - с этой ячейки листа Single начинаются значения нашего динамического диапазона; ":" - обратите внимание на оператор определения диапазона - двоеточие. Слева от двоеточия располагается ссылка на левый верхний угол диапазона, а справа - на нижний правый; ИНДЕКС( Single!$A:$A; <высота> ) - при помощи формулы ИНДЕКС как раз и вычисляется ссылка на нижний правый угол диапазона. Функция ИНДЕКС, вообще говоря, возвращает значение из диапазона, находящееся на пересечении указанной строки и столбца. Когда такой диапазон состоит из одного столбца, то третий параметр (номер столбца) можно не указывать (наш случай). Так вот в качестве диапазона мы указали весь столбец A, а номер строки (тут заменен на <высота>) мы вычисляем при помощи дополнительной формулы, о которой читайте строчкой ниже; СЧЁТЗ( Single!$A:$A ) - это то, что стоит вместо конструкции <высота> в формуле ИНДЕКС. Формула СЧЁТЗ подсчитывает количество непустых ячеек в указанном диапазоне.
Рекомендую располагать ваши диапазоны, начиная с A1. В противном случае в формулу надо будет внести незначительные коррективы. Например, если ваш диапазон будет начинаться в ячейке B2, то формула изменится так:
=Shifted!$B$3:ИНДЕКС( Shifted!$B$3:$B$10000; СЧЁТЗ( Shifted!$B$3:$B$10000) )
Тут имеется в виду, что в диапазоне заведомо не будет больше 10000 строк. В противном случае нужно было бы использовать большее значение (максимально возможное 1048576).
Теперь - более сложный случай, когда диапазон состоит более чем из одного столбца и, соответственно, может изменяться как по высоте (строки), так и по ширине (столбцы):
=Multi!$A$2:ИНДЕКС( Multi!$A:$AA; СЧЁТЗ(Multi!$A:$A); СЧЁТЗ(Multi!$1:$1) )
Multi!$A$2 - аналогично ":" - аналогично ИНДЕКС( Multi!$A:$AA; <высота>; <ширина> ) - тоже самое, что и в предыдущем примере, но добавляется третий параметр для функции ИНДЕКС, так как наш диапазон состоит из нескольких столбцови необходимо указывать, из какого именно столбца возвращать ссылку (параметр <ширина>); СЧЁТЗ( Multi!$A:$A ) - аналогично СЧЁТЗ( Multi!$1:$1 ) - вычисление номера крайнего правого столбца нашего диапазона. Предполагается, что в строке 1 располагаются заголовки нашей таблицы и количество непустых ячеек в указанном векторе равно количеству столбцов в нашем диапазоне.
Выводы
Мы научились определять динамический ИД через функцию рабочего листа ИНДЕКС, отказавшись от волатильной функции СМЕЩ. Это более универсальный способ, так как он хорошо работает как в небольших, так и в громоздких файлах. Также динамические диапазоны можно определять при помощи умных таблиц, однако, бывают ситуации, когда последние применять нельзя. Такие ситуации достаточно часто возникают, например, из-за того, что у пользователя может быть Excel 2003 или более ранний, либо лист надо защитить, а умные таблицы плохо сочетаются с защитой листа. Теперь вы знаете, как это можно сделать лучше.
Файл примера
Читайте также:
| 
0
