Как в excel получить список листов в

Обновлено: 05.07.2024

Альтернатива - см. вложение. Макросы должны быть разрешены

. можно ли посредством функций вывести на первом листе список листов?

Альтернатива - см. вложение. Макросы должны быть разрешены Serge_007

. можно ли посредством функций вывести на первом листе список листов?

Альтернатива - см. вложение. Макросы должны быть разрешены Автор - Serge_007
Дата добавления - 20.05.2012 в 17:15

спасибо, Сергей! Превосходное решение. Думаю, многим будет полезно. Виват! спасибо, Сергей! Превосходное решение. Думаю, многим будет полезно. Виват! nikitan95 [/vba]
так чтобы в общем списке листок не было самого листа "Список" и пары еще заранее известных, например Март и Сентябрь? [/vba]
так чтобы в общем списке листок не было самого листа "Список" и пары еще заранее известных, например Март и Сентябрь? Гость [/vba]
так чтобы в общем списке листок не было самого листа "Список" и пары еще заранее известных, например Март и Сентябрь? Автор - Гость
Дата добавления - 01.06.2012 в 12:00 возможно ошибаюсь, но ведь в принципе возможно написать через условие на совпадение имени в заданным и при выполнении условия задавать пустое значение . в общем попробую, т.к. в корне не представляю как работать с именованными диапазонами для листов и как их вообще задавать =) возможно ошибаюсь, но ведь в принципе возможно написать через условие на совпадение имени в заданным и при выполнении условия задавать пустое значение . в общем попробую, т.к. в корне не представляю как работать с именованными диапазонами для листов и как их вообще задавать =) Гость

[/vba]
т.к. отработав условие остается пустая ячейка и выглядеть это будет например так (исключаем "Март"):
Список
Январь 2012
Фераль

Апрель
Май
Июнь
Июль
Август

Октябрь
Ноябрь
Декабрь

а по хорошему нужно чтобы было без пустых ячеек . полистаю почитаю, может разберусь как с диапазонами работать =)

Апрель
Май
Июнь
Июль
Август

Октябрь
Ноябрь
Декабрь

а по хорошему нужно чтобы было без пустых ячеек . полистаю почитаю, может разберусь как с диапазонами работать =) Гость

Апрель
Май
Июнь
Июль
Август

Октябрь
Ноябрь
Декабрь

а по хорошему нужно чтобы было без пустых ячеек . полистаю почитаю, может разберусь как с диапазонами работать =) Автор - Гость
Дата добавления - 01.06.2012 в 13:48

Я предложил бы такой вариант:
вводите в диапазон $H$3:$H$10 не нужные, можно расширить диапазон.

Многим известно, что в excel существуют функции, которые остались от предыдущих версий программы, которые служат для того, чтобы при открытии старого файла данные в нем поддерживались. О подобных функциях сейчас редко говорят, однако они способны на некоторые очень полезные вещи.

Еще одна статья о скрытой функции, которая рассчитывает разницу между двумя датами в определенном формате - Скрытая, но мощная функция в Excel .

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

Предположим, у нас есть файл с некоторым количеством листов:

Для того, чтобы создать список, перейдите на новый лист. Во вкладке Формулы нажмите Определенные имена -> Задать имя. В открывшемся окне "Создание имени" присвойте имя вашему списку, в строке "Диапазон" пропищите формулу: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"") и нажмите ОК:

Функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ имеет два аргумента:

1. ном_типа - цифра, обозначающая тип информации, который мы хотим получить о книге excel. Один в нашем случае означает имена всех листов рабочей книги;

2. имя_текст - название рабочей книги, если оно опущено - используется активная рабочая книга.

После этого в ячейке, где будет находиться ваш список введите формулу: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА());"") и протяните ее вниз:

Функция ЕСЛИОШИБКА оставит пустыми ячейки, для которых не хватило названий листов.

✔ Ищите ответы на свои вопросы в статье Фишки excel - подборка статей . Там я перечислила все свои статьи по разбору работы функций excel.

✔ Вот здесь есть подборка видео о том, как строить необычные диаграммы - Фишки Excel - подборка видеороликов .

Странно, но в Excel невозможно напрямую сгенерировать список имен листов из книги. В этой статье будет рассказано, как создать список всех листов, содержащихся в книге. Как и в предыдущей статье, здесь мы будем использовать макрофункцию из Excel 4 XLM в именованной формуле.

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

Вставьте новый лист, на котором будет находиться список.
Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
Впишите в поле Имя название Список.
Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"") .
Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.

Рис. 9.1. Работа с окном Создание имени для создания именованной формулы

Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа. Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Список;СТРОКА()) .

Рис. 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА()) .

Рис. 9.3. Создание списка гиперссылок

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

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

Список листов с помощью формулы

Этот способ основан на использовании функции, которую нельзя найти в Менеджере. Она связана с макросами Excel 4.0. Чтобы применить формулу на практике, необходимо пройти дополнительный шаг, редко встречающийся в работе с функциями – зайти в диспетчер имен и добавить туда выражение.

Переходим на вкладку «Формулы» и кликаем по кнопке «Диспетчер имен». Опция находится в разделе «Определенные имена».

Нажимаем «Создать» в открывшемся диалоговом окне.

Открываем лист, где будет расположен список. Выбираем ячейку и записываем в ней формулу с только что созданным именем: =ИНДЕКС(Список_листов;СТРОКА()). Нажмите Enter, и в ячейке появится название первого листа.

Необходимо вывести все названия листов в столбец. Для этого зажимаем маркер заполнения, который находится в правом нижнем углу выбранной ячейки, и выделяем нужное количество ячеек. В таблице-примере 4 листа столько и было выделено ячеек.

Обратите внимание! Ячейка A1 прописывается в формуле, чтобы пользователи могли перейти на конкретную ячейку каждого листа. После нажатия Enter появится кликабельное название листа.

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

Как составить список листов через VBA

Существует другой способ составления списка листов из книги – можно подключить пользовательскую функцию через редактор Visual Basic. Такой метод может показаться сложным, но это не так, если воспользоваться шаблоном для добавления функции в программу.

Открываем VBA с помощью комбинации клавиш «Alt+F11». Если при нажатии F11 снижается яркость или срабатывает другая функция, установленная на эту кнопку, зажмите клавишу Fn.
Нажмите «Вставить» (Insert) на верхней панели и выберите в открывшемся меню пункт «Модуль» (Module).

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

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

Открываем лист для списка и вводим формулу в начальную ячейку. Теперь не нужно длинное выражение, чтобы создать список листов. Новая формула выглядит так: =SheetList(СТРОКА()).
Нажимаем Enter и получаем название листа в ячейке. Маркером заполнения создаем список.

Надстройки для составления списка листов

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

Всегда проверяйте загрузки на вредоносные элементы с помощью антивирусной программы.

Существуют платные и бесплатные надстройки. Сегодня рассмотрим набор дополнений для Excel 2007-2019 под названием «Ёxcel». Разработчик распространяет файл на своем сайте за добровольное пожертвование. Установите надстройку по инструкции – после этого можно приступать к составлению списка.

Открываем лист, где будет размещен список, и нажимаем левой кнопкой мыши на начальную ячейку будущего перечисления.
На вкладке надстройки находим кнопку «Листы». Кликаем по ней, чтобы открылось меню, и выбираем пункт «Получить список листов книги». Скриншот создателя надстройки:

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

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

Иногда в книгах Excel появляется много листов, например если документ относится к крупному проекту. Выяснить, сколько в файле страниц, можно с помощью функции ЛИСТЫ.

Обратите внимание! Функция работает только в версиях Microsoft Excel от 2013.

Выбираем пустую ячейку и записываем в ней формулу: =ЛИСТЫ(). Не обязательно заполнять аргумент «Ссылка», если нужно посчитать листы в одной книге.

Жмем Enter и получаем числовое значение.

Если все листы переименованы, и нужно узнать их номера, воспользуйтесь функцией ЛИСТ. Эта формула также доступна с 2013-й версии. У функции ЛИСТ один аргумент – «Значение». Если аргумент не заполнен, после нажатия клавиши Enter в ячейке появится номер того же листа, где была введена формула. Простое выражение с ЛИСТ выглядит так: =ЛИСТ().

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