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

Упорядочивание текста

В Excel сортировка столбцов, содержащих текст, выполняется так же, как и операция с числами. Произведем эту команду в колонке уже известной нам таблицы цен на комнаты в отеле «Питание». Выделяем столбец кликом по его заголовку и открываем панель инструментов «Редактирование» вкладки «Главная». Теперь в выпадающем меню вместо команд сортировки по возрастанию и убыванию расположены команды сортировки от А до Я и наоборот.

Выполняем построение ячеек от А до Я, опять же указывая расширение диапазона операции.

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

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

Сортировка в произвольном порядке (по настраиваемому списку)

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

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

Начинаем с того, что выделяем данные, которые нужно сортировать, и открываем диалоговое окно Сортировка (Sort), точно так же, как в предыдущих примерах – Данные > Сортировка (Data > Sort).
В поле Сортировать по (Sort by) выбираем столбец, по которому нужно выполнить сортировку. Мы хотим упорядочить наши задачи по дням недели, то есть нас интересует столбец Day. Затем в выпадающем списке под заголовком Порядок (Order) выбираем вариант Настраиваемый список (Custom list), как показано на снимке экрана ниже:
В диалоговом окне Списки (Custom Lists) в одноимённом поле выбираем нужный список. В нашем столбце Day указаны сокращённые наименования дней недели – кликаем по соответствующему варианту списка и жмём ОК.

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

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

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

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

Повторите шаги 1 и 2 из предыдущего примера. Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК.

Вот так выглядит наш список домашних дел, упорядоченных по важности:

Подсказка: Для создания длинных настраиваемых списков удобнее и быстрее импортировать их из существующего диапазона. Об этом подробно рассказано в статье .

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

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

На сегодня всё

Спасибо за внимание!

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

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

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

Частые вопросы о сортировке данных

Диапазон не сортируется по цвету с помощью фильтра

Такое бывает, если ваша версия Excel не поддерживает сортировку по цветам при количестве строк в диапазоне более 10000.

Не работает сортировка по дате

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

Как сортировать диапазон “наоборот”

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

Динамическая сортировка таблицы в Excel

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

Сортировка простых чисел. Допустим, у нас есть набор простых чисел, которые требуется упорядочить по возрастанию.
Применение формулы. Перейдите к соседней ячейке и введите следующую формулу: =МИН(А:A;СТРОКА(А1)). В данной формуле диапазон указывается в виде всего столбца, а коэффициент СТРОКА ссылается на первую ячейку.
Обновление данных. В случае изменения значений в исходном диапазоне сортировка автоматически обновится.

Если необходимо сделать сортировку в обратном порядке, поставьте в формуле МАКС.

Для динамической сортировки текстовых значений используются формулы массива:

Подготовка исходных данных. Предположим, имеется список фруктов, расположенных в случайном порядке.
Назовем столбец с фруктами. Выделите его и присвойте ему имя, например, «Фрукты» в поле имен рядом со строкой формул.
В соседней ячейке пишем: =ИНДЕКС. Так как перед нами формула массива, нажимаем сочетание «Ctrl + Shift + Enter». Размножаем её на весь столбец.
Если в исходную колонку будут добавляться строки, то вводим чуть модифицированную формулу: =ЕСЛИОШ. Добавим в диапазон «Фрукты» еще одно значение «помело» и проверим.
Впоследствии, если будут добавляться новые строки в столбец «Фрукты», динамическая сортировка автоматически применится к данным.

В зависимости от возрастания и убывания

Почему не сортируется список по алфавиту?

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

Например, список не сортируется или сортируется некорректно.

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

Формат должен быть одинаковым для всех его ячеек — Общий, Числовой или Текстовый.

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

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

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

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

Упорядочивание табличных данных

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

Выделить всю таблицу;
Кликнуть по кнопочке «АЯ» во вкладыше «Основная»;
Если необходимо поначалу создать сортировку в первом столбце, то следует указать «Поначалу по: столбцам 1»;

В типе указать «Текст» и поставить кружочек около варианта «по возрастанию»;
Чтоб упорядочить 2-ой столбец также необходимо выделить таблицу, перейти в опции «Сортировка» и указать «Столбцам 2», в отделе «Тип» значение обменяется на «Число». Поставить, в котором порядке отсортировать табличные данные, к примеру, «По убыванию». Сохранить опции.

Выполнение настраиваемой сортировки

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

Это легко исправить. Например, вы можете сделать так, что строки с одинаковыми именами будут распределяться на основе определённых параметров. Допустим, есть вариант ориентироваться по дате.

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

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

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

Реализация настройки

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

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

Обратите внимание на поле «Сортировка». Тут вы должны выбрать то, по какому типу содержимого реализуется операция

Вам предлагают четыре основных варианта: Значения. Цвет ячейки. Цвет шрифта. Значок ячейки.

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

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

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

Дополнительные возможности

То, что было описано выше – лишь часть функционала. На этом вы можете не останавливаться, так как настраиваемая сортировка предлагает дополнительные опции. Например, в окне настройки вы можете реализовать разбор данных не по столбцам, а по строкам. Как это сделать? Всё элементарно:

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

Теперь останется лишь по аналогии вводить данные и выполнять сортировку. Но в данном случае местами будут меняться столбцы.

Надстройка для сортировки данных в Excel

Создание или удаление настраиваемого списка для сортировки и подстановки данных

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

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

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

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

Пн, Вт, Ср, Чт, Пт, Сб, Вс

Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье

янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек

Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь

Примечание:

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

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

Высокое, Среднее, Низкое

Большое, Среднее, Малое

Север, Юг, Восток, Запад

Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель

Настраиваемый список может соответствовать диапазону ячеек, или его можно ввести в диалоговом окне Списки

Примечание:

Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.

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

Введение значений напрямую

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

В Excel 2010

и более поздних версиях выберите пунктыФайл >Параметры >Дополнительно >Общие >Изменить списки .

В Excel 2007

нажмитекнопку Microsoft Office и выберите пунктыПараметры Excel >Популярные >Основные параметры работы с Excel >Изменить списки .

Выберите в поле Списки

пунктНОВЫЙ СПИСОК и введите данные в полеЭлементы списка , начиная с первого элемента.

После ввода каждого элемента нажимайте клавишу ВВОД.

Завершив создание списка, нажмите кнопку Добавить

На панели Списки

появятся введенные вами элементы.

Нажмите два раза кнопку ОК

Создание настраиваемого списка на основе диапазона ячеек

Выполните указанные ниже действия.

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

Убедитесь, что ссылка на выделенные значения отображается в окне Списки

в полеИмпорт списка из ячеек , и нажмите кнопкуИмпорт .

На панели Списки

появятся выбранные вами элементы.

«Параметры» > «Дополнительно» > «Общие» > «Изменить списки». Если вы используете Excel 2007, нажмите кнопку Office и выберите «Параметры Excel» > «Популярные» > «Изменить списки».» /> Два раза нажмите кнопку ОК

Примечание:

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

Выполните указанные ниже действия.

По приведенным выше инструкциям откройте диалоговое окно «Списки».

Выделите список, который нужно удалить, в поле Списки

и нажмите кнопкуУдалить .

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

Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки

впараметрах Excel : его можно выбрать только в столбцеПорядок диалогового окнаСортировка . Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для командыЗаполнить .

При необходимости можно добавить такой список в реестр компьютера или сервера, чтобы он был доступен в Параметрах Excel

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

Надстройка для сортировки данных в Excel

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

Чтобы в Microsoft Exel упорядочить по алфавиту, необходимо выделить нужные вам ячейки и на панели инструментов нажать Сортировка и фильтр. И затем выбираете как вам нужно сортировать текст от А до Я или наоборот от Я до А.

Чтобы работать с определенной областью ячеек и данных в программе Microsoft Excel, для начала нужно ее выделить мышью, чтобы область затемнилась. Теперь на панели инструментов необходимо выбрать А Я сортировка и фильтры и далее выбирать конкретные условия сортировки.

Списки в таблице Эксель Exel можно упорядочить по алфавиту от А до Я и наоборот: от Я до А. Для начала кликните по заголовку стобца — так он весь разом выделится. Затем нажимаем на кнопку Сортировка и фильтр вверху. Выбираем нужную сортировку. Аналогично поступаем, если столбцов несколько.

Если нужно просто упорядочить данные по алфавиту, то самый простой способ выбрать Фильтр Сортировка от А до Я, при этом данные сами упорядочатся.

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

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

Для того что бы упорядочить по алфавиту список в Excel, необходимо выделить нужный столбец и нажать кнопку АЯ сортировка и фильтр, которая находится на панели управления, вкладка Главная, как показано на картинке и выбрать необходимую сортировку — от А до Я или наоборот от Я до А

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

Для того чтобы упорядочить данные в Эксель по алфавиту необходимо:

1) выделить нужный столбец,

2) нажать кнопку Сортировка и фильтр (обычно находится справа, имеет вид воронки с буквам А Я),

3) в сплывшем меню выбираем Сортировка от А до Я.

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

Сортировка по имени в таблице Exel от Microsoft довольно простая процедура.

Выделяем желаемый столбец. Жмем на панели инструментов вверху справа кнопку Сортировка и фильтр. Кликаем по выпавшей строке меню Сортировка от А до Я. вот и вся задача.

Упорядочить по алфавиту в Excel
очень просто, нужно только воспользоваться кнопкой Сортировка по возрастанию. Она выглядит как изображение двух букв А и Я со стрелочкой сбоку.

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

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

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

Чисел

Здесь все достаточно просто. Для примера возьмем следующую таблицу. Сделаем в ней сортировку данных по столбцу С
. Для этого выделяем его и на вкладке «Главная»
кликаем на кнопочку «Сортировка и фильтр»
. В следующем меню выберите или «… от минимального к максимальному»
, или «… от максимального к минимальному»
. Выберем второй вариант.

Теперь у нас данные в С
размещены в порядке убывания.

У меня столбец С
расположен между двумя другими, которые заполнены данными. В этом случае, Excel считает, что выделенный столбец – это часть таблицы (и считает правильно). В результате появилось следующее сообщение. Поскольку мне нужно сделать сортировку конкретно для Класса, выделяю маркером пункт «… в пределах указанного выделения»
и нажимаю «Сортировка»
.

Проверь себя

Задание 1.Какова логика работы функции =ЕСЛИ(А1+В1>C1; A1+B1; C1)?

Если А1+В1 больше С1, то в ячейку будет записано А1+В1, иначе — С1
Если А1+В1 больше С1, то в ячейку будет записано С1, иначе — А1+В1
А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то в ячейке будет и второе значение — С1
А1+В1 точно будет записано в ячейку, но если А1+В1 больше С1, то к этому значению будет еще и прибавлено С1

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

СЧЁТ
СЧЁТЕСЛИ
ОСТАТ
СУММ

Задание 3.Для чего в Excel используется фильтрация?

Для вывода информации в определенном порядке
Для изменения большого объема информации
Для удаления информации.
Для вывода только определенной информации.

Задание 4.Выберите все слова, которые выдаст навигатор Word при поиске слова “часть” с указанием настройки “Только слово целиком” и без указания настройки “Учитывать регистр”.