Tìm hiểu cách sử dụng Excel hàm XLOOKUP với hai tờ trong hướng dẫn từng bước này.
Tại sao bạn có thể muốn sử dụng XLOOKUP với hai trang tính?
Nếu bạn có dữ liệu ở hai nơi (trang tính hoặc sổ làm việc Excel) như được mô tả ở trên, bạn có thể muốn sử dụng XLOOKUP để kết hợp dữ liệu từ cả hai nơi để có được bức tranh đầy đủ. Ví dụ,
- Danh sách sinh viên ở sheet1, bảng giá khóa học ở sheet2, bạn muốn biết giá các khóa học so với tên sinh viên ở sheet1
- Bảng kê hóa đơn ở sheet 1, bảng kê thanh toán ở sheet 2, bạn cần biết hóa đơn nào đã được thanh toán (đối chiếu)
- Danh sách thiết bị ở sheet1, chi tiết kiểm tra ở sheet2, bạn muốn biết ngày kiểm tra cuối cùng của từng thiết bị là khi nào
Bạn cần gì?
- Bạn cần hai tờ dữ liệu.
- hoặc nếu dữ liệu ở 2 file Excel riêng biệt thì mở cả 2 file.
- Nếu bạn cần một tệp dữ liệu mẫu, hãy lấy mẫu hai tờ xlookup miễn phí.
XLOOKUP với hai trang tính (hướng dẫn từng bước)
Bước 1: Xác định cột chung giữa 2 sheet
Ví dụ: trong trường hợp trên, “Học phí” là cột chung giữa trang 1 (danh sách sinh viên) và trang 2 (danh sách khóa học).
Nếu tôi có nhiều hơn 1 cột chung thì sao?
Tôi sẽ giải thích quy trình cho sửa đổi XLOOKUP để hoạt động với nhiều cột tiếp tục xuống trang. Đọc tiếp.
Bước 2: Viết công thức XLOOKUP
Đi tới trang tính mà bạn muốn lấy dữ liệu từ trang tính “khác” và viết hàm XLOOKUP bằng mẫu bên dưới.
=XLOOKUP(
all cells in first sheet,
common column in second sheet,
column you want in second sheet,
optional output for missing values
)ví dụ: trong trường hợp sinh viên và lệ phí, chúng tôi sẽ sử dụng hàm XLOOKUP bên dưới.
=XLOOKUP(C4:C43,
Courses!B4:B15,
Courses!D4:D15
)Như đã trình bày ở trên, xlookup có thể tự động đổ giá trị cho tất cả các hàng dựa trên cột chung mà bạn đã chỉ định. Không cần phải viết hoặc kéo các công thức riêng lẻ. Bạn cũng không cần phải “khóa” tài liệu tham khảo của mình bằng kiểu công thức này. Tìm hiểu thêm về chức năng tràn và hành vi mảng động của Excel tại đây.
Sử dụng bảng? Không chọn cột đầy đủ ở sheet 1
Nếu bạn đang sử dụng Bảng Excel cho dữ liệu của mình, chỉ cần chọn ô hiện tại ở hàng đầu tiên nhưng chọn cột chung và cột bạn muốn trong trang tính 2. Excel sẽ tự động điền công thức cho bạn.
Ví dụ: cùng một công thức với các bảng có thể trông như thế này:
=XLOOKUP([@Course Name], courses[name], courses[fee])Nếu tôi có nhiều hơn một cột chung thì sao? (XLOOKUP nhiều tiêu chí)
Giả sử bạn có mức phí khác nhau cho mỗi khóa học dựa trên vị trí của sinh viên (ví dụ: Trong tiểu bang là $600, ngoài tiểu bang là $900). Một cái gì đó như thế này:
Trong những trường hợp như vậy, dữ liệu về học sinh của bạn cũng sẽ có cả cột “mã khóa học” và “loại sinh viên”. một cái gì đó như thế này:
Như bạn có thể thấy, XLOOKUP của chúng tôi cần kiểm tra cả hai cột này để tìm ra mức phí chính xác cho mỗi hàng.
XLOOKUP với hai trang tính, nhiều cột (hướng dẫn từng bước)
Bước 1: Xác định các cột chung giữa các sheet
Trong trường hợp của chúng tôi, các cột phổ biến là,
- Mã khóa học (cột C ở trang 1, cột B ở trang 2)
- Loại sinh viên (cột D ở trang 1, cột C ở trang 2)
Bước 2: Viết XLOOKUP đa tiêu chí
Thay vì tìm kiếm một giá trị cột cụ thể, chúng ta bắt đầu xlookup với 1 và xây dựng một mảng kiểm tra “boolean”. Công thức trông như thế này:
=XLOOKUP(1,
('Courses NEW'!$B$4:$B$27=Students!C4)*('Courses NEW'!$C$4:$C$27=Students!D4),
'Courses NEW'!$D$4:$D$27)Giải thích công thức:
- Chúng ta bắt đầu tra cứu với 1. Tôi sẽ giải thích đây là gì trong giây lát.
- Mảng tra cứu có nhiều phần, mỗi phần trên một cột chung. Vì chúng tôi có hai cột để khớp (mã khóa học và loại sinh viên), nên chúng tôi có hai phần ở đây.
- Phần 1: (‘Courses NEW’!$B$4:$B$27=Students!C4) kiểm tra xem khóa học nào ở cột B của bảng 2 (cột mã khóa học) khớp với khóa học của hồ sơ học sinh hiện tại (cột C của bảng 1)
- Phần 2: (‘Courses NEW’!$C$4:$C$27=Students!D4) cũng làm tương tự, nhưng đối với loại sinh viên
- Phần 1 * Phần 2: khi nhân cả hai phép kiểm tra này, chúng ta thu được một mảng 0 và 1. ví dụ: nó sẽ trông như thế này: {0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
- Mảng trả về chỉ là cột phí của sheet 2
- Khi chúng tôi đang tìm kiếm 1, xlookup khớp với 1 trong Phần 1*Phần 2 và trả về khoản phí tương ứng.
Có liên quan: tìm hiểu thêm về ý nghĩa và cách thức của logic nhân boolean này trong Excel
Đồng hồ – XLOOKUP với nhiều tiêu chí được giải thích nhanh chóng
Mẫu công thức chung cho bất kỳ số cột phổ biến nào với XLOOKUP
Sử dụng mẫu này và điều chỉnh mọi thứ theo dữ liệu của bạn để khớp với bất kỳ số cột chung nào
=XLOOKUP(
1,
(COLUMN 1 in second sheet = value 1 first sheet) *
(COLUMN 2 in second sheet = value 2 first sheet) *
(COLUMN 3 in second sheet = value 3 first sheet) *
(COLUMN 4 in second sheet = value 4 first sheet),
COLUMN YOU WANT TO GET IN SECOND SHEET,
OPTIONAL value for missing cases
)
Điều gì sẽ xảy ra nếu tôi có dữ liệu trong hai sổ làm việc riêng biệt (tệp Excel) thay vì trang tính
Quá trình này hoàn toàn giống như hai tờ giấy. Bạn chỉ cần giữ cả hai tập tin MỞ để XLOOKUP hoạt động. Nếu bạn đóng tệp thứ hai (tệp có phí trong ví dụ này), công thức trong sổ làm việc đầu tiên sẽ hoạt động miễn là bạn không chạm vào nó hoặc tính toán lại sổ làm việc (F9). Lúc đó nó sẽ báo lỗi và yêu cầu bạn mở file.
Các lựa chọn thay thế cho XLOOKUP cho kết hợp dữ liệu từ hai nơi
Mặc dù xlookup rất tuyệt nhưng bạn cũng có thể sử dụng các lựa chọn thay thế bên dưới để lấy dữ liệu từ nơi khác.
Sở thích của tôi:
Đối với các tình huống đơn giản và phân tích nhanh, tôi thích sử dụng XLOOKUP hoặc VLOOKUP để kết hợp nhanh chóng dữ liệu như thế này.
Nhưng nếu dữ liệu đến từ hai tệp riêng biệt (sổ làm việc hoặc thậm chí danh sách điểm chia sẻ, v.v.), thì tôi sử dụng Power Query. Nó mang lại cho tôi sự linh hoạt và nhiều lựa chọn hơn. Tham khảo của tôi Trang hướng dẫn Power Query để biết thêm ví dụ thú vị về những gì tính năng mạnh mẽ này có thể mang lại cho bạn.
Phần thưởng: XLOOKUP với hai trang tính: Sổ làm việc mẫu
Nếu bạn cần giúp đỡ với các công thức được giải thích ở trên, tải xuống mẫu hai trang XLOOKUP miễn phí của tôi và tham khảo các công thức ở cột E & F. Hãy cho tôi biết nếu bạn có bất kỳ câu hỏi nào bằng cách để lại nhận xét.