Kết hợp dựa trên cột chung - Mẹo Excel

David từ Florida đặt câu hỏi hôm nay:

Tôi có hai sổ làm việc. Cả hai đều có cùng dữ liệu trong cột A, nhưng các cột còn lại khác nhau. Làm cách nào để hợp nhất hai sổ làm việc đó?

Tôi hỏi David liệu có thể một sổ làm việc có nhiều bản ghi hơn sổ kia không. Và câu trả lời là có. Tôi đã hỏi David nếu trường khóa chỉ xuất hiện một lần trong mỗi tệp. Câu trả lời cũng là có. Hôm nay, tôi sẽ giải quyết vấn đề này với Power Query. Công cụ Power Query được tìm thấy trong các phiên bản Windows của Excel 2016+ trong phần Lấy & Chuyển đổi của tab Dữ liệu. Nếu bạn có phiên bản Windows của Excel 2010 hoặc Excel 2013, bạn có thể tải xuống bổ trợ Power Query cho các phiên bản đó.

Đây là sổ làm việc của David 1. Nó có Sản phẩm và sau đó là ba cột dữ liệu.

Sổ làm việc đầu tiên

Đây là sổ làm việc của David 2. Nó có Mã sản phẩm và sau đó là các cột khác. Trong ví dụ này, có các sản phẩm bổ sung trong workbook2, nhưng các giải pháp sẽ hoạt động nếu một trong hai sổ làm việc có thêm cột.

Sổ làm việc thứ hai

Đây là các bước:

  1. Chọn dữ liệu, lấy dữ liệu, từ tệp, từ sổ làm việc:

    Tải dữ liệu từ tệp
  2. Duyệt đến sổ làm việc đầu tiên và bấm OK
  3. Trong hộp thoại Bộ điều hướng, hãy chọn trang tính ở bên trái. (Ngay cả khi chỉ có một trang tính, bạn phải chọn nó.) Bạn sẽ thấy dữ liệu ở bên phải.
  4. Trong hộp thoại Bộ điều hướng, mở menu thả xuống Tải và chọn Tải đến…
  5. Chọn Chỉ Tạo kết nối và nhấn OK.

  6. Lặp lại các bước 1-5 cho sổ làm việc thứ hai.

    Tạo kết nối với sổ làm việc

    Nếu bạn đã thực hiện cả hai sổ làm việc, bạn sẽ thấy hai kết nối trên Bảng điều khiển Truy vấn & Kết nối ở bên phải màn hình Excel của bạn.

    Kết nối với cả hai sổ làm việc

    Tiếp tục với các bước để hợp nhất các sổ làm việc:

  7. Dữ liệu, Lấy dữ liệu, Kết hợp truy vấn, Hợp nhất.

    Hợp nhất hai truy vấn có các cột khác nhau
  8. Từ trên cùng thả xuống trong hộp thoại Hợp nhất, hãy chọn truy vấn đầu tiên.
  9. Từ menu thả xuống thứ hai trong hộp thoại Hợp nhất, hãy chọn truy vấn thứ hai.
  10. Nhấp vào tiêu đề Sản phẩm trong bản xem trước trên cùng (đây là trường khóa. Lưu ý rằng bạn có thể chọn nhiều hoặc nhiều trường khóa bằng cách Ctrl + Nhấp)
  11. Nhấp vào tiêu đề Mã sản phẩm trong bản xem trước thứ hai.

  12. Mở Loại Tham gia và chọn Bên ngoài Toàn bộ (Tất cả Hàng Từ Cả hai)

    Các bước 8-12 được minh họa ở đây

  13. Bấm OK. Bản xem trước dữ liệu không hiển thị các hàng thừa và chỉ hiển thị "Bảng" lặp lại trong cột cuối cùng.

    Điều này có vẻ không hứa hẹn
  14. Lưu ý rằng có một biểu tượng "Mở rộng" trong tiêu đề cho DavidTwo. Nhấp vào biểu tượng đó.

  15. Tùy chọn, nhưng tôi luôn bỏ chọn "Sử dụng tên cột gốc làm tiền tố". Bấm OK.

    Mở rộng các trường từ sổ làm việc 2

    Kết quả được hiển thị trong bản xem trước này:

    Tất cả các bản ghi từ một trong hai sổ làm việc
  16. Trong Power Query, sử dụng Trang chủ, Đóng & Tải.

Đây là tính năng tuyệt vời: nếu dữ liệu cơ bản trong một trong hai sổ làm việc thay đổi, bạn có thể nhấp vào biểu tượng Làm mới để kéo dữ liệu mới vào sổ làm việc kết quả.

Làm lại các bước 1-16 bằng cách nhấp vào biểu tượng Làm mới này.

Ghi chú

Biểu tượng làm mới thường bị ẩn. Kéo cạnh trái của ngăn Truy vấn & Kết nối sang bên trái để hiển thị biểu tượng.

Xem video

Bản ghi video

Học Excel từ Podcast, Tập 2216: Kết hợp hai sổ làm việc dựa trên một cột chung.

Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay là của David, người đã có mặt trong buổi hội thảo của tôi ở Melbourne, Florida, cho Chương Bờ biển Không gian của IIA.

David có hai sổ làm việc khác nhau trong đó Cột A là điểm chung giữa cả hai. Vì vậy, đây là Workbook 1, đây là Workbook 2-- cả hai đều có mã sản phẩm. Cái này có các mục mà cái đầu tiên không có hoặc ngược lại và David muốn kết hợp tất cả các cột. Vì vậy, chúng ta có ba cột ở đây và bốn cột ở đây. Tôi đặt cả hai thứ này vào cùng một sổ làm việc, trong trường hợp bạn đang tải xuống sổ làm việc để làm việc cùng. Lấy từng cái một trong số này, chuyển nó ra sổ làm việc của riêng nó và lưu nó.

Được rồi, để kết hợp các tệp này, chúng ta sẽ sử dụng Power Query. Power Query được tích hợp sẵn trong Excel 2016. Nếu bạn đang sử dụng phiên bản Windows 10 hoặc 13, bạn có thể truy cập Microsoft và tải xuống Power Query. Bạn có thể bắt đầu từ một sổ làm việc trống mới với một trang tính trống. Bạn sẽ lưu tệp này-- Bạn biết đấy, có thể là Sổ làm việc, để hiển thị kết quả của các tệp kết hợp .xlsx. Ổn thỏa? Và những gì chúng ta sẽ làm là, chúng ta sẽ thực hiện hai truy vấn. Chúng ta sẽ đi đến Dữ liệu, Lấy dữ liệu, Từ tệp, Từ sổ làm việc, và sau đó chúng ta sẽ chọn tệp đầu tiên. Trong bản xem trước, hãy chọn trang tính có dữ liệu của bạn và chúng tôi không phải làm gì với dữ liệu này. Vì vậy, chỉ cần mở hộp tải và chọn Load To, Only Create Connection, nhấp vào OK. Hoàn hảo. Bây giờ, chúng ta sẽ lặp lại điều đó cho mục thứ hai - Dữ liệu, Từ tệp,Từ Sổ làm việc, chọn DavidTwo, chọn tên trang tính, sau đó mở tải, Tải đến, Chỉ tạo kết nối. Bạn sẽ thấy ở đây trong bảng điều khiển này, chúng tôi có cả hai kết nối. Ổn thỏa.

Bây giờ công việc thực tế-- Dữ liệu, Lấy dữ liệu, Kết hợp Truy vấn, Hợp nhất, và sau đó trong hộp thoại Hợp nhất, chọn DavidOne, DavidTwo, và bước tiếp theo này hoàn toàn không trực quan. Bạn phải làm điều này. Chọn cột hoặc các cột chung-- vì vậy Sản phẩm và Sản phẩm. Ổn thỏa. Và sau đó, hãy rất cẩn thận ở đây với loại tham gia. Tôi muốn tất cả các hàng từ cả hai vì một hàng có thể có thêm một hàng và tôi cần thấy điều đó, sau đó chúng tôi bấm OK. Ổn thỏa. Và đây là kết quả ban đầu. Có vẻ như nó không hoạt động; nó không giống như nó đã thêm các mục bổ sung trong tệp 2. Và chúng tôi có cột 5 này - bây giờ nó rỗng. Tôi sẽ nhấp chuột phải vào cột 5 và nói, Xóa cột đó. Vì vậy, hãy mở biểu tượng mở rộng này và bỏ chọn hộp này cho Sử dụng tên cột gốc làm tiền tố và BAM! nó hoạt động. Vì vậy, các mục bổ sung có trong Tệp 2, không có trong Tệp 1,xuất hiện.

Ổn thỏa. Bây giờ trong tệp của ngày hôm nay, có vẻ như cột Mã Sản phẩm này tốt hơn cột Sản phẩm này, vì nó có thêm hàng. Nhưng có thể có một ngày trong tương lai Workbook 1 có những thứ mà Workbook 2 không có. Vì vậy, tôi sẽ để cả hai ở đó và tôi sẽ không loại bỏ bất kỳ giá trị nào bởi vì, giống như, mặc dù hàng này ở dưới cùng có vẻ là hoàn toàn trống, nhưng có thể xảy ra tình huống trong tương lai chúng tôi có một vài null ở đây vì một cái gì đó bị thiếu. Ổn thỏa? Vì vậy, cuối cùng, Đóng & Tải, và chúng ta có mười sáu hàng.

Bây giờ, trong tương lai, hãy nói rằng một cái gì đó sẽ thay đổi. Được rồi, vì vậy chúng tôi sẽ quay lại một trong hai tệp đó và tôi sẽ thay đổi lớp cho Apple thành 99 và thậm chí hãy chèn một cái gì đó mới và lưu sổ làm việc này. Ổn thỏa. Và sau đó, nếu chúng tôi muốn tệp hợp nhất của mình cập nhật, hãy đến đây-- bây giờ, hãy chú ý, khi bạn làm điều này lần đầu tiên, bạn không thể nhìn thấy biểu tượng Làm mới - bạn phải lấy thanh này và kéo nó qua . Và chúng tôi sẽ làm Làm mới, và 17 hàng được tải, dưa hấu xuất hiện, Apple thay đổi thành 99-- đó là một điều tuyệt vời. Bây giờ, bạn có muốn tìm hiểu về Power Query không? Mua cuốn sách này của Ken Puls và Miguel Escobar, M dành cho (DỮ LIỆU) MONKEY. Tôi sẽ giúp bạn tăng tốc.

Tóm tắt lại ngày hôm nay: David từ Florida có hai sách bài tập mà anh ấy muốn kết hợp; cả hai đều có các trường giống nhau trong Cột A, nhưng các cột khác đều khác nhau; một sổ làm việc có thể có các mục bổ sung không có trong sổ kia và David muốn có những mục đó; không có bản sao trong cả hai tệp; chúng ta sẽ sử dụng truy vấn nguồn để giải quyết vấn đề này, vì vậy hãy bắt đầu trong một sổ làm việc trống mới trên một trang tính trống; bạn sẽ thực hiện ba truy vấn, một truy vấn đầu tiên - Dữ liệu, Từ Tệp, Sổ làm việc, và sau đó Tải đến chỉ Kết nối đã Tạo; điều tương tự đối với sổ làm việc thứ hai, rồi đến Dữ liệu, Lấy dữ liệu, Hợp nhất, chọn hai kết nối, chọn cột chung trong cả hai - trong trường hợp của tôi là Sản phẩm-- và sau đó từ Loại kết hợp, bạn muốn tham gia đầy đủ tất cả từ Tệp 1, tất cả từ Tệp 2. Và điều tuyệt vời là nếu dữ liệu cơ bản thay đổi,bạn chỉ có thể làm mới truy vấn.

Để tải xuống sổ làm việc từ video hôm nay, hãy truy cập URL trong mô tả YouTube.

À, này, tôi muốn giống như David vì đã xuất hiện trong buổi hội thảo của tôi, tôi muốn cảm ơn bạn đã ghé qua. Hẹn gặp lại bạn lần sau cho một netcast khác từ.

Tải xuống tệp Excel

Để tải xuống tệp excel: merge-based-on-common-column.xlsx

Power Query là một công cụ tuyệt vời trong Excel.

Suy nghĩ của Excel trong ngày

Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:

"Luôn nhấn F4 khi bạn đọc dải ô hoặc ma trận trong một hàm"

Tanja Kuhn

thú vị bài viết...