☰

Формула для назначения суффикса рангу: ЕСЛИ 1, то «st», ЕСЛИ 2, то «nd» и т. д.

tag-icon • tag-icon microsoft-excel worksheet-function
Формула для назначения суффикса рангу: ЕСЛИ 1, то «st», ЕСЛИ 2, то «nd» и т. д.

Я сравниваю позицию записи с ее позицией в прошлом году: This year it is ranked 4th out of 50, in 2015 it was ranked 21stВозможно ли автоматически добавлять «st», «nd», «rd» и «th» к рейтингу?

Мои данные представлены в таблице:

  • COL A = Имя точки данных (Боб, Джейн, Билл, Энн и т. д.). Это фиксированный столбец.
  • COL B = данные за последний год,
  • COL C = рейтинг последнего года,
  • COL D = данные за предыдущий год,
  • COL E = рейтинг предыдущего года.

Я довольно уверенно использую формулы ЕСЛИ, но не уверен, как обратиться к последней цифре в ячейке и сказать: ЕСЛИ «это 1, то 'st'», ЕСЛИ «это 2, то 'nd'», ЕСЛИ «это 3, то 'rd'», иначе 'th'.

Обратите внимание, что ранг может быть многозначным числом. Буквы основаны на последней цифре. Так, ранг 31 — это 31-й. Также есть исключения для подростков. Ранги 11, 12 и 13 используют «th».

Можно ли это сделать с помощью формулы или это нужно делать средствами VBA?

решение1

Далее предполагается, что «ранг» находится в ячейке B50.

=IF(B50=1,"st",IF(B50=2,"nd",IF(B50=3,"rd","th")))

Вышеуказанный запрос даст вам такие результаты, как «st», «nd» и т. д.

Хотя я подозреваю, что вы также захотите показать номер, поэтому используйте

=IF(B50=1,B50 & "st",IF(B50=2,B50 & "nd",IF(B50=3,B50 & "rd",B50 & "th")))

Это покажет результаты типа «1-й», «2-й» и т. д.

Спасибо комментарию ejbytes, я не тестировал > 10! Следующий фрагмент делает это, так что вы можете получить 22-й или 43-й и т.д.

=IF(RIGHT(B50,1) = "1", B50&"st",IF(RIGHT(B50,1)="2",B50&"nd",IF(RIGHT(B50,1)="3",B50&"rd",B50&"th")))

Теперь мы переходим к проблемам 11, 12 и 13, а также 111, 112, 113 и т. д., так что нам нужно с ними разобраться!

=IF(RIGHT(B50,2)="11",B50 & "th",IF(RIGHT(B50,2)="12",B50&"th",IF(RIGHT(B50,2)="13",B50&"th",IF(RIGHT(B50,1)="1",B50&"st",IF(RIGHT(B50,1)="2",B50&"nd",IF(RIGHT(B50,1)="3",B50&"rd",B50&"th"))))))

Наконец, у нас осталось 0... 10-й, 50-й, 100-й, но у нас также есть 0!

=IF(B50=0,0,IF(RIGHT(B50,2)="11",B50&"th",IF(RIGHT(B50,2)="12",B50&"th",IF(RIGHT(B50,2)="13",B50&"th",IF(RIGHT(B50,1)="1",B50&"st",IF(RIGHT(B50,1)="2",B50&"nd",IF(RIGHT(B50,1)="3",B50&"rd",B50&"th")))))))

решение2

Работая с формулой @Dave IF, мне пришлось внести некоторые поправки в свой лист данных.

Распознавание самой левой цифры означало, что мне нужно добавить еще один «вспомогательный» столбец.

  • Столбец A — исходный номер ранга
  • Столбец B — это новый вспомогательный столбец =ЗНАЧЕНИЕ(ПРАВСИМВ(A1,1)) Это дает мне последнюю цифру.

  • В столбце C содержится формула

    =IF(A1=12,A1&"th",IF(A1=13,A1&"th",IF(B1=1,A1&"st",IF(B1=2,A1&"nd",IF(B1=3,A1&"rd",A1&"th")))))

Мне пришлось разбираться с 12 и 13 конкретно…из-за английского!

Это работает, но может повредить глаза. Спасибо.

Связанный контент