Lai izgūtu pēdējo vērtību dinamiskā sarakstā, mēs izmantosim opciju Datu validācija kopā ar funkcijām OFFSET un COUNTA programmā Microsoft Excel 2010/2013.
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: - Cik rindu vēlaties pārvietot sākuma punktu, un abas šīs rindas 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.
Izpildiet tālāk minētās 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 uz 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 dinamisku sarakstu, mums ir jāizveido formula, kas izgūst pēdējo slejas vērtību un automātiski atjaunina, kad tiek pievienots jauns numurs.
Izpildiet tālāk norādītās darbības:-
- 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ā jūs varat izveidot dinamisku sarakstu un automātiski aizpildīt tajā jaunus ierakstus programmā Microsoft Excel 2010 un 2013.