Зміст в екселя. Формула номера листа і сторінки в Excel. Номер сторінки на VBA

Якщо у вашій робочій книзі Excel число аркушів перевалило за другий десяток, то навігація по листам починає стає проблемою. Одним з найкрасивіших способів її рішення є створення листа-змісту з гіперпосиланнями, провідними на відповідні листи книги:

Є кілька способів реалізувати подібне.

Відео

Місце в документі

Гіперпосилання (HYPERLINK) Shiftі / або Ctrl

Зміст.

Відкрийте Диспетчер Імен на вкладці Зміст. У полі Діапазон (Reference) введіть ось таку формулу:

ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)
\u003d GET.WORKBOOK (1)

Тепер у змінній Зміст ІНДЕКС (INDEX)

функція СТРОКА (ROW)

ЗАМІНИТИ (SUBST) і ШУКАТИ (FIND) Диспетчер імен з вкладки Змісті змінимо його формулу:

Зміст Ctrl + Alt + F9

ЗАМІНИТИ (ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1); 1; ШУКАТИ ( «]»; ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)); »») & Т (ТДАТУ ())

функція ТДАТУ (NOW) Т ТДАТУ

Щоб приховати помилок # ПОСИЛАННЯ (#REF) ІНДЕКС ЕСЛІОШІБКА (IFERROR)

Гіперпосилання (HYPERLINK)

Спосіб 3. Макрос

способу 2

Alt + F11 Insert - Module

Sub SheetList () Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets Set cell \u003d Worksheets (1) .Cells (sheet.Index, 1) .Worksheets (1) .Hyperlinks.Add anchor: \u003d cell , Address: \u003d "", SubAddress: \u003d "" "& sheet.Name &" "" & "! A1" cell.Formula \u003d sheet.Name Next End With End Sub

Закрийте редактор Visual Basic і поверніться в Excel. Додайте в книгу чистий аркуш і помістіть його на перше місце. потім натисніть Alt + F8 або відкрийте меню SheetList

способі 1.

Посилання по темі

• Що таке макрос, як його створити, куди копіювати текст макросу, як запустити макрос?
• Автоматичне створення змісту книги однією кнопкою (надбудова PLEX)
• Відправка листів за допомогою функції гіперпосилання
• Швидкий перехід між листами книги Excel

Спосіб 1. Створювані вручну гіперпосилання

Вставте в книгу порожній лист і додайте на нього гіперпосилання на потрібні вам листи, використовуючи команду Вставка - Гіперпосилання (Insert - Hyperlink). У вікні, потрібно вибрати зліва опцію Місце в документіі задати зовнішнє текстове відображення і адреса комірки, куди приведе посилання:

Для зручності можна створити також і зворотні посилання на всіх аркушах вашої книги, які будуть вести назад в зміст. Щоб не займатися ручним створенням гіперпосилань і копіюванням їх потім на кожен лист, краще використовувати інший метод - функцію Гіперпосилання (HYPERLINK). Виділяємо всі листи в книзі, куди хочемо додати зворотне посилання (для масового виділення листів можна використовувати клавіші Shiftі / або Ctrl) І в будь-яку відповідну клітинку вводимо функцію такого вигляду:

Ця функція створить в поточному осередку на всіх виділених листах гіперпосилання з текстом «Назад в зміст», клацання по якій буде повертати користувача до листу Зміст.

Спосіб 2. Динамічне зміст за допомогою формул

Це хоч і злегка екзотичний, але вельми красивий і зручний спосіб створення автоматичного листа змісту вашої книги. Екзотичний - тому що в ньому використовується недокументированная XLM-функція ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (GET.WORKBOOK), Залишена розробниками для сумісності зі старими версіями Excel. Ця функція вивантажує список всіх листів поточної книги в задану змінну, з якої ми потім можемо їх витягнути і використовувати в нашому змісті.

Відкрийте Диспетчер Імен на вкладці Формули (Formulas - Name Manager)і створіть новий іменований діапазон з ім'ям, припустимо, Зміст. У полі Діапазон (Reference) введіть ось таку формулу:

ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)
\u003d GET.WORKBOOK (1)

