Excel on hämmastav tööriist andmete salvestamiseks ja analüüsimiseks. Ja sageli peate tegelema teksti andmetüüpidega, nagu nimed, piirkonnad, osakonnad või tootenimed.
Sellistel juhtudel on hea teada, kuidas tekstiandmetega manipuleerida ja soovitud tulemust saada.
Üks levinumaid ülesandeid, mida enamik Exceli kasutajaid tegema peab, on nimede andmestikuga töötamine. Sageli leiate, et ees- ja perekonnanimi on eraldi veergudes ning teil võib tekkida vajadus neid ees- ja perekonnanimesid kombineerida ning need lahtrisse koondnimeks hankida.
Selles Exceli õpetuses näitan teile Excelis ees- ja perekonnanime kombineerimiseks mitmeid erinevaid viise.
Seda saate hõlpsalt teha, kasutades lihtsaid valemeid (nt Concatenate või TextJoin) ja selliseid funktsioone nagu Flash Fill ja Power Query
4 võimalust ees- ja perekonnanime ühendamiseks Excelis
Oletame, et teil on allpool näidatud andmekogum ja soovite ühendada eesnime veerus A ja perekonnanime veerus B.
Vaatame mõningaid viise, kuidas seda teha.
Funktsiooni CONCATENATE (või võimendusliiva) kasutamine
Erinevate lahtrite erinevate tekstistringide kombineerimine on Excelis üsna lihtne. Excelis on sisseehitatud Concatenate valem, mis on loodud ainult selleks otstarbeks.
Allpool on valem, mis ühendab ees- ja perekonnanime (eraldatud tühikutega):
= CONCAT (A2, "", B2)
CONCAT on uus funktsioon, mis võeti kasutusele programmis Excel 2016 ja mis asendab funktsiooni CONCATENATE. Kuid praeguseks on mõlemad funktsioonid jätkuvalt saadaval ja saate mõlemat funktsiooni kasutada.
Allpool on CONCATENATE valem, kui soovite seda kasutada:
= CONCATENATE (A2, "", B2)
Ülaltoodud valem võtab lihtsalt ees- ja perekonnanime ning ühendab selle. Ja kuna ma tahan, et need eraldataks tühikutega, olen teise argumendina kasutanud ”” (tühik kahekordse jutumärgiga).
Ühendamise tegemiseks võite kasutada ka ampersandi operaatorit.
Eeldades, et teil on sama andmekogum, saate ees- ja perekonnanime ühendamiseks kasutada järgmist valemit:
= A2 & "" & B2
Ampersandi operaator ühendab teksti enne ja pärast seda. Ülaltoodud näites olen ühendanud kolm osa - eesnimi, tühik ja perekonnanimi.
Nüüd, kui olete kontseptsioonist aru saanud, saate soovi korral nimesid kombineerida erinevates vormingutes. Näiteks võite soovida, et nimede vahelise tühiku asemel oleks perekonnanimi ja seejärel eesnimi või koma.
Kui soovite ainult kombineeritud nime ja soovite vabaneda ees- ja perekonnanimest, peaksite esmalt teisendama valemi väärtused staatilisteks väärtusteks. Kui olete valmis, saate ees- ja perekonnanime eemaldada/kustutada.
Funktsiooni TEXTJOIN kasutamine
TEXTJOIN on funktsioon, mis on saadaval rakendustes Excel2021-2022 ja Office 365.
Kui teil on sellele funktsioonile juurdepääs, on parem seda kasutada lahtrite ja veergude ühendamiseks (kuna see on palju parem kui ülaltoodud CONCATENATE ja ampersand meetodid).
Oletame, et teil on allpool näidatud andmekogum ja soovite ees- ja perekonnanime ühendada.
Allpool on valem selle tegemiseks:
= TEXTJOIN ("", TRUE, A2: B2)
Ülaltoodud funktsioon TEXTJOIN sisaldab kolme argumenti.
- Eraldaja, mis on selles näites kahekordse jutumärgiga tühik (kuna me tahame, et ees- ja perekonnanimi eraldatakse tühikutega)
- Loogiline väärtus, kus tõene tähendab, et tühjade lahtrite olemasolul ignoreerib valem seda
- Vahemik, milles on lahtrid, mida soovite kombineerida
See on kiirem kui tavaline ühendamise valem ning seda on ka lihtsam luua ja hallata. Seega, kui teil on Excelis juurdepääs funktsioonile TEXTJOIN, on parem seda kasutada mis tahes muu valemi asemel.
Flash Filli kasutamine
Flash Fill on nutikas funktsioon, mis püüab mustrist aru saada ja teile tulemuse anda.
Las ma selgitan, kuidas see toimib.
Oletame, et teil on allpool näidatud andmekogum ja soovite ees- ja perekonnanime ühendada.
Allpool on toodud sammud, mida saate Flash Filli abil teha
- Lahtrisse C2 sisestage soovitud tulemus. Meie näites oleks see "Bobby Baker"
- Lahtris C3 alustage oodatud tulemuse tippimist. Märkate, et Excel näitab oodatud tulemust kõigis lahtrites (helehallis tekstis). See on Flash Fill tegevuses
- Vajutage sisestusklahvi
Ülaltoodud sammud täidaksid koheselt kõik lahtrid kombineeritud nimega.
Mõnel juhul on võimalik, et te ei näe välgu täitmist, kui sisestate teise lahtrisse.
Ärge muretsege, seda juhtub mõnikord.
Sellise stsenaariumi korral leiate allpool Flash Filli toimimiseks muud sammud.
- Lahtrisse C2 sisestage soovitud tulemus. Meie näites oleks see "Bobby Baker"
- Valige valige C3
- Klõpsake vahekaarti Avaleht
- Klõpsake rühmas Redigeerimine ikooni Täida
- Klõpsake nuppu Flash Fill
Ülaltoodud sammud valiksid koheselt mustri ülaltoodud lahtrist ja täidaksid kogu veeru kombineeritud nimega.
Kui välklambi täitmine ei suuda õiget mustrit üles võtta ja annab vale tulemuse, täitke kaks lahtrit käsitsi ja tehke seejärel ülaltoodud toimingud.
Võite kasutada ka kiirklahvi Control + E. täitmiseks, kasutades Flashi täitmist.
Ees- ja perekonnanime (või isegi ees-, kesk- ja perekonnanime) kombineerimine on lihtne toiming, millega Flash Fill saab hõlpsasti hakkama.
Pidage meeles, et välktäide pole täiuslik. See toimib mustrite tuvastamise ja sama mustri abil veeru kõigi lahtrite täitmiseks. Kuigi see töötab kõige tõenäolisemalt ootuspäraselt, on hea mõte Flash Filli tulemust veelkord kontrollida.
Power Query kasutamine
Power Query on hämmastav tööriist, mida kasutati andmete ekstraheerimiseks ja teisendamiseks.
Seda saab kasutada ka veergude kiireks ühendamiseks ning ees- ja perekonnanime ühendamiseks.
Power Query toimimiseks peavad teie andmed olema Exceli tabelis (või vähemalt nimega vahemikus).
Selle õpetuse jaoks teisendan 1. ja perekonnanimega andmekogumi Exceli tabeliks.
Oletame, et teil on allpool näidatud andmekogum ja soovite täisnime saamiseks need kaks veergu ühendada.
Allpool on toodud sammud andmete teisendamiseks Exceli tabeliks.
- Valige andmekogus suvaline lahter
- Klõpsake vahekaarti Lisa
- Klõpsake tabeliikooni
- Veenduge dialoogiboksis Tabeli loomine, et vahemik on õige
- Klõpsake nuppu OK
Ülaltoodud sammud teisendaksid andmevahemiku Exceli tabeliks.
Nüüd vaatame, kuidas Power Query abil ees- ja perekonnanime ühendada:
- Valige tabelis mis tahes lahter
- Klõpsake vahekaarti Andmed
- Klõpsake rühmas Andmete hankimine ja teisendamine suvandit „Lehelt”. See avab Power Query redaktori
- Veenduge Power Query Editoris, et vasakul paanil oleks valitud parem tabel. Kui teil on ainult üks tabel, näete vasakul paanil ainult ühte valikut
- Valige liidetavad veerud (hoidke all juhtklahvi ja seejärel klõpsake veeru päisel veeru valimiseks)
- Paremklõpsake ja seejärel klõpsake suvandit „Ühenda veerud”
- Avanevas dialoogiboksis Veergude ühendamine valige eraldajaks tühik (rippmenüüst)
- Sisestage uue ühendatud veeru nimi. Ma lähen "täisnimega"
- Klõpsake nuppu OK. See eemaldaks kaks olemasolevat veergu ja asendaks need uue täisveeruga.
- Klõpsake vahekaarti Fail ja seejärel nuppu Sule ja laadi.
Ülaltoodud toimingud sisestaksid töövihikusse uue töölehe koos tabeliga, millel on üks täisnimega veerg.
Kui soovite nii algseid veerge kui ka uut täisnime sisaldavat veergu, siis veergude valimise ja nende ühendamise asemel valige esmalt Power Query redaktoris vahekaart Lisa veerg, seejärel veerud ja seejärel klõpsake nuppu Ühenda Veerud. See lisab uue veeru, millel on täisnimi.
Võrreldes valemimeetodite ja Flash Filliga on Power Query kindlasti natuke pikem.
Kuid selle meetodi kasutamise eeliseks on see, et kui teie esialgsed andmed muutuvad, saate päringut kiiresti värskendada ja teie saadud andmeid värskendatakse automaatselt.
Samuti kasutatakse Power Queryd laialdaselt erinevate tabelite ja mitme töölehe ja töövihiku andmete ühendamiseks. Seega, kui teil on nimeandmed, mida soovite ühendada, on see üks samm teie juba olemasolevas energiapäringu töövoos.
Selles õpetuses olen käsitlenud, kuidas seda teha ühendage Excelis ees- ja perekonnanimi. Kuid kui teil on ees-, keskmine ja perekonnanimi, saate seda teha samade meetoditega.
Loodan, et leidsite selle õpetuse kasulikuks.
