Как найти ранг формулы

В этой статье описаны синтаксис формулы и использование функции РАНГ в Microsoft Excel.

Описание

Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)

Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.

Дополнительные сведения о новых функциях см. в разделах Функция РАНГ.СР и Функция РАНГ.РВ.

Синтаксис

РАНГ(число;ссылка;[порядок])

Аргументы функции РАНГ описаны ниже.

• Число     Обязательный. Число, для которого определяется ранг.

• Ссылка     Обязательный. Массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.

• Порядок     Необязательный. Число, определяющее способ упорядочения.

  Если значение аргумента “порядок” равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.

  Если значение аргумента “порядок” — любое число, кроме нуля, то ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания

• Функция РАНГ присваивает повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

• В некоторых случаях может потребоваться определить ранг, учитывая при этом связь. В предыдущем примере можно было бы получить измененный ранг числа 5,5 для числа 10. Для этого добавьте следующий коэффициент исправления к значению, возвращаемом функцией РАНГ. Этот коэффициент исправления подходит как для случая, когда ранг вычисляется в порядке убывания (порядок = 0 или опущен) или в порядке возрастания (порядок = нелиценз).

  Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.

  В следующем примере РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные
7
3,5
3,5
1
2
Формула	Описание (результат)	Результат
=РАНГ(A3;A2:A6;1)	Ранг числа 3,5 в приведенном выше списке (3)	3
=РАНГ(A2;A2:A6;1)	Ранг числа 7 в приведенном выше списке (5)	5

Функция

РАНГ(

)

, английский вариант RANK(),

возвращает

ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 будет иметь ранг 1, т.к. это наименьшее число, число 10 – ранг 2, а 20 – ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).

Синтаксис

РАНГ

(

число

;

ссылка

;порядок)

Число

— число, для которого определяется ранг.

Ссылка

— ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;{10:50:30:40:50}) работать не будет. Но, если ввести формулу

=РАНГ(B7;$A$7:$A$11)

, то она будет работать (хотя ячейка

B7

– вне списка с числами). Если в

B7

содержится число вне списка с числами, то формула вернет ошибку #Н/Д.

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

Порядок

— число, определяющее способ упорядочения.

• Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б
  
  о
  
  льшие ранги.
• Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б
  
  о
  
  льшим значениям присваиваются б
  
  о
  
  льшие ранги.

Примечание

: Начиная с MS EXCEL 2010 для вычисления ранга также используются функции

РАНГ.СР()

и

РАНГ.РВ()

. Последняя функция аналогична

РАНГ()

.

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне

A7:A11

, то формула

=РАНГ(A7;$A$7:$A$11)

определит ранг числа из ячейки

А7

(см.

файл примера

).

Т.к. аргумент

порядок

опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) – минимальному (10).

Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;”>”&A7)+1

В столбце

С

приведена формула

=РАНГ(A7;$A$7:$A$11;1)

с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;”<“&A7)+1

Если исходный список

отсортировать

, то ранг числа будет его позицией в списке.

Ранг по условию

Если список состоит из значений, относящихся к разным группам (например, к разным маркам машин), то ранг можно вычислить не только относительно всей совокупности данных, но и относительно данных каждой отдельной группы.

В

файле примера

ранг по условию (условием является принадлежность значения к групп) вычислен с помощью формулы:

=СЧЁТЕСЛИМН($A$3:$A$22;A3;$B$3:$B$22;”>”&B3)+1

В столбце А содержатся названия группы, в столбце В – значения.

Связь функций

НАИБОЛЬШИЙ()

/

НАИМЕНЬШИЙ()

и

РАНГ()

Функции

НАИБОЛЬШИЙ()

и

РАНГ()

являются взаимодополняющими в том смысле, что записав формулу

=НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11))

мы получим тот же исходный массив

A7:A11

.

Определяем ранг в списке с повторами

Если список содержит

повторы

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

РАНГ()

или

РАНГ.РВ()

) или среднее значение, если

РАНГ.СР()

). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).

В этом нам поможет формула

=РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1

Предполагается, что исходный список с числами находится в диапазоне

А37:А44

.

Примечание

. В

MS EXCEL 2010

добавилась функция

