30 Mẹo Và Thủ Thuật Excel Nâng Cao Hàng Đầu

Trong bài viết này, mình sẽ chia sẻ cho các bạn 30 mẹo và thủ thuật cực hay và rất quan trọng trong công việc. Hãy cùng mình tham khảo bên dưới nhé!

Video hướng dẫn

Tải file thực hành tại đây.

Mẹo và thủ thuật nâng cao - Kết hợp từ nhiều file lại thành 1 file

Để gộp nhiều file lại với nhau, bạn cần có các bước sau:

Bước 1: Đưa tất cả các file lại vào cùng một thư mục như hình dưới:

hình-gom-file-vao-thu-muc

Bước 2: Mở file Excel trống ra, tại đây bạn vào Data => Get Data => Chọn From File => From Folder

hình các bước vào thư mục

Bước 3: Truy cập vào thư mục chứa các file để gộp và nhấn Open để mở.

hình truy cập vào thư mục

Bước 4: Tiếp theo, bạn chọn vào Combie and tranform data 

kích chọn combie and transform data

Bước 5: Hiển thị hộp thoại Combie Files. Tại đây, bạn chọn vào Sheet1 là Sheet chứa dữ liệu gộp và nhấn OK

hình chọn sheet gộp file

Bước 6: Hiển thị giao diện Power Query Editor

giao diện power query

Bước 7: Tại đây, bạn cần thực hiện các thao tác sau:

- Để xóa cột bị thừa, bạn chọn vào cột đó, nhấn chuột phải và chọn Remove
- Để xóa dòng bị thừa như Null: Bạn chọn vào cột bất kì chứa null => tích chọn vào bộ lọc bên cạnh cột hình ảnh bộ lọc => bỏ tích chọn Null và nhấn OK.

hình ảnh tích chọn null

- Để đưa tiêu đề lên dòng đầu, ta vào thẻ Home => Use First Row as Headers
- Để xóa tiêu đề bị thừa từ các file phía sau, bạn tích chọn bộ lọc bên cạnh 1 cột bất kì => tích bỏ tiêu đề => OK.

Bước 8: Sau khi hoàn thành xong, bạn vào thẻ Home => Close & Load để ra ngoài file Excel nhé!

Bước 9: Các file đã được gộp thành một file hoàn chỉnh.

hình ảnh các file sau khi đã gộp file

Lưu ý: Để cập nhật file mới thêm vào thư mục, tại file đã gộp bạn chọn một ô bất kì, nhấn chuột phải => Refresh.

Xem thêm

Mẹo và thủ thuật nâng cao - Sử dụng Text to Column

 Là công cụ chuyên tách các dữ liệu thành cột riêng được chỉ định cụ thể. 

Ví dụ: Trong ví dụ dưới đây, mình cần tách số liệu trong cùng một cột sang nhiều cột riêng biệt.

hình ảnh dữ liệu text to column

Bước 1: Bạn bôi đen vùng dữ liệu cần tách, đồng thời kích chuột vào Tab Data => Text to Column

hình ảnh kích chọn text to column

Bước 2: Hiển thị ra hộp thoại Convert Text to Column Wizard, bạn chọn vào Delimited và nhấn Next

hình ảnh hộp thoại convert text to column

Bước 3: Tiếp theo chọn vào ô Other, gõ dấu "," trong ô hoặc chọn Comma.

Nếu ký tự ngăn cách là ký tự khác, các bạn chọn tùy chọn tương tự với ký tự đó:

  • Tab: ngăn cách nhau bằng phím tab.
  • Semicolon: ngăn cách nhau bằng dấu chấm phẩy.
  • Comma: ngăn cách nhau bằng dấu phẩy.
  • Space: ngăn cách nhau bằng dấu cách.
  • Other: ký tự khác, nếu các bạn chọn lựa chọn này các bạn cần nhập ký tự khác vào ô vuông bên cạnh.

và nhấn Next

hình ảnh chọn dấu tách

Bước 4: Tiếp theo bạn chọn vào General và nhấn Finish

Bạn sẽ được kết quả như bên dưới:

hình ảnh sau khi tách cột dữ liệu

Mẹo và thủ thuật nâng cao - Xóa giá trị bị trùng

Ví dụ: Ta thực hiện xóa dữ liệu bị trùng trong bảng dưới đây:

hình ảnh dữ liệu

Bước 1: Bôi đen vùng dữ liệu, sau đó vào thẻ Data => remove duplicate

hinh-anh-chon-remove-duplicated

Bước 2: Chọn cột bạn muốn xóa giá trị bị trùng, sau đó nhấn OK.

Dữ liệu sau khi xóa giá trị bị trùng

bảng dữ liệu sau khi xóa bị trùng

Mẹo và thủ thuật nâng cao - Tự động điền dữ liệu

Ví dụ: Mình sẽ sử dụng thủ thuật này để tính Điểm trung bình nhanh trong bảng dữ liệu dưới đây:

hình ảnh dữ liệu autofill

Bạn di con chuột tới vị trí cuối ô Điểm trung bình của học sinh đầu tiên, hiện ra dấu cộng. Bạn giữ con trỏ chuột và kéo xuống đến học sinh cuối cùng.

hình sau khi dùng autofill

Mẹo và thủ thuật nâng cao - Sử dụng Flash Fill

Flash Fill là công cụ giúp bạn thực hiện những thao tác lặp lại các thao tác mà bạn đã làm một cách nhanh chóng.

Ví dụ: Mình sẽ áp dụng Flash Fill cho bảng dữ liệu dưới đây:

hình ảnh dữ liệu flash fill

Ở bảng dữ liệu thứ nhất, để tách họ tên ra 2 cột riêng biệt. Bạn làm như sau:

Tại Họ tên đầu tiên, bạn viết họ ra trong cột Họ. Để hiển thị các họ các ô sau, bạn nhấn tổ hợp phím CTRL + E

hình dữ liệu cột họ

Tương tự, ở cột tên. Bạn cũng gõ tên người thứ nhất ra và nhấn CTRL + E để hiện thị các tên sau.

hình ảnh hiển thị tên

Tương tự bảng thứ hai bạn cũng làm tương tự vậy.

hình ảnh bằng thứ hai

Lưu ý: Trường hợp ở cột tháng vì thời gian dòng đầu tiên ngày và tháng trùng nhau. Vì vậy, bạn sẽ viết tiếp tháng thứ 2 và nhấn CTRL + E.

Mẹo và thủ thuật nâng cao - Sử dụng Custom Lists

Là công cụ để sắp xếp các dữ liệu trong bảng tính.

Ví dụ: Mình sẽ áp dùng công cụ này để hiển thị và sắp xếp dữ liệu bên dưới:

hình dữ liệu custiom list

Để sử dụng công cụ này, bạn vào File => Options => Advances => Chọn vào mục General => click vào Edit Custom Lists...

hình ảnh cài đặt công cụ custom list

Hiển thị hộp thoại Custom Lists, ở mục Custom lists bạn chọn NEW LIST, mục Import list from cells bạn bôi đen vùng dữ liệu

hình ảnh hộp thoại custom list

Tiếp theo, chọn Import và nhấn Ok để hoàn thành.

Sau khi đã cài đặt công cụ Custom Lists xong, bạn chọn một ô bất kì và gõ họ tên bất kì trong vùng dữ liệu.

Tiếp theo, di chuyển con chuột tới cuối ô mới gõ họ tên bất kì, hiển thị dấu cộng và bạn kéo xuống để hiển thị các tên khác.

hình hiển thị danh sách dữ liệu

Mẹo và thủ thuật nâng cao - Sử dụng Freeze Panes

Đây là công cụ giúp cố định cột dòng theo yêu cầu.

Ví dụ: Mình sẽ sử dụng công cụ này cho dữ liệu bên dưới:

dữ liệu freeze panes

Để cố định dòng tiêu đề, bạn làm như sau:

Đầu tiên, bạn chọn dòng dưới dòng tiêu đề, sau đó vào thẻ View => Freeze Panes

hình cố định dòng tiêu đề

Để cố định cột đầu tiên, bạn làm như sau:

Đầu tiên, chọn cột bên phải cột đầu tiên, sau đó vào thẻ View => Freeze Panes

hình cố định cột

Để cố định cả dòng tiêu đề và cột đầu tiên, bạn chọn vào ô giao giữa dòng sau tiêu đề và cột bên phải cột đầu tiên. Trong ví dụ là ô B4, sau đó vào View, chọn Freeze Panes.

hình cố định cột và dòng

Mẹo và thủ thuật nâng cao - New Window and Arrange

Đây là công cụ, có thể mở so sánh 2 hoặc nhiều file trên cùng một lúc.

Để xem song song 2 trang tính cùng một lúc:

Tại trang tính đầu tiên, bạn chọn vào View => Arrange All. Tiếp theo, chọn Vertical trong hộp thoại Arrange Windows, sau đó nhấn OK.

hình ảnh so sánh 2 file

Mẹo và thủ thuật nâng cao - Tables

Tables là một đối tượng được đặt tên cho phép bạn quản lý nội dung bên trong một cách độc lập với phần còn lại của dữ liệu trang tính.

Ví dụ: Mình sẽ thực hiện tạo bảng trong vùng dữ liệu dưới đây:

hình ảnh dữ liệu table

Các bước thực hiện:

Bước 1: Chọn một ô bất kì trong vùng dữ liệu.

Bước 2: Vào thẻ Insert => Table. Hiển thị hộp thoại Create Table chứa phạm vi vùng dữ liệu.

hình các bước tạo bảng table

Bước 3: Nhấn Ok để tạo bảng dữ liệu.

hình ảnh tables

Mẹo và thủ thuật nâng cao - Data Analysis 

Là công cụ chyên phân tích dữ liệu theo nhiều dạng một cách nhanh chóng và chính xác .

Ví dụ: Mình sẽ áp dụng vào để phân tích và thống kê dữ liệu sau:

hình dữ liệu data analyze

Đầu tiên, vào thẻ Home => Analyze Data. Hiển thị ra các dạng phân tích theo dữ liệu

các dạng phân tích

Tiếp theo, bạn sẽ chọn một dạng phù hợp theo yêu cầu và chọn Insert Chart ở dưới dạng phân tích đó.

chọn dạng phù hợp cho dữ liệu

Mẹo và thủ thuật nâng cao - Loại dữ liệu đa dạng thức

Là công cụ chuyển đổi dữ liệu này sang nhiều dạng dữ liệu khác phong phú.

Ví dụ: Mình sẽ áp dụng công cụ này để chuyển đổi tên công ty sang loại dạng cổ phiếu trong dữ liệu dưới đây:

hình ảnh tên công ty

Các bước thực hiện:

Bước 1: Tạo bảng cho vùng dữ liệu bằng cách chọn một ô bất kì trong vùng dữ liệu, sau đó nhấn Ctrl + T để hiển thị bảng Create Table, bôi đen vùng phạm vi dữ liệu và nhấn Ok để tạo bảng.

Bước 2: Bôi đen vùng dữ liệu chứa tên công ty. Sau đó, vào thẻ Data => Stock 

hình các bước chuyển đổi dữ liệu

Bước 3: Bảng dữ liệu sau khi được chuyển đổi sang cổ phiếu chứng khoáng 

hình ảnh có phiếu chứng khoán

Bây giờ, hãy chọn ô có kiểu dữ liệu. Các ‘Add Column‘ sẽ xuất hiện.

hình add column

Hiển thị ra các dạng dữ liệu. Tại đây, bạn có thể chọn có dạng theo như yêu cầu nhé!

Ví dụ: Bạn quan tâm đến việc chọn ra yếu tố quyết định quan trọng nhất là Giá. Bạn sẽ chọn vào Price

hình hiển thị giá dữ liệu

Mẹo và thủ thuật nâng cao - Tạo Drop list (danh sách thả xuống)

Để tạo danh sách dữ liệu thả xuống trong Excel, mình sẽ sử dụng công cụ Data Validation.

Ví dụ: Mình muốn tạo drop list dựa vào dữ liệu sau:

hình dữ liệu muốn drop list

Các bước thực hiện: 

Bước 1: Chọn một ô trống bất kì để chứa danh sách dữ liệu thả xuống.

Bước 2: Vào thẻ Data => Data Validation. Hộp thoại Data Validation hiện ra

hình ảnh tạo danh sách thả xuống

Bước 3: Tại mục Allow, chọn vào List. Mục Source, bôi đen vùng dữ liệu muốn hiển thị list danh sách. Và nhấn OK.

hình ảnh tạo tùy chọn trong data validation

Bước 4: Danh sách dữ liệu Họ và Tên sau khi đã tạo

hình ảnh danh sách list họ và tên

Mẹo và thủ thuật nâng cao - Tạo thông báo ô xác thực dữ liệu

Ví dụ: Tạo thông báo khối lượng nhập vào phải lớn hơn 0 trong dữ liệu sau:

dữ liệu thông báo

Các bước thực hiện:

Bước 1: Bôi đen các dòng trong cột khối lượng.

Bước 2: Vào thẻ Data => Data Validation. Hiển thị hộp thoại Validation, bạn vào mục Input Message

hình ảnh vào mục input message

Bước 3: Tích chọn vào ô Show input message when cell is selected. Mục Tilte, bạn ghi tiêu đề thông báo. Mục Input message, bạn ghi nội dung thông báo

hình tạo thông báo trong data validation

Bước 4: Nhấn Ok để hoàn thành hiển thị thông báo.

hình hiển thị thông báo

Mẹo và thủ thuật nâng cao - Hàm so sánh logic (Hàm IF)

Là hàm cho phép bạn thực hiện phép tính so sánh logic giữa một giá trị với giá trị bạn mong muốn. (Xem chi tiết về hàm)

Ví dụ: Mình có bảng dữ liệu bên dưới:

dữ liệu hàm if

Yêu cầu: Tính đơn giá mặt hàng, biết rằng: Nếu mặt hàng thuộc nhóm X thì giá là 2000đ, còn lại là giá 2500.

Để xử lý dữ liệu, tại ô E4 mình gõ công thức sau: =IF(C4="X",2000,2500)

hình phép tính hàm if

Mẹo và thủ thuật nâng cao - Hàm đếm dữ liệu có điều kiện (Hàm Countifs)

Ví dụ: Đếm các điều kiện được yêu cầu từ dữ liệu sau:

hình dữ liệu countifs

Đếm các sản phẩm có số tiền trên 300: =COUNTIFS($D$3:$D$13,">300")

hình đếm sản phẩm điều kiện 1

Đếm các sản phẩm sữa có số tiền trên 300: =COUNTIFS($C$3:$C$13,"Milk",$D$3:$D$13,">300")

hình đếm sản phẩm điều kiện 2

Đếm các sản phẩm sữa của cửa hàng MiniMart có số tiền trên 300: =COUNTIFS($C$3:$C$13,"Milk",$B$3:$B$13,"MiniMart",$D$3:$D$13,">300")

hình đếm sản phẩm điều kiện 3

Mẹo và thủ thuật nâng cao - Hàm tính tổng dữ liệu có điều kiện (Hàm Sumifs)

Ví dụ: Mình muốn tính tổng Số tiền trong vùng dữ liệu dưới đây:

hình dữ liệu tổng

Để tính tổng tiền mua ở cửa hàng FoodMart, bạn gõ công thức sau: =SUMIFS($E$4:$E$14,$C$4:$C$14,"FoodMart")

hình dữ liệu tổng

Mẹo và thủ thuật nâng cao - Hàm dò tìm dữ liệu theo đối tượng (Hàm Vlookup)

Ví dụ: Mình có bảng dữ liệu sau:

hình dữ liệu dò tìm

Yêu cầu: Dựa vào Họ và tên đã cho hãy dò tìm Giới tính, Lý thuyết, Thực hành, Tổng điểm của người đó dựa vào bảng dữ liệu.

Để tìm Giới tính, bạn gõ công thức sau: =VLOOKUP($H$4,B4:F10,2,0)

hình ảnh tìm giới tính

Để tìm lý thuyết, bạn cũng viết công thức như tìm giới tính và vị trí cột hiển thị là 3: =VLOOKUP($H$4,B4:F10,3,0)

hình ảnh điểm lý thuyết

Tương tự bạn cũng tìm điểm thực hành, tổng điểm như vậy.

hình ảnh dùng hàm vlookup

Mẹo và thủ thuật nâng cao - Hàm Index & Match

Index là hàm trả về giá trị của một phần tử mảng hoặc một mảng.

Match là hàm tìm kiếm vị trí của một dữ liệu trong vùng dữ liệu.

Ví dụ: Mình sẽ sử dụng hai hàm trên vào ví dụ sau: 

dữ liệu hàm index match

Yêu cầu: Tìm Số lượng dựa vào điều kiện sau:

hình ảnh điều kiện index match

Để tìm số lượng dựa vào điều kiện trên, bạn gõ công thức sau: =INDEX($E$4:$E$12,MATCH(1,(I6=B4:B12)*(I7=C4:C12)*(I8=D4:D12),0),1)

hình ảnh tính số lượng

Mẹo và thủ thuật nâng cao - Mảng động

Cho phép chúng ta trả về nhiều giá trị từ một hàm duy nhất, vì vậy trước đây chúng ta chỉ trả về một kết quả từ một hàm và điều đó không còn đúng nữa nên có 6 hàm mới bộ lọc hiển thị một thứ tự sắp xếp và duy nhất : Filter, Randarray, Sequence, Sort, Sortby, Unique,…

  • Filter: cho phép bạn lọc giá trị dựa theo điều kiện
  • Sort: Sắp xếp các mảng theo thứ tự.
  • Unique: nhận danh sách các giá trị duy nhất
  • Sortby: sắp xếp theo chức năng ví dụ tăng cột này và giảm cột khác.
  • Sequence: cho phép bạn tạo một danh sách các số liên tiếp trong một mảng, chẳng hạn như 1, 2, 3, 4
  • Randarray: tạo một phạm vi giá trị ngẫu nhiên.
  • Show Formulas: Hiển thị tất cả công thức và trang tính. 

Ví dụ: Cho dữ liệu như sau:

dữ liệu mảng động

  • Sử dụng hàm Filter: 

Yêu cầu: Lọc ra các sản phẩm 01 dựa vào dữ liệu trên:

Bạn gõ như sau: =FILTER($A$2:$D$20,$C$2:$C$20="SP 01","")

Giải thích: $A$2:$D$20: Vùng dữ liệu
                  $C$2:$C$20: Cột dữ liệu chứa điều kiện và điều kiện trong yêu cầu trên là SP 01.

hình ảnh hàm filter

  • Sử dụng hàm Sort: 

Yêu cầu: Hãy sắp xếp thứ tự tăng dần theo cột Sản phẩm:

Bạn gõ như sau: =SORT($A$3:$D$20,3,1)

Giải thích: $A$3:$D$20: Phạm vi vùng dữ liệu.
                 3: Vị trí cột trong vùng dữ liệu muốn sắp xếp.
                1: Sắp xếp tăng dần.  

hình sử dụng hàm sort

  • Sử dụng hàm Unique:

Yêu cầu: Hiển thị giá trị duy nhất trong cột Sản phẩm:

Bạn gõ như sau: =UNIQUE($C$3:$C$20,FALSE)

Giải thích: $C$3:$C$20: Phạm vi dữ liệu trong cột Sản phẩm.
                  FALSE: Sắp xếp theo hàng.
            

hình dùng hàm unique

  • Sử dụng hàm Sortby:

Yêu cầu: Hiển thị dữ liệu được sắp xếp theo tăng giá trị trong cột sản phẩm và giảm giá trị trong cột Doanh thu.

Bạn gõ như sau: =SORTBY($A$3:$D$20,$C$3:$C$20,1,$D$3:$D$20,-1)

Giải thích: $A$3:$D$20: Phạm vi vùng dữ liệu.
                 $C$3:$C$20: Phạm vi dữ liệu của cột Sản phẩm.
                 1: Sắp xếp giá trị theo tăng dần.
                 $D$3:$D$20: Phạm vi dữ liệu trong cột Doanh thu.
                 -1: Sắp xếp giá trị theo giảm dần.

hình ảnh dùng sortby

  • Sử dụng hàm Sequence:

Yêu cầu: Tạo ra một mảng gồm 5 hàng và 4 cột

Bạn gõ công thức sau: =SEQUENCE(5,4)

hình dùng hàm sequence

  • Sử dụng hàm Randarray: 

Yêu cầu: Tạo ra mảng giá trị nguyên ngẫu nhiên gồm 5 hàng, 3 cột, bắt đầu từ giá trị 1, kết thúc là giá trị 100.

Bạn gõ công thức sau: =RANDARRAY(5,3,1,100,TRUE)

hình ảnh dùng hàm randarray

Mẹo và thủ thuật nâng cao - Hiển thị công thức

Để muốn xem trong vùng dữ liệu đó có chứa hàm hay không hoặc công thức để tạo ra kết quả này là gì thì bạn cần phải để chế độ xem công thức bằng các bước như sau:

Đầu tiên, chọn trang tính muốn hiển thị công thức. Tiếp theo, vào thẻ Formulas => Chọn vào Show Formulas.

hình hiển thị công thức

Mẹo và thủ thuật nâng cao - Sử dụng hàm Filter lọc dữ liệu.

(Xem chi tiết về hàm)

Ví dụ: Cho vùng dữ liệu như sau:

dữ liệu công cụ filter

Yêu cầu: Lọc ra điểm số phần Thực hành lớn hơn 7.

Để lọc ra điểm số theo như yêu cầu đề bài. Bạn thực hiện các bước sau:

Bước 1: Bạn bôi đen vùng dữ liệu muốn lọc. Tiếp theo vào thẻ Data => Filter.

Bước 2: Chọn vào bộ lọc bên cạnh ô Thực hành.

hình chọn bộ lọc

Bước 3: Chọn vào Number Filters => Greater Than Or Equal To...

hình ảnh chọn điều kiện lọc

Bước 4: Hiển thị ra hộp thoại Custom AutoFilter. Tại đây, bạn gõ điều kiện mà đề yêu cầu.

hình ảnh gõ điều kiện yêu cầu

Bước 5: Nhấn Ok để hoàn thành.

hình ảnh sử dụng công cụ filter

Mẹo và thủ thuật nâng cao - Lọc dữ liệu theo nhiều điều kiện (Hàm Advanced Filter).

(Xem thêm về hàm).

Ví dụ: Mình có bảng dữ liệu như bên dưới:

hình dữ liệu advances

Yêu cầu: Dựa vào điều kiện dưới đây để lọc ra dữ liệu theo yêu cầu.

điều kiện lọc

Để lọc dữ liệu dựa theo nhiều điều kiện đề cho như trên bạn làm như sau:

Đầu tiên, vào thẻ Data => Advanced. Hiển thị hộp thoại Advanced Filter

Tiếp theo, Hộp thoại Advanced Filter hiện ra. Tại đây, bạn thực hiện như sau:

Tích chọn vào mục Copy to another location, mục List range: Bạn bôi đen vùng dữ liệu, mục Criteria range: Bạn bôi đen vùng chứa điều kiện dữ liệu, mục Copy to: Bạn chọn ô hiển thị kết quả.

hình thao tác hộp thoại advances

Nhấn Ok để hoàn thành.

hình ảnh hoàn thành bộ lọc advances

Mẹo và thủ thuật nâng cao - Sử dụng bộ lọc Slicer

Ví dụ: Mình sẽ dùng công cụ slicer để lọc dữ liệu sau:

hình dữ liệu slicer

Các bước thực hiện:

Bước 1: Chọn một ô bất kì trong bảng dữ liệu.

Bước 2: Trong tab Table Design, chọn vào Insert Slicer.

bước chọn vào insert slicer

Bước 3: Chọn vào trường dữ liệu muốn lọc trong bảng Insert Slicer.

hình chọn trường muốn lọc

Bước 4: Nhấn OK để hiển thị slicer trường dữ liệu đã chọn.

hình slicer trường dữ liệu muốn hiển thị

Bước 5: Chọn một hoặc nhiều nút trong hộp Slicer để hiển thị dữ liệu theo như yêu cầu.

hình chọn nút slicer

Mẹo và thủ thuật nâng cao - PivotTable

PivotTable giúp phân tích và tóm tắt dữ liệu để bạn có thể tổng hợp tất cả dữ liệu theo yêu cầu. 

Ví dụ: Dùng PivotTable để phân tích dữ liệu sau:

hình dữ liệu pivottable

Các bước thực hiện phân tích dữ liệu:

Bước 1: Chọn 1 ô bất kì trong bảng dữ liệu.

Bước 2: Vào thẻ Insert => PivotTable. Hộp thoại PivotTable hiện ra.

Tại đây, mục Table/Range: chứa vùng dữ liệu bạn muốn phân tích. Mục Choose where you want the pivotTable to be placed: Bạn tích chọn New Worksheet: để hiển thị kết quả sang sheet mới hoặc Existing Worksheet: hiển thị kết quả tại sheet chứa dữ liệu gốc và chọn ô để hiển thị kết quả tại mục Location.

thiết lập tùy chọn pivot table

Bước 3: Bảng PivotTable hiện ra. Tại đây, ví dụ mình muốn tính số lượng của môn học mà học sinh đã đăng kí.

hình ảnh hiển thị số lượng đăng kí môn

Mẹo và thủ thuật nâng cao - Pivot Table Slicers & Timelines

Table Slicer: là công cụ để lọc dữ liệu theo một trường nào đó trong bảng.

Timelines: là công cụ giúp lọc dữ liệu theo đường thời gian.

Để sử dụng công cụ này, mình sẽ ví dụ bảng PivotTable ở ví dụ trên:

Đầu tiên, bạn sẽ chọn vào vị trí bất kì trong bảng PivotTable để hiển thị ra thẻ PivotTable Analyze.

Tiếp theo, tại thẻ PivotTable Analyze, bạn chọn vào Insert slicer và chọn vào các trường dữ liệu muốn lọc trong bảng Insert Slicer.

hình ảnh chọn trường hiển thị

Nhấn OK để hiển thị slicer trường dữ liệu đã chọn.

trường dữ liệu hiển thị slicer

Chọn một hoặc nhiều nút trong hộp Slicer để hiển thị dữ liệu theo như yêu cầu.

hình chọn nút slicer pivottable

Tiếp theo công cụ Timelines, bạn cũng chọn vào Insert Timelines. Chọn vào trường thời gian bạn muốn lọc.

hình chọn trường thời gian

Và nhấn Ok. Sau đó, mình sẽ chọn vào thời gian muốn hiển thị dữ liệu nhé!

hình hiển thị dữ liệu timeline

Mẹo và thủ thuật nâng cao - Hiển thị giá trị tính toán.

Bạn muốn xem xét các giá trị hiển thị các dạng phép tính khác nhau cho bảng tổng hợp. 

Ví dụ: mình muốn hiển thị phần trăm số lượng đăng kí môn học dựa vào bảng PivotTable trên. Các bước làm như sau:

Đầu tiên, bạn sẽ bôi đen dữ liệu trong cột Môn học

Tiếp theo, nhấn chuột phải, chọn vào Show Values As và chọn % of Grand Total.

hình chọn hiển thị phần trăm tổng

Hiển thị kết quả sau:

kết quả hiển thị phép tính

Mẹo và thủ thuật nâng cao - Dữ liệu quan hệ (Relationships)

Đây là cách giúp bạn nhóm nhiều dữ liệu có mối quan hệ với nhau thành một bảng. Từ đó, sẽ phân tích và tóm tắt các giá trị trong bảng đó.

Ví dụ: Mình có 2 bảng dữ liệu như dưới.

hình dữ liệu relationship

Để nhóm 2 bảng dữ liệu trên, bạn thực hiện các bước như sau:

Bước 1: Bạn chọn 1 ô bất kì ở bảng dữ liệu thứ nhất. 

Bước 2: Vào thẻ Insert => PivotTable. Hộp thoại Create PivotTable hiện ra, bạn sẽ thiết lập vùng dữ liệu (giống hướng dẫn trên) và tích chọn Add this data to the Data Model.

hình ảnh thiết lập bảng pivottable

Bước 3: Vào thẻ PivotTable Analyze, chọn vào Relationships và chọn New

Bước 4: Tại bảng Create Relationships. Ở mục Table, bạn chọn tên bảng dữ liệu thứ nhất và chọn tên bảng dữ liệu thứ hai trong mục Relates Table. Hai bảng đều có chung quan hệ là cột ID.

hình ảnh tạo relationship

Bước 4: Nhấn Ok. Nếu liên kết được thì sẽ hiện bảng sau:

bảng liên kết manager

Bước 5: Nhấn Close để hoàn thành.

Bước 6: Trong bảng PivotTable, chọn mục All. Kéo trường SL bán ở Table 11 xuống ô Values và kéo trường ID ở Table 13 xuống ô Rows.

hình ảnh kéo trường hai bảng

Mẹo và thủ thuật nâng cao - Hàm DAX

Là hàm cho phép ta tạo các phép tính cực kỳ mạnh mẽ và linh hoạt trong bảng tổng hợp.

Ví dụ: Mình sử dụng bảng PivotTable được liên kết hai bảng trên.

Yêu cầu: Dùng tính năng DAX trong excel để đếm giá trị khác biệt và duy nhất trong excel.

Bạn làm như sau:

Bước 1: Chọn vào trường Table11 trong bảng PivotTable và nhấn chuột phải, chọn Add Measure.

hình chọn add measure

Bước 2: Hiển thị ra hộp thoại Measure. Tại đây, bạn ghi tên measure trong ô Measure Name, xuống mục Formulas: Bạn sẽ thiết lập công thức như sau: =DISTINCTCOUNT(Table11[ID])

hình ảnh thiết lập measure

Bước 3: Measure mình thiết lập đã hiển thị trong Table11 ở bảng PivotTable. Tiếp theo bạn sẽ kéo Measure đó xuống dưới mục Values.

hình ảnh hiển thị mục measureVậy là mình đã hướng dẫn xong 30 mẹo và thủ thuật nâng cao hàng đầu trong Excel. Hi vọng bạn sẽ thực hiện thành công. Nếu thấy bài viết có ích, vui lòng đánh giá bài viết bên dưới! Trân trọng.

 

CHO ĐIỂM BÀI VIẾT NÀY

vote data

HÌNH ẢNH HỌC VIÊN

học vi tính văn phòng trung tâm tin học Sao Việt học máy vi tính văn phòng trung tâm dạy tin học văn phòng lớp học máy tính văn phòng khóa học tin học Excel khóa học tin học văn phòng khóa học sử dụng máy vi tính học vi tính cho người đi làm khóa học tin học văn phòng khóa học sử dụng máy tính khóa học vẽ Autocad 2D - 3D
TOP