Cch s dng Hm Offset trong excelHmoffset: Tr v gi tr ca mt hoc mt vng cc cn c vo s ct, s dng, ln cc dng, cc ct c ch nh trcC php hm OFFSET c cc i s sau y:

OFFSET(reference,rows,cols,[height],[width])

Trong :

Reference:Vng tham chiu hay tp hp cc lin k nhau trong bng tnhexcelm bn mun tr v gi tr tham chiu. Vng tham chiu phi tham chiu ti mt hoc mt phm vi cc lin k; nu khng hm OFFSET tr v gi tr li #VALUE! .

Rows:Tham s ny bt buc. ch S hng k t referene cn phi dch ln trn nu l s m v dch xung di nu l s dng.

Cols:Tham s ny bt buc. Bt buc. S ct cn dch chuyn v bn tri reference nu cols= s m hoc dch chuyn sang phi nu l s dng.

Height:Ty chn. Chiucao, tnh bng s hng, m bn mun c cho tham chiu tr v. Chiu cao phi l s dng.

Width:Ty chn. rng, tnh bng s ct, m bn mun c cho tham chiu tr v. rng phi l s dng.Ghi ch

Nu i s hng v ct lm cho vng tham chiu tr v vt ra ngoi ng bin ca trang tnh, th hm OFFSET tr v gi tr li #REF! .

Nu i s chiu cao v rng c b qua, th n c gi nh c cng chiu cao v rng vi vng tham chiu.

Nu chngtach thc hin vic tham chiu v xut ra mt gi tr trong bng tnh th gi tr Height v Width lun lun phi bng 1 hoc b trng, nu khng hm s tr v gi tr li:#REF! .

Khi chng ta kt hp hm offset vi hm tnh ton khc nh Sum; Count, Counta th Height v Width mi c php mang gi tr ln hn 1.Ta xt v d sau

B1: ta t cng thc: =OFFSET(D1,B2,B3,B4,B5)

Reference = D1: D1 l mc t khi gi tr rows v cols xut hin hm Offset s dch chuyn ln trn, xung di, sang phi hoc sang tri D1 v tr v gi tr.Rows = B2 = 2Cols = B3 = 1Height = B4 = 1Width = B5 = 1

M t: T D1, dch chuyn xung 2 dng v dch chuyn sang phi 1 ct chnh l gi tr ca E3Vi cc gi tr trn th B1= E3="Small"

y chng ta t cc tham s vo cc ring bit ch khng nhp trc tip vo hm d thy s thay i ca n. Tuy nhin cc bn phi lu rng nu lm theo cch ny xut hin tham chiu th gi tr Height v Width phi bng 1; nu lng vo cc cng thc khc th c th khc 1.

By gi chng ta kt hp hm m counta s m xem c bao nhiu c tr v vi gi trRows = B2 = 2;Cols = B3 = 1;Height = B4 = 2;Width = B5 = 3B1=Counta(offset(D1,B2,B3,B4,B5)= 6

M t:t E3 (Nh trn) s qut xung 2 hng v qut sang phi 3 ct ta c vng d liuE3:G4; Nh vy gi tr tr v cahm offsetl mt mng cha cc lin k nhau:E3:G4 lc ny i s caHm CountAs l =counta(E3:G4)V tip theo Hm s m t E3:G4 tng cng c 6 trong vng d liu ny.

Bn hy copy cng thc v vng d liu ri dn vo A1 ca trang tnh v thc hnh nh

Fomulas=COUNTA(OFFSET(D1,B2,B3,B4,B5))12345

rows21bigredhorsepaperfish

cols12smallbrowngiraffeplasticsquid

height23tallyellowzebraironstar fish

width34shortgreenbeecopperprawn

5highbluebuterflygoldturtles

6loworangefliessilvershark

7fastpurplerinodiamonwhale

8slowwhitesealleaddolphin

Chc cc bn thnh cng