Ứng Dụng Vượt Trội Của Hàm Subtotal Trong Excel

XEM XÉT HÀM SUBTOTAL – CÁCH DÙNG VÀ CÚ PHÁP

Trong Excel, hàm SUBTOTAL được tạo ra nhằm mục đích tính các tổng phụ trong cơ sở dữ liệu hay danh sách. “Tổng phụ” ở đây không đồng nghĩa với việc cộng tất cả số liệu trong một phạm vi ô cố định. Khác với các hàm tính năng đặc thù khác trong Excel, hàm SUBTOTAL phô diễn sự đa năng của mình khi có khả năng xử lý các phép toán logic như đếm, trung bình, xác định giá trị lớn nhất/nhỏ nhất và nhiều hơn thế nữa.

Hàm này có thể tìm thấy trong mọi phiên bản Excel, từ 2007 cho đến 2016 và cả những bản dưới hơn.

Định nghĩa cú pháp của hàm SUBTOTAL:

SUBTOTAL(function_num, ref1, [ref2],…)

Chi tiết từng phần:

Function_num: số chỉ ra chức năng cần thực hiện

Ref1, Ref2, …: được sử dụng để chỉ định một hoặc nhiều phạm vi ô cần tính tổng phụ. Trong đó, Ref 1 là bắt buộc, còn Ref 2 trở đi tùy chọn và có thể đến 254.

Có hai danh mục số chỉ định chức năng như sau:

  • 1 -11 loại trừ các ô bị ẩn bằng bộ lọc, nhưng vẫn tính các hàng ẩn thủ công.
  • 101 – 111 loại trừ tất cả các ô ẩn, bao gồm cả ẩn thủ công và bị lọc.
Function_num Chức Năng Giải Thích
1 101 AVERAGE Tính giá trị trung bình
2 102 COUNT Đếm số lượng ô chứa số liệu
3 103 COUNTA Đếm số lượng ô không rỗng
4 104 MAX Tìm ra số lớn nhất
5 105 MIN Tìm ra số nhỏ nhất
6 106 PRODUCT Tính tích của các ô
7 107 STDEV Tính độ lệch chuẩn mẫu
8 108 STDEVP Tính độ lệch chuẩn tổng thể
9 109 SUM Tổng cộng số liệu
10 110 VAR Ước lượng biến động mẫu
11 111 VARP Ước lượng biến động tổng thể

Khi nhập hàm SUBTOTAL vào Excel, bạn không cần ghi nhớ các số này vì Excel sẽ hiển thị danh sách chọn lựa cho bạn ngay tại ô nhập hoặc thanh công thức.

Chẳng hạn, dưới đây là cách sử dụng công thức SUBTOTAL 9 để tính tổng giá trị từ ô C2 đến C8:

Để chèn số chức năng vào công thức, chỉ cần nhấp đúp chuột vào ô, gõ dấu phẩy, điền ô cần tính, đóng ngoặc và nhấn Enter. Công thức đầy đủ sẽ như sau:

=SUBTOTAL(9,C2:C8)

Ngoài ra, bạn cũng có thể viết công thức SUBTOTAL 1 cho việc tính giá trị trung bình, SUBTOTAL 2 cho việc đếm ô có số và SUBTOTAL 3 cho việc đếm ô không trống. Dưới đây là hình mô tả 3 chức năng khác nhau được áp dụng:

Chú ý:

Nếu bạn áp dụng công thức SUBTOTAL trong Excel cho các chức năng tính tổng như SUM hay AVERAGE, chỉ những ô có số liệu mới được tính, còn ô trống hoặc chứa dữ liệu không phải số sẽ bị bỏ qua.

Sau khi hiểu rõ cách thiết lập công thức SUBTOTAL trong Excel, điều cần xét đến là tại sao bạn nên sử dụng hàm này thay vì những hàm thông thường như SUM, COUNT hay MAX? Đáp án sẽ được giải thích trong phần tiếp theo.

3 LÝ DO ĐỂ

SỬ DỤNG HÀM SUBTOTAL

Hàm SUBTOTAL trong Excel mang lại những ưu điểm nổi bật so với các hàm thông thường như sau:

  1. Tự động cập nhật giá trị theo hàng đã chọn

Hàm SUBTOTAL của Excel loại bỏ hàng bị lọc khỏi phép tính, cho phép bạn tính tổng các số liệu một cách động linh hoạt khi áp dụng bộ lọc, và kết quả tổng phụ sẽ được cập nhật tự động.

Chẳng hạn, khi bạn lọc ra các dữ liệu chỉ của khu vực Miền Đông trong bảng doanh số, công thức SUBTOTAL sẽ chỉ tính tổng cho khu vực này mà loại trừ các khu vực còn lại một cách tự động.

Lưu ý:

Khi sử dụng hàm Excel SUBTOTAL với bất kỳ số nào trong hai bộ số 1-11 và 101-111, cả hai đều sẽ không tính các hàng đã bị loại bỏ, bạn có thể dùng SUBTOTAL 9 hay 109 đều được.

  1. Chỉ tính trên các ô hiển thị

Khi chọn số chức năng từ 101 đến 111, công thức SUBTOTAL sẽ loại trừ các ô đã bị ẩn bằng tay hoặc do lọc. Nghĩa là, nếu bạn ẩn các hàng không cần thiết bằng chức năng Hide của Excel, những số chức năng này sẽ không tính các giá trị từ những hàng đã bị ẩn.

  1. Không bao gồm giá trị từ các công thức SUBTOTAL lồng nhau

Nếu một phạm vi ô chứa công thức SUBTOTAL khác, công thức nằm trong sẽ không được tính, để đảm bảo giá trị không bị tính hai lần trong bảng.

Dưới đây là ví dụ công thức trung bình SUBTOTAL(1, C2:C10) không tính hai ô C3 và C10 vì chúng đã chứa các công thức SUBTOTAL khác.

VÍ DỤ VỀ VIỆC ÁP DỤNG SUBTOTAL TRONG EXCEL

Khi bắt đầu tìm hiểu hàm SUBTOTAL, bạn có thể cảm thấy nó phức tạp và khó hiểu. Tuy nhiên, khi đã quen sử dụng, bạn sẽ thấy hàm này thực sự hữu ích cho công việc. Dưới đây là một số thủ thuật khi dùng hàm SUBTOTAL.

VÍ DỤ 1. SUBTOTAL 9 SO VỚI SUBTOTAL 109

Excel SUBTOTAL xử lý 2 bộ số chức năng là 1-11 và 101-111, cả hai đều không tính các hàng đã lọc. Tuy nhiên, trong khi bộ 1-11 vẫn bao gồm hàng ẩn thủ công, bộ 101-111 lại không. Xem ví dụ sau để hiểu rõ sự khác biệt:

Dùng SUBTOTAL 9 và SUBTOTAL 109 để cộng tổng các hàng đã chọn như hình dưới đây:

Nhưng khi bạn ẩn các hàng không liên quan bằng chức năng Hide Rows tại Home tab > Cells group > Format > Hide & Unhide, bằng cách nhấn chuột phải và chọn Hide và muốn tổng cộng chỉ các hàng hiển thị, hãy sử dụng SUBTOTAL 109:

Các số chức năng khác cũng hoạt động theo cách tương tự. Ví dụ, sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103 nếu bạn muốn đếm ô không trống đã chọn, nhưng chỉ với SUBTOTAL 103 mới đếm chính xác ô không trống hiển thị nếu dãy đó có hàng ẩn.

Lưu ý: Hàm Excel SUBTOTAL khi dùng với số chức năng từ 101 đến 111 chỉ loại trừ hàng ẩn, không ảnh hưởng đến cột ẩn. Chẳng hạn sử dụng công thức như SUBTOTAL(109, A1:E1) để cộng hàng ngang, các cột ẩn sẽ vẫn được tính.

Khi bạn cần tổng hợp báo cáo và những số liệu cần được tóm gọn lại mà không gian trên bảng tính có hạn, những giải pháp sau đây có thể hữu ích:

  • Tạo một menu xổ xuống trong một ô với các lựa chọn như Total, Max, Min, v.v…
  • Thêm một công thức hàm IF lồng vào ô kế tiếp nơi bạn tạo menu, sử dụng hàm SUBTOTAL để thực hiện tính toán dựa trên sự lựa chọn.

Chẳng hạn, bạn muốn tính tổng phụ cho dữ liệu tại ô C12 đến C16, và ô A17 chứa menu xổ xuống với các lựa chọn như Total, Average, Max, Min, thì công thức sử dụng hàm SUBTOTAL có thể được viết như sau:

=IF(A17=”total”, SUBTOTAL(9,C2:C16), IF(A17=”average”, SUBTOTAL(1,C2:C16), IF(A17=”min”, SUBTOTAL(5,C2:C16), IF(A17=”max”, SUBTOTAL(4,C2:C16),””))))

Công thức SUBTOTAL sẽ tự động tính toán giá trị dựa trên lựa chọn của người sử dụng từ danh sách xổ xuống.

Lưu ý nhanh: Nếu menu xổ xuống và các ô công thức bị mất trên bảng tính, bạn chỉ cần chọn chúng lại từ danh sách lọc.

SỰ CỐ KHI SỬ DỤNG HÀM EXCEL SUBTOTAL

Nếu hàm SUBTOTAL không hoạt động và thông báo lỗi, đây có thể là một trong những lý do gây ra:

#VALUE! Nếu giá trị cho tham số chức năng không nằm trong khoảng từ 1 đến 11 hoặc từ 101 đến 111, hoặc nếu tham chiếu là 3D.

#DIV/0! Điều này xảy ra khi cần thực hiện phép chia cho số 0, ví dụ khi tính trung bình cộng hoặc độ lệch chuẩn của một dải cell không chứa dữ liệu số nào.

#NAME? Lỗi này thường gặp khi viết sai tên hàm SUBTOTAL.

Một mẹo: Nếu bạn mới làm quen với hàm SUBTOTAL, hãy dùng các chức năng có sẵn và để cho công thức tự động hoàn thành hoặc tham khảo video hướng dẫn sau:

[wpcc-iframe src=”https://www.youtube.com/embed/myCn3Pa0W8A” width=”560″ height=”314″ allowfullscreen=”allowfullscreen”]

Để tận dụng Excel một cách hiệu quả trong công việc, bạn cũng cần thành thạo việc sử dụng nhiều hàm và công cụ khác như:

  • Hàm SUMIF, SUMIFS cho việc tính tổng dựa trên một hoặc nhiều điều kiện
  • Hàm COUNTIF, COUNTIFS để thống kê số lượng dựa trên một hoặc nhiều điều kiện
  • Các hàm xử lý chuỗi, ngày tháng, số liệu…
  • Tham chiếu và tìm kiếm với hàm Index+Match, SUMPRODUCT…

Cùng với đó là sự hỗ trợ từ các công cụ như:

  • Conditional formatting để định dạng dựa trên điều kiện
  • Data Validation để thiết lập quy tắc nhập liệu
  • Cách đặt và sử dụng Name trong các công thức
  • Lập báo cáo sử dụng Pivot Table…

Một lượng kiến thức tương đối phải không? Tất cả những kiến thức này bạn đều có thể học được qua khóa học SM90 – Excel từ cơ bản tới chuyên gia do Siêu Marketing phát hành. Khóa học này sẽ giúp bạn có được hệ thống kiến thức một cách toàn diện và chi tiết. Điểm đặc biệt là bạn có thể học mọi lúc, mọi nơi và dễ dàng tra cứu kiến thức mỗi khi cần. Hiện nay, hệ thống đang cung cấp ưu đãi cực kỳ hấp dẫn cho khóa học này. Xem chi tiết tại sieumarketing.com.

Trương Thành Tài

0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop