Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить сортировку с настраиваемым порядком списка в сводной таблице?

Как выполнить сортировку с настраиваемым порядком списка в сводной таблице?

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

При сортировке данных в pivotTable следует помнить о следующих вещах:

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

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

В отличие от сортировки данных в диапазоне ячеек в Excel для Mac или на рабочем листе Excel для Mac, вы не можете сортировать текстовые записи, содержащие конфиденциальные данные.

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

Сортировка по собственному списку

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

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

Сортировка с помощью дополнительного столбца

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

Список можно изменить, перейдя в меню Файл — Предпочтения — Дополнительно — Общие — Изменить список.

Создание списка

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

Параметры списка

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

Настраиваемый список в параметрах сортировки

В следующем окне необходимо выбрать нужный список.

Выбор списка для сортировки

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

Как выполнить сортировку с настраиваемым порядком списка в сводной таблице?

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

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

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

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

doc-sort-custom-pivottable-1

1. Во-первых, необходимо создать собственный список, нажав Файл > Доступные параметры, затем нажмите Дополнительно в левой панели, затем прокрутите вниз и нажмите Редактировать специальные списки в разделе Общие, как показано на скриншоте:

doc-sort-custom-pivottable-1

Совет: Если вы используете Excel 2007, нажмите на кнопку Office > Параметры Excel, затем на Популярные в левой панели и выберите Изменить специальные списки в разделе Лучшие параметры Excel, см. скриншот :

doc-sort-custom-pivottable-1

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

doc-sort-custom-pivottable-1

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

doc-sort-custom-pivottable-1

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

doc-sort-custom-pivottable-1

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

О: Если поле настроено на автоматическую сортировку, щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите «Обновить», и поле будет отсортировано в соответствии с порядком списка;

Ответ: Если поле в вашей сводной таблице настроено на ручную сортировку, щелкните правой кнопкой мыши ячейку в поле региона и выберите Сортировка > Сортировать от A до Z.

doc-sort-custom-pivottable-1

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

Сортировка по дате в сводной таблице

У меня есть сводная таблица с полем даты, которое называется Mydate.

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

Затем я переставил годы и дни в обратном порядке, щелкнув на ярлыке и выбрав «Сортировать от нового к старому»

Попытка отсортировать дни после расширения месяца, чтобы показать дни, портит сортировку. он рассматривает дни выше 10 как наступающие перед днем 2, 3 и т. д. Д.

Вот как выглядит секция строковой метки:

Есть ли способ исправить это в сводной таблице или мне нужно сделать дополнительный столбец "День", чтобы это работало правильно?

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

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

Читайте так же:
Как добавить / вычесть недели до даты в Excel?

Длинный: у нас есть краткий формат даты в настройках локали Windows, чтобы иметь только одну цифру для дня. Control Panel – Clock, Language, and Region – Region and Language :

Это немецкая Windows. В английской Windows это будет, например, m/d/yy.

Теперь мы имеем следующие данные Excel.

Обратите внимание, что формат даты в Excel – mm/dd/yyyy .

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

В настройках локали Windows теперь есть две цифры для дня недели.

Опять же, это немецкая Windows. В английской Windows это будет, например, mm/dd/yyyyy .

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

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

Важно: При сортировке данных учитывайте информацию, приведенную ниже.

Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе "Региональные параметры" или "Язык и региональные стандарты" задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.

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

Текстовые значения нельзя отсортировать по регистру.

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

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

Для сортировки в классическом Excel выполните следующие шаги:

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

Щелкните по полю в строке или столбце, которое вы хотите отсортировать.

Щелкните стрелку в списке Имена строк или Имена столбцов, затем выберите нужный вариант.

Чтобы отсортировать данные в порядке возрастания или убывания, нажмите кнопки Сортировать от A до Z или Сортировать от Z до A.

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

В примере ниже данные на уровне категорий (шлем, дорожная сумка) сортируются в алфавитном порядке от A до Z.

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

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

Установка собственных параметров сортировки

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

Щелкните ячейку в строке или столбце, которую нужно отсортировать.

Затем выберите Дополнительные параметры сортировки на вкладке Ярлыки строк или Ярлыки колонок.

Выберите нужный тип сортировки в диалоговом окне Sort (Сортировка):

Читайте так же:
Как выполнить фильтрацию по месяцам в сводной диаграмме в Excel?

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

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

Вы можете сортировать по возрастанию (от A до Z) или по убыванию (от Z до A) по полям, нажав соответствующую кнопку.

Выберите «Дополнительные параметры» из выпадающего списка, а затем выберите параметр из списка:

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

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

В Microsoft Excel есть встроенные пользовательские списки дней недели и месяцев года, но вы также можете создать свой собственный.

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

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

Совет: Чтобы восстановить исходный порядок элементов, выберите опцию Как в источнике данных. Он доступен только для источника данных OLAP.

Ниже описано, как можно быстро сортировать строки и столбцы данных.

Щелкните ячейку в строке или столбце, которую вы хотите отсортировать.

Выберите соответствующий вариант сортировки, нажав на стрелку в списках Row Names (Имена строк) или Column Names (Имена столбцов).

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

Чтобы отсортировать данные в порядке возрастания или убывания, выберите Сортировать от A до Z или Сортировать от Z до А.

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

Сортировка по определенному значению

Проделайте следующие шаги:

Нажмите на стрелку в списке названий строк и выберите Сортировать по значению.

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

Выберите значение сортировки, которое вы хотите использовать в поле Select value (Выбрать значение).

В группе Параметры сортировки выберите необходимый порядок сортировки.

Трюки — решения

В перекрестных таблицах есть возможность объединять элементы в группы. Самый простой способ группировки элементов — через Works with Crosstabs ► Options ► Group ► Group by Selected.

Но вы можете сделать еще один шаг вперед. Когда поле содержит даты, Excel может автоматически создавать группы. Этой функции очень не хватает многим пользователям. В части рисунка 171.1 показана таблица, содержащая два столбца: Дата и Продажи. Она содержит 93156 строк и включает данные с 8 июля 2006 года по 8 августа 2009 года. Мы хотим агрегировать информацию о продажах по месяцам.

Рисунок 171.1. Вы можете использовать сводную таблицу для обобщения данных о продажах за месяц.

На рисунке 171.2 показана сводная таблица, созданная на основе данных (столбцы D:E). Созданные данные выглядят так же, как и исходные данные, поскольку даты не были сгруппированы. Выберите команду Группировать из контекстного меню, щелкнув правой кнопкой мыши ячейку в столбце Дата сводной таблицы. Вы увидите окно Группировка, показанное на рисунке 171.3. Выберите Месяцы и годы и убедитесь, что начальная и конечная даты указаны правильно. Нажмите кнопку OK. Элементы «Дата» в сводной таблице упорядочены по годам и месяцам (см. Рисунок 171.4).

Рис. 171.2. Сводная таблица до группировки по месяцам и годам

Читайте так же:
Как выполнить vlookup значения на нескольких листах, чтобы вернуть имена листов в Excel?

Рисунок 171.3. Используйте окно группировки для группировки элементов сводной таблицы

Если в окне группировки выбрать только Месяц, то будут объединены месяцы из разных лет. Например, в пункте Июнь будут показаны продажи за 2008 и 2009 годы. Обратите внимание, что окно группировки также содержит другие элементы времени. Например, вы можете сгруппировать данные по кварталам (Рисунок 171.5).

Сводная таблица сортировки

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

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

Шаги для создания сводной таблицы в Excel

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

Этот шаблон сортировки таблиц Excel можно скачать здесь — Шаблон сортировки электронных таблиц Excel

Шаг 1: Выберите таблицу, из которой нужно получить данные.

Шаг 2: Нажмите кнопку Insert (Вставить).

Выберите итоговую таблицу, чтобы открыть всплывающее окно.

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

4: Выберите место размещения сводной таблицы; здесь создается новая страница, а не новый лист.

Шаг 5: Укажите точное расположение сводной таблицы.

Нажмите OK, и сводная таблица будет создана.

Шаг 6: Теперь, когда перекрестная таблица создана, укажите, какие данные вы хотите отобразить.

Конечный результат должен выглядеть более или менее так:

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

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

Как отсортировать столбцы сводной таблицы в Excel?

Сортировка данных в маленьких таблицах может быть выполнена путем выбора столбца «Row Label», если вы хотите сортировать по алфавиту, а затем выбрать AZ или ZA в качестве способа сортировки.

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

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

Для больших плит выполните следующие действия:

  1. Нажмите кнопку «Данные» в меню ленты в верхней части экрана.

  1. Нажмите на кнопку Сортировать, появится всплывающее окно.

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

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

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

Как отсортировать строки сводной таблицы в Excel?

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

Рассмотрите следующую таблицу:

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

Вот шаги, которые вам нужно выполнить:

  1. Щелкните правой кнопкой мыши на ячейке, которую вы хотите начать сортировать, появится всплывающее меню:

  1. Выберите Сортировка, а затем в следующем раскрывающемся списке выберите Дополнительные параметры сортировки.

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

Выберите «Слева направо» вместо «Сверху вниз» и нажмите OK.

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

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

То, что нужно запомнить

  1. Перед сортировкой данных убедитесь, что в начале значений нет лишних пробелов, эти пробелы повлияют на результаты сортировки, особенно если мы сортируем текстовые значения или используем текстовые параметры, такие как AZ или ZA алфавитная сортировка.
  2. В сводных таблицах сортировка не может быть выполнена для текста, чувствительного к регистру, весь текст рассматривается как один и тот же регистр и не чувствителен к регистру.
  3. Сводные таблицы не позволяют сортировать по определенному форматированию, такому как цвет ячеек, цвет шрифта или индикаторы условного форматирования, такие как наборы значков.
  4. Сортировка по итоговым точкам требует практики и знания структуры данных, с которой вы работаете, так как от этого будет зависеть выбор параметров сортировки.
  5. При использовании нескольких уровней сортировки никогда не выбирайте всю таблицу, так как это изменит структуру сводной таблицы и вместо желаемого результата выдаст ошибку.

Вывод

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

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

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

Рекомендуемые статьи

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector