đếm và lọc theo điều kiện nhất định (1 người xem)

[npv]

Tôi có một ví dụ về tính số lượng khách hàng đã mua một mặt hàng cụ thể và lọc danh sách khách hàng đã mua hàng nhưng chưa biết làm thế nào. Mong các bác chỉ giáo. Thanks.

 

[cadafi]

Tôi có một ví dụ về tính số lượng khách hàng đã mua một mặt hàng cụ thể và lọc danh sách khách hàng đã mua hàng nhưng chưa biết làm thế nào. Mong các bác chỉ giáo. Thanks.

Bạn xem file đính kèm nhé! Cả hai câu hỏi đều trong 1 bảng Pivotable! Bạn xem sheet("Report") nhé!

 

[hoangdanh282vn]

Tôi có một ví dụ về tính số lượng khách hàng đã mua một mặt hàng cụ thể và lọc danh sách khách hàng đã mua hàng nhưng chưa biết làm thế nào. Mong các bác chỉ giáo. Thanks.

Tại ô G26:

=SUMPRODUCT(--((MATCH(A3:A21&C3:C21,A3:A21&C3:C21,0)=ROW(A3:A21)-2)*C3:C21=1))

 

[npv]

Bác ca_dafi ơi cho em hỏi là nếu số lượng khách hàng là thay đổi theo từng tháng nhưng em muốn làm một file sẵn có được không? Nghĩa là khoảng dữ liệu em lấy rộng ra, như vậy vùng dữ liệu có rất nhiều dữ liệu trắng khi lên bảng pivotable có ảnh hưởng gì không anh nhỉ?

Bác hoangdanh282vn ơi bác có thể giải thích rõ các hai cái công thức đó được không? À mà sao khi em thêm dòng vào thì phần đếm vẫn chuẩn nhưng mà phần chiết xuất lại không được. Và hôm qua em quên mất một yêu cầu là phần chiết xuất phải sắp xếp theo tên khách hàng, nếu làm như cách của anh ca_dafi thì rất ngon lành nhưng em vẫn băn khoăn về dữ liệu trắng còn nếu làm như cách của anh thì không phải lo về khoảng trắng đó. Em gửi file lại Anh xem giúp.

 

[cadafi]

Bác ca_dafi ơi cho em hỏi là nếu số lượng khách hàng là thay đổi theo từng tháng nhưng em muốn làm một file sẵn có được không? Nghĩa là khoảng dữ liệu em lấy rộng ra, như vậy vùng dữ liệu có rất nhiều dữ liệu trắng khi lên bảng pivotable có ảnh hưởng gì không anh nhỉ?

Bác hoangdanh282vn ơi bác có thể giải thích rõ các hai cái công thức đó được không? À mà sao khi em thêm dòng vào thì phần đếm vẫn chuẩn nhưng mà phần chiết xuất lại không được. Và hôm qua em quên mất một yêu cầu là phần chiết xuất phải sắp xếp theo tên khách hàng, nếu làm như cách của anh ca_dafi thì rất ngon lành nhưng em vẫn băn khoăn về dữ liệu trắng còn nếu làm như cách của anh thì không phải lo về khoảng trắng đó. Em gửi file lại Anh xem giúp.

Chào bạn, Bạn đang băng khoăn về dữ liệu trắng đúng hôn! Cách giải quyết rất đơn giản là dùng Name động cho Pivotable, khi có dữ liệu mới, chỉ cần bạn click chuột phải vào Pivotable và chọn Refresh data là xong.

Cách tạo Name động đơn giản là bạn có thể dùng Name với Offset như sau:
Căn cứ theo file của bạn gửi lên, ta sẽ có hai sheet, MỘt sheet DATA để lưu dữ liệu hàng ngày, hàng tháng. Một sheet Report để lấy dữ liệu theo yêu cầu.

Ta đặt một Name có tên [DATA} như sau (vào Insert/Name/Define)

DATA = OFFSET(DATA!$A$1,,,COUNTA(DATA!$A:$A)+1,3)

Sau đó thiết lập Range cho Pivotable là Data (xem hình)

Việc còn lại của bạn là gắp và thả thôi!

Chúc bạn thành công!

 

[hoangdanh282vn]

Bác hoangdanh282vn ơi bác có thể giải thích rõ các hai cái công thức đó được không? À mà sao khi em thêm dòng vào thì phần đếm vẫn chuẩn nhưng mà phần chiết xuất lại không được. Và hôm qua em quên mất một yêu cầu là phần chiết xuất phải sắp xếp theo tên khách hàng, nếu làm như cách của anh ca_dafi thì rất ngon lành nhưng em vẫn băn khoăn về dữ liệu trắng còn nếu làm như cách của anh thì không phải lo về khoảng trắng đó. Em gửi file lại Anh xem giúp.

Đó là do mình làm theo dữ liệu sẵn có của bạn, sử dụng tham chiếu tĩnh nên khi bạn thay đổi vùng dữ liệu thì phải chỉnh lại công thức cho phù hợp.

Mình gửi lại cho bạn cách dùng Name động, sẽ linh động hơn khi thêm dữ liệu hay insert dòng.

Cách làm này sử dụng công thức mảng kết hợp với Name động. Để có thể đọc được ý nghĩa của các công thức thì bạn xem thêm các bài về công thức mảng nha.

File mình gửi dùng Excel 2007

 

[npv]

Cám ơn anh ca_dafi nhiều, bác đã cho em một cái cần câu. Sau một hồi mò mẫm (bởi đây là lần đầu tiên làm với pivotable) cũng câu được con cá là kết quả được như ý. Nhưng em vẫn có một chút thắc mắc về công thức, bởi em chưa hiểu lắm cái công thức này:

DATA = OFFSET(DATA!$A$1,,,COUNTA(DATA!$A:$A)+1,3)

Nếu số cột của em tăng lên là 10, thì em thay số 3 thành số 10 phải không bác.

 

[cadafi]

Cám ơn anh ca_dafi nhiều, bác đã cho em một cái cần câu. Sau một hồi mò mẫm (bởi đây là lần đầu tiên làm với pivotable) cũng câu được con cá là kết quả được như ý. Nhưng em vẫn có một chút thắc mắc về công thức, bởi em chưa hiểu lắm cái công thức này:
Nếu số cột của em tăng lên là 10, thì em thay số 3 thành số 10 phải không bác.

Đúng như vậy bạn à!
Bạn để ý ba điểm chính đối với công thức này như sau:

DATA = OFFSET(DATA!$A$1,,,COUNTA(DATA!$A:$A)+1,3)

Trong đó:

DATA!$A$1:Bắt đầu từ vị trí ô A1 của sheet DATACOUNTA(DATA!$A:$A)+1: mở rộng xuống với số dòng bằng với giá trị mà hàm Counta() đếm được tại cột A
3: mở rộng ra ba cột

Để tổng quát hơn nữa, số cột sẽ tự động tăng, giảm thì bạn dùng công thức sau:

DATA = OFFSET(DATA!$A$1,,,COUNTA(DATA!$A:$A)+1,Counta(Data!$1:$1))