Home Articles

电子表格多个公式mashup

Asked
Viewed 1648 times
0

我正在尝试自动化一些任务管理流程,但我没有成功 . 我不能使用宏或类似的,只是公式,我不擅长电子表格黑客 .

无论如何,这是我的工作簿,其 sheets

**Form**
TASK     LI    DE    X
Test     1     3
Test2    2

**LI**
WEEK     TASK  COMPLETED
1        Test
2        Test
2        Test  *
4        Test2 *

**DE**
WEEK     TASK  COMPLETED
1        Test  *

我一直想做的是:

  • 在表单上,检查LI或DE中哪一列> 0 .

  • 对于每个> 0,检查其相应表(LI或DE)上是否存在TASK .

  • 如果有,请检查它是否有* .

  • 如果它有*,取该行的WEEK编号,将其与另一个工作表中的WEEK进行比较,取较大的数字,然后将其加载到Form上TASK的X列中 . 这里的订单并不重要 . 我只需要带有*的WEEK .

对于此示例,为了使X更改,TASK必须在工作表中使用* . 例如,如果在Form上,Test在LI和DE中有数字,而Test在LI表中有*,而在DE表中没有,则X必须保持为空 . 但是如果它们都带有*,那么X必须加载LI和DE之间更大的WEEK .

如果我用宏来做,我只需用循环检查每个列,但是使用公式我想嵌套的IF就足够了 .

我已经尝试过使用VLOOKUP,但它只需要数组中的第一项,虽然顺序无关紧要,但通常(我认为我会将此作为策略)是最后一个值 .

有任何疑问,请告诉我!我希望我明白我的问题 .

非常感谢你提前!

1 Answer

  • 2

    我认为你可以用公式来做,但是你必须循环,你需要SUMPRODUCT或Array Formula .

    这是您可以尝试的公式(使用CtrlShiftEnter验证):

    =MAX((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2))
    

    一些解释:

    • MAX 公式将找到两个祭司的两个ARRAY FORMULA之间的最大值

    • 数组公式的工作方式类似于多循环测试:

    • (LI!$C$2:$C$5="*") 检查第三列中是否有星号

    • (LI!$A$2:$A$5) 将返回周数

    • (LI!$B$2:$B$5=Form!A2) 将检查任务是否相同

    我希望我能理解你的意图:)

    [编辑]再次尝试感谢您的评论(两个任务都应该完成出现)

    =IF(AND((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2))),MAX((LI!$C$2:$C$5="*")*(LI!$A$2:$A$5)*(LI!$B$2:$B$5=Form!A2),(DE!$C$2:$C$5="*")*(DE!$A$2:$A$5)*(DE!$B$2:$B$5=Form!A2)),"")
    

Related