Excel liên tục được cải tiến với các hàm và tính năng mới, giúp người dùng xử lý dữ liệu hiệu quả hơn. Trong số đó, hàm PIVOTBY là một bổ sung đáng giá, cho phép bạn nhóm và tổng hợp dữ liệu mà không cần phải tạo PivotTable truyền thống. Không chỉ vậy, các bảng tổng hợp được tạo bằng PIVOTBY còn tự động cập nhật khi dữ liệu gốc thay đổi, mang lại sự linh hoạt và tiết kiệm thời gian đáng kể. Bạn có thể dễ dàng tùy chỉnh kết quả hiển thị 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 trong Excel, việc học PIVOTBY sẽ rất đơn giản. Điểm khác biệt chính là GROUPBY chỉ cho phép bạn nhóm các biến vào 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 nhất định vào các cột, tạo ra một cái nhìn tổng quan trực quan và dễ phân tích hơn nhiều. Điều này đặc biệt hữu ích khi bạn cần tạo các báo cáo đa chiều mà vẫn duy trì tính động của dữ liệu.
Cú pháp Hàm PIVOTBY Trong Excel
Hàm PIVOTBY có tổng cộng 11 đối số, nghe có vẻ phức tạp nhưng lại mang đến lợi ích to lớn: khả năng tùy chỉnh kết quả hiển thị theo đúng nhu cầu phân tích của bạn. Tuy nhiên, chỉ có bốn đối số đầu tiên là bắt buộc.
Các đối số bắt buộc của PIVOTBY
Các đối số 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ị dưới dạng tiêu đề hàng, biến nào hiển thị dưới dạng tiêu đề cột, nơi tìm giá trị và cách bạn muốn tổng hợp dữ liệu.
Cú pháp cơ bản như sau:
=PIVOTBY(hang_truong, cot_truong, gia_tri, ham_tong_hop)Trong đó:
hang_truong: Là các ô chứa danh mục hoặc các danh mục mà 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à nền tảng để nhóm dữ liệu theo chiều dọc.cot_truong: Là các ô chứa danh mục hoặc các danh mục mà bạn muốn xuất hiện làm tiêu đề cột ở phía trên kết quả. Đây là điểm khác biệt cốt lõi với GROUPBY, cho phép bạn phân tích dữ liệu theo chiều ngang.gia_tri: Là các giá trị sẽ xuất hiện ở trung tâm kết quả của bạn, tương ứng với các hàng và cột bạn đã chọn chohang_truongvàcot_truong. Đây là dữ liệu định lượng bạn muốn tổng hợp.ham_tong_hop: Là hàm Excel (hoặc hàm tùy chỉnh bạn đã 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, PERCENTOF).
Mặc dù hang_truong và cot_truong là bắt buộc để PIVOTBY tạo ra kết quả dạng PivotTable đúng nghĩa, bạn vẫn có thể bỏ qua một trong hai để chỉ nhóm dữ liệu theo hàng hoặc cột (giống như GROUPBY hoặc chỉ nhóm theo cột).
Các đối số tùy chọn của PIVOTBY
Các đối số tùy chọn của PIVOTBY cho phép bạn thêm tiêu đề, tổng cộng, sắp xếp dữ liệu và, trong một số trường hợp cụ thể, chỉ định thêm các tham số bổ sung.
Cú pháp đầy đủ của PIVOTBY như sau:
=PIVOTBY(hang_truong, cot_truong, gia_tri, ham_tong_hop, [co_tieu_de, do_sau_tong_hang, thu_tu_sap_xep_hang, do_sau_tong_cot, thu_tu_sap_xep_cot, mang_loc, phan_tram_theo])| Đối số | Mô tả | Ghi chú |
|---|---|---|
co_tieu_de |
Một số xác định liệu hang_truong, cot_truong và gia_tri có bao gồm tiêu đề hay không và 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ự động tạo 3 = Có tiêu đề, và hiển thị |
do_sau_tong_hang |
Một số xác định liệu hang_truong có nên chứa tổng cộng lớn (grand totals) và tổng phụ (subtotals) hay không. |
0 = Không có tổng cộng 1 = Tổng cộng lớn ở cuối (mặc định) 2 = Tổng cộng lớn và tổng phụ ở cuối -1 = Tổng cộng lớn ở đầu -2 = Tổng cộng lớn và tổng phụ ở đầu |
thu_tu_sap_xep_hang |
Một số chỉ ra 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 hang_truong, sau đó là cột trong gia_tri. Ví dụ, nếu bạn có hai cột trong hang_truong, “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ố gia_tri. Số dương là sắp xếp theo thứ tự chữ cái hoặc tăng dần, số âm là ngược lại. |
do_sau_tong_cot |
Một số xác định liệu cot_truong có nên chứa tổng cộng lớn và tổng phụ hay không. |
0 = Không có tổng cộng 1 = Tổng cộng lớn ở bên phải (mặc định) 2 = Tổng cộng lớn và tổng phụ ở bên phải -1 = Tổng cộng lớn ở bên trái -2 = Tổng cộng lớn và tổng phụ ở bên trái |
thu_tu_sap_xep_cot |
Một số chỉ ra 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 cot_truong, sau đó là cột trong gia_tri, và hoạt động tương tự như thu_tu_sap_xep_hang. |
mang_loc |
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 giá trị hoặc văn bản nhất định. | N/A (không áp dụng trực tiếp trong ví dụ này, nhưng là một tính năng mạnh mẽ để tinh chỉnh dữ liệu trước khi tổng hợp). |
phan_tram_theo |
Một số giúp bạn kiểm soát 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 ham_tong_hop. |
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
Trước tiên, chúng ta hãy tìm hiểu cách sử dụng hàm PIVOTBY ở dạng đơn giản nhất, chỉ với bốn đối số bắt buộc.
Giả sử bạn có một bảng dữ liệu Excel có tên 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 (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 đặt ra là tạo một bảng tổng hợp tổng số người xem theo môn thể thao và năm.
Bảng dữ liệu Excel ví dụ về số liệu người xem thể thao qua các năm và khu vực.
Bạn có thể sử dụng hàm GROUPBY để làm điều này, nhưng 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 thành các hàng, bạn có thể kết thúc với một danh sách thông tin dài và khó diễn giải.
Kết quả tổng hợp dữ liệu sử dụng hàm GROUPBY trong Excel, hiển thị danh sách dài các môn thể thao và năm.
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 phân bổ.
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 của bảng tính không được định dạng dưới dạng bảng Excel.
Để thực hiện điều này, tại ô F1, bạn 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ảngSports_Viewersđại diện cho sáu môn thể thao khác nhau. Các môn này sẽ là tiêu đề hàng ở phía bên trái 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 năm này sẽ là tiêu đề cột ở phía trên 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 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ụng AVERAGE chẳng hạn, kết quả sẽ hiển thị trung bình của các số liệu đó.
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ó cấu trúc – structured references) một cách thủ công, bạn có thể chọn các ô trong bảng bằng chuột, Excel sẽ tự động nhập các tham chiếu này cho bạn.
Kết quả của hàm PIVOTBY trong Excel, tổng hợp số người xem theo môn thể thao (hàng) và năm (cột) với các tổng cộng.
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 có các trường tùy chọn, Excel sẽ 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 xem tổng số liệu người xem mỗi năm và mỗi môn thể thao.
Khi không có các đối số tùy chọn, Excel cũng sắp xếp kết quả theo thứ tự chữ cái hoặc tăng dần theo đối số hang_truong, và không bao gồm tiêu đề cột cho đối số hang_truong.
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ố hang_truong:
=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)
Kết quả PIVOTBY khi sử dụng hai biến (Môn thể thao và Khu vực) làm đối số hàng, hiển thị dữ liệu chi tiết hơn.
Để 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ố hang_truong, để 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.
Hướng Dẫn Sử Dụng 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 gõ một dấu phẩy đầu tiên để 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ố ý 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 Cộng Lớn và Tổng Cộng Phụ (Grand Totals & 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 ý cách, sau đối số ham_tong_hop (SUM), hai dấu phẩy chỉ ra rằng bạn muốn bỏ qua đối số co_tieu_de, nhưng bao gồm đối số do_sau_tong_hang (trong trường hợp này, 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 lớn ở cuối kết quả tổng thể).
Kết quả của hàm PIVOTBY trong Excel có thêm hàng tổng phụ (Subtotals) và tổng cộng lớn (Grand Totals), được tô màu để dễ nhìn.
Để làm cho dữ liệu rõ ràng hơn nữa, 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 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: Thay Đổi Thứ Tự Sắp Xếp (Sort Order)
Bây giờ, hãy xem xét 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)Điều này có nghĩa là, sau khi bỏ qua đối số co_tieu_de, cũng như bao gồm tổng cộng lớn và tổng phụ (số 2 trong đối số do_sau_tong_hang), 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ố thu_tu_sap_xep_hang).
Kết quả PIVOTBY đã được sắp xếp theo tổng số người xem tăng dần, bao gồm tổng phụ và tổng cộng lớn.
Lưu ý cách việc 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 chung 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: Sử Dụng Hàm PERCENTOF Để Tính Tỷ Lệ Phần Trăm
Đây là cách xem kết quả của bạn dưới dạng tỷ lệ phần trăm.
Nhập:
=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 (hang_truong), các năm làm tiêu đề cột (cot_truong), và bạn muốn tổng hợp dữ liệu của mình (gia_tri) dưới dạng phần trăm (ham_tong_hop là PERCENTOF). Số 2 đầu tiên (đối số thu_tu_sap_xep_hang) đạ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ố phan_tram_theo) cho Excel biết rằng tỷ lệ phần trăm nên được tính dựa trên tổng dữ liệu chung. Các dấu phẩy không được điền cho Excel biết bạn muốn nó áp dụng mặc định cho các đối số co_tieu_de, do_sau_tong_hang, do_sau_tong_cot, thu_tu_sap_xep_cot, và mang_loc.
Kết quả của hàm PIVOTBY sử dụng PERCENTOF, hiển thị tỷ lệ phần trăm số người xem theo môn thể thao và năm dưới dạng số thập phân.
Bây giờ, chọn các ô chứa kết quả 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ố) của tab Home (Trang chủ) trên thanh công cụ. Trong khi bạn ở đó, 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ố 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 có thêm dữ liệu được bổ sung vào bảng gốc trong tương lai.
Hướng dẫn định dạng kết quả PIVOTBY từ số thập phân sang phần trăm trong Excel bằng nút Percent Style.
Cuối cùng, hãy dành một chút thời gian để phân tích những gì kết quả này cho bạn biết.
Kết quả cuối cùng của hàm PIVOTBY với PERCENTOF, các số liệu đã được chuyển đổi sang định dạng phần trăm rõ ràng.
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 tổng hợp 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 phân tích dữ liệu của mình.
Kết Luận
Hàm PIVOTBY trong Excel là một công cụ mạnh mẽ và linh hoạt, mang đến một cách tiếp cận hiện đại để tổng hợp và phân tích dữ liệu đa chiều, vượt trội hơn so với việc sử dụng PivotTable truyền thống trong nhiều tình huống. Khả năng tạo ra các báo cáo động, tự cập nhật và dễ dàng tùy chỉnh giúp người dùng tiết kiệm thời gian, công sức và đưa ra quyết định nhanh chóng, chính xác hơn. Từ việc chỉ sử dụng các đối số bắt buộc để có cái nhìn tổng quan đến việc khai thác tối đa các đối số tùy chọn để thêm tổng phụ, sắp xếp dữ liệu hay tính toán phần trăm, PIVOTBY chứng tỏ là một kỹ năng không thể thiếu cho bất kỳ ai làm việc với dữ liệu trong Excel.
Hãy bắt đầu thực hành với PIVOTBY ngay hôm nay để trải nghiệm sự tiện lợi và hiệu quả mà nó mang lại. Đừng ngần ngại chia sẻ những cách bạn đã áp dụng PIVOTBY vào công việc của mình hoặc những câu hỏi bạn có về hàm này trong phần bình luận bên dưới nhé!
