Ievads dinamiskajos diapazonos
Funkciju VLOOKUP bieži izmanto, lai atrastu informāciju, kas tiek glabāta Excel tabulās. Piemēram, ja mums ir cilvēku vārdu un vecumu saraksts:
Un tad mēs varam tuvējā šūnā izmantot funkciju VLOOKUP, lai noteiktu Pāvila vecumu:
Līdz šim tas ir diezgan standarts. Bet kas notiek, ja mums sarakstam jāpievieno vēl daži vārdi? Acīmredzama doma būtu mainīt diapazonu VLOOKUP. Tomēr patiešām sarežģītā modelī var būt vairākas atsauces uz VLOOKUP. Tas nozīmē, ka mums būs jāmaina katra atsauce - pieņemot, ka mēs zinām, kur tie atrodas.
Tomēr Excel piedāvā alternatīvu veidu, ko sauc par DYNAMIC diapazonu. Šis ir diapazons, kas automātiski paplašina atjauninājumus. Tas ir lieliski piemērots, ja jūsu saraksti tiek pastāvīgi paplašināti (piemēram, pārdošanas mēneša dati).
Lai iestatītu dinamisku diapazonu, mums ir jābūt diapazona nosaukumam, tāpēc mēs to saucam par AGE_DATA. Pieeja dinamisko diapazonu iestatīšanai dažādās Excel 2007 un iepriekšējās Excel versijās atšķiras:
Programmā Excel 2007 zem formulām noklikšķiniet uz “Definēt vārdu”:
Iepriekšējās Excel versijās noklikšķiniet uz “Ievietot” un pēc tam uz Vārdi.
Uznirstošajā lodziņā ievadiet mūsu dinamiskā diapazona nosaukumu - “AGE DATA”:
Lodziņā “Atsaucas uz” mums jāievada mūsu datu diapazons. Tas tiks sasniegts, izmantojot OFFSET funkciju. Tam ir 5 argumenti:
= OFFSET (atsauce, rindas, atlase, augstums, platums)
- Atsauce ir mūsu diapazona augšējā kreisā stūra adrese - šajā gadījumā šūna B5
- Rindas ir to rindu skaits no augšas pa kreisi, kuras mēs vēlamies, lai šis diapazons būtu - šajā gadījumā tā būs 0
- Cols ir to rindu skaits no augšas pa kreisi, kuras mēs vēlamies, lai šis diapazons būtu - šajā gadījumā tā būs 0
- Diapazona augstums - skatiet tālāk
- Diapazona platums - tas ir 2, un mūsu diapazonā ir DIVAS slejas (personu vārds un vecums)
Tagad diapazona augstumam būs jāmainās atkarībā no mūsu tabulas ierakstu skaita (kas pašlaik ir 7).
Protams, mēs vēlamies uzskaitīt mūsu tabulas rindas, kas tiek automātiski atjauninātas, tāpēc viens veids, kā to izdarīt, ir izmantot funkciju COUNTA. Tas tikai saskaita tukšo šūnu skaitu diapazonā. Tā kā mūsu vārdi ir B slejā, mūsu datu ierakstu skaits ir COUNTA (B: B).
Ņemiet vērā: ja jūs to ievietotu šūnā, jūs iegūtu vērtību 8, jo tajā ir iekļauts galvenes nosaukums. Tomēr, ka tas ir nebūtiski.
Tātad lodziņā “Atsaucas uz” mēs ievietojam:
= NOBĪDE ($ B $ 5,0,0, skaitīšana (B: B), 2)
Un noklikšķiniet uz pogas Labi. Mūsu dinamiskais diapazons ir izveidots.
Tagad atgriezieties pie VLOOKUP formulām un nomainiet diapazonu $ B: 4: $ C11 ar mūsu jaunā dinamiskā diapazona nosaukumu AGE_DATA, lai mums būtu:
Līdz šim nekas nav mainījies. Tomēr, ja mēs tabulai pievienojam vēl dažus nosaukumus:
Kamerā, kur mums bija Pāvils, nomainiet to ar jaunu vārdu, piemēram, Pedro (tas nebija sākotnējā sarakstā):
Un mēs redzam, ka Excel automātiski ir atgriezis Pedro vecumu - pat ja mēs neesam mainījuši VLOOKUP formulas. Tā vietā dinamiskā diapazona darbības joma ir palielinājusies, iekļaujot papildu nosaukumus.
Dinamiskie diapazoni ir ļoti noderīgi, ja mums ir arvien vairāk datu, jo īpaši, ja ir nepieciešamas tabulas VLOOKUP un PIVOT.
