Hàm subtotal là gì trong excel? Cách sử dụng hàm subtotal

SUM là một hàm quen thuộc được sử dụng nhiều trong Excel để phục vụ việc tính toán. Tuy nhiên, ở một vài trường hợp đặc thù hoặc phức tạp hơn thì các bạn còn có thể sử dụng hàm Subtotal. Vậy hàm Subtotal là gì, cách sử dụng cụ thể như thế nào? Cùng xem qua bài viết bên dưới từ muahangdambao.com các bạn nhé!

Hàm subtotal trong Excel là gì?

Trong tiếng Anh thì từ Subtotal được dịch ra tiếng Việt với ý nghĩa là tính tổng phụ. Theo đó, Subtotal là một hàm đặc biệt dùng để tính tổng phụ của một bảng, một cơ sở dữ liệu nào đó trong Excel. Sự ra đời của hàm Subtotal này đã mang đến rất nhiều các lợi ích cho người sử dụng, đặc biệt là những người thường phải làm việc với các bảng tính toán như kế toán.

Hàm Subtotal là hàm gì?
Hàm Subtotal là hàm gì?

Hàm Subtotal đã được đưa vào ứng dụng ngay từ phiên bản Excel 2003, dạng hàm này có thể giúp người dùng gia tăng thêm các tùy chọn cho số đầu tiên của hàm so với hàm Sum trước đó.

Từ đây, giúp đơn giản hóa cho những người phải làm việc với các con số lớn, phức tạp. Thế nhưng hàm này lại bắt buộc phải ghi nhớ được đối số đầu tiên bởi nó sẽ đại diện cho phép tính của bảng dữ liệu đó. Bắt đầu từ phiên bản Excel 2007 thì hệ thống này đã lại được nâng cấp, giúp hàm này có thể tự ghi nhớ các đối số để tránh gây nhầm lẫn hay quên.

Ý nghĩa hàm Subtotal là gì?

Với những thông tin kể trên, chúng ta có thể dễ dàng thấy được rằng hàm Subtotal thực sự là một công cụ vô cùng hữu ích và thông minh, giúp đơn giản hóa những công việc liên quan đến tính toán số liệu. Đặc biệt có ý nghĩa cho những người làm nghề kế toán – thường xuyên phải tính nhiều cơ sở dữ liệu. Theo đó, các bạn có thể nắm bắt rõ ràng được số tiền lời, tiền phát sinh, tiền tồn lại chỉ với 1 file Excel vô cùng dễ hiểu.

Với hàm Subtotal thì cách sử dụng cũng không quá khó, chỉ cần học qua các thao tác cơ bản thôi là đã có thể mang đến hiệu quả tốt rồi. Chính vì lợi ích đó mà hàm Subtotal đang là một trong những hàm phổ biến nhất trong Excel cũng như được nhiều người ứng dụng nhất trong công việc hàng ngày.

Hướng dẫn cách sử dụng hàm Subtotal chuẩn xác nhất

Để nắm được cách dùng hàm Subtotal 1 cách thành thạo và chính xác nhất thì bạn cần thực hiện theo những bước chỉ dẫn cụ thể dưới đây:

Bước 1: Đầu tiên bạn hãy mở trang tính Excel cần thực hiện tính toán ra. Sau đó, cần xác định chính xác những lệnh áp dụng trong trang tính này như là tính tổng, tính trung bình cộng,… phụ thuộc vào thông tin mà bạn cần.

Bước 2: Nhập lệnh như sau “=SUBTOTAL (lựa chọn hàm số mà bạn cần tính (trung bình cộng hoặc tính tổng) khai báo khoanh vùng cần tính hoặc dùng chuột) sau đó ấn Enter trên bàn phím là được.

Nếu bạn muốn tính tổng tiền dựa theo tên của khách hàng thì cần biết cách sử dụng data Subtotal. Đầu tiên bạn phải sắp xếp lại bảng tên bằng cách bôi đen hết cột Tên khách hàng -> Chọn Data -> Chọn Sort.

Cách sử dụng hàm Subtotal vô cùng đơn giản và dễ dàng
Cách sử dụng hàm Subtotal vô cùng đơn giản và dễ dàng

Lúc này sẽ có 1 hộp thoại mới xuất hiện, bạn bấm chọn Continue with the current selection -> Chọn Sort. Sau đó, hãy sắp xếp lại thứ tự các tên theo đúng bảng chữ cái Alphabet, chữ trùng lặp thì để cạnh nhau rồi ấn OK

  • Muốn tính được tổng số lượng sản phẩm và tổng tiền của khách hàng: Cần bôi đen vùng chứa dữ liệu -> Chọn Data -> Chọn Subtotal.
  • Lựa chọn tên khách hàng mà bạn cần tính: Click chuột vào At each change in
  • Lựa chọn số 9 chính làm hàm tính tổng (theo dữ liệu ví dụ đã cho sẵn) mặc định tính hàm cần tính: Bấm chọn Use function
  • Tìm kết quả click chuột vào ô số lượng và thành tiền: Nhấp chọn Add subtotal to. Sau khi đã thao tác xong ấn “OK” là được.

Với những hàm tính còn lại, hàm Subtotal cũng có các thao tác tương tự nhưng nếu bạn muốn có được nguyên bản dữ liệu như lúc đầu thì chỉ cần thao tác lần lượt như sau: Mở hộp thoại Subtotal -> Click Remove all là xong.

Chỉ cần thực hiện một vài lần theo các bước hướng dẫn là bạn sẽ thấy được hàm Subtotal rất dễ sử dụng và không hề phức tạp như nhiều người tưởng tượng. Bởi vậy, bạn nên áp dụng hàm này thường xuyên hơn vào trong quá trình tính toán vì nó sẽ là công cụ hỗ trợ tối ưu nhất để công việc của bạn có thể hoàn thành một cách hiệu quả và nhanh chóng.

Xem thêm: Ký hiệu phi ø là gì? Cách viết, chèn kí tự phi trong word, excel, autocad

Hàm Subtotal khác gì hàm Sum?

Đối với hàm Sum trong Excel

Mục đích

Tính tổng chung hoặc cố định cho tất cả dữ liệu mà không cần phải tách nhỏ. Có thể áp dụng cho mọi đối tượng (thực hiện lệnh Filter cũng không làm giá trị này thay đổi). Số liệu sẽ được tính 2 lần khi thao tác lồng các hàm Sum lại với nhau.

Cú pháp sử dụng: SUM (number1; [number2];…)

Cụ thể trong đó:

  •  Số đối sẽ được giới hạn tới thứ tự 255 và đối số đầu tiên (Trường bắt buộc).
  •  Các đối số sẽ là giá trị lẻ, tham chiếu hoặc ở trong phạm vi hay kết hợp với cả 3.
  •  Đối số là tham chiếu hay mảng thì chỉ số của các số ở trong tham chiếu hoặc mảng này sẽ được tính. Các ô trống mang giá trị logic, văn bản hoặc văn bản trong tham chiếu hoặc mảng sẽ bị bỏ qua.
Cách sử dụng hàm Sum trong Excel như thế nào?
Cách sử dụng hàm Sum trong Excel như thế nào?
  •  Với bất kỳ đối số nào là giá trị lỗi hoặc đối số là văn bản cụ thể không thể được diễn dịch thành số và dạng Excel sẽ không hiển thị kết quả và báo lỗi.

Trường hợp sử dụng đến hàm Sum

  •  Khi chúng ta cần tính tổng phát sinh khoản nợ trong sổ NKC.
  •  Tính chiết khấu hao trong các bảng phân bổ.

Đối với hàm Subtotal trong Excel

Mục đích

Hàm này được dùng để tính tổng cố định hoặc di động, có nghĩa là tính tổng riêng cho từng nhóm có mang giá trị thay đổi khi được áp dụng lệnh Filter. Số liệu không bị tính 2 lần khi đã lồng các hàm subtotal lại với nhau.

Cú pháp sử dụng: SUBTOTAL (function_num;ref1;ref2;…)

Cụ thể trong đó là:

  •  Function_num (là số đại diện cho phép toán số liệu ở trong subtotal): Từ 1 đến 11 là những con số thường được áp dụng . Với phiên bản Excel 2003, 2007 thì sẽ có thêm từ 101 cho đến 111. Số 9 sẽ được sử dụng thay thế cho hàm Sum.
  •  Số từ 1 đến 11: Thì hàm sẽ được tính tất cả giá trị bị ẩn trên lệnh Hide trong vùng dữ liệu được chọn.
  •  Số từ 101-111: Hàm sẽ không tính đến những giá trị đã bị ẩn bằng lệnh Hide. Ngoài ra, subtotal cũng không tính được các giá trị bị ẩn bằng lệnh Filter.
  •  Có thể áp dụng cho các tính toán giá trị theo định dạng cột.

Trường hợp sử dụng đến hàm Subtotal

  •  Những chi phí tài khoản vô tình phát sinh trong bảng dữ liệu kế toán.
  •  Tính tổng số tiền mặt, tiền gửi ngân hàng còn dư ra trong ngày.

Xem thêm: Cách dùng hàm if trong excel với nhiều điều kiện

Những lỗi thường gặp khi dùng hàm Subtotal trong Excel

Khi áp dụng hàm Subtotal mà bị trả về kết quả lỗi thì rất có thể bạn đã gặp phải một trong số những nguyên nhân cơ bản dưới đây:

  •  Không thể xác định chính xác được những chức năng trong các số từ 1 cho đến 11 (hay từ 101-111) trong cùng một tham chiếu (ref) hoặc là tham chiếu 3D (#VALUE!)
  •  Một tổng cụ thể đang phải chia cho giá trị bằng 0 (#DIV/0!).
  •  Có lỗi sai chính tả tên hàm subtotal (#NAME?).

Ứng dụng của hàm Subtotal trong công việc hàng ngày

Dưới đây là một số cách cơ bản để ứng dụng hàm SUBTO trong thực tế. Nếu chưa biết thì mời bạn cùng tham khảo với chúng tôi nhé!

Giả sử bạn đang có 1 bảng dữ liệu và cần lọc ra tổng KPI của đội A thì bạn cần thực hiện những thao tác như sau:

Bước 1: Bôi đen phần bảng có chứa những dữ liệu cần tính toàn. Bấm chọn Home => Nhấp chuột vào Sort and Filter => Sau đó lựa chọn Filter.

Bước 2: Hãy bấm chuột và chọn dấu mũi tên hình tam giác ngược ở phần tiêu đề cột “Đội” sau đó tick chọn “A” và nhấn OK là xong.

Bước 3: Bảng dữ liệu của đội A được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập: =SUBTOTAL(9,D2:D11)

Dùng SUBTOTAL để đánh số thứ tự tự động

Tuy nhiên, nếu không may gặp phải một dữ liệu “khó nhằn” với các hàng trống và không trống lộn xộn vào nhau, bạn sẽ cần kết hợp cả SUBTOTAL với hàm IF. Số thứ tự của các giá trị được chọn sẽ được đánh lại từ đầu khi bắt đầu thực hiện lọc dữ liệu và cách này cũng sẽ chỉ được áp dụng được khi đánh thứ tự theo cột mà thôi.

Cú pháp tổng quát của hàm này như sau: =IF (logical_test, value_if_true, SUBTOTAL (funtion_num, ref1,…)).

Cách làm như sau: Tại ô A2 bạn cần nhập công thức là: =IF (B2 =””,””, SUBTOTAL (3,$B$2:B2)).

Từ đây có thể suy luận như sau: Hàm chính cần dùng sẽ là IF và nếu như có một ô rỗng không thì chúng ta chỉ cần bỏ qua và không đếm đến nó. Nếu ô không rỗng thì hãy dùng SUBTOTAL để có thể đánh số thứ tự.

Subtotal có thể được ứng dụng để đánh số tự động
Subtotal có thể được ứng dụng để đánh số tự động

Công thức của hàm IF là: = IF (logical_test, value_if_true, value_if_false), với:

  • logical_test: Chính là điều kiện của hàm IF.
  • value_if_true: Giá trị được trả về nếu điều kiện là đúng.
  • value_if_false: Giá trị được trả về nếu điều kiện sai. Ở đây chúng ta thường dùng hàm SUBTOTAL.

Các đối số sẽ phải điền trong hàm subtotal(9) là:

  • function_num = 3 sẽ ứng với cách tính là COUNTA – và đếm ô không trống.
  • ref1 = $B$2:B2: Chính là đếm các ký tự phát sinh trong khoảng từ $B$2:B2, trong đó ô B2 được cố định bằng dấu “$” ở ngay trước địa chỉ hàng và ô. Nếu ta thực hiện các bước sao chép công thức xuống dưới thì giá trị sẽ được thay đổi theo kiểu: $B$2:B3, $B$2:B4, $B$2:B5,… và hàm sẽ được đếm đúng và mang lại kết quả như mong muốn.

Hi vọng với những chia sẻ hữu ích trên đây của muahangdambao.com sẽ giúp các bạn có thêm những kiến thức về hàm Subtotal trong phần mềm Excel. Đặc biệt là đối với những bạn chưa biết cách ứng dụng hàm Subtotal thông thạo thì qua bài viết này đã có thể áp dụng chúng trong công việc của mình. Có thể nói đây chính là những kỹ năng tin học văn phòng cơ bản mà chắc chắn các bạn cần phải nắm rõ để công việc đạt hiệu quả nhất nên đừng bỏ qua thông tin này nhé! Chúc các bạn luôn thành công !

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *