Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Многофакторная задача по поиску значений, Выбрать макс и мин значения нарушений и их количество, которые не попадают в установленные периоды и с учетом разных норм.
 
#1
28.07.2026 10:24:13
Здравствуйте. Прошу оказать помощь в решении многофакторной задачи. Необходимо найти количество нарушений и определить мин значение нарушений по нижней границе и макс значение нарушений по верхней границе по каждому показателю. При этом не должны учитываться значения измерений, попадающие в даты простоя (3 периода). Если простоев не было, то ячейки с датами будут пустые. Для выявления нарушений действуют нормы. Они могут меняться, т.е. до даты изменения действовала одна норма, а с даты изменения техдокументации - другая. Дата изменения техдокументации может быть любая: и год назад, и месяц назад, и вчера.
 
 
 
#2
28.07.2026 10:37:01
0.84 разве не является нарушением
 
 
 
#3
28.07.2026 10:39:48
Значение 0,84 попадает в период простоя
 
 
 
#4
28.07.2026 10:42:36
avsvpav, для простоев только 3 строки или может быть больше?

увидел ;)
Изменено: bigorq - 28.07.2026 10:43:27
 
 
 
#5
28.07.2026 10:43:43
Простоев только 3 периода. Заполняются вручную ежемесячно. Именно в таком виде, как указано в файле.
 
 
 
#6
28.07.2026 22:46:31
Здравствуйте.
Посмотрите вариант  файле, как понял. Возможно что то и не учёл, уж больно не понятно расписано.
Формулами массива:
Код
=МИН(ЕСЛИ(СЧЁТЕСЛИМН($K$2:$K$4;"<="&$B$2:$B$19;$L$2:$L$4;">="&$B$2:$B$19)*($A$2:$A$19=L7);"";ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M7;$C$1:$F$1;)))) 
=МАКС(ЕСЛИ(СЧЁТЕСЛИМН($K$2:$K$4;"<="&$B$2:$B$19;$L$2:$L$4;">="&$B$2:$B$19)*($A$2:$A$19=L7);"";ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M7;$C$1:$F$1;))))

и обычной количество. Длинноватая правда. И если по описанию вычислять

Цитата
написал:
до даты изменения действовала одна норма, а с даты изменения техдокументации - другая.
То по моим расчётам получается количество нарушений по показателю1 равно=1, так как 4.06.26 считается по новым нормам. Проверяйте.

Код
=СУММПРОИЗВ((СЧЁТЕСЛИМН($K$2:$K$4;"<="&$B$2:$B$19;$L$2:$L$4;">="&$B$2:$B$19)=0)*($A$2:$A$19=L$7)*($B$2:$B$19<$N$6)*СЧЁТЕСЛИМН(P$7;"<="&ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M$7;$C$1:$F$1;));R$7;">="&ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M$7;$C$1:$F$1;))))+СУММПРОИЗВ((СЧЁТЕСЛИМН($K$2:$K$4;"<="&$B$2:$B$19;$L$2:$L$4;">="&$B$2:$B$19)=0)*($A$2:$A$19=L$7)*($B$2:$B$19>=$N$6)*СЧЁТЕСЛИМН(P$8;"<="&ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M$7;$C$1:$F$1;));R$8;">="&ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M$7;$C$1:$F$1;))))
 
 
 
#7
29.07.2026 09:56:14
Возможно, действительно сумбурно описана задача. Прошу прощения.

Поясню по нормам. В период до даты изменения техдокументации (в нашем случае с 01.06.2026 до 03.06.2026) для нормирования применялась норма 1, а с 04.06.2026 включительно нормирование осуществляется по норме 2. Нормы были действующие каждая в своем периоде, и нарушения необходимо учитывать все.

На самом деле должно зафиксироваться 2 нарушения в месяц по Показателю 1. Первое - со значением 0,9, в период с 01.06.2026 по 03.06.2026 (то есть до изменения норм), второе - со значением 1,5 в период с 04.06.2026 до конца месяца. Всего 2.


Значение 1,67, которое рассчиталось как МАКС по Показателю 2, относится к Точке 2 и не может учитываться при анализе измерений по Точке 1.

