Lajittelu sukunimen mukaan Excelissä (Helppo opas)

Katso video - Kuinka lajitella sukunimen mukaan Excelissä

Jos käytät nimitietojoukkoja, niiden lajittelu on yksi yleisimmistä tehtävistä, jotka sinun on tehtävä usein.

Tietojen lajitteleminen aakkosjärjestyksessä koko nimen perusteella, jossa Excel käyttää nimen ensimmäistä merkkiä lajitteluun.

Mutta entä jos haluat lajittele tiedot sukunimen mukaan Excelissä?

Vaikka se ei olekaan niin yksinkertaista, se voidaan silti tehdä (paljon riippuu myös nimien tietojen rakenteesta).

Riippumatta siitä, mitä menetelmää käytät, sinun täytyy jotenkin poimia sukunimi koko nimestä ja laittaa se erilliseen sarakkeeseen. Tämän sarakkeen avulla voit lajitella tiedot sukunimen mukaan aakkosjärjestykseen.

Tässä Excel -opetusohjelmassa näytän sinulle, kuinka lajitella sarake nimillä sukunimen perusteella.

Aloitetaan siis!

Pura ja lajittele sukunimen mukaan Etsi ja korvaa -toiminnon avulla

Ensimmäinen askel sukunimen mukaan lajittelussa on saada sukunimi erilliseen sarakkeeseen.

Voit tehdä sen korvaamalla kaiken ennen sukunimeä tyhjällä, niin että sinulla on vain sukunimi jäljellä.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat lajitella nämä tiedot aakkosjärjestykseen sukunimen avulla.

Alla on ohjeet sukunimen mukaan lajitteluun:

1. Valitse otsikko sisältävä tietojoukko (tässä esimerkissä se olisi A1: A10)
2. Kopioi se viereiseen sarakkeeseen (jos viereinen sarake ei ole tyhjä, lisää uusi sarake ja kopioi sitten nämä nimet)
3. Nimeä kopioidun sarakkeen otsikko uudelleen. Tässä esimerkissä nimi on "Sukunimi"
4. Valitse kaikki kopioidut nimet (älä valitse otsikkoa)
5. Pidä Control -näppäintä painettuna ja paina sitten H -näppäintä. Tämä avaa Etsi ja korvaa -valintaikkunan.
6. Kirjoita Etsi mitä -kenttään * (tähtimerkki ja välilyönti)
7. Jätä Korvaa kenttä tyhjäksi
8. Napsauta Korvaa kaikki. Tämä korvaa heti kaikki etunimet ja sinulle jää vain sukunimet.

Yllä olevat vaiheet säilyttäisivät sukunimen ja poistaisivat kaiken sen edestä. Tämä toimii hyvin, vaikka sinulla olisi keskimmäisiä nimiä tai etuliitteitä (kuten herra tai rouva).

Kun sukunimet ovat viereisessä sarakkeessa, voit helposti lajitella tietojoukon (myös täydet nimet) aakkosjärjestykseen sukunimen perusteella.

Alla on ohjeet sukunimen mukaan lajitteluun:

1. Valitse koko tietojoukko otsikoilla (mukaan lukien täydelliset nimet ja poimitut sukunimet). Voit sisällyttää myös muita sarakkeita, jotka haluat lajitella, yhdessä nimien kanssa
2. Napsauta Data -välilehteä
3. Napsauta Lajittele
4. Varmista, että Lajittele -valintaikkunassa on Omat tiedot -otsikot.
5. Valitse Lajitteluperuste -vaihtoehdosta sen sarakkeen nimi, jolla on vain sukunimi
6. Valitse Lajittele päällä -kohdasta Solun arvot
7. Valitse Tilaus -vaihtoehdosta "A - Z"
8. Napsauta OK

Yllä olevat vaiheet lajittelisivat koko valitun tietojoukon sukunimen perusteella.

Kun olet valmis, voit poistaa sarakkeen, jolla on sukunimi.

Pro -vinkki: Jos luulet tarvitsevasi alkuperäiset tiedot milloin tahansa, sinulla on oltava tapa poistaa tämä tietojoukko. Voit tehdä tämän viereisessä sarakkeessa (vasemmalla tai oikealla) sarjanumerot ennen lajittelua. Jos tarvitset alkuperäiset tiedot takaisin, saat ne lajittelemalla numeroiden perusteella.

Pura ja aakkostele sukunimen mukaan kaavan avulla

Vaikka yllä näkyvä menetelmä (Etsi ja korvaa) on se, mitä haluan saada kaikki sukunimet ja lajitella sen perusteella, yksi rajoitus on, että tuloksena olevat tiedot ovat staattisia.

Tämä tarkoittaa, että jos lisään luetteloon lisää nimiä, minun on tehtävä sama prosessi uudelleen saadakseni sukunimet.

Jos et halua tätä, voit lajitella tiedot sukunimien mukaan kaavamenetelmän avulla.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko.

Alla on kaava, joka purkaa sukunimen koko nimestä:

= OIKEA (A2, LEN (A2) -FIND ("", A2))

Yllä oleva kaava perustuu kuvioon, jolla on koko nimi (joka sisältää vain etu- ja sukunimen tässä esimerkissä). Kuvio on, että etu- ja sukunimen välissä on välilyönti.

FIND -toimintoa käytetään välilyönnin sijainnin selvittämiseen. Tämä arvo vähennetään sitten nimen kokonaispituudesta, jotta saadaan sukunimen merkkien kokonaismäärä.

Tätä arvoa käytetään sitten OIKEA -funktiossa sukunimen saamiseksi.

Kun sinulla on sukunimi -sarake, voit lajitella nämä tiedot (tämä käsitellään yksityiskohtaisesti ensimmäisessä menetelmässä).

Yllä oleva kaava toimii, kun sinulla on vain etu- ja sukunimet.

Mutta entä jos sinulla on myös toinen nimi. Tai onko tervehdys nimen edessä (kuten herra tai rouva)

Tässä tapauksessa sinun on käytettävä alla olevaa kaavaa:

= OIKEA (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))

Yllä oleva kaava etsii viimeisen välilyönnin sijainnin ja purkaa sen jälkeen sukunimen.

Suosittelen käyttämään toista kaavaa kaikissa tapauksissa, ja se on tyhmämpi ja pystyy käsittelemään kaikki tapaukset (kunhan sukunimi on nimen lopussa).

Huomautus: Nämä kaksi kaavaa tukevat ehtoa, että jokaisen nimielementin välissä on vain yksi välilyönti. Jos kahta välilyöntiä tai eteen-/taaksepäin jääviä välilyöntejä, tämä kaava antaa vääriä tuloksia. Tällaisessa tapauksessa on parasta käyttää TRIM -toimintoa päästäkseen ensin eroon edessä olevista, jäljessä olevista ja kaksinkertaisista välilyönneistä ja käytä sitten yllä olevaa kaavaa.

Vaikka tämä saattaa tuntua monimutkaiselta menetelmältä, kaavan käytön etu on se, että se tekee tuloksista dynaamisia. Jos lisäät luetteloon lisää nimiä, sinun tarvitsee vain kopioida kaava ja se antaa sinulle sukunimen.

Tekstin käyttäminen sarakkeisiin

Teksti sarakkeisiin on jälleen yksinkertainen ja helppo tapa jakaa solut Excelissä.

Voit määrittää erottimen (kuten pilkun tai välilyönnin) ja käyttää sitä solun sisällön jakamiseen. Kun olet jakanut elementit erillisiin sarakkeisiin, voit käyttää sukunimeä sisältävää saraketta tietojen aakkosjärjestykseen.

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Alla on vaiheet tekstin käyttämiseksi sarakkeessa lajitteluun sukunimen mukaan:

1. Valitse sarake, jolla on nimi (paitsi otsikko)
2. Napsauta Data -välilehteä
3. Napsauta Datatyökalut -ryhmässä Teksti sarakkeisiin -vaihtoehtoa. Tämä avaa ohjatun Teksti sarakkeisiin -toiminnon
4. Valitse Ohjatun tekstin muuntaminen sarakkeiksi -toiminnon vaiheessa 1 Eroteltu ja napsauta Seuraava
5. Valitse vaiheessa 2 erotin 'Välilyönti' (ja poista kaikki muut valinnat, jos ne on valittu) ja napsauta sitten Seuraava -painiketta.
6. Valitse vaiheessa 3 etunimen sarake Tietojen esikatselussa ja valitse sitten Älä tuo sarakkeita (ohita). Tämä varmistaa, että etunimi ei ole osa tulosta ja saat vain sukunimen.
7. Vaihda myös vaiheessa 3 kohdesolu alkuperäisen datan vieressä olevaan soluun. Tämä varmistaa, että saat sukunimen erikseen ja alkuperäisten nimien tiedot ovat ehjät.
8. Napsauta Valmis

Kun olet saanut tuloksen, voit lajitella sukunimen mukaan.

Voit myös tekstittää sarakkeisiin erottaaksesi etu- ja sukunimet, kun erotin on pilkulla.

Flash Fillin käyttö

Toinen nopea ja nopea tapa saada sukunimet on käyttää Flash Fill -ominaisuutta.

Flash Fill otettiin käyttöön Excel 2013: ssa ja se auttaa käsittelemään tietoja tunnistamalla kuvioita. Jotta tämä toimisi, sinun on näytettävä Flash Fill -tehtävä odottamasi tulos pari kertaa.

Kun se tunnistaa mallin, se tekee nopeasti muun työn puolestasi.

Oletetaan, että sinulla on alla olevien nimien tietojoukko.

Alla on vaiheet Flash Fillin käyttämiseksi sukunimen saamiseksi ja lajittelemiseksi sen avulla:

1. Kirjoita soluun B2 teksti "Maury". Tämä on tulos, jota odotat solussa.
2. Siirry seuraavaan soluun ja kirjoita nimen sukunimi viereiseen soluun (Elliot tässä esimerkissä).
3. Valitse molemmat solut
4. Vie hiiri valinnan oikean alakulman päälle. Huomaat, että kohdistin muuttuu plus -kuvakkeeksi.
5. Kaksoisnapsauta sitä (tai napsauta ja vedä sitä alas). Tämä antaa sinulle jonkin tuloksen soluissa (ei todennäköisesti ole haluamasi tulos)
6. Napsauta Automaattisen täytön asetukset -kuvaketta.
7. Napsauta Flash Fill

Tämä antaa sinulle tuloksen, joka todennäköisesti on kaikkien solujen sukunimet.

Sanon todennäköisesti, koska Flash Fill ei ehkä toimi joissain tapauksissa. Koska se riippuu kuvion tunnistamisesta, se ei välttämättä pysty tekemään sitä aina. Tai joskus kuvio, jonka se tulkitsee, ei ehkä ole oikea.

Tällaisissa tapauksissa sinun on syötettävä odotettu tulos yhteen tai kahteen muuhun soluun ja suoritettava sitten vaiheet 4-7.

Kun sarakkeessa on kaikki sukunimet, voit lajitella tiedot näiden sukunimien perusteella.

Joten nämä ovat neljä eri tapaa, joilla voit lajitella tiedot sukunimen mukaan. Paras tapa olisi käyttää Etsi ja korvaa -tekniikkaa, mutta jos haluat tehdä tuloksista dynaamisia, kaavamenetelmä on oikea tapa edetä.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.