Kết hợp pivot và subtotal để đếm không trùng???

[Huyn Park]

Chào các bạn. Mình là mem mới và đã tìm hiểu các bài với chủ đề "đếm ko trùng" nhưng chưa tìm thấy bài nào có trường hợp giống như mình nên đành phải viết lại topic để hỏi. Hiện mình có 1 bảng tính với 2 cột:

Cột 1 Cột 2
A 1
A 2
A 1
B 1
B 1
B 1
...
Bảng có rất nhiều các giá trị ở cột 1 và mình muốn đếm các giá trị không trùng ở cột 2. Hiện, mình đang filter từng giá trị cột 1 và dùng "remove duplicate" cột 2, sau đó chạy pivot table để đếm. Nhưng nếu bảng có hàng nghìn giá trị ở cột 1 thì dùng cách này ko ổn. Vậy có bạn nào giúp mình với. Cảm ơn nhiều.

 

[ptm0412]

Dùng Pivot table cho toàn bảng:
kéo thả cả 2 cột A và B vào Row
Kéo cột A vào Data

 

[Huyn Park]

Tks bạn nhiều. Cho mình hỏi là khi đưa cột A vào data để đếm thì lúc này giá trị được đếm vẫn bao gồm cả các giá trị trùng (nhìn rõ đc giá trị nào trùng và bị lặp bao nhiêu lần)??? Vậy làm sao để data lúc này đếm và tự bỏ các giá trị trùng? Mình đã thử hàm count và các hàm khác trong value field trong data.

 

[ptm0412]

Theo cách tôi nói ở trên, Pivot table đếm tất cả (có trùng), nhưng đếm riêng cho từng giá trị không trùng.
Muốn đếm xem có bao nhiêu giá trị không trùng thì cũng có cách. Nhưng trước tiên bạn đưa file mẫu 3 nhóm A, B, C như bài 1, đồng thời tự tạo 1 bảng kết quả cho tôi xem bạn muốn gì.

 

[Huyn Park]

Mình gửi file đính kèm nhé. Trong đó ghi chi tiết bảng mẫu (1 phần) và kết quả mong muốn. Xem giúp mình nhé. Tks bạn.

 

[ptm0412]

Làm thử xem sao:

1. Công thức mảng
=SUM(1/COUNTIF(C2:C13,C2:C13))

2. Pivot table

 

[Huyn Park]

Đúng là sử dụng cách pivot trong file bạn gửi thì ra đúng yêu cầu mình cần. J Nhưng mình rất muốn hỏi bạn logic để thực hiện theo cách này (khi phải kết hợp 2 cột x&y lại thành cột z và dùng hàm đếm giá trị z ở cột t). Để mà tư duy và nghĩ ra thì ko thể được. Cảm ơn bạn rất nhiều.

 

[Hanh LT]

cho mình hỏi chút, mình muốn đếm xem trong 1 năm khách hàng A mua hàng trong bao nhiêu lần ( mỗi lần mua nhiều mặt hàng) thì cách làm như thế nào trong pivot table. cảm ơn nhiều.

 

[ptm0412]

Đúng là sử dụng cách pivot trong file bạn gửi thì ra đúng yêu cầu mình cần. J Nhưng mình rất muốn hỏi bạn logic để thực hiện theo cách này (khi phải kết hợp 2 cột x&y lại thành cột z và dùng hàm đếm giá trị z ở cột t). Để mà tư duy và nghĩ ra thì ko thể được. Cảm ơn bạn rất nhiều.

nếu đếm riêng X hoặc riêng Y thì bị trùng rất nhiều, nên phải nối lại
Nối lại rồi nếu đếm chỉ được kết quả giống như pivot đếm cột X
Công thức T để đổi dòng đầu tiên thành 1, các dòng sau đổi thành 0. Sum chỉ là 1.

Logic nó nằm trong đầu, và ứng biến, chỉ có thể giải thích từng trường hợp cụ thể được, chứ không truyền hết được.

Gởi Hanh LT,
Có file mẫu thì làm được

 

[Hanh LT]

Nhờ chỉ giúp, Cảm ơn nhiều
View attachment PIVOT TABLE.rar

 

[ptm0412]

Nối 3 cột mã KH, số hóa đơn (mỗi số HĐ là 1 lần mua), và cột năm
Rồi y như vậy mà làm.

 

[Hanh LT]

Nối 3 cột mã KH, số hóa đơn (mỗi số HĐ là 1 lần mua), và cột năm
Rồi y như vậy mà làm.

cho em hỏi, em làm rồi, nhưng khi qia field list để kéo ra thì không có check 2. Xin chỉ dùm.

 

[ptm0412]

1. Phải bảo đảm rằng dữ liệu nguồn của Pivot bao gồm cả 2 cột mới tạo
2. Nếu 1 Pivot table đã làm trước khi chèn cột, thì phải refresh mới hiện ra.

 

[Hanh LT]

dạ, em quên k refresh, đã làm được rùi, cảm ơn ptm0412 nhiều!

 

[arexthena]

Làm thử xem sao:

1. Công thức mảng
=SUM(1/COUNTIF(C2:C13,C2:C13))

2. Pivot table

Công thức ở cột D thật tuyệt vời!

 

[arexthena]

Nhân tiện xin hỏi luôn bác PTM một câu khó hơn. Vậy trong trường hợp có thêm một trường ngày tháng trong bảng và mình muốn một cột đếm không trùng có thể tuỳ biến trong Pivot table theo ngày, theo tuần, theo tháng được không???

 

[ptm0412]

Nhân tiện xin hỏi luôn bác PTM một câu khó hơn. Vậy trong trường hợp có thêm một trường ngày tháng trong bảng và mình muốn một cột đếm không trùng có thể tuỳ biến trong Pivot table theo ngày, theo tuần, theo tháng được không???

Dùng Pivot và group cột ngày tháng theo tuần, tháng, năm, ...

 

[arexthena]

Dùng Pivot và group cột ngày tháng theo tuần, tháng, năm, ...

Hì Hì! Như vậy sẽ ra kết quả...sai Chú PTM ơi (Đọc sơ các topic diễn đàn biết chú lớn tuổi hơn cháu nhiều nên cháu xin gọi là chú nhé!) Giả sử chú group theo ngày thôi chẳng hạn, những item đã ra kết quả bằng 1 sẽ không được đếm ở ngày tiếp theo dù ngày tiếp theo có=> Khi sum lại sẽ ra kết quả sai.
Cháu xin lấy một bảng mẫu dữ liệu sale để làm ví dụ:
Cháu add vài cột theo phương pháp làm của chú.
Giá sử dùng sum cột F => chỉ ra được kết quả đúng khi làm total, nếu group theo ngày,tuần, tháng sẽ sai. Cột g chỉ dùng đc cho ngày, H cho tuần,I cho tháng.
Chú xem bảng pivot table bên cạnh để thấy sự khác biệt giữa 2 kết quả.
Phương pháp của chú rất hay (thật sự công thức quá quen thuộc mà trước giờ cháu chưa nghĩ tới). Nhưng ý cháu hỏi ở câu trên là liệu còn cách nào có thể tối ưu hơn đối với trường hợp này không (Thay vì phải làm nhiều cột cho mỗi cách kết hợp thì chỉ cần một cột chính và 1, 2 cột phụ thôi) => Đó mới là "bài toán khó" mà cháu định nói!

P/S: Excel mà có hàm Distincount như trong powerpivot thì mình khỏe quá!

 

[ptm0412]

Bạn hỏi trong topic này rồi bạn đưa 1 file khác hẳn. Các file trong topic này cần lấy danh sách duy nhất và đếm theo cách của file đó:

File thứ nhất đếm số lần không trùng trong toàn bộ dữ liệu, không có ngày tháng, tôi dùng 1 cột nối dữ liệu và 1 cột phụ.

File thứ hai đếm số lần không trùng trong toàn bộ dữ liệu, cũng dùng 1 cột nối dữ liệu và 1 cột phụ. Trong đó có cột ngày tháng. Điều kiện đếm không trùng là khác số hóa đơn và nhóm số hóa đơn theo ngày. Số hóa đơn mỗi ngày mỗi khác nhau, mỗi hóa đơn có thể mua 1 hoặc nhiều mặt hàng, điều này rất quan trọng. Tác giả muốn đếm số lần mua của khách hàng và số lần mua hàng bằng với số tờ hóa đơn. Cùng hóa đơn nhưng nhiều mặt hàng sẽ bị loại bớt.

File của bạn đưa lên, bạn không dùng cột nối dữ liệu mà dùng sumifs, cũng ok. Nhưng bạn không nói từ đầu là đếm theo điều kiện gì. Tên cột config thì chẳng có ý nghĩa gì và trùng ngày này qua ngày khác.

Ý nghĩa đếm không trùng là tháng này đếm rồi thì tháng sau không đếm nữa, ngày này đếm rồi thì ngày sau không đếm nữa. Tổng số đếm được trên tổng thời gian phải bằng tổng các số đếm được trong từng kỳ. Số lần mua trong 1 năm bằng số lần mua của từng tháng cộng lại.

File của bạn đếm kiểu gì mà nó khác nhau? Nếu đếm trên toàn bộ thời gian thì chỉ đếm 1/4 lần, đếm theo từng tháng (giả sử 4 tháng, mỗi tháng 1 lần) thì đếm đủ 4/4 lần?

Có thể đó là đếm theo yêu cầu của bạn, nhưng không phải đếm như đếm số lần mua của khách hàng. Việc bạn đếm tương đương với đếm số mặt hàng bán của chủ tiệm, tháng này bán gạo tháng sau cũng bán gạo, có vậy nó mới trùng tháng này tháng khác, tuần này tuần khác, và có thể đếm lại lần 2, lần 3, nếu nó nằm trong tháng khác.

 

[arexthena]

Xin lỗi chú cháu chưa giải thích rõ data. Dạ đúng như chú nói ạ! Mỗi mã configs tương đương với một loại hàng hoá, 5 chữ cái đầu của config tuơng ứng với tên Brand, mỗi dòng là một sản phẩm được bán trong cột ngày tương ứng. Vậy có cách nào trong trường hợp này không chú?