Giúp sửa code: Tính tổng cho từng nhóm
Em chào thầy cô & anh chị
Xin giúp em sửa code tính tổng cho từng nhóm như sau:
Nếu Mã là 131 thì cộng các mã con có ký tự đầu là B
Nếu Mã là 331 thì cộng các mã con có ký tự đầu là M
Nếu Mã là 1388 thì cộng các mã con có ký tự đầu là Y
Nếu Mã là 3388 thì cộng các mã con có ký tự đầu là ZSub KetQua() Dim sArray, Arr() Dim i As Long Dim n7 As Range, n8 As Range Dim B As String, M As String, Y As String, Z As String Dim Wf As WorksheetFunction With ActiveSheet sArray = Range([B11], [B65536].End(3)).Value Set n7 = Range([B11], [B65536].End(3)) Set n8 = n7.Offset(, 1) End With ReDim Arr(1 To UBound(sArray, 1), 1 To 1) For i = 1 To UBound(sArray, 1) If IsNumeric(sArray(i, 1)) Then If sArray(i, 1) = 131 Then [COLOR=#ff0000]Arr(i, 1) = Wf.SumIf(n7, "B" & "*", n8)[/COLOR] ElseIf sArray(i, 1) = 331 Then [COLOR=#ff0000]Arr(i, 1) = Wf.SumIf(n7, "M" & "*", n8)[/COLOR] ElseIf sArray(i, 1) = 1388 Then [COLOR=#ff0000]Arr(i, 1) = Wf.SumIf(n7, "Y" & "*", n8)[/COLOR] Else: sArray(i, 1) = 3388 [COLOR=#ff0000] Arr(i, 1) = Wf.SumIf(n7, "Z" & "*", n8)[/COLOR] End If End If Next i Range("C11").Resize(UBound(Arr, 1)).Value = Arr End SubXin vui lòng xem File, Muốn kết quả chạy code như những cell tô màu vàng.
Set Wf = WorksheetFunction
—
Nhưng phát sinh thêm vấn đề sau khi chạy code thì số liệu bên cột C tương ứng với các mã là chuỗi như b1, b2 … z3 bị xóa
Bây giờ em muốn giữ lại các số liệu này thì phải sửa code như thế nào?
Em cảm ơn
———
P/s: code báo lỗi ở những dòng màu đỏ
Thay vì
ReDim Arr(1 To UBound(sArray, 1), 1 To 1)
Ta sửa thành
Arr = n8.Value
là được rồi
Ngoài ra, chổ này:
With ActiveSheet
sArray = Range([B11], [B65536].End(3)).Value
Set n7 = Range([B11], [B65536].End(3))
Set n8 = n7.Offset(, 1)
End WithSửa thành vầy sẽ "đẹp" hơn
With ActiveSheet
Set n7 = [SIZE=5][COLOR=#ff0000].[/COLOR][/SIZE]Range([SIZE=5][COLOR=#ff0000].[/COLOR][/SIZE][B11], [SIZE=5][COLOR=#ff0000].[/COLOR][/SIZE][B65536].End(3))
Set n8 = n7.Offset(, 1)
sArray = n7.Value
End WithChú ý các dấu chấm (.) màu đỏ nha (nêu không thì With ActiveSheet làm quái gì)
www.giaiphapexcel.com/diendan/threads/gi%C3%BAp-s%E1%BB%ADa-code-t%C3%ADnh-t%E1%BB%95ng-cho-t%E1%BB%ABng-nh%C3%B3m.75618/
Cho em hỏi câu lệnh dưới cũng có nghĩa là Xóa mảng Arr và tính tóan lại?
ReDim Arr(1 To UBound(sArray, 1), 1 To 1)Em cảm ơn!
Mình nói thêm 2 chổ chưa "đẹp" ở code trên
1> With ActiveSheet
Có nghĩa là code này có thể chạy ở bất cứ sheet nào cũng được (nó sẽ lấy dữ liệu tại sheet đó) —> Vậy thì With ActiveSheet hóa ra thừa
Vậy: Hoặc là bỏ cái With ấy đi, hoặc là With Sheets("Sheet1") (hay With chính xác tên 1 sheet nào đó)
2> Wf.SumIf(n7, "B" & "*", n8)
Có phải ghi gọn vầy sẽ hợp lý không
Wf.SumIf(n7, "B*", n8)
tương tự cho các SUMIF khác
Câu lệnh này khởi tạo mảng nhưng chưa có phần tử nào bên trong —> Lúc bạn IF, ElseIF… gì gì đó, chổ nào thỏa mản đ/k sẽ được nạp vào mảng… còn lại thì vẫn rổng như thường
Trong khi nếu Arr= n8.Value thì mảng này được khởi tạo và có giá trị luôn —> Lúc bạn IF, ElseIF… gì gì đó, chổ nào thỏa mản đ/k sẽ được nạp vào mảng… còn lại thì vẫn giữ giá trị cũ
Vậy thôi
Bây giờ em áp dụng vào bài thực tế của em như sau:
Yêu cầu của em là tính tồn cuối kỳ tại cột H & I
1/ Nếu mã Khách hàng (cột B) là chuỗi Text, thì cthức có dạng
Ví dụ tại cell H12 =MAX(D12+F12-E12-G12;0)
Ví dụ tại cell I12=MAX(E12+G12-D12-F12;0)2/ Nếu mã Khách hàng (cột B) là Number (ví dụ 131, 331, 1388, 3388), thì cthức có dạng
tính tổng cho từng nhóm như sau:
Nếu Mã là 131 thì cộng các mã con có ký tự đầu là B
Ví dụ tại cell H11 =SUMIF($B$11:$B$83;"B*";$H$11:$H$83)
Ví dụ tại cell I11=SUMIF($B$11:$B$83;"B*";$I$11:$I$83)Tương tự cho các mã dưới
Nếu Mã là 331 thì cộng các mã con có ký tự đầu là M
Nếu Mã là 1388 thì cộng các mã con có ký tự đầu là Y
Nếu Mã là 3388 thì cộng các mã con có ký tự đầu là Z
(Em có lập cthức tại cột H & I)
Em viết code như sau:Sub cuoiky_2() Dim sArray, Arr() Dim i As Long Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range Dim B As String, M As String, Y As String, Z As String Dim Wf As WorksheetFunction Set Wf = WorksheetFunction With ActiveSheet Set n7 = .Range(.[B11], .[B65536].End(3)) Set n8 = n7.Offset(, 6) Set n9 = n7.Offset(, 7) sArray = n7.Resize(, 8).Value End With ReDim Arr(1 To UBound(sArray, 1), 1 To 2) For i = 1 To UBound(sArray, 1) ' tao so lieu cot H If Not IsNumeric(sArray(i, 1)) Then If sArray(i, 3) + sArray(i, 5) - sArray(i, 4) - sArray(i, 6) > 0 Then Arr(i, 1) = sArray(i, 3) + sArray(i, 5) - sArray(i, 4) - sArray(i, 6) Else If sArray(i, 1) = 131 Then Arr(i, 1) = Wf.SumIf(n7, "B*", n8) ElseIf sArray(i, 1) = 331 Then Arr(i, 1) = Wf.SumIf(n7, "M*", n8) ElseIf sArray(i, 1) = 1388 Then Arr(i, 1) = Wf.SumIf(n7, "Y*", n8) ElseIf sArray(i, 1) = 3388 Then Arr(i, 1) = Wf.SumIf(n7, "Z*", n8) End If End If End If ' tao so lieu cot I If Not IsNumeric(sArray(i, 1)) Then If sArray(i, 4) + sArray(i, 6) - sArray(i, 3) - sArray(i, 5) > 0 Then Arr(i, 2) = sArray(i, 4) + sArray(i, 6) - sArray(i, 3) - sArray(i, 5) Else If sArray(i, 1) = 131 Then Arr(i, 2) = Wf.SumIf(n7, "B*", n9) ElseIf sArray(i, 1) = 331 Then Arr(i, 2) = Wf.SumIf(n7, "M*", n9) ElseIf sArray(i, 1) = 1388 Then Arr(i, 2) = Wf.SumIf(n7, "Y*", n9) ElseIf sArray(i, 1) = 3388 Then Arr(i, 2) = Wf.SumIf(n7, "Z*", n9) End If End If End If Next i Range("H11").Resize(UBound(Arr, 1), 2).Value = Arr End SubKhi chạy code, thì chỉ kết quả khi các cell tại cột B có giá trị là chuỗi Text (ví dụ: BKVL, B004…)
Còn các cell của cột B có giá trị là số (ví dụ :131, 331, 1388, 3388) thì CODE KHÔNG CHO KẾT QỦA (trong File em có tô các cell màu vàng)
Xin vui lòng chỉ chỗ sai của code, em cảm ơn!
Sửa lại đây:
Sub cuoiky_2()
Dim sArray, Arr()
Dim i As Long
Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range
Dim B As String, M As String, Y As String, Z As String
Dim dTmp1 As Double, dTmp2 As Double
Dim Wf As WorksheetFunction
Set Wf = WorksheetFunction
With Sheets("CNT11")
Set n7 = .Range(.[B11], .[B65536].End(3))
Set n8 = n7.Offset(, 6)
Set n9 = n7.Offset(, 7)
sArray = n7.Resize(, 8).Value
End With
For i = 1 To UBound(sArray, 1)
If Not IsNumeric(sArray(i, 1)) Then
dTmp1 = sArray(i, 3) + sArray(i, 5) - sArray(i, 4) - sArray(i, 6)
dTmp2 = sArray(i, 4) + sArray(i, 6) - sArray(i, 3) - sArray(i, 5)
If dTmp1 > 0 Then sArray(i, 7) = dTmp1
If dTmp2 > 0 Then sArray(i, 8) = dTmp2
Else
If sArray(i, 1) = 131 Then
sArray(i, 7) = Wf.SumIf(n7, "B*", n8)
sArray(i, 8) = Wf.SumIf(n7, "B*", n9)
ElseIf sArray(i, 1) = 331 Then
sArray(i, 7) = Wf.SumIf(n7, "M*", n8)
sArray(i, 8) = Wf.SumIf(n7, "M*", n9)
ElseIf sArray(i, 1) = 1388 Then
sArray(i, 7) = Wf.SumIf(n7, "Y*", n8)
sArray(i, 8) = Wf.SumIf(n7, "Y*", n9)
ElseIf sArray(i, 1) = 3388 Then
sArray(i, 7) = Wf.SumIf(n7, "Z*", n8)
sArray(i, 8) = Wf.SumIf(n7, "Z*", n9)
End If
End If
Next i
n7.Resize(, 8).Value = sArray
End SubBạn tự so sánh và suy nghĩ xem mình sai chổ nào nhé
(Khai báo biến chi cả "rừng" vậy, có xài hết đâu)
Em xin cảm ơn Thầy!
Code này chạy lần thứ nhất thì chỉ tạo kết qủa cho những mã khách hàng là chuỗi Text
Vẫn để tồn tại kết code lần thứ 1, chạy code lần thứ 2 thì mới cho kết quả cùa các mã 131, 331, 1388, 3388
Thầy xem lại giúp em.
Tại vỉ muốn SUMIF có kết quả thì mấy thằng tính MAX kia phải có kết quả trước (không thể đồng thời được)
Tôi sơ suất chút! Bạn tự nghiên cứu xem
Em tách IF ra, gán giá trị lần thứ nhất và cho chạy For thêm 1 lần nữa
Hổng biết nhìn có buồn cười không!Sub cuoiky_2() Dim sArray, arr() Dim i As Long Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, n5 As Range, n6 As Range, n7 As Range, n8 As Range, n9 As Range Dim B As String, M As String, Y As String, Z As String Dim dTmp1 As Double, dTmp2 As Double Dim Wf As WorksheetFunction Set Wf = WorksheetFunction With Sheets("CNT11") Set n7 = .Range(.[B11], .[B65536].End(3)) Set n8 = n7.Offset(, 6) Set n9 = n7.Offset(, 7) sArray = n7.Resize(, 8).Value End With For i = 1 To UBound(sArray, 1) If Not IsNumeric(sArray(i, 1)) Then dTmp1 = sArray(i, 3) + sArray(i, 5) - sArray(i, 4) - sArray(i, 6) dTmp2 = sArray(i, 4) + sArray(i, 6) - sArray(i, 3) - sArray(i, 5) If dTmp1 > 0 Then sArray(i, 7) = dTmp1 If dTmp2 > 0 Then sArray(i, 8) = dTmp2 End If Next i n7.Resize(, 8).Value = sArray For i = 1 To UBound(sArray, 1) If sArray(i, 1) = 131 Then sArray(i, 7) = Wf.SumIf(n7, "B*", n8) sArray(i, 8) = Wf.SumIf(n7, "B*", n9) ElseIf sArray(i, 1) = 331 Then sArray(i, 7) = Wf.SumIf(n7, "M*", n8) sArray(i, 8) = Wf.SumIf(n7, "M*", n9) ElseIf sArray(i, 1) = 1388 Then sArray(i, 7) = Wf.SumIf(n7, "Y*", n8) sArray(i, 8) = Wf.SumIf(n7, "Y*", n9) ElseIf sArray(i, 1) = 3388 Then sArray(i, 7) = Wf.SumIf(n7, "Z*", n8) sArray(i, 8) = Wf.SumIf(n7, "Z*", n9) End If Next i n7.Resize(, 8).Value = sArray End Sub
Thì chắc chắn phải vậy rồi (gì mà buồn cười chứ)
Có điều tôi chắc rằng bài này vẫn có thể dùng 1 vòng lập, nhưng khi ấy phải có sự tham gia của Dictionary (để đánh dấu vị trí)… đồng thời khỏi cần phải SUMIF luôn
E rằng hơi quá sức của bạn, tuy nhiên nếu bạn có hứng thú thì cứ việc nghiên cứu (làm được sẽ là bước đột phá cho bản thân đấy)
Tham gia cho vui. 1 vòng lặp, không dùng Sumif, không dùng Dic. Liều thôi, nhưng nếu hỏng trúng thì H Van chịu.
Sub test()
Dim dl(), tam7 As Double, tam8 As Double
Dim i As Long, a As Long, b As Long, c As Long, d As Long
.ClearContents
dl = Range(, .End(3)).Resize(, 8).Value
For i = 1 To UBound(dl)
If Not IsNumeric(dl(i, 1)) Then
tam7 = dl(i, 3) + dl(i, 5) – dl(i, 4) – dl(i, 6)
tam8 = dl(i, 4) + dl(i, 6) – dl(i, 3) – dl(i, 5)
If tam7 > 0 Then dl(i, 7) = tam7
If tam8 > 0 Then dl(i, 8) = tam8
Else
If dl(i, 1) = 131 Then a = i
If dl(i, 1) = 331 Then b = i
If dl(i, 1) = 1388 Then c = i
If dl(i, 1) = 3388 Then d = i
End If
If dl(i, 1) Like "B*" Then
dl(a, 7) = dl(a, 7) + dl(i, 7)
dl(a, 8) = dl(a, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "M*" Then
dl(b, 7) = dl(b, 7) + dl(i, 7)
dl(b, 8) = dl(b, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "Y*" Then
dl(c, 7) = dl(c, 7) + dl(i, 7)
dl(c, 8) = dl(c, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "Z*" Then
dl(d, 7) = dl(d, 7) + dl(i, 7)
dl(d, 8) = dl(d, 8) + dl(i, 8)
End If
Next
.Resize(i – 1, 8) = dl
End Sub
Bố trí code gì mà thụt ra thụt vào tùy tiện quá… nhìn.. đuối luôn
Bố trí như vậy nhìn vào chẳng ai hiểu được dòng nào thuộc "cấp bậc" nào
Bố trí lại đây:
Sub test()
Dim dl(), tam7 As Double, tam8 As Double
Dim i As Long, a As Long, b As Long, c As Long, d As Long
[H11:I83].ClearContents
dl = Range([B11], [B65536].End(3)).Resize(, 8).Value
For i = 1 To UBound(dl)
If Not IsNumeric(dl(i, 1)) Then
tam7 = dl(i, 3) + dl(i, 5) - dl(i, 4) - dl(i, 6)
tam8 = dl(i, 4) + dl(i, 6) - dl(i, 3) - dl(i, 5)
If tam7 > 0 Then dl(i, 7) = tam7
If tam8 > 0 Then dl(i, 8) = tam8
Else
If dl(i, 1) = 131 Then a = i
If dl(i, 1) = 331 Then b = i
If dl(i, 1) = 1388 Then c = i
If dl(i, 1) = 3388 Then d = i
End If
If dl(i, 1) Like "B*" Then
dl(a, 7) = dl(a, 7) + dl(i, 7)
dl(a, 8) = dl(a, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "M*" Then
dl(b, 7) = dl(b, 7) + dl(i, 7)
dl(b, 8) = dl(b, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "Y*" Then
dl(c, 7) = dl(c, 7) + dl(i, 7)
dl(c, 8) = dl(c, 8) + dl(i, 8)
ElseIf dl(i, 1) Like "Z*" Then
dl(d, 7) = dl(d, 7) + dl(i, 7)
dl(d, 8) = dl(d, 8) + dl(i, 8)
End If
Next
[B11].Resize(i - 1, 8) = dl
End SubDám cá rằng bài này quanghai chưa tính đến trường hợp mà dl(1,1) không phải là number —> Có nghĩa là cột 1 của dl người ta bố trí ngược lại
Hay nói cách khác: a, b, c, d là vị trí mà ta đánh dấu. Vậy ngay từ đầu vào vòng lập, gặp ngay thằng Text chứ không phải Number thì cái dấu hiệu ấy ở đâu mà ra?
Ẹc… Ẹc…
Viết code phải tổng quát chút đồng chí à
Em đã nói rồi mà, hỏng trúng thì H Van chịu mà. Ý tưởng thuật toán của em là thế, nếu chưa chuẩn thì trao chuốt tí nữa chắc ok mà.
Cái vụ thụt vào xéo xéo cho đẹp thôi mà.
Còn cái vụ tổng quát gì gì đó thì em chưa đủ kinh nghiệm để soi hết các tình huống. Tuy nhiên em vẫn thích nuôi bệnh hơn là cho uống thuốc 1 liều như anh. Em ở gần nhà bác sĩ mà.
Đọc mãi mà vẫn cóc hiểu gì ráo
Nhìn vào dữ liệu sắp xếp trong bài thì đây là bài toán tổng con đơn giản mà, viết code chỉ vài dòng là Ok, chỉ khi nào sắp xếp lung tung, mới quan tâm tới mấy em "số" với chả phải "số", "Bê" "Y" với "Bê Em" chứ nhỉ ????
"Kế với chả toán", rắc rối "bà cố". Híc
Gần giống như tổng con mà hổng phải tổng con. (vì vùng cần lấy tổng sẽ lấy tất từ trên xuống)
– Chổ nào mà cột 1 là number thì tính SUMIF (sumif theo điều kiện gì thì đã mô tả ở bài 6 đó anh)…
– Còn lại, chổ nào mà cột 1 là Text thì tính MAX
– Mà vùng tính tổng của SUMIF lại lấy kết quả từ MAX ở trên
– Vậy nên, muốn đơn giản thì ta tính MAX trước, xong sẽ tính SUMIF
– Như vậy code phải có 2 vòng lập
– Ở đây em tin rằng bài toán này vừa có thể tính MAX vừa có thể tính SUMIF (kiểu như tính MAX đến đâu thì cộng dồn đến nấy)
Vậy đấy! Mời anh tham gia
Ẹc… Ẹc…
——————-
Cái chữ TỔNG QUÁT ở đây mình muốn nói rằng: Dữ liệu bố trí sao cũng ra kết quả chính xác, thế thôi!
Ý mình là thế này, hông biết đúng hông
1)- Bạn chủ topic nói:
-Nếu Mã là 131 thì cộng các mã con có ký tự đầu là B
-Nếu Mã là 331 thì cộng các mã con có ký tự đầu là M
-Nếu Mã là 1388 thì cộng các mã con có ký tự đầu là Y
-Nếu Mã là 3388 thì cộng các mã con có ký tự đầu là Z
Nhưng trong bài bạn í đã sắp xếp dữ liệu "Mã con" nằm đúng vị trí với "Mã…cha" rồi mà, thế thì mình không cần quan tâm tới Mã con nào thuộc nhóm Mã nào nữa
2)- Bi giờ chỉ cần cho chạy từ dưới lên trên, ở cột , em nào là "text"_ Mã con_ thì tính Max dựa theo các cột D, E, F & G ( các cột này bạn í hỏi trong cái topic "Phát sinh công nợ" hay cái quái gì đó dùng Sumproduct ), em nào là "Number" _Mã….cha_thì tính tổng.
Túm lại, nếu mình hiểu trúng dữ liệu luôn được sắp xếp như thế thì code đại khái thế này:Public Sub LungTungXeng() Dim Vung As Variant, ArrKq As Variant, I As Long, TongNo As Double, TongCo As Double, VungTong As Range Vung = Range([B11], [B50000].End(xlUp)).Resize(, 6) ReDim ArrKq(1 To UBound(Vung), 1 To 2) For I = UBound(Vung) To 1 Step -1 If IsNumeric(Vung(I, 1)) Then ArrKq(I, 1) = TongNo: ArrKq(I, 2) = TongCo TongNo = 0: TongCo = 0 Else ArrKq(I, 1) = IIf(Vung(I, 3) + Vung(I, 5) > Vung(I, 4) + Vung(I, 6), Vung(I, 3) + Vung(I, 5) - Vung(I, 4) - Vung(I, 6), 0) ArrKq(I, 2) = IIf(Vung(I, 3) + Vung(I, 5) < Vung(I, 4) + Vung(I, 6), Vung(I, 4) + Vung(I, 6) - Vung(I, 3) - Vung(I, 5), 0) TongNo = TongNo + ArrKq(I, 1): TongCo = TongCo + ArrKq(I, 2) End If Next I [H11].Resize(UBound(Vung), 2) = ArrKq Set VungTong = Range([B11], [B50000].End(xlUp)) For I = 2 To 7 [B50000].End(xlUp)(2).Offset(, I) = Application.WorksheetFunction.Sum(VungTong.Offset(, I)) / 2 Next I End SubCòn nếu hiểu sai thì …."tèo"
Đã nói "kế với chả toán" rắc rối bà cố
Híc
Khoá học Trưởng phòng nhân sự
Nguồn nhân lực là một trong Tứ trụ kinh doanh của doanh nghiệp, có tác động tới sự tồn tại và phát triển bền...
Xem khóa học