Turinys
Jei nesate gana pradedantysis vartotojas, tuomet jau turėjote pastebėti, kad 99% visko programoje „Excel“ skirta dirbti su vertikaliomis lentelėmis, kur parametrai ar atributai (laukai) eina per stulpelius, o informacija apie objektus ar įvykius. eilutėse. Pivot lentelės, tarpinės sumos, formulių kopijavimas dukart spustelėjus – viskas pritaikyta būtent šiam duomenų formatui.
Tačiau nėra taisyklių be išimčių ir gana dažnai manęs klausia, ką daryti, jei darbe pasitaikė lentelė su horizontalia semantine orientacija arba lentelė, kurioje eilutės ir stulpeliai turi vienodą reikšmę:
Ir jei „Excel“ vis tiek žino, kaip rūšiuoti horizontaliai (su komanda Duomenys – Rūšiuoti – Parinktys – Rūšiuoti stulpelius), tada su filtravimu situacija prastesnė – tiesiog nėra įmontuotų įrankių, skirtų stulpelių, o ne eilučių filtravimui Excel. Taigi, jei susidursite su tokia užduotimi, turėsite sugalvoti įvairaus sudėtingumo sprendimus.
1 būdas. Nauja FILTER funkcija
Jei naudojate naują „Excel 2021“ versiją arba „Excel 365“ prenumeratą, galite pasinaudoti naujai pristatyta funkcija FILTRAS (FILTRAS), kuri gali filtruoti šaltinio duomenis ne tik pagal eilutes, bet ir pagal stulpelius. Kad ši funkcija veiktų, reikalinga pagalbinė horizontali vienmačio masyvo eilutė, kurioje kiekviena reikšmė (TRUE arba FALSE) nustato, ar kitą lentelės stulpelį rodysime, ar, atvirkščiai, slėpsime.
Virš lentelės pridėkime šią eilutę ir parašykime kiekvieno stulpelio būseną:
- Tarkime, kad visada norime rodyti pirmąjį ir paskutinįjį stulpelius (antraštes ir sumas), todėl jiems pirmoje ir paskutinėje masyvo langeliuose nustatome reikšmę = TRUE.
- Likusiuose stulpeliuose atitinkamų langelių turinys bus formulė, kuri tikrina mums reikalingą sąlygą naudojant funkcijas И (IR) or OR (ARBA). Pavyzdžiui, kad bendra suma yra nuo 300 iki 500.
Po to belieka naudoti funkciją FILTRAS norėdami pasirinkti stulpelius, virš kurių mūsų pagalbinis masyvas turi TRUE reikšmę:
Panašiai galite filtruoti stulpelius pagal nurodytą sąrašą. Šiuo atveju funkcija padės COUNTIF (COUNTIF), kuris tikrina kito stulpelio pavadinimo atvejų skaičių iš lentelės antraštės leidžiamame sąraše:
2 būdas. Suvestinė lentelė vietoj įprastos
Šiuo metu "Excel" turi integruotą horizontalųjį filtravimą pagal stulpelius tik suvestinėse lentelėse, todėl jei mums pavyks konvertuoti originalią lentelę į suvestinę, galime pasinaudoti šia integruota funkcija. Norėdami tai padaryti, mūsų šaltinio lentelė turi atitikti šias sąlygas:
- turėti „teisingą“ vienos eilutės antraštės eilutę be tuščių ir sujungtų langelių – kitaip nepavyks sukurti suvestinės lentelės;
- neturėkite dublikatų eilučių ir stulpelių etiketėse – jie suvestinėje „susitrauks“ į tik unikalių reikšmių sąrašą;
- reikšmių diapazone yra tik skaičiai (eilučių ir stulpelių sankirtoje), nes suvestinė lentelė jiems tikrai pritaikys tam tikrą agregavimo funkciją (suma, vidurkis ir kt.) ir tai neveiks su tekstu
Jei tenkinamos visos šios sąlygos, norint sukurti sukamąją lentelę, kuri atrodytų kaip mūsų originali lentelė, ją (originalią) reikės išplėsti iš kryžminės lentelės į plokščią (normalizuota). Lengviausias būdas tai padaryti yra naudojant Power Query priedą – galingą duomenų transformavimo įrankį, integruotą programoje „Excel“ nuo 2016 m.
Tai yra:
- Paverskime lentelę „išmaniąja“ dinamine komanda Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę).
- Įkeliama į Power Query su komanda Duomenys – iš lentelės / diapazonas (duomenys – iš lentelės / diapazonas).
- Filtruojame eilutę su sumomis (suvestinė turės savo sumas).
- Dešiniuoju pelės mygtuku spustelėkite pirmojo stulpelio antraštę ir pasirinkite Atskleisti kitus stulpelius (Atjunkite kitus stulpelius). Visi nepasirinkti stulpeliai paverčiami į du – darbuotojo vardą ir pavardę bei jo rodiklio reikšmę.
- Stulpelio filtravimas pagal sumas, kurios pateko į stulpelį atributas.
- Sukuriame pivot lentelę pagal gautą plokščią (normalizuotą) lentelę su komanda Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis – Uždaryti ir įkelti – Uždaryti ir įkelti į…).
Dabar galite naudoti galimybę filtruoti suvestinėse lentelėse esančius stulpelius – įprastas varneles prieš pavadinimus ir elementus. Parašo filtrai (Etiketės filtrai) or Filtruoja pagal vertę (Verčių filtrai):
Ir, žinoma, keičiant duomenis turėsite atnaujinti mūsų užklausą ir suvestinę sparčiuoju klavišu "Ctrl"+Kitas+F5 arba komanda Duomenys – Atnaujinti viską (Duomenys – Atnaujinti viską).
3 būdas. Makrokomandas VBA
Visi ankstesni metodai, kaip nesunkiai matote, nėra tiksliai filtruojami – stulpelių nepaslėpiame pirminiame sąraše, o sudarome naują lentelę su duotu stulpelių rinkiniu iš pirminio. Jei reikia filtruoti (slėpti) šaltinio duomenų stulpelius, reikia iš esmės kitokio požiūrio, būtent makrokomandos.
Tarkime, kad norime greitai filtruoti stulpelius, kuriuose vadybininko vardas lentelės antraštėje atitinka kaukę, nurodytą geltoname langelyje A4, pavyzdžiui, prasideda raide „A“ (ty gaukite „Anna“ ir „Arthur“ " kaip rezultatas).
Kaip ir pirmuoju metodu, pirmiausia įgyvendiname pagalbinę diapazono eilutę, kurioje kiekvienoje langelyje mūsų kriterijus bus patikrintas pagal formulę ir atitinkamai matomų ir paslėptų stulpelių loginės reikšmės TRUE arba FALSE bus rodomos:
Tada pridėkime paprastą makrokomandą. Dešiniuoju pelės mygtuku spustelėkite lapo skirtuką ir pasirinkite komandą Šaltinis (Pirminis kodas). Nukopijuokite ir įklijuokite šį VBA kodą į atsidariusį langą:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Tada kiekvienam diapazono langeliui("D2:O2") If cell = True Tada cell.EntireColumn.Hidden = False Kitu cell.EntireColumn.Hidden = True End If Next cell End If End Sub Jo logika yra tokia:
- Apskritai tai yra įvykių tvarkytojas Darbalapis_Keisti, ty ši makrokomanda bus automatiškai paleista bet kokiame dabartinio lapo langelio pakeitime.
- Nuoroda į pakeistą langelį visada bus kintamajame Taikinys.
- Pirmiausia patikriname, ar vartotojas tiksliai pakeitė langelį su kriterijumi (A4) – tai atlieka operatorius if.
- Tada prasideda ciklas Kiekvienam… kartoti pilkus langelius (D2:O2) su kiekvieno stulpelio TRUE / FALSE indikatoriaus reikšmėmis.
- Jei kito pilko langelio reikšmė yra TRUE (true), tada stulpelis nėra paslėptas, kitaip mes jį paslėpsime (ypatybė paslėptas).
- Dinaminės masyvo funkcijos iš Office 365: FILTER, SORT ir UNIC
- Suvestinė lentelė su kelių eilučių antrašte naudojant „Power Query“.
- Kas yra makrokomandos, kaip jas kurti ir naudoti