Stebint ar analizuojant duomenis, taip pat atliekant įvairaus pobūdžio skaičiavimus, ne visuomet reikalingi visi duomenų lentelėje esantys duomenys. Paprasčiausias ir patogiausias būdas atrinkti reikiamus duomenis iš didelių duomenų lentelių yra automatinio filtro naudojimas. Šis filtras puikiai pritaikytas net ir nepažengusiems MS Excel skaičiuoklės vartotojams, nes filtruojant automatiniu filtru užtenka keleto pelės klavišo paspaudimų. Automatinio filtravimo įrankis yra DATA kortelėje, įrankių grupėje SORT & FILTER (žr. 1 pav.).
1 pav. Automatinis filtras
Kairėje įrankių grupės pusėje yra nurodyti įrankiai, skirti duomenims rūšiuoti didėjimo, mažėjimo arba specifine tvarka, o dešinėje – filtravimo įrankiai.
Norint panaudoti automatinį filtrą, reikia pasižymėti bet kurį duomenų lentelės stulpelio pavadinimą ir DATA kortelėje aktyvuoti filtrą, paspaudžiant piktogramą. Aktyvavus filtrą, šalia kiekvieno stulpelio pavadinimo atsiranda filtravimo mygtukai. Filtravimo pasirinkimo parametrai pateikiami žemiau esančiame paveiksle.
2 Pav. Teksto filtras
Filtrai, pritaikyti stulpeliams, šiek tiek skiriasi, priklausomai nuo to, kokio tipo duomenys įrašyti stulpeliuose. Pirmieji penki pasirinkimai visuose filtruose yra vienodi. Tai duomenų rūšiavimas didėjimo, mažėjimo tvarka, taip pat rūšiavimas pagal langelių ar teksto spalvą (SORT BY COLOR). Yra galimybė ir filtruoti lentelės duomenis pagal įrašų ar langelių spalvą, pasirinkus FILTER BY COLOR.
Lentelės apačioje nurodomi visi duomenų stulpelyje esantys įrašai, kuriuos galima pasirinkti arba pašalinti, pažymint ar panaikinant varneles šalia įrašų. Taip rankiniu būdu sukuriamas reikiamas filtras, tačiau, kai įrašų yra daug, įrašų žymėjimas filtre tampa nepatogus.
Filtro parametrų pasirinkimo lentelės viduryje yra nuo stulpelio įrašų priklausantis filtras (žr. 2 pav.). Pateiktame paveiksle filtruojamas teksto stulpelis, todėl galima rinktis iš siūlomų teksto filtrų ir filtruoti lentelę nebūtinai pagal tikslų įrašą. Visi galimi filtro pasirinkimai su paaiškinimais pateikti 1 lentelėje.
Pasirinkus bet kurį teksto filtrą, pasirodo pasirinktinio filtravimo lentelė, kurioje nurodomas filtravimo kriterijus ir pobūdis (žr. 3 pav.).
3 pav. Pasirinktinis filtras
Pirmajame laukelyje nurodoma filtravimo sąlyga ir dešiniau įrašoma filtravimo kriterijaus reikšmė. Žemiau esančiuose laukeliuose galima nustatyti antrąją filtravimo sąlygą ir kriterijaus reikšmę, taip nurodant duomenų stulpelį filtruoti iškart pagal du kriterijus. Ryšys tarp dviejų filtravimo sąlygų nurodomas pasirenkant vieną iš įrašų, esančių tarp sąlygų nustatymo laukelių (AND arba OR). Pasirinkus AND nurodoma, jog viename langelyje turi būti vykdomos abi nurodytos sąlygos, kad jis būtų matomas gavus filtravimo rezultatą. OR tipas nurodo, kad turi būti vykdoma bent viena iš nurodytų sąlygų.
Pagal duomenų tipą stulpeliuose dar gali būti matomi datų ir skaičių filtrai. Datų filtre yra daugiausiai pasirinkimo variantų, kurie suteikia lankstumo filtruojant reikiamas datas (žr. 4 pav.).
4 pav. Datų filtrai
Filtre yra pateikta daug pasirinkimų, nes datos filtruojamos pagal kiekvieną datos vienetą arba intervalais. Visi galimi pasirinkimai paaiškinti žemiau:
EQUALS… – tikslios datos filtravimas;
BEFORE… – datos, ankstesnės nei nurodyta data;
AFTER… – datos, vėlesnės nei nurodyta data;
BETWEEN… – datos nurodytame intervale;
TOMORROW… – rytdienos data;
TODAY… – šiandienos data;
YESTERDAY… – vakar dienos data;
NEXT WEEK / MONTH / QUARTER / YEAR – ateinanti savaitė / mėnuo / ketvirtis / metai;
THIS WEEK / MONTH / QUARTER / YEAR – ši savaitė / mėnuo / ketvirtis / metai;
LAST WEEK / MONTH / QUARTER / YEAR – praėjusi savaitė / mėnuo / ketvirtis / metai;
YEAR TO DATE – šių metų datos iki šiandienos datos;
ALL DATES IN THE PERIOD – visos datos nurodytame periode (galima rinktis konkretų ketvirtį arba mėnesį);
CUSTOM FILTER… – pasirinktinis datų filtras.
Skaičiams filtruoti naudojamos Excel skaičiuoklėje populiarios sąlygos (žr. 5 pav.).
5 pav. Skaičių filtras
Išskleistame sąraše pateiktos tokios sąlygos stulpeliams su skaitinėmis reikšmėmis filtruoti:
EQUALS… – reikšmės, lygios nurodytai;
DOES NOT EQUAL… – reikšmės, nelygios nurodytai;
GREATER THAN… – didesnės už nurodytą reikšmės;
GREATER THAN OR EQUAL TO… – didesnės arba lygios nurodytai reikšmei;
LESS THAN… – reikšmės, mažesnės už nurodytą;
LESS THAN OR EQUAL TO… – reikšmės, mažesnės arba lygios nurodytai;
BETWEEN… – reikšmės, esančios nurodytame intervale;
TOP 10… – nurodytas kiekis didžiausių reikšmių stulpelyje;
ABOVE AVERAGE – reikšmės, didesnės už vidurkį;
BELOW AVERAGE – reikšmės, mažesnės už vidurkį;
CUSTOM FILTER… – pasirinktinis skaičių filtras.
Naudojant nurodytus filtrus, padidinamas darbo našumas ir filtruoti galima ne tik pagal konkrečią reikšmę, bet ir pagal dalį teksto ar kitokio tipo duomenis. Tuomet kaip filtravimo rezultatas gaunami visi panašūs duomenų lentelės įrašai.
Išfiltravus duomenis keliuose stulpeliuose vienu metu, duomenų lentelėje galima filtrą panaikinti konkrečiam stulpeliui arba visiems stulpeliams vienu metu. Filtras viename stulpelyje panaikinamas išskleidus filtravimo parinkčių langą ir spustelėjus aktyvų pasirinkimą CLEAR FILTER FROM „STULPELIO PAVADINIMAS“.
6 pav. Filtro panaikinimas viename stulpelyje
Panaikinus filtrą viename stulpelyje, kituose jis išlieka, taigi galima atlikti filtravimo korekcijas atskirai kiekvienam stulpeliui arba panaikinti jau nebereikalingus filtrus. Tais atvejais, kai išfiltravus duomenis reikia vėl matyti visą duomenų lentelę, filtrai naikinami visiems stulpeliams iš karto. Mygtukas visiems filtrams panaikinti yra įrankių juostos SORT & FILTER įrankių grupėje (žr. 7 pav.).
7 pav. Visų filtrų panaikinimas
Šalia automatinio filtravimo įrankio piktogramos yra mygtukas CLEAR, kuris tampa aktyvus tuomet, kai lentelėje yra pritaikytų filtrų. Paspaudus šį mygtuką, panaikinami iškart visi duomenų lentelėje pritaikyti filtrai ir ši piktograma tampa nebeaktyvi, taigi, matydami šio įrankio būklę, galime spręsti, ar lentelėje yra pritaikytų filtrų. Žemiau esantis mygtukas REAPPLY skirtas iš naujo duomenų sričiai pritaikyti tuos pačius filtrus. Jeigu išfiltruotame stulpelyje atliekami pakeitimai, t. y. pakeičiami langeliuose esantys įrašai, paspaudus REAPPLY mygtuką stulpelyje paliekami tik filtravimo taisyklę atitinkantys duomenys.
Kartu su filtravimu neretais atvejais atliekami ir skaičiavimai. Skaičiuojama suma, kiekis, vidutinis dydis ar kitas reikiamas rezultatas. Duomenų srityje pritaikius įprastas funkcijas (SUM, COUNT, AVERAGE ar kt.) ir išfiltravus reikiamus lentelės stulpelius, funkcijų rezultatas nesiskirs. Kitaip tariant, filtravimas nedaro įtakos šių funkcijų skaičiavimo rezultatams.
Skaičiavimams, priklausomiems nuo filtravimo, naudojama SUBTOTAL funkcija. Jos sintaksė pateikiama žemiau:
=SUBTOTAL(funkcijos numeris ; masyvas1; [masyvas2]; …; [masyvas n]).
Pirmas funkcijos kintamasis – funkcijos numeris, kuris nurodomas skaičiumi. Atvėrus funkcijos SUBTOTAL skliaustą, matomas funkcijų sąrašas su skaičiais, kuriuos galima įrašyti ranka arba pasirinkti tiesiai iš sąrašo (žr. 8 pav.).
8 pav. SUBTOTAL funkcijos kintamieji
Funkcijoje SUBTOTAL galima pasirinkti vieną iš 11 galimų funkcijų tarpiniams skaičiavimams. Nurodžius tinkamą funkciją, už kabliataškio pažymimas masyvas duomenų srityje, kuriam pasirinktas veiksmas turi būti taikomas. Svarbu tai, kad SUBTOTAL funkcijoje masyvas žymimas prieš duomenų lentelės filtravimą, t. y. žymimas visas duomenų stulpelis.
Panagrinėkime konkretų pavyzdį.
Duomenų lentelėje yra du stulpeliai: tiekėjo įmonės pavadinimas ir pirkimo iš tiekėjo suma. Reikia apskaičiuoti, už kokią sumą buvo nupirkta prekių iš uždarųjų akcinių bendrovių (žr. 9 pav.).
9 pav. SUBTOTAL funkcijos pritaikymas
Pirmiausia įsitikinama, kad lentelės stulpeliai nėra išfiltruoti, tuomet taikoma SUBTOTAL funkcija. Pasirenkama sumavimo funkcija, pažymėta 9 numeriu, ir taikoma visam sumos stulpeliui duomenų lentelėje. Įvedus funkciją gaunama bendra pirkimų suma. Vėliau atliekamas filtravimas lentelės stulpeliuose ir gaunamas reikiamas rezultatas (žr. 10 pav.).
10 pav. SUBTOTAL funkcijos rezultatas po filtravimo
Išfiltravus duomenų lentelės tiekėjo stulpelį, kad būtų matomos tik uždarųjų akcinių bendrovių pardavimo sumos, matomas SUBTOTAL funkcijos rezultato pokytis. Rezultatas šioje funkcijoje apskaičiuojamas tik iš nepaslėptų langelių reikšmių, taigi matoma tik ta suma, už kiek buvo pirkta iš uždarųjų akcinių bendrovių, ignoruojant paslėptus langelius, kuriuose įrašyti kiti tiekėjai.
Naudojantis automatinio filtravimo įrankiu, duomenų lentelės stulpeliai filtruojami keliais mygtukų paspaudimais ir filtrai gali būti lengvai koreguojami ar panaikinami. Skaičiavimams filtruotoje lentelėje naudojama SUBTOTAL funkcija, kuri pateikia rezultatą, apskaičiuotą tik iš matomų langelių reikšmių. Filtruojant automatiniu filtru susiduriama tik su keliomis problemomis: vieną stulpelį galima išfiltruoti pagal daugiausiai du kriterijus ir nėra galimybės filtravimo sąlygą taikyti iškart per kelis stulpelius. Tačiau šiuos trūkumus atperka naudojimosi automatiniu filtru paprastumas ir gan nemažos galimybės.
KONTROLINIAI KLAUSIMAI:
1. Kelių skirtingų tipų filtrus galima pastebėti pritaikius automatinį filtrą duomenų lentelei?
2. Kiek skirtingų filtravimo kriterijų galima nurodyti vienam filtruojamam stulpeliui?
3. Kaip išfiltruoti tik šių metų datas iki šiandienos datos?
4. Kur rasti mygtuką, panaikinantį visuose stulpeliuose pritaikytus filtrus?
5. Koks antras kintamasis nurodomas SUBTOTAL funkcijoje?