Программа Microsoft Excel: сортировка и фильтрация данных. Как сделать в Excel сортировку данных Как осуществляется сортировка данных в excel
Сортировка данных, находящихся в области строк и столбцов сводной таблицы, по умолчанию выполняется в порядке возрастания (рис. 1а) либо с применением пользовательских списков сортировки. Далеко не всегда это устраивает пользователя. Например, когда хочется отобразить заказчиков с наибольшим доходом в верхней части списка (рис. 1б). Если в сводной таблице применяется сортировка по возрастанию (убыванию), следует создать правило, контролирующее порядок сортировки по полю. Причем это правило (в отношении этого поля) будет применяться даже после добавления новых полей в сводную таблицу (рис. 1в).
Рис. 1. Сортировка по полю Заказчик : (а) по умолчанию – от А до Я; (б) в порядке уменьшения дохода; (в) порядок сортировки по полю Заказчик не изменился при добавлении поля Сектор
Скачать заметку в формате или , примеры в формате
Сортировка заказчиков в порядке убывания дохода
Чтобы отсортировать строки сводной таблицы в порядке убывания дохода, выберите любую ячейку столбца Сумма по полю Доход , например, Е4 (но не заголовок), и щелкните на значке ЯА , находящемся на вкладке Данные (рис. 2). Подобная сортировка напоминает стандартную, но это лишь внешнее сходство. При выполнении сортировки сводной таблицы Excel создает правило, которое будет работать и после внесения дополнительных изменений в сводную таблицу.
На примере сводной таблицы, находящейся в столбцах G:I (рис. 1в), видно, что произойдет после добавления нового внешнего поля строки Сектор . Сводная таблица продолжает сортировать данные в порядке убывания дохода внутри каждого сектора. Например, в секторе Производство на первом месте находится компания General Motors с доходом 750 163 доллара. За ней следует компания Ford с доходом 622 794 доллара. Если даже удалить поле Заказчик из сводной таблицы, выполнить дополнительные настройки и вернуть это поле обратно, но уже в область столбцов, Excel запомнит сортировку заказчиков в порядке уменьшения дохода.
Чтобы в сводной таблице, находящейся в столбцах G:I (рис. 1в), секторы также были отсортированы в порядке убывания дохода, можно пойти одним из трех способов:
- Выделите ячейку G4, щелкните правой кнопкой мыши и выберите Свернуть всё поле , чтобы скрыть все элементы, которые относятся к заказчику. После того как на экране будут отображаться лишь одни секторы, выделите ячейку I4 и щелкните на значке ЯА на вкладке Данные для выполнения сортировки по убыванию. Таким образом, будет создано правило сортировки для поля Сектор . Повторно выделите ячейку G4, щелкните правой кнопкой мыши и выберите Развернуть всё поле.
- Временно удалите поле Заказчик из сводной таблицы, отсортируйте таблицу по убыванию дохода (методом, который был описан для рис. 2), а потом вновь верните поле Заказчик .
- Воспользуйтесь возможностями команды Дополнительные параметры сортировки (я пользуюсь именно этим методом). Чтобы вызвать команду: (а) выделите ячейку G4, щелкните правой кнопкой мыши и выберите Сортировка → Дополнительные параметры сортировки (рис. 3) или (б) кликните на значке треугольника в поле Сектор , а затем выберите пункт Дополнительные параметры сортировки (рис. 4). В обоих случаях откроется окно Сортировка (рис. 5). Установите переключатель в положение по убыванию и выберите строку Сумма по полю Доход .
Рис. 3. Вызов команды Дополнительные параметры сортировки правой кнопкой мыши
Рис. 4. Вызов команды Дополнительные параметры сортировки с помощью меню Сортировка и фильтры поля Сектор
Рис. 5. Настройка параметров в окне Сектор
В левом нижнем углу диалогового окна Сортировка находится кнопка Дополнительно… После щелчка на этой кнопке на экране появится диалоговое окно . В этом окне можно: (а) задать пользовательский список, который будет использоваться для сортировки по первому ключу (подробнее см. ниже); (б) вместо столбца Общий итог в качестве базового столбца сортировки выбрать другой столбец.
Например, для сводной таблицы, изображенной на рис. 6 можно задать сортировку не по общему доходу, а по доходу от продажи одного вида товаров, например, Устройств (обратите внимание, что заказчики отсортированы не по столбцу F, а по столбцу С).
Рис. 6. Дополнительные параметры позволяют отсортировать заказчиков не по общему доходу, а по доходу от продаж товара Устройство
Чтобы выполнить такую сортировку:
- Раскройте список Заказчик, находящийся в ячейке А4.
- Выберите параметр Дополнительные параметры сортировки.
- В диалоговом окне Сортировка (Заказчик) щелкните на кнопке Дополнительно …
- В диалоговом окне Дополнительные параметры сортировки (Заказчик) выберите раздел Порядок сортировки и установите переключатель Значения в выделенном столбце .
- Щелкните в поле ссылки, а затем выберите ячейку С5. Обратите внимание на то, что нужно щелкнуть в одной из ячеек значений Устройство , поскольку на заголовке Устройство в ячейке С4 щелкнуть невозможно.
- Чтобы завершить установку параметров дважды кликните ОK.
Не пугайтесь, описание этого пошагового алгоритма приведено, скорее, в обучающих целях. Начиная с Excel 2013 сортировка данных сводной таблицы существенно упростилась. Теперь кнопки ЯА и АЯ на вкладке Данные используют интеллектуальные алгоритмы сортировки. При попытке выполнить сортировку с помощью этих кнопок программа попытается предугадать намерения пользователя, основываясь на том, какая ячейка была выделена перед нажатием кнопки сортировки (рис. 7):
- А1, С1, D1, Е1, F1, F2, А30, F30 – не доступны
- А2:А29 – расположит по алфавиту имена заказчиков в столбце А
- В1, В2, С2, D2, E2 – расположит по алфавиту названия товаров в строке 2
- В30, С30, D30, E30 – расположит по убыванию (возрастанию) суммы дохода в строке 30
- по возрастанию (убыванию) продаж В3:В29 – модулей, С3:С29 – устройств, D3:D29 – деталей, Е3:Е29 – препаратов, F3:F29 – итого.
Сортировка вручную
Обратите внимание на то, что в диалоговом окне Сортировка (см. рис. 5) можно вручную определить правила сортировки данных. Но сортировка сводной таблицы вручную также выполняется другим, весьма необычным способом. В отчете сводной таблицы на рис. 8а показана последовательность категорий товаров, отсортированных в алфавитном порядке: Деталь, Модуль, Препарат и Устройство . Обратите внимание на то, что объем проданных товаров, относящихся к категории Деталь , не наибольший. И вряд ли стоит эту категорию отображать первой. Установите указатель мыши в ячейке Е4 и введите слово Деталь . Стоит лишь нажать клавишу Enter , как Excel определит, что вы решили переместить колонку Деталь в последний столбец таблицы. Все числовые значения, относящиеся к этой категории товаров, переместятся из столбца В в столбец Е. Значения, относящиеся к другим категориям товаров, сместятся влево. Подобное поведение выглядит нелогичным и присуще лишь сводным таблицам Excel. Обычный набор данных Excel переупорядочить таким образом не удастся. На рис. 8б показана сводная таблица после перемещения заголовка нового столбца в ячейку Е4.
Рис. 8. Сортировка вручную: (а) категории товаров отсортированы по алфавиту, (б) категория Деталь размещена последней
Любители мыши могут просто перетаскивать заголовки требуемых колонок (или отдельные строки). Щелкните в области заголовка столбца и удерживайте указатель мыши над границей диапазона выделенных ячеек до тех пор, пока он не приобретет вид четырехнаправленной стрелки. Начинайте перетаскивать ячейку в выбранное место; появится указатель в виде жирной линии и засечками. Как только вы отпустите кнопку мыши, числовые значения тут же переместятся в новую колонку. Учтите, что при использовании ручной сортировки товары, добавляемые в источник данных, добавляются в конец списка. Это связано с тем, что программа Excel не знает, куда именно нужно добавить новый регион.
Сортировка данных согласно пользовательским спискам
Еще одно решение проблемы, связанной с настройкой последовательности представления полей, заключается в создании пользовательских списков. С помощью подобного списка будут сортироваться сводные таблицы, создаваемые в дальнейшем. По умолчанию Excelсодержит четыре пользовательских списка: для дней недели, месяцев года и сокращенных названий дней недели и месяцев года. Программа сортирует названия дней недели в естественной последовательности, начиная с Пн и кончая Вс (а не по алфавиту).
Чтобы создать собственный список сортировки, выполните следующие действия:
- В свободной от данных области рабочего листа введите названия категорий товаров в последовательности, которая соответствует создаваемому пользовательскому списку. В каждой ячейке вводите по одному названию, а названия располагайте в одном столбце (рис. 9).
- Выделите полученный список названий категорий товаров (ячейки А10:А13).
- Выберите вкладку ленты Файл и в нижней части панели навигации, отображенной в окне слева, щелкните на кнопке Параметры для открытия диалогового окна Параметры Excel.
- Выберите категорию Дополнительно , перейдите в раздел Общие и щелкните на кнопке Изменить списки .
- В диалоговом окне Списки адрес диапазона, содержащего предварительно выделенный список названий, отображается в поле Импорт списка из ячеек (рис. 10). Щелкните на кнопке Импорт , чтобы сформировать новый список категорий товаров на основе указанных данных. Новый список добавляется в нижнюю часть области Списки .
- Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Списки . Щелкните еще раз на кнопке ОК для закрытия диалогового окна Параметры Excel .
Рис. 10. Окно Списки
Только что созданный список сохраняется в настройках программы и становится доступным в следующих сеансах Excel. Теперь во всех сводных таблицах, создаваемых в будущем, будет выполняться автоматическая сортировка по полю товара в соответствии с порядком, задаваемым в списке. На рис. 11 показана новая сводная таблица (которая была создана на основе уже после добавления пользовательского списка товаров), отсортированная в соответствии с созданным списком.
Чтобы отсортировать ранее созданные сводные таблицы в соответствии с новым пользовательским списком, выполните следующие действия:
- Раскройте список поля Товар и выберите параметр Дополнительные параметры сортировки .
- В диалоговом окне Сортировка (Товар) выберите кнопку по возрастанию (от А до Я) по полю , а в раскрывающемся списке выберите Товар .
- Щелкните на кнопке Дополнительно …
- В диалоговом окне Дополнительные параметры сортировки (Товар) отмените установку флажка Автосортировка .
- Раскройте список Сортировка по первому ключу и выберите список, включающий названия категорий товара (рис. 12).
- Дважды щелкните на кнопке ОК.
Заметка написана на основе книги Билл Джелен, Майкл Александер. . Глава 4.
Сортировка данных в Excel – это очень полезный инструмент, который позволяет улучшать восприятие информации, особенно при больших объемах. В данном уроке мы научимся применять сортировку, узнаем основные команды, а также познакомимся с типами сортировки в Excel.
При добавлении данных в Excel очень важно грамотно организовать информацию на рабочем листе. Одним из инструментов, который позволяет сделать это, является сортировка. С помощью сортировки Вы можете сформировать список контактной информации по фамилии, расположить содержимое таблицы в алфавитном порядке или же в порядке убывания.
При сортировке данных в Excel в первую очередь необходимо решить, как применять сортировку: ко всему листу (таблице) или только к определенному диапазону ячеек.
Как сделать сортировку листа (таблицы, списка) в Excel
В следующем примере мы отсортируем форму заказа футболок по Фамилиям (Столбец С) и расположим их в алфавитном порядке.
При сортировке таблицы или списка в Excel необходимо, чтобы они были отделены от посторонних данных на листе как минимум одной строкой или столбцом. В противном случае в сортировке будут участвовать посторонние данные.
Как сделать сортировку диапазона в Excel
В следующем примере мы выберем отдельную небольшую таблицу на листе Excel, чтобы отсортировать количество футболок, заказанных в определенные дни.
Если сортировка в Excel выполняется неправильно, то в первую очередь проверьте верно ли введены значения. Даже небольшая опечатка может привести к проблемам при сортировке больших таблиц. В следующем примере мы забыли поставить дефис в ячейке A18, что привело к неточной сортировке.
Если в таблице нужно найти определенное значение, то удобнее всего это делать в отсортированных данных. Далее мы рассмотрим, как отсортировать таблицу в Excel по убыванию или возрастанию, цвету текста или ячеек и прочее.
Для сортировки таблиц в Excel используется пункт меню «Сортировка и фильтр» на вкладке главная. Самая простая сортировка данный в таблице Excel, это сортировка по возрастанию или по убыванию, которая может быть применена как к текстовым значениям, так и к числовым.
Для правильной сортировки все сортируемые ячейки должны иметь одинаковый формат, например, все ячейки с цифрами должны находится в числовом формате. Также перед значениями не должно быть лишних пробелов и в сортируемом диапазоне не должно быть скрытых строк и столбцов.
Сортировка значений в таблице Эксель начинается с выбора сортируемого столбца. Достаточно даже установить курсор в первую ячейку столбца. В нашем примере выделим первую ячейку второго столбца, выберем в меню «Сортировка и фильтр» , и укажем «Сортировка по возрастанию» .
В результате сортировки данных в таблице значения во второй таблице отсортированы по возрастанию, но первая ячейка так и осталась на своем месте. Все это потому, что по умолчанию первая строка в таблице считается заголовками, т.е. названиями столбцов, и не сортируется.
Диапазон сортируемых данных расширяется автоматически, т.е. Эксель автоматически выделяет всю таблицу и сортирует данные по строкам в соответствии с выбранным столбцом. Если выделить все ячейки столбца, которые необходимо отсортировать, и выбрать сортировку по возрастанию или убыванию, Эксель выдаст сообщение с предложением выбрать вариант сортировки. Будет доступен вариант «Автоматически расширить выделенный диапазон» , при котором будет выделена вся таблица, и «Сортировать в пределах указанного выделения» , что позволит отсортировать только выделенный столбец не затрагивая при этом данные в соседних столбцах. В этом случае при сортировке опять же не будет учитываться первая ячейка.
Чтобы сортировка таблиц в Excel производилась полностью по всем выделенным ячейкам, следует в меню «Сортировка и фильтр» выбирать пункт «Настраиваемая сортировка…» .
При этом мы сразу увидим, какую часть таблицы Эксель выделяет для дальнейшей сортировки.
Чтобы выделялась вся область данных, следует в появившемся окошке «Сортировка» убрать галочку с пункта «Мои данные содержат заголовки» .
Теперь в этом окошке можно настроить сортировку наших данных. В первой графе «Столбец» в строке «Сортировать по» выбираем столбец, данные в котором необходимо отсортировать. Во второй графе «Сортировка» необходимо выбрать условие, по которому будет производится сортировка. Доступны варианты «Значения» , «Цвет ячейки» , «Цвет шрифта» и «Значок ячейки» . В нашем случае мы будем сортировать значения. Ну и в последней графе «Порядок» можно выбрать порядок сортировки значений «По возрастанию» , «По убыванию» и «Настраиваемый список» . Выберем по возрастанию. В результате значения среднего столбца отсортированы.
Также можно настраивать и сортировку отдельного столбца. Кроме того, в Экселе можно настроить несколько уровней сортировки. Например, отсортируем первый столбец по возрастанию, а затем результат сортировки второго столбца также отсортируем по возрастанию. А последний третий столбец отсортируем сначала по цвету ячейки, а затем по цвету шрифта. Для добавления нового уровня необходимо в окошке «Сортировка» нажать кнопку «Добавить уровень» , при этом порядок следования уровней имеет значение.
Сортировка в Эксель – это встроенная функция, с помощью которой пользователь сможет расположить данные в столбцах на листе в удобном порядке для их последующего анализа.
Вы сможете отсортировать информацию в алфавитном порядке, по возрастанию или убыванию значений, по дате или по значкам, по цвету текста или ячейки. Именно об этом и пойдет речь в данной статье.
Чисел
Здесь все достаточно просто. Для примера возьмем следующую таблицу. Сделаем в ней сортировку данных по столбцу С . Для этого выделяем его и на вкладке «Главная» кликаем на кнопочку «Сортировка и фильтр» . В следующем меню выберите или «… от минимального к максимальному» , или «… от максимального к минимальному» . Выберем второй вариант.
Теперь у нас данные в С размещены в порядке убывания.
У меня столбец С расположен между двумя другими, которые заполнены данными. В этом случае, Excel считает, что выделенный столбец – это часть таблицы (и считает правильно). В результате появилось следующее сообщение. Поскольку мне нужно сделать сортировку конкретно для Класса, выделяю маркером пункт «… в пределах указанного выделения» и нажимаю «Сортировка» .
По алфавиту
Она делается по тому же принципу, как было описано выше. Выделяем нужный диапазон, и нажимаем кнопочку «Сортировка и фильтр» . В выпадающем меню пункты изменились. Выберите или от «А до Я» , или от «Я до А» .
Список имен в примере отсортирован по алфавиту.
По дате
Чтобы отсортировать даты в Эксель, сначала обратите внимание, какой формат установлен для тех ячеек, в которых они записаны. Выделите их и на вкладке «Главная» посмотрите на группу «Число» . Лучше всего подойдет или формат «Дата» , краткий или длинный, или «(все форматы)» – дата может быть записана различными способами: ДД.ММ.ГГГГ, ДД.МММ, МММ.ГГ.
Этот момент очень важен, так как в противном случае, даты могут быть отсортированы просто по возрастанию первых двух чисел, или по месяцам в алфавитном порядке.
После этого выделяем нужный диапазон ячеек и жмем на кнопочку «Сортировка и фильтр» . В меню можно выбрать или «от старых к новым» , или «от новых к старым» .
По цвету ячейки или текста
Этот способ можно использовать, когда в таблице Excel текст в ячейках или сами ячейки закрашены в различный цвет. Для примера возьмем столбец из чисел, закрашенных разными цветами. Отсортируем его, чтобы сначала шли числа, закрашенные в красный, затем зеленый и черный цвет.
Выделяем весь диапазон, кликаем на кнопочку «Сортировка и фильтр» и выбираем из меню «Настраиваемая…» .
В следующем окне, уберите галочку с поля , если Вы выделили их без верхней строки, которая является шапкой таблицы. Затем выбираем столбец, по которому будем сортировать, в примере это «I» . В разделе «Сортировка» из выпадающего списка выбираем «Цвет шрифта» . В разделе порядок выбираем «красный цвет» – «Сверху» . Это мы отсортировали числа красного цвета.
Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .
Наш столбец отсортирован следующим образом.
Как видите, числа идут не по порядку. Давайте отсортируем числа в порядке возрастания. Выделяем столбец, нажимаем «Сортировка и фильтр» – «Настраиваемая …» . В открывшемся окне нажмите на кнопку «Добавить уровень» . Столбец остается «I» , в следующем поле выбираем по «Значению» , порядок «По возрастанию» . Нажмите «ОК» .
Теперь наш столбец отсортирован и по цвету текста и в порядке возрастания данных.
Аналогичным образом сортируются данные и по цвету ячейки, только в разделе «Сортировка» выбирайте из списка «Цвет ячейки» .
Таблицы
Если у Вас есть таблица, в которой нужно выполнить сортировку сразу по нескольким столбцам, делаем следующее. Выделяем весь диапазон ячеек таблицы вместе с шапкой. Кликаем по кнопочке «Сортировка и фильтр» и выбираем «Настраиваемая …» .
Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.
В окне сортировки ставим галочку в поле «Мои данные содержат заголовки» . В разделе «Столбец» выбираем из списка «Класс» , сортировка по «Значению» , а порядок «По возрастанию» .
Чтобы сделать все тоже самое по среднему балу, нажмите на кнопочку «Добавить уровень» . В разделе «Столбец» выбираем «Средн.бал» . Нажмите «ОК» .
Данные в таблице отсортированы.
Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.
Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …» .
В открывшемся окне уже есть два уровня, которые мы сделали раньше. Эти уровни имеют приоритет – у первого самый большой, у второго меньше и так далее. То есть, если мы хотим, чтобы сначала выполнилась сортировка данных в таблице девочки/мальчики, затем по классу, а затем по среднему балу – нужно в таком порядке и расставить уровни.
Нажимаем на кнопку «Добавить уровень» . В разделе «Столбец» выбираем «Имя» , сортировка – «Цвет ячейки» , порядок – «розовый» , «Сверху» .
Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК» .
Таблица с отсортированными данными выглядит следующим образом.