Определяем имя листа в EXCEL
history 4 апреля 2013 г.
- Группы статей
- Имя листа, книги, файла
- Действия с листом
Определим имя листа с помощью функции ЯЧЕЙКА() .
Имя листа можно определить с помощью функции ЯЧЕЙКА() , записав формулу (см. файл примера ): =ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(ЯЧЕЙКА("имяфайла"))-ПОИСК("]";ЯЧЕЙКА("имяфайла")))
Если формула вводится в новую книгу, то ее сначала нужно сохранить. После сохранения, возможно, потребуется нажать клавишу F9 (Вкладка Формулы , Группа Вычисление , Пересчет ).
Формула вернет имя листа, содержащего ячейку с вышеуказанной формулой. Если требуется определить имя другого листа, то можно использовать туже функцию ЯЧЕЙКА() , но с аргументом "адрес".
Для этого нужно, чтобы второй аргумент содержал ссылку на ячейку другого листа, имя которого и будет определено. Если формула =ЯЧЕЙКА("адрес";лист2!A1) находится на листе1 в ячейке B1 , то имя листа ( Лист2 ) можно определить по формуле:
В этом случае имя листа не должно состоять только из цифр, т.к. если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах (‘) и формула вернет неправильный результат. Справиться с этим поможет формула =ПОДСТАВИТЬ(C1;"’";"") , которая удалит символ апострофа.
Также предполагается, что имя файла не содержит символа закрывающая квадратная скобка ( ] ).
ВНИМАНИЕ ! Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 ( Формулы/ Вычисления/ Пересчет ).
ПРИМЕНЕНИЕ: При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ() , в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1") . В статье Определение имени листа для использования в функции ДВССЫЛ() показано как использовать функцию ЯЧЕЙКА() , чтобы сохранить работоспособность формулы с функцией ДВССЫЛ() .
Имя листа в ячейке формулой в Excel
Разберем несколько вариантов добавления имени листа в ячейку в Excel с помощью формул.
Формула имени листа в Excel
Чтобы присвоить ячейке имя листа в Excel можно воспользоваться следующей формулой:
=ПСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))+1;255)
Давайте по шагам разберем принцип действия формулы имени листа.
Шаг 1. Функция ЯЧЕЙКА
Функция ЯЧЕЙКА позволяет получить данные о содержимом ссылки, в том числе и имя файла.
В данном случае формула ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ) позволяет получить полный путь файла Excel на локальном диске:
Как мы видим название листа идет сразу после названия файла, обрамленного в квадратные скобки.
Шаг 2. Функция ПОИСК
Таким образом для извлечения имени листа необходимо найти символ закрывающейся квадратной скобки (]) с помощью функции ПОИСК, которая возвращает позицию первого вхождения искомого элемента:
Прибавляя к результату 1, мы получаем позицию с которой начинается имя листа.
Шаг 3. Функция ПСТР
После нахождения квадратной скобки нам достаточно извлечь из полного названия файла правую часть, применив функцию ПСТР.
Данная функция возвращает заданное количество знаков, начиная с указанной позиции.
Так как точное количество символов в имени листа неизвестно, то в качестве последнего аргумента функции ПСТР указываем заведомо большее число, чем длина имени листа (подойдет любое число больше 31 — максимальная длина названия листа).
Альтернативная формула
Чтобы вставить название листа в ячейку можно воспользоваться альтернативной формулой:
=ПРАВСИМВ(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 );ДЛСТР(ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 ))-ПОИСК(«]»;ЯЧЕЙКА(«ИМЯФАЙЛА»; A1 )))
Отличие от предыдущего варианта заключается в использовании функции ПРАВСИМВ, которая возвращает указанное количество знаков с конца текста.
Описание используемых функций
Функция ЯЧЕЙКА:
ЯЧЕЙКА(тип_сведений; [ссылка])
Возвращает сведения о форматировании, адресе или содержимом первой ячейки ссылки.
- Тип сведений(обязательный аргумент) — текстовое значение, задающее тип сведений о ячейке (например, адрес, столбец, цвет, имяфайла, формат, скобки и т.д.);
- Ссылка(необязательный аргумент) — ячейка, по которой возвращаются данные.
Функция ПОИСК:
ПОИСК(искомый_текст; просматриваемый_текст; [начальная_позиция])
Возвращает позицию первого вхождения знака или строки текста (при чтении слева направо, прописные и строчные буквы не различаются).
- Искомый текст(обязательный аргумент) — искомый текст
- Просматриваемый текст(обязательный аргумент) — текст, по которому производится поиск;
- Начальная позиция(необязательный аргумент) — номер знака в просматриваемом тексте, с которого следует начать поиск.
Функция ПСТР:
ПСТР(текст; начальная_позиция; число_знаков)
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.