РАНГ.РВ(число;ссылка;[порядок])

Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В

файле примера

дается пояснение работы этой функции. Также добавилась функция

РАНГ.СР(число;ссылка;[порядок])

Если несколько значений имеют одинаковый ранг, возвращается среднее.

Массив рангов

Для построения некоторых сложных

формул массива

может потребоваться

массив

рангов, т.е. тот же набор рангов, но в одной ячейке.

Как видно из картинки выше, значения из диапазона

В60:В67

и в ячейке

D60

совпадают. Такой массив можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67)

или с помощью формулы

=СЧЁТЕСЛИ(A60:A67;”>”&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67;1)

или

=СЧЁТЕСЛИ(A60:A67;”<“&A60:A67)+1

.

Такой подход использется в статьях

Отбор уникальных значений с сортировкой в MS EXCEL

и

Динамическая сортировка таблицы в MS EXCEL

.

Синтаксис

= RANK (number, reference, [order]) — английская версия

= RANK (number; link; [order]) — русская версия

Что возвращает функция

Возвращает число, указывающее ранг числа относительно других значений в списке.

Синтаксис функции:

РАНГ (число; ссылка; порядок)

Описание аргумента:

• — число: указание ячейки, положение которой необходимо вычислить;
• — соединение: указание диапазона ячеек, с которым будет производиться сравнение;
• — порядок: значение, указывающее тип заказа: 0 — по убыванию, 1 — по возрастанию.

Функция RANK.RV () не отличается по работе от обычной функции RANK (). Как упоминалось выше, если программа находит несколько элементов с одинаковыми значениями, она присваивает им наивысший ранг, например, если результаты совпадают, всем им будет предоставлено место.

Функция RANK.CP () указывает, что если результаты совпадают, будет присвоено значение, соответствующее среднему значению рейтинговых чисел.

Как ранжировать список по возрастанию в Excel

Пример 1. Есть документ, в котором записаны имена учеников и сумма оценок за четверть. Необходимо определить рейтинг учеников по успеваемости.

Используем для ячейки C2 формулу = RANK (B2; $ B $ 2: $ B $ 7,0), для ячейки D2 — формулу = RANK.РВ (B2; $ B $ 2: $ B $ 7; 0) а для ячейки E2 — формула = RANK.CP (B2, $ B $ 2: $ B $ 7,0). Растягиваем все формулы на ячейки ниже.

Таким образом, можно видеть, что рейтинг для функций RANK () и RANK.RV () не отличается: есть два студента, которые заняли второе место, нет третьего места, и есть также два студента, которые заняли второе место место, четвертое место, даже пятого места не бывает… Рейтинг составлен по максимально возможному варианту.

В то же время функция RANK.CP () присваивала подобранным студентам среднее количество мест, которые они могли бы занять, если бы сумма баллов, например, имела разницу в один балл. Для второго и третьего места средний показатель — 2,5; для четвертого и пятого — 4,5.

Функция РАНГ.РВ

Оператор RANK.RV обрабатывает данные и возвращает порядковый номер аргумента, заданного агрегированным списком указанной ячейке. Если несколько значений имеют один и тот же уровень, оператор выводит самое высокое из списка значений. Например, если два значения имеют одинаковую величину, обоим будет присвоено второе число, а следующему большему значению будет четвертое. Кстати, оператор RANK делает то же самое в более ранних версиях Excel, поэтому эти функции можно считать идентичными.

Синтаксис этого оператора записывается следующим образом:

= RANGO.RV (номер; ссылка; [заказ])

Ссылочный номер и аргументы являются обязательными, а порядок указывать необязательно. В качестве аргумента «число» необходимо ввести ссылку на ячейку, содержащую значение, чей порядковый номер вы хотите узнать. Аргумент ссылки содержит адрес всего классифицируемого диапазона. Аргумент порядка может иметь два значения: «0» и «1». В первом случае порядок считается по убыванию, а во втором — по возрастанию. Если этот аргумент не указан, программа автоматически считает его равным нулю.

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

