Khi làm việc trong Google Trang tính, bạn có thể kiểm tra định kỳ tổng phụ của tập dữ liệu của mình hoặc đưa kết quả vào tài liệu cuối cùng.
Hàm SUBTOTAL là cách dễ nhất để đạt được điều này. Ngoài việc tính tổng phụ cho danh sách dữ liệu, hàm tổng phụ còn:
- Tính toán các chỉ số với các hàng dữ liệu bị ẩn hoặc đã lọc
- Chọn động các tính năng
Đây là một tính năng ít được sử dụng do sự phức tạp của nó. Điều này không hoàn toàn thân thiện với người dùng mới bắt đầu do nhu cầu sử dụng các điều khiển để làm việc. Tuy nhiên, nếu bạn làm theo hướng dẫn này, bạn sẽ nhanh chóng trở nên thông thạo chức năng SUBTOTAL.
Kiểm soát chức năng là gì?
Các điều khiển tính năng cho một tính năng cụ thể trong Google Trang tính biết cách hoạt động. Trong trường hợp của hàm SUBTOTAL, bạn có thể tìm thấy SUM, AVERAGE, MAX, v.v.
Việc sử dụng mã điều khiển hàm trong cú pháp cho hàm SUBTOTAL biết nó nên sử dụng phép tính nào.
Nghe có vẻ hơi khó hiểu nhưng cheat sheet dưới đây sẽ giúp bạn. Bạn có thể quay lại nó trong khi bạn làm việc trên bàn. Không cần phải nhớ ngay các điều khiển chức năng.
Bạn thậm chí có thể tạo một bộ chọn chức năng động để không phải nhớ mọi mã.
SUBTOTAL- Điều khiển chức năng
Công thức được sử dụng Mã (bao gồm các giá trị ẩn) Mã (không có giá trị ẩn) Trung bình 1 101 Đếm 2 102 Đếm 3 103 Tối đa 4 104 Tối thiểu 5 105 Sản phẩm 6 106 Độ lệch chuẩn 7 107 Độ lệch chuẩn Tổng thể 8 108 Tổng 9 109 Phương sai 10 110 Phương sai Tổng số 11 111
Như bạn có thể thấy trong bảng, có 11 điều khiển cho chức năng SUBTOTAL trong Google Trang tính. Mỗi điều khiển chức năng này có thể bao gồm hoặc loại trừ các giá trị ẩn.
Các điều khiển chức năng giá trị đơn hoặc kép bao gồm các giá trị ẩn. Ba chữ số trong đối số điều khiển hàm có nghĩa là các giá trị ẩn bị loại trừ.
Hãy xem các điều khiển hàm phù hợp với cú pháp như thế nào.
Cú pháp SUBTOTAL
= SUBTOTAL (function_code, range1, [range2, …])
Bạn cần ít nhất hai đối số để hàm SUBTOTAL hoạt động. Đầu tiên là một trong các điều khiển chức năng được liệt kê ở trên và thứ hai là một phạm vi dữ liệu.
Tuy nhiên, bạn có thể thêm bao nhiêu phạm vi dữ liệu vào chức năng này tùy thích.
Hãy sử dụng các đối số cú pháp sau đây làm ví dụ.
= SUBTOTAL (9, B2: B7)
Bởi vì đối số đầu tiên của hàm SUBTOTAL là một điều khiển, nó yêu cầu hàm áp dụng tập hợp mong muốn. Trong ví dụ này, 9 là điều khiển chức năng đã chọn.
Nếu bạn nhìn vào bảng trên, bạn có thể thấy rằng việc sử dụng 9 trong cú pháp sẽ áp dụng hàm SUM cho phạm vi đã cho B2: B7, bao gồm bất kỳ giá trị ẩn nào.
Để loại trừ bất kỳ giá trị ẩn nào, bạn sẽ phải sử dụng điều khiển hàm ba chữ số tương đương 109 để thay thế.
Trong ảnh chụp màn hình sau, bạn có thể thấy rằng chúng tôi đã thêm tập dữ liệu thứ hai vào tổng phụ.
Điều này cũng sẽ bao gồm tập dữ liệu từ A2: A7 trong tính toán.
Lưu ý rằng dấu ngoặc vuông “[“đượcchỉđịnhtrongvídụcúpháplàtùychọnvàsẽdẫnđếnlỗikhinhậpvàocôngthứcChúngchỉđánhdấuđiểmbắtđầuvàkếtthúccủaphạmvimàbạncóthểnhậpvàocúphápvàkhôngthựcsựlàmộtphầncủaphéptính[»указанныевпримересинтаксисанеобязательныиприведуткошибкепривводевформулуОнипростообозначаютначалоиконецдиапазоновкоторыевыможетеввестивсинтаксисинасамомделенеявляютсячастьювычислений
Tại sao bạn nên sử dụng hàm SUBTOTAL
Thay vào đó, có vẻ dễ dàng hơn khi sử dụng một hàm tích hợp tiêu chuẩn để tính toán từng tổng phụ. Rốt cuộc, trong ví dụ đầu tiên của chúng tôi, bạn có thể dễ dàng sử dụng cú pháp = SUM, (B2: B7) mà vẫn nhận được câu trả lời chính xác.
Mặc dù điều này đúng, nhưng đối với các bảng tính phức tạp hơn, tốt hơn nên sử dụng hàm SUBTOTAL.
Trong ví dụ sau, hàm SUM được sử dụng để tính tổng phụ và tổng lớn trong bảng tính.
Bạn có thể thấy rằng tổng phụ là chính xác bằng cách sử dụng hàm SUM, nhưng tổng tổng không chính xác. Điều này là do nó đã tính tổng phụ cũng như các số khác trong cột nơi tổng phụ được tạo.
Bạn có thể tránh điều này bằng cách sử dụng cú pháp SUM của hàm = SUM (C9 + C18 + C27) để có câu trả lời chính xác. Nhưng điều này chậm hơn và tẻ nhạt hơn nhiều so với việc sử dụng hàm SUBTOTAL. Cũng dễ dàng mắc lỗi hơn nhiều bằng cách nhấp vào hoặc nhập thủ công từng trường.
Đây là giao diện khi sử dụng hàm SUBTOTAL trên cùng một tập dữ liệu.
Bạn có thể thấy rằng tổng bây giờ có câu trả lời đúng là 70. Điều này là do hàm SUBTOTAL bỏ qua dữ liệu trùng lặp, trong khi hàm SUM thì không. Điều này đạt được bằng cách bỏ qua tất cả các hàm SUBTOTAL khác trong phạm vi đã cho.
Khái niệm này áp dụng cho tất cả các chức năng điều khiển có thể so sánh được sử dụng bởi hàm SUBTOTAL, không chỉ hàm SUM.
Tương tác của hàm SUBTOTAL với dữ liệu được lọc và ẩn
Hãy xem tập dữ liệu sau.
Trong đó, chúng tôi đã sử dụng các công thức sau trong mỗi ô này.
- C15 = SUM (C2: C13)
- C16 = SUBTOTAL (9; C2: C13)
- C17 = SUBTOTAL (109; C2: C13)
Như bạn có thể thấy, mỗi công thức này cho cùng một câu trả lời đúng cho tổng số lần bán hàng. Nhưng khi bạn bắt đầu lọc hoặc ẩn dữ liệu, mỗi công thức sẽ tương tác khác nhau với bảng tính.
Dữ liệu đã lọc
Trong ví dụ sau, chúng tôi đã lọc theo thương hiệu để hiển thị doanh số của một thương hiệu.
Bạn có thể thấy rằng cả hai dòng SUBTOTAL đã loại bỏ tất cả dữ liệu đã lọc khỏi các tính toán của chúng, nhưng hàm SUM thì không. Khái niệm tương tự cũng áp dụng nếu chúng ta lọc ra cột năm. Thay vào đó, nó sẽ cung cấp tổng phụ theo ngày.
Vì vậy, hãy đảm bảo rằng bạn chọn đúng chức năng về việc bạn có muốn đưa dữ liệu đã lọc vào tổng bảng tính của mình hay không.
Dữ liệu ẩn
Trong ví dụ cuối cùng, chúng tôi cũng đã thêm một hàng ẩn vào tập dữ liệu. Lưu ý rằng số trong ô C17 đã thay đổi. Điều này là do chúng tôi đã sử dụng điều khiển 109 bỏ qua các dòng ẩn. Điều tương tự cũng áp dụng cho các cột ẩn.
Đảm bảo rằng bạn chọn điều khiển chức năng chính xác và tổng phụ với các tùy chọn điều kiện để áp dụng cho mục tiêu cuối cùng của bảng tính của bạn.
Phần Tổng kết
Thoạt nhìn, hàm SUBTOTAL có vẻ hơi đáng sợ. Tuy nhiên, nếu bạn tập trung vào các điều khiển chức năng, bạn sẽ tiết kiệm được rất nhiều thời gian khi làm việc với bảng tính.
Nếu bạn thấy hướng dẫn này về cách tính tổng phụ duy nhất trong Google Trang tính hữu ích, bạn có thể quan tâm đến việc tìm hiểu về tổng phụ.
