Именованные диапазоны изменяются случайным образом в Excel 2010

Question

Я пытаюсь создать несколько именованных диапазонов в Excel, которые расширяются с помощью функции смещения, основанной на количестве непустых ячеек в столбце, с общей формулой:

=OFFSET($E1,0,0,COUNTA($E1:$E99999),1)

Менеджер имен принимает формулу, но когда я возвращаюсь к ней, кажется, что некоторые числа менялись случайным образом. Названные диапазоны не работают как исключение. Смотрите скриншоты ниже (до и после).

Это похоже на ошибку в Excel, но я не могу найти упоминания об этом в Google. Я использую Excel 2010 (14.0.7128.5000) в Windows 7.

Answer 1

Я просто столкнулся с той же проблемой, когда пытался ввести адреса для именованных диапазонов, а не выделять диапазон для его выбора.

Кажется, что если вы введете относительный адрес в формате =A1:B2 для именованного диапазона, Excel в любой момент будет рассматривать этот диапазон как относительный к текущей ячейке. Если вы переместите курсор в другую ячейку, а затем снова проверьте Диспетчер имен, вы увидите другой диапазон.

Чтобы это исправить, вам нужно использовать полностью абсолютные адреса в формате =$A$1:$B$2 .

В примере в вопросе ссылки на столбцы уже являются абсолютными, но ссылки на строки относительны. Следующее должно работать:

=OFFSET($E$1,0,0,COUNTA($E$1:$E$99999),1)

Answer 2

Я видел, как Excel делал это раньше, и я никогда не уверен, почему. Вы можете решить эту проблему, используя нотацию E:E для ссылки на весь столбец.

=OFFSET($E1,0,0,COUNTA($E:$E),1)

Если вам нужно что-то вроде примера, показанного на снимках экрана, вы можете использовать следующее.

=OFFSET($E$3,0,0,COUNTA($E:$E)-COUNTA($E$1:$E$2),1)