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

Phuocam · 9/5/19

Mr Joker đã viết:
Vì vậy em thấy xét đến trùng phút là đủ.

Ví dụ:
01/04/2019 8:35:59
01/04/2019 8:36:00

Nhận xét: Trùng phút chưa chắc đã đủ

huuthang_bd · 9/5/19

Mr Joker đã viết:
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à đủ.

Vậy hai mốc thời gian 01/04/2019 8:35:59 và 01/04/2019 8:36:00 chả nhẽ lại thành không "cùng thời gian" à

huuthang_bd · 9/5/19

Phan Thế Hiệp đã viết:
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

Hai ô ra kết quả khác, không biết có hiểu sai chỗ nào không.

Phan Thế Hiệp · 9/5/19

Mr Joker đã viết:
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à đủ.

Theo như phân tích của hai anh trên, bạn không nên không xét đến 1 giây.

Trong bài toán này, hoàn toàn phụ thuộc vào bạn biết biên độ thời gian (giây) hạn định để phân biệt sự khác nhau giữa hai giao dịch, bạn phải tự nhận xét lấy qua việc: "thông lệ" nó xảy ra như thế nào.

Ví dụ: có thể các chuyến coi là trùng lắp, sẽ có biên độ chênh lệch nhau trong vòng 10 giây, nhưng giữa hai chuyến khác nhau phát sinh, có thể cách biệt là từ 2 phút trở lên (cái này là tôi giả dụ thôi), thì hai công thức:

Bài #2 của anh @HieuCD, và
Bài #6 của bạn @dazkangel

"Rất" phù hợp với yêu cầu của bạn rồi.

Trong file kèm là tôi nâng độ rộng biên của cả hai công thức tùy theo ý bạn (sau khi bạn đã nhận xét và rút ra được cái "thông lệ" phân biệt về thời gian, như trên đã đề cập). Bạn xem thử và tùy nghi áp dụng vào công việc của bạn.

Thân

huuthang_bd · 9/5/19

Phan Thế Hiệp đã viết:
Ví dụ: có thể các chuyến coi là trùng lắp, sẽ có biên độ chênh lệch nhau trong vòng 10 giây, nhưng giữa hai chuyến khác nhau phát sinh, có thể cách biệt là từ 2 phút trở lên (cái này là tôi giả dụ thôi), thì hai công thức:

Nếu vậy thì sẽ không có trường hợp như dòng 17 và 18 trong file và không cần xét đến START và LOCATION.

Phan Thế Hiệp · 9/5/19

huuthang_bd đã viết:
Nếu vậy thì sẽ không có trường hợp như dòng 17 và 18 trong file và không cần xét đến START và LOCATION.

Hai dòng đó khác ngày.

Bởi vậy, sự phân biệt các chuyến khác nhau là do chủ thớt phải tự tìm ra, và phải khác nhau cả nhiều phút mới được.

Thân
-------------------------------------------------------------------------

huuthang_bd đã viết:
Hai ô ra kết quả khác, không biết có hiểu sai chỗ nào không.
View attachment 216753

Chính xác là bị tính sai, và ký hiệu phải là "Pi" và "Pv".

Giao dịch này là chuyển "ốm đau, thai sản vào thẻ ATM" cho công nhân viên, nhưng "bị" ngân hàng "tính phí" là 55.000đ gồm thuế VAT.
Sau này, ngân hàng phải hoàn trả lại cho Cty số tiền phí này.

Thân

huuthang_bd · 9/5/19

Phan Thế Hiệp đã viết:
Hai dòng đó khác ngày.

Nhìn cái đuôi thấy giống tưởng giống hết

Mr Joker · 9/5/19

Rất cảm ơn anh / chị đã giúp đỡ

phuocam đã viết:
Ví dụ:
01/04/2019 8:35:59
01/04/2019 8:36:00

Nhận xét: Trùng phút chưa chắc đã đủ

huuthang_bd đã viết:
Vậy hai mốc thời gian 01/04/2019 8:35:59 và 01/04/2019 8:36:00 chả nhẽ lại thành không "cùng thời gian" à

Đúng là em bỏ sót trường hợp này

Như vậy vẫn nên tính đến "GIÂY" là sẽ chính xác và đầy đủ nhất !

Phan Thế Hiệp đã viết:
Theo như phân tích của hai anh trên, bạn không nên không xét đến 1 giây.

Trong bài toán này, hoàn toàn phụ thuộc vào bạn biết biên độ thời gian (giây) hạn định để phân biệt sự khác nhau giữa hai giao dịch, bạn phải tự nhận xét lấy qua việc: "thông lệ" nó xảy ra như thế nào.

Ví dụ: có thể các chuyến coi là trùng lắp, sẽ có biên độ chênh lệch nhau trong vòng 10 giây, nhưng giữa hai chuyến khác nhau phát sinh, có thể cách biệt là từ 2 phút trở lên (cái này là tôi giả dụ thôi), thì hai công thức:

Bài #2 của anh @HieuCD, và

Bài #6 của bạn @dazkangel

"Rất" phù hợp với yêu cầu của bạn rồi.

Trong file kèm là tôi nâng độ rộng biên của cả hai công thức tùy theo ý bạn (sau khi bạn đã nhận xét và rút ra được cái "thông lệ" phân biệt về thời gian, như trên đã đề cập). Bạn xem thử và tùy nghi áp dụng vào công việc của bạn.

Thân

Cảm ơn anh nhiều. Em đã hiểu vấn đề
Chúc mọi người vui vẻ !

huuthang_bd · 9/5/19

Phan Thế Hiệp đã viết:
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

Vấn đề của thớt xong rồi thì tới vấn đề phát sinh

Phan Thế Hiệp · 9/5/19

huuthang_bd đã viết:
Vấn đề của thớt xong rồi thì tới vấn đề phát sinh

Cảm ơn vì đã quan tâm!
/-*+/

Dòng 103 và 104 (Ngày 26/04/2019 18:29:15) thực ra là tiền Phí Rút TGNH và VAT của khoảng rút tiền 20 Triệu (Dòng 90: 26/04/2019 14:13:17).
Trong Diễn giải (Dòng 104) của nó cũng thể hiện.

Thân

huuthang_bd · 9/5/19

Phan Thế Hiệp đã viết:
Dòng 103 và 104 (Ngày 26/04/2019 18:29:15) thực ra là tiền Phí Rút TGNH và VAT của khoảng rút tiền 20 Triệu (Dòng 90: 26/04/2019 14:13:17).
Trong Diễn giải (Dòng 104) của nó cũng thể hiện.

Thân

Thì tôi làm theo quy luật mà bạn mô tả mà. Cứ ráp vô quy luật nó trúng cái nào thì lấy cái đó thôi.

Phan Thế Hiệp · 9/5/19

huuthang_bd đã viết:
Thì tôi làm theo quy luật mà bạn mô tả mà. Cứ ráp vô quy luật nó trúng cái nào thì lấy cái đó thôi.

Mấy anh I.T thảo chương Internet banking của ngân hàng cũng vui lắm:
Giá trị rút tiền: thì ghi *RTM*
Phí rút tiền: ghi "DN - Rut tien mat VND khac tinh/TP noi mo TK"
Còn VAT bất kỳ chỉ vỏn vẹn "VAT TAX FOR VND"
Thêm vào đó, vui thì ghi cùng thời gian cho cùng giao dịch rút tiền+tính phí+VAT, "hổng dzui" thì như file làm hai thời gian cách biệt nhau.
Cũng may, việc thanh toán cho NCC thì không bị vậy.

Tội cho mấy "thằng" kế toán ngồi đó mà ghi rõ ra Phí của em nào và VAT của em nào.

Chút chia sẻ nỗi khỗ của nghề.
/-*+/

Mr Joker · 10/5/19

Phan Thế Hiệp đã viết:
Theo như phân tích của hai anh trên, bạn không nên không xét đến 1 giây.

Trong bài toán này, hoàn toàn phụ thuộc vào bạn biết biên độ thời gian (giây) hạn định để phân biệt sự khác nhau giữa hai giao dịch, bạn phải tự nhận xét lấy qua việc: "thông lệ" nó xảy ra như thế nào.

Ví dụ: có thể các chuyến coi là trùng lắp, sẽ có biên độ chênh lệch nhau trong vòng 10 giây, nhưng giữa hai chuyến khác nhau phát sinh, có thể cách biệt là từ 2 phút trở lên (cái này là tôi giả dụ thôi), thì hai công thức:

Bài #2 của anh @HieuCD, và

Bài #6 của bạn @dazkangel

"Rất" phù hợp với yêu cầu của bạn rồi.

Trong file kèm là tôi nâng độ rộng biên của cả hai công thức tùy theo ý bạn (sau khi bạn đã nhận xét và rút ra được cái "thông lệ" phân biệt về thời gian, như trên đã đề cập). Bạn xem thử và tùy nghi áp dụng vào công việc của bạn.

Thân

Hai công thức của anh @HieuCD và @dazkangel em đã áp dụng rất ok cho bài test nho nhỏ của em !
Nhưng khi áp dụng vào thực tế công việc thì do số lượng dòng dữ liệu rất nhiều, tầm trên dưới 10000 dòng / tháng - khiến công thức rất nặng và chạy rất rất lâu anh / chị ạ.
Vấn đề nữa là trong ~10000 dòng hàng (nghĩa là rất nhiều), đúng như anh nói về biên độ chênh lệch giây, rõ ràng vẫn tồn tại việc chênh nhau nhiều số giây hơn (có thể lên đến vài phút) nhưng thực tế nó đúng rơi vào trường hợp 2 in 1 bởi miễn là cùng 1 người (NAME), lấy cùng loại TYPE 1, xuất phát cùng 1 nơi (START) và đến cùng 1 chỗ (LOCATION)
Trường hợp bằng hoặc chênh nhau 1 giây là chiếm đa số để mình nhận diện 2 in 1, nhưng thực tế vẫn tồn tại 1 vài trường hợp cũng thuộc diện này nhưng lại có thể chênh nhau nhiều giây hoặc lên đến vài phút. Mình không tính nó vào là diện 2 in 1 thì sẽ bị dư chuyến và không chuẩn cho việc tính tiền phải trả

Và em cũng rất mong muốn ngoài thống kê được TỔNG ra thì ta có thể thống kê "đếm" chi tiết được số chuyến TYPE 1 cụ thể cho từng "TUYẾN" (vì điểm đi - điểm đến khác nhau sẽ có giá tiền cũng khác nhau) đếm được bao nhiêu chuyến 1 tuyến bất kỳ của từng cá nhân, sẽ phục vụ cho việc lên báo cáo chi tiết số tiền phải trả cho từng tuyến + cho từng NAME trong 1 khoảng thời gian tùy chọn.
Ví dụ : theo file TEST thì từ 01/04 đến 12/04/2019
01/04/2019 anh KIM đi được 1 chuyến đôi TYPE 1 từ điểm a đến điểm C
03/04/2019 anh KIM đi được 1 chuyến đơn TYPE 1 từ điểm a đến điểm C
11/04/2019 anh KIM đi được 1 chuyến đơn TYPE 1 từ điểm a đến điểm C
Như vậy tuyến a - C đi loại TYPE 1 của anh KIM đếm được tổng cộng là 3 chuyến (trong thực tế sẽ đếm trong 1 khoảng thời gian như tuần, tháng, năm,...), đây chính là cái cần thống kê. Tương tự với các tuyến khác, NAME khác, thời gian khác.

Mong anh / chị có phương án nào tối ưu hơn cho bài toán lắm yêu cầu rắc rối này thì giúp em với

