У меня есть отчет о проекте и распределении ресурсов. Мне нужна помощь в создании формулы для отображения сводки в правом разделе отчета.
Вот характеристики моего отчета:
- В столбце A от строки 2 до строки 15 представлены ресурсы (люди), выделенные для каждого проекта.
- Столбец B, столбец C …. представляет процент ресурсов, выделенных на каждый проект в месяц.
- Каждый ресурс столбца A объединен в 2 ячейки, потому что каждый ресурс может работать в 1 или 2 проектах в месяц.
- Ресурс, работающий над одним проектом в месяц, объединит обе ячейки. Ресурс, работающий над 2 проектами, будет иметь 2 строки, представляющие проект.
- Напротив каждого проекта есть число, представляющее процент этого ресурса, выделенного проекту.
Вот скриншот моего отчета:
Здесь, в резюме, я представляю общее количество недель, которые каждый человек потратит на каждый проект. Чтобы упростить расчет, я предполагаю, что каждый месяц равен ровно 4 неделям. Так,
- Если Человек А работает на 100% в Проекте Б в феврале. Тогда число, которое будет учитываться для суммирования, должно быть 4 для Проекта B.
- Аналогично, если человек А работает на 50-50% над 2 проектами в январе. Тогда количество, которое будет учитываться для подведения итогов, должно составлять 2-2 недели по каждому проекту.
В общем, число, которое следует рассматривать в итоге, можно математически представить как:
4 *[Percentage from column] / 100
Вот чего я пытаюсь достичь:
- Считать строку из ячеек (F1 — J1)
- Выполните поиск в столбце человека в двух строках (B2, C2 …) и (B3, C3 …), чтобы получить ячейки, начинающиеся со строки проекта.
- Отфильтруйте числа из вышеперечисленных ячеек (из шага 2). Произведите суммирование чисел, разделите на 100 и умножьте на 4 (чтобы получить количество недель)
Вот что я создал, взяв ответ на аналогичный вопрос в качестве ссылки отсюда:
=IFERROR((IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B2:$C2,ISNUMBER(SEARCH(J$1,$B2:$C2)))," ",REPT(" ",99)),99))))), 0) + IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER($B3:$C3,ISNUMBER(SEARCH(J$1,$B3:$C3)))," ",REPT(" ",99)),99))))), 0))*4, "")
что дает мне 6 для проекта B. Есть ли способ упростить это? Я думаю, что проделываю здесь много лишних операций. Кроме того, он возвращает 0, если проект не найден. Я предпочитаю пустую строку на этом месте
Не стесняйтесь предложить, есть ли лучший способ выполнить этот расчет.
Заметка:
- Для столбца с двумя объединенными ячейками расчет следует учитывать только один раз для этого человека.
- Расчет моей сводки основан на данных из двух строк
- Здесь я использую строки имен людей («Человек A», «Человек B» и т. Д.) И имена проектов («Проект A», «Проект B» и т. Д.) Как текст-заполнитель. В реальном отчете это реальные значения со случайными алфавитами.
PS: Это похоже на мой предыдущий вопрос, когда я создавал сводку по одному столбцу, где две ячейки в столбце могли быть объединены: Excel — фильтрация строк на основе начальной строки, чтение числа из отфильтрованных строк и выполнение арифметических операций для создания сводки столбцов . Однако здесь я выполняю вычисления для двух строк, чтобы получить сводку. А объединенные ячейки строк нужно учитывать для расчета только один раз.