1. Выделите на листе ячейку, в которой будет отображаться результат обработки данных. Нажмите кнопку «Вставить функцию». Он расположен слева от строки формул.
2. Эти действия запускают окно мастера. В нем представлены все (за редким исключением) операторы, которые можно использовать для составления формул в Excel. В категории «Статистика» или «Полный алфавитный список» находим имя «RANK.RV», выбираем его и нажимаем на кнопку «ОК».
3. После выполнения вышеуказанных шагов будет активировано окно аргументов функции. В поле «Число» введите адрес ячейки, данные, которые вы хотите классифицировать. Это можно сделать вручную, но удобнее сделать так, как будет описано ниже. Установите курсор в поле «Число», затем просто выберите нужную ячейку на листе.

   После этого ваш адрес будет введен в поле. Таким же образом мы вставляем данные в поле «Ссылка», только в этом случае мы выбираем весь диапазон, в котором происходит ранжирование.

   Если вы хотите, чтобы рейтинг шел от низшего к высшему, то в поле «Порядок» установите цифру «1». Если вы хотите, чтобы порядок распределялся от наибольшего к наименьшему (а в подавляющем большинстве случаев это именно то, что требуется), это поле остается пустым.

   После ввода всех вышеперечисленных данных нажмите кнопку «ОК».

4. После выполнения этих действий в заданной ячейке отобразится серийный номер, который имеет выбранное значение из всего списка данных.

   

   Если вы хотите ранжировать всю указанную область, вам не нужно вводить отдельную формулу для каждой меры. Прежде всего, сделаем адрес абсолютным в поле «Ссылка». Добавьте знак доллара ($) к каждому значению координат. В этом случае не следует изменять значения в поле «Число» на абсолютные значения, иначе формула будет рассчитана некорректно.

   

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

   

   Как видите, таким образом формула будет скопирована, и ранжирование будет выполнено по всему диапазону данных.

Функция РАНГ.СР

Вторая функция, выполняющая операцию ранжирования в Excel, — это RANK.SR. В отличие от функций RANK и RANK.RV, если значения нескольких элементов совпадают, этот оператор возвращает средний уровень. То есть, если два значения равны по величине и следуют за пронумерованным значением 1, им обоим будет присвоен номер 2,5.

Синтаксис RANK.CP очень похож на предыдущий оператор. Похоже на то:

= RANK.SR (число; ссылка; [порядок])

Формулу можно ввести вручную или с помощью мастера. На последнем варианте остановимся подробнее.

1. Выбираем ячейку на листе для просмотра результата. Аналогично, как и в предыдущий раз, перейдите в мастер через кнопку «Вставить функцию».
2. После открытия окна мастера функций выберите имя RANK.SR в списке категорий «Статистика» и нажмите кнопку «ОК».
3. Окно темы активировано. Аргументы для этого оператора точно такие же, как для функции RANK.RV:< >Число (адрес ячейки, в которой находится элемент, уровень которого необходимо определить);Ссылка (координаты диапазона, рейтинг в пределах которого выполняется);Заказ (необязательно).Как видите, после выполненных действий результат расчета отображался в ячейке, отмеченной в первом абзаце данной инструкции. Сама сумма представляет собой место, которое занимает конкретное значение среди других значений в диапазоне. В отличие от результата RANK.RV, итог оператора RANK.SR может иметь дробное значение.
4. Как и в случае с предыдущей формулой, изменив ссылки с относительных на абсолютные и указав индикатор выбора, вы можете ранжировать весь диапазон данных с помощью автозаполнения. Алгоритм действий точно такой же.

Как видите, в Excel есть две функции для определения ранжирования конкретного значения в диапазоне данных: RANK.RV и RANK.SR. Для более ранних версий программы используется оператор RANK, который, по сути, является полным аналогом функции RANK.RV. Основное различие между формулами RANK.RV и RANK.SR состоит в том, что первая указывает наивысший уровень, когда значения совпадают, а вторая отображает среднее значение в виде десятичной дроби. Это единственное различие между этими операторами, но это необходимо учитывать при выборе функции, которая лучше всего подходит для использования пользователем.

Функция РАНГ() в MS EXCEL

RANK (), английская версия RANK (), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10; 20; 5} число 5 будет иметь ранг 1, то есть

поскольку это наименьшее число, 10 — это ранг 2, а 20 — ранг 3 (это возрастающий ранг, поскольку меньшему значению присваивается ранг 1). Если список отсортирован, рангом числа будет его позиция (если нет повторов).

