Hlunga Advanced Excel: izibonelo. Indlela yokwenza isihlungi eziphambili Excel futhi kanjani ukuyisebenzisa?

abasebenzi Abaningi izinhlangano eziningi kufanele ngandlela-thile sisebenze Mircosot Excel, kungakhathaliseki ezimali abavamile nabahlaziyi ngokuvamile ibhekene kukhethwa inombolo yamanani kusuka kwaziswa. Ukuze lula lo msebenzi futhi sidalwe uhlelo filtration.

isihlungi Simple sezihambile

Ku-Excel libonisa isihlungi elula ukuthi iqala ngamadijithi "Idatha" ithebhu - "Hlunga» (Data - Hlunga kule nguqulo yesiNgisi uhlelo) noma ukusebenzisa ukunqamulela yamathuluzi, efana uphondo Isigaxa emise ngokudlulisela uketshezi izigubhu kanye izintamo emincane.

Ezimweni eziningi lemifanekiso nhlobo ukhetho best. Kodwa uma ufuna ukufeza ukukhethwa inombolo enkulu nemibandela (ngisho nasophahleni amakholomu amaningi, imigqa namaseli), eziningi uyazibuza kanjani ukwenza isihlungi eziphambili Excel. Uhlelo olubhalwe ngolimi lwesiNgisi libizwa isihlungi Okuthuthukisiwe.

Ukusetshenziswa lokuqala lemifanekiso eziphambili

Ku-Excel, yibona ababenza umsebenzi omningi senzeka nethebula. Okokuqala, kulula, futhi okwesibili, e kufayela elilodwa, ungagcina ulwazi emakhasini eziningana (amathebhu). Ngenhla etafuleni main, kuyinto efiselekayo ukwakha imisho embalwa, le ubhedu zazo kusala cap, kuba umugqa idatha zizongena izimo Excel isihlungi eziphambili. Ngemva kwalokho, lemifanekiso Mhlawumbe kuyobe zishintshwe uma izimo zidinga ngaphezulu, ungahlala ukufaka endaweni efanele ngisho umugqa owodwa. Nokho, kuyinto efiselekayo ukuthi izimo phakathi amaseli bese amaseli senhlobo idatha yomthombo kwaba umugqa owodwa unactuated.

Indlela yokusebenzisa lemifanekiso ezithuthukile Excel, izibonelo sichaziwe ngezansi.

Ukusebenzisa isihlungi

Ngo emigqeni etafuleni 1 no-2 zenzelwe uhla izimo, imigqa 4 kuya ku-7 - idatha nebanga lomthombo.

Ukuze uqale ukungena umugqa 2 yamanani okuhambisanayo, okuzokwenza bamhlamuke lemifanekiso eziphambili Excel.

Ukuqala ukuhlunga ngokusebenzisa cell delineation idatha kokuqala, khona-ke kumelwe ukhethe "Idatha" ithebhu bese uchofoza "Okuthuthukisiwe» (Data - ezithuthukisiwe, ngokulandelana).

Isikrini ubonisa ibanga lamaseli akhethiwe "Umthombo Ibanga". Ngokusho isibonelo ngenhla, emgqeni eqala ukubaluleka «$ A $ 4: $ F $ 12".

I "izimo ububanzi" kufanele ugcwalise amagugu «$ A $ 1: $ F $ 2."

Iwindi iqukethe izimo ezimbili:

• ukuhlunga uhlu endaweni;
• ukopisha Imiphumela endaweni ehlukile.

Isimo sokuqala ivumela ukwakheka yi on the spot, ngaphansi eyabelwe nebanga lomthombo cell. Isimo sesibili ivumela ukukhiqiza uhlu imiphumela band olulodwa, okuyinto kufanele icaciswe "Engeza umphumela ebangeni." Umsebenzisi ukhetha kukho elula, isibonelo, iwindi wokuqala, "Hlunga Okuthuthukisiwe" Excel ivaliwe.

Ngokusekelwe idatha wangena, lemifanekiso uyomenzela ithebula elandelako.

Uma usebenzisa amagama athi "Kopisha umphumela kwenye indawo" amagugu 4 no-5 imigqa ziboniswa uhla predetermined ngumsebenzisi. Ububanzi kokuqala izohlala ingashintshiwe.

Ubulula

Indlela okuchazwe zingavumi, ngoba ngcono ngokuvamile zisebenzisa VBA ulimi lokwakha izinhlelo, ngawo kuze amamakhro ngokuzenzakalelayo isihlungi eziphambili Excel.

Uma umsebenzisi ulwazi VBA, kutuswa ukufunda ezihlokweni eziningi kulesi sihloko futhi ngempumelelo ukufeza amacebo ethu. Uma ushintsha imigqa namaseli 2, eyabelwe ngaphansi Excel isihlungi eziphambili, uhla izimo kuzoshintsha izilungiselelo kabusha ngokushesha futhi waqala futhi ububanzi efanele uyomenzela imininingwane edingekayo.

imibuzo eziyinkimbinkimbi

Ngaphezu ukusebenza amanani ngokunembile predetermined, ukuhlunga eziphambili Excel, futhi ukusisingatha imibuzo eziyinkimbinkimbi. Lezi afakiwe yedatha, lapho ingxenye izinhlamvu ezibuyiselwe ngu wildcards.

Ithebula uphawu imibuzo eziyinkimbinkimbi ezibalwe ngezansi.

Kuwufanele wazi ukuthi uphawu * kungasho iyiphi Inani lezinhlamvu. Okusho ukuthi, lapho wangena value "n *" ibuyisela wonke amanani, kungakhathaliseki inani lezinhlamvu ngemva kwencwadi "n."

Uphawu oluthi "?" Kusho ngohlamvu olulodwa kuphela.

Amaqoqo we KANYE futhi NOMA

Sicela uqaphele ukuthi imininingwane enikezwe elayinini kweyodwa "uhla izimo" ezibhekwa ezilotshwe kweqembu opharetha okunengqondo (KANYE). Lokhu kusho ukuthi izimo eziningana kukhona kanyekanye wanelisekile.

Uma idatha efanayo kulotshwe ikholomu eyodwa, ukuhlunga eziphambili Excel ibona opharetha eziphathelene okunengqondo (NOMA).

Uhlu amagugu kuthatha ifomu elilandelayo:

amatafula isifinyezo

Enye indlela ukuhlunga idatha ngokusebenzisa umyalo "Insert - Ithebula - etafuleni Isifingqo» (Faka - Ithebula - weThebula kule nguqulo ngesiNgisi).

Lamathebuli efanayo usebenza nge ozinikele pre-ezikhethiwe ibanga ledatha futhi ahlukile ukuba ihlaziye kamuva. Eqinisweni, kubonakala sengathi umsebenzi Uhlu olwehlayo emikhakheni ehlukile (isibonelo, amagama izisebenzi zale nkampani) kanye ububanzi bamanani banikwa kukhethwa inkambu esiyingqayizivele.

Ububi usebenzisa amathebula isifinyezo kuyinto isidingo izinguquko inhlukano lapho kushintshwa eluhlaza idatha ngokwabo.

isiphetho

Ekuphetheni, kufanele kuphawulwe ukuthi bangaki abantu abayozuza kulesi lemifanekiso e-Microsoft Excel ubanzi kakhulu futhi ezihlukahlukene. Lwanele ukusebenzisa umcabango nokuthuthukisa ulwazi lwabo kanye namakhono.

Ngu uqobo, lemifanekiso Kulula ukusebenzisa futhi ufunde, kulula ukuqonda ukuthi ukusebenzisa lemifanekiso eziphambili Excel, kodwa yakhelwe izicelo ezidinga esincane isikhathi ukwenza ukuhlolwa Imininingwane kucutshungulwe. Njengomthetho, awusebenzi ukunikeza umsebenzi nge amasethi enkulu idatha ngenxa isici womuntu njalo. Kukhona ukusiza eyinkimbinkimbi kakhulu futhi eziphambili information processing ubuchwepheshe e-Microsoft Excel.

amamakhro Huge ethandwa, esihlelwe ngomumo ngolimi VBA. Bavumela wena ukusebenzisa inqwaba izihlungi, lula amagugu Ukukhetha futhi okukhipha ukuba zibe ezifanele.

Amamakhro ngempumelelo esikhundleni amahora amaningi umsebenzi ezilandelwayo isifinyezo, ukubika gqwa nezinye, esikhundleni ukuhlaziywa eside isikhathi amanani ezinkulu chofoza yesibili eyodwa kuphela.

Ukusebenzisa ama-macros walungisiswa futhi ungakhululekile. Noma ubani ihlangabezane nesidingo ukusebenzisa, uma uthanda, uyohlale ukuthola izinto ngokwanele kutfutfukisa lwati okwatholakala yabo izimpendulo zemibuzo.