Как отсортировать по алфавиту в Excel

Обновлено: 05.07.2024

Я понимаю, что использование Excel может быть сложной задачей, особенно для новичков. Хорошо, что есть много онлайн-ресурсов, которые помогут вам маневрировать в каждом уголке MS Excel. Сегодня мы собираемся обсудить, как сортировать по алфавиту, не затрагивая строки.

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

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

Общая сортировка

Оглавление

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

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

Использование функции сортировки

Первый шаг — выбрать данные столбца, которые вы хотите отсортировать.

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

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

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

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

Использование функции расширенной сортировки в Excel

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

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

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

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

Это руководство покажет вам несколько быстрых способов расставить по алфавиту в Excel и научит предвидеть и предотвращать проблемы с сортировкой.

Как расставить по алфавиту в Excel

В целом существует 3 основных способа сортировки по алфавиту в Excel: кнопки от A до Z или Z-A, функция сортировки и фильтр. Ниже вы найдете подробное руководство по каждому методу.

Как отсортировать столбец по алфавиту

Самый быстрый способ сортировки по алфавиту в Excel:

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

Эти же кнопки также доступны на вкладке Главная > группа Редактирование > Сортировка и фильтрация:

В любом случае Excel мгновенно расставит список по алфавиту:

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

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

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

Как видно из отсортированной таблицы справа, связанная информация в каждой строке хранится вместе:

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

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

Фильтрация и расположение в алфавитном порядке в Excel

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

Добавить фильтр в таблицу очень просто:

Выберите один или несколько заголовков столбцов.
На вкладке Главная в группе "Редактирование" нажмите "Сортировка и фильтр" > "Фильтр".
В заголовках каждого столбца появятся маленькие раскрывающиеся стрелки. Нажмите на стрелку раскрывающегося списка столбца, который вы хотите расположить в алфавитном порядке, и выберите Сортировать от А до Я:

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

Чтобы изменить порядок, выберите Сортировать от Z до A в раскрывающемся меню фильтра.

Чтобы удалить фильтр, просто снова нажмите кнопку Фильтр.

Как разместить несколько столбцов в алфавитном порядке

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

В качестве примера давайте добавим еще один столбец в наш набор данных, а затем расположим записи в алфавитном порядке сначала по региону, а затем по названию:
< /p>

Чтобы это сделать, выполните следующие действия:

В раскрывающемся списке Сортировать по выберите столбец, который вы хотите расположить в алфавитном порядке первым, в нашем случае Регион. В двух других полях оставьте настройки по умолчанию: Сортировать — значения ячеек и Порядок — от А до Я:

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

В этом примере второй уровень сортирует значения в столбце Имя в алфавитном порядке от А до Я:

Совет. Если вы сортируете по нескольким столбцам с одинаковыми критериями, нажмите Копировать уровень вместо Добавить уровень. В этом случае вам нужно будет только выбрать другой столбец в первом поле.

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

Как отсортировать строки в Excel по алфавиту

Если ваши данные расположены горизонтально, вы можете отсортировать их по строкам в алфавитном порядке. Это также можно сделать с помощью функции Excel Sort. Вот как:

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

Проблемы с сортировкой по алфавиту в Excel

Функции сортировки Excel великолепны, но если вы работаете с несовершенно структурированными данными, все может пойти не так. Вот две распространенные проблемы.

Пустые или скрытые столбцы и строки

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

Простое решение – устранить пробелы и отобразить все скрытые области перед сортировкой. В случае пустых строк (не скрытых строк!), вы можете сначала выделить всю таблицу, а затем отсортировать по алфавиту.

Неузнаваемые заголовки столбцов

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

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

Как сортировать по алфавиту в Excel с помощью формул

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

Как расположить в Excel по алфавиту по фамилии

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

Параметры сортировки Excel в этом случае не помогут, поэтому обратимся к формулам.

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

В C2 извлеките имя:

В D2 извлеките фамилию:

А затем соедините части в обратном порядке, разделенные запятой:

Подробное объяснение формул можно найти здесь, а пока сосредоточимся на результатах:

Поскольку нам нужно расположить в алфавитном порядке имена, а не формулы, преобразуйте их в значения. Для этого выделите все ячейки формулы (E2:E10) и нажмите Ctrl + C, чтобы скопировать их. Щелкните выбранные ячейки правой кнопкой мыши, выберите "Значения" в разделе Параметры вставки и нажмите клавишу Enter:

Хорошо, вы почти у цели! Теперь выберите любую ячейку в результирующем столбце, нажмите кнопку от А до Я или от Я до А на вкладке Данные, и у вас есть список в алфавитном порядке по фамилии:

Если вам нужно вернуться к исходному формату Имени Фамилии, вам нужно сделать еще немного:

