Рейтинг в сводной таблице Excel
Обновлено: 06.07.2024
ШАГ 2. В разделе СТРОКИ укажите поле Месяц продаж, в столбцах укажите поле Финансовый год, а в области ЗНАЧЕНИЯ нужно указать в поле Продажи дважды, я объясню ниже почему:
ШАГ 3. Нажмите раскрывающееся меню (Сумма ПРОДАЖ2) второго поля "Продажи" и выберите "Настройки поля значений"
.
ШАГ 4. Выберите вкладку "Показать значения как" и в раскрывающемся списке выберите Ранжировать от наибольшего к наименьшему.
Выберите Месяц продаж в качестве базового поля. Это означает, что мы ранжируем значения продаж по месяцам продаж (где ранг 1 – самый большой).
Также измените пользовательское имя с "Самый большой рейтинг" на "Самый маленький", чтобы сделать его более презентабельным. Нажмите "ОК".
Теперь у вас есть сводная таблица, показывающая ранжирование от наибольшего к наименьшему для данных о продажах за 2012, 2013 и 2014 годы.
Вы можете видеть, что каждое красное поле — это рейтинг за каждый год (за 2012, 2013, 2014 и общий рейтинг).
Вот как вы можете легко ранжировать сводную таблицу за несколько простых шагов!
Использование вычисляемого поля
ШАГ 1. Выберите любую ячейку в данных, а затем выберите «Вставка» > «Сводная таблица».
ШАГ 2. В диалоговом окне выберите «Новый рабочий лист» и нажмите «ОК».
ШАГ 3. Перетащите «Месяц продаж» в поле «Строка» и «Продажи» в поле «Значения».
ШАГ 4. Нажмите кнопку фильтра и выберите «Дополнительные параметры сортировки».
ШАГ 5. В диалоговом окне «Сортировка» выберите «Сумма продаж» в раскрывающемся списке «По возрастанию». Нажмите "ОК".
Это отсортирует данные в порядке возрастания!
ШАГ 6. Щелкните любую ячейку в сводной таблице и перейдите в раздел Анализ сводной таблицы > Вычисляемое поле.
ШАГ 7. В диалоговом окне «Вставить вычисляемое поле» введите =1 в поле формулы. Нажмите "ОК".
ШАГ 8. Щелкните правой кнопкой мыши вычисляемое поле и выберите «Настройка поля значения».
ШАГ 9. В диалоговом окне «Настройка поля значения» выберите «Нарастающая сумма» в виде «Показать значение как!». Нажмите "ОК".
Это отсортирует значения от наибольшего к наименьшему и вставит поле ранга в сводную таблицу!
Заключение
В этом руководстве вы узнали, как ранжировать сводную таблицу с помощью параметра сортировки или вставки вычисляемого поля.
ПОЛЕЗНЫЙ РЕСУРС:
Вы можете узнать больше о том, как использовать Excel, просмотрев наш БЕСПЛАТНЫЙ веб-семинар по Excel, посвященный формулам, сводным таблицам, Power Query, макросам и VBA!
Сводная таблица позволяет легко суммировать большой объем данных, чтобы увидеть, как работает ваш бизнес. Но большие числа трудно анализировать с первого взгляда. Чтобы было понятнее, вы можете показать ранг в сводной таблице Excel, а также большие числа.
Продажи продуктов питания
В этом примере есть сводная таблица с продажами продуктов питания. Он показывает следующие поля:
- Товар в области строки
- Сумма количества в области значений
- Сумма SalePrice в области значений
Видео: Показать рейтинг в сводной таблице
Чтобы узнать, как добавить поле в эту сводную таблицу и изменить его для использования расчета рейтинга, посмотрите это короткое видео. Под видео есть письменные инструкции.
Изменить заголовки значений
Следующий шаг необязателен, но я обычно меняю заголовки сводных полей в области значений. Мне нравятся более короткие заголовки, когда это возможно, чтобы занимать меньше места на листе.
Когда вы добавляете поля в область значений, Excel автоматически помещает имя функции в начало, например заголовки «Сумма количества» и «Сумма цены продажи» в этом примере.
Чтобы изменить заголовок сводной таблицы, выберите ячейку заголовка и введите новый заголовок. Я внес следующие изменения:
- Сумма количества изменена на "Обращения".
- Сумма SalePrice изменена на Sales $
ПРИМЕЧАНИЕ. При вводе нового заголовка нельзя использовать имя, точно совпадающее с существующими сводными полями, например «Количество». Однако вы можете добавить пробел в начале или в конце, чтобы создать уникальное имя поля, например «Количество».
Вот исправленная сводная таблица с более короткими заголовками в полях значений.
Сортировать по сумме продаж в долларах
В сводной таблице видно, что продажи моркови и овсянки с изюмом высоки, а у сыра очень низкие значения. Однако не так просто увидеть, как остальные продукты себя показали по сравнению с другими.
Чтобы было проще увидеть, какие продукты имеют самые высокие и самые низкие продажи, вы можете отсортировать их по столбцу Sales $:
- Нажмите правой кнопкой мыши любое число в столбце Sales $
- Укажите на команду "Сортировка".
- Нажмите "Сортировать от большего к меньшему".
После того, как продукты отсортированы по суммам продаж в долларах, легко увидеть, какие продукты приносят самый высокий доход, а какие — самые низкие продажи.
Сравнить с проданными ящиками
Однако в сводной таблице есть еще один набор чисел. Как насчет количества проданных ящиков для каждого продукта? Находятся ли эти числа в том же порядке, что и суммы продаж в долларах?
На первый взгляд кажется, что количество обращений находится в том же порядке, что и количество продаж в долларах, но если вы внимательно проверите, есть несколько мест, где числа расположены в несколько ином порядке.
- Продажи продуктов Whole Wheat было меньше, чем у картофельных чипсов, но продажи были выше в долларах США.
- Продажа кренделей была меньше, чем у бананов, но продажи были выше в долларах США.
Показать значения как ранг
Чтобы упростить поиск различий в порядке, вы можете использовать функцию сводной таблицы "Показать значения как". Это настраиваемые вычисления, которые можно применить к полю "Значение", чтобы получить другое представление чисел.
Одним из настраиваемых вычислений является рейтинг, и мы применим этот параметр к полю "Обращения". Мы оставим исходное поле «Обращения» без изменений, показывающее «Сумма количества», и добавим еще один столбец «Рейтинг количества».
Вот как добавить еще один столбец в сводную таблицу и отобразить рейтинг на основе количества проданных ящиков.
- В списке полей сводной таблицы щелкните правой кнопкой мыши поле "Количество".
- Нажмите «Добавить к значениям».
Новое поле «Сумма количества» добавляется в область «Значения» сводной таблицы справа от поля «Продажи в долларах».
В этом новом поле будет отображаться рейтинг, поэтому измените его заголовок на «Рейтинг»
Изменить расчет
Следующий шаг — изменить пользовательский расчет для нового поля, чтобы он отображал рейтинг
- Нажмите правой кнопкой мыши любое число в столбце "Рейтинг".
- Укажите на команду "Показать значения как".
- Нажмите "Рейтинг от наибольшего к наименьшему".
Затем появится небольшое окно, в котором вас попросят выбрать базовое поле для расчета.
- В этой сводной таблице есть только одно поле строки (Продукт), поэтому это единственный вариант, который вы увидите в раскрывающемся списке для выбора базового поля.
- Нажмите "ОК", чтобы применить пользовательский расчет рейтинга.
Проверить рейтинг
В исправленной сводной таблице гораздо проще обнаружить любые различия в порядке обращений и продаж в долларах. Мы можем обрабатывать в уме небольшие числа, например от 1 до 10, без особых усилий!
Мы видим, что 7 предшествует 6, а 10 предшествует 9.
Переместить поле ранга
Поскольку поле "Рейтинг" основано на количестве обращений, мы переместим его влево, чтобы оно оказалось между полями "Обращения" и "Продажи $".
Чтобы переместить поле "Рейтинг":
- Нажмите на ячейку заголовка столбца "Рейтинг".
- Укажите на нижнюю границу выбранной ячейки.
- Перетащите ячейку слева от ячейки Sales $
Теперь вы можете увидеть обращения и их рейтинг и сравнить их с отсортированными значениями в столбце Sales $.
Гораздо легче увидеть расхождения в столбце "Рейтинг", чем сравнивать большие числа в столбце "Обращения".
Получить образец файла
Чтобы получить готовый образец файла для этого примера и просмотреть примеры других пользовательских вычислений, перейдите на страницу пользовательских вычислений сводной таблицы на моем веб-сайте Contextures.
Как отобразить рейтинг в сводной таблице Excel
Оставить ответ Отменить ответ
Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются данные ваших комментариев.
Добавление рейтинга в Excel 2003/2007
Шаг 1. Отсортируйте сводную таблицу по показателю данных, который вы пытаетесь ранжировать.
Шаг 2. Добавьте вычисляемое поле с формулой =1.
По сути, каждой строке будет присвоено значение 1.
Шаг 3. Щелкните правой кнопкой мыши только что созданное вычисляемое поле и перейдите в диалоговое окно «Параметры поля значений». Там вы выберете вкладку «Показать значения как». Измените настройку «Показать данные как» на «Промежуточный итог». Поскольку это поле используется для ранжирования рынков, мы меняем базовое поле на рынок.
Результатом будет новое поле данных, в котором сообщается об относительном рейтинге каждого рынка.
Добавление рейтинга в Excel 2010
Начните со сводной таблицы, подобной показанной здесь.
Обратите внимание, что одна и та же мера данных отображается дважды — в данном случае SumOfSalesAmount.
Щелкните правой кнопкой мыши второй экземпляр показателя данных и выберите "Показать значение как", а затем "Ранжировать от наибольшего к наименьшему".
После применения вашего рейтинга вы можете настроить метки и форматирование. В результате отчет о ранжировании будет иметь чистый вид.
Вчера мой приятель из-за канавы Хуэй опубликовал сообщение о формуле условного ранга (RANKIFS) с использованием удивительного СУММПРОИЗВ.
Конечно, не каждый может придумать такую формулу суммарного произведения. По шкале удивительности Excel от одного до хуэй вам нужно быть как минимум H, чтобы писать формулы суммового произведения или подсчета, показанные в этом посте. Значит ли это, что вы не можете условно ранжировать, если не отличаете X от L?
Не волнуйтесь. Мы тебя прикрыли. Вы по-прежнему можете получить свои условные ранги без формул массива начального уровня. Просто используйте вместо этого сводные таблицы.
Двухуровневое (или многоуровневое) ранжирование со сводными таблицами
Допустим, у вас есть такие данные, и вы хотите узнать, какой рейтинг каждого учащегося находится на уровне факультета.
< бр />
Процесс создания этих рангов до смешного прост. Просто,
- Вставьте сводную таблицу из ваших данных.
- Добавьте поля уровня и сведений в область меток строк (в этом случае мы добавим отдел и студент в область меток строк)
- Добавьте поле, по которому вы хотите ранжироваться, в область значений (например, оценка)
- Нажмите правой кнопкой мыши на поле значения и выберите "Показать значения как" > "Рейтинг..."
- Установите базовое поле в качестве нижнего уровня (в нашем случае это студент)
- Бинго, проблема с ранжированием решена.
Вот краткая демонстрация того, как использовать функцию ранжирования в сводных таблицах.
Скачать пример рабочей тетради:
Нажмите здесь, чтобы загрузить книгу с несколькими сценариями ранжирования сводной таблицы. Ознакомьтесь с ним, чтобы узнать больше.
Используете ли вы сводные таблицы для ранжирования?
Я постоянно использую их для такого сложного ранжирования на основе фильтров в своих отчетах. Хотя мы могли бы использовать формулы для получения аналогичных результатов, я думаю, что Pivot лучше всего подходит для ранжирования, поскольку они естественным образом разрешают связи (без сложной логики дедупликации) и дают результаты в короткие сроки.Единственная проблема заключается в том, что нам нужно обновлять их всякий раз, когда происходит изменение данных. Но это небольшая цена за отказ от сложных формул.
Что вы думаете? Используете ли вы сводные таблицы для ранжирования данных? Поделитесь своими историями в разделе комментариев.
Кроме того, ознакомьтесь с нашей сводной таблицей, чтобы узнать обо всем, от основ до уровня ниндзя.
Читайте также: