ссылки на листы по номеру вместо названия в ячейках

Скажем sheet3.name = "d"

Есть ли способ поместить в ячейку на sheet2 формулу =sum(sheet3!b:b), где sheet3 заменяется фактическим именем листа 3?

Пока я могу заставить работать только =sum('d'!b:b).

Я мог бы использовать VBA для этого, вероятно, но мне любопытно, как это сделать в ячейке, чтобы мне не приходилось запускать макрос каждый раз.

3 6
excel vba excel-formula

3 ответа:

Если вы можете использовать пользовательскую функцию UDF , которая вернет имя листа 

Function SHEETNAME(number As Long) As String
    SHEETNAME = Sheets(number).Name
End Function

Тогда формула, подобная 

=SUM(INDIRECT(SHEETNAME(3) &"!B:B"))

Вернет сумму из столбца B на листе 3.

SHEETNAME(number) возвращает имя листа числа, которое является индексом.

So Sheet(1) возвращает Sheet1, etc

5

Используйте приведенную ниже формулу в любом месте листа, чтобы получить имя листа-лист должен иметь имя файла для этого: 

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

Вы можете либо ссылаться на эту ячейку, используя косвенное:

=SUM(Indirect("'"&A1&"'!B:B"))

Или, если вы не хотите иметь вторую ячейку, вы можете объединить две формулы в одну: 

=SUM(INDIRECT("'"&REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")&"'!B:B"))
6

Я не уверен, что это хорошая идея, но это первое, что мне пришло в голову.

Я бы добавил дополнительную функцию к вашему проекту VBA, которая вернет фактическое имя вашего листа 3: 

Function Sheet3Name()
    Sheet3Name = Sheet3.Name
End Function

Далее, когда вы создаете формулу суммы столбца B:B в ячейке Excel, вам нужно сделать это следующим образом: 

=SUM(INDIRECT(Sheet3Name()&"!A:A"))
1