HƯỚNG DẪN THÊM HÀNG TOTAL BẰNG HÀM QUERY TRONG GOOGLE SHEETS

Trong bài viết này, Gitiho sẽ hướng dẫn các bạn cách thêm hàng TOTAL vào dữ liệu được tạo bằng hàm QUERY trong Google Sheets chỉ với ký hiệu dấu ngoặc nhọn {...} thay vì sử dụng hàm ARRAYFORMULA.

Ta sẽ thêm hàng TOTAL bằng cách sử dụng công thức mảng của dạng này: 

= { QUERY ; { "TOTAL" , SUM(range) } }

Mình chắc chắn nhiều bạn sẽ thắc mắc: "Tại sao ta không dùng trực tiếp hàm SUM()?"

Câu hỏi rất hay! Đó là bởi vì dòng TOTAL ở đây sẽ được thêm một cách chủ động. Do đó, dòng này sẽ được nối trực tiếp vào cuối bảng/dữ liệu và sẽ không bị hỏng nếu bảng hoặc dữ liệu bị thay đổi. Ngoài ra, dòng TOTAL này thường sẽ di chuyển lên hoặc xuống nên có thể nói nó luôn cố định như là hàng cuối cùng của bảng hoặc dữ liệu.

Ví dụ về cách thêm hàng TOTAL trong Google Sheets

Trước khi đến với ví dụ về hàm QUERY, chúng ta hãy thử một ví dụ cực kỳ đơn giản để hiểu cơ chế của các công thức mảng mà chúng ta sẽ tạo nhé!

Giả sử ta có dữ liệu sau: 

BƯỚC 1: Kết hợp bảng bằng cách sử dụng mảng

Đầu tiên, ta hãy tạo hàng TOTAL theo cách thủ công bên cạnh bảng dữ liệu của ta trong các ô D1 & E1 như sau:

Sau đó, ta có thể sử dụng công thức sau đây trong ô G1, để kết hợp hai bảng này thành một bảng duy nhất:

={A1:B2;D1:E1}

Cú pháp chỉ đơn giản là một cặp dấu ngoặc nhọn {} và dấu chấm phẩy (;) thể hiện hai bảng trên sẽ được kết hợp theo chiều dọc.

Lưu ý: Đối với cú pháp này thì hai bảng kết hợp phải có số cột bằng nhau.

BƯỚC 2: Sử dụng mảng để tạo dòng TOTAL 

Thay vì nhập "TOTAL" vào một ô và một số vào ô liền kề thì ta chỉ cần tạo dòng TOTAL bằng một công thức:

={"TOTAL",3}

Cú pháp là một cặp dấu ngoặc nhọn {} và dấu phẩy (,) thể hiện hai phần tử trên được kết hợp theo chiều ngang.

Lưu ý: Mỗi phần tử trong cú pháp phải có số hàng bằng nhau

BƯỚC 3: Sử dụng công thức hàm SUM trong bảng TOTAL

={"TOTAL",sum(B1:B2)}

BƯỚC 4: Cách thêm hàng TOTAL vào bảng với mảng

Sử dụng công thức ở BƯỚC 1, ta thay thế phần tử thứ hai (tức D1: E1) bằng công thức ở BƯỚC 3, vì vậy công thức đầu ra của ta bây giờ là:

={A1:B2;{"TOTAL",sum(B1:B2)}}

Và đây là kết quả:

BƯỚC 5: Thụt đầu dòng trong thanh công thức

Để làm cho công thức dễ đọc hơn một chút, ta chỉ cần ngắt dòng (Ctrl + Enter) và thụt lề (Indentation):

={
    A1:B2
  ;
    {
      "TOTAL",
      sum(B1:B2)
    }
  }

Ví dụ về cách thêm hàng TOTAL bằng hàm QUERY

Đối với ví dụ này, mình sẽ sử dụng một số dữ liệu của thành phố New York, cụ thể là dữ liệu về số lượng các chi nhánh đang mở của các cơ quan khác nhau trong thành phố.

Những gì mà mình muốn làm là tổng hợp số lượng các chi nhánh cho từng đại lý, tức là kết hợp tất cả các dòng đại lý thành một dòng duy nhất với tổng số chi nhánh cho đại lý đó

Mình đang “nhóm” dữ liệu của mình thành các danh mục được liệt kê trong cột A và thêm tất cả các giá trị trong cột B vào từng danh mục đó.

Để tổng hợp số lượng dữ liệu về các chi nhánh cho mỗi đại lý, mình sử dụng hàm QUERY với mệnh đề kèm theo đó là cột: 

=QUERY($A$11:$B$61,"select A, sum(B) group by A order by sum(B) desc label sum(B) 'Total Positions'",1)

Và sẽ cho ra kết quả như sau:

Cách thêm hàng TOTAL

Về cơ bản những gì mình đang làm giống hệt như ví dụ đơn giản ở trên, tạo hai bảng riêng biệt (một bảng là bảng tóm tắt dữ liệu và một bảng là hàng TOTAL) và sau đó, mình sử dụng công thức mảng để kết hợp chúng thành một bảng duy nhất.

Dưới đây là một công thức giả định để minh họa những gì mình đang làm:

= { QUERY ; TOTAL }

Lưu ý: Các bạn nhớ sử dụng dấu chấm phẩy (;) trong công thức này nhé!

Và sau đó, TOTAL thực sự là công thức mảng của chính nó như sau:

{ "TOTAL" , SUM(range) }

Lưu ý: còn ở đây thì ta dùng dấu phẩy (,) nhé!

Và công thức cuối cùng có dạng:

= { QUERY ; { "TOTAL" , SUM(range) } }

Đến đây, ta sẽ lồng hàm QUERY vào bên trong công thức mảng và hàm SUM cho tổng:

={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)}}

Ngoài ra, để công thức cho dễ đọc hơn thì bạn có thể ngắt dòng và thụt lề bằng cách nhấn phím tắt Ctrl + Enter để xuống dòng trong thanh công thức:

={
  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)
  }
}

Hình ảnh dưới đây cho thấy cách các phần khác nhau của công thức truy xuất dữ liệu trong bảng:

Cách làm cho hàng TOTAL "động"

Bước cuối cùng nhưng không kém phần quan trọng đó là làm cho hàng TOTAL trở nên "động". Nghĩa là hàng TOTAL sẽ linh động di chuyển theo dữ liệu khi dữ liệu thay đổi:

Có hai việc ta cần làm: 

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

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

={
  QUERY(
    $A$11:$B,
    "select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) 'Total Positions'",
    1
  )
;
  {
    "TOTAL",
    SUM($B$11:$B)
  }
}

Bộ lọc cho mệnh đề WHERE 

Để chắc chắn rằng dòng TOTAL chỉ bao gồm các giá trị có liên quan đến tổng số của bạn thì bạn cần thêm một bộ lọc đó vào hàng tính tổng đó.

Ví dụ, mình muốn hiển thị các hàng bắt đầu bằng chữ DEPT thì mình sử dụng bộ lọc LIKE trong mệnh đề WHERE để cả hàng chính và hàng mới sẽ được thêm vào trong hàng TOTAL:

={
  QUERY(
    $A$11:$B,
    "select A, sum(B) where A is not null and A like 'DEPT%' group by A order by sum(B) desc label sum(B) 'Total Positions'",
    1
  )
;
  {
    "TOTAL",
    SUM(QUERY($A$11:$B,"select B where A like 'DEPT%'",1))
  }
}

Trên đây là những hướng dẫn về cách thêm dòng TOTAL bằng hàm QUERY trong Google Sheets. Ngoài ra, các bạn có thể tham khảo khóa học TẠI ĐÂY của Gitiho để nâng cao hiệu quả làm việc trên máy tính nhé. Đây là các khóa học cung cấp rất đầy đủ và chi tiết các kiến thức từ cách sử dụng Excel, Word, Google Sheets,... đến các nghiệp vụ kế toán, lập trình. Bạn sẽ thấy rõ sự khác biệt rõ rệt sau khi tham gia khóa học này. 

CÓ THỂ BẠN CŨNG QUAN TÂM:

Các phím tắt thông dụng trong Google Sheets

Hướng dẫn cách sử dụng hàm truy vấn trong Google Sheets

Hướng dẫn cách chèn thời gian (giờ - phút - giây) trong Google Sheets