Программа Microsoft Excel: сортировка и фильтрация данных. Сортировка данных в Excel Сортировка списков и диапазонов в ms excel

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

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

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

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

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

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

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

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

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

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

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

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

Примечания:

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

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

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

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

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

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

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

Примечания:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для поиска нужной информации применяют специальные фильтры

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

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

Фильтр по возрастанию и убыванию

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

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

Итак, первоначально необходимо выделить таблицу, подлежащую проведению анализа. Далее следует перейти на новый лист, кликнуть правой кнопкой мыши, а затем кликнуть по строке «Специальная вставка». Перед пользователем на экране появится окно с параметрами, среди которых нужно выбрать параметр «Значение», после чего нажать «Ок».

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

В открывшемся окне параметров имеется строка «Мои данные содержат заголовки». Возле нее расположено маленькое окошко, в котором следует поставить галочку. Остаётся выставить в ниже открывшихся окнах столбец, который подлежит анализу, а также вариант желаемой сортировки: по возрастанию или убыванию. Далее соглашаемся с выставленными параметрами , после чего таблица продемонстрирует мгновенно желаемый результат, избавляя от многочасовой изнурительной работы.

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

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

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

Установка фильтра по дате и формату

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

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

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

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

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

Если что-то пошло не так, нужно знать, как отменить сортировку в Excel, воспользовавшись одним из двух несложных действий. Можно просто нажимать несколько раз две одновременно зажатые клавиши (Ctrl + Z) до тех пор, пока таблица не примет первоначальный вид . Если же действий было совершено много, проще отменить сортировку вторым способом, закрыв таблицу без сохранения, после чего вновь её открыть и приступить заново к работе.

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

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

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



Какие же возможности предоставляет сортировка:

• Сортировка по возрастанию и убыванию.

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

• По цвету ячейки или шрифта

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

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

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

Алгоритм команды: Данные/Сортировка/Добавить уровень.

• По вашему произвольному порядку

Пользовательский список можно импортировать через параметры Office. Для этого создайте список, выделите диапазон, потом Кнопка Office/Параметры Excel/Изменить списки/Импорт. Если же список невелик, то можно его вручную забить при сортировке Данные/Сортировка/(при этом сортировка поля должна быть по значению, а не по цвету или шрифту)/Порядок/Настраиваемый список.