Розберемо формули номерів аркушів і сторінок в Excel (як стандартні, так і призначені для користувача функції) для створення змісту і зручної навігації по документу.
При створенні змісту звіту, книги або статті нам важливо знати не тільки назва глав і проставити гіперпосилання на них, а й їх розташування в документі для того, щоб при читанні нам було зручніше орієнтуватися у вмісті документа і простіше знаходити потрібну інформацію.
Додати номери аркушів в зміст можна вручну, однак в цьому випадку сам процес проставляння номерів може відняти досить багато часу. До того ж при зміні номера сторінки однієї глави поміняються номера і для всіх наступних глав, тому такий спосіб не можна назвати зручним і ефективним.
Розглянемо варіанти, які дозволять нам дізнатися номери аркушів і сторінок в автоматичному режимі.
Однак перш за все зрозуміємо в чому саме різниця між листом і сторінкою в Excel.
Лист Excel - це таблиця, що складається з осередків структурована у вигляді даних в рядках і стовпцях, а сторінка - це уявлення листа при друку. Іншими словами, в залежності від налаштувань друку, один лист може містити кілька сторінок. тому завдання пошуку номерів аркушів і сторінок будемо вирішувати окремо.
Формула номера листа в Excel
Синтаксис і опис функції:
ЛИСТ (значення)
Повертає номер вказаного листа.
У разі якщо аргумент не вказано, то функція за замовчуванням повертає номер листа, де задана функція.
Розглянемо принципи використання функції на прикладах:
При роботі з функцією зверніть увагу, що формули = ЛИСТ ( "Аркуш2") і = ЛИСТ (A1) (при цьому осередок A1, наприклад, містить текст «Аркуш2») можуть повернути різні результати, так як в першому випадку аргумент представлений у вигляді тексту (пошук листа по текстовому назвою), а в другому - у вигляді посилання (пошук листа по посилається осередку).
Номер листа на VBA
Function PageNumber (SheetName1 As String. SheetName2 As String) As Integer 'функція PageNumber (аргументи SheetName1 і SheetName2 у вигляді тексту) повертає цілі значення
Dim FirstPage As Integer. LastPage As Integer 'ініціалізація змінних
Application. Volatile True 'автоматичний перерахунок
PageNumber = 0 'повертається значення = 0
FirstPage = Worksheets (SheetName1). Index 'отримання номера початкового листа
LastPage = Worksheets (SheetName2). Index 'отримання номера кінцевого листа
For i = FirstPage To LastPage - 1 'цикл по листам
PageNumber = PageNumber + Sheets (i). PageSetup. Pages. Count 'додаток кількості сторінок поточного листа до поверненню значенням
Таким чином, отримана функція шукає кількість сторінок між двома листами, які задаються в якості аргументів.
Повертаємося в Excel і введемо нову формулу для підрахунку номерів сторінок:
При цьому на аркушах «Частина 3» і «Частина 4» (з попереднього прикладу) ми додатково додали дані, щоб лист став включати в себе кілька сторінок.
На жаль, мінусом цього варіанту визначення номера сторінки є швидкістю роботи функції.
При виконанні операції PageSetup.Pages.Count Excel для кожної сторінки звертається до налаштувань друку принтера, що на книгах великого розміру призводить до довгого часу розрахунку функції.
особливості розрахунку
Оскільки функції SheetNumber і PageNumber залежать тільки від текстових змінних, то при зміні кількості листів або сторінок в книзі вони автоматично не перераховуються.
Щоб частково уникнути проблеми перерахунку ми додали в код функції (3 рядок) команду Application.Volatile True. яка перераховує результат функції при зміні вмісту комірок (аналог повного перерахунок формул після натискання F9).
Якщо ж все одно функція істотно сповільнює роботу, то можна виключити дану рядок з коду, проте в цьому випадку не забудьте переконатися, що в підсумковому варіанті документа формули перераховані.
Удачі вам і до швидких зустрічей на сторінках блогу Tutorexcel.ru!