问题描述
我目前有一个有效的公式,但是要赶上excel需要花几秒钟的时间,我想知道是否有人知道一种更有效的处理方法。除了我将公式向下拖动时需要花费几秒钟的处理时间外,excel不会更新新单元格并最终使整个文件崩溃。
= SUMPRODUCT((((Paste!$ B $ 2:$ B $ 12000 = A2)))/ COUNTIFS(Paste!$ B $ 2:$ B $ 12000,Paste!$ B $ 2:$ B $ 12000&,Paste !$ C $ 2:$ C $ 12000,粘贴!$ C $ 2:$ C $ 12000&))
示例数据:
让我们想象一下数据在您出现时摆在我们面前:
1)不具有DA功能的公式:
您可以应用
I currently have this formula which works but it takes a few seconds for excel to catch up, and I was wondering if anyone knew about a more efficient way of handling this. Aside for it taking a few seconds to process when I drag the formula down excel doesn't update the new cells and eventually crashes the entire file.
=SUMPRODUCT(((Paste!$B$2:$B$12000=A2))/COUNTIFS(Paste!$B$2:$B$12000,Paste!$B$2:$B$12000&"",Paste!$C$2:$C$12000,Paste!$C$2:$C$12000&""))
Sample Data:
Let's imagine the data layed out in front of us as you present:
1) Formulas Without DA-functionality:
You could apply this method:
In F3
:
=SUM(--(FREQUENCY(IF(A$3:A$9=E3,MATCH(B$3:B$9,B$3:B$9,0)),ROW(B$3:B$9)-2)>0))
Enter through
2) With DA-functionality
You could apply this method:
In F3
:
=COUNTA(UNIQUE(FILTER(B3:B9,A3:A9=A2)))
Adapt any of the above to fit your ranges and needs, but do remember array formula may just way heavily on calculation.
3) Pivot Table:
If you want to avoid formulas alltogether, simply select all your data e.g. A2:B9
and follow these steps:
- Click tab
Insert
from the ribbon and choosePivotTable
- Choose where to insert the resulting table and check "Add this data to the Data Model"
- Use "Process" as column, and "Test_ID" as values.
- Go to "Value Field Settings" and summarize values by "Distinct Count"
- Confirm and enjoy the resulting table:
这篇关于寻找使用SUM PRODUCT和COUNTIF的更有效方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!