Hướng dẫn chi tiết 6 bước tạo Pivot Table trong Excel [NEW]

Bảng tổng hợp là một trong những công cụ mạnh nhất – và đáng sợ nhất của Microsoft Excel vì nó có thể giúp bạn tóm tắt và phân tích các tập dữ liệu lớn.

Bảng tổng hợp là một bản tóm tắt dữ liệu, được hiển thị dưới dạng biểu đồ cho phép bạn báo cáo và khám phá các xu hướng dựa trên thông tin của mình. Bảng tổng hợp đặc biệt hữu ích nếu bạn có các hàng dài hoặc cột giá trị mà bạn cần theo dõi các tổng và dễ dàng so sánh chúng với nhau.

Pivot 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ể đưa ra kết luận dễ dàng.

Từ “pivot” xuất phát từ thực tế là bạn có thể xoay dữ liệu trong bảng để xem nó từ một góc độ khác. Nói cách khác, bạn không thêm vào, trừ đi hoặc thay đổi dữ liệu của mình khi thực hiện xoay vòng. Thay vào đó, bạn có thể đơn giản sắp xếp lại dữ liệu để có thể xem thông tin hữu ích từ nó.

Vậy cách tạo Pivot table trong excel như thế nào?

Bảng Pivot được sử dụng để làm gì?

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

Dưới đây là bảy tình huống giả định trong đó bảng xoay 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 hiểu xem sản phẩm nào trong ba sản phẩm mang lại nhiều tiền nhất. tốt nhất. Tất nhiên, bạn có thể xem qua trang tính và thêm thủ công các số liệu bán hàng tương ứng vào tổng doanh số đang chạy mỗi khi sản phẩm 1 ra mắt. 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 cho tất cả các sản phẩm.

Hãy tưởng tượng bảng tính doanh số hàng tháng của bạn có hàng nghìn, hàng chục nghìn hàng. Việc phân loại chúng theo cách thủ công có thể mất rất nhiều thời gian. 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 số tiền tương ứng của chúng trong vòng chưa đầy một phút.

2. Hiển thị doanh số bán sản phẩm dưới dạng phần trăm tổng doanh số bán hàng

Bảng tổng hợp 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.

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

Với bảng tổng hợp, bạn có thể phân tích từng cột để cung cấp tỷ lệ phần trăm cột của cả ba tổng số cột, thay vì chỉ tổng số cột. Ví dụ: nếu ba sản phẩm được bán với tổng giá 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 để sản phẩm này đóng góp 22,5% tổng doanh thu của công ty. Công ty.

Để hiển thị doanh số bán sản phẩm dưới dạng phần trăm 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>% của Tổng doanh 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 các URL. Rất tiếc, phần mềm báo cáo blog của bạn xử lý không tốt và cuối cùng đã tách các chỉ số “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, bạn có hai phiên bản riêng biệt của từng bài đăng trên blog riêng lẻ. Để có được dữ liệu chính xác, bạn cần kết hợp các tổng số lượt xem cho từng bản sao này.

Đó là nơi mà các bảng xoay vòng 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 đăng 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à vô tình xem các số liệu. từ những bài viết 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 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 nhân viên là các phòng ban tương ứng mà họ thuộc về, bạn có thể tạo bảng tổng hợp từ dữ liệu này hiển thị tên của từng phòng ban và số lượng nhân viên. . số lượng nhân viên trong các bộ phận đó. Bảng tổng hợp loại bỏ hiệu quả nhiệm vụ sắp xếp các trang tính Excel theo tên bộ phận và đếm hàng theo cách thủ công.

5. Thêm giá trị mặc định vào các ô 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 đến trước khi nhập vào Excel, bạn có thể có nhiều ô trống gây nhầm lẫn 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 xuất hiện các bảng tổng hợp.

