Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

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

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

'---------------------------------------------------------------------------------------
' 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 ссылки статистика
Обсуждение: 3 комментария
  1. Денис:

    Можно выписать имя листа из ссылки на лист
    =ЗАМЕНИТЬ(ЗАМЕНИТЬ(ТЕКСТ(Ф.ТЕКСТ(ссылка_на_лист);0);НАЙТИ("!";ТЕКСТ(Ф.ТЕКСТ(ссылка_на_лист);0);1);ДЛСТР(ТЕКСТ(Ф.ТЕКСТ(ссылка_на_лист);0));"");1;1;"")

    • Денис, с виду функция куда сложнее читается и при этом не на всех версиях будет работать, т.к. Ф.ТЕКСТ появилась только начиная с 2013 Excel, если мне не изменяет память.

  2. Oleksii:

    Длинная, конечно, формула, но работает:
    =ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа]);ДЛСТР(ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа]))-ПОИСК("]";ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа])));ПОИСК("'";ПРАВСИМВ(ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа]);ДЛСТР(ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа]))-ПОИСК("]";ЯЧЕЙКА("адрес";[ссылка_на_ячейку_нужного_листа]))))-1)

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти