Мой секрет

Что такое ранжированный ряд в статистике. Построение вариационного ряда. Виды рядов. Ранжирование данных

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

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

где, N — Число групп;

n — Количество элементов в совокупности

N=1+3,32*ln 24= 1+3,32*1,38=5,5816=6

Разделим всю совокупность районов на 6 групп, и найдем величину интервала по формуле:

H= (Xmax — Xmin) /n (1.2)

где, Xmax=65,9-максимальное значение признака в изучаемом ранжированном ряду (район №24);

Xmin=28,1 — минимальное значение (район №1).

Величина интервала составит:

Построим ряд распределения районов, при этой величине интервала, значение Xmin=28,1, тогда верхняя граница первой группы составит:

Распределим организации по установленным группам и подсчитаем их число в каждой группе (табл.1.2).

Интервальный ряд распределения районов.

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

Для наглядности изобразим интервальный ряд в виде гистограммы (рис.1.2).

Другие материалы:

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

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

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

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

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

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

Выделяют три формы вариационного ряда: ранжированный ряд, дискретный ряд и интервальный ряд.

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

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

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

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

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

Групповая таблица здесь также имеет две графы. В первой указывается значение признака в интервале «от — до» (варианты), во второй — число единиц, входящих в интервал (частота).

Частота (частота повторения) — число повторений отдельного варианта значений признака, обозначается fi , а сумма частот, равная объему исследуемой совокупности, обозначается

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

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

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

При построении вариационного ряда с интервальными значениями прежде всего необходимо установить величину интервала i, которая определяется как отношение размаха вариации R к числу групп m:

где R = xmax — xmin ; m = 1 + 3,322 lgn (формула Стерджесса); n — общее число единиц совокупности.

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

Медиана (Ме) — это величина, которая соответствует варианту, находящемуся в середине ранжированного ряда.

Для ранжированного ряда с нечетным числом индивидуальных величин (например, 1, 2, 3, 3, 6, 7, 9, 9, 10) медианой будет величина, которая расположена в центре ряда, т.е. пятая величина.

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

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

где n — число единиц в совокупности.

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

Численное значение медианы

где xМе — нижняя граница медианного интервала; i — величина интервала; S-1 — накопленная частота интервала, которая предшествует медианному; f — частота медианного интервала.

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

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

где xМо — нижняя граница модального интервала; iМо — величина модального интервала; fМо — частота модального интервала; fМо-1 — частота интервала, предшествующего модальному; fМо+1 — частота интервала, следующего за модальным.

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

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

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

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

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

где у — ордината кривой нормального распределения; — стандартизованные отклонения; е и π — математические постоянные; x — варианты вариационного ряда; — их средняя величина; — cреднее квадратическое отклонение.

Если нужно получить теоретические частоты f» при выравнивании вариационного ряда по кривой нормального распределения, то можно воспользоваться формулой

где — сумма всех эмпирических частот вариационного ряда; h — величина интервала в группах; — cреднее квадратическое отклонение; — нормированное отклонение вариантов от средней арифметической; все остальные величины легко вычисляются по специальным таблицам.

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

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

Кривую Пуассона можно выразить отношением

где Px — вероятность наступления отдельных значений х; — средняя арифметическая ряда.

При выравнивании эмпирических данных теоретические частоты можно определить по формуле

где f» — теоретические частоты; N — общее число единиц ряда.

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

Читайте также:  Функции СИМВОЛ ЗНАК ТИП в Excel и примеры работы их формул

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

Для оценки близости эмпирических и теоретических частот применяются критерий согласия Пирсона, критерий согласия Романовского, критерий согласия Колмогорова.

Наиболее распространенным является критерий согласия К. Пирсона , который можно представить как сумму отношений квадратов расхождений между f» и f к теоретическим частотам:

Вычисленное значение критерия необходимо сравнить с табличным (критическим) значением . Табличное значение определяется по специальной таблице, оно зависит от принятой вероятности Р и числа степеней свободы k (при этом k = m — 3, где m — число групп в ряду распределения для нормального распределения). При расчете критерия согласия Пирсона должно соблюдаться следующее условие: достаточно большим должно быть число наблюдений (n 50), при этом если в некоторых интервалах теоретические частоты 5.

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

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

где m — число групп; k = (m — 3) — число степеней свободы при исчислении частот нормального распределения.

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

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

где D — максимальное значение разности между накопленными эмпирическими и теоретическими частотами; — сумма эмпирических частот.

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

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

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

Функции ранжирования

Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.

Способ 1: функция РАНГ.РВ

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

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

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

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

Урок: Мастер функций в Excel

Гистограмма

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

На рис. 6.2. изображена гистограмма распределения населения России в 1997 г. по возрастным группам.

Рис. 6.2. Распределение населения России по возрастным группам

Условие : Приводится распределение 30 работников фирмы по размеру месячной заработной платы

