Tshem tawm cov kab khoob thiab kab hauv cov ntaub ntawv

Kab laug sab thiab kab tuaj yeem ua rau mob hauv cov ntxhuav hauv ntau zaus. Txuj kev ua haujlwm rau kev txheeb xyuas, lim dej, sau cov ntsiab lus, tsim cov ntxhuav pivot, thiab lwm yam pom cov kab khoob thiab txhua kab raws li lub rooj so, tsis tas khaws cov ntaub ntawv nyob tom qab lawv. Yog tias muaj ntau qhov sib txawv, ces kev tshem tawm manually tuaj yeem raug nqi kim heev, thiab nws yuav tsis ua haujlwm kom tshem tawm tag nrho ib zaug "hauv ntau" siv cov lim dej, vim tias cov lim yuav "tawm" thaum so.

Cia peb saib ob peb txoj hauv kev los daws qhov teeb meem no.

Txoj Kev 1. Tshawb nrhiav cov hlwb khoob

Qhov no tej zaum yuav tsis yog qhov yooj yim tshaj plaws, tab sis twv yuav raug hu txoj kev yooj yim tshaj yog tsim nyog hais.

Piv txwv tias peb tab tom cuam tshuam nrog cov lus no uas muaj ntau cov kab khoob thiab kab sab hauv (highlighted for clarity):

Piv txwv tias peb paub tseeb tias thawj kab ntawm peb lub rooj (kem B) ib txwm muaj lub npe ntawm lub nroog. Tom qab ntawd cov hlwb khoob hauv kab no yuav yog ib qho cim ntawm cov kab tsis tsim nyog. Txhawm rau tshem tawm sai sai, ua cov hauv qab no:

  1. Xaiv ntau yam nrog lub nroog (B2:B26)
  2. Nias tus yuam sij F5 thiab tom qab ntawd nias highlight (Mus rau Tshwj Xeeb) los yog xaiv ntawm lub tab Tsev — Nrhiav thiab Xaiv — Xaiv ib pawg ntawm cov hlwb (Lub Tsev - Nrhiav & Xaiv - Mus rau qhov tshwj xeeb).
  3. Hauv qhov rai uas qhib, xaiv qhov kev xaiv Cov hlwb khoob (Vim li cas) thiab nias OK - Txhua lub hlwb khoob hauv thawj kab ntawm peb lub rooj yuav tsum raug xaiv.
  4. Tam sim no xaiv ntawm lub tab Tsev Hais kom Rho tawm - Rho tawm kab los ntawm daim ntawv (Delete - Rho tawm kab) los yog nias lub keyboard shortcut Ctrl+rho tawm - thiab peb txoj haujlwm tau daws.

Tau kawg, koj tuaj yeem tshem tawm cov kab khoob hauv tib txoj kev, siv lub rooj header ua lub hauv paus.

Txoj Kev 2: Tshawb nrhiav cov kab khoob

Raws li koj tau pom lawm, txoj kev dhau los tsuas yog ua haujlwm yog tias peb cov ntaub ntawv yuav tsum muaj cov kab puv puv thiab kab, uas tuaj yeem txuas rau thaum tshawb nrhiav cov hlwb khoob. Tab sis yuav ua li cas yog tias tsis muaj kev ntseeg siab li ntawd, thiab cov ntaub ntawv yuav muaj cov hlwb khoob thiab?

Ua tib zoo saib cov lus hauv qab no, piv txwv li, rau qhov xwm txheej zoo li no:

Ntawm no txoj hauv kev yuav yog me ntsis trickier:

  1. Nkag mus rau hauv cell A2 lub luag haujlwm COUNT (COUNTA), uas yuav suav cov naj npawb ntawm cov kab mob hauv kab mus rau sab xis thiab luam cov qauv no mus rau tag nrho cov lus:
  2. Xaiv cell A2 thiab qhib lub lim nrog cov lus txib Cov ntaub ntawv - Lim (Cov ntaub ntawv - Lim) los yog keyboard shortcut Ctrl+ua haujlwm+L.
  3. Cia peb lim tawm xoom los ntawm kab zauv, piv txwv li txhua kab uas tsis muaj cov ntaub ntawv.
  4. Nws tseem yog xaiv cov kab lim thiab tshem tawm nrog cov lus txib Tsev — Rho tawm -' Rho tawm kab ntawm daim ntawv (Tsev — Rho tawm — Rho tawm kab) los yog keyboard shortcut Ctrl+rho tawm.
  5. Peb tua lub lim thiab tau txais peb cov ntaub ntawv tsis muaj kab.

Hmoov tsis zoo, qhov kev ua kom yuam kev no tsis tuaj yeem ua tiav nrog txhua kab - Excel tseem tsis tau kawm paub lim los ntawm txhua kab.

Txoj kev 3. Macro tshem tawm tag nrho cov kab khoob thiab txhua kab ntawm ib daim ntawv

Koj tseem tuaj yeem siv macro yooj yim los ua haujlwm rau txoj haujlwm no. Nias lub keyboard shortcut Alt+F11 los yog xaiv los ntawm lub tab tsim tawm - Visual Basic (Tus tsim tawm - Visual Basic Editor). Yog tabs tsim tawm tsis pom, koj tuaj yeem pab nws dhau Cov ntaub ntawv – Kev xaiv – Ribbon teeb (Cov ntaub ntawv - Kev xaiv - Customize Ribbon).

Hauv Visual Basic editor qhov rais uas qhib, xaiv cov ntawv qhia zaub mov Ntxig - Module thiab hauv qhov khoob module uas tshwm sim, luam tawm thiab muab cov kab hauv qab no:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки Rau r = 1 Rau ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Yog Application.CountA(Raws (Raws) = 0) rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng is Noth Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Yog Application.CountA(Columns(r)) = 0 Ces Yog rng Yog Tsis Muaj Ces Teem rng = Columns(r) Lwm Teem rng = Union(rng, Columns( r)) Xaus Yog Tom ntej r Yog Tsis rng Tsis Muaj Dab Tsi Ces rng.Delete End Sub

Kaw tus editor thiab rov qab mus rau Excel. 

Tam sim no ntaus ua ke Alt+F8 los yog khawm Ntsig tab tsim tawm. Lub qhov rais uas qhib yuav sau tag nrho cov macro uas tam sim no muaj rau koj khiav, suav nrog cov macro koj nyuam qhuav tsim. DeleteEmpty. Xaiv nws thiab nyem lub pob khiav (khiav) - tag nrho cov kab khoob thiab kab hauv daim ntawv yuav raug tshem tawm tam sim.

Txoj Kev 4: Lub Hwj Chim Query

Lwm txoj hauv kev los daws peb cov teeb meem thiab qhov xwm txheej tshwm sim heev yog tshem tawm cov kab khoob thiab kab hauv Power Query.

Ua ntej, cia peb thauj peb lub rooj rau hauv Power Query Query Editor. Koj tuaj yeem hloov nws mus rau lub zog "ntse" ib qho nrog cov keyboard shortcut Ctrl + T lossis tsuas yog xaiv peb cov ntaub ntawv ntau thiab muab nws lub npe (piv txwv li. Cov ntaub ntawv) nyob rau hauv lub formula bar, converting rau npe:

Tam sim no peb siv cov lus txib Cov Ntaub Ntawv - Tau txais cov ntaub ntawv - Los ntawm lub rooj / ntau yam (Cov ntaub ntawv - Tau txais cov ntaub ntawv - Los ntawm cov lus / ntau) thiab thauj txhua yam rau hauv Cov Lus Nug:

Tom qab ntawd txhua yam yog yooj yim:

  1. Peb rho tawm cov kab khoob nrog cov lus txib Tsev - Txo cov kab - Tshem cov kab - Tshem cov kab khoob (Tsev - Tshem Kab - Tshem cov kab khoob).
  2. Txoj cai-nias ntawm lub npe ntawm thawj lub nroog kem thiab xaiv Unpivot Lwm Kab hais kom ua los ntawm cov ntawv qhia zaub mov. Peb lub rooj yuav yog, raws li nws yog technically raug hu ua, ib txwm muaj - hloov mus rau hauv peb kab: lub nroog, lub hli thiab tus nqi los ntawm kev sib tshuam ntawm lub nroog thiab lub hli los ntawm lub rooj qub. Qhov peculiarity ntawm qhov kev ua haujlwm hauv Power Query yog tias nws hla cov hlwb khoob hauv cov ntaub ntawv, uas yog qhov peb xav tau:
  3. Tam sim no peb ua qhov rov qab ua haujlwm - peb tig lub rooj ua haujlwm rov qab mus rau hauv ob sab hauv thiaj li yuav rov qab mus rau nws daim ntawv qub. Xaiv cov kab nrog lub hli thiab ntawm lub tab transformation xaiv ib pab neeg Pivot kab (Transform — Pivot Kem). Nyob rau hauv lub qhov rais uas qhib, raws li ib tug kem ntawm qhov tseem ceeb, xaiv lub xeem (Value), thiab nyob rau hauv cov kev xaiv siab heev - lub lag luam Tsis txhob sib sau ua ke (Tsis txhob sib sau ua ke):
  4. Nws tseem yuav xa cov txiaj ntsig rov qab rau Excel nrog cov lus txib Tsev - Kaw thiab Load - Kaw thiab Load hauv… (Lub Tsev - Kaw & Load - Kaw & Load rau…)

  • Dab tsi yog macro, nws ua haujlwm li cas, qhov twg luam cov ntawv ntawm macro, yuav ua li cas khiav macro?
  • Sau tag nrho cov hlwb khoob hauv cov npe nrog cov txiaj ntsig ntawm niam txiv lub hlwb
  • Tshem tawm tag nrho cov hlwb khoob los ntawm ib qho chaw muab
  • Tshem tawm tag nrho cov kab khoob hauv daim ntawv ua haujlwm nrog PLEX add-on

Sau ntawv cia Ncua