2. PIVOTTABLE

Pivot
Table là 1 trong công cụ tuyệt vời nhất MS Excel hỗ trợ dùng để phân tích dữ liệu theo rất nhiều góc độ, nhiều yêu cầu khác nhau từ một danh sách hay là một bảng. Từ khối tài liệu khổng lồ lúc đầu thì Pivot
Table rất có thể giúp các bạn tổng hợp tài liệu theo nhóm, thu gọn tài liệu theo yêu ước và mục đích sử dụng của người tiêu dùng một giải pháp nhanh chóng.

Bạn đang xem: Cách sử dụng pivot table nâng cao

Để sử dụng xuất sắc Pivot Table cần nắm rõ cấu trúc, nằm trong tính của Pivot Table. Bọn họ phân tích lấy ví dụ như sau nhằm hiểu rộng về Pivot Table

Ví dụ: cho bảng dự liệu sau:

*

Bảng dữ liệu 1

Yêu cầu: chế tạo bảng Thống Kê tất cả dạng sau:

*

Bảng những thống kê 1

Phân Tích Bảng Thông Kê 1:

– Bảng Thống Kê có 3 vùng color đỏ, đá quý , xanh

+ Màu đỏ: biểu hiện cột Mã SP từ bảng tài liệu 1, rất nhiều 1 Mã SP( ví dụ: CH-01 ) hiển thị do vậy gọi được coi là dòng ( ROWS )

+ Màu xanh: biểu thị cột xuất xứ từ bảng dữ liệu 1, phần đông 1 Xuất Xứ( ví dụ: Anh ) hiển thị bởi vậy gọi là Cột ( COLUMNS )

+ Màu vàng: miêu tả cột Tổng chi phí từ bảng dữ liệu 1, số đông 1 ô vào vùng màu kim cương Bảng Thống Kê 1 là giao của loại Mã SP(ROWS) và cột Xuất Xứ(COLUMNS) biểu hiện tổng giá thành theo Mã SP và nguồn gốc đây được call là Chỉ số tổng hợp(VALUES)

*

Kết Luận:

Khi sử dụng Pivot Table nhằm thống kê về cơ bạn dạng ít tốt nhất 2 vào 3 trực thuộc tính sau:

+ ROWS: Tổng hợp thông tin theo dòng

+ COLUMNS: Tổng hợp thông tin theo cột

+ VALUES: Chỉ số tổng hợp, cần chọn hàm tính toán(Sum, Count)

Ngoài ra còn nằm trong tính Pivot Table: FILTERS dùng tùy chỉnh Thuộc tính lọc để xem bảng thống kê theo một hoặc vài nhiều loại mẫu tin

*

Hình hình ảnh Layout Pivot Table

A. Bí quyết tạo Pivot Table ( Tổng vừa lòng dữ liệu từ là 1 bảng / sheet)

B1: Đặt loài chuột vào ô ngẫu nhiên của vùng tài liệu cần tổng hợp

B2: INSERT / PIVOT TABLE / XUẤT HIỆN HỘP THOẠI SAU

+ tại SELECT A TABLE OR RANGE: chọn bảng / vùng chứa tài liệu cần tổng hợp

+ tại USE AN EXTERNAL DATA SOURCE: lựa chọn nguồn từ phía bên ngoài khác tập tin hiện nay tại

+ trên NEW WORKSHEET: sinh sản 1 sheet bắt đầu thể hiện tại Pivot Table

+ tại EXISTING WORDKSHEET: Pivot Table được tạo thành tại vị trí bé trỏ bên trên sheet hiện tại hành

– Ở đây thống nhất sử dụng mặc định / OK / lộ diện sheet new chứa Vỏ Pivot Table y hệt như “Hình hình ảnh Layout Pivot Table”

B3: theo thứ tự kéo thả những cột ở trong tính vào Vỏ Pivot Table như : ROWS, COLUMNS, VALUES, FILTERS phù hợp

B4: Định dạng hiển thị mang lại Pivot Table: đặt loài chuột vào vùng Pivot Table / trên thanh Menu mở ra PIVOTTABLE TOOLS / chọn Tab ANALYZE hoặc thiết kế để tùy chỉnh thiết lập định dạng hiển thị như hy vọng muốn.

