Lárétt dálksíun í Excel

Ef þú ert ekki alveg nýliði, þá hlýtur þú að hafa tekið eftir því að 99% af öllu í Excel er hannað til að vinna með lóðréttum töflum, þar sem færibreytur eða eiginleikar (reitir) fara í gegnum dálkana og upplýsingar um hluti eða atburði eru staðsettar. í línunum. Snúningstöflur, undirsamtölur, afrita formúlur með tvísmelli – allt er sérsniðið sérstaklega fyrir þetta gagnasnið.

Hins vegar eru engar reglur án undantekninga og með nokkuð reglulegri tíðni er ég spurður hvað eigi að gera ef tafla með lárétta merkingarstefnu, eða tafla þar sem raðir og dálkar hafa sama vægi í merkingu, kæmi fyrir í verkinu:

Og ef Excel veit enn hvernig á að flokka lárétt (með skipuninni Gögn – Raða – Valkostir – Raða dálkum), þá er ástandið með síun verra – það eru einfaldlega engin innbyggð verkfæri til að sía dálka, ekki raðir í Excel. Svo, ef þú stendur frammi fyrir slíku verkefni, verður þú að finna lausnir sem eru misflækjur.

Aðferð 1. Ný FILTER aðgerð

Ef þú ert á nýju útgáfunni af Excel 2021 eða Excel 365 áskrift geturðu nýtt þér nýlega kynntan eiginleika FILTER (SÍA), sem getur síað upprunagögnin ekki aðeins eftir línum, heldur einnig eftir dálkum. Til að virka þarf þessi aðgerð auka lárétta einvíddar fylkislínu, þar sem hvert gildi (TRUE eða FALSE) ákvarðar hvort við sýnum eða öfugt, felum næsta dálk í töflunni.

Við skulum bæta eftirfarandi línu fyrir ofan töfluna okkar og skrifa stöðu hvers dálks í hana:

• Segjum að við viljum alltaf sýna fyrsta og síðasta dálkinn (hausa og heildartölur), þannig að fyrir þá í fyrstu og síðustu hólfinu í fylkinu setjum við gildið = TRUE.
• Fyrir þá dálka sem eftir eru mun innihald samsvarandi frumna vera formúla sem athugar ástandið sem við þurfum með aðgerðum И (OG) or OR (OR). Til dæmis að heildarfjöldinn sé á bilinu 300 til 500.

Eftir það er aðeins eftir að nota aðgerðina FILTER til að velja dálka fyrir ofan sem hjálparfylki okkar hefur TRUE gildi:

Á sama hátt geturðu síað dálka eftir tilteknum lista. Í þessu tilviki mun aðgerðin hjálpa COUNTIF (COUNTIF), sem athugar fjölda tilvika fyrir næsta dálknafn úr töfluhausnum á leyfilegum lista:

Aðferð 2. Snúningstafla í stað hinnar venjulegu

Eins og er hefur Excel innbyggða lárétta síun eftir dálkum eingöngu í pivot töflum, þannig að ef okkur tekst að breyta upprunalegu töflunni okkar í pivot töflu getum við notað þessa innbyggðu virkni. Til að gera þetta verður upprunataflan okkar að uppfylla eftirfarandi skilyrði:

• hafa „rétta“ einnarlínu hauslínu án tómra og sameinaðra hólfa – annars mun það ekki virka að búa til snúningstöflu;
• innihalda ekki afrit í merkingum raða og dálka – þær munu „hrynja“ saman í samantektinni í lista yfir einstök gildi;
• innihalda aðeins tölur á bilinu gilda (á mótum lína og dálka), vegna þess að snúningstaflan mun örugglega nota einhvers konar samansafnunaraðgerð á þær (summa, meðaltal osfrv.) og þetta mun ekki virka með textanum

Ef öll þessi skilyrði eru uppfyllt, þá þarf að stækka hana (upprunalega) frá krosstöflunni í flata (normalisera) til þess að búa til snúningstöflu sem lítur út eins og upprunalega töfluna okkar. Og auðveldasta leiðin til að gera þetta er með Power Query viðbótinni, öflugu gagnaumbreytingarverkfæri innbyggt í Excel síðan 2016. 

Þetta eru:

1. Við skulum breyta töflunni í „snjalla“ kraftmikla skipun Heim - Snið sem töflu (Heima - Snið sem töflu).
2. Hleðst inn í Power Query með skipuninni Gögn – úr töflu / svið (Gögn – úr töflu / svið).
3. Við síum línuna með heildartölunum (yfirlitið mun hafa sínar eigin samtölur).
4. Hægrismelltu á fyrstu dálkfyrirsögnina og veldu Taktu saman aðra dálka (Aftengja aðra dálka). Öllum óvöldum dálkum er breytt í tvo - nafn starfsmanns og gildi vísis hans.
5. Sía dálkinn með heildartölunum sem fóru inn í dálkinn Eigindi.
6. Við smíðum snúningstöflu í samræmi við flata (venjulega) töfluna sem myndast með skipuninni Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka og hlaða — Loka og hlaða til...).

Nú geturðu notað hæfileikann til að sía dálka sem eru tiltækir í snúningstöflum - venjulegu hakið fyrir framan nöfnin og hlutina Undirskriftarsíur (Ferðasíur) or Síur eftir gildi (Gildisíur):

Og auðvitað, þegar þú breytir gögnunum, þarftu að uppfæra fyrirspurn okkar og samantektina með flýtilykla Ctrl+Alt+F5 eða lið Gögn – Uppfæra allt (Gögn — endurnýja allt).

Aðferð 3. Fjölvi í VBA

Allar fyrri aðferðir, eins og þú getur auðveldlega séð, eru ekki nákvæmlega síun - við felum ekki dálkana á upprunalega listanum, heldur myndum nýja töflu með tilteknu setti dálka frá þeim upprunalega. Ef nauðsynlegt er að sía (fela) dálkana í upprunagögnunum, þá er grundvallaratriðið öðruvísi nálgun, þ.e. fjölvi.

Segjum sem svo að við viljum sía dálka á flugi þar sem nafn stjórnandans í töfluhausnum uppfyllir grímuna sem tilgreind er í gula reitnum A4, til dæmis byrjar á bókstafnum „A“ (þ.e. fáðu „Anna“ og „Arthur“ “ í kjölfarið). 

Eins og í fyrstu aðferðinni, innleiðum við fyrst aukasviðslínu, þar sem viðmiðun okkar í hverri reit verður athugað með formúlu og rökrétt gildi TRUE eða FALSE munu birtast fyrir sýnilega og falda dálka, í sömu röð:

Síðan skulum við bæta við einföldum fjölvi. Hægrismelltu á blaðflipann og veldu skipun Heimild (Frumkóði). Afritaðu og límdu eftirfarandi VBA kóða inn í gluggann sem opnast:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Every Cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

Rökfræði þess er sem hér segir:

• Almennt séð er þetta atburðastjórnun Vinnublað_Breyting, þ.e. þetta fjölvi mun sjálfkrafa keyra við allar breytingar á hvaða reit sem er á núverandi blaði.
• Tilvísunin í breytta reitinn verður alltaf í breytunni Markmál.
• Í fyrsta lagi athugum við hvort notandinn hafi breytt nákvæmlega hólfinu með viðmiðuninni (A4) - þetta er gert af rekstraraðilanum if.
• Þá byrjar hringrásin Fyrir hvert… að endurtaka yfir gráar frumur (D2:O2) með TRUE / FALSE vísitölum fyrir hvern dálk.
• Ef gildi næsta gráa reits er TRUE (true), þá er dálkurinn ekki falinn, annars felum við hann (eiginleika Falinn).

•  Dynamic array aðgerðir frá Office 365: FILTER, SORT og UNIC
• Snúningstafla með marglínuhaus með Power Query
• Hvað eru fjölvi, hvernig á að búa til og nota þau