Названия листов в Эксель на этом листе

Весь MS Office, программирование на Visual Basic for Applications и MS VB

Модератор: Naeel Maqsudov

Ответить
Brat2005
Сообщения: 61
Зарегистрирован: 26 сен 2005, 14:33

24 окт 2005, 14:36

Можно ли с помощью формулы сделать так, чтобы в одной из ячеек на листе было название этого листа, а при переименовании его (листа), оно (название) соответственно менялось в этой ячейке?
PC
Сообщения: 29
Зарегистрирован: 04 май 2005, 10:32
Откуда: Moscow

24 окт 2005, 15:06

Да, можно.
Функция ЯЧЕЙКА("filename";A1) дает полную информацию о файле, включая название листа.
PC
Сообщения: 29
Зарегистрирован: 04 май 2005, 10:32
Откуда: Moscow

24 окт 2005, 15:22

Если Вам нужно чтобы отображалось только название листа, можно использовать несколько функций.
=ПСТР(ЯЧЕЙКА("filename";A1);НАЙТИ("xls";ЯЧЕЙКА("filename";A1))+4;100)

Если кто-то знает как это сделать проще, будет просто здорово! :)
Brat2005
Сообщения: 61
Зарегистрирован: 26 сен 2005, 14:33

24 окт 2005, 15:23

Да, но немного не так )
ПСТР(ЯЧЕЙКА("filename";A1);НАЙТИ("]";ЯЧЕЙКА("filename";A1))+1;32)
Это как раз и будет имя листа )
Brat2005
Сообщения: 61
Зарегистрирован: 26 сен 2005, 14:33

24 окт 2005, 15:24

) блиннн.... вместе сказали )
просто ищется не 3 буквы, а 1 символ, а так все один к одному! )
и еще не "100", а всего лишь 32, т.к. длина имени листа, по моим данным, не более 31 символа (1 взят с "запасом")
:)
pashulka
Сообщения: 831
Зарегистрирован: 24 ноя 2004, 03:46
Контактная информация:

25 окт 2005, 00:03

Полагаю будет уместным добавить, что все вышеопубликованные формулы можно использовать только в сохранённой рабочей книге, так как, до тех пор пока Вы не сохраните свою новую рабочую книгу, =ЯЧЕЙКА("ИмяФайла";A1) будет = "" и следовательно вместо имени рабочего листа Вы получите значение ошибки.
tolikt
Сообщения: 93
Зарегистрирован: 29 окт 2005, 12:33
Откуда: NewVasюbirsk

29 окт 2005, 13:16

В модуль VBA своей книги (или книги Personal.xls) вставить код:

Function ИмяЛиста(ByRef MyRange As Range) As String
Application.Volatile
ИмяЛиста = MyRange.Parent.Name
End Function

и в нужной ячейке (напр., А1) вставить формулу:
=ИмяЛиста(A1)
или
=PERSONAL.XLS!ИмяЛиста(A1)


Плюсы такого способа:
- работает в несохранённой книге
- сама формула выглядит короче (обычно имя листа нужно не просто само по себе, а в составе сложной формулы; и нагромождение лишних функций может только лишний раз запутать)
- работает более корректно, т. к. если формула =ПСТР(ЯЧЕЙКА("filename";A1);НАЙТИ("]";ЯЧЕЙКА("filename";A1))+1;32) ссылается на книгу, которая находится в папке с экзотическим названием типа "My]Folder", то имя листа отобразится неверно.

Минусы (при переносе книги на другой компьютер):
- если функция в модуле VBA самой книги, то при открытии книги появляется предупреждение о наличии макросов, и пользователь, который не в курсе дела, может отказаться от выполнения макросов и в ячейке появляется ошибка #ИМЯ?
- если функция в модуле VBA книги Personal.xls, то при переносе книги на другой компьтер, где в его Personal.xls нет данной функции, появляется ошибка #ИМЯ? (как вариант - в другом Personal.xls функция ИмяЛиста() есть, но она совсем другая :-), тогда и результат будет не тот, что ожидался)
pashulka
Сообщения: 831
Зарегистрирован: 24 ноя 2004, 03:46
Контактная информация:

29 окт 2005, 14:49

Что касается так называемых минусов получения имени, с помощью формул, т.е. без применения VBA :
- получить имя рабочего листа можно даже в несохранённой книги, правда с помощью других функций.
- длина формулы … но это вообще несерьёзно :P , даже если подобная формула входит в состав "сложной" формулы, то ничто не мешает "хранить" её в отдельной ячейке и ссылаться на эту ячейку. Этот вариант удобен тем, что позволяет визуально определить корректность определения имени. Ну а если вариант с дополнительной ячейкой неприемлен, то можно использовать именованную формулу и решить проблему так называемой длиной формулы.
- что касается корректности определения имени, то можно найти последний символ ], можно искать расширение .xl (см. пример PC, хотя этот вариант всё-таки может возвратить некорректный результат) или использовать другие функции, которым в принципе не нужно искать этот символ.

Что касается пользовательской функции, то можно получить имя рабочего листа, без использования ячейки в качестве аргумента (естественно, что ActiveSheet.Name даже не рассматривается)
Nasty4
Сообщения: 1
Зарегистрирован: 12 дек 2017, 16:02

12 дек 2017, 16:10

Подскажите, пожалуйста. У меня похожий вопрос. Вот формула =СЧЁТЕСЛИ('1'!$K$4:$K$958;0) и есть 50 листов под названием от 1 и до 50. Что можно сделать, чтоб вручную не вводить 1, 2, 3, и так до 50.
pashulka
Сообщения: 831
Зарегистрирован: 24 ноя 2004, 03:46
Контактная информация:

13 дек 2017, 18:51

Nasty4, Если имена листов могут меняться, то выведите их(имена листов) с помощью формул, например, в столбце A (начиная с первой ячейки, т.е. A1), а затем в столбце B введите, например :

Код: Выделить всё

=СЧЁТЕСЛИ(ДВССЫЛ("'"&A1&"'!$K$4:$K$958");0)
а общее количество посчитайте с помощью функции =СУММ(), и в результате Вы получите небольшую таблицу, где будет наглядно видно количество 0 по каждому из листов и их общую сумму.

Если же наглядность не нужна, то, imho, проще написать пользовательскую функцию или макрос.
Ответить