5 thủ thuật Excel cực hữu ích dân văn phòng nhất định phải biết

Bật mí thủ thuật Excel cực hữu ích dân văn phòng nhất định phải biết để xử lý công việc nhanh, gọn, không tốn công sức thao tác nhiều bước. Hãy cùng chúng mình khám phá ngay nào!

5 thủ thuật Excel cực hay dân văn phòng phải biết

Cách thêm đường viền vào ô bằng cách sử dụng câu lệnh IF trong Excel

Để thêm đường viền xung quanh các ô đã trở nên quá đỗi cơ bản khi bạn sử dụng Excel, nhưng thêm đường viền bằng cách sử dụng hàm IF thì sao? Nghe có vẻ ảo diệu nhưng thao tác này cũng đơn giản thôi.

Như ở ví dụ này, bạn muốn thêm đường viền vào ô nếu nó lớn hơn 450. Việc thêm thủ công sẽ tốn kha khá thời gian, và 1 câu lệnh IF có thể giải quyết vấn đề này. Các bước thực hiện như sau:

1. Chọn phạm vi bạn muốn sử dụng và nhấp vào Home => Conditional Formatting => New Rule.

2. Sau đó, trong hộp thoại New Formatting Rule, chọn Use a formula to determine which cells to format và nhập công thức này =$B2>450 vào Format values where this formula is true.

Trong công thức, B2 là ô đầu tiên của phạm vi bạn muốn thêm đường viền bằng hàm IF.

3. Nhấp vào Format để mở hộp thoại Format Cells, nhấp vào tab Border, sau đó chỉ định kiểu đường viền khi bạn cần.

4. Nhấp OK để đóng hộp thoại. Bây giờ nếu ô lớn hơn 450, ô đó sẽ được thêm đường viền xung quanh.

Lưu ý: Nếu bạn muốn viền các ô với điều kiện dữ liệu trong ô đó là một văn bản cụ thể, chẳng hạn như “Apple”, bạn có thể nhập công thức =$E2="Apple" vào Format values khi dữ liệu đó đúng là văn bản như bạn yêu cầu.

Xem thêm: Cách tô màu trong Excel theo điều kiện bằng Conditional Formatting

Cách thêm đường viền tự động vào ô trong Excel

Bạn đã bao giờ thử thêm tự động đường viền vào trang tính Excel trong khi nhập giá trị vào một hàng chưa? Cách này có thể làm cho công việc của bạn hiệu quả hơn, và bạn có thể áp dụng tính năng Conditional Formatting để thực hiện. Các bước của thao tác là:

1. Chọn phạm vi ô mà bạn muốn thêm đường viền tự động khi bạn nhập giá trị. Trong ví dụ này là A1: F20 chẳng hạn.

2. Từ tab Home, nhấp vào Conditional Formatting > New Rule.

3. Trong hộp thoại New Formatting Rule, hãy nhấp vào Use a formula để xác định ô cần định dạng trong phần Select a Rule Type, sau đó nhập công thức =COUNTA($A1:$F1)>0 (A1 là ô trên cùng bên trái trong phạm vi đã chọn của bạn và F1 là ô trên cùng bên phải của phạm vi đã chọn của bạn).

4. Sau đó nhấp vào nút Format trong hộp thoại New Formatting Rule để chuyển đến hộp thoại Format Cells, nhấp vào tab Border và chọn Outline border.

5. Và sau đó nhấp vào OK để đóng hộp thoại. Bây giờ, khi bạn nhập dữ liệu vào bất kỳ ô nào trong phạm vi đã chọn đã được áp dụng định dạng có điều kiện, các đường viền sẽ tự động được thêm từ Cột A đến Cột F mà bạn đã chọn.

Xem thêm: Hướng dẫn tạo biểu đồ Gantt trong Excel bằng Conditional Formatting

Cách thêm trường được tính toán (Calculated Field) vào bảng tổng hợp (Pivot Table)

Khi đã tạo bảng tổng hợp dựa trên dữ liệu nguồn trên Excel, sẽ có đôi lúc, bạn muốn dữ liệu của bảng tổng hợp có thể thực hiện tính toán để biết thêm thông tin. Trong trường hợp này, tính năng Calculated Field có thể giúp bạn thêm trường tính toán tùy chỉnh như bạn muốn mà không cần thông báo đến dữ liệu nguồn. Chúng ta cùng đến với 1 ví dụ để xem cách thực hiện thao tác này như thế nào.

Giả sử bạn có bảng tổng hợp sau và muốn tính toán doanh số bán hàng trung bình trên mỗi người trong mỗi công ty.

Bạn sẽ cần:

1. Nhấp vào bất kỳ ô nào trong bảng tổng hợp của bạn để hiển thị các tab PivotTable Tools.

2. Sau đó bấm Options => Fields, Items, & Sets => Calculated Field.

3. Trong hộp thoại Insert Calculated Field, hãy nhập tên trường bạn cần vào hộp Name.

4. Tiếp đến, trong hộp Formula, hãy loại bỏ công thức ban đầu và nhập = only, và nhấp vào Sale Price, sau đó nhấp vào nút Insert Field để chèn trường của bạn vào hộp Formula.

5. Sau khi chèn trường Sale Price, hãy nhập dấu hiệu phân chia / và nhấp vào trường Number of People, rồi nhấp vào nút Insert Field, bạn sẽ nhận được trường được tính toán tùy chỉnh của mình.

6. Và sau đó nhấp vào nút Add trong hộp thoại Calculated Field, sau đó nhấp vào OK để đóng hộp thoại và bạn sẽ nhận được một cột mới của trường bán hàng trung bình phía sau dữ liệu ban đầu trong bảng tổng hợp của bạn.

Xem thêm: Hướng dẫn cách tạo Pivot Table trong Excel cho người mới bắt đầu

Cách thêm Check Mark vào ô bằng cách nhấp đúp trong Excel

Thông thường, để chèn check mark vào một ô, bạn cần vào hộp thoại Symbol, tìm ký hiệu đó rồi chèn thủ công. Nhưng không dễ để tìm ra ký hiệu check mark trong hộp thoại Symbol vì có rất nhiều ký hiệu được liệt kê bên trong. Các bước dưới đây (sử dụng VBA) sẽ giúp thao tác đó chỉ còn diễn ra trong “1 nốt nhạc”.

1. Mở trang tính mà bạn cần thêm dấu check mark, sau đó nhấp chuột phải và chọn View Code.

2. Trong cửa sổ Microsoft Visual Basic for Applications, sao chép và dán mã VBA bên dưới vào cửa sổ Code.

1 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
2 If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
3  Application.EnableEvents = False
4 If ActiveCell.Value = ChrW(&H2713) Then
5 ActiveCell.ClearContents
6 Else
7 ActiveCell.Value = ChrW(&H2713)
8 End If
9 Cancel = True
10 End If
11 Application.EnableEvents = True
12 End Sub

Lưu ý: Trong mã, B1: B10 là phạm vi bạn sẽ thêm dấu check mark bằng cách nhấp đúp. Bạn cũng có thể thay đổi thao tác tùy thích.

3. Nhấn các phím Alt + Q để đóng cửa sổ Microsoft Visual Basic for Applications và quay lại trang tính.

Bây giờ, nhấp đúp vào bất kỳ ô nào trong phạm vi B1: B10, dấu check mark sẽ được nhập tự động. Và nhấp đúp vào ô một lần nữa, nó sẽ bị xóa.

Cách thêm màu vào danh sách thả xuống trong Excel

Bạn tạo 1 danh sách thả xuống để việc lọc, đọc dữ liệu dễ dàng hơn và bạn muốn trực quan hóa nó bằng màu sắc (đặc biệt hữu ích với những bảng tính có số lượng dữ liệu, danh sách thả xuống lớn). Bạn cần làm thế nào để làm được điều này, các bước dưới đây sẽ cung cấp thông tin cho bạn:

Đầu tiên, hãy tạo một danh sách thả xuống:

1. Tạo danh sách dữ liệu và chọn một phạm vi mà bạn muốn đặt các giá trị danh sách thả xuống. Như trong ví dụ này là A2: A6 chẳng hạn:

2. Nhấp vào Data => Data Validation => Data Validation

3. Và trong hộp thoại Data Validation, hãy nhấp vào tab Data Validation, chọn tùy chọn List từ danh sách Allow được hiển thị trên màn hình, sau đó nhấp để chọn các giá trị danh sách bạn muốn sử dụng.

4. Sau đó nhấp vào OK

Tiếp theo, chúng ta sẽ sử dụng định dạng điều kiện để tô màu cho danh sách thả xuống:

1. Đánh dấu các ô thả xuống của bạn (đây là cột A) và nhấp vào Home => Conditional Formatting => New Rule.

2. Trong hộp thoại New Formatting Rule, bấm Format only cells that contain trong phần Select a Rule Type, bên dưới phần Format only cells with, chọn Specific Text từ danh sách thả xuống đầu tiên và chọn "containing" ở danh sách thả xuống thứ hai, sau đó nhấp vào nút để chọn giá trị mà bạn muốn định dạng một màu cụ thể.

3. Sau đó nhấp vào nút Format và chọn một màu bạn thích từ tab Fill.

4. Và sau đó nhấp vào OK> OK để đóng hộp thoại, lặp lại các bước từ 5 đến 7 cho từng lựa chọn thả xuống khác, ví dụ: Đào cho màu hồng, Nho cho màu tím…

5. Sau khi thiết lập màu cho các giá trị, khi bạn chọn bất kỳ giá trị nào từ menu thả xuống, ô sẽ tự động được tô màu với màu đã chỉ định của nó.

Xem thêm: Data Validation là gì? Cách quản lý dữ liệu đầu vào với Data Validation

Kết luận

Hãy lưu lại 5 thủ thuật Excel mà chúng mình chia sẻ trong bài viết này để làm việc hiệu quả hơn các bạn nhé. Đừng quên theo dõi Gitiho thường xuyên để cập nhật những kiến thức bổ ích về Excel.

Chúc các bạn học tập hiệu quả!