Trong kỷ nguyên dữ liệu số, khả năng tổ chức và phân tích thông tin một cách hiệu quả là chìa khóa để đưa ra các 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 tính năng mạnh mẽ nhất gần đây được giới thiệu là hàm PIVOTBY, cho phép bạn tổng hợp và nhóm dữ liệu mà không cần phải tạo lại bảng dữ liệu trong một PivotTable truyền thống. Điểm ưu việt của các bảng tóm tắt được tạo qua PIVOTBY là chúng tự động cập nhật để phản ánh những thay đổi trong dữ liệu gốc của bạn. Hơn nữa, bạn có thể tùy chỉnh hiển thị của chúng bằng cách điều chỉnh công thức.
Nếu bạn đã quen thuộc với cách sử dụng hàm GROUPBY, việc học cách dùng PIVOTBY sẽ khá đơn giản. Sự khác biệt cốt lõi là GROUPBY chỉ cho phép bạn nhóm các biến vào các hàng, trong khi PIVOTBY có một đối số bổ sung cho phép bạn nhóm một số biến vào cả các cột. Điều này mang lại sự linh hoạt vượt trội trong việc hiển thị và phân tích dữ liệu đa chiều, giúp bạn có cái nhìn toàn diện và sâu sắc hơn về các mối quan hệ trong tập dữ liệu của mình.
Cú pháp Hàm PIVOTBY trong Excel
Hàm PIVOTBY cho phép bạn nhập tổng cộng 11 đối số. Mặc dù số lượng này có vẻ “đồ sộ”, nhưng lợi ích của việc có nhiều đối số là bạn có thể tùy chỉnh kết quả để phù hợp với chính xác những gì bạn muốn dữ liệu hiển thị. Tuy nhiên, chỉ bốn trường đầu tiên là bắt buộc.
Các Đối số Bắt buộc
Các trường bắt buộc của PIVOTBY là nơi bạn chỉ định cho Excel biết những biến nào bạn muốn hiển thị ở hàng, những biến nào ở cột, nơi tìm các giá trị cần tổng hợp và cách bạn muốn tổng hợp dữ liệu của mình:
=PIVOTBY(a,b,c,d)Trong đó:
a: Là các ô chứa một hoặc nhiều danh mục bạn muốn xuất hiện làm tiêu đề hàng ở phía bên trái kết quả. Đây là chiều dọc của bảng tổng hợp.b: Là các ô chứa một hoặc nhiều danh mục bạn muốn xuất hiện làm tiêu đề cột ở phía trên kết quả. Đây là chiều ngang của bảng tổng hợp.c: Là các giá trị sẽ xuất hiện ở trung tâm của kết quả, dựa trên các hàng và cột bạn đã chọn cho đối sốavàb. Đây là dữ liệu số bạn muốn tổng hợp.d: Là hàm Excel (hoặc hàm do bạn tự tạo thông qua LAMBDA) định nghĩa cách dữ liệu sẽ được tổng hợp (ví dụ: SUM, AVERAGE, COUNT, MIN, MAX).
Mặc dù các đối số a và b là bắt buộc để PIVOTBY tạo ra loại kết quả mà nó được thiết kế, bạn vẫn có thể bỏ qua chúng để chỉ nhóm dữ liệu theo hàng hoặc cột mà thôi, biến nó thành một dạng linh hoạt của GROUPBY.
Các Đối số Tùy chọn
Các trường tùy chọn của PIVOTBY cho phép bạn bao gồm tiêu đề và tổng cộng, sắp xếp dữ liệu, và trong những trường hợp rất cụ thể, chỉ định thêm một số thông số bổ sung:
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])| Đối số | Ý nghĩa | Ghi chú |
|---|---|---|
| e | Một số chỉ định liệu các đối số a, b, và c có bao gồm tiêu đề và liệu bạn có muốn hiển thị tiêu đề trong kết quả hay 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ự tạo 3 = Có tiêu đề, và hiển thị |
| f | Một số xác định liệu đối số a có nên chứa tổng cộng (grand totals) và tổng phụ (subtotals) hay không. |
0 = Không có tổng 1 = Tổng cộng ở dưới cùng (mặc định) 2 = Tổng cộng và tổng phụ ở dưới cùng -1 = Tổng cộng ở trên cùng -2 = Tổng cộng và tổng phụ ở trên cùng |
| g | Một số chỉ định cách các cột nên được sắp xếp. | Số này tham chiếu đến các cột trong đối số a, sau đó là cột trong đối số c. Ví dụ, nếu bạn có hai cột trong đối số a, “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ố c. Số dương đại diện cho thứ tự bảng chữ cái hoặc tăng dần, và số âm đại diện cho thứ tự ngược lại (giảm dần). |
| h | Một số xác định liệu đối số b có nên chứa tổng cộng (grand totals) và tổng phụ (subtotals) hay không. |
0 = Không có tổng 1 = Tổng cộng ở bên phải (mặc định) 2 = Tổng cộng và tổng phụ ở bên phải -1 = Tổng cộng ở bên trái -2 = Tổng cộng và tổng phụ ở bên trái |
| i | Một số chỉ định cách các hàng nên được sắp xếp. | Số này tham chiếu đến các cột trong đối số b, sau đó là cột trong đối số c, và hoạt động tương tự như đối số g. |
| j | Một 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 các giá trị hoặc văn bản nhất định. | N/A (Không áp dụng, dùng cho các điều kiện lọc nâng cao) |
| k | Một số cung cấp cho bạn nhiều quyền kiểm soát hơn về cách các phép tính của kết quả được thực hiện khi bạn sử dụng hàm PERCENTOF trong đối số d. |
0 = Tổng cột (mặc định) 1 = Tổng hàng 2 = Tổng cộng (Grand total) 3 = Tổng cột cha (Parent column total) 4 = Tổng hàng cha (Parent row total) |
Hướng dẫn Sử dụng Hàm PIVOTBY với các Đối số Bắt buộc
Đầu tiên, hãy cùng khám phá cách sử dụng hàm PIVOTBY ở dạng đơn giản nhất của nó, chỉ với bốn trường bắt buộc.
Giả sử bạn có một bảng dữ liệu có tên là Sports_Viewers như hình dưới. Bảng này hiển thị số liệu người xem trực tiếp (cột D) cho sáu môn thể thao khác nhau (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 của bạn là tạo một bảng tóm tắt tổng số người xem theo môn thể thao và theo năm.
Bảng dữ liệu Excel ví dụ về lượt xem thể thao theo năm, môn thể thao và khu vực
Bạn có thể sử dụng hàm GROUPBY để làm điều này, mặc dù bạn sẽ cần sử dụng 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 vào các hàng, bạn có thể kết thúc với một danh sách thông tin rất dài và khó giải thích.
Kết quả khi sử dụng hàm GROUPBY trong Excel để tổng hợp dữ liệu, 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 lấy một hoặc nhiều biến và đặt chúng vào các cột, mang lại cái nhìn rõ ràng hơn về cách các con số của bạn được tổng hợp.
Vì công thức PIVOTBY tạo ra một mảng tràn động (dynamic spilled array), bạn phải nhập công thức của nó vào một vùng trong bảng tính của mình mà không được định dạng dưới dạng bảng Excel.
Để thực hiện điều này, tại ô F1 (hoặc một ô trống khác ngoài bảng dữ liệu gốc), bạn hãy nhập công thức sau:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)Trong đó:
Sports_Viewers[Sport]là cột được đặt tên trong bảng đã đặt tên, đại diện cho sáu môn thể thao khác nhau. Các giá trị này sẽ trở thành tiêu đề hàng ở phía bên trái của kết quả của bạn.Sports_Viewers[Year]đại diện cho cột Năm của bảng, và các giá trị này sẽ trở thành tiêu đề cột ở phía trên của kết quả.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 đó, vì vậy dữ liệu này sẽ là phần trọng tâm của kết quả.SUMnói với Excel rằng bạn muốn các cột tổng cộng cộng các số liệu lại với nhau. Mặt khác, nếu bạn sử dụngAVERAGE, kết quả sẽ hiển thị giá trị trung bình của các số liệu đó thay thế.
Thay vì gõ tên bảng và tên cột vào công thức của bạn (còn gọi là tham chiếu cấu trúc) theo cách thủ công, nếu bạn chọn các ô trong bảng bằng chuột, Excel sẽ tự động nhập chúng cho bạn.
Với 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 vắng mặt cho một số môn thể thao trong những năm nhất định. Ngoài ra, khi không có 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, có nghĩa là bạn có thể ngay lập tức thấy tổng số người xem theo từng năm và theo từng môn thể thao.
Nếu 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ố a, và không bao gồm tiêu đề cột cho đối số a.
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 trong đối số a:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
Kết quả của hàm PIVOTBY trong Excel khi nhóm dữ liệu theo hai biến (môn thể thao và khu vực) trong đối số hàng
Để nhóm các cột không liền kề vào một đối số, bạn có thể sử dụng hàm CHOOSECOLS.
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 số người xem của mỗi 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ố a, để làm cho kết quả rõ ràng hơn, bạn có thể nhập thêm một số đối số tùy chọn.
Tùy biến Kết quả với các Đối số Tùy chọn của PIVOTBY
Mỗi khi bạn tạo một công thức Excel, 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õ dấu phẩy đầu tiên để mở đối số, sau đó là 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ố ý 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 phụ và Tổng cộng (Grand Totals và Subtotals)
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 ý rằng, sau đối số d (SUM), hai dấu phẩy liên tiếp cho biết rằng bạn muốn bỏ qua đối số e (tiêu đề), nhưng bao gồm đối số 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 cộng ở cuối kết quả tổng thể).
Bảng kết quả PIVOTBY có các dòng tổng phụ và tổng cộng được tô màu, giúp dễ dàng nhận biết
Để làm cho dữ liệu thậm chí rõ ràng hơn, tôi đã 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 nghị, 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 Dữ liệu theo Thứ tự Mong muốn
Bây giờ, hãy xem một cách khác để điều khiển 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)Điều này có nghĩa là, sau khi bỏ qua đối số e, cũng như bao gồm tổng cộng và tổng phụ (số 2 trong đối số f), bạn cũng muốn sắp xếp dữ liệu của mình theo tổng số người xem theo thứ tự tăng dần (số 3 trong đối số g).
Kết quả hàm PIVOTBY được sắp xếp theo tổng lượt xem và hiển thị tổng phụ, tổng cộng
Lưu ý cách việc sắp xếp theo tổng số người xem không chỉ sắp xếp các môn thể thao theo tổng số của chúng, mà còn sắp xếp các danh mục con theo khu vực theo tổng phụ của chúng.
Ví dụ 3: Hiển thị Dữ liệu dưới dạng Phần trăm (PERCENTOF)
Dưới đây là cách xem kết quả của bạn dưới dạng phần trăm bằng cách sử dụng hàm PERCENTOF.
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 cho Excel biết rằng bạn muốn các môn thể thao làm tiêu đề hàng (đối số a), các năm làm tiêu đề cột (đối số b), và bạn muốn tổng hợp dữ liệu của mình (đối số c) dưới dạng phần trăm (đối số d). Số 2 đầu tiên (đối số g) đại diện cho thứ tự sắp xếp, trong trường hợp này, là theo các giá trị. Cuối cùng, số 2 thứ hai (đối số k) cho Excel biết rằng các phần trăm nên được tính toán dựa trên tổng dữ liệu chung (Grand total). Các dấu phẩy trống cho Excel biết bạn muốn nó áp dụng cài đặt mặc định cho các đối số e, f, h, i, và j.
Bây giờ, hãy chọn các ô chứa kết quả đã được thập phân hóa và nhấp vào biểu tượng “Percent Style” (Kiểu phần trăm) trong nhóm “Number” (Số) của tab “Home” (Trang chủ) trên ribbon. Trong khi ở đó, 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 mong muốn.
Hướng dẫn chọn các ô kết quả PIVOTBY và sử dụng nút định dạng phần trăm trong Excel
Bạn nên chọn thêm các hàng bên dưới kết quả phòng trường hợp có thêm dữ liệu được bổ sung 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 để phân tích ý nghĩa của kết quả này.
Ví dụ, số người xem bóng mềm vào năm 2022 chiếm 11.5% tổng số người 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 tổ chức 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 số người xem đến từ những người xem bóng rổ.
Ngoài việc sử dụng SUM, AVERAGE, và PERCENTOF cho đối số hàm trong PIVOTBY, bạn cũng có thể sử dụng các hàm tổng hợp khác như COUNT, MIN, hoặc MAX để tùy chỉnh sâu hơn cách dữ liệu của bạn được phân tích và trình bày.
Kết luận
Hàm PIVOTBY là một bổ sung mạnh mẽ cho bộ công cụ của Excel, mang đến khả năng nhóm và tổng hợp dữ liệu linh hoạt, mạnh mẽ hơn nhiều so với việc chỉ sử dụng PivotTable truyền thống trong một số trường hợp. Với khả năng tùy chỉnh chi tiết thông qua các đối số bắt buộc và tùy chọn, từ việc xác định tiêu đề hàng/cột, cách tổng hợp dữ liệu đến việc thêm tổng phụ, sắp xếp và hiển thị tỷ lệ phần trăm, PIVOTBY cho phép bạn tạo ra các báo cáo động, tự cập nhật và dễ dàng phân tích.
Việc nắm vững PIVOTBY sẽ giúp bạn tiết kiệm đáng kể thời gian trong việc xử lý và trình bày dữ liệu, đặc biệt khi làm việc với các tập dữ liệu lớn và phức tạp. Hãy khám phá và ứng dụng hàm này vào công việc hàng ngày của bạn để nâng cao hiệu quả phân tích dữ liệu trong Excel. Nếu bạn có bất kỳ thắc mắc nào về PIVOTBY, hãy chia sẻ ý kiến của bạn trong phần bình luận bên dưới.