B. Giải pháp tạo Pivot Table ( Tổng hợp dữ liệu từ rất nhiều bảng )

Khi thuộc lúc có khá nhiều bảng tài liệu hoặc những sheet khác biệt muốn chế tạo một Pivot
Table, bọn họ giải quyết vấn đề đó thế nào? Nếu sử dụng MS Excel 2013, thì chúng ta có một phương thức được tổ chức phù hợp để tiến hành việc này, đó được gọi là mô hình Quan Hệ dữ liệu và nó sử dụng dữ liệu quan hệ theo mô hình cơ sở tài liệu làm việc.

Ví dụ:

Cho 3 bảng dữ liệu gồm: Khach
Hang, Hoa
Don, Chi
Tiet
HD, trong số ấy bảng Khach
Hang tình dục với bảng Hoa
Don qua Ma
KH, bảng Hoa
Don tình dục với bảng Chi
Tiet
HD qua So
HD


*

Khach
Hang


*

Hoa
Don


*

Chi
Tiet
HD


Yêu Cầu: Tạo bảng thống kê lại theo mẫu mã sau(Thống kê các giao dịch của từng khách hàng và tình trạng của từng solo hàng.

*

Để làm cho được Ví dụ bằng Pivot Table triển khai từng cách sau:

B1: chế tạo Bảng động mang đến 3 bảng Khach
Hang, Hoa
Don, Chi
Tiet
HD

+ Đặt Trỏ vào vùng yêu cầu tạo bảng động

+ Vào INSERT / TABLE / HỘP THOẠI

*

+ tại WHERE IS THE DATA FOR YOUR TABLE: chọn vùng phải tạo bảng động

+ tại MY TABLE HAS HEADERS: Chọn nếu còn muốn sử dụng thương hiệu cột bao gồm sẵn, còn nếu không chọn Table tự chế tác tên cột

=> OK

+ triển khai lại để tạo nên đủ các bảng ý muốn muốn

B2: Đặt bé trỏ vào bảng Chi
Tiet
HD

B3: Vào INSERT / PIVOT TABLE / mở ra hộp thoại / Chọn add this data khổng lồ the data Mode

OK / xuất hiện thêm Vỏ PIVOT TABLE( tương đương như “Hình ảnh Layout Pivot Table” )

B4: chế tác quan hệ cho các bảng

+ Đặt con trỏ vào phía bên trong vỏ Pivot Table

+ bên trên thanh Menu mở ra PIVOTTABLE TOOLS / lựa chọn Tab ANALYZE / lựa chọn Relationship / lộ diện hộp thoại 

*

+ chọn New / lộ diện hộp thoại (*)

*

=> trên Table: chọn bảng Khach
Hang

=> tại Related table: chọn bảng Hoa
Don

=> tại Column, Related Column : lựa chọn Ma
KH 

=> OK

+ tiến hành lại từ cách (*) để chế tác quan hệ giữa bảng Hoa
Don cùng Chi
Tiet
HD

B5: sau khoản thời gian tạo tình dục giữa các bảng chúng ta kéo thả các cột vào ở trong tính PIVOTTABLE giống hệt như ở trên

Như vậy để chế tạo Pivot Table cho các bảng thuộc lúc

B1: tạo thành bảng động cho các bảng phải tổng hợp

B2: Chèn Pivot Table

B3: chế tạo ra quan hệ giữa các bảng

B4: Kéo thả những cột vào Vỏ Pivot Table

3. PIVOT chart

Tương từ bỏ như Pivot Table mách nhỏ phần đồ gia dụng thị từ hình thành sau khi thực hiện nay Pivot

Bạn đang có khá nhiều bài tập Pivot table phải xử lý, bạn muốn khám phá cách thực hiện và chắc hẳn năng của Pivot vào Excel. Trong chỉ dẫn này chúng tôi sẽ phía dẫn bạn cách áp dụng pivot table cải thiện trong Excel.


Pivot table với nhiều cấp độ trong Excel

Khi bạn muốn kéo nhiều hơn một trường vào một khu vực trong bảng Pivot, bạn có thể thực hiện nay theo các cách bên dưới đây. Dựa trên việc họ sẽ để ý một ví dụ như về nhiều trường cho khu vực Rows, các trường Values và các trường Columns.

Hãy hãy nhớ là tập tài liệu của bọn chúng tôi bao gồm 213 bạn dạng ghi và 6 trường. Mã giao dịch, Mã cổ phiếu, Ngành, giá bán trị, Ngày cùng Quốc gia.


*

Thêm các trường cho quanh vùng Rows trong Pivot
Table

Đầu tiên, chèn một Pivot
Table. Tiếp theo, kéo các trường sau vào các khu vực khác nhau.

1. Ngôi trường Ngành và trường quốc gia cho khu vực Rows.

2. Trường quý hiếm đến khu vực Values.

*

Dưới đây chúng ta cũng có thể tìm thấy bảng trụ đa cấp.

*

Thêm nhiều trường cho khu vực Values trong Pivot
Table

Đầu tiên, chèn một bảng trụ. Tiếp theo, kéo những trường sau vào các khu vực khác nhau.

1. Trường tổ quốc đến khu vực Rows.

2. Trường quý hiếm đến khu vực Values (2x).

*

Lưu ý: nếu bạn kéo ngôi trường Số chi phí vào khoanh vùng Giá trị lần sản phẩm hai, Excel cũng trở nên điền vào quanh vùng Cột.

Kết quả:

*

3. Tiếp theo, nhấp vào bất kỳ ô như thế nào trong cột Sum of giá trị2

4. Bấm chuột phải và nhấp vào Value Field Settings .

*

5. Nhập tỷ lệ tỷ lệ cho tên tùy chỉnh.

6. Trên tab Show Values As , chọn % of Grand Total .

*

7. Nhấp vào OK.

Kết quả:

*

Thêm những trường cho quanh vùng Filters

Đầu tiên, chèn một Pivot
Table. Tiếp theo, kéo những trường sau vào các khu vực khác nhau.

1. Mã giao dịch thanh toán đến quanh vùng Rows.

2. Trường quý hiếm đến khu vực Values.

3. Trường quốc gia và ngôi trường Mã cầm cố phiếu cho quanh vùng Filters.

*

4. Tiếp theo, lựa chọn United Kingdom từ bỏ mũi tên thả xuống cỗ lọc đầu tiên và GIL tự mũi tên thả xuống cỗ lọc máy hai.

Bảng Pivot hiển thị toàn bộ các giao dịch thanh toán của mã cp ‘GIL’ tới từ United Kingdom.

*

huong-dan-cach-tao-pivot-table-nhieu-cap-do
Download

Các trường/mục tính toán

Ví dụ này dạy cho chính mình cách chèn ngôi trường được thống kê giám sát hoặc mục được đo lường vào bảng luân chuyển vòng.

Dưới đây chúng ta cũng có thể tìm thấy một bảng trụ. Quay trở về Pivot vào Excel để khám phá cách chế tác bảng luân phiên này.

*

Trường tính toán

Một ngôi trường được tính toán sử dụng những giá trị xuất phát điểm từ 1 trường khác. Để chèn một trường được xem toán, hãy thực hiện các bước sau.

1. Nhấp vào bất kỳ ô nào phía bên trong bảng trụ.

2. Trên tab Analyze, trong nhóm Calculations, bấm vào Fields, Items & Sets.

3. Nhấp vào Calculated Field

*

Hộp thoại Insert Calculated Field xuất hiện.

4. Nhập Oceania đến Name.

5. Nhập phương pháp = IF (Số tiền> 100000, 3% * Số tiền, 0)

6. Nhấp vào Thêm.

*

Lưu ý: sử dụng nút Field để mau lẹ chèn những trường khi bạn nhập công thức. Để xóa trường được xem toán, chọn trường và nhấp vào Delete (bên bên dưới Under).

7. Nhấp OK.

Excel auto thêm trường Tax vào khu vực Value.

Kết quả:

*

Mục tính toán

Một mục được đo lường và thống kê sử dụng các giá trị từ những mục khác. Để chèn một mục được xem toán, thực hiện công việc sau.

1. Nhấp vào ngẫu nhiên Quốc gia như thế nào trong bảng trụ.

2. Bên trên tab Analyze, trong team Calculations, bấm vào Fields, Mục và Bộ.

3. Nhấp vào Calculated Item.

*

Hộp thoại Insert Calculated thành tựu xuất hiện.

4. Nhập Oceania mang lại Name.

5. Nhập công thức =3%*(Australia+’New Zealand’)

6. Nhấp Add.

*

Lưu ý: sử dụng nút Insert tòa tháp để hối hả chèn những mục khi chúng ta nhập công thức. Để xóa một mục được tính toán, lựa chọn mục đó cùng nhấp vào Delete (bên dưới Under).

7. Lặp lại công việc 4 mang lại 6 đến North America (Canada và United States) và Europe (France, Germany & United Kingdom) với khoảng thuế theo lần lượt là 4% cùng 5%..

8. Nhấp OK.

Kết quả:

*

Lưu ý: shop chúng tôi đã chế tạo hai đội (Sales với Tax).


cach-them-muc-tinh-toan-trong-pivot-table
Download
cach-them-truong-tinh-toan-trong-pivot-table
Download

Chèn biểu thứ Pivot

Biểu thiết bị Pivot là màn biểu diễn trực quan tiền của bảng Pivot vào Excel.

Dưới đây chúng ta có thể tìm thấy một bảng Pivot nhì chiều. Quay lại Bảng Pivot vào table để tò mò cách tạo thành bảng luân phiên này.

*

Chèn biểu thứ Pivot

Để chèn biểu đồ trục, thực hiện các bước sau.

1. Nhấp vào bất kỳ ô nào bên trong bảng Pivot.

2. Bên trên tab Analyze, trong team Tools, bấm Pivot
Chart.

*

Khi đó hộp thoại Insert Chart xuất hiện.

3. Nhấp OK.

Dưới đây bạn có thể tìm thấy biểu thiết bị Pivot. Biểu đồ dùng này đang làm bỡ ngỡ và gây tuyệt vời với sếp của bạn.

*

Lưu ý: mọi biến hóa bạn thực hiện đối với biểu vật dụng Pivot được phản ảnh ngay lập tức trong Pivot
Table cùng ngược lại.

Lọc biểu vật dụng Pivot

Để lọc biểu đồ gia dụng Pivot này, các bạn hãy thực hiện các bước sau.

1. Sử dụng các bộ thanh lọc tiêu chuẩn chỉnh (hình tam giác sát bên Product cùng Country). Ví dụ: áp dụng bộ lọc Country để chỉ hiển thị tổng số tiền vàng từng sản phẩm được xuất sang trọng Hoa Kì (United States)

*

2. Xóa bộ lọc Country.

3. Bởi vì trường Category đã đạt thêm vào khu vực Filters nên cửa hàng chúng tôi có thể lọc biểu đồ dùng Pivot này (và bảng Pivot) theo Category. Ví dụ: thực hiện bộ lọc Category nhằm chỉ hiển thị các loại rau củ được xuất sang từng quốc gia.

*

Thay đổi nhiều loại biểu đồ dùng Pivot

Bạn gồm thể biến hóa thành một nhiều loại biểu đồ vật trục khác nhau bất cứ lúc nào.

1. Chọn biểu đồ.

2. Bên trên tab Design, trong team Type, bấm Change Chart Type.

*

3. Lựa chọn Pie.

Xem thêm:

*

4. Nhấp OK.

Kết quả:

*

Lưu ý: biểu đồ hình tròn luôn áp dụng một chuỗi dữ liệu (trong trường hợp này là Beans). Để giành được biểu thứ Pivot của một quốc gia, hãy đổi khác dữ liệu trên những trục. Đầu tiên, chọn biểu đồ. Tiếp theo, trên tab Design, trong đội Data, bấm Swap Row/Column


cach-them-bieu-do-pivot
Download

Theo Excel Easy


Share this post:


Share on Twitter
Share on Facebook
Share on Pinterest
Share on Linked
In
Share on Email