Làm thế nào để tạo ra một Pivot Table trong Excel: Hướng dẫn từng bước

Bảng tổng hợp là một trong những chức năng mạnh nhất và đáng sợ nhất của Microsoft Excel. Mạnh mẽ vì nó có thể giúp bạn tóm tắt và hiểu ý nghĩa của các tập dữ liệu lớn. Đáng sợ vì bạn không hẳn là một chuyên gia Excel và các bảng tổng hợp luôn nổi tiếng là phức tạp.

Tin vui: Học cách tạo bảng tổng hợp trong Excel dễ dàng hơn nhiều so với những gì bạn có thể tin tưởng.

Nhưng trước khi chúng tôi hướng dẫn bạn quy trình tạo một bảng tổng hợp, hãy lùi lại một bước và đảm bảo rằng bạn hiểu chính xác bảng tổng hợp là gì và tại sao bạn có thể cần sử dụng bảng tổng hợp.

Nói cách khác, các bảng tổng hợp trích xuất ý nghĩa từ mớ số lộn xộn dường như vô tận trên màn hình của bạn. Và cụ thể hơn, nó cho phép bạn nhóm dữ liệu của mình theo nhiều cách khác nhau để bạn có thể rút ra kết luận hữu ích dễ dàng hơn.

Phần "trục" của bảng tổng hợp bắt nguồn từ thực tế là bạn có thể xoay (hoặc xoay) dữ liệu trong bảng để xem dữ liệu đó từ một góc độ khác. Nói rõ hơn, bạn không thêm, bớt hoặc thay đổi dữ liệu của mình khi tạo một trục. Thay vào đó, bạn chỉ đơn giản là sắp xếp lại dữ liệu để có thể tiết lộ thông tin hữu ích từ đó.

Các bảng tổng hợp được sử dụng để làm gì?

Nếu bạn vẫn cảm thấy hơi bối rối về chức năng thực sự của bảng tổng hợp, đừng lo lắng. Đây là một trong những công nghệ dễ hiểu hơn nhiều khi bạn đã thấy nó hoạt động.

Mục đích của bảng tổng hợp là cung cấp các cách thân thiện với người dùng để nhanh chóng tóm tắt lượng lớn dữ liệu. Chúng có thể được sử dụng để hiểu rõ hơn, hiển thị và phân tích dữ liệu số một cách chi tiết — đồng thời có thể giúp xác định và trả lời các câu hỏi không lường trước được xung quanh dữ liệu đó.

Dưới đây là bảy tình huống giả định trong đó bảng tổng hợp có thể là một giải pháp:

1. So sánh tổng doanh số của các sản phẩm khác nhau.

Giả sử bạn có một trang tính chứa dữ liệu bán hàng hàng tháng cho ba sản phẩm khác nhau — sản phẩm 1, sản phẩm 2 và sản phẩm 3 — và bạn muốn tìm ra sản phẩm nào trong số ba sản phẩm đó đã mang lại nhiều tiền nhất. Tất nhiên, bạn có thể xem qua bảng tính và thêm thủ công số liệu bán hàng tương ứng vào tổng số đang chạy mỗi khi sản phẩm 1 xuất hiện. Sau đó, bạn có thể làm tương tự cho sản phẩm 2 và sản phẩm 3 cho đến khi bạn có tổng số cho tất cả chúng. Miếng bánh, phải không?

Bây giờ, hãy tưởng tượng bảng tính bán hàng hàng tháng của bạn có hàng nghìn hàng nghìn hàng. Việc sắp xếp tất cả chúng theo cách thủ công có thể mất cả đời. Sử dụng bảng tổng hợp, bạn có thể tự động tổng hợp tất cả các số liệu bán hàng cho sản phẩm 1, sản phẩm 2 và sản phẩm 3 — và tính tổng tương ứng của chúng — trong vòng chưa đầy một phút.

2. Hiển thị doanh số sản phẩm dưới dạng tỷ lệ phần trăm trên tổng doanh số.

Bảng tổng hợp tự nhiên hiển thị tổng của mỗi hàng hoặc cột khi bạn tạo chúng. Nhưng đó không phải là con số duy nhất bạn có thể tự động tạo ra.

Giả sử bạn đã nhập số lượng bán hàng hàng quý cho ba sản phẩm riêng biệt vào một trang tính Excel và biến dữ liệu này thành một bảng tổng hợp. Bảng sẽ tự động cung cấp cho bạn ba tổng số ở cuối mỗi cột — đã cộng doanh số bán hàng quý của mỗi sản phẩm. Nhưng nếu bạn muốn tìm tỷ lệ phần trăm doanh số bán sản phẩm này đã đóng góp vào tất cả doanh số bán hàng của công ty, thay vì chỉ tổng doanh số bán hàng của những sản phẩm đó?

Với bảng tổng hợp, bạn có thể định cấu hình từng cột để cung cấp cho bạn tỷ lệ phần trăm của cột trong tổng số ba cột, thay vì chỉ tổng số cột. Ví dụ: nếu ba lần bán sản phẩm có tổng doanh thu là 200,000 đô la và sản phẩm đầu tiên kiếm được 45,000 đô la, bạn có thể chỉnh sửa bảng tổng hợp để thay vào đó nói sản phẩm này đóng góp 22.5% tổng doanh số của công ty.

Để hiển thị doanh số bán sản phẩm dưới dạng tỷ lệ phần trăm của tổng doanh số trong bảng tổng hợp, chỉ cần nhấp chuột phải vào ô có tổng doanh số và chọn Hiển thị giá trị dưới dạng > % trong tổng số.

3. Kết hợp dữ liệu trùng lặp.

Trong trường hợp này, bạn vừa hoàn thành việc thiết kế lại blog và phải cập nhật một loạt URL. Thật không may, phần mềm báo cáo blog của bạn đã không xử lý tốt vấn đề này và cuối cùng đã chia tách số liệu "lượt xem" cho các bài đăng đơn lẻ giữa hai URL khác nhau. Vì vậy, trong bảng tính của bạn, bạn có hai phiên bản riêng biệt của từng bài đăng blog riêng lẻ. Để có được dữ liệu chính xác, bạn cần kết hợp tổng số lượt xem cho từng bản sao này.

Đó là nơi bảng tổng hợp phát huy tác dụng. Thay vì phải tìm kiếm và kết hợp tất cả các số liệu từ các bài trùng lặp theo cách thủ công, bạn có thể tóm tắt dữ liệu của mình (thông qua bảng tổng hợp) theo tiêu đề bài đăng trên blog và voilà: số liệu lượt xem từ các bài đăng trùng lặp đó sẽ được tổng hợp tự động.

4. Lấy số lượng nhân viên cho các bộ phận riêng biệt.

Bảng tổng hợp rất hữu ích để tự động tính toán những thứ mà bạn không thể dễ dàng tìm thấy trong bảng Excel cơ bản. Một trong những điều đó là đếm các hàng mà tất cả đều có điểm chung.

Ví dụ: nếu bạn có danh sách nhân viên trong trang tính Excel và bên cạnh tên của nhân viên là các phòng ban tương ứng mà họ trực thuộc, bạn có thể tạo bảng tổng hợp từ dữ liệu này để hiển thị cho bạn từng tên phòng ban và số lượng nhân viên thuộc các khoa đó. Bảng tổng hợp loại bỏ hiệu quả nhiệm vụ sắp xếp trang tính Excel theo tên bộ phận và đếm từng hàng theo cách thủ công.

5. Thêm giá trị mặc định vào ô trống.

Không phải mọi tập dữ liệu bạn nhập vào Excel sẽ điền vào mọi ô. Nếu bạn đang đợi dữ liệu mới xuất hiện trước khi nhập dữ liệu đó vào Excel, bạn có thể có nhiều ô trống trông khó hiểu hoặc cần giải thích thêm khi hiển thị dữ liệu này cho người quản lý của mình. Đó là nơi các bảng tổng hợp xuất hiện.

Bạn có thể dễ dàng tùy chỉnh bảng tổng hợp để điền vào các ô trống bằng giá trị mặc định, chẳng hạn như $0 hoặc TBD (đối với “được xác định”). Đối với các bảng dữ liệu lớn, việc có thể gắn thẻ các ô này một cách nhanh chóng là một tính năng hữu ích khi nhiều người đang xem xét cùng một trang tính.

Để tự động định dạng các ô trống của bảng tổng hợp, hãy bấm chuột phải vào bảng của bạn và bấm Tùy chọn PivotTable. Trong cửa sổ xuất hiện, đánh dấu vào ô có nhãn Các ô trống dưới dạng và nhập nội dung bạn muốn hiển thị khi một ô không có giá trị nào khác.

Cách tạo bảng tổng hợp

1. Nhập dữ liệu của bạn vào một phạm vi hàng và cột.
2. Sắp xếp dữ liệu của bạn theo một thuộc tính cụ thể.
3. Đánh dấu các ô của bạn để tạo bảng tổng hợp của bạn.
4. Kéo và thả một trường vào vùng “Row Labels”.
5. Kéo và thả một trường vào khu vực “Giá trị”.
6. Tinh chỉnh tính toán của bạn.

Bây giờ bạn đã hiểu rõ hơn về những gì các bảng tổng hợp có thể được sử dụng, hãy tìm hiểu chi tiết về cách thực sự tạo một bảng tổng hợp.

Bước 1. Nhập dữ liệu của bạn vào một phạm vi hàng và cột.

Mọi bảng tổng hợp trong Excel đều bắt đầu bằng một bảng Excel cơ bản, nơi lưu trữ tất cả dữ liệu của bạn. Để tạo bảng này, chỉ cần nhập các giá trị của bạn vào một nhóm hàng và cột cụ thể. Sử dụng hàng trên cùng hoặc cột trên cùng để phân loại các giá trị của bạn theo những gì chúng đại diện.

Ví dụ: để tạo bảng Excel về dữ liệu hiệu suất của bài đăng trên blog, bạn có thể có một cột liệt kê từng “Trang hàng đầu”, một cột liệt kê “Số lần nhấp” của mỗi URL, một cột liệt kê “Số lần hiển thị” của mỗi bài đăng, v.v. (Chúng tôi sẽ sử dụng ví dụ đó trong các bước tiếp theo.)

Bước 2. Sắp xếp dữ liệu của bạn theo một thuộc tính cụ thể.

Khi bạn có tất cả dữ liệu bạn muốn nhập vào trang tính Excel của mình, bạn sẽ muốn sắp xếp dữ liệu này theo một cách nào đó để dễ quản lý hơn khi bạn biến nó thành bảng tổng hợp.

Để sắp xếp dữ liệu của bạn, hãy bấm vào Ngày trong thanh điều hướng trên cùng và chọn Sắp xếp biểu tượng bên dưới nó. Trong cửa sổ xuất hiện, bạn có thể chọn sắp xếp dữ liệu của mình theo bất kỳ cột nào bạn muốn và theo bất kỳ thứ tự nào.

Ví dụ: để sắp xếp trang tính Excel của bạn theo “Lượt xem đến ngày”, hãy chọn tiêu đề cột này bên dưới Cột rồi chọn xem bạn muốn sắp xếp các bài đăng của mình từ nhỏ nhất đến lớn nhất hay từ lớn nhất đến nhỏ nhất.

Chọn OK ở dưới cùng bên phải của cửa sổ Sắp xếp và bạn sẽ sắp xếp lại thành công từng hàng trong trang tính Excel của mình theo số lượt xem mà mỗi bài đăng trên blog đã nhận được.

Bước 3. Đánh dấu các ô của bạn để tạo bảng tổng hợp.

Khi bạn đã nhập dữ liệu vào trang tính Excel của mình và sắp xếp dữ liệu theo ý thích, hãy đánh dấu các ô bạn muốn tóm tắt trong bảng tổng hợp. Nhấp chuột Chèn dọc theo điều hướng trên cùng và chọn bảng tổng hợp biểu tượng. Bạn cũng có thể bấm vào bất kỳ đâu trong trang tính của mình, chọn “PivotTable” và nhập thủ công phạm vi ô bạn muốn đưa vào PivotTable.

Điều này sẽ mở ra một hộp tùy chọn, ngoài việc đặt phạm vi ô của bạn, bạn có thể chọn có khởi chạy bảng tổng hợp này trong một trang tính mới hay giữ nó trong trang tính hiện có hay không. Nếu bạn mở một trang tính mới, bạn có thể điều hướng đến và rời khỏi trang tính đó ở cuối sổ làm việc Excel của mình. Khi bạn đã chọn, hãy nhấp vào OK.

Hoặc, bạn có thể tô sáng các ô của mình, chọn PivotTable được đề xuất ở bên phải của biểu tượng PivotTable và mở bảng tổng hợp với các đề xuất được đặt trước về cách sắp xếp từng hàng và cột.

Lưu ý: Nếu bạn đang sử dụng phiên bản Excel cũ hơn, “PivotTable” có thể nằm dưới Bàn or Ngày dọc theo điều hướng trên cùng, thay vì “Chèn”. Trong Google Trang tính, bạn có thể tạo bảng tổng hợp từ Ngày menu thả xuống dọc theo điều hướng trên cùng.

Bước 4. Kéo và thả một trường vào vùng “Row Labels”.

Sau khi bạn hoàn thành Bước 3, Excel sẽ tạo một bảng tổng hợp trống cho bạn. Bước tiếp theo của bạn là kéo và thả một trường — được gắn nhãn theo tên của các cột trong bảng tính của bạn — vào Nhãn hàng khu vực. Điều này sẽ xác định định danh duy nhất nào — tiêu đề bài đăng trên blog, tên sản phẩm, v.v. — bảng tổng hợp sẽ sắp xếp dữ liệu của bạn theo.

Ví dụ: giả sử bạn muốn sắp xếp một loạt dữ liệu viết blog theo tiêu đề bài đăng. Để làm điều đó, bạn chỉ cần nhấp và kéo trường “Các trang hàng đầu” vào khu vực “Nhãn hàng”.

Lưu ý: Bảng tổng hợp của bạn có thể trông khác tùy thuộc vào phiên bản Excel mà bạn đang làm việc. Tuy nhiên, các nguyên tắc chung vẫn giữ nguyên.

Bước 5. Kéo và thả một trường vào khu vực “Giá trị”.

Khi bạn đã thiết lập những gì bạn sẽ sắp xếp dữ liệu của mình, bước tiếp theo của bạn là thêm vào một số giá trị bằng cách kéo một trường vào Các giá trị khu vực.

Bám sát ví dụ về dữ liệu viết blog, giả sử bạn muốn tóm tắt lượt xem bài đăng trên blog theo tiêu đề. Để làm điều này, bạn chỉ cần kéo trường “Chế độ xem” vào khu vực Giá trị.

Bước 6. Tinh chỉnh các tính toán của bạn.

Tổng của một giá trị cụ thể sẽ được tính toán theo mặc định, nhưng bạn có thể dễ dàng thay đổi giá trị này thành giá trị trung bình, tối đa hoặc tối thiểu tùy thuộc vào những gì bạn muốn tính toán.

Trên máy Mac, bạn có thể thực hiện việc này bằng cách nhấp vào biểu tượng nhỏ i bên cạnh một giá trị trong vùng “Giá trị”, chọn tùy chọn bạn muốn và nhấp vào “OK”. Khi bạn đã thực hiện lựa chọn của mình, bảng tổng hợp của bạn sẽ được cập nhật tương ứng.

Nếu đang sử dụng PC, bạn cần nhấp vào hình tam giác nhỏ lộn ngược bên cạnh giá trị của mình và chọn Cài đặt trường giá trị để truy cập menu.

Khi bạn đã phân loại dữ liệu của mình theo ý thích, hãy lưu công việc của bạn và sử dụng nó theo ý muốn.

Đào sâu hơn với Pivot Table

Bây giờ bạn đã học được những kiến ​​thức cơ bản về tạo bảng tổng hợp trong Excel. Với sự hiểu biết này, bạn có thể tìm ra những gì bạn cần từ bảng tổng hợp của mình và tìm ra các giải pháp mà bạn đang tìm kiếm.

Ví dụ: bạn có thể nhận thấy rằng dữ liệu trong bảng tổng hợp của mình không được sắp xếp theo cách bạn muốn. Trong trường hợp này, chức năng Sắp xếp của Excel có thể giúp bạn giải quyết. Ngoài ra, bạn có thể cần kết hợp dữ liệu từ một nguồn khác vào báo cáo của mình, trong trường hợp đó, chức năng VLOOKUP có thể hữu ích.

Ghi chú của biên tập viên: Bài đăng này ban đầu được xuất bản vào tháng 2018 năm XNUMX và đã được cập nhật để có tính toàn diện.

Nguồn: https://blog.hubspot.com/marketing/how-to-create-pivot-table-tutorial-ht