Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/автоматический подсчет проб, несоответствующих нормативам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » автоматический подсчет проб, несоответствующих нормативам (Формулы/Formulas)
автоматический подсчет проб, несоответствующих нормативам
salymeco Дата: Среда, 04.05.2022, 09:42 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Добрый день, задача такая - чтоб автоматом считались пробы не соответствующие нормативам, который указан во 2 строке
проблема - в каждой пробе может исследоваться не одно вещество, считать надо именно пробы:

1. нужно чтоб в графе Е последовательно проверялось условие (по 33 веществам) и если оно больше норматива (хоть по одному веществу), то ставился какой нибудь значек (+/1), несоответсвия не должны суммироваться
2. так же нужно чтоб автоматом велся подсчет несоответствующих нормативам проб по группам показателей (микробиология (мкб), органолептика (орг), обобщенные (общ), неорганические вещества (неорг), радиология (рад) - это столбец В (показатели выделены по цветам)
К сообщению приложен файл: 2021--.xlsx (110.3 Kb)
 
Ответить
СообщениеДобрый день, задача такая - чтоб автоматом считались пробы не соответствующие нормативам, который указан во 2 строке
проблема - в каждой пробе может исследоваться не одно вещество, считать надо именно пробы:

1. нужно чтоб в графе Е последовательно проверялось условие (по 33 веществам) и если оно больше норматива (хоть по одному веществу), то ставился какой нибудь значек (+/1), несоответсвия не должны суммироваться
2. так же нужно чтоб автоматом велся подсчет несоответствующих нормативам проб по группам показателей (микробиология (мкб), органолептика (орг), обобщенные (общ), неорганические вещества (неорг), радиология (рад) - это столбец В (показатели выделены по цветам)

Автор - salymeco
Дата добавления - 04.05.2022 в 09:42
msi2102 Дата: Среда, 04.05.2022, 14:36 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 413
Репутация: 129 ±
Замечаний: 0% ±

Excel 2007
Попробуйте так
1.3. Органолептические
Код
=СУММПРОИЗВ((I8:M16>I3:M3)*1)

или формула массива
Код
=СУММ(--(I8:M16>I3:M3))


1.4. Обобщенные
Код
=СУММПРОИЗВ((N8:S16>N3:S3)*1)

или формула массива
Код
=СУММ(--(N8:S16>N3:S3))


Сообщение отредактировал msi2102 - Среда, 04.05.2022, 14:46
 
Ответить
СообщениеПопробуйте так
1.3. Органолептические
Код
=СУММПРОИЗВ((I8:M16>I3:M3)*1)

или формула массива
Код
=СУММ(--(I8:M16>I3:M3))


1.4. Обобщенные
Код
=СУММПРОИЗВ((N8:S16>N3:S3)*1)

или формула массива
Код
=СУММ(--(N8:S16>N3:S3))

Автор - msi2102
Дата добавления - 04.05.2022 в 14:36
NikitaDvorets Дата: Среда, 04.05.2022, 15:36 | Сообщение № 3
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
salymeco, добрый день.

Вариант решения: применение UDF - пользовательских функций по каждой из задач.
Модель анализа проб на соответствие нормативам прилагается.
К сообщению приложен файл: ___04-05-2022.xlsm (122.6 Kb)
 
Ответить
Сообщениеsalymeco, добрый день.

Вариант решения: применение UDF - пользовательских функций по каждой из задач.
Модель анализа проб на соответствие нормативам прилагается.

Автор - NikitaDvorets
Дата добавления - 04.05.2022 в 15:36
salymeco Дата: Четверг, 05.05.2022, 06:40 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Добрый день, спасибо большое!!! hands hands hands

Я так понимаю это макрос? а без макроса не получится?, у меня большой объем, и переносить все в файл с поддержкой макросов - займет много времени..., к тому же я не знаю как это сделать :(


Сообщение отредактировал salymeco - Четверг, 05.05.2022, 08:27
 
Ответить
СообщениеДобрый день, спасибо большое!!! hands hands hands

Я так понимаю это макрос? а без макроса не получится?, у меня большой объем, и переносить все в файл с поддержкой макросов - займет много времени..., к тому же я не знаю как это сделать :(

Автор - salymeco
Дата добавления - 05.05.2022 в 06:40
salymeco Дата: Четверг, 05.05.2022, 08:25 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Код
=СУММПРОИЗВ((I8:M16>I3:M3)*1)
спасибо, к сожалению формула не правильно работает: одна строка - это одна проба, в одной пробе (строке) может быть несколько веществ, которые не соответствуют нормативам, надо чтоб было посчитано за одну пробу, а данная формула суммирует все несоответствия по всему диапазону

формулы NikitaDvorets тоже так считают..., досадно :(


Сообщение отредактировал Serge_007 - Пятница, 13.05.2022, 11:07
 
Ответить
Сообщение
Код
=СУММПРОИЗВ((I8:M16>I3:M3)*1)
спасибо, к сожалению формула не правильно работает: одна строка - это одна проба, в одной пробе (строке) может быть несколько веществ, которые не соответствуют нормативам, надо чтоб было посчитано за одну пробу, а данная формула суммирует все несоответствия по всему диапазону

формулы NikitaDvorets тоже так считают..., досадно :(

Автор - salymeco
Дата добавления - 05.05.2022 в 08:25
Pelena Дата: Четверг, 05.05.2022, 08:55 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
ещё вариант
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>F3:H3);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))
К сообщению приложен файл: 2021-.xlsx (112.6 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеещё вариант
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>F3:H3);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))

Автор - Pelena
Дата добавления - 05.05.2022 в 08:55
NikitaDvorets Дата: Четверг, 05.05.2022, 09:35 | Сообщение № 7
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
salymeco, добрый день.

Цитата
к сожалению формула не правильно работает: одна строка - это одна проба, в одной пробе (строке) может быть несколько веществ, которые не соответствуют нормативам, надо чтоб было посчитано за одну пробу, а данная формула суммирует все несоответствия по всему диапазону

Может быть, тогда в формулировке задачи имело бы смысл добавить столбцы по категориям веществ и изменить диапазоны суммирования? Модель прилагается.
Цитата
переносить все в файл с поддержкой макросов - займет много времени..., к тому же я не знаю как это сделать

Не так много, как Вам кажется....достаточно сохранить оригинальный файл по типу "с поддержкой макросов" и вставить туда модули vba.
К сообщению приложен файл: ___05-05-2022.xlsm (197.7 Kb)
 
Ответить
Сообщениеsalymeco, добрый день.

Цитата
к сожалению формула не правильно работает: одна строка - это одна проба, в одной пробе (строке) может быть несколько веществ, которые не соответствуют нормативам, надо чтоб было посчитано за одну пробу, а данная формула суммирует все несоответствия по всему диапазону

Может быть, тогда в формулировке задачи имело бы смысл добавить столбцы по категориям веществ и изменить диапазоны суммирования? Модель прилагается.
Цитата
переносить все в файл с поддержкой макросов - займет много времени..., к тому же я не знаю как это сделать

Не так много, как Вам кажется....достаточно сохранить оригинальный файл по типу "с поддержкой макросов" и вставить туда модули vba.

Автор - NikitaDvorets
Дата добавления - 05.05.2022 в 09:35
salymeco Дата: Пятница, 13.05.2022, 08:40 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Цитата NikitaDvorets, 05.05.2022 в 09:35, в сообщении № 7 ()
Не так много, как Вам кажется....достаточно сохранить оригинальный файл по типу "с поддержкой макросов" и вставить туда модули vba


СПАСИБО, но для меня Вы как будто говорите на иностранном языке

модули vba?

к сожалению все равно не работает- в одной пробе 2 вещества не соответствуют, а проба то одна!!! и цифра должна стоять в стоке - 1
К сообщению приложен файл: _05-05-2022.xlsm (198.3 Kb)


Сообщение отредактировал salymeco - Пятница, 13.05.2022, 09:20
 
Ответить
Сообщение
Цитата NikitaDvorets, 05.05.2022 в 09:35, в сообщении № 7 ()
Не так много, как Вам кажется....достаточно сохранить оригинальный файл по типу "с поддержкой макросов" и вставить туда модули vba


СПАСИБО, но для меня Вы как будто говорите на иностранном языке

модули vba?

к сожалению все равно не работает- в одной пробе 2 вещества не соответствуют, а проба то одна!!! и цифра должна стоять в стоке - 1

Автор - salymeco
Дата добавления - 13.05.2022 в 08:40
salymeco Дата: Пятница, 13.05.2022, 09:27 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>F3:H3);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))
работает, СПАСИБО


