Munguva ichangopfuura, shamwari yangu yakauya kwandiri nechikumbiro chekubatsira nechizvarwa chemashoko ose anobvira anosanganisira seti yemashoko akapiwa. Matambudziko erudzi urwu anogona kusimuka kana uchigadzira rondedzero yemazwi akakosha uye mitsara yekushambadzira kwepamhepo uye SEO kukwidziridzwa, kana iwe uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchinge uchibvumidza mazwi emvumo yemubvunzo wekutsvaga:
Mumasvomhu, oparesheni iyi inodanwa Cartesian chigadzirwa. Tsanangudzo yepamutemo ndeiyi: Cartesian chigadzirwa cheseti A uye B ndiyo seti yezviviri zvese, chikamu chekutanga chinova cheseti A, uye chikamu chechipiri ndecheiyo set B. Uyezve, zvinhu zveseti zvinogona kuva zvese. nhamba uye zvinyorwa.
Kushandurirwa mumutauro wevanhu, izvi zvinoreva kuti kana mune set A tine, semuenzaniso, mazwi "chena" uye "tsvuku", uye muB "BMW" uye "Mercedes", zvino mushure meCartesian chigadzirwa chezvikamu zviviri izvi isu. kuwana pane zvakabuda ndiyo seti yezvese zvingango siyana zvakasiyana zvemitsara, inogadzirwa nemazwi emazita ese ari maviri:
- white bmw
- red bmw
- white Mercedes
- red mercedes
... kureva izvo zvatinoda chete. Ngatitarisei nzira dzinoverengeka dzekugadzirisa basa iri muExcel.
Nzira 1. Zvirongwa
Ngatitange nemaformula. Ngatifungei kuti sedhata rekutanga tine rondedzero nhatu dzemazwi ekutanga mumakoramu A, B uye C, zvichiteerana, uye huwandu hwezvinhu mune yega runyorwa zvinogona kusiyana:
Chekutanga, ngatiite makoramu matatu ane indices, kureva manhamba emazwi kubva parunyorwa rwega rwega mumisanganiswa yese inobvira. Mutsara wekutanga wemayuniti (E2: G2) uchapinzwa nemaoko, uye kune mamwe ese isu tichashandisa inotevera fomula:
Mafungiro ari pano akareruka: kana index iri musero repamusoro rakapfuura rakatosvika kumagumo erondedzero, kureva yakaenzana nenhamba yezvinhu zviri murunyoro zvakaverengerwa nebasa. NY (COUNTA), tobva tatangazve manhamba. Zvikasadaro, isu tinowedzera index ne 1. Nyatsoteerera kune yakangwara kugadzirisa mitsara ine dhora zviratidzo ($) kuitira kuti iwe ugone kukopa formula pasi uye kurudyi.
Zvino zvatava nenhamba dze ordinal dzemazwi atinoda kubva pane yega rondedzero, tinogona kuburitsa iwo mazwi pachawo tichishandisa basa INDEX (INDEX) muzvikamu zvitatu zvakasiyana:
Kana usati wamboona basa iri mubasa rako kare, saka ndinokurayira zvakasimba kuti uidzidze zvishoma neine diagonally - inobatsira mumamiriro ezvinhu akawanda uye haibatsiri zvishoma (uye zvakatonyanya!) VPR (VLOOKUP).
Zvakanaka, mushure meizvozvo, zvinongosara kungonamira zvimedu zvinoguma mutsara nemutsara uchishandisa concatenation chiratidzo (&):
... kana (kana iwe uine yazvino vhezheni yeExcel) ine inoshanda basa COMBINE (TEXTJOIN), iyo inogona kunama zvese zviri mukati memaseru akatsanangurwa kuburikidza neakapihwa separator hunhu (nzvimbo):
Nzira 2. Kuburikidza Nesimba Mubvunzo
Power Query isimba rekuwedzera reMicrosoft Excel rinoita mabasa makuru maviri: 1. kurodha data muExcel kubva kunenge chero kunze kwekunze, uye 2. marudzi ese eshanduko yematafura akaremerwa. Power Query yakatovakwa muExcel 2016-2019, uye yeExcel 2010-2013 yakaiswa seyakasiyana yekuwedzera-mukati (iwe unogona kuidhawunirodha kubva kune yepamutemo Microsoft webhusaiti mahara). Kana usati watanga kushandisa Power Query mubasa rako, saka yave nguva yekufunga nezvazvo, nekuti shanduko senge idzo dzatsanangurwa pamusoro apa dzinoitwa ipapo nyore uye nemasikirwo, mune mashoma ekufamba.
Chekutanga, ngatirodzei zvinyorwa zvinyorwa semibvunzo yakaparadzana muPower Query. Kuti uite izvi, patafura yega yega, ita zvinotevera:
- Ngatisandurei matafura kuita “akangwara” nebhatani Fomati setafura Tab Home (Kumba - Fomati seTafura) kana nzira yekudimbudzira keyboard Ctrl+T. Tafura yega yega inozongopihwa zita Tafura1,2,3…, iyo, zvisinei, inogona kuchinjwa kana ichidiwa pane tab Constructor (Dhizaini).
- Waseta sero inoshanda mutafura, tinya bhatani Kubva patafura (Kubva Tafura) Tab Data (Zuva) kana kuti pane tab Simba Query (kana iwe uine iyo yakaiswa seyakasiyana yekuwedzera-mukati yeExcel 2010-2013).
- Mumubvunzo wemupepeti hwindo rinovhurwa, sarudza rairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku..) uye ipapo sarudzo Ingogadzira chinongedzo (Gadzira chinongedzo chete). Izvi zvichasiya tafura yakatakurwa mundangariro uye ichibvumira kuti iwanikwe mune ramangwana.
Kana iwe ukaita zvese nemazvo, zvino izvo zvinobuda mupaneru yerudyi zvinofanirwa kunge zviri zvikumbiro zvitatu mumodhi Kubatanidza chete nemazita etafura yedu:
Zvino tinya-kurudyi pamubvunzo wekutanga uye sarudza murairo batanidzo (Referensi)kugadzira kopi inogadziridzwa yayo, uye wobva wawedzera imwe column kune data kuburikidza nekuraira Kuwedzera koramu ž – Tsika mbiru (Wedzera Column -ž Custom Column). Mufafitera rekuisa fomula, isa zita rekoromomu itsva (semuenzaniso, Fragment2) uye chirevo chakareruka kwazvo seformula:
=Tafura2
... kureva, nemamwe mazwi, zita remubvunzo wechipiri:
Mushure mekunge mavharidza OK tichaona mbiru itsva, musero rega rega rinenge paine tafura inested ine mitsara kubva patafura yechipiri (iwe unogona kuona zviri mumatafura aya kana ukadzvanya kumashure kwesero padivi peshoko. tafura):
Inosara yekuwedzera zvese zviri mukati meaya matafura anested uchishandisa bhatani rine miseve yakapetwa kaviri mumusoro wekoramu inobuda uye kusatarisisa. Shandisa zita rekutanga rechikamu sechivakashure (Shandisa zita rekoramu rekutanga sechivakashure):
... uye isu tinowana ese anobvira musanganiswa wezvinhu kubva kumaseti maviri ekutanga:
Uyezve, zvinhu zvose zvakafanana. Wedzera imwe koramu yakaverengerwa neformula:
=Tafura3
..., wobva wawedzera matafura akaiswa matendere zvakare - uye ikozvino tatova nesarudzo dzese dzekubvumidza mazwi kubva kumaseti matatu, zvichiteerana:
Izvo zvinosara kusarudza ese matatu makoramu kubva kuruboshwe kuenda kurudyi, akabata Ctrl, uye concatenate zvirimo zvakapatsanurwa nenzvimbo uchishandisa rairo Batanidza makoramu (Batanidza Makoramu) kubva kune tab Kuchinja (Shandura):
Mhedzisiro inobuda inogona kudzoserwa pabepa nemurairo wagara uchizivikanwa Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku..):
Kana mune ramangwana chimwe chinhu chichichinja mumatafura edu ane zvimedu, zvino zvichave zvakakwana kungo gadzirisa mubvunzo wakagadzirwa nekudzvanya-kurudyi patafura inobuda uye kusarudza murairo. Gadziridza & Chengeta (Refresh) kana nekudzvanya nzira yekudimbudzira kiibhodhi Ctrl+alt+F5.
- Chii chinonzi Power Query, Power Pivot, Power Mepu uye Power BI uye nei vachida mushandisi weExcel
- Kugadzira Gantt Chati mune Simba Mubvunzo
- 5 nzira dzekushandisa iyo INDEX basa