V tem članku se bomo naučili, kako združiti ali združiti v Excelu.
Kaj je združevanje v Excelu?
Na splošno obstajata dva načina združevanja podatkov v Excelovih preglednicah:
Združite celice
Združite vrednosti celic
CONCATENATION v Excelu
Kako uporabljati funkcijo CONCATENATE Excel
Funkcija CONCATENATE v Excelu je zasnovana tako, da združuje različne kose besedila skupaj ali združuje vrednosti iz več celic v eno celico.
Sintaksa programa Excel CONCATENATE je naslednja:
| POVEZAVAJTE (besedilo1, [besedilo2],…) |
Kje besedilo je besedilni niz, sklic na celico ali vrednost, ki temelji na formuli.
Primeri:
Združevanje vrednosti več celic
Najpreprostejša formula CONCATENATE za združevanje vrednosti celic A1 in B1 je naslednja:
| = ZDRUŽEN (A1, B1) |
Upoštevajte, da bodo vrednosti povezane brez ločevalnika, kot je v drugi vrstici na spodnjem posnetku zaslona.
Če želite ločiti vrednosti s presledkom, vnesite "" v drugi argument, kot v 3. vrstico na spodnjem posnetku zaslona.
| = ZDRUŽITE (A1, "", B1) |
Če želite ločene združene vrednosti z drugimi ločevalniki, kot so vejica, presledek ali poševnica, glejte formule Excel CONCATENATE s posebnimi znaki.
Združevanje besedilnega niza in vrednosti celice
Ni razloga, da bi bila funkcija Excel CONCATENATE omejena samo na združevanje vrednosti celic. Uporabite ga lahko tudi za združevanje različnih besedilnih nizov, da bo rezultat bolj smiseln. Na primer:
| = CONCATENATE (A1, "", B1, "dokončano") |
Zgornja formula obvesti uporabnika, da je določen projekt zaključen, kot je v drugi vrstici na spodnjem posnetku zaslona. Upoštevajte, da pred besedo »dokončano« dodamo presledek za ločevanje združenih nizov besedila.
Seveda lahko dodate besedilni niz tudi na začetku ali sredi formule Concatenate:
| = CONCATENATE ("Glej", A1, "", B1) |
Med kombiniranimi vrednostmi se doda presledek (""), tako da se rezultat prikaže kot "projekt 1" in ne kot "projekt1".
Združevanje besedilnega niza in vrednosti, izračunane s formulo. Da bi bil rezultat, ki ga vrne neka formula, bolj razumljiv za vaše uporabnike, ga lahko združite z besedilnim nizom, ki pojasnjuje, kakšna je dejansko vrednost.
Na primer, za vrnitev trenutnega datuma lahko uporabite naslednjo formulo:
| = CONCATENATE ("Danes je", TEXT (TODAY (), "dd-mmm-yy")) |
Uporaba CONCATENATE v Excelu - stvari, ki si jih morate zapomniti
Da bi zagotovili, da vaše formule CONCATENATE vedno dajejo pravilne rezultate, ne pozabite na naslednja preprosta pravila:
Excel CONCATENATE funkcija za delovanje potrebuje vsaj en argument "besedilo".
V eni sami formuli CONCATENATE lahko združite do 255 nizov, skupaj 8.192 znakov.
Rezultat funkcije CONCATENATE je vedno besedilni niz, tudi če so vse izvorne vrednosti številke.
Excel CONCATENATE ne prepozna nizov. Vsako referenco celice je treba navesti ločeno. Na primer, namesto = CONCATENATE (A1: A3) napišite = CONCATENATE (A1, A2, A3).
Če je vsaj en argument funkcije CONCATENATE neveljaven, formula vrne #VALUE! napaka.
"&" za združevanje nizov v Excelu
V Microsoft Excelu, & operator je še en način združevanja celic. Ta metoda je v mnogih scenarijih zelo priročna, ker je vnos znaka ampersand (&) veliko hitrejši kot vnos besede "concatenate" 🙂
Podobno kot funkcija CONCATENATE lahko uporabite "&" v Excelu za združevanje različnih besedilnih nizov, vrednosti celic in rezultatov, ki jih vrnejo druge funkcije.
Primeri formule Excel "&"
Če želimo operater združevanja videti v akciji, prepišemo zgoraj obravnavane formule CONCATENATE:
Združite vrednosti v A1 in B1:
= A1 in B1
Vrednosti v A1 in B1 združite s presledkom:
= A1 & "" & B1
Združite vrednosti v A1, B1 in besedilni niz:
= A1 & B1 & "zaključeno"
Združite niz in rezultat funkcije TEXT / TODAY:
= "Danes je" & TEXT (TODAY (), "dd-mmm-yy")
Kot je prikazano na spodnjem posnetku zaslona, funkcija CONCATENATE in operater "&" vrneta enake rezultate:
Operater Excel "&" v primerjavi s funkcijo CONCATENATE
Mnogi uporabniki se sprašujejo, kateri je učinkovitejši način združevanja nizov v Excelu - funkcija CONCATENATE ali operator "&".
Edina bistvena razlika med operatorjema CONCATENATE in "&" je omejitev 255 nizov funkcije Excel CONCATENATE in ni nobenih omejitev pri uporabi znaka &. Razen tega med tema dvema metodama združevanja ni nobene razlike, prav tako ni nobene razlike v hitrosti med formulama CONCATENATE in "&".
In ker je 255 res velika številka in v resničnih opravilih nekomu skoraj nikoli ne bo treba združiti toliko strun, se razlika nanaša na udobje in enostavnost uporabe. Nekaterim uporabnikom se zdijo formule CONCATENATE lažje berljive, jaz osebno raje uporabljam metodo "&". Zato se preprosto držite tehnike združevanja, s katero se počutite bolj udobno.
Celice povežite s presledkom, vejico in drugimi znaki
V svojih delovnih listih boste morda morali pogosto združiti vrednosti na način, ki vključuje vejice, presledke, različna ločila ali druge znake, na primer vezaj ali poševnico. Če želite to narediti, preprosto vključite želeni znak v formulo za združevanje. Ne pozabite tega znaka vstaviti v narekovaje, kot je prikazano v naslednjih primerih.
Združitev dveh celic s presledkom:
= CONCATENATE (A1, "", B1) ali = A1 & "" & B1
Združitev dveh celic z vejico:
= CONCATENATE (A1, ",", B1) ali = A1 & "," & B1
Združitev dveh celic s vezajem:
= CONCATENATE (A1, "-", B1) ali = A1 & "-" & B1
Naslednji posnetek zaslona prikazuje, kako lahko izgledajo rezultati:
Besedilne nize povežite s prelomi vrstic
Najpogosteje bi združili besedilne nize z ločili in presledki, kot je prikazano v prejšnjem primeru. V nekaterih primerih pa bo morda treba vrednosti ločiti s prelomom vrstice ali vrnitvijo nosilca. Pogost primer je združevanje poštnih naslovov iz podatkov v ločenih stolpcih.
Težava je v tem, da v formulo ne morete preprosto vnesti preloma vrstice kot običajen znak, zato je potrebna posebna funkcija CHAR za dobavo ustrezne kode ASCII formuli za združevanje:
V sistemu Windows uporabite CHAR (10), kjer je 10 koda ASCII za vir vrstic.
V sistemu Mac uporabite CHAR (13), kjer je 13 koda ASCII za vrnitev prevoza.
V tem primeru imamo naslove v stolpcih od A do F in jih s stolpcem "&" združimo v stolpec G. Združene vrednosti so ločene z vejico (","), presledkom ("") in prelomom vrstice CHAR (10):
| = A2 & "" & B2 & CHAR (10) & C2 & CHAR (10) & D2 & "," & E2 & "" & F2 |
Opomba. Ko uporabljate prelome vrstic za ločevanje združenih vrednosti, morate imeti "Prelom besedila", če želite, da se rezultat pravilno prikaže. Če želite to narediti, pritisnite Ctrl + 1, da odprete pogovorno okno Oblikuj celice, preklopite na zavihek Poravnava in potrdite polje Besedilo.
Na enak način lahko ločene povezave ločite z drugimi znaki, kot so:
Dvojni narekovaji (") - CHAR (34)
Poševnica naprej (/) - CHAR (47)
Zvezdica (*) - CHAR (42)
Celoten seznam Kode ASCII Na voljo
Čeprav je lažji način vključitve natisljivih znakov v formulo za združevanje tako, da jih preprosto vnesete v dvojne narekovaje, kot smo to storili v prejšnjem primeru.
Kakorkoli, vse štiri spodnje formule dajejo enake rezultate:
Uporabite katero koli od spodaj navedenih formul:
| = A1 & CHAR (47) & B1
= A1 & "/" & B1 = KONKATENAT (A1, CHAR (47), B1) = ZDRUŽITE (A1, "/", B1) |
Kako združiti stolpce v Excelu
Če želite združiti dva ali več stolpcev v Excelu, v prvo celico vnesete običajno formulo za združevanje in jo nato povlečete ročico za polnjenje (majhen kvadrat, ki se prikaže v spodnjem desnem kotu polja izbrana celica).
Če želite na primer združiti dva stolpca (stolpec A in B), ki ločujeta vrednosti s presledkom, v celico C2 vnesete naslednjo formulo in jo nato kopirate v druge celice. Ko povlečete ročico za polnjenje, da kopirate formulo, se kazalec miške spremeni v križ, kot je prikazano na spodnjem posnetku zaslona:
Opomba: Hiter način kopiranja formule v druge celice v stolpcu je, da izberete celico s formulo in dvokliknete ročico za izpolnjevanje.
Upoštevajte, da Microsoft Excel določa, kako daleč lahko kopirate celice, potem ko ročaj izpolni dvakrat klikne na podlagi celic, na katere se nanaša vaša formula. Če so v vaši tabeli prazne celice, recimo v tem primeru celice A6 in B6 prazne, bi bila formula kopirana samo do 5. vrstice. V tem primeru bi morali ročaj za polnjenje povleči navzdol, da združite celotne stolpce.
Drug način za združevanje stolpcev v Excelu je uporaba ustrezne možnosti dodatka Merge Cells.
Kako združiti vrsto celic v Excelu
Združevanje vrednosti iz več celic lahko traja nekaj napora, ker funkcija Excel CONCATENATE ne sprejema nizov in zahteva vsak sklic na eno celico v vsakem argumentu.
Če želite združiti več celic, recimo od A1 do A4, potrebujete eno od naslednjih formul:
| = KONKATENAT (A1, A2, A3, A4)
ali = A1 & A2 & A3 & A4 |
Ko se pridružite dokaj majhnemu razponu, ni nič narobe, če v referenčno vrstico vnesete vse reference. Velik obseg bi bilo težko dodati, ročno vtipkati vsako referenco celice. Spodaj boste našli 3 metode hitrega združevanja dosega v Excelu.
Metoda 1. Pritisnite CTRL, da izberete več celic, ki jih želite združiti.
Če želite hitro izbrati več celic, lahko pritisnete tipko CTRL in kliknete vsako celico, ki jo želite vključiti v formulo CONCATENATE. Tu so podrobni koraki:
- Izberite celico, v katero želite vnesti formulo.
- Vrsta = CONCATENATE (v tej celici ali v vrstici s formulo.
- Pritisnite in držite tipko Ctrl in kliknite na vsako celico, ki jo želite združiti.
- Spustite gumb Ctrl, v vrstico s formulo vnesite zaključni oklepaj in pritisnite Enter.
Metoda 2. Za doseg obsega uporabite funkcijo TRANSPOSE
Ko morate združiti ogromen obseg, sestavljen iz več deset ali sto celic, prejšnja metoda ni dovolj hitra, ker zahteva klik na vsako celico. V tem primeru je boljši način, da uporabite funkcijo TRANSPOSE za vrnitev matrike in jo nato v enem zamahu zamenjate s sklici na posamezne celice.
V celico, v katero želite prikazati združeno območje, vnesite formulo TRANSPOSE, na primer:
= PREVOZ (A1: A10)
V vrstici s formulami pritisnite F9, da formulo zamenjate z izračunanimi vrednostmi. Posledično boste imeli niz številk, ki jih želite združiti.
Vnesite = CONCATENATE (pred prvo vrednostjo, nato za zadnjo vrednostjo vnesite zaključni oklepaj in pritisnite Enter.
Opomba: Ne glede na to, katero metodo uporabite, je združena vrednost v C1 besedilni niz (opazite njeno poravnavo v levo v celici), čeprav je vsaka od prvotnih vrednosti številka. To je zato, ker funkcija CONCATENATE vedno vrne besedilni niz ne glede na izvorni tip podatkov.
Metoda 3. Uporabite dodatek Merge Cells
Hiter in brez formul povezovanje katerega koli obsega v Excelu je uporaba dodatka Merge Cells for Excel z izklopljeno možnostjo "Merge all areas in selection", kot je prikazano v Združevanje vrednosti več celic v eno celico.
Združite številke in datume v različnih oblikah
Ko besedilni niz združite s številko ali datumom, boste morda želeli rezultat oblikovati drugače, odvisno od nabora podatkov. Če želite to narediti, vstavite funkcijo BESEDILO v formulo Excel za združevanje.
Funkcija TEXT (value, format_text) ima dva argumenta:
V prvem argumentu (vrednost), vnesete številko ali datum za pretvorbo v besedilo ali sklic na celico, ki vsebuje številčno vrednost.
V drugem argumentu (format_text), vnesete želeno obliko s kodami, ki jih funkcija TEXT razume.
Na začetku te vadnice smo že razpravljali o eni takšni formuli, ki združuje besedilo in datum.
Spomnil vas bom, da pri združevanju a besedilni niz in datum, za prikaz datuma v želeni obliki morate uporabiti funkcijo TEXT. Na primer:
| = CONCATENATE ("Danes je", TEXT (TODAY (), "mm/dd/yy"))
ali = "Danes je" & TEXT (TODAY (), "mm/dd/yy") |
Še nekaj primerov formul, ki združujejo a besedilna vrednost in številka sledite spodaj:
| = A2 & "" & TEXT (B2, "$#,#0.00") - prikaže številko z dvema decimalnima mestoma in znakom $.
= A2 & "" & TEXT (B2, "0.#") - ne prikazuje dodatnih ničel in znaka $. = A2 & "" & TEXT (B2, "#?/???") - prikaže številko kot ulomek. |
Kako razdeliti celice (nasprotno od CONCATENATE v Excelu)
Če iščete nasprotje CONCATENATE v Excelu, torej želite eno celico razdeliti na več celic, vam je na voljo nekaj možnosti:
Funkcija pošiljanja besedila v stolpce
Možnost Flash Fill v Excelu 2013 in 2016
Formule (funkcije MID, DESNO, LEVO)
Podrobne korake, prikazane s primeri formul in posnetki zaslona, najdete v vadnici Kako razdeliti celice v Excelu.
Dodatek Merge Cells-način za združevanje celic v Excelu brez formul
Z dodatkom Merge Cells, ki je vključen v Ultimate Suite for Excel, lahko učinkovito naredite oboje:
Združite več celic v eno, ne da bi pri tem izgubili podatke.
Vrednosti več celic povežite v eno celico in jih ločite s katerim koli ločevalnikom po vaši izbiri.
Orodje Merge Cells deluje z vsemi različicami Excela od leta 2003 do 2016 in lahko združuje vse vrste podatkov, vključno z besedilnimi nizi, številkami, datumi in posebnimi simboli. Njegovi dve ključni prednosti sta preprostost in hitrost - vsaka združitev se izvede v nekaj klikih. Zdaj pa naj vam to pokažem v akciji.
Združite vrednosti več celic v eno celico
Če želite združiti vsebino več celic, izberete obseg za združevanje in konfigurirate naslednje nastavitve:
Celice v eno pod "Kaj združiti";
V razdelku »Loči vrednosti z« izberite ločilo, ki je v tem primeru vejica in presledek;
Izberite, kam želite dati rezultat, in kar je najpomembneje
Počistite polje »Združi vsa območja v izbiri«. Ta možnost določa, ali so celice združene ali so vrednosti celic združene.
Združite stolpce po vrsticah
Če želite združiti dva ali več stolpcev, na podoben način konfigurirate nastavitve združevanja celic, vendar izberite Stolpci pod "Kaj združiti":
Pridruži vrstice stolpec za stolpec
Če želite združiti podatke v vsaki posamezni vrstici, stolpec za stolpec, se odločite za združitev Vrstice, izberite želeni razmejevalnik (v tem primeru prelom vrstice), nastavite druge nastavitve tako, kot želite, in pritisnite gumb Združi. Rezultat je lahko podoben temu:
Opomba :
V Excel 2016, Excel 2019, Excel Online in Excel Mobile se CONCATENATE nadomesti s funkcijo CONCAT, ki ima popolnoma enako sintakso. Čeprav je funkcija CONCATENATE ohranjena zaradi povratne združljivosti, je priporočljivo uporabiti CONCAT, ker Microsoft ne obljublja, da bo CONCATENATE na voljo v prihodnjih različicah Excela.
Upam, da je ta članek o tem, kako združiti ali združiti Excel, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Excel REPLACE vs SUBSTITUTE funkcija: Funkciji REPLACE in SUBSTITUTE sta najbolj napačno razumljeni funkciji. Za iskanje in zamenjavo danega besedila uporabimo funkcijo SUBSTITUTE. Kjer se REPLACE uporablja za zamenjavo številnih znakov v nizu …
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost : Pospešite svoje naloge v Excelu. Te bližnjice vam bodo pomagale povečati učinkovitost dela v Excelu.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.