Hướng dẫn cách thêm dòng tổng kết vào bảng trong Google Sheets sử dụng hàm Query

Trong bài viết này. hãy cùng Gitiho tìm hiểu về cách thêm một hàng tính tổng vào bảng sử dụng hàm QUERY trong Google Sheets nhé. Đây là một cách rất hay trong việc sử dụng các công thức mảng (array formula), sử dụng các dấu ngoặc {...} thay vì sử dùng dấu ngoặc thông thường trong các công thức Array.Formula.

Vậy tất cả những gì tôi nói ở trên có nghĩa là gì?

Ý của tôi ở đây nghĩa là chúng ta sẽ xem xét cách để thêm một hàng tính tổng như thế này: 

Để tạo được một dòng tính tổng, chúng ta sử dụng công thức mảng của dạng này: = { QUERY ; { "TOTAL" , SUM(range) } }

Và chắc bạn đang tự hỏi là tại sao phải sử dụng hàm QUERY để tạo ra một dòng tính tổng thay vì chỉ tạo một dòng mới và sử dụng hàm Sum ( =Sum(range)) như mọi người thường hay làm? 

Đây là một câu hỏi hay do đó Gitiho sẽ giải thích cho bạn. Lý do chúng ta sử dụng phương pháp này là vì dòng tính tổng khi được thêm sử dụng hàm QUERY sẽ là một dòng động và sẽ được đặt trực tiếp vào cuối bảng tính của bạn. Do đó, cho dù bạn có thêm bao nhiều dòng dữ liệu hay xóa bớt một số dòng thì dòng tính tổng của chúng ta vẫn hoạt động bình thường và không trả ra kết quả Error như hàm Sum mà bạn hay dùng.

Đây là một dòng động (dynamic) vì vậy nó vẫn sẽ di chuyển theo các thay đổi mà bạn thêm vào bảng nhưng vị trí của nó vẫn giữ nguyên ở cuối bảng tính của bạn.

Ví dụ đơn giản về cách tạo dòng tính tổng

Trước khi đề cập tới ví dụ về cách dùng hàm QUERY, chúng ta hãy cùng tìm hiểu về một ví dụ điển hình để nắm rõ cách hoạt động của các công thức mảng mà chúng ta sẽ sử dụng nhé.

Bạn hãy giả sử chúng ta đang cần phân tích tập dữ liệu này:

Và khi bắt đầu tìm hiểu một chủ đề mới, hãy bắt đầu với những thứ căn bản nhất.

Bước 1: Kết hợp các bảng tính sử dụng mảng (array)

Thao tác đầu tiên là bạn cần tạo thủ công một dòng tính tổng bên cạnh bảng tính ban đầu của chúng ta, trong cell D1 và E1 như sau:

Sau đó, chúng ta có thể sử dụng công thức sau, trong cell G1, để kết hợp hai bảng tính này thành một bảng duy nhất: ={A1:B2;D1:E1}

Và thao tác chúng ta vừa thực hiện sẽ tương tự như hình 3: 

Cú pháp trong thanh công thức là dấu mở và đóng ngoặc nhọn cùng với dấu chấm phẩy để hướng dẫn Google Sheets là 2 bảng tính này cần được kết hợp theo hàng dọc.

Để đạt được kết quả, mỗi bảng phải có chính xác số cột như nhau.

Bước 2: Tạo dòng tính tổng bằng cách sử dụng mảng.

Bây giờ chúng ta hãy sử dụng một công thức mảng để tạo dòng tính tổng đó. Thay vì phải thao tác thủ công, nhập "Total" vào một cell và một tổng các con só vào cell liền kề, chúng ta có thể thực hiện thao tác tạo dòng tính tổng đó chỉ bằng một công thức: ={"TOTAL",3}

Cú pháp trong thanh công thức là dấu mở và đóng ngoặc nhọn cùng với dấu phẩy để hướng dẫn Google Sheets là 2 bảng tính này cần được kết hợp theo hàng ngang.

Để đạt được kết quả, mỗi bảng phải có chính xác số dòng như nhau.

Bước 3: Sử dụng hàm SUM trong bảng array tính tổng

Chúng ta có thể bao gồm hàm SUM trong công thức trên như sau: ={"TOTAL",sum(B1:B2)}

Bước 4: Cách thêm hàng tính tổng vào bảng sử dụng mảng

Để thực hiện bước này, chúng ta cần phải sử dụng đến công thức ở Bước 1, thay thế yếu tố thứ hai trong mảng (phần D1: E1) với công thức ở trong Bước 3, và công thức cuối cùng mà bạn sẽ sử dụng là: ={A1:B2;{"TOTAL",sum(B1:B2)}}

Vầ dưới đây là kết quả mà Google Sheets sẽ trả về cho bạn:

Bước 5: Sử dụng việc lùi và cách quãng trong thanh công thức

Đây chỉ đơn giản là một bước trình bày, mục đích là để giúp người dùng có thể dễ dàng đọc công thức hơn khi nhập vào thanh công thức. Việc bạn cần làm là thêm một vài thao tác xuống hàng và lùi đầu dòng trước các yếu tố trong công thức trên:

Bạn đã sẵn sàng chưa? Sau khi đã tìm hiểu về ví dụ đơn giản này, bây giờ chúng ta hãy quay lại với ví dụ chính.

Ví dụ về cách tạo hàng tính tổng sử dụng hàm QUERY

