Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сравнение данных и округление при условии
 
#1
21.10.2021 16:48:10
Здравствуйте!

Помогите, пожалуйста, решить задачу (файл прилагаю, excel 2016).

Нужно получить правильное значение в ячейке X3. Есть три столбца со значениями:  столбцы M, P, S. Нужно чтобы число получившееся в ячейке К3, прошло проверку в какой диапазон значений между столбцами M и S оно попало. И округлилось в значение ячейки столбца M или P в зависимости от условия ниже. Проверяется диапазон от M3-S3 до M30-S30.  А далее идет приравнивание к числу в ячейке столбца M или P.
К примеру в ячейке K3 получилось значение 0,006 оно попадает в диапазон M3-S3(0,005-0,006). Далее условие: если  значение ячейки F3 больше или меньше 3, то X3=M3 (0,005), если же F3=3, то X3=P3(0,01). Опыт у меня с формулами excel крохотный. Я игрался с операторами ЕСЛИ, И, ИЛИ (формула(вшита в X3) получилась
Код
X3 =ЕСЛИ(И(K3>=M3;K3<=S3;ИЛИ(F3<3;F3>3));M3;P3)
. Она работает на одном диапазоне, как добавляю следующий (4 строку) - выскакивает #ЗНАЧ! - проверял по этапам как считает - вроде все верно. Но видимо оператор ЕСЛИ с И/ИЛИ не дает так много команд внести. Также игрался с ЕСЛИ,  И, НЕ - формула вшита в X4.
Код
=ЕСЛИ(И(НЕ(F3=3);K3>=M3;K3<=S3);M3;P3);ЕСЛИ(И(НЕ(F3=3);K3>=M4;K3<=S4);M4;P4)
 - то же самое. Пробовал с ВПР (недавно узнал об этой функции), но как совместно с условием F3 быть не знаю. Формула вшита в
Код
X5 =ВПР(K3;M3:M30;1;1)
.
Числа в столбцах M и P фиксированные. Числа в ячейке F3 меняются от 1 до 10. Число в ячейке К3 меняется.


PS: буду признателен, если будет решение задачи также при F3 не равно 3, но еще и не равно 5 (в общем к любому числу, а не только 3).
Изменено: Леонид - 25.10.2021 15:59:16
 
 
 
#2
23.10.2021 23:52:35
Здравствуйте!
Вариант решения прилагаю. Правда, не уверена, что правильно поняла логику Ваших вычислений. Если вам просто надо протянуть формулу по столбцу Х вниз, то решение в приложенном файле подойдет.  
 
 
 
#3
25.10.2021 14:32:01
Видимо я непонятно объяснил, пардон.
Ячейки X3, X4, X5  - это я игрался с  ЕСЛИ/И/ИЛИ, ЕСЛИ/И/НЕ, ВПР соответственно (скорее всего ни одна из этих формул в этих ячейках  не работает для моего случая, либо я неправильно формулу составил). Забудем про X4 и X5. На самом деле нужно, чтобы итоговое значение отображалось только  в одной ячейке X3.

Попробую перефразировать задачу.
К примеру:

1) если в ячейке К3 получилось число 0,006, оно попадает в диапазон 0,005-0,006 , при условии значения ячейки F3 не равно 3,  оно приравнивается к числу 0,005 (ячейка М3). Если значение ячейки F3 =3, то оно приравнивается к 0,01(ячейка P3). Т.е. значение ячейки К3=0,006 превратилось в Х3 =0,005 или 0,01.

2) если в ячейке К3 получилось число 1,1, оно попадает в диапазон 1-1,2 то ячейка Х3=1 при F3 <> 3. Или Х3=2, если F3=3.

3) если в ячейке К3 получилось число 25,5, оно попадает в диапазон 25-26, то ячейка Х3 =25 при F3 <> 3. Или Х3=30, если F3=3.
И так далее, в зависимости  от числа получившегося в ячейке K3 меняется и диапазон.

В первом случае число попало в диапазон ячеек M3-S3. Во втором случае в диапазон M16-S16. В третьем  в диапазон M24-S24.   В итоге ячейка Х3 в первом случае приравнивается к М3 или P3. Во втором - к M16 или P16, в третьем к М24 или P24 в зависимости от числа в F3. Сравниваются диапазоны значений столбцов М и S. А вот  итоговое значение в X3 приравнивается к значению столбцов М или P.