У меня сложность вызвала именно работа с датами - как исключить период простоя, заданный не массивом, а только начальным и
конечным значением, максимально короткой формулой, так как массив обрабатываемой информации очень большой. Особенно, если простоев не было, и ячейки пустые.
В примере, если убрать даты простоя, то по Показателю 1 минимальное  значение рассчиталось 0,78, которое соответствует 08.06.2026, но при этом оно укладывается в норму 2, которая была введена с 04.06.2026. Хотя количество нарушений посчиталось верно =5. По Показателю 2 МИН рассчитался верно (0,8), а количество нарушений должно быть 5 (0,8, 1,53, 1,57, 0,84, 0,9).

Интересно, Вы предложили достаточно компактную формулу учета дат простоя. Такой способ не применяла. Попробую с ней поэкспериментировать.
 
 
 
#8
29.07.2026 12:15:04
Сами же пишите с
Цитата
написал:
в период с 01.06.2026 по 03.06.2026 (то есть до изменения норм),

И тут же пишите
Цитата
написал:
второе - со значением 1,5 в период с 04.06.2026 до конца месяца.

С 4.06.2026 действуют новые нормы, от 0,7 до 1,1 и 1,5 за 4.06.2026, больше максимального значения нормы и следовательно в эти нормы от 0,7 до 1,1 не попадает. По этому у меня и получился результат =1.
Изменено: gling - 29.07.2026 12:21:32
 
 
 
#9
29.07.2026 12:39:00
Все верно.
В период с 01.06.2026 по 03.06.2026 включительно действовала норма от 1 до 1,5. По этой норме в период с 01.06.2026 по 03.06.2026 было нарушение со значением 0,9 за 03.06.2026. Его мы должны учесть.
С 4.06.2026 действуют новые нормы от 0,7 до 1,1. В этот период тоже нашлось 1 нарушение со значением 1,5 за 04.06.2026.
То есть новая норма применяется с даты изменения документации, а для периода до этой даты применяется предыдущая норма.
Итого 2 нарушения.
 
 
 
#10
29.07.2026 12:44:32
Чтобы не учитывались значения по точке 2, немного изменила формулу

={МИН(ЕСЛИ($A$2:$A$19=$L$10;ЕСЛИ(СЧЁТЕСЛИМН($K$2:$K$4;"="&$B$2:$B$19;$L$2:$L$4;">="&$B$2:$B$19);"";ИНДЕКС($C$2:$F$19;;ПОИСКПОЗ(M10;$C$1:$F$1;)));""))}
 
 
 
#11
29.07.2026 13:11:43
Хорошо, если есть необходимость включить 4.06.2026 в расчёт количества нарушений по старым нормам, значит эта дата должна быть исключина из расчёта по новым, правильно? Уточните с начальная дата новых норм, это получается с 5.06.2026 применяются новае нормы?
Но есть ещё красивее непонятки.
Цитата
написал:
должно быть 5 (0,8, 1,53, 1,57, 0,84, 0,9).

Это по каким нормам? Посмотрел внимательно ваш файл прмер и оказалось что в ячейке О11 написано от 0,7, а в Р11 прописано 1,0. 0,7 не было видно из-за границ ячейки. Так по каким нормам вы считали? Формула считала от 1,0, а вы по видимому от 0,7. И указанные вами 5 нарушений никак не попадают в нормы от 1,0 до 1,5. Поясните, как должно быть, я не понимаю. Похоже что-то я не правильно для себя уяснил.  
 
 
 
#12
29.07.2026 13:20:05
Новые нормы вступают в действие с 04.06.2026. То есть
01.06.2026 старая норма
02.06.2026 старая норма
03.06.2026 старая норма
04.06.2026 новая норма
05.06.2026 новая норма и т.д.

Цитата
написал:
в ячейке О11 написано от 0,7, а в Р11 прописано 1,0. 0,7 не было видно из-за границ ячейки.
Мое упущение.

О11=от
P11=1
При копировании не исправила и из-за сдвинутых границ не увидела.
Изменено: avsvpav - 29.07.2026 13:26:31
 
 
 
