Bạn có biết cách thực hiện Left Join hai bảng trong Google Sheets không? Nếu chưa, hãy theo dõi bài viết này để tìm hiểu cách sử dụng một công thức mảng đơn giản nhưng rất mạnh mẽ để kết hợp hai bảng lại với nhau. Chúng ta sẽ sử dụng một công thức LET để đặt tên cho các phép toán và xác định các dãy dữ liệu cần thiết từ hai bảng. Hãy cùng khám phá công thức và cách áp dụng nó trong Google Sheets!
Sử dụng công thức mảng để Left Join hai bảng trong Google Sheets
Để thực hiện Left Join hai bảng trong Google Sheets, chúng ta sẽ sử dụng một công thức mảng. Dù công thức này có vẻ phức tạp, việc tùy chỉnh nó cho các dãy dữ liệu cụ thể của bạn lại rất đơn giản.
Trong phép kết hợp Left Join, hai bảng sẽ được kết hợp thành một bảng mới. Bảng mới sẽ giữ lại tất cả các hàng từ bảng đầu tiên (bảng trái) và kết hợp các hàng trùng khớp từ bảng thứ hai (bảng phải) dựa trên một trường chung (ví dụ: ID).
Một điều quan trọng khi thực hiện Left Join hai bảng là cần phải có một trường chung trong cả hai bảng, ví dụ như ID (ví dụ: ID nhân viên, ID sản phẩm).
Lưu ý: Nếu bạn có các ID trùng lặp trong cột ID của cả hai bảng, hãy tham khảo hướng dẫn này: Conquer Duplicate IDs: Master Left, Right, Inner, & Full Joins in Google Sheets.
Bạn đã sẵn sàng khám phá khả năng của Left Join chưa? Tiếp tục đọc phần tiếp theo để tìm hiểu công thức chi tiết và cách áp dụng nó!
Cách thực hiện Left Join hai bảng: Bảng mẫu và Bảng kết quả
Thứ tự Left Join hai bảng thường chỉ yêu cầu sử dụng công thức VLOOKUP trong Google Sheets. Tuy nhiên, chúng ta sẽ kết hợp thêm các hàm khác để dễ dàng thích nghi với các bảng có kích thước khác nhau.
Bảng 1: Đơn hàng
| Order_ID | Customer_Name | Product_ID | Quantity_Cum |
|---|---|---|---|
Bảng này chứa 6 bản ghi (6 đơn hàng) và bốn cột, với Product_ID trong cột thứ ba định danh duy nhất cho mỗi sản phẩm.
Bảng 2: Sản phẩm
| Product ID | Product Name | Price_Cum |
|---|---|---|
Bảng này chứa 4 bản ghi (4 sản phẩm) và ba cột, với Product_ID trong cột đầu tiên định danh duy nhất cho mỗi sản phẩm. Chú ý rằng trong bảng này, các định danh duy nhất (Product IDs) trong cột đầu tiên không được lặp lại.
Kết quả dự kiến:
| Order ID | Customer Name | Product ID | Quantity_Cum | Product Name | Price_Cum |
|---|---|---|---|---|---|
Hãy cuộn xuống để xem ảnh chụp màn hình của các bảng trên Google Sheets.
Công thức Array cho Left Join hai bảng trong Google Sheets
Công thức này thực hiện phép Left Join giữa hai bảng trong Google Sheets, giữ lại tất cả các bản ghi từ bảng trái. Nó giả định rằng các bản ghi duy nhất tồn tại trong cột ID của bảng phải.
Công thức:
=ArrayFormula( LET( lt, A2:D8, lt_id, C2:C8, rt, F2:H6, rt_id, F2:F6, look_up, IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0)), HSTACK(lt, look_up) ) )
Bạn có thể thay thế phần biểu thức công thức, tức là HSTACK(lt, look_up) trong công thức, bằng CHOOSECOLS(HSTACK(lt, look_up), {1, 2, 3, 4, 6, 7}) để chọn các cột cần thiết và cũng theo đúng thứ tự bạn muốn.
Dưới đây là cú pháp của hàm CHOOSECOLS:
CHOOSECOLS(array, [col_num1, …])Lưu ý: Công thức này có thể chuyển đổi các ngày thành giá trị ngày. Do đó, nếu bất kỳ cột nào trong bảng trái hoặc bảng phải chứa trường ngày, trường tương ứng trong bảng kết quả phải được định dạng như là kiểu ngày sử dụng Format > Number > Date.
Bạn có thể tìm công thức mảng Left Join trong ô A12 của trang thứ hai trong ví dụ bảng mẫu được cung cấp dưới đây.
Cách thức hoạt động của công thức
Công thức sử dụng hàm LET để gán tên cho các kết quả biểu thức và trả về kết quả của biểu thức công thức, biểu thị bảng kết quả từ phép Left Join.
Cú pháp của hàm LET:
LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)Phân công của hàm LET:
lt(name1): Xác định dãy dữ liệu của bảng trái, bao gồm cả hàng tiêu đề.A2:D8(value_expression1): Dãy dữ liệu của bảng trái.
lt_id(name2): Xác định cột ID của bảng trái, bao gồm cả hàng tiêu đề.C2:C8(value_expression2): Cột ID của bảng trái.
rt(name3): Xác định dãy dữ liệu của bảng phải, bao gồm cả hàng tiêu đề.F2:H6(value_expression3): Dãy dữ liệu của bảng phải.
rt_id(name4): Xác định cột ID của bảng phải, bao gồm cả hàng tiêu đề.F2:F6(value_expression4): Cột ID của bảng phải.
Lưu ý: Bạn chỉ cần xác định các giá trị biểu thức từ 1 đến 4 liên quan đến các bảng của bạn. Công thức Array của phép Left Join sẽ tự động làm phần còn lại.
Phân công LET còn lại:
look_up(name5):IFNA(VLOOKUP(lt_id, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt), 2), 0))(value_expression5): Hàm VLOOKUP sẽ tìm kiếm các ID từ bảng trái (lt_id) trong cột ID của bảng phải (rt_id) và trả về các bản ghi trùng khớp từ bảng phải (rt).- HSTACK: Ghép cột ID của bảng phải (rt_id) với toàn bộ dữ liệu của bảng phải (rt).
- SEQUENCE: Tạo ra một dãy số đại diện cho vị trí cột trong bảng phải (từ 2 đến số cột vì cột đầu tiên là cột rt_id đã được ghép).
- Đối số cuối cùng (0) chỉ định một trùng khớp chính xác.
- Hàm VLOOKUP trả về các bản ghi trùng khớp từ bảng phải cho mỗi ID trong bảng trái.
- Công thức biểu thức:
HSTACK(lt, look_up): Đoạn này sẽ kết hợp dữ liệu từ bảng trái (lt) với kết quả tìm kiếm (look_up). Tuy nhiên, quan trọng để lưu ý rằng kết quả tìm kiếm chỉ bao gồm các bản ghi trùng khớp từ bảng phải, không phải toàn bộ bảng phải. Sự khác biệt này rất quan trọng vì nó thực hiện chính xác chức năng Left Join đã định sẵn.
Kết luận
Bây giờ bạn đã sở hữu một công cụ mạnh mẽ để thực hiện phép Left Join trong Google Sheets. Bạn có thể điều chỉnh bảng kết hợp này bằng cách sử dụng QUERY, cho phép bạn thực hiện các hành động như sắp xếp cột, sắp xếp dữ liệu và tổng hợp.
Để khám phá thêm về khả năng của hàm QUERY, bạn có thể tìm thấy nhiều hướng dẫn trong blog của chúng tôi. Những nguồn tài nguyên này sẽ hướng dẫn bạn qua cú pháp và cung cấp các ví dụ thực tế để giúp bạn nắm vững chức năng linh hoạt này.
Tài liệu tham khảo:
