Excel, công cụ bảng tính mạnh mẽ, không ngừng được cập nhật với các hàm mới nhằm tối ưu hóa khả năng xử lý và phân tích dữ liệu. Trong số đó, hàm PIVOTBY nổi bật như một giải pháp đột phá, cho phép bạn nhóm dữ liệu linh hoạt mà không cần đến PivotTable truyền thống. Điều đặc biệt là các bảng tổng hợp được tạo bằng PIVOTBY tự động cập nhật theo thời gian thực khi dữ liệu gốc thay đổi, và bạn có thể dễ dàng tùy chỉnh chúng thông qua 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 tiếp cận PIVOTBY sẽ trở nên vô cùng đơn giản. Điểm khác biệt mấu chốt là trong khi GROUPBY chỉ cho phép nhóm các biến vào các hàng, PIVOTBY bổ sung một đối số cho phép bạn nhóm các biến nhất định vào các cột, mang lại cái nhìn đa chiều và trực quan hơn về dữ liệu. Chức năng này biến PIVOTBY thành một công cụ không thể thiếu cho các nhà phân tích dữ liệu và người dùng Excel muốn tạo ra các báo cáo động, dễ đọc và dễ quản lý.
Cú Pháp Hàm PIVOTBY: Khai Thác Tiềm Năng Phân Tích Dữ Liệu
Hàm PIVOTBY cho phép bạn nhập 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ị chính xác những gì bạn muốn dữ liệu thể hiện. Tuy nhiên, chỉ bốn đối số đầu tiên là bắt buộc.
Các Đối Số Bắt Buộc
Các đối số bắt buộc của PIVOTBY là nơi bạn chỉ định cho Excel những biến nào sẽ hiển thị dưới dạng tiêu đề hàng, biến nào dưới dạng tiêu đề cột, nơi tìm các giá trị và cách bạn muốn tổng hợp dữ liệu:
=PIVOTBY(a,b,c,d)Trong đó:
a: Là các ô chứa (các) danh mục bạn muốn xuất hiện dưới dạng tiêu đề hàng ở phía bên trái kết quả.b: Là các ô chứa (các) danh mục bạn muốn xuất hiện dưới dạng tiêu đề cột ở phía trên cùng kết quả.c: Là các giá trị sẽ xuất hiện ở trung tâm kết quả, dựa trên các hàng và cột bạn đã chọn cho đối sốavàb.d: Là hàm Excel (hoặc hàm tự định nghĩa thông quaLAMBDA) xác định cách dữ liệu sẽ được tổng hợp (ví dụ:SUM,AVERAGE,COUNT,MAX,MIN).
Mặc dù đố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 duy nhất, tùy thuộc vào nhu cầu phân tích của bạn.
Các Đối Số Tùy Chọn
Các đối số tùy chọn của hàm PIVOTBY cho phép bạn tùy chỉnh chi tiết hơn kết quả, bao gồm việc thêm tiêu đề, tổng cộng, tổng phụ, sắp xếp dữ liệu và trong một số trường hợp cụ thể, xác định các tham số bổ sung:
=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])| Đối số | Mô tả | Lưu ý |
|---|---|---|
| e | Một số xác định liệu các đối số a, b, và c có chứa tiêu đề 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ị |
| 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ỉ 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 đố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. Một số dương đại diện cho thứ tự bảng chữ cái hoặc tăng dần, và một số âm đại diện cho 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ỉ 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 đố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 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 |
PIVOTBY: Chỉ Sử Dụng 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 với chỉ bốn đối số 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) 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 theo năm và khu vực.
Bạn có thể sử dụng hàm GROUPBY để thực hiện đ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 dài các thông tin khó diễn giải.
Kết quả từ hàm GROUPBY trong Excel cho thấy dữ liệu được nhóm theo hàng, 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 số liệu của bạn được sắp xếp.
Vì công thức PIVOTBY tạo ra một mảng tràn động, bạn phải nhập công thức của nó vào một khu vực 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, trong ô F1, hãy nhập:
=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. Đây sẽ là các 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à đâ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 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 vì tự gõ 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 cho 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 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 theo năm và theo môn thể thao.
Nếu không có các đối số tùy chọn, Excel cũng sẽ 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 cần đưa 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ả hàm PIVOTBY Excel với hai biến hàng: Thể thao và Khu vực, cung cấp cái nhìn chi tiết hơn.
Để 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 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.
PIVOTBY: Khai Thác 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: Tổng Cộng & Tổng Phụ
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 chỉ ra 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, 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ả chung).
Báo cáo PIVOTBY Excel hiển thị tổng phụ và tổng cộng được tô màu để dễ nhận biết.
Để 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 để 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.” 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
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.
Khi 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ố 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 Excel được sắp xếp theo tổng số liệu người xem, cải thiện khả năng phân tích dữ liệu.
Lưu ý rằng 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 của chúng, mà còn cả các phân loại khu vực theo tổng phụ của chúng. Điều này cung cấp một cái nhìn tổng quan có tổ chức và dễ hiểu hơn về hiệu suất của từng hạng mục.
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.
Khi 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 (đố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 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. 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.
Sau đó, hãy chọn các ô chứa kết quả dạng thập phân và nhấp vào biểu tượng “Percent Style” trong nhóm “Number” trên tab “Home” của dải băng. Khi bạn ở đó, hãy nhấp vào các nút “Increase Decimal” và “Decrease Decimal” để 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.
Hướng dẫn định dạng kết quả hàm PIVOTBY thành phần trăm trong Excel bằng nút Percent Style và điều chỉnh số thập phân.
Cuối cùng, hãy dành 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ố 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 trên tất cả các năm. Ngoài ra, vì dữ liệu được sắp xếp theo tổng cột 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, mở rộng khả năng phân tích dữ liệu của bạn.
Kết Luận
Hàm PIVOTBY trong Excel là một công cụ mạnh mẽ, linh hoạt, giúp bạn tạo ra các báo cáo tổng hợp dữ liệu động một cách nhanh chóng và hiệu quả. Khả năng nhóm dữ liệu theo cả hàng và cột, cùng với hệ thống đối số tùy chọn phong phú, cho phép người dùng tùy chỉnh kết quả để phù hợp với mọi nhu cầu phân tích, từ việc hiển thị tổng cộng, tổng phụ cho đến sắp xếp dữ liệu và tính toán phần trăm.
Việc nắm vững PIVOTBY không chỉ giúp bạn tiết kiệm thời gian so với việc tạo PivotTable thủ công mà còn đảm bảo dữ liệu luôn được cập nhật, mang lại cái nhìn sâu sắc và kịp thời. Hãy tích hợp hàm PIVOTBY vào quy trình làm việc của bạn để nâng cao hiệu suất phân tích dữ liệu và đưa ra các quyết định sáng suốt hơn.
Nếu bạn muốn tìm hiểu sâu hơn về các hàm Excel khác hoặc khám phá thêm các thủ thuật phân tích dữ liệu, hãy tiếp tục theo dõi tinhoccongnghe.net. Chúng tôi luôn cập nhật những thông tin và hướng dẫn mới nhất để bạn làm chủ Excel một cách chuyên nghiệp.
