Mukati
Tine matafura maviri (somuenzaniso, yekare uye itsva shanduro yemutengo runyorwa), izvo zvatinofanira kuenzanisa uye nekukurumidza kuwana misiyano:
Pakarepo zviri pachena kuti chimwe chinhu chakawedzerwa kune itsva mutengo runyorwa (mazuva, gariki ...), chinhu chakanyangarika (blackberries, raspberries ...), mitengo yachinja kune zvimwe zvinhu (maonde, mavise ...). Iwe unofanirwa kuwana nekukurumidza uye kuratidza shanduko dzese idzi.
Kune chero basa muExcel, pane inenge nguva dzose inopfuura imwe mhinduro (kazhinji 4-5). Kune dambudziko redu, nzira dzakawanda dzakasiyana dzinogona kushandiswa:
- basa VPR (VLOOKUP) - tsvaga mazita echigadzirwa kubva kune itsva mutengo runyorwa mune yekare uye ratidza mutengo wekare padivi peiyo nyowani, wobva wabata mutsauko.
- batanidza rondedzero mbiri mune imwe uye wobva wavaka tafura yepivot yakavakirwa pairi, apo misiyano ichanyatso kuoneka
- shandisa Power Query Add-in yeExcel
Ngatitorei dzose muhurongwa.
Nzira 1. Kuenzanisa matafura nebasa reVLOOKUP
Kana iwe usina kujairana nechinhu ichi chinoshamisa, saka tanga watarisa pano uye uverenge kana kuona vhidhiyo tutori pairi - zviponese makore akati wandei ehupenyu.
Kazhinji, basa iri rinoshandiswa kudhonza data kubva kune imwe tafura kuenda kune imwe nekufananidza imwe yakafanana parameter. Mune ino kesi, isu tichaishandisa kusundira mitengo yekare mumutengo mutsva:
Izvo zvigadzirwa, izvo #N/A kukanganisa kwakaitika, hazvisi mune yekare runyorwa, kureva kuti dzakawedzerwa. Kuchinja kwemitengo kunoonekwawo zvakajeka.
zvayakanakira nzira iyi: iri nyore uye yakajeka, "classic of the genre", sezvavanotaura. Inoshanda mune chero vhezheni yeExcel.
nezvayakaipira aripowo. Kuti utsvage zvigadzirwa zvakawedzerwa kune runyorwa rwemitengo mitsva, unozofanirwa kuita maitiro akafanana nenzira yakapesana, kureva kukwidza mitengo mitsva pamutengo wekare nerubatsiro rweVLOOKUP. Kana hukuru hwematafura huchichinja mangwana, ipapo mafomula achafanirwa kugadziridzwa. Zvakanaka, uye pamatafura akakura chaizvo (> 100 zviuru mitsara), mufaro uyu wese uchadzikira.
Nzira 2: Kuenzanisa matafura uchishandisa pivot
Ngatikopei matafura edu imwe pasi peimwe, tichiwedzera koramu ine zita rezita rematengo, kuitira kuti gare gare iwe unzwisise kubva pane rondedzero ipi mutsara:
Iye zvino, zvichibva patafura yakagadzirwa, tichagadzira pfupiso kuburikidza Pinza – PivotTable (Isa - Pivot Tafura). Ngatikande munda chigadzirwa kunzvimbo yemitsara, munda mutengo kune column nzvimbo uye munda Цena muchikamu:
Sezvauri kuona, tafura yepivot inongogadzira rondedzero yezvese zvigadzirwa kubva kune yekare uye mitsva yemitengo rondedzero (hapana kudzokorora!) uye rongedza zvigadzirwa nemaarufabheti. Iwe unogona kuona zvakajeka zvigadzirwa zvakawedzerwa (hazvina mutengo wekare), zvigadzirwa zvakabviswa (hazvina mutengo mutsva) uye kuchinja kwemitengo, kana iripo.
Mahombe akakwana mutafura yakadaro haana musoro, uye anogona kuremara pane tebhu Muvaki - Majumba makuru - Dzima mitsara nemakoramu (Design - Grand Totals).
Kana mitengo ikachinja (asi kwete huwandu hwezvinhu!), saka zvakakwana kungovandudza pfupiso yakagadzirwa nekudzvanya-kurudyi pairi - fefeta.
zvayakanakira: Iyi nzira ndeyekurongeka kwehukuru nekukurumidza nematafura makuru kupfuura VLOOKUP.
nezvayakaipira: Unoda kukopa nemaoko data pasi peumwe neumwe uye kuwedzera koramu ine zita rezita remutengo. Kana hukuru hwematafura huchichinja, saka iwe unofanirwa kuita zvese zvakare.
Nzira 3: Kuenzanisa matafura neMasimba Query
Power Query ndeye yemahara yekuwedzera-mukati yeMicrosoft Excel iyo inokutendera iwe kurodha data muExcel kubva kunenge chero kupi sosi uye wozoshandura iyi data mune chero nzira yaunoda. MuExcel 2016, iyi yekuwedzera yakatovakwa mukati nekusarudzika pane iyo tab Data (Data), uye yeExcel 2010-2013 iwe unofanirwa kuidhawunirodha zvakasiyana kubva pawebhusaiti yeMicrosoft uye woiisa - tora tebhu nyowani. Simba Query.
Tisati taisa rondedzero yedu yemitengo muPower Query, inofanirwa kutanga yashandurwa kuita matafura akangwara. Kuti uite izvi, sarudza huwandu ne data uye dzvanya musanganiswa pane keyboard Ctrl+T kana kuti sarudza tab iri paribhoni Kumba – Fomati setafura (Kumba - Fomati seTafura). Mazita ematafura akagadzirwa anogona kugadziriswa pane tab Constructor (Ini ndichasiya standard Tafura 1 и Tafura 2, izvo zvinowanikwa nekusarudzika).
Rodha mutengo wekare muPower Query uchishandisa bhatani Kubva Tafura/Range (Kubva Tafura/Range) kubva kune tab Data (Zuva) kana kubva pane tab Simba Query (zvichienderana neshanduro yeExcel). Mushure mekurodha, isu tichadzokera kuExcel kubva kuPower Query nemurairo Vhara uye takura - Vhara uye rodha mukati... (Vhara & Rodha - Vhara & Rodha Ku…):
... uye pahwindo rinoonekwa wobva wasarudza Ingogadzira chinongedzo (Kubatanidza Chete).
Dzokorora zvakafanana nerutsva rwemitengo.
Zvino ngatigadzirei mubvunzo wechitatu unozobatanidza nekufananidza data kubva kune maviri apfuura. Kuti uite izvi, sarudza muExcel pane tab Dhata - Tora Dhata - Sanganisa Zvikumbiro - Sanganisa (Data - Tora Dhata - Unganidza Mibvunzo - Unganidza) kana kudzvanya bhatani Gadzirai (Batanidza) Tab Simba Query.
Muhwindo rekujoinha, sarudza matafura edu muzvinyorwa zvekudonhedza, sarudza makoramu ane mazita ezvigadzirwa zviri mazviri, uye pazasi, isa nzira yekubatanidza - Zadzisa zvekunze (Kunze Kwakazara):
Mushure mekunge mavharidza OK tafura yemakoramu matatu inofanira kuoneka, apo mumutsara wechitatu unoda kuwedzera zviri mukati mematafura anested uchishandisa museve wakapetwa kaviri mumusoro:
Nekuda kweizvozvo, tinowana kubatanidzwa kwedata kubva kumatafura ese ari maviri:
Zviri nani, hongu, kutumidza mazita emakoramu mumusoro nekudzvanya kaviri pane zvinonzwisisika:
Uye zvino inonyanya kufadza. Enda kune tab Wedzera ikholamu (Wedzera Koramu) uye tinya bhatani Conditional column (Conditional Column). Uye ipapo pahwindo rinovhurwa, isa akati wandei bvunzo mamiriro neanoenderana ayo anobuda maitiro:
Rinosara kudzvanya OK uye rodha mushumo unoguma kuExcel uchishandisa bhatani rimwechete kuvhara uye kudhawunirodha (Vhara & Rodha) Tab Home (Musha):
Runako.
Uyezve, kana paine shanduko dzikaitika mumitengo yemitengo mune ramangwana (mitsetse inowedzerwa kana kubviswa, mitengo inoshanduka, nezvimwewo), zvino zvichave zvakakwana kungovandudza zvikumbiro zvedu nekapfupi kekhibhodi. Ctrl+alt+F5 kana nebhatani Zorodza zvese (Simudza Zvese) Tab Data (Zuva).
zvayakanakira: Pamwe ndiyo yakanakisa uye yakanakira nzira yezvose. Inoshanda zvine hungwaru nematafura makuru. Hazvidi kugadziridzwa kwemaoko paunenge uchiisa saizi yematafura.
nezvayakaipira: Inoda iyo Power Query yekuwedzera-mukati (muExcel 2010-2013) kana Excel 2016 kuti iiswe. Mazita emakoramu ari mudura re data haafanire kuchinjwa, zvikasadaro isu tichawana kukanganisa "Koramu yakadai nezvakadaro haina kuwanikwa!" pakuedza kugadzirisa mubvunzo.
- Maitiro ekuunganidza data kubva kune ese eExcel mafaera mune yakapihwa folda uchishandisa Power Query
- Maitiro ekutsvaga machisi pakati pezvinyorwa zviviri muExcel
- Kubatanidza mazita maviri pasina kudzokorora