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

nguyenmanhnam · 28/7/11

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).

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

nguyenmanhnam · 28/7/11

ndu96081631 đã viết:
- Dùng On Error Resume Next ở đầu code

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?

ndu96081631 · 28/7/11

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?

Ạ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

nguyenmanhnam · 28/7/11

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

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.

nguyenmanhnam · 29/7/11

.[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.

ndu96081631 · 29/7/11

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.

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

nguyenmanhnam · 29/7/11

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

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.

ndu96081631 · 29/7/11

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.

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ì

nguyenmanhnam · 29/7/11

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

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

nguyenmanhnam · 29/7/11

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ì

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.

nguyenmanhnam · 29/7/11

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ì

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

MinhCong · 29/7/11

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.

ndu96081631 · 29/7/11

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.

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

Dauthivan · 29/7/11

nguyenmanhnam đã viết:
Đặc biệt là dòng Erase Arr không biết có tác dụng gì ah?

Cái dòng đó có ý nghĩa là sau khi gán xong đã hoàn thành công việc thì những cái nhớ tạm nằm ở trong Arr sẽ xóa đi cho đỡ mệt. Thực ra cái này không có cũng được.

nmhung49 · 29/7/11

nguyenmanhnam đã viết:
Erase Arr không biết có tác dụng gì ah?.

Xóa mảng cho đỡ tốn bộ nhớ máy, mình nghĩ bạn muốn học mảng Arr trong VBA thì qua đây nè bạn chúc bạn thành công
http://www.giaiphapexcel.com/forum/showthread.php?46834-Các-câu-hỏi-về-mảng-trong-VBA-(Array)

Dauthivan · 31/7/11

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 đỡ.

ndu96081631 · 31/7/11

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 đỡ.

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

Dauthivan · 31/7/11

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. 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.

ndu96081631 · 1/8/11

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. 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.

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

Dauthivan · 9/1/12

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

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?

nmhung49 · 9/1/12

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?

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

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

Thành viên tiêu biểu

File đính kèm

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tiêu biểu

Thành viên tiêu biểu

Thành viên gắn bó

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tích cực

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Thành viên tích cực