Hiểu cách xây dựng công thức là bước cơ bản trong việc học cách sử dụng Excel. Dưới đây là hướng dẫn sơ lược về cú pháp của các hàm và công thức trong MS Excel.
Mặc dù bạn có thể làm khá nhiều việc chỉ bằng cách sao chép công thức từ các bảng tính khác hoặc chèn các hàm Excel từ menu chuyên dụng, nhưng việc nắm vững ít nhất một kiến thức cơ bản về cách thức hoạt động của cú pháp là một ý kiến tốt. Như vậy, bạn sẽ có thể sửa lỗi hoặc điều chỉnh công thức để tận dụng tối đa bảng tính của mình.
Ý nghĩa của Cú pháp (Syntax) là gì?
Các ngôn ngữ lập trình được thiết kế để cho phép bạn chỉ thị cho máy tính bằng các câu có cấu trúc. Cú pháp đơn giản là những quy tắc của ngôn ngữ có cấu trúc đó, vì máy tính không đủ thông minh để phân tích bất kỳ văn bản nào lệch khỏi tiêu chuẩn mong đợi.
Microsoft Excel, dù không phải là ngôn ngữ lập trình, nhưng vẫn có khả năng chỉ thị chương trình bằng công thức. Và giống như bất kỳ hình thức chỉ thị máy nào khác, nó cũng có cú pháp riêng. Hiểu được cú pháp này là chìa khóa để tận dụng khả năng của ứng dụng bảng tính.
Công thức trong Excel là gì?
Một công thức Excel là một biểu thức có thể được nhập vào một ô thay cho dữ liệu thực tế, sử dụng các phép toán toán học hoặc các hàm khác để tạo ra kết quả. Ví dụ, một công thức có thể được viết để cộng tất cả các số trong một cột và hiển thị kết quả.
Tại sao sử dụng công thức cho việc này? Cuối cùng, bạn có thể tự tính kết quả thủ công và chỉ nhập nó vào trường bắt buộc.
Lợi thế của việc sử dụng công thức thay vì giá trị cứng cho tính toán là nó giúp việc cập nhật bảng tính dễ dàng hơn. Bất kỳ lúc nào bạn thực hiện bất kỳ thay đổi nào trong dữ liệu cơ sở đi vào một phép tính, Excel sẽ tự động tính toán lại kết quả cho bạn.
Điều này có nghĩa là bạn có thể dễ dàng mở rộng một công thức trên hàng trăm và hàng nghìn ô mà không mất độ chính xác. Bạn thậm chí có thể sao chép và dán các công thức này trên các sổ làm việc và để Excel tự động điều chỉnh các tham số để phù hợp với vị trí mới của nó.
Xây dựng một Công thức Cơ bản
Bạn nhập một công thức vào một ô bằng cách bắt đầu bằng dấu bằng và sau đó gõ biểu thức. Ví dụ, gõ điều này sẽ cho bạn tổng của hai con số này:
= 30 + 40
Tất nhiên, thực hiện phép tính trên các số tĩnh như thế này không đặc biệt hữu ích. Để cộng các số chứa trong một ô, bạn phải sử dụng tham chiếu ô.
Giống như một ô trên bàn cờ, các ô của bảng tính có thể được tham chiếu bằng kết hợp vị trí chữ cái và số của chúng. Số thứ tự hàng số tăng khi bạn đi xuống bảng tính, trong khi số cột chữ cái tiến về bên phải.
Bạn có thể đã nhận ra vấn đề rõ ràng với việc này – chữ cái hết nhanh chóng, vậy cột sau Z thì sao? Khi đó chúng ta chỉ cần kết hợp chữ cái, cho chúng ta AA, AB, AC, và cứ thế.
Sử dụng tham chiếu này, bạn có thể sử dụng giá trị của bất kỳ ô nào trong bảng tính trong một công thức. Như sau:
= A1 + A2
Lưu ý rằng khi bạn nhấn Enter, công thức sẽ chỉ xuất hiện trong thanh công thức (formula bar) – chính ô đó hiển thị giá trị kết quả. Vì vậy, bạn cần chọn một ô để xác định xem giá trị của nó có được dẫn xuất từ công thức hay được nhập thủ công vào đó.
Đó là tất cả những gì bạn cần để tạo ra các công thức cơ bản tự thực hiện tính toán. Phần tốt nhất là nếu bạn sửa đổi bất kỳ ô nào được tham chiếu, kết quả sẽ tự động cập nhật.
Giới thiệu về Hàm (Functions)
Tham chiếu ô và phép toán cơ bản có thể đưa bạn đi xa, nhưng chúng vẫn còn nhiều điểm chưa như ý. Việc viết công thức để cộng hàng trăm ô có thể rất nhàm chán, ví dụ, ngay cả khi bạn biết các tham chiếu.
Đó là nơi các hàm xuất hiện. Giống như bất kỳ ngôn ngữ lập trình nào, Excel bao gồm một loạt các hàm tích hợp sẵn có thể thực hiện các tác vụ phức tạp với cú pháp tương đối đơn giản.
Các hàm được gọi bằng tên của chúng theo sau là bất kỳ đối số nào được đặt trong ngoặc vuông (hoặc ngoặc đơn, như chúng được gọi chính xác). Ví dụ, bạn có thể sử dụng hàm SUM để dễ dàng cộng một số lượng lớn ô:
Để rút gọn công thức hơn nữa, chúng ta có thể chỉ định một phạm vi (range) thay vì nhập tham chiếu của từng ô. Ví dụ, bạn có thể gõ A1:A4 để bao gồm toàn bộ phạm vi ô từ A1 đến A4. Điều này có thể mở rộng trên một hàng cũng như bao gồm bao nhiêu ô tùy ý.
SUM không phải là hàm duy nhất bạn có sẵn. Danh sách đầy đủ có thể được lấy bằng cách nhấp vào ký hiệu ƒ𝑥 bên cạnh thanh công thức, nhưng đây là một vài hàm phổ biến hơn:
- AVERAGE – Một hàm cơ bản khác, hàm AVERAGE trả về trung bình của phạm vi giá trị được nhập vào nó. Rất hữu ích cho tính toán tài chính.
- CONCATENATE – Hàm này hoạt động với văn bản thay vì số. Với CONCATENATE, bạn có thể nối hai chuỗi lại với nhau và xuất ra giá trị cuối cùng. Hữu ích để tạo văn bản với các toán tử điều kiện và nối chúng cho kết quả cuối cùng.
- LEN – Viết tắt của Length (Độ dài), LEN đơn giản đếm số ký tự trong một chuỗi cho trước, bao gồm cả khoảng trắng. Rất tình huống trong việc sử dụng (như tìm ra xem ô trống có thực sự chứa khoảng trắng không) nhưng tốt hơn là đếm ký tự thủ công.
- TRIM – Việc vô tình nhập thêm khoảng trắng trong một số ô là khá phổ biến, làm sai kết quả của một số phép tính hoặc các hoạt động CONCATENATE. Hàm TRIM có thể được sử dụng để loại bỏ tất cả các khoảng trắng thừa, chỉ để lại một khoảng trắng giữa mỗi từ.
Các Hàm Logic
Cho đến nay chúng ta đã thấy một loạt các hàm khác nhau thực hiện nhiều tác vụ hữu ích như SUM, AVERAGE hoặc TRIM. Nhưng các so sánh logic thì sao?
Giống như bất kỳ ngôn ngữ lập trình nào khác, cú pháp của Excel bao gồm các toán tử logic như IF, AND, cũng như OR. Hữu ích nhất là hàm IF, trong khi hai hàm kia thường được sử dụng trong một biểu thức logic.
Cú pháp của IF đơn giản: một biểu thức logic đánh giá thành TRUE hoặc FALSE, theo sau là các giá trị sẽ được thực hiện trong mỗi điều kiện được đặt theo trình tự. Tất nhiên, tất cả những điều này đều nằm trong ngoặc và được phân tách bằng dấu phẩy. Trông giống như thế này:
Lưu ý rằng đối số cuối cùng có thể bị bỏ qua, trong trường hợp hàm sẽ chỉ trả về giá trị nếu điều kiện đúng. Điều này có thể được sử dụng để xây dựng các công thức phức tạp bằng cách có điều kiện bao gồm các giá trị trong một phép tính.
Đối số đầu tiên, mặt khác, có thể nhận nhiều hình thức. Một biểu thức logic có thể đơn giản là so sánh hai giá trị bằng các ký hiệu như "<" hoặc "=", nhưng nó cũng có thể là sự kết hợp của nhiều câu lệnh như vậy bằng cách sử dụng AND hoặc OR.
Điều này là vì cả AND và OR chỉ trả về TRUE hoặc FALSE, thay vì bất kỳ chuỗi văn bản hoặc giá trị số nào. Bạn sử dụng các hàm này bằng cách bao gồm hai biểu thức logic làm đối số; AND trả về TRUE nếu cả hai đối số đều đánh giá thành TRUE, trong khi OR trả về TRUE ngay cả khi chỉ một trong chúng là TRUE. Ngược lại, FALSE được trả về.
Sử dụng IF, AND và OR, có thể xây dựng các điều kiện lồng nhau rất phức tạp đánh giá một loạt các giá trị khác nhau trước khi cuối cùng đưa ra kết quả. Tuy nhiên, các công thức như vậy thường dễ bị lỗi do tất cả các ngoặc và dấu phẩy liên quan. Tốt hơn là tách biệt các phép tính và giữ mọi thứ đơn giản.
Để đơn giản hóa thêm, Excel bao gồm một vài hàm kết hợp IF với các hoạt động hữu ích khác như SUM hoặc AVERAGE.
Sao chép Công thức
Thường xuyên bạn cần thực hiện cùng loại tính toán trên các phạm vi ô khác nhau trong một trang tính, hiển thị kết quả trong các ô riêng biệt. Bạn có phải nhập thủ công cùng một công thức lại và lại, thay đổi tham chiếu ô mỗi lần không?
Không, bạn chỉ cần sao chép chúng.
Khi bạn thử dán một ô đã sao chép trong Excel (bằng cách nhấp chuột phải vào ô đích), bạn sẽ được trình bày với nhiều tùy chọn Dán (Paste). Bạn có thể dán hoàn toàn các ô, sao chép chỉ giá trị, hoặc thậm chí dán chỉ công thức.
Tốt hơn nữa, công thức được dán được sửa đổi để phản ánh tham chiếu tương đối theo vị trí mới của nó. Nếu bạn sao chép một công thức tính tổng các giá trị của cột A và dán nó vào một ô khác, nó sẽ cập nhật các tham chiếu với số cột mới.
Bạn cần biết gì về Cú pháp hàm Excel?
Là người mới bắt đầu, đừng lo lắng về việc cố gắng ghi nhớ mọi hàm Excel có mặt. Hầu hết chúng hiếm khi hữu ích, và bạn có thể tìm thấy cú pháp của chúng bằng cách nhấp vào nút ƒ𝑥 bên cạnh thanh công thức.
Chỉ cần nhớ cú pháp cơ bản của công thức Excel – dấu bằng theo sau là một biểu thức có thể bao gồm nhiều hàm. Mỗi hàm nhận một số đối số (mặc dù một số hàm không có) và trả về một giá trị có thể được sử dụng trong một hàm hoặc phép tính khác.
Mặc dù lồng các hàm như vậy có thể trở nên phức tạp khá nhanh, khiến bạn lạc lối trong một mớ ngoặc bị thiếu. Chúng tôi khuyên bạn nên giữ mọi công thức đơn giản và rõ ràng, kéo dài một phép tính phức tạp trên nhiều ô để dễ dàng quản lý.