Подбор значения по нескольким критериям

[Lednikova]

Помогите, пожалуйста.
Мне необходимо сделать калькулятор (к сожалению не могу загрузить вложение).

Изменяемые ячейки:
Тип продукта (Директ, Корп)
Страховка (Да, Нет)
Справка о доходах (Да, Нет)
Способ выдачи (Карта, Нал)
Желаемая сумма кредита (ввод значения)

Эти параметры, являются критериями поиска значения, например страхового тарифа, годовой ставки, еж.комиссии, одноразовой..
Т.е. в зависимости, какие параметры выбраны в выпадающих списках и какая введена сумма кредита, необходимо определить строчку в каталоге продуктов и вытянуть необходимый %.
Даныне в каталоге продуктов: Код субпродукта, тип продукта, срок, одноразовая комиссия, ежемесячная, страховой тариф, годовая ставка, кредитный лимит (мин, макс), наличие страховки, документы, способ выдачи.

Мне кажется это реально, но никак не могу правильно совместить форумулы ЕСЛИ, ВПР, СУММ..

Заранее благодарна..

[Hugo121]

Думаю, процент можно вытянуть с помощью СУММПРОИЗВ().
Но конечно нужно видеть Ваш файл - сообразите небольшой пример Вашей структуры данных для форума.

[Serge 007]

Уверен, что решить можно сводной таблицей.

Lednikova, а что мешает Вам загрузить небольшой (на десяток строк) файл-пример?

[Lednikova]

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

Калькулятор выглядит следующим образом:
Тип продукта (Директ, Корп) - выбор из списка
Страховка (Да, Нет) - выбор из списка
Справка о доходах (Да, Нет) - выбор из списка
Способ выдачи (Карта, Нал) - выбор из списка
Желаемая сумма кредита (ввод значения)
Желаемый срок кредита (6, 12, 18, 24, 30, 36) - выбор из списка

Платеж по кредиту, эфф.ставка, сумма кредита - расчитываются.

На другом Листе есть каталог продуктов, где есть такие параметры:
Наименование продукта, код продукта, тип продукта, срок кредита, одноразовая комиссия,%, ежемесячная комиссия,%, годовая ставка,%, страховой тариф, %, Кредитный лимит (UAH) мин, макс (например в рамках этого продукта можно взять в кредит от 500 грн до 10000), обязательные документы, способ выдачи.

Чем больше выбирается параметров в калькуляторе, тем поиск необходимого продукта, который мы можем предложить сокращается. Выбрав тип продукта, поиск сокращается до небольшого диапазона. Выбрав страховка, предлагаемый список сокращается до 10. Выбрав справку о доходах, способ выдачи, срок и указав желаемую сумму мы точно попадаем в уникальное совпадение.
И в поля, которые мне необходимо отобразить подтягиваются следующие значения:
одноразовая комиссия,%, ежемесячная комиссия,%, годовая ставка,%, страховой тариф, %.

Одно из условий, если есть страховка - одноразовой комисси нет и наоборот. Тогда если одно из значений стоит, то в другом должен возвращаться 0.

Надеюсь немного понятно.. ))

[EducatedFool]

Не могу загрузить файл

Вы предлагаете нам потратить пару часов своего времени, чтобы воссоздать ваш файл?
(в надежде, что он совпадёт с вашим)

А потом, когда вы скажете «ой, а у меня в файле всё иначе», ещё раз всё переделать «с нуля»?

[motorway]

Если бы было более полное и чёткое описание задачи, то я мог бы сделать макрос (за вознаграждение), который всё это делает...
Если просто функциями не обойтись, всегда можно написать custom macro.

[Serge 007]

Сообщение от Lednikova

...дома могу попробовать сделать похожий файл.

Мы не торопимся и можем подождать до тех пор, пока Вы окажетесь дома

Сообщение от Lednikova

Сводная таблица хорошо просто для работы с данными, а не для калькулятора, которым будет пользоваться сотрудник отделения при консультации клиента

Т.е. Вы лучше нас знаете возможности сводных таблиц?

Впрочем подождём файла, пока что-то утверждать бессмысленно.

[Lednikova]

Прилагаю вложение.
В выделенные ячейки необходимо подтянуть данные согласно выбранным параметрам в выпадающих списках и введенной сумме кредита.

[Hugo121]

Да, годовой процент можно получить так:

=SUMPRODUCT((M12:M23=C3)*(U12:U23=C5)*(V12:V23=C7)*(W12:W23=C9)*(N12:N23=C13)*(Q12:Q23))

Но если нет вариантов - то выдаёт 0. Это нужно как-то обработать - т.е. если 0, то писать "невозможно" и УФом красить например красным.
Но если использовать связанные зависимые выпадающие списки, тогда такого варианта быть не должно.
Ну а далее этот процент можно использовать в остальных расчётах.

[Lednikova]

=SUMPRODUCT((M12:M23=C3)*(U12:U23=C 5)*(V12:V23=C7)*(W12:W23=C9)*(N12:N 23=C13)*(T12:T23>=C11)*(S12:S23<=C1 1)*(Q12:Q23))
я еще дописала диапазон кредитного лимита. В зависимости какую сумму кредита он хочет..
а как сделать связанные списки?
или чтобы в соседней ячейке выдавало - совпадений не найдено