Top 11 # Xem Nhiều Nhất Tính Năng Pivottable Của Excel Mới Nhất 3/2023 # Top Like

Trong quá trình làm việc trên Excel, bạn sẽ gặp trường hợp dữ liệu quá lớn để phân tích. Tính năng PivotTable sẽ giúp bạn xử lý vấn đề hóc búa này. PivotTable có thể giúp các bạn tổng hợp dữ liệu theo nhóm và thu gọn các dữ liệu theo yêu cầu của bạn. Bài viết sau đây sẽ hước dẫn các bạn sử dụng PivotTable một cách hiệu quả nhất.

1. Cách sử dụng PivotTable để thống kê đơn giản

Ví dụ 1: Sử dụng PivotTable để thống kê số tiền phát sinh theo ngày của bảng trên.

Bước 2: Hộp thoại Create PivotTable xuất hiện. Tại ô Table/Range sẽ hiển thị địa chỉ vùng dữ liệu. Chọn nơi muốn đặt báo cáo PivotTable trong phần Choose where you want the PivotTable report to be replaced, .

New Worksheet: đặt trong sheet mới.

Existing Worksheet: đặt trong sheet hiện tại. Nếu chọn mục này thì các bạn cần chọn vị trí đặt PivotTable trong ô Location.

Cuối cùng nhấn OK để tạo PrivotTable.

Bước 3: Sau khi bấm OK thì Excel sẽ xuất hiện cửa sổ PivotTable Fields ở phía bên phải màn hình. Trong phần này có 2 phần:

– Choose fields to add to report: chọn trường dữ liệu để thêm vào báo cáo PivotTable,

– Drag fields between areas below: 4 vùng để thả các trường dữ liệu được chọn từ phần Choose fields to add to report

Theo yêu cầu của ví dụ là thống kê số tiền phát sinh theo ngày nên chúng ta tích chọn 2 ô Ngày và Số tiền.

Nếu các bạn muốn hiện thị theo cột thì có thể chuyển ô Ngày sang vùng COLUMNS bằng cách nhấn giữ chuột trái vào ô Ngày trong vùng ROWS và kéo sang vùng COLUMNS. Hoặc có thể nhấn chuột trái vào ô Ngày sau đó chọn Move to Column Labels.

Kết quả ta sẽ được bảng dữ liệu theo dạng hàng dọc:

2. Cách sử dụng PivotTable để thống kê dữ liệu của nhiều cột

Ví dụ 2: Sử dụng PivotTable để thống kê số tiền phát sinh Nợ theo ngày.

Các bạn cũng cần tạo PivotTable như Bước 1, Bước 2 ở ví dụ trước. Tiếp theo trong hộp thoại PivotTable Fields, chúng ta đánh dấu tích chọn ba mục là Ngày, Nợ, Số tiền.

Chúng ta cũng có thể chuyển ô Nợ hoặc Ngày sang vùng COLUMNS bằng cách nhấn giữ chuột trái sau đó kéo sang vùng COLUMNS. Hoặc nhấn chuột trái sau đó chọn Move to Column Labels để bảng hiện thị dễ nhìn hơn.

Kết quả ta sẽ được bảng như sau:

3. Định dạng báo cáo

Ta có thể thiết kế, định dạng lại PivotTable bằng cách chọn thẻ Design và lựa chọn các dạng bảng tại PivotTable Styles cho đến khi ưng ý.

1. Flash Fill (Tự động điền dữ liệu)

Ttính năng gần gũi nhất đối với người sử dụng Excel 2013 đó là Flash Fill. Tính năng này tiên đoán dữ liệu một cách thông minh và tự động điền dữ liệu vào các ô trống trên bảng tính.

Ví dụ, trên bảng tính chứa danh sách học sinh có một cột hiển thị họ tên ở dạng đầy đủ.

Bây giờ, bạn muốn tách họ tên ra làm hai cột, một cột chứa họ và cột còn lại chứa tên. Với tính năng Flash Fill sẽ giúp bạn thực hiện điều này một cách dễ dàng. Đầu tiên, bạn cần tạo thêm hai cột chứa họ và chứa tên nằm bên cạnh cột họ tên có sẵn. Bạn gõ họ của học sinh đầu tiên, sau đó chọn ô họ của học sinh tiếp theo. Vào thẻ Data, chọn Flash Fill. Ngay lập tức, Excel sẽ điền tự động họ của các học sinh còn lại trong danh sách. Bạn cũng làm được tương tự như trên cột tên học sinh.

Quick Analysis là một tính năng mới rất hữu ích của Excel 2013, nó giúp hỗ trợ phân tích và thực hiện nhanh một vài tác vụ đối với vùng dữ liệu được chọn.

– Thẻ FORMATTING gồm các mục để tô màu, định dạng lại các ô dữ liệu cho dễ xem hơn. Bạn rê chuột vào từng mục sẽ thấy công dụng của nó. Chẳng hạn, mục giúp hiển thị thêm mũi tên vào các ô dữ liệu dạng số, cho biết giá trị trong ô tăng hay giảm so với giá trị trung bình trên toàn vùng chọn. Mục Color Scale tô màu cho những ô giá trị bằng nhau để bạn dễ theo dõi. Nếu muốn bỏ các định dạng đã áp dụng, bạn chọn mục Clear Format .

– Thẻ giúp bạn tạo nhanh biểu đồ từ vùng dữ liệu đã chọn. Điểm hay là bạn chỉ cần rê chuột vào từng loại biểu đồ trong danh sách để xem trước kết quả khi tạo xong. Bạn nhấn More Charts nếu muốn chọn một loại biểu đồ không có trong danh sách.

– Thẻ gồm các phép tính cơ bản có cách sử dụng rất trực quan, không yêu cầu bạn gõ lệnh tính như trước, rất mất thời gian. Các phép tính có trong thẻ gồm (tính tổng), (tính trung bình), (đếm số phần tử), Running Sum (tính tổng và cộng dồn qua từng ô),… Bạn chỉ việc rê chuột vào từng phép tính để xem kết quả hiển thị ra trên bảng tính.

Nếu bạn muốn học một lớp Chứng chỉ ứng dụng CNTT căn bản (Word, Excel) vui lòng nhấp vào đây để xem thông tin chi tiết.

ĐC: 203-205 Lê Trọng Tấn – Sơn Kỳ – Tân Phú – HCM

Website: chúng tôi

Cập nhật vào 23/02

Excel 2013 đã chứng tỏ được sức mạnh của mình khi phát huy được những tinh hoa của excel 2010 và có những phát triển mới. Để cập nhật tính năng mới của phần mềm này, chúng ta cần theo các chỉ dẫn quan trọng và hữu ích.

Microsoft Excel có thể coi là phần mềm văn phòng thân thiết với nhiều người và được sử dụng phổ biến tương đương như Microsoft Word. Nó giúp tổ chức dữ liệu dưới dạng bảng biểu một cách khoa học và linh hoạt, đồng thời hỗ trợ phân tích, thống kê, đánh giá dữ liệu một cách nhanh chóng thông qua các hàm tính toán và biểu đồ.

Bài này sẽ không phải là tài liệu hướng dẫn những thao tác cơ bản của Excel vì những thao tác này vẫn được “di truyền” từ Excel 2010 (và các đời trước nữa) sang Excel 2013. Thay vào đó chúng ta sẽ tìm hiểu về những cái mới nổi bật nhất của Excel 2013 .

Thêm các hàm toán học

Trước hết, Excel 2013 được bổ sung thêm một số hàm tính toán mới. Vì thế để tìm hiểu về các hàm mới này, bạn có thể dùng hộp thoại Insert Function.

Bước 1: Chọn ô bạn muốn thực hiện hàm.

Bước 2: Vào tab FORMULAS, chọn Insert Function ngay đầu tiên.

Bước 5: Người dùng Excel có thể sử dụng các hàm một cách nhanh chóng hơn bằng dấu “=” và gọi tên hàm, vì thế hãy tham khảo thêm tên các hàm mới tại đây.

Trong Excel 2013, bạn có thể phân tích, đánh giá dữ liệu một cách tức thì ngay sau khi nhập xong bảng dữ liệu.

Bước 1: Bôi đen toàn bộ vùng dữ liệu bạn muốn phân tích nhanh.

Người dùng Excel có thể sử dụng các hàm một cách nhanh chóng bằng dấu “=” và gọi tên hàm.

Bước 2: Bấm chọn nút Quick Analysis hiện ra phía góc dưới bên phải của vùng dữ liệu đã chọn (phím tắt là Ctrl + Q).

Bước 4: Chọn một kiểu phân tích (bạn có thể để chuột phía trên mỗi lựa chọn để xem trước).

Không chỉ giúp người dùng nhanh chóng phác họa ra hình thái dữ liệu bằng các biểu đồ, Excel 2013 còn biết khuyến nghị biểu đồ nào phù hợp với dữ liệu của bạn nếu cần.

Bước 1: Để nghe lời khuyên biểu đồ phù hợp từ Excel 2013, bôi đen vùng dữ liệu của bạn.

Bước 3: Cửa sổ Insert Chart sẽ hiện ra với tab Recommended Charts đã mở sẵn. Xem qua những kiểu biểu đồ được Excel 2013 cho là phù hợp bằng cách chọn trong danh sách. Nếu đã hài lòng, hãy bấm OK, còn nếu chưa tìm thấy biểu đồ như ý, bạn vẫn có thể mở thẻ All Charts để xem toàn bộ các kiểu biểu đồ.

Điền nhanh hay Flash Fill, là chức năng mới nổi bật của Excel 2013 được Microsoft giới thiệu rất nhiều thời gian gần đây. Chức năng này giống như một trợ lý giúp nhập liệu hoàn thiện cho bạn ngay khi nắm bắt được kiểu mẫu dữ liệu bạn định nhập.

Ví dụ, bạn đã có sẵn một danh sách dài tên họ đầy đủ của một tập thể nhưng giờ muốn tách riêng ra một cột tên, một cột họ.

Bước 1: Nhập tên người đầu tiên sang cột bên cạnh.

Flash Fill đã đoán ra được ý định của bạn và gợi ý điền hộ cho cả danh sách còn lại

Một khả năng mới nổi bật và đáng chú ý nhất của Excel 2013 đương nhiên là việc người dùng có thể lưu trữ công việc dang dở lên kho điện toán đám mây và làm tiếp trực tuyến ở bất kỳ đâu, không nhất thiết phải trên thiết bị cũ.

Slicers là tính năng bộ lọc trực quan, sử dụng một bộ lọc trực quan (Slicer) bạn có thể lọc dữ liệu của mình bằng cách nhấp vào loại dữ liệu bạn muốn từ việc sử dụng chức năng Pivot table trong Excel.

Ví dụ: Như bạn thấy trong một bảng doanh thu tiêu thụ các mặt hàng theo thu nhập của từng loại khách hàng trong một báo cáo làm bằng pivot. Và bạn muốn biết dữ liệu là thuộc khu vực nào. Có hai tùy chọn cho bạn để thực hiện điều này.

Thêm vùng cho report filter và lọc vùng mà bạn muốn

Thêm một Slicer tại vùng và bạn kích chọn vùng dữ liệu bạn muốn

Với một báo cáo lọc report filter (hoặc bất cứ cách lọc khác nào) bạn sẽ phải nhấp nhiều lần để chọn một nhóm. Cùng với slicers, đó sẽ là một vấn đề chọn đơn giản.

Bằng cách nào để chèn một slicer ?

(Slicer chỉ có thể thực hiện trong Excell phiên bản 2010 trở lên)

1. Thêm một slicer trong Excel 2010:

Trong Excel 2010, bạn chỉ có thể thêm 1 slicer cho pivot tables và để chèn 1 slicer bạn thực hiện một trong các bước sau:

Vào Insert chọn Insert Slicer

Hoặc chọn vào biểu tượng (PivotTable Tools) và kích chọn Insert Slice

2. Thêm một slicer trong Excell 2013/2016:

Trong Excel 2013 và các phiên bản cao hơn, bạn có thể thêm một slicer cho pivot tables hoặc regular tables như sau:

Khi bạn thêm một slicer cho Regular tables, Chúng hoạt động giống như lọc tự động và lọc bảng dữ liệu của bạn. Thêm một slicer cho regular table bằng cách ta vào nút lệnh Insert ribbon sau đó nhấp vào biểu tượng Insert Slicer.

Thêm slicers cho Pivot tables:

Nhấp chuột phải vào vùng dữ liệu pivot table mà bạn muốn và kích chọn “add as slicer”

Đợn chọn và đa chọn trong Slicers:

Bạn có thể chọn một mục duy nhất hoặc nhiều mục trong Slicers. Để đa lựa chọn làm theo cách sau:

Nếu các mục bạn muốn gộp lại với nhau, chỉ cần kéo từ mục đầu tiên đến cuối cùng.

Nếu các mục bạn muốn tách biệt nhau, giữ phím CTRL và nhấp vào từng mục một.

Tạo biểu đồ tương tác cùng Slicers:

Khi Slicer tương tác với các bảng Pivot, bạn có thể sử dụng chúng để tạo các biểu đồ tương tác thú vị trong Excel. Quá trình cơ bản như sau:

Thiết lập pivot table mà cung cấp cho bạn dữ liệu cho biểu đồ của bạn.

Thêm Slicer để tương tác trên bất kỳ lĩnh vực nào (giả sử Slicer trên vùng dữ liệu khách hàng)

Tạo pivot chart (hoặc thậm chí regular chart) ) từ dữ liệu pivot table.

Di chuyển Slicer bên cạnh biểu đồ và định dạng mọi thứ theo sở thích của bạn

Và biểu đồ tương tác của bạn đã sẵn sàng phục vụ nhu cầu xử lý của bạn.

Liên kết multiple slicers với cùng một báo cáo Pivot (Pivot report)

Bạn có thể thêm bất kỳ slicers cho một pivot report. Khi bạn thêm slicer đa chọn (multiple slicers), Từng loại trong số chúng đều đóng vai trò trong việc báo cho pivot table là tập hợp dữ liệu phụ nào sẽ được sử dụng để tính toán các số.

Liên kết một slicer với nhiều privot tables:

Bạn cũng có thể liên kết một Slicer đơn với bất kỳ số lượng báo cáo privot nào. Điều này cho phép chúng tôi xây dựng các báo cáo tương tác và được lọc chéo rất mạnh mẽ bằng Excel. Để kết nối nhiều privot table với Slicer đơn, hãy làm theo các bước sau:

1. Tại tùy chọn: Đặt tên cho mỗi privot tables.

Để đặt tên cho các privot tables, nhấp chuột vào bất kỳ vị trí nào trong privot, di chuột tới Analyze ribbon và sử dụng vùng tên ở góc trên bên trái của privot table để đặt tên cho nó. Nếu bạn không đặt tên cho bảng bảng của mình, Excel sẽ đặt cho chúng các tên mặc định như PivotTable73. Điều này có thể gây nhầm lẫn khi bạn có nhiều hơn một privot tables

2. Nhấp chuột phải vào slicer và đi đến Báo cáo kết nối (Report connections) (trong Excel 2010, đây được gọi là kết nối các PivotTable).

3. Kiểm tra tất cả các privot tables mà bạn muốn sau đó nhấp chọn ok.

Liên kết slicer với nhiều hơn một cột:

Bạn có thể sử dụng cùng lúc nhiều mục đích khác nhau khi liên kết một slicer cho nhiều cột

Lựa chọn Slicer cho việc sử dụng công thức:

Mặc dù slicer là tuyệt vời và thú vị, nhưng bạn thường có lẽ muốn sử dụng chúng bên ngoài khung privot table.

Ví dụ: bạn muốn sử dụng các bộ xử lý để thêm tính tương tác vào biểu đồ của mình hoặc sử dụng chúng trong bảng điều khiển của bạn.

Khi bạn muốn làm một điêug gì đó như thế, về cơ bản bạn muốn các Slicer làm việc với các công thức của bạn. Để làm điều này, chúng ta có thể sử dụng 2 cách tiếp cận:

Sử dung dummy privot table route (hoặc harvester)

Lộ trình công thức CUB

Sử dụng Dummy privot table route

Quá trình là như sau:

Bạn có thể biết nghề nào được chọn trong slicer (để bạn có thể sử dụng nó trong một số công thức hoặc biểu đồ).

Tạo một pivot table khác.

Thêm vùng nghề nghiệp vào vùng row labels.

Liên kết slicer với pivot table mới (sử dụng tính năng kết nối báo cáo của slicer)

Bây giờ khi bạn nhấp vào slicer, cả pivot gốc và dummy pivot mới này đều thay đổi.

Truy cập vào row labels như các ô thông thường trong công thức của bạn để tìm ra mục slicer nào được chọn.

Cách tiếp cận công thức CUBE:

1. Để tìm ra slicer selection,

chúng ta có thể sử dụng công thức CUBERANKEDMEMBER () như thế này: =CUBERANKEDMEMBER(ThisWorkbookDataModel,Name_of_the_slicer , item_number)

Bạn có một slicer trên vùng dữ liệu và tên của nó là Slicer-Area (bạn có thể kiểm tra tên này từ Slicer properties

2. Để có được mục đầu tiên được chọn trong slicer

Bạn có thể sử dụng công thức CUBERANKEDMEMBER như sau:

= CUBERANKEDMEMBER (ThisWorkbookDataModel, Slicer_Area, 1)

Điều này sẽ trả về mục đầu tiên được chọn trên slicer.

Nếu không có lựa chọn nào (tức là bạn đã xóa bộ lọc trên slicer), Excel sẽ trả về phiên bản tất cả.

Gợi ý1: Bạn có thể sử dụng = CUBESETCOUNT (Slicer_Area) để đếm số lượng vật phẩm được chọn trong slicer.

Gợi ý 2: Bằng cách kết hợp các công thức CUBESETCOUNT và CUBERANKEDMEMBER, bạn có thể trích xuất tất cả các mục được chọn trong slicer dễ dàng.

Định dạng slicer

Slicer là hoàn toàn tùy biến. Bạn có thể dễ dàng thay đổi giao diện của chúng, cài đặt và sử dụng màu sắc của chúng bằng cách sử dụng tùy chọn công cụ cắt (slicer tools options).

Khi có quá nhiều mục trong slicer thì xử lý như sau:

Đơn giản. Xem nếu bạn có thể thiết lập slicer của bạn trong nhiều cột. Bạn cũng có thể điều chỉnh chiều cao và chiều rộng của các nút slicer cho phù hợp với yêu cầu của bạn. Nếu slicer của bạn vẫn còn quá lớn, bạn có thể điều chỉnh kích thước phông chữ của slicer bằng cách tạo kiểu mới.

Chọn màu cho slicer

Bạn có thể chuyển sang bảng màu khác. Chỉ cần nhấp vào tùy chọn công cụ của slicer (Slicer Tools Options) và chọn một kiểu bạn muốn.

Loại bỏ/thay đổi tựa đề của slicer

Có bạn có thể. Nhấp chuột phải vào slicer và đi đến cài đặt slicer “Slicer Settings”. Bỏ chọn tùy chọn tiêu đề hiển thị để loại bỏ nút lọc tiêu đề & xóa.

Một cách đơn giản là. Nhấp chuột phải vào slicer và chọn cài đặt Slicer settings. Kiểm tra Ẩn các mục không có tùy chọn dữ liệu.

Slicers và Report filter

Sự khác biệt chính giữa slicer và Report filters.

Report filter được gắn với các bảng pivot đơn trong khi Slicer có thể được liên kết với bất kỳ số lượng pivots.

Nếu Report filter là vụng về để làm việc thì slicer lại rất dễ sử dụng.

trong khi Report filter có thể không hoạt động tốt trong môi trường màn hình cảm ứng nhưng slicer rất tốt cho giao diện người dùng màn hình cảm ứng.

Khi Report filter chiếm một ô trên mỗi bộ lọc thì Slicer chiếm nhiều không gian hơn trên giao diện người dùng.

Report filter có thể được tự động hóa với VBA đơn giản còn slicer cần nhiều mã hơn một chút để tự động hóa.

Bạn có thể truy cập các giá trị bộ lọc báo cáo bằng cách sử dụng các tham chiếu ô đơn giản. Các giá trị của slicer có thể được trích xuất bằng cách sử dụng các dummy pivot tables hoặc công thức CUBE, cả hai đều cần có năng lực.

Slicers & Compatibility

(Slicer tương thích với Excel 2010 và các phiên bản Excel cao hơn)

Nếu bạn tạo một sổ làm việc trong Excel 2010 (hoặc các phiên bản cao hơn) với slicers và gửi email cho một người bạn bằng Excel 2007, họ sẽ thấy một hộp trống nơi có slicer. Slicer hoạt động trên các phiên bản máy tính để bàn và web của Excel theo cùng một cách.

Bạn có đang sử dụng slicer? Nhận xét của bạn về slicer là gì?

Trích nguồn: chandoo