Jos haluat hakea dynaamisen luettelon viimeisen arvon, käytämme Microsoft Excel 2010/2013: n Data Validation -vaihtoehtoa sekä 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: - Kuinka monta riviä haluat siirtää aloituspisteen 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.
Noudata 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 arvoon 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 nyt lisäämme lisää maita tähän luetteloon, sen pitäisi olla käytettävissä avattavassa luettelossa automaattisesti.
Dynaamisen luettelon valmistamiseksi meidän on luotava kaava, joka hakee sarakkeen viimeisen arvon ja päivittyy automaattisesti, kun uusi numero lisätään.
Noudata 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.
Näin voit luoda dynaamisen luettelon ja lisätä siihen uusia merkintöjä automaattisesti Microsoft Excel 2010: ssä ja 2013: ssa.