Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Наверно с помощью формулы ЕСЛИ с несколькими параметрами
 
#1
26.08.2009 11:45:53
Всем, Здравствуйте!    
Задача. Есть большой список ассортимента,на который мы прогнозируем продажи.    
Продажи могут быть ниже ( с учетом погрешности) прогноза, выше и в норме. Нужно посмотреть какой продукт мы хуже всего прогнозируем, т.е по русски формула такая. Если Этот продукт в норме, то например 0 баллов, если выше на 30% + 1, если выше на 40%+ 2 балла, тоже самое если ниже ну и потом как бы суммировать эти баллы и самый максимальный - это и есть самый плохой. Файл с коммент. в приложении
 
 
 
#2
26.08.2009 11:56:49
Подсчет количества ошибок:  
В ячейку Y6 поставить формулу =СЧЁТЕСЛИ(D6:W6;"Ошибка") (выдаст 9 штук, т.е. в ячейке F6 после слова ошибка стоит пробел).  
Либо такую формулу: ="Количество ошибок равно " & СЧЁТЕСЛИ(D6:W6;"Ошибка")  
 
Соответственно в ячейки D6:W6 ставится такая формула: =если(ИЛИ(D4>D2$D4<D1);"Ошибка";"Норм")  
 
По поводу вот этого:  
 
Если Этот продукт в норме, то например 0 баллов, если выше на 30% + 1, если выше на 40%+ 2 балла, тоже самое если ниже ну и потом как бы суммировать эти баллы и самый максимальный - это и есть самый плохой  
 
не совсем понял - в каких конкретно ячейках? Идет сумма процентов - т.е. 87% по факту, прогноз 117% - 1 балл, 127% - 2 балла, или же на 30% и на 40% больше - 87% по факту, 87%*130%=113,1% и 87%*140%=121,8%?
 
 
 
#3
26.08.2009 11:58:49
извиняюсь за описки:  
> (выдаст 9 штук, т.е. в ячейке F6 после слова ошибка стоит пробел).  
не т.е., а так как )  
 
>  =если(ИЛИ(D4>D2$D4<D1);"Ошибка";"Норм")  
=если(ИЛИ(D4>D2;D4<D1);"Ошибка";"Норм")
 
 
 
#4
26.08.2009 12:11:41
Спасибо за ответ. а можно подсчитать кол-во ошибок без строчки 6 . т.к это огромный список ассортимента. т.к в ячейке Y6 = уже было бы Если 26 % выбивается из 19-29;1если нет 0 + если след. число 60% выбивается из диапазона 42-62 то 1; если нет о и т.д по всем цифрам в Итоге получается 10 !    
По поводу баллов. На примере все того же первого числа 26%. Если 26 не выбивается из 19-29 то все ок. Если превышает или ниже на 30% ( хоть в плюс хоть в минус ) то например 2  балл, есть такое число в ячейке L4 там стоит 151% т.е превышает максимальный (120%) на 31 %
 
 
 
#5
26.08.2009 12:23:30
{quote}{login=Аксинья}{date=26.08.2009 12:11}{thema=Наверно с помощью формулы ЕСЛИ с несколькими параметрами}{post}Спасибо за ответ. а можно подсчитать кол-во ошибок без строчки 6 . т.к это огромный список ассортимента. т.к в ячейке Y6 = уже было бы Если 26 % выбивается из 19-29;1если нет 0 + если след. число 60% выбивается из диапазона 42-62 то 1; если нет о и т.д по всем цифрам в Итоге получается 10 !    
По поводу баллов. На примере все того же первого числа 26%. Если 26 не выбивается из 19-29 то все ок. Если превышает или ниже на 30% ( хоть в плюс хоть в минус ) то например 2  балл, есть такое число в ячейке L4 там стоит 151% т.е превышает максимальный (120%) на 31 %{/post}{/quote}  
 
Ну без дополнительной строчки не обойтись - т.е. 2 строчки критерии мин и макс, строчка с результатом и строчка с оценкой результата.  
Формула оценки м.б. вот такой:  
=ЕСЛИ(И(D4<D2;D4>=D1);0;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>=50%;3;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>=40%;2;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>=30%;1;))))  
 
Соответственно если >50%, то ставит 3; если >40%, то 2; если >30%, то 1; если в пределах между минимумом и максимумом - 0. Вы это имели ввиду?  
 
Формула считает в обе стороны (Скажем критерий от 70% до 80%. Если результат 120%, то поставит 3, если 30%, то поставит 2)
 
 
 
#6
26.08.2009 12:29:14
но тут такие интервалы:  
минимум-30% - максимум + 30% - 0 баллов  
минимум-40% - максимум + 40% - 1 балл  
минимум-50% - максимум + 50% - 2 балла  
Все остальное - 3 балла  
 
Если нужно так:  
минимум-максимум - 0 баллов  
минимум-30% - максимум + 30% - 1 балл  
минимум-40% - максимум + 40% - 2 балла  
Все остальное - 3 балла  
 
то формула такая:  
=ЕСЛИ(И(D4<D2;D4>D1);0;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>=40%;3;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>=30%;2;ЕСЛИ(МИН(ABS(D4-D2);ABS(D4-D1))>0%;1;))))
 
 
 
#7
26.08.2009 14:23:48
Мне кажется, так будет короче, но проверить не могу:  
 
=ПОИСКПОЗ(АБС(D4);{-1;0;30;40}%+0,1^14)-1  
 
константу массива можно естественно заменить на диапазон.
KL
 
 
 
#8
27.08.2009 03:47:15
Формула массива (в ячейке D6):  
=СУММ(ЕСЛИ((D4:W4<=D1:W1)+(D4:W4>=D2:W2);ЕСЛИ((ABS(D4:W4-D1:W1)<0,3)+(ABS(D4:W4-D2:W2)<0,3);1;ЕСЛИ((ABS(D4:W4-D1:W1)>=0,3)+(ABS(D4:W4-D2:W2)>=0,3);2));))  
Формулы в строке 5 можно удалить, это проверка правильности работы.
 
 
 
Страницы: 1
Читают тему
Наверх