Cum să alfabetizezi un tabel în excel. Lista derulantă legată în Excel în ordine alfabetică

Cum să faci o listă derulantăExcel alfabetic pentru a selecta un nume de familie după literă din alfabet? Când există multe nume de familie în lista derulantă, este convenabil să faceți un alfabet în lista derulantă și apoi, selectând litera dorită, selectați numele de familie. Pentru asta trebuie să faci listele derulante Excel legate.
De exemplu, pe prima foaie avem tabelul principal, în care vom insera o listă derulantă cu nume de familie.
Pe a doua foaie carti excel Să facem o listă de nume. Sortați această listă în ordine alfabetică. Lista este cam asta.
Cum să te descurci lista mare cu nume de familie duplicat o listă fără repetiții pentru o listă derulantă, vezi articolul „Crearea unei liste în Excel fără repetiții”.
Acum să dăm un nume acestei liste. Cum să atribuiți un nume unui interval, citiți articolul „Range în Excel”. Am numit gama - „Nume de familie”.
Pentru ca limitele intervalului să se schimbe automat la adăugarea sau scăderea numelor de familie, trebuie să le faceți dinamice. Consultați articolul „Pentru a redimensiona automat un tabel Excel”.
Acum trebuie să creăm o listă de litere care încep cu numele de familie din lista noastră.
Cum se scrie alfabetul în Excel cu o formulă.
Mai întâi, în coloana C, scriem alfabetul obișnuit. Acest lucru se poate face rapid cu o formulă. În celula C, scrieți formula.
=CHAR(192+STRING(Z1)-1)
192 este codul pentru litera A. Copiați formula în jos pe coloană la litera Y. Alfabetul este gata.
Acum, în coloana următoare trebuie să scrieți doar acele litere cu care încep numele din lista noastră. Aici ne va ajutaFormula matrice Excel. În celula D2, am scris o formulă atât de lungă.
=DACA EROARE(INDEX($C$2:$C$33,MIC(DACA(COUNTIF
(Nume de familie;$C$2:$C$33&"*")>0;STRING($C$2:$C$33);"");
STRING(Z1))-ROW($C$1));"")
Aceasta este o formulă matrice, p.e. apăsăm nu doar „Enter”, ci și combinația de taste „Shift” + „Ctrl” + „Enter”. Formula va fi în bretele. Trageți formula în jos pe coloană. Am prins așa.

Tot. Acum, în celula D1 selectăm o literă, iar în celula E1 va apărea o listă derulantă cu nume de familie numai pentru această literă. De exemplu. sau

Există o categorie utilizatorii Excel, care funcționează cu cărți care conțin, în loc de 3 coli standard, un număr mult mai mare dintre ele. Cineva din aceste cărți de deviz, ale căror numere sunt numele foilor, cineva are acte de muncă efectuate de antreprenori, cineva are consumul de combustibil al mașinilor taxi cu traseu fix, cineva are un ansamblu de calcule economice și cineva caracteristici electrice echipamente tehnologice înregistrate zilnic pe foaie separată(sau în general la fiecare oră). Ei bine, dacă foile unei astfel de cărți sunt sortate în prealabil măcar cumva, dar dacă nu? Chiar și o simplă căutare a foii dorite în carte devine departe de a fi o activitate distractivă. Așa că vreau să aliniez foile în ordine alfabetică, dar în standard excela nu există o astfel de posibilitate.

Vă prezint un simplu de utilizat supliment (macro) pentru Excel, care permite sortați foile registru de lucru Excel atat ascendent cat si descendent . Dacă cineva a întâlnit o sortare nu tocmai corectă, atunci când Sheet11, și nu Sheet2, urmează imediat Sheet1, nu vă faceți griji. În acest program de completare, această problemă este rezolvată, va sorta foile cu aceeași calitate, ale căror nume constau din litere, numere și o combinație de litere, numere, spații și simboluri. Și o va face chiar și cu cearșafuri ascunse. Suplimentul sortează corect și rapid foile cu nume care constau numai din numere, cum ar fi 1, 2, 3, 4, 5, precum și foile cu nume precum 1-1-1, 1-1-2, 1-1 -3.

Add-in-ul este lansat printr-un buton afișat în meniul Excel, atunci când este apăsat, utilizatorul vede o casetă de dialog prin care poate alege modul de sortare a foilor în ordine crescătoare sau descrescătoare. De asemenea este sisortați foile după culoarea filei și listele personalizate.

De asemenea, este posibil. Add-in-ul este ușor de instalat, iar pentru cei care nu au întâlnit încă instalarea add-in-urilor în Excel, există instrucțiuni pas cu pas. Dupa parerea mea asta alternativa demna drag and drop manual de foi.

V acest exemplu Să aruncăm o privire la cel mai rapid metode eficiente sortarea datelor în Excel după diverse criterii: în numere crescătoare sau descrescătoare, după date de la vechi la nou și de la nou la vechi, alfabetic de la A la Z, precum și invers ordine alfabetică.

Sortați rândurile după coloană în Excel

Figura de mai jos prezintă o selecție de informații din baza de date despre angajații companiei:

Să presupunem că trebuie să sortăm tabelul în funcție de coloana „Bonus”, în ordinea descrescătoare a sumelor. Pentru a rezolva această problemă, vom folosi cele mai simple instrumente de sortare (de bază) din Excel.

Mutați cursorul de la tastatură în orice celulă din coloana G (puteți chiar să mergeți la antetul coloanei G1). Și apoi selectați instrumentul: „HOME” - „Editare” - „Sort și Filtrare” - „Sort Descending”.

Ca urmare, datele au fost ordonate și organizate într-o anumită structură:

După cum s-a dovedit, angajatul „Eugene Prutkiy” a primit cel mai înalt premiu.

Atenţie! Lista de sortat nu trebuie să conțină celule îmbinate.

Notă. Instrumentul pentru sortarea de bază a intervalelor este disponibil și în fila: „DATE” - „Sortare în ordine crescătoare/descrescătoare”.

Și integrat în meniurile drop-down ale antetelor de tabel în modul de filtrare automată sau tabel inteligent:

De asemenea, încorporat în meniul contextual, care apare când faceți clic Click dreapta soareci:

Cum să eliminați sortarea în Excel

Dacă tabelul trebuie restabilit la forma sa originală, atunci înainte de fiecare sortare, creați o coloană de numerotare a rândurilor. În acest exemplu, aceasta este coloana A (Nr. p / p). De multe ori este necesar să readuceți un tabel la forma sa inițială după efectuarea unor sortări numeroase și complexe de date pe acesta în funcție de diferite coloane, criterii sau condiții. Apoi este suficient să sortați după coloana cu numere de rând, astfel încât tabelul să ia forma inițială.

Cum se sortează o coloană în Excel

Uneori trebuie să efectuați o sortare independentă numai într-o coloană a unui tabel fără a lega sau modifica alte coloane. De exemplu, coloana B este „Nume”. Această problemă poate fi rezolvată la fel de ușor:

1. Selectați întreaga coloană B făcând clic stânga pe antetul coloanei foii.
2. Selectați instrumentul: „HOME” - „Editare” - „Sortați și filtrați” - „Sortați descendent”.
3. În caseta de dialog apărută „Date detectate în interval nespecificat”, selectați opțiunea: „sortați în cadrul selecției specificate”. Și faceți clic pe butonul Sortare.

Rețineți că valorile celulelor din coloanele rămase rămân neschimbate:

Această metodă ar trebui utilizată numai ca auxiliar.

Sortare pe mai multe coloane în Excel

Cum se configurează sortarea în Excel? Să presupunem că vrem să sortăm angajații după data nașterii (de la cel mai vechi la cel mai nou) în funcție de fiecare oraș (locul nașterii). Pentru asta:

Tabelul a dobândit următoarea structură:

În primul rând, valorile sale sunt sortate în funcție de numele orașelor în ordine alfabetică. În raport cu fiecare oraș, datele de naștere sunt distribuite de la cel mai vechi la cel mai nou.

Dacă lucrăm doar cu un fragment de tabel în care nu există antete de coloană, ci doar un interval de celule cu valori curente, așa cum se arată în figura de mai jos:

Apoi, în caseta de dialog a instrumentului de sortare personalizată, ar trebui să dezactivați opțiunea „Datele mele au titluri de coloană”. Ca urmare, ca criterii pentru fiecare primă listă derulantă a secțiunii „Coloană” din fiecare nivel, se vor folosi titlurile coloanelor din foaia de lucru. Foaie Excel(Coloana A, Coloana B, C etc.). Și când toți parametrii sunt aplicați, toate rândurile și celulele intervalului original (fragment de tabel) vor fi incluse în sortare, care este rezultatul corect în acest caz.

Notă. Butonul pentru apelarea instrumentului pentru sortare personalizată este disponibil și în fila „DATE” - „Sort”.

Tabele de date de la care primim diferiți angajațiîn cele mai multe cazuri, acestea nu sunt raționalizate și este dificil să lucrezi cu ele fără organizare. Este necesară sortarea: valoarea textului alfabetic, numeric în ordine crescătoare și date de la cel mai vechi la cel mai nou. Sortarea manuală a tuturor coloanelor individual nu este cea mai bună soluție pentru această sarcină. Mai ales dacă sunt mai mult de o mie de coloane. Prin urmare, este mai bine să utilizați un încorporat special instrument Excel- sortare personalizata. Acest instrument sortează automat datele după mai multe coloane în același timp. Este atât de flexibil încât poate fi aplicat la aproape orice tip, cu orice nivel de complexitate.

În acest articol, vă voi arăta cum să sortați datele în Excel după mai multe coloane, după titlurile coloanelor în ordine alfabetică și după valori pe orice rând. Veți învăța și cum să sortați datele moduri non-standard atunci când sortarea alfabetică sau după valoare numerică nu este aplicabilă.

Cred că toată lumea știe să sorteze după o coloană în ordine alfabetică sau în ordine crescătoare/descrescătoare. Acest lucru se face cu un clic pe un buton. ȘI EU(A-Z) și Z-A(Z-A) în secțiune Editare fila (Editare). Acasă(Acasă) sau în secțiune Sortați și filtrați fila (Sortare și filtrare). Date(Date):

Cu toate acestea, sortarea în Excel are mult mai multe opțiuni personalizabile și moduri de operare care nu sunt atât de evidente, dar pot fi foarte convenabile:

Sortare pe mai multe coloane

Vă voi arăta cum să sortați datele după două sau mai multe coloane în Excel. Funcționarea instrumentului este prezentată în Exemplu Excel 2010 - aceasta este versiunea instalată pe computerul meu. Dacă utilizați o versiune diferită a aplicației, nu ar trebui să aveți probleme, deoarece sortarea funcționează în același mod în Excel 2007 și Excel 2013. Diferența se vede doar în culoare casete de dialogși forma butoanelor. Asadar, haideti sa începem...

Sortarea datelor după mai multe coloane în Excel nu a fost deloc dificilă, nu? Cu toate acestea, în caseta de dialog Triere(Sortează) minciuni semnificativ mai multe posibilitati. Mai târziu, în acest articol, vă voi arăta cum să sortați după rând în loc de coloană și cum să sortați datele pe o foaie de lucru în ordine alfabetică, după titlurile coloanelor. De asemenea, veți învăța cum să sortați datele în moduri non-standard atunci când sortarea alfabetică sau după valoare numerică nu este aplicabilă.

Sortați datele în Excel după titlurile de rând și de coloană

Cred că 90% din timp Excel sortează datele după valoare într-una sau mai multe coloane. Cu toate acestea, uneori nu există seturi simple datele să fie sortate pe rând (orizontal), adică reordonați coloanele de la stânga la dreapta pe baza titlurilor coloanei sau a valorilor dintr-un anumit rând.

Iată o listă de camere furnizate de dealerul local sau descărcate de pe Internet. Lista conține o varietate de caracteristici, specificații și prețuri și arată cam așa:

Trebuie să sortăm această listă de camere în funcție de parametrii care sunt cei mai importanți pentru noi. De exemplu, în primul rând, să sortăm după numele modelului:

Ca rezultat al sortării, ar trebui să obțineți ceva de genul acesta:

În exemplul nostru, sortarea după titlurile de coloană nu are o valoare practică serioasă și se face doar pentru a vă arăta cum funcționează. În același mod, putem sorta lista noastră de camere după rândul care specifică dimensiuni, rezoluție, tip de senzor sau orice alt parametru pe care îl considerăm mai important. Să facem un alt fel, de data aceasta după preț.

Sarcina noastră este să repetăm ​​pașii 1 - 3 descriși mai sus, apoi la pasul 4, în loc de linie 1 alege o linie 4 , care conține prețurile cu amănuntul (Prețul cu amănuntul). Ca rezultat al sortării, tabelul va arăta astfel:

Vă rugăm să rețineți că datele au fost sortate nu numai în rândul selectat. Coloanele întregi sunt schimbate, dar datele nu sunt amestecate. Cu alte cuvinte, captura de ecran de mai sus este o listă de camere, sortate de la cele mai ieftine la cele mai scumpe.

Sper că acum este clar cum funcționează sortarea după rând în Excel. Dar ce se întâmplă dacă datele noastre trebuie sortate într-o ordine non-alfabetică și non-crescător/descrescător?

Sortare aleatorie (listă personalizată)

Dacă trebuie să aranjați datele într-o ordine specială (nu în ordine alfabetică), atunci puteți utiliza listele personalizate încorporate în Excel sau puteți crea propriile liste. Cu liste personalizabile încorporate, puteți sorta după zile ale săptămânii sau luni ale anului, de exemplu. Microsoft Excel oferă două tipuri de liste gata- cu nume prescurtate și complete.

Să presupunem că avem o listă de treburi săptămânale prin casă și vrem să le sortăm după ziua săptămânii sau după importanță.

Gata! Acum treburile casnice sunt ordonate în funcție de ziua săptămânii:

Cometariu: Dacă intenționați să modificați aceste date, rețineți că datele noi adăugate sau cele existente modificate nu vor fi sortate automat. Pentru a reface sortarea, faceți clic pe butonul Repeta(Reaplicați) secțiunea Sortați și filtrați fila (Sortare și filtrare). Date(Date).

După cum puteți vedea, sortarea datelor în Excel în funcție de o listă personalizată nu este deloc o sarcină dificilă. Un alt truc pe care trebuie să-l învățăm este sortarea datelor în propria noastră listă personalizată.

Sortați datele după propria listă personalizată

Tabelul nostru are o coloană prioritate- indică prioritățile sarcinilor. Pentru a-l folosi pentru a vă organiza sarcinile săptămânale de la cele mai importante la cele mai puțin importante, urmați acești pași:

Repetați pașii 1 și 2 din exemplul anterior. Când se deschide caseta de dialog Liste(Liste personalizate), în coloana cu același nume din stânga, faceți clic LISTA NOUĂ(LISTA NOUĂ) și completați valorile dorite camp Listează articole(Listă intrări). Introduceți cu atenție elementele listei dvs. în ordinea exactă în care ar trebui să fie sortate.

Clic Adăuga(Adăugați), iar lista pe care ați creat-o va fi adăugată la cele existente. Faceți clic pe următorul O.K.

Iată cum arată lista noastră de treburi casnice, sortate după importanță: