Определяем имя листа в EXCEL
history 4 апреля 2013 г.
- Группы статей
- Имя листа, книги, файла
- Действия с листом
Определим имя листа с помощью функции ЯЧЕЙКА() .
Имя листа можно определить с помощью функции ЯЧЕЙКА() , записав формулу (см. файл примера ): =ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(ЯЧЕЙКА("имяфайла"))-ПОИСК("]";ЯЧЕЙКА("имяфайла")))
Если формула вводится в новую книгу, то ее сначала нужно сохранить. После сохранения, возможно, потребуется нажать клавишу F9 (Вкладка Формулы , Группа Вычисление , Пересчет ).
Формула вернет имя листа, содержащего ячейку с вышеуказанной формулой. Если требуется определить имя другого листа, то можно использовать туже функцию ЯЧЕЙКА() , но с аргументом "адрес".
Для этого нужно, чтобы второй аргумент содержал ссылку на ячейку другого листа, имя которого и будет определено. Если формула =ЯЧЕЙКА("адрес";лист2!A1) находится на листе1 в ячейке B1 , то имя листа ( Лист2 ) можно определить по формуле:
В этом случае имя листа не должно состоять только из цифр, т.к. если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах (‘) и формула вернет неправильный результат. Справиться с этим поможет формула =ПОДСТАВИТЬ(C1;"’";"") , которая удалит символ апострофа.
Также предполагается, что имя файла не содержит символа закрывающая квадратная скобка ( ] ).
ВНИМАНИЕ ! Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 ( Формулы/ Вычисления/ Пересчет ).
ПРИМЕНЕНИЕ: При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . В статье Определение имени листа для использования в функции ДВССЫЛ() показано как использовать функцию ЯЧЕЙКА() , чтобы сохранить работоспособность формулы с функцией ДВССЫЛ() .
Формула номера листа и страницы в Excel
Разберем формулы номеров листов и страниц в Excel (как стандартные, так и пользовательские функции) для создания оглавления и удобной навигации по документу.
При создании оглавления отчета, книги или статьи нам важно знать не только название глав и проставить гиперссылки на них, но и их расположение в документе для того, чтобы при чтении нам было удобнее ориентироваться в содержимом документа и проще находить нужную информацию.
Добавить номера листов в оглавление можно вручную, однако в этом случае сам процесс проставления номеров может отнять достаточно большое количество времени. К тому же при изменении номера страницы одной главы поменяются номера и для всех последующих глав, поэтому такой способ нельзя назвать удобным и эффективным.
Рассмотрим варианты, которые позволят нам узнать номера листов и страниц в автоматическом режиме.
Однако прежде всего поймем в чем именно разница между листом и страницей в Excel.
Лист Excel — это таблица, состоящая из ячеек структурированная в виде данных в строках и столбцах, а страница — это представление листа при печати. Другими словами, в зависимости от настроек печати, один лист может содержать несколько страниц, поэтому задачу поиска номеров листов и страниц будем решать по отдельности.
Формула номера листа в Excel
Начиная с версии Excel 2013 добавлена стандартная функция ЛИСТ, которая возвращает номер листа по имеющейся ссылке.
Однако в более ранних версиях Excel стандартных функций, к сожалению, нет, поэтому для этого случая мы воспользуемся возможностями VBA и напишем пользовательскую функцию, которая по имени листа будет возвращать его порядковый номер.
Функция ЛИСТ (начиная с Excel 2013)
Синтаксис и описание функции:
ЛИСТ(значение)
Возвращает номер указанного листа.
- Значение(необязательный аргумент) — название листа или ссылка, для которой нужно узнать номер листа.
В случае если аргумент не указан, то функция по умолчанию возвращает номер листа, где задана функция.
Рассмотрим принципы использования функции на примерах:
При работе с функцией обратите внимание, что формулы =ЛИСТ("Лист2") и =ЛИСТ(A1) (при этом ячейка A1, например, содержит текст «Лист2») могут вернуть разные результаты, так как в первом случае аргумент представлен в виде текста (поиск листа по текстовому названию), а во втором — в виде ссылки (поиск листа по ссылающейся ячейке).
Номер листа на VBA
Для создания пользовательской функции перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), далее создаем новый модуль и добавляем в него следующий код: