Xin các cao thủ giúp đỡ làm lại bảng dữ liệu bằng power query

TomBui · 22/6/22

Em có một bảng tính gồm rất nhiều loại hàng, em muốn dùng power query để lọc ra những loại hàng đang làm và tính điểm chỉ tiêu. Các các giúp em với. Cám ơn mọi người trước!
Dưới đây là mẫu bảng em muốn thông qua power query để làm ra:

ptm0412 · 23/6/22

Tôi làm đến đây và bạn làm tiếp:

HTML:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Độ dày (um)", type text}, {"Kích thước", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Mặt hàng", each [#"Một/Hai kính"] & "/" & [Loại pin] & "/" & [#"Độ dày (um)"] & "/" &[Kích thước]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Tổn hao", "Mặt hàng", "Loại", "Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Tổn hao", "Mặt hàng", "Loại"}, {{"Value", each List.Sum([Value]), type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Loại] <> null and [Loại] <> "Tổn hao")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Loại]), "Loại", "Value", List.Sum)
in
    #"Pivoted Column"

TomBui · 23/6/22

ptm0412 đã viết:

Tôi làm đến đây và bạn làm tiếp:

HTML:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Độ dày (um)", type text}, {"Kích thước", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Mặt hàng", each [#"Một/Hai kính"] & "/" & [Loại pin] & "/" & [#"Độ dày (um)"] & "/" &[Kích thước]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Tổn hao", "Mặt hàng", "Loại", "Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Tổn hao", "Mặt hàng", "Loại"}, {{"Value", each List.Sum([Value]), type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Loại] <> null and [Loại] <> "Tổn hao")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Loại]), "Loại", "Value", List.Sum)
in
    #"Pivoted Column"

View attachment 277774

Mình cũng mày mò làm được đến cột tổn hao rồi, chỉ là cột tỉ trọng và điểm số cần đặt công thức. Nhưng trong power query công thức không áp dụng giống bảng tính excel bên ngoài.
Ví dụ: Tỉ trọng = Số lượng hàng một kính/P4/180/166 : Sum(tất cả các loại hàng)
Nên mong muốn mọi người hỗ trợ thêm hai cột đó.
Trân trọng!

ptm0412 · 23/6/22

TomBui đã viết:
Ví dụ: Tỉ trọng = Số lượng hàng một kính/P4/180/166 : Sum(tất cả các loại hàng)

Thêm 3 câu

Mã:

    RemovedColumns1 = Table.RemoveColumns(#"Pivoted Column",{"Hàn hỏng", "Cắt hỏng", "Lỗi NVL"}),
    SumSL = List.Sum(RemovedColumns1[Số lượng đưa vào]),
    AddTyTrong = Table.AddColumn(RemovedColumns1,"TyTrong", each [Số lượng đưa vào]/ SumSL)

TomBui · 23/6/22

ptm0412 đã viết:

Thêm 3 câu

Mã:

    RemovedColumns1 = Table.RemoveColumns(#"Pivoted Column",{"Hàn hỏng", "Cắt hỏng", "Lỗi NVL"}),
    SumSL = List.Sum(RemovedColumns1[Số lượng đưa vào]),
    AddTyTrong = Table.AddColumn(RemovedColumns1,"TyTrong", each [Số lượng đưa vào]/ SumSL)

Bạn ơi, có thể gửi mình file excel mà bạn sửa được không? Mình làm cứ bị báo lỗi ở AddTytrong.

VetMini · 23/6/22

ptm0412 đã viết:
Tôi làm đến đây và bạn làm tiếp:
...

Lên chức "tay bó bột" rồi hở

TomBui · 23/6/22

TomBui đã viết:
Bạn ơi, có thể gửi mình file excel mà bạn sửa được không? Mình làm cứ bị báo lỗi ở AddTytrong.

Mình làm được rồi cám ơn bạn. Hỏi thêm mình có thể hoà ô đầu tiên "Tỉ lệ tổn hao" như hình dưới trong power query không?

kbw1 · 23/6/22

TomBui đã viết:
Mình làm được rồi cám ơn bạn. Hỏi thêm mình có thể hoà ô đầu tiên "Tỉ lệ tổn hao" như hình dưới trong power query không?
View attachment 277779

Đây là xuất ra Pivot rồi làm chứ,

TomBui · 23/6/22

kbw1 đã viết:
Đây là xuất ra Pivot rồi làm chứ,

Hình trên là pivot, ý mình hỏi bạn ptm là có thể hoà ô được trong power query không ấy.

kbw1 · 23/6/22

TomBui đã viết:
Hình trên là pivot, ý mình hỏi bạn ptm là có thể hoà ô được trong power query không ấy.

Sao mình làm kiểu gì nó cũng ra thế này, mà không ra số giống của bạn nhỉ?
Cái cột mục tiêu toàn ra 0.01

Tổn hao	Mặt hàng	Cắt hỏng	Hàn hỏng	Lỗi NVL	Mục tiêu	Số lượng đưa vào	Tổn hao	Tổng lỗi
Tổn hao pin	Hai kính/Đơn tinh/160/166	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/160/182	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/160/210	-	-	-	-	-	-	-
	Hai kính/Đơn tinh/165/166	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/165/182	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/165/210	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/170/166	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/170/182	-	-	-	0.01	-	-	-
	Hai kính/Đơn tinh/170/210	-	-	-	0.01	-	-	-
	Hai kính/P4/180/166	-	-	-	0.01	-	-	-
	Một kính/Đơn tinh/160/166	90.00	924.00	166.00	0.01	368,380.00	0.01	1,180.00
	Một kính/Đơn tinh/160/210	-	-	-	-	-	-	-
	Một kính/Đơn tinh/165/166	3,318.00	26,140.00	3,920.00	0.01	15,954,014.00	0.01	33,378.00
	Một kính/Đơn tinh/165/182	-	-	-	0.01	-	-	-
	Một kính/Đơn tinh/165/210	-	-	-	0.01	-	-	-
	Một kính/Đơn tinh/170/166	6,908.00	44,724.00	7,518.00	0.01	36,769,646.00	0.01	58,512.00
	Một kính/Đơn tinh/170/182	-	-	-	0.01	-	-	-
	Một kính/Đơn tinh/170/210	-	-	-	0.01	-	-	-
	Một kính/Đơn tinh/175/210	-	-	-	0.01	-	-	-
	Một kính/P4/180/166	-	-	-	0.01	-	-	-
	Một kính/P5/182/166	-	-	-	0.01	-	-	-

TomBui · 23/6/22

kbw1 đã viết:
Sao mình làm kiểu gì nó cũng ra thế này, mà không ra số giống của bạn nhỉ?
Cái cột mục tiêu toàn ra 0.01

Tổn hao Mặt hàng Cắt hỏng Hàn hỏng Lỗi NVL Mục tiêu Số lượng đưa vào Tổn hao Tổng lỗi
Tổn hao pin Hai kính/Đơn tinh/160/166 - - - 0.01 - - -
Hai kính/Đơn tinh/160/182 - - - 0.01 - - -
Hai kính/Đơn tinh/160/210 - - - - - - -
Hai kính/Đơn tinh/165/166 - - - 0.01 - - -
Hai kính/Đơn tinh/165/182 - - - 0.01 - - -
Hai kính/Đơn tinh/165/210 - - - 0.01 - - -
Hai kính/Đơn tinh/170/166 - - - 0.01 - - -
Hai kính/Đơn tinh/170/182 - - - 0.01 - - -
Hai kính/Đơn tinh/170/210 - - - 0.01 - - -
Hai kính/P4/180/166 - - - 0.01 - - -
Một kính/Đơn tinh/160/166 90.00 924.00 166.00 0.01 368,380.00 0.01 1,180.00
Một kính/Đơn tinh/160/210 - - - - - - -
Một kính/Đơn tinh/165/166 3,318.00 26,140.00 3,920.00 0.01 15,954,014.00 0.01 33,378.00
Một kính/Đơn tinh/165/182 - - - 0.01 - - -
Một kính/Đơn tinh/165/210 - - - 0.01 - - -
Một kính/Đơn tinh/170/166 6,908.00 44,724.00 7,518.00 0.01 36,769,646.00 0.01 58,512.00
Một kính/Đơn tinh/170/182 - - - 0.01 - - -
Một kính/Đơn tinh/170/210 - - - 0.01 - - -
Một kính/Đơn tinh/175/210 - - - 0.01 - - -
Một kính/P4/180/166 - - - 0.01 - - -
Một kính/P5/182/166 - - - 0.01 - - -

cái đó là do dữ liệu ảnh mẫu khác thời điểm với dữ liệu trong file excel nên số liệu khác nhau, file ảnh là trước đó tớ làm thủ công kết hợp với pivot. Tớ up lên ý muốn đưa dữ liệu về form mẫu như vậy.

kbw1 · 23/6/22

TomBui đã viết:
cái đó là do dữ liệu ảnh mẫu khác thời điểm với dữ liệu trong file excel nên số liệu khác nhau, file ảnh là trước đó tớ làm thủ công kết hợp với pivot. Tớ up lên ý muốn đưa dữ liệu về form mẫu như vậy.

Query mình đang làm nó ra thế này?
Không hiểu sao đẩy được cái cột Loại nó thành chỉ tiêu ngang thế kia nhỉ?
Dạng như mình đang làm này là dùng Pivot tập hợp,
Chưa hiểu ý định của bạn làm gì mà đẩy nó kiểu pivot trên query luôn vậy.

Bài đã được tự động gộp: 23/6/22

ptm0412 đã viết:

Tôi làm đến đây và bạn làm tiếp:

HTML:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Độ dày (um)", type text}, {"Kích thước", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Mặt hàng", each [#"Một/Hai kính"] & "/" & [Loại pin] & "/" & [#"Độ dày (um)"] & "/" &[Kích thước]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Tổn hao", "Mặt hàng", "Loại", "Attribute", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Tổn hao", "Mặt hàng", "Loại"}, {{"Value", each List.Sum([Value]), type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Loại] <> null and [Loại] <> "Tổn hao")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Loại]), "Loại", "Value", List.Sum)
in
    #"Pivoted Column"

View attachment 277774

Em làm theo các bước của bác nhưng đến bước này hình như sai hay sao ấy ạ

hocexcel_1991 · 23/6/22

Gửi bạn file, xem đúng ý không?

ptm0412 · 23/6/22

kbw1 đã viết:
Em làm theo các bước của bác nhưng đến bước này hình như sai hay sao ấy ạ

Bước Unpivot của tôi là

Mã:

= Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value")

Của bạn thiếu "Loại" nên Loại trở thành 1 atttribute

TomBui đã viết:
Mình làm được rồi cám ơn bạn. Hỏi thêm mình có thể hoà ô đầu tiên "Tỉ lệ tổn hao" như hình dưới trong power query không?

Không.
Thử tạo pivot table từ query mới tạo.

kbw1 · 23/6/22

ptm0412 đã viết:
Bước Unpivot của tôi là

Mã:

= Table.UnpivotOtherColumns(#"Changed Type", {"Tổn hao", "Một/Hai kính", "Loại pin", "Độ dày (um)", "Kích thước", "Loại"}, "Attribute", "Value")

Của bạn thiếu "Loại" nên Loại trở thành 1 atttribute

Không.
Thử tạo pivot table từ query mới tạo.

View attachment 277786

Bác cho em hỏi thêm chút ạ,
Nếu để dạng cột như thế kia ở query lúc dùng pivot nó vẫn ra được ạ,
Em thấy để các chỉ tiêu số lượng đưa vào, hàn.... ở dạng 1 cột thì dùng được pivot chứ ạ.

ptm0412 · 23/6/22

kbw1 đã viết:
Bác cho em hỏi thêm chút ạ,
Nếu để dạng cột như thế kia ở query lúc dùng pivot nó vẫn ra được ạ,
Em thấy để các chỉ tiêu số lượng đưa vào, hàn.... ở dạng 1 cột thì dùng được pivot chứ ạ.

Bạn thử xem. Tôi thấy rằng làm như vậy không tính được tỷ trọng và cột tính điểm

TomBui · 23/6/22

kbw1 đã viết:
Query mình đang làm nó ra thế này?
Không hiểu sao đẩy được cái cột Loại nó thành chỉ tiêu ngang thế kia nhỉ?
Dạng như mình đang làm này là dùng Pivot tập hợp,
Chưa hiểu ý định của bạn làm gì mà đẩy nó kiểu pivot trên query luôn vậy.
View attachment 277780

Bài đã được tự động gộp: 23/6/22

Em làm theo các bước của bác nhưng đến bước này hình như sai hay sao ấy ạ

Mình làm theo hướng dẫn của bạn ptm ra được kết quả mà, bác xem file đính kèm nhé.

kbw1 · 23/6/22

hocexcel_1991 đã viết:
Gửi bạn file, xem đúng ý không?
View attachment 277785

Mình cũng đi theo hướng này,
Nhưng không hiểu sao bạn kia lại để dạng Pivot trong query
sau đó lại làm công thức ở query luôn,
Chưa hiểu mục đích chính là gì.

))))))

ngoctinh87 · 23/6/22

kbw1 đã viết:
Nhưng không hiểu sao bạn kia lại để dạng Pivot trong query
sau đó lại làm công thức ở query luôn,

Thường thì PowerQuery xử lý dữ liệu thô, còn phần tính toán tổng hợp thì dùng PowerPivot

tranhungdao12a3 · 23/6/22

kbw1 đã viết:
Mình cũng đi theo hướng này,
Nhưng không hiểu sao bạn kia lại để dạng Pivot trong query
sau đó lại làm công thức ở query luôn,
Chưa hiểu mục đích chính là gì.
))))))

Thì bạn ấy tính toán luôn bằng Power query, cũng sao đâu, power query vẫn tính toán bình thường mà. Tuy nhiên theo ý kiến của tôi thì Power Query nó mạnh về ETL, còn tính toán thì nên là công việc của Power Pivot(Cái này trực quan và tùy biến nó dễ hơn)

Xin các cao thủ giúp đỡ làm lại bảng dữ liệu bằng power query

Thành viên mới

File đính kèm

Bad Excel Member

Thành viên mới

Bad Excel Member

Thành viên mới

Ăn cùng góc phố

Thành viên mới

Thành viên tích cực

Thành viên mới

Thành viên tích cực

Thành viên mới

Thành viên tích cực

File đính kèm

Bắt đầu học Excel

File đính kèm

Bad Excel Member

Thành viên tích cực

Bad Excel Member

Thành viên mới

File đính kèm

Thành viên tích cực

Thành viên thường trực

Thành viên thường trực

Facebook

Group