Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет ущерба от простоя автомобиля после аварии, оъединить две формулы с различными переменными и условиями в одну
 
#1
30.01.2020 13:36:35
Добрый день. Столкнулся с проблемой что не могу соединить две формулы в одну рабочую. Имеется 2 переменных значения, от выбора которых будут зависеть вычисления. Для наглядности.
Первая переменная имеет 2 варианта выбора:
Наша
50/50
Вторая переменная имеет 5 вариантов выбора:
Восстановил
Оплатил
Восст., оплатил
Восст.,   оплатил и осаго
Не восст., не оплатил
Если наступает сочетание двух переменных, то от этого зависит вычисления трех столбцов
"Простой",
"Ущерб"
"Осаго"
и записывается в столбец "Общая сумма".
Сама формула, точнее первая ее часть выглядит так:

=ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восстановил");[@Простой]+[@Осаго];ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Оплатил");[@Ущерб]+[@Осаго];ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восст., оплатил");[@Осаго];ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восст., оплатил и осаго");"0";ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Не восст., не оплатил");[@Простой]+[@Ущерб]+[@Осаго])))))

Так выглядит вторая часть формулы:

=ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Восстановил");[@Простой]/2;ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Оплатил");[@Ущерб];ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Восст., оплатил");"0";ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Не восст., не оплатил");([@Простой]/2)+[@Ущерб]))))

По отдельности формулы работают. а вместе не хотят: Выдает то значение "Ложь" то что то еще.
Подскажите пожалуйста как их можно объединить либо упростить может как то саму формулу, чтобы могло работать правильно.
 
 
 
#2
30.01.2020 14:40:44
"Корректировка имеющихся формул с множеством условий с целью создания одной рабочей формулы".

Суть вопроса модератора не понимаю. Тема разве не отражает задачу? Мне нужно объединить 2 формулы. Как еще  написать я не представляю. Прошу Вашей помощи. Как примерно должна выглядеть моя тема?
 
 
 
#3
30.01.2020 14:46:08
Цитата
Андрей-1821 написал:
Прошу Вашей помощи.
Ваша задача в бизнесе, абстрагируясь от формул и методов решения, это
"Расчет ущерба от простоя автомобиля после аварии", если я все правильно понял. Вот это и можно предложить в качестве темы.
Вот горшок пустой, он предмет простой...
 
 
 
#4
30.01.2020 14:46:59
Андрей-1821, было бы не плохо файлик для примера.
Изменено: Jykermann - 30.01.2020 14:49:26
Нет ничего сложнее поиска простого решения.
 
 
 
#5
30.01.2020 16:36:00
Файл с примером загрузил
 
 
 
#6
30.01.2020 17:06:11
 у вас в примере больше половины формул не выдают результат, так и должно быть?
 
 
 
