Hôm nay chúng tôi sẽ hướng dẫn cách sử dụng công thức VLOOKUP với nhiều sheet trong Excel. Trên thực tế, một trong những chức năng quan trọng và được sử dụng rộng rãi nhất của Excel là hàm VLOOKUP. Hơn nữa, chúng ta có thể sử dụng hàm VBA VLOOKUP để tìm kiếm dữ liệu cụ thể trong một trang tính hoặc trong một phạm vi trang tính Show
Ngoài ra, hôm nay chúng tôi sẽ chỉ ra cách chúng tôi có thể sử dụng các công thức VLOOKUP để tìm kiếm một số dữ liệu cụ thể trong nhiều trang tính trong Excel Mục lục ẩn Tải sách bài tập thực hành Giới thiệu về Hàm VLOOKUP của Excel 5 cách sử dụng công thức VLOOKUP trong Excel với nhiều trang tính 1. Công thức VLOOKUP để tìm kiếm trên từng trang tính riêng biệt 2. Tìm kiếm trên nhiều Sheet với Hàm IFERROR trong Excel 3. Sử dụng công thức kết hợp để tìm kiếm trên nhiều trang tính trong Excel 4. Công thức VLOOKUP với số chỉ mục cột động 5. Công thức VLOOKUP với các hàm kết hợp trong Excel Hạn chế của hàm VLOOKUP và một số giải pháp thay thế trong Excel Cách áp dụng công thức VLOOKUP trong Excel với nhiều sổ làm việc phần thực hành Sự kết luận Đọc thêm Tải sách bài tập thực hànhCác bạn có thể tải sách bài tập thực hành tại đây Công thức VLOOKUP với nhiều trang tính. xlsx Điểm thi thử. xlsx Giới thiệu về Hàm VLOOKUP của Excel=VLOOKUP(giá trị tra cứu,bảng_mảng,col_index_num,[phạm vi tra cứu])
Sau đó, trả về giá trị từ ô đó  Phân tích công thức Ở đây, công thức VLOOKUP(G8,B4. D15,3,FALSE) đã tìm kiếm giá trị của ô G8 “Angela” trong cột đầu tiên của bảng. B 4. D15 Sau khi tìm thấy, nó di chuyển ngay đến cột thứ 3 (Vì col_index_number là 3. ) Sau đó trả về giá trị từ đó, là 322 5 cách sử dụng công thức VLOOKUP trong Excel với nhiều trang tínhỞ đây, chúng tôi có một bảng tính với các điểm trong bài kiểm tra viết và viva của một số ứng viên trong ba tuần trong các bảng tính khác nhau. Ngoài ra, tên của cái đầu tiên là Tuần 1 Sau đó, tên của trang tính thứ 2 là Tuần 2 Cuối cùng, tên của bảng thứ 3 có chứa điểm của Nhóm Marco là Tuần 3 Bây giờ, mục tiêu của chúng ta là trích xuất điểm của họ từ ba trang tính sang trang tính mới bằng cách sử dụng hàm VLOOKUP của Excel 1. Công thức VLOOKUP để tìm kiếm trên từng trang tính riêng biệtỞ đây, chúng tôi có một trang tính mới có tên là “Chỉ VLOOKUP” với tên của tất cả các ứng viên được sắp xếp theo thứ tự bảng chữ cái (A đến Z). Bây giờ, chúng ta sẽ sử dụng công thức VLOOKUP để tìm kiếm từ nhiều sheet trong Excel Trước hết, chúng tôi sẽ tìm kiếm thông qua ba trang tính riêng biệt Ở đây chúng ta sẽ tìm kiếm lookup_value từ một trang tính trong một dải ô của một trang tính khác Cú pháp của công thức sẽ là =VLOOKUP(giá trị_tra cứu,‘Tên_bảng’. table_array, col_index_number,SAI)
Điều này đang hiển thị #N/A. Lỗi, vì giá trị của ô B5 trong bảng "chỉ VLOOKUP", Alex Hales, không có trong phạm vi B5. D10 của tờ “Tuần 1“
Do đó, chúng tôi thấy chỉ những thí sinh xuất hiện trong Tuần 1 mới được hiển thị, còn lại đều hiển thị lỗi
Vì vậy, chúng tôi thấy điểm của chỉ những thí sinh xuất hiện trong Tuần 1 mới được hiển thị, phần còn lại đang hiển thị lỗi Hơn nữa, chúng tôi cũng có thể thực hiện một nhiệm vụ tương tự cho Tuần 2 và Tuần 3, nhưng điều đó sẽ không đáp ứng nhu cầu của chúng tôi. Vì vậy, chúng ta phải tìm kiếm một cách tiếp cận tốt hơn Để ý. Ở đây, chúng tôi đã sử dụng tham chiếu ô tương đối cho lookup_value (B5), nhưng tham chiếu ô tuyệt đối cho table_array ($B$5. $D$10). Bởi vì chúng tôi muốn giá trị tra cứu tăng từng cái một trong khi kéo biểu tượng Xử lý điền, nhưng table_array không đổi Đọc thêm. Tại sao VLOOKUP trả về #N/A khi tồn tại kết quả khớp? 2. Tìm kiếm trên nhiều Sheet với Hàm IFERROR trong ExcelLần này, trước tiên chúng ta sẽ tìm kiếm một ứng viên trong bảng tính đầu tiên (Tuần 1) Sau đó, nếu chúng tôi không tìm thấy anh ấy / cô ấy trong trang tính đầu tiên, chúng tôi sẽ tìm kiếm trong trang tính thứ hai (Tuần 2) Còn nếu vẫn chưa tìm được thì chúng ta tìm ở phiếu học tập thứ 3 (Tuần 3) Nếu chúng tôi vẫn không tìm thấy anh ấy / cô ấy, chúng tôi sẽ quyết định rằng anh ấy / cô ấy vắng mặt trong kỳ thi Trong phần trước chúng ta đã thấy, hàm VLOOKUP trả về N/A. Lỗi nếu nó không tìm thấy bất kỳ kết quả nào khớp với lookup_value trong table_array Nên lần này chúng ta sẽ lồng hàm VLOOKUP vào trong hàm IFERROR để xử lý lỗi Do đó cú pháp của công thức sẽ là =IFERROR(VLOOKUP(lookup_value,”Sheet1_Name”. table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet2_Name”. table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet3_Name”. table_array,col_index_number,FALSE),”Vắng mặt”)))
Kết quả là bạn sẽ thấy các dấu viết của Alex Hales Sau đó, chúng ta sẽ tìm thấy dấu viva của Alex Hales
Cuối cùng, bạn sẽ thấy cả điểm viết và viva cho tất cả các ứng viên Đọc thêm. Ví dụ VLOOKUP giữa hai trang tính trong Excel bài đọc tương tự
3. Sử dụng công thức kết hợp để tìm kiếm trên nhiều trang tính trong ExcelTrên thực tế, công thức IFERROR và VLOOKUP lồng nhau mà chúng ta đã sử dụng trước đó rất hữu ích nhưng vẫn hơi phức tạp để sử dụng. Về cơ bản, có nhiều khả năng bị nhầm lẫn và phát sinh lỗi nếu có nhiều trang tính Do đó, chúng ta sẽ tạo một công thức khác bằng cách sử dụng các hàm INDIRECT, INDEX, MATCH và COUNTIF trông phức tạp hơn nhưng tương đối dễ áp dụng hơn khi có nhiều trang tính
Phân tích công thức
Tại đây, bạn có thể sử dụng công thức tương tự để tìm điểm Viva của ứng viên
Cuối cùng, chúng tôi đã có cả điểm viết và điểm viva của tất cả các ứng viên. Hơn nữa, những người chưa tìm thấy tên đã được đánh dấu là vắng mặt Đọc thêm. Hàm INDEX MATCH vs VLOOKUP (9 ví dụ) 4. Công thức VLOOKUP với số chỉ mục cột độngCho đến bây giờ, để trích xuất các điểm trong bài kiểm tra viết, chúng tôi đang sử dụng col_index_num là 2. Và đối với điểm viva, 3 Trên thực tế, chúng tôi đang chèn các công thức riêng biệt vào cả hai cột Cuối cùng, khi chúng ta có nhiều cột, sẽ khá rắc rối khi chèn công thức vào tất cả các cột một cách riêng biệt Vì vậy, lần này chúng ta sẽ tạo một công thức để có thể chèn công thức vào cột đầu tiên và kéo nó vào tất cả các cột thông qua biểu tượng Fill Handle Giản dị. Thay vì chèn một số thuần túy làm col_index_num, hãy chèn COLUMNS($C$1. D1) nếu công thức ở cột C (Dành cho Điểm Viết) Sau đó, nó sẽ trả về 2 Sau đó, nếu chúng ta kéo nó vào cột E, nó sẽ trở thành COLUMNS($C$1. E1) và trả về 3. Và như thế
Cuối cùng, bạn sẽ thấy cả điểm viết và viva cho tất cả các ứng viên Để ý. Ở đây, chúng tôi đã thay đổi lookup_value từ B5 thành $B5, để nó không thay đổi khi kéo sang phải, nhưng thay đổi khi kéo xuống Đọc thêm. Hàm VLOOKUP động trong Excel (có 3 công thức) 5. Công thức VLOOKUP với các hàm kết hợp trong ExcelỞ đây, chúng ta sẽ sử dụng một công thức VLOOKUP khác trong Excel với nhiều sheet bỏ qua hàm IFERROR. Vì vậy, hãy xem các bước được đưa ra dưới đây bước
________số 8
Cuối cùng, bạn sẽ thấy cả điểm viết và điểm viva của tất cả các ứng viên. Ngoài ra, bạn sẽ thấy lỗi #N/A khi thiếu tên trong các trang tính được đề cập Hạn chế của hàm VLOOKUP và một số giải pháp thay thế trong Excel
Cách áp dụng công thức VLOOKUP trong Excel với nhiều sổ làm việcTrong phần này, chúng ta sẽ thảo luận về cách áp dụng công thức VLOOKUP trong Excel với nhiều sổ làm việc. Bây giờ, hãy tạo sổ làm việc sau có tên Mock Test Marks. Ngoài ra, trong sổ làm việc đó, cũng có ba trang tính. Đó là Tuần 1, Tuần 2 và Tuần 3 Tại thời điểm này, chúng tôi muốn so sánh các điểm viết sơ bộ và cuối cùng mà các ứng cử viên đạt được. Lúc đầu, chúng tôi tìm thấy các điểm viết cuối cùng. Tại đây, bạn có thể tìm thấy điều đó bằng cách làm theo bất kỳ phương pháp nào trước đó. Bây giờ, chúng tôi sẽ trích xuất các điểm viết sơ bộ từ một sổ làm việc khác
Ở đây, khi sử dụng công thức này, bạn phải mở cả hai sổ làm việc. Nếu không, bạn phải sử dụng đường dẫn/vị trí điền thay vì chỉ sử dụng tên tệp
Cuối cùng, bạn sẽ thấy cả điểm viết cuối cùng và sơ bộ cho tất cả các ứng viên phần thực hànhBây giờ, bạn có thể tự mình thực hành phương pháp được giải thích Sự kết luậnSử dụng các cách này, bạn có thể sử dụng hàm VLOOKUP của Excel làm công thức để trích xuất dữ liệu từ nhiều sheet trong workbook. Vì vậy, bạn có câu hỏi nào khác không? |
