Как отразить столбец с объединенными ячейками?

Question

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

Это заставляет объединенную сетку выглядеть следующим образом:

Это потому, что сетка ссылается: (F1 содержит имя листа)

=INDIRECT(CONCATENATE("'",F$1,"'!F12"))
=INDIRECT(CONCATENATE("'",F$1,"'!F13"))
=INDIRECT(CONCATENATE("'",F$1,"'!F14"))
=INDIRECT(CONCATENATE("'",F$1,"'!F15"))
=INDIRECT(CONCATENATE("'",F$1,"'!F16"))

Тогда как ячейки проекта теперь упорядочены следующим образом:

F12
F13
F14
F16
F17

Я просто ищу основные идеи о том, как решить эту проблему. Имейте в виду, что задачи проекта с несколькими строками не всегда будут одинаковыми.

Answer 1

Вы обнаружили один из (многих) недостатков использования объединенных ячеек. Любые формулы, которые используют ссылки на ячейки в последовательности, потерпят неудачу, когда участвуют объединенные ячейки, поскольку адрес объединенной ячейки использует только верхнюю левую ячейку для ссылки на весь объединенный диапазон. Ссылки на ячейки, содержащиеся в объединенном диапазоне, вернут ноль.

Вот как работают объединенные клетки.

Вам нужно будет изменить формулу, чтобы она искала значение на основе имени задачи в столбце Веха на листе проекта. Это также означает, что вы не можете иметь повторяющиеся имена задач, потому что будет возвращена только первая совпавшая задача, но вы можете продолжать использовать объединенные ячейки.

Рассмотрим следующий скриншот:

Формула в ячейке B2 и скопирована

=INDEX(INDIRECT("'"&$F$1&"'!F:F"),MATCH(A2,INDIRECT("'"&$F$1&"'!E:E"),0))

Отрегулируйте ссылки на ячейки, как требуется.