Как добавить лист в excel, если нет списка листов

Обновлено: 21.11.2024

Функции ЛИСТ и ЛИСТЫ были добавлены в Excel 2013. Функция ЛИСТ подсчитывает все листы в ссылке, а функция ЛИСТ возвращает номер листа для ссылки.

Введение в функции ЛИСТ и ЛИСТЫ

Среди новых функций в Excel 2013 – ЛИСТ и ЛИСТЫ.

Функция ЛИСТЫ подсчитывает все листы в ссылке.
Функция ЛИСТ возвращает номер листа для ссылки

Чтобы узнать, как использовать новые функции ЛИСТ и ЛИСТЫ в Excel 2013, посмотрите этот короткий видеоурок.

Использование функций

Вы можете использовать формулы ЛИСТ и ЛИСТЫ для устранения неполадок в книге.

ЛИСТЫ

Проверить пропущенные значения
Обратите внимание, что листы скрыты.

ЛИСТ

Показать номер листа в списке листов, чтобы найти пропущенные номера
Рассчитать номер листа в верхней части каждого листа, чтобы определить пробелы для скрытых листов.

Функция ЛИСТЫ

Получить количество листов

Функция ЛИСТЫ сообщает, сколько листов содержится в ссылке. На снимке экрана ниже эта формула:

=ЛИСТЫ(Dept01:Dept03!$I$4)

вернет 3 как количество листов в этом 3D-образце.

Проверить пропущенные значения

В приведенной выше формуле функция ЛИСТЫ сообщала нам, сколько листов содержится в справочнике. Мы могли бы использовать функцию COUNT или COUNTA, чтобы увидеть, есть ли значение на каждом листе.

=COUNTA(Dept01:Dept03!$I$4)

Затем вычтите количество листов из числа листов, чтобы увидеть, есть ли пропущенные значения.

На листе, показанном выше, в ссылке есть 3 листа, но только 2 записи. Вы можете проверить листы, чтобы найти пропущенное значение.

Обратите внимание на скрытые листы

В функции ЛИСТЫ, если вы опускаете ссылку, она сообщает вам, сколько листов в книге — видимых, скрытых или очень скрытых листов.

=ЛИСТЫ()

На снимке экрана ниже результат равен 8, но вы видите только 4 вкладки листа.

Если результат SHEETS не совпадает с количеством видимых вкладок, значит, пора искать скрытые листы.

Ограничения по ТАБЛИЦАМ

К сожалению, функция ЛИСТ не работает с несколькими ссылками, такими как:

=ЛИСТЫ(Лист1!B3,Лист2!B3,Лист4!B3)

Кроме того, вы не можете использовать ДВССЫЛ для создания ссылки на основе имен листов в ячейке, потому что ДВССЫЛ не поддерживает 3D-ссылки.

Функция ЛИСТ

Проверьте номер листа

Функция ЛИСТ сообщает вам номер листа для справки. На снимке экрана ниже эта формула:

=ЛИСТ(Dept02!I6)

возвращает 3 в качестве номера листа Dept02.

Это вкладка 3-го листа, так что вы можете сказать, что ни один из листов перед ней не скрыт.

Функция ЛИСТ также может возвращать номер листа для именованного диапазона или именованной таблицы Excel

Просмотр номера активного листа

В функции ЛИСТ, если вы опускаете ссылку, она сообщает вам номер активного листа.

=ЛИСТ()

На снимке экрана ниже результат равен 5, но активна вкладка 3-го листа. Это означает, что перед активным листом есть 2 скрытых листа.

Показать номера листов в списке

Функция ЛИСТ принимает обычную ссылку, а не трехмерную ссылку, как функция ЛИСТЫ. Таким образом, вы можете использовать ДВССЫЛ вместе с ЛИСТ, чтобы отобразить номера листов в списке имен листов.

На снимке экрана ниже показан лист меню со всеми именами листов в книге.В соседнем столбце эта формула вычисляет имя листа.

=ЛИСТ(ДВССЫЛ("'" & $B3 & "'!A1"))

Теперь я могу отсортировать список в алфавитном или числовом порядке и проверить наличие скрытых листов.

Ограничения ЛИСТ

Функция ЛИСТ, как и было обещано, но было бы неплохо иметь функцию, позволяющую получать информацию о листе на основе его номера. Или, возможно, мы могли бы использовать номер листа для создания ссылки, так же как мы можем использовать номер строки и номер столбца.

Таким образом, мы могли бы обратиться к листу, который находится на расстоянии -1 или +1 от активного листа.

Получить образец файла

Получите сжатый пример файла SHEET и SHEETS Functions. В книге также используется функция ФОРМУЛАТЕКСТ, чтобы показать формулы в примерах ячеек. Эти функции работают только в Excel 2013 или более поздней версии, поэтому вы увидите ошибки, если откроете файл в более ранней версии.

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

Примечание. Изображение в этой статье взято из Excel 2016. Ваше представление может немного отличаться, если у вас другая версия, но функциональность такая же (если не указано иное).

Из-за размера окна вкладки скрыты.

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

Строка состояния перемещена до строки формул.

Вкладки также могут исчезнуть, если разрешение экрана вашего компьютера выше, чем у человека, который последним сохранил книгу.

Попробуйте развернуть окно, чтобы открыть вкладки. Просто дважды щелкните строку заголовка окна.

Если вы по-прежнему не видите вкладки, нажмите «Вид» > «Упорядочить все» > «Мозаика» > «ОК».

Настройка "Показывать вкладки листа" отключена.

Сначала убедитесь, что параметр Показать вкладки листа включен. Для этого

Для всех остальных версий Excel нажмите «Файл» > «Параметры» > «Дополнительно» в разделе «Параметры отображения для этой книги» и убедитесь, что установлен флажок «Показать вкладки листа».

Горизонтальная полоса прокрутки закрывает вкладки.

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

Сам лист скрыт.

Чтобы отобразить рабочий лист, щелкните правой кнопкой мыши любую видимую вкладку и выберите "Показать". В диалоговом окне "Отобразить" щелкните лист, который нужно отобразить, и нажмите кнопку "ОК".

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

В Excel вы можете легко вставлять, переименовывать и удалять рабочие листы в книге.

Вставить рабочий лист

Нажмите значок плюса в нижней части экрана.

Или выберите Главная > Вставить > Вставить лист.

Переименовать лист

Дважды щелкните имя листа на вкладке "Лист", чтобы быстро переименовать его.

Или щелкните правой кнопкой мыши вкладку "Лист", выберите "Переименовать" и введите новое имя.

Удалить лист

Щелкните правой кнопкой мыши вкладку "Лист" и выберите "Удалить".

Или выберите лист, а затем выберите Главная > Удалить > Удалить лист.

Примечание. Вкладки листов отображаются по умолчанию. Если вы их не видите, нажмите «Параметры» > «Дополнительно» > «Параметры отображения для этой книги» > «Показать вкладки листа».

Чтобы вставить новый лист, выполните одно из следующих действий:

Чтобы быстро вставить новый рабочий лист в конец существующих рабочих листов, нажмите вкладку "Вставить рабочий лист" в нижней части экрана.

Чтобы вставить новый лист перед существующим листом, выберите этот лист, а затем на вкладке "Главная" в группе "Ячейки" нажмите "Вставить", а затем нажмите "Вставить лист".

Совет. Вы также можете щелкнуть правой кнопкой мыши вкладку существующего листа и выбрать "Вставить". На вкладке "Общие" нажмите "Рабочий лист" и нажмите "ОК".

Примечание. Чтобы изменить порядок листов в книге, щелкните вкладку листа, который вы хотите переместить, а затем перетащите его в нужное место.

Что вы хотите сделать?