Сообщение отредактировал Serge_007 - Пятница, 13.05.2022, 09:29
 
Ответить
Сообщение
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>F3:H3);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))
работает, СПАСИБО

Автор - salymeco
Дата добавления - 13.05.2022 в 09:27
Serge_007 Дата: Пятница, 13.05.2022, 09:30 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
модули vba?
http://www.excelworld.ru/dir/code/vba/excel_macro/9-1-0-12

Автор - Serge_007
Дата добавления - 13.05.2022 в 09:30
NikitaDvorets Дата: Пятница, 13.05.2022, 09:40 | Сообщение № 11
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
Цитата
http://www.excelworld.ru/dir/code/vba/excel_macro/9-1-0-12


Полностью согласен, речь идет о макросах.
 
Ответить
Сообщение
Цитата
http://www.excelworld.ru/dir/code/vba/excel_macro/9-1-0-12


Полностью согласен, речь идет о макросах.

Автор - NikitaDvorets
Дата добавления - 13.05.2022 в 09:40
NikitaDvorets Дата: Пятница, 13.05.2022, 09:50 | Сообщение № 12
Группа: Авторы
Ранг: Ветеран
Сообщений: 548
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
salymeco, добрый день.

Цитата
к сожалению все равно не работает- в одной пробе 2 вещества не соответствуют, а проба то одна!!! и цифра должна стоять в стоке - 1

