Вбудована в Excel сортування даних дивовижна, але вона не є динамічною. Якщо ви сортуєте дані, а потім додаєте до них дані, вам доведеться сортувати їх знову.
Сортувати дані в алфавітному порядку
У цьому пості я покажу вам різні способи сортування даних в алфавітному порядку за допомогою формул. Це означає, що ви можете додати дані, і вони автоматично відсортують їх за вас.
Якщо всі дані - це текст без дублікатів
Припустимо, у вас є дані, як показано нижче:
У цьому прикладі всі дані у текстовому форматі (без чисел, пробілів або дублікатів). Щоб відсортувати це, я буду використовувати допоміжний стовпець. У стовпці біля даних використовуйте таку формулу COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ця формула порівнює текстове значення з усіма іншими текстовими значеннями і повертає його відносний ранг. Наприклад, у комірці В2 вона повертає 8, оскільки є 8 текстових значень, які менші або рівні тексту «США» (в алфавітному порядку).
Тепер для сортування значень використовуйте таку комбінацію функцій INDEX, MATCH та ROWS:
= ІНДЕКС ($ A $ 2: $ A $ 9, МАТЧ (РЯДИ ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Ця формула просто витягує імена в алфавітному порядку. У першій комірці (С2) вона шукає назву країни з найменшим числом (Австралія має 1). У другій клітинці він повертає Канаду (яка має номер 2) і так далі…
Алергія на колонки -помічники ??
Ось формула, яка буде робити те ж саме без стовпця -помічника.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Це формула масиву, тому використовуйте Control + Shift + Enter замість Enter.
Я залишу це для вас, щоб декодувати код.
Спробуйте самі… Завантажте файл прикладу
Ця формула добре працює, якщо у вас є текстові або буквено -цифрові значення.
Але це зазнає невдачі, якщо:
- У вас є дублікати даних (спробуйте двічі поставити США).
- У даних є пробіли.
- У вас є суміш цифр і тексту (спробуйте вставити 123 в одну з клітинок).
Коли дані - це суміш чисел, тексту, дублікатів та пробілів
Тепер це трохи хитро. Я буду використовувати 4 допоміжні стовпці, щоб показати вам, як це працює (а потім дам вам величезну формулу, яка буде робити це без допоміжних стовпців). Припустимо, у вас є дані, як показано нижче:
Ви можете побачити, що є дублюючі значення, порожні та цифри. Тому я буду використовувати допоміжні стовпці для вирішення кожної з цих проблем.
Колонка помічника 1
Введіть таку формулу COUNTIF у стовпці помічника 1
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ця формула робить наступне:
- Він повертає 0 для пробілів.
- У разі дублікатів він повертає той самий номер.
- Текст і числа обробляються паралельно, і ця формула повертає однакове число для тексту та числа (наприклад, 123 та Індія отримують по 1).
Колонка помічника 2
Введіть таку функцію IS у колонку 2 помічника:
=-ISNUMBER (A2)
Колонка помічника 3
Введіть таку формулу у стовпці помічника 3:
=-ISBLANK (A2)
Колонка помічника 4
Введіть таку формулу у стовпці помічника 4
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ідея цієї формули полягає в тому, щоб розділити пробіли, числа та текстові значення.
- Якщо клітинка порожня, вона повертає значення в клітинку В2 (яка завжди буде 0) і додає значення в комірку D10. Коротко кажучи, він поверне загальну кількість порожніх клітинок у даних
- Якщо клітинка є числовим значенням, вона поверне порівняльний ранг і додасть загальну кількість пробілів. Наприклад, для 123 він повертає 2 (1 - це ранг 123 у даних, і є 1 порожня клітинка)
- Якщо це текст, він повертає порівняльний ранг і додає загальну кількість числових значень та пробілів. Наприклад, для Індії він додає порівняльний ранг тексту в тексті (це 1) і додає кількість порожніх клітинок та кількість числових значень.
Остаточний результат - Сортовані дані
Тепер ми будемо використовувати ці допоміжні стовпці, щоб отримати відсортований список. Ось формула:
= IFERROR (ІНДЕКС ($ A $ 2: $ A $ 9, МАТЧ , "")
Цей метод сортування тепер стає надійним. Я показав вам метод для 8 елементів, але ви можете розширити його на стільки предметів, скільки захочете.
Спробуйте самі… Завантажте файл прикладу
Одна формула для впорядкування всього (без стовпців -помічників)
Якщо ви можете обробляти екстремальні формули, ось формула "все в одному", яка буде сортувати дані в алфавітному порядку (без стовпців-помічників).
Ось формула:
= IFERROR (ІНДЕКС ($ A $ 2: $ A $ 9, МАТЧ $ 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 = "")*ЯКЩО (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.
Ця формула має реальну корисність. Що ти думаєш? Я хотів би у вас повчитися. Залиште свої сліди в розділі коментарів!
