Изтеглете примерната работна книга
Този урок ще покаже как динамично да сортирате данни по персонализиран ред за сортиране в Excel и Google Sheets.
Функция SORTBY в Excel 365
Функцията SORTBY създава динамичен масив от сортирани данни.
В този пример използваме функцията SORTBY, за да вземем таблица с данни и да създадем динамичен масив, който да я сортира по Екип следвайки а Персонализиран списък за сортиране:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Забележка: Функцията SORTBY е записана само в клетка H3. Останалата част от таблицата ще се попълни автоматично.
Сортираната таблица е динамична, което означава, че ако някоя от Екип или Име на играча стойностите се променят или записват или добавят / изтриват, тогава изходната таблица автоматично ще преоразмерява и пресортира.
Забележка: Функцията SORTBY не е налична в Excel 2022 или по -ранна версия. Прочетете по -долу за опциите за сортиране, налични за по -ранните версии на Excel.
За да обясним как работи тази формула, нека я разделим на стъпки:
Нашата последна формула е:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Първо дефинираме таблицата с данни, която трябва да бъде сортирана. Това ще бъде B3: D8.
След това трябва да дефинираме реда за сортиране. Ще използваме функцията MATCH, за да намерим реда, в който всеки Екип името се появява в Персонализиран списък за сортиране. Тази част от формулата е:
| 1 | = МАТЧ (B3: B8, F3: F5,0) |
Като добавим стойностите от диапазоните на клетките, получаваме:
| 1 | = MATCH ({"Blue"; "Red"; "Yellow"; "Red"; "Blue"; "Yellow"}, {"Yellow"; "Blue"; "Red"}, 0) |
Функцията MATCH замества всички Екип имена с реда, в който се появяват в Персонализиран списък за сортиране, използвайки точно съвпадение на текста:
| 1 | ={2; 3; 1; 3; 2; 1} |
Този масив от стойности след това се използва като ред за сортиране от основната функция SORTBY:
| 1 | = СОРТБИ (B3: D8, {2; 3; 1; 3; 2; 1}) |
След това се произвежда сортираната таблица
Персонализиран списък за сортиране - Pre -Excel 365
Тъй като функцията SORTBY не е достъпна за потребителите на Excel преди версията на Excel 365, сега ще покажем как да изпълним същата задача, като използваме по -ръчен процес.
Вграденият инструмент за сортиране на Excel може да се използва за сортиране по Персонализиран списък за сортиране като изберете „Персонализиран списък…“ от опциите за подреждане в инструмента за сортиране:
Ново Персонализиран списък трябва да се добави като списък, разделен със запетая. В този пример добавяме:Жълто, синьо, червено '
The Персонализиран списък след това се показва в избора на ред за сортиране
След това таблицата с данни се сортира по Персонализиран списък стойности
Имайте предвид, че този персонализиран ред за сортиране се записва в работната книга и е достъпен за повторно използване. Ако не искате да добавите a Персонализиран списък в Excel, тогава вместо това може да се опита метод, използващ помощна колона.
Персонализиран списък за сортиране с помощна колона - Pre -Excel 365
В този следващ пример използваме функцията MATCH в помощна колона, за да дефинираме a Персонализиран ред за сортиране като се позовава на a Персонализиран списък за сортиране:
| 1 | = МАТЧ (B3, $ G $ 3: $ G $ 5,0) |
Обърнете внимание на използването на символа $, когато се позовавате на Персонализиран списък за сортиране. Моля, прочетете нашата статия за Заключване на клетъчни препратки, за да научите повече по тази тема.
Добавена е една помощна колона, може да се стартира процес на ръчно сортиране с помощта на Съветник за сортиране:
След това се получава таблицата, сортирана след Персонализиран ред за сортиране:
Ако са направени някакви промени в Екип или Персонализиран списък за сортиране стойности, тогава съветникът за сортиране ще трябва да бъде стартиран отново, за да актуализира реда за сортиране на таблицата с данни.
Персонализиран списък за сортиране с формула в Google Sheets
Функцията SORTBY не е налична в Google Sheets, но нейната функция SORT може да се използва вместо нея и е по -мощна от функцията SORT в Excel 365. Тя ни позволява да използваме персонализирани списъци за сортиране за динамично сортиране на диапазони от данни.
В този пример използваме функцията за сортиране на Google Sheets, за да вземем таблица с данни и да създадем динамичен масив, който сортира Екип стойности от a Персонализиран списък за сортиране:
| 1 | = SORT (B3: D8, MATCH (B3: B8, F3: F5,0), TRUE) |
Функцията СОРТИРАНЕ на Google Sheets сортира данни по посочените номера на колони или персонализирани поръчки за сортиране във входните данни и ни позволява да зададем дали редът на сортиране е възходящ или не
