Создание списка (столбца) вкладок книги Excel

Содержание

Вопрос или проблема
Ответ или решение
Метод 1. Использование формулы Excel
Метод 2. Использование Office Scripts
Метод 3. Использование Именованных Диапазонов с Индексом
Заключение

Вопрос или проблема

Я использую Excel для Microsoft 365 Вер. 2402 Сборка 16.0…. 32-битная.

У меня есть книга с несколькими вкладками.

Есть ли простой способ сгенерировать список (столбец) имен вкладок? Мне нужно создать скрипт?

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

Вы можете использовать следующую формулу в Excel, однако файл необходимо сохранить в формате .xlsm или .xlsb, так как она использует Excel Macro 4.0 формулы

=TEXTAFTER(GET.WORKBOOK(1),"- ")

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

=TOCOL(SheetNames,3)

Другой вариант – использовать вкладку ‘Автоматизация’ на ленте с этим скриптом, написанным в Office Scripts:

function main(workbook: ExcelScript.Workbook) {
    const sheetNames: string[][] = workbook.getWorksheets().map(sheet => [sheet.getName()]);

    workbook
        .getActiveWorksheet()
        .getRangeByIndexes(0, 0, sheetNames.length, 1) // Настройте под ваш выходной диапазон
        .setValues(sheetNames)
}

Пример, показанный выше, запишет имена листов в диапазон, начиная с ячейки A1 активного листа.

Вызов .getRangeByIndexes имеет нулевую индексацию, и в примере показано ‘начать с нулевой строки, нулевого столбца и создать диапазон из sheetNames.length строк и 1 столбца’.

Таким образом, например, чтобы записать это, начиная с B2, вы бы сделали так:

.getRangeByIndexes(1, 1, sheetNames.length, 1)

По какой-то причине ответ @Mayukh Bhattacharya не работает для меня, поэтому вот альтернативный (одна и та же идея, впрочем):

Создайте новый именованный диапазон:

Затем в, скажем, ячейке A1 используйте формулу:

=Index(sheetNames,row(a1))

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

Ответ или решение

Создание списка вкладок в Excel: Простой способ

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

Метод 1. Использование формулы Excel

Для начала, вы можете воспользоваться формулой Excel, которая требует, чтобы ваш файл был сохранен в формате .xlsm или .xlsb, так как используется функционал Macros 4.0.

Создайте именованное значение:
- В Excel перейдите в «Управление именами» (Name Manager).
- Создайте новое именованное значение, назовите его, например, «SheetNames», и в поле «Ссылается на» введите следующую формулу:
```
=TEXTAFTER(GET.WORKBOOK(1), "- ")
```
Используйте формулу в ячейке:
- На нужной вам вкладке (например, в ячейке A1) введите формулу:
```
=TOCOL(SheetNames, 3)
```
- Эта формула выведет список всех имен вкладок в виде столбца.

Метод 2. Использование Office Scripts

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

Напишите скрипт:

В Excel выберите вкладку «Автоматизация» и создайте новый скрипт. Вставьте следующий код:

function main(workbook: ExcelScript.Workbook) {
 const sheetNames: string[][] = workbook.getWorksheets().map(sheet => [sheet.getName()]);

 workbook
     .getActiveWorksheet()
     .getRangeByIndexes(0, 0, sheetNames.length, 1)
     .setValues(sheetNames);
}

Настройте диапазон вывода:
- Скрипт по умолчанию начнет записывать имена с ячейки A1. Если вам нужно начать с другой ячейки (например, B2), измените строчку:
```
.getRangeByIndexes(1, 1, sheetNames.length, 1);
```

Метод 3. Использование Именованных Диапазонов с Индексом

Если предыдущие методы по каким-либо причинам вам не подошли, есть еще один способ:

Создайте именованный диапазон:
- В Excel создайте новый именованный диапазон аналогично первому методу.
Используйте функцию INDEX:
- В ячейке A1 введите:
```
=INDEX(SheetNames, ROW(A1))
```
- Протяните формулу вниз, чтобы получить список имён всех вкладок.

Заключение

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

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