Kab rov tav kab lim hauv Excel

Yog tias koj tsis yog ib tus neeg siv tshiab, ces koj yuav tsum tau pom tias 99% ntawm txhua yam hauv Excel yog tsim los ua haujlwm nrog cov lus ntsug, qhov twg tsis muaj lossis cov cwj pwm (teb) mus rau txhua kab, thiab cov ntaub ntawv hais txog cov khoom lossis cov xwm txheej nyob. hauv kab. Pivot ntxhuav, subtotals, luam cov qauv nrog ob npaug nias - txhua yam yog tsim tshwj xeeb rau cov ntaub ntawv no.

Txawm li cas los xij, tsis muaj kev cai tsis muaj kev zam thiab nrog ntau zaus kuv raug nug tias yuav ua li cas yog lub rooj nrog kab rov tav semantic orientation, lossis ib lub rooj uas kab thiab kab muaj qhov hnyav tib yam hauv lub ntsiab lus, tuaj hla hauv kev ua haujlwm:

Thiab yog tias Excel tseem paub yuav ua li cas txheeb kab rov tav (nrog cov lus txib Cov ntaub ntawv – Sort – Options – Sort kem), tom qab ntawd qhov xwm txheej nrog kev lim dej yog qhov phem dua - tsuas yog tsis muaj cov cuab yeej built-in rau lim kab, tsis yog kab hauv Excel. Yog li, yog tias koj tau ntsib nrog cov haujlwm zoo li no, koj yuav tsum tuaj nrog kev ua haujlwm ntawm ntau qib ntawm kev nyuaj.

Txoj Kev 1. Tshiab FILTER muaj nuj nqi

Yog tias koj nyob ntawm qhov tshiab ntawm Excel 2021 lossis Excel 365 subscription, koj tuaj yeem ua kom zoo dua ntawm qhov kev qhia tshiab LIM (FILTER), uas tuaj yeem lim cov ntaub ntawv tsis yog los ntawm kab, tab sis kuj los ntawm kab. Txhawm rau ua haujlwm, qhov haujlwm no yuav tsum muaj kev pabcuam kab rov tav ib-dimensional array-kab, qhov twg txhua tus nqi (TRUE lossis FALSE) txiav txim siab seb peb qhia lossis, hloov pauv, nkaum kab tom ntej hauv lub rooj.

Cia peb ntxiv cov kab hauv qab no saum toj peb lub rooj thiab sau cov xwm txheej ntawm txhua kab hauv nws:

• Cia peb hais tias peb ib txwm xav tso saib thawj thiab kawg kab (headers thiab tag nrho), yog li rau lawv nyob rau hauv thawj thiab zaum kawg hlwb ntawm array peb teem tus nqi = TRUE.
• Rau cov seem seem, cov ntsiab lus ntawm cov hlwb sib raug zoo yuav yog cov qauv uas kuaj xyuas cov xwm txheej uas peb xav tau siv cov haujlwm. И (AND) or OR (LOSSIS). Piv txwv li, tias tag nrho yog nyob rau hauv thaj tsam ntawm 300 mus rau 500.

Tom qab ntawd, nws tsuas yog siv lub luag haujlwm xwb LIM xaiv cov kab saum toj no uas peb pab pawg array muaj tus nqi TRUE:

Ib yam li ntawd, koj tuaj yeem lim cov kab los ntawm ib daim ntawv teev npe. Hauv qhov no, kev ua haujlwm yuav pab tau COUNTIF (COUNTIF), uas txheeb xyuas cov naj npawb ntawm qhov tshwm sim ntawm lub npe kab tom ntej los ntawm lub rooj header hauv daim ntawv tso cai:

Txoj kev 2. Pivot rooj es tsis txhob ntawm ib txwm ib txwm

Tam sim no, Excel muaj built-in kab rov tav lim los ntawm txhua kab hauv cov lus pivot, yog li yog tias peb tswj kom hloov peb lub rooj qub rau hauv lub rooj pivot, peb tuaj yeem siv qhov kev ua haujlwm no built-in. Txhawm rau ua qhov no, peb cov lus qhia yuav tsum ua raws li cov xwm txheej hauv qab no:

• muaj ib tug "txhim kho" ib-kab header kab uas tsis muaj khoob thiab merged hlwb - txwv tsis pub nws yuav tsis ua hauj lwm los tsim ib tug pivot rooj;
• tsis muaj qhov sib npaug hauv cov ntawv cim ntawm kab thiab kab - lawv yuav "yob" hauv cov ntsiab lus rau hauv cov npe ntawm cov txiaj ntsig tshwj xeeb;
• tsuas muaj cov lej hauv qhov ntau ntawm qhov tseem ceeb (ntawm kev sib tshuam ntawm kab thiab kab), vim hais tias lub rooj pivot yuav siv qee yam kev sib sau ua ke rau lawv (sum, nruab nrab, thiab lwm yam) thiab qhov no yuav tsis ua haujlwm nrog cov ntawv nyeem.

Yog tias tag nrho cov xwm txheej no tau ua tiav, tom qab ntawd txhawm rau tsim lub rooj pivot uas zoo li peb lub rooj qub, nws (qhov qub) yuav tsum tau nthuav dav los ntawm crosstab rau hauv ib lub tiaj tus (normalized). Thiab qhov yooj yim tshaj plaws los ua qhov no yog nrog Power Query add-in, cov ntaub ntawv hloov pauv muaj zog ua rau hauv Excel txij li xyoo 2016. 

