Hợp nhất hai danh sách mà không có danh sách trùng lặp

Một tình huống kinh điển: bạn có hai danh sách cần được hợp nhất thành một. Hơn nữa, trong danh sách ban đầu có thể có cả phần tử duy nhất và phần tử phù hợp (cả giữa danh sách và bên trong), nhưng ở đầu ra, bạn cần nhận được danh sách không có phần tử trùng lặp (lặp lại):

Theo truyền thống, chúng ta hãy xem xét một số cách để giải quyết một vấn đề phổ biến như vậy - từ nguyên thủy "trên trán" đến phức tạp hơn, nhưng thanh lịch.

Phương pháp 1: Loại bỏ các bản sao

Bạn có thể giải quyết vấn đề theo cách đơn giản nhất - sao chép thủ công các phần tử của cả hai danh sách thành một và sau đó áp dụng công cụ cho tập hợp kết quả. Loại bỏ các bản sao từ tab Ngày (Dữ liệu - Xóa bản sao):

Tất nhiên, phương pháp này sẽ không hoạt động nếu dữ liệu trong danh sách nguồn thường xuyên thay đổi - bạn sẽ phải lặp lại toàn bộ quy trình sau mỗi lần thay đổi một lần nữa. 

Phương pháp 1a. bảng xoay

Trên thực tế, phương pháp này là một sự tiếp nối hợp lý của phương pháp trước đó. Nếu danh sách không quá lớn và biết trước số lượng phần tử tối đa trong đó (ví dụ: không quá 10), thì bạn có thể kết hợp hai bảng thành một bằng các liên kết trực tiếp, thêm một cột với các phần tử ở bên phải và xây dựng một bảng tóm tắt dựa trên bảng kết quả:

Như bạn đã biết, bảng tổng hợp bỏ qua các lần lặp lại, vì vậy ở đầu ra, chúng ta sẽ nhận được một danh sách kết hợp mà không có bản sao. Cột bổ trợ với 1 chỉ cần thiết vì Excel có thể xây dựng bảng tóm tắt chứa ít nhất hai cột.

Khi danh sách ban đầu được thay đổi, dữ liệu mới sẽ chuyển đến bảng kết hợp thông qua các liên kết trực tiếp, nhưng bảng tổng hợp sẽ phải được cập nhật theo cách thủ công (nhấp chuột phải vào - Cập nhật & Lưu). Nếu bạn không cần tính toán lại một cách nhanh chóng, thì tốt hơn là sử dụng các tùy chọn khác.

Phương pháp 2: Công thức mảng

Bạn có thể giải quyết vấn đề bằng các công thức. Trong trường hợp này, việc tính toán lại và cập nhật kết quả sẽ diễn ra tự động và ngay lập tức, ngay sau những thay đổi trong danh sách ban đầu. Để thuận tiện và ngắn gọn, chúng ta hãy đưa ra tên danh sách của chúng tôi. danh sách 1 и danh sách 2sử dụng Người quản lý tên chuyển hướng công thức (Công thức - Trình quản lý tên - Tạo):

Sau khi đặt tên, công thức chúng ta cần sẽ có dạng như sau:

Thoạt nhìn thì có vẻ rùng rợn, nhưng thực tế thì mọi thứ không đáng sợ như vậy. Hãy để tôi mở rộng công thức này trên một số dòng bằng cách sử dụng tổ hợp phím Alt + Enter và thụt lề bằng dấu cách, như chúng tôi đã làm, ví dụ ở đây:

Logic ở đây là như sau:

• Công thức INDEX (List1; MATCH (0; COUNTIF ($ E $ 1: E1; List1); 0) chọn tất cả các phần tử duy nhất từ ​​danh sách đầu tiên. Ngay sau khi chúng hết, nó bắt đầu xuất hiện lỗi # N / A:

  

• Công thức INDEX (List2; MATCH (0; COUNTIF ($ E $ 1: E1; List2); 0)) trích xuất các phần tử duy nhất từ ​​danh sách thứ hai theo cách tương tự.
• Được lồng vào nhau hai hàm IFERROR thực hiện đầu ra đầu tiên của các hàm duy nhất từ ​​danh sách-1, sau đó từ danh sách-2 lần lượt.

Lưu ý đây là công thức mảng, tức là sau khi gõ xong phải nhập vào ô không bình thường đăng ký hạng mục thi, nhưng với một phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thi và sau đó sao chép (kéo) xuống các ô con có lề.

Trong phiên bản tiếng Anh của Excel, công thức này giống như sau:

= IFERROR (IFERROR (INDEX (List1, MATCH (0, COUNTIF ($ E $ 1: E1, List1), 0)), INDEX (List2, MATCH (0, COUNTIF ($ E $ 1: E1, List2), 0)) ), “”) 

Nhược điểm của phương pháp này là các công thức mảng làm chậm đáng kể công việc với tệp nếu các bảng nguồn có số lượng phần tử lớn (vài trăm hoặc nhiều hơn). 

Phương pháp 3. Truy vấn nguồn

Nếu danh sách nguồn của bạn có một số lượng lớn các phần tử, chẳng hạn như vài trăm hoặc hàng nghìn, thì thay vì một công thức mảng chậm, tốt hơn nên sử dụng một cách tiếp cận khác về cơ bản, cụ thể là các công cụ bổ trợ Power Query. Phần bổ trợ này được tích hợp vào Excel 2016 theo mặc định. Nếu bạn có Excel 2010 hoặc 2013, bạn có thể tải xuống và cài đặt riêng (miễn phí).

Thuật toán của các hành động như sau:

1. Mở một tab riêng biệt của tiện ích bổ sung đã cài đặt Truy vấn nguồn (nếu bạn có Excel 2010-2013) hoặc chỉ cần chuyển đến tab Ngày (nếu bạn có Excel 2016).
2. Chọn danh sách đầu tiên và nhấn nút Từ Bảng / Phạm vi (Từ Phạm vi / Bảng). Khi được hỏi về việc tạo “bảng thông minh” từ danh sách của chúng tôi, chúng tôi đồng ý:

   

3. Cửa sổ trình chỉnh sửa truy vấn mở ra, nơi bạn có thể thấy dữ liệu đã tải và tên truy vấn Bảng 1 (bạn có thể thay đổi nó thành của riêng bạn nếu bạn muốn).
4. Nhấp đúp vào tiêu đề bảng (từ danh sách 1) và đổi tên nó thành bất kỳ cái nào khác (ví dụ: người). Tên chính xác là gì không quan trọng, nhưng cái tên được phát minh ra phải được ghi nhớ, bởi vì. nó sẽ phải được sử dụng lại sau này khi nhập bảng thứ hai. Việc hợp nhất hai bảng trong tương lai sẽ chỉ hoạt động nếu tiêu đề cột của chúng khớp với nhau.
5. Mở rộng danh sách thả xuống ở góc trên bên trái đóng và tải xuống Và chọn Đóng và tải vào… (Đóng & Tải vào…):

   

6. Trong hộp thoại tiếp theo (nó có thể hơi khác một chút - đừng lo lắng), hãy chọn Chỉ cần tạo kết nối (Chỉ tạo kết nối):

   

7. Chúng tôi lặp lại toàn bộ quy trình (điểm 2-6) cho danh sách thứ hai. Khi đổi tên tiêu đề cột, điều quan trọng là sử dụng cùng tên (Mọi người) như trong truy vấn trước đó.
8. Trong cửa sổ Excel trên tab Ngày hoặc trên tab Truy vấn nguồn Chọn Nhận dữ liệu - Kết hợp các yêu cầu - Thêm (Lấy dữ liệu - Hợp nhất truy vấn - Nối):

   

9. Trong hộp thoại xuất hiện, hãy chọn yêu cầu của chúng tôi từ danh sách thả xuống:

   

10. Kết quả là, chúng ta sẽ nhận được một truy vấn mới, trong đó hai danh sách sẽ được kết nối với nhau. Nó vẫn để loại bỏ các bản sao bằng nút Xóa hàng - Xóa các bản trùng lặp (Xóa hàng - Xóa các bản trùng lặp):

    

11. Truy vấn đã hoàn thành có thể được đổi tên ở phía bên phải của bảng tùy chọn, đặt cho nó một cái tên lành mạnh (thực tế đây sẽ là tên của bảng kết quả) và mọi thứ có thể được tải lên trang tính bằng lệnh đóng và tải xuống (Đóng & Tải):

    

Trong tương lai, với bất kỳ thay đổi hoặc bổ sung nào đối với danh sách ban đầu, chỉ cần nhấp chuột phải để cập nhật bảng kết quả là đủ.

• Cách thu thập nhiều bảng từ các tệp khác nhau bằng Power Query
• Trích xuất các mặt hàng duy nhất từ ​​một danh sách
• Cách so sánh hai danh sách với nhau để tìm sự phù hợp và khác biệt