Cách so sánh dữ liệu trên 2 Sheet khác nhau trong file Excel

Để so sánh dữ liệu trên 2 sheet khác nhau nhưng trong cùng một file Excel, chúng ta có nhiều phương pháp khác nhau ví dụ như sử dụng VLOOKUP hoặc sử dụng hàm COUNTIF và kết hợp với Conditional Formating để tạo hiệu ứng so sánh khác biệt hoặc không khác biệt trên dữ liệu ở hai sheet khác nhau. Nhưng trong bài viết lần này của ThuThuatPhanMem.vn, chúng ta sẽ sử dụng phương pháp COUNTIF và Conditional Formating để so sánh dữ liệu.

Trước hết chúng ta có một cột dữ liệu như sau ở Sheet 1. Nó là một danh sách gồm các mặt hàng nhập kho ở ngày số 1.

Và ở sheet số hai thì ta có một danh sách các món hàng nhập kho cho ngày thứ 2. Hiện tại ta có thể sử dụng hàm COUNTIF để tính số lần trùng lặp các mặt hàng so với ngày đầu tiên.

Trước khi bắt tay vào viết công thức bạn cần phải giới hạn vùng dữ liệu cần so sánh ở sheet 1 và đặt cho nó một cái tên để phân biệt.

Bạn bôi đen vùng dữ liệu cần chọn sau đó click chuột vào phân tên của ô dữ liệu tại góc trái sheet, nhập tên cho vùng dữ liệu đó. Cái tên bạn nhập vào phải là chữ không dấu không cách nếu không Excel sẽ không thể đọc được tên của vùng dữ liệu đó.

Hoặc bạn cũng có thể đặt tên cho vùng bằng cách vào ribbon Formulas trên thanh công cụ và chọn Define Name trong mục Defined Names.

Nhập tên cho vùng tại ô Name rồi click OK để xác nhận đặt tên.

Hiện giờ vùng dữ liệu ở Sheet 1 của bạn đã có một tên đặt riêng bên bạn có thể gọi tên vùng dữ liệu đó trong một công thức hàm mà không cần phải ghi ký hiệu địa chỉ nữa. Phương pháp này rất tiện lợi cho việc sử dụng dữ liệu trên nhiều sheet khác nhau.

Công thức COUNTIF mà chúng ta cần dùng tới ở đây để đếm số lần trùng lặp của hai cột dữ liệu đó là:

=COUNTIF(mảng dữ liệu cần đếm, điều kiện phân biệt để đếm)

Và với ví dụ trong ảnh, ta có công thức hàm là:

=COUNTIF(nhapkho1;A2)

Với công thức này ta có thể đếm được số lần “Vở học sinh” xuất hiện trong mảng dữ liệu hàng hóa nhập kho ngày đầu tiên.

Tiếp đó bạn chỉ cần copy công thức đó xuống bằng cách click chuột vào góc phải bên dưới của ô công thức có sẵn rồi kéo chuột xuống bên dưới.

Như các bạn sẽ thấy những kết quả bằng 0 cho thấy mặt hàng này chưa từng được nhập kho trong ngày đầu tiên.

Các bước làm trên có thể cho ta thấy con số cụ thể của việc nhập kho trùng lặp mặt hàng nhưng không thể hiện việc trùng lặp đó ngay trên cột dữ liệu. Chúng ta có thể thực hiện việc này bằng chức năng Conditional Formating, tạo một định dạng khác biệt để khi nhìn vào ta có thể nhận ra ngay mặt hàng nào là mặt hàng không có sự trùng lặp khi so sánh với dữ liệu nhập kho ngày 1.

Bạn bôi đen mảng dữ liệu cần so sánh, sau đó vào ribbon Home trên thanh công cụ và chọn Conditional Formating nằm ở mục Styles của ribbon này.

Click vào dòng New Rule.

Sau đó ô hộp thoại chức năng New Formatting Rule sẽ xuất hiện, bạn chọn dòng cuối cùng của danh sách các loại rule là Use a formula to determine which cells to format.

Tại ô Format values where this formula is true (định dạng những ô đúng với công thức sau đây), bạn điền công thức sau đây:

=B2=0

Để giải thích một chút cho điều này. Bởi vì chúng ta cần tô màu cho những ô dữ liệu không có sự trùng lặp, có nghĩa là ở dòng dữ liệu đó kết quả tại cột “Trùng lặp với ngày 1” mà chúng ta vừa điền đầy bằng công thức COUNTIF là 0.

Chính vì vậy khi chúng ta điền điều kiện cho ô này, chúng ta cần phải đưa điều kiện rằng tìm các ô bằng 0 trong cột “Trùng lặp với ngày 1” đó.

Bạn để nguyên B2 là ô tham số không cố định để nó có thể biến đổi với các hàng trong cột.

Sau đó đừng quên đổi định dạng cho ô có điều kiện phù hợp bằng cách vào Format.

Thay đổi định dạng của ô phù hợp điều kiện trong các tab khác nhau của ô hộp thoại chức năng Format Cells, với Font là đổi font chữ, Border để đổi viền của ô và Fill để đổ màu nền cho ô dữ liệu đó. Sau đó bạn bấm OK để xác nhận.

Sau khi nhìn lại một loạt cài đặt, các bạn bấm OK để bắt đầu thay đổi định dạng cho những ô phù hợp với điều kiện đặt ra.

Như các bạn sẽ thấy, các ô phù hợp với điều kiện đặt ra đó là mặt hàng không trùng với ngày nhập kho đầu tiên sẽ được phủ một màu xanh lên ô của mình.

Nếu bạn muốn thực hiện trực tiếp việc thay đổi định dạng cho những ô có dữ liệu không trùng thì có thể sử dụng thẳng công thức hàm COUNTIF vào ô Format values where this formula is true.

Từ đó thì ta có công thức cho ô Format values where this formula is true là:

=COUNTIF(nhapkho1;A2)=0

Và kết quả nhận được:

Lưu ý: Nếu bạn đổi công thức từ =0 thành >0 thì các ô có dữ liệu trùng lặp so với ngày đầu tiên sẽ được thay đổi định dạng. Phần này, ThuThuatPhanMem.vn sẽ để lại cho các bạn luyện tập thử.

Bài viết về cách so sánh dữ liệu trên 2 Sheet khác nhau của Thủ Thuật Phần Mềm tới đây là hết, cảm ơn các bạn đã theo dõi bài viết của chúng tôi. Hy vọng các bạn có thể hiểu được kiến thức mà chúng tôi muốn truyền đạt và thực hiện thành công nó cho dữ liệu của mình.