Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Excel для Mac
Страницы: 1
RSS
альтернатива РАНГ ?, альтернатива функции РАНГ
 
#1
07.01.2023 23:28:50
Добрый вечер.
...уже не помню какую неделю мучаюсь(бьюсь головой об стену) с функцией РАНГ.
Гугл и Яндекс оказались бесполезны в поисках конкретно моей проблемы.
Может кто знает, есть ли альтернатива функции РАНГ, чтобы избавится от функции РАНГ как от ночного кошмара раз и навсегда.

Задача: ранжировать время заплыва по маске времени "00:00,00"
Ну как бы РАНГ с этим справляется, однако для меня есть два больших минуса этой функции:

- если в списке времени есть время 00:00,00 (нулевое время), то РАНГ присваивает 1-е место пловцу проплывшему за 00:00,00... это дико смешно. И как отучить РАНГ от этой привычки - не понятно.

- и второй главный минус (не знаю, может это у меня глючит эксель):

ВРЕМЯ       МЕСТО
00:34,06          4
-                       -
00:23,01          1
-                       -
-                       -
00:28,07          2
00:43,07          5
01:04,01          6
00:28,07          2

после 2-го места идёт 4-е. Как такое может быть?
Да, в этом примере два вторых места.
Логично предположить, что после второго места(хоть сколько бы их ни было) идёт третье - а тут - четвертое. Как так?
И вместо нулевого времени пришлось выставить прочерк.

Формула РАНГ вот такая: =ЕСЛИОШИБКА(РАНГ(K6;K6:K14;1);"-")
(пришлось извращаться добавив ЕСЛИОШИБКА, чтобы РАНГ смог работать хотяб с прочерком вместо нулевого времени)

Можно с этим что-то сделать и заодно отучить РАНГ присваивать 1-е место за время 00:00,00 ?
Изменено: Данил И - 08.01.2023 19:34:07
 
 
 
#2
07.01.2023 23:58:35
4ernika CS,  ознакомьтесь с правилами и поменяйте отображаемое имя.
Ваш вопро касается исключительно Мас?
 
 
 
#3
08.01.2023 00:44:59
У меня Mac с Эксель 16.66.1
далее буду мучаться с трансляцией таблиц XLS в Гугл Документы или Яндекс Документы.... но это уже другая история.
Изменено: 4ernika CS - 08.01.2023 00:45:17
 
 
 
#4
08.01.2023 00:52:08
Про Мас понятно, а как насчёт этого
Цитата
Юрий М написал:
ознакомьтесь с правилами и поменяйте отображаемое имя.
 
 
 
#5
08.01.2023 00:55:14
уже поменял. но в постах почему-то не меняется имя
 
 
 
#6
08.01.2023 01:02:12
Все новые сообщения пойдут с новым ником )
 
 
 
#7
08.01.2023 21:42:39
Немного разобрался.
почти удалось дожать функцию РАНГ... но не до конца :(

Теперь с такой формулой:
- порядок проставления мест идёт в правильном порядке для соревнований по плаванию.
- однако теперь, при такой извратной формуле, если пловец №6 и пловец №9 плывут в одинаковое время, то 2-е место только у одного пловца...
а у нас одинаковые места могут занять аж до 3-х пловцов: спокойно может быть три первых места или три вторых....
Так что, думаем дальше, может у кого найдётся свежее решение.


ВРЕМЯ         МЕСТО
00:34,06            4        =ЕСЛИОШИБКА(РАНГ(H60;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H60;H60)-1;"-")
-                         -        =ЕСЛИОШИБКА(РАНГ(H61;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H61;H61)-1;"-")
00:23,01            1        =ЕСЛИОШИБКА(РАНГ(H62;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H62;H62)-1;"-")
-                         -        =ЕСЛИОШИБКА(РАНГ(H63;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H63;H63)-1;"-")
-                         -        =ЕСЛИОШИБКА(РАНГ(H64;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H64;H64)-1;"-")
00:28,07            2        =ЕСЛИОШИБКА(РАНГ(H65;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H65;H65)-1;"-")
00:43,07            5        =ЕСЛИОШИБКА(РАНГ(H66;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H66;H66)-1;"-")
01:04,01            6        =ЕСЛИОШИБКА(РАНГ(H67;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H67;H67)-1;"-")
00:28,07            3        =ЕСЛИОШИБКА(РАНГ(H68;H$60:H$68;1)+СЧЁТЕСЛИ(H$60:H68;H68)-1;"-")
Изменено: Данил И - 08.01.2023 21:43:39
 
 
 
#8
21.01.2023 15:06:45
608 564,  поменяйте, пожалуйста, отображаемое имя - не может имя состоять только из цифр.
Ваше сообщение скрыл - какое отношение оно имеет к заявленной теме?
 
 
 
#9
29.01.2023 09:51:11
РЕШЕНО!

Напомню, задача была найти какую-нибудь альтернативу функции =РАНГ для ранжирования(простановки) мест в спортивных соревнованиях.

Функция =РАНГ, равно как и =РАНГ.РВ не подходит для ранжирования мест в спортивных соревнованиях, так как не умеет правильно ранжировать места с условием, что несколько спортсменов могут занять ОДИНАКОВЫЕ места исходя из времени заплыва/забега/звезда...

Решение нашёл применением функции массива к формулам: =ЕСЛИ, =ЕПУСТО, =СЧËТ и =ЧАСТОТА.
Только в таком виде эта формула подходит для ранжирования мест в спортивных соревнованиях:
Код
=ЕСЛИ(ЕПУСТО(H60);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68)))
(не забываем нажать SHIFT+CTRL+ENTER для того, чтобы применить массив)

