У каждого листа есть название. В одной книге Excel может быть 256 листов. Как использовать имя листа в Excel, чтобы упростить расчеты для множества листов? Например, вам нужно собрать сумму определенного столбца формулой со всех листов, как использовать имя листа в Excel для сбора данных? Ниже предлагаю описание этой возможности табличного редактора.
Имя листа Excel в расчетах
Конкретный пример. Есть два листа с данными по годам Сумма1 и Сумма2, необходимо посчитать сумму по этим листам. При этом необходимо использовать имя листа в формуле. Т.е. если добавить любое количество листов, была бы возможность быстро рассчитать эту сумму.
Для таких расчетов существует замечательная функция =ДВССЫЛ()
По факту в ней необходим один аргумент — ссылка на ячейку. Есть еще необязательный аргумент для определения типа ссылок (R1C1 или A1 — по умолчанию A1).
Чтобы указать имя листа в аргументе (ссылку), нужно воспользоваться возможностью сцепить. Разберем на примере
=ДВССЫЛ(C$2&"!b2")
C$2 — ячейка в которой записано имя листа, на который ссылаемся (в нашем случае Сумма1 и Сумма2 )
& — символ сцепки
«» — значит, что внутри ячеек воспринимается Excel как текст
!b2 — текстовое значение той ячейки, на которую ссылаемся
Решение примера выше можно посмотреть в примере.
Вернуть имя листа в Excel в ячейку
Проще всего, наверное, макросом. Создаем макрос и вносим в него такой текст:
Sub ShName() Range("A1") = ActiveSheet.Name End Sub
Т.е. ячейке A1 мы этим макросом присваиваем имя листа (не забудьте запустить макрос)
Как говорил выше, я больше работаю с формулами и функциями, т.к. они доступнее для простого пользователя, которых большинство. Наверное, лучше разделить создание формулы на несколько этапов, т.к. она получится громоздкой
=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(ЯЧЕЙКА("имяфайла"))-ПОИСК("]";ЯЧЕЙКА("имяфайла");1))
Мы используем функцию ПРАВСИМВ для того, чтобы отделить нужное количество символов в возвращенном полном имени файла при помощи ЯЧЕЙКА(«имяфайла») — например, C:\Users\[Имя листаxlsb]Лист1
Далее мы считаем количество ячеек, которых нужно оделить справа — т.е. находим полную длину пути файла (ДЛСТР(ЯЧЕЙКА(«имяфайла»))) и вычитаем из нее количество символов до знака ]
В итоге получаем, что из текста C:\Users\[Имя листаxlsb]Лист1 нам с правой стороны нужно отделить 5 символов — Лист1
Формулу можно ввести в любую ячейку, с этим не должно возникнуть проблем.
Имя файла в Excel
Так же можно вернуть в ячейке имя файла Excel
Формула будет иметь вид:
=ЯЧЕЙКА("имяфайла")
«имяфайла» — это именно такой аргумент, так и надо записывать.
Комментарии:
( 4 Comments )
Чем отличается тип ссылок r1c1 и а1? Какая между ними разница? И как поменятъ r1c1 на а1?
Зачем в формуле в текстовом значении ячейки, на которую ссылаемся, восклицательный знак? Без него формула не работает…
Подробнее можно прочитать в статье https://excelworks.ru/2015/10/16/ssulki/
Восклицательный знак — это символ того что используется имя листа. Он всегда ставиться перед именем ячейки, если в формула ссылается на другой лист.
Например, =Лист2!B2