Функция ЯЧЕЙКА в Excel: имя файла, лист и адрес
Что умеет ЯЧЕЙКА, как с её помощью вывести имя листа и путь к файлу, и все двенадцать значений аргумента — на русском и на английском.
Коротко
=ЯЧЕЙКА(тип_сведений;[ссылка]) возвращает сведения о ячейке: её адрес, номер строки, формат, защищена ли она — или путь к самому файлу. Тип сведений пишется в кавычках: =ЯЧЕЙКА("адрес";B5) даст $B$5.
Всегда указывайте второй аргумент. Без него функция берёт не свою ячейку, а последнюю изменённую — и результат начинает «прыгать» при каждой правке листа.
На практике функцию берут ради одного: вывести в ячейку имя листа или путь к файлу, чтобы они попали в заголовок отчёта или колонтитул таблицы. Готовые формулы — ниже.
У несохранённой книги "имяфайла" возвращает пустую строку: пути ещё нет. Сохраните файл, и формула заработает.
То же самое формулой в Excel
=ЯЧЕЙКА("имяфайла";A1) — полный путь, имя книги в квадратных скобках и имя листа=ПСТР(ЯЧЕЙКА("имяфайла";A1);НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1))+1;31) — только имя листа — всё, что после закрывающей скобки=ПСТР(ЯЧЕЙКА("имяфайла";A1);НАЙТИ("[";ЯЧЕЙКА("имяфайла";A1))+1;НАЙТИ("]";ЯЧЕЙКА("имяфайла";A1))-НАЙТИ("[";ЯЧЕЙКА("имяфайла";A1))-1) — только имя книги с расширением=ЛЕВСИМВ(ЯЧЕЙКА("имяфайла";A1);НАЙТИ("[";ЯЧЕЙКА("имяфайла";A1))-1) — только папка, где лежит файл=ЯЧЕЙКА("защита";B5) — 1 — ячейка будет заблокирована при защите листа, 0 — останется открытой=ЕСЛИ(ЯЧЕЙКА("тип";B5)="b";"пусто";"заполнено") — проверить, пустая ли ячейка на самом делеПошагово
Имя листа в заголовке отчёта
- Сохраните книгу — без этого путь пустой.
- В ячейку заголовка вставьте вторую формулу блока. Ссылка
A1в ней нужна, чтобы функция брала свой лист, а не последний изменённый. - Скопируйте лист или переименуйте его — заголовок поменяется сам после пересчёта (F9).
Проверить, какие ячейки открыты для ввода. Перед защитой листа удобно поставить рядом =ЯЧЕЙКА("защита";B5) и протянуть: нули — ячейки, с которых снята галочка «Защищаемая ячейка».
Где обычно ломается
- Имя листа показывается чужое. Не указан второй аргумент: без ссылки функция отвечает про последнюю изменённую ячейку, которая могла быть на другом листе.
- Пусто вместо пути. Книга ни разу не сохранялась или открыта в Excel в интернете, где «имяфайла» не поддерживается.
- #ЗНАЧ! при вводе типа. Опечатка в названии аргумента. В русском Excel —
"имяфайла"слитно, в английском —"filename". - Результат не обновился после переименования листа. ЯЧЕЙКА пересчитывается не при каждом действии — нажмите F9.
- В Google Таблицах функция есть, но поддерживает не все типы сведений: пути к файлу там нет — у таблицы нет папки на диске.
Все значения аргумента
| Русский Excel | Английский | Что возвращает |
|---|---|---|
"адрес" | "address" | адрес ячейки текстом, например $A$1 |
"столбец" | "col" | номер столбца |
"цвет" | "color" | 1, если у ячейки формат с цветом для отрицательных чисел |
"содержимое" | "contents" | значение левой верхней ячейки, не формула |
"имяфайла" | "filename" | полный путь, имя книги и имя листа |
"формат" | "format" | код числового формата ячейки |
"скобки" | "parentheses" | 1, если положительные числа показываются в скобках |
"префикс" | "prefix" | знак выравнивания текста: апостроф, крышка и так далее |
"защита" | "protect" | 1 — ячейка защищаемая, 0 — открыта для ввода |
"строка" | "row" | номер строки |
"тип" | "type" | b — пустая, l — текст, v — всё остальное |
"ширина" | "width" | два значения: ширина столбца и признак, стандартная ли она |
В Excel в интернете не поддерживаются «цвет», «имяфайла», «формат», «скобки», «префикс», «защита» и «ширина».