Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Определение трёх победителей по нескольким условиям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определение трёх победителей по нескольким условиям (Формулы/Formulas)
Определение трёх победителей по нескольким условиям
Andric Дата: Понедельник, 14.01.2019, 07:07 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте.
Помогите пожалуйста с решением. Который день бьюсь, не могу победить с виду казалось бы простое решение.

Задача: нужно вывести имена трёх победителей из C3:C10 в H3:H5 по условиям ниже.

Победители в H3:H5 определяется по критерию:
1. Наибольшее количество побед (WINS) из D3:D10, учитывая количество очков (Score) из E3:E10 (ниже).
2. При спорном количестве побед определяется наибольшее количество очков (Score).
3. При спорном количестве побед (WINS) и очков (Score), выводить надпись о споре между количеством участников, указывая имена участников (для простоты выяснения между кем и кем спор).

В первой колонке для понимания указал кто должен победить у кого спорные места, также ячейки с именами участников за спорные места вывести бы в отдельные ячейки.
Решение в H3:H5 хотелось бы получить единой формулой (для 1-3 мест в каждой ячейке соответственно своя формула).
К сообщению приложен файл: Wins3_2019-01-1.xls (30.0 Kb)
 
Ответить
СообщениеЗдравствуйте.
Помогите пожалуйста с решением. Который день бьюсь, не могу победить с виду казалось бы простое решение.

Задача: нужно вывести имена трёх победителей из C3:C10 в H3:H5 по условиям ниже.

Победители в H3:H5 определяется по критерию:
1. Наибольшее количество побед (WINS) из D3:D10, учитывая количество очков (Score) из E3:E10 (ниже).
2. При спорном количестве побед определяется наибольшее количество очков (Score).
3. При спорном количестве побед (WINS) и очков (Score), выводить надпись о споре между количеством участников, указывая имена участников (для простоты выяснения между кем и кем спор).

В первой колонке для понимания указал кто должен победить у кого спорные места, также ячейки с именами участников за спорные места вывести бы в отдельные ячейки.
Решение в H3:H5 хотелось бы получить единой формулой (для 1-3 мест в каждой ячейке соответственно своя формула).
Автор - Andric
Дата добавления - 14.01.2019 в 07:07
Светлый Дата: Понедельник, 14.01.2019, 08:18 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Здравствуйте!
На скорую руку монстрообразную массивную формулу написал:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));2));"")&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));3));"")
Ограничение до трёх претендентов на одно место.
*В файле подкорректировал формулы.
К сообщению приложен файл: Wins3_2019-01-1.xlsx (10.9 Kb)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 14.01.2019, 08:32
 
Ответить
СообщениеЗдравствуйте!
На скорую руку монстрообразную массивную формулу написал:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));2));"")&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0)=НАИМЕНЬШИЙ(МУМНОЖ(Ч(D$3:D$10*100+E$3:E$10<=ТРАНСП(D$3:D$10*100+E$3:E$10));D$3:D$10^0);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));3));"")
Ограничение до трёх претендентов на одно место.
*В файле подкорректировал формулы.
Автор - Светлый
Дата добавления - 14.01.2019 в 08:18
Светлый Дата: Понедельник, 14.01.2019, 09:35 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Немного укоротил формулу:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));2));"")&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));3));"")
*Лучше так:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));2));)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));3));)

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 14.01.2019, 09:56
 
Ответить
СообщениеНемного укоротил формулу:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));2));"")&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10))=НАИБОЛЬШИЙ(-(РАНГ(D$3:D$10;D$3:D$10)&РАНГ(E$3:E$10;E$3:E$10));СТРОКА(H2)+СУММ(ДЛСТР(H$2:H3)-ДЛСТР(ПОДСТАВИТЬ(H$2:H3;",";))));СТРОКА($3:$10));3));"")
*Лучше так:
Код
=ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));1))&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));2));)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИБОЛЬШИЙ(ЕСЛИ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10)=НАИМЕНЬШИЙ(РАНГ(D$3:D$10;D$3:D$10)/1%+РАНГ(E$3:E$10;E$3:E$10);СТРОКА(H1)+СУММ(ДЛСТР(H$2:H2)-ДЛСТР(ПОДСТАВИТЬ(H$2:H2;",";))));СТРОКА($3:$10));3));)
Автор - Светлый
Дата добавления - 14.01.2019 в 09:35
Светлый Дата: Среда, 16.01.2019, 16:29 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё немного доработал:
Код
=ИНДЕКС(C:C;ОСТАТ(МИН((СТРОКА($3:$10)%-D$3:D$10/1%-E$3:E$10)*(1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*")));1)/1%)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИМЕНЬШИЙ(ЕСЛИ(D$3:D$10/1%+E$3:E$10=МАКС((1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*"))*(D$3:D$10/1%+E$3:E$10));СТРОКА($3:$10));2));)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИМЕНЬШИЙ(ЕСЛИ(D$3:D$10/1%+E$3:E$10=МАКС((1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*"))*(D$3:D$10/1%+E$3:E$10));СТРОКА($3:$10));3));)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕщё немного доработал:
Код
=ИНДЕКС(C:C;ОСТАТ(МИН((СТРОКА($3:$10)%-D$3:D$10/1%-E$3:E$10)*(1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*")));1)/1%)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИМЕНЬШИЙ(ЕСЛИ(D$3:D$10/1%+E$3:E$10=МАКС((1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*"))*(D$3:D$10/1%+E$3:E$10));СТРОКА($3:$10));2));)&ЕСЛИОШИБКА(", "&ИНДЕКС(C:C;НАИМЕНЬШИЙ(ЕСЛИ(D$3:D$10/1%+E$3:E$10=МАКС((1-СЧЁТЕСЛИ(H$2:H2;"*"&C$3:C$10&"*"))*(D$3:D$10/1%+E$3:E$10));СТРОКА($3:$10));3));)
Автор - Светлый
Дата добавления - 16.01.2019 в 16:29
Nic70y Дата: Среда, 16.01.2019, 18:17 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8757
Репутация: 2271 ±
Замечаний: 0% ±

Excel 2010
Немного другой вариант.
место (со "2" строки)
Код
=ЕСЛИ((ВПР(H4;C:D;2;)+ВПР(H4;C:E;3;)/1000)=(ВПР(H3;C:D;2;)+ВПР(H3;C:E;3;)/1000);G3;ЛЕВБ(G3)+1&" место")
Player (массивная)
Код
=ИНДЕКС(C$1:C$10;ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(D$3:D$10+E$3:E$10/1000+СТРОКА(E$3:E$10)/1000000;СТРОКА(H1))*1000;1)*1000;))
+ Условное форматирование и черная заливка.
К сообщению приложен файл: 0461829.xlsx (11.1 Kb)

ЮMoney 41001841029809
 
Ответить
СообщениеНемного другой вариант.
место (со "2" строки)
Код
=ЕСЛИ((ВПР(H4;C:D;2;)+ВПР(H4;C:E;3;)/1000)=(ВПР(H3;C:D;2;)+ВПР(H3;C:E;3;)/1000);G3;ЛЕВБ(G3)+1&" место")
Player (массивная)
Код
=ИНДЕКС(C$1:C$10;ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(D$3:D$10+E$3:E$10/1000+СТРОКА(E$3:E$10)/1000000;СТРОКА(H1))*1000;1)*1000;))
+ Условное форматирование и черная заливка.
Автор - Nic70y
Дата добавления - 16.01.2019 в 18:17
Светлый Дата: Среда, 16.01.2019, 22:10 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата Nic70y, 16.01.2019 в 18:17, в сообщении № 5 ()
Немного другой вариант
Всё с первой строки в ячейках M3, L3. Массивные формулы:
Код
=ИНДЕКС(C:C;ОСТАТ(НАИМЕНЬШИЙ(СТРОКА($3:$10)%-D$3:D$10/1%-E$3:E$10;СТРОКА(M1));1)/1%)
Код
=ЕСЛИОШИБКА((СУММ((D$3:D$10+E$3:E$10%)*(ТРАНСП(M2:M3)=C$3:C$10)*{1;-1})<>0)+ЛЕВБ(L2);1)&" место"
Была идея в правых ячейках одноранговых размещать, но там Wins и Score, поэтому стал через запятую делать.

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата Nic70y, 16.01.2019 в 18:17, в сообщении № 5 ()
Немного другой вариант
Всё с первой строки в ячейках M3, L3. Массивные формулы:
Код
=ИНДЕКС(C:C;ОСТАТ(НАИМЕНЬШИЙ(СТРОКА($3:$10)%-D$3:D$10/1%-E$3:E$10;СТРОКА(M1));1)/1%)
Код
=ЕСЛИОШИБКА((СУММ((D$3:D$10+E$3:E$10%)*(ТРАНСП(M2:M3)=C$3:C$10)*{1;-1})<>0)+ЛЕВБ(L2);1)&" место"
Была идея в правых ячейках одноранговых размещать, но там Wins и Score, поэтому стал через запятую делать.
Автор - Светлый
Дата добавления - 16.01.2019 в 22:10
jakim Дата: Четверг, 17.01.2019, 17:45 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1199
Репутация: 313 ±
Замечаний: 0% ±

Excel 2010
Формулы
для столбца Wins

Код
=LARGE(D$3:D$10;ROWS($2:2))

для столбца Score

Код
=AGGREGATE(14;6;$E$3:$E$10/(D$3:D$10=J3);COUNTIF(J$3:J3;J3))

для столбца Player

Код
=LOOKUP(2;1/(D$3:D$10=J3)/(E$3:E$10=K3);C$3:C$10)
К сообщению приложен файл: 5797971.xlsx (9.9 Kb)
 
Ответить
Сообщение
Формулы
для столбца Wins

Код
=LARGE(D$3:D$10;ROWS($2:2))

для столбца Score

Код
=AGGREGATE(14;6;$E$3:$E$10/(D$3:D$10=J3);COUNTIF(J$3:J3;J3))

для столбца Player

Код
=LOOKUP(2;1/(D$3:D$10=J3)/(E$3:E$10=K3);C$3:C$10)
Автор - jakim
Дата добавления - 17.01.2019 в 17:45
Светлый Дата: Четверг, 17.01.2019, 22:43 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата jakim, 17.01.2019 в 17:45, в сообщении № 7 ()
для столбца Player
Из нескольких претендентов на одно место выдаёт одного и того же.
Формула для " Места". Заголовок с пробелом:
Код
=(СУММ((D$3:D$10+E$3:E$10%)*(ТРАНСП(H2:H3)=C$3:C$10)*{1;-1})<>0)+ПОДСТАВИТЬ(0&G2;G$2;)&G$2

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата jakim, 17.01.2019 в 17:45, в сообщении № 7 ()
для столбца Player
Из нескольких претендентов на одно место выдаёт одного и того же.
Формула для " Места". Заголовок с пробелом:
Код
=(СУММ((D$3:D$10+E$3:E$10%)*(ТРАНСП(H2:H3)=C$3:C$10)*{1;-1})<>0)+ПОДСТАВИТЬ(0&G2;G$2;)&G$2
Автор - Светлый
Дата добавления - 17.01.2019 в 22:43
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Определение трёх победителей по нескольким условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!