Как сделать сортировку в Экселе по алфавиту: варианты, настройки, формулы пошагово с фото

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

Быстрая сортировка

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

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

Суть быстрой сортировки в Excel сводится к следующему:

1. Найти раздел «Редактирование» – последняя позиция в горизонтальном ряду главной закладки (крайняя справа).
   
2. Щелкнуть на нее мышкой и открываем подпункт «Сортировка и фильтр».
   
3. Открывается меню с разными вариантами настройки, сортировки и фильтрации.
   

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

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


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

Вариант быстрой сортировки в пределах ограниченного диапазона

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

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


После такой перестановки информация в экселе будет искажена. Чтобы отменить действие, нужно ткнуть мышкой на значок отмены действия в верхней левой части Экселя или нажать «CTRL+Z».

Сортировка с настройками

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

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

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

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

1. Создаем новый лист в книге Экселя.
   
2. Выделяем и копируем всю область сведений на новый лист.
   
3. Выделяем скопированное, присваиваем ей статус таблицы.
   

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


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


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

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

Формулы для сортировки

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

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

Вспомогательная формула

В качестве основы будущей сортировки будет использована типовая функция Экселя «СЧЕТЕСЛИ». В строке пишется следующим образом «=СЧЕТЕСЛИ(А; «Б»), где:

• А – диапазон (столбец), в котором будет выполняться поиск;
  
• Б – набор букв, цифр или символов, которые нужно искать.
  

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


Вспомогательная формула будет выглядеть так – =СЧЕТЕСЛИ(ФРУКТЫ; “<=”&A1). Перед аргументом А1 нужно добавить «&», а условие «меньше или равно» должно быть в верхних кавычках (“ ”), а не в обычных (« »).

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


Это еще не сортировка по алфавиту, но примерно 80% задачи. Для работы основной функции сортировки необходим второй столбец, в котором приведены порядковые номера названий.

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

Основная формула

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

Для окончательной сортировки будет использоваться функция экселя: = ПОИСКПОЗ (искомое значение, массив поиска, тип сопоставления). Достаточно сложная типовая формула. Функция перебирает определенный массив значений (столбец с номерами), сравнивает с заданным и выдает относительную позицию найденного значения.

Формула, которую нужно забить в верхнюю строку (fx) для сортировки, выглядит следующим образом: =ПОИСКПОЗ(СТРОКА(А1); номер_слова; 0).

Первый аргумент «СТРОКА(А1) отправляет поиск Экселя к первому столбцу. Далее «номер_слова» – то, что соотносится или привязывается к значению из первого столбца. Последний аргумент (0) означает, что требуется точное совпадение названий. Можно указать буквенно-числовую кодировку диапазона, но чтобы не привязываться к конкретным координатам, используем имена столбцов.

Для этого необходимо войти в закладку «Формулы» на верхней ленте Экселя. Далее найти позицию «Определенные имена», войти, после указать «Фрукты» и «номер_слова» в качестве имен, используемых для замены абсолютных значений.

После того как отработает «ПОИСКПОЗ», нужно завершить перестановку названий фруктов по алфавиту. То есть перебрать текстовые значения в столбце «Фрукты» и расставить их соответственно значению в «номер_слова». Превратить вычисленное вспомогательной функцией число (для апельсина – «7») в порядковый номер или индекс.

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

Для вытаскивания значений из массива в экселе имеется функция «ИНДЕКС». Правильное написание (синтаксис) выглядит так: =ИНДЕКС (Диапазон-столбец; Порядковый_номер_ячейки).

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

В данном случае для сортировки нужно подставить свои значения. В качестве первого аргумента – «Фрукты», для второго прямо укажем функцию ПОИСКПОЗ со своими данными. Основная функция сортировки получается в следующем виде – «=(ИНДЕКС(Фрукты;ПОИСКПОЗ(А1);номер_слова;0))».

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


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

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

Что делать, если значения повторяются

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


Схема пересортировки та же. Но вспомогательную функцию «СЧЕТЕСЛИ» нужно будет модифицировать:

1. Изменить условие, вместо выражения «“<=&A1”» должно быть «”=&A1”».
   
2. К существующей функции через знак плюс добавить еще одну -«СЧЕТЕСЛИ($A&1:A1:A1)».
   

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

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

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

Видео

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

Предлагаем статью: Что делать, если не удалось открыть файл xlsx