Вграденото сортиране на данни в Excel е невероятно, но не е динамично. Ако сортирате данни и след това добавите данни към тях, ще трябва да ги сортирате отново.
Сортирайте данните по азбучен ред
В този пост ще ви покажа различни начини за сортиране на данни по азбучен ред с помощта на формули. Това означава, че можете да добавяте данни и той автоматично ще ги сортира вместо вас.
Когато всички данни са текст без дубликати
Да предположим, че имате данни, както е показано по -долу:
В този пример всички данни са в текстов формат (без числа, заготовки или дубликати). За да сортирам това, ще използвам помощна колона. В колоната до данните използвайте следната формула COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Тази формула сравнява текстова стойност с всички останали текстови стойности и връща относителния й ранг. Например в клетка В2 връща 8, тъй като има 8 текстови стойности, които са по -ниски или равни на текста „САЩ“ (азбучен ред).
Сега, за да сортирате стойностите, използвайте следната комбинация от функции INDEX, MATCH и ROWS:
= ИНДЕКС ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Тази формула просто извлича имената по азбучен ред. В първата клетка (C2) тя търси името на държавата с най -малък брой (Австралия има 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
Идеята за тази формула е да разделя празни места, числа и текстови стойности.
- Ако клетката е празна, тя връща стойността в клетка B2 (която винаги би била 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.
Тази формула има полезност в реалния свят. Какво мислиш? Бих искал да се уча от вас. Оставете следите си в секцията за коментари!