Тепер у змінній Змістмістяться наші шукані імена. Щоб витягти їх звідти на лист, можна скористатися функцією ІНДЕКС (INDEX), Яка «висмикує» елементи з масиву по їх номеру:

функція СТРОКА (ROW) видає номер поточного рядка і, в даному випадку, потрібна тільки для того, щоб вручну не створювати окремий стовпець з порядковими номерами видобутих елементів (1,2,3 ...). Таким чином, в осередку А1 у нас вийде ім'я першого листа, в А2 - ім'я другого і т.д.

Не погано. Однак, як можна помітити, функція видає не тільки ім'я листа, але і ім'я книги, яке нам не потрібно. Щоб його прибрати, скористаємося функціями ЗАМІНИТИ (SUBST) і ШУКАТИ (FIND), Які знайдуть символ закриває квадратної дужки (]) і замінять весь текст до цього символу включно на порожній рядок ( ""). Відкриємо ще раз Диспетчер імен з вкладки Формули (Formulas - Name Manager), Двічі клацнувши відкриємо створений діапазон Змісті змінимо його формулу:

ЗАМІНИТИ (ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1); 1; ШУКАТИ ( «]»; ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)); »»)
\u003d SUBST (GET.WORKBOOK (1); 1; FIND ( «]»; GET.WORKBOOK (1)); »»)

Тепер наш список листів буде виглядати значно краще:

Невелика побічна трудність полягає в тому, що наша формула в іменованому діапазоні Змістбуде перераховуватися тільки при введенні, або при примусовому перерахунку книги натисканням на клавіші Ctrl + Alt + F9. Щоб обійти цей неприємний момент, додамо до нашої формулою невеликий «хвіст»:

ЗАМІНИТИ (ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1); 1; ШУКАТИ ( «]»; ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)); »») & Т (ТДАТУ ())\u003d SUBST (GET.WORKBOOK (1); 1; FIND ( «]»; GET.WORKBOOK (1)); »») & T (NOW ())

функція ТДАТУ (NOW) видає поточну дату (З часом), а функція Т перетворює цю дату в порожню текстову рядок, яка потім приклеюється до нашого імені листа за допомогою оператора склейки (&). Тобто ім'я листа, фактично, не змінюється, але оскільки функція ТДАТУ перераховується і видає новий час і дату при будь-якій зміні листа, то і решта нашої формули змушена буде заново перерахувати теж і - як наслідок - імена аркушів будуть оновлюватися постійно.

Щоб приховати помилок # ПОСИЛАННЯ (#REF), Які будуть з'являтися, якщо скопіювати нашу формулу з функцією ІНДЕКСна велика кількість осередків, ніж у нас є аркушів, можна використовувати функцію ЕСЛІОШІБКА (IFERROR), Яка перехоплює будь-які помилки і замінює їх на порожній рядок ( ""):

І, нарешті, для додавання до імен листів «живих» гіперпосилань для швидкої навігації, можна використовувати все ту ж функцію Гіперпосилання (HYPERLINK), Яка буде формувати адреса для переходу з імені листа:

Спосіб 3. Макрос

І, нарешті, для створення змісту можна використовувати і нескладний макрос. Правда, запускати його доведеться кожного разу при зміні структури книги - на відміну від способу 2, Макрос їх сам не відстежує.

Відкрийте редактор Visual Basic, натиснувши Alt + F11 або вибравши (в старих версіях Excel) в меню Сервіс - Макрос - Редактор Visual Basic(Tools - Macro - Visual Basic Editor). У вікні редактора створіть новий порожній модуль (меню Insert - Module ) І скопіюйте туди текст цього макросу:

Sub SheetList ()
Dim sheet As Worksheet
Dim cell As Range
With ActiveWorkbook
For Each sheet In ActiveWorkbook.Worksheets
Set cell \u003d Worksheets (1) .Cells (sheet.Index, 1)
.Worksheets (1) .Hyperlinks.Add anchor: \u003d cell, Address: \u003d »», SubAddress: \u003d » '» & sheet.Name & «'» & «! A1»
cell.Formula \u003d sheet.Name
Next
End With
End Sub Закрийте редактор Visual Basic і поверніться в Excel. Додайте в книгу чистий аркуш і помістіть його на перше місце. потім натисніть Alt + F8 або відкрийте меню Сервіс - Макрос - Макроси (Tools - Macro - Macros). Знайдіть там створений макрос SheetList і запустіть його на виконання. Макрос створить на першому аркуші книги список гіперпосилань з назвами листів. Клацання по будь-якій з них перемістить Вас на потрібний лист.