Trong ví dụ này, chúng ta sẽ sử dụng một số dữ liệu về thành phố New York, cụ thể là dữ liệu về số lượng vị trí được tuyển dụng cho các công ty khác nhau đang hoạt động trong thành phố:

Yêu cầu của chúng ta là tóm tắt số lượng vị trí cho mỗi cơ quan, đồng nghĩa với việc chúng ta phải tổng kết tất cả vị trí đang được đăng tuyển cho một công ty và gộp chúng vào một dòng. Tiếp theo là nhóm chúng vào từng danh mục riêng trong danh sách được liệt kê ở cột A với các giá trị tương ứng với từng danh mục của nó trong cột B.

Để có thể thực hiện được thao tác trên, chúng ta cần sử dụng hàm QUERY với mệnh đề group by, để tổng hợp dữ liệu về số lượng vị trí cho mỗi doanh nghiệp: =QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",1)

Dưới đây sẽ là kết quả: 

Chúng ta đã đạt được kết quả như mong muốn sau khi sử dụng các bước ở trên. Vậy việc tiếp theo là làm thế nào để thêm hàng tính tổng đó?

Cách để thêm hàng tổng cộng

Nói một cách đơn giản, những gì chúng ta đang thực hiện giống hệt như ví dụ căn bản mà chúng ta đã nói đến trong các bước trên. Bước đầu tiên là tạo 2 bảng riêng biệt (một bảng là bảng tóm tắt, tương tự như bảng ở trên và bảng còn lại là bảng tính tổng). Sau đó, chúng ta sử dụng công thức mảng để kết hợp hai bảng lại thành một.

Dưới đây là một công thức ví dụ để minh họa cho những gì mà chúng ta đang thực hiện: = { QUERY ; TOTAL } < bạn hãy chú ý đến cách dùng dấu chấm phẩy trong công thức ;

Tiếp theo là công thức tính tổng (Total), đây thực sự là một công thức mảng của riêng nó như chúng ta thấy: { "TOTAL" , SUM(range) } <--  bạn hãy chú ý đến cách dùng dấu phẩy trong công thức ,

Và công thức cuối cùng của chúng ta, một công thức mảng chồng với nhau, có dạng sau: = { QUERY ; { "TOTAL" , SUM(range) } }

Bước cuối cùng là chúng ta lồng hàm QUERY vào trong công thức mảng, với một mảng thể hiện phần tổng cộng sử dụng hàm SUM: ={QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",1);{"TOTAL",SUM($B$11:$B$61)}}

Thao tác tùy chọn:

Như Bước 5 ở trên, chúng ta hãy thêm một số chỗ xuống dòng cũng như lùi giữa các yếu tố để làm cho công thức trở nên gọn gàng và dễ đọc hơn một chút. Bạn cũng có thể làm điều này trong thanh công thức của mình! Sử dụng tổ hợp phím Ctrl + Enter để chèn các dòng mới vào công thức của bạn và làm cho chúng dễ nhìn hơn!

Hình ảnh dưới đây thể hiện cách các yếu tố khác nhau trong công thức tác động đến cách thể hiện dữ liệu trong bảng, với cú pháp mảng được thể hiện bằng màu đỏ:

Thao tác cuối cùng để làm cho bảng biểu của bạn động (dynamic):

Một điều chỉnh cuối cùng mà chúng ta cần thực hiện nếu chúng ta muốn bảng tính của chúng ta trở nên động, để hàng tổng cộng di chuyển lên hoặc xuống trong bảng biểu khi chúng ta thêm hoặc xóa dữ liệu tương tự như sau:

Có hai điều chúng ta cần làm:

1. Điều chỉnh phạm vi để bao gồm cả hai cột A và B
2. Điều chỉnh hàm QUERY để loại bỏ các dòng trống được thêm vào sau Bước 1.

Bạn có thể điều chỉnh phạm vi bằng cách xóa tham chiếu dòng 61 trong công thức ($61) để chúng ta có thể bao gồm toàn bộ hai cột A và B. Sau đó, chúng ta cần điều chỉnh hàm QUERY bằng cách thêm mệnh đề WHERE để loại bỏ tất cả các dòng trống khỏi cột A.

Công thức cuối cùng sẽ là:

Trong trường hợp cần phải thêm một mệnh đề WHERE có bộ lọc thì sao?

Bạn chắc chắn đã quên tới việc phải thêm mệnh đề WHERE để lọc các dòng trống đúng không nào? Để thực hiện việc này, bạn cần thêm cùng bộ lọc vào dòng tính tổng, khi làm vậy, bạn có thể chắc chắn rằng bạn chỉ bao gồm các dữ liệu có liên quan khi Google Sheets tính tổng cho bạn.

Ví dụ như chúng ta muốn chỉ thể hiện các dữ liệu bắt đầu bằng DEPT. Để làm vậy, chúng ta cần phải sử dụng bộ lọc LIKE trong mệnh đề WHERE của chúng ta, cả mệnh đề chính và bộ lọc mà chúng ta mới nói ở trong dòng tính tổng: 

Vậy trong bài viết này bạn đã nắm thêm một chút kiến thức về công cụ Google Sheet rồi, cụ thể là về cách sử dụng hàm QUERY để thêm dòng tính tổng vào bảng trong Google Sheets. Để tìm hiểu thêm về các kiến thức liên quan đến Google Sheet, bạn hãy truy cập trang gitiho.com nhé.