Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формула сравнения чисел с выдачей в результате определенного значения
 
#1
17.03.2023 13:52:27
Добрый день. Подскажите пожалуйста, с помощью какой формулы можно произвести сравнение значений с выводом определенного числа, в зависимости от результата?

Вот что нужно сравнить и какие результаты получить:

Дано: два числа (счет матча), которые записываются через двоеточие или дефис (хорошо бы чтобы любым из этих способов): - 2:1, 2-1 или оба варианта
Эти два числа нужно сравнивать и выдавать определенные заготовленные значения (в зависимости от условий):

Значения для вывода результата:

10, 8, 7, 6, 5, 4, 3, 2, 1

Условия сравнения:

- если в ячейке колонки "5" диапазона "ПРОГНОЗЫ" указано значение "5", а в колонке "РЕЗ" первое число больше второго или числа равны - в ячейке колонки "5" диапазона "ОЧКИ" прописать значение "10".
- если в ячейке колонки "5" диапазона "ПРОГНОЗЫ" указано значение "5", а в колонке "РЕЗ" первое число менше второго - в ячейке колонки "5" диапазона "ОЧКИ" прописать значение "0".  

- если в ячейке колонки "4" диапазона "ПРОГНОЗЫ" указано значение "4", а в колонке "РЕЗ" первое число больше второго или числа равны - в ячейке колонки "4" диапазона "ОЧКИ" прописать значение "8".  
- если в ячейке колонки "4" диапазона "ПРОГНОЗЫ" указано значение "5", а в колонке "РЕЗ" первое число менше второго - в ячейке колонки "5" диапазона "ОЧКИ" прописать значение "1".

- если в ячейке колонки "3" диапазона "ПРОГНОЗЫ" указано значение "4", а в колонке "РЕЗ" первое число больше второго или числа равны - в ячейке колонки "3" диапазона "ОЧКИ" прописать значение "7".  
- если в ячейке колонки "3" диапазона "ПРОГНОЗЫ" указано значение "5", а в колонке "РЕЗ" первое число менше второго - в ячейке колонки "5" диапазона "ОЧКИ" прописать значение "2".

И по такому принципу с оставшимися двумя значениями 2 и 1.

Другими словами: нужно чтобы указавшему тот или иной прогноз, выдавались очки, в зависимости от его прогноза

прогноз 5 - результат первая цифра больше или равна второй = 10 очков, если первая цифра меньше второй = 0 очков,
прогноз 4 - результат первая цифра больше или равна второй = 8 очков, если первая цифра меньше второй = 1 очков,
прогноз 3 - результат первая цифра больше или равна второй = 7 очков, если первая цифра меньше второй = 2 очков,
прогноз 2 - результат первая цифра больше или равна второй = 6 очков, если первая цифра меньше второй = 3 очков,
прогноз 1 - результат первая цифра больше или равна второй = 5 очков, если первая цифра меньше второй = 4 очков

Прикрепляю шаблон, в котором вручную указал как это должно работать.

---------------------------------------

Хочу дополнить, так как изначально не учел все моменты и не донес все что нужно.
Наверное стоит определить цель, быть может так понятнее будет:

я провожу турниры среди футбольных болельщиков, придумываю разные системы проведения и начисления очков. Эта одна из них. Этот калькулятор поможет мне проанализировать систему по ретро данным и отбалансировать ее.

Дополнил файл еще одним диапазоном. Вот поясниения:

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

Поэтому мне нужно понять как подставить такую формулу, которая будет учитывать оба прогноза. См. обновленный прикрепленный файл.
Таким образом я смогу прогнать несколько баз ретро данных по определенным условиям, чтобы понять, какие прогнозы при каких условия выгоднее ставить.
Изменено: Foliant - 17.03.2023 17:27:11
 
 
 
#2
17.03.2023 14:57:29
Зачем в прогнозах писать по столбцу одинаковые цифры 5 4 3 2 1  - они в Вашем примере вообще ни при чем или я не понял описанное условие
А так как понял одна формула массива
Код
=ЕСЛИ(
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")-
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[2]")>=0;
{10;8;7;6;5};
{0;1;2;3;4})
Изменено: Тимофеев - 17.03.2023 14:58:01
 
 
 
#3
17.03.2023 15:37:12
Цитата
написал:
Зачем в прогнозах писать по столбцу одинаковые цифры 5 4 3 2 1  - они в Вашем примере вообще ни при чем или я не понял описанное условие
Действительно, вы правы. Достаточно диапазона ОЧКИ. Спасибо большое за формулу, все отлично работает, буду разбираться.
 
 
 
#4
17.03.2023 15:58:38
) В ячейку I5 ,и тянем
=ЕСЛИ(ЧАС(ПОДСТАВИТЬ($B5;"-";":"))>=МИНУТЫ(ПОДСТАВИТЬ($B5;"-";":"));2*I$4+(СТОЛБЕЦ()-СТОЛБЕЦ($J$4))*(I$4<>5);СТОЛБЕЦ()-СТОЛБЕЦ($I$4))
 
 
 
