Ứng dụng excel trong tính toán thiết kế đường - Chương 2 (tiếp theo)

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...
Nội dung trích xuất từ tài liệu:
Ứng dụng excel trong tính toán thiết kế đường - Chương 2 (tiếp theo) 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Òmnh− 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¸olµ 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étmÊ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íihµm MATCH th× lËp tøc Excel sÏ tù ®éng dß t×m Eyc theo 2 th«ng sè : Lo¹i mÆt ®−êngng−ê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 40 -- §è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Ómkh¸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ôcxe 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ªucÇ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, 12tÊ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 41 - 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 4 ...