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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск значений в зависимости заданных условий - Мир MS Excel

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

Excel 2007
Здравствуйте! Подскажите простое решение проблемы. Пример приложил. Во вкладке «Расчет» в столбце «C» заполняется последовательно через разный промежуток времени последовательно. В столбце «D» - параметр задаются числовые значения, а в столбцах «F» - слева и «G»-справа на основании данных листа «исходные данные» выбираются значения.
Пример: имеется «деталь 1», «параметр 1» равен 20. На листе «расчет» из списка в ячейке С2 выбирается соответствующая деталь. В ячейке D2 задается значение «параметр 1» - 20. После этого алгоритмом просматривается лист «исходные данные». Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 1» - 20 меньше 30, значит на листе «расчет» в ячейке F2 - «слева» должно появится значение 1, а в ячейке G2-«справа» тоже 1, так как значение меньше 36.
Второй расчет. Имеется та же деталь 1, но уже со значением «параметр 2», который как и первом случае так же равен 20. После выбора из списка в ячейке С3 соответствующей детали, в ячейку E3 задается значение параметра 2, проставляется цифра.20. После этого алгоритмом просматривается лист «исходные данные».
Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 2» - 20 меньше 44 и больше 28, значит на листе «расчет» в ячейке F3 - «слева» должно появится значение 3, а в ячейке G3-«справа» тоже 3, так как значение меньше 50 и больше 28.
Третий расчет. Имеется та же деталь 2, со значением «параметр 1», который равен 76. После выбора из списка в ячейке С4 соответствующей детали, в ячейку D4 задается значение параметра 2, проставляется цифра 76. После этого алгоритмом просматривается лист «исходные данные».
Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 1» - 76 меньше или равен 76 и больше 32, значит на листе «расчет» в ячейке F4 - «слева» должно появится значение 2, а в ячейке G3-«справа» тоже 3, так как значение меньше 126 и больше 74.
Если значение «параметров» попадает на границу то «значение допуска» выбирается напротив соответствующей цифры. Своими словами, если смотреть на лист «исходные данные» для детали1, «слева» в случае попадания значения от 0 до 34 включительно, то значение допуска – 1, если значение «параметр 1» больше 34 и меньше или равно 80, то значение допуска – 1.
Обратите внимание, что например для детали 1 в столбце «С» значения «слева» идут с разрывом 34, 80, 216, 274 и между 216 и 274 есть разрыв, то есть значение допуска 3 «слева» является общим до значений параметра1 – 216 и для параметра 2 – 44, а значение допуска 4, 5, 6, 7, 6, 5, 4 «слева» относится только для параметра 2, а далее значение допуска 1 также является общим для значений параметра1 – 274 и для параметра 2 – 274, то есть цифры в столбце «значения допуска» на листе «исходные данные» могут быть общими для столбцов «параметр 1» «и параметр 2», а могут относиться либо к столбцу «параметр 1» либо к столбцу «и параметр 2».
Столбцы «С» и «D» на листе заполняются последовательно через различный промежуток времени в зависимости от того какая деталь и с какими «параметрами» на выходе получается.
Типов деталей может быть и большее количество. Параметры деталей, так же динамические, то есть в данном примере для детали 1 количество строк "слева" и "справа" 11+13=24 в другой день количество может отличаться в большую или меньшую сторону.
Задачу решил, но очень коряво. Наплодил кучу листов, каждый отвечающий за одну деталь, в них поудалял пустые разрывы, сместил ячейки столбцов «значения допуска» и «параметр» на единицу относительно друг друга, значения параметров увеличил на 1, функцией «просмотр» выбрал нужные значения и при помощи огромного количества «Если», в зависимости от номера детали, в соответствующие ячейки написал «километровые» формулы. Все работает, но размер файла в итоге имеет нескромные размеры. Посмотрел большое количество сайтов, но упростить при помощи других команд не смог, хотя чувствую, что решение есть гораздо простое и короткое.
И еще, в столбце «D» - значения допуска могут так же быть нулевыми. А так же если значение полученного параметра превышает предельные размеры для каждой детали, то должно выводится слово - «превышение»
В Exel мои знания ограничены операторами «если» и «просмотр».
Пример прилагается.
Кто, что посоветует, по возможности без использования VBA. За решение или подсказку, что можно изучить буду благодарен.
К сообщению приложен файл: 4314663.xlsx (19.3 Kb)


Сообщение отредактировал АлександрРВТ - Суббота, 26.01.2019, 17:44
 
Ответить
СообщениеЗдравствуйте! Подскажите простое решение проблемы. Пример приложил. Во вкладке «Расчет» в столбце «C» заполняется последовательно через разный промежуток времени последовательно. В столбце «D» - параметр задаются числовые значения, а в столбцах «F» - слева и «G»-справа на основании данных листа «исходные данные» выбираются значения.
Пример: имеется «деталь 1», «параметр 1» равен 20. На листе «расчет» из списка в ячейке С2 выбирается соответствующая деталь. В ячейке D2 задается значение «параметр 1» - 20. После этого алгоритмом просматривается лист «исходные данные». Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 1» - 20 меньше 30, значит на листе «расчет» в ячейке F2 - «слева» должно появится значение 1, а в ячейке G2-«справа» тоже 1, так как значение меньше 36.
Второй расчет. Имеется та же деталь 1, но уже со значением «параметр 2», который как и первом случае так же равен 20. После выбора из списка в ячейке С3 соответствующей детали, в ячейку E3 задается значение параметра 2, проставляется цифра.20. После этого алгоритмом просматривается лист «исходные данные».
Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 2» - 20 меньше 44 и больше 28, значит на листе «расчет» в ячейке F3 - «слева» должно появится значение 3, а в ячейке G3-«справа» тоже 3, так как значение меньше 50 и больше 28.
Третий расчет. Имеется та же деталь 2, со значением «параметр 1», который равен 76. После выбора из списка в ячейке С4 соответствующей детали, в ячейку D4 задается значение параметра 2, проставляется цифра 76. После этого алгоритмом просматривается лист «исходные данные».
Результат, который должен получится: согласно данным листа «исходные данные» значение «параметр 1» - 76 меньше или равен 76 и больше 32, значит на листе «расчет» в ячейке F4 - «слева» должно появится значение 2, а в ячейке G3-«справа» тоже 3, так как значение меньше 126 и больше 74.
Если значение «параметров» попадает на границу то «значение допуска» выбирается напротив соответствующей цифры. Своими словами, если смотреть на лист «исходные данные» для детали1, «слева» в случае попадания значения от 0 до 34 включительно, то значение допуска – 1, если значение «параметр 1» больше 34 и меньше или равно 80, то значение допуска – 1.
Обратите внимание, что например для детали 1 в столбце «С» значения «слева» идут с разрывом 34, 80, 216, 274 и между 216 и 274 есть разрыв, то есть значение допуска 3 «слева» является общим до значений параметра1 – 216 и для параметра 2 – 44, а значение допуска 4, 5, 6, 7, 6, 5, 4 «слева» относится только для параметра 2, а далее значение допуска 1 также является общим для значений параметра1 – 274 и для параметра 2 – 274, то есть цифры в столбце «значения допуска» на листе «исходные данные» могут быть общими для столбцов «параметр 1» «и параметр 2», а могут относиться либо к столбцу «параметр 1» либо к столбцу «и параметр 2».
Столбцы «С» и «D» на листе заполняются последовательно через различный промежуток времени в зависимости от того какая деталь и с какими «параметрами» на выходе получается.
Типов деталей может быть и большее количество. Параметры деталей, так же динамические, то есть в данном примере для детали 1 количество строк "слева" и "справа" 11+13=24 в другой день количество может отличаться в большую или меньшую сторону.
Задачу решил, но очень коряво. Наплодил кучу листов, каждый отвечающий за одну деталь, в них поудалял пустые разрывы, сместил ячейки столбцов «значения допуска» и «параметр» на единицу относительно друг друга, значения параметров увеличил на 1, функцией «просмотр» выбрал нужные значения и при помощи огромного количества «Если», в зависимости от номера детали, в соответствующие ячейки написал «километровые» формулы. Все работает, но размер файла в итоге имеет нескромные размеры. Посмотрел большое количество сайтов, но упростить при помощи других команд не смог, хотя чувствую, что решение есть гораздо простое и короткое.
И еще, в столбце «D» - значения допуска могут так же быть нулевыми. А так же если значение полученного параметра превышает предельные размеры для каждой детали, то должно выводится слово - «превышение»
В Exel мои знания ограничены операторами «если» и «просмотр».
Пример прилагается.
Кто, что посоветует, по возможности без использования VBA. За решение или подсказку, что можно изучить буду благодарен.

