Bài viết sẽ cung cấp cho bạn một số mẹo cơ bản về cách hợp nhất các tệp Excel dựa trên cột . Đôi khi, chúng tôi có thể có thông tin khác nhau về những người hoặc mục giống nhau trong các sổ làm việc Excel khác nhau. Do đó, chúng tôi có thể yêu cầu hợp nhất thông tin đó trong một trang tính Excel. Trong bài viết này, chúng tôi có dữ liệu về tên của một số người và chỉ định của họ trong một Sổ làm việc Excel và tên của chúng và tiền lương trong một sổ làm việc khác. Chúng tôi sẽ hiển thị tên của họ , chỉ định và tiền lương trong một trang tính .
Hình ảnh sau đây hiển thị tên và chỉ định tương ứng mà chúng tôi đã lưu trong một tệp có tên Hợp nhất các tệp .
Và hình này hiển thị tên và tiền lương trong tệp có tên Hợp nhất tệp (tra cứu) .
3 cách hợp nhất các tệp Excel dựa trên cột
1. Sử dụng hàm VLOOKUP trong Excel để hợp nhất tệp dựa trên cột
Áp dụng Hàm VLOOKUP là một cách rất hiệu quả để hợp nhất các tệp Excel dựa trên một cột. Ở đây, chúng tôi sẽ mang đến cột tiền lương từ Hợp nhất tệp (tra cứu) và đặt nó vào tệp có tên Hợp nhất tệp . Hãy xem qua quy trình bên dưới.
Các bước:
- Đầu tiên, hãy tạo một cột cho tiền lương trong Hợp nhất tệp 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 kiếm giá trị trong ô B5 , tìm kiếm giá trị này trong phạm vi B5:C11 của Hợp nhất tệp (tra cứu) (hãy nhớ rằng chúng tôi phải sử dụng Tham chiếu ô tuyệt đối ) và trả về mức lương tương ứng cho anh chàng trong ô B5 . Chúng tôi đặt số chỉ mục cột như 2 vì tiền lương nằm trong cột thứ 2 . Chúng tôi muốn kết hợp chính xác tên vì vậy chúng tôi đã chọn FALSE .
- Nhấn nút ENTER và bạn sẽ thấy tiền lương của Jason Campbell có tên trong ô B5 .
- Sau đó, sử dụng Xử lý điền sang Tự động điền ô thấp hơn.
Do đó, bạn có thể hợp nhất các tệp excel dựa trên một cột bằng cách sử dụng Hàm VLOOKUP .
Đọc thêm: Cách hợp nhất tệp Excel vào nhãn gửi thư (Với các bước dễ dàng)
2. Hợp nhất các tệp Excel dựa trên cột với các hàm INDEX và MATCH
Chúng tôi cũng có thể sử dụng kết hợp INDEX và MATCH Chức năng để hợp nhất các tệp Excel dựa trên một cột. Ở đây, chúng tôi sẽ mang đến cột tiền lương từ Hợp nhất tệp (tra cứu) và đặt nó vào tệp có tên Hợp nhất tệp . Hãy xem qua quy trình bên dưới.
Các bước:
- Đầu tiên, hãy tạo một cột cho tiền lương trong Hợp nhất tệp 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 tìm kiếm giá trị trong ô B5 và trả về số hàng từ Hợp nhất tệp (tra cứu) tệp cho giá trị tương ứng của B5 . Sau đó đến Hàm INDEX trả về Mức lương có liên quan từ phạm vi C5:C11 trong Hợp nhất tệp (tra cứu) tập tin. Hãy nhớ rằng bạn nên sử dụng Tham chiếu ô tuyệt đối , nếu không, bạn phải đối mặt với các lỗi không mong muốn.
- Nhấn nút ENTER và bạn sẽ thấy tiền lương của Jason Campbell có tên trong ô B5 .
- Sau đó, sử dụng Xử lý điền sang Tự động điền ô thấp hơn.
Do đó, bạn có thể hợp nhất các tệp excel dựa trên một cột bằng cách sử dụng INDEX và Hàm MATCH .
Đọc thêm: Cách hợp nhất các tệp Excel thành một tệp bằng CMD (4 bước)
Bài đọc tương tự
- Cách kết hợp nhiều trang tính vào một sổ làm việc
- Cách hợp nhất tệp Excel vào tài liệu Word
3. Áp dụng Power Query Editor để hợp nhất các tệp Excel dựa trên cột
Nếu bạn thấy việc sử dụng (các) công thức hơi khó khăn, bạn có thể sử dụng Power Query Editor từ Tab Dữ liệu để hợp nhất các tệp dựa trên cột . Chỉ cần làm theo quy trình bên dưới.
Các bước:
- Mở một trang tính mới và chọn Dữ liệu >> Nhận dữ liệu >> Từ FIle >> Từ Sổ làm việc Excel
- Nhập dữ liệu cửa sổ sẽ xuất hiện, Chọn Hợp nhất tệp và Mở
- Sau đó là Điều hướng cửa sổ sẽ hiển thị. Chọn truy vấn nguồn khi chúng tôi lưu tên và chỉ định trong trang tính này của tệp có tên Hợp nhất tệp .
- Chọn Tải >> Tải lên
- Bạn sẽ thấy một hộp thoại . Chọn Chỉ tạo kết nối và nhấp vào Ok .
Thao tác này sẽ thêm bảng truy vấn nguồn từ Tệp hợp nhất trong Truy vấn &Kết nối phần.
- Sau đó, chọn lại Dữ liệu >> Nhận dữ liệu >> Từ FIle >> Từ Sổ làm việc Excel
- Nhập dữ liệu cửa sổ sẽ xuất hiện, Chọn Hợp nhất tệp (tra cứu) và Mở
- Sau đó là Điều hướng cửa sổ sẽ hiển thị. Chọn mức lương khi chúng tôi lưu tên và tiền lương trong trang tính này của tệp có tên Hợp nhất tệp (tra cứu) .
- Chọn Tải >> Tải lên
- Bạn sẽ thấy một hộp thoại . Chọn Chỉ tạo kết nối và nhấp vào Ok .
Thao tác này sẽ thêm bảng lương từ Hợp nhất tệp (tra cứu) trong Truy vấn &Kết nối phần.
- Bây giờ, chọn Dữ liệu >> Nhận dữ liệu >> Kết hợp các truy vấn >> Hợp nhất
- Sau đó, Hợp nhất cửa sổ sẽ xuất hiện. Chọn truy vấn nguồn từ biểu tượng thả xuống đầu tiên và tiền lương từ biểu tượng thả xuống thứ hai .
- Nhấp vào Cột tên của cả truy vấn .
- Nhấp vào Ok .
Bảng sau sẽ xuất hiện trong Power Query Editor .
- Nhấp vào biểu tượng được đánh dấu trong cột lương và chọn Mức lương .
- Sau đó, nhấp vào Ok .
Bạn sẽ thấy Tên , Chỉ định và Mức lương cùng nhau trong Power Query Editor .
- Sau đó, chọn Đóng &Tải .
Thao tác này sẽ hiển thị thông tin trong Bảng Excel mới trong trang tính mới .
Do đó, bạn có thể hợp nhất các tệp Excel dựa trên cột bằng cách sử dụng Power Query Editor .
Đọc thêm: Cách hợp nhất 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 giới thiệu cho bạn bộ 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 hợp nhất các tệp Excel dựa trên cột . Nếu nhập dữ liệu theo cách thủ công, điều này sẽ khiến bạn mất nhiều thời gian và bất tiện. Đó là lý do tại sao chúng tôi đã phát triển (các) công thức và lệnh để hợp nhất các tệp excel dựa trên cột . Nếu bạn có bất kỳ ý tưởng hoặc phản hồi nào tốt hơn, hãy chia sẻ với tôi trong khung bình luận. Những suy nghĩ có giá trị của bạn sẽ giúp tôi làm phong phú thêm các bài viết sắp tới của mình.
Các bài viết có liên quan
- Cách So sánh và Hợp nhất Sổ làm việc Excel (3 bước Đơn giản)
- Hợp nhất 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)