#5
17.03.2023 16:04:09
Цитата
написал:
Зачем в прогнозах писать по столбцу одинаковые цифры 5 4 3 2 1  - они в Вашем примере вообще ни при чем или я не понял описанное условие
А так как понял одна формула массива
Код
    [URL=#]?[/URL]       1  2  3  4  5      =ЕСЛИ(    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[1]"  )-    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[2]"  )>=0;    {10;8;7;6;5};    {0;1;2;3;4})  
 
Прошу прощения, подскажите пожалуйста, что мне нужно изменить в формуле, чтобы все то же самое было только с немного другими условиями:

мне нужен еще одни отдельный калькулятор, в котором условие будет не "первое число больше другого или равно", а "первое число больше за второе на 2 и более раза".

Примеры:

3:1, 2:0, 6:1, 3:0, 4:0 и т.д - поставивший 5 получит 10 очков, 4 = 8, 3=7, 2=6, 1=5
 
 
 
#6
17.03.2023 17:28:32
Цитата
написал:
) В ячейку I5 ,и тянем
=ЕСЛИ(ЧАС(ПОДСТАВИТЬ($B5;"-";":"))>=МИНУТЫ(ПОДСТАВИТЬ($B5;"-";":"));2*I$4+(СТОЛБЕЦ()-СТОЛБЕЦ($J$4))*(I$4<>5);СТОЛБЕЦ()-СТОЛБЕЦ($I$4))
Спасибо, не могли бы вы посмотреть обновленный файл (я и описание немного дополнил) и помочь мне?
 
 
 
#7
17.03.2023 17:29:08
Код
=ЕСЛИ(ЦЕЛОЕ(ЕСЛИОШИБКА(
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")/
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[2]");
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")))
>=2;
{10;8;7;6;5};
{0;1;2;3;4})
 
 
 
#8
17.03.2023 17:29:48
Цитата
написал:
Зачем в прогнозах писать по столбцу одинаковые цифры 5 4 3 2 1  - они в Вашем примере вообще ни при чем или я не понял описанное условие
А так как понял одна формула массива
Код
    [URL=#]?[/URL]       1  2  3  4  5      =ЕСЛИ(    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[1]"  )-    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[2]"  )>=0;    {10;8;7;6;5};    {0;1;2;3;4})  
 
Спасибо, не могли бы вы помочь? Я там написал дополнение в описание, а то кажется я изначально не все учел. Файл тоже обновил.
 
 
 
#9
17.03.2023 17:32:50
сейчас перечитаю
Код
=ЕСЛИ(
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")-
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[2]")
>0;
{10;8;7;6;5};
{0;1;2;3;4})

=ЕСЛИ(ЦЕЛОЕ(ЕСЛИОШИБКА(
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")/
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[2]");
ФИЛЬТР.XML("<j><i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;"-";" ");":";" ");" ";"</i><i>")&"</i></j>";"//i[1]")))
<2;
{10;8;7;6;5};
{0;1;2;3;4})
Изменено: Тимофеев - 17.03.2023 17:38:15
 
 
 
#10
17.03.2023 17:38:09
Цитата
написал:
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7      =ЕСЛИ(ЦЕЛОЕ(ЕСЛИОШИБКА(    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[1]"  )/    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[2]"  );    ФИЛЬТР.XML(  "<j><i>"  &ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B5:B14;  "-"  ;  " "  );  ":"  ;  " "  );  " "  ;  "</i><i>"  )&  "</i></j>"  ;  "//i[1]"  )))    >=2;    {10;8;7;6;5};    {0;1;2;3;4})  
 
Там есть ошибка (или это я так объяснил). 1:0 считает правильно - очки не начисляются если прогноз некорректный, и при 2:1 тоже не должны начисляться, потому что разница тоже  в 1. А у вас 2:1 (в варианте 2) приносит тоже 10 очков, а должен 0.
Суть в том, что это фора. В первом варианте фора -0,5 - что значит что команда должны победить хотя бы с разницей в 1 мяч. А во втором варианте фора уже 1.5, что значит что команда должна победить уже с разницей минимум в 2 мяча.

Прочитайте пожалуйста дополнение (в конце описания) и посмотрите новый файл. Буду вам очень признателен.  
 
 
 
#11
17.03.2023 17:38:41
поправил в #9 но читал не очень внимательно уже пора пятницу отмечать - в цифры вроде попал  - потестируйте
Изменено: Тимофеев - 17.03.2023 17:45:29
 
 
 
#12
17.03.2023 18:20:04
Цитата
написал:
поправил в #9 но читал не очень внимательно уже пора пятницу отмечать - в цифры вроде попал  - потестируйте

Спасибо большое, потестировал. Нашел ошибку:

при счете 1:0 начисляются одинаковые очки и за прогнозы на П1 и за прогнозы на П2. За П2 при счете 1:0 должно быть 0-1-2-3-4. Поправьте пожалуйста, когда у вас будет время. Благодарю!
 
 
 
#13
17.03.2023 18:42:58
поправил
 
 
 
#14
17.03.2023 18:49:48
Цитата
написал:
поправил
Супер! Огромное вам спасибо. Хороших выходных!
 
 
 
#15
17.03.2023 18:57:54
Цитата
написал:
поправил
К сожалению еще не все :) При попытке добавить новою строку (а мне это очень нужно) выдает ошибку "нельзя изменить част массива" и не удается добавить строки. Подскажите пожалуйста с чем это связано? (В самом первом вашем варианте не было такой проблемы).
 
 
 
#16
18.03.2023 08:35:47
Цитата
написал:
Цитата
написал:
поправил
К сожалению еще не все  При попытке добавить новою строку (а мне это очень нужно) выдает ошибку "нельзя изменить част массива" и не удается добавить строки. Подскажите пожалуйста с чем это связано? (В самом первом вашем варианте не было такой проблемы).
Скопировал в новую книгу и проблема решилась.
 
 
 
Страницы: 1
Наверх