Ukushintshwa kombhalo ngobuningi ngamafomula

Ake sithi unohlu lapho, ngamazinga ahlukahlukene "obuqotho," kubhalwa idatha yokuqala - isibonelo, amakheli noma amagama ezinkampani:

Kubonakala ngokucacile ukuthi idolobha elifanayo noma inkampani ikhona lapha ezinhlobonhlobo ze-motley, okusobala ukuthi, kuzodala izinkinga eziningi lapho isebenza nalawa matafula esikhathini esizayo. Futhi uma ucabanga kancane, ungathola izibonelo eziningi zemisebenzi efanayo kwezinye izindawo.

Manje ake ucabange ukuthi idatha enjalo eyisigwegwe iza kuwe njalo, okungukuthi lokhu akuyona indaba yesikhathi esisodwa "yilungise ngesandla, khohlwa", kodwa inkinga njalo futhi ngenani elikhulu lamaseli.

Okufanele ngikwenze? Ungashintshi ngesandla umbhalo ogwegwile izikhathi ezingu-100500 ufake ofanele ngebhokisi elithi “Thola futhi Faka esikhundleni” noma ngokuchofoza Ctrl+H?

Into yokuqala efika engqondweni esimweni esinjalo ukwenza ukushintshwa kwenqwaba ngokusho kwencwadi yereferensi ehlanganiswe ngaphambilini yokufanisa izinketho ezingalungile nezilungile - kanje:

Ngeshwa, ngokuvama okusobala komsebenzi onjalo, iMicrosoft Excel ayinazo izindlela ezilula ezakhelwe ngaphakathi zokuwuxazulula. Okokuqala, ake sithole ukuthi singakwenza kanjani lokhu ngamafomula, ngaphandle kokubandakanya "i-artillery esindayo" ngendlela yama-macros ku-VBA noma Umbuzo Wamandla.

Ikesi 1. Ukushintshwa kwenqwaba okugcwele

Ake siqale ngecala elilula uma kuqhathaniswa - isimo lapho udinga ukushintsha umbhalo omdala ogwegwile ufake omusha. ngokugcwele.

Ake sithi sinamathebula amabili:

Kowokuqala - amagama okuqala ahlukahlukene ezinkampani. Kweyesibili - incwadi yereferensi yokuxhumana. Uma sithola egameni lenkampani etafuleni lokuqala noma yiliphi igama elivela kukholamu Ukuthola, khona-ke udinga ukufaka esikhundleni ngokuphelele leli gama eliyisigwegwe nelilungile - kusukela kukholomu I-Substitute itafula lokubheka lesibili.

Ukuze kube lula:

• Womabili amathebula aguqulelwa ku-dynamic (“smart”) kusetshenziswa isinqamuleli sekhibhodi Ctrl+T noma iqembu Faka – Ithebula (Faka — Ithebula).
  
• Kuthebhu evelayo umakhi (Umklamo) Itafula lokuqala eliqanjwe igama Idatha, kanye nethebula lesethenjwa lesibili - Ukushintshwa.

Ukuze sichaze i-logic yefomula, ake sihambe kancane sisuka kude.

Sithatha inkampani yokuqala kuseli A2 njengesibonelo futhi sikhohlwe okwesikhashana ezinye izinkampani, ake sizame ukunquma ukuthi iyiphi inketho kukholamu. Ukuthola ihlangana khona. Ukuze wenze lokhu, khetha noma iyiphi iseli engenalutho engxenyeni yamahhala yeshidi bese ufaka umsebenzi lapho UKUTHOLA (THOLA):

Lo msebenzi unquma ukuthi uchungechunge oluncane olunikeziwe lufakiwe yini (i-agumenti yokuqala ingawo wonke amanani asuka kukholomu Ukuthola) kumbhalo womthombo (inkampani yokuqala esuka kuthebula ledatha) futhi kufanele ikhiphe inombolo ye-ordinal yohlamvu okutholwe kulo umbhalo, noma iphutha uma uchungechunge oluncane lungatholwanga.

Iqhinga lapha ukuthi njengoba singabalulanga eyodwa, kodwa amanani amaningana njengengxabano yokuqala, lo msebenzi uzobuya futhi njengomphumela hhayi inani elilodwa, kodwa uhlu lwama-elementi angu-3. Uma ungenayo inguqulo yakamuva ye-Office 365 esekela amalungu afanayo ashukumisayo, ngemva kokufaka le fomula nokuchofoza ku- Faka uzobona lolu hlu ngqo eshidini:

Uma unezinguqulo zangaphambilini ze-Excel, ngemuva kokuchofoza Faka sizobona kuphela inani lokuqala elivela ohlwini lomphumela, okungukuthi iphutha #VALUE! (#INANI!).

Akufanele wesabe 🙂 Eqinisweni, ifomula yethu iyasebenza futhi usengabona lonke uhlu lwemiphumela uma ukhetha umsebenzi ofakiwe kubha yefomula bese ucindezela ukhiye. F9(ungakhohlwa nje ukucindezela Escukuze ubuyele kufomula):

Imiphumela eminingi ewumphumela isho ukuthi egameni lenkampani eligwegwile (GK Morozko OAO) wawo wonke amanani kukholamu Ukuthola uthole eyesibili kuphela (Morozko), futhi iqala kuhlamvu lwesi-4 ngokulandelana.

Manje ake sengeze umsebenzi kufomula yethu VIEW(BHEKA):

Lo msebenzi unezimpikiswano ezintathu:

1. Inani elifunekayo - ungasebenzisa noma iyiphi inombolo enkulu ngokwanele (into eyinhloko ukuthi idlula ubude banoma yimuphi umbhalo kudatha yomthombo)
   
2. Ivektha_ebukiwe – ububanzi noma amalungu afanayo lapho sifuna khona inani elifunekayo. Nansi umsebenzi owethulwe ngaphambilini UKUTHOLA, ebuyisela amalungu afanayo {#VALUE!:4:#VALUE!}
   
3. I-Vector_Imiphumela – ububanzi esifuna ukubuyisela kubo inani uma inani elifunekayo litholakala kuseli elihambisanayo. Nawa amagama alungile avela kukholamu I-Substitute ithebula lethu lereferensi.
   

Isici esiyinhloko nesingabonakali lapha ukuthi umsebenzi VIEW uma kungekho ukufana okuqondile, hlala ubheka inani elincane eliseduze (langaphambilini).. Ngakho-ke, ngokucacisa noma iyiphi inombolo enkulu (isibonelo, 9999) njengenani elifunekayo, sizophoqa VIEW thola iseli elinenombolo encane eseduze (4) ohlwini {#VALUE!:4:#INANI!} bese ubuyisela inani elihambisanayo elivela ivekhtha yomphumela, okungukuthi igama lenkampani elilungile elisuka kukholomu. I-Substitute.

I-nuance yesibili ukuthi, ngokobuchwepheshe, ifomula yethu iyifomula yamalungu afanayo, ngoba umsebenzi UKUTHOLA ibuyisela njengemiphumela hhayi eyodwa, kodwa uhlu lwamanani amathathu. Kodwa kusukela umsebenzi VIEW isekela amalungu afanayo ngaphandle kwebhokisi, ngakho-ke akudingeki ukuthi sifake le fomula njengefomula yamalungu afanayo akudala - sisebenzisa isinqamuleli sekhibhodi Ctrl+Shift+Faka. Okulula kuzokwanela Faka.

Yilokho kuphela. Ngethemba ukuthi uyawuthola umqondo.

Kusele ukudlulisa ifomula eqediwe kuseli lokuqala B2 lekholomu Fixed - futhi umsebenzi wethu usuxazululiwe!

Vele, ngamatafula ajwayelekile (hhayi ahlakaniphile), le fomula nayo isebenza kahle (ungakhohlwa nje ngokhiye F4 kanye nokulungisa izixhumanisi ezifanele):

Ikesi 2. Ukushintshwa okuyingxenye kwenqwaba

Leli cala liyinkimbinkimbi kancane. Futhi sinamathebula amabili "ahlakaniphile":

Ithebula lokuqala elinamakheli abhalwe ngokugwegwile adinga ukulungiswa (ngawabiza Idatha2). Ithebula lesibili liyincwadi eyireferensi, okusho ukuthi udinga ukushintsha ingxenye yochungechunge oluncane ngaphakathi kwekheli (ngibize leli thebula Ukushintshwa2).

Umehluko obalulekile lapha ukuthi udinga ukufaka kuphela isiqeshana sedatha yoqobo - isibonelo, ikheli lokuqala linephutha. “ISt. Petersburg” ngakwesokudla “ISt. Petersburg”, ishiya lonke ikheli (ikhodi ye-zip, umgwaqo, indlu) njengoba linjalo.

Ifomula eqediwe izobukeka kanje (ukuze kube lula ukuqonda, ngiyihlukanise ibe mingaki imigqa esetshenziswayo Alt+Faka):

Umsebenzi oyinhloko lapha wenziwa umsebenzi wombhalo ojwayelekile we-Excel I-SUBSTITUTE (SUBSTITUTE), enezimpikiswano ezi-3:

1. Umbhalo owumthombo – ikheli lokuqala eliyisigwegwe ohlwini lwekheli
2. Esikufunayo - lapha sisebenzisa iqhinga ngomsebenzi VIEW (BHEKA)kusuka endleleni yangaphambilini yokudonsa inani kusuka kukholomu Ukuthola, efakwe njengesiqephu ekhelini eligobile.
3. Yini ongayifaka esikhundleni - ngendlela efanayo sithola inani elifanele elihambisana nalo kusuka kukholomu I-Substitute.

Faka le fomula nge Ctrl+Shift+Faka ayidingeki nalapha, nakuba, eqinisweni, iyifomula yamalungu afanayo.

Futhi kubonakala ngokucacile (bona amaphutha angu-#N/A esithombeni esidlule) ukuthi ifomula enjalo, kubo bonke ubuhle bayo, inezihibe ezimbalwa:

• Umsebenzi I-SUBSTITUTE ibucayi kakhulu, ngakho-ke i-“Spb” emugqeni ongaphambili ayitholakalanga kuthebula lokumiselela. Ukuxazulula le nkinga, ungasebenzisa noma umsebenzi ZAMENIT (BUSELELA), noma ulethe womabili amathebula kurejista efanayo.
• Uma umbhalo ulungile ekuqaleni noma ukuwo asikho isiqeshana esingashintshwa (umugqa wokugcina), bese ifomula yethu iphonsa iphutha. Lesi sikhathi singasetshenziswa ngokunqamula futhi simiselele amaphutha kusetshenziswa umsebenzi IFERROR (IFERROR):

  

• Uma umbhalo wokuqala uqukethe izingcezu ezimbalwa kusuka kuhla lwemibhalo ngesikhathi esisodwa, khona-ke ifomula yethu ithatha indawo yokugcina kuphela (emgqeni wesi-8, Ligovsky «Avenue« kushintshelwe ku "pr-t", Kodwa "S-Pb" on “ISt. Petersburg” ayisekho, ngoba "S-Pb” iphezulu ohlwini lwemibhalo). Le nkinga ingaxazululwa ngokuqalisa kabusha ifomula yethu, kodwa kakade eduze kwekholomu Fixed:

  
  

Ayiphelele futhi ayinzima ezindaweni, kodwa ingcono kakhulu kunokushintshwa okufanayo kwezandla, akunjalo? 🙂

PS

Esihlokweni esilandelayo, sizobona ukuthi singakusebenzisa kanjani ukufaka esikhundleni senqwaba kusetshenziswa ama-macros kanye Nombuzo Wamandla.

• Usebenza kanjani umsebenzi we-SUBSTITUTE ukuze umiselele umbhalo
  
• Ukuthola Okufana Nangempela Kombhalo Ngokusebenzisa Umsebenzi OQINILE
• Ukusesha okubucayi kwekesi nokufaka esikhundleni (i-VLOOKUP ezwelayo)