Встроенная в Excel сортировка данных великолепна, но не является динамической. Если вы отсортируете данные, а затем добавите к ним данные, вам нужно будет снова отсортировать их.
Сортировка данных в алфавитном порядке
В этом посте я покажу вам различные способы сортировки данных в алфавитном порядке с помощью формул. Это означает, что вы можете добавлять данные, и он автоматически отсортирует их за вас.
Когда все данные представляют собой текст без дубликатов
Предположим, у вас есть данные, как показано ниже:
В этом примере все данные представлены в текстовом формате (без чисел, пробелов и дубликатов). Чтобы отсортировать это, я буду использовать вспомогательный столбец. В столбце рядом с данными используйте следующую формулу СЧЁТЕСЛИ:
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 9; "<=" & A2)
Эта формула сравнивает текстовое значение со всеми другими текстовыми значениями и возвращает его относительный ранг. Например, в ячейке B2 он возвращает 8, поскольку есть 8 текстовых значений, которые меньше или равны тексту «США» (в алфавитном порядке).
Теперь, чтобы отсортировать значения, используйте следующую комбинацию функций ИНДЕКС, ПОИСКПОЗ и СТРОКИ:
= ИНДЕКС ($ A $ 2: $ A $ 9, ПОИСКПОЗ (СТРОКИ ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Эта формула просто извлекает имена в алфавитном порядке. В первой ячейке (C2) он ищет название страны с наименьшим номером (Австралия имеет 1). Во второй ячейке возвращается Канада (с номером 2) и так далее …
Аллергия на вспомогательные столбцы ??
Вот формула, которая будет делать то же самое без вспомогательного столбца.
= ИНДЕКС ($ A $ 2: $ A $ 9, ПОИСКПОЗ (СТРОКИ ($ A $ 2: A2), СЧЁТЕСЛИ ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Это формула массива, поэтому используйте Control + Shift + Enter вместо Enter.
Я оставлю это вам для расшифровки кода.
Попробуйте сами… Скачать файл примера
Эта формула хорошо работает, если у вас есть текстовые или буквенно-цифровые значения.
Но он терпит неудачу, если:
- У вас есть дубликаты данных (попробуйте ввести США дважды).
- В данных есть пробелы.
- У вас есть сочетание чисел и текста (попробуйте ввести 123 в одну из ячеек).
Когда данные представляют собой смесь чисел, текста, дубликатов и пробелов
Теперь это немного сложно. Я буду использовать 4 вспомогательных столбца, чтобы показать вам, как это работает (а затем дам вам огромную формулу, которая сделает это без вспомогательных столбцов). Предположим, у вас есть данные, как показано ниже:
Вы можете видеть, что есть повторяющиеся значения, пробелы и числа. Поэтому я буду использовать вспомогательные столбцы для решения каждой из этих проблем.
Столбец помощника 1
Введите следующую формулу СЧЁТЕСЛИ в вспомогательный столбец 1.
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 9; "<=" & A2)
Эта формула делает следующее:
- Он возвращает 0 для пробелов.
- В случае дубликатов возвращается тот же номер.
- Текст и числа обрабатываются параллельно, и эта формула возвращает одно и то же число для текста и числа (например, 123 и Индия получают 1).
Столбец помощника 2
Введите следующую функцию IS в вспомогательный столбец 2:
= - ЕЧИСЛО (A2)
Столбец помощника 3
Введите следующую формулу в вспомогательный столбец 3:
= - ISBLANK (A2)
Столбец помощника 4
Введите следующую формулу в вспомогательный столбец 4.
= ЕСЛИ (ISNUMBER (A2); B2; IF (ISBLANK (A2); B2; B2 + $ C $ 10)) + $ D $ 10
Идея этой формулы заключается в разделении пробелов, чисел и текстовых значений.
- Если ячейка пуста, она возвращает значение в ячейке B2 (которое всегда будет 0) и добавляет значение в ячейку D10. Вкратце, он вернет общее количество пустых ячеек в данных.
- Если ячейка представляет собой числовое значение, она вернет сравнительный ранг и добавит общее количество пробелов. Например, для 123 он возвращает 2 (1 - это ранг 123 в данных, и есть 1 пустая ячейка)
- Если это текст, он возвращает сравнительный рейтинг и добавляет общее количество числовых значений и пробелов. Например, для Индии он добавляет сравнительный рейтинг текста в тексте (который равен 1) и добавляет количество пустых ячеек и количество числовых значений.
Конечный результат - отсортированные данные
Теперь мы будем использовать эти вспомогательные столбцы для получения отсортированного списка. Вот формула:
= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 9, ПОИСКПОЗ (МАЛЕНЬКИЙ ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2) + $ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Теперь этот метод сортировки становится надежным. Я показал вам метод для 8 элементов, но вы можете распространить его на любое количество элементов.
Попробуйте сами… Скачать файл примера
Одна формула для сортировки всего (без вспомогательных столбцов)
Если вы умеете работать с крайними формулами, вот универсальная формула, которая будет сортировать данные в алфавитном порядке (без вспомогательного столбца).
Вот формула:
= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 9, ПОИСКПОЗ (МАЛЫЙ (НЕ ($ A $ 2: $ A $ 9 = "")) * ЕСЛИ (ISNUMBER ($ A $ 2: $ A $ 9), СЧЁТЕСЛИ ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), ROWS ($ A $ 2: A2) + SUM (- ISBLANK ($ A $ 2: $ A $ 9))), НЕ ($ A $ 2: $ A $ 9 = "") * IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9) + SUM (- ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Введите эту формулу в ячейку и перетащите ее вниз, чтобы получить отсортированный список. Кроме того, поскольку это формула массива, используйте Control + Shift + Enter вместо Enter.
Эта формула имеет практическое применение. Что вы думаете? Я хотел бы поучиться у вас. Оставляйте свои следы в комментариях!
