Pivot table 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.
Pivot table là một bản tóm tắt dữ liệu, được hiển thị trong một 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 bạn. Pivot table đặc biệt hữu ích nếu bạn có các hàng hoặc cột dài chứa các giá trị mà bạn cần để theo dõi các tổng và dễ dàng so sánh với nhau.
Pivot cho phép bạn nhóm các 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 1 cách dễ dàng.
Chữ “pivot” bắt nguồn 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, bớt đi hoặc thay đổi dữ liệu của mình khi bạn thực hiện xoay vòng. Thay vào đó, bạn chỉ cần sắp xếp lại dữ liệu để có thể xem thông tin hữu ích từ nó.
Vậy làm thế nào để tạo Pivot table trong excel?
Pivot table được sử dụng để làm gì?
Mục đích của Pivot table 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, hiển thị và phân tích dữ liệu số một cách chi tiết – và có thể giúp xác định và trả lời các câu hỏi bất ngờ xung quanh nó.
Dưới đây là bảy tình huống giả định trong đó pivot table 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 nhiên, bạn có thể xem qua trang tính và thêm số liệu bán hàng tương ứng theo cách thủ công vào tổng số bán hàng đ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 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 vạ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 pivot table, 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
Pivot table 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ố 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 dữ liệu này thành một Pivot table. Bảng sẽ tự động cung cấp cho bạn ba số tổng ở cuối mỗi cột đã cộng thêm doanh số bán hàng quý của 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 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 pivot table, bạn có thể phân tích mỗi cột để cung cấp tỷ lệ phần trăm của 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 bán được tổng doanh thu là $200.000 và sản phẩm đầu tiên kiếm được $45.000, bạn có thể chỉnh sửa bảng tổng hợp để thay vào đó, sản phẩm này đóng góp 22,5% tổng doanh thu của 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 pivot table, chỉ cần nhấp chuột phải vào ô có tổng doanh số và chọn Show Values As > % of Grand Total.
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 thiết kế lại blog và phải cập nhật một loạt các URL. Thật không may, phần mềm báo cáo blog của bạn không xử lý nó tốt lắm 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 spreadsheet, bạn có hai phiên bản riêng biệt của từng bài đăng trên blog cá nhân. Để 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à pivot table 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 pivot table theo tiêu đề bài đăng trên blog và vô tình 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. Nhận số lượng nhân viên cho các bộ phận riêng biệt
Pivot table 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ọ thuộc về, bạn có thể tạo pivot table từ dữ liệu này hiển thị tên từng phòng ban và số lượng nhân viên thuộc các phòng ban đó. Pivot table giú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 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 đưa vào trước khi nhập nó vào Excel, bạn có thể có nhiều ô 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 quản lý của bạn. Đó là nơi xuất hiện các pivot table.
Bạn có thể dễ dàng tùy chỉnh pivot table để lấp đầy các ô trống bằng giá trị mặc định, chẳng hạn 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 PivotTable Options. 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 Pivot Table
1. Nhập dữ liệu đồng loạt vào các 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 các ô để tạo bảng tổng hợp.
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 vùng “Values”.
6. Tinh chỉnh các phép tính.
Bây giờ bạn đã hiểu rõ hơn về những tính năng của pivot table, bạn có thể tạo pivot table theo các bước dưới đây.
Bước 1. Nhập dữ liệu đồng loạt vào các hàng và cột
Mọi pivot table trong Excel đều bắt đầu bằng một bảng Excel cơ bản, nơi chứa 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.
Ví dụ: để tạo bảng Excel dữ liệu hiệu suất bài đăng trên blog, bạn có thể tạo một cột liệt kê từng “Top Pages”, cột liệt kê “Clicks” của mỗi URL, cột liệt kê “Impressions” của mỗi bài đăng.
Bước 2. Sắp xếp dữ liệu theo một thuộc tính cụ thể
Khi 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 sau khi chuyển nó thành pivot table.
Để sắp xếp dữ liệu, hãy nhấn vào tab Data trong thanh điều hướng trên cùng và chọn biểu tượng Sort 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 “Views to Date”, hãy chọn tiêu đề cột này trong Column 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 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ổ Sort 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ước 3. Đánh dấu các ô để tạo pivot table
Khi đã nhập dữ liệu vào trang tính Excel và sắp xếp nó theo ý muốn của mình, hãy đánh dấu các ô bạn muốn tóm tắt trong pivot table. Nhấn vào Insert 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ấn 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, trong đó, ngoài việc đặt phạm vi ô, bạn có thể chọn khởi chạy Pivot table này trong một trang tính mới hoặc giữ nó trong 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. Sau khi bạn đã chọn, hãy nhấp vào OK.
Ngoài ra , bạn có thể đánh dấu các ô, 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 đặt trước về cách tổ chức từng hàng và cột.
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 vùng Row Labels . Điều này sẽ xác định 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ẽ tổ chức 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 đó, bạn chỉ cần nhấn và kéo trường “Top pages” vào khu vực “Row Labels”.
Bước 5. Kéo và thả một trường vào vùng “Values”
Khi đã 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 Value.
Liên quan với 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 đề. Để làm điều này, bạn chỉ cần kéo trường “Views” vào vùng Value.
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 “Value”, 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 nhỏ lộn ngược bên cạnh giá trị của mình và chọn Value Field Settings để 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 tùy ý.
Hiểu rõ hơn với Pivot Table
Bây giờ bạn đã biết được những kiến thức cơ bản về 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, Sort function 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 ô đã chọn trong Excel
