Функция SUMIFS в Google Spreadsheet
Я пытаюсь использовать функцию, аналогичную SUMIFS (например, SUMIF, но с более чем одним критерием) в электронной таблице Google. MS-Excel имеет эту встроенную функцию ( 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:
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
Добавляя И:
=ARRAYFORMULA(SUM(IF(AND(A1:A10>5,B1:B10=1), C1:C10, 0)))
Но функция AND не принимала инструкцию ArrayFormula и все время возвращала FALSE.
Единственное решение, которое я смог найти, это использовать QUERY, который кажется немного медленным и сложным:
=SUM(QUERY(A1:C10,"Select C where A>5 AND B=1"))
Моя цель - заполнить таблицу (похожую на сводную таблицу) множеством значений для расчета:
=SUM(QUERY(DataRange,Concatenate( "Select C where A=",$A2," AND B=",B$1)))
Кому-нибудь удалось сделать это проще и быстрее?
4 ответа
Я нашел более быструю функцию для заполнения "сводной таблицы":
=ARRAYFORMULA(SUM(((Sample!$A:$A)=$A2) * ((Sample!$B:$B)=B$1) * (Sample!$C:$C) ))
Кажется, он работает намного быстрее без более тяжелых функций String и Query.
На мой взгляд, самый простой способ создания SumIFS-подобных функций - это объединить функции FILTER и SUM.
SUM(FILTER(sourceArray, arrayCondition_1, arrayCondition_2, ..., arrayCondition_30))
Например:
SUM(FILTER(A1:A10;A1:A10>5;B1:B10=1)
Объяснение: FILTER() фильтрует строки в A1:A10, где A1:A10 > 5 и B1:B10 = 1. Затем SUM() суммирует значения этих ячеек.
Этот подход очень гибок и позволяет, например, легко создавать функции COUNTIFS() (просто используйте COUNT() вместо SUM()).
По состоянию на декабрь 2013 года Google Sheets теперь имеет функцию SUMIFS, о чем говорится в этом сообщении в блоге и задокументировано здесь.
Обратите внимание, что старые электронные таблицы не преобразуются в новую версию, хотя вы можете попробовать скопировать данные в новую рабочую книгу.
Этот парень использовал функцию Filter, чтобы разделить массив по критериям, а затем функцию sum, чтобы добавить все в одну ячейку. http://www.youtube.com/watch?v=Q4j3uSqet14 Для меня это сработало.