Mukati
Kugadziriswa kwedambudziko
Se data yekupinza, isu tine faira reExcel, uko rimwe remapepa rine akati wandei matafura ane data rekutengesa refomu rinotevera:
Cherechedza kuti:
- Matafura ehukuru hwakasiyana uye nemaseti akasiyana ezvigadzirwa uye matunhu mumitsara nemakoramu pasina kurongeka.
- Mitsetse isina chinhu inogona kuiswa pakati pematafura.
- Nhamba yematafura inogona kuva chero.
Mafungiro maviri akakosha. Zvinofungidzirwa kuti:
- Pamusoro petafura imwe neimwe, mumutsara wekutanga, pane zita remaneja uyo anotengeswa netafura (Ivanov, Petrov, Sidorov, nezvimwewo)
- Mazita ezvigadzirwa uye nharaunda mumatafura ose akanyorwa nenzira imwechete - nemhosva yakarurama.
Chinangwa chekupedzisira ndechekuunganidza data kubva kumatafura ese mune imwe furati yakajairwa tafura, yakakodzera kuongororwa kunotevera uye kuvaka pfupiso, kureva mune ino:
Danho 1. Batanidza kune faira
Ngatigadzire itsva isina chinhu Excel faira uye sarudza iyo pane iyo tab Data murayiro Tora Dhata - Kubva Faira - Kubva Bhuku (Data - Kubva faira - Kubva mubhuku rebasa). Rondedzera nzvimbo yefaira faira ine data yekutengesa uye ipapo mufafitera rekufamba sarudza pepa ratinoda uye tinya bhatani. Shandura Data (Shandura data):
Nekuda kweizvozvo, data rese kubva kwairi rinofanirwa kuiswa muPower Query edhita:
Danho 2. Chenesa marara
Delete otomatiki matanho modified type (Changed Type) и Misoro yakasimudzwa (Misoro Yakasimudzirwa) uye bvisa mitsara isina chinhu uye mitsetse ine totals uchishandisa sefa null и TOTAL nechikamu chekutanga. Somugumisiro, tinowana mufananidzo unotevera:
Danho 3. Kuwedzera vatungamiri
Kuti unzwisise gare gare kuti ndeapi anotengeswa, zvakakosha kuwedzera mutsara patafura yedu, apo mumutsara mumwe nomumwe pachava nezita rinoenderana. Nokuda kweizvi:
1. Ngatiwedzerei mbiru yebetsero ine nhamba dzemitsara tichishandisa murairo Wedzera Column - Index Column - Kubva 0 (Wedzera koramu - Index column - Kubva 0).
2. Wedzera koramu ine formula ine murairo Kuwedzera Column - Custom Column (Wedzera koramu - Tsika mbiru) uye zivisa zvinotevera kuvaka ikoko:
Mafungiro eforamu iyi ari nyore - kana kukosha kwesero rinotevera muchikamu chekutanga chiri "Chigadzirwa", saka izvi zvinoreva kuti takagumburwa nekutanga kwetafura nyowani, saka tinoratidza kukosha kwesero rakapfuura ne zita ramaneja. Zvikasadaro, isu hatiratidzi chinhu, kureva null.
Kuti titore sero yemubereki ine zita rekupedzisira, isu tinotanga tanongedzera kutafura kubva padanho rekutanga #"Index yakawedzerwa", wobva watsanangura zita rekoramu yatinoda [Koramu1] mumabhuraketi masikweya uye nhamba yesero mukoramu iyoyo mumabhuraketi akamonana. Nhamba yesero ichave imwe yakaderera pane yazvino, iyo yatinotora kubva mukoramu indekisi, maererano.
3. Inoramba ichizadza masero asina chinhu nawo null mazita kubva kumasero epamusoro ane murairo Shandura – Zadza – Pasi (Shandura - Zadza - Pasi) uye dzima koramu isingachadiwi ine indices nemitsara ine mazita ekupedzisira mukoramu yekutanga. Somugumisiro, tinowana:
Danho 4. Kubatanidza mumatafura akasiyana nemamaneja
Nhanho inotevera ndeyekuisa mitsara yemaneja wega wega mumatafura akasiyana. Kuti uite izvi, pane Shanduko tebhu, shandisa Boka nekuraira (Shandura - Boka Ne) uye muhwindo rinovhurwa, sarudza iyo Maneja koramu uye oparesheni Yese mitsara (Yese mitsetse) kungounganidza data pasina kushandisa chero basa rekubatanidza ivo (sum, avhareji, nezvimwewo). P.):
Nekuda kweizvozvo, tinowana matafura akasiyana kune wega wega maneja:
Nhanho 5: Shandura Nested Tables
Iye zvino tinopa matafura anorara musero rimwe nerimwe rekoramu inoguma Yese data muchimiro chakanaka.
Chekutanga, bvisa koramu isingachadiwi patafura yega yega meneja. Isu tinoshandisa zvakare Custom column Tab Kuchinja (Shandura - Custom column) uye iyo fomula inotevera:
Zvino, neimwe koramu yakaverengerwa, tinosimudza mutsara wekutanga mutafura yega yega kune misoro:
Uye pakupedzisira, tinoita shanduko huru - kuzarura tafura imwe neimwe tichishandisa M-function Table.UnpivotOtherColumns:
Mazita ematunhu kubva kumusoro achapinda mukoromo itsva uye isu tichawana yakamanikana, asi panguva imwe chete, tafura yakareba yakajairwa. Masero asina chinhu ane null havana hanya.
Kubvisa zvisingakoshi zvepakati makoramu, isu tine:
Nhanho 6 Wedzera Nested Tafura
Inosara yekuwedzera ese akajairwa nested matafura mune imwechete runyorwa uchishandisa bhatani rine mbiri miseve mumusoro wekoromo:
... uye tinozowana zvataida:
Iwe unogona kutumira kunze tafura inoguma kudzokera kuExcel uchishandisa murairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…).
- Vaka matafura ane misoro yakasiyana kubva kumabhuku akawanda
- Kuunganidza data kubva kune ese mafaera mune yakapihwa folda
- Kuunganidza data kubva pamapepa ese ebhuku mune imwe tafura