Với nhiều bạn, Excel luôn là một bài toán vô cùng khó khăn với vô vàn các hàm tính toán khác nhau mà bạn không thể nhớ hết. Vậy hôm nay, Gamehot24h xin giới thiệu với bạn một số mẹo để sử dụng hàm Vlookup - tham chiếu nhiều kết quả cùng lúc trên Excel một cách dễ dàng nhất. Dưới đây sẽ là hướng dẫn phương pháp tham chiếu chỉ bằng 1 công thức duy nhất.
Thông thường hàm Vlookup chỉ cho ra 1 kết quả tra cứu đơn lẻ. Để có thể nhận được nhiều kết quả tham chiếu hơn, bạn cần phải sử dụng chuỗi các điều kiện khác nhau để kết hợp chung vào hàm Vlookup, cụ thể ta sẽ sử dụng các hàm sau:
- Hàm IF: Là hàm điều kiện có chức năng trả về kết quả A nếu như đáp ứng được một điều kiện cụ thể nào đó, còn nếu không đáp ứng được điều kiện ấy thì thay vào đó nó sẽ trả về kết quả B.
- Hàm SMALL: Trả về giá trị nhỏ nhất nằm ở vị trí thứ “k” trong chuỗi
- Hàm INDEX: Trả về yếu tố chuỗi dựa trên số lượng cột và hàng bạn sử dụng
- Hàm ROW: Trả về số thứ tự theo hàng
- Hàm COLUMN: Trả về số thứ tự theo cột
- Hàm IFERROR: Cố định vị trí sai số.
Một số cách sử dụng khác nhau của công thức Vlookup:
Công thức 1: Dùng Vlookup để tham chiếu nhiều kết quả trong cùng một cột
Ví dụ minh họa cho thấy cột A bao gồm tên các nhà phân phối và cột B thể hiện loại mặt hàng mà từng nhà phân phối cung cấp. Lưu ý là ở cột A ta để ý thấy có một số tên nhà phân phối bị lặp đi lặp lại nhiều hơn 1 lần. Nhiệm vụ của bạn là phải thống kế xem mỗi nhà phân phối tổng cộng cung cấp những mặt hàng nào. Dưới đây là hướng dẫn chi tiết cách làm:
1. Phân loại tên từng nhà phân phối riêng ra một hàng khác, có thể cùng nằm chung trong trang tính hiện tại. Ảnh dưới đây minh họa tên các nhà phân phối được phân loại trong hàng D2:G2
2. Ngay ở ô dưới cái tên nhà phân phối đầu tiên, lựa chọn số ô trống vừa đủ sao cho nó lớn hơn hoặc bằng số lượng mặt hàng tối đa mà một nhà phân phối có thể cung cấp, sau đó điền một trong các công thức dưới đây vào chỗ trống. Bấm tổ hợp phím Ctrl+Shift+Enter để hoàn tất (trong trường hợp này, bạn chỉ có thể chỉnh sửa nội dung công thức cho toàn bộ các ô được lựa chọn mà thôi). Hoặc bạn có thể điền công thức vào ô trống đầu tiên, bấm tổ hợp phím Ctrl+Shift+Enter và sao chép công thức đó cho các ô trống phía dưới (khi đó bạn vẫn có thể chỉnh sửa công thức cho từng ô một)
=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””), ROW()-2)),””)
Hoặc
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,””), ROW()-2)),””)
Công thức đầu tiên có thể trông gọn nhẹ hơn nhưng công thức thứ hai lại được sử dụng phổ biến hơn do ít phải điều chỉnh (cấu trúc cú pháp cũng như cách thức hoạt động của nó sẽ được giải thích cụ thể hơn ở phần sau)
- Sao chép công thức này tương tự cho các cột khác. Để làm được, bạn chỉ cần bôi đen phần cột vừa điền công thức vào và click phím điều hướng (là ô hình vuông nhỏ nằm ở góc dưới bên phải của phần được bôi đen) rồi kéo sang phải là xong.
Kết quả ta sẽ có hình như sau:
Cách thức hoạt động:
- Hàm IF
Trọng tâm của công thức chính là hàm IF, với chức năng tìm kiếm vị trí của từng tổ hợp giá trị tương tự của ô cần tìm (D2) trong khu vực cần tìm là A3:A13: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””)
Nếu tìm thấy kết quả giống, hàm ROW sẽ trả về kết quả là số thứ tự theo cột của ô trống đầu tiên trong phạm vị trả kết quả (B3:B13), sau đó bạn phải tính toán 1 con số để trừ bớt đi sao cho khoảng cách giữa khu vực cần tìm và khu vực trả về kết quả là bằng 1. Lý do là bởi ta đang đi tìm vị trí tương đối của yếu tố đầu tiên trong chuỗi. Với ví dụ minh họa, ta cần trừ bớt đi 2 bởi vì phần khu vực trả về kết quả nằm bắt đầu ở hàng thứ 3. Nếu khu vực đó nằm ở hàng thứ 2 thì bạn phải trừ bớt đi 1, và tương tự như vậy.
Hoặc cách khác bạn có thể làm là sử dụng cách diễn đạt sau: ROW(lookup_column)-MIN(ROW(lookup_column))+1, khi đó kết quả trả về vẫn giống tương tự nhưng không cần phải điều chỉnh gì thêm mặc cho vị trí của khu vực trả về kết quả là ở đâu. Đối với ví dụ minh họa, ta sử dụng cấu trúc sau: ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.
Nếu không tìm được kết quả giống, thì kết quả trả về sẽ có dạng string rỗng (“”)
Đến đây bạn đang có một tập hợp các số (đại diện cho số lượng kết quả giống) và các ký tự dạng string rỗng (đại diện cho số lượng kết quả khác). Giả sử ví dụ với ô D3, ta có tập hợp kết quả như hình dưới đây:
Thử kiểm tra đối chiếu với dữ liệu gốc, bạn có thể thấy là giá trị “Adam” (giá trị tham chiếu trong ô D2) xuất hiện ở vị trí thứ 3, 8 và 10 của khu vực kết quả tham chiếu (A3:A13)
- Hàm SMALL
Tiếp theo, hàm SMALL(tập hợp, k) được sử dụng để xác định vị trí cần trả về của từng kết quả tham chiếu.
Với thông số “tập hợp” đã được xác định từ trước, việc cần quan tâm là xác định số thứ tự “k” , đồng nghĩa với giá trị nhỏ nhất thứ “k” được trả về. Để làm được điều đó, bạn cần phải sử dụng một bộ đếm số dưới dạng hàm ROW()-n, trong đó n là số thứ tự hàng của ô tính đầu tiên trừ đi 1. Trong ví dụ này, ta điền công thức vào dải ô D3:D7, cho nên ROW()-2 trả về kết quả là 1 đối với ô D3 (hàng thứ 3 trừ đi 2 đơn vị) và trả về kết quả là 2 đối với ô D4 (hàng thứ 4 trừ đi 2 đơn vị), và lặp lại tương tự như vậy.
Kết quả là, hàm SMALL sẽ đặt giá trị nhỏ nhất của tập hợp trong ô D3, giá trị nhỏ thứ nhì vào ô D4, và cứ tiếp diễn như thế. Từ đó, công thức dài loằng ngoằng phức tạp như ban đầu được giản lược đi thành dạng đơn giản hơn như sau:
“Lời khuyên: Để nhìn thấy giá trị tính toán ẩn sau từng phần của công thức, chỉ cần bôi đen phần đó lại và bấm phím F9”
- Hàm INDEX
Hàm này hoạt động khá đơn giản. Hàm này được dùng để lấy giá trị của một thành phần trong tập hợp dựa trên số thứ tự theo hàng của nó
- Hàm IFERROR
Cuối cùng, công thức được kết thúc bằng hàm IFERROR để xử lý một số lỗi có thể xảy ra, điều này là không thể tránh khỏi bởi vì bạn không thể tính được có bao nhiều kết quả tham chiếu giống nhau được trả về cho từng giá trị khác nhau, từ đó có thể trong quá trình sao chép công thức sang các ô tính khác bạn không thể biết chính xác nên sao chép sang bao nhiêu ô là đủ. Để tránh xuất hiện những cảnh báo lỗi không cần thiết, hàm này sẽ trả về các chuỗi ký tự dạng string rỗng (“”) để thay thế.
Lưu ý: Bạn cần phân biệt cách sử dụng tham chiếu tuyệt đối và tham chiếu tương đối trong các công thức Excel. Tất cả tham chiếu sẽ không bị thay đổi ngoại trừ cột tham chiếu tương đối của giá trị cần tham chiếu (D$2), điều này là yêu cầu bắt buộc trong quá trình sao chép công thức sang các cột bên cạnh, để từ đó cho ra các kết quả phù hợp với từng cột cụ thể.
Sau khi tổng hợp lại, ta có được công thức tổng quát dành cho hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo cột trong Excel:
Công thức số 1: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW( return_range )- m ,””), ROW() – n )),””)
Công thức số 2: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range , ROW( lookup_range) -MIN(ROW( lookup_range ))+1,””), ROW() – n)),””)
Trong đó:
- m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
- n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị
Lưu ý: Trong ví dụ trên, cả m và n đều có giá trị bằng 2 bởi vì khu vực trả kết quả và khu vực chứa công thức đều bắt đầu từ hàng thứ 3. Tùy theo từng bảng tính cụ thể mà con số này có thể khác nhau.
Công thức 2: Dùng Vlookup để tham chiếu nhiều kết quả trong cùng một hàng
Trong trường hợp bạn vẫn muốn tham chiếu nhiều kết quả cùng lúc nhưng các kết quả trả về phải nằm trên cùng 1 hàng thay vì cột thì ta sử dụng công thức như sau:
=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,””), COLUMN()-4)),””)
Hoặc
=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,””),COLUMN()-4)), “”)
Tương tự ví dụ trước đó, đây đều là công thức dành cho tập hợp, nên là bạn lưu ý phải bấm tổ hợp phím Ctrl+Shift+Enter để hoàn tất.
Cách thức hoạt động của nó tương tự như ví dụ trước, ngoại trừ 1 điều là bạn sử dụng hàm COLUMN thay vì hàm ROW nhằm xác định vị trí trả về kết quả giống với giá trị tham chiếu: COLUMN()-n. Trong đó n là số thứ tự theo cột của ô tính đầu tiên chứa công thức, trừ đi 1 đơn vị. Trong ví dụ này công thức được nhập vào dải ô E2:H2. Với việc cột E nằm ở thứ tự số 5 thì khi đó n sẽ bằng 4.
Lưu ý: Để có thể sao chép chính xác công thức sang các hàng khác, lưu ý hãy để thông số tham chiếu dạng tuyệt đối theo cột và tương đối theo hàng, chẳng hạn: $D3
Sau khi tổng hợp lại, ta có được công thức tổng quát dành cho hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo hàng trong Excel:
Công thức số 1: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW( return_range) – m, “”), COLUMN() – n)), “”)
Công thức số 2: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW(lookup_range) – MIN(ROW( lookup_range))+1,””),COLUMN() – n)), “”)
Trong đó:
m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
n là số thứ tự theo cột của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị
Công thức 3: Dùng Vlookup để tham chiếu nhiều kết quả cùng lúc với điều kiện ràng buộc
Bạn có thể đã quen với việc sử dụng Vlookup để tra cứu kết quả trong Excel thông qua 1 điều kiện ràng buộc. Nhưng nếu như bạn cần phải tham chiếu nhiều kết quả dựa trên nhiều hơn 1 tiêu chí? Chẳng hạn với ví dụ vừa rồi, nếu như có thêm 1 cột tiêu chí về “Tháng” được bổ sung vào, làm cách nào để bạn có thể phân loại được danh sách các mặt hàng mà một nhà phân phối cung cấp theo từng tháng?
Nếu bạn quen với các công thức dành cho tập hợp, bạn sẽ thấy là thường sẽ có dấu “*” và hàm AND được sử dụng. Vì vậy bạn có thể vẫn tái sử dụng các công thức như phía trên và thêm vào các điều kiện ràng buộc dưới đây:
- Trường hợp trả về nhiều kết quả theo cùng 1 cột
IFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)
Trong đó
- m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
- n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị
Giả sử danh sách nhà phân phối (lookup_range1) nằm ở dải ô A1:A30, danh sách các tháng (lookup_range2) nằm ở dải ô B1:B30, nhà phân phối cần quan tâm (lookup_value1) nằm ở ô tính E3 và tháng cần quan tâm (lookup_value2) nằm ở ô tính F3, khi đó ta có công thức sau:
=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E$3=$A$3:$A$30)) * (–($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,””), ROW()-2)),””)
Với dạng trên, ta có thể dễ dàng thiết lập nó trở thành một bảng điều khiển, chẳng hạn bạn chỉ cần điền tên nhà phân phối vào ô E3 và tên tháng cần tìm vào ô F3, lập tức kết quả cho ra một danh sách các sản phẩm ở cột G.
- Trường hợp trả về nhiều kết quả theo cùng 1 hàng
Nếu bạn muốn lấy nhiều kết quả tham chiếu cùng lúc nhưng chịu phụ thuộc vào nhiều điều kiện ràng buộc khác nhau, lời khuyên là nên sử dụng bố cục hàng ngang để kết quả tham chiếu được sẽ được bố trí theo cùng 1 hàng. Hãy sử dụng công thức sau:
IFERROR(INDEX( return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2))), ROW(return_range) – m, “”), COLUMN() – n)),””)
- m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
- n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị
Sử dụng dữ liệu của ví dụ trên, công thức sẽ có dạng như sau:
=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E3=$A$3:$A$30))*(–($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,””), COLUMN()-6)),””)
Trên đây là một số mẹo nhỏ giúp bạn dễ dàng chinh phục và sử dụng hàm Vlookup để tham chiếu nhiều kết quả trong Excel. Chúc bạn luôn thành công!
source https://gamehot24h.com/kien-thuc/cach-su-dung-ham-vlookup-nang-cao-de-tham-chieu-nhieu-ket-qua-cung-luc-tren-excel-755626.html
Không có nhận xét nào:
Đăng nhận xét