Bendroji formulė
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Santrauka
Vienas iš būdų sukurti dinaminį pavadintą diapazoną su formule yra naudoti funkciją OFFSET kartu su funkcija COUNTA. Dinaminiai diapazonai taip pat žinomi kaip plečiantys diapazonai - jie automatiškai plečiasi ir susitraukia, kad sutalpintų naujus ar ištrintus duomenis.
Pastaba: OFFSET yra nepastovi funkcija, o tai reiškia, kad ji perskaičiuojama kiekvieną kartą pakeitus darbalapį. Naudojant modernią mašiną ir mažesnį duomenų rinkinį, tai neturėtų sukelti problemų, tačiau galite pastebėti lėtesnį didelių duomenų rinkinių veikimą. Tokiu atveju apsvarstykite galimybę sukurti dinaminį pavadintą diapazoną su funkcija INDEX.
Parodytame pavyzdyje dinaminiam diapazonui naudojama formulė yra:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Paaiškinimas
Ši formulė naudoja funkciją OFFSET, kad būtų sukurtas diapazonas, kuris plečiasi ir susitraukia koreguodamas aukštį ir plotį pagal ne tuščių langelių skaičių.
Pirmasis OFFSET argumentas reiškia pirmąją duomenų ląstelę (kilmę), kuri šiuo atveju yra langelis B5. Kiti du argumentai yra eilučių ir stulpelių poslinkiai ir pateikiami kaip nulis.
Paskutiniai du argumentai nurodo aukštį ir plotį. Aukštis ir plotis sugeneruojami naudojant COUNTA, o tai daro gautą atskaitą dinamišką.
Norėdami nustatyti aukštį, mes naudojame funkciją COUNTA, kad suskaičiuotume ne tuščias reikšmes diapazone B5: B100. Tai daro prielaidą, kad nėra tuščių reikšmių duomenyse ir jokių verčių, viršijančių B100. COUNTA pateikia 6.
Pločiui mes naudojame funkciją COUNTA, kad skaičiuotume tuščias reikšmes diapazone B5: Z5. Tai daro prielaidą, kad nėra antraštės langelių ir jokių antraščių, esančių už Z5. COUNTA pateikia 6.
Šiuo metu formulė atrodo taip:
=OFFSET(B5,0,0,6,6)
Turėdamas šią informaciją, OFFSET grąžina nuorodą į B5: G10, kuri atitinka 6 eilučių aukščio ir 6 stulpelių skersmens diapazoną.
Pastaba: Naudojami aukščio ir pločio diapazonai turėtų būti koreguojami, kad atitiktų darbalapio išdėstymą.
Variacija su visomis stulpelių / eilučių nuorodomis
Taip pat galite naudoti visas stulpelių ir eilučių nuorodas į aukštį ir plotį taip:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Atkreipkite dėmesį, kad aukštis koreguojamas -2, atsižvelgiant į B4 ir B2 langelių antraštės ir pavadinimo vertes. Šio požiūrio pranašumas yra diapazonų paprastumas COUNTA viduje. Trūkumas yra didžiulio dydžio stulpeliai ir eilės - reikia pasirūpinti, kad būtų išvengta klaidingų verčių už diapazono ribų, nes jos gali lengvai išmesti skaičių.
Paskutinės eilės nustatymas
Yra keli būdai, kaip nustatyti paskutinę duomenų rinkinio eilutę (paskutinę santykinę padėtį), atsižvelgiant į darbalapio duomenų struktūrą ir turinį:
- Paskutinė eilutė mišriuose duomenyse su tuščiais
- Paskutinė mišrių duomenų eilutė be tuščių
- Paskutinė teksto duomenų eilutė
- Paskutinė skaitinių duomenų eilutė