Для зручності можна створити також і зворотні посилання на всіх аркушах вашої книги, які будуть вести назад в зміст, як це було описано в способі 1.

Спосіб мій. Мій варіант

т
Назва листа - \u003d ЕСЛІОШІБКА (ЗАМІНИТИ (ІНДЕКС (Зміст; СТРОКА () - 3); 1; ШУКАТИ ( «]»; ІНДЕКС (Зміст; СТРОКА () - 3)); »»); »»)

Дата - \u003d ЕСЛІОШІБКА (ЯКЩО (ДВССИЛ ( « '» & B4 & »'» & »! A1") \u003d 0; »»; ДВССИЛ ( « '» & B4 & »'» & »! A1")); »»)

Найменування - \u003d ДВССИЛ ( « '» & B4 & »'» & »! A3")

ЗП - \u003d ДВССИЛ ( « '» & B4 & »'» & »! E5")

податок на ЗП - \u003d ДВССИЛ ( « '» & B4 & »'» & »! E6")

амортизація - \u003d ДВССИЛ ( « '» & B4 & »'» & »! E7")

матеріали - \u003d ДВССИЛ ( « '» & B4 & »'» & »! E8")
доп матеріали - \u003d ДВССИЛ ( « '» & B4 & »'» & »! E9")

ДВССИЛ ( « '» & B4 & »'» & »! E10")

ДВССИЛ ( « '» & B4 & »'» & »! E11") \u003d ДВССИЛ ( « '» & B4 & »'» & »! E12") \u003d ДВССИЛ ( « '» & B4 & »'» & »! E13") \u003d ДВССИЛ ( « '» & B4 & »'» & »! E18") \u003d ДВССИЛ ( « '» & B4 & »'» & »! E19")

програма Microsoft Excel зручна для складання таблиць і проведення розрахунків. Робоча область - це безліч осередків, які можна заповнювати даними. Згодом - форматувати, використовувати для побудови графіків, діаграм, зведених звітів.

Робота в Ексель з таблицями для початківців користувачів може на перший погляд здатися складною. Вона істотно відрізняється від принципів побудови таблиць в Word. Але почнемо ми з малого: з створення та форматування таблиці. І в кінці статті ви вже будете розуміти, що кращого інструменту для створення таблиць, ніж Excel не придумаєш.

Як створити таблицю в Excel для чайників

Робота з таблицями в Excel для чайників не терпить поспіху. Створити таблицю можна різними способами і для конкретних цілей кожен спосіб має свої переваги. Тому спочатку візуально оцінюємо ситуацію.

Подивіться уважно на робочий лист табличного процесора:

Це безліч осередків в стовпці і рядках. По суті - таблиця. Стовпці позначені латинськими буквами. Рядки - цифрами. Якщо вивести цей лист на друк, отримаємо чисту сторінку. Без будь-яких кордонів.

Спочатку давайте навчимося працювати з осередками, рядками і стовпцями.



Як виділити стовпець і рядок

Щоб виділити весь стовпець, клацаємо по його назві (латинської букви) лівою кнопкою миші.

Для виділення рядка - за назвою рядка (по цифрі).

Щоб виділити декілька стовпців або рядків, клацаємо лівою кнопкою миші по назві, тримаємо і протягаємо.

Для виділення стовпця за допомогою гарячих клавіш ставимо курсор в будь-яку клітинку потрібного стовпчика - натискаємо Ctrl + пробіл. Для виділення рядка - Shift + пробіл.

Як змінити кордони осередків

Якщо інформація при заповненні таблиці не поміщається потрібно змінити кордони осередків:

Для зміни ширини стовпців і висоти рядків відразу в певному діапазоні виділяємо область, збільшуємо 1 стовпець / рядок (пересуваємо вручну) - автоматично зміниться розмір всіх виділених стовпців і рядків.

Примітка. Щоб повернути колишній розмір, можна натиснути кнопку «Скасування» або комбінацію гарячих клавіш CTRL + Z. Але вона спрацьовує тоді, коли робиш відразу. Пізніше - не допоможе.

Щоб повернути рядки в вихідні кордону, відкриваємо меню інструменту: «Головна» - «Формат» і вибираємо «Автоподбор висоти рядка»

Для стовпців такий метод не актуальний. Натискаємо «Формат» - «Ширина за замовчуванням». Запам'ятовуємо цю цифру. Виділяємо будь-яку клітинку в стовпці, межі якого необхідно «повернути». Знову «Формат» - «Ширина стовпчика» - вводимо заданий програмою показник (як правило це 8,43 - кількість символів шрифту Calibri з розміром в 11 пунктів). ОК.

Як вставити стовпець або рядок

Виділяємо стовпець / рядок правіше / нижче того місця, де потрібно вставити новий діапазон. Тобто стовпець з'явиться зліва від виділеного вічка. А рядок - вище.

Натискаємо правою кнопкою миші - вибираємо в випадаючому меню «Вставити» (або тиснемо комбінацію гарячих клавіш CTRL + SHIFT + "\u003d").

Відзначаємо «стовпець» і тиснемо ОК.

Порада. Для швидкої вставки стовпця потрібно виділити стовпець в бажаному місці і натиснути CTRL + SHIFT + "\u003d".

Всі ці навички знадобляться при складанні таблиці в програмі Excel. Нам доведеться розширювати межі, додавати рядки / стовпці в процесі роботи.

Покрокове створення таблиці з формулами

Тепер при друку кордону стовпців і рядків будуть видні.

За допомогою меню «Шрифт» можна форматувати дані таблиці Excel, Як в програмі Word.

Поміняйте, наприклад, розмір шрифту, зробіть шапку «жирним». Можна встановити текст по центру, призначити переноси і т.д.

Як створити таблицю в Excel: покрокова інструкція

Найпростіший спосіб створення таблиць уже відомий. Але в Excel є більш зручний варіант (в плані подальшого форматування, роботи з даними).

Зробимо «розумну» (динамічну) таблицю:

Примітка. Можна піти іншим шляхом - спочатку виділити діапазон комірок, а потім натиснути кнопку «Таблиця».

Тепер вносите необхідні дані в готовий каркас. Якщо буде потрібно додатковий стовпець, ставимо курсор в призначену для назви осередок. Вписуємо найменування і натискаємо Enter. Діапазон автоматично розшириться.

Якщо необхідно збільшити кількість рядків, зачіпляються в нижньому правому куті за маркер автозаповнення і простягаємо вниз.

Як працювати з таблицею в Excel

З виходом нових версій програми робота в Ексель з таблицями стала цікавіше і динамічніше. Коли на аркуші сформована розумна таблиця, стає доступним інструмент «Робота з таблицями» - «Конструктор».

Тут ми можемо дати ім'я таблиці, змінити розмір.

Доступні різні стилі, можливість перетворити таблицю в звичайний діапазон або зведений звіт.

Можливості динамічних електронних таблиць MS Excel величезні. Почнемо з елементарних навичок введення даних і автозаповнення:

Якщо натиснути на стрілочку праворуч кожного підзаголовка шапки, то ми отримаємо доступ до додаткових інструментів для роботи з даними таблиці.

Іноді користувачеві доводиться працювати з величезними таблицями. Щоб подивитися підсумки, потрібно перегорнути не одну тисячу рядків. Видалити рядки - не варіант (дані згодом знадобляться). Але можна приховати. Для цієї мети скористайтеся числовими фільтрами (картинка вище). Прибираєте галочки навпроти тих значень, які повинні бути заховані.

»Ми розглянули, як зробити зміст книги Excel на першому аркуші. Але, часто, ми працюємо в одній, але великій таблиці з розділами, підрозділами, тощо В цьому випадку є ще один спосіб зробити зміст вExcel в таблиці - розмістити його в шапці таблиці.
Наприклад, у нас така таблиця. Для прикладу ми зробили маленьку таблицю.
Нам потрібно в цій таблиці швидко переходити в розділ таблиці певного класу, щоб не пересувати таблицю вручну.
Вставимо кілька рядків під або над шапкою таблиці (кому як зручно). В осередках нових вставлених рядків напишемо номери класів.

Для більшої візуалізації, ми підфарбували осередки з переліком класів. Спочатку дамо ім'я кожному осередку з номером класу в таблиці.
Тепер на номери класів у другому рядку зробимо гіперпосилання. Як зробити гіперпосилання в Excel, дивіться в статті «Як зробити гіперпосилання в Excel». Це буде зміст нашої таблиці.
У діалоговому вікні «Вставка гіперпосилання" вибираємо в розділі «Посилання на» натискаємо - «місцем в документі». Вибираємо ім'я потрібного діапазону.
Так робимо все посилання.
Залишилося закріпити рядки з шапкою і гіперпосиланнями, щоб з будь-якого місця таблиці, ми могли перейти в потрібний клас. Як це зробити, дивіться в статті «Як закріпити рядок в Excel і стовпець». Вийшло так.
Тут ми закріпили два рядки - це видно по межах за таблицею під другим рядком. Посилання на номер класу у другому рядку стали синіми. Натискаємо на потрібний номер класу і курсор стане на номер цього класу в таблиці. Ми натиснули на посилання 1В класу. Курсор стоїть в таблиці на осередку 1В класу.
Можна не закріплювати шапку таблиці, а закріпити тільки рядок з вмістом таблиці.
Тут посилання можна зробити не тільки на цей лист, але і на інші листи книги.
Які ще закладки можна зробити в таблиці, дивіться в статті "

Як зробити зміст у Excel, якщо ні спеціальної функції? Можна використовувати функцію "Гіперпосилання" в Excel.

Наприклад, у нас є величезна таблиця на кілька аркушів. На першій сторінці ми зробимо зміст цієї книги. Друга і наступні сторінки - це таблиці різних відділів (відділ продажів, відділ закупівель, тощо). Приблизно, так (у прикладі таблиці маленькі).

Принцип складання змісту в тому, щоб зробити гіперпосилання.

але, є одна хитрість. Гіперпосилання будемо робити не на комірки, а на ім'я комірки, Назва відділу. Якщо зробимо гіперпосилання на осередок, то, якщо вставимо рядок вище цього осередку, то гіперпосилання працювати не буде, зрушиться.

Отже, на кожній сторінці присвоюємо імена осередкам з назвою відділів - «Відділ продажів», «Відділ закупівель». Якщо таблиці довгі, то, можна привласнити імена і підрозділам - «1 відділ», «2 відділ». При присвоєння імені, не забуваємо вказати лист, на якому знаходиться ця назва. Як присвоїти ім'я клітинці, дивіться в статті «Присвоїти ім'я в Excel осередку, діапазону, формулою». У диспетчері імен видно все імена.

Тепер, якщо ми вставимо рядок в таблицю, то зміниться адреса комірки з ім'ям, тобто ім'я прив'язане до назви відділу.

На першій сторінці складаємо зміст з назви відділів і підрозділів.

Тепер залишається зробити гіперпосилання на ці слова. Як це зробити, дивіться в статті «Як зробити гіперпосилання в Excel». Встаємо на клітинку з назвою, яке хочемо зробити гіперпосиланням. З контекстного меню вибираємо функцію «Гіперпосилання». У діалоговому вікні вибираємо зі списку потрібне ім'я. Натискаємо «ОК». Все, посилання готова.

Все, зміст готове. Тепер можна швидко перейти на потрібну сторінку в потрібний підрозділ.

Посилання на лист виходить такий, як в осередку А2. Але в рядку формул можна підкоригувати назву, наприклад, як в осередку А3 - прибрали знак оклику і адреса комірки. Якщо листи підписані по-іншому, то в списку будуть ці назви листів. Дивіться осередок А4.

Можна зробити на кожному аркуші книги зворотний гіперпосилання до змісту книгиExcel.

Спочатку дамо ім'я змісту через функцію «Присвоїти ім'я». Наприклад - «Зміст». Зміст у нас на першому аркуші. На другому аркуші робимо гіперпосилання на перший лист «Зміст».

Тепер копіюємо цю гіперпосилання. Переходимо на лист 3, натискаємо кнопку «Ctrl», утримуючи її натиснутою, натискаємо лівою пахвою ярлики всіх листів книги, в яких потрібно встановити посилання на зміст. Так ми виділили всі листи відразу.

Тепер вставляємо гіперпосилання в потрібну комірку аркуша. Все, посилання «Зміст» з'явилася на всіх виділених аркушах. Залишилося разгруппировать листи. Для цього, натискаємо правою кнопкою миші на будь-який виділений ярлик і вибираємо з контекстного меню функцію «Розгрупувати листи».

Вставте в книгу порожній лист і додайте на нього гіперпосилання на потрібні вам листи, використовуючи команду Вставка - Гіперпосилання (Insert - Hyperlink). У вікні, потрібно вибрати зліва опцію Місце в документіі задати зовнішнє текстове відображення і адреса комірки, куди приведе посилання:

Для зручності можна створити також і зворотні посилання на всіх аркушах вашої книги, які будуть вести назад в зміст. Щоб не займатися ручним створенням гіперпосилань і копіюванням їх потім на кожен лист, краще використовувати інший метод - функцію Гіперпосилання (HYPERLINK). Виділяємо всі листи в книзі, куди хочемо додати зворотне посилання (для масового виділення листів можна використовувати клавіші Shiftі / або Ctrl) І в будь-яку відповідну клітинку вводимо функцію такого вигляду:

Ця функція створить в поточному осередку на всіх виділених листах гіперпосилання з текстом "Назад в зміст", клацання по якій буде повертати користувача до листу Зміст.

Спосіб 2. Динамічне зміст за допомогою формул

Це хоч і злегка екзотичний, але вельми красивий і зручний спосіб створення автоматичного листа змісту вашої книги. Екзотичний - тому що в ньому використовується недокументированная XLM-функція ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (GET.WORKBOOK), Залишена розробниками для сумісності зі старими версіями Excel. Ця функція вивантажує список всіх листів поточної книги в задану змінну, з якої ми потім можемо їх витягнути і використовувати в нашому змісті.

Відкрийте Диспетчер Імен на вкладці Формули (Formulas - Name Manager)і створіть новий іменований діапазон з ім'ям, припустимо, Зміст. У полі Діапазон (Reference) введіть ось таку формулу:

ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)
\u003d GET.WORKBOOK (1)

Тепер у змінній Змістмістяться наші шукані імена. Щоб витягти їх звідти на лист, можна скористатися функцією ІНДЕКС (INDEX), Яка «висмикує» елементи з масиву по їх номеру:

функція СТРОКА (ROW) видає номер поточного рядка і, в даному випадку, потрібна тільки для того, щоб вручну не створювати окремий стовпець з порядковими номерами видобутих елементів (1,2,3 ...). Таким чином, в осередку А1 у нас вийде ім'я першого листа, в А2 - ім'я другого і т.д.

Не погано. Однак, як можна помітити, функція видає не тільки ім'я листа, але і ім'я книги, яке нам не потрібно. Щоб його прибрати, скористаємося функціями ЗАМІНИТИ (SUBST) і ШУКАТИ (FIND), Які знайдуть символ закриває квадратної дужки (]) і замінять весь текст до цього символу включно на порожній рядок ( ""). Відкриємо ще раз Диспетчер імен з вкладки Формули (Formulas - Name Manager), Двічі клацнувши відкриємо створений діапазон Змісті змінимо його формулу:

\u003d SUBST (GET.WORKBOOK (1); 1; FIND ( "]"; GET.WORKBOOK (1)); "")

Тепер наш список листів буде виглядати значно краще:

Невелика побічна трудність полягає в тому, що наша формула в іменованому діапазоні Змістбуде перераховуватися тільки при введенні, або при примусовому перерахунку книги натисканням на клавіші Ctrl + Alt + F9. Щоб обійти цей неприємний момент, додамо до нашої формулою невеликий "хвіст":

ЗАМІНИТИ (ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1); 1; ШУКАТИ ( "]"; ПОЛУЧІТЬ.РАБОЧУЮ.КНІГУ (1)); "") & Т (ТДАТУ ())\u003d SUBST (GET.WORKBOOK (1); 1; FIND ( "]"; GET.WORKBOOK (1)); "") & T (NOW ())

функція ТДАТУ (NOW) видає поточну дату (з часом), а функція Т перетворює цю дату в порожню текстову рядок, яка потім приклеюється до нашого імені листа за допомогою оператора склейки (&). Тобто ім'я листа, фактично, не змінюється, але оскільки функція ТДАТУ перераховується і видає новий час і дату при будь-якій зміні листа, то і решта нашої формули змушена буде заново перерахувати теж і - як наслідок - імена аркушів будуть оновлюватися постійно.

Щоб приховати помилок # ПОСИЛАННЯ (#REF), Які будуть з'являтися, якщо скопіювати нашу формулу з функцією ІНДЕКСна більшу кількість осередків, ніж у нас є аркушів, можна використовувати функцію ЕСЛІОШІБКА (IFERROR), Яка перехоплює будь-які помилки і замінює їх на порожній рядок ( ""):

І, нарешті, для додавання до імен листів "живих" гіперпосилань для швидкої навігації, можна використовувати все ту ж функцію Гіперпосилання (HYPERLINK), Яка буде формувати адреса для переходу з імені листа:

Спосіб 3. Макрос

І, нарешті, для створення змісту можна використовувати і нескладний макрос. Правда, запускати його доведеться кожного разу при зміні структури книги - на відміну від способу 2, Макрос їх сам не відстежує.

Відкрийте редактор Visual Basic, натиснувши Alt + F11 або вибравши (в старих версіях Excel) в меню Сервіс - Макрос - Редактор Visual Basic(Tools - Macro - Visual Basic Editor) . У вікні редактора створіть новий порожній модуль (меню Insert - Module ) І скопіюйте туди текст цього макросу:

Sub SheetList ()
Dim sheet As Worksheet
Dim cell As Range
With ActiveWorkbook
For Each sheet In ActiveWorkbook.Worksheets
Set cell \u003d Worksheets (1) .Cells (sheet.Index, 1)
.Worksheets (1) .Hyperlinks.Add anchor: \u003d cell, Address: \u003d "", SubAddress: \u003d "" "& sheet.Name &" "" & "! A1"
cell.Formula \u003d sheet.Name
Next
End With
End Sub

Закрийте редактор Visual Ba sic і поверніться в Excel. Додайте в книгу чистий аркуш і помістіть його на перше місце. потім натиснітьAlt + F8 або відкрийте менюСервіс - Макрос - Макроси (Tools - Macro - Macros). Знайдіть там створений макросSheetList і запустіть його на виконання. Макрос створить на першому аркуші книги список гіперпосилань з назвами листів. Клацання по будь-якій з них перемістить Вас на потрібний лист.

Для зручності можна створити також і зворотні посилання на всіх аркушах вашої книги, які будуть вести назад в зміст, як це було описано в способі 1.

Спосіб мій. Мій варіант

т

Назва листа - \u003d ЕСЛІОШІБКА (ЗАМІНИТИ (ІНДЕКС (Зміст; СТРОКА () - 3); 1; ШУКАТИ ( "]"; ІНДЕКС (Зміст; СТРОКА () - 3)); ""); "")
Посилання - \u003d гіперпосилання ( "#" & "" "& B4 &" "" & "! A10"; "\u003e\u003e\u003e")
Дата - \u003d ЕСЛІОШІБКА (ЯКЩО (ДВССИЛ ( "" "& B4 &" "" & "! A1") \u003d 0; ""; ДВССИЛ ( "" "& B4 &" "" & "! A1")); "")
Найменування - \u003d ДВССИЛ ( "" "& B4 &" "" & "! A3")
ЗП - \u003d ДВССИЛ ( "" "& B4 &" "" & "! E5")
податок на ЗП - \u003d ДВССИЛ ( "" "& B4 &" "" & "! E6")
амортизація - \u003d ДВССИЛ ( "" "& B4 &" "" & "! E7")

матеріали - \u003d ДВССИЛ ( "" "& B4 &" "" & "! E8")
доп матеріали - \u003d ДВССИЛ ( "" "& B4 &" "" & "! E9")
і далі по стовпцях
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E10")
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E11")
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E12")
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E13")
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E18")
\u003d ДВССИЛ ( "" "& B4 &" "" & "! E19")