[Nhờ hỗ trợ] Đếm không trùng có nhiều điều kiện

beginning_2311 · 27/6/18

Gửi các a, chị,
E có bảng tính cần đếm khách hàng không trùng theo điều kiện thời gian (từng quý), e đã mò thử theo các công thức countif, frequency các kiểu mà không được.
Nhờ các cao thủ sửa lỗi giúp e ah.
E xin chân thành cảm ơn ah./

ndu96081631 · 27/6/18

beginning_2311 đã viết:
Gửi các a, chị,
E có bảng tính cần đếm khách hàng không trùng theo điều kiện thời gian (từng quý), e đã mò thử theo các công thức countif, frequency các kiểu mà không được.
Nhờ các cao thủ sửa lỗi giúp e ah.
E xin chân thành cảm ơn ah./

Thử công thức này xem

Mã:

=COUNT(1/FREQUENCY(IF((C6:C49>=H2)*(C6:C49<=I2),MATCH(A6:A49,A6:A49,0)),MATCH(A6:A49,A6:A49,0)))

beginning_2311 · 27/6/18

Cảm ơn a. Công thức đúng rồi ah.
Nếu e muốn dùng COUNTIF trong trường hợp này có được không ah? FREQUENCY e dùng không quen lắm ah.

ndu96081631 · 27/6/18

beginning_2311 đã viết:
Cảm ơn a. Công thức đúng rồi ah.
Nếu e muốn dùng COUNTIF trong trường hợp này có được không ah? FREQUENCY e dùng không quen lắm ah.

Tôi thấy FREQUENCY trong trường hợp này dễ nhớ ấy chứ. Cú pháp:

Mã:

=COUNT(1/FREQUENCY(biểu thức điều kiện, biểu thức điều kiện))

vậy thôi

batman1 · 28/6/18

beginning_2311 đã viết:
Cảm ơn a. Công thức đúng rồi ah.
Nếu e muốn dùng COUNTIF trong trường hợp này có được không ah? FREQUENCY e dùng không quen lắm ah.

FREQUENCY là đẹp rồi. Nhưng theo tôi cũng có thể bớt 1 mảng MATCH và không tạo mảng nghịch đảo

Mã:

=SUM(--(FREQUENCY(IF((C6:C49>=H2)*(C6:C49<=I2),MATCH(A6:A49,A6:A49,0)),ROW($1:$50))>0))

beginning_2311 · 28/6/18

batman1 đã viết:
FREQUENCY là đẹp rồi. Nhưng theo tôi cũng có thể bớt 1 mảng MATCH và không tạo mảng nghịch đảo

Mã:

=SUM(--(FREQUENCY(IF((C6:C49>=H2)*(C6:C49<=I2),MATCH(A6:A49,A6:A49,0)),ROW($1:$50))>0))

Cảm ơn bác. Code này e thử rồi nhưng test ở khoảng thời gian khác thì không chính xác ah. Đến giờ vẫn chỉ có cách của bác ndu96081631 là hoạt động hiệu quả ah.

ngoctinh87 · 28/6/18

beginning_2311 đã viết:
Cảm ơn a. Công thức đúng rồi ah.
Nếu e muốn dùng COUNTIF trong trường hợp này có được không ah? FREQUENCY e dùng không quen lắm ah.

Bạn thử dùng công thức này xem sao

Mã:

=SUMPRODUCT(((C6:C49>=H2)*(C6:C49<=I2))*(COUNTIF(OFFSET(A5,,,ROW(1:44),),A6:A49)=0))

excel_lv1.5 · 28/6/18

ngoctinh87 đã viết:
Bạn thử dùng công thức này xem sao

Mã:

=SUMPRODUCT(((C6:C49>=H2)*(C6:C49<=I2))*(COUNTIF(OFFSET(A5,,,ROW(1:44),),A6:A49)=0))

