Как отсортировать данные с дубликатами для графика Excel
В процессе сортировки с помощью формул Excel часто возникает проблема при наличии повторяющихся значений. Рассмотрим пример как правильно сделать умную сортировку с обработкой дубликатов используя формулы Excel.
Как найти дублирующиеся значения в исходных данных Excel
Имеется таблица исходных данных о продуктах и их количестве. Необходимо с помощью формул выбирать из исходной таблицы Топ-5 наибольших значений в последнем столбце при каждом обновлении данных. Логика проста для следующих действий на пути к решению задачи. Сортируем значения с помощью функции LARGE по убыванию и делаем выборку данных из первых 5-ти строк. Но есть небольшой нюанс в Техническом Задании! Некоторые значения содержат дубликаты для разных наименований продуктов. В базах данных номенклатуры товаров очень часто случаются такие совпадения, особенно в количествах.
Для начала проверим исходную таблицу на наличие повторяющихся значений с помощью условного форматирования. Для этого сначала выделите весь диапазон ячеек исходной таблицы A1:C11 и выберите инструмент: «Home» - «Conditional Formatting» - «Highlight Cells Rules» - «Duplicate Values». В появившемся диалоговом окне «Duplicate Values» просто нажмите Ok.
Как видно таблица содержит одинаковые значения в последнем столбце. Значит некоторые товары, а конкретно «Product1» и «Product7» совпадают по количеству в базе данных.
Проблема формулы при сортировке одинаковых значений в Excel
Если мы будем сортировать исходные данные с помощью традиционных формул, по причине наличия дубликатов в разных наименованиях возникнут проблемы после сортировки.
Как видно такой подход будет ошибочным, так как теперь дублируются и наименования товаров, что является недопустимым в отчете о Топ-5 показателей.
Как правильно сортировать дубликаты с помощью формулы в Excel
Для решения данной задачи следует использовать более сложные, но эффективные формулы. На первом шаге мы будем также использовать функцию LARGE:
=LARGE($C$1:$C$10,A1)
Но для подстановки наименований продуктов соответственным значениям будем использовать полностью модифицированную формулу:
=INDEX($B$1:$B$10,SMALL(IF($C$1:$C$10=E3,ROW($C$1:$C$10)),COUNTIF($E$1:E3,E3)))
Как видно на рисунке теперь формулы сортируют исходные значения без ошибок с любым количеством дубликатов.
Полезный совет! Модифицированная формула использует функцию ROW. Поэтому используйте ее, всегда заполняя столбец от первой строки рабочего листа Excel, чтобы не усложнять и сохранить работоспособность функций. Поэтому шапка таблицы с названиями заголовков столбцов изначально расположена внизу под значениями, чтобы не нарушать конструкции сложной формулы с функцией ROW. И не усложнять формулу, оставив ее наиболее простой вариант для восприятия и понимания.
Сортировка рейтинга по убыванию на чарте Excel
Теперь подготовим данные для чарта. В столбцах «Top-5 Products» и «Value» мы ссылаемся на первые 5 ячеек из отсортированных последних двух столбцов в исходной таблице «Sort Name» и «Sort Value». А третий столбец содержит формулу для расчета правильного отступа линий гистограмм, относительно указанного значения управления в ячейке E13.
=$C$14*$E$13-C14
Значения в столбце отступа позволят нам построить горизонтальную гистограмму и вынести подписи данных за внешнюю границу линий. Чтобы это легче понять лучше наглядно показать. Выделите диапазон ячеек таблицы чарта B13:D18 и выберите инструмент: «Insert» - «Charts» - «2-D Bar» - «100% Stacked Bar».
Как видно благодаря последнему столбцу «Indent» у нас наибольше значение не занимает всю ширину поля визуализации. Остается место для творческого дизайна и подписей данных с внешней стороны бара.
Выполним несколько подготовительных настроек чарта рейтинга топ-5. Выделите вертикальную ось Y на чарте и нажмите комбинацию горячих клавиш CTRL+1 (единица должна нажиматься не на бухгалтерской дополнительной клавиатуре, а та клавиша основной клавиатуры, что расположен над клавишей Q) для вызова дополнительного окна настройки параметров «Format Axis». В разделе настроек «Axis Options» поставьте флажок на против опции «Categories in reverse order» и в результате ось отсортирует свои значения по убыванию. Соответственно все бары чарта также будут отображаться по убыванию, как и следует презентовать Рейтинг топ-5.
Далее выделите серию данных «Indent» и снова нажмите комбинацию клавиш CTRL+1 для вызова дополнительного окна настроек «Format Data Series». В разделе «Series Options» должно быть выбрано «Series Indent».
Затем нажмите на плюс возле чарта для вызова контекстного меню, где следует отметить флажком опцию «Data Labels» - «Inside Base».
Мы добавили подписи на внешнюю сторону баров, теперь следует правильно указать источник данных. Для этого выделите одним кликом левой кнопкой мышки подписи данных и нажмите комбинацию клавиш CTRL+1 для вызова дополнительного окна «Format Data Labels». В разделе «Labels Options» поставьте флажок на опции «Value From Cells» и нажмите на кнопку «Select Range» чтобы в появившемся окне «Data Label Range» указать правильный источник для отображения подписей баров. То есть ссылка на второй столбец: =Sheet1!$C$14:$C$18. После чего не забудьте снять флажок с опции «Value» как показано ниже на рисунке.
Для улучшения внешнего вида баров и лучшей читабельности подписей расширьте ширину баров. Для этого выделите любую серию данных на чарте, нажмите CTRL+1 для вызова окна «Format Data Series». В разделе «Series Options» измените параметр «Gap Width» на 80%.
Цветовая схема для завершения дизайна визуализации отсортированных данных
Теперь все что осталось сделать – это окрасить все элементы визуализации красивыми цветами. Как вариант может быть такая цветовая схема.
Благодаря тому что сортировка реализована с помощью формул, данные чарта могут автоматически обновляться и сохранять структуру рейтинга по убыванию без проблем с дубликатами. Пример практического использования сортировки графиков на шаблоне дашборда в Excel:
Дашборд для управления KPI планами в Excel.
