CONCATENATE umsebenzi ku-Excel - umhlahlandlela wokusetshenziswa onezibonelo

Ngezikhathi ezithile kungase kudingeke ukuhlanganisa amanani aqukethwe kumaseli ahlukene abe linye. I & symbol ivamise ukusetshenziselwa lokhu. Kodwa ukusebenza kwayo kulinganiselwe ngandlela thize njengoba ingakwazi ukuhlanganisa izintambo eziningi.

Lo msebenzi olula uthathelwa indawo inguqulo esebenza kakhudlwana - I-STsEPIT. Eqinisweni, ezinguqulweni zanamuhla ze-Microsoft Office, lo msebenzi awusekho, uthathelwa indawo ngokuphelele umsebenzi ISINYATHELO. Isasebenziseka okwamanje, ifakiwe ekusebenzisaneni emuva, kodwa ngemva kwesikhashana ingase ingabi njalo. Ngakho-ke, kunconywa ukuthi ku-Excel 2016, izinguqulo eziku-inthanethi nezintsha, sebenzisa umsebenzi ISINYATHELO.

CONCATENATE umsebenzi – incazelo enemininingwane

Umsebenzi I-STsEPIT ibhekisela embhalweni. Lokhu kusho ukuthi isetshenziselwa ukwenza imisebenzi ngamavelu ombhalo. Ngesikhathi esifanayo, ungacacisa ama-agumenti ngamafomethi ahlukene: umbhalo, izinombolo, noma njengereferensi yeseli. 

Ngokuvamile, imithetho yokusebenzisa lesi sici imi kanje:

1. Isemikholoni isetshenziswa ukuhlukanisa ama-agumenti. Uma umsebenzisi enquma ukusebenzisa ezinye izinhlamvu, isibonisi sizoba umphumela wezimpawu zokucaphuna.
2. Uma inani lefomethi yombhalo lisetshenziswa njenge-agumenti yomsebenzi futhi lifakwe ngqo kufomula, kufanele lifakwe kuzimpawu zokucaphuna. Uma kukhona ireferensi enanini elinjalo, khona-ke azikho izingcaphuno ezidingekayo. Okufanayo kuya kumanani ezinombolo. Uma udinga ukwengeza idijithi kuyunithi yezinhlamvu, khona-ke ukucaphuna akudingeki. Uma wephula le mithetho, iphutha elilandelayo lizovela – #NAME?
3. Uma udinga ukwengeza isikhala phakathi kwezinto ezixhunyiwe, kufanele sengezwe njengeyunithi yezinhlamvu ehlukile, okungukuthi, kumamaki okucaphuna. Thanda lokhu: " " .

Manje ake sibhekisise i-syntax yalo msebenzi. Ulula kakhulu. 

syntax

Ngakho, empeleni, kune-agumenti eyodwa kuphela - lena intambo yombhalo okufanele ifakwe. Ingxabano ngayinye, njengoba sesivele sazi, ihlukaniswa yi-semicolon. Ungacacisa kufika kuma-agumenti angu-255. Bona ngokwabo baphindwa ngendlela yabo. Kudingeka impikiswano yokuqala. Futhi njengoba sesivele sazi, ungacacisa izimpikiswano ngamafomethi amathathu: umbhalo, inombolo, nesixhumanisi. 

Izinhlelo zokusebenza zomsebenzi we-CONCATENATE

Inombolo yezindawo zohlelo lokusebenza I-STsEPIT enkulu. Eqinisweni, ingasetshenziswa cishe yonke indawo. Ake sibheke ezinye zazo ngokuningiliziwe:

1. Ukubalwa kwezimali. Isibonelo, i-accountant inomsebenzi wokugqamisa uchungechunge nenombolo yedokhumenti, bese ifaka le datha njengomugqa owodwa kuseli eyodwa. Noma udinga ukwengeza ochungechungeni kanye nenombolo yedokhumenti ekhishwe nguye. Noma bhala amaresidi amaningana kuseli eyodwa ngesikhathi esisodwa. Eqinisweni, kukhona inqwaba yezinketho, ungabhala unomphela. 
2. Imibiko yehhovisi. Ikakhulukazi uma udinga ukunikeza idatha yesifinyezo. Noma hlanganisa igama nesibongo.
3. I-Gamification. Lona umkhuba othandwa kakhulu osetshenziswa ngenkuthalo kwezemfundo, ukukhulisa izingane, kanye nasezinhlelweni zokwethembeka zezinkampani ezahlukahlukene. Ngakho-ke, emkhakheni wezemfundo nebhizinisi, lo msebenzi ungaba wusizo futhi. 

Lo msebenzi ufakiwe kusethi ejwayelekile okufanele wonke umsebenzisi we-Excel ayazi. 

Umsebenzi ophambene we-CONCATENATE ku-Excel

Eqinisweni, awukho umsebenzi onjalo ongaphambana ngokuphelele nomsebenzi othi "CONCATENATE". Ukwenza ukuhlukaniswa kwamaseli, eminye imisebenzi isetshenziswa, njenge I-LEVSIMV и KUPHELAfuthi PSTR. Eyokuqala ikhipha inombolo ethile yezinhlamvu kwesokunxele seyunithi yezinhlamvu. Owesibili ungakwesokudla. KODWA PSTR ingakwenza endaweni engafanele futhi igcine endaweni engafanele. 

Ungase futhi udinge ukwenza imisebenzi eyinkimbinkimbi, kodwa kukhona amafomula ahlukene ayo. 

Izinkinga ezijwayelekile ngomsebenzi we-CONCATENATE

Uma uthi nhlá, umsebenzi I-STsEPIT pretty elula. Kodwa ekusebenzeni, kuvela ukuthi izinkinga eziningi zingenzeka. Ake sizibhekisise. 

1. Izingcaphuno ziboniswa kuchungechunge lomphumela. Ukuze ugweme le nkinga, udinga ukusebenzisa i-semicolon njengesihlukanisi. Kodwa, njengoba kushiwo ngenhla, lo mthetho awusebenzi ezinombolweni.
2. Amazwi asondelene kakhulu. Le nkinga yenzeka ngoba umuntu akawazi wonke ama-nuances okusebenzisa umsebenzi I-STsEPIT. Ukuveza amagama ngokwehlukana, kufanele wengeze uhlamvu lwesikhala kuwo. Noma ungayifaka ngqo ngemva kwempikiswano yombhalo (kokubili ngaphakathi kweseli futhi uma ufaka umbhalo ngokwehlukana kufomula). Isibonelo esifana nalesi: =CONCATENATE(“Sawubona”, “sithandwa”). Siyabona ukuthi lapha kwengezwe isikhala ekugcineni kwegama elithi “Sawubona”. 
3. #IGAMA? Lokhu kubonisa ukuthi azikho izingcaphuno ezishiwo ku-agumenti yombhalo. 

Izincomo zokusebenzisa umsebenzi

Ukuze usebenze ngalo msebenzi ngempumelelo, udinga ukucabangela izincomo ezimbalwa ezibalulekile:

1. Sebenzisa & ngangokunokwenzeka. Uma udinga ukujoyina imigqa yombhalo emibili kuphela, asikho isidingo sokusebenzisa umsebenzi ohlukile kulokhu. Ngakho-ke isipredishithi sizosebenza ngokushesha, ikakhulukazi kumakhompyutha abuthaka anenani elincane le-RAM. Isibonelo yile fomula elandelayo: =A1 & B1. Iyafana nefomula =ISIQEPHU(A1,B1). Ikakhulukazi inketho yokuqala ilula uma ufaka ifomula mathupha.
2. Uma kudingekile ukuhlanganisa uhlobo lwemali noma idethi neyunithi yezinhlamvu yombhalo, kanye nolwazi nganoma iyiphi enye ifomethi ngaphandle kwalezi ezibalwe ngenhla, kuzomele uqale uyicubungule ngomsebenzi. TEXT. Iklanyelwe ukuguqula izinombolo, izinsuku, izimpawu zibe umbhalo.

Njengoba ubona, akunzima ukuqonda la ma-nuances nhlobo. Futhi balandela ulwazi olungenhla. 

Ukusetshenziswa Okuvamile Komsebenzi we-CONCATENATE

Ngakho ifomula evamile ithi: CONCATENATE([umbhalo2];[umbhalo2];…). Faka umbhalo wakho ezindaweni ezifanele. Kubalulekile ukuqaphela ukuthi imfuneko yombhalo owamukelwe imi kanje: kufanele ibe ngaphansi kobude benkambu lapho kufakwa khona inani. Njengezimfanelo, awukwazi ukusebenzisa amanani achazwe ngaphambilini kuphela, kodwa futhi nolwazi kumaseli, kanye nemiphumela yokubala usebenzisa amanye amafomula.

Kulolu hlelo, asikho isincomo esiyisibopho sokusebenzisa idatha ukuze ufake ngefomethi yombhalo. Kodwa umphumela wokugcina uzoboniswa ngefomethi "Yombhalo".

Kunezindlela ezimbalwa zokufaka umsebenzi: imanuwali eyodwa kanye ne-semi-automatic eziningana. Uma ungumuntu osaqalayo, kuyanconywa ukuthi usebenzise indlela yebhokisi lengxoxo yokufaka izimpikiswano. Abasebenzisi abanolwazi oluthe xaxa bohlelo bangafaka amafomula mathupha. Ekuqaleni kuzobonakala kungenangqondo, kodwa empeleni, akukho lutho olusebenza kahle njengokufakwa kwekhibhodi olungakasungulwa. 

Ngendlela, izincomo zokusebenzisa i-Excel ngokujwayelekile: funda njalo ama-hotkeys. Bazokusiza ukuthi wonge isikhathi esiningi.

Kodwa ngenkathi usaqala, kuzodingeka usebenzise iwindi elenziwe ngokukhethekile.

Ngakho kanjani ukuyibiza? Uma ubheka umugqa wokufaka wefomula, khona-ke kwesokunxele kuwo kukhona inkinobho encane "fx". Uma uyicindezela, kuvela imenyu elandelayo. Sidinga ukukhetha umsebenzi esiwufunayo ohlwini.

Ngemva kokukhetha umsebenzi esiwufunayo, kuzovuleka iwindi lokufaka izimpikiswano. Ngayo, ungasetha ububanzi noma ufake ngesandla umbhalo, isixhumanisi seseli. 

Uma ufaka idatha mathupha, khona-ke okokufaka kuyenziwa, kuqala ngophawu "olulinganayo". Okusho ukuthi, kanje:

=CONNATENATE(D2;”;E2)

Ngemuva kwayo yonke imisebenzi eyenziwa yithi, sizobona esitokisini esiwumphumela umbhalo othi "21.09", oqukethe izingxenye eziningana: inombolo engu-21, etholakala esitokisini esinenkomba ye-D2 nomugqa 09, osesitokisini E2. . Ukuze bahlukaniswe ichashazi, sisebenzise njengengxabano yesibili. 

Igama liyabophezela

Ukuze kucace, ake sibheke isibonelo esichaza indlela yokuhlanganisa amagama. 

Ake sithi sinetafula elinjalo. Iqukethe ulwazi mayelana negama lokuqala, isibongo, idolobha, isimo samakhasimende. Umsebenzi wethu uwukuhlanganisa igama nesibongo bese sithola igama eliphelele. 

Ngokusekelwe kuleli thebula, siyaqonda ukuthi izinkomba zamagama kufanele zinikezwe kukholamu B, nezibongo - A. Ifomula ngokwayo izobhalwa kuseli lokuqala ngaphansi kwesihloko esithi “Igama Eligcwele”.

Ngaphambi kokufaka ifomula, khumbula ukuthi umsebenzi ngeke uhlobanise ulwazi oluningi kunalokho olushiwo umsebenzisi. Ngakho-ke, uma udinga ukwengeza ama-delimiters, izimpawu zombuzo, amachashazi, amadeshi, izikhala, kufanele zifakwe njengezimpikiswano ezihlukene.

Esibonelweni sethu, sidinga ukuhlukanisa igama nesibongo ngesikhala. Ngakho-ke, sidinga ukufaka izimpikiswano ezintathu: ikheli leseli eliqukethe igama lokuqala, uhlamvu lwesikhala (ungakhohlwa ukulifaka ezimpawu zokucaphuna), kanye nekheli leseli eliqukethe isibongo. 

Ngemva kokuba sesichaze izimpikiswano, sizibhala efomini ngokulandelana okufanele. 

Kubaluleke kakhulu ukunaka i-syntax yefomula. Sihlala siyiqala ngesibonakaliso esilinganayo, emva kwalokho sivula amabakaki, sibhale izingxabano, sihlukanise nge-semicolon, bese sivala amabakaki.

Kwesinye isikhathi ungabeka ukhefana phakathi kwezingxabano. Uma kusetshenziswa inguqulo yesiNgisi ye-Excel, kufakwa ukhefana. Uma -inguqulo yolimi, bese kuba yi-semicolon. Ngemuva kokuthi sicindezele u-Enter, inguqulo ehlanganisiwe izovela.

Manje osekusele nje ukusebenzisa umaka wokugcwalisa ngokuzenzakalelayo ukuze ufake le fomula kuwo wonke amanye amaseli akule kholomu. Ngenxa yalokho, sinegama eliphelele leklayenti ngalinye. Inhloso ifeziwe.

Ngendlela efanayo, ungaxhuma isifunda nedolobha.

Ukuxhumanisa izinombolo nombhalo

Njengoba sesivele sazi, usebenzisa umsebenzi I-STsEPIT singahlanganisa amanani ezinombolo ngamavelu ombhalo. Ake sithi sinetafula elinedatha emayelana nenani lempahla esitolo. Okwamanje sinama-apula angama-25, kodwa lo mugqa usakazwe phezu kwamaseli amabili. 

Sidinga umphumela wokugcina olandelayo.

Kulokhu, sidinga futhi izimpikiswano ezintathu, futhi i-syntax isafana. Kodwa ake sizame ukuqedela umsebenzi wobunzima obuthe ukukhula kancane. Ake sithi sidinga ukubhala iyunithi yezinhlamvu eyinkimbinkimbi "Sinama-apula angama-25". Ngakho-ke, sidinga ukwengeza umugqa owodwa ngaphezulu othi “Sinawo” kuma-agumenti amathathu akhona. Umphumela ubukeka kanje.

=CONCATENATE(“Sine “;F17;” “;F16)

Uma efisa, umsebenzisi angengeza cishe izimpikiswano eziningi ngendlela afuna ngayo (ngaphakathi komkhawulo ongenhla).

Ixhuma i-VLOOKUP ne-CONCATENATE

Uma usebenzisa imisebenzi VPR и I-STsEPIT ndawonye, ​​​​kungase kubonakale kuthakazelisa kakhulu futhi, okubalulekile, inhlanganisela esebenzayo. Ukusebenzisa umsebenzi VPR senza ukusesha okuqondile etafuleni ngokuya ngombandela othile. Ngemva kwalokho singangeza ulwazi olutholiwe emugqeni osuvele ukhona.

Ngakho-ke, ake sithi sinetafula elinjalo. Ichaza ukuthi yiziphi izimpahla okwamanje ezisendaweni yokuqala neyesibili. 

Kudingeka sithole intengo yento ethile endaweni ethile yokugcina impahla. Kulokhu, umsebenzi usetshenziswa VPR. Kodwa ngaphambi kokuyisebenzisa, kufanele uqale ulungiselele itafula kancane. VPR ikhipha idatha kwesokunxele, ngakho udinga ukufaka ikholomu eyengeziwe kwesokunxele sethebula nedatha yoqobo. 

Ngemva kwalokho sihlanganisa idatha. 

Lokhu kungenziwa ngale fomula:

=B2&»/»&C2

Noma kunjalo.

=CONNATENATE(B2;”/”;C2)

Ngakho, sihlanganise amakholomu amabili ndawonye, ​​sisebenzisa i-slash eya phambili njengesihlukanisi phakathi kwamanani amabili. Okulandelayo, sidlulisele le fomula kuyo yonke ikholomu A. Sithola itafula elinjalo.

Okulandelayo, sithatha ithebula elilandelayo bese siligcwalisa ngolwazi mayelana nomkhiqizo okhethwe yisivakashi. Kudingeka sithole ulwazi mayelana nezindleko zezimpahla kanye nenombolo ye-warehouse etafuleni lokuqala. Lokhu kwenziwa ngokusebenzisa uhlelo VPR.

Okulandelayo, khetha iseli K2, bese ubhala ifomula elandelayo kuyo. 

{=ВПР(G2&»/»&H2;A2:E6;5;0)}

Noma ingabhalwa ngomsebenzi I-STsEPIT.

{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}

I-syntax kuleli cala ifana nendlela ukuhlanganiswa kolwazi mayelana nenombolo kanye nendawo yokugcina impahla. 

Udinga ukufaka umsebenzi VPR ngokusebenzisa inhlanganisela yokhiye "Ctrl" + "Shift" + "Enter".

Njengoba ubona, yonke into ilula.