Оглавление в экселе. Формула номера листа и страницы в Excel. Номер страницы на VBA

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

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

Видео

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

ГИПЕРССЫЛКА (HYPERLINK) Shift и/или Ctrl

Оглавление .

Откройте Диспетчер Имен на вкладке Оглавление . В поле Диапазон (Reference) введите вот такую формулу:

ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
=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 = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = 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)
=GET.WORKBOOK(1)

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX) , которая «выдергивает» элементы из массива по их номеру:

Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.

Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND) , которые найдут символ закрывающей квадратной скобки (]) и заменят весь текст до этого символа включительно на пустую строку («»). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager) , двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:

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

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

Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9 . Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой «хвост»:

ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()) =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 = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:=»», SubAddress:=»‘» & sheet.Name & «‘» & «!A1»
cell.Formula = sheet.Name
Next
End With
End Sub Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте менюСервис - Макрос - Макросы (Tools - Macro - Macros) . Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.

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

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

т
Название листа - =ЕСЛИОШИБКА(ЗАМЕНИТЬ(ИНДЕКС(Оглавление;СТРОКА()-3);1;НАЙТИ(«]»;ИНДЕКС(Оглавление;СТРОКА()-3));»»);»»)

Дата - =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ(«‘»&B4&»‘»&»!A1″)=0;»»;ДВССЫЛ(«‘»&B4&»‘»&»!A1″));»»)

Наименование - =ДВССЫЛ(«‘»&B4&»‘»&»!A3″)

ЗП - =ДВССЫЛ(«‘»&B4&»‘»&»!E5″)

налог на ЗП - =ДВССЫЛ(«‘»&B4&»‘»&»!E6″)

амортизация - =ДВССЫЛ(«‘»&B4&»‘»&»!E7″)

материалы - =ДВССЫЛ(«‘»&B4&»‘»&»!E8″)
всп материалы - =ДВССЫЛ(«‘»&B4&»‘»&»!E9″)

ДВССЫЛ(«‘»&B4&»‘»&»!E10″)

ДВССЫЛ(«‘»&B4&»‘»&»!E11″)=ДВССЫЛ(«‘»&B4&»‘»&»!E12″)=ДВССЫЛ(«‘»&B4&»‘»&»!E13″)=ДВССЫЛ(«‘»&B4&»‘»&»!E18″)=ДВССЫЛ(«‘»&B4&»‘»&»!E19″)

Программа Microsoft Excel удобна для составления таблиц и произведения расчетов. Рабочая область – это множество ячеек, которые можно заполнять данными. Впоследствии – форматировать, использовать для построения графиков, диаграмм, сводных отчетов.

Работа в Экселе с таблицами для начинающих пользователей может на первый взгляд показаться сложной. Она существенно отличается от принципов построения таблиц в Word. Но начнем мы с малого: с создания и форматирования таблицы. И в конце статьи вы уже будете понимать, что лучшего инструмента для создания таблиц, чем Excel не придумаешь.

Как создать таблицу в Excel для чайников

Работа с таблицами в Excel для чайников не терпит спешки. Создать таблицу можно разными способами и для конкретных целей каждый способ обладает своими преимуществами. Поэтому сначала визуально оценим ситуацию.

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.



Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

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

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

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

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+"=").

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+"=".

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

Как создать таблицу в Excel: пошаговая инструкция

Простейший способ создания таблиц уже известен. Но в Excel есть более удобный вариант (в плане последующего форматирования, работы с данными).

Сделаем «умную» (динамическую) таблицу:

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

Теперь вносите необходимые данные в готовый каркас. Если потребуется дополнительный столбец, ставим курсор в предназначенную для названия ячейку. Вписываем наименование и нажимаем ВВОД. Диапазон автоматически расширится.

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

Как работать с таблицей в 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)
=GET.WORKBOOK(1)

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX) , которая «выдергивает» элементы из массива по их номеру:

Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.

Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND) , которые найдут символ закрывающей квадратной скобки (]) и заменят весь текст до этого символа включительно на пустую строку (""). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager) , двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:

=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

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

Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9 . Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой "хвост":

ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА()) =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 = Worksheets(1).Cells(sheet.Index, 1)
.Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet.Name & """ & "!A1"
cell.Formula = sheet.Name
Next
End With
End Sub

Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню Сервис - Макрос - Макросы (Tools - Macro - Macros) . Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.

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

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

т

Название листа - =ЕСЛИОШИБКА(ЗАМЕНИТЬ(ИНДЕКС(Оглавление;СТРОКА()-3);1;НАЙТИ("]";ИНДЕКС(Оглавление;СТРОКА()-3));"");"")
Ссылка - =ГИПЕРССЫЛКА("#"&"""&B4&"""&"!A10";">>>")
Дата - =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("""&B4&"""&"!A1")=0;"";ДВССЫЛ("""&B4&"""&"!A1"));"")
Наименование - =ДВССЫЛ("""&B4&"""&"!A3")
ЗП - =ДВССЫЛ("""&B4&"""&"!E5")
налог на ЗП - =ДВССЫЛ("""&B4&"""&"!E6")
амортизация - =ДВССЫЛ("""&B4&"""&"!E7")

материалы - =ДВССЫЛ("""&B4&"""&"!E8")
всп материалы - =ДВССЫЛ("""&B4&"""&"!E9")
и далее по столбцам
=ДВССЫЛ("""&B4&"""&"!E10")
=ДВССЫЛ("""&B4&"""&"!E11")
=ДВССЫЛ("""&B4&"""&"!E12")
=ДВССЫЛ("""&B4&"""&"!E13")
=ДВССЫЛ("""&B4&"""&"!E18")
=ДВССЫЛ("""&B4&"""&"!E19")