Đếm và tính tổng 1 giá trị trong mảng với điều kiện (8 người xem)

[Sapa2016]

Dear Anh chị,
Em có 1 bài như này cần nhờ anh chị giúp ạ.
E có 1 file như em đính kèm dưới đây . Nhờ anh chị chỉ cho em xem làm cách nào tính được với ạ.

 

[quocgiacan]

Dear Anh chị,
Em có 1 bài như này cần nhờ anh chị giúp ạ.
E có 1 file như em đính kèm dưới đây . Nhờ anh chị chỉ cho em xem làm cách nào tính được với ạ.

Thử:

H4=IF($A4<>$A3,SUMPRODUCT(($A$4:$A$100=$A4)*(LEFT($B$4:$G$100)="A")),0)
Hoặc:
H4=IF($A4<>$A3,SUMPRODUCT(COUNTIFS(OFFSET($B$4:$B$100,,{0,2,4}),"A*",$A$4:$A$100,$A4)),0)
I4=IF($A4<>$A3,SUMPRODUCT(SUMIFS(OFFSET($C$4:$C$100,,{0,2,4}),OFFSET($B$4:$B$100,,{0,2,4}),"A*",$A$4:$A$100,$A4)),0)

Enter, rồi fill cả hai xuống.

Chúc bạn ngày vui.

 

[huonglien1901]

Thử:

H4=IF($A4<>$A3,SUMPRODUCT(($A$4:$A$100=$A4)*(LEFT($B$4:$G$100)="A")),0)
Hoặc:
H4=IF($A4<>$A3,SUMPRODUCT(COUNTIFS(OFFSET($B$4:$B$100,,{0,2,4}),"A*",$A$4:$A$100,$A4)),0)
I4=IF($A4<>$A3,SUMPRODUCT(SUMIFS(OFFSET($C$4:$C$100,,{0,2,4}),OFFSET($B$4:$B$100,,{0,2,4}),"A*",$A$4:$A$100,$A4)),0)

Enter, rồi fill cả hai xuống.

Chúc bạn ngày vui.

Anh Quốc giải thích giúp em dòng này với!

{0,2,4}

dựa vào đâu để ra mảng đó Anh!

Em cảm ơn Anh nhiều！

 

[quocgiacan]

Anh Quốc giải thích giúp em dòng này với!

{0,2,4}

dựa vào đâu để ra mảng đó Anh!

Em cảm ơn Anh nhiều！

Em xem hàm dưới đây, và tập tính thử: nếu 0 thì sao? nếu 2 thì sao? và nếu 4 thì sao?
=OFFSET($C$4:$C$100,,{0,2,4})

Chúc em ngày vui.

 

[huonglien1901]

Em xem hàm dưới đây, và tập tính thử: nếu 0 thì sao? nếu 2 thì sao? và nếu 4 thì sao?
=OFFSET($C$4:$C$100,,{0,2,4})

Chúc em ngày vui.

Dạ, em hiểu rồi Anh ơi,

Em cảm ơn Anh nhiều!
Chúc Anh ngày vui!

 

[Sapa2016]

E cam ơn anh chị đã hướng dẫn ạ. Nhưng em vẫn có chút thắc mắc nhờ anh chị chỉ giúp em ạ.
Nếu tính dùng sumproduct thì em hiểu nhưng nếu hàm có chưa offset em vẫn chưa hiểu chỗ mảng "{0,2,4}" (theo em hiểu đây là colums của offset đúng ko ạ).
Vậy sao mình lại có mảng này ạ?
Anh chị chỉ giúp em với nhé.

 

[quocgiacan]

Dạ, em hiểu rồi Anh ơi,

Em cảm ơn Anh nhiều!
Chúc Anh ngày vui!

Kìa! em hiểu thì bi giờ chỉ lại cho bạn thử xem.

Chúc em ngày vui

 

[huonglien1901]

E cam ơn anh chị đã hướng dẫn ạ. Nhưng em vẫn có chút thắc mắc nhờ anh chị chỉ giúp em ạ.
Nếu tính dùng sumproduct thì em hiểu nhưng nếu hàm có chưa offset em vẫn chưa hiểu chỗ mảng "{0,2,4}" (theo em hiểu đây là colums của offset đúng ko ạ).
Vậy sao mình lại có mảng này ạ?
Anh chị chỉ giúp em với nhé.

Cho em mạng phép Anh @quocgiacan xin trả lời cho bạn.
Đây chính là cách đếm thôi bạn, để tạo ra mảng đó, bạn để ý cột Bag nó nằm ở vì trí 1,3,5 nên khi dùng offset sẽ nhảy vị trị đến các ví trị đó,
Khi muốn từ vùng tham chiếu &B&4:&B&100 bạn muốn nhảy bao nhiêu cột thì nhảy,
Bạn nhảy qua đúng chính nó là số 0(tương ứng là vị trí số 1)
Bạn nhảy qua 2 cột tương ứng vị trí thứ 3
Bạn nhảy qua 4 cột tương ứng vị trí thứ 5
Nên sẽ có hàm mảng {0,2,4} đó.
Theo sự hiểu biết của em, em chỉ giải thích chừng đó. Nếu có gì sai mong Anh quốc tha lỗi cho em. Em có thiếu sót gì nhờ Anh bổ sung thêm.

Chúc Anh ngày vui!

 

[quocgiacan]

E cam ơn anh chị đã hướng dẫn ạ. Nhưng em vẫn có chút thắc mắc nhờ anh chị chỉ giúp em ạ.
Nếu tính dùng sumproduct thì em hiểu nhưng nếu hàm có chưa offset em vẫn chưa hiểu chỗ mảng "{0,2,4}" (theo em hiểu đây là colums của offset đúng ko ạ).
Vậy sao mình lại có mảng này ạ?
Anh chị chỉ giúp em với nhé.

Nói chứ, công thức này hơi 'quái quái' chút nha, bạn phải hơi trừu tượng chút như lúc học hình học không gian lớp 11 vậy.
H4=IF($A4<>$A3, SUMPRODUCT( COUNTIFS(OFFSET($B$4:$B$100,,{0,2,4}),"A*",$A$4:$A$100,$A4) ) ,0)

1. COUNTIFS( OFFSET($B$4:$B$100,,{0,2,4}), "A*" ,$A$4:$A$100,$A4)
   • Nếu tính từ B4:B100 là cột chuẩn mốc:
     • với 0 kết quả là Vùng: B4:B100
     • với 2 kết quả là Vùng: D4: D100
       
     • với 4 kết quả là Vùng: F4:F100
   • Đó là những Vùng chứa mã mà bạn cần lấy chỉ các ký tự đầu là "A*".
   • Cứ hình dung như nó nhảy như 'kangaroo' qua từng cột một để so khớp xem các Vùng liệt kê trên có thỏa điều kiện hay không.
   • Về phần: (.....$A$4:$A$100,$A4) là điều kiện so khớp ngày đang xét.
   • Kết quả của hàm COUNTIFS(...) này sẽ ra 1 mảng chứa số liệu đếm được trên ba cột 'vừa nhảy' đó, Ví dụ: {2,3,3} cho ngày 15/08/2017 hoặc {2,4,3} cho ngày 16/08/2017
2. SUMPRODUCT(..Countifs(.trên.)..) làm nhiệm vụ cộng lại các mảng vừa tìm được.

Chúc bạn ngày vui.

 

[Sapa2016]

À đúng rồi. giờ em hiểu rồi ạ. em cảm ơn anh chị nhiều nhé. Hihi.

 

[ndu96081631]

"Buồn tình" thử chơi bài này bằng COUNTIF và SUMIF

=(A3<>A4)*COUNTIF(OFFSET($B4:$F4,,,COUNTIF($A$4:$A$100,A4)),"A*")

và

