Hàm GroupBy và PivotBy (5 người xem)

[ptm0412]

Hai hàm mới này được admin @OverAC (Đỗ Nguyên Bình) chia sẻ trong 2 buổi sinh nhật GPE 18 tại Đà Nẵng và TP HCM bằng 1 clip của Ms Leila Gharani.

Dưới đây là file thực hành do tôi thực hiện.

 

[tranhungdao12a3]

Em mở rộng thêm câu hỏi để mọi người áp dụng:
1. Tìm giá trị đơn hàng cao nhất trong từng nhóm CategoryName và sản phẩm tương ứng
2. Tính trung bình đơn giá (UnitPrice) theo từng nhóm CategoryName và ShipCountry
3. Tính tổng giá trị (Value) của sản phẩm theo từng CategoryName, chỉ với những đơn hàng có số lượng (Quantity) lớn hơn 20
4. Tìm tổng giá trị bán hàng (Value) và giá trị trung bình đơn giá (UnitPrice) cho từng CategoryName, chỉ tính những sản phẩm có UnitPrice lớn hơn mức trung bình của nhóm đó.
5. Tính số lượng sản phẩm bán được (Quantity) trong từng CategoryName và ShipCountry, chỉ với những đơn hàng có giá trị (Value) cao hơn mức trung bình của nhóm.
6. Tính tổng giá trị (Value) và số lượng đơn hàng có số lượng sản phẩm (Quantity) lớn hơn 50, phân theo từng năm và quốc gia vận chuyển (ShipCountry).
7. Tính số lượng đơn hàng có giá trị (Value) lớn hơn giá trị trung bình trong từng nhóm CategoryName và thành phố vận chuyển (ShipCity), đồng thời tính trung bình giá trị các đơn hàng này.

 

[Duy Tùng 9x]

Em mở rộng thêm câu hỏi để mọi người áp dụng:
1. Tìm giá trị đơn hàng cao nhất trong từng nhóm CategoryName và sản phẩm tương ứng
2. Tính trung bình đơn giá (UnitPrice) theo từng nhóm CategoryName và ShipCountry
3. Tính tổng giá trị (Value) của sản phẩm theo từng CategoryName, chỉ với những đơn hàng có số lượng (Quantity) lớn hơn 20
4. Tìm tổng giá trị bán hàng (Value) và giá trị trung bình đơn giá (UnitPrice) cho từng CategoryName, chỉ tính những sản phẩm có UnitPrice lớn hơn mức trung bình của nhóm đó.
5. Tính số lượng sản phẩm bán được (Quantity) trong từng CategoryName và ShipCountry, chỉ với những đơn hàng có giá trị (Value) cao hơn mức trung bình của nhóm.
6. Tính tổng giá trị (Value) và số lượng đơn hàng có số lượng sản phẩm (Quantity) lớn hơn 50, phân theo từng năm và quốc gia vận chuyển (ShipCountry).
7. Tính số lượng đơn hàng có giá trị (Value) lớn hơn giá trị trung bình trong từng nhóm CategoryName và thành phố vận chuyển (ShipCity), đồng thời tính trung bình giá trị các đơn hàng này.

Anh có thể chia sẻ đáp án đúng theo từng câu hỏi không ạ? Ý là kết quả cuối (không cần công thức ạ), để em thực hành học hỏi ạ. Em cảm ơn!

 

[ptm0412]

Anh có thể chia sẻ đáp án đúng theo từng câu hỏi không ạ? Ý là kết quả cuối (không cần công thức ạ), để em thực hành học hỏi ạ. Em cảm ơn!

Đề như vậy là rõ lắm rồi.

 

[Duy Tùng 9x]

Đề như vậy là rõ lắm rồi.

Dạ, vì em cũng sợ là hiểu sai ý thì viết hàm lại sai ạ.

Bài đã được tự động gộp: 9/10/24

Em mở rộng thêm câu hỏi để mọi người áp dụng:

Em xin đóng góp công thức, cũng chưa rõ có đúng như mong muốn của anh không. Mong được học hỏi thêm ạ. Mà anh có phải anh "Tung Nguyen" trên Zalo không vậy ạ
1. =PIVOTBY(Table1[ProductName],Table1[CategoryName],Table1[Value],MAX,,0,,0)
2. =PIVOTBY(Table1[ShipCity],Table1[CategoryName],Table1[UnitPrice],AVERAGE,,0,,0)
3. =PIVOTBY(Table1[ProductName],Table1[CategoryName],Table1[Value],SUM,,0,,0,,Table1[Quantity]>20)
4.=GROUPBY(Table1[CategoryName],Table1[UnitPrice],HSTACK(SUM,AVERAGE),,0,,Table1[UnitPrice]>AVERAGEIF(Table1[CategoryName],Table1[CategoryName],Table1[UnitPrice]))
5.=PIVOTBY(Table1[ShipCity],Table1[CategoryName],Table1[Quantity],SUM,,0,,0,,Table1[Quantity]>AVERAGEIFS(Table1[Quantity],Table1[CategoryName],Table1[CategoryName],Table1[ShipCity],Table1[ShipCity]))
6. =PIVOTBY(Table1[ShipCountry],YEAR(Table1[OrderDate]),Table1[Value],HSTACK(SUM,COUNTA),,0,,0,,Table1[Quantity]>50)
7.=PIVOTBY(Table1[ShipCity],Table1[CategoryName],Table1[Value],HSTACK(COUNTA,AVERAGE),,0,,0,,Table1[Value]>AVERAGEIFS(Table1[Value],Table1[ShipCity],Table1[ShipCity],Table1[CategoryName],Table1[CategoryName]))

 

[ptm0412]

Câu 2: ShipCountry chứ không phải ShipCity

Dạ, vì em cũng sợ là hiểu sai ý thì viết hàm lại sai ạ.

Sai ý thì không nói, nhưng sợ sai kết quả thì tạo 1 pivot table dò ngang để kiểm tra kết quả là được.

 

[ptm0412]

Tiêu đề của clip bài 1 là:


Câu hỏi: Hai hàm này có thực sự thay thế được cho Pivot table không? Ưu điểm và nhược điểm khi so sánh?

 

[ptm0412]

Tập hợp các câu trả lời thu được (mỗi số thứ tự là 1 người trong nhóm zalo):
1.
Groupby - Pivotby
- Nhanh gọn, 1 phát ăn luôn nếu chỉ cần lấy thông tin
- Tự update khi data thay đổi
- Không phải tạo cả Pivottable
- Nhét thêm được nhiều điều kiện để kết hợp Filter data - tính toán

Nhược: PivotChart và các tính năng trong Pivot Analise, nặng khi data nhiều

2.
Cái hay của pivot thì vẫn là khả năng hỗ trợ cùng từ slicer. Nó lọc rất nhanh cái này. Pivotby hay groupby thì phải tạo thêm 1 ô rồi drop list mới thay slicer. Nhưng cũng ko có hay vì pivot table nó bỏ hoặc thêm điều kiện nhanh chóng.
Pivot Table nó có các chức năng tính tỷ lệ % khá hay, như mấy cái tỷ lệ trong nhóm, chênh lệch 2 nhóm, chênh lệch giữa 2 tháng(hay còn gọi là tỷ trọng tăng trưởng). Nói chung pivot table em vẫn thấy rất hay. Dù cái tỷ lệ % này nhiều ng chắc chưa mò hết, nên đôi khi khéo phải dùng cột phụ ngoài tính

3.
Ưu điểm của Pivot: Pivot = xoay. Xoay 1 phát ra cái mới bằng cách kéo thả, trực quan hơn. hàm thì phải tìm và thay thế 1 tham số nào đó bằng tham số mới. Mấy ông sếp khoái cái này hơn. Filter bằng slicer cũng nhanh hơn
Nhược điểm của Pivot: lọc phức tạp thì lâu hơn. Chẳng hạn lọc kiểu câu 4, 5, 7 trong bài 2 của tranhumgdao12a3

4.

 

[VetMini]

Hai từ "Pivot" và "Group" liên quan đến "xắp xếp để tìm những điểm chốt".
Chúng thiên về sắp xếp hướng nhìn (quản trị) hơn xào nấu dữ liệu (quản lý)

Bảng dữ liệu hợp lý và đầy đủ là người sếp chỉ việc xoay qua xoay lại là sẽ nắm hết tình hình chung.
Vì vậy, các chi tiết như "thêm dòng dữ liệu, các công thức khủng,..." mà ở GPE này hay thắc mắc thì lại rất ít khi xảy ra các cơ quan làm việc có thứ tự, chừng mực,...