txheem
Formulation ntawm qhov teeb meem
Piv txwv tias peb muaj lub rooj zoo li no uas peb yuav tsum "dab" nrog txhua hnub:
Rau leej twg lub rooj zoo li me me - lub hlwb muab nws nees nkaum npaug ntawm cheeb tsam, ntxiv ob peb blocks ntxiv thiab ob lub nroog loj.
Lub luag haujlwm yog kom tshem tawm ib ntus ntawm cov kab screen thiab kab uas tam sim no tsis tsim nyog rau kev ua haujlwm, piv txwv li,
- zais cov ntsiab lus los ntawm lub hli, tawm hauv lub quarter xwb
- zais tag nrho los ntawm lub hlis thiab peb lub hlis twg, tawm tsuas yog tag nrho rau ib nrab xyoo
- zais cov nroog uas tsis tsim nyog tam sim no (Kuv ua haujlwm hauv Moscow - vim li cas kuv thiaj yuav pom St. Petersburg?), thiab lwm yam.
Nyob rau hauv lub neej tiag tiag, muaj ib lub hiav txwv ntawm cov piv txwv ntawm tej rooj.
Txoj Kev 1: nkaum kab thiab kab
Txoj kev, hais ncaj ncees, yog thawj zaug thiab tsis yooj yim heev, tab sis ob lo lus tuaj yeem hais txog nws. Ib qho yav dhau los xaiv kab lossis kab ntawm ib daim ntawv tuaj yeem muab zais los ntawm txoj cai-nias rau kab lossis kab header thiab xaiv cov lus txib los ntawm cov ntawv qhia zaub mov. Hide (Hide):
Rau cov duab rov qab, xaiv kab / kab uas nyob ib sab thiab, los ntawm txoj cai-nias, xaiv los ntawm cov ntawv qhia zaub mov, ntsig txog, zaub (Unhide).
Qhov teeb meem yog tias koj yuav tsum tau nrog txhua kab thiab kab ib tus zuj zus, uas yog qhov tsis yooj yim.
Txoj kev 2. Pab pawg
Yog tias koj xaiv ntau kab lossis kab thiab ces xaiv los ntawm cov ntawv qhia zaub mov Cov ntaub ntawv – pab pawg thiab cov qauv – pab pawg (Cov ntaub ntawv - Pab Pawg thiab Cov Txheej Txheem - Pab Pawg), ces lawv yuav muab kaw rau hauv ib lub voj voog square (pab pawg). Ntxiv mus, pab pawg tuaj yeem ua zes ib leeg rau lwm tus (txog 8 qib kev ua zes raug tso cai):
Ib txoj hauv kev yooj yim dua thiab sai dua yog siv cov keyboard shortcut rau pab pawg ua ntej xaiv kab lossis kab. Alt+Shift+Right Arrow, thiab rau ungrouping Alt+Shift+Sab laug Xub, feem.
Txoj kev zais cov ntaub ntawv tsis tsim nyog no yooj yim dua - koj tuaj yeem nyem rau ntawm lub pob nrog "+"Lossis"-", los yog ntawm cov nyees khawm nrog cov lej pab pawg nyob rau sab sauv sab laug ntawm daim ntawv - tom qab ntawd txhua pab pawg ntawm qib xav tau yuav raug kaw lossis nthuav tawm ib zaug.
Tsis tas li ntawd, yog tias koj lub rooj muaj cov kab lus sau los yog kab nrog cov haujlwm ntawm kev sib sau cov hlwb nyob sib ze, uas yog, lub caij nyoog (tsis yog 100% tseeb) uas Excel nws yuav tsim txhua pab pawg tsim nyog nyob rau hauv lub rooj nrog ib tug txav - los ntawm cov ntawv qhia zaub mov Cov ntaub ntawv – pab pawg thiab cov qauv – Tsim qauv (Cov ntaub ntawv - Pab Pawg thiab Cov Txheej Txheem - Tsim Cov Qauv). Hmoov tsis zoo, qhov kev ua haujlwm zoo li no ua haujlwm tsis zoo thiab qee zaum ua tiav qhov tsis muaj tseeb ntawm cov ntxhuav nyuaj. Tab sis koj tuaj yeem sim.
Hauv Excel 2007 thiab tshiab dua, tag nrho cov kev xyiv fab no nyob ntawm tab Cov ntaub ntawv (Hnub) hauv pab pawg qauv (Cov ntsiab lus):
Txoj Kev 3. Nkaum cov kab / kab uas cim nrog cov macro
Txoj kev no yog kab tias muaj ntau yam. Cia peb ntxiv ib kab khoob thiab ib kab khoob rau qhov pib ntawm peb daim ntawv thiab kos nrog txhua lub cim cov kab thiab kab uas peb xav nkaum:
Tam sim no cia peb qhib Visual Basic Editor (ALT + F11), ntxig ib qho tshiab khoob rau hauv peb phau ntawv (menu Ntxig - Module) thiab luam cov ntawv ntawm ob qho yooj yim macros muaj:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False ' Disable screen update to speed up For each cell In ActiveSheet.UsedRange.Rows(1).Cells 'Iterate over all cells in the first row If cell.Value = "x " Ces cell .EntireColumn.Hidden = True 'yog tias nyob rau hauv cell x - nkaum kab Tom ntej Rau Txhua lub xov tooj Hauv ActiveSheet.UsedRange.Columns(1).Cells 'mus dhau tag nrho cov cell ntawm thawj kab Yog cell.Value = "x" Ces cell.EntireRow.Hidden = Tseeb 'yog nyob rau hauv cell x - nkaum kab Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'tso tseg tag nrho cov hiding kab thiab kab Rows.Hidden = False End Sub
Raws li koj tuaj yeem twv, macro Hide zais thiab macro Qhia - Qhia rov qab sau cov kab thiab kab. Yog tias xav tau, macros tuaj yeem raug muab rau hotkeys (Alt + F8 thiab khawm tsis), los yog tsim cov nyees khawm ncaj qha rau ntawm daim ntawv kom tso tawm ntawm lub tab Developer – Ntxig – Khawm (Tus tsim tawm - Ntxig - Khawm).
Txoj Kev 4. nkaum kab/kem uas muaj xim
Cia peb hais tias nyob rau hauv qhov piv txwv saum toj no, peb, ntawm qhov tsis sib xws, xav nkaum tag nrho, piv txwv li ntshav thiab dub kab thiab kab daj thiab ntsuab. Tom qab ntawd peb cov macro yav dhau los yuav tsum tau hloov kho me ntsis los ntawm kev ntxiv, es tsis txhob kuaj xyuas qhov muaj "x", ib daim tshev rau kev sib piv cov xim sau nrog randomly xaiv cov qauv cell:
Sub HideByColor() Dim cell As Range Application.ScreenUpdating = False For each cell In ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color Ces cell.EntireColumn.Hidden = Muaj tseeb yog tias cell.Interior.Color = Range("K2").Interior.Color Ces cell.EntireColumn.Hidden = Tseeb Tom ntej rau Txhua lub cell hauv ActiveSheet.UsedRange.Columns(2).Cells Yog cell.Interior.Color = Range ("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Ces cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Txawm li cas los xij, peb yuav tsum tsis txhob hnov qab txog ib qho lus ceeb toom: qhov macro no tsuas yog ua haujlwm yog tias lub hlwb ntawm lub rooj tau sau nrog cov xim manually, thiab tsis siv cov kev cai formatting (qhov no yog qhov txwv ntawm Sab hauv.Color khoom). Yog li, piv txwv li, yog tias koj txiav txim siab tag nrho cov kev sib cog lus hauv koj lub rooj uas tus lej tsawg dua 10 siv cov kev cai formatting:
... thiab koj xav zais lawv hauv ib qho lus tsa suab, tom qab ntawd cov macro yav dhau los yuav tsum tau "ua tiav". Yog tias koj muaj Excel 2010-2013, ces koj tuaj yeem tawm mus siv tsis siv cov cuab yeej Sab hauv vaj tse DisplayFormat.Interior, uas outputs xim ntawm tes, tsis hais seb nws tau teem li cas. Lub macro los nkaum cov kab xiav yuav zoo li no:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = False For each cell In ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Ces cell .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Cell G2 raug coj los ua piv txwv rau kev sib piv xim. Hmoov tsis txog cov cuab yeej DisplayFormat tshwm sim hauv Excel tsuas yog pib los ntawm 2010 version, yog li yog tias koj muaj Excel 2007 lossis laus dua, koj yuav tau los nrog lwm txoj hauv kev.
- Dab tsi yog macro, qhov twg los ntxig macro code, siv lawv li cas
- Tsis siv neeg pab pawg hauv ntau cov npe