Hợp nhất các ô trong Google Sheets từ nhiều hàng thành một hàng dựa trên giá trị cột

Hợp nhất các hàng trùng lặp trong bảng tính của bạn có thể chuyển sang một trong những nhiệm vụ phức tạp nhất. Hãy xem công thức Google có thể giúp gì và làm quen với một tiện ích bổ sung thông minh thực hiện tất cả công việc cho bạn.

Các chức năng kết hợp các ô có cùng giá trị trong Google Sheets

Bạn không nghĩ Google Sheets sẽ thiếu chức năng cho loại nhiệm vụ này, phải không? 😉 Dưới đây là các công thức bạn sẽ cần để hợp nhất các hàng và loại bỏ các ô trùng lặp trong bảng tính.

CONCATENATE – Chức năng và nhà điều hành Google Sheets để tham gia hồ sơ

Điều đầu tiên tôi nghĩ đến khi tôi nghĩ rằng không chỉ đơn giản là loại bỏ các bản sao mà còn mang các hàng trùng lặp lại với nhau là chức năng CONCATENATE của Google Sheets và một ký hiệu (&) – một đặc biệt điều hành nối.

Giả sử bạn có một danh sách các bộ phim để xem và bạn muốn nhóm chúng theo thể loại:

• Bạn chỉ có thể hợp nhất các ô trong Google Sheets với khoảng trắng giữa các giá trị:

  =CONCATENATE(B2," ",C2," ",B8," ",C8)
=B2&" "&C2&" "&B8&" "&C8
  

• Hoặc sử dụng khoảng trắng với bất kỳ dấu nào khác để kết hợp các hàng trùng lặp với nhau:

  =CONCATENATE(A3,": ",B3," (",C3,"), ",B6," (",C6,") ")
=A3&": "&B3&" ("&C3&"), "&B6&" ("&C6&") "
  

Khi các hàng được hợp nhất, bạn có thể loại bỏ các công thức và chỉ giữ lại văn bản theo ví dụ của hướng dẫn này: Chuyển đổi công thức thành giá trị trong Google Sheets

Đơn giản như cách này có vẻ như, nó rõ ràng là xa lý tưởng. Nó đòi hỏi bạn phải biết chính xác vị trí của các bản sao và chính bạn là người nên chỉ ra chúng cho công thức. Vì vậy, điều này có thể làm việc cho các bộ dữ liệu nhỏ, nhưng phải làm gì khi chúng lớn hơn?

Hợp nhất các ô vẫn giữ dữ liệu với UNIQUE + THAM GIA

Bộ công thức này tìm thấy các bản sao trong Google Sheets (và hợp nhất các ô với các bản ghi duy nhất) cho bạn. Tuy nhiên, bạn vẫn chịu trách nhiệm và phải hiển thị các công thức để tìm. Hãy xem cách nó hoạt động trên cùng một danh sách cần xem.

1. Tôi sử dụng Google Sheets UNIQUE trong E2 để kiểm tra các thể loại trong cột A:

   =UNIQUE(A2:A)
   
   Công thức trả về danh sách tất cả các thể loại bất kể chúng lặp lại hay không lặp lại trong danh sách ban đầu. Nói cách khác, nó loại bỏ các bản sao khỏi cột A.

   Tiền boa. Nếu bạn thêm nhiều giá trị vào cột A, công thức sẽ tự động mở rộng danh sách với các bản ghi duy nhất.

2. Sau đó, tôi xây dựng công thức tiếp theo của mình với chức năng Google Sheets THAM GIA:

   =JOIN(", ",FILTER(B:B,A:A=E2))
   
   Làm thế nào để các yếu tố của công thức này hoạt động?

   • LỌC quét cột A cho tất cả các trường hợp của giá trị trong E2. Khi được định vị, nó kéo các bản ghi tương ứng từ cột B.
   • THAM GIA kết hợp các giá trị này trong một ô bằng dấu phẩy.

   Sao chép công thức xuống và bạn sẽ nhận được tất cả các tiêu đề được sắp xếp theo thể loại.

   Ghi chú. Trong trường hợp bạn cũng cần nhiều năm, bạn sẽ phải tạo công thức trong cột lân cận vì THAM GIA hoạt động với một cột mỗi lần:

   =JOIN(", ",FILTER(C:C,A:A=E2))
   

Vì vậy, tùy chọn này trang bị cho Google Sheets một vài chức năng để kết hợp nhiều hàng thành một dựa trên các bản sao. Và nó tự động xảy ra. Vâng, gần như vậy. Tôi dự định sẽ giữ giải pháp hoàn hảo cho đến cuối bài viết. Nhưng cứ thoải mái nhảy đến nó ngay 😉

Hàm QUERY để xóa các dòng trùng lặp trong Google Sheets

Có một chức năng nữa giúp vận hành các bảng khổng lồ – QUERY. Ban đầu có vẻ hơi khó khăn, nhưng một khi bạn học cách sử dụng nó, nó sẽ trở thành người bạn đồng hành thực sự của bạn trong bảng tính.

Đây là chức năng QUERY:

= QUERY (dữ liệu, truy vấn, [headers])

Làm thế nào nó hoạt động:

• dữ liệu (bắt buộc) – phạm vi của bảng nguồn của bạn.
• truy vấn (bắt buộc) – một tập hợp các lệnh để xác định các điều kiện để có được dữ liệu cụ thể.
  Tiền boa. Bạn có thể nhận được một danh sách đầy đủ của tất cả các lệnh đây.
• tiêu đề (tùy chọn) – số lượng hàng tiêu đề trong bảng nguồn của bạn.

Nói một cách đơn giản, Google Sheets QUERY trả về một số bộ giá trị dựa trên các điều kiện bạn chỉ định.

ví dụ 1

Tôi chỉ muốn nhận những bộ phim truyện tranh mà tôi chưa xem:

=QUERY(A1:C,"select * where A='Comic Book'")

Công thức xử lý toàn bộ bảng nguồn của tôi (A1: C) và trả về tất cả các cột (chọn *) cho phim truyện tranh (trong đó A = ‘Truyện tranh’).

Tiền boa. Tôi không cố ý chỉ định hàng cuối cùng của bảng (A1: C) – để giữ cho công thức linh hoạt và trả về các bản ghi mới trong trường hợp các hàng khác được thêm vào bảng.

Như bạn có thể thấy, nó hoạt động tương tự như một bộ lọc. Nhưng trên thực tế, dữ liệu của bạn có thể lớn hơn nhiều – với những con số bạn có thể cần tính toán.

Ví dụ 2

Giả sử tôi đang thực hiện một nghiên cứu nhỏ và theo dõi phòng vé cuối tuần cho những bộ phim mới nhất ra rạp:

Tôi sử dụng Google Sheets QUERY để xóa các bản sao và đếm tổng số tiền kiếm được cho mỗi bộ phim cho tất cả các ngày cuối tuần. Tôi cũng sắp xếp thứ tự chúng theo thể loại:

=QUERY(B1:D, "select B,C, SUM(D) group by B,C")

Ghi chú. Cho nhóm bởi lệnh, bạn phải liệt kê tất cả các cột sau lựa chọnnếu không, công thức sẽ không hoạt động.

Để sắp xếp các bản ghi theo phim thay vào đó, tôi chỉ cần thay đổi thứ tự các cột cho nhóm bởi:

=QUERY(B1:D, "select B,C, SUM(D) group by C,B")

Ví dụ 3

Giả sử bạn điều hành thành công một cửa hàng sách và bạn theo dõi tất cả các cuốn sách có trong kho trên tất cả các chi nhánh của bạn. Danh sách lên tới hàng trăm cuốn sách:

• Do sự cường điệu trong bộ truyện Harry Potter, bạn quyết định kiểm tra xem bạn đã viết bao nhiêu cuốn sách của J.K Rowling:

  =QUERY('Copy of In stock'!A1:D,"select A,B,C,D where A='Rowling'")
  