Задача : Изобразить интервальный вариационный ряд графически в виде гистограммы и кумуляты.
Решение :

  1. Неизвестная граница открытого (первого) интервала определяется по величине второго интервала: 7000 — 5000 = 2000 руб. С той же величиной находим нижнюю границу первого интервала: 5000 — 2000 = 3000 руб.
  2. Для построения гистограммы в прямоугольной системе координат по оси абсцисс откладываем отрезки, величины которых соответствуют интервалам варицонного ряда.
    Эти отрезки служат нижним основанием, а соответствующая частота (частость) — высотой образуемых прямоугольников.
  3. Построим гистограмму:

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

Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.

Способ 1: функция РАНГ.РВ

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

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

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

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

    Выделяем на листе ячейку, в которую будет выводиться результат обработки данных. Щелкаем по кнопке «Вставить функцию». Она локализуется слева от строки формул.

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

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

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

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

После того, как все вышеуказанные данные внесены, жмем на кнопку «OK».

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

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

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

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

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

Читайте также:  Примеры формул с логическими функциями ИСТИНА ЛОЖЬ и НЕ в Excel

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

Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;”>”&A7)+1

В столбце С приведена формула =РАНГ(A7;$A$7:$A$11;1) с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;”

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

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

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

Как видно из картинки выше, значения из диапазона В60:В67 и в ячейке D60 совпадают. Такой массив можно получить с помощью формулы =РАНГ(A60:A67;A60:A67) или с помощью формулы =СЧЁТЕСЛИ(A60:A67;">"&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;" .

Функция РАНГ (RANK) в Excel используется для присвоения ранга значению из списка относительно других чисел.

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

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

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

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

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

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

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

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

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2 Пример функции РАНГ.РВ в Excel

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

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

Ранжирование товаров по количеству в прайсе

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

Добавим колонку ранжирования и в ячейку C2 впишем следующую формулу: =РАНГ.РВ($B2;$B$2:$B$10;0). Протянем эту формулу вниз и получим следующий результат распределения мест:

Теперь нам потребуются три дополнительные колонки для создания удобной для восприятия таблицы. В первой колонке у нас будет записаны порядковые номера, во второй – отображены наименования товара, в третьей – их количество. Для того, чтобы таблица работала корректно и обновляла значения при их изменении в колонках А и B, применим к ячейке F2 формулу:

а к ячейке G2 – формулу:

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

Сортировка данных

Начнем с самого простого и доступного варианта — сортировки.

Мы уже частично разбирали как можно структурировать данные с помощью .
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр , а далее указать по какому критерию нужно произвести сортировку.

В данном случае выберем Сортировка по убыванию , где значения будут расположены от большего к меньшему:


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

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

RANKX — функция ранжирования (ранга) в DAX (Power BI и Power Pivot)

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы рассмотрим DAX функцию RANKX, создающую ранги среди значений (ранжирование) в Power BI или Power Pivot.

Разберем параметры этой функции, свойства и примеры формул.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция RANKX в Power BI и Power Pivot

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

  • Таблица — исходная таблица или табличное выражение
  • Выражение — любое выражение, вычисляемое для каждой строки исходной таблицы с целью формирования полного списка возможных значений для ранжирования
  • Значение — (необязательный параметр) любое выражение, ранг которого необходимо найти. По умолчанию, если этот параметр не указан, вместо этого параметра возвращается значение выражения из текущей строки
  • Порядок — (необязательный параметр) вид ранжирования параметра «Значение»:
    1) DESC — упорядоченный порядок рангов по убыванию (по умолчанию);
    2) ASC — по возрастанию
  • Равные Значения — (необязательный параметр) способ определения ранга при наличии равных значений:

Пример формулы на основе DAX функции RANKX

В Power BI Desktop имеется исходная таблица «Продажи Менеджеров»:

Исходная таблица в Power BI

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

Воспользуемся рассматриваемой DAX функцией RANKX и попробуем на основе нее составить код формулы для меры [Ранг]:

В данной формуле, согласно синтаксису функции RANKX, мы использовали только 2 обязательных параметра, остальные (необязательные) мы не стали прописывать.

В качестве первого параметра мы прописали ссылку на исходную таблицу «Продажи Менеджеров».

Во втором параметре указали нужное нам выражение, которое должно рассчитываться для каждой строки исходной таблицы, указанной в первом параметре. А именно, произвели суммирование значений столбца [Продажи] при помощи функции SUM (функция суммирования в языке DAX).

Посмотрим, как отработала, созданная нами мера расчета ранга, в отчетах Power BI:

Неправильный результат работы функции RANKX

Как мы видим из визуализации в Power BI, наша формула на основе функции RANKX отработала неверно и возвратила для каждого менеджера один и тот же ранг, равный 1.

