Hiểu cách xây dựng công thức là một bước cơ bản trong việc học cách sử dụng Excel. Đây là phầ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ể tiến xa 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 bạn nên có ít nhất kiến thức cơ bản về cách thức hoạt động thực sự của cú pháp. Bằng cách đó, bạn sẽ có thể sửa bất kỳ lỗi nào hoặc điều chỉnh các công thức để tận dụng tối đa bảng tính của mình.
Nội dung bài viết
Ý nghĩa của cú pháp là gì?
Ngôn ngữ lập trình được thiết kế để cho phép bạn hướng dẫn máy tính bằng các câu có cấu trúc. Cú pháp chỉ đơn giản đề cập đến các quy tắc của ngôn ngữ có cấu trúc như vậy, vì máy tính không đủ thông minh để phân tích bất kỳ văn bản nào khác với tiêu chuẩn mong đợi.
Microsoft Excel tuy không phải là ngôn ngữ lập trình nhưng vẫn có khả năng hướng dẫn chương trình bằng các công thức. Và cũng giống như bất kỳ hình thức hướng dẫn máy nào khác, điều này có cú pháp riêng. Hiểu cú pháp này là chìa khóa để tận dụng các khả năng của ứng dụng bảng tính.
Công thức trong Excel là gì?
Công thức Excel là một biểu thức có thể được nhập vào ô thay cho dữ liệu thực tế, sử dụng các phép toán 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 một công thức cho việc này? Sau cùng, bạn có thể tự tính toán kết quả theo cách thủ công và chỉ cần nhập kết quả vào trường bắt buộc.
Ưu điểm của việc sử dụng công thức thay vì giá trị cứng để tính toán là điều này giúp việc cập nhật bảng tính dễ dàng hơn. Bất cứ khi nào bạn thực hiện bất kỳ thay đổi nào trong dữ liệu cơ bản dùng để tính toán, 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 quy mô công thức trên hàng trăm, hàng nghìn ô mà không làm mất đi độ 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ố cho 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 công thức vào một ô bằng cách bắt đầu bằng dấu bằng, sau đó nhập biểu thức. Ví dụ: gõ cái này sẽ cho bạn tổng của hai số sau:
= 30 + 40
Tất nhiên, việc thực hiện các 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ố có trong một ô, bạn phải sử dụng tham chiếu ô.
Giống như một hình vuông trên bàn cờ, các ô của bảng tính có thể được tham chiếu bằng cách kết hợp vị trí chữ cái và số của chúng. Số hàng số tăng lên khi bạn đi xuống bảng tính, trong khi số cột theo thứ tự bảng chữ cái tiến về phía bên phải.
Bạn có thể đã phát hiện ra vấn đề rõ ràng với điều này – bảng chữ cái hết khá nhanh, vậy còn columns sau Z? Sau đó, chúng ta bắt đầu kết hợp các bảng chữ cái để tạo ra AA, AB, AC, v.v.
Bằng cách 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 công thức. Như thế này:
= A1 + A2
Lưu ý rằng một khi bạn nhấn Enter, công thức sẽ chỉ xuất hiện trong thanh công thức – chính ô đó sẽ 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ó nguồn gốc từ mộ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 có khả năng tự thực hiện các phép tính. Điều tuyệt vời nhất là nếu bạn sửa đổi bất kỳ ô nào đang được tham chiếu, kết quả sẽ tự cập nhật.
Giới thiệu về hàm
Tham chiếu ô và số học cơ bản có thể đưa bạn đi xa nhưng chúng vẫn còn nhiều điều chưa được mong đợi. Chẳng hạn, việc viết các công thức tính tổng hàng trăm ô có thể rất tẻ nhạt, ngay cả khi bạn biết các tham chiếu.
Đó là lúc 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 dựng 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 theo tên của chúng, theo sau là bất kỳ đối số nào được đặt trong dấu ngoặc đơn (hoặc dấu ngoặc đơn, vì chúng được gọi đúng). Ví dụ: bạn có thể sử dụng hàm SUM để cộng một số lượng lớn ô một cách dễ dàng:
Để rút ngắn công thức hơn nữa, chúng ta có thể chỉ định một phạm vi thay vì nhập tham chiếu của mỗi ô. Ví dụ: bạn có thể nhập A1:A4 để bao gồm toàn bộ phạm vi ô từ A1 đến A4. Điều này cũng có thể được mở rộng trên một hàng và bao gồm bao nhiêu ô tùy thích.
SUM không phải là chức năng duy nhất bạn có sẵn. Bạn có thể lấy danh sách đầy đủ bằng cách nhấp vào biểu tượng ƒ𝑥 bên cạnh thanh công thức, nhưng đây là một số danh sách phổ biến hơn:
- TRUNG BÌNH – Một hàm cơ bản khác là hàm AVERAGE trả về giá trị trung bình của dải giá trị được nhập vào trong đó. Rất hữu ích cho việc tính toán tài chính.
- NỐI – Cái này hoạt động với văn bản chứ không phải 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 khi tạo văn bản với các toán tử có điều kiện và nối chúng để có kết quả cuối cùng.
- LEN – Dạng rút gọn của Độ dài, LEN chỉ đơn giản là đếm số ký tự trong một chuỗi cho trước, bao gồm cả khoảng trắng. Cách sử dụng của nó rất tùy tình huống, (như tìm hiểu xem một ô trống có thực sự có khoảng trắng hay không) nhưng tốt hơn là đếm các ký tự theo cách thủ công.
- TRIM – Việc vô tình nhập thêm dấu cách vào một số ô là khá phổ biến, làm sai lệch kết quả của một số phép tính hoặc thao tác 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 duy nhất giữa mỗi từ.
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òn những so sánh logic thì sao?
Cũng 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, hai hàm còn lại thường được sử dụng trong biểu thức logic.
Cú pháp của IF rất đơn giản: một biểu thức logic đánh giá là TRUE hoặc FALSE, theo sau là các giá trị cần theo sau trong một trong hai điều kiện được đặt theo trình tự. Tất nhiên, tất cả những điều này nằm trong dấu ngoặc và được phân tách bằng dấu phẩy. Một cái gì đó như thế này:
Lưu ý rằng đối số cuối cùng có thể được 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 đưa các giá trị vào một phép tính có điều kiện.
Mặt khác, lập luận đầu tiên có thể có nhiều dạng. Một biểu thức logic có thể đơn giản là so sánh hai giá trị bằng cách sử dụ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à do cả AND và OR chỉ trả về TRUE hoặc FALSE chứ không phải 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 đưa vào hai biểu thức logic làm đối số; AND trả về TRUE nếu cả hai đối số đều có giá trị TRUE, trong khi OR trả về TRUE ngay cả khi một trong số chúng là TRUE. Ngược lại, FALSE sẽ đượ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 đưa ra kết quả cuối cùng. Mặc dù những công thức như vậy thường dễ mắc lỗi do có nhiều dấu ngoặc và dấu phẩy liên quan. Tốt hơn là nên tách các tính toán ra và giữ mọi thứ đơn giản.
Để đơn giản hóa mọi thứ hơn nữa, Excel bao gồm một số hàm kết hợp IF với các phép toán hữu ích khác như SUM hoặc AVERAGE.
Sao chép công thức
Thông thường, bạn cần thực hiện cùng một loại phép tính 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 đi nhập lại cùng một công thức theo cách thủ công, thay đổi tham chiếu ô mỗi lần không?
Không, bạn chỉ có thể 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ẽ thấy nhiều tùy chọn Dán. Bạn có thể dán toàn bộ các ô, chỉ sao chép các giá trị hoặc thậm chí chỉ dán công thức.
Tốt hơn nữa, công thứ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 bằng 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 bận tâm đến việc cố gắng ghi nhớ mọi hàm Excel hiện có. 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 ghi nhớ cú pháp Excel cơ bản của công thức – 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ị mà sau đó có thể được sử dụng trong hàm hoặc phép tính khác.
Mặc dù các hàm lồng nhau như thế này có thể trở nên phức tạp khá nhanh, khiến bạn rơi vào tình trạng thiếu dấu ngoặc lộn xộn. Chúng tôi khuyên bạn nên giữ mọi công thức đơn giản và dễ hiểu, kéo dài một phép tính phức tạp trên nhiều ô để giúp mọi thứ có thể quản lý được.
