Filtruoti galimų elementų skaičius yra ribotas. „Excel“ negali filtruoti stulpelių, kuriuose elementų skaičius viršija 999 (o ne eilučių skaičių).
Jei norite filtruoti, kai yra daugiau nei 999 elementų, naudokite išplėstinį filtrą.
Norėdami sukurti išplėstinį filtrą, „Microsoft Excel“ naudosime „OFFSET“ ir „COUNTA“ funkcijas.
SKAITMĖ: Pateikia ląstelių, kuriose yra reikšmių, skaičių.
Funkcijos „COUNTA“ sintaksė: = COUNTA (vertė1, vertė2, vertė3 …)
Pavyzdys: A1: A5 diapazone ląstelės A2, A3 ir A5 turi reikšmes, o langeliai A1 ir A4 yra tušti. Pasirinkite langelį A6 ir parašykite formulę-
= SKAIČIUS (A1: A5) funkcija grįš 3
NUOSTATA: Pateikiama nuoroda į diapazoną, kuris yra atskirtas eilučių ir stulpelių skaičiumi iš kito diapazono ar langelio.
OFFSET funkcijos sintaksė: = OFFSET (nuoroda, eilutės, stulpeliai, aukštis, plotis)
Nuoroda:- Tai langelis arba diapazonas, iš kurio norite kompensuoti.
Eilutės ir stulpeliai, kuriuos reikia perkelti: - Eilučių, kurias norite perkelti iš pradinio taško, skaičius ir abi šios gali būti teigiamos, neigiamos arba nulinės.
Aukštis ir plotis: - Tai diapazono, kurį norite grąžinti, dydis. Tai yra neprivalomas laukas.
Paimkime pavyzdį, kad suprastume „Excel“ funkciją „Offset“.
Turime duomenų diapazone A1: D10. A stulpelyje nurodytas produkto kodas, B stulpelyje - Kiekis, C stulpelyje nurodoma produkto kaina, o D stulpelyje - visos išlaidos. Turime grąžinti ląstelės C5 reikšmę E2 langelyje.
Norėdami gauti norimą rezultatą, turime atlikti toliau nurodytus veiksmus.
- Pasirinkite langelį E2 ir parašykite formulę.
- = Poslinkis (A1,4,2,1,1)ir paspauskite klaviatūros klavišą Enter.
- Funkcija grąžins langelio C5 vertę.
Šiame pavyzdyje turime gauti vertę iš langelio C5 iki E2. Mūsų atskaitos langelis yra pirmasis langelis diapazone, kuris yra A1 ir C5, yra 4 eilutės žemiau ir 2 stulpeliai į dešinę nuo A1. Taigi formulė yra = OFFSET (A1,4,2,1,1) arba = OFFSET (A1,4,2) (kadangi 1,1 neprivaloma).
Dabar paimkime pavyzdį, kad gautume paskutinę dinaminio sąrašo vertę.
Šalių pavadinimus turime įvairiuose diapazonuose. Dabar, jei prie šio sąrašo pridėsime daugiau šalių, jis turėtų būti automatiškai pasiekiamas išskleidžiamajame sąraše.
Norėdami paruošti išplėstinį filtrą, atlikite toliau nurodytus veiksmus:-
- Pasirinkite langelį B2.
- Eikite į skirtuką Duomenys, pasirinkite Duomenų įrankių grupė Duomenų patvirtinimas.
- Pasirodys dialogo langas „Duomenų patvirtinimas“. Skirtuke „Nustatymai“ išskleidžiamajame sąraše Leisti pasirinkite „Pasirinktinis“.
- Formulės langelis bus suaktyvintas.
- Į šį langelį įrašykite formulę.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Spustelėkite Gerai.
- Šiame etape paskutinis atnaujintas langelis yra A11.
- Norėdami patikrinti, ar duomenų tikrinimas veikia tinkamai, A12 langelyje pridėkite miesto pavadinimą.
Kai tik pridėsite įrašą A12, jis bus įtrauktas į išskleidžiamąjį sąrašą.
Taip „Microsoft Excel“ galime pridėti daugiau įrašų nei 999 elementų.