Формула номера листа и страницы в Excel

Разберем формулы номеров листов и страниц в Excel (как стандартные, так и пользовательские функции) для создания оглавления и удобной навигации по документу.

При создании оглавления отчета, книги или статьи нам важно знать не только название глав и проставить гиперссылки на них, но и их расположение в документе для того, чтобы при чтении нам было удобнее ориентироваться в содержимом документа и проще находить нужную информацию.
Добавить номера листов в оглавление можно вручную, однако в этом случае сам процесс проставления номеров может отнять достаточно большое количество времени. К тому же при изменении номера страницы одной главы поменяются номера и для всех последующих глав, поэтому такой способ нельзя назвать удобным и эффективным.

Рассмотрим варианты, которые позволят нам узнать номера листов и страниц в автоматическом режиме.
Однако прежде всего поймем в чем именно разница между листом и страницей в Excel.
Лист Excel — это таблица, состоящая из ячеек структурированная в виде данных в строках и столбцах, а страница — это представление листа при печати. Другими словами, в зависимости от настроек печати, один лист может содержать несколько страниц, поэтому задачу поиска номеров листов и страниц будем решать по отдельности.

Формула номера листа в Excel

Начиная с версии Excel 2013 добавлена стандартная функция ЛИСТ, которая возвращает номер листа по имеющейся ссылке.
Однако в более ранних версиях Excel стандартных функций, к сожалению, нет, поэтому для этого случая мы воспользуемся возможностями VBA и напишем пользовательскую функцию, которая по имени листа будет возвращать его порядковый номер.

Функция ЛИСТ (начиная с Excel 2013)

Синтаксис и описание функции:

ЛИСТ(значение)
Возвращает номер указанного листа.

  • Значение (необязательный аргумент) — название листа или ссылка, для которой нужно узнать номер листа.

В случае если аргумент не указан, то функция по умолчанию возвращает номер листа, где задана функция.
Рассмотрим принципы использования функции на примерах:

Пример функции ЛИСТ
При работе с функцией обратите внимание, что формулы =ЛИСТ("Лист2") и =ЛИСТ(A1) (при этом ячейка A1, например, содержит текст «Лист2») могут вернуть разные результаты, так как в первом случае аргумент представлен в виде текста (поиск листа по текстовому названию), а во втором — в виде ссылки (поиск листа по ссылающейся ячейке).

Номер листа на VBA

Для создания пользовательской функции перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), далее создаем новый модуль и добавляем в него следующий код:

Теперь мы можем использовать данную функцию удобным нам способом — либо непосредственно ввести формулу в любую ячейку, либо же воспользоваться мастером функций (найти функцию можно в категории Определенные пользователем):

Пользовательская функция поиска листа

Номер страницы на VBA

Вставить номера страниц в Excel можно с помощью колонтитулов, но если мы хотим добавить номер страницы в ячейку на листе, то такой способ не подходит. Воспользуемся параметром листа PageSetup.Pages.Count, который определяет количество страниц заданного листа, отправляемых на печать.
Алгоритм поиска номера страницы следующий — для всех листов, находящихся между двумя заданными листами, суммируем количество находящихся на них страниц.
Возвращаемся в редактор Visual Basic и добавляем в модуль новую функцию:

Таким образом, полученная функция ищет количество страниц между двумя листами, которые задаются в качестве аргументов.
Возвращаемся в Excel и введем новую формулу для подсчета номеров страниц:

Пользовательская функция поиска страницы
При этом на листах «Часть 3» и «Часть 4» (из предыдущего примера) мы дополнительно добавили данные, чтобы лист стал включать в себя несколько страниц.

К сожалению, минусом данного варианта определения номера страницы является скоростью работы функции.
При выполнении операции PageSetup.Pages.Count Excel для каждой страницы обращается к настройкам печати принтера, что на книгах большого размера приводит к долгому времени расчёта функции.

Особенности расчета

Поскольку функции SheetNumber и PageNumber зависят только от текстовых переменных, то при изменении количества листов или страниц в книге они автоматически не пересчитываются.
Чтобы частично избежать проблемы пересчета мы добавили в код функции (3 строка) команду Application.Volatile True, которая пересчитывает результат функции при изменении содержимого ячеек (аналог полного пересчет формул по нажатию F9).
Если же все равно функция существенно замедляет работу, то можно исключить данную строку из кода, однако в этом случае не забудьте убедиться, что в итоговом варианте документа формулы пересчитаны.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Добавить комментарий

Ваш e-mail не будет опубликован.

Читайте ранее:
Функция СМЕЩ в Excel

Рассмотрим примеры использования функции СМЕЩ в Excel, которая возвращает ссылку смещенную на заданное количество строк и столбцов от исходной ссылки.

Закрыть