Tính giá trung bình ngày (ngày tự chọn) (1 người xem)

[nguyenvannghia1412]

Chào ACE diễn dàn GiaiPhapExcell!

Em có 1 file Excell tính giá trung bình của (6 ngày, 9 ngày, 12 ngày) từ 2 sheet dữ liệu. Em nhờ ACE diễn dàn hướng dẫn em viết macro cho file Excell này.

Yêu cầu nội dung ở trong file Excell, mà em gửi kèm.

Chân thành cảm ơn./.

 

[leonguyenz]

Chào ACE diễn dàn GiaiPhapExcell!
Em có 1 file Excell tính giá trung bình của (6 ngày, 9 ngày, 12 ngày) từ 2 sheet dữ liệu. Em nhờ ACE diễn dàn hướng dẫn em viết macro cho file Excell này.
Yêu cầu nội dung ở trong file Excell, mà em gửi kèm.
Chân thành cảm ơn./.

Tôi làm thử giá 9 ngày và 12 ngày theo dữ liệu đưa lên, bạn tham khảo. Từ đó bạn áp dụng cho cách tính các ngày còn lại.
Đặt Name cho 2 bảng (CAP_DIEN và MCCB)
Công thức giá 9 ngày:

=VLOOKUP($B6,INDIRECT($B$1),MATCH("NGÀY "&$E$1,OFFSET(INDIRECT($B$1),-2,,1),0),0)*0.15+AVERAGE(OFFSET(INDIRECT($B$1),MATCH($B6,OFFSET(INDIRECT($B$1),,,,1),0)-1,MATCH("NGÀY "&$E$1,OFFSET(INDIRECT($B$1),-2,,1),0)-9,1,8))*0.85

Công thức giá 12 ngày:

=VLOOKUP($B6,INDIRECT($B$1),MATCH("NGÀY "&$E$1,OFFSET(INDIRECT($B$1),-2,,1),0),0)*0.075+AVERAGE(OFFSET(INDIRECT($B$1),MATCH($B6,OFFSET(INDIRECT($B$1),,,,1),0)-1,MATCH("NGÀY "&$E$1,OFFSET(INDIRECT($B$1),-2,,1),0)-12,1,11))*0.925

Viết Marco hay công thức thì bạn nên sửa lại dữ liệu, ngày thì ghi rõ ra ngày để tính toán, không nên ghi kiểu nửa mùa "NGÀY 28/05". Và dữ liệu không nên Merge cells, ...v.v.

 

[nguyenvannghia1412]

Cảm ơn Anh rất nhiều./

 

[nguyenvannghia1412]

Em cảm ơn anh./
Tại file e rút ngắn bớt, chứ file e dữ liệu khá lớn, nên e sợ áp dung công thức của a hướng dẫn sẽ có sai sót nên e định nhờ diễn dàn hướng dẫn e viết chương trình để hạn chế sai sót.
Em có chỉnh sửa lại file theo góp ý của a về ngày tháng.

Và em gửi lại file nhờ diễn dàn hướng dẫn cách nào it sai sót nhất.
Mà quan trọng là biết hàng lúc nào biết xuất ra và nhập vào ở cột yêu cầu.

Một lần nữa rất cám ơn ACE diễn dàn./.

 

[leonguyenz]

Em cảm ơn anh./
Tại file e rút ngắn bớt, chứ file e dữ liệu khá lớn, nên e sợ áp dung công thức của a hướng dẫn sẽ có sai sót nên e định nhờ diễn dàn hướng dẫn e viết chương trình để hạn chế sai sót.
Em có chỉnh sửa lại file theo góp ý của a về ngày tháng.
Và em gửi lại file nhờ diễn dàn hướng dẫn cách nào it sai sót nhất.
Mà quan trọng là biết hàng lúc nào biết xuất ra và nhập vào ở cột yêu cầu.
Một lần nữa rất cám ơn ACE diễn dàn./.

Thử hàm tự tạo này xem:

Function PriceGPE(iTm As String, Rng As Range, iDate As Date, xDay As Long)
Dim i As Long, k As Long, FindR As Range
Dim Tmp1 As Single, Tmp2 As Single, a As Single
On Error Resume Next
Set FindR = Rng.Resize(, 1).Find(iTm, , xlValues, xlWhole)
If Not FindR Is Nothing Then
    For i = 2 To Rng.Columns.Count
        If Rng(1, i) >= iDate - xDay And Rng(1, i) < iDate Then
            k = k + 1
            Tmp2 = Tmp2 + FindR.Offset(, i - 1)
        ElseIf Rng(1, i) = iDate Then
            Tmp1 = FindR.Offset(, i - 1)
            Exit For
        End If
    Next i
