Các câu hỏi về hàm SUBTOTAL (3 người xem)

[BNTT]

Bạn tự làm một ví dụ đi, sẽ thấy nó khác nhau thôi:

- Mở một bảng tính mới, chừa một hàng trống để dễ thấy.

- Chọn dãy A2:B100, nhập công thức: = RANDBETWEEN(-100, 100) rồi nhấn Ctrl-Enter (Điền ngẫu nhiên vào dãy đó các con số trong khoảng -100 đến 100)

- Vẫn chọn A2:B100, Copy, Paste Special với tham số Value (tức là hủy bỏ cái hàm RANDBETWEEN đi, chỉ lấy mấy con số thôi)

- Chọn dãy C2:C100, nhập công thức: = B2 (sau khi nhập công thức này cũng nhấn Ctrl-Enter nha bạn)

- Ở B101, bạn dùng công thức: = SUM(B2:B100)

- Ở C101, bạn dùng công thức: = SUBTOTAL(9, C2:C100)

- Bạn sẽ thấy con số tổng ở hai ô B101 và C101 này giống nhau, phải không.

- Chọn A1:C100, chọn Data/Autofilter

- Bi giờ bạn đứng ở cột A, áp dụng Autofiter cho cột A với một giá trị gì đó (ví dụ chỉ lọc ra những con số lớn hơn bao nhiêu đó chẳng hạn, hoặc là lọc bỏ mấy con số > 0, v.v...)

- Xem lại B101 và C101. Kết quả khác nhau rồi.​

Bạn tự suy nghĩ xem nó khác nhau là do đâu ?

Hàm Sum : Sẽ cộng tất cả các giá trị trong vùng
Subtotal(đối số 9) : Sẽ cộng tất cả các giá trị trong vùng trừ các ô có chứa công thức Subtotal


Thân!

Hic, OkBap ơi, xem lại dùm cái định nghĩa SUBTOTAL !

 

[nhatthai]

Cólẽ Là Thế Này, Bạn Xem File

 

[Trần Thị Ánh Tuyết]

Chào bạn, khi cài công thức Sum thì dùng auto filter để lọc giá trị nào đó trong bảng tính phải tính lại công thức Sum, với Subtotal thi không cần
"Cái gì không thể mua được bằng tiền thì có thể mua được bằng nhiều tiền hơn" là sao nhỉ ? nếu vậy thì cái gì cũng mua được bằng tiền hết, phải không ?

 

[Mr Okebab]

Hic, OkBap ơi, xem lại dùm cái định nghĩa SUBTOTAL !

Không hẳn đâu, chỉ thiếu trường hợp Filter thôi.
Subtotal làm ra chính yếu là để nhằm không cộng các tổng con vào (Cho khác SUM),

Bác làm luôn cái sự phân biệt giữa các hàng bị ẩn do người dùng và do Auto Filter nhé. Vì hai cái này sẽ khác nhau đấy (Với các đối số >100)

Và thêm cái là hàm Sum thì cộng tất cả, còn hàm Subtotal thì sẽ loại trừ các giá trị của các ô có công thức Subtotal, đây mới chính là cái chính yếu của Subtotal (Như công cụ Subtotal cũng lợi dụng điểm này)

Cảm ơn bác nhiều!

"Cái gì không thể mua được bằng tiền thì có thể mua được bằng nhiều tiền hơn" là sao nhỉ ? nếu vậy thì cái gì cũng mua được bằng tiền hết, phải không ?

Phủ định để khẳng định nhằm nhấn mạnh ý nghĩa khẳng định : Không gì không thể mua được bằng tiền!! (Đây là dịch nghĩa chứ không liên quan đến quan điểm)



Thân!

 

[huycuongxd]

Cách dùng hàm Subtotal

E vừa mới gia nhập diễn đàn, kiến thức về Excel còn ít ỏi quá! Mong các tiền bối chỉ dạy cho e cách sử dụng Hàm Subtotal với nhé, e đọc sách nhưng khó hiểu và chưa làm được. Cảm ơn các bác nhiều lắm!

 

[luonchinguoc]

Hướng dẫn sử dụng Subtotal

E vừa mới gia nhập diễn đàn, kiến thức về Excel còn ít ỏi quá! Mong các tiền bối chỉ dạy cho e cách sử dụng Hàm Subtotal với nhé, e đọc sách nhưng khó hiểu và chưa làm được. Cảm ơn các bác nhiều lắm!

SUBTOTAL (function_num, ref1, ref1,...)

Trong đó, function_num là những số từ 1 đến 11 (includes hidden value - bao gồm cả những giá trị ẩn), và từ 101 đến 111 (ignores hidden value - bỏ qua những giá trị ẩn)

Giá trị ẩn ở đây thường là số liệu trong những hàng (row) đã được dấu đi, ví dụ như lọc bằng Autofilter.

Cách dùng hàm bằng các số (1 tương đương với 101, 2 tương đương 102, v.v...)
1 : AVERAGE
2 : COUNT
3 : COUNTA
4 : MAX
5 : MIN
6 : PRODUCT
7 : STDEV
8 : STDEVP
9 : SUM
10 : VAR
11 : VARP

