Trong bối cảnh dữ liệu ngày càng bùng nổ, khả năng tổng hợp và phân tích thông tin một cách nhanh chóng, chính xác là yếu tố then chốt giúp các doanh nghiệp và cá nhân đưa ra quyết định sáng suốt. Excel, với vai trò là công cụ bảng tính hàng đầu, liên tục được cập nhật để đáp ứng nhu cầu này. Một trong những bổ sung đáng giá nhất là hàm PIVOTBY, một tính năng mạnh mẽ cho phép bạn nhóm và tổng hợp dữ liệu một cách linh hoạt, tạo ra các báo cáo động mà không cần đến PivotTable truyền thống.
PIVOTBY là bước tiến vượt trội, đặc biệt nếu bạn đã quen thuộc với hàm GROUPBY. Trong khi GROUPBY chỉ cho phép nhóm dữ liệu theo các hàng, PIVOTBY mang đến khả năng mở rộng đáng kể bằng cách cho phép bạn nhóm các biến vào cả hàng và cột. Điều này tạo ra một cái nhìn đa chiều hơn về dữ liệu của bạn, giúp việc phân tích trở nên trực quan và hiệu quả hơn rất nhiều. Hơn nữa, các báo cáo được tạo ra bằng PIVOTBY có khả năng tự động cập nhật khi dữ liệu gốc thay đổi, đảm bảo tính kịp thời và chính xác của thông tin. Với PIVOTBY, bạn có thể dễ dàng tùy chỉnh kết quả để phù hợp với mọi yêu cầu phân tích, từ đơn giản đến phức tạp, khẳng định vị thế của mình trong việc làm chủ Excel để xử lý dữ liệu.
PIVOTBY là gì và tại sao bạn cần nó?
PIVOTBY là một hàm mảng động (dynamic array function) trong Excel, được thiết kế để tạo ra các báo cáo tổng hợp tương tự như PivotTable nhưng trực tiếp từ công thức trong ô. Điều này loại bỏ nhu cầu tạo và quản lý một PivotTable riêng biệt, mang lại sự linh hoạt và tính tự động hóa cao hơn.
Điểm khác biệt cốt lõi với GROUPBY và PivotTable:
- So với GROUPBY: Cả PIVOTBY và GROUPBY đều là hàm mảng động để tổng hợp dữ liệu. Tuy nhiên, GROUPBY chỉ cho phép nhóm các trường dữ liệu vào các hàng. PIVOTBY vượt trội hơn khi bổ sung khả năng nhóm các trường vào các cột, giúp tạo ra một bảng tổng hợp hai chiều, dễ đọc và dễ phân tích hơn nhiều.
- So với PivotTable: PivotTable là công cụ mạnh mẽ nhưng đòi hỏi người dùng phải kéo thả các trường, cập nhật thủ công (refresh) khi dữ liệu nguồn thay đổi. PIVOTBY hoạt động dựa trên công thức, nghĩa là kết quả sẽ tự động cập nhật ngay lập tức khi dữ liệu gốc thay đổi. Điều này làm cho PIVOTBY trở thành lựa chọn lý tưởng cho các báo cáo động, bảng điều khiển (dashboards) hoặc các tình huống cần tính toán tức thì mà không muốn can thiệp vào cấu trúc bảng PivotTable.
Lợi ích khi sử dụng PIVOTBY:
- Tính tự động hóa cao: Báo cáo cập nhật tức thì.
- Linh hoạt trong công thức: Dễ dàng tùy chỉnh và tích hợp vào các công thức khác.
- Tạo báo cáo đa chiều: Nhóm theo cả hàng và cột, mang lại cái nhìn sâu sắc hơn.
- Giảm thiểu thao tác thủ công: Không cần kéo thả, tiết kiệm thời gian.
Cú pháp hàm PIVOTBY trong Excel
Hàm PIVOTBY có tổng cộng 11 đối số, cho phép bạn tinh chỉnh kết quả báo cáo đến mức độ chi tiết mong muốn. Tuy nhiên, bạn chỉ cần nắm vững 4 đối số bắt buộc đầu tiên để bắt đầu sử dụng hàm này.
Các đối số bắt buộc: Nền tảng của PIVOTBY
Cú pháp cơ bản của PIVOTBY chỉ với các đối số bắt buộc là:
=PIVOTBY(row_fields, col_fields, values, function)Trong đó:
row_fields(a): Là tham chiếu đến các ô chứa (một hoặc nhiều) danh mục bạn muốn hiển thị làm tiêu đề hàng, chạy xuống phía bên trái của kết quả.col_fields(b): Là tham chiếu đến các ô chứa (một hoặc nhiều) danh mục bạn muốn hiển thị làm tiêu đề cột, chạy ngang phía trên của kết quả.values(c): Là tham chiếu đến các ô chứa các giá trị số hoặc văn bản sẽ xuất hiện ở trung tâm báo cáo của bạn, tương ứng với các hàng và cột đã chọn trong đối sốrow_fieldsvàcol_fields.function(d): Là hàm tổng hợp Excel (hoặc một hàm bạn tự tạo qua LAMBDA) định nghĩa cách dữ liệu sẽ được tổng hợp. Các hàm phổ biến bao gồm SUM (tính tổng), AVERAGE (tính trung bình), COUNT (đếm), MIN (tìm giá trị nhỏ nhất), MAX (tìm giá trị lớn nhất), PERCENTOF (tính phần trăm).
Lưu ý quan trọng: Mặc dù row_fields và col_fields là bắt buộc để PIVOTBY tạo ra báo cáo hai chiều, bạn có thể bỏ qua một trong hai đối số này (sử dụng "" hoặc bỏ trống giữa hai dấu phẩy) để nhóm dữ liệu chỉ theo hàng hoặc chỉ theo cột. Tuy nhiên, trong những trường hợp này, hàm GROUPBY có thể là lựa chọn tối ưu hơn.
Các đối số tùy chọn: Tùy chỉnh báo cáo chuyên sâu
Các đối số tùy chọn cho phép bạn bổ sung tiêu đề, tổng cộng/tổng phụ, sắp xếp dữ liệu và áp dụng các bộ lọc.
Cú pháp đầy đủ của PIVOTBY bao gồm các đối số tùy chọn như sau:
=PIVOTBY(row_fields, col_fields, values, function, [header], [row_total_function], [row_sort_order], [col_total_function], [col_sort_order], [filter_array], [col_data_calc])Dưới đây là bảng giải thích chi tiết các đối số tùy chọn:
| Đối số | Mô tả | Giá trị và Ý nghĩa |
|---|---|---|
header (e) |
Số chỉ định xem row_fields, col_fields và values có bao gồm tiêu đề hay không, và bạn có muốn hiển thị tiêu đề trong kết quả không. |
– 0 = Không có tiêu đề (mặc định) – 1 = Có tiêu đề, nhưng không hiển thị – 2 = Không có tiêu đề, nhưng tự động tạo – 3 = Có tiêu đề, và hiển thị |
row_total_function (f) |
Số xác định xem row_fields có nên chứa tổng chính và tổng phụ hay không. |
– 0 = Không có tổng cộng – 1 = Tổng chính ở cuối (mặc định) – 2 = Tổng chính và tổng phụ ở cuối – -1 = Tổng chính ở đầu – -2 = Tổng chính và tổng phụ ở đầu |
row_sort_order (g) |
Số chỉ định cách các hàng nên được sắp xếp. | Số này đề cập đến các cột trong đối số row_fields, sau đó là cột trong đối số values. Ví dụ: Nếu row_fields có hai cột, “2” sẽ sắp xếp kết quả theo cột thứ hai của đối số này, và “3” sẽ sắp xếp kết quả theo đối số values. Một số dương biểu thị thứ tự chữ cái hoặc tăng dần, và một số âm biểu thị thứ tự ngược lại (giảm dần). |
col_total_function (h) |
Số xác định xem col_fields có nên chứa tổng chính và tổng phụ hay không. |
– 0 = Không có tổng cộng – 1 = Tổng chính ở bên phải (mặc định) – 2 = Tổng chính và tổng phụ ở bên phải – -1 = Tổng chính ở bên trái – -2 = Tổng chính và tổng phụ ở bên trái |
col_sort_order (i) |
Số chỉ định cách các cột nên được sắp xếp. | Số này đề cập đến các cột trong đối số col_fields, sau đó là cột trong đối số values, và hoạt động tương tự như đối số row_sort_order (g). |
filter_array (j) |
Biểu thức logic hoặc công thức cho phép bạn lọc ra các hàng chứa giá trị hoặc văn bản nhất định. | N/A |
col_data_calc (k) |
Số cung cấp cho bạn nhiều quyền kiểm soát hơn về cách thực hiện các phép tính của kết quả khi bạn sử dụng hàm PERCENTOF trong đối số function (d). |
– 0 = Tổng cột (mặc định) – 1 = Tổng hàng – 2 = Tổng chung (Grand total) – 3 = Tổng cột cha – 4 = Tổng hàng cha |
Hướng dẫn sử dụng PIVOTBY với các đối số bắt buộc
Hãy cùng khám phá cách sử dụng hàm PIVOTBY ở dạng đơn giản nhất, chỉ với bốn trường bắt buộc.
Giả sử bạn có một bảng dữ liệu tên là Sports_Viewers chứa thông tin về lượt xem trực tiếp (cột D) của sáu môn thể thao (cột B) trên bốn khu vực (cột C) trong bốn năm (cột A). Yêu cầu là tạo một báo cáo tóm tắt tổng số người xem theo từng môn thể thao và từng năm.
Bảng dữ liệu Excel mẫu về lượt xem thể thao theo năm, vùng và môn
Bạn hoàn toàn có thể sử dụng hàm GROUPBY để thực hiện việc này. Tuy nhiên, bạn sẽ cần dùng đến các đối số tùy chọn để tạo ra một kết quả dễ phân tích hơn. Hơn nữa, vì GROUPBY chỉ sắp xếp dữ liệu thành các hàng, bạn có thể nhận được một danh sách dài khó hiểu.
Kết quả tổng hợp dữ liệu sử dụng hàm GROUPBY trong Excel cho thấy danh sách dài, khó phân tích
Thay vào đó, việc sử dụng hàm PIVOTBY cho phép bạn đưa một hoặc nhiều biến vào các cột, cung cấp một bức tranh rõ ràng hơn về cách các số liệu của bạn được sắp xếp.
Lưu ý quan trọng: Vì công thức PIVOTBY tạo ra một mảng động tràn (dynamic spilled array), bạn phải nhập công thức vào một vùng trong bảng tính mà không được định dạng dưới dạng bảng Excel (Excel table) để tránh lỗi.
Để tạo báo cáo, tại ô F1, bạn hãy nhập công thức sau:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)Giải thích chi tiết công thức:
Sports_Viewers[Sport]là cột được đặt tên trong bảngSports_Viewersđại diện cho sáu môn thể thao khác nhau. Đây sẽ là các tiêu đề hàng dọc theo phía bên trái của báo cáo.Sports_Viewers[Year]đại diện cho cột Năm của bảng, và đây sẽ là các tiêu đề cột dọc theo phía trên của báo cáo.Sports_Viewers[Viewers]là cột hiển thị số lượng người đã xem các môn thể thao đó trong những năm đó, do đó dữ liệu này sẽ là phần trung tâm của báo cáo.SUMchỉ định cho Excel rằng bạn muốn các cột tổng cộng sẽ cộng các số liệu lại với nhau. Ngược lại, nếu bạn sử dụng AVERAGE, kết quả sẽ hiển thị giá trị trung bình của các số liệu đó.
Mẹo: Thay vì gõ tên bảng và tên cột trong công thức của bạn (còn được gọi là tham chiếu có cấu trúc) một cách thủ công, bạn có thể chọn các ô trong bảng bằng chuột và Excel sẽ tự động nhập chúng cho bạn.
Báo cáo PIVOTBY Excel hiển thị tổng lượt xem thể thao theo năm và môn, với tổng cộng hàng và cột
Nhờ cách PIVOTBY hiển thị và tổ chức dữ liệu, bạn có thể rút ra những hiểu biết sâu sắc từ kết quả ngay lập tức. Ví dụ, bạn có thể thấy rằng dữ liệu bị thiếu đối với một số môn thể thao trong một số năm nhất định. Ngoài ra, khi không sử dụng các trường tùy chọn, Excel tự động thêm tổng cộng vào cả hàng và cột của kết quả PIVOTBY, nghĩa là bạn có thể ngay lập tức thấy tổng lượt xem theo từng năm và theo từng môn thể thao.
Khi không có các đối số tùy chọn, Excel cũng tự động sắp xếp kết quả theo thứ tự bảng chữ cái hoặc tăng dần theo đối số row_fields (a), và không bao gồm tiêu đề cột cho đối số row_fields.
Bây giờ, giả sử bạn muốn bao gồm một cột chia mỗi môn thể thao thành bốn khu vực khác nhau. Để làm điều này, bạn sẽ cần bao gồm cả môn thể thao và khu vực vào đối số row_fields (a):
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
Kết quả PIVOTBY Excel với hai trường dữ liệu (môn thể thao và khu vực) được nhóm trong phần hàng
Kết quả này cung cấp cái nhìn chi tiết hơn về dữ liệu của bạn bằng cách hiển thị tổng lượt xem của từng môn thể thao theo khu vực. Tuy nhiên, vì bạn đã thêm một biến phụ vào đối số row_fields, để làm cho kết quả rõ ràng hơn, bạn có thể cần nhập một số đối số tùy chọn.
Khai thác sức mạnh PIVOTBY với các đối số tùy chọn
Khi tạo bất kỳ công thức Excel nào, mỗi đối số được phân tách bằng dấu phẩy. Điều này có nghĩa là nếu bạn muốn bỏ qua bất kỳ đối số tùy chọn nào, bạn chỉ cần gõ một dấu phẩy để mở đối số, sau đó là một dấu phẩy thứ hai để đóng nó. Việc không có gì giữa hai dấu phẩy này cho Excel biết rằng bạn đã cố tình bỏ qua đối số này, và bạn muốn chương trình áp dụng cài đặt mặc định.
Ví dụ 1: Thêm tổng cộng và tổng phụ cho báo cáo
Một cách để làm cho kết quả PIVOTBY trong ví dụ trước rõ ràng hơn là thêm các hàng tổng phụ cho mỗi môn thể thao.
Để làm điều này, bạn cần nhập:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2)Lưu ý cách, sau đối số function (SUM), hai dấu phẩy cho biết bạn muốn bỏ qua đối số header (e), nhưng bao gồm đối số row_total_function (f) – trong trường hợp này, số 2 có nghĩa là bạn muốn bao gồm tổng phụ ở cuối mỗi môn thể thao, cũng như tổng chính ở cuối báo cáo tổng thể.
Báo cáo PIVOTBY Excel hiển thị tổng phụ theo từng môn thể thao, làm tăng tính rõ ràng của dữ liệu
Mẹo: Để làm cho dữ liệu rõ ràng hơn, bạn có thể sử dụng định dạng có điều kiện để tô màu bất kỳ hàng nào trong kết quả mà cột F không trống, cột G trống và cột F không chứa các từ “Grand Total.” Việc áp dụng định dạng trực tiếp cho các mảng tràn không được khuyến khích, vì định dạng được gắn vào các ô, chứ không phải dữ liệu. Điều này có nghĩa là nếu dữ liệu thay đổi, định dạng sẽ không thay đổi theo.
Ví dụ 2: Sắp xếp kết quả theo thứ tự tùy chỉnh
Bây giờ, hãy xem xét một cách khác để thao tác với kết quả của bạn: sắp xếp theo một trong các cột đầu ra.
Nhập công thức:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)Công thức này có nghĩa là, sau khi bỏ qua đối số header (e) và bao gồm tổng chính và tổng phụ (số 2 trong đối số row_total_function (f)), bạn cũng muốn sắp xếp dữ liệu của mình theo tổng lượt xem tổng thể theo thứ tự tăng dần (số 3 trong đối số row_sort_order (g)). Cụ thể, số 3 ở đây là vị trí của cột Viewers trong mảng dữ liệu đã nhóm (cột đầu tiên là Sport, thứ hai là Region, thứ ba là tổng Viewers).
Kết quả PIVOTBY Excel được sắp xếp theo tổng lượt xem tăng dần, bao gồm tổng phụ và tổng chính
Lưu ý cách việc sắp xếp theo tổng lượt xem không chỉ đặt các môn thể thao theo thứ tự dựa trên tổng số lượt xem tổng thể của chúng, mà còn sắp xếp các danh mục phụ theo khu vực dựa trên tổng phụ của chúng.
Ví dụ 3: Hiển thị dữ liệu dưới dạng phần trăm với PERCENTOF
Đây là cách để xem kết quả của bạn dưới dạng phần trăm.
Nhập công thức:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2)Công thức này chỉ định cho Excel rằng bạn muốn các môn thể thao làm tiêu đề hàng (đối số row_fields), các năm làm tiêu đề cột (đối số col_fields), và bạn muốn tổng hợp dữ liệu của mình (đối số values) dưới dạng phần trăm (hàm PERCENTOF trong đối số function). Số 2 đầu tiên (đối số row_sort_order (g)) đại diện cho thứ tự, trong trường hợp này là theo các giá trị (tức là tổng lượt xem). Cuối cùng, số 2 thứ hai (đối số col_data_calc (k)) cho Excel biết rằng các phần trăm nên tương đối so với tổng dữ liệu chung (Grand total). Các dấu phẩy trống cho Excel biết bạn muốn áp dụng cài đặt mặc định cho các đối số header, row_total_function, col_total_function, col_sort_order và filter_array.
Bảng dữ liệu PIVOTBY Excel thể hiện phần trăm tổng lượt xem của từng môn thể thao theo năm
Bây giờ, hãy chọn các ô chứa kết quả đã được chuyển đổi thành số thập phân và nhấp vào biểu tượng “Percent Style” (Kiểu phần trăm) trong nhóm Số (Number) của tab Trang đầu (Home) trên Ribbon. Trong khi bạn đang ở đó, hãy nhấp vào các nút “Increase Decimal” (Tăng số thập phân) và “Decrease Decimal” (Giảm số thập phân) để xác định số lượng chữ số thập phân.
Mẹo: Hãy chọn thêm các hàng bên dưới kết quả phòng trường hợp dữ liệu được thêm vào bảng gốc trong tương lai.
Cuối cùng, hãy dành một chút thời gian để nắm bắt những gì kết quả này cho bạn biết. Ví dụ, số lượng người xem môn bóng mềm (softball) vào năm 2022 chiếm 11.5% tổng lượt xem của tất cả các môn thể thao trong tất cả các năm. Ngoài ra, vì dữ liệu được sắp xếp theo cột tổng cộng theo thứ tự tăng dần, bạn có thể thấy rằng gần một phần tư (23.7%) tổng lượt xem đến từ những người xem bóng rổ.
Các hàm tổng hợp khác có thể dùng với PIVOTBY
Ngoài việc sử dụng SUM và PERCENTOF cho đối số function trong PIVOTBY, bạn cũng có thể sử dụng các hàm tổng hợp khác như:
- AVERAGE: Tính giá trị trung bình.
- COUNT: Đếm số lượng mục.
- MIN: Tìm giá trị nhỏ nhất.
- MAX: Tìm giá trị lớn nhất.
Hãy thử nghiệm với các hàm này để khám phá thêm nhiều cách tổng hợp và phân tích dữ liệu của bạn, từ đó tạo ra những báo cáo PIVOTBY phù hợp nhất với nhu cầu cụ thể.
PIVOTBY là một công cụ phân tích dữ liệu vô cùng mạnh mẽ và linh hoạt trong Excel, mang đến khả năng tạo ra các báo cáo động, đa chiều mà không cần PivotTable truyền thống. Từ việc nhóm dữ liệu theo cả hàng và cột, tự động cập nhật kết quả, cho đến tùy chỉnh sâu với các đối số tùy chọn như tổng phụ, sắp xếp hay tính toán phần trăm, PIVOTBY giúp người dùng khai thác tối đa giá trị từ dữ liệu của mình.
Việc nắm vững hàm PIVOTBY không chỉ giúp bạn tối ưu hóa quy trình làm việc mà còn nâng cao khả năng phân tích, giúp bạn đưa ra những quyết định chính xác và kịp thời. Hãy thử nghiệm PIVOTBY với các bộ dữ liệu của riêng bạn, khám phá các đối số khác nhau và chia sẻ kinh nghiệm của bạn để cùng nâng cao kỹ năng sử dụng Excel. Bạn có thể tham khảo thêm các bài viết khác về phân tích dữ liệu và các hàm Excel nâng cao trên trangtincongnghe.net để làm chủ công cụ này một cách toàn diện.
