Kuchotsa mizere yopanda kanthu ndi mizati mu data

Mizere yopanda kanthu ndi zipilala zimatha kukhala zowawa pamatebulo nthawi zambiri. Ntchito zokhazikika pakusanja, kusefa, kufupikitsa, kupanga ma pivot tables, ndi zina zambiri. amawona mizere yopanda kanthu ndi mizati ngati kusweka kwa tebulo, osatenga zomwe zili kumbuyo kwawo. Ngati pali mipata yambiri yotereyi, ndiye kuti kuchotsa pamanja kungakhale kokwera mtengo kwambiri, ndipo sikungagwire ntchito kuchotsa zonse mwakamodzi "zochuluka" pogwiritsa ntchito zosefera, chifukwa fyuluta nayonso "idzapunthwa" panthawi yopuma.

Tiyeni tione njira zingapo zothetsera vutoli.

Njira 1. Sakani ma cell opanda kanthu

Izi sizingakhale zabwino kwambiri, koma njira yosavuta ndiyoyenera kutchulidwa.

Tiyerekeze kuti tikuchita ndi tebulo lotere lomwe lili ndi mizere yopanda kanthu ndi zipilala mkati mwake (zowunikira kuti zimveke bwino):

Tiyerekeze kuti tikutsimikiza kuti gawo loyamba la tebulo lathu (gawo B) lili ndi dzina la mzinda nthawi zonse. Kenako ma cell opanda kanthu mugawoli adzakhala chizindikiro cha mizere yopanda kanthu. Kuti muchotse zonse mwachangu, chitani izi:

  1. Sankhani malo okhala ndi mizinda (B2:B26)
  2. Dinani fungulo F5 ndiyeno pezani yosangalatsa (Pitani ku Special) kapena sankhani pa tabu Kunyumba — Pezani ndi Sankhani — Sankhani gulu la ma cell (Kunyumba - Pezani&Sankhani - Pitani ku Chapadera).
  3. Pazenera lomwe limatsegulidwa, sankhani njira Maselo opanda kanthu (Zopanda kanthu) Ndi kukanikiza OK - maselo onse opanda kanthu mugawo loyamba la tebulo lathu ayenera kusankhidwa.
  4. Tsopano sankhani pa tabu Kunyumba lamulo Chotsani - Chotsani mizere papepala (Chotsani - Chotsani mizere) kapena dinani njira yachidule ya kiyibodi Ctrl+opanda - ndipo ntchito yathu yathetsedwa.

Zachidziwikire, mutha kuchotsa mizati yopanda kanthu chimodzimodzi, pogwiritsa ntchito mutu wa tebulo ngati maziko.

Njira 2: Sakani mizere yopanda kanthu

Monga momwe mwaganizira kale, njira yapitayi idzagwira ntchito ngati deta yathu ili ndi mizere yodzaza ndi mizere, yomwe imatha kulumikizidwa pofufuza maselo opanda kanthu. Koma bwanji ngati palibe chidaliro choterocho, ndipo deta ingakhalenso ndi maselo opanda kanthu?

Yang'anani pa tebulo ili, mwachitsanzo, pazochitika zotere:

Apa njira idzakhala yovuta kwambiri:

  1. Lowani mu cell A2 ntchitoyo COUNT (COUNTA), yomwe iwerengera kuchuluka kwa maselo odzazidwa mumzere kumanja ndikukopera fomulayi mpaka patebulo lonse:
  2. Sankhani selo A2 ndikuyatsa fyuluta ndi lamulo Zosefera - Zosefera (Deta - Sefa) kapena njira yachidule ya kiyibodi Ctrl+kosangalatsa+L.
  3. Tiyeni tisefe ziro ndi gawo lowerengeka, mwachitsanzo, mizere yonse pomwe palibe deta.
  4. Zimatsalira kusankha mizere yosefedwa ndikuwachotsa ndi lamulo Kunyumba - Chotsani -' Chotsani mizere papepala (Kunyumba - Chotsani - Chotsani mizere) kapena njira yachidule ya kiyibodi Ctrl+opanda.
  5. Timazimitsa fyuluta ndikupeza deta yathu popanda mizere yopanda kanthu.

Tsoka ilo, chinyengo ichi sichingachitikenso ndi mizati - Excel sinaphunzirebe kusefa ndi mizati.

Njira 3. Macro kuchotsa mizere yopanda kanthu ndi mizati pa pepala

Mutha kugwiritsanso ntchito macro yosavuta kuti mugwiritse ntchito ntchitoyi. Dinani njira yachidule ya kiyibodi alt+F11 kapena sankhani kuchokera pa tabu Woyambitsa - Visual Basic (Wopanga - Visual Basic Editor). Ngati ma tabo Woyambitsa sichikuwoneka, mutha kuyitsegula Fayilo - Zosankha - Kukhazikitsa Riboni (Fayilo - Zosankha - Sinthani Riboni).

Pawindo la Visual Basic editor lomwe limatsegula, sankhani lamulo la menyu Ikani - Module ndipo mugawo lopanda kanthu lomwe likuwoneka, koperani ndi kumata mizere iyi:

   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 Ndiye Ngati rng Si Kanthu Ndiye Ikani rng = Mizere (r) Else Set rng = Mgwirizano(rng, Mizere(r)) Kutha Ngati Chotsatira r Ngati Sichopanda Chilichonse Ndiye rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Ndiye Ngati rng Alibe Kanthu Ndiye Ikani rng = Mizati(r) Else Set rng = Union(rng, Columns( r)) Mapeto Ngati Next r Ngati si rng Palibe Ndiye rng.Delete End Sub

Tsekani mkonzi ndikubwerera ku Excel. 

Tsopano kugunda kuphatikiza alt+F8 kapena batani Macros tsamba Woyambitsa. Zenera lomwe limatsegula lidzalemba macros onse omwe akupezeka kuti muthe kuyendetsa, kuphatikiza macro omwe mwangopanga kumene. ChotsaniEmpty. Sankhani izo ndi kumadula batani Thamangani (thamanga) - mizere yonse yopanda kanthu ndi mizati pa pepala idzachotsedwa nthawi yomweyo.

Njira 4: Kufufuza Mphamvu

Njira ina yothetsera vuto lathu komanso zochitika zodziwika bwino ndikuchotsa mizere yopanda kanthu ndi mizere mu Power Query.

Choyamba, tiyeni tiyike tebulo lathu mu Power Query Query Editor. Mutha kuyisintha kukhala "yanzeru" yokhala ndi njira yachidule ya kiyibodi Ctrl + T kapena ingosankha mtundu wathu wa data ndikuupatsa dzina (mwachitsanzo. Deta) mu bar ya formula, kusinthidwa kukhala dzina:

Tsopano timagwiritsa ntchito lamulo la Data - Pezani deta - Kuchokera patebulo / mndandanda (Deta - Pezani Deta - Kuchokera patebulo / mndandanda) ndikuyika chirichonse mu Power Query:

Ndiye zonse ndi zophweka:

  1. Timachotsa mizere yopanda kanthu ndi lamulo Kunyumba - Chepetsani mizere - Chotsani mizere - Chotsani mizere yopanda kanthu (Kunyumba - Chotsani Mizere - Chotsani mizere yopanda kanthu).
  2. Dinani kumanja pamutu wagawo loyamba la City ndikusankha Unpivot Other Columns lamulo kuchokera pazosankha. Gome lathu lidzakhala, monga momwe limatchulidwira molondola, yokhazikika - osinthidwa kukhala mizati itatu: mzinda, mwezi ndi mtengo kuchokera pamzere wa mzinda ndi mwezi kuchokera patebulo loyambirira. Chodabwitsa cha ntchitoyi mu Power Query ndikuti imalumpha ma cell opanda kanthu muzochokera, zomwe ndizomwe tikufuna:
  3. Tsopano timagwira ntchito yobwereranso - timatembenuza tebulo lotsatila kukhala lamitundu iwiri kuti tibwerere ku mawonekedwe ake oyambirira. Sankhani ndime yokhala ndi miyezi komanso pa tabu Transformation sankhani gulu Pivot column (Sinthani - Pivot Column). Pazenera lomwe limatsegulidwa, monga mndandanda wazinthu, sankhani chomaliza (Value), ndipo pazosankha zapamwamba - ntchitoyo. Osaphatikiza (Osaphatikiza):
  4. Zimatsalira kukweza zotsatira kubwerera ku Excel ndi lamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…)

  • Kodi macro ndi chiyani, imagwira ntchito bwanji, komwe mungakopere zolemba za macro, momwe mungayendetsere macro?
  • Kudzaza ma cell opanda kanthu pamndandanda ndi ma cell a makolo
  • Kuchotsa ma cell onse opanda kanthu pagulu loperekedwa
  • Kuchotsa mizere yonse yopanda kanthu patsamba logwirira ntchito ndi chowonjezera cha PLEX

Siyani Mumakonda