Nhóm hàm tìm kiếm INDEX và MATCH:
Hàm Index: Là hàm trả về giá trị của một ô hoặc một mảng dữ liệu trong mảng dữ liệu gốc.
Cú Pháp: INDEX(Array,Row_num,[Column_num])
- Array: Phạm vi ô hoặc một hằng số mảng, bắt buộc
- Row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị
- Column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.
Hàm Match: Là hàm trả về vị trí của một giá trị trong mảng một chiều
Cú Pháp: MATCH(Lookup_value,Lookup_array,[Match_type])
- Lookup_value: Giá trị tìm kiếm,có thể là một giá trị số, văn bản, giá trị logic hay một tham chiếu ô đến một số, văn bản hay giá trị logic, bắt buộc
- Lookup_array: Mảng để tìm kiếm, bắt buộc
- Match_type: Kiểu tìm kiếm. Không bắt buộc.
Bài toán:
1. Lọc ra đơn giá thuê dựa vào Bảng giá thuê phòng
2. Tính thành tiền dựa vào đơn giá thuê và số ngày ở
Bài toán được thể hiện cụ thể trong hình ảnh dưới đây:
Bài 1: Lọc ra đơn giá thuê dựa vào Bảng giá thuê phòng
Các bước giải bài toán:
Bước 1: Dùng hàm INDEX để lấy ra đơn giá thuê trong "bảng giá thuê phòng", ta mặc định đối số thứ 2 và đối số thứ 3 trong hàm INDEX dựa vào dữ liệu thực tế. Vùng dữ liệu sẽ là: $D$15:$F$17, khi đó dựa vào dữ liệu trong "bảng giá thuê phòng" để xác định đối số thứ hai và đối số thứ ba của hàm INDEX. Đối số thứ hai là chỉ số dòng dựa vào "loại", do "loại" là A nên sẽ lấy tại dòng đầu tiên, trong đó chỉ số cột sẽ dựa vào số tầng, số tầng là 1 nên cột lấy dữ liệu sẽ là cột một (các bạn nhìn vào "bảng giá thuê phòng" để xác định vị trí theo dữ liệu thực tế).
=INDEX($D$15:$F$17,1,1)
Bước 2: Dùng hàm MATCH để xác định chỉ số dòng cần lấy dựa vào "loại":
Tại đây chúng ta sẽ dùng hàm MATCH để tìm vị trí của "loại" trong "bảng giá thuê phòng". Mục đích ở đây sẽ lấy ra vị trí dòng chứa "loại". Theo bài toán thì A là dòng 1, B là dòng 2, C là dòng 3. Khi đó chúng ta sẽ dùng hàm MATCH để lấy ra vị trí trong mảng như sau: =MATCH(LEFT(C5,1),$C$15:$C$17,0). hàm LEFT(C5,1) chính là lấy ra "loại" trong "số phòng".
Bước 3: Xác định chỉ số cột cần lấy
Ở đây, chúng ta cần xác minh vị trí cột cần lấy trong mảng dữ liệu dựa vào chỉ số tầng.
Chú ý: nếu chúng ta dùng hàm RIGHT(C5,1) thì sẽ lấy ra giá trị "1" nhưng ở đây sẽ là văn bản, mà ở bảng giá thuê phòng là con số, vậy chúng ta cần dùng một hàm để chuyển "1" về dạng số đó là hàm "VALUE" => kết quả là VALUE(RIGHT(C5,1).
Từ đây chúng ta dùng hàm MATCH để tìm vị trí tầng như sau: =MATCH(VALUE(RIGHT(C5,1)),$D$14:$F$14,0)
Bước 4: Khi chúng ta đã hoàn thành việc tìm kiếm vị trí dòng, vị trí cột. Các bạn copy các hàm MATCH để thay thế vào chỉ số dòng, chỉ số cột mà chúng ta đang mặc định sẵn là 1,1 trong hàm INDEX:
=INDEX($D$15:$F$17,MATCH(LEFT(C5,1),$C$15:$C$17,0),MATCH(VALUE(RIGHT(C5,1)),$D$14:$F$14,0))
Bước 5: chúng ta copy công thức xuống cho các ô còn lại và đối chiếu kết quả thực kế ở một số ô để kiểm tra.
Bài 2: Tính thành tiền dựa vào đơn giá thuê và số ngày ở
Bài này đơn giản nên mình cho công thức thực tế, chỉ cần lấy "số ngày ở" nhân với "đơn giá thuê": F5*E5
Sau khi hoàn thành 2 bài toán ta có bảng dữ liệu như sau:
Để tìm hiểu thêm nhiều kiến thức bổ ích, các bạn tham khảo khóa học Excel nâng cao của công ty.
Link khóa học