Hàm Sum nhiều tính năng (1 người xem)

hoangdanh282vn · 9/10/08

Mình gửi đến các bạn vài hàm Sum tự tạo nhằm đa dạng hóa tính năng cộng của hàm Sum

Đầu tiên là hàm SumType :
Chức năng :
- Cộng các ô có chứa hay không chứa công thức.
- Cộng các ô có hay không có màu nền
- Cộng các ô có hay không có màu Font chữ
- Cộng các ô có hay không có Font chữ in đậm
- Bỏ qua các ô bị lỗi khi cộng
PS : Xem hàm Sumpower ở bài #5 với nhiều tính năng hơn

PHP:

Public Function SumType(TypeFunc As Byte, Opt As Boolean, Rng1 As Range, _
      Optional Rng2 As Range, Optional Rng3 As Range, Optional Rng4 As Range, _
      Optional Rng5 As Range) As Double
Dim Cell As Range, SumT As Double
For Each Cell In Rng1
If Not IsError(Cell) Then
    If IsNumeric(Cell) Then
    Select Case TypeFunc
    Case 1 'cong cac cell dang value hay formula.'
        If Cell.HasFormula = Opt Then SumT = SumT + Cell
    Case 2 'cong cac cell co mau hay khong co mau nen.'
        If Cell.Interior.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 3 'cong cac cell co mau hay khong co mau font chu.'
        If Cell.Font.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 4 'cong cac cell co hay khong co font in dam.'
        If Cell.Font.Bold = Opt Then SumT = SumT + Cell
    End Select
    End If
End If
Next
'---------------------------------------------------------------------'
If Not Rng2 Is Nothing Then
For Each Cell In Rng2
If Not IsError(Cell) Then
    If IsNumeric(Cell) Then
    Select Case TypeFunc
    Case 1 'cong cac cell dang value hay formula.'
        If Cell.HasFormula = Opt Then SumT = SumT + Cell
    Case 2 'cong cac cell co mau hay khong co mau nen.'
        If Cell.Interior.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 3 'cong cac cell co mau hay khong co mau font chu.'
        If Cell.Font.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 4 'cong cac cell co hay khong co font in dam.'
        If Cell.Font.Bold = Opt Then SumT = SumT + Cell
    End Select
    End If
End If
Next
End If
'---------------------------------------------------------------------'
If Not Rng3 Is Nothing Then
For Each Cell In Rng3
If Not IsError(Cell) Then
    If IsNumeric(Cell) Then
    Select Case TypeFunc
    Case 1 'cong cac cell dang value hay formula.'
        If Cell.HasFormula = Opt Then SumT = SumT + Cell
    Case 2 'cong cac cell co mau hay khong co mau nen.'
        If Cell.Interior.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 3 'cong cac cell co mau hay khong co mau font chu.'
        If Cell.Font.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 4 'cong cac cell co hay khong co font in dam.'
        If Cell.Font.Bold = Opt Then SumT = SumT + Cell
    End Select
    End If
End If
Next
End If
'---------------------------------------------------------------------.'
If Not Rng4 Is Nothing Then
For Each Cell In Rng4
If Not IsError(Cell) Then
    If IsNumeric(Cell) Then
    Select Case TypeFunc
    Case 1 'cong cac cell dang value hay formula.'
        If Cell.HasFormula = Opt Then SumT = SumT + Cell
    Case 2 'cong cac cell co mau hay khong co mau nen.'
        If Cell.Interior.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 3 'cong cac cell co mau hay khong co mau font chu.'
        If Cell.Font.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 4 'cong cac cell co hay khong co font in dam.'
        If Cell.Font.Bold = Opt Then SumT = SumT + Cell
    End Select
    End If
End If
Next
End If
'---------------------------------------------------------------------.'
If Not Rng5 Is Nothing Then
For Each Cell In Rng5
If Not IsError(Cell) Then
    If IsNumeric(Cell) Then
    Select Case TypeFunc
    Case 1 'cong cac cell dang value hay formula.'
        If Cell.HasFormula = Opt Then SumT = SumT + Cell
    Case 2 'cong cac cell co mau hay khong co mau nen.'
        If Cell.Interior.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 3 'cong cac cell co mau hay khong co mau font chu.'
        If Cell.Font.ColorIndex > 0 = Opt Then SumT = SumT + Cell
    Case 4 'cong cac cell co hay khong co font in dam.'
        If Cell.Font.Bold = Opt Then SumT = SumT + Cell
    End Select
    End If
End If
Next
End If
SumType = SumT
End Function

ndu96081631 · 9/10/08

Thêm dòng:

Application.Volatile

vào đầu code nữa thì mới đúng! Vì việc thay đổi màu nền, màu chử không được Excel cập nhật đâu (dù có bấm F9)

Mr Okebab · 9/10/08

Em thử dùng phương thức Union để gộp các Range lại có được hay không ?? Chứ xét từng Cell trong từng Range thì . . hơi phê.

Bản thân hàm Sum đã bỏ qua hết các ký tự và chỉ cộng số, vì vậy có thể lợi dụng được hàm Sum của excel, không cần lúc nào cũng For Each

Với mỗi Cell em lại xét Case TypeFunc thì không nên, vì TypeFunc đã được xác định trước nên đặt For Each trong Case thì hợp lý hơn. Nếu có thể thì nên dùng Goto và Lable cho gọn gàng. Mặc dù Code dài hơn nhưng lại dễ điều khiển và chạy nhanh hơn

Không nên cộng có màu hay không có màu (cái khác cũng vậy), mà nên là cùng màu hay không cùng màu, như thế sẽ linh hoạt hơn.

Mình cũng đang nghiên cứu về cách khai báo xem có bao nhiêu mảng cũng được, mà lại chưa biết cách. Không lẽ có 30 mảng thì phải liệt kê ra cả 30 ??? Hàm SUM là 30 đối số đó.

Thân!

rollover79 · 9/10/08

Em cũng học đòi tạo 1 cái hàm Count không nhiều tính năng như sau, hàm trả về số các ô của các vùng tham số

Mã:

Function CountAll(ParamArray rng() As Variant) As Long
    Dim i As Long
    Dim iRet As Long
    For i = LBound(rng) To UBound(rng)
        iRet = iRet + rng(i).Count
    Next
    CountAll = iRet
End Function

Nhưng không hiểu sao cái thằng ParamArray này chỉ chấp nhận 29 vùng mà không phải là 30 như các hàm của excel.

hoangdanh282vn · 13/10/08

Cảm ơn các anh đã góp ý.
EM xin gửi Function mới với nhiều tính năng hơn.

1 : Cộng các ô có hay không có chứa giá trị lỗi
2 : Cộng các ô có giá trị bằng ("=") với Điều kiện
3 : Cộng các ô có giá trị nhỏ hơn ("<") Điều kiện
4 : Cộng các ô có giá trị lớn hơn (">") Điều kiện
5 : Cộng các ô có hay không có chứa công thức
6 : Cộng các ô có hay không có chứa font chữ in đậm
7 : Cộng các ô có hay không có chứa mầu font chữ
8 : Cộng các ô có chứa mầu font chữ tương tự như ô Điều kiện
9 : Cộng các ô có hay không có chứa mầu nền
10 : Cộng các ô có chứa mầu nền tương tự như ô Điều kiện

PHP:

Public Function SumPower(TypeFunc As Byte, Source As Variant, _
              ParamArray AllRange() As Variant) As Double
Dim Rng As Variant, Cell As Range, SumT As Double
Application.Volatile
With WorksheetFunction
Select Case TypeFunc
Case 1 'Cong các ô có hay không có chua Giá Tri Loi'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
        If -IsError(Cell) = Source Then SumT = .Sum(SumT, Cell)
        Next
    Next
    GoTo Finish
Case 2 'Cong các ô có Giá Tri bang gia tri nguon "Source"'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If Val(Cell) = Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 3 'Cong các ô có Giá Tri nho hon gia tri nguon "Source"'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If Val(Cell) < Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 4 'Cong các ô có Giá Tri lon hon gia tri nguon "Source"'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If Val(Cell) > Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 5 'Cong các ô có hay không có chua Công Thuc'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If -Cell.HasFormula = Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 6 'Cong các ô có hay không có chua Font Chu In Dam'
    For Each Rng In AllRange()
       For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If -Cell.Font.Bold = Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 7 'Cong các ô có hay không có chua Mau Font Chu'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If -(Cell.Font.ColorIndex > 0) = Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 8 'Cong các ô có chua Mau Font Chu tuong tu nhu ô goc "Source"'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If Cell.Font.ColorIndex = Source.Font.ColorIndex Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 9 'Cong các ô có hay không có chua Mau Nen'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If -(Cell.Interior.ColorIndex > 0) = Source Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
    GoTo Finish
Case 10 'Cong các ô có chua Mau Nen tuong tu nhu ô goc "Source"'
    For Each Rng In AllRange()
        For Each Cell In Union(Rng.SpecialCells(2, 19), Rng.SpecialCells(-4123, 19))
            If Not IsError(Cell) Then
                If Cell.Interior.ColorIndex = Source.Interior.ColorIndex Then
                    SumT = .Sum(SumT, Val(Cell))
                End If
            End If
        Next
    Next
End Select
End With
Finish: SumPower = SumT
End Function

knga · 21/12/08

Tôi là người có dùng excell nhưng không thạo. Tôi có đọc bài "hàm FUNCTION nhiều tính năng" của bạn Hoangdanh và thấy rất thích vì công việc của tôi có liên quan. Đó là vì tôi luôn phải cộng các ô trong bảng tính, nhưng tôi chỉ biết cộng thủ công bằng cách chọn từng ô một, việc này luôn gây nhầm lẫn vì số ô là rất nhiều. Tôi muốn bạn giúp chỉ rõ cách tính thứ 10-cộng các ô có cùng màu..., vì tôi hầu như không biết gì về các hàm tự tạo trong excell.

hoan240179 · 26/12/08

Hàm sumpower của bạn hoangdanh rất hay! cho tôi hỏi một chút:
Tôi có một file (c:\dulieu\doanhso.xls) có trường Doanhso và trường Tenhang, và file (c:\dulieu\tonghop.xls). tôi muốn tính tổng doanh số của từng mặt hàng rồi ghi giá trị sang file tổng hợp nhưng không muốn mở thủ công file doanhso.xls làm cách nào mong bạn và các chuyên gia Excel giúp đỡ.

hoangdanh282vn · 26/12/08

hoan240179 đã viết:
Hàm sumpower của bạn hoangdanh rất hay! cho tôi hỏi một chút:
Tôi có một file (c:\dulieu\doanhso.xls) có trường Doanhso và trường Tenhang, và file (c:\dulieu\tonghop.xls). tôi muốn tính tổng doanh số của từng mặt hàng rồi ghi giá trị sang file tổng hợp nhưng không muốn mở thủ công file doanhso.xls làm cách nào mong bạn và các chuyên gia Excel giúp đỡ.

Nếu bạn muốn tính tổng và update dữ liệu mà không muốn mở file thủ công thì dùng VBA.

Bạn gửi file dữ liệu và file report lên nha, nêu rõ yêu cầu trong file nữa và bạn post bài tại đây nha.
http://www.giaiphapexcel.com/forum/showthread.php?t=15218&page=12

BNTT · 26/12/08

hoangdanh282vn đã viết:
Cảm ơn các anh đã góp ý.
EM xin gửi Function mới với nhiều tính năng hơn.

1 : Cộng các ô có hay không có chứa giá trị lỗi
2 : Cộng các ô có giá trị bằng ("=") với Điều kiện
3 : Cộng các ô có giá trị nhỏ hơn ("<") Điều kiện
4 : Cộng các ô có giá trị lớn hơn (">") Điều kiện
5 : Cộng các ô có hay không có chứa công thức
6 : Cộng các ô có hay không có chứa font chữ in đậm
7 : Cộng các ô có hay không có chứa mầu font chữ
8 : Cộng các ô có chứa mầu font chữ tương tự như ô Điều kiện
9 : Cộng các ô có hay không có chứa mầu nền
10 : Cộng các ô có chứa mầu nền tương tự như ô Điều kiện

Có thể đặt hàng thêm không?
11. Cộng những ô có số được gạch dưới ?
12. Cộng những ô có số được in nghiêng?
13. Cộng những ô có đóng khung (border) chung quanh?
v.v...

hoangdanh282vn · 26/12/08

BNTT đã viết:
Có thể đặt hàng thêm không?
11. Cộng những ô có số được gạch dưới ?
12. Cộng những ô có số được in nghiêng?
13. Cộng những ô có đóng khung (border) chung quanh?
v.v...

Mấy cái v.v..., anh cứ nêu hết, em sẽ tổng hợp và làm một thể.
Nhưng chỉ sợ người dùng choáng ngợp vì quá nhiều trường hợp để nhớ và áp dụng.

BNTT · 26/12/08

hoangdanh282vn đã viết:
Mấy cái v.v..., anh cứ nêu hết, em sẽ tổng hợp và làm một thể.
Nhưng chỉ sợ người dùng choáng ngợp vì quá nhiều trường hợp để nhớ và áp dụng.

Trước mắt thì anh chỉ nghĩ được mấy cái đó và thêm mấy cái này thôi:

14. Cộng những ô được định dạng bằng một loại font nào đó (Arial, Tahoma,...)
15. Cộng những ô được định dạng theo một kiểu nào đó (ví dụ, trong 1 hàng, có cái là số thường, có cái là %, có cái thì có dấu $ ở trước..., mình chỉ cộng riêng mấy cái % thôi chẳng hạn)

Và thêm cái nữa, cái này thì rất chung chung:

16. Cộng những ô có được định dạng (font chữ, kiểu chữ, màu chữ, màu tô, loại định dạng số,... nói chung là tất cả các loại định dạng có thể có được, giống như cầm cái chổi định dạng đi quét) giống y chang một ô nào đó được cho trước?

hoangdanh282vn · 26/12/08

knga đã viết:
Tôi là người có dùng excell nhưng không thạo. Tôi có đọc bài "hàm FUNCTION nhiều tính năng" của bạn Hoangdanh và thấy rất thích vì công việc của tôi có liên quan. Đó là vì tôi luôn phải cộng các ô trong bảng tính, nhưng tôi chỉ biết cộng thủ công bằng cách chọn từng ô một, việc này luôn gây nhầm lẫn vì số ô là rất nhiều. Tôi muốn bạn giúp chỉ rõ cách tính thứ 10-cộng các ô có cùng màu..., vì tôi hầu như không biết gì về các hàm tự tạo trong excell.

Bạn copy file Sumpower.xla
Vào Tools\Add-ins\Browse vào paste file xla vừa copy vào trong thư mục addins này. Sau đó chọn nó và ok, thoát ta ngoài.

Trong hộp thoại add-ins giờ đã xuất hiện Sumpower. Tick chọn vào nó và ok là sử dụng được.

Bạn muốn sử dụng chức năng 10 : công các ô có màu nền như ô điều kiện ?

Giả sử vùng muốn cộng là A1:B10; D1

10. Ô điều kiện là E1 có màu nền đỏ

Công thức để cộng các vùng trên với màu như ô E1 :

=Sumpower(10,E1,A1:B10,D110)

TaiKhiem · 16/3/09

Hàm tổng nhieuđiều kiện?
Làm ơn chỉ giùm hàm tính tổng có nhiều điều kiện? VD: tổng các ô từ ô A1:A10 là cột ghi mã các loại hàng hoá: A, B, C ...; Từ ô B1:B10 Số lượng từng loại hàng hoá; C1:C10 là giá trị hàng hóa tương ứng.
T

TaiKhiem · 16/3/09

Hàm sumif nhiều điều kiện

Các pác chỉ giùm hàm tính tổng (sumif) nhưng với từ 2 điều kiện trở lên. Thanks!

yeudoi · 16/3/09

TaiKhiem đã viết:
Các pác chỉ giùm hàm tính tổng (sumif) nhưng với từ 2 điều kiện trở lên. Thanks!

Nếu nhiều điều kiện các bạn có thể dùng sumproduct

gacon_vie · 15/4/09

Anh ơi ! Hàm sumpower anh viết rất hay, em có một chút góp ý bổ sung nếu anh có thể sửa thêm cho hoàn chỉnh hơn. Giả sử ta dùng hàm số 10, khi em cài đúng công thức rồi enter nó sẽ cho kết quả đúng, nhưng khi ta sửa lại màu của một số ô thì kết quả không tự thay đổi mà vẫn giữ nguyên kết quả ban đầu (không tự động refresh). Mong anh xem giúp nhé. Chân thành cảm ơn anh nhiều.

vanthiet85 · 23/11/09

em cộng các số trong 1 cột theo công thức của hàm sumpower(sum(),1,G2:G71) tránh các ô có lỗi và ký tự ra mà ko dc?? Rảnh anh xem lại hàm hộ e với.
Thanks

hungmo · 15/7/10

Mình lấy file của bạn hoangdanh282vn
thêm vào một số hàm nữa nè ! các xem góp ý nhé !
Không up được ở diễn đàn nên up chỗ khác vậy ! Thông cảm

http://www.mediafire.com/?lttjtmtmdkzotth

Xem góp ý thêm !

an_nguyenh · 13/5/15

Anh cho tôi hỏi tại sao sử dụng tải file có đuôi là xla vào office2010 thì nhận mà vào offiec2007 thì lại không nhận. Giúp tôi với. thanks

hvn8006 · 14/5/15

Trong trường hợp dùng hàm sum tự tạo như của bạn. Nếu sang một máy khác để in mà không có addin hàm tự tạo đó thì kết quả có bị thay đổi không? Nhờ bạn chỉ giúp.

Hàm Sum nhiều tính năng (1 người xem)

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

Nguyễn Cảnh Hoàng Danh

File đính kèm

Huyền thoại GPE

Ngon Ngất Ngây

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

Nguyễn Cảnh Hoàng Danh

File đính kèm

Thành viên mới

Thành viên mới

Nguyễn Cảnh Hoàng Danh

Bùi Nguyễn Triệu Tường

Nguyễn Cảnh Hoàng Danh

Bùi Nguyễn Triệu Tường

Nguyễn Cảnh Hoàng Danh

File đính kèm

Thành viên mới

Thành viên mới

Thành viên gắn bó

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

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

Đếm ngược thời gian