Bạn có thể dễ dàng tùy chỉnh bảng tổng hợp để lấp đầy các ô trống với giá trị mặc định như $ 0 hoặc TBD được xác định. Đối với các bảng dữ liệu lớn, 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, hãy chọn hộp có nhãn Empty Cells As và nhập những gì bạn muốn hiển thị khi một ô không có giá trị khác.

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

1. Nhập dữ liệu hàng loạt theo hàng và cột.

2. Sắp xếp dữ liệu theo một thuộc tính cụ thể.

3. Đánh dấu vào các hộp để tạo bảng tổng hợp.

4. Kéo và thả một trường vào vùng “Nhãn Hàng”.

5. Kéo và thả một trường vào vùng “Giá trị”.

6. Tinh chỉnh các phép tính.

Bây giờ bạn đã hiểu rõ hơn về các tính năng của bảng tổng hợp, bạn có thể tạo bảng tổng hợp bằng cách làm theo các bước bên dưới.

Bước 1. Nhập dữ liệu hàng loạt theo 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 tập hợp các 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.

tạo bảng tổng hợp trong excel

Ví dụ: để tạo bảng Excel dữ liệu hiệu suất bài đăng blog, bạn có thể tạo một cột liệt kê từng “Trang hàng đầu”, cột liệt kê “Số lần nhấp” của mỗi URL và cột liệt kê “Số lần hiển thị” của mỗi URL bưu kiện. .

Bước 2. Sắp xếp dữ liệu 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 tổ chức dữ liệu này theo cách dễ quản lý hơn sau khi chuyển nó thành bảng tổng hợp.

Để sắp xếp dữ liệu, hãy nhấn vào tab Dữ liệu trong thanh điều hướng trên cùng và chọn biểu tượng Sắp xếp 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 trong Cột và sau đó chọn bạn muốn sắp xếp các bài đăng từ nhỏ nhất đến lớn nhất hoặc 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ắ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ảng xoay

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

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

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

Ngoài ra, bạn có thể đánh dấu các ô, chọn một PivotTable được đề xuất ở bên phải biểu tượng PivotTable và mở bảng tổng hợp với các đề xuất đặt trước về cách tổ chức từng hàng và cột.

bảng xoay

Lưu ý: Nếu bạn đang sử dụng phiên bản Excel cũ hơn, “PivotTables” có thể nằm trong Bảng hoặc Dữ liệu 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ừ menu thả xuống Dữ liệu 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 “Nhãn hàng”

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 bằng tên của các cột trong bảng tính của bạn – vào vùng Nhãn Hàng. Điều này sẽ xác định một số nhận dạng duy nhất – tiêu đề bài đăng trên blog, tên sản phẩm, bảng tổng hợp sẽ sắp xếp theo dữ liệu của bạn.

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

bảng xoay

Lưu ý: PivotTable 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 được giữ nguyên.

Bước 5. Kéo và thả một trường vào vùng “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 là thêm một số giá trị bằng cách kéo một trường vào vùng Giá trị.

bảng xoay

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

Bước 6. Tinh chỉnh các phép tính

Tổng của một giá trị cụ thể sẽ được tính 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 Mac, bạn có thể thực hiện việc này bằng cách nhấp vào chữ i nhỏ bên cạnh 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 đã lựa chọn xong, 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 lộn ngược nhỏ 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 theo ý muốn của mình, hãy lưu công việc của bạn và sử dụng nó tùy ý.

Có được thông tin chi tiết với Pivot Table

Bây giờ bạn đã biết những điều cơ bản về cách tạo Pivot Table 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ừ PivotTables của mình và tìm ra các giải pháp 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 bạn không được sắp xếp theo cách bạn muốn. Nếu đúng như vậy, hàm Sắp xếp của Excel có thể giúp bạn. Ngoài ra, bạn có thể 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 đó, hàm VLOOKUP có thể hữu ích cho bạn.

Xem thêm: 3 cách khóa các ô đã chọn trong Excel