tự động tính trung bình từng năm!

[levu103]

Chào mấy anh chị,
Xin giúp em viết đoạn code với yêu cầu sau:
Cột A: ngày tháng năm (vd 1-1-2008; 1-5-2008; 5-12-2008; 1-1-2009...2010..
Cột B: giá trị thu nhập chi nhánh 1
Cột C: giá trị thu nhập chi nhánh 2
Cột D: tổng thu nhập cộng dồn chi nhánh 1
Cột E: tổng thu nhập cộng dồn chi nhánh 2
- Tính trung bình thu nhập (cột B và C) cho từng năm không lấy giá trị 0 (trung bình thu nhập trong năm 2008, 2009...)
- Lấy giá trị thu nhập cộng dồn (cột D và E) tại ngày tháng cuối cùng trong năm đó
Tính tay thì làm dễ rồi. Nhưng vì số liệu em nhiều quá và thay đổi liên tục nên mỗi lần làm lại rất tốn thời gian. Nên cầu cứu đến mấy anh chị trên diễn đàn. Xin lỗi là vì số liệu em nhiều quá nên không đưa 1 file ví dụ lên được. Mong giúp em!

Thanks

 

[SA_DQ]

. Xin lỗi là vì số liệu em nhiều quá nên không đưa 1 file ví dụ lên được. Mong giúp em!

Fải đưa lên chứ bạn, bằng cách cắt bớt sang file giả lập độ vài ngàn rcords thôi;

Chờ file của bạn, nếu bạn muốn nhận được trả lời nhanh nhứt có thể!

 

[levu103]

Chào anh,
Đầu tiên rất cám ơn anh đã quan tâm. Em xin up lên 1 bảng VD. Đầy là dự báo từ 2005-2030, em chỉ lấy vài năm ra thôi. Mong anh giúp em!

Thanks

 

[dat_butmuc]

Chào anh,
Đầu tiên rất cám ơn anh đã quan tâm. Em xin up lên 1 bảng VD. Đầy là dự báo từ 2005-2030, em chỉ lấy vài năm ra thôi. Mong anh giúp em!

Thanks

Xem qua dữ liệu của bạn, tôi nghĩ nên dùng Pivot
File kèm
....
đang để 20000 dòng, vượt quá thì mở rộng thêm vùng dữ liệu
Mỗi lần cập nhật dữ liệu hay thay đổi dữ liệu gốc thì vào Pivot Table Tools -> Refresh !

 

[levu103]

Chào anh Dat_butmuc,
Dung pivot table thì khi tính average sẽ tính luôn giá trị 0. Em cần tính trung bỏ các giá trị 0. Thanks anh giúp đỡ!

 

[dat_butmuc]

Chào anh Dat_butmuc,
Dung pivot table thì khi tính average sẽ tính luôn giá trị 0. Em cần tính trung bỏ các giá trị 0. Thanks anh giúp đỡ!

Thử với công thức xem?
File kèm !

 

[ThuNghi]

Thử với công thức xem?
File kèm !

Đâu cần ct gì mà phức tạp vậy. Chỉ cần lấy duy nhất năm, mà có 4 năm thì nhập tay.
Và chỉ cần dùng sumproduct hay ex 2007 thì dùng sumifs hay AVERAGEIFS là ok mà.
I3=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)
Hay dùng code. Cái tính TB này học của NDU đó.

Sub TinhTB()
Dim endR As Long, i As Long, s As Long, iR As Long, k As Long
Dim ArrKQ(), ArrData(), ArrTmp1(), ArrTmp2()
Dim Dic As Object, tmpY As String
Set Dic = CreateObject("Scripting.Dictionary")
With Sheet1
  endR = .Cells(100000, 1).End(xlUp).Row
  ArrData = .Range("B3:F" & endR).Value
End With
s = 0
ReDim ArrKQ(1 To UBound(ArrData), 1 To 5)
ReDim ArrTmp1(1 To UBound(ArrData), 1 To 4)
ReDim ArrTmp2(1 To UBound(ArrData), 1 To 4)
For i = 1 To UBound(ArrData)
  tmpY = ArrData(i, 1)
  If Not Dic.Exists(tmpY) Then
    s = s + 1
    Dic.Add tmpY, s
    ArrKQ(s, 1) = tmpY
  End If
  If Dic.Exists(tmpY) Then
    iR = Dic.Item(tmpY)
    For k = 1 To 4
      If ArrData(i, k + 1) > 0 Then
        ArrTmp1(iR, k) = ArrTmp1(iR, k) + 1
        ArrTmp2(iR, k) = ArrTmp2(iR, k) + ArrData(i, k + 1)
        ArrKQ(iR, k + 1) = ArrTmp2(iR, k) / ArrTmp1(iR, k)
      End If
    Next k
  End If
Next i
With Sheet1
  .[H3].Resize(s, 5) = ArrKQ
End With
Erase ArrKQ(), ArrData(), ArrTmp1(), ArrTmp2()
Set Dic = Nothing
End Sub

I3=AVERAGEIFS(C$3:C$2381,$B$3:$B$2381,$H3,C$3:C$2381,">0")

 

[dat_butmuc]

Đâu cần ct gì mà phức tạp vậy. Chỉ cần lấy duy nhất năm, mà có 4 năm thì nhập tay.
Và chỉ cần dùng sumproduct hay ex 2006 thì dùng sumifs là ok mà.
I3=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)
Hay dùng code. Cái tính TB này học của NDU đó.

Đồng ý với Bác là Code trong trường hợp này là "ngon" !
nhưng mà đâu phải có 4 năm, của bạn ấy là

dự báo từ 2005-2030

tuy nhiên cũng có thể nhập tay nên làm cho nó tổng quát hơn chút !
Còn cái này:

I3=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)

không được đâu nha -> nó sẽ ra #Div/0! hoặc nếu có như vầy

=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(--($B$3:$B$2381=$H3))

cũng sai !
Ít nhất thì cũng là vầy:

=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0))

Ở đây đang tận dụng hàm AVERAGEIFS đối với Excel~2007 vì đặt Name & lường trước các CH3, CH4, CHn..có vẻ phức tạp vậy chứ không thì cũng chỉ là:

=AVERAGEIFS(dat,OFFSET(dat,,-1),$H3,dat,">0")

 

[ThuNghi]

Sorry vì copy ct vào thiếu
=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0))

 

[dat_butmuc]

Sorry vì copy ct vào thiếu
=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0))

"Chọt" Bác chút !
Chỗ màu Đỏ là không cần thiết - nên bỏ đi , nó sẽ thành:

=SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381))/SUMPRODUCT(($B$3:$B$2381=$H3)*(C$3:C$2381<>0))

 

[SA_DQ]

Bạn tham khảo thêm hàm tự tạo

cho iêu cầu:

Tính trung bình thu nhập (cột B và C) cho từng năm không lấy giá trị 0 (trung bình thu nhập trong năm 2008, 2009...)

Với iêu cầu sau chắc dễ hơn & . . . .