Cov no yog:

1. Cia peb hloov lub rooj rau hauv "smart" dynamic hais kom ua Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table).
2. Loading rau hauv Power Query nrog cov lus txib Cov ntaub ntawv - Los ntawm Rooj / Range (Cov ntaub ntawv - Los ntawm Table / Range).
3. Peb lim cov kab nrog tag nrho (cov ntsiab lus yuav muaj nws tus kheej tag nrho).
4. Right-click rau thawj kab ntawv kab lus thiab xaiv Uncollapse lwm kab (Unpivot Lwm Kab). Tag nrho cov kab uas tsis raug xaiv tau hloov mus ua ob - lub npe ntawm cov neeg ua haujlwm thiab tus nqi ntawm nws qhov taw qhia.
5. Lim lub kem nrog tag nrho cov uas tau mus rau hauv kem Tus Cwj Pwm.
6. Peb tsim ib lub rooj pivot raws li qhov tshwm sim tiaj tus (normalized) lub rooj nrog cov lus txib Tsev - Kaw thiab Load - Kaw thiab Load hauv… (Lub Tsev - Kaw & Load - Kaw & Load rau…).

Tam sim no koj tuaj yeem siv lub peev xwm los lim cov kab muaj nyob rau hauv cov lus pivot - cov ntawv txheeb xyuas ib txwm nyob rau pem hauv ntej ntawm cov npe thiab cov khoom Kos npe lim (Label Filters) or Lim los ntawm tus nqi (Tus nqi lim):

Thiab tau kawg, thaum hloov cov ntaub ntawv, koj yuav tsum hloov kho peb cov lus nug thiab cov ntsiab lus nrog cov keyboard shortcut Ctrl+Alt+F5 los yog pab neeg Cov ntaub ntawv - Refresh Txhua (Cov ntaub ntawv - Refresh All).

Txoj Kev 3. Macro hauv VBA

Tag nrho cov txheej txheem dhau los, raws li koj tuaj yeem pom tau yooj yim, tsis yog kev lim dej raws nraim - peb tsis zais cov kab hauv thawj daim ntawv teev npe, tab sis tsim ib lub rooj tshiab nrog cov kab uas tau muab los ntawm tus thawj. Yog tias nws yuav tsum tau lim (tshem) cov kab hauv cov ntaub ntawv, tom qab ntawd xav tau ib qho kev sib txawv ntawm qhov tseem ceeb, xws li, macro.

Piv txwv li peb xav lim txhua kab ntawm ya uas lub npe ntawm tus thawj tswj hwm hauv lub rooj header txaus siab rau daim npog ntsej muag uas tau teev tseg hauv lub xov tooj daj A4, piv txwv li, pib nrog tsab ntawv "A" (uas yog, tau txais "Anna" thiab "Arthur. "raws li qhov tshwm sim). 

Raws li nyob rau hauv thawj txoj kev, peb ua ntej siv ib tug pab pawg-kab, qhov twg nyob rau hauv txhua lub xovtooj ntawm tes peb cov txheej txheem yuav raug soj ntsuam los ntawm ib tug formula thiab cov logic muaj nuj nqis TRUE los yog FALSE yuav muab tso tawm kom pom thiab zais txhua kab, raws li nram no:

Tom qab ntawd cia peb ntxiv ib qho yooj yim macro. Right-click rau ntawm nplooj ntawv tab thiab xaiv cov lus txib Tau qhov twg los (Qhov chaw code). Luam thiab paste cov VBA code hauv qab no rau hauv lub qhov rai uas qhib:

Private Sub Worksheet_Change(ByVal Target As Range) Yog Target.Address = "$A$4" Ces Rau Txhua cell In Range("D2:O2") Yog cell = True Ces cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End Yog Next cell End Yog End Sub  

Nws logic yog raws li nram no:

• Feem ntau, qhov no yog tus tuav cov xwm txheej Worksheet_Hloov, piv txwv li no macro yuav cia li khiav ntawm ib qho kev hloov pauv rau ib lub xov tooj ntawm daim ntawv tam sim no.
• Qhov kev siv rau qhov hloov pauv ntawm tes yuav ib txwm nyob hauv qhov sib txawv Target.
• Ua ntej, peb xyuas tias tus neeg siv tau hloov raws nraim ntawm lub xov tooj ntawm tes nrog cov qauv (A4) - qhov no yog ua los ntawm tus neeg teb xov tooj if.
• Ces lub voj voog pib Rau Txhua… mus iterate dua grey hlwb (D2:O2) nrog TRUE / FALSE qhov taw qhia qhov tseem ceeb rau txhua kab.
• Yog hais tias tus nqi ntawm lub cell grey tom ntej no yog TRUE (tseeb), ces kab ntawv tsis zais, txwv tsis pub peb zais nws (cov khoom ntiag tug muab zais).

•  Dynamic array ua haujlwm los ntawm Office 365: FILTER, SORT, thiab UNIC
• Pivot rooj nrog multiline header siv Power Query
• Dab tsi yog macro, tsim thiab siv lawv li cas