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

[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