Сортировка ячеек в excel по содержимому. Cортировка строки столбцов в списках Excel

03.03.2024

Три главных алгоритма сортировки в Excel.

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

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

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

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

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

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

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

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

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

Простейшая сортировка.

Простейшая сортировка в Excel осуществляется с помощью кнопок «Сортировка по возрастанию» и «Сортировка по убыванию», расположенных на панели инструментов «Стандартная». (На рисунке ниже эти кнопки обведены красным эллипсом.)

Задача №1:

Определить: какое из выпущенных изделий самое тяжелое и какова его масса? Когда это изделие было изготовлено?

1. Открываем в MS Excel файл .

2. Активируем щелчком мыши ячейку E7 с заголовком столбца «Масса 1шт, т» (можно активировать любую ячейку в интересующем нас столбце).

3. Нажимаем кнопку «Сортировка по убыванию» на панели инструментов «Стандартная».

4. Считываем ответ на поставленный вопрос в самой верхней строке базы данных (строка №8). Самое тяжелое изделие в базе – Балка 045 из заказа № 2. Изготавливалась Балка 045 с 23-его по 25-е апреля 2014-ого года (смотри записи в строках Excel №8-10).

5. Вернуть базе данных вид, предшествовавший сортировке в Excel можно (если нужно), нажав кнопку «Отменить» той же панели инструментов «Стандартная». Или можно применить «Сортировку по возрастанию» для столбца «Дата» базы данных.

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

Сортировка этим способом может быть осуществлена последовательно по двум или трем столбцам.

Задача №2:

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

1. Активируем щелчком мыши любую ячейку базы данных (например, ячейку C11).

2. Нажимаем кнопку главного меню «Данные» и выбираем «Сортировка…».

3. В выпавшем окне «Сортировка диапазона» выбираем из выпадающих списков значения так, как показано на снимке с экрана слева и нажимаем «ОК».

4. Задача №2 выполнена. Записи, во-первых, отсортированы по номерам заказов и, во-вторых, внутри каждого из заказов расположены по алфавиту имен изделий.

Итоги.

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

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

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, отзывы и замечания пишите в комментариях.

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

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

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

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

Теперь будем добавлять столбцы, по которым будет идти сортировка. Первым, в нашем случае, идет столбец Продукты . В поле Сортировка выставляем Значение и устанавливаем Порядок от А до Я .

Теперь продукты будут сортироваться сначала по первому столбцу и дополнительно по второму. При этом вторая сортировка не оказывает влияние на первую. Таким же образом можно осуществлять сортировку и по большему количеству столбцов.

Кстати, сортировка может также осуществляться и по строкам. Как это сделать я расскажу в следующем уроке.

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

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

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

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

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

Примечания:

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

Дополнительные сведения см. в статьях Фильтрация данных в таблице или диапазоне Excel и Применение условного форматирования в Excel .

Сортировка текстовых значений

Примечания: Возможные проблемы

Убедитесь, что все данные сохранены в текстовом виде. Если столбец, который подлежит сортировке, содержит числа, сохраненные в числовом формате, и числа, сохраненные в текстовом формате, необходимо отформатировать все ячейки либо как числа, либо как текст. В противном случае числа, сохраненные в числовом формате, после сортировки окажутся выше чисел, сохраненных в текстовом формате. Для преобразования всех выбранных данных в текстовый формат нажмите CTRL+1 , чтобы открыть диалоговое окно Формат ячеек , откройте вкладку Число и в разделе Категория выберите вариант Общий , Число или Текст .

Удалите все начальные пробелы. Иногда импортируемые из других приложений данные могут дополняться начальными пробелами. Прежде чем выполнять сортировку, удалите их из данных. Это можно сделать вручную или с помощью функции СЖПРОБЕЛЫ .

Сортировка чисел

Примечания:

Возможные проблемы

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

Сортировка значений даты и времени

Примечания: Возможные проблемы

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

Для сортировки по дням недели измените формат ячеек таким образом, чтобы в них выводились дни недели. Для сортировки по дням недели независимо от даты преобразуйте их в текст с помощью функции ТЕКСТ . Однако функция ТЕКСТ возвращает текстовое значение, поэтому сортировка будет выполнена по алфавитно-цифровым данным. Дополнительные сведения см. в статье Отображение дат в виде дней недели .

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

Возможно, вы захотите выполнить сортировку по двум или нескольким столбцам или строкам, чтобы сгруппировать данные с одинаковыми значениями в одном столбце или строке, а затем отсортировать эти группы с одинаковыми значениями по другому столбцу или строке. Например, если есть столбцы "Отдел" и "Сотрудник", можно сначала выполнить сортировку по столбцу "Отдел" (для группировки всех сотрудников по отделам), а затем - по имени (для расположения имен сотрудников каждого отдела в алфавитном порядке). Можно одновременно выполнять сортировку по 64 столбцам.

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

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

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

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

Для сортировки в порядке, заданном пользователем, можно применять настраиваемые списки. Например, столбец может содержать значения, по которым вы хотите выполнить сортировку, такие как "Высокий", "Средний" и "Низкий". Как настроить сортировку, чтобы сначала отображались значения "Высокий", затем - "Средний", а в конце - "Низкий"? Если отсортировать их в алфавитном порядке (от А до Я), то значения "Высокий" будут отображаться вверху, но за ними окажутся значения "Низкий", а не "Средний". А при сортировке от Я до А значения "Средний" окажутся в самом верху. В действительности значения "Средний" всегда, независимо от порядка сортировки должны отображаться в середине. Вы можете решить эту проблему, создав настраиваемый список.

Сортировка с учетом регистра

Сортировка слева направо

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

Примечание: Таблицы не поддерживают возможность сортировки слева направо. Сначала преобразуйте таблицу в диапазон: выделите в ней любую ячейку и выберите пункты Работа с таблицами > Преобразовать в диапазон .

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

Сортировка по части значения в столбце

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

Сортировка меньшего диапазона в пределах большего

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

К счастью, Excel выдает предупреждение, если обнаруживает подобную попытку:

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

Если результат не соответствует желаемому, нажмите кнопку Отменить .

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

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

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

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

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

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

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

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

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

Чтобы включить в сортировку первую строку данных (так как она не является заголовком столбца), на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр , выберите команду Настраиваемая сортировка и снимите флажок Мои данные содержат заголовки .

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

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

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

Пошаговые действия:

В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
На вкладке Данные найти группу команд .

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

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

Пошаговые действия:

Выделить одну ячейку из сортируемого массива данных.

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

На вкладке Данные найти группу команд и на ней выбрать команду Сортировка .
Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец , Сортировка , Порядок ) необходимо выбрать:

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

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

Сортировка по форматированию

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

Пошаговый порядок действий:

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

Можно также воспользоваться командой «Копировать уровень» , заменив в поле «Порядок» прежнее значение на новое.

После выбора сортировки нажать кнопку ОК .

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

Видео по использованию сортировки Excel

Что можно сортировать?

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

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

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

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

Существует 2 основных типа сортировки — по возрастанию и по убыванию

Итак, перед тем как начать, необходимо открыть Excel и заполнить какой-то информацией. К примеру, можно заполнить 10 ячеек цифрами от 1 до 10. Теперь нужно выделить весь столбец (в данном случае — все 10 ячеек) и выбрать в панели меню пункты «Данные — Сортировка». Откроется новое окно, в котором надо указать, как отсортировать информацию, по возрастанию или по убыванию. Можно, например, выбрать пункт «по убыванию» и нажать кнопку «ОК». Теперь числа будут идти от 10 и до 1. Можно снова открыть окно сортировки и выбрать «по возрастанию» — числа будут идти от 1 до 10. Также эта процедура может выполняться одновременно по 3 колонкам. Хотя такую сортировку лучше выполнять .

Для примера можно создать таблицу, в которой будет храниться информация о товаре на складе. Таблица будет состоять из 3 колонок: наименование, цвет, количество. Товары нужно написать так, чтобы одной и той же категории было несколько. К примеру, ботинки мужские черные (3 модели), ботинки мужские красные (2 модели), туфли женские белые (4 модели) и т.д. Количество может быть любое.

Итак, чтобы включить автофильтр, необходимо выделить весь лист и в панели меню выбрать пункты «Данные — Фильтр — Автофильтр». В ячейках с названиями столбцов (наименование, количество и пр.) должен появиться небольшой значок, при нажатии на который откроется выпадающий список. Наличие такой стрелочки означает, что автофильтр включен правильно. В этом списке можно отсортировать данные по убыванию или по возрастанию, указать, чтобы в таблице отображались только первые 10 наименований (в данном примере эта опция не сработает) или чтобы показывало какой-то определенный товар (например, ботинки мужские). Также можно выбрать пункт «Условие» и указать, например, чтобы программа отобразила все товары, количество которых меньше или равно 10.

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

Хитрости сортировки

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

Для этого необходимо выделить всю таблицу, открыть окно сортировки и в поле «Порядок» указать пункт «Настраиваемый список». Откроется новое окно, где можно выбрать нужную последовательность месяцев в году. Если такого списка нет (например, название месяцев на английском), то его можно создать самому, выбрав опцию «Новый список».

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