确定职责分离冲突的Excel数组

Question

我有两个列表；第一个是基于特定能力的职责冲突分离列表(例如，审批者不能是采购订单的请求者或接收者)。一个能力可能与其他能力有许多冲突，并且一个员工可能具有多个能力。目标是比较每个员工的能力是否与每个员工的SOD冲突匹配，每次1列(SOD冲突1与冲突1匹配，SOD冲突2与冲突2匹配)。非常重要的一点是，循环返回员工的姓名/id，以查看该特定姓名/id的后续实例是否与先前的姓名发生冲突。这样，所有员工的能力都会被计算出来。

目前，我有下面的公式，只有当实例是我的职责分离冲突表的顶行时，才显示冲突。

{=IF(($A$2:$A$7=$A11)*($B$12:$B$17=$B11)*($A12:$A$17=B$2:B$7),"CONFLICT","")}

您能否帮助我使用公式，以便我可以将其拖到员工能力列表中，以确定冲突是否存在于第一个实例之外？布朗尼分，如果你可以有隔离的职责冲突列出:) TIA。仅供参考，我的VBA技能是不存在的，但我洗耳恭听学习，只要ELI5它，如果你冒险这条路。

这里有一个使用Google Docs的完整文件的链接...

https://docs.google.com/spreadsheets/d/1kzFB1qfARoqK53ZFeEXRjuHQMN00Fs3quL6Jc73NPqQ/edit?usp=sharing

   A             B               C              D           E
1  Type          SOD Confict 1   SOD Confict 2
2  PO request    Test            Test       
3  PO approval   PO request      PO receive     
4  PO receiv     PO request      PO approval        
5  PO Purchase   Test            PO Test 2      
6  PO Placement  Test 3          PO Test 4      
7  Test          PO Test 5       PO Test 6      
8               
9               
10 Ability       Person          Employee ID    Conflict 1  Conflict 2
11 PO request    John            1              CONFLICT    CONFLICT
12 Test          John            1      
13 PO receive    John            1      
14 PO receive    Jake            2      
15 PO request    Jake            2      
16 PO approval   Jimmy           3      
17 PO receive    Jimmy           3  

Answer 1

完成修改。

考虑一下这个屏幕截图。

​

​

D11和E11返回空单元格。

D12：“测试”是一个‘in rows 2 and 5. In these cases, column A has the values冲突1测试PO请求’，这与约翰在A11中的第一个能力相匹配。因此，PO request是一个冲突。E12也是如此。

D13: A13中的PO receive不是SOD Conflict 1。结案了。

E13:在第3行的SOD Conflict 2中发现了A13中的PO receive。在该行的A列中，PO approval与约翰在A11中的第一个能力不匹配。没有冲突。

D15: A15中的PO request在SOD Conflict 1中找到，并且A4中的值与Jake的第一个能力PO receive匹配，因此冲突是PO receive。E15为空，因为PO request不是SOD Conflict 2。

D17: Jimmy的PO receive不是SOD Conflict 1，所以单元格是空的。

E17:在SOD conflict 2中可以找到PO receive，在A3中对应的值是PO approval，这是Jimmy的第一个能力。因此与PO approval产生了冲突。

D11中的公式是

=IFERROR(INDEX($A$2:$A$7,SUMPRODUCT((B$2:B$7=$A11)*(($A$2:$A$7=INDEX($A$11:$A$30,MATCH($B11,$B$11:$B$30,0)))*ROW($A$1:$A$6)))),"")

它不是数组公式。复制到E11并向下复制。

Answer 2

这里有一个使用Google Docs的完整文件的链接...

https://docs.google.com/spreadsheets/d/1kzFB1qfARoqK53ZFeEXRjuHQMN00Fs3quL6Jc73NPqQ/edit?usp=sharing

我最终在一个vlookup中使用了一个嵌套的vlookup，在这个范围内我包含了雇员ID和能力，所以为了避免数组，只有1个唯一的项。可能有点脏，但解决了问题。