Hàm đếm loại bỏ đếm trùng với nhiều điều kiện

[thuong-minh]

Dear các bác,

Em có một file dữ liệu nguồn: thống kê phòng với 2 chỉ tiêu dựa trên cột H - ngày đi, và cột I - số phòng.
Và một file dữ liệu đích: chứa kết quả tính toán dựa trên dữ liệu nguồn.

Căn cứ theo ngày đi. Trong cùng 1 ngày đi, nếu có nhiều khách cùng ở một phòng, có ngày đến và ngày đi giống nhau, thì chỉ tính một khách, loại bỏ những dòng khác bị trùng.

Ví dụ:
TT Họ tên Ngày Đến Ngày khách đi Phòng Đêm ở
13 Khách 1 25/07/2017 01/08/2017 1302 7
16 Khách 2 31/07/2017 01/08/2017 1602 1
17 Khách 3 31/07/2017 01/08/2017 1602 1
18 Khách 4 31/07/2017 01/08/2017 1602 1
19 Khách 5 31/07/2017 01/08/2017 1603 1
20 Khách 6 31/07/2017 01/08/2017 1603 1

Kết quả mong muốn tìm được:
- Đếm số phòng: Ngày 01/08/2017: có 03 phòng
- Đếm số đêm: Ngày 01/08/2017: có 09 đêm

Câu hỏi đưa ra: sử dụng công thức thế nào để đạt được 2 kết quả trên a.

Lưu ý, dòng số thứ tự là khác nhau ạ, không phải dòng liền kề nhau.

Em đã thử dùng hàm Sumproduct để loại bỏ đếm trùng, nhưng làm sai công thức nên mãi đến giờ em vẫn đang đếm thủ công bằng tay. Đau khổ.

Kính mong các bác chỉ giáo giùm em với a. Em xin cảm ơn và hậu tạ (cái này nhớ chỉ bảo kỹ a )

 

[quocgiacan]

Dear các bác,

Em có một file dữ liệu nguồn: thống kê phòng với 2 chỉ tiêu dựa trên cột H - ngày đi, và cột I - số phòng.
Và một file dữ liệu đích: chứa kết quả tính toán dựa trên dữ liệu nguồn.

Căn cứ theo ngày đi. Trong cùng 1 ngày đi, nếu có nhiều khách cùng ở một phòng, có ngày đến và ngày đi giống nhau, thì chỉ tính một khách, loại bỏ những dòng khác bị trùng.

Ví dụ:
TT Họ tên Ngày Đến Ngày khách đi Phòng Đêm ở
13 Khách 1 25/07/2017 01/08/2017 1302 7
16 Khách 2 31/07/2017 01/08/2017 1602 1
17 Khách 3 31/07/2017 01/08/2017 1602 1
18 Khách 4 31/07/2017 01/08/2017 1602 1
19 Khách 5 31/07/2017 01/08/2017 1603 1
20 Khách 6 31/07/2017 01/08/2017 1603 1

Kết quả mong muốn tìm được:
- Đếm số phòng: Ngày 01/08/2017: có 03 phòng
- Đếm số đêm: Ngày 01/08/2017: có 09 đêm

Câu hỏi đưa ra: sử dụng công thức thế nào để đạt được 2 kết quả trên a.

Lưu ý, dòng số thứ tự là khác nhau ạ, không phải dòng liền kề nhau.

Em đã thử dùng hàm Sumproduct để loại bỏ đếm trùng, nhưng làm sai công thức nên mãi đến giờ em vẫn đang đếm thủ công bằng tay. Đau khổ.

Kính mong các bác chỉ giáo giùm em với a. Em xin cảm ơn và hậu tạ (cái này nhớ chỉ bảo kỹ a )

1. Đếm số phòng:

   =SUMPRODUCT(($H$2:$H$63="01/08/2017"*1)/COUNTIFS($G$2:$G$63,$G$2:$G$63,$H$2:$H$63,$H$2:$H$63,$I$2:$I$63,$I$2:$I$63))

2. Đếm số đêm:

   =SUMPRODUCT(($H$2:$H$63="01/08/2017"*1)*$J$2:$J$63/COUNTIFS($G$2:$G$63,$G$2:$G$63,$H$2:$H$63,$H$2:$H$63,$I$2:$I$63,$I$2:$I$63))

Có thể ghi ngày muốn truy lục, vd: K1=01/08/2017, thì thay vào công thức chỗ "01/08/2017"*1 bằng $K$1

Chúc bạn ngày vui
p/s: khuyên bạn trên diễn đàn GPE này đừng sử dụng từ "hậu tạ" dù nó xuất phát từ lòng thành của bạn. Cái mà anh em GPE cần: là bạn sử dụng "món quà" cho thích hợp là "điều kiện đủ" rồi.

 

[thuong-minh]

dạ, em cảm ơn bác.
Đấy, em còn "xanh" lắm, nên cũng cần dc các bác "uốn nắn" cho chín dần a

 

[thuong-minh]

cho em hỏi ý nghĩa của từng công thức đc ko a. Em biết công thức ok rồi, nhưng ko hiểu nội dung của công thức a

 

[thuong-minh]

1. Đếm số phòng:

   =SUMPRODUCT(($H$2:$H$63="01/08/2017"*1)/COUNTIFS($G$2:$G$63,$G$2:$G$63,$H$2:$H$63,$H$2:$H$63,$I$2:$I$63,$I$2:$I$63))

2. Đếm số đêm:

   =SUMPRODUCT(($H$2:$H$63="01/08/2017"*1)*$J$2:$J$63/COUNTIFS($G$2:$G$63,$G$2:$G$63,$H$2:$H$63,$H$2:$H$63,$I$2:$I$63,$I$2:$I$63))

Có thể ghi ngày muốn truy lục, vd: K1=01/08/2017, thì thay vào công thức chỗ "01/08/2017"*1 bằng $K$1

Chúc bạn ngày vui

Em đã thử và thành công. Tuy nhiên, khi làm, nếu có dòng trắng trong sheet dữ liệu nguồn, thì công thức sẽ không nhận. Khi em xóa dòng trắng đi thì công thức chạy bình thường nhé.

 

[dazkangel]

Gửi bạn 1 cách khác tham khảo:
Đếm số phòng:

=SUMPRODUCT((H2:H63="1/8/17"*1)*(MATCH(I2:I63,I2:I63,0)=ROW(A1:A62)))

Đếm số đêm:

=SUMPRODUCT(J2:J63*(H2:H63="1/8/17"*1)*(MATCH(I2:I63,I2:I63,0)=ROW(A1:A62)))

 

[quocgiacan]

cho em hỏi ý nghĩa của từng công thức đc ko a. Em biết công thức ok rồi, nhưng ko hiểu nội dung của công thức a

Bạn xem tham khảo cách giải thích và tìm hiểu trong file dữ liệu (tôi nghĩ cũng tượng tự hao hao bài của bạn) theo đường link sau: http://www.giaiphapexcel.com/diendan/threads/Đếm-dữ-liệu-bỏ-dữ-liệu-trùng.128102/#post-803129.

Chúc bạn gày vui.

 

[quocgiacan]

Gửi bạn 1 cách khác tham khảo:
Đếm số phòng:

=SUMPRODUCT((H2:H63="1/8/17"*1)*(MATCH(I2:I63,I2:I63,0)=ROW(A1:A62)))

Đếm số đêm:

=SUMPRODUCT(J2:J63*(H2:H63="1/8/17"*1)*(MATCH(I2:I63,I2:I63,0)=ROW(A1:A62)))

Cẩn thận trong yêu cầu của chủ thớt

Căn cứ theo ngày đi. Trong cùng 1 ngày đi, nếu có nhiều khách cùng ở một phòng, có ngày đến và ngày đi giống nhau, thì chỉ tính một khách, loại bỏ những dòng khác bị trùng

Ví dụ: điều chỉnh dòng G18 thành 25/07/2017 sẽ hiểu liền. kết quả đúng phải là 4 và 16.

Đôi khi ngắn rất có hại vì tạo lầm tưởng kết quả đúng cho người tạo lúc đầu, nhưng dữ liệu biến động nó sẽ lộ nhược điểm ra ngay.

Chúc em ngày vui.

 

[dazkangel]

Cẩn thận trong yêu cầu của chủ thớt

Ví dụ: điều chỉnh dòng G18 thành 25/07/2017 sẽ hiểu liền. kết quả đúng phải là 4 và 16.

Đôi khi ngắn rất có hại vì tạo lầm tưởng kết quả đúng cho người tạo lúc đầu, nhưng dữ liệu biến động nó sẽ lộ nhược điểm ra ngay.

Chúc em ngày vui.

Khà khà hôm qua em đã ngộ ra rồi mà lười chưa sửa vì chủ bài yêu cầu cả ngày đến và đi
Chắc do ham làm nhanh chưa đọc kỹ đề