So sánh hai bảng

Chúng tôi có hai bảng (ví dụ: phiên bản cũ và mới của bảng giá), chúng tôi cần so sánh và nhanh chóng tìm ra sự khác biệt:

Rõ ràng là có thứ đã được thêm vào bảng giá mới (chà là, tỏi…), có thứ đã biến mất (mâm xôi, mâm xôi…), giá một số mặt hàng (sung, dưa…) đã thay đổi. Bạn cần nhanh chóng tìm và hiển thị tất cả những thay đổi này.

Đối với bất kỳ tác vụ nào trong Excel, hầu như luôn có nhiều hơn một giải pháp (thường là 4-5). Đối với vấn đề của chúng tôi, nhiều cách tiếp cận khác nhau có thể được sử dụng:

• chức năng VPR (Vlookup) - tìm tên sản phẩm từ bảng giá mới trong bảng giá cũ và hiển thị giá cũ bên cạnh bảng giá mới, sau đó nắm bắt sự khác biệt
• hợp nhất hai danh sách thành một và sau đó xây dựng một bảng tổng hợp dựa trên nó, nơi sự khác biệt sẽ được nhìn thấy rõ ràng
• sử dụng Bổ trợ Power Query cho Excel

Hãy đưa tất cả chúng theo thứ tự.

Phương pháp 1. So sánh các bảng với hàm Vlookup

Nếu bạn hoàn toàn không quen thuộc với tính năng tuyệt vời này, thì trước tiên hãy xem ở đây và đọc hoặc xem video hướng dẫn về nó - hãy tiết kiệm cho bạn vài năm tuổi thọ.

Thông thường, hàm này được sử dụng để kéo dữ liệu từ bảng này sang bảng khác bằng cách khớp với một số tham số chung. Trong trường hợp này, chúng tôi sẽ sử dụng nó để đẩy giá cũ sang giá mới:

Các sản phẩm đó, có lỗi # N / A, không có trong danh sách cũ, tức là đã được thêm vào. Sự thay đổi về giá cũng có thể nhìn thấy rõ ràng.

Ưu điểm phương pháp này: đơn giản và rõ ràng, "cổ điển của thể loại", như họ nói. Hoạt động trong bất kỳ phiên bản nào của Excel.

Nhược điểm cũng ở đó. Để tìm kiếm sản phẩm được thêm vào bảng giá mới, bạn sẽ phải thực hiện quy trình tương tự theo chiều ngược lại, tức là kéo giá mới lên giá cũ với sự trợ giúp của hàm VLOOKUP. Nếu kích thước của các bảng thay đổi vào ngày mai, thì các công thức sẽ phải được điều chỉnh. Vâng, và trên các bảng thực sự lớn (> 100 nghìn hàng), tất cả niềm hạnh phúc này sẽ dần chậm lại.

Phương pháp 2: So sánh các bảng bằng cách sử dụng pivot

Hãy sao chép lần lượt các bảng của chúng tôi bên dưới bảng khác, thêm một cột có tên bảng giá, để sau này bạn có thể hiểu từ danh sách nào hàng nào:

Bây giờ, dựa trên bảng đã tạo, chúng tôi sẽ tạo một bản tóm tắt thông qua Chèn - PivotTable (Chèn - Bảng tổng hợp). Hãy ném một lĩnh vực Sản phẩm khu vực đường thẳng, lĩnh vực Giá cả đến khu vực cột và trường Цena vào phạm vi:

Như bạn có thể thấy, bảng tổng hợp sẽ tự động tạo danh sách chung của tất cả các sản phẩm từ bảng giá cũ và mới (không lặp lại!) Và sắp xếp các sản phẩm theo thứ tự bảng chữ cái. Bạn có thể thấy rõ các sản phẩm được thêm vào (không có giá cũ), sản phẩm bị loại bỏ (không có giá mới) và các thay đổi về giá, nếu có.

Tổng số trong một bảng như vậy không có ý nghĩa và chúng có thể bị vô hiệu hóa trên tab Khối mã lệnh - Tổng số - Vô hiệu hóa đối với hàng và cột (Thiết kế - Tổng số).

Nếu giá cả thay đổi (nhưng không thay đổi số lượng hàng hóa!), Thì chỉ cần cập nhật bản tóm tắt đã tạo bằng cách nhấp chuột phải vào nó là đủ - Refresh.

Ưu điểm: Cách tiếp cận này là một thứ tự cường độ nhanh hơn với các bảng lớn hơn so với VLOOKUP. 

Nhược điểm: bạn cần sao chép thủ công các dữ liệu dưới nhau và thêm cột có tên bảng giá. Nếu kích thước của các bảng thay đổi, thì bạn phải làm lại mọi thứ.

Phương pháp 3: So sánh các bảng với Power Query

Power Query là một phần bổ trợ miễn phí cho Microsoft Excel cho phép bạn tải dữ liệu vào Excel từ hầu hết mọi nguồn và sau đó chuyển đổi dữ liệu này theo bất kỳ cách nào mong muốn. Trong Excel 2016, phần bổ trợ này đã được tích hợp sẵn theo mặc định trên tab Ngày (Dữ liệu), và đối với Excel 2010-2013, bạn cần tải xuống riêng từ trang web của Microsoft và cài đặt - tải tab mới Truy vấn nguồn.

Trước khi tải bảng giá của chúng tôi vào Power Query, trước tiên chúng phải được chuyển đổi thành bảng thông minh. Để thực hiện việc này, hãy chọn phạm vi có dữ liệu và nhấn tổ hợp trên bàn phím Ctrl+T hoặc chọn tab trên ruy-băng Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng). Có thể sửa tên của các bảng đã tạo trên tab xây dựng (Tôi sẽ để lại tiêu chuẩn Bảng 1 и Bảng 2, được lấy theo mặc định).

Tải giá cũ trong Power Query bằng nút Từ Bảng / Phạm vi (Từ Bảng / Phạm vi) từ tab Ngày (Ngày) hoặc từ tab Truy vấn nguồn (tùy thuộc vào phiên bản Excel). Sau khi tải xong, chúng ta sẽ quay lại Excel từ Power Query bằng lệnh Đóng và tải - Đóng và tải vào… (Đóng & Tải - Đóng & Tải Đến…):

… Và trong cửa sổ xuất hiện, sau đó chọn Chỉ cần tạo kết nối (Chỉ kết nối).

Lặp lại tương tự với bảng giá mới. 

Bây giờ, hãy tạo một truy vấn thứ ba sẽ kết hợp và so sánh dữ liệu từ hai truy vấn trước. Để thực hiện việc này, hãy chọn trong Excel trên tab Dữ liệu - Nhận dữ liệu - Kết hợp các yêu cầu - Kết hợp (Dữ liệu - Lấy dữ liệu - Hợp nhất các truy vấn - Hợp nhất) hoặc nhấn nút Kết hợp (Hợp nhất) chuyển hướng Truy vấn nguồn.

Trong cửa sổ kết hợp, hãy chọn bảng của chúng tôi trong danh sách thả xuống, chọn các cột có tên hàng hóa trong đó và ở dưới cùng, hãy đặt phương thức kết hợp - Hoàn thành bên ngoài (Toàn bộ bên ngoài):

Sau khi nhấp vào OK một bảng gồm ba cột sẽ xuất hiện, trong đó ở cột thứ ba, bạn cần mở rộng nội dung của các bảng lồng nhau bằng cách sử dụng mũi tên kép trong tiêu đề:

Kết quả là, chúng tôi nhận được sự hợp nhất dữ liệu từ cả hai bảng:

Tất nhiên, tốt hơn là đổi tên các tên cột trong tiêu đề bằng cách nhấp đúp vào những tên dễ hiểu hơn:

Và bây giờ là thú vị nhất. Đi tới tab Thêm cột (Thêm cột) và bấm vào nút Cột có điều kiện (Cột có điều kiện). Và sau đó trong cửa sổ mở ra, hãy nhập một số điều kiện thử nghiệm với các giá trị đầu ra tương ứng của chúng:

Nó vẫn còn để nhấp vào OK và tải báo cáo kết quả lên Excel bằng cách sử dụng cùng một nút đóng và tải xuống (Đóng & Tải) chuyển hướng Trang Chủ (Trang Chủ):

Làm đẹp.

Hơn nữa, nếu có bất kỳ thay đổi nào xảy ra trong bảng giá trong tương lai (dòng được thêm vào hoặc xóa, giá thay đổi, v.v.), thì chỉ cần cập nhật yêu cầu của chúng tôi bằng một phím tắt là đủ Ctrl+Khác+F5 hoặc bằng nút Làm mới tất cả (Làm mới tất cả) chuyển hướng Ngày (Ngày).

Ưu điểm: Có lẽ là cách đẹp nhất và tiện lợi nhất. Hoạt động thông minh với các bảng lớn. Không yêu cầu chỉnh sửa thủ công khi thay đổi kích thước bảng.

Nhược điểm: Yêu cầu cài đặt bổ trợ Power Query (trong Excel 2010-2013) hoặc Excel 2016. Tên cột trong dữ liệu nguồn không được thay đổi, nếu không chúng tôi sẽ gặp lỗi “Không tìm thấy cột như vậy và cột đó!” khi cố gắng cập nhật truy vấn.

• Cách thu thập dữ liệu từ tất cả các tệp Excel trong một thư mục nhất định bằng Power Query
• Cách tìm kết quả phù hợp giữa hai danh sách trong Excel
• Hợp nhất hai danh sách mà không có danh sách trùng lặp