Tìm các sản phẩm có doanh thu cao nhất theo từng nhóm hàng (sử dụng Power Query)

Liên hệ QC
lehuong1409

lehuong1409

RILY
Tham gia
17/9/12
Bài viết
1,350
Được thích
1,571
Giới tính
Nữ
Chào các anh chị ạ
Hiện em có dữ liệu như file đính kèm
1717213303431.png
Dựa vào cột doanh thu em muốn tìm theo từng nhóm mỗi nhóm một vài mặt hàng có doanh thu lớn nhất, Số lượng mặt hàng cần lấy tại mỗi nhóm là khác nhau
1717213130749.png

Em đã giải bài toán này bằng hàm và cũng có tìm hiểu để thử giải bằng Power Query nhưng đoạn COUNTIFS với điều kiện >= doanh thu thì em chưa tìm được giải pháp. Kính mong anh chị em trong diễn đàn hỗ trợ em giải quyết bài toán này bằng Power Query ạ. Em xin chân thành cảm ơn
 

File đính kèm

  • Tìm TOP các sản phẩm có doanh thu cao nhất.xlsx
    169.7 KB · Đọc: 21
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Dùng code sau và không cần cột D:
JavaScript: 
let  
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},    
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        Sort = Table.Sort(Filter,{{"Doanh thu", Order.Descending}}),
        TopN = Table.FirstN(Sort,TOP[TOP]{n}),
        AddRank = Table.AddIndexColumn(TopN, "Thứ hạng", 1, 1, Int64.Type)
    in
        AddRank),
    Result =  Table.Combine(DataN)
in
    Result

1717219819298.png
 
Lần chỉnh sửa cuối:
Upvote 0 Downvote
ptm0412 đã viết:
Dùng code sau và không cần cột D:
JavaScript: 
let 
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},   
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        Sort = Table.Sort(Filter,{{"Doanh thu", Order.Descending}}),
        TopN = Table.FirstN(Sort,TOP[TOP]{n}),
        AddRank = Table.AddIndexColumn(TopN, "Thứ hạng", 1, 1, Int64.Type)
    in
        AddRank),
    Result =  Table.Combine(DataN)
in
    Result

View attachment 301299
Nhấp chuột vào đây để mở rộng...
Hiện cháu thực hiện và đang báo lỗi như sau:
1717223992335.png
Cháu có hướng rồi cháu sẽ tìm hiểu để hoàn thành với đề bài của mình ạ.
Cột D sử dụng khi cháu dùng hàm để ra được đáp án ạ. Cháu cảm ơn chú ạ
 
Upvote 0 Downvote
Upvote 0 Downvote
Upvote 0 Downvote
Dạ cháu/em đã sửa và làm được rồi ạ. Cháu cảm ơn chú @ptm0412 và chị @hoamattroicoi ạ
Bài đã được tự động gộp:

tranhungdao12a3 đã viết:
Bạn có thể thử giải pháp Dax sau:
View attachment 301329
Nhấp chuột vào đây để mở rộng...
Em cảm ơn anh đã phản hồi ạ, Cột check em tạo ra để làm hàm nên sử dụng trên cột Check thì em chỉ cần lọc là có kết quả rồi ạ. Query em mới tập sự nên còn phải học thêm nhiều ạ
 
Upvote 0 Downvote
Gửi bạn thêm cách, nếu đồng hạng thì lấy luôn.
Mã: 
let
    doanhthu = Excel.CurrentWorkbook(){[Name="doanhthu"]}[Content],
    top = Excel.CurrentWorkbook(){[Name="top"]}[Content],
    ket_qua = Table.Combine(Table.Group(doanhthu,"Nhóm sản phẩm",{"All", (tbl)=> Table.SelectRows(Table.AddRankColumn(tbl,"Check",{"Doanh thu",1},[RankKind = 1]), each [Check]<= top{[Nhóm=[Nhóm sản phẩm]]}[TOP])})[All])
in
    ket_qua
 

File đính kèm

  • Tìm TOP các sản phẩm có doanh thu cao nhất.xlsx
    109.5 KB · Đọc: 15
Upvote 0 Downvote
tranhungdao12a3 đã viết:
Bạn có thể Load Table TopN đó lên, sau đó Ranking theo TopN: Nếu cần Tùy biến Top thì sửa nguồn tại bảng TopN rồi Refresh là được:
View attachment 301337
Nhấp chuột vào đây để mở rộng...
Em đã tìm hiểu và làm theo được cách này ạ. Nhưng hiện tại hàm SELECTEDVALUE của em không có, em có tham khảo và dùng MIN MAX thay thế và đạt kết quả như mong muốn rồi ạ. Cảm ơn anh đã chia sẻ thêm một cách giải quyết vấn đề của em ạ. Em cảm ơn
Bài đã được tự động gộp:

hocexcel_1991 đã viết:
Gửi bạn thêm cách, nếu đồng hạng thì lấy luôn.
Mã: 
let
    doanhthu = Excel.CurrentWorkbook(){[Name="doanhthu"]}[Content],
    top = Excel.CurrentWorkbook(){[Name="top"]}[Content],
    ket_qua = Table.Combine(Table.Group(doanhthu,"Nhóm sản phẩm",{"All", (tbl)=> Table.SelectRows(Table.AddRankColumn(tbl,"Check",{"Doanh thu",1},[RankKind = 1]), each [Check]<= top{[Nhóm=[Nhóm sản phẩm]]}[TOP])})[All])
in
    ket_qua
Nhấp chuột vào đây để mở rộng...
Chào bạn, Cột Check mình đưa ra chỉ dùng khi mình chạy ra kết quả bằng công thức. Còn nếu sử dụng Power Query thì mình không muốn có thêm cột Check đó nữa. Cảm ơn bạn đã hỗ trợ
 
Upvote 0 Downvote
lehuong1409 đã viết:
Em đã tìm hiểu và làm theo được cách này ạ. Nhưng hiện tại hàm SELECTEDVALUE của em không có, em có tham khảo và dùng MIN MAX thay thế và đạt kết quả như mong muốn rồi ạ. Cảm ơn anh đã chia sẻ thêm một cách giải quyết vấn đề của em ạ. Em cảm ơn
Bài đã được tự động gộp:


Chào bạn, Cột Check mình đưa ra chỉ dùng khi mình chạy ra kết quả bằng công thức. Còn nếu sử dụng Power Query thì mình không muốn có thêm cột Check đó nữa. Cảm ơn bạn đã hỗ trợ
Nhấp chuột vào đây để mở rộng...
Cột "Check" đó không liên quan đến cột "Check" mà bạn tạo ở excel, nó là cột "Rank". Bạn ko muốn nó hiển thị thì thêm step xóa nó đi là được.
 
Upvote 0 Downvote
Bạn gửi lại giúp mình file bạn đã hỗ trợ tạo được không bạn? Vì mình chạy ra thì vẫn bị báo lỗi, mình tìm sửa chưa được nên thấy giá trị Check lại hiểu nhầm là bạn lấy thông tin ở cột Check. Cảm ơn bạn
 
Upvote 0 Downvote
lehuong1409 đã viết:
Bạn gửi lại giúp mình file bạn đã hỗ trợ tạo được không bạn? Vì mình chạy ra thì vẫn bị báo lỗi, mình tìm sửa chưa được nên thấy giá trị Check lại hiểu nhầm là bạn lấy thông tin ở cột Check. Cảm ơn bạn
Nhấp chuột vào đây để mở rộng...
Mình đình kèm file lại ở #9 rồi nhé
 
Upvote 0 Downvote
Học được hàm Table.AddRankColumn, nhờ nó lấy được đồng hạng mà không phải sort
JavaScript: 
let  
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},    
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        AddRank = Table.AddRankColumn(Filter,"Thứ hạng",{"Doanh thu",1},[RankKind = 1]),
        TopN = Table.SelectRows(AddRank,each [Thứ hạng] <= TOP[TOP]{n})
    in
        TopN),
    Result =  Table.Combine(DataN)
in
    Result

1717468915037.png
 
Upvote 0 Downvote
Tập tành M code với mọi người :)

JavaScript: 
let
    Source = Excel.CurrentWorkbook(){[Name="SalesGroup"]}[Content],
    GroupCondition = Excel.CurrentWorkbook(){[Name="Group"]}[Content],
    GroupedRows = Table.Group(Source, {"Group"},
        {{"Details", each Table.AddRankColumn(_,"Rank",{"Amount",1})}}),
    Expand = Table.Combine(GroupedRows[Details]),
    Filter = Table.AddColumn(GroupCondition, "Table", each
        let
        crGroup = _[Group],
        crNo = _[TOP],
        filter = Table.SelectRows(Expand,each crGroup = [Group] and crNo>=[Rank])
        in filter
),
    Combine = Table.Combine(Filter[Table])
in
    Combine
 
Upvote 0 Downvote
ptm0412 đã viết:
Cũng với dữ liệu trên, bạn thử lấy những dòng còn lại sau khi lấy Top như trên. Số nhỏ nhất có thứ hạng 1
Nhấp chuột vào đây để mở rộng...
Xem thử ổn ko anh.

JavaScript: 
let
    Source = Excel.CurrentWorkbook(){[Name="SalesGroup"]}[Content],
    GroupCondition = Excel.CurrentWorkbook(){[Name="Group"]}[Content],
    GroupedRows = Table.Group(Source, {"Group"},
        {{"Details", each Table.AddRankColumn(_,"Rank",{"Amount",0})}}),
    Expand = Table.Combine(GroupedRows[Details]),
    findMaxCount = Table.Group(Source, {"Group"},
        {"Count", each Table.RowCount(_), Int64.Type}),
    MergeWMaxCount = Table.NestedJoin(GroupCondition, {"Group"}, findMaxCount, {"Group"}, "findMaxCount", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergeWMaxCount, "findMaxCount", {"Count"}, {"Count"}),
    AddRemain = Table.SelectColumns(Table.AddColumn(Expanded, "Remain", each [Count] - [TOP], type number),{"Group", "Remain"}),
    Filter = Table.AddColumn(AddRemain, "Table", each
        let
        crGroup = _[Group],
        crNo = _[Remain],
        filter = Table.SelectRows(Expand,each crGroup = [Group] and crNo>=[Rank])
        in filter
),
    Combine = Table.Combine(Filter[Table])
in
    Combine
 
Upvote 0 Downvote
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

N
Cách đem một file Excel qua máy khác in mà không bị thay đổi kích thước mặc định
2
Trả lời
35
Đọc
26K
pthau72
pthau72
Kiều Mạnh
Remote Data Service - Dịch vụ dữ liệu từ xa Qua Internet
    • Thích
    • Yêu thích
Trả lời
12
Đọc
1K
Kiều Mạnh
Kiều Mạnh
PHONGGANHHAO
Công thức tính định mức nước bị sai
Trả lời
9
Đọc
134
PHONGGANHHAO
PHONGGANHHAO
GPE-Trợ Lý
  • Dán lên cao
Đại Tiệc Sinh Nhật GPE thứ 18 - TeamBuilding Đà Nẵng 2N1D [06-07/07/2024]
    • Thích
    • Yêu thích
    • Cảm ơn
2
Trả lời
22
Đọc
2K
PhanTuHuong
PhanTuHuong
C
Thảo luận về file KHSX
Trả lời
1
Đọc
34
Hoàng Tuấn 868
H
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom