Dünaamilise loendi viimase väärtuse toomiseks kasutame Microsoft Excel 2010/2013 suvandit Andmete valideerimine koos funktsioonidega 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: - Mitu rida soovite alguspunkti teisaldada 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.
Järgige allpool nimetatud 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 ripploendis olema automaatselt saadaval.
Dünaamilise loendi koostamiseks peame looma valemi, mis toob välja veeru viimase väärtuse ja uuendatakse automaatselt uue numbri lisamisel.
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 saate luua dünaamilise loendi ja lisada sinna automaatselt uusi kirjeid Microsoft Excel 2010 ja 2013.