Cảm ơn mọi người rất nhiều.

HieuCD · 10/5/19

Mr Joker đã viết:
Hai công thức của anh @HieuCD và @dazkangel em đã áp dụng rất ok cho bài test nho nhỏ của em !
Nhưng khi áp dụng vào thực tế công việc thì do số lượng dòng dữ liệu rất nhiều, tầm trên dưới 10000 dòng / tháng - khiến công thức rất nặng và chạy rất rất lâu anh / chị ạ.
Vấn đề nữa là trong ~10000 dòng hàng (nghĩa là rất nhiều), đúng như anh nói về biên độ chênh lệch giây, rõ ràng vẫn tồn tại việc chênh nhau nhiều số giây hơn (có thể lên đến vài phút) nhưng thực tế nó đúng rơi vào trường hợp 2 in 1 bởi miễn là cùng 1 người (NAME), lấy cùng loại TYPE 1, xuất phát cùng 1 nơi (START) và đến cùng 1 chỗ (LOCATION)
Trường hợp bằng hoặc chênh nhau 1 giây là chiếm đa số để mình nhận diện 2 in 1, nhưng thực tế vẫn tồn tại 1 vài trường hợp cũng thuộc diện này nhưng lại có thể chênh nhau nhiều giây hoặc lên đến vài phút. Mình không tính nó vào là diện 2 in 1 thì sẽ bị dư chuyến và không chuẩn cho việc tính tiền phải trả

Và em cũng rất mong muốn ngoài thống kê được TỔNG ra thì ta có thể thống kê "đếm" chi tiết được số chuyến TYPE 1 cụ thể cho từng "TUYẾN" (vì điểm đi - điểm đến khác nhau sẽ có giá tiền cũng khác nhau) đếm được bao nhiêu chuyến 1 tuyến bất kỳ của từng cá nhân, sẽ phục vụ cho việc lên báo cáo chi tiết số tiền phải trả cho từng tuyến + cho từng NAME trong 1 khoảng thời gian tùy chọn.
Ví dụ : theo file TEST thì từ 01/04 đến 12/04/2019
01/04/2019 anh KIM đi được 1 chuyến đôi TYPE 1 từ điểm a đến điểm C
03/04/2019 anh KIM đi được 1 chuyến đơn TYPE 1 từ điểm a đến điểm C
11/04/2019 anh KIM đi được 1 chuyến đơn TYPE 1 từ điểm a đến điểm C
Như vậy tuyến a - C đi loại TYPE 1 của anh KIM đếm được tổng cộng là 3 chuyến (trong thực tế sẽ đếm trong 1 khoảng thời gian như tuần, tháng, năm,...), đây chính là cái cần thống kê. Tương tự với các tuyến khác, NAME khác, thời gian khác.

Mong anh / chị có phương án nào tối ưu hơn cho bài toán lắm yêu cầu rắc rối này thì giúp em với Cảm ơn mọi người rất nhiều.

10.000 dòng dùng SumIfS khá nặng, còn hàm mảng sẽ đơ máy. Nếu bạn chấp nhận dùng code VBA thì thiết kế tất cả báo cáo cần thiết trên sheet thật để mình viết code

Phan Thế Hiệp · 10/5/19

huuthang_bd đã viết:
Vấn đề của thớt xong rồi thì tới vấn đề phát sinh

Công thức dưới đây là công thức thực tế đang quản lý trong sổ theo dõi ngân hàng, với mục đích chỉ nhằm trao đổi cho vui về công thức mảng.

Mạn phép anh @huuthang_bd, "múa rìu qua mắt thợ" chút

, giải thích sơ bộ những chỗ lạ cho các em tham khảo thêm:

Mã:

=IF(--C4>0,CHOOSE(IFERROR(1/(1/SUM(COUNTIF(E4,{"*hoan tien*","*ban ngoai te*","*Interest*"})*{1,2,3})),4),"Ht","Bn","TL","TH"),LOOKUP(B4,AGGREGATE(15,6,$B$4:$B$150/(ABS(A4-$A$4:$A$104)<2/86399),{1,2,3}^(SUMPRODUCT(N(ABS(A4-$A$4:$A$104)<2/86399))>1)),IF(SUM(COUNTIFS($A$4:$A$150,A4+{-1,0,1}/86400+10^-12,$E$4:$E$150,{"*RTM*";"*Rut*"})),{"Rv","Rp","R"},{"Pv","Pi","TC"})))

Enter fill xuống.

Trên, chọn giải pháp LOOKUP( "Giá trị tiền", "Mảng Vectơ chỉ hướng", "Ký hiệu chọn" )
Trong đó: "Mảng Vectơ chỉ hướng" làm theo như điều kiện đặt ra, tức sẽ tìm các ngày-giờ chỉ chênh lệch nhau 1 giây. Ở đây sẽ phát sinh 3 trường hợp:

Hoặc thỏa cả 3 dòng: lúc đó Mảng: {1,2,3}^(SUMPRODUCT(N(ABS(A4-$A$4:$A$104)<2/86399))>1) <=> {1,2,3}^(3>1) <=>{1,2,3}^1 = {1,2,3}. Ý sẽ lấy đủ 3 giá trị sắp theo nhỏ đến lớn.
Nếu chỉ có hai giá trị thỏa: : lúc đó Mảng: {1,2,3}^(SUMPRODUCT(..'Như trên'..)>1) <=> {1,2,3}^(2>1) <=>{1,2,3}^1 = {1,2,3}. Ý sẽ lấy đủ 3 giá trị sắp theo nhỏ đến lớn. Nhưng chỉ có hai giá trị {1,2}, còn thành phần lớn nhất sẽ báo lỗi. Cái này áp dụng được cho khoản Phí và tiền thuế VAT lúc nào cũng đi chung cùng Ngày-GIờ-Phút-Giây (cl:+-1). Ví dụ: nó trả về Mảng giá trị tiền Phí và VAT: {1000,10000,#N/A!}
Nếu chỉ có một giá trị thỏa: : lúc đó Mảng: {1,2,3}^(SUMPRODUCT(..'Như trên'..)>1) <=> {1,2,3}^(1>1) <=>{1,2,3}^0 = {1,1,1}. Ý sẽ lấy cùng một giá trị trả ra đủ 3 cột. Thường ứng vào các khoản thanh toán cho NCC cùng ngân hàng, hay giống như dạng Rút TGNH nhưng phân ra làm hai thời gian khi tính Phí rút và VAT của nó. Ví dụ: có 1 giá trị rút tiền mặt (dòng 90): 20Triệu, lúc đó mảng yêu cầu trả về giá trị nhỏ nhất: {1,1,1} => {20tr, 20tr, 20tr}.
Do cách sắp đặt trên nên lúc nào Giá trị thanh toán hay Giá trị rút tiền mặt cũng nằm ở vị trí thứ 3 (cao nhất) trong Vectơ chỉ hướng của Lookup().

Riêng "Mảng Ký hiệu chọn" phân thành hai trường hợp: Hoặc dành cho Rút tiền mặt, hoặc không phải. Đó cũng là điều kiện đếm: SUM(COUNTIFS($A$4:$A$150,A4+{-1,0,1}/86400+10^-12,$E$4:$E$150,{"*RTM*";"*Rut*"}))
Lưu ý:

A4+{-1,0,1}/86400+10^-12 là mảng theo chiều ngang (cột)
{"*RTM*";"*Rut*"} là mảng theo chiều dọc (dòng).

Chúc anh em ngày vui.
/-*+/

Mr Joker · 10/5/19

HieuCD đã viết:
10.000 dòng dùng SumIfS khá nặng, còn hàm mảng sẽ đơ máy. Nếu bạn chấp nhận dùng code VBA thì thiết kế tất cả báo cáo cần thiết trên sheet thật để mình viết code

Vâng, nếu anh giúp được em thì thật tốt quá

thực ra nếu dùng hàm thì em còn hiểu được đôi chút, nhưng để đáp ứng bài này thì em cũng biết hàm mảng sẽ không thể chạy nổi.
Dùng VBA thì em mù tịt, anh giúp em thì may quá. Em cảm ơn nhiều !

P/S: @Phan Thế Hiệp anh thiết kế cái báo cáo đó sang sheet khác với sheet dữ liệu giúp em nhé, vì sheet dữ liệu của em nó sẽ có tầm 10000 dòng là khá phức tạp rồi! Với thiết kế code tùy chỉnh được biên độ GIÂY để tính độ trùng thời gian được thì tốt quá ạ

Có 1 chút vấn đề em trình bày mong bác thiết kế VBA giúp em là trong bảng TEST cột TYPE em để là 1 hoặc 2 nhưng thực tế trong bảng dữ liệu công việc của em nó sẽ là 1xxx hoặc 2xxx, để xác định TYPE nào thì chỉ cần tách lấy ký tự đầu tiên của "1xxx hoặc 2xxx" sẽ được 1 hoặc 2 ra để thống kê là được ạ, phần xxx đằng sau không quan trọng.

HieuCD · 11/5/19

Mr Joker đã viết:
Vâng, nếu anh giúp được em thì thật tốt quá thực ra nếu dùng hàm thì em còn hiểu được đôi chút, nhưng để đáp ứng bài này thì em cũng biết hàm mảng sẽ không thể chạy nổi.
Dùng VBA thì em mù tịt, anh giúp em thì may quá. Em cảm ơn nhiều !

P/S: @Phan Thế Hiệp anh thiết kế cái báo cáo đó sang sheet khác với sheet dữ liệu giúp em nhé, vì sheet dữ liệu của em nó sẽ có tầm 10000 dòng là khá phức tạp rồi! Với thiết kế code tùy chỉnh được biên độ GIÂY để tính độ trùng thời gian được thì tốt quá ạ
Có 1 chút vấn đề em trình bày mong bác thiết kế VBA giúp em là trong bảng TEST cột TYPE em để là 1 hoặc 2 nhưng thực tế trong bảng dữ liệu công việc của em nó sẽ là 1xxx hoặc 2xxx, để xác định TYPE nào thì chỉ cần tách lấy ký tự đầu tiên của "1xxx hoặc 2xxx" sẽ được 1 hoặc 2 ra để thống kê là được ạ, phần xxx đằng sau không quan trọng.

Không có dữ liệu thật nên công thức dài dòng

Mã:

Function CountIfTime(ByVal TimeRng As Range, ByVal NameRng As Range, _
  ByVal TypeRng As Range, ByVal StartRng As Range, ByVal LocalRng As Range, _
  ByVal dTime As Double, ByVal dk As Variant)
  '=CountIfTime(Cot Time, Cot Name, Cot Type, Cot Start, Cot Location, Tieu Chuan Thoi Gian, INDEX((Dieu kien 1)*(Dieu kien 2) …  ,))
 
  Dim S, Dic As Object
  Dim i As Long, k As Long, q As Long, j As Long, n As Long
  Dim iKey As String, tmp, Test As Boolean
 
  Set Dic = CreateObject("scripting.dictionary")
  n = TimeRng.Rows.Count
  For i = 1 To n
    If dk(i, 1) Then
      iKey = NameRng(i, 1) & "#" & TypeRng(i, 1) & "#" & StartRng(i, 1) & "#" & LocalRng(i, 1)
      If Dic.exists(iKey) = False Then
        k = k + 1
        Dic.Add iKey, Array(TimeRng(i, 1).Value2)
      Else
        tmp = TimeRng(i, 1).Value2
        Test = True
        S = Dic.Item(iKey)
        q = UBound(S)
        For j = 0 To q
          If Abs(tmp - S(j)) < dTime Then Test = False: Exit For
        Next j
        If Test = True Then
          k = k + 1
          ReDim Preserve S(0 To q + 1)
          S(q + 1) = tmp
          Dic.Item(iKey) = S
        End If
      End If
    End If
  Next i
  CountIfTime = k
  Set Dic = Nothing
End Function

Xem cách dùng công thức trong File

Mr Joker · 19/5/19

HieuCD đã viết:

Không có dữ liệu thật nên công thức dài dòng

Mã:

Function CountIfTime(ByVal TimeRng As Range, ByVal NameRng As Range, _
  ByVal TypeRng As Range, ByVal StartRng As Range, ByVal LocalRng As Range, _
  ByVal dTime As Double, ByVal dk As Variant)
  '=CountIfTime(Cot Time, Cot Name, Cot Type, Cot Start, Cot Location, Tieu Chuan Thoi Gian, INDEX((Dieu kien 1)*(Dieu kien 2) …  ,))

  Dim S, Dic As Object
  Dim i As Long, k As Long, q As Long, j As Long, n As Long
  Dim iKey As String, tmp, Test As Boolean

  Set Dic = CreateObject("scripting.dictionary")
  n = TimeRng.Rows.Count
  For i = 1 To n
    If dk(i, 1) Then
      iKey = NameRng(i, 1) & "#" & TypeRng(i, 1) & "#" & StartRng(i, 1) & "#" & LocalRng(i, 1)
      If Dic.exists(iKey) = False Then
        k = k + 1
        Dic.Add iKey, Array(TimeRng(i, 1).Value2)
      Else
        tmp = TimeRng(i, 1).Value2
        Test = True
        S = Dic.Item(iKey)
        q = UBound(S)
        For j = 0 To q
          If Abs(tmp - S(j)) < dTime Then Test = False: Exit For
        Next j
        If Test = True Then
          k = k + 1
          ReDim Preserve S(0 To q + 1)
          S(q + 1) = tmp
          Dic.Item(iKey) = S
        End If
      End If
    End If
  Next i
  CountIfTime = k
  Set Dic = Nothing
End Function

Xem cách dùng công thức trong File

Em chào anh
Anh ơi, VBA em thì mù tịt nên em áp dụng đoạn code này của anh hoài mà vẫn chưa được

Cụ thể là em cần áp dụng cho bảng dữ liệu tầm 10000 dòng + bảng dữ liệu nằm ở 1 sheet riêng, bảng báo cáo nằm ở 1 sheet riêng (như file đính kèm), em viết rõ thiết kế và yêu cầu trong file anh giúp em hoàn thiện với nhé.
Em cảm ơn rất nhiều !

Mr Joker · 23/5/19

Em chào các anh / chị
Anh chị giúp em với !
Cảm ơn anh chị đã quan tâm

HieuCD · 23/5/19

Mr Joker đã viết:
Em chào anh
Anh ơi, VBA em thì mù tịt nên em áp dụng đoạn code này của anh hoài mà vẫn chưa được
Cụ thể là em cần áp dụng cho bảng dữ liệu tầm 10000 dòng + bảng dữ liệu nằm ở 1 sheet riêng, bảng báo cáo nằm ở 1 sheet riêng (như file đính kèm), em viết rõ thiết kế và yêu cầu trong file anh giúp em hoàn thiện với nhé.
Em cảm ơn rất nhiều !

Bạn nhập kết quả bằng tay và gởi lại file

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

Thành viên mới

Chuyên gia GPE

Chuyên gia GPE

-⍟-

File đính kèm

Chuyên gia GPE

-⍟-

Chuyên gia GPE

Thành viên mới

Chuyên gia GPE

File đính kèm

-⍟-

Chuyên gia GPE

-⍟-

Thành viên mới

Chuyên gia GPE

-⍟-

File đính kèm

Thành viên mới

File đính kèm

Chuyên gia GPE

File đính kèm

Thành viên mới

File đính kèm

Thành viên mới

Chuyên gia GPE

Facebook

Group