Filtreerimiseks saadaval olevate üksuste arv on piiratud. Excel ei saa filtreerida veerge, milles üksuste arv ületab 999 (mitte ridade arvu).
Kui soovite filtreerida rohkem kui 999 üksust, kasutage täpsemat filtrit.
Täiustatud filtri loomiseks kasutame Microsoft Excelis funktsioone OFFSET ja COUNTA.
COUNTA: See tagastab väärtusi sisaldavate lahtrite arvu.
Funktsiooni COUNTA süntaks: = COUNTA (väärtus1, väärtus2, väärtus3 …)
Näide: Vahemikus A1: A5 sisaldavad lahtrid A2, A3 ja A5 väärtusi ning lahtrid A1 ja A4 on tühjad. Valige lahter A6 ja kirjutage valem-
= COUNTA (A1: A5) funktsioon naaseb 3
NIHE: See tagastab viite vahemikule, mis on nihutatud mitmest reast ja veerust teisest vahemikust või lahtrist.
Funktsiooni OFFSET süntaks: = OFFSET (viide, read, veerud, kõrgus, laius)
Viide:- See on lahter või vahemik, millest soovite nihutada.
Teisaldatavad read ja veerud: - Ridade arv, mida soovite lähtepunktist liigutada ja mõlemad võivad olla positiivsed, negatiivsed või nullid.
Kõrgus ja laius: - See on selle vahemiku suurus, mille soovite tagastada. See on valikuline väli.
Võtame näite Exceli funktsiooni Offset mõistmiseks.
Meil on andmeid vahemikus A1: D10. Veerg A sisaldab tootekoodi, veerg B sisaldab kogust, veerg C sisaldab tootehinda ja veerg D sisaldab kogukulu. Peame tagastama lahtri C5 väärtuse lahtris E2.
Soovitud tulemuse saavutamiseks peame järgima alltoodud samme.
- Valige lahter E2 ja kirjutage valem.
- = Nihe (A1,4,2,1,1)ja vajutage klaviatuuril Enter.
- Funktsioon tagastab lahtri C5 väärtuse.
Selles näites peame väärtuse saama lahtrist C5 kuni E2. Meie võrdluslahtrid on vahemiku esimene lahter, mis on A1 ja C5, 4 rida allpool ja 2 veergu paremal A1 -st. Seega on valem = OFFSET (A1,4,2,1,1) või = OFFSET (A1,4,2) (kuna 1,1 on valikuline).
Võtame nüüd näite dünaamilise loendi viimase väärtuse hankimiseks.
Meil on riikide nimed vahemikus. Kui nüüd lisada sellesse loendisse rohkem riike, peaks see olema ripploendis automaatselt saadaval.
Täpsema filtri ettevalmistamiseks järgige alltoodud samme:-
- Valige lahter B2.
- Minge vahekaardile Andmed, valige rühmast Andmetööriistad Andmete valideerimine.
- Ilmub dialoogiboks „Andmete valideerimine”. Vahekaardil „Seaded” valige ripploendist Luba „Kohandatud”.
- Valemikast aktiveeritakse.
- Kirjutage valem sellesse kasti.
- = Nihe (A: A, 1,0, COUNTA (A: A) -1,1).
- Klõpsake nuppu OK.
- Selles etapis on viimati uuendatud lahter A11.
- Andmete valideerimise nõuetekohase toimimise kontrollimiseks lisage lahtrisse A12 linna nimi.
Niipea kui lisate kirje A12 -sse, lisatakse see ripploendisse.
Nii saame Microsoft Excelis lisada rohkem kirjeid kui 999 üksust.