Ukuhlunga ikholomu evundlile ku-Excel

Uma ungeyena ngempela umsebenzisi we-novice, kufanele ukuthi usubonile ukuthi u-99% wayo yonke into ku-Excel yakhelwe ukusebenza ngamatafula aqondile, lapho amapharamitha noma izimfanelo (izinkambu) zidlula amakholomu, kanye nolwazi mayelana nezinto noma imicimbi. emigqeni . Amathebula e-Pivot, amanani amancane, amafomula okukopisha ngokuchofoza kabili - yonke into yakhelwe ngokukhethekile le fomethi yedatha.

Kodwa-ke, ayikho imithetho ngaphandle kokuhlukile futhi ngemvamisa evamile ngiyabuzwa ukuthi ngenzeni uma itafula elinomumo we-semantic ovundlile, noma ithebula lapho imigqa namakholomu anesisindo esifanayo ngencazelo, lihlangana nomsebenzi:

Ukuhlunga ikholomu evundlile ku-Excel

Futhi uma i-Excel isazi ukuhlunga ngokuvundlile (ngomyalo Idatha – Hlunga – Izinketho – Hlunga amakholomu), khona-ke isimo ngokuhlunga sibi kakhulu - awekho nje amathuluzi akhelwe ngaphakathi okuhlunga amakholomu, hhayi imigqa ku-Excel. Ngakho-ke, uma ubhekene nomsebenzi onjalo, kuzodingeka uqhamuke nama-workaround anamazinga ahlukahlukene obunzima.

Indlela 1. Umsebenzi Omusha WESIHLUNGI

Uma ukunguqulo entsha ye-Excel 2021 noma ukubhalisa kwe-Excel 365, ungasebenzisa isici esisanda kwethulwa. FILTER (ISIHLUNGI), engahlunga idatha yomthombo hhayi ngemigqa kuphela, kodwa namakholomu. Ukuze usebenze, lo msebenzi udinga umsizi womugqa womugqa owodwa ovundlile, lapho inani ngalinye (TRUE noma FALSE) linquma ukuthi sibonisa noma, ngokuphambene, sifihla ikholomu elandelayo kuthebula.

Ake sengeze umugqa olandelayo ngenhla kwetafula lethu futhi sibhale isimo sekholomu ngayinye kulo:

Ukuhlunga ikholomu evundlile ku-Excel

  • Ake sithi sihlale sifuna ukubonisa amakholomu okuqala nawokugcina (izihloko namathothali), ngakho-ke kuwo kumaseli okuqala nawokugcina ochungechunge sibeka inani = TRUE.
  • Kumakholomu asele, okuqukethwe kwamaseli ahambisanayo kuzoba ifomula ehlola isimo esisidingayo sisebenzisa imisebenzi И (FUTHI) or OR (OR). Isibonelo, lelo ngqikithi isebangeni ukusuka ku-300 kuye ku-500.

Ngemuva kwalokho, kusele kuphela ukusebenzisa umsebenzi FILTER ukuze ukhethe amakholomu ngaphezulu lapho iqembu lethu elisizayo linenani elithi TRUE:

Ukuhlunga ikholomu evundlile ku-Excel

Ngokufanayo, ungakwazi ukuhlunga amakholomu ngohlu olunikeziwe. Kulokhu, umsebenzi uzosiza COUNTIF (COUNTIF), ehlola inani lezenzeko zegama lekholomu elandelayo kunhlokweni yethebula ohlwini oluvunyelwe:

Ukuhlunga ikholomu evundlile ku-Excel

Indlela yesi-2. Ithebula le-Pivot esikhundleni selivamile

Njengamanje, i-Excel inokuhlunga okwakhelwe ngaphakathi okuvundlile ngamakholomu kuphela kumathebula e-pivot, ngakho-ke uma sikwazi ukuguqula ithebula lethu langempela libe ithebula le-pivot, singasebenzisa lokhu kusebenza okwakhelwe ngaphakathi. Ukwenza lokhu, ithebula lethu lomthombo kufanele lanelise imibandela elandelayo:

  • ube nomugqa wesihloko womugqa owodwa “olungile” ngaphandle kwamaseli angenalutho nahlanganisiwe - ngaphandle kwalokho ngeke kusebenze ukwakha ithebula le-pivot;
  • zingaqukathi izimpinda kumalebula emigqa namakholomu - "zizogoqa" esifinyezweni zibe uhlu lwamanani ahlukile kuphela;
  • iqukethe izinombolo kuphela kububanzi bamanani (empambanweni yemigqa namakholomu), ngoba ithebula le-pivot nakanjani lizosebenzisa uhlobo oluthile lomsebenzi wokuhlanganisa kuwo (isamba, isilinganiso, njll.) futhi lokhu ngeke kusebenze nombhalo.

Uma zonke lezi zimo zihlangatshezwana nazo, ngakho-ke ukuze kwakhiwe ithebula le-pivot elifana nethebula lethu langempela, lona (eloqobo) lizodinga ukunwetshwa lisuka ku-crosstab liye endaweni eyisicaba (elijwayelekile). Futhi indlela elula yokwenza lokhu ngesengezo Sombuzo Wamandla, ithuluzi elinamandla lokuguqula idatha elakhelwe ku-Excel kusukela ngo-2016. 

Lezi izi:

  1. Ake siguqule ithebula libe umyalo “ohlakaniphile” oguquguqukayo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula).
  2. Ilayisha Embuzweni Wamandla ngomyalo Idatha - Isuka Kuthebula / Ububanzi (Idatha - Isuka Kuthebula / Ububanzi).
  3. Sihlunga umugqa ngamathothali (isifinyezo sizoba namathothali aso).
  4. Chofoza kwesokudla esihlokweni sekholomu yokuqala bese ukhetha Goqa amanye amakholomu (Yehlisa iphivothi Amanye Amakholomu). Wonke amakholomu angakhethiwe aguqulwa abe amabili - igama lesisebenzi kanye nenani lenkomba yakhe.
  5. Ukuhlunga ikholomu ngamathothali angenile kukholamu izimfanelo.
  6. Sakha ithebula le-pivot ngokusho kwethebula eliyisicaba (elijwayelekile) ngomyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala & Layisha — Vala & Layisha ku…).

Manje ungasebenzisa ikhono lokuhlunga amakholomu atholakala kumathebula e-pivot - omaka abavamile phambi kwamagama nezinto Izihlungi Zesiginesha (Izihlungi zelebula) or Izihlungi ngevelu (Izihlungi Zenani):

Ukuhlunga ikholomu evundlile ku-Excel

Futhi kunjalo, lapho ushintsha idatha, uzodinga ukubuyekeza umbuzo wethu nesifinyezo ngesinqamuleli sekhibhodi. Ctrl+Alt+F5 noma iqembu Idatha - Vuselela Konke (Idatha — Vuselela Konke).

Indlela 3. Macro in VBA

Zonke izindlela zangaphambilini, njengoba ungabona kalula, azihlungi ncamashi - asiwafihli amakholomu ohlwini lwangempela, kodwa sakha itafula elisha elineqoqo elinikeziwe lamakholomu asuka kweyokuqala. Uma kudingeka ukuhlunga (ukufihla) amakholomu kudatha yomthombo, khona-ke indlela ehluke kakhulu iyadingeka, okungukuthi, i-macro.

Ake sithi sifuna ukuhlunga amakholomu empukaneni lapho igama lomphathi kunhlokweni yetafula lenelisa imaski eshiwo kuseli eliphuzi A4, ngokwesibonelo, liqala ngohlamvu “A” (okungukuthi, thola u-“Anna” kanye “no-Arthur. " ngenxa yalokho). 

Njengasendleleni yokuqala, siqale sisebenzise umugqa osizayo wobubanzi, lapho kuseli ngalinye umbandela wethu uzobhekwa ngefomula futhi amanani anengqondo ethi TRUE noma FALSE azoboniswa kumakholomu abonakalayo nafihliwe, ngokulandelana:

Ukuhlunga ikholomu evundlile ku-Excel

Bese sengeza i-macro elula. Chofoza kwesokudla kuthebhu yeshidi bese ukhetha umyalo Umthombo (Ikhodi yomthombo). Kopisha bese unamathisela ikhodi ye-VBA elandelayo efasiteleni elivulekayo:

I-Private Sub Worksheet_Change(ByVal Target Njengobubanzi) Uma I-Target.Address = "$A$4" Bese Kuseseli Ngalinye Ku-Range("D2:O2") Uma iseli = Iqiniso Bese kuthi cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Isiphetho Seqiniso Uma Iseli Elilandelayo Liphela Uma End I-Sub

I-logic yayo imi kanje:

  • Ngokuvamile, lesi yisiphathi somcimbi Ukushintsha_Ishidi Lokusebenza, okungukuthi le macro izosebenza ngokuzenzakalelayo kunoma yiluphi ushintsho kunoma iyiphi iseli eshidini lamanje.
  • Ireferensi yeseli eshintshiwe izohlala ikunguquko Target.
  • Okokuqala, sibheka ukuthi umsebenzisi ulishintshe ncamashi iseli ngombandela (A4) - lokhu kwenziwa ngu-opharetha. if.
  • Bese uqala umjikelezo Kokunye… ukuphindaphinda amaseli ampunga (D2:O2) ngamavelu esikhombi esithi TRUE / FALSE kukholomu ngayinye.
  • Uma inani leseli elimpunga elilandelayo lithi TRUE (iqiniso), khona-ke ikholomu ayifihlwa, ngaphandle kwalokho siyayifihla (indawo Afihliwe).

  •  Imisebenzi yamalungu afanayo anamandla evela ku-Office 365: FILTER, SORT, kanye ne-UNIC
  • Ithebula le-Pivot elinenhlokweni yemigqa eminingi kusetshenziswa Umbuzo Wamandla
  • Ayini ama-macros, angawadala kanjani futhi uwasebenzise

 

shiya impendulo