寻找使用SUM PRODUCT和COUNTIF的更有效方法 [英] Finding a more efficient way of using SUM PRODUCT and COUNTIF

问题描述

我目前有一个有效的公式，但是要赶上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 CtrlShiftEnter

---

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 choose PivotTable
• 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的更有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！