• Синтаксис
• РАНГ (число>; ссылка>>
• Число — это номер, по которому определяется ранг.

Ссылка: ссылка на список чисел (диапазон ячеек с числами). Невозможно указать массив напрямую, формула = RANK (10; {10: 50: 30: 40: 50}) не будет работать. Но если вы введете формулу = РАНГ (B7; $ A $ 7: $ A $ 11), она будет работать (хотя ячейка B7 отсутствует в списке с числами). Если B7 содержит число вне нумерованного списка, формула вернет ошибку # N / A.

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

Заказ — это номер, который определяет, как вы заказываете.

• Если порядок равен 0 (ноль) или опущен, MS EXCEL присваивает ранг = 1 максимальному числу, меньшим значениям назначаются более высокие ранги.
• Если порядок — ненулевое число, MS EXCEL присваивает ранг = 1 минимальному числу, большим значениям присваиваются более высокие ранги.

Примечание. Начиная с MS EXCEL 2010 функции RANK.SR () и RANK.RV () также используются для вычисления ранга. Последняя функция аналогична RANK().

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне A7: A11, формула = RANK (A7; $ A $ 7: $ A $ 11) определит ранг числа из ячейки A7 (см. Файл примера).

Поскольку аргумент порядка опущен, MS EXCEL присвоил ранг = 1 максимальному числу (50) и максимальному рангу (5 = количество значений в списке) минимальному (10).

1. Альтернатива: = СЧЁТЕСЛИ ($ A $ 7: $ A $ 11; «>» & A7) +1
2. Столбец C содержит формулу = RANK (A7; $ A $ 7: $ A $ 11; 1) с возрастающим рангом, ранг = 1 присваивается минимальному числу. Альтернатива: = СЧЁТЕСЛИ ($ A $ 7: $ A $ 11; «» & B3) +1
3. Столбец A содержит имена групп, столбец B — значения.

Связь функций НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ() и РАНГ()

Функции LARGE () и RANK () дополняют друг друга в том смысле, что, написав формулу = LARGE ($ A $ 7: $ A $ 11; RANK (A7; $ A $ 7: $ A $ 11)), вы получите тот же массив исходный A7: A11.

Определяем ранг в списке с повторами

Если список содержит повторы, повторяющимся значениям (выделенным цветом) будет присвоен один и тот же ранг (максимальный, если используется функция RANK () или RANK.RV ()) или среднее значение, если RANK.AV()).

Наличие повторяющихся номеров влияет на ранги последующих номеров.

Например, если в списке целых чисел, отсортированных в порядке возрастания, есть число 10, которое получает 5-й ранг дважды, число 11 будет ранжировано 7 (ни одно из чисел не будет ранжировано 6).

Иногда это неудобно, и ранги не нужно повторять (например, при определении премиальных мест, когда несколько человек не могут занять места).

Формула = РАНГ (A37; A $ 37: A $ 44) + СЧЁТЕСЛИ (A $ 37: A37, A37) -1

Предполагается, что исходный список с номерами находится в диапазоне A37: A44.

Примечание. В MS EXCEL 2010 добавлена ​​функция РАНГ.

