Cách sử dụng hàm INDEX 3 và 4 tham số

 Hàm INDEX là 1 hàm tìm kiếm khá phổ biến trong Excel, khi gõ hàm INDEX thì Excel sẽ có 2 dòng gợi ý

và hàm INDEX theo cấu trúc đầu tiên =INDEX(array,row_num,column_num) được sử dụng phổ biến để lấy giá trị trong mảng (array) ở dòng nào (row_num) và cột nào (column_num). 

Vậy hàm INDEX theo cấu trúc thứ 2 là =INDEX(reference,row_num,column_num,area_num) được sử dụng như thế nào? hàm INDEX này có 4 tham số

CÁCH SỬ DỤNG HÀM INDEX 4 THAM SỐ TRONG EXCEL

Ví dụ tình huống: Mỗi tháng có một bảng giá riêng (bảng giá các tháng 1, 2, 3 lần lượt từ H3:I5, K3:L5, N3:O5

và yêu cầu tìm ra giá theo tháng

Khi đó công thức dùng hàm INDEX với 4 tham số là

=INDEX(($H$3:$I$5,$K$3:$L$5,$N$3:$O$5),MATCH(B3,$H$3:$H$5,0),2,MONTH(A3))

Trong đó:

- ($H$3:$I$5,$K$3:$L$5,$N$3:$O$5) - reference là các vùng bảng giá của từng tháng

- MATCH(B3,$H$3:$H$5,0)  - row_num để tìm kiếm sản phẩm phát sinh ở dòng nào

- 2 - column_num là giá lấy ở cột số 2 của từng bảng

- MONTH(A3) - area_num là tháng nào. Tham số này chính là lấy tròng vùng thứ mấy của reference

Như vậy, đặc điểm khác biệt giữa hàm INDEX 3 và 4 tham số là:

- Hàm INDEX 3 tham số thì trỏ vào 1 vùng hoặc 1 mảng và lấy ra giá trị thuộc dòng thứ mấy và cột thứ mấy của mảng

- Hàm INDEX 4 tham số thì trỏ vào nhiều vùng và người dùng cần chỉ ra vùng lấy kết quả là thứ bao nhiêu, dòng thứ mấy và cột thứ mấy

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hàm Vlookup tìm kiếm theo ký tự đại diện Wildcard

 Trong Excel có những tình huống tìm kiếm theo 1 phần ký tự như sau:

- Người dùng nhập liệu danh sách vào 1 ô và chỉ tìm kiếm theo 1 phần tử trong danh sách đó (như hình ảnh dưới đâu)

- Tạo mã đối tượng, mã code,... nhưng khi tìm kiếm thì chỉ tìm kiếm theo 1 phần của mã đó chứ không dùng hết các ký tự của mã

- Do người dùng vô tình nhập thêm khoảng trống đằng trước hoặc đằng sau 

HƯỚNG DẪN TÌM KIẾM VLOOKUP THEO KÝ TỰ ĐẠI DIỆN WILDCARD VỚI DẤU *

Tình huống ví dụ: Cột Items là danh sách các mã

Yêu cầu bài toán: Tìm kiếm theo từng Items cụ thể để lấy được thông tin Part

Công thức ở cột F như sau:

=VLOOKUP("*"&E3&"*",$A$3:$C$8,2,0)

Trong đó: Dấu * được ghép ở 2 đầu với mục đích tìm kiếm dòng chứa giá trị ở ô E3. Sau đó kéo công thức cho các ô còn lại

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn xử lý hiển thị hàm VLOOKUP trả về kết quả là 0

 Hàm VLOOKUP là một trong những hàm phổ biến và được dùng nhiều nhất trong Excel. Nhưng khi sử dụng hàm VLOOKUP, nếu ô cần lấy ra ở bên dữ liệu nguồn mà để trống thì kết quả hàm VLOOKUP trả về là 0. Thậm trí, nếu cột kết quả mà là dạng ngày tháng Date mà format số 0 về dạng Short Date thì sẽ hiển thị là 00/01/1900 => trông sẽ rất xấu

Bài viết này sẽ HƯỚNG DẪN CÁCH XỬ LÝ HIỂN THỊ KẾT QUẢ HÀM VLOOKUP TRẢ VỀ LÀ 0

- Nếu kết quả lấy ra là Date thì thiết lập Format Cell => Custom là: dd/mm/yyyy;;;

- Nếu kết quả lấy ra là Text thì Format Cell => Custom là: ;;;@

- Nếu kết quả lấy ra là Time thì thiết lập Format Cell => Custom là: hh:mm;;; hoặc h:mm AM/PM;;;

- Nếu kết quả lấy ra là Date Time thì thiết lập Format Cell => Custom là: dd/mm/yyyy hh:mm;;;

- Nếu kết quả lấy ra là Number thì không cần thay đổi format cũng được. Vì kết quả lấy ra là 0 cũng là 1 con số rồi

Lưu ý: Các mẫu Format trên thì người dùng có thể tùy chỉnh thêm theo mong muốn

Videos hướng dẫn chi tiết các format hiển thị kết quả hàm Vlookup trả về là 0

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm XLOOKUP trong Excel 365

 Hàm XLOOKUP là 1 hàm tìm kiếm rất mạnh có trong Microsofts Exel 365 với nhiều tùy chọn tìm kiếm rất thông minh. 

Cấu trúc hàm XLOOKUP:

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

Các tham số XLOOKUP:

- lookup_value: Giá trị tìm kiếm 

- lookup_array: tìm kiếm ở mảng nào

- return_array: kết quả ở mảng nào

- if_not_found: nếu không tìm thấy thì trả về kết quả thay thế là gì (thay thế cho hàm IFERROR)

- match_mode: Tham số này có 4 lựa chọn

  * 0: Exact match: Tìm kiếm tuyệt đối chính xác

  * -1: Exact match or next smaller item: Tìm kiếm chính xác, nếu không tìm đươc thì sẽ lấy giá trị gần nhất mà nhỏ hơn

  * 1: Exact match or next larger item: Tìm kiếm chính xác, nếu không tìm được thì sẽ lấy giá trị gần nhất mà lớn hơn

  * 2: wildcard character match: Tìm kiếm kiểu dùng ký tự đại diện *, ?, ~

- search_mode: Tham số này có 4 tùy chọn

 * 1: Search fist to last: Tìm kiếm từ trên xuống dưới (từ trái sang phải)

 * -1: Search last to fist: Tím kiếm từ dưới lên trên (từ phải sang trái)

 * 2: Binary search (sorter accending order): Sắp xếp theo thứ tự tăng dần rồi tìm kiếm

 * -2: Binary search (sorter descending order): Sắp xếp theo thứ tự giảm dần rồi tìm kiếm

Ví dụ tình huống dùng hàm XLOOKUP

Giả sử giá trị tìm kiếm lookup_value là 5.5 thì ta có các công thức sau:

=XLOOKUP(5.5,B2:B8,C2:C8,"Kiểm tra lại nhé",-1,1)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Cách dùng hàm Vlookup biến đổi tham số thứ 3 - cột trả về

 Hàm Vlookup là một hàm rất phổ biến với người dùng Excel và với cấu trúc như sau:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Trong đó, tham số col_index_num là số thứ tự cột trả về (tính từ trái sang phải theo table_array). Nhưng trong nhiều tình huống, tham số col_index_num không phải cố định 1 cột như Bảng giá được tách cột. Ví dụ: Mỗi tháng 1 giá, mỗi khu vực 1 giá,...Khi đó, nhiều người sẽ nghĩ tới hàm IF hoặc hàm INDEX, MATCH để xử lý.

Bài viết này sẽ hướng dẫn cách dùng hàm VLOOKUP có sự biến đổi ở tham số thứ 3 theo tình huống như hình ảnh dưới đây

Diễn giải tình huống - Lập công thức tìm đơn giá . Trong đó:

- Bảng giá theo từng vật tư (Vùng từ A12:D17)

- Ký hiệu 1,2,3 của khu vực là 1 ký tự bên phải của mã khách hàng. Ví dụ: KH02-1 => Khách hàng thuộc khu vực 1

Lập luận logic bài toán: Số thứ tự của cột trả về là X

- Nếu khách hàng thuộc khu vực 1 thì lấy cột thứ 2 => X=2 hay X=1+1

- Nếu khách hàng thuộc khu vực 2 thì lấy cột thứ 3 => X=3 hay X=2+1

- Nếu khách hàng thuộc khu vực 3 thì lấy cột thứ 4 => X=3 hay X=3+1

=> Như vậy X=Mã số khu vực+1

=> Công thức tính số thứ tự cột kết quả trả về là: RIGHT(B3,1)+1

Kết hợp với hàm VLOOKUP thì ta có công thức:

=VLOOKUP(C3,$A$12:$D$17,RIGHT(B3,1)+1,0)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM TÌM KIẾM TRONG EXCEL (PHẦN 3) - HÀM OFFSET

 Hàm OFFSET là hàm tìm kiếm cao cấp trong Excel, gồm nhiều tham số và tư duy theo tọa độ dịch chuyển nên được xếp vào dạng hàm khó sử dụng. 

Để thuận tiện cho việc minh họa cách dùng thì các ví dụ trong bài viết sẽ được sử dụng theo mẫu số liệu như hình ảnh sau đây:

*** Cấu trúc hàm OFFSET: =OFFSET(reference,rows,cols,[height],[width])

*** Cách sử dụng hàm OFFSET: Hàm OFFSET là hàm tìm kiếm theo tọa độ dịch chuyển tính từ điểm đầu tiên của vùng hoặc ô xuất phát:

      - reference: Vùng hoặc ô bắt đầu

     - rows: số dòng tịnh tiến

     - cols: số cột tịnh tiến

     Ví dụ: =OFFSET(B4,1,4) thì kết quả trả về là 219

    - [height]: Số dòng cần lấy. Nếu không điền thì mặc định là lấy 1 dòng

    - [with]: số cột cần lấy. Nếu không điền thì mặc định là lấy 1 cột

    => Nếu không điền [height] và [with] thì lấy 1 dòng, 1 cột => tức kết quả trả về là giá trị 1 ô

    Ví dụ: =OFFSET(B4,5,2,1,3) thì kết quả trả về là 1 dòng 3 cột gồm 3 phần tử lấy ra là: 116, 117, 114. Nếu đặt hàm MIN bên ngoài là: =MIN(OFFSET(B4,5,2,1,3)) thì kết quả trả về là 114

*** Ứng dụng hàm OFFSET: Hàm OFFSET được sử dụng nhiều trong các bài toán tìm kiếm cao cấp, lấy ra một tập hợp các giá trị, tìm kiếm lệch dòng, tham chiều nhiều sheet...và thường được phối hợp với những hàm như MATCH, INDIRECT,ADDRESS, COUNTIF,...những công thức khi sử dụng các hàm này phối hợp cùng thì người lập công thức ở cấp độ tư duy logic khá cao

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM TÌM KIẾM TRONG EXCEL (PHẦN 2) - HÀM INDEX VÀ MATCH

 INDEX và MATCH là 2 hàm tìm kiếm được ứng dụng rất phổ biến trong thực tế. Khi sử dụng thì thường hai hàm này sẽ đi cùng với nhau.

Để thuận tiện cho việc mô tả ứng dụng của 2 hàm này thì các ví dụ trong bài viết này sẽ theo bảng ví dụ mẫu như hình ảnh dưới đây:

Hàm MATCH

   *** Cấu trúc: =MATCH(lookup_value,lookup_array,match_type)

   *** Cách dùng hàm MATCH: Hàm MATCH là hàm tìm kiếm trong mảng 1 chiều (chiều ngang hoặc chiều dọc) và kết quả trả về số thứ tự của phần tử tìm kiếm trong mảng. Ví dụ: =MATCH("SPE",A4:A9,0) thì được kết quả trả về là 5. Tức tìm từ trên xuống dưới thì hàm SPE ở vị trí thứ 5 trong mảng (vùng)

Hàm INDEX

   *** Cấu trúc: =INDEX(array,row_num,column_num)

   *** Cách dùng hàm INDEX: Hàm INDEX là hàm tìm kiếm trong mảng 1 chiều và 2 chiều, kết quả trả về có thể là 1 mảng hoặc 1 giá trị.

         Ví dụ: =INDEX(B4:F9,5,2) thì kết quả trả về 1 giá trị là: 207

         Ví dụ: =INDEX(B4:F9,,2) thì kết quả là 1 tập hợp giá trị ở cột thứ 2 của vùng là 293, 215, 260, 115, 203, 116. Khi đó, nếu dùng hàm SUM bên ngoài hàm INDEX là =SUM(INDEX(B4:F9,,2)) thì sẽ ra tổng các phần tử là 1203

         Ví dụ: =INDEX(B4:F9,3,) thì kết quả là 1 tập hợp giá trị ở dòng số 3 của gùng là 263, 260, 259, 255,255. Nếu dùng hàm MAX bên ngoài hàm INDEX là =MAX(INDEX(B4:F9,3,)) thì kết quả trả về là 263

Hàm INDEX kết hợp với hàm MATCH

   *** Cấu trúc: =INDEX(array,MATCH(lookup_value,lookup_array,match_type),MATCH(lookup_value,lookup_array,match_type))

   *** Cách sử dụng INDEX phối hợp MATCH: Do hàm MATCH trả về số thứ tự của phần tử tìm kiếm, hàm INDEX là dò tìm theo tọa độ dòng, cột của phần tử trong mảng. Đây chính là lý do mà 2 hàm này áp dụng hay đi cùng với nhau

        Ví dụ: =INDEX(B4:F9,MATCH("SPD",A4:A9,0),4) thì kết quả trả về là 112

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn tìm kiếm ngược từ phải sang trái trong Excel

 Trong Excel thì hàm VLOOKUP là hàm tìm kiếm thông dụng nhất mà đa số người dùng Excel đều biết. Cách thức hoạt động tìm kiếm của hàm VLOOKUP là tìm kiếm từ trái sang phải. Trong đó, cột đầu tiên là cột chứa giá trị tìm kiếm. Nhưng trong thực tế thì có nhiều trường hợp cần tìm kiếm ngược - tức tìm kiếm từ phải sang trái => Đồng nghĩa là cột chứa giá trị tìm kiếm ở bên phải, cột chứa giá trị trả về ở bên trái. 

Videos dưới đây sẽ hướng dẫn người dùng phương pháp tìm kiếm từ phải sang trái - tìm kiếm ngược theo các cách như sau:

- Hàm VLOOKUP kết hợp với hàm CHOOSE

Công thức: =VLOOKUP(G2,CHOOSE({1,2},D2:D13,C2:C13),2,0)

- Hàm INDEX kết hợp với hàm MATCH

Công thức: =INDEX(C2:C13,MATCH(G2,D2:D13,0))

- Hàm LOOKUP

Công thức: =LOOKUP(2,1/(D2:D13=G2),C2:C13)

- Hàm XLOOKUP (Có trong Office 365)

Công thức: =XLOOKUP(G2,D2:D13,C2:C13,"")

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Sử dụng hàm LOOKUP mảng thay thế hàm IF lồng ghép

 Trong Excel nâng cao thì hàm Lookup là hàm khá đa năng trong tìm kiếm và được ứng dụng như:

- Tìm kiếm tương đối trong khoảng điều kiện: Giống Vlookup tìm kiếm kiểu TRUE

- Giá trị truyền vào là tập hợp mảng giá trị

Nhưng bản chất của Lookup là tìm kiếm tương đối và loại bỏ giá trị lỗi khi tìm kiếm nên được mệnh danh là hàm tìm kiếm cao cấp, ứng dụng trong những tình huống tìm kiếm nhiều điều kiện, tìm kiếm theo mảng rất tốt.

Videos dưới dây sẽ hướng dẫn sử dụng hàm lookup theo ví dụ đơn giản là tìm kiếm trong khoảng giá trị (cách dùng giống với Vlookup tìm kiếm tương đối)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Sử dụng hàm Vlookup tìm kiếm tương đối để rút ngắn công thức IF lồng ghép

 Hàm Vlookup tìm kiếm tương đối được ứng dụng rất nhiều trong các bài toán xét điều kiện trong khoảng. Điển hình như:

- Xét học lực, xếp loại học tập

- Xét KPI

- Tính thuế

- Tính thưởng

- Tính phạt

- Tính chiết khấu, giảm giá

....

Các bài toán này đều có đặc điểm chung là đều có khoảng giá trị điều kiện (từ bao nhiêu đến bao nhiêu thì được kết quả là gì). Với dạng bài toán này thì nhiều người hay nghĩ tới phương án dùng hàm IF, nhưng phương án này sẽ gặp phải những hạn chế lớn:

- Nhiều khoảng điều kiện thì sẽ có nhiều hàm IF lồng ghép với nhau, người lập công thức không cẩn thận hoặc không có phương pháp lồng ghép tốt thì sẽ dễ nhầm, sai kết quả, lỗi công thức

- Hàm IF sẽ có giới hạn số lượng cấp lồng (Level If), nhiều bài toán có quá nhiều điều kiện khoảng thì hàm IF sẽ báo lỗi vượt Level

- Phải sắp xếp điều kiện để khối IF lồng ngắn gọn hơn, nếu không sắp xếp điều kiện thì phải hối hợp thêm hàm AND, OR, Not và làm công thức dài hơn.

Để khắc phục những hạn chế này, videos dưới đây sẽ hướng dẫn cách dùng hàm VLOOKUP tìm kiếm tương đối với tham số cuối cùng kiểu TRUE hoặc 1. Khi đó chỉ cần dùng 1 hàm VLOOKUP thì sẽ được kết quả tương đương, công thức ngắn gọn hơn rất nhiều.

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

NHÓM HÀM TÌM KIẾM TRONG EXCEL (PHẦN 1)

 NHÓM HÀM TÌM KIẾM TRONG EXCEL (PHẦN 1)

Trong Excel có lẽ hàm tìm kiếm được biết tới phổ biến và thông dụng nhất là hàm VLOOKUP. Tuy nhiên, xét về thực tế thì Excel còn có nhiều hàm tìm kiếm bổ trợ hơn như: HLOOKUP, INDEX, MATCH, LOOKUP, OFFSET. Trong bản Excel 365 còn hỗ trợ thêm hàm XLOOKUP với khả năng tích hợp và tìm kiếm thông minh hơn

1. Lưu ý khi dùng hàm tìm kiếm

Có ít nhất 3 điểm cần lưu ý hoặc 3 câu hỏi cần người dùng cần giải quyết khi lập công thức sử dụng hàm tìm kiếm:

- Giá trị tìm kiếm là giá trị nào?

- Vùng kết quả tìm kiếm ở đâu hoặc kết quả tìm kiếm cần trả về là gì?

- Tìm kiếm theo kiểu nào (dòng, cột, so sánh, tịnh tiến, địa chỉ...)?

2. Hàm tìm kiếm phổ biến nhất VLOOKUP với kiểu tìm kiếm là tuyệt đối chính xác

Tìm kiếm tuyệt đối chính xác là tìm kiếm kiểu FALSE hay còn gọi là kiểu 0. Hàm VLOOKUP có cấu trúc như sau:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Trong đó:

- lookup_value: là giá trị tìm kiếm hay còn gọi là căn cứ để tìm kiếm

- table_array: là bảng tìm kiếm (vùng dữ liệu). Trong đó, cột đầu tiên của bảng tìm kiếm phải chứa giá trị tìm kiếm (lookup_value)

- col_index_num: là số thứ tự cột chứa kết quả trong bảng tìm kiếm cần lấy ra. Số thứ tự này được tính từ trái sang phải của bảng tìm kiếm và tính từ cột đầu tiên của bảng

- [range_lookup]: kiểu tìm kiếm. Mặc định là tìm kiếm kiểu TRUE hay còn gọi là kiểu 1. Nếu người dùng không điền tham số này thì mặc định VLOOKUP sẽ lấy là TRUE. Trong bài này là tìm kiếm tuyệt đối nên bắt buộc người dùng phải điền là FALSE hoặc 0

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết