Bài viết sẽ cung cấp cho bạn một số mẹo cơ bản về cách gộp tệp Excel dựa trên cột. Đôi khi, chúng ta có thể có thông tin khác nhau về cùng những người hoặc mặt hàng trong các sổ làm việc Excel khác nhau. Do đó, chúng ta có thể yêu cầu gộp thông tin đó vào một trang tính Excel. Trong bài viết này, chúng ta có dữ liệu về tên và chức danh (designation) của một số người trong một Sổ làm việc Excel và tên và lương (salaries) của họ trong sổ làm việc khác. Chúng ta sẽ hiển thị tên, chức danh và lương của họ trong một trang tính duy nhất.
Hình ảnh sau hiển thị tên và chức danh tương ứng mà chúng ta lưu trong tệp tên Merge Files.
Và hình này hiển thị tên và lương trong tệp tên Merge Files (lookup).
3 cách gộp tệp Excel dựa trên cột
1. Sử dụng hàm VLOOKUP của Excel để gộp tệp dựa trên cột
Áp dụng Hàm VLOOKUP là cách rất hiệu quả để gộp tệp Excel dựa trên một cột. Ở đây, chúng ta sẽ mang cột Lương (Salary) từ tệp Merge Files (lookup) và đặt nó vào tệp tên Merge Files. Hãy xem quy trình dưới đây.
Các bước:
- Đầu tiên, tạo một cột cho lương trong Merge Files và nhập công thức sau vào ô D5 của tệp đó.
=VLOOKUP($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$C$11,2,FALSE)
Ở đây, Hàm VLOOKUP tìm giá trị trong ô B5, tìm giá trị này trong phạm vi B5:C11 của tệp Merge Files (lookup) (nhớ rằng chúng ta phải dùng Tham chiếu ô tuyệt đối) và trả về lương tương ứng cho người ở ô B5. Chúng ta đặt số chỉ mục cột là 2 vì lương ở cột thứ 2. Chúng ta muốn khớp chính xác tên nên chọn FALSE.
- Nhấn nút ENTER và bạn sẽ thấy lương của Jason Campbell có tên ở ô B5.
- Sau đó, dùng Fill Handle (Chân điền) để Tự động điền (AutoFill) các ô dưới.
Vậy bạn có thể gộp tệp Excel dựa trên cột bằng Hàm VLOOKUP.
Đọc thêm: Cách gộp tệp Excel vào nhãn thư (Với các bước dễ dàng)
2. Gộp tệp Excel dựa trên cột với hàm INDEX và MATCH
Chúng ta cũng có thể dùng kết hợp INDEX và MATCH Hàm để gộp tệp Excel dựa trên một cột. Ở đây, chúng ta sẽ mang cột Lương từ tệp Merge Files (lookup) và đặt vào tệp tên Merge Files. Hãy xem quy trình dưới đây.
Các bước:
- Đầu tiên, tạo một cột cho lương trong Merge Files và nhập công thức sau vào ô D5 của tệp đó.
=INDEX('[Merge Files (lookup).xlsx]lookup'!$C$5:$C$11,MATCH($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$B$11,0))
Ở đây, Hàm MATCH tra cứu giá trị trong ô B5 và trả về số hàng từ tệp Merge Files (lookup) cho giá trị tương ứng của B5. Sau đó Hàm INDEX trả về Lương liên quan từ phạm vi C5:C11 trong tệp Merge Files (lookup). Hãy nhớ dùng Tham chiếu ô tuyệt đối, nếu không bạn sẽ gặp lỗi bất ngờ.
- Nhấn nút ENTER và bạn sẽ thấy lương của Jason Campbell có tên ở ô B5.
- Sau đó, dùng Fill Handle (Chân điền) để Tự động điền (AutoFill) các ô dưới.
Vậy bạn có thể gộp tệp Excel dựa trên cột bằng Hàm INDEX và MATCH.
Đọc thêm: Cách gộp tệp Excel thành một bằng CMD (4 bước)
Bài đọc tương tự
- Cách kết hợp nhiều trang tính thành một sổ làm việc
- Cách gộp tệp Excel vào tài liệu Word
3. Áp dụng Trình soạn thảo Power Query để gộp tệp Excel dựa trên cột
Nếu bạn thấy dùng công thức hơi khó khăn, bạn có thể dùng Trình soạn thảo Power Query (Power Query Editor) từ thẻ Data Tab (Thẻ Dữ liệu) để gộp tệp dựa trên cột. Chỉ làm theo quy trình dưới đây.
Các bước:
- Mở trang tính mới và chọn Data >> Get Data >> From File >> From Excel Workbook
- Cửa sổ Import Data (Nhập dữ liệu) sẽ xuất hiện, Chọn Merge File và Open
- Sau đó cửa sổ Navigator (Trình điều hướng) sẽ hiện lên. Chọn power query vì chúng ta lưu tên và chức danh trong trang tính này của tệp tên Merge Files.
- Chọn Load >> Load To
- Bạn sẽ thấy một hộp thoại. Chọn Only Create Connection (Chỉ tạo kết nối) và nhấp Ok.
Thao tác này sẽ thêm trang tính power query từ tệp Merge File trong phần Queries & Connections (Truy vấn & Kết nối).
- Sau đó lại chọn Data >> Get Data >> From File >> From Excel Workbook
- Cửa sổ Import Data sẽ xuất hiện, Chọn Merge Files (lookup) và Open
- Sau đó cửa sổ Navigator sẽ hiện lên. Chọn salary vì chúng ta lưu tên và lương trong trang tính này của tệp tên Merge Files (lookup).
- Chọn Load >> Load To
- Bạn sẽ thấy một hộp thoại. Chọn Only Create Connection và nhấp Ok.
Thao tác này sẽ thêm trang tính salary từ tệp Merge Files (lookup) trong phần Queries & Connections.
- Bây giờ, chọn Data >> Get Data >> Combine Queries >> Merge
- Sau đó cửa sổ Merge (Gộp) sẽ xuất hiện. Chọn power query từ biểu tượng thả xuống thứ nhất và salary từ biểu tượng thả xuống thứ hai.
- Nhấp vào cột Name (Tên) của cả hai truy vấn (queries).
- Nhấp Ok.
Bảng sau sẽ xuất hiện trong Trình soạn thảo Power Query.
- Nhấp vào biểu tượng được đánh dấu trong cột salary và chọn Salary.
- Sau đó nhấp Ok.
Bạn sẽ thấy Tên, Chức danh và Lương cùng nhau trong Trình soạn thảo Power Query.
- Sau đó, chọn Close & Load (Đóng & Tải).
Thao tác này sẽ hiển thị thông tin trong một Bảng Excel mới trong một trang tính mới.
Vậy, bạn có thể gộp tệp Excel dựa trên cột bằng Trình soạn thảo Power Query.
Đọc thêm: Cách gộp nhiều tệp Excel thành một trang tính bằng VBA (3 tiêu chí)
Phần thực hành
Ở đây, tôi trình bày cho bạn tập dữ liệu của bài viết này để bạn có thể tự thực hành.
Kết luận
Cuối cùng, bài viết này chỉ cho bạn một số phương pháp dễ dàng về cách gộp tệp Excel dựa trên cột. Nếu bạn nhập dữ liệu thủ công, điều đó sẽ tốn nhiều thời gian và phiền toái. Đó là lý do chúng tôi phát triển công thức và lệnh để gộp tệp Excel dựa trên cột. Nếu bạn có ý tưởng hay hoặc phản hồi tốt hơn, vui lòng chia sẻ với tôi trong hộp bình luận. Những suy nghĩ quý giá của bạn sẽ giúp tôi làm giàu các bài viết sắp tới.
Các bài viết liên quan
- Cách so sánh và gộp sổ làm việc Excel (3 bước dễ dàng)
- Gộp nhiều tệp Excel thành một trang tính (4 phương pháp)
- Cách kết hợp nhiều sổ làm việc thành một sổ làm việc trong Excel (6 cách)