Sisällys
Jos et ole aivan aloittelija, sinun on jo täytynyt huomata, että 99% kaikesta Excelissä on suunniteltu toimimaan pystysuorien taulukoiden kanssa, joissa parametrit tai attribuutit (kentät) kulkevat sarakkeiden läpi ja tietoa objekteista tai tapahtumista linjoissa. Pivot-taulukot, välisummat, kaavojen kopioiminen kaksoisnapsautuksella – kaikki on räätälöity erityisesti tätä tietomuotoa varten.
Sääntöjä ei kuitenkaan ole ilman poikkeuksia ja melko säännöllisin väliajoin minulta kysytään, mitä tehdä, jos työssä törmäsi semanttisesti vaakasuoraan suuntautuneeseen taulukkoon tai taulukkoon, jossa riveillä ja sarakkeilla on sama merkitys:
Ja jos Excel vielä osaa lajitella vaakasuunnassa (komennolla Tiedot – Lajittele – Asetukset – Lajittele sarakkeet), silloin tilanne suodatuksen kanssa on huonompi – Excelissä ei yksinkertaisesti ole sisäänrakennettuja työkaluja sarakkeiden, ei rivien, suodattamiseen. Joten jos kohtaat tällaisen tehtävän, sinun on keksittävä erilaisia monimutkaisia kiertotapoja.
Menetelmä 1. Uusi FILTER-toiminto
Jos käytät Excel 2021:n uutta versiota tai Excel 365 -tilausta, voit hyödyntää äskettäin esiteltyä ominaisuutta SUODATIN (SUODATTAA), joka voi suodattaa lähdetiedot paitsi rivien, myös sarakkeiden mukaan. Toimiakseen tämä funktio vaatii ylimääräisen vaakasuuntaisen yksiulotteisen taulukkorivin, jossa jokainen arvo (TRUE tai FALSE) määrittää, näytetäänkö tai päinvastoin piilotetaanko taulukon seuraava sarake.
Lisätään seuraava rivi taulukkomme yläpuolelle ja kirjoitetaan siihen kunkin sarakkeen tila:
- Oletetaan, että haluamme aina näyttää ensimmäisen ja viimeisen sarakkeen (otsikot ja summat), joten asetamme niille taulukon ensimmäiseen ja viimeiseen soluun arvon = TRUE.
- Muille sarakkeille vastaavien solujen sisältö on kaava, joka tarkistaa tarvitsemamme ehdon funktioiden avulla И (JA) or OR (OR). Esimerkiksi, että kokonaissumma on välillä 300-500.
Sen jälkeen jää vain käyttää toimintoa SUODATIN valitaksesi sarakkeet, joiden yläpuolella aputaulukollamme on TOSI arvo:
Vastaavasti voit suodattaa sarakkeita tietyn luettelon mukaan. Tässä tapauksessa toiminto auttaa LASKE.JOS (COUNTIF), joka tarkistaa sallitun luettelon taulukon otsikosta seuraavan sarakkeen nimen esiintymisten määrän:
Tapa 2. Pivot-taulukko tavallisen sijaan
Tällä hetkellä Excelissä on sisäänrakennettu vaakasuora suodatus sarakkeiden mukaan vain pivot-taulukoissa, joten jos onnistumme muuttamaan alkuperäisen taulukomme pivot-taulukoksi, voimme käyttää tätä sisäänrakennettua toimintoa. Tätä varten lähdetaulukkomme on täytettävä seuraavat ehdot:
- sinulla on "oikea" yksirivinen otsikkorivi ilman tyhjiä ja yhdistettyjä soluja – muuten pivot-taulukon rakentaminen ei toimi;
- älä sisällä kaksoiskappaleita rivien ja sarakkeiden otsikoissa – ne "kutistuvat" yhteenvedossa vain yksilöllisten arvojen luetteloksi;
- sisältävät vain numeroita arvoalueella (rivien ja sarakkeiden leikkauskohdassa), koska pivot-taulukko käyttää niihin ehdottomasti jonkinlaista aggregointifunktiota (summa, keskiarvo jne.) eikä tämä toimi tekstin kanssa
Jos kaikki nämä ehdot täyttyvät, alkuperäiseltä taulukolta näyttävän pivot-taulukon rakentamiseksi se (alkuperäinen) on laajennettava ristitaulukosta litteäksi (normalisoitu). Ja helpoin tapa tehdä tämä on Power Query -apuohjelma, tehokas tietojen muunnostyökalu, joka on rakennettu Exceliin vuodesta 2016 lähtien.
Nämä ovat:
- Muunnetaan taulukko "älykkääksi" dynaamiseksi komennoksi Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona).
- Ladataan Power Queryyn komennolla Tiedot – taulukosta/välistä (tiedot – taulukosta/välistä).
- Suodatamme rivin loppusummalla (yhteenvedolla on omat summansa).
- Napsauta hiiren kakkospainikkeella ensimmäisen sarakkeen otsikkoa ja valitse Pura muut sarakkeet (Poista muut sarakkeet). Kaikki valitsemattomat sarakkeet muunnetaan kahdeksi – työntekijän nimeksi ja hänen indikaattorinsa arvoksi.
- Sarakkeen suodattaminen sarakkeeseen menneillä kokonaismäärillä Ominaisuus.
- Rakennamme pivot-taulukon tuloksena olevan tasaisen (normalisoidun) taulukon mukaan komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje & Lataa — Sulje & Lataa…).
Nyt voit käyttää kykyä suodattaa pivot-taulukoiden sarakkeita – tavallisia valintamerkkejä nimien ja kohteiden edessä Allekirjoitussuodattimet (Etikettisuodattimet) or Suodattaa arvon mukaan (Arvosuodattimet):
Ja tietysti, kun muutat tietoja, sinun on päivitettävä kyselymme ja yhteenveto pikanäppäimellä Ctrl+muut+F5 tai joukkue Tiedot – Päivitä kaikki (Data – Päivitä kaikki).
Menetelmä 3. Makro VBA:ssa
Kaikki aiemmat menetelmät, kuten voit helposti nähdä, eivät ole täsmälleen suodatusta – emme piilota sarakkeita alkuperäisessä luettelossa, vaan muodostamme uuden taulukon tietyllä sarakejoukolla alkuperäisestä. Jos lähdetiedon sarakkeet on suodatettava (piilota), tarvitaan olennaisesti erilainen lähestymistapa, nimittäin makro.
Oletetaan, että haluamme suodattaa lennossa sarakkeita, joissa taulukon otsikossa oleva johtajan nimi täyttää keltaisessa solussa A4 määritetyn maskin, esimerkiksi alkaa kirjaimella "A" (eli saa "Anna" ja "Arthur" " tuloksena).
Kuten ensimmäisessä menetelmässä, toteutamme ensin apualueen rivin, jossa jokaisessa solussa kriteerimme tarkistetaan kaavalla ja loogiset arvot TRUE tai FALSE näytetään vastaavasti näkyville ja piilotetuille sarakkeille:
Lisätään sitten yksinkertainen makro. Napsauta hiiren kakkospainikkeella arkkivälilehteä ja valitse komento lähde (Lähdekoodi). Kopioi ja liitä seuraava VBA-koodi avautuvaan ikkunaan:
Private Sub Worksheet_Change(ByVal Target As Range) Jos Target.Address = "$A$4" Sitten jokaiselle alueen solulle("D2:O2") Jos solu = tosi Sitten solu.KokoSarake.Piilotettu = Epätosi Muu solu.KokoSarake.Piilotettu = True End If Next solu End If End Sub Sen logiikka on seuraava:
- Yleensä tämä on tapahtumakäsittelijä Työtaulukko_Muuta, eli tämä makro suoritetaan automaattisesti minkä tahansa nykyisen arkin solun muutoksen yhteydessä.
- Viittaus muutettuun soluun on aina muuttujassa Kohde.
- Ensin tarkistamme, että käyttäjä on vaihtanut täsmälleen solua, jolla on kriteeri (A4) – tämän tekee operaattori if.
- Sitten sykli alkaa Jokaiselle… toistaa harmaita soluja (D2:O2) TOSI / EPÄTOSI -ilmaisinarvoilla jokaiselle sarakkeelle.
- Jos seuraavan harmaan solun arvo on TRUE (tosi), saraketta ei piiloteta, muuten piilotamme sen (ominaisuus kätketty).
- Dynaamiset taulukon toiminnot Office 365:stä: FILTER, SORT ja UNIC
- Pivot-taulukko monirivisellä otsikolla Power Queryn avulla
- Mitä makrot ovat, miten niitä luodaan ja käytetään