Снова разделите имена на две части, используя приведенные ниже формулы (где E2 – это имя, разделенное запятыми):

Получить имя:

=ВПРАВО(E2, ДЛСТР(E2) - ПОИСК(" ", E2))

Получить фамилию:

=ВЛЕВО(E2, ПОИСК(" ", E2) - 2)

И соедините две части вместе:

Выполните преобразование формул в значения еще раз, и все готово!

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

Как упорядочить каждую строку в Excel по алфавиту

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

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

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

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

=ИНДЕКС($B2:$D2, ПОИСКПОЗ(СТОЛБЦЫ($B2:B2), СЧЁТЕСЛИ($B2:$D2, " Ctrl + Shift + Enter .

Если вам не очень удобно работать с формулами массива Excel, выполните следующие действия, чтобы правильно ввести их на листе:

Введите формулу в первую ячейку (в нашем случае G2) и нажмите Ctrl + Shift + Enter . Когда вы это сделаете, Excel заключит формулу в . Не пытайтесь вводить фигурные скобки вручную, это не сработает.
Выберите ячейку с формулой (G2) и перетащите маркер заполнения вправо, чтобы скопировать формулу в другие ячейки первой строки (в данном примере до ячейки I2).
Выделите все ячейки с формулами в первой строке (G2:I2) и перетащите маркер заполнения вниз, чтобы скопировать формулу в другие строки.

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

Если в вашем наборе данных есть пробелы, заключите формулу в функцию ЕСЛИОШИБКА:

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

Как работает эта формула

Приведенная выше формула основана на классической комбинации ПОИСКПОЗ ИНДЕКС, используемой для горизонтального поиска в Excel. Но так как нам нужен "алфавитный поиск", мы перестроили его следующим образом:

СЧЁТЕСЛИ($B2:$D2," ПОИСКПОЗ ищет искомое значение, вычисленное функцией СТОЛБЦ(), в массиве поиска, возвращаемом функцией СЧЁТЕСЛИ(), и возвращает его относительное положение. Например, для G2 искомое значение равно 1. , который находится на 3-й позиции в массиве поиска, поэтому ПОИСКПОЗ возвращает 3.

Наконец, ИНДЕКС извлекает реальное значение на основе его относительного положения в строке.Для G2 извлекается третье значение в диапазоне B2:D2, то есть Ария.

Как отсортировать каждый столбец в алфавитном порядке в Excel

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

=ИНДЕКС(A$3:A$5,ПОИСКПОЗ(СТРОКИ(A$3:A3),СЧЁТЕСЛИ(A$3:A$5," Ctrl + Shift + Enter :

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

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

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

Вас также может заинтересовать

64 комментария к статье "Как расположить в Excel по алфавиту: отсортировать столбцы и строки от A до Z или от Z до A"

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

Одной из наиболее распространенных функций электронной таблицы Excel является сортировка данных по алфавиту.

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

Кроме того, есть ярлыки для быстрой реорганизации ячеек от "А до Я" или от "Я до А". Эти ярлыки находятся на вкладках "Данные" и "Главная".

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

Вот как это сделать.

Ознакомьтесь с продуктами, упомянутыми в этой статье:

Microsoft Office 365 для дома (от 99,99 долл. США в Best Buy)

Macbook Pro (от 1299 долларов США в Best Buy)

Microsoft Surface 2 (от 1999 долларов США в Best Buy)

Как расположить столбцы в алфавитном порядке в Excel

<р>1. Откройте электронную таблицу Excel, которую нужно расставить по алфавиту, на компьютере Mac или ПК.

<р>2. Выберите данные, которые хотите отсортировать, или ничего не выделяйте, если хотите отсортировать весь документ.

<р>3. Перейдите на вкладку "Данные" в верхней части страницы.

<р>4. Нажмите «Сортировать», расположенную посередине панели инструментов.

<р>5. Откроется всплывающее окно с подробной информацией о том, как сортировать строки и столбцы. Excel автоматически подставит инструкции для сортировки данных по столбцу А от А до Я.

<р>6. Нажмите раскрывающееся меню "Столбец", чтобы перейти к столбцу B (или другому столбцу, если их больше двух).

<р>7. Если в вашем списке есть заголовки, установите флажок в правом верхнем углу. Это исключит верхнюю строку из сортировки, и столбец теперь будет помечен как таковой (вместо столбца A).

<р>8. Выберите, хотите ли вы по-прежнему организовать по этому первому столбцу или по другому столбцу, снова щелкнув раскрывающееся меню «Столбец».

<р>9. В раскрывающемся меню в разделе «Порядок» выберите, хотите ли вы отсортировать по алфавиту от «А до Я» или от «Я до А».

<р>10. Нажмите «ОК» после того, как сделаете свой выбор. Это автоматически заполнит ваши столбцы в алфавитном порядке (или наоборот, если вы выберете).

Эти параметры вместе с быстрыми клавишами также находятся на вкладке "Главная".

Как отсортировать по строке в Excel

<р>1. Вы также можете отсортировать по строке в алфавитном порядке в том же параметре «Сортировка».

<р>2. Снова откройте всплывающее окно «Сортировка» и нажмите кнопку «Параметры…».

<р>3. Вас спросят, хотите ли вы сортировать сверху вниз или слева направо. Выберите «Сортировать слева направо», чтобы отсортировать по строке в алфавитном порядке. (Excel автоматически пытается сначала отсортировать по столбцу в алфавитном порядке.)

<р>4. Нажмите "ОК".

<р>5. Вы также можете нажать кнопку «минус» (-), чтобы полностью удалить действие столбца и начать заново, создав новую инструкцию. Нажмите кнопку "плюс" (+), а затем "Параметры...", чтобы указать порядок строк.

<р>6.Не забудьте отметить "В моем списке есть заголовки", если это применимо.

<р>7. В раскрывающемся меню в разделе «Порядок» выберите, хотите ли вы отсортировать по алфавиту от «А до Я» или от «Я до А».

<р>8. Нажмите «ОК» после того, как сделаете свой выбор. Это автоматически заполнит ваши столбцы в алфавитном порядке (или наоборот, если вы выберете).

Эти параметры вместе с быстрыми клавишами также находятся на вкладке "Главная".

Как расположить в Excel по алфавиту с помощью горячих клавиш

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

Однако Excel автоматически попытается выполнить сортировку по первому столбцу (или по последним сохраненным инструкциям), поэтому часто лучше использовать функцию "Сортировка", чтобы указать строки или столбцы, как описано выше.

<р>1. Выберите ячейки, которые вы хотите отсортировать по алфавиту.

<р>2. Перейдите на вкладку "Данные", где вы увидите кнопки "AZ" и "ZA", расположенные в центре панели инструментов рядом с функцией "Сортировка".

<р>3. Выберите способ организации ячеек.

<р>4. В зависимости от того, какой выбор вы сделали, вас может приветствовать всплывающее окно, подтверждающее эту область вашей электронной таблицы. Выберите «Продолжить с текущим выбором», а затем «Сортировать», если это произойдет. Это изменит порядок только тех данных, которые вы выделили. Нажмите "Отмена", если вы допустили ошибку и хотите отсортировать весь документ.

<р>5. Вы также можете найти эти ярлыки "AZ" и "ZA" на вкладке "Главная".

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

Сортировка данных в алфавитном порядке

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

Когда все данные представляют собой текст без дубликатов

Предположим, у вас есть данные, как показано ниже:

В этом примере все данные представлены в текстовом формате (без чисел, пробелов или дубликатов). Чтобы отсортировать это, я буду использовать вспомогательный столбец. В столбце рядом с данными используйте следующую формулу СЧЁТЕСЛИ:

Эта формула просто извлекает имена в алфавитном порядке. В первой ячейке (C2) ищется название страны с наименьшим числом (в Австралии 1). Во второй ячейке возвращается Канада (с номером 2) и т. д.

Аллергия на вспомогательные столбцы??

Вот формула, которая сделает то же самое без вспомогательного столбца.

Вспомогательный столбец 3

Введите следующую формулу в вспомогательный столбец 3:

Вспомогательный столбец 4

Введите следующую формулу в вспомогательный столбец 4

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

Если ячейка пуста, возвращается значение в ячейке B2 (которое всегда равно 0) и добавляется значение в ячейке D10. В двух словах, он вернет общее количество пустых ячеек в данных.
Если ячейка является числовым значением, она вернет сравнительный рейтинг и добавит общее количество пробелов. Например, для числа 123 возвращается 2 (1 – это ранг числа 123 в данных, а также 1 пустая ячейка).
Если это текст, он возвращает сравнительный рейтинг и добавляет общее количество числовых значений и пробелов.Например, для Индии добавляется сравнительный рейтинг текста (равный 1), а также количество пустых ячеек и количество числовых значений.

Конечный результат — отсортированные данные

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

Этот метод сортировки теперь становится надежным. Я показал вам метод для 8 элементов, но вы можете расширить его до любого количества элементов.

Попробуйте сами. Загрузите пример файла. Одна формула для сортировки всего (без вспомогательных столбцов)

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

Вот формула:

Введите эту формулу в ячейку и перетащите ее вниз, чтобы получить отсортированный список. Кроме того, поскольку это формула массива, используйте Control + Shift + Enter вместо Enter.

Эта формула полезна в реальных условиях. Как вы думаете? Я хотел бы учиться у вас. Оставьте свои следы в разделе комментариев!

Читайте также: