Xin giúp đỡ tính tổng theo điều kiện thay đổi

[tranquangdiep]

Em chào các bác, em có vấn đề này mong được các bác giúp đỡ ạ.
Em có các giá trị theo tháng của các năm từ 1980-2019, mỗi tháng giá trị sẽ là âm hoặc dương. Em chỉ quan tâm các giá trị âm và lọc ra được (Cột D trong file đính kèm). Sau đó em đã đếm được số lần xảy ra mà không trùng nhau: 1 tháng âm, 2 tháng âm liên tiếp, 3 tháng âm liên tiếp .... 11 tháng âm liên tiếp trong toàn bộ chuỗi số liệu. (Nếu số lần xảy ra liên tiếp 11 tháng là 1 lần, thì số lần xảy ra liên tiếp 10,9,8,7,6,5,4,3,2 và 1 sẽ k dc tính nữa, chỗ này em xin lỗi cũng không biết giải thích sao cho rõ, các bác xem file thì nó là 3 cột H I và J chắc hiểu dc ạ)

Vấn đề là giờ e muốn tính tổng các giá trị tại cột D theo số lần âm liên tiếp.
Em ví dụ với tính tổng các giá trị mà xảy ra 3 tháng liên tiếp như hình vẽ ạ
Trong chuỗi số liệu có 16 lần xảy ra 3 tháng âm liên tiếp; mong muốn tính các giá trị em khoang tương ứng tại cột D với 16 lần cho 3 tháng âm liên tiếp.
Mong các bác thứ lỗi nếu e trình bày chưa rõ ràng ạ.
Em cảm ơn các bác.

 

[Phuocam]

Em chào các bác, em có vấn đề này mong được các bác giúp đỡ ạ.
Em có các giá trị theo tháng của các năm từ 1980-2019, mỗi tháng giá trị sẽ là âm hoặc dương. Em chỉ quan tâm các giá trị âm và lọc ra được (Cột D trong file đính kèm). Sau đó em đã đếm được số lần xảy ra mà không trùng nhau: 1 tháng âm, 2 tháng âm liên tiếp, 3 tháng âm liên tiếp .... 11 tháng âm liên tiếp trong toàn bộ chuỗi số liệu. (Nếu số lần xảy ra liên tiếp 11 tháng là 1 lần, thì số lần xảy ra liên tiếp 10,9,8,7,6,5,4,3,2 và 1 sẽ k dc tính nữa, chỗ này em xin lỗi cũng không biết giải thích sao cho rõ, các bác xem file thì nó là 3 cột H I và J chắc hiểu dc ạ)

Vấn đề là giờ e muốn tính tổng các giá trị tại cột D theo số lần âm liên tiếp.
Em ví dụ với tính tổng các giá trị mà xảy ra 3 tháng liên tiếp như hình vẽ ạ
Trong chuỗi số liệu có 16 lần xảy ra 3 tháng âm liên tiếp; mong muốn tính các giá trị em khoang tương ứng tại cột D với 16 lần cho 3 tháng âm liên tiếp.
Mong các bác thứ lỗi nếu e trình bày chưa rõ ràng ạ.
Em cảm ơn các bác.

Bạn thử công thức này trong G2 và copy xuống:

=SUM(--(FREQUENCY(IF($C$2:$C$481<0,ROW($C$2:$C$481)),IF($C$2:$C$481>=0,ROW($C$2:$C$481)))=H2))

Đây là công thức mảng, nhớ kết thúc bằng Ctrl+Shift+Enter.

.

 

[tranquangdiep]

Bạn thử công thức này trong G2 và copy xuống:

=SUM(--(FREQUENCY(IF($C$2:$C$481<0,ROW($C$2:$C$481)),IF($C$2:$C$481>=0,ROW($C$2:$C$481)))=H2))

Đây là công thức mảng, nhớ kết thúc bằng Ctrl+Shift+Enter.

.

Em cảm ơn bác, em đã copy công thức của bác và làm theo đúng hướng dẫn, kết quả ra đúng như cột J của em ạ. Nhưng em cần là tính tổng như e có viết trên bài post cơ ạ.

 

[HieuCD]

Em chào các bác, em có vấn đề này mong được các bác giúp đỡ ạ.
Em có các giá trị theo tháng của các năm từ 1980-2019, mỗi tháng giá trị sẽ là âm hoặc dương. Em chỉ quan tâm các giá trị âm và lọc ra được (Cột D trong file đính kèm). Sau đó em đã đếm được số lần xảy ra mà không trùng nhau: 1 tháng âm, 2 tháng âm liên tiếp, 3 tháng âm liên tiếp .... 11 tháng âm liên tiếp trong toàn bộ chuỗi số liệu. (Nếu số lần xảy ra liên tiếp 11 tháng là 1 lần, thì số lần xảy ra liên tiếp 10,9,8,7,6,5,4,3,2 và 1 sẽ k dc tính nữa, chỗ này em xin lỗi cũng không biết giải thích sao cho rõ, các bác xem file thì nó là 3 cột H I và J chắc hiểu dc ạ)

Vấn đề là giờ e muốn tính tổng các giá trị tại cột D theo số lần âm liên tiếp.
Em ví dụ với tính tổng các giá trị mà xảy ra 3 tháng liên tiếp như hình vẽ ạ
Trong chuỗi số liệu có 16 lần xảy ra 3 tháng âm liên tiếp; mong muốn tính các giá trị em khoang tương ứng tại cột D với 16 lần cho 3 tháng âm liên tiếp.
Mong các bác thứ lỗi nếu e trình bày chưa rõ ràng ạ.
Em cảm ơn các bác.

Cột phụ

F2 =IF(C2>=0,"",IF(F1<>"",F1,MATCH(TRUE,C2:$C$500>=0,0)-1))

Công thức mảng nhấn Ctrl+Shift+Enter
Copy xuống

K2 =SUMIF($F$2:$F$500,H2,$C$2:$C$500)

Copy xuống

 

[tranquangdiep]

Cột phụ

F2 =IF(C2>=0,"",IF(F1<>"",F1,MATCH(TRUE,C2:$C$500>=0,0)-1))

Công thức mảng nhấn Ctrl+Shift+Enter
Copy xuống

K2 =SUMIF($F$2:$F$500,H2,$C$2:$C$500)

Copy xuống

Em cảm ơn bác, em đã làm được theo sự giúp đỡ của bác

 

[Phuocam]

Cột phụ

Anh ơi, em ứ chịu cột phụ đâu. Em muốn làm trực tiếp luôn ...

Hahaha ...

.

 

[HieuCD]

Anh ơi, em ứ chịu cột phụ đâu. Em muốn làm trực tiếp luôn ...

Hahaha ...

.

Làm trực tiếp quá đã nhưng quá khó và mệt lắm, tuổi già sức yếu không kham nổi, nhường phần sướng cho bạn. Khà Khà Khà

 

[thanhwru99]

mình xin chào cả nhà, mình có câu hỏi xin nhờ các ace xem giúp, giả sử mình có file dữ liệu như đính kèm, giờ mình cần đếm số lần xuất hiện giá trị nhỏ hơn hoặc bằng -1 từ ít nhất liên tiếp 2 lần trở lên và tính tổng giá trị của các lần đếm này, biết tổng được tính cho từng lần đếm này sẽ được tính từ khi bắt đầu nhỏ hơn hoặc bằng -1 đến khi xuất hiện giá trị lớn hơn hoặc 0, như trong hình sau thì từ -1.2 tới -0.2 sẽ đếm là 1 lần, và tính tổng cho lần đếm này là -1.2+-1.1+-1.3+-.02. Mình xin cảm ơn các ace!

 

[Phan Thế Hiệp]

mình xin chào cả nhà, mình có câu hỏi xin nhờ các ace xem giúp, giả sử mình có file dữ liệu như đính kèm, giờ mình cần đếm số lần xuất hiện giá trị nhỏ hơn hoặc bằng -1 từ ít nhất liên tiếp 2 lần trở lên và tính tổng giá trị của các lần đếm này, biết tổng được tính cho từng lần đếm này sẽ được tính từ khi bắt đầu nhỏ hơn hoặc bằng -1 đến khi xuất hiện giá trị lớn hơn hoặc 0, như trong hình sau thì từ -1.2 tới -0.2 sẽ đếm là 1 lần, và tính tổng cho lần đếm này là -1.2+-1.1+-1.3+-.02. Mình xin cảm ơn các ace!

View attachment 299288

Bạn nên rút kinh nghiệm, khi lên diễn đàn GPE nên viết trọn chữ, không nên viết tắt như 'ace'.

1/ Đếm số lần xuất hiện giá trị <0 và liền tiếp >=2: công thức ô A3:

=IFERROR(INDEX(TEXT(FREQUENCY(IF($A$2:$KN$2<0,$A$1:$KN$1,""),IF($A$2:$KN$2>=0,$A$1:$KN$1,)),"[>1]0;"),COLUMN(B:B)),"")

Kết thúc bằng Ctrl +Shift +Enter, fill ngang qua các cột còn lại.

2/ Tính tổng theo từng đoạn giá trị theo yêu cầu (1/): công thức A4:

=IF(A3<>"",SUM(OFFSET(A2,,,,-A3)),"")

Kết thúc bằng Enter, fill ngang qua các cột còn lại.

Thân

 

[thanhwru99]

Bạn nên rút kinh nghiệm, khi lên diễn đàn GPE nên viết trọn chữ, không nên viết tắt như 'ace'.

Cảm ơn bạn @Phan Thế Hiệp đã lưu ý cho mình, mình xin rút kinh nghiệm về điều này.

1/ Đếm số lần xuất hiện giá trị <0 và liền tiếp >=2: công thức ô A3:

=IFERROR(INDEX(TEXT(FREQUENCY(IF($A$2:$KN$2<0,$A$1:$KN$1,""),IF($A$2:$KN$2>=0,$A$1:$KN$1,)),"[>1]0;"),COLUMN(B:B)),"")

Kết thúc bằng Ctrl +Shift +Enter, fill ngang qua các cột còn lại.


2/ Tính tổng theo từng đoạn giá trị theo yêu cầu (1/): công thức A4:

=IF(A3<>"",SUM(OFFSET(A2,,,,-A3)),"")

Kết thúc bằng Enter, fill ngang qua các cột còn lại.

Thân

Cảm ơn những chỉ dẫn của bạn, tuy nhiên ý mình là tính tổng số lần trong cả chuỗi đó, sao cho điểm bắt đầu đếm khi giá trị nhỏ hơn hoặc bằng -1 từ ít nhất 2 giá trị cạnh nhau liên tiếp trở lên và kết thúc khi có giá trị lớn hơn hoặc bằng 0 xuất hiện (p/s, mình xin lỗi vì có thể trong phần tin nhắn trước mình ghi là "2 lần trở lên" có thể làm mọi người hiểu nhầm ý). Ví dụ nếu có chuỗi như 1 đoạn trong file thì từ -1.2 tới -0.2 sẽ đếm là có 1 lần xuất hiện và tính tổng các giá trị (-1.2+-1.1+-1.3+-.2), và dãy số ở đây có tổng số lần xuất hiện là chỉ 1 lần. 2 số cuối -1.1 và -0.9 không được đếm, vì số liên tiếp sau -1.1 là -0.9 lớn hơn -1. Tuy nhiên, nếu thay -0.9 bằng -1.0 hoặc 1 số nhỏ hơn -1 thì sẽ đếm lần thứ 2. Trong file nếu mình tính thủ công sẽ có tổng là 8 sự kiện theo yêu cầu.

-1.2​

-1.1​

-1.3​

-0.2​

1.6​

2.0​

1.1​

-0.2​

0.3​

0.1​

-0.8​

-0.2​

-1.1​

-0.9​

Xin cảm ơn bạn @Phan Thế Hiệp và các anh chị em trong diễn đàn.

 

[Phan Thế Hiệp]

điểm bắt đầu đếm khi giá trị nhỏ hơn hoặc bằng -1 từ ít nhất 2 giá trị cạnh nhau liên tiếp trở lên và kết thúc khi có giá trị lớn hơn hoặc bằng 0 xuất hiện

Vậy, chỉnh công thức như sau:

1/ Đếm số lần xuất hiện giá trị <=-1 và liền tiếp từ 2 lần giá trị <=-1 trở lên: công thức ô A3:

=IF(IFERROR(OFFSET(A2,,-1)>-1,1)*(A2<=-1)*(B2<=-1)*IFERROR(1-(MAX(OFFSET($A$2,,LOOKUP(10^6,$A1:OFFSET(A1,,-1)/(--$A3:OFFSET(A3,,-1)>0))-1):OFFSET(A2,,-1))<0),1),(MATCH(TRUE,INDEX(A2:$KO2>=0,),)-1),"")

Enter, fill qua phải.
Kết quả n tại dòng 3 biểu thị có bao nhiêu số âm trong đoạn đó (tính từ cột ra kết quả đến n-1 cột kế tiếp) thỏa điều kiện, Vd: AH3= 2, tức AH2 và AI2 có giá trị thỏa điều kiện.

2/ Tính tổng theo từng đoạn giá trị theo yêu cầu (1/): công thức A4:

=IF(A3<>"",SUM(OFFSET(A2,,,,A3)),"")

Enter, fill qua phải.

Thân

 

[thanhwru99]

Cảm ơn bạn @Phan Thế Hiệp rất nhiều!

 

[thanhwru99]

Mình đã kiểm tra thêm 1 số trường hợp, thì thấy xuất hiện 1 số trường hợp không đúng (như ví dụ sau), bạn @Phan Thế Hiệp và các anh chị em xem giúp mình được không, theo mình hiểu có lẽ lỗi do trong công thức cứ gặp giá trị nhỏ hơn hoặc bằng -1 thì bắt đầu kiểm tra luôn thay vì để hết cả 1 sự kiện thì mới kiểm tra. Nếu theo ví dụ dãy sau thì chỉ có 1 sự kiện xuất hiện thay vì 2 sự kiện nếu mình áp dụng công thức trên. Xin cảm ơn bạn @Phan Thế Hiệp và các anh chị em trong diễn đàn.

-0.8​

-0.5​

-0.7​

-1.1​

-1.1​

-1.1​

-1.1​

-0.6​

-0.4​

-0.9​

-0.1​

-0.1​

-0.1​

-0.4​

-0.5​

-0.2​

-0.9​

-1.4​

-1.8​

0.6​

0.7​

16​

2​

 

[Phan Thế Hiệp]

Mình đã kiểm tra thêm 1 số trường hợp, thì thấy xuất hiện 1 số trường hợp không đúng (như ví dụ sau), bạn @Phan Thế Hiệp và các anh chị em xem giúp mình được không, theo mình hiểu có lẽ lỗi do trong công thức cứ gặp giá trị nhỏ hơn hoặc bằng -1 thì bắt đầu kiểm tra luôn thay vì để hết cả 1 sự kiện thì mới kiểm tra. Nếu theo ví dụ dãy sau thì chỉ có 1 sự kiện xuất hiện thay vì 2 sự kiện nếu mình áp dụng công thức trên. Xin cảm ơn bạn @Phan Thế Hiệp và các anh chị em trong diễn đàn.

-0.8​

-0.5​

-0.7​

-1.1​

-1.1​

-1.1​

-1.1​

-0.6​

-0.4​

-0.9​

-0.1​

-0.1​

-0.1​

-0.4​

-0.5​

-0.2​

-0.9​

-1.4​

-1.8​

0.6​

0.7​

16​

2​

À! Theo tôi thấy là bạn chưa cập nhật lại công thức tôi đã chỉnh sau cùng tại bài #11 (22:48 hôm qua), vì tôi cũng thấy lỗi đó rồi.

Vậy, bạn cập nhật lại công thức dưới đây nó gọn gàng hơn chút: ô A3=

=IF(IFERROR(OFFSET(A2,,-1)>-1,1)*(A2<=-1)*(B2<=-1)*IFERROR(1-(MAX(OFFSET($A2,,MATCH(10^6,$A3:OFFSET(A3,,-1))):OFFSET(A2,,-1))<0),1),(MATCH(TRUE,INDEX(B2:$KO2>=0,),)),"")

Bạn thử lại các dữ liệu theo file đính kèm dưới đây.

Thân

 

[thanhwru99]

Cảm ơn bạn @Phan Thế Hiệp rất nhiều! Mình đã kiểm tra và đã giải quyết được bài toán theo chỉ dẫn của bạn.