Sisu
Kui te pole päris algaja kasutaja, siis olete kindlasti juba märganud, et 99% kõigest Excelis on loodud töötama vertikaalsete tabelitega, kus parameetrid või atribuudid (väljad) läbivad veerge ja asub teave objektide või sündmuste kohta. ridades. Pivot-tabelid, vahesummad, valemite kopeerimine topeltklõpsuga – kõik on spetsiaalselt selle andmevormingu jaoks kohandatud.
Siiski pole eranditeta reegleid ja üsna korrapärase sagedusega küsitakse minult, mida teha, kui töösse sattus horisontaalse semantilise orientatsiooniga tabel või tabel, kus ridadel ja veerudel on sama tähendus:
Ja kui Excel ikka teab, kuidas horisontaalselt sortida (käsuga Andmed – Sordi – Suvandid – Sorteeri veerge), siis on filtreerimisega olukord hullem – Excelis pole lihtsalt sisseehitatud tööriistu veergude, mitte ridade filtreerimiseks. Seega, kui seisate silmitsi sellise ülesandega, peate leidma erineva keerukusega lahendusi.
Meetod 1. Uus funktsioon FILTER
Kui kasutate Excel 2021 uut versiooni või Excel 365 tellimust, saate äsja kasutusele võetud funktsiooni ära kasutada FILTER (FILTER), mis suudab filtreerida lähteandmeid mitte ainult ridade, vaid ka veergude järgi. Selle funktsiooni tööks on vaja horisontaalset ühemõõtmelist massiivi abi, kus iga väärtus (TRUE või FALSE) määrab, kas me näitame või vastupidi peidame tabeli järgmise veeru.
Lisame tabeli kohale järgmine rida ja kirjutame sellesse iga veeru olek:
- Oletame, et tahame alati kuvada esimest ja viimast veergu (päised ja kogusummad), seega määrame nende jaoks massiivi esimeses ja viimases lahtris väärtuse = TRUE.
- Ülejäänud veergude jaoks on vastavate lahtrite sisuks valem, mis kontrollib funktsioonide abil vajalikku tingimust И (JA) or OR (VÕI). Näiteks, et kogusumma on vahemikus 300 kuni 500.
Pärast seda jääb üle ainult funktsiooni kasutada FILTER veergude valimiseks, mille kohal on meie abimassiivi väärtus TRUE:
Samamoodi saate filtreerida veerge antud loendi järgi. Sel juhul aitab funktsioon COUNTIF (COUNTIF), mis kontrollib lubatud loendi tabeli päisest järgmise veeru nime esinemiste arvu:
2. meetod. Pivot-tabel tavalise tabeli asemel
Praegu on Excelis sisseehitatud horisontaalne filtreerimine veergude järgi ainult liigendtabelites, nii et kui meil õnnestub oma algne tabel pivot-tabeliks teisendada, saame seda sisseehitatud funktsiooni kasutada. Selleks peab meie lähtetabel vastama järgmistele tingimustele.
- omama “õiget” üherealist päise rida ilma tühjade ja liidetud lahtriteta – vastasel juhul ei tööta pivot-tabeli koostamine;
- ärge sisaldage ridade ja veergude siltides duplikaate – need "kokkutõmbuvad" kokkuvõttes ainult unikaalsete väärtuste loendiks;
- sisaldama ainult numbreid väärtusvahemikus (ridade ja veergude ristumiskohas), sest pivot-tabel rakendab neile kindlasti mingit liitmisfunktsiooni (summa, keskmine jne) ja see ei tööta tekstiga
Kui kõik need tingimused on täidetud, tuleb meie algse tabeliga sarnase pivot-tabeli koostamiseks see (algne) laiendada risttabelist tasaseks (normaliseerida). Ja kõige lihtsam viis seda teha on Power Query lisandmooduliga, mis on võimas andmete teisendamise tööriist, mis on Excelisse sisse ehitatud alates 2016. aastast.
Need on järgmised:
- Teisendame tabeli nutikaks dünaamiliseks käsuks Avaleht – vorminda tabelina (Avaleht — vorminda tabelina).
- Power Querysse laadimine käsuga Andmed – tabelist/vahemikust (andmed – tabelist/vahemikust).
- Filtreerime rea kogusummadega (kokkuvõttel on oma kogusummad).
- Paremklõpsake esimese veeru pealkirja ja valige Tühista teiste veergude ahendamine (Tühista muud veerud). Kõik valimata veerud teisendatakse kaheks – töötaja nimi ja tema näitaja väärtus.
- Veeru filtreerimine veergu läinud kogusummadega Atribuut.
- Ehitame pöördetabeli vastavalt saadud tasasele (normaliseeritud) tabelile käsuga Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht – sulge ja laadi – sulge ja laadi…).
Nüüd saate kasutada pivot-tabelites saadaolevate veergude filtreerimise võimalust – tavalised linnukesed nimede ja üksuste ees Allkirjafiltrid (Siltide filtrid) or Filtreerib väärtuse järgi (Väärtusfiltrid):
Ja loomulikult peate andmete muutmisel värskendama meie päringut ja kokkuvõtet kiirklahviga Ctrl+muu+F5 või meeskond Andmed – Värskenda kõike (Andmed – värskenda kõiki).
3. meetod. Makro VBA-s
Kõik eelnevad meetodid, nagu hästi näha, ei ole just filtreerivad – me ei peida algses loendis olevaid veerge, vaid moodustame esialgsest antud veergude komplektiga uue tabeli. Kui lähteandmetes on vaja veerge filtreerida (peita), siis on vaja põhimõtteliselt teistsugust lähenemist, nimelt makrot.
Oletame, et tahame käigult filtreerida veerge, kus tabeli päises olev halduri nimi vastab kollases lahtris A4 määratud maskile, näiteks algab tähega “A” (st saame “Anna” ja “Arthur” " tulemusena).
Nagu esimese meetodi puhul, rakendame esmalt abivahemiku rea, kus igas lahtris kontrollitakse meie kriteeriumi valemiga ja vastavalt nähtavate ja peidetud veergude jaoks kuvatakse loogilised väärtused TRUE või FALSE:
Seejärel lisame lihtsa makro. Paremklõpsake lehe vahekaarti ja valige käsk allikas (Lähtekood). Kopeerige ja kleepige avanevas aknas järgmine VBA kood:
Private Sub Worksheet_Change(ByVal Target As Range) Kui Target.Address = "$A$4" Siis iga lahtri jaoks vahemikus ("D2:O2") Kui lahter = tõene Siis cell.EntireColumn.Hidden = vale Muu lahter.Terve veerg.peidetud = True End If Next cell End If End Sub Selle loogika on järgmine:
- Üldiselt on see sündmuste töötleja Tööleht_Muuda, st seda makrot käitatakse automaatselt praegusel lehel mis tahes lahtri muudatuste korral.
- Viide muudetud lahtrile on alati muutujas sihtmärk.
- Esmalt kontrollime, et kasutaja on muutnud täpselt kriteeriumiga (A4) lahtrit – seda teeb operaator if.
- Siis algab tsükkel Igaühele… hallide lahtrite (D2:O2) kordamiseks iga veeru indikaatori väärtustega TRUE/FALSE.
- Kui järgmise halli lahtri väärtus on TRUE (tõene), siis veergu ei peideta, vastasel juhul peidame selle (omadus varjatud).
- Dünaamilise massiivi funktsioonid Office 365-st: FILTER, SORT ja UNIC
- Mitmerealise päisega liigendtabel Power Query abil
- Mis on makrod, kuidas neid luua ja kasutada