Macro hàm Sumif (sumif dữ liệu của dòng) (4 người xem)

[roadno1]

Mình có 1 bảng dữ liệu từ [AE6: BI812] và có 2 cột BK và BL dùng để sumif dữ liệu theo hàng ngang (với điều kiện >0 ở cột BK & <0 ở cột BL) của bảng dữ liệu.
VD: BK6 = SUMIF(AE6:BI6,">0"), BL6 = SUMIF(AE6:BI6,"<0")
BK7 = SUMIF(AE7:BI7,">0"), BL7 = SUMIF(AE7:BI7,"<0")
……nối tiếp…………………………………………………………………….
BK812 = SUMIF(AE812:BI812,">0"), BL812 = SUMIF(AE812:BI812,"<0")
Nhưng vấn đề là khi mình ẩn 1 vài cột bất kì trong vùng [AE6: BI812] thì hàm sumif vẫn tính luôn giá trị của cột ẩn đó. Mình muốn hàm sumif không tính các giá trị khi có cột bị ẩn.
Mọi người có cách nào không. Macro mình không biết nên nhờ mọi người giúp đỡ

 

[Let'GâuGâu]

Mình có 1 bảng dữ liệu từ [AE6: BI812] và có 2 cột BK và BL dùng để sumif dữ liệu theo hàng ngang (với điều kiện >0 ở cột BK & <0 ở cột BL) của bảng dữ liệu.
VD: BK6 = SUMIF(AE6:BI6,">0"), BL6 = SUMIF(AE6:BI6,"<0")
BK7 = SUMIF(AE7:BI7,">0"), BL7 = SUMIF(AE7:BI7,"<0")
……nối tiếp…………………………………………………………………….
BK812 = SUMIF(AE812:BI812,">0"), BL812 = SUMIF(AE812:BI812,"<0")
Nhưng vấn đề là khi mình ẩn 1 vài cột bất kì trong vùng [AE6: BI812] thì hàm sumif vẫn tính luôn giá trị của cột ẩn đó. Mình muốn hàm sumif không tính các giá trị khi có cột bị ẩn.
Mọi người có cách nào không. Macro mình không biết nên nhờ mọi người giúp đỡ

Bạn đang hỏi về cthức hay macro?
đối với cthức, nếu bạn muốn tính cái gì đó bỏ qua mấy cột ân thì phải xài subtotal
sao bạn không đưa file lên?

 

[roadno1]

Subtotal không làm được vấn đề trên, Mình hỏi về Macro bạn

 

[Let'GâuGâu]

Subtotal không làm được vấn đề trên, Mình hỏi về Macro bạn

thấy file mới biết, ko file người ta ngồi tưởng tượng để viết code?

 

[roadno1]

thấy file mới biết, ko file người ta ngồi tưởng tượng để viết code?

Đây là file giả định của mình, Bạn xem nhe

 

[Let'GâuGâu]

Đây là file giả định của mình, Bạn xem nhe

bạn kiểm tra lại xem có xài được không

Function SumifBypassHiddenCol(rng As Range, sym As String, so As Byte)
Dim i As Integer
If rng.Rows.Count > 1 Then SumifBypassHiddenCol = "#Value!": Exit Function
For i = rng.Columns(1).Column To rng.Columns.Count
If Columns(i).Hidden = False Then
    Select Case sym
    Case Chr(62)
        If Cells(rng.Row, i) > so Then cong = cong + Cells(rng.Row, i)
    Case Chr(60)
        If Cells(rng.Row, i) < so Then cong = cong + Cells(rng.Row, i)
    Case Else
        SumifBypassHiddenCol = "#Value!": Exit Function
    End Select
End If
Next
SumifBypassHiddenCol = cong
End Function

cthuc tai

S19=SumifBypassHiddenCol(A19:R19,">",0)

điều kiện thì mới làm 2 cái là > và < thôi,

 

[roadno1]

bạn kiểm tra lại xem có xài được không

Function SumifBypassHiddenCol(rng As Range, sym As String, so As Byte)
Dim i As Integer
If rng.Rows.Count > 1 Then SumifBypassHiddenCol = "#Value!": Exit Function
For i = rng.Columns(1).Column To rng.Columns.Count
If Columns(i).Hidden = False Then
    Select Case sym
    Case Chr(62)
        If Cells(rng.Row, i) > so Then cong = cong + Cells(rng.Row, i)
    Case Chr(60)
        If Cells(rng.Row, i) < so Then cong = cong + Cells(rng.Row, i)
    Case Else
        SumifBypassHiddenCol = "#Value!": Exit Function
    End Select
End If
Next
SumifBypassHiddenCol = cong
End Function

cthuc tai

S19=SumifBypassHiddenCol(A19:R19,">",0)

điều kiện thì mới làm 2 cái là > và < thôi,

Mình không biết add code đó vào đâu hết bạn, mình add thử vào sheet & modul nhưng nó không chạy

 

[comet_1701]

Mình không biết add code đó vào đâu hết bạn, mình add thử vào sheet & modul nhưng nó không chạy

Tôi gán vào module vẫn chạy bình thường mà, ban thử chỉnh lại chế độ enable marco xem

 

[roadno1]

Tôi gán vào module vẫn chạy bình thường mà, ban thử chỉnh lại chế độ enable marco xem

Mình add code vào Modul rồi nó chạy nhưng phải F9 thì số liệu mới cập nhật, không tự động cập nhật khi ẩn cột

 

[Quang_Hải]

Mình add code vào Modul rồi nó chạy nhưng phải F9 thì số liệu mới cập nhật, không tự động cập nhật khi ẩn cột

Hàm tự tạo là vậy mà. Khi nào có sự thay đổi tính toán trong bất kỳ ô nào của bảng tính thì nó mới cập nhật được

 

[Let'GâuGâu]

Mình add code vào Modul rồi nó chạy nhưng phải F9 thì số liệu mới cập nhật, không tự động cập nhật khi ẩn cột

chửa cháy
chép cái này vào sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = Rows.Count * Selection.Columns.Count Then
    hoi = MsgBox("Ban muon an cot?", vbYesNo, "")
    If hoi = vbYes Then Selection.Columns.Hidden = True: Calculate
End If
End Sub

===========
cũng không ổn, thôi làm cái sub, nhấn nút cho nó chạy là chắc ăn

 

[roadno1]

Có cách nào để nó tự động được không bạn, vì mình không động đến công thức mà chỉ đụng tới số liệu thôi

 

[roadno1]

Cũng không ổn thật anh, vì cứ đụng tới cột là hỏi có ần hay không, mà đâu phải lúc nào đụng tới cột là ẩn nó đâu anh
Có hướng nào tốt hơn không anh

 

[ndu96081631]

chửa cháy

Bạn cho dòng Application.Volatile vào đầu đoạn code Function SumifBypassHiddenCol của bạn là được

 

[Quang_Hải]

Bạn cho dòng Application.Volatile vào đầu đoạn code Function SumifBypassHiddenCol của bạn là được

Cũng sẽ không ép phê đâu anh ơi.
Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

 

[ndu96081631]

Cũng sẽ không ép phê đâu anh ơi.

Sao lại không?
Ẩn cột không ép phê nhưng khi hiện cột sẽ có đấy
Hàm tự tạo với những thay đổi không liên quan với giá trị trên cell (như thay đổi màu sắc, kẻ khung, ẩn dòng, ẩn cột) thì chỉ có thể chấp nhận ở mức độ nào đó thôi

 

[Quang_Hải]

Sao lại không?
Ẩn cột không ép phê nhưng khi hiện cột sẽ có đấy
Hàm tự tạo với những thay đổi không liên quan với giá trị trên cell (như thay đổi màu sắc, kẻ khung, ẩn dòng, ẩn cột) thì chỉ có thể chấp nhận ở mức độ nào đó thôi

Em đã từng thử thế này nhưng không ăn thua

Function SumVisible(rng As Range, Optional dk As Boolean = True) As Double
Application.Volatile
Dim it, kqduong, kqam
For Each it In rng
    If it.Columns.Hidden = False Then
        If it > 0 Then kqduong = kqduong + it Else kqam = kqam + it
    End If
Next
If dk = True Then SumVisible = kqduong Else SumVisible = kqam
End Function

Phải chữa cháy thế này

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End Sub

 

[roadno1]

bạn kiểm tra lại xem có xài được không

Function SumifBypassHiddenCol(rng As Range, sym As String, so As Byte)
Dim i As Integer
If rng.Rows.Count > 1 Then SumifBypassHiddenCol = "#Value!": Exit Function
For i = rng.Columns(1).Column To rng.Columns.Count
If Columns(i).Hidden = False Then
    Select Case sym
    Case Chr(62)
        If Cells(rng.Row, i) > so Then cong = cong + Cells(rng.Row, i)
    Case Chr(60)
        If Cells(rng.Row, i) < so Then cong = cong + Cells(rng.Row, i)
    Case Else
        SumifBypassHiddenCol = "#Value!": Exit Function
    End Select
End If
Next
SumifBypassHiddenCol = cong
End Function

cthuc tai

S19=SumifBypassHiddenCol(A19:R19,">",0)

điều kiện thì mới làm 2 cái là > và < thôi,

Anh Let'GâuGâu có 1 vấn đề nữa là code chỉ dùng được trong vùng từ A-T thôi, không được insesert thêm bất kì cột nào trước cột A (nếu em insert 1 cột trước cột A hiện tại thì kết quả cho ra sai anh, hàm sum sẽ bỏ đi 1 cột liền kề với cột S), nếu bảng tính được đặt ở vùng từ AA - BZ thì code không chạy nữa anh, công thức không có tác dụng nữa, kết quả lúc nào cũng là 0, anh xem thử giúp em, file giả định vùng tính của em là từ cột A -> T. Nhưng vùng tính trên file của em là từ cột AE->BI nên khi chép code của anh vào thì không có tác dụng gì hết, anh xem giúp em nha

 

[Let'GâuGâu]

Anh Let'GâuGâu có 1 vấn đề nữa là code chỉ dùng được trong vùng từ A-T thôi, không được insesert thêm bất kì cột nào trước cột A (nếu em insert 1 cột trước cột A hiện tại thì kết quả cho ra sai anh, hàm sum sẽ bỏ đi 1 cột liền kề với cột S), nếu bảng tính được đặt ở vùng từ AA - BZ thì code không chạy nữa anh, công thức không có tác dụng nữa, kết quả lúc nào cũng là 0, anh xem thử giúp em, file giả định vùng tính của em là từ cột A -> T. Nhưng vùng tính trên file của em là từ cột AE->BI nên khi chép code của anh vào thì không có tác dụng gì hết, anh xem giúp em nha

khi bạn insert thêm cột A thì các hàm khác cũng vậy mà, tôi thử hàm sum cũng vậy
đây là kết quả khi tôi insert cột A (s19 thành T19)

T19=SumifBypassHiddenCol(B19:S19,">",0)

còn kết quả sai khi để chổ khác là do tôi tính sai vi rí cột

For i = rng.Columns(1).Column To [COLOR=#0000ff]rng.Columns(rng.Columns.Count).Column[/COLOR]

cái vụ tự độn tính bạn đã giải pháp chưa?

 

[roadno1]

khi bạn insert thêm cột A thì các hàm khác cũng vậy mà, tôi thử hàm sum cũng vậy
đây là kết quả khi tôi insert cột A (s19 thành T19)

T19=SumifBypassHiddenCol(B19:S19,">",0)

còn kết quả sai khi để chổ khác là do tôi tính sai vi rí cột

For i = rng.Columns(1).Column To [COLOR=#0000ff]rng.Columns(rng.Columns.Count).Column[/COLOR]

cái vụ tự độn tính bạn đã giải pháp chưa?

Cảm ơn anh, số liệu tính toán không sai nữa. Còn vụ tính toán tự động thì có vấn đề anh.
Xử lý dữ liệu khá chậm mỗi khi rê chuột đến cell khác thì bị giật vì chậm