Viết lệnh VBA cho chuỗi có vòng lặp biến đổi dựa trên giá trị của chuỗi ban đầu (4 người xem)

myguitar · 10/2/15

Em chào cả nhà,

Em đang phải viết lệnh VBA cho một chuỗi số liệu với vòng lặp biến đổi theo giá trị của chuỗi số liệu.
Bình thường thì vòng lặp là 10 giá trị của chuỗi. Nhưng trong trường hợp chuỗi xuất hiện giá trị âm thì giá trị vòng lặp tăng lên theo số các số âm trong chuỗi.

Công thức tính của cái này hơi phức tạp nên em gửi kèm trong tệp đính kèm phần tính toán của em. Trong tệp, chuỗi lặp cố định ký hiệu là E10. Còn chuỗi lặp biến đổi ký hiệu là Ej. Chuỗi Ej trong tệp thì em mới tính thủ công. Nhờ các bác xem giúp em lệnh nào để tính Ej một cách tự động mà ko phải lần mò từng giá trị âm với ạ.

Em cảm ơn các bác!

hungpecc1 · 10/2/15

myguitar đã viết:
Em chào cả nhà,

Em đang phải viết lệnh VBA cho một chuỗi số liệu với vòng lặp biến đổi theo giá trị của chuỗi số liệu.
Bình thường thì vòng lặp là 10 giá trị của chuỗi. Nhưng trong trường hợp chuỗi xuất hiện giá trị âm thì giá trị vòng lặp tăng lên theo số các số âm trong chuỗi.

Công thức tính của cái này hơi phức tạp nên em gửi kèm trong tệp đính kèm phần tính toán của em. Trong tệp, chuỗi lặp cố định ký hiệu là E10. Còn chuỗi lặp biến đổi ký hiệu là Ej. Chuỗi Ej trong tệp thì em mới tính thủ công. Nhờ các bác xem giúp em lệnh nào để tính Ej một cách tự động mà ko phải lần mò từng giá trị âm với ạ.

Em cảm ơn các bác!

cho hỏi vấn đề này áp dụng vào lĩnh vực gì vậy ?

myguitar · 10/2/15

hungpecc1 đã viết:
cho hỏi vấn đề này áp dụng vào lĩnh vực gì vậy ?

Bài tập về nhà của em ạ. Sắp phải nộp rùi. Đợi mãi chưa thấy bác nào cứu em, hu hu

giola · 10/2/15

myguitar đã viết:
Bài tập về nhà của em ạ. Sắp phải nộp rùi. Đợi mãi chưa thấy bác nào cứu em, hu hu

Các số âm đó có tính tiếp khi lặp không- tức là ví dụ giờ tăng lên E14 tức là phải lùi thêm 4 giá trị D, trong 4 giá trị này lại có 2 giá trị âm (<0) thì có mở rộng lên thành E16 (tức là 16 giá trị) không, cứ vậy tiếp tục mở rộng nếu có giá trị âm (<0) hay không???

-- vì là bài tập nên gợi ý bạn thử làm

+ xét số giá trị số âm, nếu lặp thì dùng lặp xét tiếp.
+ mở rộng vùng theo a và tính như bạn đã làm với sum_10
+ chú ý vùng tham chiếu của hàm phải đủ số giá trị, nếu không đủ thì hàm báo lỗi,

và bạn chưa nói bài tập gì, ứng dụng j

VetMini · 11/2/15

myguitar đã viết:
Bài tập về nhà của em ạ. Sắp phải nộp rùi. Đợi mãi chưa thấy bác nào cứu em, hu hu

Ở đấy mà khóc lóc.
Muốn được trả lời nhanh chóng thì diễn đạt cho rõ ràng, ví dụ chi tiết.
Cho ví dụ thẳng thừng mò dần từng giá trị âm ra sao. Tức là diễn tả lại nếu làm tay từng bước ra sao.

myguitar · 11/2/15

VetMini đã viết:
Ở đấy mà khóc lóc.
Muốn được trả lời nhanh chóng thì diễn đạt cho rõ ràng, ví dụ chi tiết.
Cho ví dụ thẳng thừng mò dần từng giá trị âm ra sao. Tức là diễn tả lại nếu làm tay từng bước ra sao.

Bác ơi, em đã nêu rõ các bước ở trong tệp Excel đính kèm rồi đó. Công thức tính và ví dụ cụ thể cũng ở trong tệp. Trong tệp em tính E10 bằng lệnh VBA, sau đó dùng Conditional Formating để tìm ra các giá trị trong chuỗi D thỏa mãn <0.

Ở chỗ nào có D<0 thì tính vòng lặp mới, với độ lớn của vòng lặp mới bằng 10 cộng với số các giá trị âm của chuỗi D.

Em đang tìm cách diễn giải và tính công thức toán chứ ứng dụng cho lĩnh vực nào thì em cũng chịu, vì đây là bài tập mà em được giao.

myguitar · 11/2/15

giola đã viết:
Các số âm đó có tính tiếp khi lặp không- tức là ví dụ giờ tăng lên E14 tức là phải lùi thêm 4 giá trị D, trong 4 giá trị này lại có 2 giá trị âm (<0) thì có mở rộng lên thành E16 (tức là 16 giá trị) không, cứ vậy tiếp tục mở rộng nếu có giá trị âm (<0) hay không???

-- vì là bài tập nên gợi ý bạn thử làm

+ xét số giá trị số âm, nếu lặp thì dùng lặp xét tiếp.
+ mở rộng vùng theo a và tính như bạn đã làm với sum_10
+ chú ý vùng tham chiếu của hàm phải đủ số giá trị, nếu không đủ thì hàm báo lỗi,

và bạn chưa nói bài tập gì, ứng dụng j

Cứ ở đâu có giá trị âm trong chuỗi D thì độ lớn của vòng lặp được tính tăng lên ạ. Các giá trị âm không cần phải liên tiếp. Ví dụ trong chuỗi D xuất hiện 4 giá trị âm thì lúc đó ở chuỗi Ej, thay vì tính Sum_10 thì ta phải tính Sum_14; Sum_13; Sum_12 và Sum_11.

Mấy công thức toán này xuất phát từ một concept trong ngành môi trường gì đó. Em chỉ biết là phải tìm cách để tính được nó bằng Excel và em nghĩ là có thể dùng VBA thôi ạ.

Trong tệp Excel em gửi thì em tính Sum_10 (tức chuỗi E) bằng VBA. Sau đó, dùng Conditional Formating để nhận ra các giá trị âm trong chuỗi D. Cứ ở chỗ nào có giá trị âm của chuỗi D thì em tăng độ lớn của vòng lặp và tính thủ công. Cái em đang muốn làm là tìm lệnh gì đó để nó tự nhận ra được giá trị âm ở chuỗi D và tự tăng độ lớn vòng lặp để rồi tính theo công thức như em đã đưa ra.

VetMini · 11/2/15

Để yên hàm Sum_10 của bạn.
Chép 2 hàm sau đây vào cùng module.
Trong cột D, đặt công thức =CalcSum10()
Trong cột E, đặt công thức =CaclSum10J()

Mã:

Public Function CalcSum10(Optional ByVal offsetR As Integer = -1, Optional ByVal offsetC As Integer = -1) As Double
[COLOR=#008000]' hàm tính sum 10 ô theo công thức quái đản, bố ai biết để làm cái gì
[/COLOR]CalcSum10 = Sum_10(Application.Caller.Offset(offsetR - 9, offsetC).Resize(10, 1)) [COLOR=#008000]' chọn 10 ô để sum (chú ý độ lệch)[/COLOR]
End Function

Public Function CalcSum10J(Optional ByVal offsetR As Integer = 0, Optional ByVal offsetC As Integer = -2) As Double
[COLOR=#008000]' hàm tính sum 10 ô, theo công thức quái đản, [COLOR=#008000]nới rộng theo số phần tử âm, [/COLOR]bố ai biết để làm cái gì
[/COLOR]Dim Rg As Range
Dim I As Long, num As Integer
Set Rg = Application.Caller.Offset(offsetR - 9, offsetC).Resize(10, 1) [COLOR=#008000]' chọn 10 ô để đếm số phần tử âm[/COLOR]
For I = Rg.Rows.Count To 1 Step -1
    If Rg.Cells(I, 1).Value < 0 Then
        num = num + 1
    Else
        Exit For
    End If
Next I
CalcSum10J = Sum_10(Rg.Offset(-num, 0).Resize(Rg.Rows.Count + num, 1)) [COLOR=#008000]' chọn 10 ô, cộng thêm số phần tử âm[/COLOR]
Set Rg = Nothing
End Function

myguitar · 11/2/15

VetMini đã viết:

Để yên hàm Sum_10 của bạn.
Chép 2 hàm sau đây vào cùng module.
Trong cột D, đặt công thức =CalcSum10()
Trong cột E, đặt công thức =CaclSum10J()

Mã:

Public Function CalcSum10(Optional ByVal offsetR As Integer = -1, Optional ByVal offsetC As Integer = -1) As Double
[COLOR=#008000]' hàm tính sum 10 ô theo công thức quái đản, bố ai biết để làm cái gì
[/COLOR]CalcSum10 = Sum_10(Application.Caller.Offset(offsetR - 9, offsetC).Resize(10, 1)) [COLOR=#008000]' chọn 10 ô để sum (chú ý độ lệch)[/COLOR]
End Function

Public Function CalcSum10J(Optional ByVal offsetR As Integer = 0, Optional ByVal offsetC As Integer = -2) As Double
[COLOR=#008000]' hàm tính sum 10 ô, theo công thức quái đản, [COLOR=#008000]nới rộng theo số phần tử âm, [/COLOR]bố ai biết để làm cái gì
[/COLOR]Dim Rg As Range
Dim I As Long, num As Integer
Set Rg = Application.Caller.Offset(offsetR - 9, offsetC).Resize(10, 1) [COLOR=#008000]' chọn 10 ô để đếm số phần tử âm[/COLOR]
For I = Rg.Rows.Count To 1 Step -1
    If Rg.Cells(I, 1).Value < 0 Then
        num = num + 1
    Else
        Exit For
    End If
Next I
CalcSum10J = Sum_10(Rg.Offset(-num, 0).Resize(Rg.Rows.Count + num, 1)) [COLOR=#008000]' chọn 10 ô, cộng thêm số phần tử âm[/COLOR]
Set Rg = Nothing
End Function

Em cảm ơn bác nha. Em thử dùng lệnh bác viết nhưng sao nó không ra nhỉ. Bác thử chạy trên file Excel em gửi chưa ạ?
Em thấy công thức tính E10 cũng không quái đản lắm, chỉ là tính tổng của các trung bình cộng của 10 số, tính ngược từ số thứ 10 đến số thứ 1. Như trong file Excel em gửi thì E10 được tính bằng: (Trung bình cộng của D10) + (trung bình cộng của D10 và D9) + (trung bình cộng của D10, D9, D8) + ...+ (trung bình cộng của D10, D9,..., D1)
(Ở đây em diễn giải ký hiệu D theo số thứ tự, chứ ko viết theo hàng trong bảng tính Excel cho giống ví dụ em đưa ra)

Tính Ej thì là tính tổng các trung bình cộng của j số, tính ngược từ số thứ j đến số thứ 1. Chỉ có điều biến j chạy theo số các giá trị âm của chuỗi ban đầu nên nó mới phức tạp ạ.

hungpecc1 · 11/2/15

giả sử có dãy giá trị từ D1 đến D11 như sau :
1,2,3,4,5,6,7,8,9,10,-11
Thì E10 bằng bao nhiểu ? và Ej bằng bao nhiêu ?
:drive1:

SA_DQ · 11/2/15

Mình chưa rõ là D10 của bạn bằng bao nhiêu vậy? & D9 nữa;

Bạn cứ D9, D10 hoài, nhưng cứ vậy thì giống đánh đố người khác mà thôi?

./.

myguitar · 11/2/15

hungpecc1 đã viết:
giả sử có dãy giá trị từ D1 đến D11 như sau :
1,2,3,4,5,6,7,8,9,10,-11
Thì E10 bằng bao nhiểu ? và Ej bằng bao nhiêu ?

Có hai giá trị E10 được tính: E10 tính trong khoảng D10 đến D1 và E10 tính trong khoảng D11 đến D2. Cụ thể:
E10 (từ D10 đến D1) = (10)/1 + (10+9)/2 + (10+9+8)/3 + (10+9+8+7)/4 + ...+ (10+9+8+7+...+1)/10 = 77,5
E10 (từ D11 đến D2) = (-11)/1 + (-11+10)/2 + (-11+10+9)/3 + ... + (-11+10+9+...+2)/10 = 23,06

Có 1 số âm => j=10+1= 11
E11 = (-11)/1 + (-11+10)/2 + (-11+10+9)/3 + ... + (-11+10+9+...+1)/11 = 27,06.

Bác xem thêm phần tệp đính kèm em tính theo cách trung bình cộng và tính theo lệnh VBA Sum_10 nhé.

myguitar · 11/2/15

Em quên đính kèm tệp, hì

hungpecc1 · 11/2/15

myguitar đã viết:
Em quên đính kèm tệp, hì

trong file đính kèm bạn đã viết được sum_10 ra đúng kết quả như bạn mong muốn rồi, bh bạn bạn hỏi vấn đề gì nữa ?

Ba Tê · 11/2/15

myguitar đã viết:
Có hai giá trị E10 được tính: E10 tính trong khoảng D10 đến D1 và E10 tính trong khoảng D11 đến D2. Cụ thể:
E10 (từ D10 đến D1) = (10)/1 + (10+9)/2 + (10+9+8)/3 + (10+9+8+7)/4 + ...+ (10+9+8+7+...+1)/10 = 77,5
E10 (từ D11 đến D2) = (-11)/1 + (-11+10)/2 + (-11+10+9)/3 + ... + (-11+10+9+...+2)/10 = 23,06

Có 1 số âm => j=10+1= 11
E11 = (-11)/1 + (-11+10)/2 + (-11+10+9)/3 + ... + (-11+10+9+...+1)/11 = 27,06.

Bác xem thêm phần tệp đính kèm em tính theo cách trung bình cộng và tính theo lệnh VBA Sum_10 nhé.

Xem thử cái mớ bòng bong này coi sao, nhức đầu thiệt đó.
Có lẽ nó chẳng gọn gàng tí nào.

giola · 11/2/15

myguitar đã viết:
Cứ ở đâu có giá trị âm trong chuỗi D thì độ lớn của vòng lặp được tính tăng lên ạ. Các giá trị âm không cần phải liên tiếp. Ví dụ trong chuỗi D xuất hiện 4 giá trị âm thì lúc đó ở chuỗi Ej, thay vì tính Sum_10 thì ta phải tính Sum_14; Sum_13; Sum_12 và Sum_11.

Mấy công thức toán này xuất phát từ một concept trong ngành môi trường gì đó. Em chỉ biết là phải tìm cách để tính được nó bằng Excel và em nghĩ là có thể dùng VBA thôi ạ.

Trong tệp Excel em gửi thì em tính Sum_10 (tức chuỗi E) bằng VBA. Sau đó, dùng Conditional Formating để nhận ra các giá trị âm trong chuỗi D. Cứ ở chỗ nào có giá trị âm của chuỗi D thì em tăng độ lớn của vòng lặp và tính thủ công. Cái em đang muốn làm là tìm lệnh gì đó để nó tự nhận ra được giá trị âm ở chuỗi D và tự tăng độ lớn vòng lặp để rồi tính theo công thức như em đã đưa ra.

nói loằng ngoằng, nhưng bạn chưa trả lời các câu hỏi này

giola đã viết:
Các số âm đó có tính tiếp khi lặp không- tức là ví dụ giờ tăng lên E14 tức là phải lùi thêm 4 giá trị D, trong 4 giá trị này lại có 2 giá trị âm (<0) thì có mở rộng lên thành E16 (tức là 16 giá trị) không, cứ vậy tiếp tục mở rộng nếu có giá trị âm (<0) hay không???

VetMini · 12/2/15

myguitar đã viết:
Em cảm ơn bác nha. Em thử dùng lệnh bác viết nhưng sao nó không ra nhỉ. Bác thử chạy trên file Excel em gửi chưa ạ?
Em thấy công thức tính E10 cũng không quái đản lắm, chỉ là tính tổng của các trung bình cộng của 10 số, tính ngược từ số thứ 10 đến số thứ 1. Như trong file Excel em gửi thì E10 được tính bằng: (Trung bình cộng của D10) + (trung bình cộng của D10 và D9) + (trung bình cộng của D10, D9, D8) + ...+ (trung bình cộng của D10, D9,..., D1)
(Ở đây em diễn giải ký hiệu D theo số thứ tự, chứ ko viết theo hàng trong bảng tính Excel cho giống ví dụ em đưa ra)

Tính Ej thì là tính tổng các trung bình cộng của j số, tính ngược từ số thứ j đến số thứ 1. Chỉ có điều biến j chạy theo số các giá trị âm của chuỗi ban đầu nên nó mới phức tạp ạ.

Đương nhiên là thử rồi. Kết quả ra in hệt như ví dụ của bạn.
Đọc cho kỹ lại sẽ thấy rằng tôi có yêu cầu bạn cho ví dụ rõ ràng, nếu không ra đúng là do lỗi của bạn.

Trong bài #1, bạn có cho biết là hàm Sum_10 của mình đã tính đúng rồi, chỉ cần khai triển cho các trường hợp Ej's. Hàm CalcSum10J làm đúng công việc đó: tức là nó tìm đoạn sum và đưa vào Sum_10 để tính. Hàm CalcSum10 thực ra không cần thiết, nó chỉ giúp bạn tổng quát hoá giải pháp của mình thôi.

Cách thử:
1. copy sheet1 snag sheet mới: sheet1(2)
2. tại ô C13, ghi công thức =CalcSum10(); kéo xuống
3. tại ô D19, ghi công thức =CalcSum10J(); kéo xuống
So kết quả ở sheet1, sẽ thấy in hệt.
Nhắc lại: nếu không đúng là do lỗi bạn cho vid dụ không đúng.

SA_DQ · 12/2/15

[Thongbao]Đúng là đơn giản mà trình bày chưa rõ thôi!.[/Thongbao]

Mình dùng file bài đầu của bạn đó nhe:

Bạn chép hàm người dùng này tiếp vô hàm của bạn:

PHP:

Function Sum10Plus(Rng As Range)
 Dim J As Byte, Dem As Byte, W As Byte, Chuc As Byte
 Dem = Rng.Cells.Count
 For J = 0 To Dem - 1
    If Rng(Dem - J).Value < 0 Then
        W = 1 + W
    Else
        Chuc = 1 + Chuc
    End If
    If Chuc = 10 Then
        Set Rng = Range(Rng(Dem - 9 - W), Rng(Dem))
        Exit For
    End If
 Next J
 Sum10Plus = Sum_10(Rng)
End Function

Hàm này sẽ giúp chúng ta xác định số ô cần thiết theo iêu cầu để đưa vô hàm của bạn

Cách sử dụng:

Tại ô [F19] bạn nhập cú fáp =Sum10Plus(B$4:B19)

Sau khi {ENTER} bạn sẽ nhận được trị 0.196891053 thay vì 1.20 của bạn tại [D19]

/(/hững mong là không cần đưa file lên!

myguitar · 13/2/15

Ba Tê đã viết:
Xem thử cái mớ bòng bong này coi sao, nhức đầu thiệt đó.
Có lẽ nó chẳng gọn gàng tí nào.

Bác Ba Tê giỏi quá }}}}}

. Em đợi bác mãi. Đúng là bài này phức tạp thật. Bác cho em cắp sách học VBA theo bác với!!!

myguitar · 13/2/15

SA_DQ đã viết:
[Thongbao]Đúng là đơn giản mà trình bày chưa rõ thôi!.[/Thongbao]

Mình dùng file bài đầu của bạn đó nhe:

Bạn chép hàm người dùng này tiếp vô hàm của bạn:

PHP:

Function Sum10Plus(Rng As Range) Dim J As Byte, Dem As Byte, W As Byte, Chuc As Byte Dem = Rng.Cells.Count For J = 0 To Dem - 1 If Rng(Dem - J).Value < 0 Then W = 1 + W Else Chuc = 1 + Chuc End If If Chuc = 10 Then Set Rng = Range(Rng(Dem - 9 - W), Rng(Dem)) Exit For End If Next J Sum10Plus = Sum_10(Rng) End Function

Hàm này sẽ giúp chúng ta xác định số ô cần thiết theo iêu cầu để đưa vô hàm của bạn

Cách sử dụng:

Tại ô [F19] bạn nhập cú fáp =Sum10Plus(B$4:B19)

Sau khi {ENTER} bạn sẽ nhận được trị 0.196891053 thay vì 1.20 của bạn tại [D19]

/(/hững mong là không cần đưa file lên!

Em cảm ơn bác! Em đã thử làm theo cách của bác thì ô [D19] đến [D22] ra đúng kết quả nhưng kéo lệnh này xuống dưới thì nó tính cả những giá trị khi mà cột B2 ko chứa giá trị âm. Còn giá trị 1.20 trong tệp của em là do em để định dạng số với 2 dấu thập phân sau dấu phẩy ạ. Nếu ko ngại thì em phiền bác gửi cho em tệp Excel. Em cảm ơn bác nhé.

Viết lệnh VBA cho chuỗi có vòng lặp biến đổi dựa trên giá trị của chuỗi ban đầu (4 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

Thành viên gắn bó

Thành viên mới

theo chiều Gió

Ăn cùng góc phố

Thành viên mới

Thành viên mới

Ăn cùng góc phố

Thành viên mới

Thành viên gắn bó

/(hông là gì!

Thành viên mới

File đính kèm

Thành viên mới

Thành viên gắn bó

Cạo Rồi Khỏi Gội

File đính kèm

theo chiều Gió

Ăn cùng góc phố

/(hông là gì!

Thành viên mới

Thành viên mới

Đếm ngược thời gian