Thống kê (đếm) theo nhiều điều kiện!

[Mr Joker]

Chào các anh chị / các tiền bối
Em có 1 vấn đề đã tìm tòi mà chưa giải quyết được nên muốn nhờ các anh, chị giúp đỡ ạ!
Em có 1 bảng chi tiết như file đính kèm, giờ em đang muốn đếm số lượng chuyến "2 in 1" của "Type 1" đáp ứng những điều kiện sau:
_ Cùng NAME
_ Cùng TYPE (ví dụ Type 1)
_ Cùng START
_ Cùng LOCATION
_Và cuối cùng là cùng TIME, đây là vấn đề rắc rối vì khác TIME thì đếm là 1 chuyến như bình thường, nhưng nếu đáp ứng tất cả các điều kiện trên bao gồm cả "cùng TIME" thì lại chỉ được đếm là 1 chuyến thôi (vì do có thể 2 Type 1 trong cùng 1 lần)

Với 4 điều kiện đầu thì em có thể dùng hàm COUNTIFS để đếm được SỐ CHUYẾN theo từng tiêu chí, nhưng để đáp ứng thêm điều kiện TIME thì em chưa có cách giải quyết!
Vì đáp ứng điều kiện"cùng TIME" là bằng nhau về ngày + tháng + năm + giờ + phút - có thể bằng giây hoặc chênh lệch nhau 1 giây thì sẽ gọi là "cùng TIME"
Về định dạng và nội dung cột TIME là cố định như vậy không thay đổi.

Mong các anh chị giúp đỡ ạ ! Em cảm ơn rất nhiều

 

[HieuCD]

Chào các anh chị / các tiền bối
Em có 1 vấn đề đã tìm tòi mà chưa giải quyết được nên muốn nhờ các anh, chị giúp đỡ ạ!
Em có 1 bảng chi tiết như file đính kèm, giờ em đang muốn đếm số lượng chuyến "2 in 1" của "Type 1" đáp ứng những điều kiện sau:
_ Cùng NAME
_ Cùng TYPE (ví dụ Type 1)
_ Cùng START
_ Cùng LOCATION
_Và cuối cùng là cùng TIME, đây là vấn đề rắc rối vì khác TIME thì đếm là 1 chuyến như bình thường, nhưng nếu đáp ứng tất cả các điều kiện trên bao gồm cả "cùng TIME" thì lại chỉ được đếm là 1 chuyến thôi (vì do có thể 2 Type 1 trong cùng 1 lần)

Với 4 điều kiện đầu thì em có thể dùng hàm COUNTIFS để đếm được SỐ CHUYẾN theo từng tiêu chí, nhưng để đáp ứng thêm điều kiện TIME thì em chưa có cách giải quyết!
Vì đáp ứng điều kiện"cùng TIME" là bằng nhau về ngày + tháng + năm + giờ + phút - có thể bằng giây hoặc chênh lệch nhau 1 giây thì sẽ gọi là "cùng TIME"
Về định dạng và nội dung cột TIME là cố định như vậy không thay đổi.

Mong các anh chị giúp đỡ ạ ! Em cảm ơn rất nhiều

Tạo 1 cột phụ cho dể làm

 

[Phan Thế Hiệp]

@dazkangel, @hocexcel_1991, @huonglien1901 ....

Anh nghĩ với khả năng của các em, cũng với yêu cầu của chủ thớt như trên, không làm cột phụ, các em cố gắng tạo chỉ với 1 công thức cho ô I2 (rồi kéo xuống).

Đây anh chủ yếu tìm dịp chơi và học, rồi anh em cùng chia sẻ cho vui về công thức mảng, nha mấy em!

Chúc anh em ngày vui.


Gợi ý: trong công thức anh làm có sử dụng công thức cốt lõi của anh @HieuCD, và dùng thêm công thức tính phân đoạn MAX("Mảng") của anh @huuthang_bd...

 

[hocexcel_1991]

@dazkangel, @hocexcel_1991, @huonglien1901 ....

Anh nghĩ với khả năng của các em, cũng với yêu cầu của chủ thớt như trên, không làm cột phụ, các em cố gắng tạo chỉ với 1 công thức cho ô I2 (rồi kéo xuống).

Đây anh chủ yếu tìm dịp chơi và học, rồi anh em cùng chia sẻ cho vui về công thức mảng, nha mấy em!

Chúc anh em ngày vui.


Gợi ý: trong công thức anh làm có sử dụng công thức cốt lõi của anh @HieuCD, và dùng thêm công thức tính phân đoạn MAX("Mảng") của anh @huuthang_bd...

Em dùng công thức của anh rồi sửa lại tí, thấy kết quả đúng, không biết có sai trường hợp nào không?

=COUNT(1/(MATCH($D$2:$D$19&$E$2:$E$19&INT($C$2:$C$19),$D$2:$D$19&$E$2:$E$19&INT($C$2:$C$19),0)=ROW($1:$18))/(($A$2:$A$19=G2)*($B$2:$B$19=--RIGHT(H2))))
nhấn CSE

P/S: mấy công thức mảng em chỉ "chôm" trên mạng rồi sửa lại thôi. Chứ đụng vào mấy cái này nhức óc lắm

 

[Phan Thế Hiệp]

Em dùng công thức của anh rồi sửa lại tí, thấy kết quả đúng, không biết có sai trường hợp nào không?

=COUNT(1/(MATCH($D$2:$D$19&$E$2:$E$19&INT($C$2:$C$19),$D$2:$D$19&$E$2:$E$19&INT($C$2:$C$19),0)=ROW($1:$18))/(($A$2:$A$19=G2)*($B$2:$B$19=--RIGHT(H2))))
nhấn CSE

P/S: mấy công thức mảng em chỉ "chôm" trên mạng rồi sửa lại thôi. Chứ đụng vào mấy cái này nhức óc lắm

Mới được cài "sườn" thôi em! cố lên!

Chưa chính xác, vì hàm INT($C$2:$C$19).
Do giờ phải tính đến "Giây" chỉ chênh lệch với nhau 1 giây thì xem là cùng một thời gian và loại trùng.

Cứ tập luyện suy nghĩ, đến khi có kết quả mình sẽ thấm nhiều hơn.

Thân

 

[dazkangel]

@dazkangel, @hocexcel_1991, @huonglien1901 ....

Anh nghĩ với khả năng của các em, cũng với yêu cầu của chủ thớt như trên, không làm cột phụ, các em cố gắng tạo chỉ với 1 công thức cho ô I2 (rồi kéo xuống).

Đây anh chủ yếu tìm dịp chơi và học, rồi anh em cùng chia sẻ cho vui về công thức mảng, nha mấy em!

Chúc anh em ngày vui.


Gợi ý: trong công thức anh làm có sử dụng công thức cốt lõi của anh @HieuCD, và dùng thêm công thức tính phân đoạn MAX("Mảng") của anh @huuthang_bd...

Thử:

=SUMPRODUCT((A$2:A$19=G2)*(B$2:B$19=--RIGHT(H2))/MMULT(COUNTIFS(A$2:A$19,A$2:A$19,B$2:B$19,B$2:B$19,C$2:C$19,C$2:C$19+{0,1,-1}*1/86400,D$2:D$19,D$2:D$19,E$2:E$19,E$2:E$19),{1;1;1}))

 

[huonglien1901]

@dazkangel, @hocexcel_1991, @huonglien1901 ....

Anh nghĩ với khả năng của các em, cũng với yêu cầu của chủ thớt như trên, không làm cột phụ, các em cố gắng tạo chỉ với 1 công thức cho ô I2 (rồi kéo xuống).

Đây anh chủ yếu tìm dịp chơi và học, rồi anh em cùng chia sẻ cho vui về công thức mảng, nha mấy em!

Chúc anh em ngày vui.


Gợi ý: trong công thức anh làm có sử dụng công thức cốt lõi của anh @HieuCD, và dùng thêm công thức tính phân đoạn MAX("Mảng") của anh @huuthang_bd...

Em dạo này bận công việc quá nên thời gian lên diễn đàn cũng ít. Tí nữa thử suy nghĩ cách làm thử xem. Chứ để lâu ngày đầu óc quên hết kiến thức à.

 

[Phan Thế Hiệp]

Thử:

=SUMPRODUCT((A$2:A$19=G2)*(B$2:B$19=--RIGHT(H2))*MMULT(COUNTIFS(A$2:A$19,A$2:A$19,B$2:B$19,B$2:B$19,C$2:C$19,C$2:C$19+{0,1,-1}*1/86400,D$2:D$19,D$2:D$19,E$2:E$19,E$2:E$19),{1;1;1})^-1)

Công thức của em rất hay, nhưng có 1 chỗ nhược cần phải nới rộng biên:
Dùng C$2:C$19+{0,1,-1}*1/86400 sẽ bị hạn chế theo biên độ {0,1,-1} của từng giá trị theo dòng.
Ví dụ: Giả sử các giá trị tại C4, C5, C6 có chênh lệch vơi nhau 1 giây lần lượt như sau:

• C4= 01/04/2019 8:35:03 AM -> sẽ có 3 giá trị 8:35:02 ; 8:35:03 ; 8:35:04 (Nó sẽ bị hụt mất: 05)
• C5= 01/04/2019 8:35:04 AM -> sẽ có 3 giá trị 8:35:03 ; 8:35:04 ; 8:35:05
• C6= 01/04/2019 8:35:05 AM -> sẽ có 3 giá trị 8:35:04 ; 8:35:05 ; 8:35:06 (Nó sẽ bị hụt mất: 03)

Thành ra sẽ có kết quả lẻ.

Gợi ý thêm 1 cách:

• Chọn ra các giá trị chênh lệch nhau 1-2 giây (hoặc n giây), theo từng dòng dữ liệu.
• Quy đổi thống nhất về 1 giá trị nhỏ chung nhất, theo từng dòng dữ liệu.
• Làm được hai bước trên thì các giá trị chênh lệch nhau 1-2 giây sẽ còn thể hiện 1 ngày giờ-phút-giây thống nhất. (như ví dụ trên thì các dòng C4: C6 sẽ chỉ hiện: 01/04/2019 8:35:03 AM
• Dựa trên kết quả đó sẽ tìm được các giá trị trùng lắp như yêu cầu đề bài, loại ra, và đếm.

Thân

 

[Mr Joker]

Chào anh / chị
Trước hết em cảm ơn sự nhiệt tình của mọi người
Vấn đề này phức tạp hơn em tưởng

 

[dazkangel]

Công thức của em rất hay, nhưng có 1 chỗ nhược cần phải nới rộng biên:
Dùng C$2:C$19+{0,1,-1}*1/86400 sẽ bị hạn chế theo biên độ {0,1,-1} của từng giá trị theo dòng.
Ví dụ: Giả sử các giá trị tại C4, C5, C6 có chênh lệch vơi nhau 1 giây lần lượt như sau:

• C4= 01/04/2019 8:35:03 AM -> sẽ có 3 giá trị 8:35:02 ; 8:35:03 ; 8:35:04 (Nó sẽ bị hụt mất: 05)
• C5= 01/04/2019 8:35:04 AM -> sẽ có 3 giá trị 8:35:03 ; 8:35:04 ; 8:35:05
• C6= 01/04/2019 8:35:05 AM -> sẽ có 3 giá trị 8:35:04 ; 8:35:05 ; 8:35:06 (Nó sẽ bị hụt mất: 03)

Thành ra sẽ có kết quả lẻ.

Gợi ý thêm 1 cách:

• Chọn ra các giá trị chênh lệch nhau 1-2 giây (hoặc n giây), theo từng dòng dữ liệu.
• Quy đổi thống nhất về 1 giá trị nhỏ chung nhất, theo từng dòng dữ liệu.
• Làm được hai bước trên thì các giá trị chênh lệch nhau 1-2 giây sẽ còn thể hiện 1 ngày giờ-phút-giây thống nhất. (như ví dụ trên thì các dòng C4: C6 sẽ chỉ hiện: 01/04/2019 8:35:03 AM
• Dựa trên kết quả đó sẽ tìm được các giá trị trùng lắp như yêu cầu đề bài, loại ra, và đếm.

Thân

Thêm 1 cách khác đỡ cộng trừ hơn, nhưng quy đổi kiểu gì thì em không biết cách làm:

=SUMPRODUCT((A$2:A$19=G2)*(B$2:B$19=--RIGHT(H2))/COUNTIFS(A$2:A$19,A$2:A$19,B$2:B$19,B$2:B$19,C$2:C$19,"<="&C$2:C$19+1/86400,C$2:C$19,">="&C$2:C$19-1/86400,D$2:D$19,D$2:D$19,E$2:E$19,E$2:E$19))

 

[Phan Thế Hiệp]

Thêm 1 cách khác đỡ cộng trừ hơn, nhưng quy đổi kiểu gì thì em không biết cách làm:

=SUMPRODUCT((A$2:A$19=G2)*(B$2:B$19=--RIGHT(H2))/COUNTIFS(A$2:A$19,A$2:A$19,B$2:B$19,B$2:B$19,C$2:C$19,"<="&C$2:C$19+1/86400,C$2:C$19,">="&C$2:C$19-1/86400,D$2:D$19,D$2:D$19,E$2:E$19,E$2:E$19))

Xem ra và cũng thú thiệt cách của em hay và gọn hơn cách của anh nghĩ ra.
Ban đầu anh tính thì gọn, tức dùng: ABS(....)<10/86399, nhưng không dè phát sinh thêm nhiều thứ nữa!
Đành, chịu thua em!

Chúc em ngày vui.

 

[dazkangel]

Xem ra và cũng thú thiệt cách của em hay và gọn hơn cách của anh nghĩ ra.
Ban đầu anh tính thì gọn, tức dùng: ABS(....)<10/86399, nhưng không dè phát sinh thêm nhiều thứ nữa!
Đành, chịu thua em!

Chúc em ngày vui.

Anh thử đưa cách quy đổi để về sau cần em có bí kíp học theo.

 

[Phan Thế Hiệp]

Anh thử đưa cách quy đổi để về sau cần em có bí kíp học theo.

Đem lên làm gì cho xấu hỗ em!

Thiệt tình là anh ôn lại công thức của anh @huuthang_bd trong việc tìm phân đoạn MIN/MAX("Mảng"), cái này thì em biết rồi, tuy nhiên khi đem vào thì, ôi chao! nó dài quá.

Chúc em ngày vui.

 

[dazkangel]

Đem lên làm gì cho xấu hỗ em!

Thiệt tình là anh ôn lại công thức của anh @huuthang_bd trong việc tìm phân đoạn MIN/MAX("Mảng"), cái này thì em biết rồi, tuy nhiên khi đem vào thì, ôi chao! nó dài quá.

Chúc em ngày vui.

Cái tìm min, max 1 mảng em mất cả tuần tìm ra cách làm. Hàm min/max mảng của anh huuthang_bd em không biết cách làm ra sao ...
Nếu được anh cho em đường dẫn đến bài đó học mót co thêm kiến thức.

 

[Phan Thế Hiệp]

Cái tìm min, max 1 mảng em mất cả tuần tìm ra cách làm. Hàm min/max mảng của anh huuthang_bd em không biết cách làm ra sao ...
Nếu được anh cho em đường dẫn đến bài đó học mót co thêm kiến thức.

Cũng từ sự tìm tòi đặt vấn đề của @eke_rula mà có các công thức hay sản sinh:
Tính tổng Min,Max trong của các vùng dữ liệu 1 chiều trong vùng dữ liệu 2 chiều

Chúc em ngày vui.

 

[dazkangel]

Cũng từ sự tìm tòi đặt vấn đề của @eke_rula mà có các công thức hay sản sinh:
Tính tổng Min,Max trong của các vùng dữ liệu 1 chiều trong vùng dữ liệu 2 chiều

Chúc em ngày vui.

Híc công thức hay vậy mà em không biết, làm em ngồi mò cả tuần mới ra cách làm. Công thức bữa bên bài tìm theo 20 điều kiện đó em nghĩ mãi mới ra.

 

[huuthang_bd]

Giả sử giờ như bên dưới thì có gọi là cùng thời gian không?
12:00:00
12:00:01
12:00:02
12:00:03

 

[Phan Thế Hiệp]

Giả sử giờ như bên dưới thì có gọi là cùng thời gian không?
12:00:00
12:00:01
12:00:02
12:00:03

Vụ này thì chỉ có thớt mới biết tính hay không.

Mấy cái giờ chênh lệch nhau 1 giây thường hay thấy trong các giao dịch thanh toán hoàn thành, để Ghi Nợ/Có tại Sổ phụ ngân hàng, như ví dụ trong file kèm.

Thân

 

[Phan Thế Hiệp]

Chào anh em,

Rất thích chủ đề giờ chênh lệch này, vì hoàn toàn giống với thực tế đang theo dõi sổ sách tại Cty.
Nay, gửi anh em yêu cầu (mà thực tế đang làm) như sau:

Chỉ tạo bằng công thức cho cột F, trả về kết quả là các ký hiệu quy định trong bảng I1:L11, với các điều kiện sau:

1/ Nếu Phát sinh Nợ >0: Đặc điểm trên giao dịch có cùng Ngày-Giờ-Phút-Giây (chỉ lệch đúng 1 giây) thường có 3 dòng:

○ Đối với Thanh toán cho Nhà Cung Cấp (NCC), hoặc các cơ quan hữu quan (BHXH, Thuế...): thường nội dung diễn giải giống nhau
→ Giá trị lớn nhất: là Thanh toán (thường khớp đúng với giá trị đang theo dõi bên sheet "SoKTChitiet")
→ Giá trị lớn nhì: là Phí NH chuyển khoản
→ Giá trị cuối: là VAT Phí NH chuyển khoản
Lưu ý: Có vài NCC cùng chung Ngân hàng, thì chỉ có 1 dòng phát sinh giá trị thanh toán, không có 2 dòng Phí và VAT.

○ Đối với Rút Tiền gửi Ngân hàng:
→ Giá trị lớn nhất: là Rút tiền gửi ngân hàng (TGHN) nhập Qũy Tiền mặt (QTM). Diễn giải thường có chữ RTM
(thường khớp đúng với giá trị đang theo dõi bên sheet "SoKTChitiet")
→ Giá trị lớn nhì: là Phí Rút TGNH
→ Giá trị cuối: là VAT Phí rút TGNH

2/ Nếu Phát sinh Có >0: Được phân ra:
→ Bán Ngoại tệ: khi trong Diễn giải có ghi nội dung "BAN NGOAI TE"
→ Lãi tiền gửi ngân hàng: khi trong Diễn giải có ghi nội dung "Interest"
→ Hoàn tiền: khi trong Diễn giải có ghi nội dung "HOAN NHAP"
→ Số còn lại là Thu tiền

Kết quả mong muốn trả về như đã đánh tay vào cột F file kèm.

Chúc anh em tham khảo vui.
Thân

 

[Mr Joker]

Cảm ơn các anh / chị
Em chợt nhớ ra 1 vấn đề, cái quên này khá là quan trọng nhưng không rõ là có đơn giản bớt công thức mọi người đang xây dựng để đáp ứng điều kiện của bài này không nữa

Theo logic thì có thể bỏ qua phần xét đến tiêu chí GIÂY (bằng GIÂY hoặc lệch 1 GIÂY cũng không cần xét đến nữa), chỉ cần đáp ứng tiêu chí trùng NAME - trùng START - trùng LOCATION - và cuối cùng là trùng TIME, trong TIME chỉ cần trùng ngày - tháng - năm - giờ - phút là đủ đưa vào diện "2 chuyến nhưng ĐẾM 1 LẦN"
vì thực tế là để thực hiện xong 1 chuyến đã tốn một thời gian nhất định, thế nên không thể có trường hợp đáp ứng đầy đủ những tiêu chí trên trùng đến tận phút mà lại không phải là 1 chuyến đi đôi. Anh / chị hiểu ý em không ạ?
Ví dụ 2 "TYPE 1" (đã đáp ứng trùng NAME - START - LOCATION) có dữ liệu thời gian như sau:
01/04/2019 8:35:03
01/04/2019 8:35:04
vì trong 60 giây của phút thứ 35 này không thể có NAME nào thực hiện được 2 "TYPE 1" riêng biệt được, nên chắc chắn đây là 1 chuyến đi đôi. Vì vậy em thấy xét đến trùng phút là đủ.