Автор - АлександрРВТ
Дата добавления - 26.01.2019 в 15:07
krosav4ig Дата: Суббота, 26.01.2019, 19:57 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Здравствуйте.
Многабукафф, лень читать.
Цитата АлександрРВТ, 26.01.2019 в 15:07, в сообщении № 1 ()
20 меньше 44 и больше 28
:o
Состряпал на скорую руку, проверяйте
Код
=МАКС(('исходные данные'!$B$2:$B$141=$C2)*(МИН(ЕСЛИ(('исходные данные'!$B$2:$B$141=$C2)*(ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141)>=МАКС($D2:$E2))*('исходные данные'!$C$2:$C$141=F$1);ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141)))=ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141))*('исходные данные'!$C$2:$C$141=F$1)*(0&'исходные данные'!$D$2:$D$141))
К сообщению приложен файл: 5926047.xlsx (21.0 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 26.01.2019, 22:18
 
Ответить
СообщениеЗдравствуйте.
Многабукафф, лень читать.
Цитата АлександрРВТ, 26.01.2019 в 15:07, в сообщении № 1 ()
20 меньше 44 и больше 28
:o
Состряпал на скорую руку, проверяйте
Код
=МАКС(('исходные данные'!$B$2:$B$141=$C2)*(МИН(ЕСЛИ(('исходные данные'!$B$2:$B$141=$C2)*(ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141)>=МАКС($D2:$E2))*('исходные данные'!$C$2:$C$141=F$1);ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141)))=ЕСЛИ($D2<"";'исходные данные'!$E$2:$E$141;'исходные данные'!$F$2:$F$141))*('исходные данные'!$C$2:$C$141=F$1)*(0&'исходные данные'!$D$2:$D$141))

Автор - krosav4ig
Дата добавления - 26.01.2019 в 19:57
АлександрРВТ Дата: Суббота, 26.01.2019, 20:27 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
krosav4ig, спасибо за ответ!

Работает для "параметров 2", но не совсем правильно работает при задании "параметров 1".
К сообщению приложен файл: 4633045.xlsx (19.6 Kb)
 
Ответить
Сообщениеkrosav4ig, спасибо за ответ!

Работает для "параметров 2", но не совсем правильно работает при задании "параметров 1".

Автор - АлександрРВТ
Дата добавления - 26.01.2019 в 20:27
АлександрРВТ Дата: Суббота, 26.01.2019, 20:39 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте.
Многабукафф, лень читать.


я старался :)
 
Ответить
Сообщение
Здравствуйте.
Многабукафф, лень читать.


я старался :)

Автор - АлександрРВТ
Дата добавления - 26.01.2019 в 20:39
krosav4ig Дата: Суббота, 26.01.2019, 22:19 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Цитата АлександрРВТ, 26.01.2019 в 20:27, в сообщении № 3 ()
не совсем правильно работает при задании "параметров 1".

Забыл один диапазон пригвоздить, исправил в своем посте


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщение
Цитата АлександрРВТ, 26.01.2019 в 20:27, в сообщении № 3 ()
не совсем правильно работает при задании "параметров 1".

Забыл один диапазон пригвоздить, исправил в своем посте

Автор - krosav4ig
Дата добавления - 26.01.2019 в 22:19
АлександрРВТ Дата: Воскресенье, 27.01.2019, 00:36 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
krosav4ig спасибо за помощь! Тема закрыта.
 
Ответить
Сообщениеkrosav4ig спасибо за помощь! Тема закрыта.

Автор - АлександрРВТ
Дата добавления - 27.01.2019 в 00:36
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений в зависимости заданных условий (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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