Список имен рабочих листов в Excel — 5 способов

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

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

Метод 1: вручную

Я считаю, что каждый пользователь Excel знает этот путь, но для полноты картины начну с него. Чтобы получить имя листа, вам нужно щелкнуть правой кнопкой мыши по имени и выбрать в появившемся меню «Переименовать»..

Это выделяет имя (то же самое можно сделать, просто дважды щелкнув имя левой кнопкой)

А теперь осталось скопировать его с помощью CTRL + C и вставить туда, где нам удобно..

Повторите эту процедуру со всеми остальными листами и составьте список их имен. Это звучит не очень «мастерски» или «круто». Однако по своему опыту я говорю вам — когда у вас 5-6 стежков и это одноразовое упражнение, которое не будет повторяться в будущем, это самый быстрый и эффективный способ получить список имен рабочих листов. В этом конкретном случае просто не беспокойтесь о других способах, которые вы увидите ниже..

Метод 2: макрос

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

Если вы не знакомы с написанием макросов, просто выполните следующие действия:

  1. В файле, в котором вы хотите составить список имен листов, создайте лист с именем Index..
  1. Скопируйте этот код:

Sub Sheet_Names_List ()

    Таблицы («Индекс»). Выбрать

    Листы («Индекс») Переместить до: = Листы (1)

    Колонны (1) .Вставить

    Столбцы (1) .Выбрать

    Selection.ClearContents

    Cells (1, 1) = «Имя листа»

    Ячейки (1, 1) .Выберите

    Selection.Font.Bold = True

            Для i = 2 To Sheets.Count

        Cells (i, 1) = Sheets (i). Name

    Далее я

Конец подписки

Короче говоря, на всякий случай этот код перемещает индексный лист в крайнее левое положение, вставляет столбец в крайний левый угол, а затем начинает сканирование других листов и выполнение метода 1 за вас..

  1. Используйте ALT + F11, чтобы открыть редактор Visual Basic
  1. Вставьте код внутри, сохраните изменения (CTRL + S) и закройте редактор VBA.
  1. Если вы хотите запустить макрос, вам нужно перейти в Developer Macros
Читать также:  21 великий футболист, ставший отличными тренерами

Затем выберите макрос с именем Sheet_Names_List и нажмите «Выполнить».

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

Хорошо, что эта комбинация не является одной из стандартных для Excel, например, CTR + S (сохранить) или CTR + P (печать). В этом случае я выбрал CTRL + SHIFT + M (нажмите все три сразу).

Преимущества этого метода очевидны — скорость и эффективность..

Минусы в том, что макрос всегда делает именно то, на что он запрограммирован, и если что-то изменится и не соответствует тому, что написано в коде — он просто не будет работать..

Например, он всегда будет вставлять новый столбец и создавать в нем новый список. Если пользователь хочет, чтобы старый список был удален, а на его месте появился новый — код необходимо изменить. Если в файле нет листа с именем Index, будет получено сообщение об ошибке.

Метод 3: полу-макро

Этот метод снова использует скорость и эффективность VBA, но без записи макросов. Подходит для одноразового или нечастого составления списка имен рабочих листов..

Откройте с помощью ALT + F11 VBA Editor, а затем нажмите CTRL + G, чтобы открыть окно Immediate.

Вам нужно ввести этот простой код: для каждого s в листах: debug.print s.name:next

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

Метод 4: дедушка VBA

До версии Excel 5.0 макросы были написаны в XLM (не путать с XML, который является форматом хранения). Хотя это совершенно древняя технология, многие из ее более чем 500 функций все еще можно использовать..

Читать также:  Ошибки в Excel, как их быстро и легко найти

Например, используя функцию GET.WORKBOOK, мы можем получить различную информацию о конкретном файле..

Его синтаксис прост: = GET.WORKBOOK (параметр; текст)

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

В нашем случае интересны параметры 1 и 16.

1 создает виртуальный столбец с именами листов, помещая имя файла в квадратные скобки перед ними

16 возвращает имя файла

Но мы не можем использовать их как стандартные функции в Excel — возникает ошибка.

Нам нужно создать именованную область с соответствующей функцией. Для этого комбинацией CTRL + F3 вызовите меню Name Manager и выберите New

Там в Name вводим название области (SheetNames), а в Refers To — формулу = GET.WORKBOOK (1)

Точно так же мы создаем область FileName с формулой = GET.WORKBOOK (16)

Следующий шаг — ввести несколько последовательных чисел в один столбец, а в следующем написать следующую формулу:

Когда мы копируем его в конец, мы получаем что-то похожее на список имен рабочих листов. Обратите внимание, что для чисел 8, 9 и 10 формула возвращает ошибку, потому что у нас не так много рабочих листов..

Все, что нам нужно сделать, это удалить имя файла. Это делается с помощью функции ЗАМЕНА и второй именованной области FileName. Текст формулы: = ПОДСТАВИТЬ (B1; «[» Имя файла «]»; «»)

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

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

К сожалению, этот метод не является полностью автоматическим. Обратите внимание на рисунке ниже, как удаляются Sheet1 и Sheet2, а монархи переименовываются в королей. Но это не отражено в списке имен.

Читать также:  Фундамент фундамента

Надо запустить, так сказать, макрос-дедушку комбинацией клавиш CTRL + ALT + F9.

Если вам интересно и вы хотите узнать больше о функциях XLM, по этой ссылке вы можете бесплатно скачать 653-страничное руководство в формате pdf..

Внимание! Не полагайтесь слишком на XLM — неизвестно, как долго Microsoft будет поддерживать его, хотя в настоящее время он работает во всех версиях Excel..

Метод 5: Power Query

Не знаю, самый ли это гибкий способ, но, безусловно, самый современный. &# x1f609;

Шаги следующие:

  1. Мы переходим к Data Get Data From File From Workbook (для версии 2016 и выше. Для более ранних версий это немного отличается, но вам все равно придется искать из файла)
  1. Устанавливаем путь к текущему файлу
  1. В этом меню выберите имя файла с желтой папкой на передней панели и нажмите «Преобразовать данные».
  1. Когда Power Query загружен, появляется таблица с двумя столбцами. Выберите столбец данных, затем щелкните правой кнопкой мыши и удалите его с помощью Удалить
  1. Вот наш список, все, что нам нужно сделать, это загрузить его с помощью Close Load To

В этом случае я решил загрузить в существующий Указатель листа

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

Когда есть изменения в количестве и / или именах листов, все, что вам нужно сделать, это щелкнуть правой кнопкой мыши где-нибудь в списке и выбрать Обновить.

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

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?