Microsoft Excel là một công cụ không thể thiếu trong công việc phân tích dữ liệu, và PivotTable chính là trái tim của khả năng này. Với PivotTable, bạn có thể dễ dàng biến những bộ dữ liệu khổng lồ, phức tạp thành những bản tóm tắt có ý nghĩa chỉ trong tích tắc. Tuy nhiên, nhiều người dùng Excel thường chỉ dừng lại ở các chức năng cơ bản của PivotTable, bỏ lỡ tiềm năng to lớn mà công cụ này mang lại để khai thác sâu hơn thông tin từ dữ liệu của họ.
Tại thuthuattonghop.net, chúng tôi hiểu rằng việc nắm vững các kỹ thuật nâng cao trong Excel, đặc biệt là PivotTable, có thể giúp bạn tiết kiệm hàng giờ làm việc và đưa ra các quyết định dựa trên dữ liệu chính xác hơn. Bài viết này sẽ mở rộng kiến thức chuyên môn của bạn về PivotTable, từ việc tạo bảng cơ bản đến áp dụng các tính năng lọc, nhóm và phân tích chuyên sâu. Mục tiêu là giúp bạn không chỉ làm việc hiệu quả hơn mà còn khẳng định vị thế của mình như một chuyên gia phân tích dữ liệu thực thụ.
Để tiện theo dõi và thực hành, bạn có thể tải bộ dữ liệu mẫu miễn phí của Microsoft từ liên kết: bộ dữ liệu mẫu. Trước khi bắt đầu, hãy đảm bảo rằng mỗi cột trong bộ dữ liệu của bạn đã được gán đúng định dạng số liệu cần thiết (văn bản, tiền tệ, ngày tháng, v.v.) để PivotTable có thể hoạt động hiệu quả nhất.
I. Khởi Tạo PivotTable Từ Bộ Dữ Liệu Lớn Của Bạn
Chuyển đổi một bộ dữ liệu lớn thành PivotTable là bước đầu tiên để khai thác tiềm năng phân tích mạnh mẽ của công cụ này. Quy trình này khá đơn giản và trực quan.
Để bắt đầu, hãy chọn bất kỳ ô nào trong phạm vi dữ liệu mà bạn muốn tạo PivotTable. Tiếp theo, trên thanh công cụ (ribbon), đi tới tab Insert và nhấp vào nửa trên của biểu tượng “PivotTable”.
Tạo PivotTable: Chọn ô bất kỳ trong bảng dữ liệu Excel và nhấp vào biểu tượng PivotTable trên thanh công cụ Insert.
Một hộp thoại “PivotTable From Table Or Range” sẽ xuất hiện. Tại đây, bạn cần kiểm tra lại xem phạm vi dữ liệu hoặc tên bảng đã được chọn có chính xác hay không. Sau đó, quyết định vị trí bạn muốn đặt PivotTable: trên trang tính hiện tại (active worksheet) hoặc một trang tính mới (new worksheet). Với các bộ dữ liệu lớn, việc chọn một trang tính mới thường là lựa chọn tối ưu để giữ cho trang tính gốc gọn gàng và dễ quản lý. Sau khi đã thiết lập xong, nhấp vào “OK”.
Hộp thoại Create PivotTable trong Excel, nơi chọn phạm vi dữ liệu và vị trí đặt bảng tổng hợp trên trang tính mới.
Sau khi PivotTable được tạo, một khung “PivotTable Fields” sẽ xuất hiện, cho phép bạn kéo và thả các trường dữ liệu vào các khu vực khác nhau (Rows, Columns, Values, Filters). Ví dụ, nếu bạn muốn phân tích lợi nhuận thu được ở mỗi quốc gia, bạn chỉ cần nhấp và kéo trường “Country” vào khu vực “Rows” và trường “Profit” vào khu vực “Values”.
Bạn có thể tùy chỉnh vị trí và kích thước của khung PivotTable Fields bằng cách nhấp và kéo tiêu đề của nó để di chuyển hoặc thay đổi kích thước, giúp không gian làm việc của bạn trở nên gọn gàng hơn. Nếu muốn phân tích sâu hơn, chẳng hạn như xem chi tiết các sản phẩm riêng lẻ được bán ra ở mỗi quốc gia, bạn có thể kéo trường “Product” vào khu vực “Columns”.
Kéo trường Product vào khu vực Columns trong PivotTable Fields để phân tích doanh thu theo sản phẩm.
Để loại bỏ một trường khỏi PivotTable, bạn chỉ cần nhấp và kéo trường đó ra khỏi khung PivotTable Fields. Thao tác này giúp bạn dễ dàng điều chỉnh và tinh chỉnh báo cáo của mình.
II. Hiển Thị Giá Trị Dưới Dạng Phần Trăm Tổng
Sau khi đã tóm tắt dữ liệu thành lợi nhuận theo quốc gia, bạn có thể muốn đi xa hơn bằng cách hiển thị số liệu của mỗi quốc gia dưới dạng phần trăm của tổng lợi nhuận toàn cầu. Cách này giúp bạn nhanh chóng so sánh đóng góp tương đối của từng quốc gia.
Nếu không có PivotTable, bạn sẽ phải tạo công thức chia lợi nhuận của từng quốc gia cho tổng lợi nhuận và định dạng kết quả dưới dạng phần trăm. Tuy nhiên, PivotTable cung cấp công cụ tích hợp giúp thực hiện tác vụ này nhanh chóng và chính xác hơn nhiều.
Đầu tiên, hãy nhân đôi cột “Sum of Profit” bằng cách kéo trường “Profit” một lần nữa vào khu vực “Values” trong khung PivotTable Fields. Bây giờ, bạn sẽ thấy hai cột “Sum of Profit” cạnh nhau.
Nhân đôi cột Sum of Profit trong PivotTable Excel để hiển thị thêm dữ liệu lợi nhuận.
Tiếp theo, để chuyển đổi cột nhân đôi thành dạng phần trăm của tổng, hãy nhấp chuột phải vào tiêu đề cột của cột “Sum of Profit” thứ hai mà bạn vừa thêm. Di chuột qua tùy chọn “Show Values As”, sau đó nhấp vào “% Of Grand Total”.
Chọn tùy chọn "% Of Grand Total" trong menu chuột phải của PivotTable để hiển thị giá trị dưới dạng phần trăm tổng.
Ngay lập tức, PivotTable của bạn sẽ hiển thị lợi nhuận của mỗi quốc gia dưới dạng phần trăm của tổng lợi nhuận chung. Để tăng tính rõ ràng và dễ đọc, bạn có thể đổi tên tiêu đề cột mặc định (ví dụ: “Sum of Profit2”) thành “Tỷ lệ lợi nhuận (%)” hoặc “Phần trăm Tổng lợi nhuận”.
Bảng tổng hợp PivotTable hiển thị lợi nhuận của từng quốc gia dưới dạng phần trăm trên tổng lợi nhuận toàn cầu.
III. Lọc Dữ Liệu Hiệu Quả Với Các Giá Trị Hàng Đầu, Slicers Và Timelines
PivotTable cung cấp nhiều công cụ lọc mạnh mẽ, giúp bạn tập trung vào những thông tin quan trọng nhất trong dữ liệu.
1. Lọc Các Giá Trị Hàng Đầu (Top N Filter)
Giả sử bạn muốn xác định và báo cáo ba phân khúc kinh doanh (Segments) có lợi nhuận cao nhất để khen thưởng hoặc phân tích sâu hơn.
Đầu tiên, hãy thiết lập PivotTable bằng cách kéo trường “Segment” vào khu vực “Rows” và trường “Profit” vào khu vực “Values”.
Di chuyển trường Segment vào khu vực Rows và Profit vào Values trong PivotTable để xem lợi nhuận theo phân khúc khách hàng.
Tại thời điểm này, bạn có thể sắp xếp PivotTable theo cột “Sum of Profit” từ lớn nhất đến nhỏ nhất bằng cách nhấp chuột phải vào bất kỳ ô nào trong cột đó, di chuột qua “Sort”, và chọn “Largest To Smallest”.
Sắp xếp dữ liệu trong PivotTable theo lợi nhuận từ lớn nhất đến nhỏ nhất bằng cách chọn "Largest To Smallest".
Tuy nhiên, cách này vẫn hiển thị tất cả các phân khúc, kể cả những phân khúc có hiệu suất thấp hoặc thậm chí thua lỗ. Để chỉ hiển thị ba phân khúc có hiệu suất tốt nhất, hãy nhấp chuột phải vào bất kỳ ô nào trong cột “Row Labels” (chứa tên phân khúc), di chuột qua “Filter”, và chọn “Top 10”.
Chọn tùy chọn "Top 10" trong bộ lọc hàng của PivotTable để chỉ hiển thị các giá trị hàng đầu theo tiêu chí.
Trong hộp thoại “Top 10 Filter” xuất hiện, thay đổi giá trị số ở trường thứ hai thành 3. Đảm bảo các trường khác khớp với tiêu chí bạn muốn lọc (ví dụ: “Items” và “by Sum of Profit”). Sau đó, nhấp “OK”.
Thay đổi giá trị trong hộp thoại "Top 10 Filter" thành 3 để hiển thị ba phân khúc có lợi nhuận cao nhất.
Bây giờ, PivotTable của bạn sẽ chỉ hiển thị ba phân khúc có lợi nhuận cao nhất. Sau khi đã sắp xếp dữ liệu theo cột “Sum of Profit” (nếu chưa), bạn có thể sao chép và dán PivotTable này vào các báo cáo, email hoặc tài liệu Word để chia sẻ một cách nhanh chóng và chuyên nghiệp.
Để ẩn các tổng cộng lớn (Grand Totals) không cần thiết, bạn có thể vào tab “Design” trên thanh công cụ, nhấp vào mũi tên xuống của “Grand Totals” và chọn “Off For Rows And Columns”.
PivotTable hiển thị ba phân khúc có lợi nhuận cao nhất sau khi áp dụng bộ lọc Top 3.
2. Sử Dụng Slicers để Lọc Trực Quan
Slicers là một trong những tính năng tuyệt vời nhất của PivotTable, mang lại trải nghiệm lọc dữ liệu cực kỳ trực quan và thân thiện với người dùng.
Giả sử bạn có một PivotTable hiển thị tổng lợi nhuận theo quốc gia và muốn lọc dữ liệu đó theo sản phẩm. Bạn có thể thêm trường “Product” vào khu vực “Row” và lọc thủ công, nhưng cách này tốn thời gian và không tiện lợi khi chia sẻ.
Thay vào đó, hãy loại bỏ trường “Product” khỏi khu vực “Rows” (nếu bạn đã thêm nó), chọn bất kỳ ô nào trong PivotTable. Sau đó, trong tab “PivotTable Analyze” trên thanh công cụ, nhấp vào “Insert Slicer”.
Chọn "Insert Slicer" trong tab PivotTable Analyze để thêm bộ lọc tương tác vào bảng tổng hợp.
Trong hộp thoại “Insert Slicers”, đánh dấu chọn vào biến hoặc các biến mà bạn muốn dùng để lọc dữ liệu – trong trường hợp này là “Product” – và nhấp “OK”.
Chọn trường "Product" trong hộp thoại Insert Slicers để tạo Slicer lọc dữ liệu theo sản phẩm.
Bây giờ, một hộp Slicer sẽ xuất hiện trên trang tính của bạn. Bạn có thể lọc lợi nhuận của từng quốc gia theo sản phẩm cụ thể bằng cách nhấp vào một trong các mục trong slicer. Hơn nữa, bạn có thể lọc theo nhiều mục cùng lúc bằng cách giữ phím Ctrl khi nhấp vào các tùy chọn. Ví dụ, PivotTable có thể hiển thị lợi nhuận của mỗi quốc gia từ doanh số bán hàng của Carretera và Velo.
Sử dụng Slicer trong Excel để lọc và chỉ hiển thị lợi nhuận từ các sản phẩm Carretera và Velo.
Để xóa tất cả các bộ lọc đã áp dụng trên Slicer, chỉ cần nhấp vào biểu tượng “Clear Filter” ở góc trên bên phải của Slicer.
3. Sử Dụng Timelines để Lọc Theo Thời Gian
Trong khi bạn có thể tạo Slicer để lọc dữ liệu theo ngày tháng, PivotTable trong Excel còn có một công cụ Timeline tích hợp, thực hiện công việc này hiệu quả hơn.
Lưu ý quan trọng: Tính năng Timeline của PivotTable chỉ hoạt động nếu một cột trong bộ dữ liệu nguồn của bạn được định dạng chính xác là ngày tháng.
Chọn bất kỳ ô nào trong PivotTable và một lần nữa, đi đến tab “PivotTable Analyze” trên thanh công cụ. Lần này, nhấp vào “Insert Timeline”. Sau đó, trong hộp thoại “Insert Timelines”, đánh dấu chọn vào trường “Date” (hoặc tên cột ngày của bạn) và nhấp “OK”.
Chọn "Insert Timeline" và trường "Date" để thêm bộ lọc thời gian trực quan vào PivotTable.
Một thanh Timeline sẽ xuất hiện trên trang tính của bạn. Bạn có thể nhấp vào mũi tên xuống trên Timeline để chia các ngày thành năm, quý, tháng hoặc ngày. Sau đó, chọn một khoảng thời gian cụ thể hoặc nhấp và kéo thanh Timeline để hiển thị dữ liệu trong một giai đoạn dài hơn. Công cụ này cực kỳ hữu ích cho việc phân tích xu hướng và hiệu suất theo thời gian.
Bộ lọc Timeline trong PivotTable Excel được cài đặt theo tháng, cho phép điều chỉnh khoảng thời gian hiển thị dữ liệu.
IV. Nhóm Dữ Liệu Tùy Chỉnh Trong PivotTable
Một cách khác để phân tích dữ liệu hiệu quả bằng PivotTable là tạo các nhóm tùy chỉnh. Điều này đặc biệt hữu ích khi bạn muốn tổng hợp các mục riêng lẻ thành các danh mục lớn hơn để so sánh hoặc báo cáo.
Ví dụ, PivotTable của bạn đang hiển thị số lượng đơn vị bán ra trên mỗi quốc gia. Tuy nhiên, bạn lại quan tâm hơn đến việc so sánh doanh số giữa các khu vực địa lý lớn như Châu Mỹ (Bắc và Nam Mỹ) và Châu Âu.
PivotTable hiển thị số lượng đơn vị bán ra tại năm quốc gia khác nhau, sẵn sàng để nhóm dữ liệu.
Để thực hiện việc này, hãy chọn tất cả các quốc gia sẽ tạo thành nhóm đầu tiên bằng cách giữ phím Ctrl trong khi bạn nhấp vào từng quốc gia. Sau đó, nhấp chuột phải vào một trong các quốc gia đã chọn và nhấp “Group”.
Chọn Canada, Mexico, và USA trong PivotTable và sử dụng tùy chọn "Group" để nhóm các quốc gia này lại.
Khi các quốc gia đó đã được nhóm lại, bạn có thể đổi tên nhóm để dễ hiểu hơn. Ví dụ, đổi tên “Group1” thành “Châu Mỹ”.
Ba quốc gia được nhóm thành một nhóm mới có tên "Châu Mỹ" trong PivotTable để phân tích tổng hợp.
Cuối cùng, lặp lại quy trình tương tự cho các hàng còn lại để tạo nhóm “Châu Âu”.
Các quốc gia trong PivotTable đã được nhóm thành "Châu Mỹ" và "Châu Âu" để so sánh hiệu suất khu vực.
Bây giờ, bạn có thể mở rộng hoặc thu gọn từng nhóm bằng cách nhấp vào biểu tượng “+” hoặc “-” bên cạnh tên nhóm để xem chi tiết hoặc tổng quan dữ liệu.
Nút mở rộng (+) và thu gọn (-) trong PivotTable giúp dễ dàng xem hoặc ẩn chi tiết các nhóm dữ liệu.
Ngoài ra, bạn có thể thay đổi cách tổng hợp dữ liệu trong cột “Values” (ví dụ: từ Sum sang Average, Count, Max, Min, v.v.) bằng cách nhấp chuột phải vào tiêu đề cột đó, di chuột qua “Summarize Values By”, và chọn tùy chọn mong muốn.
V. Phân Tích Chi Tiết Dữ Liệu Với Tính Năng Drill Down
Mặc dù PivotTable được thiết kế để tóm tắt giá trị nhằm mục đích phân tích nhanh, nhưng bạn vẫn có thể “drill down” (đi sâu) vào một danh mục cụ thể để khám phá dữ liệu chi tiết hơn. Tính năng này cho phép bạn xem các bản ghi dữ liệu gốc tạo nên một giá trị tổng hợp trong PivotTable.
Trong ví dụ này, giả sử PivotTable của bạn hiển thị các quốc gia, phân khúc, sản phẩm và số lượng bán ra cho mỗi mục. Nếu bạn muốn xem thêm thông tin về doanh số sản phẩm Paseo do các đối tác kênh tại Canada thực hiện, bạn chỉ cần nhấp đúp vào ô tương ứng trong cột “Values”.
Chọn một ô trong PivotTable đại diện cho doanh số bán sản phẩm Paseo tại Canada qua kênh đối tác để xem chi tiết.
Sau khi nhấp đúp, một trang tính mới sẽ tự động mở ra, hiển thị một bảng chứa tất cả các cột từ bộ dữ liệu gốc, nhưng chỉ cho các tiêu chí mà bạn đã chọn (trong trường hợp này là doanh số Paseo bởi đối tác kênh ở Canada). Điều này cực kỳ hữu ích để kiểm tra tính chính xác của dữ liệu, tìm hiểu nguyên nhân sâu xa của một con số, hoặc xem xét các giao dịch cụ thể.
Bảng dữ liệu chi tiết (Drill Down) được tạo ra trên một trang tính mới sau khi nhấp đúp vào giá trị trong PivotTable.
Bạn có thể áp dụng phương pháp tương tự cho các danh mục rộng hơn. Ví dụ, nhấp đúp vào tổng số đơn vị bán ra tại Canada sẽ mở một bảng trên trang tính mới, hiển thị tất cả dữ liệu doanh số cho mọi phân khúc và sản phẩm tại Canada.
VI. Tự Động Tạo Trang Báo Cáo Riêng Biệt Cho Từng Bộ Lọc
PivotTable trong Excel có thể là điểm khởi đầu để tạo ra các báo cáo cá nhân hóa một cách tự động, tiết kiệm đáng kể thời gian và công sức.
Giả sử bạn muốn gửi một báo cáo tới người quản lý của từng phân khúc kinh doanh, trong đó có chi tiết lợi nhuận của phân khúc đó theo từng quốc gia.
Đầu tiên, hãy tạo một PivotTable với trường “Country” trong khu vực “Rows” và “Profit” trong khu vực “Values”.
Tạo PivotTable với trường Country trong Rows và Profit trong Values để phân tích lợi nhuận theo quốc gia.
Tiếp theo, nhấp và kéo trường “Segment” vào khu vực “Filter” của khung PivotTable Fields. Bạn sẽ thấy bộ lọc tương ứng được thêm vào hàng 1 trên trang tính Excel.
Kéo trường Segment vào khu vực Filters của PivotTable Fields để thêm bộ lọc phân khúc vào báo cáo.
Tại thời điểm này, bạn có thể nhấp vào mũi tên xuống của bộ lọc trong ô B1 để chọn từng phân khúc và tạo báo cáo riêng lẻ theo cách thủ công.
Mở rộng bộ lọc PivotTable để hiển thị danh sách các phân khúc khác nhau có thể lựa chọn.
Tuy nhiên, công cụ PivotTable của Excel có một tính năng tích hợp cho phép bạn tạo tất cả các báo cáo này cùng một lúc. Chọn bất kỳ ô nào trong PivotTable. Sau đó, trong tab “PivotTable Analyze” trên thanh công cụ, nhấp vào mũi tên xuống của “Options”. Cuối cùng, nhấp vào “Show Report Filter Pages”.
Chọn "Show Report Filter Pages" trong tab PivotTable Analyze để tự động tạo báo cáo riêng cho từng bộ lọc.
Vì bạn đã thêm trường “Segment” vào khu vực “Filter” của PivotTable Fields, đây sẽ là tùy chọn duy nhất trong hộp thoại “Show Report Filter Pages”. Chọn nó và nhấp “OK”.
Chọn trường "Segment" trong hộp thoại Show Report Filter Pages để Excel tạo các trang báo cáo riêng cho từng phân khúc.
Ngay lập tức, Excel sẽ tạo ra các trang tính mới, mỗi trang chứa một PivotTable riêng đã được lọc sẵn cho từng phân khúc. Điều này giúp bạn dễ dàng phân tích và phân phối các báo cáo cá nhân hóa một cách hiệu quả.
Các trang báo cáo được tạo tự động bởi PivotTable, mỗi trang dành riêng cho một phân khúc, giúp phân tích chi tiết.
Kết Luận
PivotTable là một trong những tính năng mạnh mẽ và linh hoạt nhất của Microsoft Excel, vượt xa khả năng tóm tắt dữ liệu cơ bản. Bằng cách thành thạo các kỹ thuật nâng cao như hiển thị phần trăm tổng, lọc Top N, sử dụng Slicers và Timelines trực quan, nhóm dữ liệu tùy chỉnh, khám phá chi tiết với Drill Down, và tự động tạo trang báo cáo lọc, bạn có thể biến dữ liệu thô thành những thông tin chi tiết có giá trị, hỗ trợ đắc lực cho việc ra quyết định kinh doanh.
Những kỹ năng này không chỉ giúp bạn làm việc hiệu quả hơn mà còn khẳng định khả năng phân tích dữ liệu chuyên sâu của bạn trong mọi môi trường làm việc. Hãy áp dụng ngay những kiến thức này vào công việc hàng ngày của bạn để thấy sự khác biệt. Đừng ngần ngại khám phá và thử nghiệm thêm với PivotTable, vì đây là một công cụ luôn có những điều mới mẻ để bạn học hỏi.
Nếu bạn có bất kỳ câu hỏi nào về cách sử dụng PivotTable hoặc muốn chia sẻ kinh nghiệm của mình, hãy để lại bình luận bên dưới. Truy cập thuthuattonghop.net thường xuyên để cập nhật thêm các hướng dẫn và thủ thuật công nghệ hữu ích khác!
Tài Liệu Tham Khảo
- Bộ dữ liệu mẫu Microsoft
- Tìm hiểu về các tùy chọn định dạng số trong Excel
- Cách đặt tên vùng trong Excel
- Cách sử dụng hàm PIVOTBY trong Excel
