Как динамически ссылаться на текст в таблице в формуле

На чтение 3 мин Опубликовано 09.10.2024

Вопрос или проблема

У меня есть две таблицы:

project_dashboard — это обзор всех моих запланированных, текущих и завершённых проектов

project_1 — это сводка всех шагов, которые потребовались для завершения 1 конкретного проекта и суммарных затрат

Сейчас я хочу использовать SUMIFS, чтобы сложить стоимость, указанную в таблице project_1, в таблице project_dashboard для быстрой сводки

НО

в будущем будет десятки проектов, и я хочу, чтобы одна формула динамически рассчитывала стоимость всех проектов по отдельности, основываясь на имени проекта, которое совпадает с именем таблицы project_1

—Имя ————- Стоимость

project_1——-SUMIFS(project_1[Price], project_1[Status], "Завершен")

project_2——-SUMIFS(project_2[Price], project_2[Status], "Завершен")

Я пробовал INDIRECT, но не смог заставить его работать с таблицей 🙁

Вы можете попробовать использовать следующую формулу:

=SUMIFS(
    INDIRECT(
        [@NAME] & "[Price]"
    ),
    INDIRECT(
        [@NAME] & "[Status]"
    ), "Завершен"
)

Ответ или решение

Чтобы динамически ссылаться на текст в таблице в Excel и суммировать значения с использованием функции SUMIFS, вам подойдет следующий подход с использованием функции INDIRECT. Это позволит вам создавать универсальную формулу, которая будет работать для множества проектов без необходимости вручную изменять формулы для каждого из них.

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

Вот как можно реализовать вашу задачу:

Структура таблиц:
- Таблица project_dashboard содержит колонки: Имя и Стоимость.
- Таблица project_1 (и аналогичные для других проектов) имеет колонки: Price и Status.
Формула:
В ячейке таблицы project_dashboard, где вы хотите отобразить стоимость, используйте следующую формулу:
```
=SUMIFS(
   INDIRECT([@Имя] & "[Price]"),
   INDIRECT([@Имя] & "[Status]"), "Completed"
)
```
В данной формуле:
- [@Имя] – это ссылка на ячейку в текущей строке столбца "Имя", которая содержит название проекта (например, project_1, project_2 и т.д.).
- INDIRECT([@Имя] & "[Price]") создает ссылку на колонку Price в таблице, имя которой указано в соответствующей строке допустим, project_1.
- INDIRECT([@Имя] & "[Status]") создает ссылку на колонку Status для поиска статуса.
Проверка работоспособности:
Убедитесь, что:
- Все названия проектов в таблице project_dashboard точно совпадают с названиями ваших таблиц, включая регистр символов.
- Убедитесь, что столбцы Price и Status правильно заданы в каждой из таблиц проектов.
Применение:
После ввода формулы для первого проекта в ячейке Стоимость, Вы можете просто перетащить маркер заполнения вниз, чтобы скопировать формулу для всех других проектов в project_dashboard. Excel автоматически скорректирует ссылки на имя проекта в каждой строке.

Этот метод поможет вам динамически суммировать стоимость проектов на основе статуса "Completed" без необходимости менять формулы для каждого отдельного проекта. Таким образом, вы получите удобный и эффективный механизм для анализа данных по вашим проектам.