Excel Pivot Tables: Beginner to Advanced

Tôi vẫn nhớ khoảnh khắc tôi nhận ra rằng mình đã làm phân tích dữ liệu sai trong ba năm. Đó là năm 2009, và tôi đang ngồi trong một phòng hội nghị chật chội tại một công ty sản xuất vừa ở Ohio, quan sát đồng nghiệp của tôi, Sarah, biến một bảng tính 50.000 hàng thành một phân tích doanh số toàn diện trong chưa đầy bốn phút. Tôi đã dành cả buổi chiều trước đó để tạo các công thức thủ công, sao chép dữ liệu giữa các bảng và xây dựng các biểu đồ mà tốn tới sáu giờ để hoàn thành. Sarah đã sử dụng một thứ gọi là bảng xoay (pivot table), và trong khoảnh khắc đó, toàn bộ cách tiếp cận của tôi đối với việc làm việc với dữ liệu đã thay đổi hoàn toàn.

Ngày nay, sau 15 năm làm nhà phân tích tài chính và tư vấn dữ liệu làm việc với mọi người từ các công ty Fortune 500 đến các tổ chức phi lợi nhuận nhỏ, tôi đã thấy các bảng xoay giúp các tổ chức tiết kiệm hàng nghìn giờ và hàng triệu đô la. Tôi đã đào tạo hơn 2.000 chuyên gia về kỹ thuật Excel, và các bảng xoay vẫn là công cụ biến đổi duy nhất mà tôi dạy. Tuy nhiên, tôi ước tính rằng ít hơn 30% người dùng Excel thực sự hiểu cách sử dụng chúng một cách hiệu quả.

Bài viết này là nỗ lực của tôi để thay đổi điều đó. Tôi sẽ dẫn bạn qua tất cả những gì tôi ước ai đó đã dạy tôi vào năm 2009—từ những điều cơ bản nhất đến các kỹ thuật nâng cao sẽ khiến bạn trông như một phù thủy dữ liệu trước đồng nghiệp của mình.

Bảng Xoay là Gì và Tại Sao Bạn Nên Quan Tâm?

Để bắt đầu, hãy để tôi đưa ra một định nghĩa thực sự có ý nghĩa: một bảng xoay là một công cụ tóm tắt động cho phép bạn tổ chức lại và phân tích các tập dữ liệu lớn mà không cần viết một công thức nào. Hãy nghĩ về nó như một con dao Swiss Army cho phân tích dữ liệu—nó có thể cắt, xắt, nhóm, lọc và tính toán dữ liệu của bạn theo hàng chục cách khác nhau, tất cả thông qua một giao diện kéo và thả đơn giản.

Dưới đây là lý do tại sao điều này quan trọng trong thực tiễn. Năm ngoái, tôi đã làm việc với một khách hàng bán lẻ có 127.000 bản ghi giao dịch kéo dài ba năm. Giám đốc marketing của họ cần trả lời các câu hỏi như: Sản phẩm nào bán chạy nhất ở mỗi khu vực? Những xu hướng theo mùa là gì? Những phân khúc khách hàng nào tạo ra nhiều doanh thu nhất? Sử dụng các công thức Excel truyền thống và phân tích thủ công, điều này sẽ mất vài ngày. Với các bảng xoay, chúng tôi đã trả lời tất cả những câu hỏi này trong khoảng 45 phút.

Thực lực thực sự của các bảng xoay nằm ở sự linh hoạt của chúng. Khác với các công thức tĩnh bị hỏng khi bạn thêm dữ liệu mới, các bảng xoay có thể được làm mới chỉ bằng một cú nhấp chuột duy nhất. Khác với các tóm tắt thủ công yêu cầu xây dựng lại khi bạn muốn xem dữ liệu theo cách khác, các bảng xoay cho phép bạn sắp xếp lại phân tích của mình ngay lập tức. Tôi đã thấy các nhà phân tích giảm thời gian báo cáo hàng tháng từ hai ngày đầy đủ xuống còn chưa đầy hai giờ chỉ bằng cách chuyển từ các báo cáo dựa trên công thức sang bảng điều khiển bảng xoay.

Nhưng đây là điều mà hầu hết người mới bắt đầu không nhận ra: các bảng xoay không chỉ dành cho các tập dữ liệu khổng lồ. Ngay cả khi bạn đang làm việc với vài trăm hàng dữ liệu, các bảng xoay có thể tiết kiệm thời gian và giảm thiểu sai sót. Tôi sử dụng chúng cho mọi thứ từ phân tích chi phí cá nhân đến theo dõi thời gian dự án cho khách hàng. Khi bạn hiểu logic đằng sau chúng, bạn sẽ bắt đầu thấy cơ hội để sử dụng các bảng xoay ở khắp mọi nơi.

Thiết Lập Dữ Liệu của Bạn: Nền Tảng Mà Mọi Người Đều Bỏ Qua

Đây là nơi mà hầu hết các hướng dẫn về bảng xoay làm bạn thất vọng. Họ nhảy thẳng vào việc tạo bảng xoay mà không giải thích rằng cấu trúc dữ liệu của bạn quyết định liệu phân tích của bạn sẽ dễ dàng hay không thể. Tôi đã tư vấn cho hàng chục công ty nơi các nhà phân tích gặp khó khăn với các bảng xoay không phải vì họ không hiểu công cụ, mà vì dữ liệu của họ đã được thiết lập sai ngay từ đầu.

"Một bảng xoay về cơ bản là một động cơ tóm tắt động loại bỏ 90% công việc thủ công trong phân tích dữ liệu. Những gì mất hàng giờ với công thức mất vài phút với bảng xoay."

Dưới đây là các quy tắc không thể thương lượng cho dữ liệu sẵn sàng bảng xoay. Thứ nhất, dữ liệu của bạn phải ở định dạng bảng đúng với các tiêu đề ở hàng đầu tiên và không có hàng hoặc cột trống. Mỗi cột cần có một tiêu đề rõ ràng, mô tả—không phải "Cột1" hoặc ô trống. Tôi từng mất ba giờ để khắc phục một bảng xoay của khách hàng chỉ để phát hiện ra rằng họ có một hàng trống ở giữa tập dữ liệu của họ đã gây ra tất cả các phép tính của họ thất bại.

Thứ hai, mỗi cột chỉ nên chứa một loại dữ liệu. Điều này nghe có vẻ hiển nhiên, nhưng tôi liên tục thấy các bảng tính mà ai đó đã kết hợp thông tin—như việc đặt "Q1 2023" vào một ô duy nhất thay vì có các cột riêng biệt cho quý và năm. Khi dữ liệu của bạn được tách biệt đúng cách, bạn có thể nhóm và lọc nó theo những cách mạnh mẽ. Khi nó được kết hợp, bạn bị mắc kẹt với những tùy chọn hạn chế.

Thứ ba, tránh các ô hợp nhất, các tổng phụ và định dạng trong phạm vi dữ liệu của bạn. Đây là kẻ thù của các bảng xoay. Tôi đã làm việc với một công ty dịch vụ tài chính có các báo cáo được định dạng đẹp với các tiêu đề hợp nhất và các hàng tổng phụ cách nhau mỗi 20 dòng. Nó trông tuyệt vời đối với người đọc, nhưng hoàn toàn không sử dụng được cho các bảng xoay. Chúng tôi đã phải loại bỏ tất cả định dạng và xây dựng lại cấu trúc dữ liệu của họ trước khi có thể thực hiện bất kỳ phân tích có ý nghĩa nào.

Dưới đây là một ví dụ thực tiễn. Cấu trúc dữ liệu xấu: một cột được gán nhãn "Thông Tin Bán Hàng" chứa văn bản như "John Smith - 45,000 đô la - Đông Bắc." Cấu trúc dữ liệu tốt: ba cột riêng biệt được gán nhãn "Đại Diện Bán Hàng," "Doanh Thu," và "Khu Vực" với dữ liệu được tách biệt đúng cách. Phiên bản thứ hai mất 30 giây để thiết lập ban đầu nhưng tiết kiệm cho bạn hàng giờ thất vọng sau này.

Tạo Bảng Xoay Đầu Tiên: Hướng Dẫn Từng Bước

Hãy cùng tạo bảng xoay đầu tiên của bạn. Tôi sẽ sử dụng một kịch bản thực tế dựa trên một dự án mà tôi đã hoàn thành vào tháng trước cho một công ty thương mại điện tử. Hãy tưởng tượng bạn có một tập dữ liệu với 5.000 đơn hàng chứa các cột cho Ngày Đặt Hàng, Tên Khách Hàng, Danh Mục Sản Phẩm, Tên Sản Phẩm, Số Lượng, Giá Đơn Vị và Tổng Doanh Thu.

Bắt đầu bằng cách nhấp vào bất kỳ đâu trong phạm vi dữ liệu của bạn—bạn không cần phải chọn toàn bộ tập dữ liệu. Đi tới tab Chèn và nhấp vào "Bảng Xoay." Excel sẽ tự động phát hiện phạm vi dữ liệu của bạn và hỏi bạn muốn đặt bảng xoay ở đâu. Tôi luôn khuyên bạn nên chọn "Bảng tính Mới" cho vài bảng xoay đầu tiên của bạn. Điều này giữ cho dữ liệu gốc của bạn tách biệt và làm cho việc thử nghiệm trở nên dễ dàng hơn mà không lo lắng về việc làm hỏng bất cứ điều gì.

Khi bạn nhấp OK, bạn sẽ thấy một bảng xoay trống ở bên trái và pane Trường Bảng Xoay ở bên phải. Đây là nơi phép màu xảy ra. Pane trường hiển thị tất cả các tiêu đề cột của bạn, và bên dưới nó là bốn khu vực: Lọc, Cột, Hàng và Giá Trị. Hãy nghĩ về những điều này như là những khối xây dựng cho phân tích của bạn.

Hãy trả lời một câu hỏi đơn giản: Doanh thu tổng cộng của mỗi danh mục sản phẩm là bao nhiêu? Kéo "Danh Mục Sản Phẩm" vào khu vực Hàng và "Tổng Doanh Thu" vào khu vực Giá Trị. Ngay lập tức, Excel tính toán tổng doanh thu cho mỗi danh mục. Không cần công thức, không cần tính thủ công—chỉ cần kéo và thả. Khi tôi trình bày điều này cho những người mới bắt đầu, tôi có thể nhìn thấy chính xác khoảnh khắc họ nhận ra rằng họ đã lãng phí bao nhiêu thời gian.

Giờ hãy làm cho nó thú vị hơn. Kéo "Ngày Đặt Hàng" vào khu vực Cột. Excel tự động nhóm các ngày theo tháng và năm, cho bạn thấy mỗi danh mục hoạt động như thế nào theo thời gian. Đây là loại phân tích sẽ yêu cầu các công thức phức tạp và nhiều bước sử dụng các phương pháp truyền thống, nhưng với các bảng xoay, nó chỉ mất khoảng năm giây.

Đây là một mẹo chuyên nghiệp mà tôi đã học được cách khó khăn: luôn kiểm tra loại phép tính mà Excel đang sử dụng trong khu vực Giá Trị. Theo mặc định, nó cộng các số và đếm văn bản. Nhưng đôi khi bạn muốn trung bình, tỷ lệ phần trăm hoặc các phép tính khác. Nhấp vào biểu tượng "i" nhỏ bên cạnh trường của bạn trong khu vực Giá Trị để thay đổi loại phép tính. Tôi từng trình bày một báo cáo cho các giám đốc điều hành cho thấy "số lượng doanh thu" thay vì "tổng doanh thu" vì tôi quên kiểm tra cài đặt này. Điều đó thật xấu hổ và hoàn toàn có thể tránh được.