Как отсортировать сгруппированные строки в excel

Как отсортировать сгруппированные строки в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как сортировать данные в таблицах Excel (правильный способ)

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

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

1. Сортировка данных всего в несколько кликов
2. Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
3. Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.

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

Как сортировать данные в электронной таблице Excel (короткое видео)

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

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

Примеры данных (бесплатная загрузка рабочей книги Excel)

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

1. Простая сортировка в Excel

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

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

Заметьте, что в вариантах сортировки, вы можете отсортировать текст «А до Я» или «Я до А». Эти простые варианты помогут отсортировать данные в Excel В алфавитном или обратным порядках, в зависимости от того что вы выбрали.

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

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

Выполнить обычную сортировку на самом деле так просто. Просто кликните по данным, выберите вариант сортировки и Excel перестроит данные в таблице.

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

2. Как НЕ нужно сортировать данные в Excel

Не менее важно узнать, о самом опасном способе сортировки данных в Excel, такой метод может испортить ваши исходные данные.

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

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

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

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

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

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

На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.

Таким образом, при сортировке данных есть два ключевых «НЕ»:

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

3. Расширенная сортировка данных Excel.

До сих пор простая сортировка позволяла нам сортировать данные однотипно. Что, если мы хотим два типа данных в нашей сортировке?

Что, если мы хотим…

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

Ответ на всё это — расширенная сортировка, при которой вы можете установить несколько уровней сортировки данных. Давайте рассмотрим последний пример, используя образцы данных.

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

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

В моем случае я выберу Client в раскрывающемся меню и оставлю значение Сортировка равным Значения, а Порядок — От А до Я. На простом языке это отсортирует электронную таблицу Excel на основе алфавитного порядка.

Теперь давайте нажмем Добавить уровень. Это создаст новую строку в параметрах сортировки и позволит нам добавить второй уровень организации.

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

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

Как только мы нажмем OK, Excel отсортирует таблицу на основе правил, которые мы создали в этом окне.

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

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

Повторение и продолжение обучения

Сортировка это ещё одно умение, которое нужно держать в готовом виде в своей книге Excel. Когда вам нужно повторно сопоставить данные в электронной таблице, слишком много времени можно потратить для вырезания и вставки строк в определенном порядке, поэтому сортировка является обязательной.

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

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

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

Как отсортировать сгруппированные строки в excel

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

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

К сообщению приложен файл: 2016-06-06-____.xlsm (83.9 Kb)
Сообщение отредактировал FB — Пятница, 10.06.2016, 11:52

Сообщение Уважаемые коллеги!

Описание проблемы:
Группировка строк происходит макросам.
Все бы ничего но вот в последнее время сотрудники что то сделали
что при фильтрации, сортировки
сгруппированная область как бы закреплена за номером строки в самом файле
и остается не подвижно при манипуляциях. Автор — FB
Дата добавления — 10.06.2016 в 11:50

Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Замечаний: 0% ±

Excel 2010
Ни кто не может помочь ?
Ни кто не может помочь ? FB
Сообщение Ни кто не может помочь ? Автор — FB
Дата добавления — 10.06.2016 в 14:23
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Замечаний: 0% ±

2019
Цитата FB, 10.06.2016 в 11:50, в сообщении № 1
как бы закреплена за номером строки в самом файле

А что раньше не так было?
Тогда вложите старый файл — посмотрим.
Цитата FB, 10.06.2016 в 11:50, в сообщении № 1
как бы закреплена за номером строки в самом файле

А что раньше не так было?
Тогда вложите старый файл — посмотрим. SLAVICK
Цитата FB, 10.06.2016 в 11:50, в сообщении № 1
как бы закреплена за номером строки в самом файле

А что раньше не так было?
Тогда вложите старый файл — посмотрим. Автор — SLAVICK
Дата добавления — 10.06.2016 в 14:31

Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Замечаний: 0% ±

Excel 2010

Раньше было не так. Раньше при сортировки сгрупированные области передвигались. А сейчас фильтронешь или отсортируешь и в группировке уже другие данные. Я поищу но думаю что не найду.

Раньше было не так. Раньше при сортировки сгрупированные области передвигались. А сейчас фильтронешь или отсортируешь и в группировке уже другие данные. Я поищу но думаю что не найду. FB

Сообщение Раньше было не так. Раньше при сортировки сгрупированные области передвигались. А сейчас фильтронешь или отсортируешь и в группировке уже другие данные. Я поищу но думаю что не найду. Автор — FB
Дата добавления — 10.06.2016 в 14:34

Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Замечаний: 0% ±

Excel 2010
Цитата SLAVICK, 10.06.2016 в 14:31, в сообщении № 3
А что раньше не так было?
Тогда вложите старый файл — посмотрим.

Создал на другом ПК файл. там сортируется с учетом группировки

Цитата SLAVICK, 10.06.2016 в 14:31, в сообщении № 3
А что раньше не так было?
Тогда вложите старый файл — посмотрим.

Создал на другом ПК файл. там сортируется с учетом группировки FB

Сообщение отредактировал FB — Пятница, 10.06.2016, 14:44
Цитата SLAVICK, 10.06.2016 в 14:31, в сообщении № 3
А что раньше не так было?
Тогда вложите старый файл — посмотрим.

Создал на другом ПК файл. там сортируется с учетом группировки Автор — FB
Дата добавления — 10.06.2016 в 14:43

Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Замечаний: 0% ±

Excel 2010

Создал на другом ПК файл. там сортируется с учетом группировки

Создал на другом ПК файл. там сортируется с учетом группировки FB

К сообщению приложен файл: 7317024.xlsx (7.7 Kb)

Создал на другом ПК файл. там сортируется с учетом группировки Автор — FB
Дата добавления — 10.06.2016 в 14:44

Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Замечаний: 0% ±

2019

У меня и в 1-м файле значения сортирует. но группировки остаются.

Сделайте пример с двумя листами как есть и что должно получится после сортировки.
А то ничего не понятно.

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

У меня и в 1-м файле значения сортирует. но группировки остаются.

Сделайте пример с двумя листами как есть и что должно получится после сортировки.
А то ничего не понятно.

Или Вам нужно чтобы все значения подтянулись вверх?
Тогда откройте плюсы — и сортируйте. SLAVICK

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK — Пятница, 10.06.2016, 15:42

Сообщение У меня и в 1-м файле значения сортирует. но группировки остаются.

Сделайте пример с двумя листами как есть и что должно получится после сортировки.
А то ничего не понятно.

Или Вам нужно чтобы все значения подтянулись вверх?
Тогда откройте плюсы — и сортируйте. Автор — SLAVICK
Дата добавления — 10.06.2016 в 15:38

Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Замечаний: 0% ±

Excel 2010

SLAVICK,
Файл как нужно :

гр. 1 ( 5 5 5 5 6), гр.3, гр.3, гр.5

Если отсортировать его по убыванию то порядок будет такой гр.5, гр.3, гр.3, гр.1 ( 5.5.5.5.6).
Т.е. область сгрупированных данных «гр.1» уползла полностью с верху вниз.

А у меня получается, что сортировка происходит игнорируя группировку и выставляет числа в порядке 1,3,3,5,5,5,5,5,6. и наоборот. а область группировки остается на месте и в гр.1 уже объединяет в себе не те цифры.
Не получается на одном листе сделать 2 варианта.
Мне нужно как в 1 варианте я описал.
Т.к. Файл очень большой и столбцов там гораздо больше мне нужно разобраться в чем причина.

SLAVICK,
Файл как нужно :

гр. 1 ( 5 5 5 5 6), гр.3, гр.3, гр.5

Если отсортировать его по убыванию то порядок будет такой гр.5, гр.3, гр.3, гр.1 ( 5.5.5.5.6).
Т.е. область сгрупированных данных «гр.1» уползла полностью с верху вниз.

А у меня получается, что сортировка происходит игнорируя группировку и выставляет числа в порядке 1,3,3,5,5,5,5,5,6. и наоборот. а область группировки остается на месте и в гр.1 уже объединяет в себе не те цифры.
Не получается на одном листе сделать 2 варианта.
Мне нужно как в 1 варианте я описал.
Т.к. Файл очень большой и столбцов там гораздо больше мне нужно разобраться в чем причина.

К сообщению приложен файл: 7526611.xlsx (19.4 Kb)
Сообщение отредактировал FB — Пятница, 10.06.2016, 17:14

Сообщение SLAVICK,
Файл как нужно :

гр. 1 ( 5 5 5 5 6), гр.3, гр.3, гр.5

Если отсортировать его по убыванию то порядок будет такой гр.5, гр.3, гр.3, гр.1 ( 5.5.5.5.6).
Т.е. область сгрупированных данных «гр.1» уползла полностью с верху вниз.

А у меня получается, что сортировка происходит игнорируя группировку и выставляет числа в порядке 1,3,3,5,5,5,5,5,6. и наоборот. а область группировки остается на месте и в гр.1 уже объединяет в себе не те цифры.
Не получается на одном листе сделать 2 варианта.
Мне нужно как в 1 варианте я описал.
Т.к. Файл очень большой и столбцов там гораздо больше мне нужно разобраться в чем причина.

Благодарю. Автор — FB
Дата добавления — 10.06.2016 в 17:10

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

Сортировать данные в Excel умеют даже новички. Трудно не заметить сразу три кнопки на ленте.

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

Сортировка в Excel по нескольким столбцам

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

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

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

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

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

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

Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки…

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

Теперь в окне сортировки в поле Порядок нужно выбрать Настраиваемый список…

И в следующем окне указать нужный список.

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

Сортировка в Excel по цвету ячейки, шрифта, значку

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

Сортировка по столбцам

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

Последующие настройки обычные: задаем строку (!) и порядок. Единственное, теперь нельзя использовать названия строк (по аналогии с названием столбцов), будут только номера.

Сортировка промежуточных итогов

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

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

Внутреннее содержимое групп не изменится. Общий итог останется внизу.

Сортировка строк в случайном порядке

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

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

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