Bµi gi¶ng EXCEL - GV Th.S NguyÔn Biªn C−¬ng - Trang 40 -
5.9. Hµm INDEX :
5.9.1. ý nghÜa cña hµm :
Tr¶ vÒ gi¸ trÞ cña 1 phÇn tö trong 1 b¶ng ( hoÆc 1 m¶ng 2 chiÒu ) th«ng qua chØ
sè hµng & chØ sè cét cña phÇn tö ®ã.
5.9.2. Có ph¸p cña hµm :
Hµm nµy cã 2 lo¹i có ph¸p, lo¹i có ph¸p thø nhÊt :
INDEX(array;row_num;column_num)
- §èi sè thø nhÊt : array – m¶ng 2 chiÒu, th−êng ®−îc khai b¸o d−íi d¹ng 1
vïng « tÝnh liªn tôc cã n hµng & m cét. Khi vïng « ®−îc khai b¸o, phÇn tö ë
gãc trªn cïng bªn tr¸i cña vïng sÏ ®−îc hiÓu lµ phÇn tö ë hµng 1- cét 1 ®Ó tõ ®ã
x¸c ®Þnh vÞ trÝ hµng & cét cña c¸c phÇn tö cßn l¹i;
- §èi sè thø hai : row_num – chØ sè hµng cña phÇn tö cÇn truy suÊt gi¸ trÞ –
ph¶i lµ 1 sè nguyªn d−¬ng;
- §èi sè thø ba : column_num – chØ sè cét cña phÇn tö cÇn truy suÊt gi¸ trÞ –
ph¶i lµ 1 sè nguyªn d−¬ng;
5.9.3. øng dông cña hµm & c¸c vÝ dô minh ho¹ :
VÝ dô 2.6 : NhËp b¶ng tra trÞ sè m«®un ®µn håi yªu cÇu cña kÕt cÊu mÆt ®−êng mÒm
nh− h×nh d−íi;
C«ng thøc trong « tÝnh B11 : =INDEX(A4:J7;2;4) cho gi¸ trÞ b»ng 1470. Lý do :
m¶ng dß t×m A4:J7 sÏ cã phÇn tö ë hµng 1 – cét 1 lµ « tÝnh A4; Hµng dß t×m khai b¸o
lµ 2, cét dß t×m khai b¸o lµ 4 v× vËy phÇn tö cÇn t×m ë hµng 2 cét 4 cña m¶ng chÝnh lµ «
tÝnh D5 cña trang tÝnh vµcã gi¸ trÞ b»ng 1470.
C¸ch lµm nµy kh«ng hay v× ng−êi dïng ph¶i chØ ra râ phÇn tö ë hµng mÊy, cét
mÊy – ch¼ng kh¸c nµo c¸ch tra b¶ng b»ng tay th«ng th−êng. Song nÕu kÕt hîp víi
hµm MATCH th× lËp tøc Excel sÏ tù ®éng dß t×m Eyc theo 2 th«ng sè : Lo¹i mÆt ®−êng
ng−êi dïng nhËp ë « tÝnh B9, L−u l−îng trôc xe tÝnh to¸n ë « tÝnh B10. C¸ch lµm nh−
sau :
NhËp vµo « tÝnh B12 c«ng thøc :
=INDEX(A4:J7;MATCH(B9;A4:A7;0); MATCH(B10;A4:J4;0))
C«ng thøc nµy còng tr¶ gi¸ trÞ lµ 1470. Lý do :
- §èi sè thø 2 cña hµm INDEX trªn lµ MATCH(B10;A4:A7;0) – hµm nµy t×m B9
cã gi¸ trÞ lµ chuçi “A1” trong d·y A4:A7 v× vËy nã tr¶ gi¸ trÞ b»ng 2 ( phÇn tö thø 2
trong d·y A4:A7 b»ng ®óng gi¸ trÞ dß t×m lµ chuçi “A1”;
Bµi gi¶ng EXCEL - GV Th.S NguyÔn Biªn C−¬ng - Trang 41 -
- §èi sè thø 3 cña hµm INDEX trªn lµ MATCH(B10;A4:J4;0) – hµm nµy t×m B10
cã gi¸ trÞ lµ 50 trong d·y A4:J4 v× vËy nã tr¶ gi¸ trÞ b»ng 3 ( phÇn tö thø 3 trong d·y
A4:J4 b»ng ®óng gi¸ trÞ dß t×m lµ 50;
V× vËy, gi¸ trÞ hµm INDEX tr¶ vÒ còng gièng nh− gi¸ trÞ trong « tÝnh B11; §iÓm
kh¸c biÖt lµ ë chç : nÕu ng−êi dïng thay ®æi “Lo¹i mÆt ®−êng” hoÆc “L−u l−îng trôc
xe tÝnh to¸n” ë 2 « tÝnh B9 & B10 th× lËp tøc hµm sÏ tù ®éng tra l¹i M«®un ®µn håi yªu
cÇu theo b¶ng & ®iÒn gi¸ trÞ vµo « tÝnh B12.
VÝ dô 2.7 : Chóng ta nhËn thÊy : nÕu ng−êi dïng nhËp vµo « tÝnh B10 mét gi¸ trÞ
kh«ng ph¶i lµ: 10, 20, 50, 100 . . . th× Excel sÏ b¸o lçi N/A, v× lý do hµm MATCH thø
2 trong hµm INDEX kh«ng t×m thÊy gi¸ trÞ nµo trong d·y A4:J4 cã gi¸ trÞ b»ng gi¸ trÞ
dß t×m (« B10). MÆt kh¸c, khi L−u l−îng trôc xe tÝnh to¸n khi quy ®æi th−êng lµ 1 sè
lÎ, v× vËy ph¶i tra trong b¶ng 2 gi¸ trÞ Eyc trªn & d−íi råi tiÕn hµnh néi suy ®Ó cã gi¸
trÞ Eyc. §Ó lµm ®−îc ®iÒu nµy ph¶i sö dông ®èi sè thø 3 cña hµm MATCH -
match_type.
Sö dông l¹i b¶ng tÝnh ë trªn :
- NhËp l−u l−îng trôc xe tÝnh to¸n lµ 40 trong « B10;
- Trong « tÝnh B11 nhËp c«ng thøc tra Eyc kÒ d−íi :
=INDEX(A4:J7;MATCH(B9;A4:A7;0);MATCH(B10;A4:J4;1))
- Trong « tÝnh B12 nhËp c«ng thøc tra Eyc kÒ trªn :
=INDEX(A4:J7;MATCH(B9;A4:A7;0);MATCH(B10;A4:J4;1)+1)
- Trong « tÝnh B13 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ d−íi :
=INDEX(A4:J4;1;MATCH(B10;A4:J4;1))
- Trong « tÝnh B14 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ trªn :
=INDEX(A4:J4;1;MATCH(B10;A4:J4;1)+1)
- Trong « tÝnh B14 nhËp c«ng thøc tÝnh Eyc ®· néi suy tõ b¶ng tra :
=B11+(B12-B11)/(B14-B13)*(B10-B13)
Gi¸ trÞ Eyc trong « tÝnh B14 chÝnh lµ gi¸ trÞ cÇn t×m.
Thùc chÊt b¶ng tra Eyc cßn phô thuéc vµo t¶i träng trôc tÝnh to¸n lµ 10 tÊn, 12
tÊn hay 9,5 tÊn nh− h×nh trang bªn; V× vËy, c¸ch gi¶i quyÕt nh− trªn vÉn ch−a triÖt
®Ó.Lóc nµy cã thÓ ph¶i dïng lo¹i có ph¸p thø 2 cña hµm INDEX.
Bµi gi¶ng EXCEL - GV Th.S NguyÔn Biªn C−¬ng - Trang 42 -
Lo¹i có ph¸p thø hai cña hµm INDEX:
INDEX (reference; row_num; column_num; area_num)
- §èi sè thø nhÊt : reference – vïng tham chiÕu, cã thÓ chøa 1 vïng « tÝnh liªn tôc
hoÆc nhiÒu vïng «. VD : B4:K7 hoÆc ( B4:K7;B8:K11;B12:K15) – lóc nµy B4:K7
lµ vïng « sè 1; B8:K11 lµ vïng « sè 2;B12:K15 lµ vïng « sè 3;
- §èi sè thø hai : row_num – chØ sè hµng cña phÇn tö cÇn truy suÊt gi¸ trÞ – ph¶i lµ
1 sè nguyªn d−¬ng;
- §èi sè thø ba : column_num – chØ sè cét cña phÇn tö cÇn truy suÊt gi¸ trÞ – ph¶i
lµ 1 sè nguyªn d−¬ng;
- §èi sè thø t− : area_num – sè cña vïng « sÏ truy suÊt gi¸ trÞ trong reference.
VÝ dô 2.8 : NhËp b¶ng tÝnh nh− h×nh d−íi :
Bµi gi¶ng EXCEL - GV Th.S NguyÔn Biªn C−¬ng - Trang 43 -
- NhËp d÷ liÖu vµo c¸c « tÝnh B14:B16;
- §Æt tªn cho vïng « B5:K7, B8:K10; B11:K13; B5:B7; B4:K4 lÇn l−ît lµ EDH10,
EDH12, EDH9.5, COT1, HANG1.
- Trong « tÝnh B17 nhËp c«ng thøc tra Eyc kÒ d−íi :
=INDEX((EDH10;EDH12;EDH9.5);MATCH(B15;COT1;0);MATCH(B16;HANG1;1)
;IF(B14=10;1;IF(B14=12;2;3)))
ë ®©y, reference – vïng tham chiÕu, chøa 3 vïng « tÝnh liªn tôc
(EDH10;EDH12;EDH9.5)– lóc nµy EDH10 lµ vïng « sè 1; EDH12 lµ vïng « sè 2;
EDH9.5 lµ vïng « sè 3. §èi sè thø 4 cña hµm INDEX - area_num lµ 1 hµm IF sÏ lùa
chän viÖc tham chiÕu vïng « nµo phô thuéc vµo « tÝnh B16 – t¶i träng trôc tÝnh to¸n;
- Trong « tÝnh B18 nhËp c«ng thøc tra Eyc kÒ trªn :
=INDEX((EDH10;EDH12;EDH9.5);MATCH(B15;COT1;0);MATCH(B16;HANG1;1)
+1;IF(B14=10;1;IF(B14=12;2;3)))
- Trong « tÝnh B19 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ d−íi :
=INDEX(HANG1;1;MATCH(B16;HANG1;1))
- Trong « tÝnh B20 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ trªn :
=INDEX(HANG1;1;MATCH(B16;HANG1;1)+1)
- Trong « tÝnh B21 nhËp c«ng thøc tÝnh Eyc ®· néi suy tõ b¶ng tra :
=B17+(B18-B17)/(B20-B19)*(B16-B19)
NhËn xÐt : c¸ch gi¶i quyÕt trªn còng ch−a thËt triÖt ®Ó, v× nÕu sè lo¹i t¶i träng trôc xe
lín h¬n 8 th× hµm IF kh«ng thÓ ph©n lo¹i vïng tham chiÕu ®−îc n÷a. Lóc nµy ph¶i sö
dông c¸ch lµm kh¸c nh− sau :
- Chän trang tÝnh võa lµm ë VÝ dô 2.7 , gäi Menu Edit
– Move or copy Sheet , trong hép tho¹i bËt hép
kiÓm tra Creat a Copy ®Ó t¹o ra 1 trang tÝnh míi,
gièng hÖt trang tÝnh nguån; chän vÞ trÝ trang tÝnh
trong sæ tÝnh trong môc Before sheet – OK sÏ cã
trang tÝnh míi nh− h×nh trang bªn;
- Söa l¹i néi dung trong c¸c « tÝnh ë vïng « B5:B13
gièng nh− h×nh trang bªn. C¸c th«ng tin 10A1, 10A2,
10B1 . . . b©y giê chøa th«ng tin kÐp ( võa x¸c ®Þnh
t¶i träng trôc tÝnh to¸n, võa x¸c ®Þnh lo¹i mÆt ®−êng).
- §Æt tªn cho vïng C5:K13, B5:B13, C4:K4 lÇn l−ît lµ
EDH, COT, HANG;
- Trong « tÝnh B17 nhËp c«ng thøc tra Eyc kÒ d−íi :
=INDEX(EDH;MATCH(B14&B15;COT;0);MATCH(B16;HANG;1))
ë ®©y, gi¸ trÞ t×m kiÕm trong hµm MATCH sö dông to¸n tö nèi ®Ó ghÐp 2 th«ng tin
nµy (B14&B15).
- Trong « tÝnh B18 nhËp c«ng thøc tra Eyc kÒ trªn :
=INDEX(EDH;MATCH(B14&B15;COT;0);MATCH(B16;HANG;1)+1)
- Trong « tÝnh B19 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ d−íi :
=INDEX(HANG;1;MATCH(B16;HANG;1))
- Trong « tÝnh B20 nhËp c«ng thøc tra L−u l−îng trôc xe kÒ trªn :
=INDEX(HANG;1;MATCH(B16;HANG;1)+1)
- Trong « tÝnh B21 nhËp c«ng thøc tÝnh Eyc ®· néi suy tõ b¶ng tra :
=B17+(B18-B17)/(B20-B19)*(B16-B19)
Bµi gi¶ng EXCEL - GV Th.S NguyÔn Biªn C−¬ng - Trang 44 -
B¶ng tra lo¹i nµy cßn ®−îc gäi lµ b¶ng 3 chiÒu; Còng cã thÓ dïng c¸ch trªn ®Ó
thiÕt lËp c¸ch tù ®éng tra b¶ng cña c¸c b¶ng nhiÒu chiÒu h¬n.
Bµi tËp 2.5 : LËp b¶ng tÝnh tù ®éng x¸c ®Þnh hÖ sè dßng ch¶y lò α biÕt b¶ng tra hÖ sè
nµy nh− h×nh d−íi :