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

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

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

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

'---------------------------------------------------------------------------------------
' 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")), то функция вернет полный путь с именем того листа, который активен в данный момент(даже если это лист другой книги).

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

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


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

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


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

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

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2018 Excel для всех   Войти