Сортировка данных в диаграмме Excel

Обновлено: 21.11.2024

Как отсортировать диаграмму по значению в Excel?

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

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

<р>1. Выберите исходное значение данных, по которому вы хотите выполнить сортировку. Смотрите скриншот:

<р>2. Перейдите на вкладку «Данные», перейдите в группу «Сортировка и фильтр» и выберите нужный порядок сортировки. Смотрите скриншот:

<р>3. Затем в появившемся диалоговом окне убедитесь, что установлен флажок «Развернуть выбор», и нажмите кнопку «Сортировать». Смотрите скриншот:

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

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

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

Способ 1. Сортировка на исходном листе

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

Выберите исходный диапазон на листе.
Затем нажмите кнопку "Сортировка и фильтр" на панели инструментов.
Затем в раскрывающемся меню выберите параметр «Пользовательская сортировка». Таким образом, на листе появится окно «Сортировка».
В окне "Сортировка" задайте нужные критерии. Здесь мы отсортируем второй столбец по его значениям. Кроме того, порядок сортировки — «от меньшего к большему». Вы также можете установить критерии в соответствии с вашими реальными потребностями и фактическим рабочим листом.
Затем нажмите кнопку "ОК" в окне "Сортировка".

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

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

Способ 2. Сортировка на сводной диаграмме

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

Выберите целевой диапазон на листе.
Затем нажмите на вкладку "Вставить" на ленте.
После этого нажмите маленькую кнопку со стрелкой под кнопкой "Сводная таблица".
Далее вы увидите раскрывающееся меню только с двумя вариантами. На этом шаге вам нужно выбрать второй вариант «Сводная диаграмма».
После этого на листе появится всплывающее окно «Создать сводную таблицу со сводной диаграммой». Поскольку мы выбрали область на шаге 1, здесь вам не нужно снова выбирать диапазон. Конечно, вы пропустите шаг 1, здесь вы также можете ввести ссылку на диапазон в текстовое поле. Кроме того, вы также можете использовать мышь, чтобы выбрать диапазон напрямую.
Теперь вам нужно решить, где вы хотите разместить сводную диаграмму. Вы можете либо поместить его в новый рабочий лист, либо поместить в существующий рабочий лист. Здесь мы выбираем первый вариант «Новый рабочий лист». С другой стороны, если вам нужно поместить в существующий рабочий лист, вам также нужно ввести местоположение в соответствующее текстовое поле. И вы можете использовать мышь, чтобы выбрать точное местоположение.
Когда вы закончите настройку сводной диаграммы, вы можете нажать кнопку «ОК» в окне. Таким образом, вы создали новую сводную диаграмму в текущем файле Excel.
В «Списке полей сводной таблицы» выберите поле, которое нужно отобразить на диаграмме. В этом примере мы покажем два поля на диаграмме, поэтому мы проверяем два параметра.
Теперь щелкните сводную диаграмму на листе. В то же время на ленте вы увидите вкладку «Инструменты для сводных диаграмм».
Затем нажмите кнопку поля на диаграмме в левой нижней части.
В новом всплывающем списке выберите параметр «Дополнительные параметры сортировки».
После этого вы увидите новое всплывающее окно «Сортировка». В этом окне вы можете установить параметры в соответствии с вашими потребностями. Здесь, в этом примере, мы выберем вариант «По возрастанию». Целевым столбцом является «Сумма восстановления Excel DataNumen».
После этого нажмите кнопку «ОК» внизу, чтобы завершить настройку. Таким образом, результат сортировки появится на диаграмме. И вы также обнаружите, что на исходном листе диапазон уже изменился соответствующим образом.

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

Сравнение двух вышеуказанных методов

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

Сортировка в сводной диаграмме

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

Причину повреждения Excel можно обнаружить

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

Но если вы ничего не знаете о восстановлении Excel, не пытайтесь восстанавливать файлы Excel самостоятельно. Неправильное поведение приведет к необратимому повреждению ваших ценных файлов. Лучший выбор – обратиться за помощью к профессионалам. Кроме того, вы можете использовать сторонний инструмент для восстановления поврежденного файла Excel xlsx. Таким образом, ваши данные и информация будут в безопасности. Этот инструмент будет установлен на вашем собственном компьютере. Кроме того, он прост в эксплуатации. Ваши данные и информация не будут видны никому другому, потому что процесс восстановления полностью происходит на вашем собственном компьютере.

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

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

Что делает это таким классным, так это то, что мы добьемся этого без ручной фильтрации и без ручного скрытия.

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

Приступаем к работе!

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

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

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

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

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

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

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

Чтобы создать список названий выбранных компаний, мы можем использовать функцию ФИЛЬТР, чтобы создать список компаний, в которых для ячейки «Показать» установлено значение «1».

Первый аргумент функции FILTER указывает, какой массив мы фильтруем. Мы могли бы выбрать весь набор названий компаний и соответствующих продаж (A4:B18), но мы будем использовать другой подход.

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

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

Начнем с использования ФИЛЬТРА, чтобы выбрать список названий компаний.

Теперь мы сообщаем FILTER, что хотим включать только те компании, у которых в столбце "Показать" стоит цифра "1".

Результирующий рассеянный массив результатов выглядит следующим образом.

Мы хотим отсортировать результаты функции ФИЛЬТР в порядке убывания соответствующих продаж.

У нас есть две новые функции динамического массива, помогающие в сортировке: SORT и SORTBY.

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

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

Обновленная функция начинает изменяться следующим образом:

Мы говорим функции SORTBY отсортировать исходный список.

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

ПРИМЕЧАНИЕ. Второй аргумент SORTBY должен быть того же размера, что и первый аргумент.

Наконец, мы указываем SORTBY сортировать список в порядке убывания (-1).

Теперь у нас есть отфильтрованный список компаний, отсортированный по их продажам.

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

Следующий шаг – поиск продаж выбранных компаний в нашей таблице подготовки данных.

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

В таблице подготовки данных рядом с первой возвращенной компанией мы вводим следующую функцию ВПР:

Нанесение небольшого количества косметики

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

Выполнение еще одного теста

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

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

Начнем с выбора таблицы подготовки данных (E3:F10) и выберите Вставка (вкладка) -> Диаграммы (группа) -> Двумерная гистограмма с кластерами.

Проблема со статическими диапазонами

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

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

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

Создание диапазона динамической диаграммы

Менее известная способность диаграмм заключается в том, что они не только могут принимать прямые ссылки на свои данные (например, Sheet1!$A$1:$C$10), но также могут принимать именованные диапазоны.

Мы настроим это, создав именованный диапазон, который указывает на разнесенный массив.

Выберите Формулы (вкладка) -> Определенные имена (группа) -> Диспетчер имен.

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

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

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

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

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

В диалоговом окне "Редактировать ряд" измените поле "Значения ряда:", удалив все, что находится справа от восклицательного знака, и заменив его именованным диапазоном "Значение".

В диалоговом окне "Выбор источника данных" в зоне "Метки горизонтальной оси (категории)" нажмите "Изменить".

В диалоговом окне "Метки оси" измените поле "Диапазон меток оси:", удалив все, что находится справа от восклицательного знака, и заменив его именованным диапазоном "Компенсация".

ПРИМЕЧАНИЕ. Если вы не помните имена, назначенные ранее в Диспетчере имен, для быстрого доступа нажмите клавишу F4 (на шагах 3 или 5), чтобы открыть диалоговое окно «Вставить имена», в котором появится список всех именованных диапазонов.

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

Настройка диаграммы

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

Добавьте метки данных к столбцам.
Удалить ярлыки оси Y.
Настроить заголовок
Изменить цвет полос
Удалить горизонтальные линии сетки.
Удалить границу диаграммы

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

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

Хитрость, чтобы заставить это работать, состоит в том, чтобы списки групп были установлены в стороне…

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

Не слишком отвлекайтесь на формулу, просто поймите, что если пользователь выберет "Все" из раскрывающегося списка, каждая компания получит "1" в соответствующей ячейке "Показать".

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

Не беспокойтесь о том, как работает функция СУММПРОИЗВ; это просто семя, которое мы сажаем в ваш мозг, чтобы показать, что возможно.

Дополнительную информацию о функции СУММПРОИЗВ см. в видео и публикациях о функции СУММПРОИЗВ здесь и на канале XelPlus на YouTube.

Увеличение и уменьшение размеров таблиц

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

Если мы добавим новую компанию с названием «Жирный шрифт» с объемом продаж 20 000, новая компания появится, когда мы выберем «Все» в раскрывающемся списке.

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

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

Практическая рабочая тетрадь

Вы можете скачать рабочую тетрадь ЗДЕСЬ.

ПОЛНЫЙ курс о функциях Excel в 365 и Office 2021

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

Это довольно простая концепция, которая оживит ваши диаграммы, когда и как вам нужно.

Разделы:

Основная настройка данных

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

Вот основные настройки данных:

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

Убедитесь, что у вас есть заголовки/заголовки в первой строке, и все будет в порядке.

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

Фильтрация данных на диаграмме

Давайте покажем только магазины с продажами более 180 000 долларов США и находящиеся в Восточном регионе.

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

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

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

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

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

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

Теперь порядок в магазине будет обратным:

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

Проблема изменения размера диаграммы

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

Щелкните правой кнопкой мыши диаграмму и выберите Формат области диаграммы.

В открывшемся окне перейдите в раздел "Свойства" и выберите "Не перемещать и не изменять размер с помощью ячеек", а затем нажмите "Закрыть".

После этого размер диаграммы не изменится при фильтрации отображаемых данных.

Примечания

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

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

Как создать диаграмму и управлять ею в Excel
Учебное пособие. В этом учебном пособии я собираюсь познакомить вас с созданием и управлением диаграммами в Excel. Перед.

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

Объединение нескольких типов диаграмм в Excel для создания мощных диаграмм
Учебное пособие. В этом учебном пособии я покажу вам, как объединить несколько типов диаграмм для создания СИ.

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

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Читайте также: