Excel: как суммировать значения на основе расчета, а не диапазона?

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

Ниже приведены простые сведения о том, чего я пытаюсь достичь:

Task_Name   User    Start_Date  End_Date
Task_A  Peter   01/09/2016  28/10/2016
Task_B  Peter   01/09/2016  05/09/2016


Total days for September
Peter   25

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

Я использую приведенную ниже формулу, чтобы получить PR рабочего дня. задача:

=MAX(NETWORKDAYS(MAX($C2,DATE(YEAR($B$9),MONTH($B$9),1)),MIN($D2,DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))))),0)

где:

$B$9 = 01/09/2016

Эта формула правильно рассчитывает 22 дня для первой задачи.

Я думал сделать что-то вроде:

{=SUM(MAX(NETWORKDAYS(MAX($C2:$C3,DATE(YEAR($B$9),MONTH($B$9),1)),MIN($D2:$D3,DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))))),0))}

Выше не работает, и я играл с разными вариантами, но не могу взломать код - пока :)

С нетерпением жду комментариев.

Изменить:

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

/Томас


date excel excel-formula sum sumifs
person ThomasG    schedule 12.08.2016    source источник
comment
Я не понимаю, почему людям не нравятся вспомогательные столбцы или промежуточные расчетные столбцы и т. Д. Они жизненно важны, если вы пытаетесь построить что-то сложное. Если у вас сложная формула высотой в несколько строк и что-то перестает работать, найти проблему будет невозможно. Если бы расчет был разбит на более мелкие шаги, вы могли бы легко увидеть, в чем проблема. Также ... Если потянуть влево за столбец помощника, он исчезнет. Или вы создаете лист расчетов. Все расчеты производятся на листе 2, и вы просто привязываете готовую стоимость к основному листу.   -  person Andreas    schedule 12.08.2016
comment
@pnuts точно! Всегда есть место для расчетов, и их всегда можно спрятать.   -  person Andreas    schedule 12.08.2016
comment
Практическое правило - если формула длиннее, чем ваш большой палец, значит, она слишком длинная!   -  person Mark Fitzgerald    schedule 12.08.2016
comment
Вам придется заменить функции MIN и MAX эквивалентными операторами IF, чтобы они работали как формула массива. К сожалению, есть еще одна проблема - NETWORKDAYS, похоже, не любит массивы, поэтому вы можете застрять со вспомогательным столбцом.   -  person Tom Sharpe    schedule 12.08.2016

Ответы (1)


arrow_upward
0
arrow_downward 

{=SUMPRODUCT(NETWORKDAYS(
IF(DATE(YEAR($B$9),MONTH($B$9),1)<$C2:$C3+0,IF($C2:$C3+0<DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))),$C2:$C3+0,DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))+1)),DATE(YEAR($B$9),MONTH($B$9),1)),

IF(DATE(YEAR($B$9),MONTH($B$9),1)<$D2:$D3+0,IF($D2:$D3+0<DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))),$D2:$D3+0,DATE(YEAR($B$9),MONTH($B$9),DAY(EOMONTH($B$9,0))+1)),DATE(YEAR($B$9),MONTH($B$9),1))

))}

Вышеупомянутое должно работать, и оно должно позаботиться об отрицательных числах. По сути, он гарантирует, что обе даты ограничены началом и концом указанного месяца (указанного в ячейке $ B $ 9). При фактическом указании дней в качестве начала или конца месяца мне пришлось добавить дополнительный день в конец месяца, чтобы избежать подсчета, когда на самом деле в месяц не было дня.

Я не тестировал его всесторонне, но не стесняйтесь поэкспериментировать с ним и дать мне обратную связь. Я разделил формулу по строкам для лучшей видимости, но это маленький монстр :)

person Genie    schedule 15.08.2016