• Bạn quyết định đi xa hơn và chỉ giữ bộ truyện Harry Potter bỏ qua những câu chuyện khác:

  =QUERY('In stock'!A1:D,"select A,B,C,D where (A='Rowling' and C contains 'Harry Potter')")
  

• Sử dụng chức năng Google Sheets QUERY, bạn cũng có thể đếm tất cả những cuốn sách này:

  =QUERY('In stock'!A1:D,"select A,B, sum(D) where (A='Rowling' and C contains 'Harry Potter') group by A,B")
  

Tôi đoán bây giờ bạn đã có ý tưởng về cách hàm QUERY “loại bỏ trùng lặp” trong Google Sheets. Mặc dù đó là một tùy chọn có sẵn cho tất cả, nhưng đối với tôi, nó giống như một cách kết hợp các hàng trùng lặp.

Hơn nữa, cho đến khi bạn học truy vấn nó sử dụng và quy tắc áp dụng chúng, chức năng sẽ không giúp được nhiều.

Cách nhanh nhất để kết hợp các hàng trùng lặp

Khi bạn từ bỏ tất cả hy vọng tìm ra một giải pháp đơn giản để kết hợp nhiều hàng dựa trên các bản sao, tiện ích bổ sung cho Google Sheets của chúng tôi tạo ra một lối vào tuyệt vời. 🙂

Kết hợp Hàng trùng lặp quét một cột với các bản ghi lặp lại, hợp nhất các ô tương ứng với các cột khác, tách các bản ghi này bằng các dấu phân cách và hợp nhất các số. Tất cả cùng một lúc và trong một vài cú click chuột!

Ghi nhớ danh sách sách của tôi trong cửa hàng với vài trăm hàng? Chúng ta hãy xem công cụ sẽ quản lý nó như thế nào.

Tiền boa. Vì tiện ích là một phần của Dụng cụ điện, vui lòng cài đặt nó trước và trực tiếp đến Hợp nhất và kết hợp nhóm:

Sau đó nhấp vào biểu tượng bổ trợ để mở:

1. Khi tiện ích bổ sung đang chạy, hãy chọn phạm vi bạn muốn kết hợp các hàng trùng lặp:
   
2. Chọn các cột có chứa các giá trị lặp lại. Trong trường hợp của tôi, họ là Họ và Tên đầu tiên:
   
3. Bước tiếp theo cho phép bạn quyết định như sau:
   • các cột có giá trị bạn sẽ mang lại với nhau
   • cách kết hợp các bản ghi đó: hợp nhất hoặc tính toán
   • dấu phân cách để hợp nhất các ô với văn bản
   • hàm số để tính

   Đối với tôi, tôi muốn có tất cả các cuốn sách thuộc về một tác giả được mang đến một ô và cách nhau bởi các dòng ngắt. Nếu bất kỳ tiêu đề nào lặp lại, phần bổ trợ sẽ chỉ hiển thị chúng một lần.

   Về số lượng, tôi ổn với tổng số sách cho mỗi tác giả. Các số cho các tiêu đề trùng lặp, nếu có, sẽ được cộng lại với nhau.
   

4. Khi điều chỉnh tất cả các cài đặt, nhấp vào Hoàn thành. Tiện ích bổ sung sẽ thực hiện công việc và hiển thị thông báo với mọi thứ được xử lý trong vài giây:
   

Công cụ này đã kết hợp các hàng trùng lặp trong danh sách sách của tôi. Đây là một phần của dữ liệu của tôi bây giờ:

Tiền boa. Hãy xem nhanh cách tôi sử dụng tiện ích bổ sung:

Tôi thực sự khuyến khích bạn tìm hiểu công cụ và các tùy chọn của nó tốt hơn, đối với Google Sheets là “tối và đầy kinh hoàng” nếu bạn hiểu ý tôi là gì;)

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

Source link

The post Hợp nhất các ô trong Google Sheets từ nhiều hàng thành một hàng dựa trên giá trị cộ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/hop-nhat-cac-o-trong-google-sheets-tu-nhieu-hang-thanh-mot-hang-dua-tren-gia-tri-cot.html