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

Văn Vũ Như Quỳnh
Văn Vũ Như Quỳnh
Aug 12 2020

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.

Thêm dòng TOTAL bằng hàm QUERY trong Google Sheets

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.

Top khóa học Google Sheets được học nhiều

Xây dựng bảng lương bằng Google Sheets cơ bản từ A-Z

Tô Văn Thuật

578 học viên

0 (0 đánh giá)

Miễn phí

Xây dựng file thông tin quản lý hồ sơ nhân sự bằng Google Sheets từ A-Z

Tô Văn Thuật

521 học viên

5 (1 đánh giá)

Miễn phí

Google Sheets từ Cơ bản đến Nâng cao, công cụ thay thế Excel

Nguyễn Văn Qúy

379 học viên

5 (5 đánh giá)

499,000đ

799,000đ

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: 

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

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:

Tạo hàng TOTAL thủ công | Cách thêm hàng TOTAL trong gg sheets

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.

Cách thêm hàng TOTAL trong gg sheets

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ả:

Cách thêm hàng TOTAL bằng hàm QUERY trong gg sheets

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ố.

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

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:

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

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òngthụ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 thêm dòng TOTAL bằng hàm QUERY trong gg sheets

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ách làm dòng TOTAL

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

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn tạo danh sách tùy chọn Drop List trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách cố định hàng/cột trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách bảo vệ trang tính trong Google Sheets

Hướng dẫn cách khắc phục lỗi về ô và dòng trong Google Sheets

Hướng dẫn cách khắc phục lỗi về ô và dòng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Tổng hợp các phím tắt thông dụng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

Hướng dẫn tô màu xen kẽ giữa các hàng trong Google Sheets

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội