Как сделать список листов в Excel?
Поговорим о различных способах создания списка листов книги в Excel для составления оглавления всей книги и удобной навигации между листами.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Причин составить список всех листов книги в Excel может быть превеликое множество — составление оглавления, навигация и т.д.
Для небольшого количества листов эту задачу можно сделать вручную, просто руками скопировав все названия.
Однако при изменении имени листа (или их очередности в книге) придется по новой переписывать названия или изменять порядок.
К тому же, если в книге насчитывается не один или два, а уже несколько десятков листов, то с этой задачей будет справиться еще сложнее.
Поэтому рассмотрим 2 варианта создания списка, которые помогут нам автоматизировать процесс:
Способ 1. Формула
Воспользуемся свойствами функции ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (в английском варианте GET.WORKBOOK), которая позволит нам получить список всех листов текущей книги.
Для начала перейдем в Диспетчер имен — переходим в панели вкладок на вкладку Формулы в раздел Определенные имена и добавляем новое имя (к примеру, Список_листов) в качестве диапазона которого прописываем следующую формулу:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)
Формально в качестве формулы можно было прописать только =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1), однако в этом случае в названиях листов будет также содержаться названия книги (например, [Книга1.xlsb]Лист1).
Удаляем все до символа правой закрывающейся квадратной скобки ], в итоге мы оставляем только имя листа (Лист1), и чтобы не делать это формульно каждый раз при обращении к отдельным элементам переменной Список_листов, сделаем это один раз сразу для всех элементов (как в примере выше).
Таким образом, в новой созданной переменной Список_листов содержатся название всех листов текущей книги, своего рода массив с данными, теперь же нам нужно извлечь их оттуда.
Для работы с массивом воспользуемся функцией ИНДЕКС (в английской версии INDEX), которая по порядковому номеру вытаскивает элемент массива, и функцией СТРОКА (английский вариант ROW) для создания простой нумерации:
Далее для удобства навигации с помощью функция ГИПЕРССЫЛКА (в английской версии HYPERLINK) добавим гиперссылки к названиям листов:
В результате каждая гиперссылка будет вести на ячейку A1 соответствующего имени листа.
Переходим к следующему способу.
Способ 2. Пользовательская функция (UDF)
Чтобы каждый раз не вспоминать алгоритм извлечения имени листа, реализуем пользовательскую функцию с идентичным функционалом.
Все стандартно: переходим в редактор Visual Basic (сочетание клавиш Alt + F11), создаем новый модуль и копируем туда текст функции:
Имена в EXCEL
Имя можно присвоить диапазону ячеек, формуле, константе или таблице. Использование имени позволяет упростить составление формул, снизить количество опечаток и неправильных ссылок, использовать трюки, которые затруднительно сделать другим образом.
Ниже приведены примеры имен.
Объект именования
Пример
Формула без использования имени
Формула с использованием имени
имя ПродажиЗа1Квартал присвоено диапазону ячеек C20:C30
имя НДС присвоено константе 0,18
имя УровеньЗапасов присвоено формуле ВПР(A1;$B$1:$F$20;5;ЛОЖЬ)
имя МаксПродажи2006 присвоено таблице, которая создана через меню Вставка/ Таблицы/ Таблица
имя Диапазон1 присвоено диапазону чисел 1, 2, 3
Для создания имени сначала необходимо определим объект, которому будем его присваивать.
Присваивание имен диапазону ячеек
1.Создание имени диапазона через команду Создать из выделенного фрагмента :
Проверить правильность имени можно через инструмент Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )
2.Создание имени диапазона через команду Присвоить имя :
3.Создание имени в поле Имя:
4.Создание имени через контекстное меню:
ВНИМАНИЕ! По умолчанию при создании новых имен используются абсолютные ссылки на ячейки (абсолютная ссылка на ячейку имеет формат $A$1 ).
5. Быстрое создание нескольких имен
Пусть имеется таблица, в каждой строке которой содержится определенный массив значений.
Чтобы создать все имена сразу, нужно:
Получим в Диспетчере имен ( Формулы/ Определенные имена/ Диспетчер имен ) сразу все 9 имен!
Присваивание имен формулам и константам
Присваивать имена формулам и константам имеет смысл, если формула достаточно сложная или часто употребляется. Например, при использовании сложных констант, таких как 2*Ln(ПИ), лучше присвоить имя выражению =2*LN(КОРЕНЬ(ПИ())) Присвоить имя формуле или константе можно, например, через команду Присвоить имя (через меню Формулы/ Определенные имена/ Присвоить имя ):
Присваивание имен таблицам
В. СИНТАКСИЧЕСКИЕ ПРАВИЛА ДЛЯ ИМЕН
Ниже приведен список синтаксических правил, которым следует руководствоваться при создании и изменении имен.
В качестве имен не следует использовать следующие специальные имена:
Если Вы в качестве имени использовали, например, слово Критерии с областью действия Лист1, то оно будет удалено при задании критериев для Расширенного фильтра на этом листе (без оповещения).
С. ИСПОЛЬЗОВАНИЕ ИМЕН
Уже созданное имя можно ввести в ячейку (в формулу) следующим образом.
D. ПОИСК И ПРОВЕРКА ИМЕН ОПРЕДЕЛЕННЫХ В КНИГЕ
Диспетчер имен: Все имена можно видеть через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ), где доступна сортировка имен, отображение комментария и значения.
Е. ОБЛАСТЬ ДЕЙСТВИЯ ИМЕНИ
Все имена имеют область действия: это либо конкретный лист, либо вся книга. Область действия имени задается в диалоге Создание имени ( Формулы/ Определенные имена/ Присвоить имя ).
VBA Excel. Рабочий лист (обращение, переименование, скрытие)
Обращение к рабочим листам Excel из кода VBA. Переименование листов, скрытие и отображение с помощью кода VBA Excel. Свойства Worksheets.Name и Worksheets.Visible.
Обращение к рабочим листам
Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.
Откройте редактор VBA и обратите внимание на вашу книгу в проводнике, где уникальные имена листов указаны без скобок, а в скобках – имена листов, отображаемые на ярлычках в открытой книге Excel. Уникальные имена листов отсортированы по алфавиту и их расположение по порядку не будет соответствовать их индексам (номерам), если листы перемещались по отношению друг к другу. Индексы листов смотрите по порядку расположения ярлычков в открытой книге. Переместили листы – изменились их индексы.
Обращение к рабочему листу в коде VBA Excel:
Количество листов в рабочей книге Excel определяется так:
Переименование листов
В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.
С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:
expression.Name
где expression – переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.
Допустим, у нас есть лист с уникальным именем (Name) – Лист1, индексом – 1 и именем Name – МойЛист, которое необходимо заменить на имя – Реестр.
Скрытие и отображение листов
Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:
expression.Visible
где expression – переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:
Аналоги присваиваемых значений:
