Thống kê số lần xuất hiện theo điều kiện (1 người xem)

[Tuan_hcth]

Em chào các anh, chị trên diễn đàn

Nhờ các anh, chị giúp em giải quyết bài toán thống kê như sau: Căn cứ vào dữ liệu Bảng chi tiết (sheet1) làm sao để tại cột H (bảng thống kê) có thể thống kê những nhân viên của tưngf đơn vị xuất hiện từ 3 lần trở lên kể từ ngày 16/8 trở đi ạ.

 

[Lê Hồng Minh83]

Em chào các anh, chị trên diễn đàn

Nhờ các anh, chị giúp em giải quyết bài toán thống kê như sau: Căn cứ vào dữ liệu Bảng chi tiết (sheet1) làm sao để tại cột H (bảng thống kê) có thể thống kê những nhân viên của tưngf đơn vị xuất hiện từ 3 lần trở lên kể từ ngày 16/8 trở đi ạ.

Thế này nó không hay lắm, nhưng có cái xài tạm

=COUNTIFS($D$9:$D$28,">=3",$B$9:$B$28,G4)

 

[hocexcel_1991]

Em chào các anh, chị trên diễn đàn

Nhờ các anh, chị giúp em giải quyết bài toán thống kê như sau: Căn cứ vào dữ liệu Bảng chi tiết (sheet1) làm sao để tại cột H (bảng thống kê) có thể thống kê những nhân viên của tưngf đơn vị xuất hiện từ 3 lần trở lên kể từ ngày 16/8 trở đi ạ.

Thử công thức này đúng không

=SUMPRODUCT(($C$4:$C$28>=DATE(2018,8,16))*($B$4:$B$28=G4)*(COUNTIF(OFFSET($A$3,,,ROW($1:$25)),$A$4:$A$28)>=3))

 

[Tuan_hcth]

Thử công thức này đúng không

=SUMPRODUCT(($C$4:$C$28>=DATE(2018,8,16))*($B$4:$B$28=G4)*(COUNTIF(OFFSET($A$3,,,ROW($1:$25)),$A$4:$A$28)>=3))

Bạn có thể giải thích giúp công thức trên được không. Trong công thức mình thấy có đoạn sử dụng 3 dấu (,) nghĩa là thế nào ạ

 

[hocexcel_1991]

Bạn có thể giải thích giúp công thức trên được không. Trong công thức mình thấy có đoạn sử dụng 3 dấu (,) nghĩa là thế nào ạ

Bạn nghiên cứu hàm offset nhé
https://support.office.com/vi-vn/article/offset-hàm-offset-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

 

[Tuan_hcth]

Thử công thức này đúng không

=SUMPRODUCT(($C$4:$C$28>=DATE(2018,8,16))*($B$4:$B$28=G4)*(COUNTIF(OFFSET($A$3,,,ROW($1:$25)),$A$4:$A$28)>=3))

Giờ thêm một điều kiện về mặt hàng nữa thì công thức sẽ thế nào ạ. Có nghĩa là thống kê những nhân viên xuất hiện từ 3 lần trở lên của một mặt hàng theo từng đơn vị kể từ ngày 16/8 trở đi (File đính kèm).

 

[congnt92]

Giờ thêm một điều kiện về mặt hàng nữa thì công thức sẽ thế nào ạ. Có nghĩa là thống kê những nhân viên xuất hiện từ 3 lần trở lên của một mặt hàng theo từng đơn vị kể từ ngày 16/8 trở đi (File đính kèm).

Bạn thử

I4=IFERROR(INDEX($A$4:$A$28,AGGREGATE(15,6,ROW($1:$25)/((COUNTIF($H4:H4,$A$4:$A$28)=0)*(COUNTIFS($B$4:$B$28,$H4,$C$4:$C$28,"$",$D$4:$D$28,">="&DATE(2018,8,16),$A$4:$A$28,T(OFFSET($A$3,ROW($1:$25),)))>=3)),COLUMNS($I$4:I$4))),"")

Kéo sang phải vài cột.
Còn nếu bạn lại cần thêm yêu cầu gom tất cả tên nhân viên vào 1 ô thì mình chịu, phải dùng vba thôi.

 

[Tuan_hcth]

Bạn thử

I4=IFERROR(INDEX($A$4:$A$28,AGGREGATE(15,6,ROW($1:$25)/((COUNTIF($H4:H4,$A$4:$A$28)=0)*(COUNTIFS($B$4:$B$28,$H4,$C$4:$C$28,"$",$D$4:$D$28,">="&DATE(2018,8,16),$A$4:$A$28,T(OFFSET($A$3,ROW($1:$25),)))>=3)),COLUMNS($I$4:I$4))),"")

Kéo sang phải vài cột.
Còn nếu bạn lại cần thêm yêu cầu gom tất cả tên nhân viên vào 1 ô thì mình chịu, phải dùng vba thôi.

Công thức không cho ra kết quả gì cả bạn à. Yêu cầu của mình là thống kê có bao nhân viên xuất hiện từ 3 lần trở lên ứng với mặt hàng $ theo từng đơn vị (file tại bài # 6). Nhờ bạn kiểm tra lại giúp mình với.

 

[CHAOQUAY]

Giờ thêm một điều kiện về mặt hàng nữa thì công thức sẽ thế nào ạ. Có nghĩa là thống kê những nhân viên xuất hiện từ 3 lần trở lên của một mặt hàng theo từng đơn vị kể từ ngày 16/8 trở đi (File đính kèm).

Dùng thêm cột phụ được không bạn

 

[Tuan_hcth]

Dùng thêm cột phụ được không bạn

Có cách nào không phải dùng cột phụ không ạ

 

[CHAOQUAY]

Có cách nào không phải dùng cột phụ không ạ

Dùng cột phụ thì cũng chỉ đếm được số lượng nhân viên theo yêu cầu thôi. Nếu cần điền tên nhân viên nữa thì phải dùng vba. Công thức trực tiếp thì không biết làm bạn ạ.

 

[Tuan_hcth]

Dùng cột phụ thì cũng chỉ đếm được số lượng nhân viên theo yêu cầu thôi. Nếu cần điền tên nhân viên nữa thì phải dùng vba. Công thức trực tiếp thì không biết làm bạn ạ.

Chỉ cần đếm số lượng thỏa mãn điều kiện thôi bạn, không cần tên nhân viên. Cột phụ thì sẽ thế nào bạn

 

[CHAOQUAY]

Chỉ cần đếm số lượng thỏa mãn điều kiện thôi bạn, không cần tên nhân viên. Cột phụ thì sẽ thế nào bạn

Vậy dung cột phụ nhé

 

[Phan Thế Hiệp]

Có cách nào không phải dùng cột phụ không ạ

Thử:

I4=SUM(N(FREQUENCY(IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$24))>2))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[congnt92]

Công thức không cho ra kết quả gì cả bạn à. Yêu cầu của mình là thống kê có bao nhân viên xuất hiện từ 3 lần trở lên ứng với mặt hàng $ theo từng đơn vị (file tại bài # 6). Nhờ bạn kiểm tra lại giúp mình với.

Ra kết quả mà bạn.
Bạn xem file

Edit:
Xin lỗi bạn, mình hiểu nhầm là hiển thị tên nhân viên.

 

[Tuan_hcth]

Thử:

I4=SUM(N(FREQUENCY(IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$24))>2))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

Cảm ơn bạn. Cho mình hỏi trong công thưc trên "N" là gì vâỵ bạn.

 

[Phan Thế Hiệp]

Cảm ơn bạn. Cho mình hỏi trong công thưc trên "N" là gì vâỵ bạn.

Có chuyên gia giải thích ở đây nè bạn:
https://support.office.com/vi-vn/article/n-hàm-n-a624cad1-3635-4208-b54a-29733d1278c9

Thân

 

[Tuan_hcth]

Có chuyên gia giải thích ở đây nè bạn:
https://support.office.com/vi-vn/article/n-hàm-n-a624cad1-3635-4208-b54a-29733d1278c9

Thân

Cho mình hỏi thêm lài tại sao ROW($1:$24))>2 lại chỉ lớn hơn 2 trong khi yêu cầu là lớn hơn hoặc bằng 3 mà. Và ROW($1:$24)) chỉ tham chiếu đến dòng 24, trong khi dữ liệu có đến dòng 28, trường hợp dữ liệu nhiều hơn thì đoạn này phải sửa thế nào ạ

 

[be_09]

Em chào các anh, chị trên diễn đàn

Nhờ các anh, chị giúp em giải quyết bài toán thống kê như sau: Căn cứ vào dữ liệu Bảng chi tiết (sheet1) làm sao để tại cột H (bảng thống kê) có thể thống kê những nhân viên của tưngf đơn vị xuất hiện từ 3 lần trở lên kể từ ngày 16/8 trở đi ạ.

Ba cái vụ thống kê này sao không dùng PivotTable cho đơn giản.
- Tại C1 chọn ngày cần tính.
- Tại E4 chọn số lượng cần hiển thị.

 

[congnt92]

Cho mình hỏi thêm lài tại sao ROW($1:$24))>2 lại chỉ lớn hơn 2 trong khi yêu cầu là lớn hơn hoặc bằng 3 mà. Và ROW($1:$24)) chỉ tham chiếu đến dòng 24, trong khi dữ liệu có đến dòng 28, trường hợp dữ liệu nhiều hơn thì đoạn này phải sửa thế nào ạ

Thế bạn nghĩ thế nào nếu trong toán học ta có
x thuộc tập số tự nhiên và x > 2
Với x thuộc tập số tự nhiên và x >= 3
Về điều chỉnh row() thì bạn nhắm 1 nhân viên tối đa có thể xuất hiện bao nhiêu lần thì điều chỉnh tăng lên xíu. Hoặc để chắc ăn cho thành ROW($1:$100)) cũng được .
Với dữ liệu của bạn để ROW($1:$4)) cũng được. Bạn tìm hiểu hàm FREQUENCY để hiểu hơn.

 

[Phan Thế Hiệp]

Cho mình hỏi thêm lài tại sao ROW($1:$24))>2 lại chỉ lớn hơn 2 trong khi yêu cầu là lớn hơn hoặc bằng 3 mà. Và ROW($1:$24)) chỉ tham chiếu đến dòng 24, trong khi dữ liệu có đến dòng 28, trường hợp dữ liệu nhiều hơn thì đoạn này phải sửa thế nào ạ

Hàm Frequency() bạn có thể xem như là 1 hàm đếm theo điều kiện (đại khái bạn coi nó là Countif() đi cho dễ hình dung), cách thức nó hoạt động như sau:

1. Đoạn biểu thức điều kiện: ($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4)
   • ($D$4:$D$28>=$I$1): Chọn cột 'Ngày' có các dòng nào thỏa >= 16/08/2018, nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • ($C$4:$C$28=$H$1): Chọn cột 'Mặt hàng' có các dòng nào có ký tự "$", nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • ($B$4:$B$28=$H4): Chọn cột 'Đơn vị' có các dòng nào có đơn vị (vd tại H4 là 'đơn vị 1'), nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • Nhân ba biểu thức trên sẽ tạo 1 Mảng dọc chứa các giá trị thỏa/không thỏa hình dạng như {0;0;0;0;0;1;1;1;0;1;1;0;1....} (Xem cột E file kèm)
2. MATCH($A$4:$A$28,$A$4:$A$28,): Tạo ra 1 Mảng chứa số dòng mà các Mã hàng xuất hiện lần đầu trong cột A, Ví dụ: A4: mã hàng A có Stt= 1, A9: mã hàng A cũng có Stt là 1 (tương tự cho các Mã A phía dưới), A5: mã hàng B có Stt= 2, A10: mã hàng B cũng có Stt là 2 (tương tự cho các Mã B phía dưới)....Tương ứng với biểu thức điều kiện (1.), hễ dòng nào thỏa điều kiện thì lấy "Stt" sau khi Match() trả ra, dòng không thỏa thì bằng FALSE. (Xem cột F file kèm)
3. Kết quả từ hàm =IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)) sẽ cho ra 1 Mảng dọc chứa các 'Stt' dòng của các mã hàng thỏa điều kiện. (Xem cột G file kèm)
4. Hàm ROW(1:24) hoặc bạn có thể thay đổi độ dài tùy hỹ ROW(1:1000) cũng chẳng sao, vì nó có ý nghĩa cung cấp các "giá trị số dòng" mà 'Stt' dòng có thể có từ kết quả của hàm IF() trên. (Xem cột H file kèm)
5. Hàm FREQUENCY( IF( 'biểu thức "3."' ) , ROW(1:24) ) cứ xem như nó đếm tổng thể các 'Stt' dòng, mỗi 'Stt' dòng có bao nhiêu dòng thỏa điều kiện, kể như nó giống như Countif() (Xem cột I và J file kèm)
6. Cuối cùng, lấy kết quả từ Frequency() tạo biểu thức điều kiện: Frequency() >2 (lớn hơn 2 đồng nghĩa từ 3 trở lên), dùng N() để đổi TRUE/FALSE về 1/0, rồi dùng SUM() cộng các kết quả 1/0 đó.(Xem cột K và K2 file kèm)

Thân

 

[Tuan_hcth]

Hàm Frequency() bạn có thể xem như là 1 hàm đếm theo điều kiện (đại khái bạn coi nó là Countif() đi cho dễ hình dung), cách thức nó hoạt động như sau:

1. Đoạn biểu thức điều kiện: ($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4)
   • ($D$4:$D$28>=$I$1): Chọn cột 'Ngày' có các dòng nào thỏa >= 16/08/2018, nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • ($C$4:$C$28=$H$1): Chọn cột 'Mặt hàng' có các dòng nào có ký tự "$", nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • ($B$4:$B$28=$H4): Chọn cột 'Đơn vị' có các dòng nào có đơn vị (vd tại H4 là 'đơn vị 1'), nếu thỏa ra giá trị 1, không thỏa ra giá trị 0.
   • Nhân ba biểu thức trên sẽ tạo 1 Mảng dọc chứa các giá trị thỏa/không thỏa hình dạng như {0;0;0;0;0;1;1;1;0;1;1;0;1....} (Xem cột E file kèm)
2. MATCH($A$4:$A$28,$A$4:$A$28,): Tạo ra 1 Mảng chứa số dòng mà các Mã hàng xuất hiện lần đầu trong cột A, Ví dụ: A4: mã hàng A có Stt= 1, A9: mã hàng A cũng có Stt là 1 (tương tự cho các Mã A phía dưới), A5: mã hàng B có Stt= 2, A10: mã hàng B cũng có Stt là 2 (tương tự cho các Mã B phía dưới)....Tương ứng với biểu thức điều kiện (1.), hễ dòng nào thỏa điều kiện thì lấy "Stt" sau khi Match() trả ra, dòng không thỏa thì bằng FALSE. (Xem cột F file kèm)
3. Kết quả từ hàm =IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)) sẽ cho ra 1 Mảng dọc chứa các 'Stt' dòng của các mã hàng thỏa điều kiện. (Xem cột G file kèm)
4. Hàm ROW(1:24) hoặc bạn có thể thay đổi độ dài tùy hỹ ROW(1:1000) cũng chẳng sao, vì nó có ý nghĩa cung cấp các "giá trị số dòng" mà 'Stt' dòng có thể có từ kết quả của hàm IF() trên. (Xem cột H file kèm)
5. Hàm FREQUENCY( IF( 'biểu thức "3."' ) , ROW(1:24) ) cứ xem như nó đếm tổng thể các 'Stt' dòng, mỗi 'Stt' dòng có bao nhiêu dòng thỏa điều kiện, kể như nó giống như Countif() (Xem cột I và J file kèm)
6. Cuối cùng, lấy kết quả từ Frequency() tạo biểu thức điều kiện: Frequency() >2 (lớn hơn 2 đồng nghĩa từ 3 trở lên), dùng N() để đổi TRUE/FALSE về 1/0, rồi dùng SUM() cộng các kết quả 1/0 đó.(Xem cột K và K2 file kèm)

Thân

Thật sự cảm ơn bạn và mọi người rất nhiều. Mình muốn hỏi thêm một chút giờ điều kiện >=3 thay thành <3, từ 3 đến 5 thì phần công thức điều chỉnh thế nào.

 

[Phan Thế Hiệp]

Thật sự cảm ơn bạn và mọi người rất nhiều. Mình muốn hỏi thêm một chút giờ điều kiện >=3 thay thành <3, từ 3 đến 5 thì phần công thức điều chỉnh thế nào.

Từ điều kiện >2 ( hay >=3) điều chỉnh:

1. Điều kiện <3: thay >2 bằng <3 và thêm một số yếu tố loại giá trị 0

   =SUM(IFERROR(N(1/(1/(FREQUENCY(IF(($D$4:$D$28>=$O$1)*($C$4:$C$28=$N$1)*($B$4:$B$28=$N4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$1000))))<3),""))

2. Điều kiện >=3 đến 5: thì phải dùng thêm hàm MATCH() ở ngoài:

   =SUM(N(MATCH(FREQUENCY(IF(($D$4:$D$28>=$O$1)*($C$4:$C$28=$N$1)*($B$4:$B$28=$N4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$1000)),{0,3,6})=2))

Cả hai đều phải kết thức bằng Ctrl+Shift+Enter

Thân

 

[congnt92]

Thật sự cảm ơn bạn và mọi người rất nhiều. Mình muốn hỏi thêm một chút giờ điều kiện >=3 thay thành <3, từ 3 đến 5 thì phần công thức điều chỉnh thế nào.

từ 3-5 thì bạn thử

=SUM(N(IFERROR(MATCH(FREQUENCY(IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$24)),{3,4,5,6},1)<4,"")))

Và như vậy nếu < 3 (tức là 1-2, không lấy 0) bạn có thể thay thành

=SUM(N(IFERROR(MATCH(FREQUENCY(IF(($D$4:$D$28>=$I$1)*($C$4:$C$28=$H$1)*($B$4:$B$28=$H4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$24)),{1,2,3},1)<3,"")))

nhưng như thế này rắc rối hơn vì mình có thể loại ngay giá trị 0 sẵn trong hàm luôn cũng được.

Edit: tổng quát a-b thì sửa chỗ {1,2,3},1)<3 thành

row((a:(b+1)),1)<b-a+2

 

[Tuan_hcth]

Từ điều kiện >2 ( hay >=3) điều chỉnh:

1. Điều kiện <3: thay >2 bằng <3 và thêm một số yếu tố loại giá trị 0

   =SUM(IFERROR(N(1/(1/(FREQUENCY(IF(($D$4:$D$28>=$O$1)*($C$4:$C$28=$N$1)*($B$4:$B$28=$N4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$1000))))<3),""))

2. Điều kiện >=3 đến 5: thì phải dùng thêm hàm MATCH() ở ngoài:

   =SUM(N(MATCH(FREQUENCY(IF(($D$4:$D$28>=$O$1)*($C$4:$C$28=$N$1)*($B$4:$B$28=$N4),MATCH($A$4:$A$28,$A$4:$A$28,)),ROW($1:$1000)),{0,3,6})=2))

Cả hai đều phải kết thức bằng Ctrl+Shift+Enter

Thân

Nhờ bạn xem giúp, nếu điều kiện chỉ lớn hơn 3 thì công thức sửa lại thế nào vậy. Mình đế > 3, >=3 nhưng không đúng

 

[hue xoan]

Các bác ơi, giải giúp em bài này với ạ, anh muốn đếm số điểm giao theo từng ngày và theo từng kênh khác nhau (bỏ các giá trị địa chỉ trùng)
Em cảm ơn,

 

[Phan Thế Hiệp]

Các bác ơi, giải giúp em bài này với ạ, anh muốn đếm số điểm giao theo từng ngày và theo từng kênh khác nhau (bỏ các giá trị địa chỉ trùng)
Em cảm ơn,

Thử:

D2=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)=1,COUNT(1/(MATCH($B$2:$B$100,IF(($A$2:$A$100=A2)*($C$2:$C$100=C2),$B$2:$B$100),)=ROW($1:$100))),)

Kết thúc bằng Ctrl+Shift+Enter.

Thân