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

roadno1 · 1/8/14

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 · 1/8/14

roadno1 đã viết:
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 · 1/8/14

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

Let'GâuGâu · 1/8/14

roadno1 đã viết:
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 · 2/8/14

Let'GâuGâu đã viết:
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 · 3/8/14

roadno1 đã viết:
Đâ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

Mã:

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

Mã:

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

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

roadno1 · 3/8/14

Let'GâuGâu đã viết:

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

Mã:

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

Mã:

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 · 3/8/14

roadno1 đã viết:
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 · 4/8/14

comet_1701 đã viết:
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 · 4/8/14

roadno1 đã viết:
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 · 4/8/14

roadno1 đã viết:
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

Mã:

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 · 4/8/14

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 · 4/8/14

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 · 4/8/14

Let'GâuGâu đã viết:
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 · 4/8/14

ndu96081631 đã viết:
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 · 4/8/14

quanghai1969 đã viết:
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 · 4/8/14

ndu96081631 đã viết:
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

PHP:

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

PHP:

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

roadno1 · 4/8/14

Let'GâuGâu đã viết:

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

Mã:

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

Mã:

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 · 4/8/14

roadno1 đã viết:
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)

Mã:

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

Mã:

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 · 4/8/14

Let'GâuGâu đã viết:
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)

Mã:

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

Mã:

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

roadno1 · 6/8/14

quanghai1969 đã viết:

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

PHP:

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

PHP:

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

Dear anh với code này thì công thức goc trong cell là gì vậy anh

Quang_Hải · 6/8/14

roadno1 đã viết:
Dear anh với code này thì công thức goc trong cell là gì vậy anh

Cú pháp:
=SumVisible(vùng dữ liệu) nếu lấy số dương

=SumVisible(vùng dữ liệu,0) nếu lấy số âm

roadno1 · 6/8/14

quanghai1969 đã viết:
Cú pháp:
=SumVisible(vùng dữ liệu) nếu lấy số dương

=SumVisible(vùng dữ liệu,0) nếu lấy số âm

Dear anh!
Tốc độ xử lý còn quá chậm, có cách nào làm cho nó xử lý nhanh hơn không anh. Anh có thể tạo 1 cái nút để click khi cần cập nhật được không anh, vì em không nhất thiết phải cập nhật liền khi số liệu thây đổi hay rê chuột như hiện tại nó giật giật cảm thấy khó chịu sao đó anh. Em tính độ trễ khi di chuyển chuột khoảng tầm 3 giây đó anh
Thank anh

Quang_Hải · 6/8/14

roadno1 đã viết:
Dear anh!
Tốc độ xử lý còn quá chậm, có cách nào làm cho nó xử lý nhanh hơn không anh. Anh có thể tạo 1 cái nút để click khi cần cập nhật được không anh, vì em không nhất thiết phải cập nhật liền khi số liệu thây đổi hay rê chuột như hiện tại nó giật giật cảm thấy khó chịu sao đó anh. Em tính độ trễ khi di chuyển chuột khoảng tầm 3 giây đó anh
Thank anh

Vậy bỏ cái đoạn Application.CalculateFull đi
Mỗi lần muốn cập nhật thì bấm F9

roadno1 · 6/8/14

Let'GâuGâu đã viết:
chửa cháy
chép cái này vào sheet1

Mã:

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

Dear anh Let'GâuGâu , anh đã làm cái sub chưa dậy anh hjj

roadno1 · 6/8/14

quanghai1969 đã viết:
Vậy bỏ cái đoạn Application.CalculateFull đi
Mỗi lần muốn cập nhật thì bấm F9

Chuột nó vẫn quay tròn anh, độ trễ vẫn vậy không thây đổi anh

Tóc gió thôi bay · 6/8/14

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

Subtotal làm được, nếu bạn...nằm ngang

roadno1 · 6/8/14

Tóc gió thôi bay đã viết:
Subtotal làm được, nếu bạn...nằm ngang

Mình hỏi đứng chứ không phải ngang bạn

Quang_Hải · 6/8/14

roadno1 đã viết:
Chuột nó vẫn quay tròn anh, độ trễ vẫn vậy không thây đổi anh

Nếu dữ liệu nhiều có lẽ nên dùng Sub và 1 cái nút bấm mới có thể hết xoay vòng tròn

roadno1 · 6/8/14

quanghai1969 đã viết:
Nếu dữ liệu nhiều có lẽ nên dùng Sub và 1 cái nút bấm mới có thể hết xoay vòng tròn

Anh giúp em tạo 1 sub được không anh +-+-+-+

roadno1 · 8/8/14

HTML:

  Function Sum_Visible_Cells(Cells_To_Sum As Object)     Application.Volatile
     For Each cell In Cells_To_Sum
         If cell.Rows.Hidden = False Then
             If cell.Columns.Hidden = False Then
                 Total = Total + cell.Value
             End If
         End If
     Next
     Sum_Visible_Cells = Total
 End Function

Em tìm được đoạn code này trên http://www.mrexcel.com nó chạy hàm SUBTOTAL cho hàng ngang, Anh Let'GâuGâu có thể cải tiến theo hàm sumif cho số >0 hoặc <0 được không anh

Let'GâuGâu · 8/8/14

roadno1 đã viết:
HTML:

Function Sum_Visible_Cells(Cells_To_Sum As Object) Application.Volatile For Each cell In Cells_To_Sum If cell.Rows.Hidden = False Then If cell.Columns.Hidden = False Then Total = Total + cell.Value End If End If Next Sum_Visible_Cells = Total End Function

Em tìm được đoạn code này trên http://www.mrexcel.com nó chạy hàm SUBTOTAL cho hàng ngang, Anh Let'GâuGâu có thể cải tiến theo hàm sumif cho số >0 hoặc <0 được không anh

có 2 cái khó
1 là hàm subtotal có tác dụng với ẩn dòng, nhưng lại không có tác dụng với ẩn cột.
2 là khi ẩn dòng hoặc ẩn cột thì excel không tự chạy calculate.
vì vậy tôi nghĩ là chỉ có thể làm cái nút nhấn, khi bạn ẩn hoặc hiện xong thì nhấn cái nút đó để nó tính lại
nhưng có cái khó là khi mình làm sub thì cái vùng dữ liệu của mình phải cố định, không ấn định vùng dữ liệu thì rất khó làm

tôi vẫn không tìm được giải pháp nào hay, thôi bạn xem cách củ chuối này xài được ko?
tôi để lại một hàng đầu tiên, khi nào cần ẩn cột, bạn đánh vào đó một ký tự bất kỳ, không ẩn nữa thì xóa nó đi

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

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

Thành viên chính thức

Thành viên mới

Thành viên chính thức

Thành viên mới

Thành viên chính thức

File đính kèm

Thành viên mới

Thành viên chính thức

Thành viên tiêu biểu

Thành viên chính thức

Thành viên gạo cội

Thành viên mới

Thành viên chính thức

Thành viên chính thức

Huyền thoại GPE

Thành viên gạo cội

Huyền thoại GPE

Thành viên gạo cội

Thành viên chính thức

Thành viên mới

Thành viên chính thức

Thành viên chính thức

Thành viên gạo cội

Thành viên chính thức

Thành viên gạo cội

Thành viên chính thức

Thành viên chính thức

Thành viên mới

File đính kèm

Thành viên chính thức

Thành viên gạo cội

Thành viên chính thức

Thành viên chính thức

Thành viên mới

File đính kèm

Thời gian đếm ngược.