Забыли пароль?


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

Имя предыдущего листа

Иногда возникают ситуации, когда необходимо знать имя листа(текущего или на котором ссылка) - об этом я уже писал в статье Как получить имя листа формулой. Но куда сложнее ситуация с получением имени предыдущего листа. Т.е. имя того листа, который расположен перед тем листом, на котором записана формула. Зачем это может потребоваться? Например, если в книге ведут учет касс и каждый лист это отдельная дата. И в определенной ячейке необходимо получить данные по остаткам из предыдущего листа(дня).
Стандартные формулы(та же ЯЧЕЙКА(CELL)) могут это сделать, но в силу специфики получается так, что они бесполезны. В той же ЯЧЕЙКА для получения имени конкретного листа необходимо указать ссылку на ячейку на этом самом листе. Замкнутый круг. Ведь можно сразу указать эту ссылка. А если лист со ссылкой будет удален, то получим ошибку и ссылку надо будет перепрописывать заново.
А значит обычные формулы нам в автоматизации не помогут - такое возможно только через VBA. Лучше всего делать это при помощи функции пользователя(UDF). И главное, эта функция не так уж и сложна:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция возвращает имя листа, расположенного перед листом с функцией
'          Не требует аргументов
'          Если функция записана на первом по порядку листе - функция вернет ПУСТО
'---------------------------------------------------------------------------------------
Function GetPrevSheet_Name()
    'заставляем пересчитывать формулу при любом изменении
    Application.Volatile True
    Dim ws As Worksheet
    'получаем ссылку на лист, в котором записана формула
    Set ws = Application.Caller.Parent
    'если лист с формулой 2-ой или более по порядку
    If ws.Index > 1 Then
        'получаем имя предыдущего листа
        GetPrevSheet_Name = ws.Previous.Name
    Else
        GetPrevSheet_Name = vbNullString
    End If
End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций(Shift+F3), отыскав её в категории Определенные пользователем (User Defined Functions).
Синтаксис записи функции на лист:
=GetPrevSheet_Name()
Функция будет исправно работать даже если предыдущий лист будет удален - ведь она непосредственно на него никак на завязана.
Однако, сама по себе функция в таком виде не очень практична - она ничего не дает, кроме имени листа. Чтобы получить с её помощью значение конкретной ячейки предыдущего листа(пусть это будет ячейка "А1"), необходимо использовать функцию преобразования текстового представления адреса в реальный - ДВССЫЛ(INDIRECT):
=ДВССЫЛ("'"&GetPrevSheet_Name()&"'!A1")
=INDIRECT("'"&GetPrevSheet_Name()&"'!A1")

Формула не очень наглядна, хоть и не сложна. Самое непонятное здесь, наверное, это апострофы. Я добавил их до и после имени листа на тот случай, если в имени листа будут различные знаки препинания и другие "неугодные" именам листов символы(пробел в том числе). В этом случае апострофы обязательны, но они никак не мешают, если никаких символов нет.
Но даже в связке с ДВССЫЛ(INDIRECT) наиболее часто функция будет использоваться для получения значения одной конкретной ячейки(как в примере выше). И куда реже в составе каких-либо других функций(ИНДЕКС, ВПР и т.п.), требующих указания диапазонов. Поэтому было бы неправильно, если бы я не привел в статье функцию, которая сразу делает именно нужное - возвращает значение указанной ячейки из предыдущего листа:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция возвращает значение указанной ячейки на предыдущем листе
'          Если функция записана на первом по порядку листе - функция вернет ошибку ЗНАЧ!
'          Аргументы:
'              rc - ссылка на ячейку на текущем листе,
'                   по адресу которой необходимо получить значение из предыдущего листа
'---------------------------------------------------------------------------------------
Function GetPrevSheet_Value(Optional rc As Range)
    'заставляем пересчитывать формулу при любом изменении
    Application.Volatile True
    Dim rCell As Range
    If rc Is Nothing Then
        Set rCell = Application.Caller
    Else
        Set rCell = rc
    End If
    Dim ws As Worksheet
    'получаем ссылку на лист, в котором записана формула
    Set ws = Application.Caller.Parent
    'если лист с формулой 2-ой или более по порядку
    If ws.Index > 1 Then
        'получаем значение из предыдущего листа
        'на основании адреса указанной ячейки
        GetPrevSheet_Value = ws.Previous.Range(rCell.Address).Value
    Else
        GetPrevSheet_Value = CVErr(xlErrValue)
    End If
End Function

Используется функция так же, как и предыдущая.
Синтаксис записи на листе для получения значения ячейки А1 из предыдущего листа:
=GetPrevSheet_Value(A1)
Если надо получить из предыдущего листа значение ячейки, адрес которой равен адресу ячейки, в которую введена сама функция:
=GetPrevSheet_Value()
Если функция записана на первом по порядку листе, то результатом будет значение ошибки #ЗНАЧ!(#VALUE!)


Значение из предыдущего листа нужно получать куда чаще, чем из следующего. Но раз есть одно - нужно и другое. Функции ниже получают имя и значение ячейки следующего листа:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция возвращает имя листа, расположенного после листа с функцией
'          Не требует аргументов
'          Если функция записана на первом по порядку листе - функция вернет ПУСТО
'---------------------------------------------------------------------------------------
Function GetNextSheet_Name()
    'заставляем пересчитывать формулу при любом изменении
    Application.Volatile True
    Dim ws As Worksheet
    'получаем ссылку на лист, в котором записана формула
    Set ws = Application.Caller.Parent
    'если лист с формулой не последний
    If ws.Index < ws.Parent.Worksheets.Count Then
        'получаем имя предыдущего листа
        GetNextSheet_Name = ws.Next.Name
    Else
        GetNextSheet_Name = vbNullString
    End If
End Function
'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Функция возвращает значение указанной ячейки на предыдущем листе
'          Если функция записана на первом по порядку листе - функция вернет ошибку ЗНАЧ!
'          Аргументы:
'              rc - ссылка на ячейку на текущем листе,
'                   по адресу которой необходимо получить значение из предыдущего листа
'---------------------------------------------------------------------------------------
Function GetNextSheet_Value(Optional rc As Range)
    'заставляем пересчитывать формулу при любом изменении
    Application.Volatile True
    Dim rCell As Range
    If rc Is Nothing Then
        Set rCell = Application.Caller
    Else
        Set rCell = rc
    End If
    Dim ws As Worksheet
    'получаем ссылку на лист, в котором записана формула
    Set ws = Application.Caller.Parent
    'если лист с формулой не последний
    If ws.Index < ws.Parent.Worksheets.Count Then
        'получаем значение из предыдущего листа
        'на основании адреса указанной ячейки
        GetNextSheet_Value = ws.Next.Range(rCell.Address).Value
    Else
        GetNextSheet_Value = CVErr(xlErrValue)
    End If
End Function

Скачать файл пример:

  Имя предыдущего листа.xls (85,5 KiB, 41 скачиваний)

Так же см.:
Как получить имя листа формулой


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

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

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

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


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