Hôm nay chúng ta hãy cùng tìm hiểu cách thêm hàng tổng nhóm vào Google Sheets mà không cần sử dụng Google Apps Script.
Tôi muốn nói đến việc thêm/insert một hàng dưới mỗi nhóm/danh mục chứa tổng số, ví dụ như tổng số tiền bán hàng của các nhóm, trong một dải mới.
Xin lưu ý, tôi không đang nói về hoặc chủ đề không phải về Dữ liệu (menu) > Nhóm. Bạn có thể đọc thêm ở đây – Cách nhóm hàng và cột trong Google Sheets.
Khi chúng ta có dữ liệu (bản ghi) liên quan đến doanh số bán hàng, mua hàng, chi phí hoặc bất kỳ danh mục nào được nhập vào bảng tính, chúng ta có thể sắp xếp dữ liệu đó thành các nhóm.
Ví dụ, tất cả các giao dịch mua hàng từ “công ty 1” sẽ được tổ chức thành một nhóm, “công ty 2” sẽ được tổ chức thành một nhóm khác, và cứ tiếp tục như vậy.
Nếu có một cột số tiền trong dữ liệu như vậy (thông thường sẽ có), liệu chúng ta có thể thêm/insert hàng tổng nhóm vào dữ liệu đó trong Google Sheets không?
Để thêm hàng tổng nhóm, thường chúng ta sẽ thêm thủ công các hàng trống dưới mỗi nhóm và sử dụng công thức SUM hoặc SUBTOTAL để thêm tổng nhóm (trong phần tài nguyên, bạn có thể tìm thấy chủ đề này).
Phương pháp thủ công trên có hai nhược điểm chính.
- Nó sẽ tốn rất nhiều thời gian.
- Nó sẽ làm cho dữ liệu của chúng ta không có cấu trúc. Công việc xử lý dữ liệu sẽ khó khăn trong dữ liệu không có cấu trúc như vậy.
Vậy tại sao không sử dụng một công thức trong một dải mới để thêm hàng tổng nhóm vào Google Sheets?
Dữ liệu của bạn có thể chứa nhiều nhóm. Bất kể bạn đã sắp xếp nó hay chưa, tôi có một công thức sẽ sắp xếp dữ liệu để tạo ra các nhóm và thêm các hàng tổng dưới mỗi nhóm.
Các bước để thêm hàng tổng nhóm vào Google Sheets
Đầu tiên, tôi sẽ cho bạn xem kết quả của công thức của tôi. Nó có thể phù hợp hoặc không phù hợp với bạn. Nhưng bạn có thể làm theo công thức vì tôi đã bao gồm một mẹo Sumif tuyệt vời sẽ hữu ích cho bạn trong tương lai.
[FIXME: Insert Image Here]
Có một công thức trong ô I1, nó sẽ thêm hàng tổng nhóm dưới mỗi nhóm và một công thức khác trong ô O1, nó sẽ thêm tổng của mỗi nhóm.
Cả hai công thức đều là công thức mảng. Vì vậy, bạn chỉ cần chèn chúng vào các ô đã nêu trên và quên điều đó. Công thức sẽ tự động xử lý dữ liệu được nhập vào trong tương lai của bạn (trong B1:G).
Trong bài hướng dẫn này, bạn có thể tìm hiểu cách thêm hàng tổng nhóm vào Google Sheets theo bốn bước dễ thực hiện. Chúng ta bắt đầu nhé!
Đặt tên duy nhất cho các nhóm và kết hợp chuỗi “Total” (Bước #1)
Để có được tên nhà cung cấp duy nhất và thêm chuỗi “Total”, chúng ta có thể sử dụng hàm UNIQUE (cho dữ liệu mẫu vui lòng tham khảo B1:G9 trên hình ảnh). Bạn sẽ hiểu mục đích của điều này trong quá trình học.
Giả sử tôi đã sử dụng công thức Unique dưới đây trong ô H2.
=unique(B2:B)
Tất nhiên, nó sẽ trả về các giá trị duy nhất từ B2:B như sau.
[FIXME: Insert Image Here]
Hàm Unique sẽ để lại một ô trống ở cuối vì chúng ta có dải mở B2:B trong Unique. Để thêm chuỗi “Total” vào các giá trị trên, chúng ta có thể sửa đổi công thức như sau.
=ArrayFormula( unique( B2:B&" Total" ) )
[FIXME: Insert Image Here]
Làm thế nào để loại bỏ chuỗi không mong muốn “Total” trong ô cuối cùng?
Để làm điều đó, chúng ta có thể sử dụng hàm Filter với Unique.
Công thức #1:
=unique( filter( B2:B&" Total",B2:B<>"" ) )
Tôi giả định công thức bước #1 dễ hiểu với bạn. Dưới đây là bước #2 để thêm hàng tổng nhóm vào Google Sheets.
Thêm cột trống với Kết quả Duy nhất của từng Cột Đơn (Bước #2)
Để chèn hàng tổng nhóm trên vào dữ liệu nguồn, chúng ta phải sửa đổi công thức duy nhất trên vì dữ liệu nguồn có tổng cộng 6 cột. Chúng là “Tên Nhà cung cấp”, “Số Hóa đơn”, “Số lượng”, “Đơn vị”, “Đơn giá” và “Số tiền”.
Chúng ta không thể kết hợp kết quả Duy nhất trên với dữ liệu nguồn do không phù hợp về số cột.
Chúng ta chỉ có thể kết hợp các mảng có kích thước tương tự. Vì vậy, chúng ta phải thêm 5 cột trống vào kết quả công thức duy nhất. Đây là cách làm.
Dải dữ liệu của chúng ta là B2:G. Chúng tôi đã tạo dữ liệu để điền vào cột B (công thức duy nhất). Những cột còn lại là C2:G.
Để tạo ra cột ô trống tương ứng với C2:G, hãy sử dụng kỹ thuật dưới đây. Tôi có nghĩa là chia các giá trị trong dải C2:G cho 0.
=filter(C2:G/0,B2:B<>"")
Hàm Filter trên sẽ tạo ra một loạt ô lỗi trong 5 cột. Chèn biểu thức này vào công thức #1 dưới đây và xem kết quả.
=unique({ filter( B2:B&" Total",B2:B<>"" ), filter( C2:G/0,B2:B<>"" ) })
[FIXME: Insert Image Here]
Để loại bỏ lỗi và biến các ô thành trống, thay thế C2:G/0 bằng iferror(C2:G/0).
Công thức #2:
=unique({ filter( B2:B&" Total",B2:B<>"" ), filter( iferror(C2:G/0),B2:B<>"" ) })
Bây giờ chúng ta đã sẵn sàng để chèn hàng tổng nhóm (không có cột tổng) trong dữ liệu nguồn. Xem bước #3 để làm điều đó.
Chèn hàng tổng nhóm (Bước #3)
Để kết hợp hai dải dữ liệu/mảng, chúng ta có thể sử dụng Dấu ngoặc nhọn. Dưới đây là công thức chung phù hợp với tình huống của chúng ta.
={B1:G;formula_2}
Vì vậy, công thức sẽ là;
={B1:G; unique({ filter( B2:B&" Total",B2:B<>"" ), filter( iferror(C2:G/0),B2:B<>"" ) }) }
Sử dụng công thức này trong bảng tính của bạn sẽ làm cho bảng tính bị đóng băng vì B1:G chứa nhiều hàng trống. Chúng ta có thể lọc ra những hàng không mong muốn bằng cách sử dụng Query.
Vì vậy, đây là công thức Query đó để chèn hàng tổng nhóm (không có cột tổng) vào Google Sheets.
=query( {B1:G; unique({ filter( B2:B&" Total",B2:B<>"" ), filter( iferror(C2:G/0),B2:B<>"" ) }) }, "Select * where Col1 is not null order by Col1 asc" )
Ngoài việc loại bỏ các hàng trống, tôi đã bao gồm mệnh đề “Order by” để sắp xếp dữ liệu. Điều đó đảm bảo các hàng tổng được chèn sẽ được đặt đúng vị trí dưới mỗi nhóm.
[FIXME: Insert Image Here]
Bây giờ chúng ta đã đến bước cuối cùng của việc chèn hàng tổng nhóm vào Google Sheets.
Thêm cột tổng vào các nhóm (Bước #4)
Trong ô O1, hãy chèn công thức SUMIF sau đây, đó là một công thức khó khăn.
={"Total"; ArrayFormula( sumif(I2:I&" Total",I2:I,N2:N) ) }
Công thức trên đặt tổng của các nhóm vào hàng chứa “Tổng” trong cột I.
Tất cả các hàng khác sẽ có giá trị 0, mà tôi đã ẩn đi bằng cách định dạng có điều kiện. Những mẹo này (ẩn) được giải thích chi tiết ở đây – Hai cách để loại bỏ các giá trị 0 trong Google Sheets [Cách làm].
Giải thích Công thức:
- “Total” – đặt tiêu đề.
- I2:I&” Total” – Phạm vi Sumif.
- I2:I – Tiêu chí Sumif.
- N2:N – Phạm vi Sum.
Tôi đã thêm giá trị “Total” vào tất cả các giá trị trong dải I2:I (trong phạm vi Sumif). Cùng một lúc, các tiêu chí I2:I chỉ chứa giá trị “Total” kèm theo một số giá trị khác.
Vì vậy, kết quả chỉ xuất hiện trong các hàng chứa “Total” vật lý trong I2:I.
Đó là tất cả về cách thêm hàng tổng nhóm vào Google Sheets.
Ví dụ_bảng_9620
Tài nguyên:
- Cách tính tổng đến ô trống đầu tiên trong một cột trong Google Sheets.
- Nhóm và Tổng phụ trong Google Sheets và Excel.
- Chỉ lấy tổng khi Nhóm | Nhóm phụ bị thu gọn trong Google Sheets.
- Tự động thêm tổng vào dữ liệu đã lọc trong Google Sheets.