Công thức này chưa đúng đâu bạn, countif thiếu điều kiện "<=" và ">=" nữa, bạn thử từ 5->9 xem!!!

batman1 · 28/6/18

beginning_2311 đã viết:
Cảm ơn bác. Code này e thử rồi nhưng test ở khoảng thời gian khác thì không chính xác ah. Đến giờ vẫn chỉ có cách của bác ndu96081631 là hoạt động hiệu quả ah.

Bạn có thể đính kèm cái tập tin với khoảng thời gian khác mà bạn test và cho là không chính xác không?

Cần nhớ là 50 trong ROW($1:$50) phải thay bằng số tùy ý nhưng phải lớn hơn hoặc bằng số dòng trong vùng dữ liệu. Tại sao? Bởi MATCH với dữ liệu khác có thể trả về nhiều giá trị > 50 - nhiều dòng sau dòng 50 đều thỏa điều kiên.

beginning_2311 · 29/6/18

batman1 đã viết:
Bạn có thể đính kèm cái tập tin với khoảng thời gian khác mà bạn test và cho là không chính xác không?

Cần nhớ là 50 trong ROW($1:$50) phải thay bằng số tùy ý nhưng phải lớn hơn hoặc bằng số dòng trong vùng dữ liệu. Tại sao? Bởi MATCH với dữ liệu khác có thể trả về nhiều giá trị > 50 - nhiều dòng sau dòng 50 đều thỏa điều kiên.

Rất xin lỗi bác. E đã test lại công thức của bác ra kết quả đúng rồi ah. E chưa hiểu ý nghĩa của việc thay thế 1 mảng MATCH bằng ROW($1:$50), nhờ bác chỉ giúp ah.

batman1 · 29/6/18

beginning_2311 đã viết:
Rất xin lỗi bác. E đã test lại công thức của bác ra kết quả đúng rồi ah. E chưa hiểu ý nghĩa của việc thay thế 1 mảng MATCH bằng ROW($1:$50), nhờ bác chỉ giúp ah.

Lẽ ra việc đọc và hiểu FREQUENCY thuộc về bạn.
Nếu bạn hiểu FREQUENCY với MATCH thứ 2 (vì không thấy bạn kêu) thì tại sao lại không hiểu FREQUENCY với ROW(...)?

IF((C6:C49>=H2)*(C6:C49<=I2);MATCH(A6:A49;A6:A49;0)) sẽ trả về mảng tmp có 1 cột 44 dòng. Nếu dòng nào trong C6:C49 không thỏa điều kiện (đk) về ngày thì giá trị tương ứng trong mảng tmp là False, ngược lại thì giá trị tương ứng trong mảng tmp sẽ là vị trí tính từ 1 của giá trị tương ứng trong cột A trong mảng A6:A49. Một điều đáng chú ý là nếu Ci, Cj đều thỏa đk và Ai = Aj, với i < j, thì giá tri thứ (j-5) = giá trị thứ (i-5) (trong mảng tmp) = vị trí của Ai trong mảng A6:A49 . Cái này do MATCH quyết định (đọc trợ giúp về MATCH).

Vd. C12, C19 và C35 thỏa đk, và A12 = A19 = A35 = NGUYEN7. Khi tìm A12, A19 hoặc A35 trong A6:A49 thì MATCH đều trả về 7 = vị trí xuất hiện lần đầu tiên của NGUYEN7 tại A12.

Do các khách hàng giống nhau thì tương ứng với những số giống nhau trong mảng tmp nên nếu gọi số khách hàng (duy nhất) là x thì x cũng chính là số các phần tử duy nhất ngoài False trong mảng tmp. Vậy chỉ cần tính số phần tử duy nhất trong mảng tmp, ngoài False. Để tính x thì ta dùng FREQUENCY.

FREQUENCY bỏ qua, không xét, coi như là không khí các giá trị False.