Одновременная вставка нескольких рабочих листов

Удерживая нажатой клавишу SHIFT, выберите такое же количество вкладок существующих листов, которые вы хотите вставить в открытую книгу.

Например, если вы хотите добавить три новых листа, выберите три вкладки существующих листов.

На вкладке "Главная" в группе "Ячейки" нажмите "Вставить", а затем нажмите "Вставить лист".

Совет. Можно также щелкнуть правой кнопкой мыши выбранные вкладки листа и выбрать команду "Вставить". На вкладке "Общие" нажмите "Рабочий лист" и нажмите "ОК".

Чтобы изменить порядок листов в книге, щелкните вкладку листа, который вы хотите переместить, а затем перетащите его в нужное место.

Изменить количество рабочих листов по умолчанию в новой книге

Перейдите на вкладку "Файл".

Что и где находится кнопка Microsoft Backstage?

Дополнительные сведения о кнопке Microsoft Backstage см. в разделе Что такое Backstage и где находится?

В Excel нажмите "Параметры".

В категории "Общие" в разделе "При создании новых книг" в поле "Включить столько листов" введите количество листов, которое вы хотите включить по умолчанию при создании новой книги.

Нажмите на любую другую вкладку, чтобы вернуться к файлу.

Вставить новый лист на основе пользовательского шаблона

При необходимости создайте шаблон рабочего листа, на основе которого вы хотите создать новый рабочий лист.

Как создать шаблон рабочего листа

Выберите рабочий лист, который вы хотите использовать в качестве шаблона.

Перейдите на вкладку "Файл".

В разделе "Информация" нажмите "Сохранить как".

В поле Имя файла введите имя шаблона листа.

Чтобы создать собственный шаблон рабочего листа, введите имя файла, которое вы хотите использовать.

Чтобы создать шаблон листа по умолчанию, введите лист.

Примечание. Пользовательские шаблоны автоматически сохраняются в папке "Шаблоны". Шаблон рабочего листа по умолчанию, sheet.xltx или sheet.xltm, следует сохранить в папке XLStart, которая обычно находится в папке C:\Program Files\Microsoft Office\Office14\XLStart.

Выполните одно из следующих действий:

На компьютере под управлением Windows Vista в списке выберите Шаблон Excel или Шаблон Excel с поддержкой макросов.

На компьютере под управлением Microsoft Windows XP в поле Тип файла выберите Шаблон Excel или Шаблон Excel с поддержкой макросов.

Нажмите "Сохранить".

Щелкните правой кнопкой мыши вкладку листа и выберите "Вставить".

Дважды щелкните шаблон нужного типа листа.

Переименовать лист

На панели вкладок "Лист" щелкните правой кнопкой мыши вкладку листа, который вы хотите переименовать, и выберите "Переименовать лист".

Выберите текущее имя, а затем введите новое имя.

Совет. Вы можете указать имя листа при печати рабочего листа.

Как печатать имена листов

На вкладке "Вставка" в группе "Текст" нажмите "Верхний и нижний колонтитулы".

В режиме макета страницы щелкните место, где должно отображаться имя листа.

В группе элементов "Верхний и нижний колонтитулы" нажмите "Имя листа".

Удалить один или несколько листов

Выберите лист или листы, которые вы хотите удалить.

Совет. Если выбрано несколько рабочих листов, [Группа] отображается в строке заголовка в верхней части рабочего листа. Чтобы отменить выбор нескольких рабочих листов в книге, щелкните любой невыбранный рабочий лист. Если невыбранный лист не отображается, щелкните правой кнопкой мыши вкладку выбранного листа и выберите в контекстном меню пункт Разгруппировать листы.

На вкладке "Главная" в группе "Ячейки" нажмите стрелку рядом с кнопкой "Удалить" и выберите "Удалить лист".

