Формула для подсчета вычислений на основе значений с участием двух других столбцов

У меня есть электронная таблица, показывающая таблицу показаний датчика температуры в помещении, и я пытаюсь подсчитать показания, которые ниже минимума, который изменяется в зависимости от времени суток (столбец даты и времени 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 показаний, которые должны были быть подсчитаны.

Кто-нибудь может помочь?

Редактировать:

Вот образец

Квартира: 4F 4Дж 4Н Снаружи «# часов нарушения» 1 1 3 04.01.2023 10:00:00 70,88 73.04 70,7 58,1 04.01.2023 9:00:00 70,7 73,22 67,50 55.04 04.01.2023 8:00:00 68,18 72,32 67,82 54.14 04.01.2023 7:00:00 68,18 72,5 67,28 54,86 04.01.2023 6:00:00 67,28 71,6 66,38 53,96 04.01.2023 5:00:00 67,64 71,96 66.02 54,68 04.01.2023 4:00:00 67,82 61 66,56 57

Ячейками счетчика будут ячейки после «# часов нарушения», а ожидаемые значения отражают параметры, описанные выше. Для целей этого примера я выделил жирным шрифтом значения, которые будут учитываться.

Мне бы хотелось, чтобы формула работала в Таблицах.

Редактировать:

Опять же, и просто чтобы собрать все условия в одном месте:

Я хочу подсчитать значения для каждого датчика, которые ниже расчетного минимума. Этот минимум изменяется в зависимости от времени суток и температуры наружного воздуха следующим образом:

  • Между 22:00 и 6:00, согласно столбцу A (ночь), минимум всегда равен 62.
  • Между 6:00 и 22:00 включительно (день) минимальное значение равно 68, если наружная температура (столбец Q) меньше 55, и ноль, если наружная температура больше 55.

Таким образом, в приведенном выше примере таблицы ожидаемые значения отображаются в строке «# часов с нарушением» и выделены полужирным шрифтом.

Вы можете увидеть мою таблицу с рабочим условным форматированием и вспомогательной колонкой здесь. Ячейка счетчика над столбцом 1F была моей неудачной попыткой включить все, что мне нужно, в функцию СУММПРОИЗВ. Я также попытался сделать это с помощью функции СЧЁТЕСЛИ с такими же нефункциональными результатами.

Пожалуйста, отредактируйте свой вопрос, чтобы включить образец данных в виде текста, который можно скопировать/вставить, а также пример того, каким будет результат из этого образца. См. Как создать минимальный, полный и проверяемый пример , а также Почему нельзя загружать изображения кода/данных/ошибок при задании вопроса?. Кроме того, вы хотите решение в Excel или в таблицах? Это разные программы.

Ron Rosenfeld 04.01.2023 18:53

В Excel вы можете использовать функцию FILTER или, может быть, MINIFS. Я не знаю, есть ли аналог Sheets.

Ron Rosenfeld 04.01.2023 18:55

Не могли бы вы дополнить свой вопрос кратким изложением условий, которые необходимо выполнить, и ожидаемого результата на основе предоставленных вами входных данных? Спасибо

David Leal 04.01.2023 21:40
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
3
83
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Если приведенный выше пример таблицы копируется на новый лист в 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)))

Отличный. Это делает именно то, что мне нужно, и намного сложнее, чем все, что я пытался сделать. Спасибо!

Nathan 05.01.2023 03:52

Есть ли способ заставить это игнорировать нулевые значения? В настоящее время он считает пустые ячейки ниже минимума.

Nathan 05.01.2023 16:56

На данный момент я снимаю флаг «отвечено», в основном потому, что мне пришлось вернуться к менее предпочтительному способу, которым я занимался, поскольку я не могу считать пустые ячейки низкими показаниями.

Nathan 05.01.2023 21:18

Я обновил ответ версией формулы, которая должна игнорировать пустые показания.

The God of Biscuits 05.01.2023 22:28

Большое спасибо. Это (бисквит) Находка!

Nathan 06.01.2023 02:45

Другие вопросы по теме