Cần giúp tự động gõ Subtotal(9,...) tính tổng từng nhóm phân cách bởi dòng màu vàng (1 người xem)

[hung2412]

Chào Quý vị và các bạn GPE!
Nhờ Quý vị và các bạn giúp đỡ cho tôi vấn đề này với, cụ thể như sau:
Tự động đánh công thức Subtotal(9,...) cho các dòng màu vàng từ cột B đến cột E (Tính tổng Subtotal theo từng nhóm phân cách bởi dòng màu vàng) (Dòng Tổng cộng cuối cùng tính tổng Subtotal các dòng màu vàng lại với nhau). Dữ liệu các dòng là không cố định.
Ví dụ:
- Gõ công thức vào ô B3 là: =Subtotal(9,B4:B11)
- Gõ công thức vào ô B12 là: =Subtotal(9,B13:B17)
Mong Quý vị và các bạn GPE chỉ giáo!
Trân trọng cảm ơn!
P/s: Có 01 File đính kèm.

 

[Hạ viên]

không hiểu bạn hỏi gì luôn > nếu muốn copy tự động công thức thì crt+r từ cột B đến E thui .
Còn cột tổng cộng thì tương tự ....

 

[hung2412]

không hiểu bạn hỏi gì luôn > nếu muốn copy tự động công thức thì crt+r từ cột B đến E thui .
Còn cột tổng cộng thì tương tự ....

Lúc đầu các dòng màu vàng thì chưa hề có công thức Subtotal => Tự động điền công thức Subtotal vào ô dòng màu vàng (Cái này mới khó) để tính tổng các dòng bên dưới (Các dòng cần tính tổng Subtotal này không cố định). Chứ Ctr + r để copy công thức thì tôi biết rồi.

 

[phihndhsp]

Lúc đầu các dòng màu vàng thì chưa hề có công thức Subtotal => Tự động điền công thức Subtotal vào ô dòng màu vàng (Cái này mới khó) để tính tổng các dòng bên dưới (Các dòng cần tính tổng Subtotal này không cố định). Chứ Ctr + r để copy công thức thì tôi biết rồi.

tại sao bạn không thêm tí xíu nữa để áp dụng subtotal trên thanh công cụ thì hay hơn rất nhiều, cần chi mà phải bôi vàng rồi điền công thức vô. tại sao mình lại mất công bôi vàng từ dòng đó chi, bạn có chăc là những ô bạn bôi màu vàng đều chính xác không?
nếu bạn nói là chính xác thì tôi sẽ mở cho bạn 1 cách là bạn sử dụng VBA tìm kiếm dòng màu vàng đâu và hiện tại rồi điền công thức vô là ok. tôi thấy bạn cũng biết về VBA nên cái này bạn làm được dùng vòng lặp để duyệt dựa trên nền màu vàng mà bạn chọn

 

[hung2412]

tại sao bạn không thêm tí xíu nữa để áp dụng subtotal trên thanh công cụ thì hay hơn rất nhiều, cần chi mà phải bôi vàng rồi điền công thức vô. tại sao mình lại mất công bôi vàng từ dòng đó chi, bạn có chăc là những ô bạn bôi màu vàng đều chính xác không?
nếu bạn nói là chính xác thì tôi sẽ mở cho bạn 1 cách là bạn sử dụng VBA tìm kiếm dòng màu vàng đâu và hiện tại rồi điền công thức vô là ok. tôi thấy bạn cũng biết về VBA nên cái này bạn làm được dùng vòng lặp để duyệt dựa trên nền màu vàng mà bạn chọn

Tôi xin trả lời bạn là:
- Dùng Subtotal trên thanh công cụ thì phải có 1 tiêu chí giống nhau nhất định => Subtotal theo tiêu chí đó (Nhưng File này lại không có tiêu chí nào giống nhau cả, chỉ có tên Đội xây dựng 1, Đội xây dựng 2,... lại nằm trên dòng đầu màu vàng).
- Tôi có cách bôi màu vàng cho các dòng một cách chính xác.
=> Vấn đề khó ở chỗ là đặt công thức Subtotal cho dòng màu vàng đó (Vùng cần tính tổng Subtotal thì tôi biết điểm đầu (Dùng Offset ngay dòng bên dưới), còn điểm cuối cần tính là tôi chịu vì dữ liệu là không cố định).
Mong Quý vị chỉ giáo.

 

[phihndhsp]

Tôi xin trả lời bạn là:
- Dùng Subtotal trên thanh công cụ thì phải có 1 tiêu chí giống nhau nhất định => Subtotal theo tiêu chí đó (Nhưng File này lại không có tiêu chí nào giống nhau cả, chỉ có tên Đội xây dựng 1, Đội xây dựng 2,... lại nằm trên dòng đầu màu vàng).
- Tôi có cách bôi màu vàng cho các dòng một cách chính xác.
=> Vấn đề khó ở chỗ là đặt công thức Subtotal cho dòng màu vàng đó (Vùng cần tính tổng Subtotal thì tôi biết điểm đầu (Dùng Offset ngay dòng bên dưới), còn điểm cuối cần tính là tôi chịu vì dữ liệu là không cố định).
Mong Quý vị chỉ giáo.

tôi có câu này mà
tại sao bạn không thêm tí xíu nữa để áp dụng subtotal
bài này bạn dùng công thức chắc chắn là không được

 

[hung2412]

tôi có câu này mà
tại sao bạn không thêm tí xíu nữa để áp dụng subtotal
bài này bạn dùng công thức chắc chắn là không được

Tôi đã hiểu ý Quý vị, nhưng để "thêm tí xíu nữa để áp dụng subtotal" thì tôi cũng muốn lắm, nhưng không biết phải làm răng.

 

[ndu96081631]

Chào Quý vị và các bạn GPE!
Nhờ Quý vị và các bạn giúp đỡ cho tôi vấn đề này với, cụ thể như sau:
Tự động đánh công thức Subtotal(9,...) cho các dòng màu vàng từ cột B đến cột E (Tính tổng Subtotal theo từng nhóm phân cách bởi dòng màu vàng) (Dòng Tổng cộng cuối cùng tính tổng Subtotal các dòng màu vàng lại với nhau). Dữ liệu các dòng là không cố định.
Ví dụ:
- Gõ công thức vào ô B3 là: =Subtotal(9,B4:B11)
- Gõ công thức vào ô B12 là: =Subtotal(9,B13:B17)
Mong Quý vị và các bạn GPE chỉ giáo!
Trân trọng cảm ơn!
P/s: Có 01 File đính kèm.

Có phải những dòng màu vàng thì cột A luôn bắt đầu bằng từ "ĐỘI..." không?
Tôi hỏi thế là muốn biết QUY LUẬT nào để phân biệt được dòng tô vàng với dòng không tô vàng
Có quy luật rồi thì mọi chuyện được giải quyết ngay
- Hoặc là bạn gửi lên đây dữ liệu đầy đủ (để mọi người xem và suy luận).
- Hoặc là bạn tự phát biểu quy luật chung nhé
- Ngoài ra có thể tham khảo bài này và tự làm:
http://www.giaiphapexcel.com/forum/...ập-công-thức-tính-tổng-con-cho-từng-phân-đoạn

 

[phihndhsp]

Option Explicit


Sub subtotal01()
Dim LastR As Long
Dim i As Long
Dim chantren As Long
Dim chanduoi As Long
Dim Hailan As Byte
 
Sheet1.Select
LastR = Range("A65000").End(xlUp).Row
chantren = 0
chanduoi = 0
For i = 3 To LastR
  If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 0) Then
     chantren = i
     Hailan = Hailan + 1
     i = i + 1
  End If
  If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 1) Then
     chanduoi = i
     Hailan = Hailan + 1
      i = i - 1
  End If
  
  If (Hailan = 2) Then
    Cells(chantren, 2).FormulaR1C1 = "=SUBTOTAL(9,R" & chantren + 1 & "C2:R" & chanduoi - 1 & "C2)"
    Hailan = 0
   End If


Next


End Sub

copy cái đó vào module rồi chạy

 

[Quang_Hải]

Chào Quý vị và các bạn GPE!
Nhờ Quý vị và các bạn giúp đỡ cho tôi vấn đề này với, cụ thể như sau:
Tự động đánh công thức Subtotal(9,...) cho các dòng màu vàng từ cột B đến cột E (Tính tổng Subtotal theo từng nhóm phân cách bởi dòng màu vàng) (Dòng Tổng cộng cuối cùng tính tổng Subtotal các dòng màu vàng lại với nhau). Dữ liệu các dòng là không cố định.
Ví dụ:
- Gõ công thức vào ô B3 là: =Subtotal(9,B4:B11)
- Gõ công thức vào ô B12 là: =Subtotal(9,B13:B17)
Mong Quý vị và các bạn GPE chỉ giáo!
Trân trọng cảm ơn!
P/s: Có 01 File đính kèm.

Mình thì cứ theo kiểu ngắn gọn. Viết code cho cột E. Nếu thấy xài được thì chế biến thêm.

Sub QH()
Dim r&
For r = 3 To [E65536].End(3).Row
   If Cells(r, 5) = "" Then Cells(r, 5) = "=Subtotal(9,R[1]C:R" & Cells(r + 1, 5).End(4).Row & "C)"
Next
End Sub

 

[hung2412]

Option Explicit


Sub subtotal01()
Dim LastR As Long
Dim i As Long
Dim chantren As Long
Dim chanduoi As Long
Dim Hailan As Byte
 
Sheet1.Select
LastR = Range("A65000").End(xlUp).Row
chantren = 0
chanduoi = 0
For i = 3 To LastR
  If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 0) Then
     chantren = i
     Hailan = Hailan + 1
     i = i + 1
  End If
  If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 1) Then
     chanduoi = i
     Hailan = Hailan + 1
      i = i - 1
  End If
  
  If (Hailan = 2) Then
    Cells(chantren, 2).FormulaR1C1 = "=SUBTOTAL(9,R" & chantren + 1 & "C2:R" & chanduoi - 1 & "C2)"
    Hailan = 0
   End If