=SUBTOTAL(102,$D$49) nghĩa là đếm trong dãy từ $D$4 đến D9 có bao nhiêu cell, bỏ qua (không đếm) nhừng cell đã bị Filter dấu đi.
__________________

Link gốc:
http://www.giaiphapexcel.com/forum/showpost.php?p=27149&postcount=31
http://www.giaiphapexcel.com/forum/attachment.php?attachmentid=3770&d=1189580574

 

[ndu96081631]

SUBTOTAL (function_num, ref1, ref1,...)

Trong đó, function_num là những số từ 1 đến 11 (includes hidden value - bao gồm cả những giá trị ẩn), và từ 101 đến 111 (ignores hidden value - bỏ qua những giá trị ẩn)

Cái này chỉ đúng với Office từ 2003 trở lên thôi nhé! Với Office2002 về trước thì chỉ có tham số từ 1 đến 11 (không có 101 đến 111)

 

[minhduc13]

Cách này tui cũng từng xem qua và từng xài, nhưng vấn đề là không hiểu cách làm việc của nó như thế nào, chỉ biết bị động thay những chỗ cần thay mà chả hiểu sao nó lại như thế cả --> vì không hiểu nên không sáng tạo được. Có ai giải thích xem nó làm việc như thế nào không?

 

[tedaynui]

Hàm SUBTOTAL với cột

Mình dùng hàm SUBTOTAL để tính tổng các ô không ẩn. Ví dụ
SUBTOTAL(109,A1:A100) --> khi ẩn dòng thì OK

Nhưng mình cũng muốn với cách làm như vậy đối với cột thì không được. Ví dụ
SUBTOTAL(109,A1:H1) --> khi ẩn cột thì nó vẫn tính tất cả

Vậy có công thức nào áp dụng được với cột mong được các bạn giúp đỡ. Xin cám ơn !
(Không dùng VBA)

TDN

 

[ndu96081631]

Theo tôi thì SUBTOTAL có liên quan mật thiết với Filter... Và ta cũng chỉ thấy Filter theo chiều dọc chứ có cái Filter nào theo chiều ngang đâu
E rằng nếu muốn thế phải UDF

 

[TKT]

Mình dùng hàm SUBTOTAL để tính tổng các ô không ẩn. Ví dụ
SUBTOTAL(109,A1:A100) --> khi ẩn dòng thì OK

Nhưng mình cũng muốn với cách làm như vậy đối với cột thì không được. Ví dụ
SUBTOTAL(109,A1:H1) --> khi ẩn cột thì nó vẫn tính tất cả

Vậy có công thức nào áp dụng được với cột mong được các bạn giúp đỡ. Xin cám ơn !
(Không dùng VBA)

TDN

Trong phần trợ giúp của excel nói về hàm Subtotal như sau:
Trích:

For the function_num constants from 1 to 11, the SUBTOTAL function includes  the values of [COLOR=Red][B]rows[/B][/COLOR] hidden by the Hide command under the Row submenu of the Format menu). Use these  constants when you want to subtotal hidden and nonhidden numbers in a list. For  the function_Num constants from 101 to 111, the SUBTOTAL function ignores values  of [B][COLOR=Red]rows hidden[/COLOR][/B] by the Hide command under the Row submenu of the Format menu). Use these  constants when you want to subtotal only nonhidden numbers in a list.

Không nói gì về column cả.
Cũng giống như chức năng Pivot Table và Auto Filter.
Em không bao giờ nhập liệu theo hàng vì bảng thân người lập trình Excel đã thiết kế số dòng luôn luôn nhiều hơn số cột vả lại còn liên quan đến các trường (Field) bên Access nữa , cũng như một số phần mềm khác.

 

[Po_Pikachu]

Tuy là không bằng hàm SubTotal hoặc Sum về việc đa năng và quét mảng nhưng xài cho cột và dòng ẩn thì được.

Function Tong(cel As Range)
For j = 1 To InStr(1, cel.Address(0, 0), ":")
    k = Mid(cel.Address(0, 0), j, 1)
    If IsNumeric(k) = True Then
    rn1 = rn1 & k
    End If
Next
For m = InStr(1, cel.Address(0, 0), ":") To Len(cel.Address(0, 0))
    k = Mid(cel.Address(0, 0), m, 1)
    If IsNumeric(k) = True Then
    rn2 = rn2 & k
    End If
Next
If rn1 = rn2 Then
For i = 1 To cel.Columns.Count
    If ActiveSheet.Columns(i).Hidden <> True Then
    Tong = Tong + cel.Cells(i)
    End If
Next
Else
For i = 1 To cel.Rows.Count
    If ActiveSheet.Rows(i).Hidden <> True Then
    Tong = Tong + cel.Cells(i)
    End If
Next
End If
End Function

 

[ndu96081631]

Code dài quá đồng chí ơi...
Tôi thì làm vầy:

Function SumVisible(Rng As Range)
   Dim VCel As Range
   Dim Temp As Double
   For Each VCel In Rng
       If Not VCel.Rows.Hidden And Not VCel.Columns.Hidden Then Temp = Temp + VCel
   Next
   SumVisible = Temp
End Function

Tôi còn nghĩ sẽ dùng SpecialCells(xlCellTypeVisible) nhưng đáng tiếc không thực hiện được (Khi dùng Sub thì cho kết quả chính xác, nhưng gán vào Function thì trật lất---> Không hiểu tại sao)

Ah quên... phải thêm Application.Volatile vào đầu code nữa (nếu không nó sẽ không cập nhật được giá trị mới khi ta ẩn dòng hoặc cột

 

[Po_Pikachu]

Tuy hơi yếu, nhưng cũng cố gắng "lết" đến đây!

Function Tong(cel As Range, Optional loai As String = "") Application.Volatile  If UCase$(loai)  "ROW" Then For i = 1 To cel.Count     If ActiveSheet.Columns(cel.Cells(i).Column).Hidden  True Then     Tong = Tong + cel.Cells(i)     End If Next Else For i = 1 To cel.Count     If ActiveSheet.Rows(cel.Cells(i).Row).Hidden  True Then     Tong = Tong + cel.Cells(i)     End If Next End If End Function

Mong các bác đừng chê cười! Thân.

 

[ndu96081631]

Tôi làm thí nghiệm với 2 đoạn code sau:

Function SumVisible(Rng As Range)
  Dim VRng As Range
  Application.Volatile
  Set VRng = Rng.SpecialCells(xlCellTypeVisible)
  SumVisible = Application.WorksheetFunction.Sum(VRng)
End Function

Sub Test()
  Dim VRng As Range
  Set VRng = Selection.SpecialCells(xlCellTypeVisible)
  MsgBox Application.WorksheetFunction.Sum(VRng)
End Sub

2 code này giống nhau hoàn toàn, ấy thế mà Sub thì cho kết quả chính xác còn Function thì sai
Nhờ các cao thủ giãi thích giúp
File đính kèm đây

 

[tedaynui]

Cám ơn các bác đã quan tâm ! Như mình đã nêu : Nếu dùng công thức thì thế nào ?

@ ndu :
Em nghĩ Function nó không chịu .SpecialCells(xlCellTypeVisible). Vì vậy, nếu dùng VBA thì chắc phải đưa vào vòng lặp duyệt từng Cell nhưng như vậy thì sẽ rất chậm. Có lẽ, dùng vòng lặp duyệt từng cột có ẩn hay không, nếu không ẩn thì Subtotal và đưa kết quả vào mảng ; Sau đó cộng chúng lại.

@ Po_Pikachu :
Hàm của bạn cho kết quả như ý (#4). Tuy nhiên, ẩn dòng thì OK nhưng khi ẩn cột thì nó không tự động cập nhật kết quả mới kể cả nhấn F9. Ngoại trừ F2 và Enter.

TDN

 

[ndu96081631]

Có lẽ, dùng vòng lặp duyệt từng cột có ẩn hay không, nếu không ẩn thì Subtotal và đưa kết quả vào mảng ; Sau đó cộng các mảng đó lại.

Tôi cũng nghĩ đây là cách nhanh nhất!
Vậy tạm thời tôi dùng:

Function SumVisible(Rng As Range) As Double
  Dim i As Integer
  Dim iRng As Range
  Dim Temp As Double
  Application.Volatile
  For i = 1 To Rng.Columns.Count
    If Not Rng(, i).Columns.Hidden Then
      Set iRng = Rng(, i).Resize(Rng.Rows.Count, 1)
      Temp = Temp + Application.WorksheetFunction.Subtotal(109, iRng)
    End If
  Next
  SumVisible = Temp
End Function

Nhờ thầy Phước xem lại giúp còn chổ nào không ổn không?

 

[tedaynui]

Tôi cũng nghĩ đây là cách nhanh nhất!

Cám ơn anh, em thấy kết quả như ý rồi nhưng kẹt chỗ : khi ẩn cột thì F9 nó mới chịu Calculate, hic hic

Thân
TDN

 

[ndu96081631]

Cám ơn anh, em thấy kết quả như ý rồi nhưng kẹt chỗ : khi ẩn cột thì F9 nó mới chịu Calculate, hic hic

Thân
TDN

Điều này đương nhiên rồi, vì ẩn hiện cột đâu phải là sự kiện change... Chính vì lẽ đó nên tôi phải thêm Application.Volatile vào
Còn nữa: Tôi nghĩ code trên cần phải cải tiến thêm trong 1 vài trường hợp đặc biệt, chẳng hạn như range là 2 vùng không liên tục ---> Vậy phải viết code thế nào đây? Mong thầy gợi ý thêm (khó quá, chưa nghĩ ra được)

 

[kinghoang]

To ndu: như vậy không có cách nào cho nó tự động calculate lại khi mình ẩn cột ah? Phải tự bấm F9 sao? Mình cũng đang mắc ở chỗ này mong bạn chỉ giáo.