=(A3<>A4)*SUMIF(OFFSET($B4:$F4,,,COUNTIF($A$4:$A$100,A4)),"A*",$C4:$G$100)

Một đặc điểm của SUMIF mà ít người biết đến (chỗ $C4:$G$100 ấy)

 

[ndu96081631]

Một đặc điểm của SUMIF mà ít người biết đến (chỗ $C4:$G$100 ấy)

Ai biết chính xác mình đang muốn nói đến ĐẶC ĐIỂM nào sẽ thưởng... 1 xị

 

[huonglien1901]

Ai biết chính xác mình đang muốn nói đến ĐẶC ĐIỂM nào sẽ thưởng... 1 xị

Em trả lời sai Thầy đừng la em nhé!
Chắc Thầy đang nói đến vùng tính tổng dựa vào vùng điều kiện đó Thầy.
Không biết đúng ý Thầy không nữa.

 

[ndu96081631]

Chắc Thầy đang nói đến vùng tính tổng dựa vào vùng điều kiện đó Thầy.
.

Bạn nói rõ hơn được không?

 

[quocgiacan]

Ai biết chính xác mình đang muốn nói đến ĐẶC ĐIỂM nào sẽ thưởng... 1 xị

1 xị 'chuối hột' chia 2 người chắc hổng đủ thầy ơi!
Đặc điểm của nó:

1. Nó sẽ tự đồng bộ với vùng so trong đối số 1 của SUMIF(), cho dù lúc ban đầu Vùng đưa vào công thức được chọn nhiều hơn.
2. Nó chỉ cộng những giá trị số, xem các chuỗi =0
3. Điều kiện thỏa nếu là Mảng 2 chiều, sau khi đồng bộ Vùng, nó sẽ cộng những giá trị tương ứng thành phần thỏa điều kiện trong mảng, do đó trong công thức phải có sự lệch cột.

Chúc thầy ngày vui.

 

[ndu96081631]

1 xị 'chuối hột' chia 2 người chắc hổng đủ thầy ơi!
Đặc điểm của nó:

1. Nó sẽ tự đồng bộ với vùng so trong đối số 1 của SUMIF(), cho dù lúc ban đầu Vùng đưa vào công thức được chọn nhiều hơn.
2. Nó chỉ cộng những giá trị số, xem các chuỗi =0
3. Điều kiện thỏa nếu là Mảng 2 chiều, sau khi đồng bộ Vùng, nó sẽ cộng những giá trị tương ứng thành phần thỏa điều kiện trong mảng, do đó trong công thức phải có sự lệch cột.

Chúc thầy ngày vui.

Mời bạn hiền về Biên Hòa nhận 1 xị nhé!
--------------
Mình xin nói rõ chỗ này một chút:
Cú pháp hàm SUMIF là
SUMIF(range, criteria, [sum_range])
Theo quy định thì range và sum_range phải cùng kích thước. Tuy nhiên, cho dù bạn gõ nhầm hay cố tình khiến cho sum_range khác kích thước với range thì anh Bill cũng sẽ chỉnh lại kích thước sum_range sao cho bằng với range (anh Bill lấy đối số range làm chuẩn). Ví dụ ta gõ
=SUMIF(A1:A10, "điều kiện", C1:C2)
thì kết quả cũng sẽ tương đương với
=SUMIF(A1:A10, "điều kiện", C1:C10)
Vậy:
- Nếu ta vô ý gõ nhầm, sẽ dẫn đến sai sót mà ta không phát hiện
- Nhưng nếu ta cố tình thì lại là chuyện khác: có thể áp dụng để rút gọn công thức. Chẳng hạn bài trên, thậm chí có thể viết vầy:

=(A3<>A4)*SUMIF(OFFSET($B4:$F4,,,COUNTIF($A$4:$A$100,A4)),"A*",C4)

Mặc kệ cái sum_range cho nó tự chỉnh
Và đó chính là điểm đặc biệt!

 

[quocgiacan]

Mời bạn hiền về Biên Hòa nhận 1 xị nhé!
--------------
Mình xin nói rõ chỗ này một chút:
Cú pháp hàm SUMIF là
SUMIF(range, criteria, [sum_range])
Theo quy định thì range và sum_range phải cùng kích thước. Tuy nhiên, cho dù bạn gõ nhầm hay cố tình khiến cho sum_range khác kích thước với range thì anh Bill cũng sẽ chỉnh lại kích thước sum_range sao cho bằng với range (anh Bill lấy đối số range làm chuẩn). Ví dụ ta gõ
=SUMIF(A1:A10, "điều kiện", C1:C2)
thì kết quả cũng sẽ tương đương với
=SUMIF(A1:A10, "điều kiện", C1:C10)
Vậy:
- Nếu ta vô ý gõ nhầm, sẽ dẫn đến sai sót mà ta không phát hiện
- Nhưng nếu ta cố tình thì lại là chuyện khác: có thể áp dụng để rút gọn công thức. Chẳng hạn bài trên, thậm chí có thể viết vầy:

=(A3<>A4)*SUMIF(OFFSET($B4:$F4,,,COUNTIF($A$4:$A$100,A4)),"A*",C4)

Mặc kệ cái sum_range cho nó tự chỉnh
Và đó chính là điểm đặc biệt!

Phải là "chiến tướng" lâm cả ngàn trận mới rút ra được nhận xét về sự 'đồng bộ' quá ư đặc biệt này của họ Sumif(), chứ quả thật là anh Bill và lính anh ta không bảo ban gì cả trong giới thiệu về hàm Sumif().

Thiệt ngưỡng mộ thầy!

Nhân tiện nói về sự đồng bộ dù các Vùng có sự khập khiễng, thì Lookup(lookup_value, lookup_vector, [result_vector]) cũng là một điển hình thú vị nữa thầy.
Dù rằng trong lời giới thiệu 'result_vector' phải cùng kích thước với 'lookup_vector' , nhưng gần đây giải bài thì thấy không nhất thiết phải như vậy, ví dụ công thức: LOOKUP({2;3;4},ROW(1:100),{"a";"b";"c";"d";"e"}) vẫn cho ra kết quả mảng 1 chiều {"b";"c";"d"}, bất chấp 'hai anh chàng màu xanh' này cao thấp ra sao.

Càng đi sâu vào hàm thấy có nhiều chuyện ngộ nghĩnh rất thú vị!

Cảm ơn về bài học của thầy.

Chúc thầy ngày vui.


(*) Trích giới thiệu Lookup(): https://support.office.com/vi-vn/ar...51d-8251-369d5e3864cb?ui=vi-VN&rs=vi-VN&ad=VN

• result_vector [Tùy chọn]: Phạm vi chỉ chứa một hàng hay một cột. Tham đối result_vector phải có cùng kích cỡ với lookup_vector. Nó phải cùng một kích cỡ.

 

[Sapa2016]

Các anh chị trả lời rôm rả trên topic của em quá. em vẫn còn yếu về apply hàm và công thức excel lắm nên mỗi lần lên đây học được ở các anh chị rất nhiều ạ. E chỉ biết sử dụng đơn lẻ các hàm chứ chưa biết cách kết hợp và linh hoạt chúng. E sẽ học tập dần dần ạ. E cảm ơn anh chị nhé. hihi.

 

[hoanghoai1987]

Em có file này nhờ các anh/ chị giúp đỡ ạ. Em muốn tỉnh tổng của 1 bảng tính thỏa mãn 1 điều kiện trong bảng tính thì làm ntn ạ
Em cảm ơn trước ạ

 

[befaint]

Em có file này nhờ các anh/ chị giúp đỡ ạ. Em muốn tỉnh tổng của 1 bảng tính thỏa mãn 1 điều kiện trong bảng tính thì làm ntn ạ
Em cảm ơn trước ạ

D17=SUMPRODUCT(($B$3:$B$14=D$16)*($C$3:$M$14=$C17))
'Copy cho các ô khác.'