Формула сверхурочных Excel на основе ячейки в качестве критерия

Question

Я упростил это настолько, насколько я могу:

Я хотел бы отследить, когда сверхурочные работают и кем.

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

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

Я надеюсь, что это имеет больше смысла и большое спасибо тем, кто помог мне до сих пор.

Answer 1

Вы можете достичь этого, например, с помощью SUMPRODUCT:

• Общее количество сверхурочных по работникам:
  =SUMPRODUCT((D2:H2>C2)*(D2:H2-C2))
• Общее количество сверхурочных за день:
  =SUMPRODUCT((D2:D5>$C$2:$C$5)*(D2:D5-$C$2:$C$5))

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

В качестве альтернативы вы также можете использовать эту формулу (для ввода в D6):
=SUM(IF(D2:D5>$C$2:$C$5,D2:D5-$C$2:$C$5,0))
Это формула массива (после нее нужно нажать CTRL+SHIFT+ENTER), а также она немного длиннее, но, вероятно, лучше показывает логику формулы.