Kubvisa mitsara isina chinhu uye makoramu mune data

Mitsara isina chinhu uye makoramu zvinogona kurwadza mumatafura muzviitiko zvakawanda. Mashandiro akajairwa ekugadzirisa, kusefa, kupfupikisa, kugadzira matafura epivot, nezvimwewo vanoona mitsara isina chinhu nemakoramu sepafura yetafura, pasina kutora data iri kuseri kwavo. Kana pane mapeji akawanda akadaro, ipapo kuabvisa nemaoko kunogona kudhura zvikuru, uye hazvizoshandi kubvisa zvose kamwechete "muzhinji" uchishandisa kusefa, nokuti sefa "inogumburwa" pakuputika.

Ngatitarisei nzira dzinoverengeka dzekugadzirisa dambudziko iri.

Nzira 1. Tsvaga masero asina chinhu

Iyi inogona kunge isiri iyo yakanyanya kunaka, asi zvechokwadi nzira iri nyore yakakodzera kutaurwa.

Ngatitii tiri kubata netafura yakadaro ine mitsetse yakawanda isina chinhu uye makoramu mukati (yakasimudzwa kuti ijekeswe):

Ngatitii tine chokwadi chekuti koramu yekutanga yetafura yedu (column B) inogara ine zita reguta. Ipapo maseru asina chinhu mukoramu iyi ichave chiratidzo chemitsetse isina chinhu. Kuti ubvise zvese nekukurumidza, ita zvinotevera:

  1. Sarudza nzvimbo ine maguta (B2:B26)
  2. Dhinda kiyi F5 uye pfuurira Highlight (Enda kuSpecial) kana sarudza pane tab Musha — Tsvaga uye Sarudza — Sarudza boka remasero (Kumba - Tsvaga & Sarudza - Enda kune yakakosha).
  3. Muhwindo rinovhurwa, sarudza sarudzo Masero asina chinhu (Zvisina basa) uye tigoshingairira OK - maseru ese asina chinhu muchikamu chekutanga chetafura yedu anofanira kusarudzwa.
  4. Zvino sarudza pane tab Home murayiro Delete - Delete mitsara kubva papepa (Delete - Delete rows) kana kudzvanya nzira yekudimbudzira kiibhodhi Ctrl+Minus - uye basa redu rinogadziriswa.

Ehe, unogona kubvisa makoramu asina chinhu nenzira imwecheteyo, uchishandisa iyo tafura musoro sehwaro.

Nzira 2: Tsvaga mitsara isina chinhu

Sezvaungave watofunga, iyo yapfuura nzira inongoshanda chete kana data yedu iine yakazara yakazara mitsara nemakoramu, ayo anogona kusungirirwa paari kutsvaga masero asina chinhu. Asi zvakadini kana pasina chivimbo chakadaro, uye data inogona kunge iine masero asina chinhu zvakare?

Tarisa patafura inotevera, semuenzaniso, kune yakadaro kesi:

Heino nzira yacho ichava diki tricker:

  1. Pinda muchitokisi A2 basa NY (COUNTA), iyo inoverenga nhamba yemasero akazadzwa mumutsara wekurudyi uye kukopa iyi fomula pasi kutafura yese:
  2. Sarudza cell A2 wobatidza sefa nemurairo Data – Sefa (Data - Sefa) kana nzira yekudimbudzira keyboard Ctrl+kusuduruka+L.
  3. Ngatisefai mazero nekoramu yakaverengerwa, kureva mitsetse yese pasina data.
  4. Inoramba ichisarudza mitsara yakasvibiswa uye kuibvisa nemurairo Musha - Delete -' Delete mitsara kubva pashizha (Kumba - Delete - Delete rows) kana nzira yekudimbudzira keyboard Ctrl+Minus.
  5. Isu tinodzima sefa uye tinotora data redu pasina mitsetse isina chinhu.

Nehurombo, hunyengeri uhwu hahuchakwanisa kuitwa nemakoramu - Excel haisati yadzidza kusefa nemakoramu.

Nzira 3. Macro kubvisa mitsara yose isina chinhu uye makoramu papepa

