У меня есть электронная таблица, показывающая таблицу показаний датчика температуры в помещении, и я пытаюсь подсчитать показания, которые ниже минимума, который изменяется в зависимости от времени суток (столбец даты и времени A) и наружной температуры (столбец Q). Минимум 62 градуса между 22:00 и 6:00 и 68, если температура наружного воздуха ниже 55 между 6:00 и 22:00. Минимум отсутствует, если дневная температура выше 55.
У меня есть условное форматирование, которое успешно выделяет температуры ниже минимальной, но я чертовски долго подсчитываю эти нестандартные показания для сводного заголовка. Я использую листы Google, но подозреваю, что есть простой ответ на мою проблему, характерную для Excel.
Самое близкое, к чему я пришел, - создать вспомогательный столбец для вычисления минимума с использованием этой формулы в столбце R:
=IF(OR(HOUR($A6)>22, HOUR($A6)<6),62,IF($Q6<55,68,0))
Затем я смог получить желаемый результат с помощью этой формулы в заголовке:
=SUMPRODUCT(--(B6:B<$R6:$R))
Однако электронная таблица обновляется автоматически, добавляя строку каждый час, и я хотел бы избежать необходимости иметь дело с обновлением вспомогательного столбца, поэтому мне нужно, чтобы все было в формуле подсчета в начале каждого столбца показаний датчика. Я попытался включить вычисления для вспомогательного столбца в формулу суммарного произведения следующим образом:
=SUMPRODUCT(--(B6:B<IF(OR(HOUR($A6:$A)>22, HOUR($A6:$A)<6),62,IF($Q6:$Q<55,68,0))))
Но это вернуло значение 482, когда я знал, что в этом столбце было только 15 показаний, которые должны были быть подсчитаны.
Кто-нибудь может помочь?
Редактировать:
Вот образец
Ячейками счетчика будут ячейки после «# часов нарушения», а ожидаемые значения отражают параметры, описанные выше. Для целей этого примера я выделил жирным шрифтом значения, которые будут учитываться.
Мне бы хотелось, чтобы формула работала в Таблицах.
Редактировать:
Опять же, и просто чтобы собрать все условия в одном месте:
Я хочу подсчитать значения для каждого датчика, которые ниже расчетного минимума. Этот минимум изменяется в зависимости от времени суток и температуры наружного воздуха следующим образом:
Таким образом, в приведенном выше примере таблицы ожидаемые значения отображаются в строке «# часов с нарушением» и выделены полужирным шрифтом.
Вы можете увидеть мою таблицу с рабочим условным форматированием и вспомогательной колонкой здесь. Ячейка счетчика над столбцом 1F была моей неудачной попыткой включить все, что мне нужно, в функцию СУММПРОИЗВ. Я также попытался сделать это с помощью функции СЧЁТЕСЛИ с такими же нефункциональными результатами.
В Excel вы можете использовать функцию FILTER
или, может быть, MINIFS
. Я не знаю, есть ли аналог Sheets.
Не могли бы вы дополнить свой вопрос кратким изложением условий, которые необходимо выполнить, и ожидаемого результата на основе предоставленных вами входных данных? Спасибо
Если приведенный выше пример таблицы копируется на новый лист в A1, то следующая формула в B2 генерирует указанные «часы нарушения» (примечание: сначала удалите вручную введенные значения в B2-D2):
=arrayformula(lambda(lastrow,bycol(n(B3:index(D3:D,lastrow)<ifs(not(isbetween(hour(A3:index(A3:A,lastrow)),6,22)),62,E3:index(E3:E,lastrow)<55,68,TRUE,)),lambda(x,sum(x))))(counta(A3:A)))
Это также должно автоматически пересчитываться при добавлении дополнительных строк.
РЕДАКТИРОВАТЬ Эта версия формулы должна игнорировать любые пустые показания. Я также немного упорядочил формулу, абстрагировав все диапазоны в переменные LAMBDA, чтобы упростить следование логике:
=arrayformula(lambda(lastrow,lambda(times,outtemp,readings,bycol(--if (isblank(readings),,readings<ifs(not(isbetween(hour(times),6,22)),62,outtemp<55,68,TRUE,)),lambda(x,sum(x))))(A3:index(A3:A,lastrow),E3:index(E3:E,lastrow),B3:index(D3:D,lastrow)))(counta(A3:A)))
Отличный. Это делает именно то, что мне нужно, и намного сложнее, чем все, что я пытался сделать. Спасибо!
Есть ли способ заставить это игнорировать нулевые значения? В настоящее время он считает пустые ячейки ниже минимума.
На данный момент я снимаю флаг «отвечено», в основном потому, что мне пришлось вернуться к менее предпочтительному способу, которым я занимался, поскольку я не могу считать пустые ячейки низкими показаниями.
Я обновил ответ версией формулы, которая должна игнорировать пустые показания.
Большое спасибо. Это (бисквит) Находка!
Пожалуйста, отредактируйте свой вопрос, чтобы включить образец данных в виде текста, который можно скопировать/вставить, а также пример того, каким будет результат из этого образца. См. Как создать минимальный, полный и проверяемый пример , а также Почему нельзя загружать изображения кода/данных/ошибок при задании вопроса?. Кроме того, вы хотите решение в Excel или в таблицах? Это разные программы.