Как вставить название листа в ячейку excel
Функция ЛИСТ в Excel возвращает числовое значение, соответствующее номеру листа, на который указывает ссылка, переданная функции в качестве параметра.
Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса
Функция ЛИСТЫ в Excel возвращает числовое значение, которое соответствует количеству листов, на которые предоставлена ссылка.
- Обе функции полезны для использования в документах, содержащих большое количество листов.
- Лист в Excel – это таблица из всех ячеек, отображаемых на экране и находящихся за его пределами (всего 1 048 576 строк и 16 384 столбца). При отправке листа на печать он может быть разбит на несколько страниц. Поэтому нельзя путать термины «лист» и «страница».
- Количество листов в книге ограничено лишь объемом ОЗУ ПК.
Функция ЛИСТ имеет в своем синтаксисе всего 1 аргумент и то не обязательный для заполнения: =ЛИСТ(значение).
- значение – необязательный аргумент функции, который содержит текстовые данные с названием листа либо ссылку, для которой требуется установить номер листа. Если данный параметр не указан, функция вернет номер листа, в одной из ячеек которого она была записана.
Функция листы имеет следующий синтаксис: =ЛИСТЫ(ссылка).
- ссылка – объект ссылочного типа, для которого требуется определить количество листов. Данный аргумент является необязательным. Если данный параметр не указан, функция вернет количество листов, содержащихся в книге, на одном из листов которой она была записана.
Как получить имя листа формулой в Excel
Пример 1. При выполнении расчетной работы студент использовал программу Excel, в которой создал книгу из несколько листов. Для собственного удобства, студент решил в ячейках A2 и B2 каждого листа выводить данные о названии листа и его порядковом номере соответственно. Для этого он использовал следующие формулы:
Описание аргументов для функции ПСТР:
- ЯЧЕЙКА("имяфайла") – функция, возвращающая текст, в котором функция ПСТР выполняет поиск заданного количества символов. В данном случае вернется значение «C:\Users\soulp\Desktop\[ЛИСТ_ЛИСТЫ.xlsx]Статические расчеты», где после символа «]» находится искомый текст – название листа.
- НАЙТИ("]";ЯЧЕЙКА("имяфайла"))+1 – функция, возвращающая номер позиции символа «]», единица добавлена с той целью, чтобы функция ПСТР не учитывала символ «]».
- 31 – максимальное количество символов в названии листа.
=ЛИСТ() – данная функция без параметра вернет номер текущего листа. В результате ее вычисления получим количество листов в текущей книге.
Примеры использования функции ЛИСТ и ЛИСТЫ
Пример 2. В книге Excel содержится несколько листов. Необходимо:
- Вернуть номер текущего листа.
- Вернуть номер листа с названием «Статические_расчеты».
- Вернуть номер листа «Динамические_расчеты», если его ячейка A3 содержит значение 0.
Внесем данные в таблицу:
Далее составим формулы для всех 4-х условий:
- для условия №1 используем следующую формулу: =ЛИСТ()
- для условия №2 введем формулу: =ЛИСТ("Статические_расчеты")
- для условия №3 запишем формулу:
Функция ЕСЛИ выполняет проверку условия равенства значения, хранящегося в ячейке A3 листа Динамические_расчеты, нулю или пустому значению.
В результате получим:
Обработка информации о листах книги по формуле Excel
Пример 3. В книге Excel содержится несколько листов. Необходимо определить общее количество листов, а также число листов, содержащихся между листами «Статические_расчеты» и «Экономические_расчеты».
Исходная таблица имеет вид:
Общее количество листов вычислим по формуле:
Для определения количества листов, содержащихся между двумя указанными листами, запишем формулу:
- Статические_расчеты:Экономические_расчеты!A2 – ссылка на ячейку A2 диапазона листов между «Статические_расчеты» и «Экономические_расчеты» включая эти листы.
- Для получения искомого значения было вычтено число 2.
В результате получим следующее:
Формула выводит подробную информацию по данным о листах в определенном диапазоне их расположения в рабочей книге Excel.
Есть ли простой способ вставить имя текущего листа в одну ячейку? Как вставить имя всех листов в ячейки? В этой статье вы найдете хитрые методы решения этих проблем.
Быстро вставьте имя активного листа с помощью Kutools for Excel
Быстро вставляйте все имена листов с гиперссылками в ячейки в виде индекса
Быстро вставить имя текущего листа в ячейку с функциями
Просто введите формулу = ПРАВЫЙ (ЯЧЕЙКА ("имя файла", D2), LEN (ЯЧЕЙКА ("имя файла", D2)) - НАЙТИ ("]", ЯЧЕЙКА ("имя файла", D2))) в любую камеру и нажмите Enter key, в ячейке отображается имя текущего листа.
Эта формула может отображать только имя текущего листа, но не имя другого листа.
Быстро вставляйте все имена листов в ячейки с помощью VBA
Если вы хотите вставить все имена листов в ячейки, макрос VBA - хороший выбор.
Шаг 1: Удерживайте другой + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
Шаг 2: нажмите Вставить > Модульи вставьте следующий макрос в окно модуля.
VBA для вставки имен всех листов в ячейки:
Sub SheetNames ()
Колонны (1) .Вставить
Для i = 1 To Sheets.Count
Cells (i, 1) = Sheets (i). Name
Затем я
End Sub
Шаг 3: нажмите F5 ключ для запуска этого макроса. Затем вы увидите, что имена всех листов будут указаны в столбце A текущего листа. Смотрите скриншот:
Внимание: В коде VBA вы можете изменить Ячейки (i, 1) к другой ссылке, чтобы вставить имена листов, начинающиеся с других ячеек. Например, вставьте имена листов, начинающиеся с C3, измените его на Ячейки (i + 2, 3) .
Быстро вставьте имя активного листа с помощью Kutools for Excel
Если вы хотите вставить информацию об активном листе, включая имя листа, имя книги, путь к файлу и т. Д., В ячейку или верхний / нижний колонтитул, вы можете использовать Kutools for ExcelАвтора Вставить информацию о книге.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите ячейку или диапазон, в который нужно поместить имя листа, и щелкните Kutools Plus > Workbook > Вставить информацию о книге.
2. Затем выберите информацию из книги, которую нужно вставить, из Информация раздел и укажите место, из которого вы хотите разместить информацию Вставить в раздел. Затем нажмите OK.
Вставить информацию о книге в ячейку / верхний / нижний колонтитул
Быстро вставляйте все имена листов с гиперссылками в ячейки в виде индекса
Kutools for Excel's Создать список имен листов Утилита не только вставляет все имена листов в ячейки, но также вставляет гиперссылки на соответствующие листы.
После бесплатная установка Kutools for Excel, сделайте следующее:
Шаг 1: нажмите Kutools Plus > Рабочий лист > Создать список имен листов.
Шаг 2: в Создать список имен листов диалоговом окне, укажите настройки в соответствии с вашими потребностями и нажмите OK.
Затем вы увидите, что все имена листов вставлены в новый лист, и каждое имя листа ссылается на соответствующий лист. См. Следующие скриншоты:
Освободи Себя Создать список имен листов Утилита упрощает создание списка всех имен рабочих листов активной книги на новом листе, который содержит гиперссылки или кнопки макросов для быстрого перехода к другим листам. Щелкните, чтобы узнать больше об этой утилите.
Задача: записать в отдельной ячейке или внутри формулы имя текущего листа(т.е. того, в котором сама функция).
В принципе это очень легко сделать простейшей функцией пользователя:
Синтаксис:
получение имени листа, в котором записана функция:
=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);"[";"")
Функция ЛИСТ в Excel возвращает числовое значение, соответствующее номеру листа, на который указывает ссылка, переданная функции в качестве параметра.
Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса
Функция ЛИСТЫ в Excel возвращает числовое значение, которое соответствует количеству листов, на которые предоставлена ссылка.
- Обе функции полезны для использования в документах, содержащих большое количество листов.
- Лист в Excel – это таблица из всех ячеек, отображаемых на экране и находящихся за его пределами (всего 1 048 576 строк и 16 384 столбца). При отправке листа на печать он может быть разбит на несколько страниц. Поэтому нельзя путать термины «лист» и «страница».
- Количество листов в книге ограничено лишь объемом ОЗУ ПК.
Функция ЛИСТ имеет в своем синтаксисе всего 1 аргумент и то не обязательный для заполнения: =ЛИСТ(значение).
- значение – необязательный аргумент функции, который содержит текстовые данные с названием листа либо ссылку, для которой требуется установить номер листа. Если данный параметр не указан, функция вернет номер листа, в одной из ячеек которого она была записана.
Функция листы имеет следующий синтаксис: =ЛИСТЫ(ссылка).
- ссылка – объект ссылочного типа, для которого требуется определить количество листов. Данный аргумент является необязательным. Если данный параметр не указан, функция вернет количество листов, содержащихся в книге, на одном из листов которой она была записана.
Как получить имя листа формулой в Excel
Пример 1. При выполнении расчетной работы студент использовал программу Excel, в которой создал книгу из несколько листов. Для собственного удобства, студент решил в ячейках A2 и B2 каждого листа выводить данные о названии листа и его порядковом номере соответственно. Для этого он использовал следующие формулы:
Описание аргументов для функции ПСТР:
- ЯЧЕЙКА("имяфайла") – функция, возвращающая текст, в котором функция ПСТР выполняет поиск заданного количества символов. В данном случае вернется значение «C:\Users\soulp\Desktop\[ЛИСТ_ЛИСТЫ.xlsx]Статические расчеты», где после символа «]» находится искомый текст – название листа.
- НАЙТИ("]";ЯЧЕЙКА("имяфайла"))+1 – функция, возвращающая номер позиции символа «]», единица добавлена с той целью, чтобы функция ПСТР не учитывала символ «]».
- 31 – максимальное количество символов в названии листа.
=ЛИСТ() – данная функция без параметра вернет номер текущего листа. В результате ее вычисления получим количество листов в текущей книге.
Примеры использования функции ЛИСТ и ЛИСТЫ
Пример 2. В книге Excel содержится несколько листов. Необходимо:
- Вернуть номер текущего листа.
- Вернуть номер листа с названием «Статические_расчеты».
- Вернуть номер листа «Динамические_расчеты», если его ячейка A3 содержит значение 0.
Внесем данные в таблицу:
Далее составим формулы для всех 4-х условий:
- для условия №1 используем следующую формулу: =ЛИСТ()
- для условия №2 введем формулу: =ЛИСТ("Статические_расчеты")
- для условия №3 запишем формулу:
Функция ЕСЛИ выполняет проверку условия равенства значения, хранящегося в ячейке A3 листа Динамические_расчеты, нулю или пустому значению.
В результате получим:
Обработка информации о листах книги по формуле Excel
Пример 3. В книге Excel содержится несколько листов. Необходимо определить общее количество листов, а также число листов, содержащихся между листами «Статические_расчеты» и «Экономические_расчеты».
Исходная таблица имеет вид:
Общее количество листов вычислим по формуле:
Для определения количества листов, содержащихся между двумя указанными листами, запишем формулу:
- Статические_расчеты:Экономические_расчеты!A2 – ссылка на ячейку A2 диапазона листов между «Статические_расчеты» и «Экономические_расчеты» включая эти листы.
- Для получения искомого значения было вычтено число 2.
В результате получим следующее:
Формула выводит подробную информацию по данным о листах в определенном диапазоне их расположения в рабочей книге Excel.
Читайте также: