Xin giúp đỡ viết hàm Sum có điều kiện dấu âm, dương liên tục (5 người xem)

algorithm · 6/5/19

Xin chào anh/chị
em có thắc mắc sau mong anh chị giúp đỡ

em đang viết 1 hàm mà muốn kết quả ra giống như các trường hợp trong hình;

1/ âm âm âm = sum ( các số âm ) hoặc dương dương dương (sum các số dương)
2/ âm âm (zero) = kết quả = số 0
3/ dương dương âm âm = kết quả bằng sum các số 0
=> tóm tắt lại tức là nếu như ô cuối cùng là số 0 thì kết quả là số 0, nếu từ âm qua dương hoặc từ dương qua âm thì chỉ sum các ô cuối cùng có cùng dấu âm hoặc dương. Nếu cùng dương hoặc cùng âm thì sum lại toàn bộ ạ

em xin cám ơn anh chị đã đọc và giúp đỡ ạ

Phuocam · 6/5/19

Cái case giữa 4 và 5, cell I44 rỗng thì kết quả là rỗng?

befaint · 6/5/19

..

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If flag = False Then
            T = ia
            flag = True
        Else
            sCheck = Sgn(fa) * Sgn(ia)
            If sCheck = -1 Then
                T = ia
            ElseIf sCheck = 0 Then
                T = 0
                flag = False
            ElseIf sCheck = 1 Then
                T = T + ia
            End If
        End If
        fa = ia
    Next ia
    Tinh_Tong = T
End Function

Mã:

M40=Tinh_Tong(F40:I40)

algorithm · 6/5/19

phuocam đã viết:
Cái case giữa 4 và 5, cell I44 rỗng thì kết quả là rỗng?

dạ em xin lỗi. cái dòng đó em insert để tập làm bằng tay nhưng không được và quên xóa ạ, chỉ có 5 case đó thôi ạ

Bài đã được tự động gộp: 6/5/19

befaint đã viết:

..

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If flag = False Then
            T = ia
            flag = True
        Else
            sCheck = Sgn(fa) * Sgn(ia)
            If sCheck = -1 Then
                T = ia
            ElseIf sCheck = 0 Then
                T = 0
                flag = False
            ElseIf sCheck = 1 Then
                T = T + ia
            End If
        End If
        fa = ia
    Next ia
    Tinh_Tong = T
End Function

Mã:

M40=Tinh_Tong(F40:I40)

cám ơn anh nhìu nhìu lắm luôn, em cứ loay hoay if, sumif mấy hôm nay <3

dazkangel · 6/5/19

Thử, Ctrl+Shift+Enter:

Mã:

=SUM(IFERROR((F$39:I$39>=IFERROR(MATCH(,F40:I40,),))*SUBSTITUTE(SUBSTITUTE(F40:I40,"(",""),")",""),))

Phan Thế Hiệp · 6/5/19

algorithm đã viết:
Xin chào anh/chị
em có thắc mắc sau mong anh chị giúp đỡ
=> tóm tắt lại tức là nếu như ô cuối cùng là số 0 thì kết quả là số 0, nếu từ âm qua dương hoặc từ dương qua âm thì chỉ sum các ô cuối cùng có cùng dấu âm hoặc dương. Nếu cùng dương hoặc cùng âm thì sum lại toàn bộ ạ

Cách nữa:

Mã:

M40=IF(I40<>0,SUM(INDEX(F40:I40,,IFERROR(LOOKUP(2,1/IF(I40<0,F40:I40>=0,F40:I40<=0),{2,3,4,5}),)):I40),)

Kết thúc bằng Ctrl+Shift+Enter
Hoặc:

Mã:

M40=IF(I40<>0,SUM(INDEX(F40:I40,,IFERROR(LOOKUP(2,1/(SIGN(I40)*F40:I40<=0),{2,3,4,5}),)):I40),)

Chỉ Enter.

Thân

algorithm · 6/5/19

befaint đã viết:

..

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If flag = False Then
            T = ia
            flag = True
        Else
            sCheck = Sgn(fa) * Sgn(ia)
            If sCheck = -1 Then
                T = ia
            ElseIf sCheck = 0 Then
                T = 0
                flag = False
            ElseIf sCheck = 1 Then
                T = T + ia
            End If
        End If
        fa = ia
    Next ia
    Tinh_Tong = T
End Function

Mã:

M40=Tinh_Tong(F40:I40)

anh cho em hỏi thêm một tí nữa ạ
Em thử tính tổng từ cột F tới cột L ạ ( thì sẽ xảy ra trường hợp cột K;L chưa có giá trị điền vào, vì đó là giá trị trong tương lai )
Nên khi chọn tính tổng từ cột F tới cột L thì hàm sẽ hiểu giá trị cuối cùng là ô trống và trả về giá trị 0)
Cho em hỏi làm sao để có thể chọn vùng tính dữ liệu là từ cột F tới cột L nhưng không xét tới các ô bỏ trống ạ
em xin cám ơn

Bài đã được tự động gộp: 6/5/19

Phan Thế Hiệp đã viết:
Cách nữa:

Mã:

M40=IF(I40<>0,SUM(INDEX(F40:I40,,IFERROR(LOOKUP(2,1/IF(I40<0,F40:I40>=0,F40:I40<=0),{2,3,4,5}),)):I40),)

Kết thúc bằng Ctrl+Shift+Enter
Hoặc:

Mã:

M40=IF(I40<>0,SUM(INDEX(F40:I40,,IFERROR(LOOKUP(2,1/(SIGN(I40)*F40:I40<=0),{2,3,4,5}),)):I40),)

Chỉ Enter.

Thân

Bài đã được tự động gộp: 6/5/19

dazkangel đã viết:
Thử, Ctrl+Shift+Enter:

Mã:

=SUM(IFERROR((F$39:I$39>=IFERROR(MATCH(,F40:I40,),))*SUBSTITUTE(SUBSTITUTE(F40:I40,"(",""),")",""),))

Của anh Phan Thế Hiệp đúng rồi ạ, còn của anh dazkagel có vẻ chưa đúng ở case 5

các anh cho em hỏi nếu như em bổ sung tháng 5,6 nhưng chưa có số liệu sẵn thì sao ạ, nếu chọn vùng dữ liệu từ cột F tới cột K thì công thức sẽ hiểu là giá trị 0
em xin cám ơn

befaint · 6/5/19

befaint đã viết:
không xét tới các ô bỏ trống

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If Len(ia) > 0 Then
            If flag = False Then
                T = ia
                flag = True
            Else
                sCheck = Sgn(fa) * Sgn(ia)
                If sCheck = -1 Then
                    T = ia
                ElseIf sCheck = 0 Then
                    T = 0
                    flag = False
                ElseIf sCheck = 1 Then
                    T = T + ia
                End If
            End If
            fa = ia
        End If
    Next ia
    Tinh_Tong = T
End Function

HieuCD · 6/5/19

algorithm đã viết:
anh cho em hỏi thêm một tí nữa ạ
Em thử tính tổng từ cột F tới cột L ạ ( thì sẽ xảy ra trường hợp cột K;L chưa có giá trị điền vào, vì đó là giá trị trong tương lai )
Nên khi chọn tính tổng từ cột F tới cột L thì hàm sẽ hiểu giá trị cuối cùng là ô trống và trả về giá trị 0)
Cho em hỏi làm sao để có thể chọn vùng tính dữ liệu là từ cột F tới cột L nhưng không xét tới các ô bỏ trống ạ
em xin cám ơn

Bài đã được tự động gộp: 6/5/19

Bài đã được tự động gộp: 6/5/19

Của anh Phan Thế Hiệp đúng rồi ạ, còn của anh dazkagel có vẻ chưa đúng ở case 5
View attachment 216564
các anh cho em hỏi nếu như em bổ sung tháng 5,6 nhưng chưa có số liệu sẵn thì sao ạ, nếu chọn vùng dữ liệu từ cột F tới cột K thì công thức sẽ hiểu là giá trị 0
em xin cám ơn

Thử công thức

Mã:

=SUMPRODUCT(F40:I40*({1,2,3,4}>MIN(IFERROR(LOOKUP(2,1/((F40:I40=0)+((F40:I40*G40:J40)<0)=1)/(F40:I40<>""),{1,2,3,4}),0),3)))

algorithm · 6/5/19

befaint đã viết:

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If Len(ia) > 0 Then
            If flag = False Then
                T = ia
                flag = True
            Else
                sCheck = Sgn(fa) * Sgn(ia)
                If sCheck = -1 Then
                    T = ia
                ElseIf sCheck = 0 Then
                    T = 0
                    flag = False
                ElseIf sCheck = 1 Then
                    T = T + ia
                End If
            End If
            fa = ia
        End If
    Next ia
    Tinh_Tong = T
End Function

dạ chính xác rồi ạ, em cám ơn anh nhiều lắm luôn

VetMini · 6/5/19

befaint đã viết:
For Each ia In a

Đặt tên biến gì ghê vậy?

befaint · 6/5/19

VetMini đã viết:
Đặt tên biến gì ghê vậy?

Em đặt vậy cho "thân thiện"

algorithm · 6/5/19

HieuCD đã viết:
Thử công thức

Mã:

=SUMPRODUCT(F40:I40*({1,2,3,4}>MIN(IFERROR(LOOKUP(2,1/((F40:I40=0)+((F40:I40*G40:J40)<0)=1)/(F40:I40<>""),{1,2,3,4}),0),3)))

hàm này đúng rồi ạ, mà thú thật với anh là hàm này nâng cao quá, nếu em muốn áp dụng vô các trường hợp khác thì em không biết phải chỉnh sửa sao cho hợp lý, nhưng cám ơn anh vì đã giúp em biết thêm hàm sumproduct, em sẽ nghiên cứu thêm về hàm này ạ

VetMini · 6/5/19

befaint đã viết:
Em đặt vậy cho "thân thiện"

Như vầy càng thêm "thân thiện" cho môi trường:
Dim tap As Boolean, ao, ca, ia, Ta, sSet ' táp, ao, cá, ... , tả, sền sệt

Phan Thế Hiệp · 6/5/19

algorithm đã viết:
các anh cho em hỏi nếu như em bổ sung tháng 5,6 nhưng chưa có số liệu sẵn thì sao ạ, nếu chọn vùng dữ liệu từ cột F tới cột K thì công thức sẽ hiểu là giá trị 0
em xin cám ơn

Vậy chỉnh lại:

Mã:

=SUM(INDEX(F40:M40,,IFERROR(LOOKUP(2,1/(SIGN(LOOKUP(2,1/(F40:M40<>""),F40:M40))*F40:I40<=0)/(F40:M40<>""),$F$39:$J$39+1),)):M40)

Chỉ Enter.

Thân

algorithm · 7/5/19

Phan Thế Hiệp đã viết:
Vậy chỉnh lại:

Mã:

=SUM(INDEX(F40:M40,,IFERROR(LOOKUP(2,1/(SIGN(LOOKUP(2,1/(F40:M40<>""),F40:M40))*F40:I40<=0)/(F40:M40<>""),$F$39:$J$39+1),)):M40)

Chỉ Enter.

Thân

anh Hiệp thân mến,
em đã ngồi suy nghĩ cái công thức anh viết từ hôm qua tới giờ nhưng thật sự là em vẫn chưa hiểu được quy trình tính toán của hàm này dẫn tới việc em không thể edit công thức trong các trường hợp khác nhau.
theo như em hiểu thì anh đang xác định dấu âm và dấu dương nhưng tới đoạn iferror là em chịu thua luôn.
Do đó trong tình huống em thay đổi cách trả kết quả như sau

khi đó case 1,2,3,4,5 em chuyển về quản lý theo %, khi đó 20% và 80% nó sẽ như nhau, kết quả trả về theo giá trị cố định
em ví dụ như case 1: giá trị cuối cùng là -30% thì sẽ trả về giá trị tương ứng là -26 (vì nó là % âm)
case 2: giá trị cuối cùng là 0% thì trả về 0 y như trước đây
Case 3: giá trị cuối cùng là 20% + 30% thì trả về giá trị là SUM=25+27 (25 tương ứng với 20% và 27 tương ứng với 30%)
----
Mong được anh giúp đỡ vì em thật sự không hiểu được ý nghĩa của hàm anh giúp ở trên ạ.
em xin cám ơn

Phan Thế Hiệp · 7/5/19

algorithm đã viết:
anh Hiệp thân mến,
em đã ngồi suy nghĩ cái công thức anh viết từ hôm qua tới giờ nhưng thật sự là em vẫn chưa hiểu được quy trình tính toán của hàm này dẫn tới việc em không thể edit công thức trong các trường hợp khác nhau.
theo như em hiểu thì anh đang xác định dấu âm và dấu dương nhưng tới đoạn iferror là em chịu thua luôn.
Do đó trong tình huống em thay đổi cách trả kết quả như sau
View attachment 216636
khi đó case 1,2,3,4,5 em chuyển về quản lý theo %, khi đó 20% và 80% nó sẽ như nhau, kết quả trả về theo giá trị cố định
em ví dụ như case 1: giá trị cuối cùng là -30% thì sẽ trả về giá trị tương ứng là -26 (vì nó là % âm)
case 2: giá trị cuối cùng là 0% thì trả về 0 y như trước đây
Case 3: giá trị cuối cùng là 20% + 30% thì trả về giá trị là SUM=25+27 (25 tương ứng với 20% và 27 tương ứng với 30%)
----
Mong được anh giúp đỡ vì em thật sự không hiểu được ý nghĩa của hàm anh giúp ở trên ạ.
em xin cám ơn

Nhờ anh @HieuCD hướng dẫn và anh cũng thống nhất, nên sẽ thay đoạn công thức (F44:L44<>"") thành ($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)) để tính toán chính xác hơn. Cụ thể:
@HieuCD:

Mã:

N44=SUMPRODUCT(F44:L44*($F$43:$L$43>MIN(IFERROR(LOOKUP(2,1/((F44:L44=0)+((F44:L44*G44:M44)<0)=1)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43),0),$L$43)))

Enter, fill xuống.
@pth:

Mã:

L44=SUM(INDEX(F44:L44,,IFERROR(LOOKUP(2,1/(SIGN(LOOKUP(2,1/(F44:L44<>""),F44:L44))*F44:L44<=0)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43+1),)):L44)

Enter, fill xuống.

Nói chung, cả hai công thức đều tính toán để lấy ra Ô/Vùng cần cộng lại như yêu cầu.
Công thức @pth thì chọn giải pháp tìm Vùng như sau:

Nếu giá trị cuối cùng là "Âm": thì đi tìm ô có giá trị "Dương" hoặc bằng "0" hoặc "ô trống rỗng" gần nhất trước giá trị cuối cùng đó, nếu tìm thấy thì cộng thêm 1, tức là ô bắt đầu trong Vùng cần cộng. Ví dụ: Case 5 (dòng 49), có:
- tháng 1= -30%
- tháng 2= +30%
- tháng 3= -30%
- tháng 4= -20%
  - =>Do giá trị cuối cùng là Âm (tháng 4=-20%), nên công thức sẽ đi tìm Giá trị "Dương" hoặc "0" gần nhất trước đó, ta thấy chỉ có tháng 2= +30% thỏa.
  - =>Do nó đang ở tháng 2 (số 2 ở hàng G43), nên cộng thêm 1 nữa bằng 3 tức Vùng cộng là từ tháng 3 (-30%) và tháng 4 (-20%)
  - Kết quả cuối cùng sẽ thành =SUM(H49:L49)
Ngược lại, nếu giá trị cuối cùng là "Dương": thì đi tìm ô có giá trị "Âm" hoặc bằng "0" hoặc "ô trống rỗng" gần nhất trước giá trị cuối cùng đó, nếu tìm thấy thì cộng thêm 1, tức là ô bắt đầu trong Vùng cần cộng. Ví dụ: Case 3 (dòng 46), sẽ tìm thấy giá trị tháng 2=0% gần nhất trước nó, rồi cộng 1 sẽ bằng 3, tức Vùng cộng từ tháng 3 đến tháng 4, Kết quả cuối cùng sẽ thành =SUM(H46:L46)

Riêng, em muốn quy đổi từ -30% về -26, hay 20% về số gì gì... đó, thì phải thiết lập bảng cho chuẩn đi, lúc đó các anh sẽ quy đổi ra được.

Thân

Phan Thế Hiệp · 8/5/19

algorithm đã viết:
anh Hiệp thân mến,
em đã ngồi suy nghĩ cái công thức anh viết từ hôm qua tới giờ nhưng thật sự là em vẫn chưa hiểu được quy trình tính toán của hàm này dẫn tới việc em không thể edit công thức trong các trường hợp khác nhau.
theo như em hiểu thì anh đang xác định dấu âm và dấu dương nhưng tới đoạn iferror là em chịu thua luôn.
Do đó trong tình huống em thay đổi cách trả kết quả như sau
View attachment 216636
khi đó case 1,2,3,4,5 em chuyển về quản lý theo %, khi đó 20% và 80% nó sẽ như nhau, kết quả trả về theo giá trị cố định
em ví dụ như case 1: giá trị cuối cùng là -30% thì sẽ trả về giá trị tương ứng là -26 (vì nó là % âm)
case 2: giá trị cuối cùng là 0% thì trả về 0 y như trước đây
Case 3: giá trị cuối cùng là 20% + 30% thì trả về giá trị là SUM=25+27 (25 tương ứng với 20% và 27 tương ứng với 30%)
----
Mong được anh giúp đỡ vì em thật sự không hiểu được ý nghĩa của hàm anh giúp ở trên ạ.
em xin cám ơn

Nhìn hồi lâu mới hiểu được bạn muốn dùng Giá trị dòng số 52 và 53 để quy đổi các giá trị âm dương phía trên! phải vậy không!?
Nếu đúng, dùng công thức sau:

Mã:

=SUMPRODUCT(SIGN(F44:L44*($F$43:$L$43>MIN(IFERROR(LOOKUP(2,1/((F44:L44=0)+((F44:L44*G44:L44)<0)=1)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43),0),$L$43)))*$F$53:$L$53)
Hoặc
=SUMPRODUCT((LOOKUP(2,1/(SIGN(LOOKUP(2,1/(F44:L44<>""),F44:L44))*F44:L44<=0)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43)<$F$43:$L$43)*SIGN(F44:L44)*$F$53:$L$53)

Enter, fill xuống.

Thân

algorithm · 8/5/19

Phan Thế Hiệp đã viết:
Nhìn hồi lâu mới hiểu được bạn muốn dùng Giá trị dòng số 52 và 53 để quy đổi các giá trị âm dương phía trên! phải vậy không!?
Nếu đúng, dùng công thức sau:

Mã:

=SUMPRODUCT(SIGN(F44:L44*($F$43:$L$43>MIN(IFERROR(LOOKUP(2,1/((F44:L44=0)+((F44:L44*G44:L44)<0)=1)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43),0),$L$43)))*$F$53:$L$53) Hoặc =SUMPRODUCT((LOOKUP(2,1/(SIGN(LOOKUP(2,1/(F44:L44<>""),F44:L44))*F44:L44<=0)/($F$43:$L$43<=LOOKUP(10^10,F44:L44,$F$43:$L$43)),$F$43:$L$43)<$F$43:$L$43)*SIGN(F44:L44)*$F$53:$L$53)

Enter, fill xuống.

Thân

anh Hiệp đúng là super, xin lỗi anh vì phản hồi chậm trễ do hôm qua em về nhà không có laptop để làm tiếp. sáng giờ em đang chuẩn bị 1 bảng chuẩn để gởi lại cho anh.
Phần diễn giải ở trên em trình bày lung tung quá nên chắc anh cũng khó hiểu thiệt

Cám ơn anh rất nhiều ạ

HeSanbi · 8/5/19

befaint đã viết:

..

PHP:

Function Tinh_Tong(ByVal Rng As Range)
    Dim flag As Boolean, a, fa, ia, T, sCheck
    a = Rng.Value
    If Rng.Count = 1 Then a = Array(a)
    For Each ia In a
        If flag = False Then
            T = ia
            flag = True
        Else
            sCheck = Sgn(fa) * Sgn(ia)
            If sCheck = -1 Then
                T = ia
            ElseIf sCheck = 0 Then
                T = 0
                flag = False
            ElseIf sCheck = 1 Then
                T = T + ia
            End If
        End If
        fa = ia
    Next ia
    Tinh_Tong = T
End Function

Mã:

M40=Tinh_Tong(F40:I40)

Vấn đề ở code trên: Ô sau cùng khác 0, 2 ô bất kì đều 0 thì ... 0
Góp vui:

PHP:

Function Loc_Tinh%(ByVal Rngs As Range)
  Dim Value, T%
  For Each Value In Rngs.Value
    If Value < 0 And T > 0 Or Value = 0 Or Value > 0 And T < 0 Then T = 0
    T = T + Value
  Next
  Loc_Tinh = T
End Function

Xin giúp đỡ viết hàm Sum có điều kiện dấu âm, dương liên tục (5 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 mới

|||||||||||||

Thành viên mới

<New Horizons>

-⍟-

Thành viên mới

|||||||||||||

Chuyên gia GPE

Thành viên mới

Ăn cùng góc phố

|||||||||||||

Thành viên mới

Ăn cùng góc phố

-⍟-

File đính kèm

Thành viên mới

File đính kèm

-⍟-

File đính kèm

-⍟-

File đính kèm

Thành viên mới

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Đếm ngược thời gian