FREQUENCY(IF(...);ROW($1:$50)) trả về mảng tmp1 có 1 cột 51 dòng.

Giả sử n dòng <> False trong mảng tmp có giá trị 1 ≤ x1 < x2 < ... < xn ≤ số dòng trong vùng dữ liệu.

Lúc đó các phần tử từ 1 tới x1-1 trong mảng tmp1 = 0, phần tử thứ x1 trong mảng tmp1 = số lần mà x1 xuất hiện trong mảng tmp, tức ≥ 1, các phần tử từ x1+1 tới x2-1 bằng 0, ..., phần tử thứ xn trong mảng tmp1 = số lần mà xn xuất hiện trong mảng tmp, tức ≥ 1, các phần tử từ xn+1 tới cuối cùng đều = 0.

Cụ thể trong mảng tmp số 5 xuất hiện 2 lần, 6 xuất hiện 2 lần và 7 xuất hiện 3 lần, còn lại là False. Tức tmp1(1) = tmp1(2) = tmp1(3) = tmp1(4) = 0. tmp1(5) = 2, tmp1(6) = 2, tmp1(7) = 3. Còn lại tmp1(8) = tmp1(9) = ... = tmp1(51) = 0.

(tmp1 > 0) trả về mảng tmp2 có 1 cột 51 dòng mà tmp2(1) = tmp2(2) = tmp2(3) = tmp2(4) = FALSE, tmp2(5) = tmp2(6) = tmp2(7) = TRUE, tmp2(8) = tmp2(9) = ... = tmp2(51) = FALSE.

--(tmp1 > 0), tức --tmp2 trả về mảng result mà result(1) = result(2) = result(3) = result(4) = 0, result(5) = result(6) = result(7) = 1, result(8) = result(9) = ... = result(51) = 0.

Rõ ràng trong mảng result.có đúng x giá trị 1 còn lại là các giá trị 0. Vậy SUM những giá trị này là có kết quả. Thế thôi.

Do trong trường hợp xấu nhất / tốt nhất tất cả các giá trị trong mảng tmp đều <> False và khác nhau từng đôi một (khi các dòng của C6:C49 đề thỏa đk và các giá trị trong A6:A49 khác nhau từng đôi một), tức tmp là mảng chứa các giá trị (1, 2, ..., số dòng dữ liệu). Để FREQUENCY trả về đủ các giá trị thì trong ROW(...) cận trên ít nhất phải = số dòng vùng dữ liệu. Có thể chọn số tùy ý miễn >= số dòng vùng dữ liệu.

Tôi viết dài. Hi vọng không gõ nhầm chỗ nào.

beginning_2311 · 30/6/18

Không biết phải cho bác Batman1 bao nhiêu :throb:

nữa mới đủ. Bác nói đúng còn nhiều điều về FREQUENCY và mảng e phải tìm hiểu thêm.
Cảm ơn bác rất rất nhiều.

gintano1 · 6/10/19

batman1 đã viết:
Lẽ ra việc đọc và hiểu FREQUENCY thuộc về bạn.
Nếu bạn hiểu FREQUENCY với MATCH thứ 2 (vì không thấy bạn kêu) thì tại sao lại không hiểu FREQUENCY với ROW(...)?

IF((C6:C49>=H2)*(C6:C49<=I2);MATCH(A6:A49;A6:A49;0)) sẽ trả về mảng tmp có 1 cột 44 dòng. Nếu dòng nào trong C6:C49 không thỏa điều kiện (đk) về ngày thì giá trị tương ứng trong mảng tmp là False, ngược lại thì giá trị tương ứng trong mảng tmp sẽ là vị trí tính từ 1 của giá trị tương ứng trong cột A trong mảng A6:A49. Một điều đáng chú ý là nếu Ci, Cj đều thỏa đk và Ai = Aj, với i < j, thì giá tri thứ (j-5) = giá trị thứ (i-5) (trong mảng tmp) = vị trí của Ai trong mảng A6:A49 . Cái này do MATCH quyết định (đọc trợ giúp về MATCH).