#7
03.02.2020 10:44:59
Андрей-1821, общая формула выглядит так:
Код
=ЕСЛИОШИБКА(ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восстановил");[@Простой]+[@Осаго];
              ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Оплатил");[@Ущерб]+[@Осаго];
                         ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восст., оплатил");[@Осаго];
                                    ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Восст., оплатил и осаго");"0";
                                               ЕСЛИ(И([@Вина]="Наша";[@[Состояние ремонта]]="Не восст., не оплатил");[@Простой]+[@Ущерб]+[@Осаго];
                                                            ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Восстановил");[@Простой]/2;
                                                                       ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Оплатил");[@Ущерб];
                                                                                   ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Восст., оплатил");"0";
                                                                                              ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Не восст., не оплатил");([@Простой]/2)+[@Ущерб];"")))))))));"Нет")
Если Вы хотите получить числовое значение, то столбцы D и Е должны содержать суммы, а не "Нет". В противном случае формула проставляет "Нет".
Также у Вас не хватает условия для:
Код
ЕСЛИ(И([@Вина]="50/50";[@[Состояние ремонта]]="Восст., оплатил и осаго");
Допишите необходимое значение по аналогии.
Нет ничего сложнее поиска простого решения.
 
 
 
#8
03.02.2020 13:46:28
Еще вариант. Массивная формула
=ВЫБОР(ПОИСКПОЗ(G2&H2;{"50/50":"50/50":"50/50":"50/50":"50/50":"Наша":"Наша":"Наша":"Наша":"Наша"}&{"Восстановил":"Оплатил":"Восст., оплатил":"Восст., оплатил и осаго":"Не восст., не оплатил":"Восстановил":"Оплатил":"Восст., оплатил":"Восст., оплатил и осаго":"Не восст., не оплатил"};);C2/2+E2;D2+E2;E2;0;C2/2+D2+E2;C2+E2;D2+E2;E2;0;C2+D2+E2)
Изменено: abc1 - 03.02.2020 13:47:06
 
 
 
#9
03.02.2020 18:56:29
Цитата
Jykermann написал:
Если Вы хотите получить числовое значение, то столбцы D и Е должны содержать суммы, а не "Нет". В противном случае формула проставляет "Нет".
Не обязательно. В формулах можно заменить D3+E3 на АГРЕГАТ(9;6;D3:E3). Она хавает все что можно суммировать пропуская мусор.
 
 
 
#10
03.02.2020 20:38:25
Файл с рабочей единой формулой для всех ячеек во вложении. Хотя использовать такое количество вложений, конечно, форменный мазохизм)
суммы заменены на универсальную формулу АГРЕГАТ(9;7;D2;E2)
Еще в ячейках с числами должен быть именно числовой формат, т.к. часть "цифр" были с проб ела ми т.е фактически текстом, поэтому их формулы не считали и при замене на нормальный формат отскочили в левый край ячейки, выдав себя.  
Изменено: KLIJIN - 03.02.2020 20:47:27
 
 
 
#11
03.02.2020 20:49:56
KLIJIN,
Цитата
Не обязательно. В формулах можно заменить D3+E3 на АГРЕГАТ(9;6;D3:E3). Она хавает все что можно суммировать пропуская мусор.
Но в условии не сказано, что если "Нет", то нужно суммировать :)
Возможно, смысл в том, что если "Нет" одного значения, то "Нет" и общей суммы. Это уже ТС виднее.

И у Вас не правильно посчитана строка 2. По условию:
Код
[@Вина]="50/50";[@[Состояние ремонта]]="Восстановил");[@Простой]/2
А это получается: 1687,4 / 2 = 843,70
Нет ничего сложнее поиска простого решения.
 
 
 
#12
03.02.2020 21:07:38
ну лишние сравнения можно убрать
=IF(G2="50/50";IF(H2="Восстановил";(C2/2)+E2;
IF(H2="Оплатил";AGGREGATE(9;7;D2;E2);
IF(H2="Восст., оплатил";E2;
IF(H2="Восст., оплатил и осаго";"0";
IF(H2="Не восст., не оплатил";(C2/2)+AGGREGATE(9;7;D2;E2);)))));
IF(H2="Восстановил";AGGREGATE(9;7;C2;E2);
IF(H2="Оплатил";AGGREGATE(9;7;D2;E2);
IF(H2="Восст., оплатил";E2;
IF(H2="Восст., оплатил и осаго";"0";
IF(H2="Не восст., не оплатил";AGGREGATE(9;7;C2:E2);))))))

ну и медвежий вариант с паразитированием на AGGREGATE от KLIJIN,
=IFERROR(CHOOSE(MATCH(H2;$J$2:$J$6;)+5*(MATCH(G2;$K$2:$K$3;)-1);
AGGREGATE(9;7;C2;E2);
AGGREGATE(9;7;D2;E2);
E2;0;AGGREGATE(9;7;C2:E2);
(C2/2)+E2;
AGGREGATE(9;7;D2;E2);
E2;0;(C2/2)+AGGREGATE(9;7;D2;E2));"")

но в сами расчеты даже не смотрел.
Изменено: БМВ - 03.02.2020 21:08:51
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх