Это руководство покажет вам, как расположить по алфавиту в Excel с помощью функции сортировки и фильтрации, а также формулы для упорядочивания и форматирования данных.
Microsoft Excel - эффективное программное обеспечение для систематизации важных данных, таких как финансы, данные о продажах, информация о клиентах и т. Д. Распределение данных по таблицам в алфавитном порядке - один из лучших способов быстро организовать эти данные, получить к ним доступ и ссылаться на них. Упорядочивание диапазона ячеек в алфавитном порядке означает сортировку диапазона в Excel по алфавиту. Данные можно сортировать в обоих направлениях: по возрастанию или по убыванию.
Например, предположим, что у вас есть большой набор данных о заказах клиента и вы хотите найти историю заказов конкретного клиента. Перебрать весь список станет довольно утомительно. Вместо того, чтобы тратить время на прокрутку, вы можете расположить столбцы в алфавитном порядке, чтобы быстро находить данные.
В зависимости от типа набора данных, с которым вы работаете, вы можете использовать различные способы упорядочивания данных в Excel. Excel позволяет сортировать один столбец или одну строку, выбранный диапазон или весь рабочий лист, а также позволяет группировать несколько столбцов или строк вместе во время их сортировки. Давайте узнаем, как расположить данные в таблицах Excel по строкам или столбцам.
Зачем располагать данные в Excel по алфавиту
Распределение данных по алфавиту дает множество преимуществ:
- Это делает данные более понятными и упрощает чтение.
- Это упрощает пользователю поиск определенного значения или имени клиента в таблице Excel.
- Это помогает пользователю визуально идентифицировать повторяющиеся записи, чтобы мы могли предотвратить ошибки ввода данных.
- Это позволяет пользователям легко группировать столбцы или списки вместе, чтобы пользователи могли просматривать их рядом.
Упорядочивание по алфавиту (сортировка по алфавиту) - простой и распространенный способ быстрой сортировки информации. Это особенно полезно для очень больших наборов данных. Всего в Excel есть 4 способа расположить данные в алфавитном порядке: кнопка A – Z или Z – A, функция сортировки, функция фильтрации и формулы.
Расстановка столбцов в Excel по алфавиту
Самый быстрый способ сортировки по алфавиту в Excel - использовать встроенную функцию сортировки.
Сначала откройте электронную таблицу, содержащую набор данных. Выберите ячейки или столбец, которые вы хотите отсортировать, и убедитесь, что не выбрали пустые ячейки. Затем перейдите на вкладку «Данные» и нажмите «A – Z» для сортировки по возрастанию или «Z – A» для сортировки по убыванию в группе «Сортировка и фильтр».
Если между ними есть пустые ячейки, Excel предполагает, что все данные отсортированы, и останавливается над черной ячейкой. Если вы хотите отсортировать весь столбец, просто выберите любую ячейку в столбце и используйте указанную выше опцию.
К тем же параметрам можно получить доступ с помощью инструмента «Сортировка и фильтр» в разделе «Редактирование» вкладки «Главная».
Как только вы нажмете кнопку, Excel сразу же отсортирует ваш список по алфавиту.
Как сортировать и хранить строки вместе в Excel
Вы можете легко отсортировать столбец, если на вашем листе есть только один столбец, но это становится немного сложнее, если ваш рабочий лист содержит несколько столбцов рядом с столбцом, который вы хотите отсортировать. Кроме того, иногда мы работаем с электронными таблицами, где нам нужно сохранить нетронутыми соответствующие строки после сортировки столбца.
Например, если у нас есть таблица со списком оценок учащихся. Когда мы сортируем столбец с именами учащихся в алфавитном порядке, мы также хотим, чтобы отметки, введенные в строках рядом с ними, также перемещались соответствующим образом.
В таких случаях вы можете использовать кнопку «A – Z» или «Z – A», чтобы отсортировать один из столбцов в алфавитном порядке, и он автоматически упорядочит данные в других столбцах, сохраняя строки вместе. Давайте посмотрим, как это работает, на другом примере.
Сначала выберите первый столбец, по которому вы хотите отсортировать другие столбцы. Затем выберите «А-Я» или «Я-А» на вкладке «Данные» или «Главная», чтобы отсортировать данные в одном столбце, и Excel автоматически изменит порядок остальных столбцов соответствующим образом.
После того, как вы нажмете кнопку сортировки, появится диалоговое окно с предупреждением о сортировке. Установите переключатель «Расширить выбор» и нажмите кнопку «Сортировка».
Это сохранит строки вместе при сортировке столбцов без несоответствия данных (как показано ниже).
Если вы выберете вариант «Продолжить с текущим выбором», Excel отсортирует только выбранный столбец.
Как отсортировать в алфавитном порядке по нескольким столбцам
Если вы хотите отсортировать набор данных в алфавитном порядке по нескольким столбцам, вы можете сделать это с помощью функции сортировки Excel.
Например, мы хотим расположить следующую таблицу в алфавитном порядке сначала по странам, а затем по имени:
Сначала выберите всю таблицу, которую вы хотите отсортировать по алфавиту, затем перейдите на вкладку «Данные» и в группе «Сортировка и фильтр» нажмите «Сортировать».
Или нажмите значок «Сортировка и фильтр» в разделе «Редактирование» на вкладке «Главная» и выберите «Пользовательская сортировка», чтобы получить доступ к той же функции.
Появится диалоговое окно «Сортировка», в котором будет показан первый столбец (параметр), в котором будет сортироваться таблица. В раскрывающемся списке «Сортировать по» выберите основной столбец, по которому вы хотите сначала расположить данные в алфавитном порядке, в нашем случае «Страна», а в раскрывающемся списке «Порядок» выберите либо «От А до Я», либо от Я до А. .
Затем нажмите кнопку «Добавить уровень», чтобы добавить второй уровень сортировки. Выберите второй столбец (в нашем случае «Имя»), по которому вы хотите расположить данные в алфавитном порядке в поле «Сортировать по», и выберите «От А до Я» или «Я до А». Если в вашей таблице есть заголовки вверху, установите флажок «Мои данные имеют заголовки», чтобы при сортировке заголовки можно было пропустить. При необходимости добавьте дополнительные уровни сортировки и нажмите «ОК».
Таблица расположена в алфавитном порядке: сначала по странам, а затем по именам, как показано ниже.
Как отсортировать строки в Excel по алфавиту
Иногда вам может потребоваться расположить в Excel по алфавиту строки, а не столбцы. Это можно сделать с помощью функции сортировки Excel. Например, предположим, что у вас есть электронная таблица, в которой столбцы первой строки от B до T содержат названия городов, где количество магазинов, доступных для различных типов розничной торговли, а столбец A содержит тип продавца. Ячейки используются для отслеживания количества магазинов в каждом городе в разных категориях розничных продавцов.
Сначала выберите все строки, которые вы хотите расположить по алфавиту; Если вы не хотите перемещать метки строк, не выбирайте их. Теперь перейдите на вкладку «Данные» и нажмите «Сортировка» в группе «Сортировка и фильтр».
В диалоговом окне «Сортировка» нажмите кнопку «Параметры».
Появится еще одно небольшое диалоговое окно, в котором вы сможете выбрать «Сортировать слева направо» или «Сортировать сверху вниз». Выберите вариант «Сортировать слева направо», чтобы вместо этого расположить их по строкам, и нажмите «ОК», чтобы вернуться в диалоговое окно «Сортировка».
Теперь выберите строку, которую вы хотите расположить в алфавитном порядке, в раскрывающемся списке «Сортировать по» (строка 1 в этом примере). В другом поле оставьте значения ячеек в поле «Сортировка по» и выберите «от А до Я» (в порядке возрастания) в поле «Порядок». Затем нажмите «ОК», чтобы закрыть диалоговое окно.
Теперь первая строка в нашей таблице отсортирована в алфавитном (возрастающем) порядке, а остальные строки переставлены соответствующим образом, как показано ниже.
Как отсортировать столбец по алфавиту с помощью фильтра
Еще один быстрый способ сортировки по алфавиту в Excel - использование функции «Фильтр». После применения фильтров к столбцам доступ к параметрам сортировки для всех столбцов можно получить одним щелчком мыши.
Чтобы добавить фильтр к столбцам, выберите один или несколько столбцов и нажмите кнопку «Фильтр» в группе «Сортировка и фильтр» на вкладке «Данные». Если вы хотите применить фильтр ко всей таблице, выберите любую ячейку в таблице и нажмите кнопку «Фильтр».
Вы также можете получить доступ к параметру «Фильтр», щелкнув инструмент «Сортировка и фильтр» в группе редактирования на вкладке «Главная» и выбрав «Фильтр».
В заголовках каждого столбца появится небольшая стрелка раскрывающегося списка. Щелкните стрелку любого столбца, который нужно отсортировать в алфавитном порядке, и выберите «Сортировать от А до Я» или «Сортировать от Я до А».
Это расположит нужный столбец в выбранном алфавитном порядке, и если вы выбрали «Сортировать от А до Я», на кнопке фильтра появится небольшая стрелка вверх, указывающая порядок сортировки (по возрастанию):
Расширенная сортировка в произвольном порядке
Сортировка по алфавиту не идеальна для всех данных по алфавиту. Иногда данные можно расположить в алфавитном порядке, но это не самый эффективный способ их сортировки.
Представим себе ситуацию, когда у вас может быть список, содержащий названия месяцев или дней недели, сортировка по алфавиту в этой ситуации бесполезна. Имеет смысл отсортировать этот список в хронологическом порядке. Если вы отсортируете от А до Я, месяцы будут расположены в алфавитном порядке, сначала будет апрель, затем август, февраль, июнь и так далее. Но это не то, что вам нужно. К счастью, в Excel очень легко расположить в хронологическом порядке, используя опцию Advanced Custom Sort.
Начните с выбора таблицы, которую хотите отсортировать. Затем выберите «Пользовательская сортировка» в разделе «Сортировка и фильтр» в разделе «Редактирование» на вкладке «Главная».
В диалоговом окне «Сортировка» выберите столбец, содержащий месяцы года в разделе столбца, выполните сортировку по «Значениям», как обычно, и в разделе «Порядок» выберите «Пользовательский список».
В диалоговом окне «Пользовательские списки» вы можете создать свой собственный список. Есть несколько настраиваемых списков по умолчанию, включая дни недели, сокращенные месяцы, месяцы года и т. Д. Выберите подходящий вариант сортировки в соответствии с вашими потребностями (в нашем случае январь, февраль ...) и нажмите «ОК». Затем снова нажмите «ОК», чтобы отсортировать список в хронологическом порядке.
Вы заметите, что вы успешно отсортировали список по месяцам.
Как сортировать по алфавиту в Excel с помощью формул Excel
Если вы поклонник формул, вы можете использовать формулы Excel, чтобы расположить список в алфавитном порядке. Две формулы, которые можно использовать для размещения данных в алфавитном порядке, - это СЧЁТЕСЛИ и ВПР.
Например, у нас есть список имен, которые мы хотим расположить по алфавиту с помощью формулы.
Чтобы отсортировать это, мы добавим временный столбец с именем «Порядок сортировки» в существующую таблицу.
В ячейке (A2) рядом с данными используйте следующую формулу СЧЁТЕСЛИ:
= СЧЁТЕСЛИ ($ B $ 2: $ B $ 20, "<=" & B2) Приведенная выше формула сравнивает текстовое значение в ячейке B2 со всеми другими текстовыми значениями (B3: B20) и возвращает его относительный ранг. Например, в ячейке B2 он возвращает 11, поскольку есть 11 текстовых значений, которые в алфавитном порядке меньше или равны тексту «Nancy». Результатом является порядок сортировки имени сотрудника в ячейке B2.
Затем с помощью маркера заполнения перетащите эту формулу, чтобы заполнить ее по всему диапазону. Это дает порядок сортировки каждого имени в списке.
Теперь нам нужно расположить данные, показанные на скриншоте выше, на основе номера порядка сортировки, и для этого мы собираемся использовать функцию ВПР.
Синтаксис:
= ВПР (; A: B; 2,0)Здесь «номер сортировки» представляет собой числа в порядке возрастания от 1 до 20. В порядке убывания числа должны быть от 20 до 1.
Здесь у нас есть столбец D для отсортированных имен. Для ячейки D2 введите следующую формулу ВПР:
= ВПР (1; A: B; 2,0)Точно так же для второй и третьей ячеек вы должны использовать формулу как:
= ВПР (2; A: B; 2,0) и = ВПР (3; A: B; 2,0) и так далее…
После ввода этой формулы ВПР в каждую ячейку рядом с данными список упорядочивается по алфавиту.
Вместо того, чтобы вручную вводить вышеупомянутую формулу (1-20) в каждую ячейку, вы также можете использовать функцию строки, чтобы упростить вашу работу. Функция Row () возвращает номер строки текущей ячейки. Итак, с помощью строковой функции формула будет иметь вид:
= ВПР (СТРОКА () - 1; A: B; 2,0)Эта формула даст вам тот же результат, что и приведенная выше формула.
Затем с помощью маркера заполнения перетащите эту формулу, чтобы заполнить ее по всему диапазону.
Как расположить записи по фамилии в алфавитном порядке
Иногда нам часто приходится сортировать таблицы с фамилиями в алфавитном порядке. В таких случаях, даже если имена начинаются с имен, вам необходимо расположить их в алфавитном порядке по фамилии. Вы можете сделать это с помощью текстовых формул Excel.
Для этого сначала вам нужно будет извлечь имя и фамилию из полных имен в два разных столбца. Затем переверните имена, отсортируйте их, а затем верните их в исходную форму.
С полным именем в A2 введите следующие формулы в две разные ячейки (B2 и C2), а затем скопируйте формулы вниз по столбцам (используя дескриптор заполнения) до последней ячейки с данными:
Чтобы извлечь имя, введите эту формулу в ячейку C2:
= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)
Чтобы извлечь фамилию, введите эту формулу в ячейку D2:
= ВПРАВО (A2; LEN (A2) -ПОИСК (""; A2,1))
А затем в ячейке E2 соединили имя и фамилию в обратном порядке, разделенные запятой:
= D2 & "," & C2
Мы разделили имя и фамилию и поменяли их местами. Теперь нам нужно расположить их в алфавитном порядке и перевернуть обратно. Как вы можете видеть выше, когда мы извлекаем имена с помощью формулы, столбцы C, D и E фактически содержат формулы, но они отформатированы так, чтобы выглядеть как значения. Итак, нам нужно преобразовать формулу в значения.
Для этого сначала выберите все ячейки формулы (E1: E31) и нажмите Ctrl + C скопировать их. Щелкните выделенные ячейки правой кнопкой мыши, щелкните значок «Значения» в разделе «Параметры вставки» и нажмите клавишу «Ввод».
Затем выберите любую ячейку в результирующем столбце и нажмите кнопку «От А до Я» или «Я до А» на вкладке «Данные» или «Главная» в соответствии с вашими потребностями.
После нажатия кнопки появится диалоговое окно с предупреждением о сортировке. Выберите вариант «Развернуть выделение» и нажмите кнопку «Сортировка».
Как видите, весь столбец отсортирован по фамилии.
Теперь, если вы хотите вернуть его к исходному «формату имени и фамилии», вам просто нужно ввести те же формулы, но с другими ссылками на ячейки.
Вам нужно снова разделить имя (E2) на две части, используя следующие формулы:
В G2 извлеките имя:
= ВПРАВО (E2; LEN (E2) -SEARCH (""; E2))
В G2 вытащите фамилию:
= ВЛЕВО (E2; ПОИСК (""; E2) -2)
И соедините две части вместе, чтобы получить исходное полное имя:
= G2 & "" & H2
Каждый раз, когда вы вводите формулу в первую ячейку, обязательно копируйте формулу вниз по столбцу (используя дескриптор заполнения) до последней ячейки с данными.
Теперь все, что вам нужно сделать, это преобразовать формулы в преобразование значений еще раз, как мы делали выше, и все готово. это сделано и.