Завантажте зразок робочої книги
У цьому посібнику показано, як перелічити назви аркушів книги з формулою в Excel.
Список імен аркушів за допомогою іменованого діапазону та формули
В Excel немає вбудованої функції, яка б містила список усіх аркушів у книзі. Однак цього можна досягти за допомогою комбінації різних функцій.
Щоб перелічити назви аркушів у книзі, ми зробимо наступне:
- Створіть іменований діапазон «Робочі листи»
- Використовуйте формулу, щоб перелічити всі назви аркушів.
Створення діапазону імен для імен аркушів
Щоб створити іменований діапазон для назв аркушів, у Стрічка Excel: Формули> Менеджер імен> Створити
Введіть "Робочі листи" у полі імені:
У розділі «Відноситься до» діалогового вікна нам потрібно буде записати формулу
| 1 | = ОТРИМАТИ РОБОЧУ КНИГУ (1) & Т (ЗАРАЗ ()) " |
Ця формула зберігає назви всіх аркушів (у вигляді масиву у такому форматі: “[робоча книга.xlsm]. Огляд”) у книзі до іменованого діапазону “Робочі аркуші”.
Функція “GET.WORKBOOK”-це функція макросу, тому вашу книгу потрібно зберегти як робочу книгу з підтримкою макросів (формат файлу: .xlsm), щоб імена аркушів оновлювалися щоразу під час відкриття книги.
Примітка: Під час заповнення діалогового вікна Редагувати ім'я робочу книгу слід вибрати як область діапазону імен.
Використання формули для переліку імен аркушів
Тепер ми використовуємо формулу, щоб перелічити назви аркушів. Нам знадобляться функції INDEX, MID, FIND і ROWS:
| 1 | = ІНДЕКС (MID (Робочі аркуші, Знайти ("]", Робочі аркуші) +1,255), РЯДКИ ($ B $ 5: B5)) |
- Формула вище бере масив "Робочі листи" і відображає назву кожного аркуша залежно від його положення.
- Функції MID та FIND витягують назви аркушів з масиву (видаляючи назву книги).
- Тоді функції INDEX та ROW відображають кожне значення в цьому масиві.
- Тут "Огляд" - це перший аркуш у робочих зошитах, а "Очищення" - останній.
Докладніше про те, як функції MID та FIND працюють для отримання імен аркушів, див. За посиланням get-sheet-name.
Альтернативний метод
У вас також є можливість створити список імен аркушів у Менеджері імен. Замість
| 1 | = ОТРИМАТИ РОБОЧУ КНИГУ (1) & Т (ЗАРАЗ ()) |
встановіть для поля «Відноситься до» значення
| 1 | = ЗАМІНИТИ (GET.WORKBK (1), 1, FIND ("]", GET.WORKBK (1)), "") |
Тепер у вашій формулі немає необхідності MID, FIND і ROWS. Іменований діапазон уже складається лише з назв аркушів.
Використовуйте цю простішу формулу INDEX, щоб перелічити аркуші:
| 1 | = ІНДЕКС (Назва аркуша, В3) |
