tranhungdao12a3
Thành viên thường trực
- Tham gia
- 29/11/07
- Bài viết
- 374
- Được thích
- 341
Sao mình tính không có sản phẩm nào đạt trên 50% của mỗi đơn hàng, không biết mình có hiểu ý sai bạn không? Bạn thử tính tay 1 số sản phẩm đạt trên 50% rồi gửi lên xem.Chào các anh chị!
Em có một câu hỏi nhờ anh chị giúp đỡ:
Em có dữ liệu khách hàng mua hàng các sản phẩm khác nhau, em muốn tìm ra sản phẩm được khách hàng thường xuyên mua, dữ liệu và câu hỏi như sau:
View attachment 286766
Cảm ơn các anh chị nhiều!
(Đoán mò): Định nghĩa về đơn hàng: mỗi ngày mua hàng của 1 khách hàng sẽ tính là 1 đơn hàng,Sao mình tính không có sản phẩm nào đạt trên 50% của mỗi đơn hàng, không biết mình có hiểu ý sai bạn không? Bạn thử tính tay 1 số sản phẩm đạt trên 50% rồi gửi lên xem.
Có nhiều chứ bạn, bạn đếm tổng số đơn hàng của khách hàng, sau đó sản phẩm nào xuất hiện >=1/2 số đơn hàng thì nó là sản phẩm thường xuyên thôiSao mình tính không có sản phẩm nào đạt trên 50% của mỗi đơn hàng, không biết mình có hiểu ý sai bạn không? Bạn thử tính tay 1 số sản phẩm đạt trên 50% rồi gửi lên xem.
Bạn tính vậy cũng được, do dữ liệu một số dòng phần số lượng =0 mình quên không sửa.(Đoán mò): Định nghĩa về đơn hàng: mỗi ngày mua hàng của 1 khách hàng sẽ tính là 1 đơn hàng,
vì thế Distinccount đơn hàng của khách hàng sẽ là
View attachment 286804
Logic tính vậy sẽ phức tạp anh. Theo em logic thử như vầy:Bài này viết code còn thấy gian nan.
Giả sử "các sản phẩm được mua cùng nhau" tạm xét theo cặp (2 loại sản phẩm).
Xét dữ liệu của 1 khách hàng.
Đầu tiên, lọc danh sách các mặt hàng xuất hiện nhiều hơn 1 lần ở các đơn hàng.
Tạo danh sách các cặp sản phẩm từ danh sách vừa lọc được ở bước trên.
Xét từng cặp sản phẩm đó, đếm số lần xuất hiện của chúng trong tất cả các đơn hàng. Đối chiếu số lần xuất hiện và xuất kết quả.
---
Trường hợp 3, 4, ..., n các sản phẩm được mua cùng nhau thì làm tương tự. Rất phức tạp.
Trường hợp = 50% thì cũng có thể chúng không được mua cùng nhau đâu.... tiếp đó sẽ so sánh nếu sản phẩm đó xuất hiện >=50% số đơn hàng của khách hàng là được.
Bạn làm cái Sales/Customer Analysis như thế này bắt buộc phải rõ quy trình Hóa đơn và Thanh toán Hóa đơn. Sau đó thiết kế CSDL, cố gắng chuẩn hóa đến bậc 3. Từ đó mới tính tiếp được....
Logic tính vậy sẽ phức tạp anh. Theo em logic thử như vầy:
...
Theo một cách logic như đã diễn giải thì có thể dùng Power Bi phân tích được, vấn đề phát sinh với dữ liệu lớn và hàng trăm sku, nếu nó chạy tổ hợp như vậy rất là nặng máy, nếu chạy 1,2 cái câu hỏi như vậy thì khả năng dẫn đến ngồi chơi xơi nước. Em vẫn nghĩ R hay Python sẽ mạnh hơn trong cái khoản phân tích này dù sao Power Bi chủ yếu mạnh mẽ ở phần trực quan hóa thôi. Em tìm kiếm giải pháp ngoài Power BiBạn làm cái Sales/Customer Analysis như thế này bắt buộc phải rõ quy trình Hóa đơn và Thanh toán Hóa đơn. Sau đó thiết kế CSDL, cố gắng chuẩn hóa đến bậc 3. Từ đó mới tính tiếp được.
Cái này làm xuôi rót hay không là do kỹ năng thiết kế bảng chứ không phải các công thức Excel hay VBA.
Nếu là bài toán đoán Mẫu (Model) thì dùng file CSV và dùng R hay Python. Đương nhirn là nếu chịu bỏ tiền ra thì có cả đống phần mềm chuyên nghiệp để tính.
Khách hàng | Tổng đơn | Sản phẩm | Lượng đơn |
---|---|---|---|
S0005360 | 26 | C-PIE02P | 19 |
S0005360 | 26 | C-PIE06P | 23 |
S0005360 | 26 | C-PIE12P | 26 |
S0005360 | 26 | CSTS06P | 21 |
S0005360 | 26 | CSTS12P(8) | 24 |
S0005360 | 26 | OStarKim Chi - Small | 14 |
S0005360 | 26 | OStarS.W - Small | 18 |
S0005360 | 26 | SwingNYSteak-Small | 16 |
S0005361 | 21 | C-PIE12P | 15 |
S0005361 | 21 | OStarS.W - Normal | 12 |
S0005361 | 21 | OStarS.W - Small | 11 |
S0005361 | 21 | SwingNYSteak - Normal | 11 |
S0005361 | 21 | SwingNYSteak-Small | 11 |
S0005362 | 24 | C-PIE06P | 14 |
S0005362 | 24 | C-PIE12P | 17 |
S0005362 | 24 | CSTS06P | 13 |
S0005362 | 24 | CSTS12P(8) | 17 |
S0005362 | 24 | OStarS.W - Normal | 14 |
S0005362 | 24 | OStarS.W - Small | 17 |
S0005362 | 24 | SwingNYSteak - Normal | 15 |
S0005362 | 24 | SwingNYSteak-Small | 17 |
S0005366 | 26 | C-PIE06P | 18 |
S0005366 | 26 | C-PIE12P | 23 |
S0005366 | 26 | CSTS06P | 21 |
S0005366 | 26 | CSTS12P(8) | 21 |
S0005366 | 26 | OStarKim Chi - Small | 16 |
S0005366 | 26 | OStarS.W - Small | 20 |
S0005366 | 26 | SwingChicken - Small | 15 |
S0005366 | 26 | SwingNYSteak-Small | 19 |
S0005367 | 25 | C-PIE02P | 15 |
S0005367 | 25 | C-PIE12P | 21 |
S0005367 | 25 | CSTS12P(8) | 19 |
S0005367 | 25 | OStarKim Chi - Small | 16 |
S0005367 | 25 | OStarS.W - Small | 17 |
S0005367 | 25 | SwingNYSteak-Small | 14 |
S0005373 | 26 | C-PIE06P | 15 |
S0005373 | 26 | C-PIE12P | 21 |
S0005373 | 26 | CSTS12P(8) | 17 |
S0005373 | 26 | OStarKim Chi - Small | 18 |
S0005373 | 26 | OStarPMai-TMuoi - Small | 14 |
S0005373 | 26 | OStarS.W - Small | 21 |
S0005373 | 26 | SwingNYSteak-Small | 23 |
S0005374 | 11 | OStarS.W - Small | 6 |
S0005375 | 26 | C-PIE02P | 16 |
S0005375 | 26 | C-PIE06P | 20 |
S0005375 | 26 | C-PIE12P | 25 |
S0005375 | 26 | CSTS02P | 14 |
S0005375 | 26 | CSTS06P | 17 |
S0005375 | 26 | CSTS12P(8) | 26 |
S0005375 | 26 | OStarKim Chi - Small | 20 |
S0005375 | 26 | OStarNatural - Small | 20 |
S0005375 | 26 | OStarPMai-TMuoi - Small | 22 |
S0005375 | 26 | OStarS.W - Small | 26 |
S0005375 | 26 | SwingNYSteak-Small | 25 |
S0005379 | 23 | C-PIE12P | 15 |
S0005379 | 23 | CSTS12P(8) | 13 |
S0005379 | 23 | OStarPMai-TMuoi - Small | 12 |
S0005379 | 23 | OStarS.W - Small | 17 |
S0005379 | 23 | SwingNYSteak - Normal | 13 |
S0005379 | 23 | SwingNYSteak-Small | 17 |
S0005380 | 23 | C-PIE06P | 13 |
S0005380 | 23 | C-PIE12P | 14 |
S0005380 | 23 | CSTS12P(8) | 15 |
S0005381 | 24 | OStarKim Chi - Normal | 18 |
S0005381 | 24 | OStarS.W - Normal | 18 |
S0005381 | 24 | OStarS.W - Small | 17 |
S0005381 | 24 | SwingChicken-Normal | 13 |
S0005381 | 24 | SwingNYSteak - Normal | 17 |
S0005381 | 24 | SwingNYSteak-Small | 15 |
S0005384 | 20 | C-PIE02P | 13 |
S0005384 | 20 | C-PIE12P | 16 |
S0005384 | 20 | CSTS12P(8) | 14 |
S0005384 | 20 | OStarS.W - Small | 12 |
S0005386 | 21 | C-PIE12P | 11 |
S0005386 | 21 | OStarKim Chi - Small | 13 |
S0005386 | 21 | OStarS.W - Small | 14 |
S0005386 | 21 | SwingNYSteak-Small | 14 |
S0005396 | 25 | C-PIE06P | 16 |
S0005396 | 25 | C-PIE12P | 21 |
S0005396 | 25 | CSTS12P(8) | 18 |
S0005396 | 25 | OStarKim Chi - Small | 16 |
S0005396 | 25 | OStarS.W - Small | 17 |
S0005396 | 25 | SwingNYSteak-Small | 17 |
S0005398 | 14 | C-PIE02P | 8 |
S0005398 | 14 | C-PIE06P | 10 |
S0005398 | 14 | C-PIE12P | 13 |
S0005398 | 14 | CSTS12P(8) | 13 |
S0005401 | 26 | C-PIE06P | 16 |
S0005401 | 26 | C-PIE12P | 21 |
S0005401 | 26 | C-PIE12P(Dark) | 14 |
S0005401 | 26 | CSTS12P(8) | 15 |
S0005401 | 26 | OStarKim Chi - Small | 25 |
S0005401 | 26 | OStarNatural - Small | 25 |
S0005401 | 26 | OStarPMai-TMuoi - Small | 22 |
S0005401 | 26 | OStarS.W - Small | 26 |
S0005401 | 26 | SwingCheese - Small | 16 |
S0005401 | 26 | SwingChicken - Small | 25 |
S0005401 | 26 | SwingMAXX - Bo Small | 16 |
S0005401 | 26 | SwingNYSteak-Small | 26 |
S0005404 | 26 | C-PIE06P | 15 |
S0005404 | 26 | C-PIE12P | 25 |
S0005404 | 26 | CSTS12P(8) | 14 |
S0005404 | 26 | OStarKim Chi - Small | 23 |
S0005404 | 26 | OStarNatural - Small | 18 |
S0005404 | 26 | OStarPMai-TMuoi - Small | 18 |
S0005404 | 26 | OStarS.W - Small | 23 |
S0005404 | 26 | SwingNYSteak-Small | 16 |
S0005415 | 26 | C-PIE02P | 18 |
S0005415 | 26 | C-PIE12P | 25 |
S0005415 | 26 | CSTS02P | 18 |
S0005415 | 26 | CSTS12P(8) | 20 |
S0005415 | 26 | OStarKim Chi - Normal | 14 |
S0005415 | 26 | OStarS.W - Normal | 16 |
S0005415 | 26 | OStarS.W - Small | 16 |
S0005415 | 26 | SwingNYSteak - Normal | 17 |
S0005416 | 26 | C-PIE02P | 14 |
S0005416 | 26 | C-PIE06P | 14 |
S0005416 | 26 | C-PIE12P | 22 |
S0005416 | 26 | CSTS12P(8) | 15 |
S0005416 | 26 | OStarKim Chi - Normal | 19 |
S0005416 | 26 | OStarS.W - Normal | 20 |
S0005416 | 26 | OStarS.W - Small | 14 |
S0005416 | 26 | SwingNYSteak - Normal | 19 |
S0005423 | 26 | C-PIE02P | 26 |
S0005423 | 26 | C-PIE06P | 20 |
S0005423 | 26 | C-PIE12P | 26 |
S0005423 | 26 | C-PIE12P(Dark) | 15 |
S0005423 | 26 | CSTS02P | 24 |
S0005423 | 26 | CSTS06P | 24 |
S0005423 | 26 | CSTS12P(8) | 25 |
S0005423 | 26 | OStarKim Chi - Normal | 21 |
S0005423 | 26 | OStarKim Chi - Small | 24 |
S0005423 | 26 | OStarNatural - Small | 24 |
S0005423 | 26 | OStarPMai-TMuoi - Small | 24 |
S0005423 | 26 | OStarS.W - Normal | 22 |
S0005423 | 26 | OStarS.W - Small | 25 |
S0005423 | 26 | SwingCheese - Small | 17 |
S0005423 | 26 | SwingChicken - Small | 24 |
S0005423 | 26 | SwingChicken-Normal | 16 |
S0005423 | 26 | SwingMAXX - Bo Normal | 14 |
S0005423 | 26 | SwingMAXX - Bo Small | 20 |
S0005423 | 26 | SwingNYSteak - Normal | 21 |
S0005423 | 26 | SwingNYSteak-Small | 25 |
Sản phẩm mua >50% số đơn như này chuẩn rồi anh, Vấn đề là ở câu số 2, cần trích ra sản phẩm nào mà cùng nhau xuất hiện ở >75% số đơn của mỗi khách hàng ý anh!Tôi lập bảng cấu trúc tương tự trên Access. Giả sử mỗi khách hàng trong một ngày chỉ có duy nhất một đơn hàng (có thể nhiều mặt hàng khác nhau), với những sản phẩm xuất hiện trong hơn 1/2 trong tổng số đơn của mỗi khách tôi query được kết quả như thế này. Không biết đúng không nhỉ?
Khách hàng Tổng đơn Sản phẩm Lượng đơn S0005360 26C-PIE02P 19S0005360 26C-PIE06P 23S0005360 26C-PIE12P 26S0005360 26CSTS06P 21S0005360 26CSTS12P(8) 24S0005360 26OStarKim Chi - Small 14S0005360 26OStarS.W - Small 18S0005360 26SwingNYSteak-Small 16S0005361 21C-PIE12P 15S0005361 21OStarS.W - Normal 12S0005361 21OStarS.W - Small 11S0005361 21SwingNYSteak - Normal 11S0005361 21SwingNYSteak-Small 11S0005362 24C-PIE06P 14S0005362 24C-PIE12P 17S0005362 24CSTS06P 13S0005362 24CSTS12P(8) 17S0005362 24OStarS.W - Normal 14S0005362 24OStarS.W - Small 17S0005362 24SwingNYSteak - Normal 15S0005362 24SwingNYSteak-Small 17S0005366 26C-PIE06P 18S0005366 26C-PIE12P 23S0005366 26CSTS06P 21S0005366 26CSTS12P(8) 21S0005366 26OStarKim Chi - Small 16S0005366 26OStarS.W - Small 20S0005366 26SwingChicken - Small 15S0005366 26SwingNYSteak-Small 19S0005367 25C-PIE02P 15S0005367 25C-PIE12P 21S0005367 25CSTS12P(8) 19S0005367 25OStarKim Chi - Small 16S0005367 25OStarS.W - Small 17S0005367 25SwingNYSteak-Small 14S0005373 26C-PIE06P 15S0005373 26C-PIE12P 21S0005373 26CSTS12P(8) 17S0005373 26OStarKim Chi - Small 18S0005373 26OStarPMai-TMuoi - Small 14S0005373 26OStarS.W - Small 21S0005373 26SwingNYSteak-Small 23S0005374 11OStarS.W - Small 6S0005375 26C-PIE02P 16S0005375 26C-PIE06P 20S0005375 26C-PIE12P 25S0005375 26CSTS02P 14S0005375 26CSTS06P 17S0005375 26CSTS12P(8) 26S0005375 26OStarKim Chi - Small 20S0005375 26OStarNatural - Small 20S0005375 26OStarPMai-TMuoi - Small 22S0005375 26OStarS.W - Small 26S0005375 26SwingNYSteak-Small 25S0005379 23C-PIE12P 15S0005379 23CSTS12P(8) 13S0005379 23OStarPMai-TMuoi - Small 12S0005379 23OStarS.W - Small 17S0005379 23SwingNYSteak - Normal 13S0005379 23SwingNYSteak-Small 17S0005380 23C-PIE06P 13S0005380 23C-PIE12P 14S0005380 23CSTS12P(8) 15S0005381 24OStarKim Chi - Normal 18S0005381 24OStarS.W - Normal 18S0005381 24OStarS.W - Small 17S0005381 24SwingChicken-Normal 13S0005381 24SwingNYSteak - Normal 17S0005381 24SwingNYSteak-Small 15S0005384 20C-PIE02P 13S0005384 20C-PIE12P 16S0005384 20CSTS12P(8) 14S0005384 20OStarS.W - Small 12S0005386 21C-PIE12P 11S0005386 21OStarKim Chi - Small 13S0005386 21OStarS.W - Small 14S0005386 21SwingNYSteak-Small 14S0005396 25C-PIE06P 16S0005396 25C-PIE12P 21S0005396 25CSTS12P(8) 18S0005396 25OStarKim Chi - Small 16S0005396 25OStarS.W - Small 17S0005396 25SwingNYSteak-Small 17S0005398 14C-PIE02P 8S0005398 14C-PIE06P 10S0005398 14C-PIE12P 13S0005398 14CSTS12P(8) 13S0005401 26C-PIE06P 16S0005401 26C-PIE12P 21S0005401 26C-PIE12P(Dark) 14S0005401 26CSTS12P(8) 15S0005401 26OStarKim Chi - Small 25S0005401 26OStarNatural - Small 25S0005401 26OStarPMai-TMuoi - Small 22S0005401 26OStarS.W - Small 26S0005401 26SwingCheese - Small 16S0005401 26SwingChicken - Small 25S0005401 26SwingMAXX - Bo Small 16S0005401 26SwingNYSteak-Small 26S0005404 26C-PIE06P 15S0005404 26C-PIE12P 25S0005404 26CSTS12P(8) 14S0005404 26OStarKim Chi - Small 23S0005404 26OStarNatural - Small 18S0005404 26OStarPMai-TMuoi - Small 18S0005404 26OStarS.W - Small 23S0005404 26SwingNYSteak-Small 16S0005415 26C-PIE02P 18S0005415 26C-PIE12P 25S0005415 26CSTS02P 18S0005415 26CSTS12P(8) 20S0005415 26OStarKim Chi - Normal 14S0005415 26OStarS.W - Normal 16S0005415 26OStarS.W - Small 16S0005415 26SwingNYSteak - Normal 17S0005416 26C-PIE02P 14S0005416 26C-PIE06P 14S0005416 26C-PIE12P 22S0005416 26CSTS12P(8) 15S0005416 26OStarKim Chi - Normal 19S0005416 26OStarS.W - Normal 20S0005416 26OStarS.W - Small 14S0005416 26SwingNYSteak - Normal 19S0005423 26C-PIE02P 26S0005423 26C-PIE06P 20S0005423 26C-PIE12P 26S0005423 26C-PIE12P(Dark) 15S0005423 26CSTS02P 24S0005423 26CSTS06P 24S0005423 26CSTS12P(8) 25S0005423 26OStarKim Chi - Normal 21S0005423 26OStarKim Chi - Small 24S0005423 26OStarNatural - Small 24S0005423 26OStarPMai-TMuoi - Small 24S0005423 26OStarS.W - Normal 22S0005423 26OStarS.W - Small 25S0005423 26SwingCheese - Small 17S0005423 26SwingChicken - Small 24S0005423 26SwingChicken-Normal 16S0005423 26SwingMAXX - Bo Normal 14S0005423 26SwingMAXX - Bo Small 20S0005423 26SwingNYSteak - Normal 21S0005423 26SwingNYSteak-Small 25
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
tbl0 = Table.Group(Source, {"Khách hàng"}, {{"Count", each List.Count(List.Distinct([Ngày]))}}),
tbl1 = Table.Group(Source, {"Khách hàng", "Sản Phẩm"}, {{"OderSP", each Table.RowCount(_), Int64.Type}}),
tbl2 = Table.NestedJoin(tbl1, {"Khách hàng"}, tbl0, {"Khách hàng"}, "tbl1", JoinKind.LeftOuter),
tbl3 = Table.ExpandTableColumn(tbl2, "tbl1", {"Count"}, {"TotalOder"}),
tbl4 = Table.AddColumn(tbl3, "DK", each [OderSP]/[TotalOder]),
tbl5 = Table.SelectRows(tbl4, each [DK] > 0.5),
KQ = Table.RemoveColumns(tbl5,{"DK"})
in
KQ
=LET(so,A2:A4386,sp,B2:B4386,sl,C2:C4386,d,D2:D4386,_ab,so&"|"&sp,uso,UNIQUE(so),od_1,MAP(uso,LAMBDA(x,ROWS(UNIQUE(FILTER(d,so=x))))),uss,UNIQUE(_ab),od_2,MAP(uss,LAMBDA(y,ROWS(FILTER(sp,_ab=y)))),kh,TEXTBEFORE(uss,"|"),_sp,TEXTAFTER(uss,"|"),od_3,XLOOKUP(kh,uso,od_1),FILTER(HSTACK(kh,od_3,_sp,od_2),od_2/od_3>0.5))
Cảm ơn bạn, mình chưa xài E365 nên chưa coi công thức được, mình sẽ xem sau khi càiDựa theo kết quả #9
Power Query
Công thức E365Mã:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], tbl0 = Table.Group(Source, {"Khách hàng"}, {{"Count", each List.Count(List.Distinct([Ngày]))}}), tbl1 = Table.Group(Source, {"Khách hàng", "Sản Phẩm"}, {{"OderSP", each Table.RowCount(_), Int64.Type}}), tbl2 = Table.NestedJoin(tbl1, {"Khách hàng"}, tbl0, {"Khách hàng"}, "tbl1", JoinKind.LeftOuter), tbl3 = Table.ExpandTableColumn(tbl2, "tbl1", {"Count"}, {"TotalOder"}), tbl4 = Table.AddColumn(tbl3, "DK", each [OderSP]/[TotalOder]), tbl5 = Table.SelectRows(tbl4, each [DK] > 0.5), KQ = Table.RemoveColumns(tbl5,{"DK"}) in KQ
Mã:=LET(so,A2:A4386,sp,B2:B4386,sl,C2:C4386,d,D2:D4386,_ab,so&"|"&sp,uso,UNIQUE(so),od_1,MAP(uso,LAMBDA(x,ROWS(UNIQUE(FILTER(d,so=x))))),uss,UNIQUE(_ab),od_2,MAP(uss,LAMBDA(y,ROWS(FILTER(sp,_ab=y)))),kh,TEXTBEFORE(uss,"|"),_sp,TEXTAFTER(uss,"|"),od_3,XLOOKUP(kh,uso,od_1),FILTER(HSTACK(kh,od_3,_sp,od_2),od_2/od_3>0.5))
Option Explicit
Sub test()
Dim lr&, i&, j&, k&, s, rng, arr(), id As String, cell As Range
Dim dicKH As Object, dicSP As Object
Set dicKH = CreateObject("Scripting.Dictionary")
Set dicSP = CreateObject("Scripting.Dictionary")
With Sheets("data")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
rng = .Range("A2:D" & lr).Value
ReDim arr(1 To 100000, 1 To 2)
For i = 1 To UBound(rng)
id = rng(i, 1) & "|" & rng(i, 4)
If Not dicKH.exists(id) Then
dicKH.Add id, rng(i, 2)
k = k + 1: arr(k, 1) = rng(i, 1): arr(k, 2) = rng(i, 4)
Else
dicKH(id) = dicKH(id) & "|" & rng(i, 3)
End If
If Not dicSP.exists(rng(i, 2)) Then dicSP.Add rng(i, 2), ""
Next
End With
With Sheets("Tonghop")
.Cells.ClearContents
.Range("B1").Value = Sheets("Data").Range("A1").Value: .Range("C1").Value = Sheets("Data").Range("D1").Value
.Range("B2").Resize(k, 2).Value = arr
.Range("D1").Resize(1, dicSP.Count).Value = dicSP.keys
.Range("D2").Resize(dicKH.Count, dicSP.Count).Formula = "=COUNTIFS(data!$A$2:$A$4387,$B2,data!$D$2:$D$4387,$C2,data!$B$2:$B$4387,D$1)"
rng = .Range("B2").Resize(dicKH.Count, 1).Value
End With
dicKH.RemoveAll
With Sheets("Thongke")
.Cells.ClearContents
.Range("B1").Value = Sheets("Tonghop").Range("B1").Value: .Range("C1").Value = "So don hang"
For i = 1 To UBound(rng)
If Not dicKH.exists(rng(i, 1)) Then
dicKH.Add rng(i, 1), 1
Else
dicKH(rng(i, 1)) = dicKH(rng(i, 1)) + 1
End If
Next
.Range("B2").Resize(dicKH.Count, 1).Value = WorksheetFunction.Transpose(dicKH.keys)
.Range("C2").Resize(dicKH.Count, 1).Value = WorksheetFunction.Transpose(dicKH.items)
.Range("D1").Resize(1, dicSP.Count).Value = dicSP.keys
With .Range("D2").Resize(dicKH.Count, dicSP.Count)
.Formula = "=IF(SUMIF(Tonghop!$B$2:$B$10000,$B2,Tonghop!D$2:D$10000)/$C2>0.5,SUMIF(Tonghop!$B$2:$B$10000,$B2,Tonghop!D$2:D$10000)/$C2,"""")"
.NumberFormat = "0%"
End With
For Each cell In .Range("D1").Resize(1, dicSP.Count)
If WorksheetFunction.Sum(cell.Resize(dicKH.Count, 1)) = 0 Then cell.ClearContents
Next
.Range("D1").Resize(1, dicSP.Count).SpecialCells(xlBlanks).EntireColumn.Delete
End With
End Sub
Anh cho em xin với ạBài này có sẵn bài giải bằng Python rồi đó.
Và có cả bài giải kết hợp Python + Power BI.
Anh cho em xin với ạ
Đây ạ ! Nhìn code cũng hay hay ạ https://github.com/Gaelim/Populatiry-EngineBạn tìm theo từ khoá này nhé.
"Frequently bought together items with Python and Power BI"
Điều kiện >=75% số đơn tức là sản phẩm nào mà cùng nhau xuất hiện ở >=75% số đơn của khách hàng thì trích ra ạ.Bài toán tương đối phức tạp nên mình sẽ làm qua các bước sau:
1- Tạo sheet "Tonghop"
Mỗi đơn hàng là 1 dòng
Mỗi sản phẩm là 1 cột
Nộii dung trong bảng là đếm số lần xuất hiện của mỗi sản phẩm
2- Tạo sheet "Thongke"
Mỗi khách hàng là 1 dòng, và có thống kên số đơn hàng của khách hàng đó
Mỗi sản phẩm là 1 cột. Cho mỗi sản phẩm, thống kê % xuất hiện của sản phẩm đó cho mỗi khách hàng. Chỉ liệt kê số liệu >50%, nếu dưới thì tự động loại bỏ ra.
3- Về yêu cầu trích ra "các SP cùng nhau xuất hiện >75%", mình vẫn chưa hiểu yêu cầu này.
Nếu được thì căn cứ vào các bảng kết quả nói trên, bạn làm tay một vài trường hợp ra xem sao.
PHP:Option Explicit Sub test() Dim lr&, i&, j&, k&, s, rng, arr(), id As String, cell As Range Dim dicKH As Object, dicSP As Object Set dicKH = CreateObject("Scripting.Dictionary") Set dicSP = CreateObject("Scripting.Dictionary") With Sheets("data") lr = .Cells(Rows.Count, "A").End(xlUp).Row rng = .Range("A2:D" & lr).Value ReDim arr(1 To 100000, 1 To 2) For i = 1 To UBound(rng) id = rng(i, 1) & "|" & rng(i, 4) If Not dicKH.exists(id) Then dicKH.Add id, rng(i, 2) k = k + 1: arr(k, 1) = rng(i, 1): arr(k, 2) = rng(i, 4) Else dicKH(id) = dicKH(id) & "|" & rng(i, 3) End If If Not dicSP.exists(rng(i, 2)) Then dicSP.Add rng(i, 2), "" Next End With With Sheets("Tonghop") .Cells.ClearContents .Range("B1").Value = Sheets("Data").Range("A1").Value: .Range("C1").Value = Sheets("Data").Range("D1").Value .Range("B2").Resize(k, 2).Value = arr .Range("D1").Resize(1, dicSP.Count).Value = dicSP.keys .Range("D2").Resize(dicKH.Count, dicSP.Count).Formula = "=COUNTIFS(data!$A$2:$A$4387,$B2,data!$D$2:$D$4387,$C2,data!$B$2:$B$4387,D$1)" rng = .Range("B2").Resize(dicKH.Count, 1).Value End With dicKH.RemoveAll With Sheets("Thongke") .Cells.ClearContents .Range("B1").Value = Sheets("Tonghop").Range("B1").Value: .Range("C1").Value = "So don hang" For i = 1 To UBound(rng) If Not dicKH.exists(rng(i, 1)) Then dicKH.Add rng(i, 1), 1 Else dicKH(rng(i, 1)) = dicKH(rng(i, 1)) + 1 End If Next .Range("B2").Resize(dicKH.Count, 1).Value = WorksheetFunction.Transpose(dicKH.keys) .Range("C2").Resize(dicKH.Count, 1).Value = WorksheetFunction.Transpose(dicKH.items) .Range("D1").Resize(1, dicSP.Count).Value = dicSP.keys With .Range("D2").Resize(dicKH.Count, dicSP.Count) .Formula = "=IF(SUMIF(Tonghop!$B$2:$B$10000,$B2,Tonghop!D$2:D$10000)/$C2>0.5,SUMIF(Tonghop!$B$2:$B$10000,$B2,Tonghop!D$2:D$10000)/$C2,"""")" .NumberFormat = "0%" End With For Each cell In .Range("D1").Resize(1, dicSP.Count) If WorksheetFunction.Sum(cell.Resize(dicKH.Count, 1)) = 0 Then cell.ClearContents Next .Range("D1").Resize(1, dicSP.Count).SpecialCells(xlBlanks).EntireColumn.Delete End With End Sub
Sản phẩm B mua cùng sản phẩm A, ngược lại chưa chắc Sản phẩm A mua cùng sản phẩm BChào các anh chị!
Em có một câu hỏi nhờ anh chị giúp đỡ:
Em có dữ liệu khách hàng mua hàng các sản phẩm khác nhau, em muốn tìm ra sản phẩm được khách hàng thường xuyên mua, dữ liệu và câu hỏi như sau:
Cảm ơn các anh chị nhiều!
Option Explicit
Sub XYZ()
Dim arr(), a, SP(), res(), res2(), dh$, dhSP$, dic As Object, dicDH As Object
Dim sRow&, sR$, i&, j&, r&, id&, jd&, sSP&, k&, k2&, sDon#, iSP#
Set dic = CreateObject("scripting.dictionary")
Set dicDH = CreateObject("scripting.dictionary")
arr = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
sRow = UBound(arr)
ReDim res(1 To sRow, 1 To 2)
For i = 1 To sRow
If arr(i, 3) > 0 Then
If dic.exists(arr(i, 2)) = False Then
sSP = sSP + 1 'So San Pham
res(sSP, 2) = arr(i, 2)
dic.Add arr(i, 2), sSP
End If
dh = arr(i, 1) & "|" & arr(i, 4)
dhSP = dh & "|" & arr(i, 2)
If dic.exists(dhSP) = False Then
dic.Add dhSP, ""
If dicDH.exists(dh) = False Then
sDon = sDon + 1 'So Don hang
dicDH.Add dh, Array(arr(i, 2))
Else
a = dicDH(dh) 'San Pham cua Don hang
ReDim Preserve a(0 To UBound(a) + 1)
a(UBound(a)) = arr(i, 2)
dicDH(dh) = a
End If
End If
End If
Next i
ReDim SP(1 To sSP, 0 To sSP)
ReDim res2(1 To sSP, 1 To sSP)
For Each a In dicDH.items
sRow = UBound(a)
For i = 0 To sRow
id = dic(a(i))
SP(id, 0) = SP(id, 0) + 1
For j = 0 To sRow
jd = dic(a(j))
If id <> jd Then
SP(id, jd) = SP(id, jd) + 1
End If
Next j
Next i
Next a
sDon = sDon * 0.5 '50% So Don hang
For i = 1 To sSP
If SP(i, 0) > sDon Then
k = k + 1
res(k, 1) = res(i, 2)
End If
iSP = SP(i, 0) * 0.75 '75% So San Pham
jd = 1
For j = 1 To sSP
If SP(i, j) > iSP Then
If jd = 1 Then
k2 = k2 + 1
res2(k2, 1) = res(i, 2)
End If
jd = jd + 1
res2(k2, jd) = res(j, 2)
End If
Next j
Next i
Range("F6").Resize(k) = res 'SP mua thuong xuyên
Range("H6").Resize(k2, sSP) = res2 'Sp mua cung nhau
End Sub
Kết quả như vầy anh ạ:Sản phẩm B mua cùng sản phẩm A, ngược lại chưa chắc Sản phẩm A mua cùng sản phẩm B
Kiểm tra lại
Mã:Option Explicit Sub XYZ() Dim arr(), a, SP(), res(), res2(), dh$, dhSP$, dic As Object, dicDH As Object Dim sRow&, sR$, i&, j&, r&, id&, jd&, sSP&, k&, k2&, sDon#, iSP# Set dic = CreateObject("scripting.dictionary") Set dicDH = CreateObject("scripting.dictionary") arr = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value sRow = UBound(arr) ReDim res(1 To sRow, 1 To 2) For i = 1 To sRow If arr(i, 3) > 0 Then If dic.exists(arr(i, 2)) = False Then sSP = sSP + 1 'So San Pham res(sSP, 2) = arr(i, 2) dic.Add arr(i, 2), sSP End If dh = arr(i, 1) & "|" & arr(i, 4) dhSP = dh & "|" & arr(i, 2) If dic.exists(dhSP) = False Then dic.Add dhSP, "" If dicDH.exists(dh) = False Then sDon = sDon + 1 'So Don hang dicDH.Add dh, Array(arr(i, 2)) Else a = dicDH(dh) 'San Pham cua Don hang ReDim Preserve a(0 To UBound(a) + 1) a(UBound(a)) = arr(i, 2) dicDH(dh) = a End If End If End If Next i ReDim SP(1 To sSP, 0 To sSP) ReDim res2(1 To sSP, 1 To sSP) For Each a In dicDH.items sRow = UBound(a) For i = 0 To sRow id = dic(a(i)) SP(id, 0) = SP(id, 0) + 1 For j = 0 To sRow jd = dic(a(j)) If id <> jd Then SP(id, jd) = SP(id, jd) + 1 End If Next j Next i Next a sDon = sDon * 0.5 '50% So Don hang For i = 1 To sSP If SP(i, 0) > sDon Then k = k + 1 res(k, 1) = res(i, 2) End If iSP = SP(i, 0) * 0.75 '75% So San Pham jd = 1 For j = 1 To sSP If SP(i, j) > iSP Then If jd = 1 Then k2 = k2 + 1 res2(k2, 1) = res(i, 2) End If jd = jd + 1 res2(k2, jd) = res(j, 2) End If Next j Next i Range("F6").Resize(k) = res 'SP mua thuong xuyên Range("H6").Resize(k2, sSP) = res2 'Sp mua cung nhau End Sub
Khách hàng | Tổng đơn | 75% đơn | Số đơn | Sản phẩm mua cùng 75% số Bill |
S0005360 | 26 | 19.5 | 20 đơn | Sản phẩm: CSTS06P,CSTS12P(8) |
S0005360 | 26 | 19.5 | 21 đơn | Sản phẩm: C-PIE06P,C-PIE12P,CSTS06P |
S0005360 | 26 | 19.5 | 22 đơn | Sản phẩm: C-PIE06P,CSTS12P(8) |
S0005360 | 26 | 19.5 | 23 đơn | Sản phẩm: C-PIE06P,C-PIE12P |
S0005360 | 26 | 19.5 | 24 đơn | Sản phẩm: C-PIE12P,CSTS12P(8) |
S0005366 | 26 | 19.5 | 20 đơn | Sản phẩm: C-PIE12P,CSTS12P(8) |
S0005373 | 26 | 19.5 | 21 đơn | Sản phẩm: OStarS.W - Small,SwingNYSteak-Small |
S0005375 | 26 | 19.5 | 20 đơn | Sản phẩm: C-PIE06P,C-PIE12P,CSTS12P(8),OStarKim Chi - Small,OStarNatural - Small,OStarS.W - Small,SwingNYSteak-Small |
S0005375 | 26 | 19.5 | 21 đơn | Sản phẩm: C-PIE12P,OStarPMai-TMuoi - Small,SwingNYSteak-Small |
S0005375 | 26 | 19.5 | 22 đơn | Sản phẩm: CSTS12P(8),OStarPMai-TMuoi - Small,OStarS.W - Small |
S0005375 | 26 | 19.5 | 24 đơn | Sản phẩm: C-PIE12P,SwingNYSteak-Small |
S0005375 | 26 | 19.5 | 25 đơn | Sản phẩm: C-PIE12P,CSTS12P(8),OStarS.W - Small,SwingNYSteak-Small |
S0005375 | 26 | 19.5 | 26 đơn | Sản phẩm: CSTS12P(8),OStarS.W - Small |
S0005381 | 24 | 18 | 18 đơn | Sản phẩm: OStarKim Chi - Normal,OStarS.W - Normal |
S0005398 | 14 | 10.5 | 12 đơn | Sản phẩm: C-PIE12P,CSTS12P(8) |
S0005401 | 26 | 19.5 | 20 đơn | Sản phẩm: C-PIE12P,OStarKim Chi - Small,SwingChicken - Small |
S0005401 | 26 | 19.5 | 21 đơn | Sản phẩm: C-PIE12P,OStarNatural - Small,OStarPMai-TMuoi - Small,OStarS.W - Small,SwingNYSteak-Small |
S0005401 | 26 | 19.5 | 22 đơn | Sản phẩm: OStarKim Chi - Small,OStarPMai-TMuoi - Small,OStarS.W - Small,SwingChicken - Small,SwingNYSteak-Small |
S0005401 | 26 | 19.5 | 24 đơn | Sản phẩm: OStarKim Chi - Small,OStarNatural - Small,SwingChicken - Small |
S0005401 | 26 | 19.5 | 25 đơn | Sản phẩm: OStarKim Chi - Small,OStarNatural - Small,OStarS.W - Small,SwingChicken - Small,SwingNYSteak-Small |
S0005401 | 26 | 19.5 | 26 đơn | Sản phẩm: OStarS.W - Small,SwingNYSteak-Small |
S0005404 | 26 | 19.5 | 22 đơn | Sản phẩm: C-PIE12P,OStarKim Chi - Small,OStarS.W - Small |
S0005404 | 26 | 19.5 | 23 đơn | Sản phẩm: OStarKim Chi - Small,OStarS.W - Small |
S0005415 | 26 | 19.5 | 20 đơn | Sản phẩm: C-PIE12P,CSTS12P(8) |
S0005423 | 26 | 19.5 | 20 đơn | Sản phẩm: C-PIE02P,C-PIE06P,C-PIE12P,CSTS02P,CSTS06P,CSTS12P(8),OStarKim Chi - Normal,OStarKim Chi - Small,OStarNatural - Small,OStarPMai-TMuoi - Small,OStarS.W - Normal,OStarS.W - Small,SwingChicken - Small,SwingMAXX - Bo Small,SwingNYSteak - Normal,SwingNYSteak-Small |
S0005423 | 26 | 19.5 | 21 đơn | Sản phẩm: C-PIE02P,C-PIE12P,CSTS12P(8),OStarKim Chi - Normal,OStarNatural - Small,OStarS.W - Normal,OStarS.W - Small,SwingNYSteak - Normal,SwingNYSteak-Small |
S0005423 | 26 | 19.5 | 22 đơn | Sản phẩm: C-PIE02P,C-PIE12P,CSTS02P,CSTS06P,OStarKim Chi - Small,OStarNatural - Small,OStarPMai-TMuoi - Small,OStarS.W - Normal,SwingChicken - Small |
S0005423 | 26 | 19.5 | 23 đơn | Sản phẩm: CSTS02P,CSTS06P,CSTS12P(8),OStarKim Chi - Small,OStarNatural - Small,OStarPMai-TMuoi - Small,OStarS.W - Small,SwingChicken - Small,SwingNYSteak-Small |
S0005423 | 26 | 19.5 | 24 đơn | Sản phẩm: C-PIE02P,C-PIE12P,CSTS02P,CSTS06P,CSTS12P(8),OStarKim Chi - Small,OStarNatural - Small,OStarPMai-TMuoi - Small,OStarS.W - Small,SwingChicken - Small,SwingNYSteak-Small |
S0005423 | 26 | 19.5 | 25 đơn | Sản phẩm: C-PIE02P,C-PIE12P,CSTS12P(8),OStarS.W - Small,SwingNYSteak-Small |
S0005423 | 26 | 19.5 | 26 đơn | Sản phẩm: C-PIE02P,C-PIE12P |
CSTS06P,CSTS12P(8),
|
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2