So sánh dữ liệu trong hai trang tính hoặc cột của Google về sự phù hợp và khác biệt

Cho dù mùa hè có gõ cửa hay mùa đông xâm chiếm Westeros, chúng tôi vẫn làm việc trong Google Sheets và phải so sánh các phần khác nhau của các bảng với nhau. Trong bài viết này, tôi đang chia sẻ các cách khớp dữ liệu của bạn và đưa ra các mẹo để thực hiện nhanh chóng.

So sánh hai cột hoặc tờ

Một trong những nhiệm vụ bạn có thể có là quét hai cột hoặc trang tính để tìm sự trùng khớp hoặc khác biệt và xác định chúng ở đâu đó bên ngoài các bảng.

So sánh hai cột trong Google Sheets cho phù hợp và khác biệt

Tôi sẽ bắt đầu với việc so sánh hai ô trong Google Sheets. Cách này cho phép bạn quét toàn bộ cột theo hàng.

Ví dụ 1. Google Sheets – so sánh hai ô

Trong ví dụ đầu tiên này, bạn sẽ cần một cột trợ giúp để nhập công thức vào hàng đầu tiên của dữ liệu để so sánh:

=A2=B2

Nếu các ô khớp nhau, bạn sẽ thấy TRUE, nếu không thì SAI. Để kiểm tra tất cả các ô trong một cột, sao chép công thức xuống các hàng khác:

Ví dụ 2. Google Sheets – so sánh hai danh sách cho phù hợp và khác biệt

• Một giải pháp gọn gàng hơn sẽ là sử dụng Hàm IF. Bạn sẽ có thể đặt trạng thái chính xác cho các tế bào giống hệt nhau và khác nhau:

  =IF(A2=B2,"Match","Differ")
  

  Tiền boa. Nếu dữ liệu của bạn được viết trong các trường hợp khác nhau và bạn muốn xem xét các từ đó là khác nhau, thì đây là công thức dành cho bạn:

  =IF(EXACT(A2,B2),"Match","Differ")

  Trong đó CHÍNH XÁC xem xét trường hợp và tìm kiếm các bản sao hoàn chỉnh.

• Để chỉ xác định các hàng với các tế bào trùng lặp, sử dụng công thức này:

  =IF(A2=B2,"Match","")

• Để chỉ đánh dấu các hàng bằng hồ sơ độc đáo giữa các ô trong hai cột, lấy cái này:

  =IF(A2=B2,"","Differ")

Ví dụ 3. So sánh hai cột trong Google Sheets

• Có một cách để tránh sao chép công thức qua từng hàng. Bạn có thể giả mạo một công thức IF mảng trong ô đầu tiên của cột trợ giúp của bạn:

  =ArrayFormula(IF(A2:A=C2:C,"","Differ"))

  IF này ghép từng ô của cột A với cùng một hàng trong cột C. Nếu hồ sơ là khác nhau, hàng sẽ được xác định phù hợp. Điều tuyệt vời ở công thức mảng này là nó tự động đánh dấu từng hàng một:
  

• Trong trường hợp bạn muốn đặt tên cho các hàng bằng các tế bào giống hệt nhau, điền vào đối số thứ hai của công thức thay vì đối số thứ ba:

  =ArrayFormula(IF(A2:A=C2:C,"Match",""))

Ví dụ 4. So sánh hai Google Sheets về sự khác biệt

Thông thường, bạn cần so sánh hai cột trong Google Sheets thuộc về một bảng lớn. Hoặc chúng có thể là các bảng hoàn toàn khác nhau như báo cáo, bảng giá, ca làm việc mỗi tháng, v.v … Sau đó, tôi tin rằng, bạn không đủ khả năng để tạo một cột trợ giúp hoặc có thể khá khó quản lý.

Nếu điều này nghe có vẻ quen thuộc, đừng lo lắng, bạn vẫn có thể đánh dấu sự khác biệt trên một tờ khác.

Đây là hai bảng với sản phẩm và giá của chúng. Tôi muốn định vị tất cả các ô có nội dung khác nhau giữa các bảng này:

Bắt đầu với việc tạo một trang tính mới và nhập công thức tiếp theo vào A1:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

Ghi chú. Bạn phải sao chép công thức trên phạm vi bằng với kích thước của bảng lớn nhất.

Kết quả là, bạn sẽ chỉ thấy những ô khác nhau về nội dung. Công thức cũng sẽ kéo các bản ghi từ cả hai bảng và phân tách chúng bằng một ký tự bạn nhập vào công thức:

Công cụ cho Google Sheets để so sánh hai cột và trang tính

Tất nhiên, mỗi ví dụ ở trên có thể được sử dụng để so sánh hai cột từ một hoặc hai bảng hoặc thậm chí chính các bảng. Tuy nhiên, có một công cụ chúng tôi tạo ra cho nhiệm vụ này sẽ có lợi cho bạn rất nhiều.

Nó sẽ so sánh hai trang tính và cột của Google cho các bản sao hoặc đơn nhất trong 3 bước. Làm cho nó đánh dấu các bản ghi tìm thấy bằng một cột trạng thái (có thể được lọc, bằng cách này) hoặc tô màu, sao chép hoặc di chuyển chúng đến một vị trí khác, hoặc thậm chí xóa các ô và xóa toàn bộ các hàng với bất kỳ bản sao nào.

Tôi đã sử dụng tiện ích bổ sung để tìm các hàng trong Trang tính 1 vắng mặt trên Trang tính 2 dựa trên Trái cây và MSRP cột:

Chúng tôi đã mô tả tất cả các tùy chọn của công cụ trên nó trang trợ giúp để thuận tiện hơn cho bạn. Hãy tự mình thử nó và nhận thấy nó giúp bạn tiết kiệm được bao nhiêu thời gian. 🙂

So sánh dữ liệu trong hai Bảng tính của Google và tìm nạp các bản ghi bị thiếu

So sánh hai Google Sheets về sự khác biệt và lặp lại là một nửa công việc, nhưng còn thiếu dữ liệu thì sao? Cũng có các chức năng đặc biệt cho việc này, ví dụ, VLOOKUP. Hãy xem những gì bạn có thể làm.

Tìm dữ liệu bị thiếu

ví dụ 1

Hãy tưởng tượng bạn có hai danh sách các sản phẩm (cột A và B trong trường hợp của tôi, nhưng chúng có thể chỉ đơn giản là trên các tờ khác nhau). Bạn cần tìm những người được trình bày trong danh sách đầu tiên nhưng không phải trong danh sách thứ hai. Công thức này sẽ thực hiện các mẹo:

=ISERROR(VLOOKUP(A2,$B:$B,1,0))

Công thức hoạt động như thế nào:

• VLOOKUP tìm kiếm sản phẩm từ A2 trong danh sách thứ hai. Nếu nó ở đó, hàm trả về tên sản phẩm. Nếu không, bạn sẽ gặp lỗi # N / A, nghĩa là giá trị không được tìm thấy trong cột B.
• ISERROR kiểm tra những gì VLOOKUP trả về và hiển thị cho bạn TRUE nếu đó là giá trị và SAI nếu đó là lỗi.

Do đó, các ô có FALSE là thứ bạn đang tìm kiếm. Sao chép công thức sang các ô khác để kiểm tra từng sản phẩm từ danh sách đầu tiên:

Ghi chú. Nếu các cột của bạn nằm trong các trang tính khác nhau, công thức của bạn sẽ tham chiếu một trong số chúng:

=ISERROR(VLOOKUP(A2,Sheet2!$B:$B,1,0))

Tiền boa. Để có được bằng một công thức một ô, nó phải là một mảng. Công thức như vậy sẽ tự động điền vào tất cả các ô với kết quả:

=ArrayFormula(ISERROR(VLOOKUP(A2:A10,$B:$B,1,0)))

Ví dụ 2

Một cách thông minh khác là đếm tất cả các lần xuất hiện của sản phẩm từ A2 trong cột B:

=IF(COUNTIF($B:$B, $A2)=0, "Not found", "")

Nếu hoàn toàn không có gì để đếm, hàm IF sẽ đánh dấu các ô bằng Không tìm thấy. Các ô khác sẽ vẫn trống:

Ví dụ 3

Ở đâu có VLOOKUP, ở đó có MATCH. Bạn biết điều đó, phải không? 😉 Đây là công thức để khớp sản phẩm thay vì đếm:

=IF(ISERROR(MATCH($A2,$B:$B,0)),"Not found","")

Tiền boa. Vui lòng chỉ định phạm vi chính xác của cột thứ hai nếu nó vẫn giữ nguyên:

=IF(ISERROR(MATCH($A2,$B2:$B28,0)),"Not found","")

Kéo dữ liệu khớp

ví dụ 1

Nhiệm vụ của bạn có thể là một chút fancier: bạn có thể cần phải kéo tất cả thông tin còn thiếu cho các bản ghi chung cho cả hai bảng, ví dụ, cập nhật giá. Nếu vậy, bạn sẽ cần bọc MATCH bằng INDEX:

=INDEX($E:$E,MATCH($A2,$D:$D,0))

Công thức so sánh trái cây trong cột A với trái cây trong cột D. Đối với mọi thứ được tìm thấy, nó kéo giá từ cột E sang cột B.

Ví dụ 2

Như bạn có thể đoán, một ví dụ khác sẽ sử dụng Chức năng VLOOKUP của Google Sheets mà chúng tôi đã mô tả một thời gian trước đây.

Hợp nhất các trang tính bằng cách sử dụng tiện ích bổ sung

Nếu bạn mệt mỏi với công thức, bạn có thể sử dụng chúng tôi Hợp nhất bảng bổ trợ để nhanh chóng khớp và hợp nhất hai trang tính Google. Bên cạnh mục đích cơ bản của nó là lấy dữ liệu còn thiếu, nó cũng có thể cập nhật các giá trị hiện có và thậm chí thêm các hàng không khớp. Bạn có thể thấy tất cả các thay đổi về màu sắc hoặc trong một cột trạng thái có thể được lọc.

Định dạng có điều kiện để so sánh dữ liệu trong hai Google Sheets

Có thêm một cách tiêu chuẩn mà Google đưa ra để so sánh dữ liệu của bạn – bằng cách tô màu các trận đấu và / hoặc khác biệt thông qua định dạng có điều kiện. Phương pháp này làm cho tất cả các bản ghi bạn đang tìm kiếm nổi bật ngay lập tức. Công việc của bạn ở đây là tạo một quy tắc với một công thức và áp dụng nó cho một phạm vi dữ liệu chính xác.

Đánh dấu trùng lặp trong hai tờ hoặc cột

Hãy so sánh hai cột trong Google Sheets cho khớp và chỉ tô màu các ô trong cột A mà kiểm đếm với các ô trong cùng một hàng trong cột C:

1. Chọn phạm vi với các bản ghi để tô màu (A2: A10 cho tôi).
2. Đi đến Định dạng> Định dạng có điều kiện trong menu bảng tính.
3. Nhập một công thức đơn giản cho quy tắc:

   =A2=C2

4. Chọn màu để làm nổi bật các ô.

Tiền boa. Nếu các cột của bạn thay đổi kích thước liên tục và bạn muốn quy tắc xem xét tất cả các mục mới, hãy áp dụng nó cho toàn bộ cột (A2: A, giả sử dữ liệu để so sánh bắt đầu từ A2) và sửa đổi công thức như sau:

=AND(A2=C2,ISBLANK(A2)=FALSE)

Điều này sẽ xử lý toàn bộ các cột và bỏ qua các ô trống.

Ghi chú. Để so sánh dữ liệu từ hai trang tính khác nhau, bạn sẽ phải thực hiện các điều chỉnh khác cho công thức. Bạn thấy, định dạng có điều kiện trong Google Sheets không hỗ trợ các tham chiếu chéo. Tuy nhiên, bạn có thể truy cập các trang khác một cách gián tiếp:

=A2=INDIRECT("Sheet2!C2:C")

Trong trường hợp này, vui lòng đảm bảo chỉ định phạm vi để áp dụng quy tắc cho – A2: A10.

So sánh hai bảng và cột Google cho sự khác biệt

Để làm nổi bật các bản ghi không khớp với các ô trên cùng một hàng trong một cột khác, mũi khoan giống như trên. Bạn chọn phạm vi và tạo quy tắc định dạng có điều kiện. Tuy nhiên, công thức ở đây khác nhau:

=A2<>C2

Một lần nữa, sửa đổi công thức để làm cho quy tắc động (để nó xem xét tất cả các giá trị mới được thêm vào trong các cột này):

=AND(A2=C2,ISBLANK(A2)=FALSE)

Và sử dụng tham chiếu gián tiếp đến một trang tính khác nếu cột để so sánh với đó là:

=A2<>INDIRECT("Sheet1!C2:C")

Ghi chú. Đừng quên chỉ định phạm vi để áp dụng quy tắc cho – A2: A10.

So sánh hai danh sách và hồ sơ nổi bật trong cả hai

Tất nhiên, nhiều khả năng các bản ghi tương tự trong các cột của bạn sẽ bị phân tán. Giá trị trong A2 trong một cột sẽ không nhất thiết phải ở hàng thứ hai của cột khác. Trong thực tế, nó có thể xuất hiện nhiều sau đó. Rõ ràng, điều này đòi hỏi một phương pháp tìm kiếm các mục khác.

Ví dụ 1. So sánh hai cột trong Google Sheets và đánh dấu sự khác biệt (uniqu)

Để làm nổi bật các giá trị duy nhất trong mỗi danh sách, bạn cần tạo hai quy tắc định dạng có điều kiện cho mỗi cột.

Cột màu A: =COUNTIF($C$2:$C$9,$A2)=0
Cột màu C: =COUNTIF($A$2:$A$10,$C2)=0

Dưới đây là những điều kỳ lạ mà tôi có:

Ví dụ 2. Tìm và tô sáng các bản sao trong hai cột trong Google Sheets

Bạn có thể tô màu các giá trị chung sau khi sửa đổi nhỏ trong cả hai công thức từ ví dụ trước. Chỉ cần làm cho công thức tính mọi thứ lớn hơn không.

Bản sao màu giữa các cột chỉ trong A: =COUNTIF($C$2:$C$9,$A2)>0
Bản sao màu giữa các cột chỉ trong C: =COUNTIF($A$2:$A$10,$C2)>0

Cách nhanh chóng để khớp cột và đánh dấu hồ sơ

Định dạng có điều kiện đôi khi có thể khó khăn: bạn có thể vô tình tạo một vài quy tắc trên cùng một phạm vi hoặc áp dụng màu sắc thủ công trên các ô với quy tắc. Ngoài ra, bạn phải để mắt đến tất cả các phạm vi: những phạm vi bạn nêu bật thông qua các quy tắc và những phạm vi bạn sử dụng trong các quy tắc. Tất cả những điều này có thể làm bạn bối rối rất nhiều nếu bạn không chuẩn bị và không chắc chắn nơi để tìm kiếm vấn đề.

May mắn thay So sánh cột hoặc tờ đủ trực quan để giúp bạn khớp hai cột trong một bảng, hai bảng khác nhau trên một trang hoặc thậm chí hai trang riêng biệt và làm nổi bật những dấu hiệu hoặc bản sao có thể lẻn vào dữ liệu của bạn.

Đây là cách tôi tô màu giữa hai bảng dựa trên Trái cây và MSRP các cột sử dụng công cụ:

Tất cả các phương pháp này hiện đang theo ý của bạn – thử nghiệm với chúng, sửa đổi và áp dụng cho dữ liệu của bạn. Nếu không có gợi ý nào giúp ích cho nhiệm vụ cụ thể của bạn, vui lòng thảo luận về trường hợp của bạn trong các bình luận bên dưới.

Bạn cũng có thể quan tâm:

Source link

The post So sánh dữ liệu trong hai trang tính hoặc cột của Google về sự phù hợp và khác biệt appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/so-sanh-du-lieu-trong-hai-trang-tinh-hoac-cot-cua-google-ve-su-phu-hop-va-khac-biet.html