PB (число; ссылка; [порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает одинаковые значения ранга повторяющимся числам).

Пример файла объясняет, как работает эта функция. Мы также добавили функцию RANK.AV (число; ссылка; [порядок]). Если несколько значений имеют одинаковый ранг, возвращается среднее значение.

Массив рангов

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

1. Как видно из изображения выше, значения в диапазоне B60: B67 и в ячейке D60 совпадают. Эту матрицу можно получить по формуле = РАНГ (A60: A67; A60: A67) или по формуле = СЧЁТЕСЛИ (A60: A67; «>» & A60: A67) +1
2. Повышение рангов можно получить с помощью формулы = РАНГ (A60: A67; A60: A67; 1) или = СЧЁТЕСЛИ (A60: A67;»

Как сортировать данные в таблицах Excel (правильный способ)

Итак, нам вручили электронную таблицу Excel с тысячами строк, и вы понимаете, что все данные расположены в неправильном порядке. Возможно, вам потребуется отсортировать его по именам столбцов или отсортировать данные от самого высокого до самого низкого.

На первый взгляд, сортировать данные в Excel просто, и приложение определенно упрощает сортировку. Однако более важным является то, как вы можете сортировать и переупорядочивать данные в ваших таблицах. Вот три метода сортировки данных Excel, которые вы узнаете:

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

Как сортировать данные в электронной таблице Excel (короткое видео)

Этот скринкаст охватывает несколько методов сортировки данных. Посмотрите это 3-минутное видео, чтобы быстро изучить эти профессиональные методы работы с Excel. Мы начнем с простой сортировки и перейдем к более продвинутым методам, чтобы вы всегда могли отсортировать свои данные так, как хотите.

Примеры данных (бесплатная загрузка рабочей книги Excel)

В рамках этого руководства я создал книгу, с которой вы можете работать, когда узнаете, как сортировать данные. Загрузите книгу бесплатно и используйте ее при изучении сортировки в Excel.

Простая сортировка в Excel

Сортировка может быть очень простой, требуется всего пара щелчков мышью, чтобы реорганизовать данные в таблицах. Давайте узнаем, как это сделать.

В книге Excel начните с щелчка по ячейке столбца, которую вы хотите отсортировать. Теперь убедитесь, что вы находитесь на вкладке «Главная» на ленте Excel и найдете кнопку «Сортировка и фильтр» в правой части этой панели.

Кнопка «Сортировка и фильтр» расположена справа от вкладки «Главная.

Обратите внимание, что в параметрах сортировки вы можете отсортировать текст «от А до Я» или от «Я до А». Эти простые параметры помогут вам отсортировать данные в Excel в алфавитном порядке или в обратном порядке, в зависимости от вашего выбора.

При сортировке данных в Excel сортируется вся строка. По сути, выбранный вами столбец будет «ключом», который Excel использует для определения способа сортировки данных, но каждая строка представляет собой запись, которая должна оставаться сгруппированной.

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

В приведенном выше примере параметры сортировки изменились, потому что я выбрал числовой столбец.

На самом деле простая сортировка настолько проста. Просто щелкните данные, выберите вариант сортировки, и Excel восстановит данные в таблице.

Я отсортировал данные в этой таблице по клиентам всего за несколько щелчков мышью.

Интересный совет: попробуйте также выполнить сортировку, щелкнув правой кнопкой мыши внутри столбца и выбрав «Сортировка», а затем укажите, как сортировать исходные данные.

Как НЕ нужно сортировать данные в Excel

не менее важно знать самый опасный способ сортировки данных в Excel, он может испортить исходные данные.

Проблема возникает, если в таблице много данных и вы случайно отсортировали только один столбец данных. Каждая строка данных в Microsoft Excel действительно выглядит как запись, которая должна быть одинаковой во всей строке.

Я дал цвет линиям в этом примере, чтобы мы могли убедиться, что наши данные отсортированы правильно. Если данные отсортированы правильно, цвета должны плавно пересекать каждую строку.

Большая ошибка для пользователей Excel — выбрать только один столбец при сортировке и выбрать неправильный вариант в следующем окне.

Excel даже пытается предупредить нас, показывая обнаруженные данные за пределами указанного диапазона. Во всплывающем окне вы можете выбрать автоматическое расширение выбранного диапазона (выберите это!) И сортировку в пределах указанного выбора.

Мне всегда казалось, что варианты, предлагаемые этим окном, не совсем понятны. Просто знайте, что вы захотите использовать автоматическое расширение выделения, чтобы Excel касался всех столбцов при сортировке данных.

В целях тестирования давайте посмотрим, что произойдет, если мы выберем столбец и выберем порядок сортировки для сортировки в пределах указанного выбора.

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

На скриншоте ниже вы можете увидеть, насколько проблематична такая сортировка. Поскольку столбец «Сумма счета» был отсортирован от наименьшего к наибольшему, все остальные столбцы остались на своих местах. Это означает, что наши данные больше не верны.

Как видно из несоответствия цветов, были отсортированы только данные в столбце Billed Amount, поэтому таблица теперь неверна.

Следовательно, при сортировке данных есть два ключа «НЕ»:

1. Не начинайте с выделения столбца на листе.
2. Не используйте опцию сортировки в коридорах указанного диапазона, если вы работаете с более чем одним столбцом, убедитесь, что вы расширили выбранный диапазон.

Расширенная сортировка данных Excel

До сих пор простая сортировка позволяла нам сортировать данные одинаково. Что, если нам нужны два типа данных для сортировки?

А если бы мы хотели…

• Сортировка в алфавитном порядке по штатам, а затем по регионам.
• Отсортировано в алфавитном порядке по имени клиента, а затем по каждому типу проекта, который мы для него сделали.
• Отсортируйте клиентов в алфавитном порядке, а затем по количеству для каждого отдельного проекта, от наибольшего к наименьшему.

Ответ на все это — расширенная сортировка, где вы можете установить несколько уровней сортировки данных. Давайте посмотрим на последний пример, используя образцы данных.

Чтобы начать, щелкните в любом месте своих данных и найдите параметр «Сортировка и фильтр», затем выберите «Пользовательская сортировка.

Перейдите к расширенным параметрам сортировки, выбрав «Сортировка и фильтр»> «Пользовательская сортировка.

В этом окне мы можем добавлять разные уровни сортировки. Для начала щелкните раскрывающееся меню рядом с полем «Сортировка» и выберите столбец, который нужно отсортировать.

В моем случае я выберу «Клиент» в раскрывающемся меню и оставлю «Сортировка по значениям» и «Сортировка от А до Я». На простом языке это будет отсортировать электронную таблицу Excel в алфавитном порядке.

Теперь нажмите «Добавить слой». Это создаст новую строку в сопоставлении и позволит нам добавить второй уровень организации.

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

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

После нажатия кнопки «ОК» Excel отсортирует таблицу в соответствии с правилами, которые мы создали в этом окне.

Совет: для более продвинутой сортировки попробуйте использовать раскрывающееся меню «Сортировка», чтобы изменить тип сортировки на расширенные функции, такие как сортировка по цвету ячеек.

Расширенная сортировка позволяет создать в таблице два уровня организации данных. Если сортировки по одному фактору недостаточно, используйте расширенную сортировку, чтобы добавить больше параметров.

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел {30;2;26} число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).

Синтаксис функции:

1. Число. Для, которого необходимо определить номер в ранжировании.
2. Ссылка. На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
3. Порядок. Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).

