Excel 2010: создание именованного диапазона без ссылки на лист в формуле

Question

В Name Manager я создал именованный диапазон с этой ссылкой:

=COUNTIF($A$4:A4, Tbl_InventoryMain[Barcode])

Excel автоматически вставляет текущее имя листа до $A$4:A4 . Мне нужно, чтобы это оставалось $A$4:A4 , чтобы использовать эту же ссылку на других листах. Я не хочу создавать именованный диапазон для каждого листа.

Как я могу получить эту именованную ссылку для работы на любом листе?

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

Answer 1

Мой ответ сфокусирован на создании ссылки на $A$4:A4 которая работает на всех листах, так как это часть ссылки, к которой Excel добавляет нежелательное имя листа. (Я полагаю, у вас нет проблем со ссылкой на таблицу.)

Вы можете решить эту проблему, используя функцию INDIRECT следующим образом:

1. На любом листе выберите ячейку A1

2. Создайте именованный диапазон со ссылкой на:

   =INDIRECT("$A$4"):INDIRECT("R[3]C[0]",FALSE)
   

По вашему вопросу вторая ячейка в вашей ссылке на $A$4:A4 является относительной, и ссылка на это имя построена соответственно. Поэтому, если вы используете это имя в ячейке A1 оно вернет диапазон $A$4:A4 . Если вы ссылаетесь на имя из B1 имя будет правильно возвращать диапазон $A$4:B4 .

Как это устроено

Первая половина формулы проста. INDIRECT("$A$4") всегда будет ссылаться на ячейку A4 на текущем листе.

Вторая половина немного сложнее. INDIRECT("R[3]C[0]",FALSE) относится к «3 строкам внизу в одном столбце относительно текущей ячейки». Если вы находитесь в ячейке A1 это ячейка A4 . Аргумент FALSE указывает функции INDIRECT интерпретировать текст как ссылку в стиле R1C1 вместо ссылки по умолчанию в стиле A1.

Завершенная формула

Ваша окончательная ссылка на имя будет выглядеть примерно так:

=COUNTIF(INDIRECT("$A$4"):INDIRECT("R[3]C[0]",FALSE), Tbl_InventoryMain[Barcode])