СУМИФ со сложными условиями с использованием матрицы

Я привык использовать некоторые матричные формулы в excel, но я не в состоянии решить эту.

Я работаю с базой данных о расходах фирм на человеческие ресурсы, которая структурирована следующими столбцами:

[Month] / [Type_Of_Expenditures] / [Employee_Name] / [Value]

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

Одним из видов расходов, которые фирма может иметь с работодателем, является увольнение. Каждый раз, когда сотрудник уволен, появляется строка, где [@Type_Of_Expenditures]="Lay_Off"и стоимость увольнения этого сотрудника появляется на [@Value]

То, что я пытаюсь сделать, это следующее: Я хочу суммировать общую стоимость за месяц, учитывая только тех сотрудников, которые не были уволены. Но моя проблема заключается в том, что если работодатель "Ana"был уволен в этом месяце, он будет получать не только "Lay_Off"расходы, но и другие расходы, такие как "Wage". Так мы можем иметь, например:

[Month] // [Name] // [Type_of_expenditure] // [Value]
[...]
01/2016 // Ana // Lay_off // U0 
01/2016 // Ana // Wage // U0
[...]

Моя проблема заключается в создании структуры SUMIF, которая будет суммировать все[Values], что обусловлено конкретным[Month], но не будет суммироваться, если работодатель был уволен в этот конкретный месяц, независимо от того, что это [Type_Of_Expedniture]. В "Ana example", Я не хочу суммировать ни верхнюю, ни нижнюю строку, которую я описал выше.

Что еще хуже, мы говорим о таблице с более чем 80 000 строк, поэтому эффективность имеет значение.

Может кто-нибудь помочь мне в этом?

1 ответ

  1. Используйте такую формулу массива:

    =SUM(IF((MONTH(G1)=MONTH($A:$A))*(YEAR(G1)=YEAR($A:$A))*(B2:B22=G2)*ISERROR(MATCH($C:$C&"LAY_OFF",$C:$C&$B:$B,0)),$D:$D))
    

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

    Будучи формулой массива, она должна быть подтверждена Ctrl-Shift-Enter. Если все сделано правильно, excel поместит {} вокруг формулы.

    Введите описание изображения здесь

    Только для FYI общая зарплата, если бы Джордж был включен, была бы 11550.


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

    Это можно также сделать с SUMPRODUCT():

    =SUMPRODUCT((MONTH(G1)=MONTH($A:$A))*(YEAR(G1)=YEAR($A:$A))*(B2:B22=G2)*ISERROR(MATCH($C:$C&"LAY_OFF",$C:$C&$B:$B,0)),$D:$D)
    

    Но это может быть немного сложнее, чем формула выше.