Exceli valem: dünaamiline nimega vahemik koos OFFSET -

Üldine valem

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Kokkuvõte

Üks võimalus dünaamilise nimelise vahemiku loomiseks valemiga on funktsioon OFFSET koos funktsiooniga COUNTA. Dünaamilisi vahemikke nimetatakse ka laienevateks vahemikeks - need laienevad ja tõmbuvad automaatselt kokku uute või kustutatud andmete mahutamiseks.

Märkus: OFFSET on kõikuv funktsioon, mis tähendab, et see arvutab töölehe iga muudatuse korral uuesti. Kaasaegse masina ja väiksema andmekogumiga ei tohiks see probleemi tekitada, kuid suurte andmekogumite toimivus võib olla aeglasem. Sel juhul kaaluge funktsiooni INDEX abil dünaamilise nimega vahemiku ehitamist.

Näidatud näites on dünaamilise vahemiku jaoks kasutatav valem järgmine:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Selgitus

See valem kasutab funktsiooni OFFSET laieneva ja kokkutõmbuva vahemiku loomiseks, reguleerides kõrgust ja laiust mittetühjade lahtrite arvu põhjal.

OFFSETi esimene argument tähistab andmete esimest lahtrit (päritolu), milleks on antud juhul lahter B5. Järgmised kaks argumenti on ridade ja veergude nihked ning esitatakse nullina.

Kaks viimast argumenti tähistavad kõrgust ja laiust. Kõrgus ja laius luuakse käigu pealt, kasutades COUNTA, mis muudab saadud võrdlusdünaamika.

Kõrguse jaoks loeme vahemikus B5: B100 mittetühjade väärtuste lugemiseks funktsiooni COUNTA. See eeldab, et andmetes pole tühje väärtusi ega väärtusi, mis ületaksid väärtust B100. COUNTA tagastab 6.

Laiuse jaoks kasutame funktsiooni COUNTA, et loendada tühjad väärtused vahemikus B5: Z5. See eeldab, et pole päiserakke ega päisi, mis jäävad kaugemale Z5-st. COUNTA tagastab 6.

Siinkohal näeb valem välja selline:

=OFFSET(B5,0,0,6,6)

Selle teabega tagastab OFFSET viite B5: G10-le, mis vastab vahemikule 6 rea kõrgus 6 veeru ulatuses.

Märkus. Kõrguse ja laiuse vahemikke tuleks kohandada vastavalt töölehe paigutusele.

Variatsioon veergude / ridade täielike viidetega

Kõrguse ja laiuse jaoks saate kasutada ka täielikke veergude ja ridade viiteid:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Pange tähele, et kõrgust reguleeritakse -2-ga, et võtta arvesse lahtrite B4 ja B2 päise ja pealkirja väärtusi. Selle lähenemise eeliseks on vahemike lihtsus COUNTA-s. Puuduseks on tohutu suurusega täisveerud ja read - tuleb hoolitseda selle eest, et vältida valesid väärtusi vahemikust väljaspool, kuna need võivad loenduse hõlpsalt maha visata.