GROUPBY / PIVOTBY
GROUPBY(nhóm_dòng, giá_trị, hàm_tính, [tiêu_đề], [tổng], [thứ_tự_sắp], [lọc])PIVOTBY(nhóm_dòng, nhóm_cột, giá_trị, hàm_tính, [tiêu_đề], [tổng_dòng], [thứ_tự_dòng], [tổng_cột], [thứ_tự_cột], [lọc])Gom nhóm và tính tổng bằng CÔNG THỨC: GROUPBY ra bảng dọc kiểu một cột nhóm một cột số, PIVOTBY ra bảng chéo có nhóm ở dòng và nhóm ở cột. Làm thay PivotTable cho những báo cáo cần tự cập nhật, không phải nhớ bấm Refresh.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| nhóm_dòng | Bắt buộc | Cột dùng để gom nhóm, ví dụ cột chi nhánh hoặc cột khách hàng. Gom nhiều cấp thì đưa nhiều cột vào bằng HSTACK. |
| nhóm_cột | Bắt buộc | Chỉ có ở PIVOTBY: cột trải ngang, thường là cột tháng. |
| giá_trị | Bắt buộc | Cột số cần tính, ví dụ cột thành tiền. |
| hàm_tính | Bắt buộc | Ghi TÊN hàm không kèm ngoặc: SUM, AVERAGE, COUNTA, MAX, PERCENTOF. Viết SUM() là lỗi ngay. |
| tiêu_đề | Tuỳ chọn | 0 = vùng nguồn không có tiêu đề · 1 = có nhưng không hiện · 2 = không có nhưng vẫn hiện · 3 = có và hiện. |
| tổng | Tuỳ chọn | 0 = bỏ dòng tổng · 1 hoặc bỏ trống = có dòng Tổng cộng ở cuối · 2 = tổng nằm trên đầu. |
| thứ_tự_sắp | Tuỳ chọn | Số cột dùng để sắp, số âm là giảm dần. |
| lọc | Tuỳ chọn | Mảng đúng/sai cùng số dòng với vùng nguồn, viết y như điều kiện của FILTER. |
Ví dụ
=GROUPBY(C2:C5000, F2:F5000, SUM)Doanh thu theo chi nhánh, thêm đơn hàng mới là bảng tự dài ra
Kết quả: bảng 2 cột + dòng tổng
=GROUPBY(C2:C5000, F2:F5000, SUM, 3, 1, -2)Như trên nhưng có tiêu đề và sắp doanh thu giảm dần
Kết quả: bảng đã sắp
=PIVOTBY(C2:C5000, MONTH(A2:A5000), F2:F5000, SUM)Bảng chéo chi nhánh theo dòng, tháng theo cột, lấy thẳng từ cột ngày chứng từ
Kết quả: bảng chéo 12 cột
Lưu ý khi dùng
- Nhóm được ngay từ biểu thức chứ không cần dựng cột phụ: MONTH(cột_ngày) hay LEFT(cột_mã, 2) đưa thẳng vào đối số nhóm.
- Khác PivotTable ở chỗ không kéo thả, không bấm đúp để xem chi tiết. Cần khám phá số liệu thì vẫn dùng PivotTable; cần một báo cáo cố định tự cập nhật thì dùng hàm này.
- Muốn chốt số cuối kỳ thì Copy rồi Paste Values, vì hàm luôn tính lại theo dữ liệu hiện tại.
Bẫy hay dính
Kết quả tự sắp xếp và tự chèn dòng Tổng cộng, nên SỐ DÒNG đổi mỗi khi phát sinh nhóm mới. Ô ở nơi khác trỏ cứng vào một ô trong vùng tràn, kiểu =H7 để lấy số của Hà Nội, sẽ lặng lẽ chuyển sang lấy số của chi nhánh khác ngay khi có thêm một chi nhánh đứng trước nó. Luôn tra lại theo TÊN bằng XLOOKUP thay vì trỏ toạ độ ô.