Как отсортировать по время

Старая форма входа

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как отсортировать по время (Формулы/Formulas)

Adonis

Дата: Суббота, 11.07.2020, 19:34 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 21

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Здравствуйте. Как отсортировать время первого гола и последующих в разные ячейки с указанием кто забил (1 или 2 команда)

К сообщению приложен файл: 5071592.xlsx (42.9 Kb)

Ответить

Pelena

Дата: Суббота, 11.07.2020, 21:02 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19177

Репутация: 4417 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Если б не было вариантов типа 90+1 или 45+2, формула была бы гораздо проще, а так формула массива

Код

=ЕСЛИОШИБКА(ЕСЛИ(Z$1>0;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(--ЛЕВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($H2:$Y2;"0+";);"5+";5+ПРАВСИМВ($H2:$Y2));2);"");Z$1);ЕСЛИ(Y2="";"";ПРОСМОТР("яяя";$H$1:ИНДЕКС($H$1:$Y$1;ПОИСКПОЗ(Y2;ЕСЛИОШИБКА(--ЛЕВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($H2:$Y2;"0+";);"5+";5+ПРАВСИМВ($H2:$Y2));2);"");0)))));"")

К сообщению приложен файл: 8235254.xlsx (233.3 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Adonis

Дата: Суббота, 11.07.2020, 21:14 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 21

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Pelena, Спасибо

Ответить

bmv98rus

Дата: Воскресенье, 12.07.2020, 09:02 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата Pelena, 11.07.2020 в 21:02, в сообщении № 2 ()

90+1 или 45+2

Так как это дополнительные минуты, то это может быть и в основном времени 45, 90, то и в дополнительном то есть первое дополнительное время это 105 второе 120. Соответственно вместо 105+1 будет 10 и 12 вместо 120+1

Код

=IFERROR(INDEX($H2:$Y2;MATCH(SMALL(IFERROR(LEFT($H2:$Y2;FIND("+";$H2:$Y2&" +")-1)+IFERROR(MID($H2:$Y2;FIND("+";$H2:$Y2)+1;2););1=0);INT(COLUMNS($Z:Z)/2+1));IFERROR(LEFT($H2:$Y2;FIND("+";$H2:$Y2&" +")-1)+IFERROR(MID($H2:$Y2;FIND("+";$H2:$Y2)+1;2););1=0);));"")

Код

=IFERROR(INDEX($H$1:$Y$1;FLOOR(MATCH(SMALL(IFERROR(LEFT($H2:$Y2;FIND("+";$H2:$Y2&" +")-1)+IFERROR(MID($H2:$Y2;FIND("+";$H2:$Y2)+1;2););1=0);INT(COLUMNS($Z:Z)/2+1));IFERROR(LEFT($H2:$Y2;FIND("+";$H2:$Y2&" +")-1)+IFERROR(MID($H2:$Y2;FIND("+";$H2:$Y2)+1;2););1=0););9)+1);"")

К сообщению приложен файл: example1773.xlsx (44.9 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Воскресенье, 12.07.2020, 11:42

Ответить

Adonis

Дата: Воскресенье, 12.07.2020, 13:12 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 21

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

bmv98rus, Спасибо

Ответить

AlexM

Дата: Воскресенье, 12.07.2020, 22:13 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

Формула для времени массивная

Код

=ПРОСМОТР(;-ПОИСК(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2<>"";--ЛЕВБ($H2:$Y2;2));СТОЛБЕЦ(B$1)/2);$H2:$Y2);$H2:$Y2)

Вторая обычная

Код

=ЕСЛИ(ПОИСКПОЗ(Z2;$H2:$Y2;)<10;$H$1;$Q$1)

Скрыть ошибку функцией ЕСЛИОШИБКА()

К сообщению приложен файл: 5071592_01.xlsx (51.2 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

bmv98rus

Дата: Воскресенье, 12.07.2020, 23:01 | Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

AlexM, Алексей, если будет например и 90+1 и 90+2, то ...
За вторую - хоть и не самостоятельная, но простая, тут поддержу.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Понедельник, 13.07.2020, 08:35

Ответить

Светлый

Дата: Понедельник, 13.07.2020, 00:35 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Я вот тоже массивную универсальную формулу придумал. Для всех столбцов одна:

Код

=ЕСЛИОШИБКА(ЕСЛИ(ЕЧЁТН(СТОЛБЕЦ(B2));ИНДЕКС(2:2;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2>0;СТОЛБЕЦ($H:$Y)%%+ПОДСТАВИТЬ($H2:$Y2;"+";","));СТОЛБЕЦ(B2)/2);1%)/1%%);(ПОИСКПОЗ(Y2;$H2:$Y2;)>9)+1&"k");"")

*Или отдельными формулами:

Код

=ЕСЛИОШИБКА(ИНДЕКС(2:2;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2>0;СТОЛБЕЦ($H:$Y)%%+ПОДСТАВИТЬ($H2:$Y2;"+";","));СТОЛБЕЦ(B2)/2);1%)/1%%);"")

Код

=ЕСЛИОШИБКА((ПОИСКПОЗ(Z2;$H2:$Y2;)>9)+1&"k";"")

**В зависимости от настроек "," можно заменить на "."
***Ещё у других подсмотрел:

Код

=ЕСЛИОШИБКА(ЕСЛИ(Z$1>0;ИНДЕКС(2:2;ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2>0;СТОЛБЕЦ($H:$Y)%%+ПОДСТАВИТЬ($H2:$Y2;"+";","));СТОЛБЕЦ(B2)/2);1%)/1%%);(ПОИСКПОЗ(Y2;$H2:$Y2;)>9)+1&"k");"")

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

