Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Как доработать функцию ВПР

Автор karlos67, 30.07.2012, 15:55

« назад - далее »

karlos67

Всем добрый день!

Решаю такую задачу (см. файл). Есть некая итоговая оценка, значение которой зависит от значений шести оценок, входящих в данную группу. В примере это столбец "Итоговая оценка" и столбцы "Оценка 1-6". Алгоритм присвоения итоговой оценки, в зависимости от оценок 1-6, четко описан и изменению не подлежит. Необходима формула для автоматического выбора значения итоговой оценки в зависимости от значений оценок группы (оценки 1-6). Для этого использую функцию ВПР. В таблице создал столбец "Код итоговой оценки" и с помощью СЦЕПИТЬ создаю некий код итоговой оценки, по которому потом и провожу сравнение.
Например (смотреть "Пример № 1"), если оценки с 1-ой по 5-ую имеют значение 1, а оценка № 6 равна 2, то итоговая оценка должна быть равна 1. В данном примере ВПР работает и выдает верный результат.

Проблема в следующем (смотреть "Пример № 2"). Существуют такие варианты присвоения итоговой оценки, когда она зависит не от всех значений оценок 1-6, а только от одной. Например: итоговая оценка будет равна 3, если значение оценки 5 равно 3. Значения остальных оценок не важны. Условие присвоения данной оценки приведено в строке 8 таблицы - "Оценка 5" = 3, значения остальных оценок = * (т.е. может быть любое). Результат работы функции СЦЕПИТЬ() по данному условию показывает, что в пятой позиции обязательно должно быть 3 (****3*). Естественно с таким значением ВПР отказывается работать.

Конечно можно добавить строк в таблицу и прописать все возможные значения, 111131, 111132, 111133 и т.д., но не хочется тратить на это время и такой подход не оптимален. Думаю правильнее будет использовать знаки подстановки и какую-нибудь функцию для сравнению (типа как условие "содержит" в автофильтре), чтобы сравнение значения оценки из примера № 2 (222231) и шаблона ****3* давало результат ИСТИНА.

Уже голову сломал!
Прошу помощи! Спасибо!

karlos67

Названия функций автоматически в гиперссылки преобразуются! Я аж испугался :)

Serge 007

В текст не особо вчитывался, не люблю "многабукаф".

Так надо?
=ВПР("~"&E19&E20&E21&E22&E23&E24;A2:B15;2;)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

karlos67

Цитата: Serge 007 от 30.07.2012, 16:05
В текст не особо вчитывался, не люблю "многабукаф".

Так надо?
=ВПР("~"&E19&E20&E21&E22&E23&E24;A2:B15;2;)

Выдает ошибку #Н/Д (значение недоступно). А зачем "~"?
Если убрать последнюю ";" выдает значение 4.

Serge 007

Цитата: karlos67 от 30.07.2012, 16:31
Выдает ошибку #Н/Д (значение недоступно).
В каком случае?

Цитата: karlos67 от 30.07.2012, 16:31
А зачем "~"?
Можно без неё

Цитата: karlos67 от 30.07.2012, 16:31
Если убрать последнюю ";" выдает значение 4.
Не надо самодеятельности :)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

karlos67

Serge 007, зачем поменяли значения оценок 1-5 в примере № 2 на *? Там должны быть именно цифры! А * - это типа любой символ, т.е. если Оценка 6 = 3, то при любых значениях оценок 1-5 итоговая оценка должна быть 3.

Serge 007

Вот теперь стало понятнее
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

karlos67

Нифигасе формула! Спасибо! Буду изучать.

DV68

Или так:
=ЕСЛИ(ИЛИ(E19=3;E20=3;E22=3;E23=3;E24=3);3;ЕСЛИ(ИЛИ(E19=4;E20=4;E22=4;E23=4);4;ВПР(E19&E20&E21&E22&E23&E24;A2:B15;2;)))

MCH

В примере рассписаны не все варианты, какая итоговая оценка должна быть для следующих вариантов?
1, 1, 3, 1, 1, 1
2, 2, 3, 2, 2, 2
1, 1, 4, 1, 1, 1
2, 2, 4, 2, 2, 2
2, 1, 1, 1, 1, 1
2, 2, 1, 1, 1, 1
2, 2, 2, 1, 1, 1

Serge 007

Цитата: Dmitr68 от 31.07.2012, 13:49
Или так:
Ещё вариант:
=ЕСЛИ(СУММПРОИЗВ((E19:E24={3;4})*{3;4})>2;СУММПРОИЗВ((E19:E24={3;4})*{3;4});ВПР(E19&E20&E21&E22&E23&E24;A2:B15;2;))
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

karlos67

Да MCH, условие не полное. Не хотел слишком грузить.
В принципе я уже решил эту задачу немного другим способом. Возможно слишком громоздко, но главное, что работает)
Если кому интересно поковырять, вот полное условие задачи.