Cách Viết On Tháng Năm 13, 2022
Bài viết sau đây, Học Excel Online sẽ hướng dẫn một số cách đề hàm Vlookup nhiều kết quả phù hợp trong Excel dựa trên một hoặc nhiều điều kiện và trả về nhiều kết quả trong một cột, một hàng hoặc một ô.
Khi sử dụng Microsoft Excel để phân tích dữ liệu tình huống thường gặp là bạn muốn lấy tất cả các giá trị cho một ID cụ thể như tên, địa chỉ email hoặc một số định danh duy nhất khác. Một giải pháp nghĩ đến đó là sử dụng hàm Vlookup trong Excel nhưng vấn đề nó chỉ trả về một kết quả khớp duy nhất.
Vlookup cho nhiều giá trị với nhiều điều kiện có thể được thực hiện thông qua việc sử dụng kết hợp một số chức năng. Nội dung dưới đây sẽ giải thích những kiến thức cơ bản và một số cách điều chỉnh để giải quyết các nhiệm vụ tương tự. Ngoài ra bài viết cũng sẽ cung cấp cho bạn một số mẹo nhỏ chỉ bằng cú nhấp chuột mà không yêu cầu bất kỳ kiến thức nào phức tạp nào.
Vlookup nhiều giá trị bằng công thức
Như đã đề cập ở phần đầu hướng dẫn thì hàm Vlookup không thể trả về nhiều giá trị. Tác vụ này được thực hiện chỉ khi kết hợp các hàm sau trong công thức mảng:
Bạn đang đọc: CÁCH DÙNG HÀM VLOOKUP NHIỀU GIÁ TRỊ VỚI NHIỀU TIÊU CHÍ
- IF – nhìn nhận điều kiện kèm theo và trả về một giá trị nếu điều kiện kèm theo được phân phối và một giá trị khác nếu điều kiện kèm theo không được cung ứng .
- SMALL – nhận giá trị nhỏ nhất thứ k trong chuỗi .
- INDEX – trả về một thành phần dựa trên số hàng và cột mà bạn chỉ định .
- ROW – trả về số thứ tự theo hàng .
- COLUMN – trả về số thứ tự theo cột .
- IFERROR – Cố định vị trí sai số .
Dưới đây bạn sẽ tìm thấy một vài ví dụ về các công thức như vậ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 : Chúng ta có tên người bán cột A, và loại sản phẩm họ đã bán trong cột B. Lưu ý ở cột A ta thấy có 1 số ít tên người bán lặp đi lặp lại hơn 1 lần. Nhiệm vụ của tất cả chúng ta là thống kê người bán tổng số bán những mẫu sản phẩm nào. Dưới đây là hướng dẫn chi tiết cụ thể cách triển khai .
- Nhập tên người bán riêng ra một hàng khác, hoàn toàn có thể nằm trong cùng trang tính hiện tại. Trong ví dụ này, tên được nhập vào các ô D2 : G2 :
2. Ngay cái tên tiên phong, hãy chọn một ô trống bằng hoặc lớn hơn số lượng mẫu sản phẩm mà người bán hoàn toàn có thể phân phối, sau đó nhập một trong các công thức dưới đây vào chỗ trống và bấm tổng hợp Ctrl + Shift + Enter để hoàn tất [ trong trường hợp này bạn chỉ hoàn toàn có thể chỉnh sửa công thức trong khoanh vùng phạm vi mà bạn đã chọn ]. Hoặc, bạn hoàn toàn có thể nhập công thức vào ô tiên phong, nhấn Ctrl + Shift + Enter, và sau đó sao chép công thức vào một vài ô nữa trong cột [ trong trường hợp này, bạn sẽ hoàn toàn có thể chỉnh sửa công thức trong từng ô riêng không liên quan gì đến nhau ] .Dù đã có ứng dụng, nhưng kiến thức và kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa ? Hãy để tôi giúp bạn, ĐK khoá học Excel :
=IFERROR[INDEX[$B$3:$B$13,SMALL[IF[D$2=$A$3:$A$13,ROW[$B$3:$B$13]-2,””], ROW[]-2]],””]
Hoặc là :
=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 tiên phong hoàn toà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ổ cập hơn do ít phải kiểm soát và điều chỉnh [ cấu trúc cú pháp cũng như phương pháp hoạt động giải trí của nó sẽ được lý giải đơn cử hơn ở phần sau ] . 3. Sao chép công thức này tương tự như 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 vắn 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ả sẽ giống như sau :
Công thức này hoạt động như sau:
Cốt lõi của công thức chính là hàm IF với công dụng là xác lập vị trí của từng tổng hợp giá trị tương tự như của ô cần tìm [ D2 ] trong khu vực cần tìm là A3 : A13 : IF [ D USD 2 = $ A $ 3 : USD A $ 13, ROW [ USD B USD 3 : USD B $ 13 ] – 2, ” ” ] . IF so sánh giá trị tra cứu [ D2 ] với mỗi giá trị trong vùng tài liệu [ A3 : A13 ] và nếu tìm thấy khớp sẽ trả về vị trí tương đối của hàng, nếu không khớp một chuỗi rỗng [ “ ” ] Open .
Nếu tìm thấy tác dụng giống thì hàm ROW sẽ trả về tác dụng là số thứ tự theo cột của ô trống tiên phong trong khoanh vùng phạm vi trả tác dụng [ B3 : B13 ]. Sau đó tất cả chúng ta sẽ giám sát 1 số lượng để trừ bớt đi sao cho khoảng cách khu vực cần tìm và khu vực trả tác dụng bằng 1. Với ví dụ trên ta cần trừ bớt đi 2 vì phần khu vực trả về tác dụng khởi đầu ở hàng thứ 3. Nếu khu vực nằm ở hàng thứ 2 thì phải trừ đi 1 tương tự như như vậy .
Hoặc một cách khác là bạn có thể sử dụng biểu thức: ROW[lookup_column]-MIN[ROW[lookup_column]]+1
Khi đó kết quả vẫn trả về giống như vậy và không cần phải điều chỉnh gì mặc cho vị trí của khu vực trả kết quả ở đâu. Trong ví dụ này sẽ là ROW[$A$3:$A$13]-MIN[ROW[$A$3:$A$13]]+1.
Nếu không tìm được tác dụng giống, thì hiệu quả trả về sẽ có dạng rỗng [ “ ” ] . Đến đây bạn đang có một tập hợp các số [ đại diện thay mặt cho số lượng hiệu quả giống ] và các ký tự dạng rỗng [ đại diện thay mặt cho số lượng tác dụng khác ]. Giả sử ví dụ với ô D3, ta có tập hợp tác dụng như hình dưới đây :
Thử kiểm tra so sánh với tài liệu gốc, bạn hoàn toàn có thể thấy là giá trị “ Adam ” [ giá trị tham chiếu trong ô D2 ] Open ở vị trí thứ 3, 8 và 10 của khu vực hiệu 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 lập vị trí cần trả về của từng hiệu quả tham chiếu . Với những thông số kỹ thuật đã xác lập từ trước việc cần làm là xác lập số thứ tự “ k ” tức là giá trị thứ k nhỏ nhất được trả về. Để làm điều này bạn sử dụng bộ đếm số dưới dạng hàm ROW [ ] – n, trong đó n là số thứ tự hàng của ô tính tiên phong trừ đi 1. Trong ví dụ này, ta điền công thức vào dải ô D3 : D7, vì vậy ROW [ ] – 2 trả về hiệu quả là 1 so với ô D3 [ hàng thứ 3 trừ đi 2 đơn vị chức năng ] và trả về tác dụng là 2 so với ô D4 [ hàng thứ 4 trừ đi 2 đơn vị chức năng ], và lặp lại tựa như 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 nối như thế. Từ đó, công thức dài loằng ngoằng phức tạp như khởi đầu được giản lược đi thành dạng đơn thuần hơn như sau :
Lời khuyên : Để nhìn thấy giá trị giám sát ẩ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 index 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, bạn lông hàm IFERROR vào các công thức để giải quyết và xử lý tổng thể các lỗi hoàn toàn có thể xảy ra. Điều này không hề tránh khỏi vì bạn sẽ không biết có bao nhiêu hiệu quả tham chiếu trả giống nau được trả về cho từng giá trị, và từ đó trong quy trình sao chép công thức sang những ô tính khác bạn không hề biết đúng chuẩn nên sao chép bao nhiêu ô là đủ. Để tránh Open những cảnh báo nhắc nhở lỗi không thiết yếu, hàm này sẽ trả về các chuỗi ký tự dạng string rỗng [ “ ” ] để thay thế sửa chữa .
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ể.
Kết hợp tổng thể những điều này lại với nhau, tất cả chúng ta có được được các công thức chung sau để Vlookup nhiều giá trị 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 tiên phong của của khu vực trả hiệu quả, trừ 1 đơn vị chức năng .
- n là số thứ tự theo cột của ô tính chứa công thức khởi đầu, trừ đi 1 đơn vị chức năng .
Lưu ý : Trong ví dụ trên, cả m và n đều có giá trị bằng 2 chính do khu vực trả tác dụng và khu vực chứa công thức đều mở màn từ hàng thứ 3. Tùy theo từng bảng tính đơn cử mà số lượng này hoàn toàn có thể khác nhau .
Công thức 2: Dùng Vlookup để tham chiế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 tác dụng cùng lúc nhưng các hiệu quả trả về sẽ nằm trên cùng 1 hàng thay vì cột thì tất cả chúng ta sử dụng công thức sau đây :
=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 quan tâm phải bấm tổng hợp phím Ctrl + Shift + Enter để hoàn tất .
Cách thức hoạt động giải trí của nó tựa như 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 mục đích xác lập vị trí trả về hiệu quả : COLUMN [ ] – n. Trong đó n là số thứ tự theo cột của ô tính tiên phong chứa công thức, trừ đi 1 đơn vị chức năng. 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 hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo hàng trong Excel như sau :
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 đó :
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
Vlookup thường được biết đến tra cứu trả tác dụng trong Excel trải qua 1 điều kiện kèm theo ràng buộc. Tuy nhiên nếu bạn muốn tham chiếu nhiều hiệu quả dựa trên nhiều hơn 1 tiêu chuẩn ? Chẳng hạn với ví dụ vừa qua nếu như thêm một tiêu chuẩn là cột “ tháng ” và nhu yếu đặt ra làm thế nào phân loại được list mẫu sản phẩm người bán phân phối theo từng tháng .
Nếu bạn quá quen với các công thức mảng, bạn sẽ thấy là thường có dấu “ * ” và hàm AND được sử dụng. Vì vậy mà bạn vẫn hoàn toàn có thể sử dụng công thức đã đàm đạo trong 2 ví dụ trên và nhu yếu kiểm tra nhiều điều kiện kèm theo như ví dụ được minh họa dưới đây .
Trường hợp trả nhiều kết quả theo một 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 tiên phong của khu vực trả hiệu quả, trừ đi 1 đơn vị chức năng
- n là số thứ tự theo hàng của ô tính chứa công thức khởi đầu, trừ đi 1 đơn vị chức năng .
Giả sử list Seller [ người bán ] [ lookup_range1 ] nằm ở dải ô A1 : A30, list Months các tháng [ lookup_range2 ] nằm ở dải ô B1 : B30, nhà phân phối cần chăm sóc [ lookup_value1 ] nằm ở ô tính E3 và tháng cần chăm sóc [ 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 hoàn toàn có thể thuận tiện thiết lập nó trở thành một bảng tinh chỉnh và điều khiển, ví dụ điển hình bạn chỉ cần điền tên Seller [ người bán ] vào ô E3 và tên Month [ tháng ] cần tìm vào ô F3, lập tức tác dụng cho ra một list các loại 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 tác dụng tham chiếu cùng lúc nhưng chịu nhờ vào vào nhiều điều kiện kèm theo ràng buộc khác nhau, bạn nên sử dụng bố cục tổng quan hàng ngang để hiệu quả tham chiếu sẽ được sắp xếp theo cùng 1 hàng. Hãy sử dụng công thức sau :
FERROR[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 tiên phong của khu vực trả hiệu quả, trừ đi 1 đơn vị chức năng
- n là số thứ tự theo hàng của ô tính chứa công thức khởi đầu, trừ đi 1 đơn vị chức năng .
Sử dụng tài 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]],””]
Tương tự, bạn vẫn hoàn toàn có thể dùng hàm Vlookup để tham chiếu tác dụng với 3, 4 hay nhiều điều kiện kèm theo 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 tác dụng cùng lúc bằng hàm Vlookup ràng buộc bởi nhiều điều kiện kèm theo vẫn có phương pháp hoạt động giải trí giống như cách lý giải ở ví dụ tiên phong. Sự độc lạ duy nhất nằm ở chỗ hàm IF giờ đây sẽ phải giải quyết và xử lý nhiều điều kiện kèm theo ràng buộc hơn .
1=[[–[lookup_value1=lookup_range1]]*[–[lookup_value2=lookup_range2]]*…]
Kết quả của mỗi quy trình so sánh 2 biến lookup_value = lookup_range là một chuỗi các hiệu quả logic dạng TRUE [ nếu đúng ] hoặc FALSE [ nếu sai ]. Dấu gạch nối đôi [ – ] để biến hiệu quả logic thành ký tự số 1 và 0. Bởi vì bất kể số nào nhân với 0 cũng bằng 0 nên phần nhiều bạn sẽ chỉ thấy số 1 minh họa thành tố phân phối điều kiện kèm theo ràng buộc. Bây giờ, bạn cần làm đơn thuần chỉ là so sánh chuỗi tác dụng với 1 sao cho hàm ROW trả về tác dụng là số lượng các hàng phân phối được toàn bộ điều kiện kèm theo ràng buộc, còn không hiệu quả sẽ là một ký tự rỗng .
Chú ý: 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ố 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 độ xử lý của Excel một cách tương đối.
Cách Vlookup trả về nhiều giá trị trong một ô
Để triển khai việc làm này bạn hoàn toàn có thể sử dụng phần hỗ trợ đi kèm là Ultimate Suite và Combine Rows Wizard dành cho Excel. Nếu bạn chưa có nó trong Excel thì bạn hoàn toàn có thể tải xuống phiên bản dùng thử 14 ngày không tính tiền và thực thi các bước như bên dưới .
Dữ liệu gốc và kết quả mong đợi với Ultimate Suite
Chúng ta sẽ liên tục thao tác với tập dữ liệu đã sử dụng trong các ví dụ trước. Nhưng lần nay thay vì trích xuất nhiều tác dụng tham chiếu trong từng ô riêng không liên quan gì đến nhau thì sẽ Open trong một ô duy nhất và phân tách bởi dấu phẩy, dấu cách hoặc ngăn cách theo cách bạn chọn .
Di chuyển các hàng có nhiều kết quả vào bảng chính
Trong bảng chính bạn nhập list các tên duy nhất trong cột tiên phong, các tháng trong cột thứ 2 và sắp xếp chúng như dưới đây và triển khai các bước sau :
- Chọn bảng chính của bạn hoặc bấm vào bất kể ô nào trong đó, sau đó bấm vào nút Hợp nhất Hai bảng trên thanh công cụ :
- Phần tính năng hỗ trợ để xác lập và chọn hàng loạt bảng, vì thế bạn chỉ cần nhấp vào Tiếp theo :
Lời khuyên : Khi bạn sử dụng công cụ lần tiên phong thì nên chọn “ create a backup copy of the worksheet ” để tránh thực trạng xảy ra sự cố .
3. Chọn Lookup table và nhấn next:
4. Bạn chọn những cặp giá trị phù hợp được so sánh trong bảng chính và bảng tra cứu [trong ví dụ này là cột Seller và Month], sau đó nhấn Next.
5. Chọn cột mà bạn muốn lấy các giá trị phù hợp [trong ví dụ này là cột Product] và nhấn Next.
6. Bạn lựa chọn hình thức sắp xếp nhiều kết quả trong bảng chính. Với ví dụ này sẽ là “Insert rows with duplicate matching values after the row with the same value”. Nếu không có tùy chọn nhất Finish.
Kết quả: Tất cả các hàng phù hợp sẽ được di chuyển đến bảng chính và được phân theo các giá trị trong các cột tra cứu: Seller, Month, Product.
Tuy nhiên đây chưa phải là tác dụng sau cuối mà tất cả chúng ta mong ước. Chúng ta đang sử dụng Vlookup để tìm kiếm nhiều tác dụng cùng lúc và được cách nhau dấu phẩy, dấu cách hoặc dấu khác. Để làm như vậy tất cả chúng ta liên tục các bước sau .
Kết hợp các hàng trùng lặp thành một hàng
Để hợp nhất “ các hàng trùng lặp ” trong một hàng, tất cả chúng ta sẽ sử dụng một công cụ khác – Combine Rows Wizard
- Chọn bảng được tạo bởi công cụMerge Tables[ xem ảnh chụp màn hình hiển thị ở trên ] hoặc bất kể ô nào trong bảng và nhấp vào nútCombine Rowstrên thanh công cụ :Bạn kiểm tra đúng bảng và nhấnNext
3. Chọn cột chính làm việc [ Seller và Month trong ví dụ này] và nhấp vào Next :
4. Chọn [các] cột chứa nhiều kết quả [ Product trong ví dụ này], chọn dấu phân cách mong muốn [dấu chấm phẩy, dấu phẩy, dấu cách hoặc ngắt dòng] và nhấp vào Finish:
Theo tùy chọn, bạn hoàn toàn có thể bật một trong các tính năng bổ trợ hoặc cả hai :
- Xóa các giá trị trùng lặp – nếu cột được hợp nhất chứa một vài giá trị giống nhau, lần Open tiên phong sẽ được giữ lại, các tác dụng trùng lặp sẽ bị xóa .
- Bỏ qua các ô trống
5. Đợi vài giây để xử lý, bạn sẽ nhận được kết quả như dưới hình:
Xem thêm: Cách ghi Séc rút tiền mặt ngân hàng Vietcombank mới nhất
Trong ví dụ này, không sử dụng tùy chọn này do đó công cụ này trả về tổng thể các hiệu quả tương thích được tìm thấy. Ví dụ, trong ô C2, tất cả chúng ta có chuỗi này : Lemon, Banana, Apple, Lemon, Banana [ vui mừng xem tác dụng ở bước 5 bên dưới ]. Nếu bạn chọn xóa các mục trùng lặp, hiệu quả sẽ là : Lemon, Banana, Apple .
Kết quả
Trên đây là cách tra cứu và trả về nhiều giá trị trong Excel bằng các 2 công cụ trên. Nếu bạn muốn dùng thử chúng, bạn hoàn toàn có thể tải xuống phiên bản Ultimate Suite bên dưới để thưởng thức .
Source: //cachlam365.net
Category: Cách Viết