Descărcați exemplul de registru de lucru
Acest tutorial va arăta cum să sortați dinamic datele printr-o comandă de sortare personalizată în Excel și Foi de calcul Google.
Funcția SORTBY în Excel 365
Funcția SORTBY produce o matrice dinamică de date sortate.
În acest exemplu, folosim funcția SORTBY pentru a lua un tabel de date și a produce o matrice dinamică care o sortează după Echipă în urma unui Lista de sortare personalizată:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Notă: Funcția SORTBY este scrisă numai în celula H3. Restul tabelului se va completa automat.
Tabelul sortat este dinamic, ceea ce înseamnă că, dacă vreunul dintre Echipă sau Numele jucatorului valorile se schimbă sau se înregistrează sau sunt adăugate / șterse, apoi tabelul de ieșire va redimensiona și sorta automat.
Notă: Funcția SORTBY nu este disponibilă în Excel 2022 sau anterior. Citiți mai jos pentru opțiunile de sortare disponibile pentru versiunile anterioare de Excel.
Pentru a explica modul în care funcționează această formulă, să o împărțim în pași:
Formula noastră finală este:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
În primul rând, definim tabelul de date care trebuie sortat. Acesta va fi B3: D8.
În continuare, trebuie să definim ordinea de sortare. Vom folosi funcția MATCH pentru a găsi ordinea în care fiecare Echipă numele apare în Listă de sortare personalizată. Această parte a formulei este:
| 1 | = MATCH (B3: B8, F3: F5,0) |
Adăugând valorile din intervalele de celule, obținem:
| 1 | = MATCH ({"Albastru"; "Roșu"; "Galben"; "Roșu"; "Albastru"; "Galben"}, {"Galben"; "Albastru"; "Roșu"}, 0) |
Funcția MATCH înlocuiește toate Echipă nume cu ordinea în care apar în Listă de sortare personalizată, folosind o potrivire exactă a textului:
| 1 | ={2; 3; 1; 3; 2; 1} |
Această matrice de valori este apoi utilizată ca ordine de sortare de către funcția SORTBY principală:
| 1 | = SORTBY (B3: D8, {2; 3; 1; 3; 2; 1}) |
Apoi se produce masa sortată
Listă de sortare personalizată - Pre-Excel 365
Deoarece funcția SORTBY nu este disponibilă pentru utilizatorii Excel înainte de versiunea Excel 365, vom arăta acum cum să efectuați aceeași sarcină folosind un proces mai manual.
Instrumentul încorporat de sortare Excel poate fi folosit pentru a sorta după un Listă de sortare personalizată selectând „Listă personalizată …” din opțiunile de sortare din instrumentul de sortare:
Un nou Listă personalizată trebuie adăugat ca o listă separată prin virgulă. În acest exemplu, adăugăm: „Galben, albastru, roșu ”
The Listă personalizată este apoi afișat în selecția ordinii de sortare
Tabelul de date este apoi sortat după Listă personalizată valori
Rețineți că această comandă de sortare personalizată este salvată în registrul de lucru și este disponibilă pentru a fi utilizată din nou. Dacă nu doriți să adăugați un Listă personalizată la Excel, atunci se poate încerca o metodă care utilizează o coloană de ajutor.
Listă de sortare personalizată cu coloană de ajutor - Pre-Excel 365
În acest exemplu următor, folosim funcția MATCH într-o coloană de ajutor pentru a defini un Comandă de sortare personalizată referindu-se la a Lista de sortare personalizată:
| 1 | = MATCH (B3, $ G $ 3: $ G $ 5,0) |
Rețineți utilizarea simbolului $ atunci când faceți referire la Lista de sortare personalizată. Vă rugăm să citiți articolul nostru despre blocarea referințelor celulei pentru a afla mai multe despre acest subiect.
Odată ce coloana de ajutor a fost adăugată, un proces de sortare manuală poate fi rulat folosind Expertul de sortare:
Aceasta produce apoi tabelul sortat după Comandă de sortare personalizată:
Dacă se fac modificări la Echipă sau Listă de sortare personalizată valori, atunci expertul de sortare va trebui relansat pentru a actualiza ordinea de sortare a tabelului de date.
Listă de sortare personalizată cu formulă în Foi de calcul Google
Funcția SORTBY nu este disponibilă în Foi de calcul Google, dar funcția SORT poate fi utilizată în schimb și este mai puternică decât funcția SORTBY în Excel 365. Ne permite să folosim liste de sortare personalizate pentru a sorta dinamic intervalele de date.
În acest exemplu, folosim funcția SORTS Sheets Google pentru a lua un tabel de date și a produce o matrice dinamică care sortează Echipă valori de a Listă de sortare personalizată:
| 1 | = SORT (B3: D8, MATCH (B3: B8, F3: F5,0), ADEVĂRAT) |
Funcția SORGE Foi de calcul Google sortează datele după numerele de coloane specificate sau ordinele de sortare personalizate din datele de intrare și ne permite să stabilim dacă ordinea de sortare este crescătoare sau nu
