Ikhipha imigqa namakholomu angenalutho kudatha

Imigqa namakholomu angenalutho kungaba buhlungu ematafuleni ezimweni eziningi. Imisebenzi ejwayelekile yokuhlunga, ukuhlunga, ukufingqa, ukudala amathebula e-pivot, njll. ibona imigqa namakholomu angenalutho njengokuhlukana kwethebula, ngaphandle kokucosha idatha etholakala ngemuva kwayo. Uma kunezikhala eziningi ezinjalo, khona-ke ukuzikhipha ngesandla kungase kubize kakhulu, futhi ngeke kusebenze ukususa konke ngesikhathi esisodwa "ngobuningi" usebenzisa ukuhlunga, ngoba isihlungi sizophinde "sikhubeke" ngesikhathi sokuphumula.

Ake sibheke izindlela ezimbalwa zokuxazulula le nkinga.

Indlela 1. Sesha amaseli angenalutho

Lokhu kungase kungabi lula kakhulu, kodwa nakanjani indlela elula efanele ukukhulunywa ngayo.

Ake sithi sibhekene nethebula elinjalo eliqukethe imigqa namakholomu amaningi angenalutho ngaphakathi (okuqokonyiswe ukuze kucace):

Ake sithi siyaqiniseka ukuthi ikholamu yokuqala yetafula lethu (ikholomu B) ihlale iqukethe igama ledolobha. Khona-ke amaseli angenalutho kule kholomu azoba uphawu lwemigqa engenalutho engadingekile. Ukuze uwasuse ngokushesha, yenza okulandelayo:

  1. Khetha ububanzi obunamadolobha (B2:B26)
  2. Cindezela inkinobho F5 bese ucindezela Qinisekisa (Iya kokuthi Okukhethekile) noma khetha kuthebhu Ikhaya — Thola bese Khetha — Khetha iqoqo lamaseli (Ikhaya — Thola & Khetha — Yiya kokukhethekile).
  3. Ewindini elivulayo, khetha inketho Amaseli angenalutho (Akuzi lutho) bese ucindezela OK – wonke amaseli angenalutho kukholamu yokuqala yetafula lethu kufanele akhethwe.
  4. Manje khetha kuthebhu Ikhaya Umyalo Susa – Susa imigqa eshidini (Susa - Susa imigqa) noma cindezela isinqamuleli sekhibhodi Ctrl+susa – futhi umsebenzi wethu usuxazululiwe.

Yebo, ungasusa amakholomu angenalutho ngendlela efanayo, usebenzisa unhlokweni wetafula njengesisekelo.

Indlela yesi-2: Sesha imigqa engenalutho

Njengoba ungase ucabange kakade, indlela yangaphambilini izosebenza kuphela uma idatha yethu iqukethe imigqa namakholomu agcwele ngokugcwele, okungaxhunywa kuwo lapho kuseshwa amaseli angenalutho. Kodwa kuthiwani uma kungekho ukuzethemba okunjalo, futhi idatha ingase ibe namaseli angenalutho futhi?

Bheka ithebula elilandelayo, isibonelo, esimweni esinjalo:

Lapha indlela izoba yinkimbinkimbi kancane:

  1. Faka kuseli A2 umsebenzi I-COUNT (COUNTA), ezobala inani lamaseli agcwalisiwe kumugqa oya kwesokudla bese ikopisha le fomula phansi kulo lonke ithebula:
  2. Khetha iseli A2 bese uvule isihlungi ngomyalo Idatha – Hlunga (Idatha — Isihlungi) noma isinqamuleli sekhibhodi Ctrl+Shift+L.
  3. Masihlunge oziro ngekholomu ebaliwe, okungukuthi yonke imigqa lapho ingekho khona idatha.
  4. Kusele ukukhetha imigqa ehlungiwe bese uyisusa ngomyalo Ikhaya - Susa -' Susa imigqa eshidini (Ekhaya - Susa - Susa imigqa) noma isinqamuleli sekhibhodi Ctrl+susa.
  5. Sivala isihlungi futhi sithole idatha yethu ngaphandle kwemigqa engenalutho.

Ngeshwa, leli qhinga alisakwazi ukwenziwa ngamakholomu - i-Excel ayikafundi ukuhlunga ngamakholomu.

Indlela 3. Imakhro ukukhipha yonke imigqa namakholomu angenalutho eshidini

Ungasebenzisa futhi i-macro elula ukwenza lo msebenzi ngokuzenzakalelayo. Cindezela isinqamuleli sekhibhodi Alt+F11 noma khetha kuthebhu Unjiniyela - Visual Basic (Unjiniyela — Isihleli Esiyisisekelo Esibonakalayo). Uma amathebhu Unjiniyela ayibonakali, ungayinika amandla ngokusebenzisa Ifayela – Izinketho – Ukusethwa kweRibhoni (Ifayela — Izinketho — Yenza Iribhoni Ngokwezifiso).

Kuwindi lomhleli we-Visual Basic elivulayo, khetha umyalo wemenyu Faka – Imojula futhi kumojula engenalutho evelayo, kopisha futhi unamathisele imigqa elandelayo:

   I-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 Bese Uma rng Ayilutho Bese Setha rng = Imigqa(r) Okunye Setha rng = Inyunyana(rng, Imigqa(r)) Qeda Uma Okulandelayo r Uma Kungeyona I-rng Ayilutho Bese rng.Delete 'удаляем пустые столбцы Setha rng = Nothing For r = 1 To I-ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Bese Uma rng Ingelutho Bese Setha rng = Amakholomu(r) Okunye Setha rng = Inyunyana(rng, Ikholomu( r)) Qeda Uma Okulandelayo r Uma Kungenjalo rng Akunalutho Bese rng.Susa End Sub

Vala isihleli bese ubuyela ku-Excel. 

Manje hit inhlanganisela Alt+F8 noma inkinobho amamakhro ithebhu Unjiniyela. Iwindi elivulayo lizoklelisa wonke ama-macros atholakalayo manje kuwe ukuthi uwasebenzise, ​​kufaka phakathi ama-macro osanda kuwenza. SusaOkungenalutho. Ikhethe bese uchofoza inkinobho Qalisa (gijima) - yonke imigqa namakholomu angenalutho eshidini azosuswa ngokushesha.

Indlela yesi-4: Umbuzo wamandla

Enye indlela yokuxazulula inkinga yethu kanye nesimo esivame kakhulu ukususa imigqa namakholomu angenalutho kokuthi Umbuzo Wamandla.

Okokuqala, masilayishe ithebula lethu kusihleli Sombuzo Wamandla. Ungayiguqulela “ehlakaniphile” eguquguqukayo ngesinqamuleli sekhibhodi esithi Ctrl+T noma mane ukhethe uhla lwethu lwedatha bese ulunikeza igama (ngokwesibonelo. Idatha) kubha yefomula, iguqulela kugama elithi:

Manje sisebenzisa umyalo Idatha - Thola idatha - Kusuka etafuleni / ububanzi (Idatha - Thola Idatha - Kusuka etafuleni / ububanzi) bese ulayisha yonke into kokuthi Umbuzo Wamandla:

Khona-ke konke kulula:

  1. Sisusa imigqa engenalutho ngomyalo othi Ikhaya - Nciphisa imigqa - Susa imigqa - Susa imigqa engenalutho (Ikhaya - Susa Imigqa - Susa imigqa engenalutho).
  2. Chofoza kwesokudla esihlokweni sekholomu yokuqala yeDolobha bese ukhetha umyalo othi Unpivot Amanye Amakholomu kumenyu yokuqukethwe. Ithebula lethu lizoba, njengoba libizwa kahle ngobuchwepheshe, kujwayelekile - aguqulelwe abe amakholomu amathathu: idolobha, inyanga kanye nenani elisuka ezimpambanweni zomgwaqo zedolobha nenyanga ukusuka etafuleni lokuqala. Okukhethekile kwalo msebenzi kokuthi Umbuzo Wamandla ukuthi weqa amaseli angenalutho kudatha yomthombo, okuyikhona esikudingayo:
  3. Manje senza umsebenzi wokuhlehla - sibuyisela ithebula eliwumphumela libe yi-two-dimensional elilodwa ukuze silibuyisele esimweni salo sangempela. Khetha ikholomu enezinyanga nakuthebhu Uguquko khetha iqembu Ikholomu ye-Pivot (Guqula — Ikholomu Ye-Pivot). Ewindini elivulayo, njengekholomu yamanani, khetha okokugcina (Value), futhi kukho okuthuthukisiwe - ukusebenza. Ungahlanganisi (Ungahlanganisi):
  4. Kusele ukulayisha umphumela emuva ku-Excel ngomyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala&Layisha — Vala&Layisha ku…)

  • Iyini i-macro, isebenza kanjani, kuphi ukukopisha umbhalo we-macro, indlela yokusebenzisa i-macro?
  • Ukugcwalisa wonke amaseli angenalutho ohlwini ngamavelu amaseli angumzali
  • Ikhipha wonke amaseli angenalutho ebangeni elinikeziwe
  • Ukukhipha yonke imigqa engenalutho ekhasini lokusebenza ngesengezo se-PLEX

shiya impendulo