Простой рецепт создания формулы:
(не вдаваясь в подробности логики работы формулы)

ГДЕ:
=ЕСЛИ(ЕПУСТО(_номер_первой_ячейки_с_временем_);""
  • следит за тем, что если ВРЕМЯ у спортсмена отсутствует(не заполненная ячейка), то ставим в ячейку с этой формулой (т.е. предназначенную для отображения мест) - ПУСТОТУ, тем самым исключив ячейку из общего подсчёта всех ячеек в массиве. Номер ячейки не "заморожен" знаком доллара, так как должен изменяться.
  • Если в формулу не поставить "ЕСЛИ(ЕПУСТО...",  то вместо "пустоты" формула проставит " 1 " (первые места) всем спортсменам у которых отсутствует время.

=СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68))
  • подсчитываем(ранжируем) диапазон ячеек с H60 по H68 (в моём случае) содержащих ВРЕМЯ спортсменов. Диапазон должен быть "заморожен" знаком доллара.
  • Переменная <= говорит о том, в какую сторону сортировать места - в данном случае самое КОРОТКОЕ время - ПЕРВОЕ место. Если переменную поставить наоборот: => то это будет - самое ДЛИННОЕ время на ПЕРВОМ месте.
  • После переменной идёт номер первой ячейки с временем - он НЕ должен быть заморожен знаком доллара.
  • Далее знак умножения.
  • Далее - диапазон ячеек со временем пишем дважды. Этот диапазон обязательно "замораживаем" знаком доллара.
После этого у нас получается формула:
Код
=ЕСЛИ(ЕПУСТО(H60);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68)))

Далее переводим формулу в массив нажатием на клавиатуре SHIFT+CTRL+ENTER и у формулы автоматом появляются фигурные скобки массива:
Код
={ЕСЛИ(ЕПУСТО(H60);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68)))}

Далее просто копируем формулу протягиванием вниз на нужное количество ячеек - в моём случае это уже с ячейки H61 по H68 и получаем формулу в массиве (с фигурными скобками):
Код
={ЕСЛИ(ЕПУСТО(H60);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H61);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H61)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H62);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H62)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H63);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H63)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H64);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H64)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H65);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H65)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H66);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H66)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H67);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H67)*$H$60:$H$68;$H$60:$H$68)))}
={ЕСЛИ(ЕПУСТО(H68);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H68)*$H$60:$H$68;$H$60:$H$68)))}

Ранжирование мест в спортивных соревнованиях готово!
(аплодисменты) :)  :D  ;)



PS
Если берём Гугл Таблицы (Google Sheets) и делаем таблицу спортивных соревнований онлайн, а не локально в Эксель на компьютере, ТО копируем эту формулу:
Код
=ЕСЛИ(ЕПУСТО(H60);"";СЧЁТ(1/ЧАСТОТА(($H$60:$H$68<=H60)*$H$60:$H$68;$H$60:$H$68)))
в нашу Гугл Таблицу и нажимаем SHIFT+CMD+ENTER (для компьютеров Mac), тем самым вводим нашу формулу в массив.

Однако в отличаемо от Эксель, формула массива в Гугл Таблице примет вот такой вид:
Код
=ArrayFormula((ЕСЛИ(ЕПУСТО(I60);"";СЧЁТ(1/ЧАСТОТА(($I$54:$I$68<=I60)*$I$54:$I$68;$I$54:$I$68)))))
(то есть появятся не фигурные скобки, а формула массива Гугл: =ArrayFormula(_вся_наша_формула_ ) )

Далее, также как и в Эксель, копируем формулу протягиванием вниз на нужное количество ячеек. Всё.  ;)
Теперь у нас правильный подсчёт мест спортсменов в онлайне  :)  в Гугл Таблицах (Google Sheets). :D  
Изменено: Данил И - 29.01.2023 10:46:23
 
 
 
Страницы: 1
Наверх