Isihlungi esiphezulu kwi-Excel: imizekelo. Indlela yokwenza iifayile eziphambili kwi-Excel kunye nendlela yokuyisebenzisa?

Abasebenzi abaninzi bazo zonke iintlobo zemibutho kufuneka basebenze nganoma yiyiphi indlela kunye ne-Mircosot Excel, nokuba ngaba ngabagcini-zimali okanye abahlalutyi abaqhelekileyo, bahlala bejongana nemfuno yokukhetha inani lexabiso ukusuka kwiinkcukacha ezininzi. Ukwenza lula ukuphunyezwa kwalo msebenzi, inkqubo yokucoca ifakwe.

Isihlomelo esiqhelekileyo kunye noPhuculo oluPhambili

I-Excel inikeza isihlungi esilula esivela kwiTebhu yedata kwiNgesi yesiNgesi kwenkqubo okanye usebenzisa i-shortcut kwibar yamathuluzi ekhangeleka njengombhobho owenziwe ngumbono wokumpontshelwa kwamanzi kwisitya kunye nentamo emncinci.

Kwiimeko ezininzi, le fayili iyona ndlela efanelekileyo kakhulu. Kodwa, ukuba kufuneka ukhethe ukhetho oluninzi lweemeko (kunye neekholamu ezimbalwa, imiqolo kunye neeseli), abaninzi bayazibuza indlela yokwenza isihlungi esongezelelweyo kwi-Excel. Inguqulelo yesiNgesi ibizwa ngokuba yifayile ephezulu.

Ukusetyenziswa kokuqala kwecebo eliphambili

Kwi-Excel, uninzi lomsebenzi lwenziwe ngeetafile. Okokuqala, kulula, kwaye okwesibini, kwifayile enye ungagcina ulwazi kumaphepha amaninzi (iithebhu). Ngaphezulu kwetafile ephakamileyo kuyinqweneleka ukwakha imigqa emininzi, ephezulu ekushiyelwe yintloko, yile migca ukuba iifom yefayile ye Excel iya kufakwa. Kwixesha elizayo, icebo liya kuguqulwa ngokuqinisekileyo: ukuba kukho imfuneko engaphezulu, unako ukufaka enye umgca kwindawo efanelekileyo. Kodwa kuyinto enqwenelekayo, ukuba phakathi kweeseli zodidi lweemeko kunye neeseli zedatha yokuqala kuluhlu olungasetyenzisiweyo.

Indlela yokusebenzisa ifayile ephambili kwi-Excel, imizekelo, cinga ngezantsi.

Ukusebenzisa ifayile

Kule tafile ngasentla, imizila 1 kunye neyesi-2 yimiba yeemeko, imigca 4 kuya kwe-7 yimiba yeenkcukacha eziluhlaza.

Ukuqala, faka ixabiso elihambelana nomgca wesi-2, apho isihlomelo esongezelelweyo kwi-Excel siya kugxeka.

Ukuqala uluhlu lwefayile, khetha iiseli zedatha yomthombo, uze ukhethe ithebhu "Idatha" uze uklikhele iqhosha "eliphambili" ngokufanelekileyo.

Kwifestile evulekileyo, uluhlu lweeseli ezikhethiweyo kwi-"Ibanga lokuqala" liboniswa. Ngokomzekelo owenziwe, umtya uthatha ixabiso "$ A $ 4: $ F F $ 12".

Intsimi "Inkalo yesango" kufuneka izaliswe ngamaxabiso "$ A $ 1: $ F F $ 2".

Ifestile iqulethe iimeko ezimbini:

• Coca uluhlu kwindawo;
• Khuphela isiphumo kwenye indawo.

Isimo sokuqala sikwenza ukuba uvelise umphumo kwisithuba esabelwe iiseli zobume bokuqala. Isimo sesibini sikwenza ukuba udale uluhlu lweziphumo kwinqanaba elithile, elimele lichazwe kwi-"Indawo yesiphumo" kwintsimi. Umsebenzisi ukhetha ukhetho olulungileyo, umzekelo, wokuqala, iwindow "Advanced Filter" iwindlu e-Excel ivaliwe.

Ngokusekelwe kwidata engenisiweyo, icebo liza kuvelisa itafile elandelayo.

Ukuba usebenzisa "Khuphela umphumo kwenye indawo", imilinganiselo esuka kumigca e-4 neye-5 iya kuboniswa kwinqanaba elichazwe ngumsebenzisi. Uluhlu lwangaphambili aluhlali luguqukile.

Ukulula ukusebenzisa

Indlela echazweyo ayifanelekanga, ngoko ke ukuphucula kusetyenziswa ulwimi lweprogram lwe-VBA, olusetyenziswa ukwenza i-macros evumela ukuba i-automated i-filter ephambili kwi-Excel.

Ukuba umsebenzisi unolwazi lwe-VBA, kucetyiswa ukuba ufunde amanqaku amanqaku kwesi sihloko kwaye uphumelele ngokuphumelelayo isicwangciso. Ukuba utshintshe ixabiso leeseli zomgca wesi-2, isihlomelo esongezelelweyo esabelwe kwi-Excel, uluhlu lweemeko luya kutshintsha, izitrato ziya kuhlaziywa kwakhona, ziqaliswe ngokutsha kwaye ulwazi oluyimfuneko luyakhiwa kwinqanaba elifunekayo.

Imibuzo enzima

Ukongeza kokusebenza ngamaxabiso achazwe ngokuchanekileyo, icandelo eliphambili kwi-Excel liyakwazi ukusingatha imibuzo enzima. Le yile data engeniswe kuyo, apho inxalenye yabalinganiswa ithatyathwa yi-wildcards.

Itheyibhile yecatshulwa yemibuzo enzima iboniswe ngezantsi.

Kufanelekile ukwazi ukuba isimboli sinokuthi nayiphi na inombolo yabalinganiswa. Oko kukuthi, ngexabiso elithengiweyo "n *" zonke ixabiso ziya kubuyiselwa, kungakhathaliseki ukuba inani labalinganiswa emva kwencwadi "n".

"?" Uphawu lithetha umlingisi omnye.

Iimpahla ze-OR kunye NA

Kufuneka uqaphele ukuba ulwazi oluchazwe ngumgca omnye kwi "Uluhlu lwesimo" lubonwa njengento ebhaliweyo (AND AND) kwisithintelo. Oku kuthetha ukuba iimeko ezininzi zizaliseka kanyekanye.

Ukuba idatha ibhaliwe kwikholam enye, isihlomelo esongezelelweyo kwi-Excel siyabonwa ngumsebenzisi ochaphazelekayo (OR).

Itafile yexabiso lithatha le fomu elandelayo:

Itafile zishwankathelo

Enye indlela yokucoca idatha ngokusebenzisa umyalelo we-Insert-Table-Pivotable on inguqulo yesiNgesi.

Iitheyibhile ezikhankanywe ngasentla zisebenza ngendlela efanayo kunye nohlu lweenkcukacha ezabelwe ngaphambili kwaye ukhethe amanani athile ukuba ahlalutye kwakhona. Enyanisweni, kubonakala ngathi kusebenza uluhlu oluhlayo lwemihlaba ekhethekileyo (umzekelo, amagama omqeshwa wenkampani) kwaye uluhlu lwamaxabiso aboniswayo xa kukho intsimi ekhethekileyo.

Ukuphazamiseka kokusebenzisa iitafile ze-pivot kuyimfuneko yokwenza ulungelelanise idatha yangaphambili xa utshintsha idatha enjalo.

Isiphelo

Ekugqibeleni, kufuneka kuqatshelwe ukuba ububanzi befayile kwiMicrosoft Excel bubanzi kakhulu kwaye buhluka. Kwanele ukusebenzisa iingcamango nokuphuhlisa ulwazi lwakho, izakhono kunye nobuchule.

Ihluzo ngokwalo kulula ukuyisebenzisa kwaye lifunde, kulula ukuqonda indlela yokusebenzisa i-filter ephambili kwi-Excel, kodwa yenzelwe amaxesha xa kuyimfuneko ukuhlunga ulwazi malunga nexesha elincinci lokuqhubela phambili. Njengomthetho, awuboneleli umsebenzi kunye nemigca emininzi yolwazi ngenxa yezinto eziqhelekileyo zomntu. Apha iikhompyutheni ezicamngceleyo neziphambili zokucwangcisa ulwazi kwi-Microsoft Excel ziza kuhlangula.

I-Macros ebhalwe ngolwimi lwe-VBA lithandwa kakhulu. Ziyakuvumela ukuba usebenze inani elininzi lokuhlunga olukunceda ukhethe amanani kunye nokuvelisa kwiindawo ezifanelekileyo.

I-Macros ithatha indawo yexesha elide lomsebenzi ukwenza iifom zengqesho ukuhlanganisa isishwankathelo, ngezihlandlo kunye nokunye ukunika ingxelo, ngokufaka ixesha elide lokuhlalutya kweempahla ezinkulu kunye neklikhe enye.

Ukusetyenziswa kwe-macros kuchanekile kwaye kuphazamisekile. Nabani na oye wahlangabezana nesidingo sokufaka isicelo, uya kuhlala efumana, ukuba ufuna, izinto ezikwakhayo ukuphuhlisa ulwazi lwabo kwaye uphendule iimpendulo kwimibandela yomdla.