End If
If k Then
    Select Case xDay
        Case 6: a = 0.2
        Case 9: a = 0.15
        Case 12: a = 0.075
    End Select
    If Tmp1 = 0 Then
        PriceGPE = 0
    Else
        PriceGPE = Tmp1 * a + Tmp2 * (1 - a) / k
    End If
End If
End Function

Cú pháp: =PriceGPE(Tên vật tư, bảng dò bao gồm tiêu đề có ngày tháng, ngày đã chọn, giá x ngày)
Ví dụ, D6:

=PriceGPE($B6,IF($B$1="MCCB",MCCB!$B$3:$T$20,'CAP DIEN'!$B$3:$T$20),$E$1,9)

Phần còn lại bạn tự xử lý nhé.

 

[nguyenvannghia1412]

Cám ơn Anh Mr.Burn

Code a tạo rât hay, rất hữu it. Nhưng có 2 vấn đề là a chưa xem dùm e là:

1. Ngày cập nhật giá ở 2 file dữ lieu không liên tục (tuần chỉ 5 ngày thôi). còn ở sheet tính giá trung bình (ví dụ: tính giá 6 ngày của ngày chon là ngày 28/5/2014 là bao gồm các ngày 27; 26; 23; 22; 21; 20 /05/2014) nghĩa là trước 28/05/2014 là những ko cần biết ngày nào mien là lấy đúng 6 ô trước ngày 28/05/2014 là ok.

2. A chưa xem cột yêu cầu để biết ngày xuất ra hay nhập vào.
Ví dụ: - Trước tiên xem ngày chon là ngày 28/05/2014 mà Giá (9-12) = Giá (6 ngày)thì tô màu xanh 2 ô này.
- Tiếp theo là xem tiếp các cột của ngày kế ngày chon là ngày 28/05/2014 mà:
+ Giá (9-12)<Giá(6 ngày) thì cột yêu cầu xuất hiện chữ "Nhập"
+ Giá (9-12)>Giá (6 ngày) thì cột yêu cầu xuất hiện chữ "Xuất"

Mong anh xem giúp.

 

[leonguyenz]

Bạn không nói rõ là 8 ngày có đơn giá mà nói "8 ngày trước ngày 28/05"...
Sửa lại code:

Function PriceGPE(iTm As String, Rng As Range, iDate As Date, xDay As Long)
Dim i As Long, j As Long, FindR As Range
Dim Tmp1 As Single, Tmp2 As Single, a As Single
On Error Resume Next
Set FindR = Rng.Resize(, 1).Find(iTm, , xlValues, xlWhole)
If Not FindR Is Nothing Then
    For i = Rng.Columns.Count To 2 Step -1
        If Rng(1, i) = iDate Then
            Tmp1 = FindR.Offset(, i - 1)
            For j = 2 To xDay
                Tmp2 = Tmp2 + FindR.Offset(, i - j)
            Next j
        End If
    Next i
End If
Select Case xDay
    Case 6: a = 0.2
    Case 9: a = 0.15
    Case 12: a = 0.075
End Select
If Tmp1 = 0 Then
    PriceGPE = 0
Else
    PriceGPE = Tmp1 * a + Tmp2 * (1 - a) / (xDay - 1)
End If
End Function

Tô màu thì bạn có thể dùng Conditional Formatting, ví dụ với cột F:G : =AND($F6<>0,$G6<>0,$F6=$G6)
Nhập or Xuất thì dùng IF: =IF(F6<G6,"Nhập",IF(F6>G6,"Xuất",""))
Tôi thấy (9-12) luôn nhỏ hơn (6), không thấy trường hợp lớn hơn.

 

[nguyenvannghia1412]

Hi Anh Mr.Burn!
Cám ơn anh, anh đã giúp rất nhiều.

Anh có thể giúp em 1 chuyện nữa nha.

Em muốn là khi mình chon sheet và ngày rồi. thì mình chạy marco. Nó sẽ tự tính toán hết tất cả vật tư mà mình có ở bảng cập nhật giá. Và nếu giá ngày mình chọn =0 thì nó sẽ bỏ qua không tính.

Còn anh thấy không có trường hợp lớn hơn là vì giá trong bảng nó tang hang ngày, còn thực tế có lúc tang lúc giảm nên sẽ có số âm hoặc dương.

Cám ơn anh rất nhiều! và cũng cám ơn diễn dàn đã lập ra diễn dàn này để mọi người có thể trao đổi thong tin./.