Tổng hợp mẹo và thủ thuật Excel nâng cao giúp tăng hiệu suất làm việc

Nếu bạn đang sử dụng Excel trong công việc của mình thì ngoài việc nắm được những kiến thức cơ bản, biết được những thủ thuật Excel nâng cao này còn giúp bạn gia tăng hiệu suất và tiết kiệm thời gian hơn đấy. Cùng Gitiho tìm hiểu nhé!

Gộp nhiều file thành một file

Trước tiên để gộp nhiều file lại với nhau, chúng ta phải đưa tất cả các file này vào cùng một thư mục trên máy tính.

Tiếp theo, mở một file Excel rỗng, vào mục Data > Get Data > From File > From Folder.

Trong hộp thoại Browse, mở thư mục chứa các file lúc nãy bạn đã lưu lại cùng nhau và nhấn Open.

Chọn Combie and tranform data để kết hợp và chuyển đổi các file này lại thành 1 file.

Hộp thoại Combie Files xuất hiện, chọn Sheet 1 là sheet chứa dữ liệu gộp và nhấn OK.

Giao diện Power Query Editor sẽ hiện ra như sau:

Thiết lập lại các cài đặt trong Power Query Editor:

• Xóa các cột bị thừa: click chuột phải > Remove
• Xóa các dòng bị thừ (Null): Chọn các cột chứa null, click vào biểu tượng tam giác ở tiêu đề cột (bộ lọc) > bỏ tích null và nhấn OK

• Đưa tiêu đề lên dòng đầu: tại thẻ Home > Use First Row as Headers
• Xóa tiêu đề bị thừa: chọn bộ lọc bên cạnh một cột bất kỳ > tích bỏ các tiêu đề thừa > OK.

Hoàn thành xong, chúng ta vào thẻ Home > Close & Load để thoát khỏi Power Query Editor và quay trở lại giao diện chính của Excel nhé.

Chúng ta sẽ được file gộp hoàn chỉnh như sau:

Tách dữ liệu thành các cột riêng với Text to Column

Ví dụ bạn đang muốn tách dữ liệu của một ô sang nhiều ô riêng biệt.

Click chọn vùng dữ liệu cần tách, vào menu Data > Text to Column.

Trong hộp thoại Convert Text to Column Wizard, ta thiết lập các cài đặt như sau:

Step 1 of 3: nhấn chọn Delimited > Next.

Step 2 of 3: chọn mục Other và gõ dấu phẩy "," trong ô hoặc chọn Comma > Nhấn Next.

Nếu văn bản của bạn có ký tự ngăn cách khác, bạn hãy bấm chọn ký tự tương ứng. Ví dụ:

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

Step 3 of 3: chọn định dạng dữ liệu là General và nhấn Finish. Chúng ta sẽ được kết quả như sau:

Xóa dữ liệu trùng trong Excel

Giả sử bạn muốn xóa các dữ liệu trùng trong bảng sau đây:

Bôi đen bảng dữ liệu đó, tại thẻ Data > Remove Duplicate

Chọn cột muốn xóa giá trị bị trùng trong hộp thoại Remove Duplicated, sau đó nhấn OK. Ta sẽ thu được kết quả sau:

Thủ thuật Excel nâng cao giúp nhập dữ liệu tự động

Ví dụ chúng ta đang có bảng tổng hợp điểm của học sinh, và bạn muốn điền nhanh cột điểm trung bình cho danh sách này:

Click chọn ô điểm trung bình đầu tiên đã được tính sẵn bởi công thức, sau đó di chuột tới cuối ô bên phải sẽ hiện ra hình vuông màu xanh, bạn hãy nhấn giữ con trỏ chuột và kéo xuống các cô còn lại để sao chép công thức nhé. 

Sử dụng Flash Fill để lặp lại các thao tác

Ví dụ bạn đang cần điền dữ liệu cho cột Họ và cột Tên, cùng với cột Năm và Tháng như hình dưới đây:

Bảng 1: Chúng ta click chọn ô Họ (C3), sau đó nhấn tổ hợp phím Ctrl + E, và làm tương tự với cột Tên.

Bảng 2: Bạn hãy điền năm và tháng cho dòng đầu tiên. (Ví dụ ô G4 là 2005 và H4 là 11). Sau đó nhấn tổ hợp Ctrl + E tương tự như trên.

Cố định cột và dòng với Freeze Panes

Ví dụ bạn đang cần cố định hàng tiêu đề cho bảng sau đây:

Nhấn chọn dòng tiêu đề muốn cố định, vào thẻ View > Freeze Top Row. Tương tự nếu bạn muốn cố định cột thì hãy chọn Free First Column, cố định cả hàng và cột thì chọn Free Panes.

Mở song song 2 file Excel cùng lúc để so sánh 

Nếu bạn muốn mở 2 file Excel song song cùng lúc để so sánh dữ liệu, ta làm như sau:

Tại file đầu tiên, chọn View > Arrange All. Trong hộp thoại Arrange Windows, chọn Vertical, sau đó nhấn OK.

Chúng ta được kết quả

Tạo bảng trong Excel

Ví dụ bạn có vùng dữ liệu sau, yêu cầu hãy tạo bảng cho nó:

Chọn một ô bất kỳ trong vùng dữ liệu > Tại menu Insert > Table.

Tại hộp thoại Create Table, xác nhận lại vùng dữ liệu muốn tạo bảng, tích chọn vào ô My table has headers > nhấn OK.

Ta được bảng dữ liệu sau:

Phân tích dữ liệu Analyze Data

Ví dụ sử dụng Data Analysis để phân tích và thống kê dữ liệu từ bảng sau:

Tại menu Home > Analyze Data. Excel sẽ hiển thị cho bạn các dạng phân tích theo dữ liệu như sau:

Chọn một kiểu phù hợp với yêu cầu > Insert Chart để chèn vào bảng tính Excel.

Tạo danh sách thả xuống Drop list

Ví dụ hãy tạo danh sách thả xuống cho cột Họ và tên trong bảng sau:

Chọn một ô trống bất kỳ trong Excel để tạo Drop list. Sau đó vào thẻ Data > Data Validation.

Chúng ta thiết lập cài đặt cho hộp thoại Data Validation như sau:

• Mục Allow > chọn List
• Chọn vùng dữ liệu muốn hiển thị list ở mục Source > nhấn OK.

Chúng ta có được danh sách thả xuống như sau:

Tạo thông báo xác thực dữ liệu trước khi nhập

Ví dụ: bạn muốn tạo thông báo giá trị nhập phải lớn hơn trong cột khối lượng.

Bước 1: Click chọn vùng cần điền khối lượng > Vào menu Data > Data Validation. 

Bước 2: Tại hộp thoại Validation, trong thẻ Input Message :

• Tích vào ô Show input message when cell is selected
• Title: ghi tiêu đề thông báo
• Input message: viết tiêu đề thông báo.

Bước 3: Nhấn OK để đóng hộp thoại này lại, ta thu được kết quả như sau:

Một số hàm Excel nâng cao

Hàm IF dùng để so sánh logic

Dùng để thực hiện một phép so sánh logic giữa 2 giá trị và trả về một giá trị nếu điều kiện đúng.

Ví dụ: Yêu cầu tính đơn giá các mặt hàng trong bảng dưới đây, biết đơn giá của mặt hàng thuộc nhóm X là 2000đ, còn lại thì giá sẽ là 2500.

Ta có công thức tính đơn giá tại ô E4 như sau:

=IF(C4="X",2000,2500)

Trong công thức trên, hàm IF sẽ so sánh: 

• Nếu giá trị trong ô C4 là "X", thì kết quả sẽ là 2000. 
• Ngược lại, nếu giá trị không phải "X", thì kết quả sẽ là 2500.

Kéo công thức xuống các ô còn lại, ta thu được kết quả sau:

Hàm Countifs đếm dữ liệu có điều kiện

Ví dụ: Đếm các giá trị được yêu cầu dưới đây:

Công thức dùng để đếm các sản phẩm có số tuền lớn hơn 300:

=COUNTIFS($D$3:$D$13,">300")

Công thức đếm các sản phẩm sữa có số tiền lớn hơn 300:

=COUNTIFS($C$3:$C$13,"Milk",$D$3:$D$13,">300")

Công thức đếm các sản phẩm sữa tại cửa hàng MiniMart có số tiền hơn 300:

=COUNTIFS($C$3:$C$13,"Milk",$B$3:$B$13,"MiniMart",$D$3:$D$13,">300")

Hàm Sumifs tính tổng dữ liệu có điều kiện

Ví dụ: Tính tổng doanh số của các sản phẩm thuộc "Loại 1"  trong bảng dưới đây:

Ta nhập công thức sau:

=SUMIFS(C2:C6, B2:B6, "Loại 1")

Trong đó:

• C2:C6 là phạm vi chứa dữ liệu doanh số.
• B2:B6 là phạm vi chứa loại sản phẩm.
• "Loại 1" là điều kiện mà chúng ta muốn kiểm tra.

Hàm Vlookup dò tìm dữ liệu theo đối tượng

Ví dụ: Dò tìm Giới tính, Lý thuyết, Thực hành, Tổng điểm dựa trên Họ và tên đã cho sẵn vào bảng dữ liệu sau:

Công thức dò tìm Giới tính của học sinh có tên Cao Văn Cốt như sau:

=VLOOKUP($H$4,B4:F10,2,0)

Công thức dò điểm Lý thuyết:

=VLOOKUP($H$4,B4:F10,3,0)

Tương tự, chúng ta cũng có thể dễ dàng dò tìm được điểm thực hành và tổng điểm như sau:

Hàm Index & Match tìm kiếm và trả về dữ liệu

Ví dụ: yêu cầu tìm số lượng của mặt hàng có điều kiện như sau:

Ta nhập công thức sau để tính số lượng:

=INDEX($E$4:$E$12,MATCH(1,(I6=B4:B12)*(I7=C4:C12)*(I8=D4:D12),0),1)

Trong đó:

• Hàm MATCH: Tìm vị trí của dòng trong phạm vi dữ liệu mà các điều kiện được thoả mãn.
• Hàm INDEX: Trả về giá trị của ô ở vị trí đã được xác định trong phạm vi dữ liệu.

Ta được kết quả sau:

Sử dụng các hàm cơ bản thì dễ nhưng để xử lý các công việc phức tạp thì bạn cần nhiều hơn thế. Để nắm vững 150 hàm Excel và cách vận dụng chúng trong công việc thực tế, bạn có thể tham khảo khóa học Tuyệt đỉnh Excel của Gitiho. 

Bên cạnh đó, khóa học còn giúp bạn thành thạo Excel từ cơ bản đến nâng cao như trích lọc, định dạng, phân tích dữ liệu, tạo biểu đồ và báo cáo trực quan, từ đó bạn có thể lập kế hoạch và quản lý dự án bằng Excel hiệu quả. 

Đăng ký ngay để học cùng Gitiho bạn nhé!

Thủ thuật Excel nâng cao giúp hiển thị tất cả các công thức

Khi bạn muốn kiểm tra lại tất cả các công thức có trong bảng tính nhưng lại phải tốn thời gian click chọn từng ô một để xem. Có một cách nhanh hơn để hiển thị tất cả các công thức chỉ với 1 vài thao tác:

Tại trang tính muốn hiển thị tất cả công thức, vào thẻ Formulas > Chọn Show Formulas.

Ngoài ra, bạn có thể bấm nhanh phím tắt  Ctrl + ~ để làm điều này.

Lọc dữ liệu với nhiều điều kiện

Sử dụng công cụ lọc Filter

Ví dụ: Lọc các giá trị có điểm Thực hành lớn hơn 7 trong bảng sau:

Bôi đen bảng dữ liệu > Vào thẻ Data > chọn Filter.

Lúc này tại tiêu đề của mỗi cột sẽ hiển thị biểu tượng lọc. Bạn hãy click chuột và bộ lọc của ô Thực hành.

Tiếp theo là vào Number Filters > Greater Than Or Equal To…

Trong hộp thoại Custom AutoFilter, gõ điều kiện mà bạn muốn (điểm thực hành lớn hơn 7)

Nhấn OK để đóng hộp thoại, ta được kết quả như sau:

Dùng Advanced Filter để lọc dữ liệu theo nhiều điều kiện

Ví dụ: Lọc ra dữ liệu trong bảng theo yêu cầu sau:

Đầu tiên, vào menu Data > Advanced. Lúc này hộp thoại Advanced Filter hiện ra, bạn thực hiện cài đặt như sau:

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

Nhấn OK để đóng hộp thoại, ta thu được kết quả như sau:

Bộ lọc dữ liệu Slicer

Ví dụ: Cách dùng Slicer để lọc dữ liệu trong bảng sau:

Click chọn 1 ô bất kỳ trong bảng dữ liệu, vào menu Table Design > chọn Insert Slicer.

Tích chọn cột dữ liệu muốn lọc trong bảng Insert Slicer.

Nhấn OK, slicer sẽ hiển thị trường dữ liệu lọc mà bạn chọn như sau:

Cuối cùng, hãy chọn một hoặc nhiều điều kiện muốn lọc trong hộp Slicer, chúng ta sẽ được bảng lọc như sau:

Phân tích và tóm tắt dữ liệu với PivotTable

Ví dụ: Dùng PivotTable để phân tích và tổng hợp dữ liệu cho bảng tính theo yêu cầu:

Click chọn một ô bất kỳ trong bảng dữ liệu > vào thẻ Insert > chọn PivotTable. Chúng ta sẽ thiết lập cài đặt cho hộp thoại PivotTable như sau:

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

Bảng PivotTable Fields hiện ra, giả sử chúng ta muốn tính số lượng môn học được học sinh đăng ký như sau:

Trên đây là một số thủ thuật Excel nâng cao giúp bạn tự tin giải quyết vấn đề nhanh chóng. Hy vọng những kiến thức này sẽ hữu ích cho công việc của bạn. Chúc bạn thành công!