Причины этой ошибки 2:

  1. В этой визуализации при расчете конкретной ячейки ранга, значение столбца [Менеджер] является фильтром исходной таблицы.Например, рассмотрим строку с менеджером Петров — при расчете ранга Петрова, исходная таблица будет автоматически отфильтрована только этим менеджером и сумма продаж возвратится только по одному Петрову.Соответственно, ранг рассчитается только исходя из одного этого менеджера и, естественно, ранг будет равен 1. И так по каждому менеджеру.Решить эту проблему мы сможем при помощи еще одной функции языка DAX — ALLSELECTED (прочитать информацию об этой функции Вы можете в этой статье), обернув в эту функцию исходную таблицу из первого параметра. Этим самым, мы удалим все фильтры, которые наложились на столбец [Менеджер] строками самой визуализации.
  2. Так как выражение во втором параметре вычисляется по каждой строке исходной таблицы, указанной в первом параметре, то необходимо передать контекст этой строки из первого параметра во второй параметр.Но, проблема в том, что функция SUM не принимает контекст строки.Чтобы решить эту вторую проблему, нужно обернуть функцию SUM в другую DAX функцию — CALCULATE, которая сможет передать контекст строки из таблицы в выражение. И тогда функция SUM рассчитает сумму продаж в рамках этого контекста строки, то есть, рассчитает сумму продаж только по одному конкретному менеджеру.

Итак, исправим нашу формулу согласно этим двум пунктам:

Испробуем эту доработанную меру в Power BI:

Результат работы формулы в Power BI на основе DAX функции RANKX

Теперь, наша мера расчета ранга по продажам менеджеров работает как надо. У менеджера Воснецовой сумма продаж составляет 530745, что является самой наивысшей суммой и поэтому у этой суммы продаж ранг 1. А у менеджера Петров самая минимальная сумма продаж, равная 120000 и именно поэтому, у его продаж ранг 4.

Если же мы хотим поменять порядок ранжирования, чтобы у самой минимальной суммы был ранг 1, а у самой максимальной суммы — ранг 4, то в параметрах функции RANKX нужно добавить 4 параметр «Порядок» и поставить у него значение ASC (по возрастанию). Тогда формула будет такой:

Исходя из синтаксиса функции RANKX в этой формуле в параметрах мы указали 3 параметра — первый, второй и четвертый. Третий и пятый мы пропустили. Этим самым и объясняется лишняя точка с запятой перед ASC в новой измененной формуле, то есть, это и есть пропущенный необязательный третий параметр.

Итак, посмотрим на результат в Power BI:

Результат работы функции RANKX по возрастанию

Теперь, как мы видим, ранг 1 соответствует самой минимальной сумме продаж, а ранг 4 — самой максимальной.

На этом, с разбором DAX функции RANKX, создающей ранги (ранжирование) в Power BI или Power Pivot, все.

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функция EARLIER в Power BI

DAX функция EARLIER в Power BI и Power Pivot

DAX функции UNION, INTERSECT и EXCEPT

Функции объединения таблиц в DAX: UNION, INTERSECT и EXCEPT в Power BI и Power Pivot

DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR в Power BI

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

Добавить комментарий

Добрый день.
Необходимо вывести место в рейтинге Филиала.
Использую формулу:
Рейтинг филиалов:=RANKX(ALL(‘Филиал'[наименование]);[отклонение];;ASC;Dense)
(где, [отклонение] — мера)
Результат корректный, по всем Филиалам в группе.
Когда выбираю один из Филиалов результат по части из них меняется.
Например, 12 место становится 13 и т.п.
Заранее спасибо.

К сожалению, мне не совсем ясна исходная информация, но, возможно, проблема из-за функции ALL, попробуйте ее заменить на ALLSELECTED

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

ROW_NUMBER

ROW_NUMBER – функция нумерации в Transact-SQL, которая возвращает просто номер строки.

ROW_NUMBER () OVER ([PARTITION BY столбы группировки] ORDER BY столбец сортировки)

где, partition by — это не обязательное ключевое слово, после которого указывается столбец или столбцы, по которым группировать данные, а order by столбец для сортировки, т.е. по данному столбцу будут отсортированы данные, а потом пронумерованы, он уже обязателен. Сразу скажу, чтобы не возвращаться, что эти ключевые слова относятся ко всем функциям ранжирования, которые мы будем сегодня использовать.

Пример без группировки с сортировкой по цене

Скриншот 2

Пример с группировкой по категории и с сортировкой по цене

Скриншот 3

Как видите, здесь уже нумерация идет в каждой категории.

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

Пример без группировки с сортировкой по цене и отличие от row_number()

Скриншот 4

Пример с группировкой по категории и с сортировкой по цене и отличие от row_number()

Скриншот 5

Функции ранжирования

Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.

Способ 1: функция РАНГ.РВ

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

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

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

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

Урок: Мастер функций в Excel

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

Если список чисел находится в диапазоне 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;» =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11 .

Способ 2: функция РАНГ.СР

Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.

Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:

Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.

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

После открытия окна Мастера функций выделяем в перечне категории «Статистические» наименование РАНГ.СР и жмем на кнопку «OK».

  • Число (адрес ячейки содержащей элемент, уровень которого следует определить);
  • Ссылка (координаты диапазона, ранжирование внутри которого выполняется);
  • Порядок (необязательный аргумент).

Внесение данных в поля происходит точно таким же способом, как и у предыдущего оператора. После того, когда все настройки выполнены, жмем на кнопку «OK».

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

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

Ссылка на основную публикацию