Сортировка данных по убыванию и возрастанию

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

Глаза разбегаются, правда? Для того, чтобы было легче читать эту таблицу, определимся с тем, какая информация для нас существенна, а какая нет.

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

Сразу отметим 2 важных момента.

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

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

Как убрать формулы из всех ячеек листа

Для того, чтобы одновременно убрать все формулы нужно:

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

2. Скопировать выделенную область (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Копировать»)

3. Воспользоваться специальной вставкой (не снимая выделения области, правой кнопкой мыши вызываем меню и выбираем «Специальная вставка» -> «значения»)

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

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

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

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

ВНИМАНИЕ: Это касается только объединенных ячеек в шапке. Если внутри таблицы есть объединенные ячейки, Excel не будет сортировать такие строки. Придется снимать объединение.

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

Для этого выделяем всю область таблицы вместе с шапкой, находим в главном меню «Данные» и выбираем «Сортировку»:

В выпавшем окне Excel предложит сделать сортировку по всем показателям, перечисленным в шапке. Мы выберем «Чистую прибыль»:

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

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

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

Вы не задумывались, почему в финансовых отчетах данные представлены в том порядке, в каком они там есть? Элементы затрат, например, всегда начинаются с материалов, затем зарплата, аренда, амортизация и т.д. Статьи затрат также идут в своем порядке. Также детализация по городам: частый вариант по удалению от Москвы или по численности населения.

Рейтинги в PowerPivot и Power BI с помощью RANKX |

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

Рейтинги в PowerPivot и Power BI с помощью RANKX

Вы не задумывались, почему в финансовых отчетах данные представлены в том порядке, в каком они там есть? Элементы затрат, например, всегда начинаются с материалов, затем зарплата, аренда, амортизация и т.д. Статьи затрат также идут в своем порядке. Также детализация по городам: частый вариант по удалению от Москвы или по численности населения.

Почему порядок именно такой? Удобно ли это для принятия решений? На самом деле – нет. В большинстве случаев данные эффективней располагать по убыванию оцениваемого показателя.

Другая ситуация: вас могут попросить вывести не всех менеджеров, а только десятку лучших. И опять, вам нужно ранжировать данные, а затем отсортировать по ТОП-10. В PowerPivot и Power BI предусмотрено несколько способов сделать это. Один из этих способов — специальная DAX-формула RANKX .

Итак, задача: отсортировать и присвоить рейтинги данным. Например, присвоить рейтинги менеджерам в зависимости от продаж – чем больше продажи, тем выше место менеджера. Воспользуемся для этого формулой RANKX (ее аналог в «обычном» Excel — формула РАНГ ).

Расчет рейтингов с RANKX

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

Продажи:= SUM ( ‘продажи'[Продажи с НДС] )
Рейтинг1:= RANKX ( ALL ( ‘менеджеры’ ); [Продажи] )

Рейтинги рассчитаны, в таблице смущает только общий итог по столбцу = 1. Чтобы итог не выводился, немного изменим формулу:

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

Применение ALL и ALLSELECTED

При ранжировании использовалась формула ALL , чтобы обратиться к справочнику менеджеров — ALL (‘менеджеры’). Из-за этого рейтинг менеджеров составлен по компании в целом. Он не зависит о того, какие выбраны фильтры в отчете. Чтобы составить рейтинг с учетом фильтров, обратимся к справочнику с помощью формулы ALLSELECTED :

То есть, если ранжирование не должно зависеть от выбранных фильтров, используйте формулу ALL , если зависит – ALLSELECTED .

Посмотрим, как еще можно рассчитать рейтинги с помощью RANKX .

Синтаксис формулы RANKX

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

Так удобно ранжировать, например, дебиторскую задолженность – чем она меньше, тем лучше. Если указать DESC , то ранжирование будет по убыванию.

Если в формуле указано Dense , а в выражениях есть одинаковые значения, то ранги будут последовательными. Так, на рисунке ниже ранги идут по порядку — от 1 до 5.

Если в формуле указано Skip , то ранги не будут последовательными. На рисунке после ранга «2» следующий ранг – это сразу «5». Для Skip общее число мест будет таким же, как количество менеджеров.

В Excel 2010 и более ранних версиях диаграмму с цилиндрами, конусами или пирамидами можно было построить, выбрав соответствующий тип графика на вкладке Вставка (Insert) в разделе Диаграммы (Charts).

Сортировка данных по убыванию и возрастанию — learn-office

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