Определим ранжирование чисел в списке без повторов:

Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.

В третьем столбце приведена формула с рангом по возрастанию.

Определим номера значений в списке, где присутствуют повторяющиеся значения.

Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.

Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2<30;РАНГ(A2;$A$2:$A$13);””)).

Серым цветом выделены значения, которые соответствуют заданному условию. Для чисел, которые больше 30, выводится пустая строка.



Пример функции РАНГ.РВ в Excel

В версиях Excel, начиная с 2010 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).

Как видно из примера, данная функция обрабатывает повторяющиеся числа в списке точно так же, как и в обычной формуле. Если необходимо избежать повторений рангов, используем другую формулу (см. выше).

Пример функции РАНГ.СР в Excel

Возвращает номера числового значения в списке (порядковый номер относительно других значений). То есть выполняет ту же задачу. Только при обнаружении идентичных значений возвращает средний показатель.

Вот результат работы функции:

Формула в столбце «по убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так, функция значению 87 присвоила средний номер 1,5.

Допустим, в списке чисел три повторяющихся значения (выделены оранжевым цветом).

Функция присвоила каждому из них ранг 5, что является средним для 4, 5 и 6.

Сравним работу двух функций:

Напомним, что эти две функции работают только в Excel 2010 и выше. В более ранних версиях для этих целей можно задействовать формулу массива.

Скачать примеры функции ранжирования РАНГ в Excel.

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

• 

=COUNTIFS( $A$2:$A$11 , A2 , $B$2:$B$1 1 ,">"& B2 )+1

=COUNTIFS( $A$2:$A$11 , A2 , $B$2:$B$1 1 ,"<"& B2 )+1

=COUNTIFS( $A$2:$A$11 , A2 , $B$2:$B$1 1 ,">"& B2 )+1

=COUNTIFS( $A$2:$A$11 , A2 , $B$2:$B$1 1 ,"<"& B2 )+1