Имя предыдущего листа
Иногда возникают ситуации, когда необходимо знать имя листа(текущего или на котором ссылка) - об этом я уже писал в статье Как получить имя листа формулой. Но куда сложнее ситуация с получением имени предыдущего листа. Т.е. имя того листа, который расположен перед тем листом, на котором записана формула. Зачем это может потребоваться? Например, если в книге ведут учет касс и каждый лист это отдельная дата. И в определенной ячейке необходимо получить данные по остаткам из предыдущего листа(дня).
Стандартные формулы(та же ЯЧЕЙКА
А значит обычные формулы нам в автоматизации не помогут - такое возможно только через 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(
Функция будет исправно работать даже если предыдущий лист будет удален - ведь она непосредственно на него никак на завязана.
Однако, сама по себе функция в таком виде не очень практична - она ничего не дает, кроме имени листа. Чтобы получить с её помощью значение конкретной ячейки предыдущего листа(пусть это будет ячейка "А1"), необходимо использовать функцию преобразования текстового представления адреса в реальный -
Но даже в связке с
'--------------------------------------------------------------------------------------- ' 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 из предыдущего листа:
Если надо получить из предыдущего листа значение ячейки, адрес которой равен адресу ячейки, в которую введена сама функция:
Если функция записана на первом по порядку листе, то результатом будет значение ошибки
Значение из предыдущего листа нужно получать куда чаще, чем из следующего. Но раз есть одно - нужно и другое. Функции ниже получают имя и значение ячейки следующего листа:
'--------------------------------------------------------------------------------------- ' 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, 623 скачиваний)
Так же см.:
Как получить имя листа формулой
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
