VinaOnline.net

Sử dụng hàm MATCH và hàm INDEX : Minh họa chi tiết

Sử dụng hàm MATCH và hàm INDEX thực chất là thay thế hàm VLOOKP/HLOOKUP và hàm IF. Hai hàm này kết hợp sẽ rất tiện lợi trong một số trường hợp tính toán. Vinaonline.net cùng bạn khảo sát chi tiết cách sử dụng hàm MATCH và hàm INDEX trong Excel.

I. Sử dụng hàm MATCH và hàm INDEX : MATCH

– Công dụng: Trả về vị trí cột hoặc vị trí hàng.

1. Cú pháp

MATCH(lookup_value, lookup_array, [match_type])

* lookup_value (bắt buộc) : Giá trị cần tìm kiếm kiếm (số, chuỗi, địa chỉ ô,…).

* lookup_array (bắt buộc) : Gồm một cột hoặc một hàng.

* [match_type] (không bắt buộc) : Có 3 tùy chọn.

1 hoặc bỏ đối số này : Hàm MATCH tìm giá trị lớn nhấtnhỏ hơn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm (lookup_array) phải đặt theo thứ tự TĂNG dần.

0 : Hàm MATCH tìm giá trị chính xác giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm không nhất thiết theo tứ tự.

-1 : Hàm MATCH tìm giá trị nhỏ nhấtlớn hoặc bằng giá trị cần tìm kiếm. Giá trị trong bảng tìm kiếm phải đặt theo thứ tự GIẢM dần.

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Minh họa

2. Ví dụ

MATCH(“A”,B1:E1,0) = 1

MATCH(“A”,B1:E1,1) = 1

Tìm thấy “A” tại vị trí (cột) 1 trong bảng tìm kiếm.

MATCH(“E“,B1:E1,0) = #N/A

MATCH(“E“,B1:E1,1) = 4

Tìm giá trị lớn nhất gần với E

MATCH(“E“,B1:E1,-1) = #N/A

Chú ý:

Thông thường sử dụng đối số [match_type] là 0. Các tùy chọn khác rất mơ hồ, ít sử dụng trong thực tế.

II. Sử dụng hàm MATCH và hàm INDEX : INDEX

– Công dụng : Dò tìm trong bảng và trả về giá trị là giao của HÀNG và CỘT.

1. Cú pháp

INDEX(array, row_num, [column_num])

* array (bắt buộc) : Là một bảng gồm:

– Nhiều hàng, nhiều cột.

– Một hàng, nhiều cột.

– Nhiều hàng, một cột.

* row_num : Chỉ số hàng, từ 1, 2, …, n. Nếu array chỉ có một hàng, có thể bỏ qua đối số này.

* column_num : Chỉ số cột, từ 1, 2, …, n. Nếu array chỉ có một cột, có thể bỏ qua đối số này.

2. Ví dụ

INDEX(B1:E1,3) = C

array chỉ có một hàng (B1:E1), row_num không cần thiết. 3 là column_num.

INDEX(A1:A5,5) = T

array chỉ có một cột (A1:A5), 5 là row_num. column_num không cần thiết.

INDEX(A1:E5,2,4) = 500; hàng 2, cột 4

INDEX(A1:E5,4,2) = 40; hàng 4, cột 2

III. Sử dụng hàm MATCH và hàm INDEX : Áp dụng

Dựa vào bảng đã cho, hãy điền SỐ LƯỢNG

Sử dụng hàm MATCH và hàm INDEX - Minh họa

Sử dụng hàm MATCH và hàm INDEX – Áp dụng

Phân tích yêu cầu:

Đối với yêu cầu này, bạn có thể sử dụng hàm VLOOKUP / HLOOKUP kết hợp với hàm IF sẽ đáp ứng được. Vấn đề đặt ra, nếu có nhiều cột / hàng thì sao? Vì vậy tốt nhất là sử dụng hàm INDEX và kết hợp với hàm MATCH để lấy chỉ số hàng / cột trong hàm INDEX.

– Mảng / Bảng dò tìm (bảng giá trị – array) :

+ Trường hợp 1: chọn từ B2:E5, tại ô B2 (có giá trị 20) là hàng 1, cột 1.

+ Trường hợp 2: chọn từ A1:E5, tại ô B2 (có giá trị 20) là hàng 2, cột 2.

Tùy theo đối số array (bảng dò tìm) trong hàm INDEX mà sử dụng hàm MATCH cho hợp lý.

– Đối số hàng (row_num) : Là một cột (A2:A5 – Trường hợp 1), (A1:A5 – Trường hợp 2).

– Đối số cột (column_num) : Là một hàng (A2:E5 – Trường hợp 1), (A1:E5 – Trường hợp 2).

+ Trường hợp 1:

Trả về chỉ số hàng: MATCH(RIGTH(A8,1),$A$2:$A$5,0)

RIGHT(A8,1) để được 1 ký tự phải (V, G, H, T)

Trả về chỉ số cột: MATCH(LEFT(A8,1),$B$1:$E$1,0)

