Tính tổng các giá trị liên tiếp thỏa mãn điều kiện cho trước (1 người xem)

[myguitar]

Nhờ các bác giúp em bài này với ạ: Em đang phải tính tổng của các giá trị liên tiếp cùng thỏa mãn 1 điều kiên cho trước. Ví dụ:
Chuỗi 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8
Tính tổng các số liên tiếp thỏa mãn "<=1" thì các số được tính tổng là (các số màu đỏ) 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8; tương tự, đếm số các số có màu đỏ (các số liên tiếp thỏa mãn <= 1)

Em xin phép gửi kèm tệp ví dụ và kết quả cần có mà em tính thủ công. Các bác có biết công thức nào để tính bài toán này không ạ? Bộ số liệu của em rất dài nên không tính thủ công được

Em cảm ơn cả nhà!

 

[lacbuidoi]

Bạn xem bài: http://www.giaiphapexcel.com/forum/showthread.php?54293-Tô-màu-trong-Excel-có-điều-kiệnt để biết cách tô màu có điều kiện sau đó sort dữ liệu và thử áp dụng cách này xem.
(Hy vọng trúng)
Nguồn: http://www.exceltrick.com

 

[nhapmon]

Nhờ các bác giúp em bài này với ạ: Em đang phải tính tổng của các giá trị liên tiếp cùng thỏa mãn 1 điều kiên cho trước. Ví dụ:
Chuỗi 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8
Tính tổng các số liên tiếp thỏa mãn "<=1" thì các số được tính tổng là (các số màu đỏ) 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8; tương tự, đếm số các số có màu đỏ (các số liên tiếp thỏa mãn <= 1)

Em xin phép gửi kèm tệp ví dụ và kết quả cần có mà em tính thủ công. Các bác có biết công thức nào để tính bài toán này không ạ? Bộ số liệu của em rất dài nên không tính thủ công được

Em cảm ơn cả nhà!

C6=IF(AND(B5>1,B6<1),SUMIF(B6:$B$25,"<=1",B6:$B$25)-SUM(C7:C$25),"")
D6=IF(AND(B5>1,B6<1),COUNTIF(B6:$B$25,"<=1")-SUM(D7:$D$25),"")

kéo ----> dòng 25

 

[concogia]

Nhờ các bác giúp em bài này với ạ: Em đang phải tính tổng của các giá trị liên tiếp cùng thỏa mãn 1 điều kiên cho trước. Ví dụ:
Chuỗi 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8
Tính tổng các số liên tiếp thỏa mãn "<=1" thì các số được tính tổng là (các số màu đỏ) 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8; tương tự, đếm số các số có màu đỏ (các số liên tiếp thỏa mãn <= 1)

Em xin phép gửi kèm tệp ví dụ và kết quả cần có mà em tính thủ công. Các bác có biết công thức nào để tính bài toán này không ạ? Bộ số liệu của em rất dài nên không tính thủ công được

Em cảm ơn cả nhà!

Một cách làm bằng công thức, nếu dữ liệu quá nhiều làm máy chạy như con rùa bò thì dùng VBA viết một Sub xử nó
Thân

 

[myguitar]

Em cảm ơn bác lacbuidoi. Cách tô màu rồi tính thì cũng được ạ, nhưng không giải quyết triệt để. Bởi vì nếu có một giá trị riêng lẻ vẫn thỏa mãn điều kiện trong chuỗi thì vẫn bị tô màu. Ví dụ chuỗi 1 2 1 1 2 3 5 1 5 1 1 0 5 thì hai số "1" màu xanh vẫn thỏa mãn <=1 nhưng không phải là liên tiếp (1 2 1 1 2 3 5 1 5 1 1 0 5)

Bác nào có lời giải thì giúp em với ạ.

 

[ec ec]

em cảm ơn bác lacbuidoi. Cách tô màu rồi tính thì cũng được ạ, nhưng không giải quyết triệt để. Bởi vì nếu có một giá trị riêng lẻ vẫn thỏa mãn điều kiện trong chuỗi thì vẫn bị tô màu. Ví dụ chuỗi 1 2 1 1 2 3 5 1 5 1 1 0 5 thì hai số "1" màu xanh vẫn thỏa mãn <=1 nhưng không phải là liên tiếp (1 2 1 1 2 3 5 1 5 1 1 0 5)

bác nào có lời giải thì giúp em với ạ.

c6=if((b5>1)*(b6<=1)*(b7<=1),sumproduct(offset(b6,,,match(true,b7:b$25>1,0))),"")

