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ội dung được viết bởi Kim Thu

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.

Gộp nhiều file thành một file trong Excel bước 1

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

Gộp nhiều file thành một file trong Excel bước 2

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.

Gộp nhiều file thành một file trong Excel bước 3

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.

Gộp nhiều file thành một file trong Excel bước 4

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.

Gộp nhiều file thành một file trong Excel bước 5

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

Gộp nhiều file thành một file trong Excel bước 6

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
Gộp nhiều file thành một file trong Excel bước 7
  • Đư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:

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

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.

tách dữ liệu của một ô sang nhiều ô trong Excel bước 1

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

tách dữ liệu của một ô sang nhiều ô trong Excel bước 2

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.

tách dữ liệu của một ô sang nhiều ô trong Excel bước 3

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

tách dữ liệu của một ô sang nhiều ô trong Excel bước 4

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:

tách dữ liệu của một ô sang nhiều ô trong Excel

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:

yêu cầu Xóa dữ liệu trùng trong Excel

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

Xóa dữ liệu trùng trong Excel bước 1

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:

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

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:

Nhập dữ liệu tự động nhanh

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é. 

Nhập dữ liệu tự động

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:

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

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.

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

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.

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

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:

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

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.

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

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.

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

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

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

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ó:

Ví dụ Tạo bảng trong Excel

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.

Cách Tạo bảng trong Excel

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

Tạo bảng trong Excel thành công

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:

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

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:

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

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.

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

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:

Tạo danh sách thả xuống Drop list ví dụ

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

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

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.
Tạo danh sách thả xuống Drop list bước 2

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

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

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.

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

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

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

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.
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

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

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

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.

Hàm IF dùng để so sánh logic ví dụ\

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 IF dùng để so sánh logic

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:

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

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")
Hàm Countifs đếm dữ liệu có điều kiện

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")
Hàm Countifs đếm dữ liệu có điều kiện

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 Countifs đếm dữ liệu có điều kiện

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:

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

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 Sumifs tính tổng dữ liệu có điều kiện

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:

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

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)
Hàm Vlookup dò tìm dữ liệu theo đối tượng

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

=VLOOKUP($H$4,B4:F10,3,0)
Hàm Vlookup dò tìm dữ liệu theo đối tượng

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 Vlookup dò tìm dữ liệu theo đối tượng

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:

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

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:

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

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.

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

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:

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

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

Sử dụng công cụ lọc 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.

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

Tiếp theo là vào Number FiltersGreater Than Or Equal To…

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

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)

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

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

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

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:

Dùng Advanced Filter để lọc dữ liệu

Đầ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ả.
Dùng Advanced Filter để lọc dữ liệu

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

Dùng Advanced Filter để lọc dữ liệu

Bộ lọc dữ liệu Slicer

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

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

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

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

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

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

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

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

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:

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

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:

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

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ả.
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

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:

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

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!

Khóa học phù hợp với bất kỳ ai đang muốn tìm hiểu lại Excel từ con số 0. Giáo án được Gitiho cùng giảng viên thiết kế phù hợp với công việc thực tế tại doanh nghiệp, bài tập thực hành xuyên suốt khóa kèm đáp án và hướng dẫn giải chi tiết. Tham khảo ngay bên dưới!

/5 - ( bình chọn)

/5 - ( bình chọn)

0 thảo luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội
Giấy phép mạng xã hội số: 588, cấp bởi Bộ thông tin và truyền thông