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

Question

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

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

• Column Aот Row 2до Row 15представляет собой Ресурс (людей), выделенный для каждого проекта

• Column B, Column C.... представляет собой процент ресурсов, выделенных на каждый проект в месяц

• Каждый ресурс column Aобъединен в 2 ячейки, поскольку каждый ресурс может работать либо в 1, либо в 2 проектах в месяц.

• Ресурс, работающий над одним проектом в месяц, будет иметь обе ячейки, объединенные. Ресурс, работающий над 2 проектами, будет иметь 2 строки, представляющие проект

• Напротив каждого проекта указано число, представляющее процент ресурсов, выделенных на проект.

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

В нижнем отчете у меня есть раздел сводки, который представляет количество ресурсов, выделенных на проект. В настоящее время эти подсчеты вручную добавляются мной. Мне нужна помощь в создании формулы для этих сводок. Вот чего я пытаюсь добиться:

1. Прочитать строку из ячеек (A20 - A24)
2. Выполните поиск в столбце месяца (B2 - B15), чтобы получить ячейки, начинающиеся со строки проекта.
3. Отфильтруйте числа из ячеек выше (из шага 2)
4. Сложите числа и разделите на 100, чтобы получить количество ресурсов.

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

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

что возвращает мне число 50из ячейки, B2содержащей "Project A 50%"текст.

Answer 1

Если у вас O365, вы можете использовать FILTERфункцию:

• Отфильтровать список по содержимому A20:Ann

• Возвращает только последнее значение, разделенное пробелом, в каждой строке (процент)

• Суммируйте результаты

• Используйте IFERRORв случае, если проект не существует

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")
  

или(в зависимости от того, хотите ли вы вернуть ноль или ""несуществующую запись)

    =IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,ISNUMBER(SEARCH($A20,B$2:B$15)))," ",REPT(" ",99)),99))))),"")

Редактировать:В комментариях автор упоминает, что проекты могут иметь схожие названия, но с добавлением символа, и их следует рассматривать отдельно. Для этого требуется другой критерий фильтра:

=IFERROR(SUM(--((TRIM(RIGHT(SUBSTITUTE(FILTER(B$2:B$15,LEFT(B$2:B$15,LEN($A20))=$A20)," ",REPT(" ",99)),99))))),"")

Answer 2

Представление данных в строках 2-15 — беспорядок, поскольку вы объединяете два фрагмента информации в одной ячейке. Это фактически отключит возможность использования всех числовых функций ячеек. Разделите идентификатор проекта и количество ресурсов на два столбца.

Учитывая идентификатор проекта в столбце B и ресурс в столбце C, вы просто устанавливаете ячейку B20 в значение "=sumif(b$2:b$15;"A";c$2:c$15)". Вы изменяете эту формулу на "B" для проекта B.

Для более быстрого ввода без изменения формулы создайте скрытый столбец, содержащий ABCD .... и пусть фиксированная строка из формулы выше указывает на скрытые значения ABCD ....

Answer 3

Вам, вероятно, не очень понравится этот ответ, но, пожалуйста,сохраняйте открытость ума

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

1. Объединенные ячейки кажутся хорошей идеей на первый взгляд, но позже они принесут вам только неприятности.
2. Объединение переменных в одну ячейку «Проект А 50%» всегда усложняет анализ, поскольку теперь вам придется разделять эти переменные, чтобы понять их смысл.
3. Ввод данных в перекрестную таблицу может быть немного проще, но это стоит денег. Что, если вам теперь нужно подготовить отчет, в котором вас спрашивают о квартальном ресурсе, выделенном для проекта A в годовом исчислении? Кроме того, что произойдет, когда вы перейдете к следующему году? Создать новую таблицу? Тогда как вы их сравните?

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

Лучше всего вам подойдет что-то вроде этого:

Обратите внимание, что это форматируется как таблица с помощью Ctrl+T или «Форматировать как таблицу» на вкладке «Главная» ленты.

Такая структура имеет ряд преимуществ:

1. Ввод данных теперь стал тривиальным: достаточно просто ввести текст в новой строке.
2. Анализ можно проводить по любому измерению (человек, месяц, проект) с помощью сводной таблицы — никаких сложных формул для простых вопросов
3. Составление графиков теперь стало тривиально простым
4. Вы можете добавлять новую информацию в каждую строку в виде столбцов. Возможно, вы хотите добавить в строку имя линейного менеджера или название команды человека в качестве XLOOKUP из другой таблицы, или, возможно, вы хотите добавить заметки о производительности или отпуске в течение этого месяца для этого человека
5. Если вам когда-нибудь понадобится скопировать это в другую электронную таблицу или отправить кому-то в формате CSV или загрузить в таблицу в базе данных или фрейм данных для анализа, вам не придется тратить время на разделение и заполнение пустых ячеек, прежде чем вы сможете это сделать.

РЕДАКТИРОВАТЬ:

Чтобы ответить на ваш вопрос с помощью этой измененной структуры данных, вы можете просто создать сводную таблицу: