MS Excel: добавление имени листа в ячейку без использования макроса

Question

У меня есть рабочая тетрадь с несколькими листами, и я хочу иметь отдельный лист для отображения элементов из предыдущих листов. Итак, у меня есть листы с надписями "Столы", "Стулья", "Полки" и "Результаты". На каждом листе мебели хранится информация о количестве сломанных предметов, а на листе "Результат" отображается вся информация.

На каждом листе мебели есть столбец "Статус", который указывает, поврежден ли предмет мебели или нет, и я использую COUNTIF() для суммирования результатов, например:

Furniture | Number of broken
-----------------------------
Chairs    | =COUNTIF(Chairs!G:G, "Broken")

Это дает мне количество сломанных предметов из листа "Стулья". Однако, если я переименую лист "Стулья" в другое, я также должен переименовать ячейку "Стулья" в "Мебель". COUNTIF() автоматически переименовывает ссылочный лист, и я ищу аналогичные функции для отображения только имени листа в ячейке.

Я нашел эту функцию:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

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

Answer 1

Под Мебель вы можете создать список имен листов:
Создайте Определить Имя, назовите его SheetNames
В относится к записи: =GET.WORKBOOK(1)&T(NOW())
В примере A2 в листе результатов под Мебель напишите следующее:
=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

и перетащите его вниз, чтобы получить все листы, при каждом изменении имени листа оно будет отображаться в столбце
Если вы получите #REF! это означает, что больше нет листов
Теперь вам нужно использовать Indirect в countif для отображения имени листа:

=COUNTIF(INDIRECT(A2&"!G:G"),"Broken")
и вы можете перетащить его
INDIRECT(A2&"!G:G") является эквивалентом Chairs!G:G")

Answer 2

Ясс,

Ваша формула будет работать нормально, с модификацией:

=MID(CELL("filename",chairs!A1),FIND("]",CELL("filename",chairs!A1))+1,255)

Функция CELL возвращает информацию о ячейке, указанной во втором параметре. Так что изменив его с А1 на стулья!A1, информация поступает из рабочего листа стульев, а не по умолчанию, который является рабочим листом, в который вводится формула.