Excel – фильтрация столбцов на основе начальной строки, чтение числа из отфильтрованных ячеек и выполнение арифметической операции для создания сводки двух строк.

У меня есть отчет о проекте и распределении ресурсов. Мне нужна помощь в создании формулы для отображения сводки в правом разделе отчета.

Вот характеристики моего отчета:

  • В столбце 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

Вот чего я пытаюсь достичь:

  1. Считать строку из ячеек (F1 – J1)
  2. Выполните поиск в столбце человека в двух строках (B2, C2 …) и (B3, C3 …), чтобы получить ячейки, начинающиеся со строки проекта.
  3. Отфильтруйте числа из вышеперечисленных ячеек (из шага 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, если проект не найден. Я предпочитаю пустую строку на этом месте

Не стесняйтесь предложить, есть ли лучший способ выполнить этот расчет.

Заметка:

  1. Для столбца с двумя объединенными ячейками расчет следует учитывать только один раз для этого человека.
  2. Расчет моей сводки основан на данных из двух строк
  3. Здесь я использую строки имен людей («Человек A», «Человек B» и т. Д.) И имена проектов («Проект A», «Проект B» и т. Д.) Как текст-заполнитель. В реальном отчете это реальные значения со случайными алфавитами.

PS: Это похоже на мой предыдущий вопрос, когда я создавал сводку по одному столбцу, где две ячейки в столбце могли быть объединены: Excel – фильтрация строк на основе начальной строки, чтение числа из отфильтрованных строк и выполнение арифметических операций для создания сводки столбцов . Однако здесь я выполняю вычисления для двух строк, чтобы получить сводку. А объединенные ячейки строк нужно учитывать для расчета только один раз.

0

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *