Trong thế giới dữ liệu khổng lồ ngày nay, việc 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 quyết định sáng suốt. Với Excel, một công cụ không thể thiếu trong mọi lĩnh vực, việc quản lý và tóm tắt dữ liệu đôi khi có thể trở nên phức tạp, đặc biệt khi bạn muốn có cái nhìn đa chiều mà không cần đến PivotTable truyền thống. Đó là lúc hàm PIVOTBY của Excel phát huy sức mạnh. Hàm này không chỉ cho phép bạn nhóm các con số mà còn tự động cập nhật kết quả khi dữ liệu gốc thay đổi, đồng thời cung cấp khả năng tùy chỉnh cao thông qua công thức của nó.
Nếu bạn đã quen thuộc với cách sử dụng hàm GROUPBY thì việc học PIVOTBY sẽ rất dễ dàng. Sự khác biệt chính là GROUPBY chỉ nhóm các biến thành các hàng, trong khi PIVOTBY có thêm một đối số cho phép bạn nhóm các biến thành cả cột, mang lại cái nhìn tổng quan và sâu sắc hơn về dữ liệu.
Cú pháp hàm PIVOTBY: Khám phá các đối số
Hàm PIVOTBY cho phép bạn nhập tổng cộng 11 đối số. Mặc dù con số này có vẻ đáng gờm, nhưng lợi ích của việc có nhiều đối số như vậy là bạn có thể tùy chỉnh kết quả để phù hợp với những gì bạn muốn dữ liệu hiển thị.
Tuy nhiên, chỉ có bốn trường đầu tiên là bắt buộc.
Các đối số bắt buộc của PIVOTBY
Các trường PIVOTBY bắt buộc là nơi bạn chỉ định cho Excel biết bạn muốn hiển thị những biến nào ở hàng, những biến nào ở cột, nơi tìm các giá trị 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 (các) danh mục bạn muốn hiển thị làm tiêu đề hàng ở phía bên trái của kết quả.
- b là các ô chứa (các) danh mục bạn muốn hiển thị làm tiêu đề cột ở phía trên cùng của kết quả.
- c là các giá trị sẽ xuất hiện ở trung tâm của kết quả, theo các hàng và cột bạn đã chọn cho các đối số a và b.
- d là hàm Excel (hoặc hàm bạn tự tạo thông qua LAMBDA) định nghĩa cách dữ liệu sẽ được tổng hợp.
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 một trong hai đối số này để chỉ nhóm dữ liệu theo hàng hoặc cột.
Các đối số tùy chọn nâng cao của PIVOTBY
Các trường PIVOTBY tùy chọn 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 một số tham 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 và tổng phụ hay không | 0 = Không có tổng 1 = Tổng cộng ở cuối (mặc định) 2 = Tổng cộng và tổng phụ ở cuối -1 = Tổng cộng ở đầu -2 = Tổng cộng và tổng phụ ở đầu |
| g | Một số chỉ định cách các cột nên được sắp xếp | Con số này đề cập đến các cột trong đối số a, tiếp theo 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. 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. |
| h | Một số xác định liệu đối số b có nên chứa tổng cộng và tổng phụ 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 | Con số này đề cập đến các cột trong đối số b, tiếp theo 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 |
| 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 chung 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 cơ bản
Để bắt đầu, chúng ta sẽ tìm hiểu 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 số liệu người 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ản 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 chứa năm, môn thể thao, khu vực và số người xem
Bạn có thể sử dụng hàm GROUPBY để làm điều này, nhưng sẽ cần thêm 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ể kết thúc với một danh sách dài thông tin khó diễn giải.
Kết quả của công thức GROUPBY trong Excel
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 sắp xếp.
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 của mình không được định dạng dưới dạng bảng Excel.
Để làm điều này, tại ô F1, 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 đã đặt tên trong bảng đã đặt tên, đại diện cho sáu môn thể thao khác nhau. Đây sẽ là các tiêu đề hàng ở phía bên trái của kết quả.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 ở phía trên cùng của kết quả.Sports_Viewers[Viewers]là cột hiển thị số 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ả.SUMcho Excel biết bạn muốn tổng các cột cộng các con số lại với nhau. Mặt khác, nếu bạn sử dụng AVERAGE, kết quả sẽ hiển thị giá trị trung bình của những con số đó.
Thay vì nhập thủ công tên bảng và tên cột vào công thức của bạn (còn được gọi là tham chiếu có cấu trúc), 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 đối với một số môn thể thao trong một số năm nhất định. Ngoài ra, trong trường hợp 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, 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 các 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 vào đối số a:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
Kết quả công thức PIVOTBY sử dụng hai biến trong đối số trường hàng
Để nhóm các cột không liền kề vào một đối số, hãy 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 bổ sung vào đối số a, để làm cho kết quả rõ ràng hơn, bạn có thể nhập một số đối số tùy chọn.
Khai thác sức mạnh của PIVOTBY với các đối số tùy chọn
Bất cứ khi nào 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 nhập 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ì vậy 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 cho biết 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, 2 có nghĩa là bạn muốn bao gồm một 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ể).
Kết quả của công thức PIVOTBY với các tổng phụ được tô màu
Để làm cho dữ liệu thậm chí còn rõ ràng hơn, tôi đã sử dụng định dạng có điều kiện (conditional formatting) để 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 từ “Grand Total.” Không nên áp dụng định dạng trực tiếp cho các mảng tràn ô, 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 để thao tác kết quả của bạn: sắp xếp theo một trong các cột đầu ra.
Nhập:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)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ả của công thức PIVOTBY bao gồm các đối số cho tổng và thứ tự sắp xếp
Lưu ý cách sắp xếp theo tổng số người xem không chỉ đặt các môn thể thao theo thứ tự tổng cộng của chúng, mà còn đặt các danh mục phụ theo khu vực theo tổng phụ của chúng.
Ví dụ 3: Hiển thị kết quả 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.
Nhập:
=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2)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ự, trong trường hợp này, là theo 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 tương ứng với dữ liệu tổng thể. 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.
Minh họa máy tính với logo Excel, biểu tượng chức năng và bàn phím
Bây giờ, 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 “Number” (Số) trên tab “Home” (Trang chủ) của thanh công cụ. Trong khi bạn đang ở đó, 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.
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.
Các ô được chọn trong Excel, với các nút "Percent Style", "Increase Decimal" và "Decrease Decimal" được làm nổi bật
Cuối cùng, hãy dành một chút thời gian để hiểu kết quả này cho bạn biết điều gì.
Ví dụ, số người xem bóng mềm (softball) vào năm 2022 chiếm 11,5% tổng số người xem tất cả các môn thể thao qua 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.
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 cho người dùng một phương pháp linh hoạt và hiệu quả để nhóm, tóm tắt và phân tích dữ liệu. Khả năng tự động cập nhật, nhóm dữ liệu theo cả hàng và cột, cùng với các tùy chọn tùy chỉnh phong phú thông qua 11 đối số, biến PIVOTBY thành một giải pháp thay thế tuyệt vời cho PivotTable truyền thống, đặc biệt khi bạn cần sự linh hoạt và tính động trong các báo cáo của mình.
Việc nắm vững PIVOTBY không chỉ giúp bạn tiết kiệm thời gian mà còn nâng cao khả năng khai thác giá trị từ dữ liệu, giúp bạn đưa ra những quyết định sáng suốt hơn. Hãy thử nghiệm với các đối số khác nhau và khám phá cách hàm này có thể biến đổi quy trình làm việc với dữ liệu của bạn.
Bạn đã từng sử dụng PIVOTBY trong các dự án của mình chưa? Hãy chia sẻ những mẹo hoặc trường hợp sử dụng sáng tạo của bạn trong phần bình luận bên dưới, hoặc khám phá thêm các bài viết chuyên sâu khác về Excel và phân tích dữ liệu trên xalocongnghe.com để trở thành một chuyên gia Excel thực thụ!
