Các hàm trong Excel (Phần 2): Một số kỹ thuật sử dụng hàm trong Excel

Ở bài viết phần 1, bạn đọc đã được hướng dẫn về các hàm trong Excel cũng như cách viết hàm. Bài viết này, cách cố định tọa độ và các kỹ thuật viết hàm sẽ được giới thiệu.

Cố định tọa độ trong công thức hàm trong Excel

Bài viết này tiếp tục ứng dụng ví dụ ở bài viết phần 1.

Hàm trong Excel có đặc điểm rất thú vị chính là khi bạn sử dụng tọa độ trong công thức hàm, bạn hoàn toàn có thể copy (sao chép) công thức phía trên xuống các dòng ngay bên dưới, thông qua thao tác đặt chuột vào vị trí góc bên phải dưới cùng của ô tính và giữ chuột + kéo xuống các ô dưới.

Xảy ra hiện tượng như sau:

• Khi bấm vào ô đầu tiên (C2), công thức nếu B2=E2, điều này hoàn toàn hợp lý
• Nhưng bấm vào xem công thức ở ô thứ C3, C4, C5 thì công thức bị sai, ở chỗ tham chiếu lần lượt với ô E3, E4, E5, khiến kết quả bị sai
• Đúng ra công thức luôn phải so sánh cột B với ô E2, cho dù tọa độ có thay đổi là B2, B3 hay B4,5

Làm thế nào để ô E2 không thay đổi trong công thức hàm trong Excel khi kéo công thức từ trên xuống dưới?

Xem thêm: Các hàm trong Excel (Phần 1): Cách viết hàm và nạp giá trị hàm

Cách khắc phục:

Bôi đen ô E2 trong công thức đầu tiên được lập, sau đó nhấn phím F4

Lúc này công thức xuất hiện có thêm ký hiệu $, nó có nghĩa là khi bạn đặt dấu $ vào trước vị trí nào thì sẽ cố định tọa độ đó.

Ví dụ: $E$2, thì cột E được cố định, đồng thời dòng 2 cũng được cố định. Và bạn có thể hiểu rằng, công thức đang cố định ô E2. Ô E2 này sẽ không thay đổi khi sao chép công thức.

Bạn thực hiện kéo công thức xuống các ô phía dưới sẽ thấy tọa độ côt B thay đổi nhưng ô E2 sẽ được giữ nguyên trong công thức.

Bạn đặt $ trước cột thì sẽ cố định cột, đặt $ trước dòng thì sẽ cố định dòng.

Thao tác cố định công thức như sau:

• Bôi đen tọa độ cần cố định trong công thức, sau đó nhấn phím F4, bạn sẽ có được kết quả là $E$2
• Bấm tiếp phím F4 một lần nữa, bạn sẽ có được E$2, lúc này chỉ có dòng được cố định
• Bấm tiếp một lần nữa phím F4, bạn sẽ có $E2, chỉ có cột được cố định, nhưng không cố định dòng
• Bấm tiếp F4 thêm một lần nữa, tọa độ này sẽ được loại bỏ hoàn toàn dấu cố định, chỉ còn lại E2 như ban đầu

Xem thêm: Hướng dẫn cách cố định các đối tượng với hàng, cột trong Excel

Kỹ thuật viết các hàm trong Excel sao cho chính xác

Làm sao để viết công thức hàm trong Excel mà hệ thống nhận định đúng hàm đó?

Ví dụ: Bạn muốn tìm hàm bắt đầu bằng chữ V

Các thao tác khi viết hàm như sau:

• Nhập dấu = trước tiên
• Tiếp tục nhập chữ V, trong hàm về chữ V được Excel gợi ý cho bạn phần bên dưới, lúc đó bạn sẽ chọn hàm mà bạn đang tìm kiếm

• Bạn muốn chọn 1 hàm phía dưới, hãy nhấn mũi tên đi xuống. Giả sử lựa chọn hàm VLOOKUP, bạn có thể thực hiện mong muốn này bằng 2 cách:

1. Nháy đúp chuột, tuy nhiên việc này hơi mất thời gian, bởi bạn đang sử dụng bàn phím để thao tác

2. Sử dụng phím TAB, ngay lập tức Excel sẽ lấy tên hàm mà vị trí chuột đang đặt ở đó

Với cách viết hàm trong Excel thông thường, bạn hay nhập luôn tên hàm và quên mất dấu mở ngoặc đằng sau, dẫn đến Excel không nhận diện tên hàm. Còn với phím TAB, Excel sẽ tự động hiển thị tên hàm kèm dấu mở ngoặc để bạn nhập tham chiếu trong nội dung hàm.

Nếu để ý xuống gợi ý bên dưới mà Excel tự động hiển thị cho người dùng như sau:

Điều này thuận tiện cho bạn đọc khi không cần nhớ hết cách viết hay điều kiện của từng hàm, chỉ cần dựa vào gợi ý này cũng có thể đoán được hàm này mong muốn được viết như thế nào, cú pháp đầy đủ là gì, gồm bao nhiêu thành phần.

Xem thêm: Các hàm trong Excel không cần có đối số truyền vào

Thành phần bắt buộc và không bắt buộc trong công thức hàm trong Excel

Hàm VLOOKUP ở đây sẽ có thành phần:

• lookup_value sau đó là dấu phẩy (,), là dấu để ngăn cách các thành phần trong hàm
• Theo sau là thành phần thứ 2 (table _array) rồi lại đến dấu phẩy (,) và đến thành phần thứ 3 (col_index_num)
• Tiếp đến lại là dấu phẩy (,) và theo sau là thành phần thứ 4 (range_lookup) được đặt trong dấu ngoặc vuông, thể hiện đối tượng không bắt buộc phải nhập trong hàm.

Tức là, hàm này có 4 thành phần, nhưng 3 thành phần không nằm trong dấu ngoặc vuông là 3 thành phần bắt buộc phải nhập. Sau đó, thành phần cuối cùng mà nằm trong dấu ngoặc vuông là thành phần không bắt buộc phải nhập. Bạn nhập cũng được mà không nhập cũng không ảnh hưởng gì cả. Nó sẽ hoạt động theo cơ chế mặc định, có sẵn, giúp cho hàm hoạt động bình thường. Do đó, bạn nên để ý các thành phần trong hàm để nhập hàm sao cho đúng. 

Xem thêm: Cách dùng hàm VLOOKUP trong Excel lọc dữ liệu trùng nhau

Dấu ngăn cách thành phần trong công thức hàm trong Excel

Dấu ngăn cách ở đây là dấu phẩy. Nhưng trong một số trường hợp, dấu này sẽ là dấu chấm phẩy. Trường hợp này xảy ra khi bạn thay đổi thiết lập trong Control Panel > Clock and Region > Region > Additional Setting > mục Number > Decimal Symbol:

Khi đó, công thức gợi ý trong Excel sử dụng phần ngăn cách là dấu chấm phẩy

Còn nếu như bạn sử dụng dạng mặc định của Excel (dấu chấm để ngăn cách phần thập phân trong số thập phân) thì dấu phẩy sẽ để ngăn cách các thành phần trong hàm.

Lưu ý: Phần này tuy hơi rắc rối 1 chút, nhưng các bạn cần để ý một chút cách cài đặt này, thì sẽ thuận tiện hơn rất nhiều trong khi viết các báo cáo trên Excel. Khi thiết lập trong Control Panel rồi, hãy ghi nhớ để thống nhất trong công thức sau này, khi copy từ trên mạng, tài liệu khác về.

Cách viết thành phần của các hàm trong Excel phức tạp

Trường hợp bạn viết nhiều hàm phức tạp. Ví dụ như hàm IF, có 3 thành phần với đề bài sau:

Yêu cầu: Nếu hôm này là chủ nhật thì nghỉ, còn không thì đi làm

Công thức hàm: 

=IF(TODAY()="Chủ nhật", "Nghỉ", "Đi làm")

Trong đó

• TODAY(): Là hàm xét xem ngày hôm nay, có phải là chủ nhật không. (Xem thêm về hàm TODAY)

Lưu ý:

1. Sau mỗi dấu phẩy ngăn cách các thành phần, bạn nên dùng khoảng cách (Space) để ngăn cách sao cho công thức hàm dễ nhìn hơn, tránh nhầm lẫn.
2. Ngoài ra, bạn có thể sử dụng phương pháp xuống dòng trong ô bằng phím Alt + Enter: Sau mỗi thành phần trong hàm, bạn sử dụng phím Alt + Enter để xuống dòng, và khi đó hàm IF được tách thành 3 dòng như sau:

Khi làm như vậy, cho dù thành phần trong hàm rất là dài, nhưng đã được tách ra nhiều dòng, thì bạn hoàn toàn có thể nhập các nội dung hàm như vậy để phân tách ý nghĩa và bám sát theo yêu cầu đề bài hơn, so với việc viết công thức hàm trên cùng 1 dòng.

3. Và khi lồng các hàm vào với nhau, các bạn cũng có thể tránh tình huống nhầm lẫn các thành phần bên trong hàm.

Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết dễ hiểu nhất

Thiết lập chế độ tính toán trong Excel

Giả sử bạn sử dụng hàm SUM như ví dụ dưới đây:

Nếu bạn thay số 5,7 bằng bất kỳ số nào khác, Excel cũng tự động thay đổi kết quả cho bạn. Nhưng đôi khi, hàm sẽ không tự động cho ra kết quả luôn. Vậy thì bạn phải làm thế nào?

Bạn vào Formulas > mục Calculation. Tại Calculation Options có hiển thị 3 chế độ tính toán:

• Automatic: Tính toán tự động (Cơ chế mặc định của Excel). Nếu hiển thị dấu tick ở mục này, tức là Excel của bạn đang ở cơ chế này. Khi bạn thay đổi bất kỳ một nội dung nào ở trong Excel, thì toàn bộ công thức trong file đó sẽ được tính toán lại, cho bạn kết quả mới.
• Manual: Khi thay đổi thành phần tính toán, kết quả sẽ không được tự động tính toán lại. Chỉ khi bạn ấn phím F9 hoặc chuyển về chế độ Automatic, kết quả mới được tính toán mới lại. Nó áp dụng trong trường hợp bảng tính của bạn lớn có chứa nhiều dữ liệu tính toán, file nặng và tốc độ tính toán chậm. Mỗi khi bạn thay đổi dữ liệu, để tránh chờ đợi Excel tự tính toán, mất thời gian, bạn nên chọn chế độ Manual, và khi thay đổi dữ liệu xong mới chuyển về Automatic hay nhấm F9 để được kết quả đúng.
• Automatic Except for Data Tables: Tính toán tự động ngoại trừ bảng dữ liệu.

Xem thêm: Tìm hiểu về cách tự tạo hàm Excel trong VBA

Kết luận

Hy vọng bài viết này đã đem đến cho các bạn cái nhìn đầu tiên về kỹ thuật sử dụng cũng như viết các hàm trong Excel, cách thiết lập chế độ tính toán trong Excel. Đừng chần chừ đăng ký khóa học Tuyệt đỉnh Excel để làm chủ các kỹ năng tin học văn phòng để tự tin khi làm việc, tối ưu hóa thời gian cũng như được lựa chọn trong các kỳ tuyển dụng việc làm tốt nhé.