Nhờ tính tổng theo các thành phần bằng VBA

Liên hệ QC
N

nguyenmanhnam

Thành viên tiêu biểu
Tham gia
24/7/10
Bài viết
434
Được thích
266
Xin hộ em về tính tổng theo các thành phần (tính tổng cột C theo số thứ tự công việc ở cột A).
TongTPbangVBA.jpg


Do em chưa biết nhiều, ví dụ có vẻ hơi "Ngố", mong các thày giúp.
 

File đính kèm

  • Tong cac thanh phan bang VBA.xlsx
    9.7 KB · Đọc: 80
Lần chỉnh sửa cuối:
Sắp xếp theo thời gian sắp xếp theo bầu chọn
nguyenmanhnam đã viết:
Xin mọi người giúp em hiểu là tại sao khi ấn câu này ở đầu thì nó lại tính ah?
Nhấp chuột vào đây để mở rộng...
Ạh... nó chẳng tính gì đâu, chẳng qua để câu đó, nếu gặp lỗi nó đi luôn thôi
Tức khi gặp cell rổng, giá trị Tmp + "" sẽ lỗi và nó đi luôn, Tmp vẫn giữ nguyên giá trị không tăng thêm tí nào
 
Upvote 0
ThuNghi đã viết:
Sao kg dùng ct subtotal mà phải viết code.
Bạn xem coe nhé, xét theo cột A và tính tổng cột C.
Sub TinhTong()
Dim endR&, i&, sotien As Double
Dim Arr()
With Sheet1
'Xac dong cell cuoi co du lieu o cot C tu dong 65000 - dau'
endR = .Cells(65000, 3).End(xlUp).Row
'Gan vao array cot can tinh'
Arr = .Range("A2:C" & endR).Value
End With
'Dung vong lap' UBound(Arr)= so dong cua arr
For i = UBound(Arr) To 1 Step -1 'duyt tu duoi len
If Len(Arr(i, 1)) > 0 Then 'ie o nay chua co gi
Arr(i, 3) = sotien 'gan vao C
sotien = 0
Else
sotien = sotien + Arr(i, 3)
End If
Next i
'gán vào
With Sheet1
.[A2].Resize(UBound(Arr), 3) = Arr
End With
Erase Arr
End Sub
Nếu file ex 2007 thì save thành xlsm
Nhấp chuột vào đây để mở rộng...
Em chưa hiểu lắm về tại sao lại phải thực hiện gán (các dòng bôi xanh), em cứ tưởng công thức tại dòng (bôi đỏ) chính là gán rồi. Phải chăng nó chỉ có tác dụng nhằm loại bỏ dòng 1 (là dòng tiêu đề chữ sẽ không tính toán được)?. Nhưng hình như cũng không phải vì em xóa đoạn bôi xanh đi, xóa nốt tiêu đề dòng em mà Code vẫn không chạy. Đặc biệt là dòng Erase Arr không biết có tác dụng gì ah?. Xin được kính nhờ các thày giải thích hộ em.
 
Lần chỉnh sửa cuối:
Upvote 0
.[A2].Resize(UBound(Arr), 3) = Arr không hiểu có tác dụng gì?

With Sheet1
.[A2].Resize(UBound(Arr), 3) = Arr
End With
Erase Arr
Xin mọi người giải thích hộ em về sự cần thiết của 3 dòng này, em nghiên cứu mãi mà chưa được.
 
Upvote 0
nguyenmanhnam đã viết:
Em chưa hiểu lắm về tại sao lại phải thực hiện gán (các dòng bôi xanh), em cứ tưởng công thức tại dòng (bôi đỏ) chính là gán rồi. Phải chăng nó chỉ có tác dụng nhằm loại bỏ dòng 1 (là dòng tiêu đề chữ sẽ không tính toán được)?. Nhưng hình như cũng không phải vì em xóa đoạn bôi xanh đi, xóa nốt tiêu đề dòng em mà Code vẫn không chạy. Đặc biệt là dòng Erase Arr không biết có tác dụng gì ah?. Xin được kính nhờ các thày giải thích hộ em.
Nhấp chuột vào đây để mở rộng...
Thôi, bạn đừng tìm hiểu chi cho mất công! Cái đó người ta dùng phương pháp xử lý mảng. Tuy rằng tốc độ làm việc của code là vô địch nhưng rất khó hiểu
Bạn cứ thông thả nghiên cứu, khi nào rành hết về vòng lập rồi hẳn nghiên cứu tiếp về mảng cũng chưa muộn
----------
Tóm lại người ta đưa toàn bộ dữ liệu (từ cột A đến cột C) vào 1 mảng và tính toán trên đó. Sau khi tính xong (cũng gần giống với cách tính của chúng ta) người ta lại gán mảng đã tính vào bảng tính bằng câu lệnh:
PHP: 
With Sheet1
.[A2].Resize(UBound(Arr), 3) = Arr
End With
Vậy thôi
 