Iwe unogona zvakare kushandisa iri nyore macro kuita otomatiki basa iri. Dzvanya nzira yekudimbudzira kiibhodhi alt+F11 kana sarudza kubva pane tab yokuvaka - Visual Basic (Mugadziri - Visual Basic Edhita). Kana ma tabo yokuvaka haisi kuoneka, unogona kuigonesa kuburikidza Faira - Sarudzo - Ribhoni Setup (Faira - Sarudzo - Gadzirisa Ribhoni).

MuVisual Basic editor hwindo rinovhura, sarudza murairo wemenyu Pinza – Module uye mune isina chinhu module inoonekwa, kopira uye unamate mitsara inotevera:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Zvadaro Kana Kana rng Hachizi Chiro Zvadaro Set rng = Mitsara(r) Zvimwe Set rng = Union(rng, Mitsetse(r)) End Kana Inotevera r Kana Isiri rng Hapana Chiripo Ipapo rng.Delete 'удаляем пустые столбцы Set rng = Hapana For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Zvino Kana rng Isipo Zvadaro Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End Kana Inotevera r Kana Isiri rng Hapana Chinhu Ipapo rng.Delete End Sub

Vhara mupepeti uye dzokera kuExcel. 

Zvino rova ​​​​musanganiswa alt+F8 kana bhatani macro Tab yokuvaka. Iwindo rinovhura rinonyora ese macros aripo kwauri kuti umhanye, kusanganisira iyo macro yauchangobva kugadzira. DeleteEmpty. Sarudza iyo uye tinya bhatani manya (mhanya) - yese mitsara isina chinhu uye makoramu pane pepa achabviswa ipapo ipapo.

Nzira 4: Power Query

Imwe nzira yekugadzirisa dambudziko redu uye yakajairika mamiriro ndeyekubvisa mitsara isina chinhu uye makoramu muPower Query.

Kutanga, ngatiisei tafura yedu muPower Query Query Editor. Unogona kuishandura kuita ine simba "smart" imwe ine keyboard yekudimbudzira Ctrl + T kana ingosarudza yedu data renji woipa zita (semuenzaniso. Data) mune formula bar, ichishandura kune zita:

Iye zvino isu tinoshandisa iyo yekuraira Dhata - Tora data - Kubva patafura / renji (Data - Tora Dhata - Kubva patafura / renji) uye rongedza zvese muSimba Query:

Ipapo zvese zviri nyore:

  1. Isu tinodzima mitsara isina chinhu nekuraira Kumba - Deredza mitsetse - Bvisa mitsetse - Bvisa mitsara isina chinhu (Kumba - Bvisa Mitsetse - Bvisa mitsara isina chinhu).
  2. Tinya-kurudyi pamusoro weiyo yekutanga Guta column uye sarudza iyo Unpivot Mamwe Columns kuraira kubva pane yemukati menyu. Tafura yedu ichave, sezvainodanwa nenzira kwayo, zvakajairwa - yakashandurwa kuita makoramu matatu: guta, mwedzi uye kukosha kubva pamharadzano yeguta uye mwedzi kubva patafura yekutanga. Hunhu hwekuvhiya uku muPower Query ndeyekuti inosvetuka maseru asina chinhu mune sosi data, ndizvo zvatinoda:
  3. Iye zvino tinoita reverse oparesheni - tinoshandura tafura inoguma zvakare kuita maviri-dimensional imwe kuitira kuti idzorere kune yayo yekutanga fomu. Sarudza koramu ine mwedzi uye pane tab Kuchinja sarudza timu Pivot column (Shandura - Pivot Column). Mufafitera rinovhurwa, sekoramu yezvakakosha, sarudza yekupedzisira (Kukosha), uye mune zvepamberi sarudzo - kushanda. Usaunganidza (Usabatanidza):
  4. Izvo zvinosara kurodha mhedzisiro kudzokera kuExcel nemurairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…)

  • Chii chinonzi macro, chinoshanda sei, kupi kukopa zvinyorwa zvemacro, maitiro ekumhanyisa macro?
  • Kuzadza ese asina chinhu maseru mune rondedzero nehunhu hwemaseru evabereki
  • Kubvisa ese asina chinhu maseru kubva kune yakapihwa renji
  • Kubvisa mitsara yese isina chinhu mupepa rebasa nePLEX yekuwedzera

Leave a Reply