PDA

View Full Version : Tính tổng theo màu nền



thinhpt
20-10-07, 12:18 AM
Tôi muôn tạo một hàm tương tự nhw tính tổng tương tự hàm SUM, nhưng có thêm tham số màu nền. Hàm sẽ tính tổng các số trong range, nhưng chỉ tính tổng các số có cùng màu nền như trong tham số được cung cấp.

Mong anh em gần xa chỉ giáo (VBA)

SA_DQ
20-10-07, 07:10 AM
Tham khảo ở đây trước đi nha!!

http://www.giaiphapexcel.com/forum/archive/index.php/t-344.html


)(&&@@ --=--

MEGAheartno1
20-10-07, 09:46 AM
Em tưởng cái này vẫn có thể dùng " FORMULA IS " CONDITIONAL FORMATTING. Có dùng được không ạ. Cái này theo em được biết max 3 điều kiện tức là có thể dùng 3 FORMULA phải không ? Xin chỉ giáo

anhtuan1066
20-10-07, 10:13 AM
Conditional Formating chỉ đánh dấu cell theo màu nào đó thỏa mản điều kiện chứ làm sao mà SUM dc như bạn thinhpt vừa nói chứ!

Mr Okebab
20-10-07, 01:47 PM
Tôi muôn tạo một hàm tương tự nhw tính tổng tương tự hàm SUM, nhưng có thêm tham số màu nền. Hàm sẽ tính tổng các số trong range, nhưng chỉ tính tổng các số có cùng màu nền như trong tham số được cung cấp.

Mong anh em gần xa chỉ giáo (VBA) Bạn tham khảo ở đây nhé.
http://tinyurl.com/2mcpjy


Hoặc bạn có thể chi File VD
Thân!

thinhpt
23-10-07, 11:29 PM
Sau khi tham khảo các vi dụ anh em chỉ, mình vẫn không hiểu. Kèm theo đây là ví dụ cụ thể mong anh em chỉ giáo.

Mr Okebab
23-10-07, 11:44 PM
Sau khi tham khảo các vi dụ anh em chỉ, mình vẫn không hiểu. Kèm theo đây là ví dụ cụ thể mong anh em chỉ giáo.

Bạn xem nhé :

Thân!

thinhpt
23-10-07, 11:56 PM
Hay quá, cám ơn bác OK nhiều.

Cái mình biết là hạt cát so với biển cả kiến thức menh mông.

michealvu
17-01-08, 12:19 PM
Mình thấy rất tuyệt, nhưng không biết lý do tại sao khi mình thực hành thử trên chính file của bạn thì ok. Nhưng khi mình áp dụng vào file công việc thì lại không ra kết quả. không biết nó có ràng buộc điều kiện gì không.

Mr Okebab
17-01-08, 12:30 PM
Mình thấy rất tuyệt, nhưng không biết lý do tại sao khi mình thực hành thử trên chính file của bạn thì ok. Nhưng khi mình áp dụng vào file công việc thì lại không ra kết quả. không biết nó có ràng buộc điều kiện gì không.

Vì nó chỉ là 1 Function (Công thức) có tác dụng trong chính File đó. Muốn nó tác dụng trong File khác thì có 2 cách :

Copy Function đó vào chính File đó
Biến file chứa Function thành Add-insĐây là điều cần có hiểu biết chút xíu về VBA.

Thân!

toanexcel
14-11-08, 09:10 AM
tình hình la em muốn hỏi các bác là làm sao để có thể tính tổng của các ô có củng màu sắc trong một cột, tức là điều kiện lọc là cùng màu thì cộng lại ây ạ. thaks các bác nhiều!!!!--=----=--

huuthang_bd
14-11-08, 09:42 AM
tình hình la em muốn hỏi các bác là làm sao để có thể tính tổng của các ô có củng màu sắc trong một cột, tức là điều kiện lọc là cùng màu thì cộng lại ây ạ. thaks các bác nhiều!!!!--=----=--

Cái này thì không dùng công thức bình thường được rồi. Chắc phải dùng VBA mới đc.

DOSNET
14-11-08, 09:51 AM
tình hình la em muốn hỏi các bác là làm sao để có thể tính tổng của các ô có củng màu sắc trong một cột, tức là điều kiện lọc là cùng màu thì cộng lại ây ạ. thaks các bác nhiều!!!!--=----=--
Bạn thử dùng hàm tự tạo này xem !


Function Demtheomau(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim ICol As Long
Dim vResult
ICol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = ICol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = ICol Then
vResult = 1 + vResult
End If
Next rCell
End If
Demtheomau = vResult

End Function

hoangdanh282vn
14-11-08, 10:01 AM
tình hình la em muốn hỏi các bác là làm sao để có thể tính tổng của các ô có củng màu sắc trong một cột, tức là điều kiện lọc là cùng màu thì cộng lại ây ạ. thaks các bác nhiều!!!!--=----=--

Bạn tham khảo hàm Sumpower nha : bài #5

http://www.giaiphapexcel.com/forum/showthread.php?t=14706

ndu96081631
14-11-08, 12:10 PM
Cái này thì không dùng công thức bình thường được rồi. Chắc phải dùng VBA mới đc.
Làm đươc đấy!
Tải file tại đây về tham khảo nhé
http://www.giaiphapexcel.com/forum/showthread.php?t=7613
Trong này có SUMCOLOR

tigertiger
14-11-08, 12:12 PM
Làm đươc đấy!
Tải file tại đây về tham khảo nhé
http://www.giaiphapexcel.com/forum/showthread.php?t=7613
Trong này có SUMCOLOR


Cái này Dùng Name và macro4 khó sử dụng
vì rất nhiều phần mềm anti virus chặn Macro4

a nghĩ cách khác xem sao???
cách hay hơn đó chắc giải quyết được

ChanhTQ@
17-11-08, 10:45 AM
tình hình la em muốn hỏi các bác là làm sao để có thể tính tổng của các ô có củng màu sắc trong một cột, tức là điều kiện lọc là cùng màu thì cộng lại ây ạ. thaks các bác nhiều!!!!--=----=--

http://www.giaiphapexcel.com/forum/showthread.php?t=344

toanexcel
20-11-08, 07:44 AM
}}}}} cảm ơn các bác nhiều, em làm được rôi. hiii :-=

oanhson
04-12-09, 08:27 AM
Cho em hỏi có cách nào tính tổng của các ô đã bôi màu không ạh?

MinhCong
04-12-09, 08:39 AM
Cho em hỏi có cách nào tính tổng của các ô đã bôi màu không ạh?
Bài này trên diễn đàn có nhiều rồi thông thường phải dùng đế VB. Tặng Bạn file tính số ô tô màu của Bác AnhTuan dùng macro 4 về nghiên cứu nè.

ndu96081631
04-12-09, 09:18 AM
hIic hic... khó hiểu thế này thì mình phải từ bỏ ý định để làm thủ công mất thui. huuuuuuuuu!$@!!!$@!!!$@!!
Thủ công vẫn được với điều kiện bạn xài Excel 2007, nó cho phép Filter theo màu ---> Khi ấy bạn kết hợp với hàm SUBTOTAL là ra

oanhson
04-12-09, 09:46 AM
Thủ công vẫn được với điều kiện bạn xài Excel 2007, nó cho phép Filter theo màu ---> Khi ấy bạn kết hợp với hàm SUBTOTAL là ra
EM chỉ làm trên ex 2003 thui anh ah. Có cách nào khác không anh.

ndu96081631
04-12-09, 09:47 AM
EM chỉ làm trên ex 2003 thui anh ah. Có cách nào khác không anh.
Cách khác tức là lập trình ấy... Bạn đưa file lên đi

SA_DQ
04-12-09, 10:13 AM
Cách khác tức là lập trình ấy... Bạn đưa file lên đi

Nhưng nếu màu được bôi bỡi CF thì khỏi đưa file lên vì cài E2007 dễ hơn nhiều lần viết hàm để tính nó! ( Hãy tham khảo bài 56 sắc cầu vòng trong excel)

concogia
04-12-09, 11:51 AM
Cách khác tức là lập trình ấy... Bạn đưa file lên đi
Thầy ơi, nếu bạn ấy tô màu bằng CF sao ta không tóm ngay cái điều kiện tô màu dùng làm điều kiện để sumif, được không thày?

oanhson
04-12-09, 11:56 AM
Cách khác tức là lập trình ấy... Bạn đưa file lên đi
Mong mọi người giúp đỡ..

ndu96081631
04-12-09, 12:11 PM
Mong mọi người giúp đỡ..
Bạn nên miêu tả sơ qua chứ ---> Mở file định làm nhưng thấy ghi gì gì tùm lum bên trong nên càng không hiểu
- Tổng cột nào?
- Theo điều kiện gì?
- Đặt công thức vào cell nào?
(Có phải tính tổng các cell màu đỏ không ta?)

SA_DQ
04-12-09, 12:29 PM
Option Explicit
Function SumColorCells(LookUpRange As Range) As Double
Dim Clls As Range
For Each Clls In LookUpRange
With Clls
If .Offset(, -3).Value = Range("Tong").Value Then _
SumColorCells = SumColorCells + .Value
End With
Next Clls
End Function

Hướng dẫn sử dụng:

(1) Gàn tên cho 1 ô đang có chữ 'Tổng' với tên là 'Tong'

(2) Tại ô nào bất kỳ muốn hiện tổng trị đã bôi màu tại cột 'F', như [F7] ta nhập cú pháp sau:
=SumColorCells(F8:F31)

Thực tế, đây không phải tính tổng theo màu mà tính tổng theo dữ liệu ô cận kề (Đó là ô có chữ 'Tổng'); Chuyện này rất bấp bênh & chỉ áp dụng được với những bảng đặc thù cụ thể mà thôi.

oanhson
04-12-09, 02:36 PM
Bạn nên miêu tả sơ qua chứ ---> Mở file định làm nhưng thấy ghi gì gì tùm lum bên trong nên càng không hiểu
- Tổng cột nào?
- Theo điều kiện gì?
- Đặt công thức vào cell nào?
(Có phải tính tổng các cell màu đỏ không ta?)
Vâng, em xin lỗi. Em muốn tính tổng từng ngày tại các ô màu đỏ ở cột Chi.

oanhson
04-12-09, 02:43 PM
Option Explicit
Function SumColorCells(LookUpRange As Range) As Double
Dim Clls As Range
For Each Clls In LookUpRange
With Clls
If .Offset(, -3).Value = Range("Tong").Value Then _
SumColorCells = SumColorCells + .Value
End With
Next Clls
End FunctionHướng dẫn sử dụng:

(1) Gàn tên cho 1 ô đang có chữ 'Tổng' với tên là 'Tong'

(2) Tại ô nào bất kỳ muốn hiện tổng trị đã bôi màu tại cột 'F', như [F7] ta nhập cú pháp sau:
=SumColorCells(F8:F31)

Thực tế, đây không phải tính tổng theo màu mà tính tổng theo dữ liệu ô cận kề (Đó là ô có chữ 'Tổng'); Chuyện này rất bấp bênh & chỉ áp dụng được với những bảng đặc thù cụ thể mà thôi.
Anh có thể thực hiện trên file rồi gửi lên được không ah, em đã làm như anh hướng dẫn nhưng không được.

SA_DQ
04-12-09, 03:22 PM
Anh có thể thực hiện trên file rồi gửi lên được không ah, em đã làm như anh hướng dẫn nhưng không được.

Lí do sửa: Trước mình tính tổng các ô có màu tại cột 'Tồn' (?)
Giờ thỉ đúng cho cả 2 cột luôn:


Option Explicit
Function SumColorCells(LookUpRange As Range) As Double
Dim Clls As Range
For Each Clls In LookUpRange
With Clls
If .Offset(, Range("Tong").Column - LookUpRange.Column).Value = Range("Tong").Value Then _
SumColorCells = SumColorCells + .Value
End With
Next Clls
End Function

oanhson
04-12-09, 03:37 PM
Em chép code rùi cho chạy trên file khác nhưng không chạy được, Mọi người hướng dẫn em cách chép code nhé. em cám ơn...

sealand
04-12-09, 03:47 PM
Bạn thử hàm này xem sao


Function SumColor(rg As Range) As Double
For Each it In rg
If it.Interior.ColorIndex <> -4142 Then SumColor = SumColor + it
Next
End FunctionKhi đổi màu xong bạn nhấn F9 để tính toán lại nhé. Mình thêm Application.Volatile những hàm vẫn không tự cập nhật ngay lập tức

HYen17
04-12-09, 04:00 PM
Em chép code rùi cho chạy trên file khác nhưng không chạy được, Mọi người hướng dẫn em cách chép code nhé. em cám ơn...

Còn việc liên quan ít nhiều đến hàm, đó là gán 1 ô có chứa 'Tổng' có tên là 'Tong'

:-=


To Đất Cảng thân mến:

Hình như với các ô CF thì trò này chưa được ở e2K3

oanhson
04-12-09, 04:53 PM
Bạn thử hàm này xem sao


Function SumColor(rg As Range) As Double
For Each it In rg
If it.Interior.ColorIndex <> -4142 Then SumColor = SumColor + it
Next
End FunctionKhi đổi màu xong bạn nhấn F9 để tính toán lại nhé. Mình thêm Application.Volatile những hàm vẫn không tự cập nhật ngay lập tức
Mình làm theo cách này rất tốt và không cần phải nhấn F9 vẫn tự động tính lại mà. Cám ơn bạn rất nhiều..--=0

oanhson
04-12-09, 08:48 PM
Hic hic, mình dùng CF nên chỉ bôi màu được chữ thôi nên công thức cho kết quả "=0", phải làm sao bjờ các bác ui...

oanhson
05-12-09, 10:55 AM
Lí do sửa: Trước mình tính tổng các ô có màu tại cột 'Tồn' (?)
Giờ thỉ đúng cho cả 2 cột luôn:


Option Explicit
Function SumColorCells(LookUpRange As Range) As Double
Dim Clls As Range
For Each Clls In LookUpRange
With Clls
If .Offset(, Range("Tong").Column - LookUpRange.Column).Value = Range("Tong").Value Then _
SumColorCells = SumColorCells + .Value
End With
Next Clls
End Function
Cách của Bác rất hay,cám ơn bác rất nhiều...

lammtv
27-03-10, 06:59 AM
Nhờ các bạn giúp mình bài tập tính tổng các số theo màu này với!Mình có ghi chi tiết trong file Excel!
Cảm ơn các bạn nhiều!

Chu.cuoi92
27-03-10, 08:10 AM
Nhờ các bạn giúp mình bài tập tính tổng các số theo màu này với!Mình có ghi chi tiết trong file Excel!
Cảm ơn các bạn nhiều!
Bạn xem bài nhé! mình làm bằng cả 2 cách luôn!

lammtv
27-03-10, 09:41 AM
Bạn xem bài nhé! mình làm bằng cả 2 cách luôn!
thành thật là cảm ơn bạn rất nhiều!nhờ bạn bớt chút thời gian giải thích dùm mình 2 cách đó 1 cách chi tiết nữa dc ko vậy?
Làm phiền bạn nhé!
Thanks!

nmhanh
17-05-10, 01:39 PM
Chào các anh chị em,

Xin nhờ các anh chị em giúp tôi tính tổng dữ liệu theo:

- Điều kiện ngày tháng.

hoặc

- Điều kiện màu của ô chứa dữ liệu.

Tôi gửi kèm theo file minh hoạ ở đây.

Rất mong sớm nhận được sữ giúp đỡ.

Xin cảm ơn.

Hai Lúa Miền Tây
17-05-10, 02:05 PM
Chào các anh chị em,

Xin nhờ các anh chị em giúp tôi tính tổng dữ liệu theo:

- Điều kiện ngày tháng.

hoặc

- Điều kiện màu của ô chứa dữ liệu.

Tôi gửi kèm theo file minh hoạ ở đây.

Rất mong sớm nhận được sữ giúp đỡ.

Xin cảm ơn.

Câu 1: Dùng hàm sau

E2=SUMPRODUCT((WEEKDAY($A$2:$A$32)=F2)*($B$2:$B$32 )) -->Kéo xuống

Lưu ý là phải tham chiếu đến cell phụ theo ngày trong tuần, Thứ 2=2 --> Thứ 7=7, Chủ Nhật =1

Câu 2: Dùng code sau:


Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult

End Function

Cú Pháp:

Cộng Tổng =ColorFunction(CellChuaDKMau,VungCanCong,True) Nếu muốn đếm thay True=False

Lê Duy Thương
17-05-10, 02:14 PM
Chào các anh chị em,

Xin nhờ các anh chị em giúp tôi tính tổng dữ liệu theo:

- Điều kiện ngày tháng.

hoặc

- Điều kiện màu của ô chứa dữ liệu.

Tôi gửi kèm theo file minh hoạ ở đây.

Rất mong sớm nhận được sữ giúp đỡ.

Xin cảm ơn.
công thức đếm người tham dự ngày
=SUM(IF((WEEKDAY($A$2:$A$32,1)=1),$B$2:$B$32))
=SUM(IF((WEEKDAY($A$2:$A$32,1)=2),$B$2:$B$32))
=SUM(IF((WEEKDAY($A$2:$A$32,1)=7),$B$2:$B$32))
1=cn, 2= thứ 2, 3=thứ 3
nhớ bấm ctrl+shifl+enter nhé
còn sum theo màu thì công thức tôi không biết

concogia
17-05-10, 03:05 PM
Chào các anh chị em,

Xin nhờ các anh chị em giúp tôi tính tổng dữ liệu theo:
- Điều kiện ngày tháng.
hoặc
- Điều kiện màu của ô chứa dữ liệu.
Tôi gửi kèm theo file minh hoạ ở đây.
Rất mong sớm nhận được sữ giúp đỡ.
Xin cảm ơn.
Xin gởi một cách giải
câu 2:


Public Function tongmau(vung, mau As Range) As Long
Dim i, kq As Long
i = mau.Interior.ColorIndex
For Each mau In vung
If mau.Interior.ColorIndex = i Then kq = kq + mau
Next
tongmau = kq
End Function
Cú pháp: =tongmau(vung,mau)
vung: là vùng muốn tính tổng, trong bài là B2:B32
màu: chọn bất cứ cell nào có màu muốn tính tổng

Hieu07061984
05-06-10, 08:18 AM
Câu 1: thì mình có thể dùng hàm Sumif hoặc dùng mảng.
Còn câu 2 thì mình bó tay bởi vì không rành về Macro.

dat_butmuc
05-06-10, 09:31 AM
Chào các anh chị em,

Xin nhờ các anh chị em giúp tôi tính tổng dữ liệu theo:

- Điều kiện ngày tháng.

hoặc

- Điều kiện màu của ô chứa dữ liệu.

Tôi gửi kèm theo file minh hoạ ở đây.

Rất mong sớm nhận được sữ giúp đỡ.

Xin cảm ơn.

1. Điều kiện ngày tháng (với dữ liệu sẵn có như của bạn thì chỉ cần 1 công thức & không cần cột phụ), tại dòng 2 dùng cái này rồi fill xuống:

=SUMPRODUCT((LEFT(TEXT($A$2:$A$32,"[$-409A]ddd"),3)=D2)*($B$2:$B$32))

2. Điều kiện theo màu (bạn đang dùng phiên bản >= Excel 2007 thì có thể hỗ trợ tính tổng theo màu được rồi - không cần VBA hay Macro gì hết)
Dùng Filter dòng tiêu đề, dùng hàm =SUBTOTAL(109,B2:B33) ở dưới
Bạn vào từng Filter by color -> Chọn Filter by Cell color sẽ ra kết quả như mong muốn !

SA_DQ
05-06-10, 10:18 AM
2. Điều kiện theo màu (bạn đang dùng phiên bản >= Excel 2007 thì có thể hỗ trợ tính tổng theo màu được rồi - không cần VBA hay Macro gì hết)
Dùng Filter dòng tiêu đề, dùng hàm =SUBTOTAL(109,B2:B33) ở dưới
Bạn vào từng Filter by color -> Chọn Filter by Cell color sẽ ra kết quả như mong muốn !

Nếu mảng ô màu được tô bỡi CF thì trong Excel 2K3 khó vô chừng; Còn trong E2K7 trở lên có dễ không vậy, bạn?

dat_butmuc
05-06-10, 10:29 AM
Nếu mảng ô màu được tô bỡi CF thì trong Excel 2K3 khó vô chừng; Còn trong E2K7 trở lên có dễ không vậy, bạn?


Bác, tôi chưa test cái này cho E2K3 !
Còn E2K7 trở lên thì cũng không biết dễ hay không nữa, thực sự là tôi chẳng mấy khi tính tổng hay làm gì đó gắn với công thức hay điều kiện bằng màu sắc => không thực tế !
Trở lại với trường hợp yêu cầu trong Topic này, với chừng ấy điều kiện và gói gọn như vậy nên góp ý với tác giả cái sẵn có dùng được cho nó đơn giản !

Những cái này, Bác phải chỉ cho Em mới đúng chứ, nhỉ !

SA_DQ
05-06-10, 10:57 AM
Những cái này, Bác phải chỉ cho Em mới đúng chứ, nhỉ !

Nhưng chưa có nhu cầu nạp Ex 2K7 nên hỏi để biết thêm thôi.

Vả lại mình cũng đề fòng trưồng hợp tác giả tô màu các ô bằng CF mà chưa tỏ hết í, thì uổng công một số người!

Đúng là tính hay đếm tổng các ô tô màu là thực tế, chỉ khi các ô đó được tô bỡi CF mà thôi; Nhưng cái này hơi cay & chua lắm thay!

Dù gì cũng rất chân thành cảm ơn bạn nhiều!