Этот момент легко исправляется.
Но, поскольку Pelena разработала формулу, Вам, скорее всего, её вариант удобнее.


Сообщение отредактировал NikitaDvorets - Пятница, 13.05.2022, 09:51
 
Ответить
Сообщениеsalymeco, добрый день.

Цитата
к сожалению все равно не работает- в одной пробе 2 вещества не соответствуют, а проба то одна!!! и цифра должна стоять в стоке - 1

Этот момент легко исправляется.
Но, поскольку Pelena разработала формулу, Вам, скорее всего, её вариант удобнее.

Автор - NikitaDvorets
Дата добавления - 13.05.2022 в 09:50
salymeco Дата: Пятница, 13.05.2022, 13:33 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Добрый день, спасибо всем!

как же все таки быть с построчной проверкой?
сообщение №1: "...чтоб в графе Е последовательно проверялось условие (по 33 веществам - это в примере строки 8,9,10) и если оно больше норматива (в примере это строка 3, она постоянная) (хоть по одному веществу), то ставился какой нибудь значек (+/1) (наверное лучше ставить значек +, а не циыру, чтоб не путаться), несоответсвия не должны суммироваться".
К сообщению приложен файл: 0961022.xlsx (111.8 Kb)


Сообщение отредактировал salymeco - Пятница, 13.05.2022, 13:39
 
Ответить
СообщениеДобрый день, спасибо всем!

как же все таки быть с построчной проверкой?
сообщение №1: "...чтоб в графе Е последовательно проверялось условие (по 33 веществам - это в примере строки 8,9,10) и если оно больше норматива (в примере это строка 3, она постоянная) (хоть по одному веществу), то ставился какой нибудь значек (+/1) (наверное лучше ставить значек +, а не циыру, чтоб не путаться), несоответсвия не должны суммироваться".

Автор - salymeco
Дата добавления - 13.05.2022 в 13:33
salymeco Дата: Вторник, 17.05.2022, 12:54 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Добрый день, спасибо большое всем за помощь в решении данной задачи, прилагаю к сообщению работающий файл
К сообщению приложен файл: 2022__.xlsx (111.8 Kb)
 
Ответить
СообщениеДобрый день, спасибо большое всем за помощь в решении данной задачи, прилагаю к сообщению работающий файл

Автор - salymeco
Дата добавления - 17.05.2022 в 12:54
salymeco Дата: Среда, 18.05.2022, 14:15 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Добрый день, хочу попросить помощи в дополнение к уже решенной задаче

чтоб еще считалось количество проб по показателям?, например в столбце С считались пробы по показателям (мкб, общ, .... и т.д.) - выделено желтым, а далее уже решенная задача в столбце F (НЕ СООТВ)

условия те же: проба одна, а веществ может быть несколько в одной пробе, т.е считались только пробы в диапазоне
К сообщению приложен файл: 2022_.xlsx (111.4 Kb)


Сообщение отредактировал salymeco - Среда, 18.05.2022, 14:33
 
Ответить
СообщениеДобрый день, хочу попросить помощи в дополнение к уже решенной задаче

чтоб еще считалось количество проб по показателям?, например в столбце С считались пробы по показателям (мкб, общ, .... и т.д.) - выделено желтым, а далее уже решенная задача в столбце F (НЕ СООТВ)

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

Автор - salymeco
Дата добавления - 18.05.2022 в 14:15
Pelena Дата: Среда, 18.05.2022, 18:46 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Собственно, формула почти та же
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>0);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))
К сообщению приложен файл: 2622485.xlsx (112.9 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеСобственно, формула почти та же
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16>0);0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))

Автор - Pelena
Дата добавления - 18.05.2022 в 18:46
salymeco Дата: Среда, 18.05.2022, 20:54 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Спасибо огромное, все работает, только последнее изменение почему то не считает пробы с показателем 0 (ноль), а такой результат очень даже может быть и поставить прочерк или оставить пустую ячейку я не могу...

вы не представляете насколько это нужные для работы данные, и в ручную их обрабатывать занимает уйму времени
К сообщению приложен файл: 9968582.xlsx (111.6 Kb)


Сообщение отредактировал salymeco - Среда, 18.05.2022, 21:28
 
Ответить
СообщениеСпасибо огромное, все работает, только последнее изменение почему то не считает пробы с показателем 0 (ноль), а такой результат очень даже может быть и поставить прочерк или оставить пустую ячейку я не могу...

вы не представляете насколько это нужные для работы данные, и в ручную их обрабатывать занимает уйму времени

Автор - salymeco
Дата добавления - 18.05.2022 в 20:54
Pelena Дата: Среда, 18.05.2022, 23:43 | Сообщение № 18
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
вместо >0 поставьте во всех формулах <>""
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16<>"");0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениевместо >0 поставьте во всех формулах <>""
Код
=СУММПРОИЗВ(--(МУМНОЖ(ЕСЛИОШИБКА(--(F8:H16<>"");0);ТРАНСП(СТОЛБЕЦ(F3:H3))^0)>0))

Автор - Pelena
Дата добавления - 18.05.2022 в 23:43
salymeco Дата: Четверг, 19.05.2022, 07:21 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 24
Репутация: 0 ±
Замечаний: 0% ±

Спасибо, теперь все работает как надо
 
Ответить
СообщениеСпасибо, теперь все работает как надо

Автор - salymeco
Дата добавления - 19.05.2022 в 07:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » автоматический подсчет проб, несоответствующих нормативам (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!