d6=if((b5>1)*(b6<=1)*(b7<=1),sumproduct(--(offset(b6,,,match(true,b7:b$25>1,0))<>"")),"")

 

[Phuocam]

Nhờ các bác giúp em bài này với ạ: Em đang phải tính tổng của các giá trị liên tiếp cùng thỏa mãn 1 điều kiên cho trước. Ví dụ:
Chuỗi 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8
Tính tổng các số liên tiếp thỏa mãn "<=1" thì các số được tính tổng là (các số màu đỏ) 1 2 3 2 1 4 1 1 1 4 1 1 1 1 5 2 3 1 1 7 1 8 1 9 9 1 0 0 5 1 5 6 8 8; tương tự, đếm số các số có màu đỏ (các số liên tiếp thỏa mãn <= 1)
Em xin phép gửi kèm tệp ví dụ và kết quả cần có mà em tính thủ công. Các bác có biết công thức nào để tính bài toán này không ạ? Bộ số liệu của em rất dài nên không tính thủ công được
Em cảm ơn cả nhà!

Công thức D6:
=IF(OR(B5<=1,B6>1,B7>1),"",MATCH(TRUE,B7:$B$25>1,0))
Kết thúc bằng Ctrl+Shift+Enter
C6 làm giống Bác Cò:
=IF(D6="","",SUMPRODUCT(OFFSET(B6,,,D6)))

 

[lacbuidoi]

Em cảm ơn bác lacbuidoi. Cách tô màu rồi tính thì cũng được ạ, nhưng không giải quyết triệt để. Bởi vì nếu có một giá trị riêng lẻ vẫn thỏa mãn điều kiện trong chuỗi thì vẫn bị tô màu. Ví dụ chuỗi 1 2 1 1 2 3 5 1 5 1 1 0 5 thì hai số "1" màu xanh vẫn thỏa mãn <=1 nhưng không phải là liên tiếp (1 2 1 1 2 3 5 1 5 1 1 0 5)

Bác nào có lời giải thì giúp em với ạ.

- THú thật mình vẫn chưa hiểu cách bác đặt vấn đề .... một góp ý của mình: bác chia vùng dữ liệu ra rồi đặt tên rồi sau đó .........

 

[myguitar]

Bác ơi, hình như em lowtech quá. Hàm của bác và bác Cò thì ok rồi. Chỉ có điều em thử nối dài chuỗi số liệu và kéo copy công thức xuống bên dưới chuỗi vừa cho thêm nhưng nó không chạy.
Chắc em phải ngồi cày hàm Sumproduct và Match.
Nếu bác rảnh, nhờ bác thử thêm độ lớn chuỗi số liệu lên tầm khoảng 50 số, rồi kéo copy công thức hộ e xem có được không nhé!

Em cảm ơn bác Phuocam và bác Cò rất nhiều. Nếu còn thêm vài nút thanks thì em bấm cho các bác nhiệt tình

 