Сообщение отредактировал Светлый - Понедельник, 13.07.2020, 01:04

Ответить

AlexM

Дата: Понедельник, 13.07.2020, 07:41 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

Цитата bmv98rus, 12.07.2020 в 23:01, в сообщении № 7 ()

и 90+1 и 90+2

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

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 13.07.2020, 07:46

Ответить

bmv98rus

Дата: Понедельник, 13.07.2020, 07:54 | Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата AlexM, 13.07.2020 в 07:41, в сообщении № 9 ()

но думаю что такое не возможно.

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

Светлый, допустим был не самый хороший матч и к прибавленное время было 10мин
90+1 и 90+10 ......

Это не критика формул, это скорее определение ограничений этих формул.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Понедельник, 13.07.2020, 08:00

Ответить

AlexM

Дата: Понедельник, 13.07.2020, 07:59 | Сообщение № 11

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

bmv98rus, если ест 90+10, то 90+1 исключат.
Одной массивной формулой. ЕСЛИОШИБКА() добавить.

Код

=ЕСЛИ(Z$1=0;2-(ПОИСКПОЗ(Y2;$H2:$Y2;)<10)&"k";ПРОСМОТР(;-ПОИСК(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2<>"";--ЛЕВБ($H2:$Y2;2));СТОЛБЕЦ(B$1)/2);$H2:$Y2);$H2:$Y2))

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

AlexM

Дата: Понедельник, 13.07.2020, 08:49 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

bmv98rus, Возможно я исправил

Код

=ЕСЛИ(Z$1=0;2-(ПОИСКПОЗ(Y2;$H2:$Y2;)<10)&"k";ПРОСМОТР(;-ПОИСК(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2<>"";ЛЕВБ($H2:$Y2;2)+СТОЛБЕЦ($H2:$Y2)%);СТОЛБЕЦ(B$1)/2);ЛЕВБ($H2:$Y2;2)+СТОЛБЕЦ($H2:$Y2)%);$H2:$Y2))

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

bmv98rus

Дата: Понедельник, 13.07.2020, 08:55 | Сообщение № 13

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

сортировку доработал чуток

Код

=IFERROR(SUBSTITUTE(TEXT(SMALL(IFERROR(--(SUBSTITUTE(TRIM($H2:$Y2);"+";":")&":00");1=0);INT(COLUMNS($Z:Z)/2+1));"[ч]+м");"+0";);"")

Ограничение- добавлено не более 59 минут :-)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Понедельник, 13.07.2020, 09:00

Ответить

AlexM

Дата: Понедельник, 13.07.2020, 17:06 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

еще обнаружил ошибочку. Исправил

Код

=ЕСЛИ(Z$1=0;2-(ПОИСКПОЗ(Y2;$H2:$Y2;)<10)&"k";ПРОСМОТР(;-ПОИСК(НАИМЕНЬШИЙ(ЕСЛИ($H2:$Y2<>"";ЛЕВБ($H2:$Y2;2)+ЕОШ(-$H2:$Y2)*ПРАВБ($H2:$Y2;2));СТОЛБЕЦ(B$1)/2);ЛЕВБ($H2:$Y2;2)+ЕОШ(-$H2:$Y2)*ПРАВБ($H2:$Y2;2));$H2:$Y2))

К сообщению приложен файл: 7507313.xlsx (267.5 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

bmv98rus

Дата: Понедельник, 13.07.2020, 18:09 | Сообщение № 15

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Кстати , а что будет если в течении одной минуты будет два гола от противоположных команд? Это вопрос к ТС.

Вторая самостоятельная (подсмотренная :-). но на самом деле я не люблю так, но короче сильно )

Код

=IFERROR(1+(MATCH(SUBSTITUTE(TEXT(SMALL(IFERROR(--(SUBSTITUTE(TRIM($H2:$Y2);"+";":")&":00");1=0);INT(COLUMNS($Z:Z)/2+1));"[ч]+м");"+0";);$H2:$Y2&"";)>9)&"k";"")

К сообщению приложен файл: example1773_1.xlsx (44.8 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Понедельник, 13.07.2020, 18:37

Ответить

AlexM

Дата: Понедельник, 13.07.2020, 20:24 | Сообщение № 16

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4483

Репутация: 1114 ±

Замечаний: 0% ±

Excel 2003

Цитата bmv98rus, 13.07.2020 в 18:09, в сообщении № 15 ()

течении одной минуты будет два гола от противоположных команд?

формула на третьем листе

К сообщению приложен файл: 5071592_02.xlsx (431.2 Kb)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 13.07.2020, 20:25

Ответить

bmv98rus

Дата: Понедельник, 13.07.2020, 20:53 | Сообщение № 17

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

Репутация: 768 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата AlexM, 13.07.2020 в 20:24, в сообщении № 16 ()

ормула на третьем листе

Алексей, тут вопрос, а что было раньше, курица или яйцо? Может голы забивались в обратной последовательности. Именно по этому вопрос к ТС.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

Светлый

Дата: Понедельник, 13.07.2020, 22:45 | Сообщение № 18

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

Репутация: 510 ±

Замечаний: 0% ±

Excel 2013, 2016

Ещё вариант. Даже не массивная.

Код

=ЕСЛИОШИБКА(ЕСЛИ(Z$1>0;ИНДЕКС(2:2;ОСТАТ(АГРЕГАТ(15;6;ЛЕВБ($H2:$Y2;2)*100+ПРАВБ($H2:$Y2;2)+СТОЛБЕЦ($H2:$Y2)%;СТОЛБЕЦ(B2)/2);1)*100);(ПОИСКПОЗ(Y2;$H2:$Y2;)>9)+1&"k");"")