LEFT(A8,1) để được 1 ký tự trái (A, B, C, D)

+ Trường hợp 2: Tương tự (xem nội dung hàm INDEX)

Kết quả:

+ 1:

=INDEX($B$2:$E$5, MATCH(RIGHT(A8,1),$A$2:$A$5,0), MATCH(LEFT(A8,1),$B$1:$E$1,1))

+ 2:

=INDEX($A$1:$E$5, MATCH(RIGHT(A8,1),$A$1:$A$5,0), MATCH(LEFT(A8,1),$A$1:$E$1,1))

Kết luận

Sử dụng hàm MATCH và hàm INDEX ít gặp trong những bài toán thông thường. Nhưng rất hữu ích nếu gặp những yêu cầu tham chiếu dạng bảng. Bạn có thể tham khảo thêm các hàm dò tìm hoặc bài tập củng cố để rèn kỹ năng phân tích và giải quyết một bài toán Excel. Chúc bạn thành công.

Bài tập số lần lặp không xác định : Minh họa chi tiết

Bài tập số lần lặp không xác định nhằm củng cố kiến thức vòng lặp While … do. Bạn nên tham khảo các bài viết trước để có kiến thức về lặp trình pascal. Bài tập số lần lặp không xác định sẽ bổ sung hai bài toán được ứng dụng trong vòng lặp While … do.

I. Bài tập số lần lặp không xác định : Tìm ước số chung lớn nhất

Ý tưởng thực hiện

– Nếu a=b thì UCLN(a,b) = a

– Khi a <> b

* Nếu a>b thì a = a-b

* Ngược lại, thì b = b-a

Ví dụ

– a = 5, b = 10

=> UCLN(5,10) = UCLN(5,5) = 5

– a = 5, b = 13

=> UCLN(a,b) = UCLN(5,8) = UCLN(5,3) = UCLN(2,3) = UCLN(2,1) = UCLN(1,1) = 1

 Program ucln;

Uses crt;

Var a,b:integer;

BEGIN

ClrScr;

Write(‘Nhap a: ‘);Readln(a);

Write(‘Nhap b: ‘);Readln(b);

While a<>b do

If a>b Then

a:=a-b

Else

b:=b-a;

Write(‘Uoc chung lon nhat cua a va b: ‘,a);

Readln;

END.

II. Bài tập số lần lặp không xác định : Đổi số thập phân thành nhị phân

Bài tập số lần lặp không xác định : Đổi số thập phân thành nhị phân

Ý tưởng thực hiện

Thực hiện phép chia số thập phân cho 2.

– Thu được kết quả của phép chia (phần nguyên). Nếu kết quả chưa bằng 0 thì tiếp tục lấy kết quả chia cho 2.

– Số dư của phép chia chính là dãy nhị phân cần tìm (lấy ngược từ cuối về đầu)

Giải thích hình minh họa

13 chia 2 được 6, dư 1

6 chia 2 được 3, dư 0

3 chia 2 được 1, dư 1

1 chia 2 được 0, dư 1

Hàm / phép tính sử dụng trong chương trình

mod: Lấy phần dư của phép chia.

Ví dụ:

(6 mod 2) = 0; (6 mod 4) = 2

div: Lấy phần nguyên của phép chia.

Ví dụ:

(6 div 2) = 3; (6 div 4) = 1

STR(number, s): Đổi số number thành chuỗi và gán vào s.

Ví dụ:

STR(10,s): Đổi số 10 thành chuỗi ’10’ và gán vào biến s; s lúc này chứa giá trị ’10’.

– CONCAT(s1,s2): Ghép hai chuỗi.

Ví dụ:

s1=’ab’

s2=’CD’

CONCAT(s1,s2) = ‘abCD’

CONCAT(s2,s1) = ‘CDab’

Chú ý:

– Bạn nên quan tâm thứ tự chuỗi trong hàm CONCAT. CONCAT(s1,s2) <> CONCAT(s2,s1).

Program NhiPhan;

Uses Crt;

Var n: Byte;

t,s: String;

BEGIN

ClrScr;

Write(‘Nhap so: ‘); Readln(n);

s:=”;

While n>0 do

Begin

STR((n mod 2),t);    // Đổi số dư thành chuỗi và gán vào biến

s:=CONCAT(t,s);    // Gán biến t vào s (tương đương s:= t+s; chú ý t đứng trước s)

n:= (n div 2);      // Tiếp tục thực hiện phép chia.

End;

Writeln(‘Day nhi phan: ‘,s);

Readln;

END.

Chú ý:

– Phần dư của phép chia sẽ đổi thành chuỗi để thực hiện ghép chuỗi. Nếu không thực hiện công việc này sẽ xảy ra: 1 + 0 = 1; 1 + 1 = 2.

Lời kết

Khi bạn có nền tảng về lập trình pascal thì bài viết này nhằm nhấn mạnh sự khác biệt giữa hai vòng lặp While … do và For. Chúc bạn trở thành cao thủ lập trình.