Filtrēšanai pieejamo vienumu skaits ir ierobežots. Excel nevar filtrēt slejas, kurās vienumu skaits pārsniedz 999 (nevis rindu skaitu).
Lai filtrētu, ja ir vairāk nekā 999 vienumu, izmantojiet uzlaboto filtru.
Lai izveidotu papildu filtru, programmā Microsoft Excel izmantosim funkcijas “OFFSET” un “COUNTA”.
COUNTA: Tas atgriež to šūnu skaitu, kurās ir vērtības.
Funkcijas COUNTA sintakse: = COUNTA (vērtība1, vērtība2, vērtība3 …)
Piemērs: Diapazonā A1: A5 šūnas A2, A3 un A5 satur vērtības, un šūnas A1 un A4 ir tukšas. Atlasiet šūnu A6 un uzrakstiet formulu-
= COUNTA (A1: A5) funkcija atgriezīsies 3
OFFSET: Tas atgriež atsauci uz diapazonu, kas ir nobīdīts no rindām un kolonnām no cita diapazona vai šūnas.
Funkcijas OFFSET sintakse: = OFFSET (atsauce, rindas, kolonnas, augstums, platums)
Atsauce:- Šī ir šūna vai diapazons, no kura vēlaties kompensēt.
Pārvietojamās rindas un kolonnas: - Rindu skaits, kuras vēlaties pārvietot no sākuma punkta, un tās abas var būt pozitīvas, negatīvas vai nulles.
Augstums un platums: - Šis ir diapazona lielums, kuru vēlaties atgriezt. Šis ir neobligāts lauks.
Ņemsim piemēru, lai saprastu funkciju Offset programmā Excel.
Mums ir dati diapazonā A1: D10. A slejā ir norādīts produkta kods, B slejā ir daudzums, C slejā ir norādītas katra produkta izmaksas, bet D slejā - kopējās izmaksas. Mums jāatgriež šūnas C5 vērtība šūnā E2.
Lai iegūtu vēlamo rezultātu, mums jāveic šādas darbības.
- Atlasiet šūnu E2 un uzrakstiet formulu.
- = OFFSET (A1,4,2,1,1)un nospiediet tastatūras taustiņu Enter.
- Funkcija atgriezīs šūnas C5 vērtību.
Šajā piemērā mums jāiegūst vērtība no šūnas C5 līdz E2. Mūsu atsauces šūna ir pirmā šūna diapazonā, kas ir A1 un C5, ir 4 rindas zemāk un 2 kolonnas pa labi no A1. Tādējādi formula ir = OFFSET (A1,4,2,1,1) vai = OFFSET (A1,4,2) (jo 1,1 nav obligāta).
Tagad ņemsim piemēru, lai iegūtu pēdējo vērtību dinamiskajā sarakstā.
Mums ir valstu nosaukumi diapazonā. Tagad, ja šim sarakstam pievienosim vēl valstis, tam vajadzētu būt automātiski pieejamam nolaižamajā sarakstā.
Lai sagatavotu papildu filtru, rīkojieties šādi:-
- Atlasiet šūnu B2.
- Atveriet cilni Dati, grupā Datu rīki atlasiet Datu validācija.
- Parādīsies dialoglodziņš “Datu validācija”. Cilnē “Iestatījumi” nolaižamajā sarakstā Atļaut atlasiet “Pielāgots”.
- Formulu lodziņš tiks aktivizēts.
- Šajā lodziņā ierakstiet formulu.
- = Nobīde (A: A, 1,0, COUNTA (A: A) -1,1).
- Noklikšķiniet uz Labi.
- Šajā posmā pēdējā atjauninātā šūna ir A11.
- Lai pārbaudītu, vai datu validācija darbojas pareizi, šūnā A12 pievienojiet pilsētas nosaukumu.
Tiklīdz pievienosit ierakstu A12, tas tiks pievienots nolaižamajam sarakstam.
Tādā veidā mēs varam pievienot vairāk ierakstu nekā 999 vienumus programmā Microsoft Excel.