Lần chỉnh sửa cuối:
Upvote 0
ndu96081631 đã viết:
Thôi, bạn đừng tìm hiểu chi cho mất công! Cái đó người ta dùng phương pháp xử lý mảng. Tuy rằng tốc độ làm việc của code là vô địch nhưng rất khó hiểu
Bạn cứ thông thả nghiên cứu, khi nào rành hết về vòng lập rồi hẳn nghiên cứu tiếp về mảng cũng chưa muộn
Nhấp chuột vào đây để mở rộng...
Nếu là công thức mảng bình thường trong Excel thì em cũng có thể hiểu được.
Em đọc ở phần trên em cũng biết được phần nào bản chất xử lý của nó, tiện đây xin thày chỉ giúp hộ em với ah.
 
Upvote 0
nguyenmanhnam đã viết:
Nếu là công thức mảng bình thường trong Excel thì em cũng có thể hiểu được.
Em đọc ở phần trên em cũng biết được phần nào bản chất xử lý của nó, tiện đây xin thày chỉ giúp hộ em với ah.
Nhấp chuột vào đây để mở rộng...
Lấy bài toán tạo STT cho bạn hình dung.
1> Theo cách thông thường, để tạo số thứ tự từ 1 đến 50000 ta làm như sau:
PHP: 
Sub STT1()
  Dim i As Long
  For i = 1 To 50000
    Cells(i, 1) = i
  Next
End Sub
2> Bằng phương pháp mảng thì người ta làm bài toán trên như vầy:
PHP: 
Sub STT2()
  Dim i As Long, Arr()
  ReDim Arr(1 To 50000, 1 To 1)
  For i = 1 To 50000
    Arr(i, 1) = i
  Next
  Range("A1:A50000").Value = Arr
  ''Hoac Range("A1").Resize(UBound(Arr)).Value = Arr
End Sub
Hãy chạy 2 code và so sánh tốc độ xem cái nào nhanh hơn và thử mường tượng xem bạn hiểu được thứ gì
 
Upvote 0
ndu96081631 đã viết:
Lấy bài toán tạo STT cho bạn hình dung.
Sub STT2()
Dim i As Long, Arr()
ReDim Arr(1 To 50000, 1 To 1)
For i = 1 To 50000
Arr(i, 1) = i
Next
Range("A1:A50000").Value = Arr
''Hoac Range("A1").Resize(UBound(Arr)).Value = Arr
Nhấp chuột vào đây để mở rộng...
Vâng, hình như em không tỉnh táo nên nhầm lẫn 2 đối tượng Arr(i,1) nó khác hẳn với Rng(i)trong bài của thày. Em sẽ xem lại ngay
 
Lần chỉnh sửa cuối:
Upvote 0
ndu96081631 đã viết:
Lấy bài toán tạo STT cho bạn hình dung.
Hãy chạy 2 code và so sánh tốc độ xem cái nào nhanh hơn và thử mường tượng xem bạn hiểu được thứ gì
Nhấp chuột vào đây để mở rộng...

Cách 2 chạy nhanh hơn, em hiểu thế này có đúng không hả thày, nếu có gì sai mong thày chỉ bảo:
Cách 1 lâu hơn là bởi tính toán xong nó hiện ra màn hình xong tức ra sản phẩm cuối cùng (làm hết quy trình xong mới đi tiếp).
Cách 2 nó chỉ tính toán sơ bộ nhớ vào biến tạm Arr(i,1) tạm trong máy tính (chưa hiện ra màn hình vội) kiểu tựa như tắt tạm chức năng hiện ra trước đi tiếp cho nhanh, sau khi tính toán sơ bộ cho cả mảng thì đổ bộ 1 lúc ra toàn bộ kết quả ra. Bởi vậy nhanh hơn?.

