#google-sheets #count #sum #average #google-query-language
#google-sheets #подсчитать #сумма #среднее #google-query-language
Вопрос:
Я использую следующую формулу для вычисления общей строки, которая вычисляет средние значения для процентов в столбцах. Однако, поскольку данные содержат некоторые пустые значения, вычисленные средние значения неверны.
={"TOTAL",arrayformula(transpose(query(transpose(query({(B16:M19)},"Select " amp; TEXTJOIN(",",true,arrayformula(if(filter($B$15:15,$B$15:15<>false),"Sum(Col" amp; Column($B$15:$M$15) - Column(B$15) 1 amp; ")/Count(Col" amp; Column($B$15:$M$15) - Column(B$15) 1 amp; ")"))))),"select Col2 format Col2 '0.00%'",)*100%))}
Иногда я также получаю ошибки в моей рабочей таблице, а именно.,
Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC
или
Unable to parse query string for Function QUERY parameter 2: TypeError: Cannot read property 'get' of undefined
Они исчезнут, если я приведу диапазон следующим образом:
(B16:M19) 0 .... or.... N(B16:M19)
Но это приводит к тому, что среднее значение вычисляется неправильно, поскольку в среднем учитываются заменяемые 0 или пустые ячейки. Как этого избежать и правильно вычислить значения?
P.S: мне пришлось жестко закодировать диапазон, поскольку я не знаю, как сделать эту формулу более динамичной. Если у кого-то есть идея, пожалуйста, поделитесь со мной.
Вот пример таблицы. Таблица FTR
Комментарии:
1. Не могли бы вы показать, какие значения вы ожидаете в строке итогов, и объяснить основу для этих значений? Вы имеете в виду, что общее среднее значение для 1/1/2020 должно составлять 50% (среднее значение 0% и 100% для Алекса и мини), а не 25% (среднее значение 0%, 0%, 0% и 100% для Алекса, Джо, Майка и мини)?
2. Общее среднее значение для 1/1/2020 должно составлять только 100%, поскольку среднее значение должно исключать все пустое или 0.
3. Приведенный ниже ответ вам не подходит?
4. Нет. Это только для 1 столбца. Оно должно быть динамически сгенерировано для всех столбцов.
Ответ №1:
Я придумал эту формулу для ваших общих значений. См. B23 в таблице таблиц:
=IFERROR(SUM(B16:B19)/COUNT(FILTER(B16:B19,B16:B19>0)),0)
Если это подходит для вас, я могу попытаться посмотреть, можно ли его разработать как arrayformula.
Он просто суммирует каждый столбец в таблице и делит его на количество значений в таблице, которые больше нуля, поэтому он исключает как пробелы, так и нули в таблице.