Hướng dẫn tạo Pivot Table khi báo cáo trong Excel

0
557

Pivot table là một trong những công cụ mạnh mẽ nhưng khó nhằn có tiếng của Microsoft Excel. Mạnh mẽ là bởi công cụ này nó có thể tổng hợp và truy xuất thông tin từ một bảng dữ liệu siêu nặng, và khó nhằn là vì công cụ này có vẻ khá phức tạp. Chúng ta hãy cùng nhau tìm hiểu pivot table là gì và lý do vì sao đây là một công cụ cực kỳ quan trọng khi làm việc trên Excel, đặc biệt nếu bạn muốn học Excel lĩnh vực kế toán, tài chính, hoặc các lĩnh vực kinh tế.

Pivot table là gì?

Pivot table là một bảng dữ liệu đã được tóm tắt, xuất ra dạng đồ thị, và dựa vào đó bạn sẽ lập được báo cáo và xu hướng dựa trên những thông tin đó. Nếu bảng tính của bạn có hàng tá các cột và hàng “dài ngoằn”, Pivot table thật sự là một công cụ hữu ích giúp bạn tổng hợp dễ dàng và so sánh với các dữ liệu khác một cách nhanh chóng hơn. Nói cách khác, Pivot table có thể hoạt động trên một “biển” dữ liệu vô tận. Nói một cách cụ thể hơn, công cụ này có thể nhóm các dữ liệu lại với nhau bằng nhiều phương pháp, nhờ đó bạn sẽ dễ dàng thực hiện tác vụ mong muốn.

Phần “Pivot” trong cụm Pivot table mang nghĩa rằng bạn có thể luân phiên hoặc quay (pivot) các dữ liệu trong bảng để có được những cách nhìn nhận và đánh giá khác nhau. Rõ ràng hơn nữa, bạn không cần phải thực hiện các phép tính cộng trừ, cũng không cần phải thay đổi dữ liệu khi bạn muốn làm đánh giá. Mà thay vào đó, chỉ đơn giản là sắp xếp lại dữ liệu, các thông tin hữu dụng sẽ được hiện ra.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 1

Cách dùng Pivot Table

Nếu bạn còn thấy rối rắm về cách Pivot Table hoạt động, đừng lo lắng. Đây sẽ là những hướng dẫn vô cùng đơn giản vì bạn sẽ được thấy lần lượt các bước thực hiện ngoài thực tế. Sau đây là 7 hướng dẫn chi tiết khi sử dụng Pivot Table cho mỗi mục đích khác nhau.

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

GIả sử, bạn đang có một bảng tính bao gồm tổng doanh thu theo tháng của 3 sản phẩm khác nhau: sản phẩm 1, sản phẩm 2, sản phẩm 3 – và bạn muốn chỉ ra được đâu là sản phẩm bán được nhiều tiền nhất. Chỉ cần tạo ra một bảng có chức năng tính doanh thu của sản phẩm 1 mỗi lần xuất hiện. Bạn cũng có thể làm điều tương tự với hai sản phẩm kia, cho đến khi bạn có được tổng số của mọi sản phẩm. Đơn giản phải không nào?

Bây giờ hãy cùng tưởng tượng, bảng dữ liệu của doanh thu hàng tháng có hàng trăm cột và hàng trăm dòng. Nếu bạn truy xuất bảng dữ liệu đó theo cách thủ công, có thể mất một khoảng thời gian dài. Nhưng khi sử dụng pivot table, bạn có thể tự động tập hợp lại tất cả các ô doanh thu cho sản phẩm 1, 2, 3 và tính toán tổng cho mỗi sản phẩm chỉ trong tích tắc.

2. Hiển thị doanh thu sản phẩm dưới dạng phần trăm trong tổng doanh thu

Pivot table có thể hiển thị tổng của mỗi cột hoặc hàng. Nhưng đó chưa phải là tất cả. Có thể nói, bạn nhập số liệu bán hàng theo quý cho ba sản phẩm khác nhau và biến các dữ liệu này trở thành một pivot table. Table này có thể tự động tính toán và hiển thị tổng của ba sản phẩm đó ở phía dưới cùng của mỗi cột – và thế là bạn đã tự động có thêm doanh thu bán hàng của mỗi quý. Nhưng nếu như bạn muốn biết doanh thu này chiếm bao nhiêu phần trăm trong tổng doanh thu hoặc so sánh doanh thu bán hàng theo quý với doanh thu bán hàng của cả công ty thì sao?

Với pivot table, bạn có thể định dạng mỗi cột và xuất ra phần trăm tổng doanh thu của 3 sản phẩm, thay vì chỉ là một cột tổng như bình thường. Ví dụ, nếu ba sản phẩm có doanh thu bán hàng tổng cộng 200,000 đô la và sản phẩm đầu tiên thu được 45,000 đô la, bạn có thể tạo một pivot table và chỉ ra được doanh thu của sản phẩm này chiếm 22.5% tổng doanh thu của cả công ty. Để hiển thị doanh thu bán hàng dưới dạng phần trăm của tổng doanh thu, chỉ cần click chuột phải vào ô tổng doanh thu và chọn “Show Values As” (Hiển thị giá trị dưới dạng) > “% of Grand Total” (Phần trăm của tổng).

3. Kết hợp hai dữ liệu giống nhau

Trong trường hợp này, giả sử bạn vừa thiết kế lại trang blog và cần cập nhật rất nhiều URLs. Và thật không may, ứng dụng báo cáo blog của bạn không hoạt động hiệu quả và dẫn đến tình trạng lượt view của mỗi bài đăng bị chia ra giữa hai đường link khác nhau. Vì vậy, bảng tính sẽ hiển thị hai thông số khác nhau ở của mỗi đường link bài đăng. Để lấy được chính xác dữ liệu, bạn cần tính tổng lượt view của mỗi bản trùng này.

Tính trạng rắc rối này lý giải vì sao chúng ta nên sử dụng pivot table. Thay vì tìm kiếm bằng tay và kết hợp tất cả số liệu từ những thông tin trùng lặp, bạn có thể tóm tắt dữ liệu (thông qua pivot table) bằng tên của bài blog và sau đó: các dữ liệu từ các ô trùng kia sẽ được tổng hợp tự động.

4. Tính đầu người cho từng ban ngành riêng biệt

Pivot table rất hữu dụng cho việc tính toán tự động – những thứ bạn không dễ dàng nhìn thấy trong một bảng biểu Excel thông thường. Một trong số đó là tính dãy hàng có nhiều điểm chung với nhau.

Ví dụ, nếu bạn có một danh sách nhân viên trong cùng một sheet, và bên cạnh tên nhân viên chính là tên các cơ quan ban ngành hoặc phòng ban tương ứng, bạn có thể tạo một pivot table từ dữ liệu này để hiển thị mỗi phòng ban và số lượng nhân viên của mỗi bên. Pivot table loại trừ những thao tác truy xuất bằng tay một cách hiệu quả (cách thông thường: xuất dữ liệu từ từng tên phòng ban và tính mỗi dòng bằng tay).

5. Thêm dữ liệu mặc định vào các ô trống

Không phải tập dữ liệu nào khi thêm vào Excel cũng sẽ được trải đều trong từng ô. Nếu bạn đang trong quá trình chờ để nhận dữ liệu mới trước khi nhập vào Excel, có lẽ sẽ xuất hiện rất nhiều ô trống gây rối mắt và cần giải thích thêm nếu bạn cần trình bày chúng. Đây là “ca” pivot table có thể ra tay.

Dễ dàng tùy chỉnh một pivot table để thêm các dữ liệu mặc định vào trong từng ô trống ví dụ như 0$ hoặc TBD (đang chờ thông tin). Đối với những bảng có dữ liệu lớn, gộp nhanh các ô này với nhau là một chức năng hữu dụng nếu nhiều người cùng xem cùng một bảng tính. Để định dạng tự động các ô trong trong pivot table, bấm chuột phải vào bảng và nhấp “PivotTable Options”. Một hộp thoại sẽ hiện ra, đánh dấu vào ô “Empty Cells As” và nhập dữ liệu bạn muốn hiện trong các ô không có dữ liệu.

Cách tạo Pivot table

  • 1. Nhập dữ liệu thành các hàng hoặc cột
  • 2. Phân loại dữ liệu theo từng thuộc tính cụ thể
  • 3. Đánh dấu các ô bạn muốn tạo pivot table.
  • 4. Kéo và thả một vùng để trở thành vùng “Row Labels
  • 5. Kéo và thả một vùng để thành vùng “Values”
  • 6. Điều chỉnh các phép tính của bạn.

Bây giờ bạn đã có thêm nhiều trải nghiệm về cách dùng pivot, tiếp theo hãy cùng nhau tìm hiểu những phương pháp để tạo pivot table trong thực tế nhé.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 2

1. Nhập dữ liệu thành các dãy hàng và cột

Mỗi pivot table trong Excel đều bắt đầu từ một bảng tính Excel cơ bản. Để tạo pivot table, chỉ cần nhập dữ liệu vào một vùng cột và hàng cụ thể. Sử dụng hàng trên cùng và cột ngoài cùng để phân từng loại dữ liệu.

Ví dụ, để tạo một bảng biểu hiển thị các lượt truy cập của trang blog, bạn sẽ cần một cột để liệt kê từng đường link, một cột của các tên bài post, một cột thể hiện lượt xem của mỗi bài, vv… (Chúng ta cũng sẽ sử dụng ví dụ này cho những bước hướng dẫn tiếp theo).

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 3

2. Sắp xếp dữ liệu bằng các thuộc tính cụ thể

Khi bạn muốn nhập dữ liệu vào bảng Excel, chắc chắn bạn sẽ muốn sắp xếp dữ liệu để có thể sử dụng chúng trong pivot table một cách dễ dàng.

Để sắp xếp dữ liệu, nhấp chuột vào tab “Data” trên thanh công cụ và chọn biểu tượng “Sort” ở phía dưới. Một hộp thoại sẽ hiện ra và bạn có thể lựa chọn để phân loại dữ liệu bằng cột hoặc hàng theo bất kỳ trình tự nào bạn muốn. Ví dụ, bạn muốn phân loại bảng tính theo “Views to Date” (Xem theo ngày), chọn tính năng “Views to Date” dưới phần “Column”, sau đó chọn kiểu phân loại bạn muốn (theo thứ tự từ lớn đến nhỏ hoặc ngược lại). Chọn “OK” ở phía góc trái của hộp thoại Sort, và bảng tính của bạn sẽ được sắp xếp theo số lượng lượt view của từng bài post.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 4

3. Đánh dấu ô để tạo PivotTable

Mỗi khi bạn nhập dữ liệu vào bảng làm việc của Excel và phân loại theo ý thích, đánh dấu các ô muốn tổng kết thành một pivot table. Nhấp “Insert” trên thanh công cụ, và chọn biểu tượng “PivotTable”. Bạn cũng có thể click vào bất cứ đâu trên bảng tính và chọn “PivotTable”, và nhập bằng tay các ô dữ liệu bạn muốn thêm vào PivotTable.

Một hộp thoại nữa sẽ xuất hiện bên cạnh, bạn có thể chọn xuất bảng pivot table này sang một trang sheet mới hoặc giữ tại trang hiện tại. Nếu bạn lưu tại một trang mới, bạn có thể tìm lại hoặc đặt chúng vào cuối trang làm việc. Khi đã chọn xong, click “OK”. Bạn cũng có thể highlight các ô, chọn “Recommended PivotTables” ngay bên phải biểu tượng PivotTable và mở một pivot table với các đề nghị đặt trước để thiết lập các cột với hàng.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 5


Lưu ý: Nếu bạn sử dụng các phiên bản Excel trước 2016 (phiên bản cũ hơn), “PivotTables” có thể sẽ ở trong tab “Tables” hoặc “Data” trên thanh công cụ. Trên Google Sheets, bạn có thể tạo pivot table ở tab “Data” trên thanh công cụ.

4. Kéo và thả vùng dữ liệu vào vùng “Row Labels”

Khi hoàn thành Bước 1, Excel sẽ tạo một bảng pivot table trống cho bạn. Bước tiếp theo sẽ là kéo và thả một vùng (có nhãn được đặt theo tên gọi của các cột trong bảng tính) vào vùng “Row Labels”. Điều này sẽ giúp bạn xác định được đâu là những yếu tố riêng biệt như tên bài đăng, tên sản phẩm,… Pivot table sẽ sắp xếp dữ liệu của bạn theo những yếu tố đó.

Ví dụ, bạn muốn phân loại một lượng lớn dữ liệu các bài đăng ở blog bằng tên. Để thực hiện, bạn chỉ cần nhấp chuột và kéo mục “Title” đến vùng “Row Labels”.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 6

Ghi chú: Pivot table của bạn có thể có giao diện khác nhau tùy thuộc vào từng phiên bản Excel bạn đang sử dụng. Tuy nhiên, các yếu tố cơ bản vẫn được giữ nguyên.

5. Kéo và thả một mục vào vùng “Values”

Mỗi khi bạn xác định được bạn sẽ làm gì với dữ liệu của mình, bước tiếp theo sẽ là thêm các giá trị bằng cách kéo một mục vào vùng “Values”. Nếu bạn muốn tổng hợp lượt view bài đăng theo tiêu đề, bạn chỉ cần đơn giản kéo mục “Views” vào vùng Values.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 7

6. Tùy chỉnh tính toá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ũng có thể dễ dàng chuyển thành những phép tính khác như trung bình, giá trị lớn nhất hoặc nhỏ nhất. Ở dòng máy Mac, bạn có thể thực hiện thao tác này bằng cách nhấp chuột vào chữ “i” nhỏ bên cạnh vùng “Values”, chọn yêu cầu bạn muốn và click “OK”. Mỗi khi chọn yêu cầu, bảng pivot table của bạn sẽ được cập nhật theo ngay sau đó. Nếu sử dụng máy tính bàn, bạn cần nhấp chuột vào hình tam giác nhỏ úp ngược bên cạnh vùng giá trị và chọn “Value Field Settings” để mở menu.

Hướng dẫn tạo Pivot Table khi báo cáo trong Excel 8

Tìm hiểu sâu hơn về PivotTable

Bây giờ bạn đã nắm được các bước cơ bản để tạo pivot table trong Excel, nhưng nếu muốn thực hiện các tác vụ phức tạp hơn, nhiêu đây vẫn chưa đủ.

Ví dụ, bạn nhận ra rằng dữ liệu không được sắp xếp theo cách mong muốn. Trong trường hợp đó, chức năng Sort của Excel có thể giúp được bạn. Hơn nữa, bạn cũng có thể kết hợp dữ liệu với một nguồn khác trong báo cáo, bạn có thể kết hợp với hàm VLOOKUP để lọc dữ liệu.

TRẢ LỜI

Please enter your comment!
Please enter your name here