Hướng dẫn cơ bản để sử dụng bảng Pivot trong excel

Rate this post

Bảng Pivot là gì?

Nhiều người trong chúng ta sử dụng Excel để làm việc với dữ liệu và ngày nay, rất nhiều người trong chúng ta làm việc với rất nhiều dữ liệu. Không có gì lạ khi thấy những người làm việc với cơ sở dữ liệu Excel chứa hàng trăm ngàn hàng thông tin – và không có lý khi nghĩ rằng phải có một cách dễ dàng để trích xuất thông tin có ý nghĩa từ các nguồn dữ liệu của chúng tôi. 

Tất nhiên, Excel có một số công cụ giúp bạn sắp xếp và điều hướng các bộ dữ liệu này. Chúng ta có thể sắp xếp, lọc và nhóm chúng hoặc thậm chí tạo tổng phụ – nhưng cha đẻ lớn của phân tích dữ liệu trong Excel chắc chắn là bảng xoay vòng. 

Các bảng Pivot là một cách tuyệt vời để nhanh chóng trích xuất dữ liệu từ và tóm tắt các tập dữ liệu lớn. Họ cho phép bạn biến điều này: 
thành cái này:

Trong bài viết này, chúng tôi sẽ chỉ cho bạn cách tạo bảng xoay chuyên gia chỉ bằng vài cú click chuột.

Đặt dữ liệu của bạn

Trước khi chúng tôi đi sâu vào và tạo bảng trụ đầu tiên, trước tiên chúng tôi muốn đảm bảo dữ liệu của chúng tôi phù hợp. Các quy tắc thông thường của bảng dữ liệu trong Excel được áp dụng, vì vậy:

  • Hàng trên cùng phải là một hàng tiêu đề
  • Dữ liệu trong mỗi cột phải nhất quán (tức là tất cả các ngày, tất cả các số)
  • Không nên có hàng hoặc cột trống hoàn toàn
  • Bất kỳ dữ liệu không liên quan nên được phân tách bằng một cột trống (tốt nhất trên một trang tính khác)

Ngoài ra, bạn có thể muốn nghĩ về cấu trúc chung hoặc bố cục của dữ liệu của bạn. Một bố cục điển hình mà chúng ta thấy rất thường trông giống như thế này: 

Mặc dù nó có vẻ tốt, nhưng thực tế đây là một cách tổ chức dữ liệu thô rất kém hiệu quả. Nếu, giả sử, bạn muốn xem tất cả các tổng số hàng tháng trên 20.000 bảng, bạn sẽ phải lọc riêng từng cột và thêm một cột mới mỗi tháng. Một cách tốt hơn để lưu trữ thông tin này sẽ như thế này:

Bố cục đơn giản trong ExcelTrong định dạng này, bạn có thể nhanh chóng lọc doanh số hơn 20.000 bảng chỉ trong một cột. Với ví dụ hàng đầu, thật dễ dàng để thấy doanh số hàng tháng cho mỗi khách hàng nhưng thật khó để thao túng nó hơn nữa. Ví dụ thấp hơn là khó đọc, ban đầu, nhưng dễ dàng hơn để trích xuất thông tin từ đó. Mặc dù bạn có thể ném cả hai ví dụ vào bảng xoay vòng, nhưng chỉ có tập hợp dưới cùng sẽ đặc biệt hữu ích.

Tạo Bảng Pivot

Bây giờ chúng ta đã hiểu, một cách rộng rãi, bảng xoay là gì và cách chúng ta nên cấu trúc dữ liệu của mình, chúng ta hãy xem cách tạo một bảng. 

Chọn một ô duy nhất trong bảng dữ liệu của bạn: 

Đầu cho tab Chèn và nhấp vào Bảng Pivot Pivot Bảng: 

Bảng PivotTại thời điểm này, bạn sẽ thấy hộp thoại Chèn Chèn Bảng Pivot Pivot: 

Hộp thoại có một số tùy chọn: 

Trong trường hợp này, dữ liệu là trong Excel và chúng tôi muốn bảng xoay vòng trên một trang tính mới, vì vậy chúng tôi chỉ cần nhấp vào OK. 
Tại thời điểm này, một bảng trụ trống phải ở bên trái của trang tính. 

Bảng trụ trốngMiễn là chúng ta có một ô trong khu vực bảng trụ được chọn, chúng ta cũng sẽ thấy Danh sách trường của bảng trụ và một vài tab nhạy ngữ cảnh trên Dải băng; Phân tích và thiết kế (Trong Excel 2007 và 2010, các tab mới được gọi là Tùy chọn và Thiết kế).

Danh sách trường của danh sách xếp hạng liệt kê tất cả các tên trường từ hàng tiêu đề của bảng dữ liệu gốc. Bốn hộp ở phía dưới là các khu vực trên Bảng Pivot nơi dữ liệu có thể được hiển thị. 

Nếu chúng ta có nhiều trường, chúng ta có thể sử dụng hộp Tìm kiếm ở đầu Danh sách trường để nhanh chóng tìm thấy một trường. 

Nút Tùy chọn cho phép chúng tôi thay đổi bố cục của Danh sách trường và thay đổi thứ tự sắp xếp của các trường.

Nếu chúng ta có một tập dữ liệu lớn hoặc có lẽ là một máy chậm hơn, các thay đổi đối với bảng xoay có thể mất vài giây để xuất hiện. Tuy nhiên, nếu chúng ta đang thực hiện một số thay đổi đối với bảng, điều này có thể trở nên khá khó chịu. Trong những trường hợp này, đánh dấu vào tùy chọn Cập nhật bố cục trì hoãn ở cuối danh sách trường sẽ cho phép chúng tôi thực hiện một số thay đổi đối với bảng trụ mà không kích hoạt cập nhật. Khi các thay đổi hoàn tất, chỉ cần nhấp vào nút cập nhật và tất cả các thay đổi được áp dụng cùng một lúc. 

Một điều đáng nói nữa là các bảng trụ không có liên kết trực tiếp đến dữ liệu nguồn. Khi bảng được tạo, dữ liệu nguồn được sao chép vào Bộ đệm Pivot và tất cả các thao tác bảng xoay vòng được thực hiện trên dữ liệu được lưu trong bộ nhớ cache – các thay đổi đối với dữ liệu nguồn gốc sẽ không được phản ánh trong bảng xoay vòng.

Nếu nguồn dữ liệu của bạn đã thay đổi và bạn cần hiển thị dữ liệu mới, hãy nhấp vào nút Làm mới trên tab Phân tích. Điều này sẽ buộc Excel xây dựng lại Cache Cache và dữ liệu mới sẽ được hiển thị.

Thêm các trường vào bảng Pivot

Bây giờ chúng ta đã tạo bảng xoay vòng của mình, đã đến lúc bắt đầu thêm dữ liệu vào nó. Nếu bạn muốn xem tổng doanh số cho từng quốc gia, đánh dấu vào trường Quốc gia hoặc kéo nó vào phần Hàng trong danh sách trường bảng trụ. 

Trường quốc gia trong ExcelMột danh sách các quốc gia duy nhất được đặt vào cột bên trái của bảng trụ. Khi chúng tôi đánh dấu một trường, bảng sẽ xác định loại dữ liệu mà nó chứa; giá trị văn bản và ngày sẽ được thả vào phần Hàng, trong khi giá trị số sẽ được thả vào phần Giá trị và được tính tổng. Việc đánh dấu vào trường Tổng số các điểm sẽ làm cho nó bị rơi vào phần Giá trị và được tóm tắt. 

Trường giá trị trong ExcelChúng tôi có thể nhanh chóng thay đổi dữ liệu bằng cách đánh dấu / bỏ chọn các trường hoặc kéo các trường vào / tắt bảng xoay vòng. 
Nếu chúng ta chọn nhiều trường dữ liệu, bảng xoay vòng sẽ tự động nhóm dữ liệu.

Bỏ chọn các trường trong ExcelTrong trường hợp này, chúng tôi đã chọn cả hai trường Danh mục và SalesPerson. Bảng xoay vòng hiển thị tổng số cho từng danh mục, sau đó hiển thị bảng phân tích cho từng nhân viên bán hàng trong mỗi danh mục. 

Chúng ta có thể dễ dàng thay đổi màn hình bằng cách kéo các trường trong phần Hàng để thay đổi vị trí của chúng: 

