Трюк №40. Использование одного имени для диапазонов на разных рабочих листах Excel

Иногда бывает удобно использовать одно и то же имя для данных на одном и том же месте на разных рабочих листах одной рабочей книги. Для этого в Excel нужно сделать несколько дополнительных шагов.

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

Предположим, у вас есть рабочая книга с тремя рабочими листами. У этих трех листов простые имена: Sheetl, Sheet2 и Sheets. Вы хотите создать именованный диапазон с именем MyRange (можно использовать любое допустимое имя), который будет относиться к диапазону А1:А10 на листе Sheetl, когда активен лист Sheetl, A1 :А10 на листе Sheet2, когда активен лист Sheet2, и А1:А10 на листе Sheet3, когда активен лист Sheets.

Для этого активируйте лист Sheetl, выделите диапазон А1:А10 и щелкните в поле имени, как описано в разделе «Трюк №39. Адресация данных по имени». Введите Sheetl!MyRange и нажмите клавишу Enter. To же самое сделайте на листах Sheet2 и Sheets, введя Sheet2!MyRange и Sheets!My Range соответственно.

Теперь активируйте любой лист и щелкните стрелку раскрывающего списка поля имени. Вы должны увидеть имя MyRange только один раз. Выберите его и вы попадете непосредственно в диапазон А1:А10. Теперь активируйте другой лист и сделайте то же самое. Вы всегда будете попадать в диапазон А1:А10 на активном листе.

Этот трюк работает благодаря тому, что перед именем диапазона вы поставили имя листа и восклицательный знак. Выбрав команду Вставка → Имя → Присвоить (Insert → Name → Define), вы увидите только одно имя — имя, относящееся к активному в данный момент листу.

Если в имени рабочего листа есть пробелы, нельзя просто сослаться на диапазон А1:А10 на листе Sheet1 как Sheet1 IMyRange. Нужно назвать его ‘Sheet1’!MyRange, поместив слово Sheetl в одиночные кавычки, апострофы. В действительности, апострофы можно использовать и с именем рабочего листа, не содержащим пробелы, поэтому лучше вставлять их при каждой ссылке на рабочие листы.

В именованных диапазонах можно использовать и относительные ссылки. По умолчанию именованные диапазоны — абсолютные, но вы можете исправить это. Сделайте следующее:
Выделите ячейку All на любом рабочем листе и выберите команду Вставка → Имя → Присвоить (Name → Insert → Define). В поле Имя (Names in workbook) введите МуNumbers. В поле Формула (Refers to) введите =А$1:А$10, щелкните на кнопке Добавить (Add ), затем щелкните на кнопке ОК. Теперь в ячейку А1 введите число 1. Выделите ячейку А1, подведите указатель к маркеру заполнения и нажмите левую кнопку мыши. Удерживая клавишу Ctrl, перетащите маркер к ячейке А10. Так вы создадите список с арифметической прогрессией с шагом 1.

В ячейку В1 введите 1 и перетащите маркер на ячейку В10, в этот раз не удерживая клавишу Ctrl. В ячейке АИ введите следующую формулу: =SUM(MyNumbers), в русской версии Excel =CYMM(MyNumbers). В ячейке В11 введите следующую формулу: =SUM(MyNumbers), в русской версии Excel =CYMM(MyNumbers).

Вы должны получить ответы 55 и 10 соответственно, так как ячейка АИ была активной, когда вы использовали команду Вставка → Имя → Присвоить (Name → Insert → Define) и присвоили имя диапазону А$1:А$10, то есть диапазону с относительной ссылкой на столбец и абсолютной ссылкой на строку.
[stextbox id=»warning»]Чтобы создать абсолютную ссылку на диапазон, воспользуйтесь знаком доллара ($).[/stextbox]
Используемое в формуле имя MyNumbers всегда обозначает 10 ячеек непосредственно над формулой. Если вы вставите формулу =cyMM(MyNumbers) (=SUM(MyNumbers)) в ячейку АИ на другом рабочем листе, она все так же будет обозначать ячейки А1:А10 на листе, который был активен при создании имени диапазона.

Предположим, вы хотите упростить суммирование значений в десяти ячейках. Выделите ячейку АН на любом листе. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Name) введите MySum. Затем в поле Формула (Refers to) введите следующую формулу: =SUM(A$1:A$10), в русской версии Excel =СУММ(А$1:А$10). Щелкните на кнопке Добавить (Add), затем щелкните на кнопке ОК. Теперь в ячейке А1 введите число 1. Выделите ячейку А1, подведите указатель мыши к маркеру заполнения и нажмите левую кнопку мыши. Удерживая клавишу Ctrl, перетащите маркер к ячейке А10. После этого в ячейке В1 введите 1 и перетащите маркер к ячейке В10, не удерживая клавишу Ctrl. В ячейке АИ введите следующую формулу: =MySum, в ячейке В11 также введите формулу =MySum. Вы получите такой же результат, что и в предыдущем примере, не используя функцию СУММ (SUM). Сочетание абсолютных и относительных ссылок и вложение нескольких функций друг в друга может быть очень полезным и сэкономит много времени.