#13
29.07.2026 13:25:04
Цитата
написал:
И указанные вами 5 нарушений никак не попадают в нормы от 1,0 до 1,5. Поясните, как должно быть, я не понимаю. Похоже что-то я не правильно для себя уяснил.  
Все значения из диапазона от 1 до 1,5 включительно попадают в норму. Все остальные - это нарушения.
0,8< 1 (нижняя граница нормы)
1,53>1.5 (верхняя граница нормы),
1,57>1.5,
0,84< 1
0,9< 1.
Соответственно, все эти значения - нарушения.
 
 
 
#14
29.07.2026 13:26:21
Тогда объясните как состыковать это
Цитата
написал:
04.06.2026 новая норма

И
Цитата
написал:
С 4.06.2026 действуют новые нормы от 0,7 до 1,1. В этот период тоже нашлось 1 нарушение со значением 1,5 за 04.06.2026.

Мне не понятно порог до 1,1 а 1,5 это нарушение, это как?  
Изменено: gling - 29.07.2026 14:50:29
 
 
 
#15
29.07.2026 13:29:11
Новая норма от 0,7 до 1,1 действует для Показателя 1 с 04.06.2026 включительно. Значения измерений по этому показателю в столбце С.
Новая норма от 1 до 1,5 действует для Показателя 2 с 04.06.2026 включительно. Значения измерений по этому показателю в столбце D.
 
 
 
#16
29.07.2026 13:30:31
Цитата
написал:
Мне не понятно порог до 1,1 а 1,5 это нарушение, это как?
То есть значение измерения 1.5 выше верхней границы нормы 1,1. Это не допустимо, поэтому это нарушение.
 
 
 
#17
29.07.2026 13:59:47
Цитата
написал:
Для выявления нарушений действуют нормы. Они могут меняться, т.е. до даты изменения действовала одна норма, а с даты изменения техдокументации - другая.

По этому описанию я воспринял, что это нормы нарушений. А оказывается это номы для правильных показаний, а что выходит за эти нормы, это нарушение. Надеюсь теперь правильно понял, конечно же и формулы надо переделывать под это понятие. Доберусь до компа, попробую.
Поправил формулы, попробовал проверить, не знаю правильно ли получилось. Проверяйте в файле.
Изменено: gling - 29.07.2026 19:51:32
 
 
 
#18
30.07.2026 11:07:30
Спасибо, что не оставляете попытки помочь.

В новом файле попробовала поменять разные периоды, нормы.

МАКС и МИН значения нарушений определяются не совсем корректно.

Необходимо находить не просто МАКС и МИН среди всех измерений, а именно макс и мин значения среди выявленных нарушений.
Цитата
написал:
Необходимо найти количество нарушений и определить мин значение нарушений по нижней границе и макс значение нарушений по верхней границе по каждому показателю.

Количество рассчитывается верно.

Еще столкнулась с одной проблемой.
В примере первоначально это не указала. Бывают случаи, что по какому-нибудь показателю измерений не было, и ячейка остается пустая, а в расчет эта ячейка попадает как нулевое значение. Это не верно. В этом случае, надо, чтоб ячейка не принималась в рассмотрение.
 
 
 
#19
30.07.2026 15:10:21
Потестите эти варианты.
 
 
 
#20
31.07.2026 13:47:51
Из того, что успела посмотреть - расчет мин и макс все-таки некорректный.

Меня сейчас перекинули на другой проект. Поэтому расчет ставлю на паузу. Обязательно вернусь к нему позже.

Предложенные Вами идеи я точно возьму для расчета. Спасибо Вам за участие.
 
 
 
#21
31.07.2026 15:54:55
Цитата
написал:
расчет мин и макс все-таки некорректный.
Вы бы подсказали в чём. А то опять таки по моему пониманию задачи, всё считает корректно. Только при отсутствии нарушений мин и макс показывают нули. Но это можно убрать форматом ячеек (Основной;-Основной;"") при необходимости.
 
 
 
Страницы: 1
Читают тему
Наверх