Google 电子表格中的 SUMIFS 函数 [英] SUMIFS function in Google Spreadsheet
问题描述
我正在尝试具有与 SUMIFS 类似的功能(例如 SUMIF,但具有多个单个标准)在 Google 电子表格中.MS-Excel 内置了此功能 (http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1).
I'm trying to have a similar function to SUMIFS (like SUMIF but with more than a single criterion) in a Google Spreadsheet. MS-Excel has this function built-in (http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1).
我尝试使用 ArrayFormula (http://support.google.com/docs/bin/answer.py?hl=en&answer=71291),类似于 SUMIF:
I've tried to use ArrayFormula (http://support.google.com/docs/bin/answer.py?hl=en&answer=71291), similar to the SUMIF:
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
通过添加AND:
By Adding AND:
=ARRAYFORMULA(SUM(IF(AND(A1:A10>5,B1:B10=1), C1:C10, 0)))
但是 AND 函数没有选择 ArrayFormula 指令并一直返回 FALSE.
But the AND function didn't pick up the ArrayFormula instruction and returned FALSE all the times.
我能找到的唯一解决方案是使用 QUERY,这似乎有点慢和复杂:
The only solution I could find was to use QUERY which seems a bit slow and complex:
=SUM(QUERY(A1:C10,"Select C where A>5 AND B=1"))
我的目标是用许多要计算的值填充一个表(类似于数据透视表):
My Target is to fill up a table (similar to a Pivot Table) with many values to calculate:
=SUM(QUERY(DataRange,Concatenate( "Select C where A=",$A2," AND B=",B$1)))
有没有人设法以更简单、更快捷的方式做到这一点?
Did anyone manage to do it in a simpler and faster way?
推荐答案
我发现了一个更快的函数来填充数据透视表":
I found a faster function to fill up the "pivot table":
=ARRAYFORMULA(SUM(((Sample!$A:$A)=$A2) * ((Sample!$B:$B)=B$1) * (Sample!$C:$C) ))
如果没有较重的 String 和 Query 函数,它似乎运行得更快.
It seems to run much faster without the heavier String and Query functions.
这篇关于Google 电子表格中的 SUMIFS 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!