У меня туговато с формулировкой постановки задачи, но надеюсь на примерах стало понятнее :)
Заранее спасибо за помощь.
 
 
 
 
#4
25.10.2021 22:47:54
Файл с учётом проверки всего массива периодов прилагаю.
Только получается, что очень многие значения выпадают из указанных диапазонов, по крайней мере, при тех данных, что у Вас указаны в файле сейчас.
Например: при F3=1 получаем K3=0,024, а это число не входит ни в один диапазон М-S.  
 
 
 
#5
26.10.2021 16:53:10
Спасибо большое за помощь!

Формула работает. И да, Вы правы многие числа выпадают из диапазона M-S. Этого я не учел. Просчитался над решением, т.к. много колонок до этого сверял - не стал их выносить в файл.

Задача стоит такая:
есть диапазоны нагрузок тензодатчиков - столбцы M и P. М - минимальная нагрузка, P - максимальная. При подборе для заказчиков весовой системы из датчиков  допустимо использование датчиков с минимальной нагрузкой, если расчетная величина нагрузки датчика попадает в диапазон: минимальная нагрузка + небольшой запас. Это как раз диапазон значений столбцов М-S, в этом случае К3 приравнивается к значениям столбца М.
Если же выше этого значения М-S, при этом расчетная величина попадает в диапазон М-P (по сути диапазон М-P состоит из двух диапазонов M-S и S-P, получается сверять надо эти два диапазона ), тогда система выбирается из датчиков с большей нагрузкой.  В этом случае К3 приравнивается к значениям столбца P.  При этом я изменил условие по значению ячейки F3. Если K3 попало в диапазон M-S при F3<>3, то округление в значение ячейки столбца М, если F3=3, то округление в значение
ячейки столбца P. Если К3 попало в диапазон S-P, то округление в значение ячейки столбца P независимо от числа в F3.  Как-то так:

Код
Если К3>=М3 и К3<=S3 при F3<>3, то Х3=М3. Если F3=3, то Х3=P3.
Если K3>S3 и К3<=P3, то Х3=P3.
И так далее от строки 3 до 30 строки. Прошу помочь опять.

PS: Подскажите, пожалуйста, с какими операторами составили формулу? Видел в ней индекс, сумм и т.д. Можете ссылку на инфу для самообразования дать :).  
 
 
 
#6
26.10.2021 23:04:27
Исходя из условий:
Цитата
Леонид написал:
Код
Если К3>=М3 и К3 3, то Х3=М3. Если F3=3, то Х3=P3.
Если K3>S3 и К3<=P3, то Х3=P3.
получается формула такой:
Код
=ЕСЛИОШИБКА(ИНДЕКС(M3:M30;СУММ(($K$3>=M3:M30)*($K$3<=S3:S30)*(F3<>3)*СТРОКА(M3:M30))-2);ЕСЛИОШИБКА(ИНДЕКС(P3:P30;СУММ(($K$3>=M3:M30)*($K$3<=S3:S30)*(F3=3)*СТРОКА(P3:P30))-2);ИНДЕКС(P3:P30;СУММ(($K$3>=S3:S30)*($K$3<=P3:P30)*СТРОКА(P3:P30))-2)))
Это формула массива, т.е. вводится через Ctrl+Shift+Enter.
Файл с формулой прилагаю.
И всё равно в файле при Ваших данных при значении F3>=5 получается K3, выпадающие из диапазонов.
Цитата
Леонид написал:
Можете ссылку на инфу для самообразования дать .  
Вам нужно понять как работают формулы массива, на этом сайте тоже что-то было. Сама училась по формулам более опытных людей здесь на форуме. Посмотрите, как вычисляется формула в ячейке Х3 через вкладку Формулы-Зависимость формул-Вычислить формулу несколько раз, возможно Вам станет понятен механизм вычисления. Мне это точно помогает понять, где ошибка, как поправить.
 
 
 
#7
28.10.2021 14:40:12
Спасибо, буду изучать.
 
 
 
Страницы: 1
Наверх