Совет. Вы также можете щелкнуть правой кнопкой мыши вкладку листа рабочего листа или вкладку любого выбранного листа, который вы хотите удалить, а затем нажать "Удалить лист".

Нужна дополнительная помощь?

В этом посте мы узнаем, как получить список всех имен листов в текущей книге без использования VBA.

Это может быть очень удобно, если у вас большая книга с сотнями листов и вы хотите создать оглавление. Этот метод использует малоизвестные и часто забываемые функции макросов Excel 4.

Эти функции не похожи на другие функции Excel, такие как СУММ, ВПР, ИНДЕКС и т. д. Эти функции не будут работать на обычном листе, они работают только в именованных функциях и листах макросов.Для этого трюка мы собираемся использовать один из них в именованной функции.

В этом примере я создал книгу с большим количеством листов. В этом примере 50 листов, поэтому я поленился и не переименовал их из имен по умолчанию.

Теперь мы создадим нашу именованную функцию.

Перейдите на вкладку "Формулы".
Нажмите кнопку "Определить имя".
Введите SheetNames в поле имени.
Введите следующую формулу в поле "Относится к".
Нажмите кнопку "ОК".

На листе рабочей книги введите числа 1,2,3 и т. д. в столбец A, начиная со строки 2, а затем в ячейку B2 введите следующую формулу, скопируйте и вставьте ее вниз по столбцу, пока не получите список все имена ваших листов.

В качестве бонуса мы также можем создать гиперссылку, так что если вы нажмете на ссылку, вы попадете на этот лист. Это может быть удобно для навигации по электронной таблице с большим количеством листов. Для этого добавьте эту формулу в столбец C.

Обратите внимание, что для использования этого метода вам необходимо сохранить файл как книгу с поддержкой макросов (.xls, .xlsm или .xlsb). Не слишком сложно и не требуется VBA.

Видеоруководство

В этом видео показаны два способа перечисления всех имен листов в книге.

В. У меня есть рабочая книга Excel с десятками рабочих листов с пользовательскими именами. Можно ли создать список имен этих рабочих листов в Excel без необходимости их повторного ввода?

А. Да, вы можете создать список имен рабочих листов вашей книги Excel следующим образом. На вкладке «Формулы» выберите «Определенные имена», «Определить имя», чтобы открыть диалоговое окно «Новое имя», показанное ниже.

Введите SheetNames в поле Name, введите следующую формулу в поле Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)), "") , а затем выберите ОК. Это действие создаст именованную формулу, которую затем можно использовать в сочетании с функцией ИНДЕКС для создания списка имен рабочих листов. Продолжая, предположим, что у вас есть 25 именованных рабочих листов. Затем выберите ячейку, в которой вы хотите, чтобы ваш список имен отображался (ячейка B2 в этом примере), и введите числа от 1 до 25 (в ячейках от A2 до A26 в этом примере, как показано на рисунке ниже). Затем в ячейке B2 введите формулу =INDEX(SheetNames,A2) , а затем скопируйте и вставьте формулу вниз на 25 строк. Эти формулы вернут список имен вкладок рабочего листа в том же порядке, что и вкладки рабочего листа.

(Примечание: рабочая книга должна быть сохранена как файл Excel Macro Enabled Workbook, чтобы сохранить формулу определенного имени.)

В результате вы сможете щелкнуть каждую ссылку, чтобы перейти к различным рабочим листам, перечисленным в вашем оглавлении. (В качестве альтернативы вы можете выбрать каждое имя в оглавлении по одному и нажать Ctrl+K, чтобы применить гиперссылку непосредственно к каждому имени рабочего листа. Преимущество этого альтернативного подхода заключается в том, что отдельный столбец гиперссылок (например, как столбец C в приведенном выше примере) не является обязательным, но недостатком является то, что каждая гиперссылка должна применяться по одной.)

Кроме того, я рекомендую вставить на каждый рабочий лист кнопку, которая вернет вас к оглавлению следующим образом:

Читайте также: