Для создания удобных и комфортных условий пользователю при просмотре и анализе информации, размещенной в таблицах, программа MS Excel предоставляет достаточно широкие возможности сортировки данных. Сортировка в Excel записей большой базы данных...
Несколько облегчает пользователю поиск интересующей информации.
При сортировке все строки базы данных изменяют свое местоположение в соответствии с выбранным нами алгоритмом, оставаясь сами, конечно, неизменными.
Три главных алгоритма сортировки в 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
Исходная таблица содержит: ФИО сотрудника, его должность и стаж.
Требуется сделать упорядочивание данных согласно стажу работы - от меньшего к большему.
Для этого нужно выделить диапазон чисел, которые нужно упорядочить. В нашем случае это будет диапазон D3:D8.
Может быть выдано предупреждение о наличии данных рядом с указанным диапазоном. Среди предлагаемых действий нужно выбрать "Сортировать в пределах указанного выделения" и нажать на кнопку "Сортировка".
В результате данные будут упорядочены, и на первом месте будет отображаться сотрудник с наименьшим стажем работы.
Второй способ упорядочить числа по возрастанию в Excel
Первое действие будет таким же, как и в первом способе - нужно выделить диапазон чисел, которые требуется отсортировать.
Затем на панели инструментов в разделе "Главная" нажмите на кнопку "Сортировка и фильтр". Появится подменю, в котором нужно выбрать команду "Сортировка от минимального к максимальному".
Эта команда позволит вам осуществить упорядочивание чисел по возрастанию.
Сортировка в Excel — это встроенная функция анализа данных. С помощью нее можно выставить фамилии в алфавитном порядке, отсортировать средний балл абитуриентов по возрастанию или убыванию, задать порядок строк в зависимости от цвета или значка и т.д. Также с помощью этой функции можно быстро придать таблице удобный вид, что позволит пользователю быстрее находить необходимую информацию, анализировать ее и принимать решения.
Видео по использованию сортировки Excel
Что можно сортировать?
Excel умеет сортировать данные по тексту (в алфавитном порядке или наоборот), числам (по возрастанию или по убыванию), по дате и времени (от новых к старым, и наоборот). Сортировать можно как по одному столбцу, так и одновременно по нескольким. Например, сначала можно отсортировать всех клиентов в алфавитном порядке, а затем — по общей сумме их покупок. Кроме того, Эксель может сортировать по настраиваемым спискам или по формату (цвет ячейки, цвет текста и т.д.). Обычно сортировка применяется только по колонкам, но есть возможность применять эту функцию и к строкам.
Все указанные настройки этой опции сохраняются вместе с книгой Excel, что позволяет повторно сортировать информацию при открытии книги (если необходимо).
Сортировка данных в Excel
Сортировку в Экселе условно можно разделить на простую и сложную. Простой считается сортировка по возрастанию или по убыванию.
Существует 2 основных типа сортировки — по возрастанию и по убыванию
Итак, перед тем как начать, необходимо открыть Excel и заполнить какой-то информацией. К примеру, можно заполнить 10 ячеек цифрами от 1 до 10. Теперь нужно выделить весь столбец (в данном случае — все 10 ячеек) и выбрать в панели меню пункты «Данные — Сортировка». Откроется новое окно, в котором надо указать, как отсортировать информацию, по возрастанию или по убыванию. Можно, например, выбрать пункт «по убыванию» и нажать кнопку «ОК». Теперь числа будут идти от 10 и до 1. Можно снова открыть окно сортировки и выбрать «по возрастанию» — числа будут идти от 1 до 10. Также эта процедура может выполняться одновременно по 3 колонкам. Хотя такую сортировку лучше выполнять .
Для примера можно создать таблицу, в которой будет храниться информация о товаре на складе. Таблица будет состоять из 3 колонок: наименование, цвет, количество. Товары нужно написать так, чтобы одной и той же категории было несколько. К примеру, ботинки мужские черные (3 модели), ботинки мужские красные (2 модели), туфли женские белые (4 модели) и т.д. Количество может быть любое.
Итак, чтобы включить автофильтр, необходимо выделить весь лист и в панели меню выбрать пункты «Данные — Фильтр — Автофильтр». В ячейках с названиями столбцов (наименование, количество и пр.) должен появиться небольшой значок, при нажатии на который откроется выпадающий список. Наличие такой стрелочки означает, что автофильтр включен правильно. В этом списке можно отсортировать данные по убыванию или по возрастанию, указать, чтобы в таблице отображались только первые 10 наименований (в данном примере эта опция не сработает) или чтобы показывало какой-то определенный товар (например, ботинки мужские). Также можно выбрать пункт «Условие» и указать, например, чтобы программа отобразила все товары, количество которых меньше или равно 10.
Если стрелочка автофильтра окрашена в синий цвет, это означает, что данная колонка уже была отсортирована.
Хитрости сортировки
Допустим, что у пользователя есть таблица, в которой присутствует столбец с названиями месяцев года. И когда необходимо отсортировать его, к примеру, по возрастанию, получается примерно так: август, апрель, декабрь и т.д. А хотелось бы, чтобы сортировка происходила в привычной последовательности, т.е. январь, февраль, март и т.д. Сделать это можно с помощью особой настройки по пользовательскому листу.
Для этого необходимо выделить всю таблицу, открыть окно сортировки и в поле «Порядок» указать пункт «Настраиваемый список». Откроется новое окно, где можно выбрать нужную последовательность месяцев в году. Если такого списка нет (например, название месяцев на английском), то его можно создать самому, выбрав опцию «Новый список».
Отсортировать данные совсем не трудно. Зато в результате можно получить удобную таблицу или отчет для быстрого просмотра необходимой информации и принятия решения.
В этой статье я покажу Вам, как в Excel выполнить сортировку данных по нескольким столбцам, по заголовкам столбцов в алфавитном порядке и по значениям в любой строке. Вы также научитесь осуществлять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.
Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):
Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:
Сортировка по нескольким столбцам
Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере. Если Вы работаете в другой версии приложения, никаких затруднений возникнуть не должно, поскольку сортировка в Excel 2007 и Excel 2013 работает практически так же. Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…
Сортировать данные по нескольким столбцам в Excel оказалось совсем не сложно, правда? Однако, в диалоговом окне Сортировка (Sort) кроется значительно больше возможностей. Далее в этой статье я покажу, как сортировать по строке, а не по столбцу, и как упорядочить данные на листе в алфавитном порядке по заголовкам столбцов. Вы также научитесь выполнять сортировку данных нестандартными способами, когда сортировка в алфавитном порядке или по значению чисел не применима.
Сортировка данных в Excel по заголовкам строк и столбцов
Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.
Вот список фотокамер, предоставленный региональным представителем или скачанный из интернета. Список содержит разнообразные данные о функциях, характеристиках и ценах и выглядит примерно так:
Нам нужно отсортировать этот список фотокамер по наиболее важным для нас параметрам. Для примера первым делом выполним сортировку по названию модели:
В результате сортировки у Вас должно получиться что-то вроде этого:
В рассмотренном нами примере сортировка по заголовкам столбцов не имеет серьёзной практической ценности и сделана только для того, чтобы продемонстрировать Вам, как это работает. Таким же образом мы можем сделать сортировку нашего списка фотокамер по строке, в которой указаны размеры, разрешение, тип сенсора или по любому другому параметру, который сочтём более важным. Сделаем ещё одну сортировку, на этот раз по цене.
Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4 , в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:
Обратите внимание, что отсортированы оказались данные не только в выбранной строке. Целые столбцы меняются местами, но данные не перемешиваются. Другими словами, на снимке экрана выше представлен список фотокамер, расставленный в порядке от самых дешёвых до самых дорогих.
Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?
Сортировка в произвольном порядке (по настраиваемому списку)
Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.
Предположим, у нас есть список еженедельных дел по дому, и мы хотим упорядочить их по дню недели или по важности.
Готово! Теперь домашние дела упорядочены по дням недели:
Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).
Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.
Сортировка данных по собственному настраиваемому списку
В нашей таблице есть столбец Priority – в нём указаны приоритеты задач. Чтобы упорядочить с его помощью еженедельные задачи от более важных к менее важным, выполним следующие действия.
Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.
Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК .
Вот так выглядит наш список домашних дел, упорядоченных по важности:
Работа с таблицами во многих случаях требует анализа содержащихся в них данных. Excel предоставляет мощные средства для обработки информации, возможность построения статистических отчетов и выборки данных по условию. Но иногда достаточно просто произвести сортировку элементов, чтобы решить поставленную задачу.
Упорядочивание чисел
Сортировка чисел по возрастанию или убыванию - распространенная задача. Она помогает выстроить числовые параметры - количество товаров и клиентов, суммы продаж и доходов, и представить информацию в более наглядном виде. Сделаем в Excel сортировку этих данных.
Например, в таблице файла туристической компании хранится информация о стоимости проживания в отеле. Цена зависит от типа номера и предлагаемого питания. Турист заинтересован в поиске наиболее экономного размещения, поэтому нужно отсортировать данные по возрастанию цены.
Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».
Программа, обнаружив наличие связанных столбцов, уточняет, нужно ли выполнять сортировку в Excel только по выделенному полю, или же расширить диапазон.
Выбираем «Расширить», так как мы заинтересованы в упорядочивании полных записей таблицы, и нажимаем «Сортировка». Записи выстроены в порядке возрастания стоимости проживания.
Эта операция выполняется еще проще, если таблица отформатирована по стилю. В таком случае нужно вызвать контекстное меню столбца, щелкнув по выпадающему списку в заголовке графы. В меню находятся аналогичные команды, с помощью которых можно выполнить сортировку в Excel.
Для того чтобы числа были отсортированы верно, обратите внимание на корректность формата данных в столбце. Если значения внесены в таблицу как текст, а не число, выстраивание пройдет не в ожидаемом порядке. В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.
Упорядочивание текста
В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. Произведем эту команду в колонке уже известной нам таблицы цен на комнаты в отеле «Питание». Выделяем столбец кликом по его заголовку и открываем панель инструментов «Редактирование» вкладки «Главная». Теперь в выпадающем меню вместо команд сортировки по возрастанию и убыванию расположены команды сортировки от А до Я и наоборот.
Выполняем построение ячеек от А до Я, опять же указывая расширение диапазона операции.
При упорядочивании строковых данных убедитесь в том, что значения не содержат начальных пробелов, так как в таком случае желаемый порядок ячеек будет нарушен. Эта проблема случается, если информация выгружается в файл из другого источника: базы данных, стороннего приложения или другого файла.
Для форматированных таблиц сортировку в Excel выполнить еще проще. Зайдя в контекстное меню заголовка колонки, вы также увидите команды построения ячеек в порядке от А до Я и наоборот.
Упорядочивание дат
Все указанные выше операции, применимые для чисел и строковой информации, используются и для выстраивания дат и времени. Отличие состоит только в наименовании команды, для такого типа данных она называется «Сортировка от старых к новым» и наоборот. Ниже показан пример.
Для дат действует то же правило, что и для чисел: чтобы упорядочивание прошло корректно, убедитесь в том, что сортируемая информация представлена именно в формате даты, а не в текстовом.
Настраиваемые списки
Но не всегда есть возможность выстроить необходимый список штатными средствами, бывает необходимость выполнить сортировку в Excel по полям, к которым не подходит ни один из описанных выше методов. К примеру, в списке дел на неделю мы хотим выстроить задачи по дням недели.
Чтобы выполнить такую сортировку, программа предоставляет возможность использования настраиваемых списков. В MS Excel есть ряд предустановленных списков, одним из которых и является перечень дней недели. Чтобы их посмотреть, зайдите на вкладку «Данные» и найдите панель инструментов сортировки и фильтрации Excel. По кнопке «Сортировка» открывается окно выполнения операции по нескольким столбцам.
В поле «Порядок» выбираем элемент «Настраиваемый список». Из перечня выбираем дни недели и нажимаем ОК.
Упорядочивание по цвету и значкам
Еще одной удобной опцией выстраивания элементов ячейки по порядку является возможность их сортировки по цвету. При этом может использоваться цвет как заливки, так и шрифта текста. В таблице дел обозначим рабочие задачи оранжевым цветом, семейные дела - голубым, праздники - бежево-желтым. И выстроим их по типу.
Для этого заходим в настраиваемую сортировку. Кроме описанного выше способа, можно воспользоваться меню панели «Редактирование» вкладки «Главная». Выбираем из списка команд «Сортировка и фильтр» элемент «Настраиваемая сортировка».
Выбираем поле «Задача», тип «Цвет ячейки» и устанавливаем оранжевый цвет сверху. Чтобы домашние дела находились внизу списка, добавляем еще один уровень и для тех же параметров устанавливаем голубой цвет снизу.
Этот способ позволяет упорядочивать элементы таблицы и по значкам. Например, мы можем пометить отдельные задачи восклицательным знаком как важные и выводить их в начале списка.
Выше мы описали, как в Excel сделать сортировку данных разных типов, используя цвет ячеек и шрифта текста и пометки в строках. Надеемся, эта информация поможет вам в работе с программой.