[Let'GâuGâu]

Bác ơi, hình như em lowtech quá. Hàm của bác và bác Cò thì ok rồi. Chỉ có điều em thử nối dài chuỗi số liệu và kéo copy công thức xuống bên dưới chuỗi vừa cho thêm nhưng nó không chạy.
Chắc em phải ngồi cày hàm Sumproduct và Match.
Nếu bác rảnh, nhờ bác thử thêm độ lớn chuỗi số liệu lên tầm khoảng 50 số, rồi kéo copy công thức hộ e xem có được không nhé!

Em cảm ơn bác Phuocam và bác Cò rất nhiều. Nếu còn thêm vài nút thanks thì em bấm cho các bác nhiệt tình

bạn nên trích dẫn khi trả lời, vì có nhiều người trả lời cho bạn
==============
các cthuc cùa Phuocam ca concogia đều cố định vùng tham chiếu (hoặc cố định dòng cuối cùng)
vì vẫy bạn muốn khi kéo nhó chạy theo thì bạn phải tạo name động để vùng tham chiếu tự động giãn ra khi bạn nhập thêm dữ liệu
hoặc bạn nhắm xem vùng dữ liệu có bao nhiêu dòng thì để sẳn cho nó vd như B7:B100

==============
bạn cũng có thể tạo nhiều cái nick, mỗi cái bấm cám ơn một lần.............hihihihi

 

[myguitar]

Công thức D6:
=IF(OR(B5<=1,B6>1,B7>1),"",MATCH(TRUE,B7:$B$25>1,0))
Kết thúc bằng Ctrl+Shift+Enter
C6 làm giống Bác Cò:
=IF(D6="","",SUMPRODUCT(OFFSET(B6,,,D6)))

Bác ơi, hình như em lowtech quá. Hàm của bác và bác Cò thì ok rồi. Chỉ có điều em thử nối dài chuỗi số liệu và kéo copy công thức xuống bên dưới chuỗi vừa cho thêm nhưng nó không chạy.
Chắc em phải ngồi cày hàm Sumproduct và Match.
Nếu bác rảnh, nhờ bác thử thêm độ lớn chuỗi số liệu lên tầm khoảng 50 số, rồi kéo copy công thức hộ e và gửi lại file excel xem có được không bác nhé!

Em cảm ơn bác Phuocam và bác Cò rất nhiều. Nếu còn thêm vài nút thanks thì em bấm cho các bác nhiệt tình

 

[Ba Tê]

Bác ơi, hình như em lowtech quá. Hàm của bác và bác Cò thì ok rồi. Chỉ có điều em thử nối dài chuỗi số liệu và kéo copy công thức xuống bên dưới chuỗi vừa cho thêm nhưng nó không chạy.
Chắc em phải ngồi cày hàm Sumproduct và Match.
Nếu bác rảnh, nhờ bác thử thêm độ lớn chuỗi số liệu lên tầm khoảng 50 số, rồi kéo copy công thức hộ e và gửi lại file excel xem có được không bác nhé!

Em cảm ơn bác Phuocam và bác Cò rất nhiều. Nếu còn thêm vài nút thanks thì em bấm cho các bác nhiệt tình

Nếu đến cả ngàn dòng thì dùng VBA thử xem

 

[myguitar]

Nếu đến cả ngàn dòng thì dùng VBA thử xem

Em có đến hàng nghìn dòng dữ liệu luôn. Với bài này thì lệnh VBA thế nào hả bác? Em mở file của bác nhưng không nhìn được công thức, chỉ thấy mỗi kết quả thôi bác ạ.

 

[Ba Tê]

Em có đến hàng nghìn dòng dữ liệu luôn. Với bài này thì lệnh VBA thế nào hả bác? Em mở file của bác nhưng không nhìn được công thức, chỉ thấy mỗi kết quả thôi bác ạ.

Có công thức nào đâu mà nhìn. Mở file nhớ Enable macros, muốn có kết quả thì click vào hình con Jerry.
Muốn xem code thì ấn Alt+F11 sẽ thấy cái này:

Public Sub GPE()
Dim sArr(), dArr(), I As Long, K As Long, J As Long, Num As Double
sArr = Range([B6], [B6].End(xlDown)).Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 2)
For I = 1 To UBound(sArr, 1) - 1
    If sArr(I, 1) > 1 Then
        Num = 0: K = 0
    Else
        K = K + 1: Num = Num + sArr(I, 1)
        For J = I + 1 To UBound(sArr, 1)
            If sArr(J, 1) <= 1 Then
                K = K + 1
                Num = Num + sArr(J, 1)
            Else
                Exit For
            End If
        Next J
        If K > 1 Then
            dArr(I, 1) = Num
            dArr(I, 2) = K
            I = I + K
            Num = 0: K = 0
        End If
    End If
Next I
[C6].Resize(I - 1, 2) = dArr
End Sub

Hết!

 

[myguitar]

Có công thức nào đâu mà nhìn. Mở file nhớ Enable macros, muốn có kết quả thì click vào hình con Jerry.
Muốn xem code thì ấn Alt+F11 sẽ thấy cái này:

Public Sub GPE()
Dim sArr(), dArr(), I As Long, K As Long, J As Long, Num As Double
sArr = Range([B6], [B6].End(xlDown)).Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 2)
For I = 1 To UBound(sArr, 1) - 1
    If sArr(I, 1) > 1 Then
        Num = 0: K = 0
    Else
        K = K + 1: Num = Num + sArr(I, 1)
        For J = I + 1 To UBound(sArr, 1)
            If sArr(J, 1) <= 1 Then
                K = K + 1
                Num = Num + sArr(J, 1)
            Else
                Exit For
            End If
        Next J
        If K > 1 Then
            dArr(I, 1) = Num
            dArr(I, 2) = K
            I = I + K
            Num = 0: K = 0
        End If
    End If
Next I
[C6].Resize(I - 1, 2) = dArr
End Sub

Hết!

Bác giỏi quá! Em cảm ơn bác nhiều nhiều!