Я пытаюсь создать формулу, которая вычисляет отклонения от определенного порогового значения параметра в часах. Регистрируемые параметры — это поток воды в галлонах в минуту и добавление химикатов. Каждый параметр регистрируется каждый час. Если параметр ниже порогового значения параметра, это считается отклонением. Отклонениянедвойной учет: если оба параметра отклоняются в течение одного и того же часового периода, то это считается только одним часом отклонения.
У меня настроено три столбца. Один — столбец даты и времени (1/1/2016 1:00am, 1/1/2016 2:00am и т. д.), а два других — столбцы параметров. У меня нет проблем с настройкой формул для расчета часов отклонения для каждого параметра отдельно. Моя проблема заключается в настройке формулы, которая по сути суммирует отклонения обоих параметров в часах без двойного учета промежутков времени, где оба параметра отклоняются.
Я был бы признателен за любую помощь или предложения по правильному направлению. До сих пор я пытался использовать функции IF и AND и некоторые другие, но не уверен в правильной комбинации, которую мне нужно использовать.
Если что-то нужно уточнить, пожалуйста, дайте мне знать. Спасибо.
- РЕДАКТИРОВАТЬ *
Мне не удалось заставить работать генератор таблиц, поэтому вот изображение того, как настроены данные:
Это пример формулы в ячейке E3, которую я использую, чтобы определить, является ли заданный параметр отклоняющимся (по сути, то же самое, что и в ячейке G3):
=IF(B3>=127,"No",A3)
Вот формула, которая подсчитывает общие отклонения расхода воды в ячейке F3 (по сути, та же, что и в ячейке H3):
=COUNTIF(E3:E8,">0")
Результат, как видно на изображении выше, составляет в общей сложности 5 часов отклонений расхода воды и 3 часа отклонений добавления химикатов. Однако, поскольку оба параметра отклоняются в течение одного и того же периода времени в определенных случаях, общее количество отклоняющихся часов составляет 6. Это то, что я хочу, чтобы формула вычислила для меня. Ниже приведен незаконченный фрагмент формулы, с которой я работал, хотя она ужасно неадекватна:
=IF(AND(E3:E8,G3:8)
Мой ход мыслей был таким: ЕСЛИ столбцы E и G равны в течение одного и того же периода времени, то считайте их как один час отклонения. Очевидно, что здесь не хватает некоторых вещей. Я просто не могу понять, куда идти дальше, или вообще двигаюсь ли я в правильном направлении.
решение1
- Например, вы можете вычесть количество дубликатов из общего числа:
= F3 + H3 - COUNTIFS(B3:B8,"<127",C3:C8,"<368") - Другой подход может заключаться в добавлении нового столбца, в котором вы проверяете, не прошли ли какие-либо параметры, а затем просто подсчитываете их, как и раньше:
=IF(and(B3>=127,C3>=368),"No",A3)