Hôm nay, Học Excel Online xin giới thiệu với bạn cách 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:
Dưới đây là một số cách sử dụng khác nhau của công thức này: 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)
Kết quả ta sẽ có hình như sau: Cách thức hoạt động:
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)
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 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ó
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 đó:
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:
IFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””) Trong đó
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.
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)),””)
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)),””) Và đây là kết quả thu được: Tương tự, bạn vẫn có thể dùng hàm Vlookup để tham chiếu kết quả với 3, 4 hay nhiều điều kiện ràng buộc hơn. Cách thức hoạt động Về cơ bản, công thức tham chiếu nhiều kết quả cùng lúc bằng hàm Vlookup ràng buộc bởi nhiều điều kiện vẫn có cách thức hoạt động giống như cách giải thích ở ví dụ đầu tiên. Điểm khác biệt duy nhất nằm ở chỗ hàm IF bây giờ có nhiệm vụ phải xử lý nhiều điều kiện ràng buộc hơn. Kết quả của mỗi quá trình so sánh 2 biến lookup_value=lookup_range là một chuỗi các kết quả logic dạng TRUE (nếu đúng) hoặc FALSE (nếu sai). Dấu gạch nối đôi (–) được dùng để biến cách kết quả logic trên thành dạng ký tự số 1 và 0. Bởi vì bất cứ số nào nhân với 0 cũng cho ra kết quả là 0, bạn sẽ chỉ thấy các số 1 minh họa các thành tố đáp ứng được điều kiện ràng buộc xuất hiện trong chuỗi kết quả. Giờ việc bạn cần làm đơn giản chỉ là so sánh chuỗi kết quả cuối cùng với 1 sao cho hàm ROW trả về kết quả là số lượng các hàng đáp ứng được tất cả điều kiện ràng buộc, còn không kết quả sẽ là một ký tự rỗng. Chú ý quan trọng: Tất cả các công thức Vlookup trong bài viết này đều ở dạng công thức dành cho tập hợp. Vì thế, công thức sẽ lặp lại các thành tốt của chuỗi mỗi lần dữ liệu gốc bị thay đổi, hoặc trang tính được tính toán lại. Đối với các bảng tính lớn với số lượng hàng và cột lên đến hàng trăm thì quá trình này sẽ làm giảm tốc độ phản hồi của Excel một cách tương đối. Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. |