Kéo các trường trong ExcelKhi dữ liệu bảng xoay được nhóm theo kiểu này, mỗi tiêu đề nhóm sẽ hiển thị một nút trừ nhỏ. Nhấp vào nút này cho phép chúng tôi thu gọn các chi tiết cho nhóm đó.

Thu gọn chi tiết trong ExcelTheo cách tương tự, nhấp vào nút + sẽ mở rộng chi tiết cho nhóm. Để mở rộng hoặc thu gọn toàn bộ một trường, chọn một trong các mục nhập của trường chính và sau đó nhấp vào nút Mở rộng trường / Thu hẹp trường trên tab Phân tích.

Mở rộng trường trong ExcelThêm nhiều trường vào phần Hàng có thể khiến bảng xoay vòng rất dài, nhưng chúng ta cũng có thể hiển thị dữ liệu dọc theo đầu bảng, trong phần Cột. 

Thay vì đánh dấu một trường, hãy kéo nó từ danh sách trường vào phần Cột hoặc, kéo một trường từ Hàng vào Cột: 

Phần cột trong ExcelTrong ví dụ này, bảng xoay vòng sẽ được định hướng lại để hiển thị trường SalesPerson ở bên trái và Danh mục chạy dọc trên cùng. 

Ví dụ bảng PivotNhư bạn có thể thấy, một bảng trụ cho phép các tập dữ liệu lớn được trình bày và tóm tắt rất nhanh, chỉ với vài cú nhấp chuột. Bây giờ chúng ta đã có những điều cơ bản trên đường đi, chúng ta hãy xem xét một số tính năng bảng trụ khác.

Lọc bảng Pivot

Thay vì hiển thị dữ liệu cho tất cả nhân viên bán hàng, chúng tôi đã quyết định rằng chúng tôi chỉ muốn xem dữ liệu cho lựa chọn của họ. Để thực hiện việc này, nhấp vào nút Bộ lọc ở bên phải của phần Nhãn Hàng: 

Bộ lọc trong ExcelĐiều này sẽ thả xuống danh sách Bộ lọc, sẽ quen thuộc nếu bạn có Bộ lọc tự động trên trang tính. Ở đây, chỉ cần chọn các mục yêu cầu và nhấp vào OK. 

Các mục không được đánh dấu bây giờ sẽ bị xóa khỏi bảng trụ và hàng Grand Total được cập nhật để hiển thị tổng số chỉ cho các mục hiển thị. 

Để xóa bộ lọc, chỉ cần nhấp vào nút Bộ lọc, sau đó chọn tùy chọn Xóa bộ lọc từ hoàng SalesPerson. 

Xóa bộ lọc trong ExcelDanh sách Bộ lọc cũng chứa một vài phương thức lọc bổ sung, chẳng hạn như Bộ lọc nhãn và Bộ lọc giá trị.

Bộ lọc nhãn có thể được sử dụng để lọc danh sách dài các mục nhập nhãn hiệu quả hơn. Chẳng hạn, nếu chúng ta chỉ muốn nhìn thấy nhân viên bán hàng có tên bắt đầu bằng chữ D, chúng ta có thể sử dụng Bộ lọc Bắt đầu với Bộ lọc Nhãn. 

Tùy chọn bộ lọc nhãn trong ExcelMặt khác, các bộ lọc giá trị cho phép chúng tôi lọc các mục trong bảng trụ dựa trên các giá trị được tính cho trường cụ thể đó. 

Ví dụ: nếu chúng tôi chỉ muốn hiển thị những nhân viên bán hàng đã bán được hơn 2.500.000 bảng Anh, chúng tôi có thể sử dụng Bộ lọc giá trị của Greater Greater Than. 

Các bộ lọc tương tự cũng có sẵn cho các trường Cột. Để lọc mức toàn cầu hơn, chúng tôi sử dụng phần Bộ lọc của Danh sách trường: 

Phần bộ lọc trong ExcelTrong ví dụ trên, trường Khách hàng đã được kéo vào phần Bộ lọc. Điều này thêm trường quốc gia vào đầu Bảng Pivot, hoạt động trên toàn bộ báo cáo.

Lĩnh vực khách hàng trong ExcelSử dụng bộ lọc cấp Báo cáo, chúng tôi có thể nhanh chóng chọn một Khách hàng hoặc nhóm Khách hàng cụ thể và dữ liệu trên bảng xoay vòng sẽ thay đổi. 

Chọn một khách hàng cụ thể trong ExcelChỉ cần nhớ chuyển tất cả các bộ lọc này trở lại để hiển thị tất cả dữ liệu khi bạn đã hoàn thành với chúng. Các bộ lọc được duy trì và được áp dụng lại nếu một trường đã bị xóa khỏi bảng và được thêm lại.

Cắt dữ liệu và ngày

Giống như các bộ lọc, Bộ cắt cho phép chúng tôi nhanh chóng tập trung vào một tập hợp con cụ thể của dữ liệu, trong khi chức năng Timelines cho phép chúng tôi cắt các trường ngày hiệu quả hơn. Máy thái được giới thiệu trong phiên bản 2010 của Excel; nhưng Timelines chỉ áp dụng cho Excel 2013 trở đi. 

Để sử dụng chức năng này, hãy chọn tab Phân tích (hoặc Tùy chọn, nếu bạn đang sử dụng Excel 2010), sau đó bấm vào nút Chèn Trình cắt: 

Chèn các lát cắt trong ExcelChọn trường (hoặc các trường) để cắt dữ liệu của bạn bằng cách bấm OK: 

Dữ liệu lát trong ExcelĐể lọc bảng xoay vòng, tất cả những gì chúng ta phải làm là nhấp vào nút máy thái. 

Nút cắt trong ExcelNút cắtGiữ phím Ctrl để chọn nhiều mục, sau đó nhấp vào nút Xoá ở đầu trình cắt để loại bỏ các bộ lọc.

Máy thái có tab nhạy ngữ cảnh riêng trên ruy băng. Bằng cách sử dụng tab Tùy chọn Bộ cắt, chúng ta có thể thay đổi giao diện của nó và cũng kiểm soát số lượng cột. 

Tab tùy chọn máy cắt trong ExcelĐặt số cột của Bộ cắt thành 7 sẽ cho phép chúng tôi hiển thị tất cả các quốc gia mà không cần phải cuộn lên và xuống một danh sách dài.

Nếu bạn muốn xóa một bộ cắt, chỉ cần chọn đường viền của bộ cắt và nhấn xóa trên bàn phím. 

Các mốc thời gian (chỉ được sử dụng từ Excel 2013 trở đi) cho phép chúng tôi cắt các trường ngày. Chọn bảng trụ của bạn và sau đó, từ tab Phân tích, bấm Chèn Dòng thời gian. Chọn trường ngày thích hợp và nhấp vào OK.

Tính năng dòng thời gian trong ExcelDòng thời gian cho phép chúng tôi lọc bảng xoay vòng theo thời gian ngày. Nhấp vào một tháng hoặc kéo qua nhiều tháng và Bảng Pivot sẽ được lọc. Chúng tôi có thể điều chỉnh các khoảng thời gian được hiển thị bằng cách nhấp vào nút dấu chấm ở đầu Dòng thời gian. 

Cài đặt dòng thời gian trong ExcelGiống như Trình cắt, Dòng thời gian cũng có tab nhạy ngữ cảnh. Sử dụng tab Tùy chọn Timeline trên tab, chúng tôi có quyền kiểm soát việc hiển thị các yếu tố khác nhau trên Dòng thời gian: 

Tab tùy chọn dòng thời gianCả hai Máy cắt và Timelines đều cho phép chúng tôi nhanh chóng lọc bảng xoay vòng để xem chi tiết dữ liệu mà chúng tôi quan tâm. , chúng là những bổ sung hoàn hảo cho Bảng điều khiển dữ liệu, cho phép người dùng không có kinh nghiệm trước đó sửa đổi báo cáo bảng trụ.

Thay đổi phương pháp tính toán

Theo mặc định, khi chúng tôi thêm một trường vào khu vực Giá trị của bảng xoay vòng, Excel sẽ tính tổng các giá trị số và đếm các giá trị văn bản – nhưng điều này có thể không phải luôn luôn là những gì chúng tôi muốn. Trong ví dụ sau, chúng tôi đã thêm trường OrderID vào phần giá trị và, vì OrderID là số, bảng xoay vòng Tổng hợp nó. 

