Dữ liệu ánh xạ là một phần thiết yếu của Excel . Do đó, biết một số phương pháp hữu ích để ánh xạ dữ liệu có thể tiết kiệm rất nhiều thời gian và cải thiện quy trình làm việc. Với suy nghĩ này, bài viết này trình bày 4 các cách hữu ích về cách ánh xạ dữ liệu trong Excel VLOOKUP . Hơn nữa, chúng ta cũng sẽ thảo luận về cách nhận được lần xuất hiện thứ N của một giá trị và ẩn lỗi bằng cách sử dụng VLOOKUP chức năng.
Bạn có thể tải xuống sách bài tập thực hành từ liên kết bên dưới.
4 cách lập bản đồ dữ liệu bằng hàm VLOOKUP trong Excel
Trong bài viết này, chúng tôi sẽ kết hợp VLOOKUP chức năng với MATCH , COUNTIF , CHỈ ĐỊNH và hàm IF sang dữ liệu bản đồ . Vì vậy, đừng chần chừ thêm nữa, hãy đi sâu vào!
Ở đây, chúng tôi đã sử dụng Microsoft Excel 365 phiên bản, bạn có thể sử dụng bất kỳ phiên bản nào khác tùy theo sự thuận tiện của bạn.
Phương pháp-1 :Sử dụng hàm VLOOKUP để ánh xạ dữ liệu trong Excel
Hãy bắt đầu mọi thứ với phương pháp rõ ràng nhất, tức là sử dụng hàm VLOOKUP để ánh xạ dữ liệu trong Excel. Vì vậy, hãy bắt đầu.
Xem xét tập dữ liệu được hiển thị trong B4:D14 tế bào. Tại đây, tập dữ liệu hiển thị danh sách ID của nhân viên s, Tên của họ và Bộ phận nơi họ làm việc.
Các bước :
- Ngay từ đầu, hãy điều hướng đến G5 ô và nhập biểu thức bên dưới.
=VLOOKUP(G4,B5:D14,3,FALSE)
Trong công thức này, G4 ô tham chiếu đến ID 1008 và B5:D14 phạm vi ô đại diện cho ID , Tên và Cục cột.
Phân tích công thức:
- VLOOKUP (G4, B5:D14,3, FALSE) → tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng, sau đó trả về một giá trị trong cùng một hàng từ cột bạn chỉ định. Đây, G4 ( lookup_value đối số) được ánh xạ từ B5:D14 ( table_array đối số) mảng. Tiếp theo, 3 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE ( range_lookup đối số) đề cập đến Đối sánh chính xác của giá trị tra cứu.
- Đầu ra → Tiếp thị
Cuối cùng, kết quả sẽ giống như hình dưới đây.
Phương pháp-2 :Ánh xạ dữ liệu với hàm VLOOKUP và MATCH (hàm VLOOKUP hai chiều)
Trong phương pháp thứ hai, chúng tôi sẽ kết hợp VLOOKUP và MATCH hàm ánh xạ một giá trị tại giao điểm của một hàng và cột cụ thể. Đây còn được gọi là VLOOKUP hai chiều . Vì vậy, hãy xem nó hoạt động.
Giả sử chúng tôi có Danh sách bán hàng tập dữ liệu được hiển thị trong B4:E12 tế bào. Tại đây, chúng tôi có danh sách Mục và số lượng Đơn vị đã bán vào tháng 1 , tháng 2 và tháng 3 .
Các bước :
- Ban đầu, chọn Mục và Tháng , ví dụ:chúng tôi đã chọn Truyền hình và tháng 3 tương ứng.
- Tiếp theo, chuyển đến H6 và nhập công thức cho bên dưới.
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
Đây, H4 và H5 ô tham chiếu đến Mục và Tháng tương ứng trong khi B5:E5 đại diện cho các tiêu đề cột.
Phân tích công thức:
- TRẬN ĐẤU (H5, B5:E5, 0) → trả về vị trí tương đối của một mục trong một mảng phù hợp với giá trị đã cho. Đây, H5 là lookup_value đối số đề cập đến Tháng 3 . Sau đây, B5:E5 đại diện cho lookup_array đối số từ nơi giá trị được so khớp. Gần đây, 0 là match_type tùy chọn đối số cho biết Đối sánh chính xác tiêu chí.
- Đầu ra → 4
- VLOOKUP (H4, B6:E10, MATCH (H5, B5:E5, 0), FALSE) → trở thành
- VLOOKUP (H4, B6:E10, 4, FALSE) → Đây, H4 ( lookup_value đối số) được ánh xạ từ B6:E10 ( table_array đối số) mảng. Tiếp theo, 4 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE ( range_lookup đối số) đề cập đến Đối sánh chính xác của giá trị tra cứu.
- Đầu ra → 243
Cuối cùng, kết quả của bạn sẽ giống như hình dưới đây.
Phương pháp-3 :Sử dụng các hàm VLOOKUP và COUNTIF để lập dữ liệu bản đồ
Một cách khác để dữ liệu bản đồ trong Excel là kết hợp hàm COUNIF trong VLOOKUP hàm số. Thật đơn giản và dễ dàng, chỉ cần làm theo các bước sau.
Hãy xem xét Sách bán chạy nhất tập dữ liệu được hiển thị trong B4:C11 tế bào. Trong tập dữ liệu này, chúng tôi có tên của Người bán chạy nhất và Giá của nó tương ứng là USD.
Các bước :
- Để bắt đầu, hãy chuyển đến F5 ô và nhập biểu thức bên dưới.
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
Trong biểu thức này, B5:C9 phạm vi ô đại diện cho Sách bán chạy nhất và Giá cột. Ngược lại, F4 ô đề cập đến Người bán chạy nhất (đây, nó là House of Wisdom )
Phân tích công thức:
- COUNTIF (B5:B9, F4) → đếm số lượng ô trong một phạm vi đáp ứng điều kiện đã cho. Đây, B5:B9 là phạm vi đối số đề cập đến Sách bán chạy nhất . Đang theo dõi, F4 đại diện cho tiêu chí đối số trả về số lượng giá trị đã so khớp.
- Đầu ra → 1
- VLOOKUP (F4, B5:C9,2, TRUE) → Đây, F4 ( lookup_value đối số) được ánh xạ từ B5:C9 ( table_array đối số) mảng. Tiếp theo, 2 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, TRUE ( range_lookup đối số) đề cập đến Đối sánh gần đúng của giá trị tra cứu.
- Đầu ra → 25
- IF (COUNTIF (B5:B9, F4), VLOOKUP (F4, B5:C9,2, TRUE), 0) → trở thành
- IF (1,25,0) → kiểm tra xem một điều kiện có được đáp ứng hay không và trả về một giá trị nếu TRUE và một giá trị khác nếu FALSE . Đây, 1 là logic_test đối số nhắc IF hàm trả về 25 ( value_if_true đối số) nếu không nó trả về 0 ( value_if_false lập luận).
- Đầu ra → $ 25
Do đó, kết quả sẽ giống như ảnh chụp màn hình bên dưới.
Phương pháp-4 :Ánh xạ dữ liệu bằng hàm VLOOKUP và hàm INDIRECT trong Excel
Bạn cũng có thể dữ liệu bản đồ trong Excel bằng cách kết hợp INDIRECT và VLOOKUP chức năng. Bây giờ, hãy đi qua các bước.
Giả sử chúng tôi có Danh sách hàng tạp hóa tập dữ liệu được hiển thị trong B4:I10 tế bào. Tập dữ liệu hiển thị Giá cùng một Mặt hàng trong 3 các thành phố trên khắp Hoa Kỳ .
Các bước :
- Trước tiên, hãy chuyển đến B5 ô>> nhấp vào Dữ liệu >> sau đó nhấn vào nút Xác thực dữ liệu thả xuống.
Thao tác này sẽ mở Xác thực dữ liệu hộp thoại.
- Thứ hai, trong phần Cho phép trình đơn thả xuống chọn Danh sách tùy chọn>> trong Nguồn chọn trường B6:B10 ô>> nhấp vào nút OK nút.
- Thứ ba, chọn B6:C10 ô>> chuyển đến Công thức >> nhấp đúp vào tab Xác định tên tùy chọn.
Thao tác này sẽ mở ra Chỉnh sửa tên thuật sĩ.
- Tại đây, hãy nhập một tên phù hợp (trong trường hợp này là Boston ) cho phạm vi dữ liệu>> nhấp vào nút OK nút.
Theo cách tương tự, hãy xác định Phạm vi được đặt tên cho Atlanta .
Tương tự, xác định Phạm vi được đặt tên cho Denver .
- Thứ tư, điều hướng đến C14 ô>> trên tab Dữ liệu và nhấp vào Xác thực dữ liệu nút.
- Tương tự, chọn Danh sách tùy chọn>> nhập Phạm vi được đặt tên như thể hiện trong ảnh chụp màn hình bên dưới.
Bây giờ, hãy chọn Mục và Vị trí từ trình đơn thả xuống. Ví dụ:chúng tôi đã chọn Tomato và Atlanta tương ứng.
- Sau đó, trong D14 nhập ô trong biểu thức sau.
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
Đây, B14 và C14 các ô trỏ đến Mục và Vị trí tương ứng.
Phân tích công thức:
- INDIRECT (C14) → trả về tham chiếu được chỉ định bởi một chuỗi văn bản. Đây, C14 là ref_text đối số đề cập đến Phạm vi được đặt tên Boston .
- VLOOKUP (B14, INDIRECT (C14), 2, FALSE) → Đây, B14 ( lookup_value đối số) được ánh xạ từ Phạm vi được đặt tên, INDIRECT (C14) đó là table_array tranh luận. Tiếp theo, 2 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE ( range_lookup đối số) đề cập đến Đối sánh chính xác của giá trị tra cứu.
- Đầu ra → 1,2 đô la
Sau đó, sau khi hoàn thành các bước trên, kết quả sẽ giống như hình bên dưới.
Hơn nữa, bạn cũng có thể sử dụng phương pháp này trong trường hợp bạn có dữ liệu trong nhiều trang tính và bạn muốn lập bản đồ nó từ các trang tính cụ thể dựa trên tra cứu giá trị.
Áp dụng hàm VLOOKUP để thu được lần xuất hiện thứ N
Giả sử bạn có Bán hàng văn phòng phẩm danh sách như được hiển thị trong B4:D13 các ô bên dưới. Bây giờ, bạn muốn biết mặt hàng thứ 2 hoặc thứ 3 được mua bởi cùng một khách hàng. Để giải quyết vấn đề này, chúng tôi có thể sử dụng COUNTIF và VLOOKUP các chức năng để thu được kết quả. Vì vậy, hãy xem quy trình chi tiết.
Các bước :
- Trước hết, hãy chèn một cột vào Cột B và đổi tên tiêu đề của nó thành Cột người trợ giúp >> trong B5 nhập công thức cho bên dưới.
=C5&COUNTIF($C$5:C5, C5)
📄 Lưu ý: Hãy đảm bảo chèn Cột trợ giúp ở phía ngoài cùng bên trái của tập dữ liệu, vì theo mặc định là VLOOKUP chức năng nhìn từ trái sang phải.
Đây, C5 ô tham chiếu đến Tên John và COUNTIF hàm đếm sự xuất hiện của John từ phạm vi đã cho $ C $ 5:C5 . Cuối cùng, Ký hiệu và (&) toán tử kết hợp văn bản và số.
- Tiếp theo, nhập Tên và Phiên bản , ví dụ:đây là Julie và 3 >> chuyển đến H6 ô và nhập biểu thức bên dưới.
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
Trong công thức này, H4 và H5 các ô cho biết Tên và Phiên bản .
Sau khi hoàn thành các bước trên, kết quả sẽ giống như ảnh chụp màn hình bên dưới.
Sử dụng hàm VLOOKUP và IF để ẩn lỗi # N / A
Bạn có thể sử dụng ISNA và VLOOKUP các hàm trong hàm IF để ẩn # N / A lỗi khi giá trị tra cứu không hợp lệ được đưa ra. Bây giờ, hãy cho phép tôi chứng minh quy trình bên dưới.
Các bước :
- Ngay từ đầu, hãy điều hướng đến G5 ô và nhập biểu thức bên dưới.
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
Trong công thức này, G4 ô tham chiếu đến ID 1008 và B5:D14 phạm vi ô đại diện cho ID , Tên và Cục cột.
Phân tích công thức:
- ISNA (VLOOKUP (G4, B5:D14,3, FALSE)) → kiểm tra xem giá trị có phải là #NA không và trả về TRUE hoặc FALSE . Đây, G4 ( lookup_value đối số) được ánh xạ từ B5:D14 ( table_array đối số) mảng. Tiếp theo, 3 ( col_index_num đối số) đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE ( range_lookup đối số) đề cập đến Đối sánh chính xác của giá trị tra cứu.
- Đầu ra → FALSE
- IF (ISNA (VLOOKUP (G4, B5:D14,3, FALSE)), “”, VLOOKUP (G4, B5:D14,3, FALSE)) → trở thành
- IF (FALSE, “”, VLOOKUP (G4, B5:D14,3, FALSE)) → kiểm tra xem một điều kiện có được đáp ứng hay không và trả về một giá trị nếu TRUE và một giá trị khác nếu. Đây, FALSE là logic_test đối số nhắc IF hàm trả về giá trị từ VLOOKUP hàm ( value_if_true đối số) nếu không nó trả về giá trị trống “” ( value_if_false lập luận).
- Đầu ra → Nhân sự
Cuối cùng, bạn sẽ nhận được kết quả hiển thị trong hình dưới đây.
Ngoài điều này, nếu chúng tôi nhập ID không hợp lệ số ( ID 1012 ), công thức trả về giá trị trống thay vì # N / A lỗi được hiển thị trong ảnh chụp màn hình dưới đây.
Phần thực hành
Chúng tôi đã cung cấp một Thực hành phần bên phải của mỗi tờ để bạn có thể tự luyện tập. Hãy đảm bảo bạn tự làm.
Kết luận
Tôi hy vọng tất cả các phương pháp về cách ánh xạ dữ liệu trong Excel sử dụng VLOOKUP bây giờ sẽ nhắc bạn áp dụng chúng trong bảng tính Excel của mình một cách hiệu quả hơn. Nếu bạn có bất kỳ câu hỏi hoặc phản hồi nào, vui lòng cho tôi biết trong phần bình luận. Hoặc bạn có thể xem các bài viết khác của chúng tôi liên quan đến các hàm Excel trên trang web này.