Excel имя листа как переменная

Хитрости »

4 Март 2015              53350 просмотров

Как получить имя листа формулой

Задача: записать в отдельной ячейке или внутри формулы имя текущего листа(т.е. того, в котором сама функция).

В принципе это очень легко сделать простейшей функцией пользователя:

'---------------------------------------------------------------------------------------
' Procedure : GetShName
' DateTime  : 04.03.2015 10:44
' Author    : The_Prist(Щербаков Дмитрий)
'             http://www.excel-vba.ru
' Purpose   : Функция возвращает в ячейку имя листа
'             rCell - Необязательный аргумент.
'                     Если указан - функция вернет имя листа, на котором расположена эта ячейка
'                     Если не указан - функция вернет имя листа, в котором записана функция
'---------------------------------------------------------------------------------------
Function GetShName(Optional rCell As Range)
    If Not rCell Is Nothing Then
        GetShName = rCell.Parent.Name
    Else
        GetShName = Application.Caller.Parent.Name
    End If
End Function

Синтаксис:
получение имени листа, в котором записана функция:
=GetShName()
получение имени листа, в котором расположена указанная ячейка
=GetShName(A1) — данная запись равнозначна записи без ячейки, т.к. ячейка все равно в пределах листа с самой функцией
=GetShName(Лист2!A1)

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

=ПСТР(ЯЧЕЙКА(«filename»;A2);ПОИСК(«]»;ЯЧЕЙКА(«filename»;A2))+1;31)
=MID(CELL(«filename»,A2),SEARCH(«]»,CELL(«filename»,A2))+1,31)

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

Теперь разберем эту формулу поподробнее
Самая основная часть — ЯЧЕЙКА(«filename»;A2). Функция ЯЧЕЙКА (CELL) с записанным первым аргументом «filename» возвращает полный путь к книге, включая имя листа и адрес ячейки, в которой записана функция:
C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1
Т.к. нам нужно только имя листа — мы применяем ПСТР (MID), которая возвращает часть текста, начиная с указанной позиции символа. ПОИСК (SEARCH) ищет нам именно эту позицию — позицию символа «]».
Если по шагам просмотреть этапы работы формулы, то будет нечто вроде:
=ПСТР(ЯЧЕЙКА(«filename»;A2);ПОИСК(«]»;ЯЧЕЙКА(«filename»;A2))+1;31)
Шаг1 =>
=ПСТР(ЯЧЕЙКА(«filename»;A2);ПОИСК(«]»;C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1)+1;31)
Шаг2 =>
=ПСТР(ЯЧЕЙКА(«filename»;A2);49+1;31)
Шаг3 =>
=ПСТР(C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1;50;31)
Шаг4 =>
=Лист1
Первый момент: почему применяю цифру 31 последним аргументом ПСТР? По факту, там необходимо указывать точное количество символов, но если указать больше — то будут взяты все символы от указанного и до последнего. Т.е можно было бы указать и 99, но 31 — это максимальное количество символов, которое можно использовать в имени листа.
Второй момент: первым аргументом функции ЯЧЕЙКА указывается текст, обозначающий тип сведений. В русской локализации он доступен на русском — «имяфайла». Однако при открытии файла с этой функцией в другой локализации тип сведений не будет переведен и функция не сможет работать. Поэтому я указываю на английском, т.к. он является универсальным в данном случае и будет работать в любой локализации. Однако нет никакой ошибки, если указать на русском: ЯЧЕЙКА(«имяфайла»;A2)

Если вторым аргументом функции ЯЧЕЙКА ничего не указывать(=ЯЧЕЙКА(«filename»)), то функция вернет полный путь с именем того листа, который активен в данный момент(даже если это лист другой книги). Это всегда надо учитывать. Часто функцию используют для определения имени книги, в которой сама функция записана(например, для запросов Power Query — Относительный путь к данным PowerQuery). И в какой-то момент может получиться так, что ожидаете увидеть имя книги или листа с функцией, а получаете совсем другое.

Правда, у этой формулы есть свои недостатки: обязательно необходимо, чтобы книга была сохранена на диске. Это означает, что формула не сработает для книги, которая была только что создана и не сохранена. Связано это с ограничениями возможностей параметра «filename» функции ЯЧЕЙКА(CELL). Она не может получить путь к файлу, который еще не сохранен.
Функция пользователя(UDF) GetShName(приведенная в самом начале статьи) лишена этого недостатка.

Для чего вообще может быть нужно записывать имя листа в ячейку? Ну, например, если имя листа периодически меняется, а в своих формулах вы используете функции вроде ДВССЫЛ со ссылкой на этот лист. Либо для создания более наглядного оглавления через гиперссылки.

Кто-то уже явно догадался, что подобным же образом можно получить не только имя листа — но и имя книги:
=ПСТР(ЯЧЕЙКА(«filename»);ПОИСК(«[«;ЯЧЕЙКА(«filename»))+1;ПОИСК(«]»;ЯЧЕЙКА(«filename»))-ПОИСК(«[«;ЯЧЕЙКА(«filename»))-1)
так же как и для имени листа — можно указать ячейку из другой книги и тогда формула вернет имя той книги, из которой указана ячейка.
Если ячейка не указана — функция вернет имя активной в данный момент книги.

Так же можно получить полный путь к книге и имя книги(без квадратных скобок и имени листа):
=ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА(«filename»;A1);1;ПОИСК(«]»;ЯЧЕЙКА(«filename»;A1))-1);»[«;»»)

Так же см.:
Имя предыдущего листа

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

You’re saying this works?

Sheets("Blank (2)").name = sname

Then, do this:

With Sheets("Blank (2)")
    .Name = sname
    .Activate
    .Select
End With

The idea is to grab a reference to the worksheet you’re working with, instead of constantly fetching the object from the Sheets collection every time.

Alternatively:

Dim target As Worksheet
Set target = ThisWorkbook.Worksheets("Blank (2)")
target.Name = sname
target.Activate
target.Select

Note that the Sheets collection, unqualified as you’ve used it, implicitly refers to the active workbook. Assuming that active workbook is the same workbook the code is written in, it’s best to qualify the call with ThisWorkbook, and to use the Worksheets collection instead — because the Sheets collection can contain non-worksheet objects (e.g. charts).

Lastly, I doubt there’s any need to .Select anything here. Read more about avoiding .Select and .Activate here.