Tổng số bảng tổng hợpTuy nhiên, tổng của tất cả các OrderID không được sử dụng – điều chúng ta thực sự cần là số lượng OrderID. 

Để làm điều này, chúng ta cần thay đổi phương pháp tính toán. Nhấp vào mũi tên thả xuống bên cạnh tên trường trong phần Giá trị của danh sách trường và chọn, Cài đặt trường giá trị. 

Hộp thoại Cài đặt trường cho phép chúng tôi thay đổi tên hiển thị của trường, Định dạng số và chức năng được áp dụng cho trường.

Hộp thoại cài đặt trườngTrong trường hợp này, chọn Đếm và bấm OK. Bảng Pivot hiện được cập nhật để hiển thị số lượng đơn đặt hàng trong mỗi danh mục thay vì tổng. 

Từ Excel 2010 trở đi, các phương thức tính toán phổ biến có thể được thay đổi bằng cách nhấp chuột phải vào bảng xoay vòng và đi tới Tóm tắt giá trị của By By. 

Trong ví dụ tiếp theo, bảng xoay vòng tổng hợp chính xác trường Trường Total, và chúng ta có thể dễ dàng thay đổi nó để hiển thị mức trung bình nếu cần. Tuy nhiên, lần này, chúng ta cần xem Tổng và Trung bình cùng nhau. 

Tổng và trung bình trong ExcelChúng tôi đã đánh dấu Tổng trường, nhưng chúng tôi vẫn có thể kéo nó, lần thứ hai, vào phần Giá trị: 
Phần giá trịBây giờ chúng tôi có thể đi tới Cài đặt trường cho Tổng của 2 và thay đổi thành Trung bình:

Trung bình trong ExcelKhi chúng tôi có nhiều mục trong phần Giá trị, chúng tôi cũng cần xem xét cách các mục này sẽ được hiển thị trong bảng xoay vòng. Trong ví dụ trên, bạn có thể thấy rằng các cột Tổng và Trung bình đang được hiển thị cho mỗi danh mục. 

Nhiều giá trị được hiển thị trong các cột theo mặc định và phần cột hiện chứa mục Giá trị. 

Giá trị phần cộtHoán đổi trường được hiển thị và mục Giá trị xung quanh sẽ hiển thị tổng cho tất cả các danh mục, theo sau là mức trung bình của chúng. 

Hoán đổi trường hiển thị trong ExcelTổng trong ExcelNgoài ra, chúng tôi có thể kéo mục Giá trị từ phần Cột vào phần Hàng: 

Kéo mục giá trị trong ExcelLần này, chúng tôi có hiển thị giá trị được tính dưới mỗi mục trong tiêu đề hàng. Không có đúng hay sai với điều này, cách bạn hiển thị các giá trị của mình là vấn đề sở thích cá nhân.

Phân nhóm dữ liệu

Nhóm dữ liệu tự động trong ExcelKhi chúng tôi thêm nhiều trường vào các phần Hàng hoặc Cột của bảng xoay vòng, Excel sẽ tự động nhóm dữ liệu. 

Trong ví dụ ở bên trái, chúng ta có thể thấy rằng cả hai trường SalesPerson và Shipper đã được thêm vào phần hàng và Excel hiển thị tổng doanh số cho mỗi Người bán hàng và sau đó chia nhỏ hoặc nhóm cho mỗi Người gửi. 

Điều này xảy ra theo mặc định cho nhiều trường – nhưng nếu chúng ta muốn nhóm dữ liệu khác thì sao? Điều này là phổ biến khi chúng ta đang xem dữ liệu ngày hoặc số trong phần Hàng hoặc Cột. 
Trường OrderDate trong ExcelTrong ví dụ bên phải, chúng tôi đã thêm trường OrderDate vào phần Hàng và Excel hiển thị mỗi ngày riêng lẻ – trong trường hợp này là hơn 3800 ngày!

Tuy nhiên, không chắc là chúng tôi muốn xem nhiều ngày độc đáo này – chúng tôi sẽ có nhiều khả năng muốn xem dữ liệu được sắp xếp theo năm, quý hoặc tháng. 

Đây là nơi nhóm đến. 

Để nhóm các ngày trong ví dụ, chúng ta có thể chọn một trong các ngày, sau đó, từ tab Phân tích, chọn Nhóm Nhóm Trường. 
Tùy chọn trường nhóm trong ExcelNhóm trong ExcelHộp thoại Nhóm đã xác định rằng chúng tôi đang xử lý một trường ngày và đưa ra cho chúng tôi các tùy chọn nhóm ngày có liên quan. Vì vậy, bây giờ bạn có thể chọn các nhóm thích hợp cho dữ liệu của mình tại đây, chúng tôi đã nhóm dữ liệu theo năm và quý: 

Năm và quýTất nhiên, chúng tôi luôn có thể thay đổi suy nghĩ của mình bằng cách nhấp vào Ung Ungroup trên tab Phân tích hoặc nhấp lại vào Nhóm Nhóm cách dữ liệu được hiển thị.

Người dùng Excel 2016 cũng sẽ thấy rằng các trường ngày được tự động nhóm theo năm, quý và tháng. 

Các trường số có thể được nhóm theo một cách tương tự. Trong ví dụ bên dưới, trường OrderID đã được thêm vào phần Hàng, nhưng chúng ta cần nhóm các đơn hàng trong 1000 giây. 

Ví dụ nhóm ExcelMột lần nữa, chọn trường và sau đó nhấp vào Nhóm Nhóm Field trên tab Phân tích. 

Tùy chọn trường nhómExcel cũng cho phép bạn đặt điểm bắt đầu và điểm kết thúc để nhóm – trong ví dụ này, chúng tôi sẽ thay đổi các điểm này thành 10.000 và 18.000 để giữ các nhóm trong các phần hợp lý. Bạn cũng có thể đặt khoảng thời gian nhóm, 1000 trong trường hợp này. Khi bạn bấm OK, việc nhóm sẽ hoàn tất.
Phân nhóm điểm bắt đầu và điểm kết thúc

Định dạng và tóm tắt

Trong ví dụ bên dưới, chúng tôi đang hiển thị tổng doanh số cho từng danh mục sản phẩm, được sắp xếp bởi Nhân viên bán hàng và Người gửi hàng: 

Tổng doanh số cho các sản phẩm khác nhauMặc dù trường Tổng trong dữ liệu nguồn được định dạng dưới dạng tiền tệ, khi được đưa vào bảng xoay vòng, nó được phân loại như một con số Chúng ta cần thay đổi định dạng của trường, để nó hiển thị dưới dạng tiền tệ. 

Để thực hiện việc này, nhấp chuột phải vào một trong các giá trị trên bảng xoay vòng và chọn Định dạng Số Số Số: 
Tùy chọn định dạng số trong ExcelLưu ý rằng chúng tôi không chọn Định dạng Di động Định dạng ở đây. Mục đích của chúng tôi là định dạng trường Tổng, không phải các ô. Bạn cũng có thể truy cập vào Định dạng số Số vụn thông qua hộp thoại cài đặt trường: 

Định dạng số hộp thoại cài đặt trườngHộp thoại định dạng số tiêu chuẩn sẽ xuất hiện, trong đó chúng ta có thể đặt định dạng bắt buộc, (trong trường hợp này, tiền tệ là trường hợp này) và bấm OK.

Bởi vì chúng tôi đang định dạng trường chứ không phải các ô, tất cả các giá trị được định dạng. 
Các tùy chọn định dạng khác có thể được tìm thấy trên tab Thiết kế trực tuyến của Công cụ bảng Pivot. 
Tab thiết kế trong ExcelBằng cách sử dụng tab Thiết kế của Cảnh sát, chúng tôi có thể nhanh chóng thay đổi kiểu tổng thể của Bảng Pivot bằng cách áp dụng Kiểu Bảng Pivot. Chúng tôi cũng có thể kiểm soát việc hiển thị tổng phụ và tổng lớn, và, bằng cách sử dụng Bố cục Báo cáo Bố cục, thay đổi giao diện chung của bảng trụ của chúng tôi. 

Bảng Pivot trong bố cục nhỏ gọn (là tùy chọn mặc định): 

Bảng Pivot bố trí nhỏ gọnBảng Pivot trong bố cục phác thảo (điều này đặt nhiều trường vào các cột riêng của chúng):
Bố trí phác thảo bảng Pivot

Bắt một chút Flashier

Cũng như hiển thị dữ liệu tóm tắt thô, các bảng trụ cung cấp một số tùy chọn khác về cách dữ liệu giá trị được biểu diễn. Trong ví dụ dưới đây, chúng ta có thể thấy tổng doanh số cho mỗi nhân viên bán hàng cho mỗi năm. Tuy nhiên, thay vì xem dữ liệu thô, chúng tôi muốn xem dữ liệu dưới dạng tỷ lệ phần trăm: 

Số lượng tổng doanh số trong ExcelĐể thực hiện việc này, hãy chọn một trong các giá trị trong bảng xoay vòng, sau đó nhấp vào Cài đặt trường Field Cài đặt trên Tab Phân tích. Khi hộp thoại Cài đặt trường giá trị của hộp thoại được mở, hãy nhấp vào tab Hiển thị giá trị dưới dạng trực tuyến. 
Hiển thị giá trị dưới dạng tùy chọn trong ExcelSử dụng trình đơn thả xuống bên dưới các giá trị Hiển thị là giá trị, tìm% của Tổng số cột, sau đó bấm OK. 

Bảng xoay vòng hiện hiển thị tổng doanh số cho mỗi nhân viên bán hàng theo tỷ lệ phần trăm cho mỗi năm.

Bây giờ chúng tôi muốn tìm thứ hạng tương đối của mỗi nhân viên bán hàng cho mỗi năm. Để thực hiện việc này, chúng tôi sẽ chuyển đến hộp thoại Cài đặt trường và thay đổi các giá trị hiển thị của As Như thành Xếp hạng lớn nhất thành Nhỏ nhất. 

Xếp hạng lớn nhất đến nhỏ nhấtCó một số cách khác để hiển thị dữ liệu bảng trụ của bạn và rất đáng để thử nghiệm để tìm ra cách nào hữu ích nhất cho bạn. Hãy nhớ rằng mỗi phiên bản Excel giới thiệu các cách hiển thị dữ liệu mới của bạn, do đó, không phải tất cả các giá trị Hiển thị của các bộ lọc vì các bộ lọc có thể có sẵn cho bạn. 

Để đặt lại dữ liệu về màn hình gốc, hãy đặt Hiển thị các giá trị thành Không có tính toán.

Một công cụ bảng trụ hữu ích khác là tùy chọn Khoan xuống. Khoan xuống cho phép bạn dễ dàng trích xuất một tập hợp dữ liệu phụ từ nguồn dữ liệu ban đầu của bạn. Chẳng hạn, trong ví dụ trên, chúng ta có thể thấy rằng người bán hàng được đặt cuối cùng trong năm 2016 là Andrew Fuller. Chúng tôi cần xem lại dữ liệu về doanh số của Andrew trong năm 2016. 

Lướt con trỏ qua giá trị dữ liệu cho Andrew Fuller vào năm 2016 và nhấp đúp chuột. 

Bảng xoay vòng sẽ trích xuất tất cả dữ liệu cung cấp vào giá trị nhấp đúp và hiển thị trên một bảng riêng biệt. Tại đây chúng ta có thể xem dữ liệu về doanh số của Andrew Fuller năm 2016.Ví dụ bảng Excel

Một bước nữa: Power Pivot

Một trong những hạn chế của các bảng trụ là chúng rút dữ liệu từ một nguồn duy nhất. Điều này có thể là từ một cơ sở dữ liệu bên ngoài hoặc từ dữ liệu bảng tính. Tuy nhiên, các hệ thống dữ liệu phức tạp thường lưu trữ dữ liệu trên nhiều bảng và các bảng trụ chỉ có thể truy cập một trong số chúng. 

Trước đây, giải pháp cho vấn đề này là tạo một truy vấn trên cơ sở dữ liệu kết hợp dữ liệu cần thiết vào một nguồn cho bảng xoay vòng. Bây giờ chúng ta có thể đạt được điều tương tự bằng Power Pivot. 

Power Pivot được giới thiệu như một bổ trợ cho Excel 2010, nhưng hiện đã được tích hợp vào Excel 2013 và 2016, cho phép chúng tôi xoay vòng nhiều nguồn dữ liệu như thể chúng là một.

Trong ví dụ dưới đây, dữ liệu bán hàng được trải rộng trên 4 bảng tính riêng biệt và chúng tôi cần kết hợp dữ liệu trong một bảng trụ. Dữ liệu trên mỗi tờ đã được xác định là bảng dữ liệu. 

Bảng dữ liệu trong ExcelTrước tiên, chúng tôi sẽ tạo bảng xoay vòng dựa trên trang tính của Khách hàng, nhưng biết rằng chúng tôi muốn sử dụng dữ liệu từ các trang tính khác, trong hộp thoại Tạo bảng Pivot Pivot, đánh dấu vào Thêm vào Mô hình dữ liệu. 

Tạo bảng trụChúng ta có thể xem và thêm các trường từ bảng Khách hàng như bình thường. Trong ví dụ này, chúng tôi đã thêm trường Quốc gia vào Phần Hàng: Hàng trường quốc giaBây giờ chúng tôi muốn hiển thị ngày đặt hàng từ Bảng Đơn hàng. Trong Danh sách trường bảng Pivot, nhấp vào tùy chọn All All All ở trên cùng. Danh sách Trường hiển thị các bảng khác có trong sổ làm việc:
Các trường bảng PivotBây giờ chúng ta có thể mở rộng ra bảng Đơn đặt hàng trên mạng và thêm OrderDate vào Phần Cột. 

Chúng ta hãy tiến thêm một bước này và thêm Tổng doanh số từ bảng LineItems: 

Bảng LineItemsTại thời điểm này, chúng ta có thể thấy có gì đó không đúng. Có thể chúng tôi đã bán chính xác £ 1,449.367.31of cho mỗi quốc gia mỗi năm, nhưng nhiều khả năng là nó sai. Trong trường hợp này, Excel không hiểu dữ liệu trên 3 trang tính liên quan với nhau như thế nào. Và nó cho thấy điều này trong Danh sách trường: 
Danh sách trường ExcelExcel đã xác định rằng các mối quan hệ được yêu cầu giữa các trang tính để bảng xoay vòng hoạt động chính xác. Chúng tôi có thể thực hiện việc này bằng cách tạo thủ công các mối quan hệ hoặc nhấp vào Tự động phát hiện và xem liệu Excel có thể xác định chúng cho chúng tôi không. Trong trường hợp này, nhấp vào Tự động phát hiện:

Excel tự động phát hiệnExcel đã xác định mối quan hệ giữa các bảng Khách hàng, Đơn hàng và LineItems và bảng xoay vòng sẽ hiển thị kết quả chính xác.

Kết quả bảng Pivot đúngNếu Excel không làm được điều này, bạn sẽ phải làm thủ công. Chúng ta có thể thấy các mối quan hệ bằng cách nhấp vào Mối quan hệ của mối quan hệ trên thẻ Phân tích:

Quan hệ phân tích tabTrong ví dụ này, chúng ta có một mối quan hệ khác để thực hiện và có thể được thực hiện bằng cách nhấp vào nút Mới Mới trong cửa sổ Mối quan hệ.

Cửa sổ quan hệ trong ExcelTrong cửa sổ Tạo mối quan hệ, bạn chọn hai bảng có liên quan và các trường sẽ tham gia chúng. Tất nhiên, điều này phụ thuộc vào việc bạn hiểu về cách các bảng có liên quan – trong trường hợp này, mỗi hàng dữ liệu trong LineItems có số lượng sản phẩm được đặt hàng. Chính điều này được liên kết với số lượng sản phẩm trong bảng Sản phẩm.

Khi bạn đã hoàn tất, nhấp OK và mối quan hệ được tạo. Sau đó, bạn có thể đóng hộp thoại Mối quan hệ và bảng mới liên quan có thể được sử dụng trong bảng xoay vòng. 

Power Pivot là một công cụ cực kỳ mạnh mẽ và chúng tôi vừa xem xét phương pháp dễ dàng kết nối nhiều nguồn dữ liệu với mô hình dữ liệu Power Pivot. Nếu bạn nghĩ rằng nó có thể hữu ích cho bạn, có rất nhiều điều để khám phá. 

Chúng tôi hy vọng bạn thích hướng dẫn này về các bảng trụ và cảm thấy được truyền cảm hứng để khám phá bằng cách sử dụng dữ liệu của riêng bạn.

Nguồn: Sưu tầm

Scroll to Top