Tính tổng nhiều điều kiện? (1 người xem)
- Thread starter củ lạc
- Ngày gửi
Người dùng đang xem chủ đề này
quocgiacan
-⍟-
- Tham gia
- 22/3/08
- Bài viết
- 3,392
- Được thích
- 5,910
- Giới tính
- Nam
Góp thêm 2 công thức khác:
Mã:
K6=SUMPRODUCT((TEXT($D$6:$D$22,"mm/yyyy")=TEXT($J6,"mm/yyyy"))*($E$6:$E$22=K$5),($F$6:$F$22))
Hoặc K6=SUMPRODUCT((EOMONTH(OFFSET($D$6,ROW($1:$17)-1,),-1)+1=$J6)*($E$6:$E$22=K$5),($F$6:$F$22))Chúc bạn ngày vui.
befaint
|||||||||||||
- Tham gia
- 6/1/11
- Bài viết
- 14,601
- Được thích
- 19,843
Anh xét cả năm...
Em cố theo anh. he he.
Mã:
K6=SUMPRODUCT((MONTH($D$6:$D$22)=MONTH($J6))*(YEAR($D$6:$D$22)=YEAR($J6))*($E$6:$E$22=K$5),$F$6:$F$22)quocgiacan
-⍟-
- Tham gia
- 22/3/08
- Bài viết
- 3,392
- Được thích
- 5,910
- Giới tính
- Nam
Đúng vậy em, nhiều khi phòng ngừa chủ thớt gõ: J6= 01/01/2016, hoặc J7= 01/02/2018 anh em mình sẽ
Khà khà khà.
Chúc em ngày vui.
befaint
|||||||||||||
- Tham gia
- 6/1/11
- Bài viết
- 14,601
- Được thích
- 19,843
Đúng vậy em, nhiều khi phòng ngừa chủ thớt gõ: J6= 01/01/2016, hoặc J7= 01/02/2018 anh em mình sẽ
Khà khà khà.
Chúc em ngày vui.
Chủ thớt yêu cầu tới đâu thì làm tới đó. Làm thừa chưa chắc đã đúng ý ạ.
Chúc anh ngày vui!
quocgiacan
-⍟-
- Tham gia
- 22/3/08
- Bài viết
- 3,392
- Được thích
- 5,910
- Giới tính
- Nam
Thì bởi!Chủ thớt yêu cầu tới đâu thì làm tới đó. Làm thừa chưa chắc đã đúng ý ạ.
Chúc anh ngày vui!
Có mấy bài anh lo xa quá, nhưng lại không đúng ý chủ thớt, y như em nói vậy! thiệt tình đôi lúc cũng khổ lắm lắm...
Nhưng tại bản tính của anh nghĩ "Cẩn tắc vô ưu", cẩn thận giờ thì không lo sau này.
Chúc em ngày thiệt vui.
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Đúng vậy em, nhiều khi phòng ngừa chủ thớt gõ: J6= 01/01/2016, hoặc J7= 01/02/2018 anh em mình sẽ
Khà khà khà.
Chúc em ngày vui.
Nếu xét tháng năm thì SUMIFS() nhìn cũng dễ hiểu:
PHP:
K6=SUMIFS($F$6:$F$22;$E$6:$E$22;K$5;$D$6:$D$22;">="&$J6;$D$6:$D$22;"<="& DATE(YEAR($J6);MONTH($J6)+1;0))AnhPhong411
Thành viên thường trực
- Tham gia
- 29/7/15
- Bài viết
- 343
- Được thích
- 84
- Nghề nghiệp
- Nhân viên kinh doanh các phần mềm doanh nghiệp và
Xin được góp vui cùng mọi người công thức mảng:
=SUM(IF(MONTH($D$6:$D$22)=MONTH($J6),IF($E$6:$E$22=K$5,$F$6:$F$22,0),0))
note=> Nhấn CTRL+SHIFT+ENTER nhé
quocgiacan
-⍟-
- Tham gia
- 22/3/08
- Bài viết
- 3,392
- Được thích
- 5,910
- Giới tính
- Nam
Khoái có anh Ba Tê góp vui. Khà khà.
Cái đoạn cuối DATE(YEAR($J6);MONTH($J6)+1;0) chắc hiểu: vầy EDATE($J6;1)-1 hoặc EOMONTH($J6,0), thì đúng ý anh không!
Chúc anh ngày vui.
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Khoái có anh Ba Tê góp vui. Khà khà.
Cái đoạn cuối DATE(YEAR($J6);MONTH($J6)+1;0) chắc hiểu: vầy EDATE($J6;1)-1 hoặc EOMONTH($J6,0), thì đúng ý anh không!
Chúc anh ngày vui.
Đúng đó, nhưng Ex 2003 thì không xài được. Nếu 2007 về sau thì tìm hàm nào đó thay cho gọn.
Không thêm cột phụ ạ.
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Bạn dùng CT này tại E6:
PHP:
E6=SUMPRODUCT((Cong!$E$4:$E$15=D6)*SUMIF(Dulieu!$C$4:$C$6,Cong!$C$4:$C$15,Dulieu!$D$4:$D$6)*Cong!$D$4:$D$15)eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Không có gì bạn!!!!!!!!!Cảm ơn eke_rula nhé.
eke_rula ơi, còn công thức nào khác không,
công thức này sum ở mảng dữ liệu vài chục nghìn dòng máy tính rít lên như máy cày bạn ah.
%Cacualation trên thanh trạng thái mãi mới lên được 1 tý. chạy từ nãy giờ mới đc 11% bạn ơi.
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Không dùng cột phụ thì dùng ct mảng, ct mảng mấy chục ngàn dòng chạy chậm lắm, bạn dùng cột phụ bên sheet Công dùng Vlookup()* , rồi sumif ben sheet tổng cộng sẽ nhẹ hơn nhiều đấy!!!eke_rula ơi, còn công thức nào khác không,
công thức này sum ở mảng dữ liệu vài chục nghìn dòng máy tính rít lên như máy cày bạn ah.
%Cacualation trên thanh trạng thái mãi mới lên được 1 tý. chạy từ nãy giờ mới đc 11% bạn ơi.
Cảm ơn bạn, mới đầu mình cũng định như vậy nhưng không lường đến vấn đề này.
OK, muộn rồi ngủ thôi bạn ơi.
Cảm ơn bạn nhiều.
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Bạn đổi sang công thức mảng này xem, chạy nhanh hơn không!!!
Mã:
=SUM(IF(Cong!$E$4:$E$15=D6,SUMIF(Dulieu!$C$4:$C$6,Cong!$C$4:$C$15,Dulieu!$D$4:$D$6)*Cong!$D$4:$D$15))Cảm ơn eke_rula
nhanh hơn 1 tý xíu bạn ah chắc cũng không còn cách nào khác là sử dụng cột phụ vlookup bên sheet cong thôi.
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Yêu cầu "Tính tổng ô J20 thỏa mãn các điều kiện tại cột M và N"? Cột N có cái gì?
Rõ là "không tôn trọng" bài viết của chính mình.
Hàm tự tạo:
PHP:
Public Function SumIf_GPE(Rng As Range, RTen As Range, Rlop As Range) As Double
Dim sArr(), R1 As Range, R2 As Range, I As Long, J As Long
sArr = Rng.Value
With CreateObject("Scripting.Dictionary")
For Each R1 In RTen
For Each R2 In Rlop
.Item(R1.Value & "#" & R2.Value) = ""
Next R2
Next R1
For I = 1 To UBound(sArr)
If .Exists(sArr(I, 1) & "#" & sArr(I, 2)) Then SumIf_GPE = SumIf_GPE + sArr(I, 3)
Next I
End With
End Function
PHP:
=SumIf_GPE(C5:E20;L6:L8;M6:M7)Xin lỗi Ba Tê, tôi nhìn nhầm !
Cụ thể vùng điều kiện trong vùng "L6:L8","M6:M7"
Cảm ơn bạn đã giúp đỡ, kết quả đúng như tôi mong muốn rồi nhưng bài này có cách nào giải quyết được bằng công thức không bạn?
Lần chỉnh sửa cuối:
quocgiacan
-⍟-
- Tham gia
- 22/3/08
- Bài viết
- 3,392
- Được thích
- 5,910
- Giới tính
- Nam
Như anh Ba Tê (thành viên kỳ cựu) hướng dẫn bạn, và qua những bài phía trên ma eke_rula gửi cho bạn, tôi có gợi ý bạn nên tìm hiểu kỹ các công thức, nghiệm rồi sẽ áp dụng được cho sự thay đổi như yêu cầu mới này.
Gợi ý công thức: SUMPRODUCT(SUMIFS(.............))
Chúc bạn ngày vui.
Cảm ơn bạn, tôi cũng loay hoay mãi cả buổi chiều nay mà chưa đc .
Mong bạn chỉ dẫn thêm cho.
Cảm ơn
Theo gợi ý của bạn tôi đã thử công thức sau nhưng kết quả không chính xác:
J20=SUMPRODUCT(SUMIFS($E$5:$E$20,$C$5:$C$20,$L$6:$L$8,$D$5:$D$20,$M$6:$M$7))
Mong bạn chỉ dẫn thêm.
Cảm ơn.
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Bài này giải bằng mảng 2 chiều bạn phải thêm Tranpose thì CT mói hiểu được, bạn dùng CT mảng sau:
PHP:
=SUM(SUMIFS(E5:E20,C5:C20,L6:L8,D5:D20,TRANSPOSE(M6:M7)))Cảm ơn eke_rula
Kết quả chuẩn rồi bạn ơi, bạn có thể giải thích cho mình hiểu thêm tại sao lại TRANSPOSE(M6:M7), mà không TRANSPOSE(L6:L8) cùng nữa, mình thấy đều là 2 mảng điều kiện tương tự như nhau.
Nếu đổi lại TRANSPOSE(M6:M7) thành TRANSPOSE(L6:L8) thì có được không bạn mình đã thử thấy nó vẫn trả kết quả đúng bạn ah.
Cảm ơn eke_rula thêm một lần nữa
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Được bạn ạ, một trong 2 cái dùng Tranpose là được!!!
Không dùng Tranpose thì nó đang là mảng dọc, dùng Tranpose nó sẽ chuyển mảng dọc thành mảng ngang, bài này mình nói là phải dùng mảng 2 chiều để giải, nên ct phải có cả mảng ngang và mảng dọc mới tạo được mảng 2 chiều, dùng cả 2 tranpose hoặc cả 2 không dùng transpose thì giống tương tự công thức của bạn làm ở trên sẽ bị lỗi, vì cùng đều là mảng ngang hoặc mảng dọc!!!
Bạn có thể đọc bài mảng 2 chiều ở đây mình có ví dụ 1 bài tương tự như bài có bạn, và giải thích:
http://www.giaiphapexcel.com/forum/showthread.php?122685-Đố-vui-về-công-thức-mảng-2-chiều!!/page6
Bài #53, khúc nói về mảnh 2 chiều!!!
Hi vọng giúp được bạn!!!
Lần chỉnh sửa cuối:
Rất chu đáo và chi tiết, tôi sẽ cố gắng.
eke_rula cho mình hỏi thêm đối với bài toán có dấu hiệu nhận biết như thé nào nào thì dùng mảng 2 chiều?
Cảm ơn bạn nhiều.
Lần chỉnh sửa cuối:
eke_rula
Thành viên tích cực
- Tham gia
- 12/11/16
- Bài viết
- 1,076
- Được thích
- 1,245
Bạn đọc cái link mình gửi đi!!!
OK bạn, mình đang đọc, mình xem bài 53 choáng quá nên mình đọc lại từ bài 1, không ngờ chủ topic lại là bạn, khâm phục.
Đố vui có khác đọc đến bài 17 mà cười nẫu cả ruột:
http://www.giaiphapexcel.com/forum/...-công-thức-mảng-2-chiều!!&p=768060#post768060
Ba Tê
Cạo Rồi Khỏi Gội
- Tham gia
- 5/5/09
- Bài viết
- 12,123
- Được thích
- 17,590
- Giới tính
- Nam
Thử công thức này.
Nếu cột D chỉ trong 1 năm:
PHP:
L6=SUMPRODUCT((MONTH($D$6:$D$22)=MONTH($K6))*($E$6:$E$22=L$5)*($F$6:$I$22))
PHP:
L6=SUMPRODUCT((MONTH($D$6:$D$22)=MONTH($K6))*(YEAR($D$6:$D$22)=YEAR($K6))*($E$6:$E$22=L$5)*($F$6:$I$22))Cảm ơn Ba Tê đã giúp đỡ:
Tôi xin lỗi vì chưa nêu rõ vấn đề nên công thức trên của bạn tôi chưa thể tự sửa theo ý mình được, tôi muốn sử dụng công thức giống như bài 8 của bạn:
http://www.giaiphapexcel.com/forum/...Tính-tổng-nhiều-điều-kiện&p=767803#post767803
Để có thể lấy số liệu từ 1 thời điểm đến 1 thời điểm.
Bạn xem lại file kèm và giúp tôi nhé.
Cảm ơn
theo dữ liệu file của bạn
Mã:
M6 =SUMPRODUCT(($E$6:$E$22=M$5)*($D$6:$D$22>=$K6)*($D$6:$D$22<=EOMONTH($L6,0))*($F$6:$F$22+$G$6:$G$22+$H$6:$H$22+$I$6:$I$22))Bài viết mới nhất
- Question
- Question
- Question