как узнать имя листа в excel
Имя листа в ячейке формулой в Excel
Разберем несколько вариантов добавления имени листа в ячейку в Excel с помощью формул.
Формула имени листа в Excel
Чтобы присвоить ячейке имя листа в Excel можно воспользоваться следующей формулой:
=ПСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))+1;255)
Давайте по шагам разберем принцип действия формулы имени листа.
Шаг 1. Функция ЯЧЕЙКА
Функция ЯЧЕЙКА позволяет получить данные о содержимом ссылки, в том числе и имя файла.
В данном случае формула ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ) позволяет получить полный путь файла Excel на локальном диске:
Как мы видим название листа идет сразу после названия файла, обрамленного в квадратные скобки.
Шаг 2. Функция ПОИСК
Таким образом для извлечения имени листа необходимо найти символ закрывающейся квадратной скобки (]) с помощью функции ПОИСК, которая возвращает позицию первого вхождения искомого элемента:
Прибавляя к результату 1, мы получаем позицию с которой начинается имя листа.
Шаг 3. Функция ПСТР
После нахождения квадратной скобки нам достаточно извлечь из полного названия файла правую часть, применив функцию ПСТР.
Данная функция возвращает заданное количество знаков, начиная с указанной позиции.
Так как точное количество символов в имени листа неизвестно, то в качестве последнего аргумента функции ПСТР указываем заведомо большее число, чем длина имени листа (подойдет любое число больше 31 — максимальная длина названия листа).
Альтернативная формула
Чтобы вставить название листа в ячейку можно воспользоваться альтернативной формулой:
=ПРАВСИМВ(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ДЛСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))-ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 )))
Отличие от предыдущего варианта заключается в использовании функции ПРАВСИМВ, которая возвращает указанное количество знаков с конца текста.
Описание используемых функций
Функция ЯЧЕЙКА:
ЯЧЕЙКА(тип_сведений; [ссылка])
Возвращает сведения о форматировании, адресе или содержимом первой ячейки ссылки.
Функция ПОИСК:
ПОИСК(искомый_текст; просматриваемый_текст; [начальная_позиция])
Возвращает позицию первого вхождения знака или строки текста (при чтении слева направо, прописные и строчные буквы не различаются).
Функция ПСТР:
ПСТР(текст; начальная_позиция; число_знаков)
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.
Примеры использования функций ЛИСТ и ЛИСТЫ в формулах Excel
Функция ЛИСТ в Excel возвращает числовое значение, соответствующее номеру листа, на который указывает ссылка, переданная функции в качестве параметра.
Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса
Функция ЛИСТЫ в Excel возвращает числовое значение, которое соответствует количеству листов, на которые предоставлена ссылка.
Функция ЛИСТ имеет в своем синтаксисе всего 1 аргумент и то не обязательный для заполнения: =ЛИСТ(значение).
Функция листы имеет следующий синтаксис: =ЛИСТЫ(ссылка).
Как получить имя листа формулой в Excel
Пример 1. При выполнении расчетной работы студент использовал программу Excel, в которой создал книгу из несколько листов. Для собственного удобства, студент решил в ячейках A2 и B2 каждого листа выводить данные о названии листа и его порядковом номере соответственно. Для этого он использовал следующие формулы:
Описание аргументов для функции ПСТР:
=ЛИСТ() – данная функция без параметра вернет номер текущего листа. В результате ее вычисления получим количество листов в текущей книге.
Примеры использования функции ЛИСТ и ЛИСТЫ
Пример 2. В книге Excel содержится несколько листов. Необходимо:
Внесем данные в таблицу:
Далее составим формулы для всех 4-х условий:
Функция ЕСЛИ выполняет проверку условия равенства значения, хранящегося в ячейке A3 листа Динамические_расчеты, нулю или пустому значению.
В результате получим:
Обработка информации о листах книги по формуле Excel
Пример 3. В книге Excel содержится несколько листов. Необходимо определить общее количество листов, а также число листов, содержащихся между листами «Статические_расчеты» и «Экономические_расчеты».
Исходная таблица имеет вид:
Общее количество листов вычислим по формуле:
Для определения количества листов, содержащихся между двумя указанными листами, запишем формулу:
В результате получим следующее:
Формула выводит подробную информацию по данным о листах в определенном диапазоне их расположения в рабочей книге Excel.
Как получить имя листа формулой
Задача: записать в отдельной ячейке или внутри формулы имя текущего листа(т.е. того, в котором сама функция).
В принципе это очень легко сделать простейшей функцией пользователя:
Но бывают случаи, когда использование макросов весьма нежелательно. Тогда можно воспользоваться чуть более громоздкой и менее понятной формулой:
=ПСТР(ЯЧЕЙКА(«filename»; A2 );ПОИСК(«]»;ЯЧЕЙКА(«filename»; A2 ))+1;31)
=MID(CELL(«filename»,A2),SEARCH(«]»,CELL(«filename»,A2))+1,31)
Однако эта формула вернет точно такой же результат, как функция пользователя выше и макросы совершенно не нужны.
Если вторым аргументом функции ЯЧЕЙКА ничего не указывать( =ЯЧЕЙКА(«filename») ), то функция вернет полный путь с именем того листа, который активен в данный момент(даже если это лист другой книги).
Правда, у этой формулы есть свои недостатки: обязательно необходимо, чтобы книга была сохранена на диске. Это означает, что формула не сработает для книги, которая была только что создана и не сохранена. Связано это с ограничениями возможностей параметра «filename» функции ЯЧЕЙКА(CELL). Она не может получить путь к файлу, который еще не сохранен.
Функция пользователя(UDF) GetShName (приведенная в самом начале статьи) лишена этого недостатка.
Для чего вообще может быть нужно записывать имя листа в ячейку? Ну, например, если имя листа периодически меняется, а в своих формулах вы используете функции вроде ДВССЫЛ со ссылкой на этот лист. Либо для создания более наглядного оглавления через гиперссылки.
Так же можно получить полный путь к книге и имя книги(без квадратных скобок и имени листа):
=ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА(«filename»;A1);1;ПОИСК(«]»;ЯЧЕЙКА(«filename»;A1))-1);»[«;»»)
Статья помогла? Поделись ссылкой с друзьями!
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 могут присваиваться следующие значения:
Аналоги присваиваемых значений:
Как определить название листа Excel
В моей программе есть необходимость открывать в DataGridView данные из Excel. Делаю это так:
Хочу использовать именно такой вариант чтения, потому что данные в этом случае загружаются в DataGridView быстро, не смотря на количество строк и столбцов на листе, в отличие от последовательного чтения значений из ячеек.
Но вот загвоздка. В этом моем коде есть строка команды
. эта строка выбирает данные из листа Excel с именем «Лист1». Подскажите, как быть, если этот лист называется не «Лист1», т.е. как узнать имена существующих листов в открытом таким образом файле Excel?
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Eugene22, Так ему и надо программно получить список листов в книге Excel, которая прицеплена как БД.
Есть 2 варианта.
1) Использовать библиотеку Excel (Microsoft.Office.Interop). Добавляем ее в референцы и получаем коллекцию Sheets.
Но, надо полагать, автору надо избавиться от такого референца.
2) получить коллекцию таблиц (листов книги) после подключения к БД.
Вот такая функция:
отобразить в, например, DataGridView. Просто интересно, может там уже есть готовая информация.
И вот тут почитай про метод, которым получаешь информацию. Там можно задавать ограничения как-то.
В общем, кому интересно, вот как я реализовал открытие нужного листа Excel. Рекомендую весь этот код записать в отдельный модуль, чтобы не путать с другими процедурами. У меня этот модуль называется mdlExcel:
Пользуйтесь кому надо.
Помощь в написании контрольных, курсовых и дипломных работ здесь.
При создании нового листа произвести проверку на существование вводимого имени листа в текущей книге EXCEL
При создании нового листа, произвести проверку на существование вводимого имени листа в текущей.
Как задать условие проверки того, что вводимое название листа уже есть в этой книге? (Да-да, я не шучу)
Простецкую лабу по VBA нужно усложнить тем, чтобы в случае того, что при создании листа расчета и.