Next


End Sub

copy cái đó vào module rồi chạy

Vâng, tôi thử chạy code thấy chạy khá ổn, trước mắt chưa thấy vấn đề gì, xin cảm ơn Quý vị ạ.

 

[hung2412]

Mình thì cứ theo kiểu ngắn gọn. Viết code cho cột E. Nếu thấy xài được thì chế biến thêm.

Sub QH()
Dim r&
For r = 3 To [E65536].End(3).Row
   If Cells(r, 5) = "" Then Cells(r, 5) = "=Subtotal(9,R[1]C:R" & Cells(r + 1, 5).End(4).Row & "C)"
Next
End Sub

Vâng, code này chạy khá ổn nếu như các dòng con (Không phải dòng màu vàng) có số liên tục không gián đoạn (Nghĩa là không có dòng trống).

 

[hung2412]

Option Explicit
Sub subtotal01()
Dim LastR As Long
Dim i As Long
Dim chantren As Long
Dim chanduoi As Long
Dim Hailan As Byte 
Sheet1.Select
LastR = Range("A65000").End(xlUp).Row
chantren = 0
chanduoi = 0
For i = 3 To LastR  
If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 0) Then     
chantren = i     
Hailan = Hailan + 1     
i = i + 1  
End If  
If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 1) Then     
chanduoi = i     
Hailan = Hailan + 1      
i = i - 1  
End If    
If (Hailan = 2) Then    
Cells(chantren, 2).FormulaR1C1 = "=SUBTOTAL(9,R" & chantren + 1 & "C2:R" & chanduoi - 1 & "C2)"    
Hailan = 0   
End If
Next
End Sub

copy cái đó vào module rồi chạy

Cho tôi hỏi là cái này liệu có chuyển thành dạng Function (Hàm VBA) có được không ạ?

 

[Quang_Hải]

Vâng, code này chạy khá ổn nếu như các dòng con (Không phải dòng màu vàng) có số liên tục không gián đoạn (Nghĩa là không có dòng trống).

Cột B đến cột D có thể có dòng trống, lẽ nào cột E lại trống công thức sao? Không có lý nhỉ! Cứ thử được thì xài, không được thì cũng không hại gì ai.

Dạng bài này không thể dùng UDF được đâu.

Sub QH()
Dim r&
For r = 3 To [E65536].End(3).Row
   If Cells(r, 5) = "" Then
      Range(Cells(r, 2), Cells(r, 5)) = _
      "=Subtotal(9,R[1]C:R" & Cells(r + 1, 5).End(4).Row & "C)"
   End If
Next
End Sub

 

[phihndhsp]

Vâng, code này chạy khá ổn nếu như các dòng con (Không phải dòng màu vàng) có số liên tục không gián đoạn (Nghĩa là không có dòng trống).

cái này tôi đã hỏi bạn chắc chắn là có màu hay không rồi mà và có phải bạn muốn bố trí như vậy? và bạn đã nói như đinh đóng cột là chắc rồi mà

 

[hung2412]

cái này tôi đã hỏi bạn chắc chắn là có màu hay không rồi mà và có phải bạn muốn bố trí như vậy? và bạn đã nói như đinh đóng cột là chắc rồi mà

Vâng, tôi có để ý là If (Range("B" & i).Interior.ColorIndex = 6 And Hailan = 0) Then phải có màu vàng (màu 6), nhưng mà câu nói này có liên quan gì đến số liên tục không gián đoạn (Nghĩa là không có dòng trống) đâu nhỉ? Tôi thử cột B có vài dòng con không có số mà Code vẫn chạy đúng.

 

[hung2412]

Cột B đến cột D có thể có dòng trống, lẽ nào cột E lại trống công thức sao? Không có lý nhỉ! Cứ thử được thì xài, không được thì cũng không hại gì ai.

Dạng bài này không thể dùng UDF được đâu.

Sub QH()
Dim r&
For r = 3 To [E65536].End(3).Row
   If Cells(r, 5) = "" Then
      Range(Cells(r, 2), Cells(r, 5)) = _
      "=Subtotal(9,R[1]C:R" & Cells(r + 1, 5).End(4).Row & "C)"
   End If
Next
End Sub

"Dạng bài này không thể dùng UDF được đâu" là sao ạ? Em đầu ngắn không hiểu, mong Quý vị nói rõ được không ạ.

 

[TrungChinhs]

"Dạng bài này không thể dùng UDF được đâu" là sao ạ? Em đầu ngắn không hiểu, mong Quý vị nói rõ được không ạ.

UDF thì cũng chỉ gán công thức cho từng ô và thao tác giống như dùng hàm Sum vậy. Chẳng lẽ lại viết UDF thay thế cho hàm Subtotal ?