Suodatettavien kohteiden määrä on rajoitettu. Excel ei voi suodattaa sarakkeita, joissa kohteiden määrä ylittää 999 (ei rivien määrää).
Jos haluat suodattaa yli 999 kohdetta, käytä lisäsuodatinta.
Edistyneen suodattimen luomiseksi käytämme Microsoft Excelin OFFSET- ja COUNTA -toimintoja.
COUNTA: Se palauttaa arvot sisältävien solujen määrän.
COUNTA -funktion syntaksi: = COUNTA (arvo1, arvo2, arvo3 …)
Esimerkki: Alueella A1: A5 solut A2, A3 ja A5 sisältävät arvot ja solut A1 ja A4 ovat tyhjiä. Valitse solu A6 ja kirjoita kaava-
= COUNTA (A1: A5) toiminto palaa 3
OFFSET: Se palauttaa viittauksen alueeseen, joka on siirtynyt rivien ja sarakkeiden lukumäärän ulkopuolelle toiselta alueelta tai solulta.
OFFSET -toiminnon syntaksi: = OFFSET (viite, rivit, sarakkeet, korkeus, leveys)
Viite:- Tämä on solu tai alue, josta haluat siirtyä.
Siirrettävät rivit ja sarakkeet: - Rivien määrä, jonka haluat siirtää lähtöpisteestä, ja molemmat voivat olla positiivisia, negatiivisia tai nollaisia.
Korkeus ja leveys: - Tämä on palautusalueen koko. Tämä on valinnainen kenttä.
Otetaan esimerkki Excelin offset -toiminnon ymmärtämiseksi.
Meillä on tietoja alueella A1: D10. Sarake A sisältää tuotekoodi, sarake B sisältää määrän, sarake C sisältää tuotekustannukset ja sarake D sisältää kokonaiskustannukset. Meidän on palautettava solun C5 arvo solussa E2.
Halutun tuloksen saavuttamiseksi meidän on noudatettava alla mainittuja vaiheita.
- Valitse solu E2 ja kirjoita kaava.
- = SIIRTO (A1,4,2,1,1)ja paina näppäimistön Enter -näppäintä.
- Funktio palauttaa solun C5 arvon.
Tässä esimerkissä meidän on hankittava arvo solusta C5 - E2. Vertailusolumme on alueen A1 ensimmäinen solu ja C5 on 4 riviä alapuolella ja 2 saraketta oikealla A1: stä. Näin ollen kaava on = OFFSET (A1,4,2,1,1) tai = OFFSET (A1,4,2) (koska 1,1 on valinnainen).
Otetaan nyt esimerkki dynaamisen luettelon viimeisen arvon hakemiseksi.
Meillä on maiden nimet eri alueilla. Jos lisäämme tähän luetteloon lisää maita, sen pitäisi olla avattavassa luettelossa automaattisesti.
Valmistele lisäsuodatin noudattamalla seuraavia ohjeita:-
- Valitse solu B2.
- Siirry Data -välilehdelle ja valitse Data Tools -ryhmästä Data Validation.
- "Tietojen validointi" -valintaikkuna tulee näkyviin. Valitse Asetukset -välilehden avattavasta Salli -luettelosta Mukautettu.
- Kaavaruutu aktivoidaan.
- Kirjoita kaava tähän ruutuun.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Napsauta OK.
- Tässä vaiheessa viimeisin päivitetty solu on A11.
- Jos haluat tarkistaa, toimiiko tietojen validointi oikein, lisää kaupungin nimi soluun A12.
Heti kun lisäät merkinnän A12: een, se lisätään avattavaan luetteloon.
Tällä tavalla voimme lisätä enemmän merkintöjä kuin 999 kohdetta Microsoft Exceliin.