Giải thích nôm na theo kiểu dân gian:
Cách 1: Nhặt được 1 quả bóng chạy vào vị trí cần để sau đó mới chạy ra nhặt quả thứ 2...đến quả 5.000 (tốn thời gian chạy trên đường vào chỗ cần để).

Cách 2: Lấy tạm cái rổ nào đó, vừa cầm vừa cho vào rổ, khi nhặt được hết rồi thì mới đổ toàn bộ vào vị trí của nó.
em hiểu như vậy không biết có gì sai không ah?

Tức là 1 thằng tính tạm xong mới gán thể hiện ở dòng Range("A1:A50000").Value = Arr đặt ở cuối, còn cái kia thì gán ngay ban đầu.
 
Lần chỉnh sửa cuối:
Upvote 0
ndu96081631 đã viết:
Hãy chạy 2 code và so sánh tốc độ xem cái nào nhanh hơn và thử mường tượng xem bạn hiểu được thứ gì
Nhấp chuột vào đây để mở rộng...

Em hiểu rồi, trong ví dụ cụ thể bài này thì bản chất khác nhau ở dòng bôi đỏ được đưa vào vị trí trước hay là sau, chính điều đó tạo sự khác biệt về tốc độ giúp công thức mảng nhanh hơn.

Cách 1 (tốc độ nhanh hơn):
Sub TinhTong()
Dim endR&, i&, sotien As Double
Dim Arr()
With Sheet1
'Xac dong cell cuoi co du lieu o cot C tu dong 65000 - dau'
endR = .Cells(65000, 3).End(xlUp).Row
'Gan vao array cot can tinh'
Arr = .Range("A2:C" & endR).Value
End With
'Dung vong lap' UBound(Arr)= so dong cua arr
For i = UBound(Arr) To 1 Step -1 'duyt tu duoi len
If Len(Arr(i, 1)) > 0 Then 'ie o nay chua co gi
Arr(i, 3) = sotien 'gan vao C
sotien = 0
Else
sotien = sotien + Arr(i, 3)
End If
Next i
'gán vào
With Sheet1
.[A2].Resize(UBound(Arr), 3) = Arr
End With
Erase Arr
End Sub

Cách 2 (tốc độ chậm hơn 1 chút):
Sub Test()
Dim Rng As Range, i As Long, Tmp As Double
Set Rng = Range("C2:C10")
For i = Rng.Rows.Count To 1 Step -1
Tmp = Tmp + Rng(i).Value
If Rng(i) = "" Then Rng(i) = Tmp: Tmp = 0
Next
End Sub
 
Upvote 0
Theo mình hiểu như thế này:
VD: Dữ liệu của Bạn từ A1:A10. Bây giờ yêu cầu xử lý dữ liệu bên mảng A1:A10 vào cho kết quả bên B1:B10
C1: Bạn xử lý A1 -> Sau đó copy dán ngày vào B1. Tiếp theo A2 -> B2.....
C2: Xử lý 1 lần số liệu bên A1, A2...A10 sau đó copy từ A1:A10 và dán vào B1:B10 là xong
=> cách 2 giảm được 9 lần copy.
 
Upvote 0
nguyenmanhnam đã viết:
Em hiểu rồi, trong ví dụ cụ thể bài này thì bản chất khác nhau ở dòng bôi đỏ được đưa vào vị trí trước hay là sau, chính điều đó tạo sự khác biệt về tốc độ giúp công thức mảng nhanh hơn.
Nhấp chuột vào đây để mở rộng...
Bạn có thể hiểu đơn giản thế này:
- Bất cứ thủ tục nào thực hiện TÍNH TOÁN TRỰC TIẾP TRÊN CELL đều cho tốc độ chậm
- Nếu người ta cho mọi thứ vào mảng và tính toán trên đó, xong việc lại gán kết quả vào bảng tính thì tốc độ sẽ nhanh hơn
--------------
Có thể mường tượng như thế này:
- Bạn vừa được phân công quản lý 1 phân xưởng có 100 công nhân
- Bạn dự tính sẽ phân bố lại vị trí làm việc cho từng công nhân và bạn đã làm như sau:
1> Cách 1: Đích thân dẫn từng công nhân đến từng vị trí làm việc (làm xong cũng vã mồ hôi)
2> Cách 2: Về văn phòng, soạn 1 bảng phân công trên giấy rồi đưa cho người ta xem để người ta tự mình đến vị trí làm việc như đã ghi trên giấy (khỏe re)
​
- Xem qua cũng biết cách nào tối ưu hơn rồi
 
Upvote 0
Giúp em hiểu một số đối tượng trong VBA

Em vẫn lơ mơ chưa phần biệt được sự khác nhau giữa Range và mảng, cụ thể trong bài giải của thày ptm0412 và thày ndu:

Arr = .Range("A2:C" & endR).Value
Set Rng = Range("C2:C10")

Tức là em thắc mắc tại sao mảng (Arr) lại có Value đi cùng trong khi đó Rng lại không, nghĩa là tại sao không là:
Set Rng = Range("C2:C10").Value?

Một số kí hiệu khi khai báo biến có nghĩa là gì (các kí hiệu mà em bôi màu đỏ):
Dim endR&, i&, sotien As Double
Dim Arr()
Vì chưa biết nhiều về VBA, xin được mọi người giúp đỡ.
 
Upvote 0
Dauthivan đã viết:
Em vẫn lơ mơ chưa phần biệt được sự khác nhau giữa Range và mảng, cụ thể trong bài giải của thày ptm0412 và thày ndu:

Arr = .Range("A2:C" & endR).Value
Set Rng = Range("C2:C10")

Tức là em thắc mắc tại sao mảng (Arr) lại có Value đi cùng trong khi đó Rng lại không, nghĩa là tại sao không là:
Set Rng = Range("C2:C10").Value?

Một số kí hiệu khi khai báo biến có nghĩa là gì (các kí hiệu mà em bôi màu đỏ):
Dim endR&, i&, sotien As Double
Dim Arr()
Vì chưa biết nhiều về VBA, xin được mọi người giúp đỡ.
Nhấp chuột vào đây để mở rộng...
Arr = .Range("A2:C" & endR).Value ---> là đưa toàn bộ giá trị trong từng cell của vùng "A2:C" & endR vào Arr và đương nhiên Arr trở thành 1 mảng 2 chiều (thế nên mới cần .Value... mà thật ra không có .Value nó vẫn hiểu)
Set Rng = Range("C2:C10") ---> bất cứ biến nào thuộc dạng biến đối tượng đều phải có động tác Set (chẳng hạn là Range, Workbook, Worksheet, Object... vân vân...). Khi ta thực hiện lệnh Set Rng = Range("C2:C10") thì mục đích của ta là muốn nói mọi thứ thuộc về vùng Range("C2:C10"), chứ không riêng gì Value (như giá trị, công thức, màu sắc, font chữ....)
--------------------------
Dim Arr() ---> Cái này xác định chính xác Arr chính là 1 mảng. Xin lưu ý thêm rằng nếu ta thấy lệnh như vầy Dim Arr() as Long thì không có nghĩa Arr là biến Long mà phải hiểu rằng Arr là 1 mảng chứa những giá trị thuộc biến Long
Dim endR&, i& ---> Cái này tôi không chắc lắm nhưng nếu tôi đoán không lầm thì nó tương đương với Dim endR as Long, i as Long
Có thể thí nghiệm để kiểm chứng
PHP: 
Sub Test()
  Dim i&
  MsgBox TypeName(i)
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
Xin chỉ rõ hơn hộ em về sự khác nhau giữa mảng và Range

Xin thày hãy chỉ giúp hộ em chút nữa sự khác nhau giữa Mảng và Range, cách chuyển đổi giữa 2 cái này.</span><br />Về khai báo em thấy nó cứ gần giống nhau, chẳng lẽ nguyên nhân “do cái tên” khi khai biến cách đặt tên một cái là ….as Range và một cái là Dim…() mà về cuối Range kết quả ngay, trong khi cái kia phải gán một lần nữa mới ra ah.</span><br />
 
Upvote 0
Dauthivan đã viết:
Xin thày hãy chỉ giúp hộ em chút nữa sự khác nhau giữa Mảng và Range, cách chuyển đổi giữa 2 cái này.</span><br />Về khai báo em thấy nó cứ gần giống nhau, chẳng lẽ nguyên nhân “do cái tên” khi khai biến cách đặt tên một cái là ….as Range và một cái là Dim…() mà về cuối Range kết quả ngay, trong khi cái kia phải gán một lần nữa mới ra ah.</span><br />
Nhấp chuột vào đây để mở rộng...
Cũng chẳng biết nói thế nào nữa. Tóm lại làm nhiều sẽ tự ngộ ra vấn đề
Có thể mường tượng thế này: Range là THỰC còn mảng là ẢO
- 1 cánh đồng đã được chia thành nhiều thửa ruộng, đó là THỰC (Range)
- 1 bản vẽ cánh đồng trên giấy, đó là ẢO (Array)
---------------
- Range bao gồm nhiều thành phần trong đó như Value, Color, Font... vân vân (như đã nói ở trên)
- Array chỉ là những giá trị (Value)... ----> Cái này có thể chưa đúng nhưng tạm hiểu vậy đi
Thật ra Range và Array chẳng liên quan gì nhau cả, chẳng qua khi cần thiết người ta mới dùng 1 phương pháp nào đó để chuyển đổi qua lại thôi
Khai báo đương nhiên đã xác định được biến là cái gì rồi. Ví dụ: Dim Arr as Range thì đương nhiên Arr là biến Range, còn nếu Dim Arr() thì đó là Array
Để chuyển từ Range sang Array, chỉ cần Arr = Range(...).Value
Đê chuyển từ Array sang Range, chỉ cẩn Range(...).Value = Arr
Chuyển đổi thật đơn giản
 
Lần chỉnh sửa cuối:
Upvote 0
ThuNghi đã viết:
Sao kg dùng ct subtotal mà phải viết code.
Bạn xem coe nhé, xét theo cột A và tính tổng cột C.
PHP: 
Sub TinhTong()
Dim endR&, i&, sotien As Double
Dim Arr()
With Sheet1
  'Xac dong cell cuoi co du lieu o cot C tu dong 65000 - dau'
  endR = .Cells(65000, 3).End(xlUp).Row
  'Gan vao array cot can tinh'
  Arr = .Range("A2:C" & endR).Value
End With
'Dung vong lap' UBound(Arr)= so dong cua arr
For i = UBound(Arr) To 1 Step -1 'duyt tu duoi len
  If Len(Arr(i, 1)) > 0 Then 'ie o nay chua co gi
    Arr(i, 3) = sotien 'gan vao C
    sotien = 0
  Else
    sotien = sotien + Arr(i, 3)
  End If
Next i
'gán vào
With Sheet1
  .[A2].Resize(UBound(Arr), 3) = Arr
End With
Erase Arr
End Sub
Nếu file ex 2007 thì save thành xlsm
Nhấp chuột vào đây để mở rộng...

Em xin hỏi nếu làm theo công thức mảng mà bây giờ em muốn thuật toán của bài đi theo chiều từ trên xuống dưới (ngược chiều với cách trên của bác Thunghi) thì bây giời em phải làm sao ah?
 
Upvote 0
Dauthivan đã viết:
Em xin hỏi nếu làm theo công thức mảng mà bây giờ em muốn thuật toán của bài đi theo chiều từ trên xuống dưới (ngược chiều với cách trên của bác Thunghi) thì bây giời em phải làm sao ah?
Nhấp chuột vào đây để mở rộng...
Với điều kiện dữ liệu này bạn làm như thế này xem sao
PHP: 
Sub testtong()
Dim arr, arrkq, lRow As Long, kqrow As Long
Range("c2,c6").ClearContents
arr = Range("c2:c10").Value
For lRow = 1 To UBound(arr, 1)

    If arr(lRow, 1) = "" Then
        kqrow = lRow
        arrkq = 0
    End If
    
    arrkq = arrkq + arr(lRow, 1)
    Cells(kqrow + 1, 3).Value = arrkq
    
Next

End Sub
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

solomon2211
PDF Tools giúp ghép, tách, xóa file pdf
    • Thích
    • Yêu thích
2
Trả lời
22
Đọc
400
YêuCN
Y
T
Tách dữ liệu mã số đơn vị và tên đơn vị ra 02 cột bằng VBA
Trả lời
12
Đọc
199
Duy Tùng 9x
Duy Tùng 9x
HBTuyenPQ
  • Question
Nhờ giúp đỡ code VBA tạo bài trắc nghiệm
Trả lời
5
Đọc
39
Hoàng Tuấn 868
H
C
Vị trí cuối cùng của chủ đề đã lướt qua trong cuộc đời!!!
    • Ngạc nhiên
    • Thích
Trả lời
2
Đọc
53
ptm0412
ptm0412
V
Phân tích dữ liệu cập nhật giá cổ phiếu và BCTC từ trên Vietstock,cophieu68 hoặc Cafef
Trả lời
3
Đọc
43
ptm0412
ptm0412
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom