Tìm công thức thay thế cho Pivot Table

[anhp2k123]

Em muốn lọc ra 5 lỗi lớn nhất trong sản xuất, hiện tại e mới chỉ dùng đc pivot table nó không đáp ứng được yêu cầu công việc. Em nhờ mọi người trong nhóm chỉ giúp em xem có công thức nào để lọc được như kết quả pivot không ạ? Em cảm ơn!

 

[le_vis]

Em muốn lọc ra 5 lỗi lớn nhất trong sản xuất, hiện tại e mới chỉ dùng đc pivot table nó không đáp ứng được yêu cầu công việc. Em nhờ các cao nhân chỉ giúp em xem có công thức nào để lọc được như kết quả pivot không ạ? thanks!

Nếu là tôi thấp nhân, thì dùng sumifs là được mà

 

[anhp2k123]

Nếu là tôi thấp nhân, thì dùng sumifs là được mà

e vẫn chưa hình dung dùng sumifs như nào, ac có thể viết công thức đc ko ạ?

 

[le_vis]

Bạn có biết tại sao không thấy có ai giúp bạn không ?
Bởi vì : - 1 là Bạn dùng Từ "Cao nhân"
- 2 là Bạn viết tắt lung tung
- 3 Hãy dùng tiếng việt đi, đừng nửa tây nửa ta nữa
Thấy bạn mới tham gia bài tôi tham gia vậy. Ban xem file đính kèm nhé

 

[anhp2k123]

Bạn có biết tại sao không thấy có ai giúp bạn không ?
Bởi vì : - 1 là Bạn dùng Từ "Cao nhân"
- 2 là Bạn viết tắt lung tung
- 3 Hãy dùng tiếng việt đi, đừng nửa tây nửa ta nữa
Thấy bạn mới tham gia bài tôi tham gia vậy. Ban xem file đính kèm nhé

dạ em cảm ơn góp ý của bác em sẽ chú ý.
Em muốn giải thích thêm là cột lỗi của e nó không cố định nó có thay đổi thường xuyên nên e không thể lấy lỗi để dùng hàm sumifs để lọc ra 5 lỗi lớn nhất được, mong bác cho e thêm ý kiến nữa ạ

 

[le_vis]

dạ em cảm ơn góp ý của bác em sẽ chú ý.
Em muốn giải thích thêm là cột lỗi của e nó không cố định nó có thay đổi thường xuyên nên e không thể lấy lỗi để dùng hàm sumifs để lọc ra 5 lỗi lớn nhất được, mong bác cho e thêm ý kiến nữa ạ

Tại cột M trong file bạn thử xóa tên một mã lỗi bất kỳ đi và thay vào 1 mã lỗi khác xem nó chay ra sao sẽ hiểu (Mã lỗi cột M bạn có thể thay đổi theo sự thay đổi thường xuyên cửa bạn)

 

[ptm0412]

Tôi thấy dù có dùng cách gì đi nữa thì vẫn thua Pivot table vì công thức thì cứng, pivot thì động.

Dữ liệu thay đổi chỉ cần refresh. Nếu dữ liệu tăng thêm thì tạo Name động cho vùng dữ liệu rồi lấy Source là name đó.

 

[VetMini]

Tôi thấy dù có dùng cách gì đi nữa thì vẫn thua Pivot table vì công thức thì cứng, pivot thì động.

Dữ liệu thay đổi chỉ cần refresh. Nếu dữ liệu tăng thêm thì tạo Name động cho vùng dữ liệu rồi lấy Source là name đó.

Nếu dữ liệu cho vào Data Model thì cớ thể dùng cột công thức phụ (giống như trong Power Pivot), hoặc một số hàm đặc biệt DAX.

 

[tle2003]

Em muốn lọc ra 5 lỗi lớn nhất trong sản xuất, hiện tại e mới chỉ dùng đc pivot table nó không đáp ứng được yêu cầu công việc. Em nhờ mọi người trong nhóm chỉ giúp em xem có công thức nào để lọc được như kết quả pivot không ạ? Em cảm ơn!

Trước tiên, tôi muốn nói rằng Pivot là lựa chọn khả thi , tuy vậy với hiểu biết hạn chế của mình tôi cũng có thể đưa ra lựa chọn khác cho vui thôi nhé ( ban xem file đính kèm).
Cột G: tạo danh sách duy nhất từ cột E
Cột H: Số lượng NG
Cột I: Xếp hạng lỗi ( Vì dữ liệu bạn cho có trùng nếu công thức có thêm 1 tí)
Cột J: Ghi vào thứ tự mà ban muốn (1 dến 5)
Cột K: 5 lỗi hàng
Cột L: Số lượng NG

Lưu ý: range dùng là 2-22, bạn cần điều chỉnh theo ý bạn
Côt G và K , Nhập công thức dưới dạng công thức mảng (bằng cách nhấn Ctrl+Shift+Enter.)

 

[ptm0412]

Pivot table trong file bài 1 đang lỗi do đã sửa tên cả 5 cột.
Làm lại thì vẫn có kết quả top 5, chưa cần tới cột phụ hay DAX.



Tuy vậy, Pivot table hay bất cứ hàm, công thức, VBA, Query, .. đều xảy ra trường hợp top 5 có nhiều hơn 5 thành phần do trùng số. Tự ý bỏ bớt 1 sẽ là tội phạm.

 

[ptm0412]

Thí dụ công thức bài 9: Nếu có trùng 2 con 16 như hình, đáng lẽ đồng hạng 5 nhưng 1 trong 2 bị tụt hạng 6 chả vì lý do gì. Anh thợ sơn thoát tội dù sơn lỗi bằng số lỗi của thợ gaz

 

[VetMini]

Thí dụ công thức bài 9: Nếu có trùng 2 con 16 như hình, đáng lẽ đồng hạng 5 nhưng 1 trong 2 bị tụt hạng 6 chả vì lý do gì. Anh thợ sơn thoát tội dù sơn lỗi bằng số lỗi của thợ gaz
...

Giao công việc thống kê cho người hoàn toàn mù tịt về thống kê.
Xếp hạng mà không biết có đồng hạng thì còn làm ăn quái gì nữa.

Đó chỉ là căn bản, người biết làm việc nhìn thấy sự cách biết giữa hạng nhất và hạng 4, hạng 4 và hạng 5 là báo động rồi.

Cái này đáng lý phải vẽ chart trước.

 

[ptm0412]

Giao công việc thống kê cho người hoàn toàn mù tịt về thống kê.
Xếp hạng mà không biết có đồng hạng thì còn làm ăn quái gì nữa.

Đó chỉ là căn bản, người biết làm việc nhìn thấy sự cách biết giữa hạng nhất và hạng 4, hạng 4 và hạng 5 là báo động rồi.

Cái này đáng lý phải vẽ chart trước.

Theo tôi đây là dữ liệu mẫu giả lập, tôi mới điền thêm vào để có con 104 đè 2 con 16 xuống, trước đó chỉ có 5 loại lỗi bao gồm 2 con 16. Sự chênh lệch quá lớn là tại con số giả lập.
Ý tôi muốn nói về cái công thức bài 9: Cố tình cộng thêm CountIf và trừ 1 để hạ thấp hạng 1 trong 2 con trùng. Như bài 10 tôi nói: tự ý bỏ bớt 1 trong 2 con trùng là tội phạm.
Giải thích cho người đọc khác tại sao là tội phạm:
- Nếu lấy top 5 để khen thưởng, thì đã cố ý loại 1 người không được thưởng (người giữ lại là em sếp chăng? Hay là trù dập 1 em?)
- Nếu lấy top 5 để phạt tức là bỏ lọt tội phạm (tội phạm bỏ lọt này là con cháu của ai đó chăng?)

 

[VetMini]

....
Giải thích cho người đọc khác tại sao là tội phạm:
- Nếu lấy top 5 để khen thưởng, thì đã cố ý loại 1 người không được thưởng (người giữ lại là em sếp chăng? Hay là trù dập 1 em?)
- Nếu lấy top 5 để phạt tức là bỏ lọt tội phạm (tội phạm bỏ lọt này là con cháu của ai đó chăng?)

Vì người Việt dùng tiếng Tây bồi quen cho nên từ từ tiếng "top" bị hiểu lầm.
Theo định nghĩa, top/bottom tính bằng Rank chứ đâu có thể bằng công thức khác được.

 

[ptm0412]

Theo định nghĩa, top/bottom tính bằng Rank chứ đâu có thể bằng công thức khác được.

Anh không xem công thức trong file bài 9 rồi. Công thức dùng hàm Rank, và vẫn bị tình trạng đồng hạng. Tác giả biết là đồng hạng nên phát minh ra việc cộng thêm vào Rank một hàm CountIf và trừ 1 để hạ hạng những con số trùng. Vì vậy tôi mới gọi là cố ý.

 

[VetMini]

Anh không xem công thức trong file bài 9 rồi. Công thức dùng hàm Rank, và vẫn bị tình trạng đồng hạng. Tác giả biết là đồng hạng nên phát minh ra việc cộng thêm vào Rank một hàm CountIf và trừ 1 để hạ hạng những con số trùng. Vì vậy tôi mới gọi là cố ý.

À. Xin lỗ, tính tôi cứ thấy mấy cái vụ xếp hạng tùm lum thế này là không buồn xem công thức, rối não và mất thì giờ vô ích.

 

[tranhungdao12a3]

Em muốn lọc ra 5 lỗi lớn nhất trong sản xuất, hiện tại e mới chỉ dùng đc pivot table nó không đáp ứng được yêu cầu công việc. Em nhờ mọi người trong nhóm chỉ giúp em xem có công thức nào để lọc được như kết quả pivot không ạ? Em cảm ơn!

Bạn có thể viết hàm Dax Power Pivot để xếp hạng:

Vì có đồng hạng 4 nên nó sẽ lấy cả hai. Nếu dữ liệu nó có 4 lỗi lớn hơn 16, tức là lỗi thứ 5 là Sơn lỗi và Gass nó sẽ đồng hạng 5 thì phải lấy cả hai. Còn muốn vẫn chỉ lấy 5 lỗi thì phải có tiêu chí phụ để so sánh chọn 1 thằng trong hai thằng đồng hạng(ví dụ nếu đồng hạng thì thằng nào tiêu chí "Sản xuất" bé hơn thì xếp hạng trước chẳng hạn). Đại loại đồng hạng thì phải có tiêu chí phụ để so sánh chọn.

 

[ptm0412]

Bạn có thể viết hàm Dax Power Pivot để xếp hạng:

Bài 10 tôi dùng Pivot table thuần (không có Power query và DAX) và vẫn ra kết quả. Cột xếp hạng không cần vì lấy top 5 và sort giảm dần thì đã biết hạng.

 

[tranhungdao12a3]

Bài 10 tôi dùng Pivot table thuần (không có Power query và DAX) và vẫn ra kết quả. Cột xếp hạng không cần vì lấy top 5 và sort giảm dần thì đã biết hạng.

Mấy bài toán xếp hạng này dùng Dax là phù hợp, nhưng phải Dax trong Power Bi khi đó viết bảng kết quả thì xử lý xếp hạng nhiều điều kiện và chặn lấy bao nhiêu cũng được. Còn bài toán cơ bản này dùng Pivot cũng được, không cần phải phức tạp

 

[ptm0412]

Mấy bài toán xếp hạng này dùng Dax là phù hợp, nhưng phải Dax trong Power Bi khi đó viết bảng kết quả thì xử lý xếp hạng nhiều điều kiện và chặn lấy bao nhiêu cũng được. Còn bài toán cơ bản này dùng Pivot cũng được, không cần phải phức tạp

Nếu excel thuần không power thì dùng cột phụ, chẳng hạn như
dk chính * 100 + (đk phụ 1) *10 + đk phụ 2 * 1
dùng cột này để rank hoặc pivot thuần.


các con số 100, 10, 1 là giảm dần và bắt đầu là 100, 1 ngàn, hay 1 triệu tùy theo dữ liệu thực tế