Vd. C12, C19 và C35 thỏa đk, và A12 = A19 = A35 = NGUYEN7. Khi tìm A12, A19 hoặc A35 trong A6:A49 thì MATCH đều trả về 7 = vị trí xuất hiện lần đầu tiên của NGUYEN7 tại A12.

Do các khách hàng giống nhau thì tương ứng với những số giống nhau trong mảng tmp nên nếu gọi số khách hàng (duy nhất) là x thì x cũng chính là số các phần tử duy nhất ngoài False trong mảng tmp. Vậy chỉ cần tính số phần tử duy nhất trong mảng tmp, ngoài False. Để tính x thì ta dùng FREQUENCY.

FREQUENCY bỏ qua, không xét, coi như là không khí các giá trị False.

FREQUENCY(IF(...);ROW($1:$50)) trả về mảng tmp1 có 1 cột 51 dòng.

Giả sử n dòng <> False trong mảng tmp có giá trị 1 ≤ x1 < x2 < ... < xn ≤ số dòng trong vùng dữ liệu.

Lúc đó các phần tử từ 1 tới x1-1 trong mảng tmp1 = 0, phần tử thứ x1 trong mảng tmp1 = số lần mà x1 xuất hiện trong mảng tmp, tức ≥ 1, các phần tử từ x1+1 tới x2-1 bằng 0, ..., phần tử thứ xn trong mảng tmp1 = số lần mà xn xuất hiện trong mảng tmp, tức ≥ 1, các phần tử từ xn+1 tới cuối cùng đều = 0.

Cụ thể trong mảng tmp số 5 xuất hiện 2 lần, 6 xuất hiện 2 lần và 7 xuất hiện 3 lần, còn lại là False. Tức tmp1(1) = tmp1(2) = tmp1(3) = tmp1(4) = 0. tmp1(5) = 2, tmp1(6) = 2, tmp1(7) = 3. Còn lại tmp1(8) = tmp1(9) = ... = tmp1(51) = 0.

(tmp1 > 0) trả về mảng tmp2 có 1 cột 51 dòng mà tmp2(1) = tmp2(2) = tmp2(3) = tmp2(4) = FALSE, tmp2(5) = tmp2(6) = tmp2(7) = TRUE, tmp2(8) = tmp2(9) = ... = tmp2(51) = FALSE.

--(tmp1 > 0), tức --tmp2 trả về mảng result mà result(1) = result(2) = result(3) = result(4) = 0, result(5) = result(6) = result(7) = 1, result(8) = result(9) = ... = result(51) = 0.

Rõ ràng trong mảng result.có đúng x giá trị 1 còn lại là các giá trị 0. Vậy SUM những giá trị này là có kết quả. Thế thôi.

Do trong trường hợp xấu nhất / tốt nhất tất cả các giá trị trong mảng tmp đều <> False và khác nhau từng đôi một (khi các dòng của C6:C49 đề thỏa đk và các giá trị trong A6:A49 khác nhau từng đôi một), tức tmp là mảng chứa các giá trị (1, 2, ..., số dòng dữ liệu). Để FREQUENCY trả về đủ các giá trị thì trong ROW(...) cận trên ít nhất phải = số dòng vùng dữ liệu. Có thể chọn số tùy ý miễn >= số dòng vùng dữ liệu.

Tôi viết dài. Hi vọng không gõ nhầm chỗ nào.

Anh có thể giải thích giúp em cách thức hoạt động hàm Frequency trong trường hợp Data array và bin array là cùng một mảng không ạ, em có thử và ra kết quả, nhưng không hiểu cách hoạt động của nó. Ví dụ ta có mảng A1:A6 là 1,2,3,1,2,3 thì kết quả khi áp dụng Frequency với mảng này là 2,2,2,0,0,0,0 mà không phải là 2,2,2,2,2,2,0

batman1 · 7/10/19

gintano1 đã viết:
Anh có thể giải thích giúp em cách thức hoạt động hàm Frequency trong trường hợp Data array và bin array là cùng một mảng không ạ, em có thử và ra kết quả, nhưng không hiểu cách hoạt động của nó. Ví dụ ta có mảng A1:A6 là 1,2,3,1,2,3 thì kết quả khi áp dụng Frequency với mảng này là 2,2,2,0,0,0,0 mà không phải là 2,2,2,2,2,2,0

Cùng hay khác mảng không liên quan. Có thể hiểu nôm na, ít ra là tôi hiểu thế, là FREQUENCY chỉ trả về kết quả một lần cho mỗi số duy nhất trong bins_array. Số 0 sẽ được trả về cho mỗi lần xuất hiện về sau của cùng số đó trong bins_array.

Trong vd. của bạn thì số lần xuất hiện của 1, 2, 3 được trả về ở phần tử thứ 1, 2, 3 của mảng kết quả. Còn kết quả cho lần xuất hiện về sau của cũng 3 số đó (ở phần tử thứ 4, 5, 6 của bins_array) thì là 0.

gintano1 · 7/10/19

batman1 đã viết:
Cùng hay khác mảng không liên quan. Có thể hiểu nôm na, ít ra là tôi hiểu thế, là FREQUENCY chỉ trả về kết quả một lần cho mỗi số duy nhất trong bins_array. Số 0 sẽ được trả về cho mỗi lần xuất hiện về sau của cùng số đó trong bins_array.

Trong vd. của bạn thì số lần xuất hiện của 1, 2, 3 được trả về ở phần tử thứ 1, 2, 3 của mảng kết quả. Còn kết quả cho lần xuất hiện về sau của cũng 3 số đó (ở phần tử thứ 4, 5, 6 của bins_array) thì là 0.

Em cảm ơn anh, vì em chưa hiểu cách hoạt động cho các số lặp lại, giờ em hiểu rồi anh

Bài đã được tự động gộp: 7/10/19

batman1 đã viết:
Cùng hay khác mảng không liên quan. Có thể hiểu nôm na, ít ra là tôi hiểu thế, là FREQUENCY chỉ trả về kết quả một lần cho mỗi số duy nhất trong bins_array. Số 0 sẽ được trả về cho mỗi lần xuất hiện về sau của cùng số đó trong bins_array.

Trong vd. của bạn thì số lần xuất hiện của 1, 2, 3 được trả về ở phần tử thứ 1, 2, 3 của mảng kết quả. Còn kết quả cho lần xuất hiện về sau của cũng 3 số đó (ở phần tử thứ 4, 5, 6 của bins_array) thì là 0.

Câu hỏi này e chạy evaluate công thức của thầy ndu96081631 ra, trong diễn đàn có bài viết nào nói rõ về cách hoạt động cũng như các trường hợp nâng cao về hàm Frequency không ạ, em search google thì các bài viết hầu như được dịch từ support của microsoft ra, rất chung chung và chỉ nói về trường hợp bin array sắp xếp từ nhỏ đến lớn

[Nhờ hỗ trợ] Đếm không trùng có nhiều điều kiện

beginning_2311

Thành viên mới

File đính kèm

ndu96081631

Huyền thoại GPE

beginning_2311

Thành viên mới

ndu96081631

Huyền thoại GPE

batman1

Thành viên gạo cội

beginning_2311

Thành viên mới

ngoctinh87

Thành viên thường trực

excel_lv1.5

Thành viên tích cực

batman1

Thành viên gạo cội

beginning_2311

Thành viên mới

File đính kèm

batman1

Thành viên gạo cội

beginning_2311

Thành viên mới

gintano1

Thành viên mới

batman1

Thành viên gạo cội

gintano1

Thành viên mới

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian