Công thức trong Excel, ví dụ và mẹo để tiết kiệm thời gian

Trong bài viết này, Học Excel Online sẽ hướng dẫn những điều cơ bản về tạo và sử dụng các công thức trong Excel. Và bởi vì một trong những cách hiệu quả nhất để học là thông qua thực hành, chúng tôi cũng sẽ thảo luận về một số công thức ví dụ để làm cho mọi việc trở nên dễ hiểu hơn.

Trong MS Excel, công thức là các phương trình thực hiện các tính toán khác nhau trong các bảng tính. Mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, khái niệm công thức bảng tính Excel vẫn giống nhau trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn.

• Tất cả các công thức Excel bắt đầu với một dấu bằng (=).
• Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm. Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:
  • Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5
  • Sử dụng hàm SUM: = SUM (B1: B5)
• Nhấn phím Enter để hoàn thành công thức. Làm xong!

Khi bạn tạo một công thức trong Excel, bạn có thể sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc tất cả các phần sau: ·

• Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·
• Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel của bạn, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. Ví dụ, để tổng hợp giá trị trong tất cả các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·
• Tên –  được xác định cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name). ·
• Các hàm – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·
• Toán tử – các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được thực hiện.

Các toán tử trong các bảng tính Excel

Để nói với Microsoft Excel loại công việc bạn muốn thực hiện trong một công thức, bạn sử dụng các ký hiệu đặc biệt được gọi là các toán tử. Có 4 loại toán tử trong Excel:

• Số học – để thực hiện các phép toán cơ bản.
• So sánh (hợp lý) – để so sánh các giá trị.
• Kết nối – để kết hợp các giá trị văn bản vào một chuỗi duy nhất.
• Tham chiếu – để tạo ra các cơn thịnh nộ và các đối số riêng biệt trong các hàm của Excel.

Sử dụng toán tử số học trong công thức Excel

Các toán tử này được sử dụng để thực hiện các phép toán cơ bản như cộng, trừ, nhân và chia.

Ví dụ: nếu bạn có giá cả trong ô A2 và VAT trong ô B2, bạn có thể tính số tiền thuế GTGT bằng cách sử dụng công thức tỷ lệ phần trăm sau: = A2 * B2

Các toán tử so sánh trong các công thức Excel

Trong các công thức Microsoft Excel, so sánh, hoặc hợp lý, toán tử được sử dụng để so sánh hai giá trị. Kết quả của việc so sánh luôn là một giá trị logic của TRUE hoặc FALSE. Các toán tử logic sau có sẵn trong Excel:

Ví dụ, công thức = A1 = B1 trả về TRUE nếu các ô A1 và B1 có cùng một giá trị (số, văn bản hoặc ngày tháng), FALSE nếu không.

Toán tử nối văn bản

Toán tử nối văn bản trong Excel là ký hiệu và (&). Bạn có thể sử dụng nó để kết hợp hai hoặc nhiều chuỗi văn bản trong một chuỗi duy nhất.

Ví dụ: nếu bạn có mã quốc gia trong cột A và số điện thoại trong cột B, bạn có thể sử dụng công thức sau để nhận số điện thoại kết hợp với mã quốc gia:

= A1 & “” & B1

Trong công thức trên, chúng ta thêm một khoảng trống vào giữa để làm cho các con số dễ đọc hơn:

Kết quả tương tự có thể đạt được bằng cách sử dụng chức năng CONCATENATE.

Các toán tử tham chiếu trong các công thức Excel và các hàm

Để cung cấp vùng dữ liệu cho MS Excel công thức và đối số riêng biệt trong hàm Excel, các toán tử sau đây được sử dụng.

Dấu hai chấm (:) – nó là một toán tử phạm vi cho phép bạn tạo một tham chiếu cho nhiều ô nằm giữa 2 ô bạn chỉ định.

Ví dụ, phạm vi A1: A00 bao gồm 100 ô từ A1 đến A100. Để tìm trung bình của 100 ô này, bạn sử dụng công thức sau: = AVERAGE (A1: A00)

Bạn cũng có thể tham khảo toàn bộ cột (A: A) hoặc toàn bộ hàng (1: 1). Ví dụ: công thức sau tìm tổng số tất cả các số trong cột A: = SUM (A: A).

Dấu phẩy (,) – được sử dụng để phân biệt đối số trong các công thức bảng tính Excel. Ví dụ, công thức = IF (A1> 0, “tốt”, “xấu”) đọc như sau: nếu A1 lớn hơn không, trả lại “tốt”, nếu không “xấu”.

Chú thích. Dấu phẩy là dấu phân cách mặc định ở Bắc Mỹ và một số nước khác. Tại các quốc gia Châu Âu, dấu phẩy được giữ như Biểu tượng thập phân và dấu phân cách thường được đặt thành dấu chấm phẩy (;). Trong trường hợp này, bạn cần phải tách biệt đối số với các dấu chấm phẩy, ví dụ: = IF (A1> 0, “tốt”, “xấu”).

Vì vậy, nếu bạn đang cố gắng tạo ra một công thức trong bảng tính của bạn, nhưng Excel không chấp nhận nó và đưa ra lỗi “formula invalid formula”, hãy chuyển tới Regional Settings (Control Panel> Region and Language> Additional Settings) và kiểm tra ký hiệu nào được đặt là dấu phân cách ở đó. Đó là dấu mà bạn cần sử dụng để phân biệt đối số trong công thức Excel của bạn.

Khoảng trắng – nó là một toán tử giao lộ cho phép bạn lấy được các ô tương ứng với hai tham chiếu mà bạn chỉ định. Ví dụ: nếu bạn có một danh sách các mục trong cột A và một số dữ liệu có liên quan trong các cột khác, bạn có thể nhận được giá trị tại giao điểm của một cột và hàng cho trước bằng cách sử dụng công thức như sau: = B3: D3 C2: C4

Công thức mà bạn tạo trong bảng tính Excel của bạn có thể đơn giản hoặc phức tạp:

• Các công thức Excel đơn giản chỉ thực hiện một thao tác toán học, ví dụ = 10 * 5 hoặc = SUM (A1: A10)
• Các công thức Excel phức tạp (tiên tiến) bao gồm nhiều tính toán, ví dụ = 10 * 5 + 20 hoặc = SUM (A1: A10) / 2

Hơn nữa trong hướng dẫn này, bạn sẽ tìm thấy các bước chi tiết để tạo cả hai loại công thức bảng tính Excel.

Cách tạo các công thức trong Excel

Như đã đề cập, bất kỳ công thức Excel nào bắt đầu với dấu bằng (=). Vì vậy, bất cứ công thức nào bạn sẽ viết, bắt đầu bằng cách gõ = trong ô chỉ định hoặc trong thanh công thức Excel. Và bây giờ, chúng ta hãy cùng xem xét cách bạn có thể tạo các công thức khác nhau trong Excel.

Làm thế nào để tạo các công thức đơn giản trong Excel

Mặc dù các công thức Excel đơn giản chỉ thực hiện một phép tính, chúng có thể thực hiện theo nhiều cách khác nhau. Bạn có thể cung cấp dữ liệu nguồn dưới dạng hằng số, tham chiếu ô, hoặc các tên được xác định, và thực hiện tính toán bằng cách sử dụng các toán tử toán học hoặc các hàm Excel.

Cách tạo các công thức tiên tiến trong Excel

Khi bạn có một số kinh nghiệm với các công thức Excel đơn giản, bạn có thể muốn thực hiện một số tính toán trong một công thức. Và các ví dụ sau cho thấy cách bạn có thể làm điều này.

Tạo các công thức phức tạp với các hằng số và các toán tử toán học

Đối với một công thức phức tạp để chính xác, một số thao tác nhất định phải được thực hiện trước. Lệnh mặc định hoạt động của các công thức Excel là:

• Phép tính trong dấu ngoặc đơn
• Lũy thừa
• Nhân và chia, tùy điều kiện nào đến trước trong một công thức
• Cộng và trừ, tùy điều kiện nào đến trước trong một công thức

Ví dụ: bạn có thể sử dụng các công thức sau để tính tổng số và hoa hồng:

Và bây giờ, hãy phân tích các công thức này để xem cách Microsoft Excel tính toán chúng như thế nào:

Công thức: = $B2*$D2 + $B2 * $D2 * $C2

• Lần nhân thứ nhất: $B2 * $D2 (giá * số lượng. = Số tiền)
• Phép nhân số thứ 2 và thứ 3: $B2*$D2*$C2 (giá * số lượng * VAT% = số VAT)
• Phép cộng: Số tiền + Số tiền thuế VAT = tổng số

Công thức hoa hồng: = ($B2 * $D2 + $B2 * $D2 * $C2) * 10%

Để tính khoản hoa hồng 10%, bạn cần nhân tổng cộng với 10%, do đó bạn kèm theo tính toán trước đó trong ngoặc và nhận được kết quả mong muốn.

Tất nhiên, không có gì ngăn cản bạn nhân tổng số đã được tính trong cột E với 10%, trong trường hợp này công thức sẽ thành một phép tính đơn giản = E2 * 10%. Tuy nhiên, trong các bảng tính lớn, nên viết các công thức được tính toán độc lập một cách hợp lý.

Trong công thức Microsoft Excel, lồng hàm bên trong một hàm khác bằng cách sử dụng một hàm như một đối số của một hàm khác. Trong các phiên bản hiện tại của Excel 2016, 2013, 2010 và 2010, bạn có thể sử dụng tối đa 64 hàm lồng nhau. Trong các phiên bản cũ của Excel 2003 và phiên bản thấp hơn, chỉ được phép có 7 hàm.

Dưới đây là một ví dụ rất đơn giản về công thức Excel lồng nhau có chứa hàm SUM để tìm tổng số, và chức năng ROUND để tròn số đó đến số nguyên gần nhất (0 chữ số thập phân): = ROUND (SUM (B2: B6), 0)

Trong tất cả các hàm Excel, hàm IF lồng thường xuyên hơn tất cả các hàm khác. Như bạn có thể biết, hàm IF được sử dụng để đánh giá một điều kiện cụ thể và trả về một giá trị khi điều kiện được đáp ứng và một giá trị khác khi điều kiện không được đáp ứng. Tuy nhiên, thực tế bạn thường phải đối phó với các tình huống có nhiều hơn hai kết quả có thể xảy ra. Và nếu trường hợp này, bạn có thể viết một số hàm IF và lồng nó vào nhau:

Các công thức mảng trong Excel là một bước cải tiến. Một công thức mảng Excel đơn có thể thực hiện hàng ngàn phép tính và thay thế hàng trăm công thức thông thường. Nghiên cứu công thức mảng chắc chắn đòi hỏi thời gian và công sức, nhưng xứng đáng.

Vì hướng dẫn này dành cho người mới bắt đầu, tôi sẽ không hăm dọa bạn bởi các định nghĩa các hằng số mảng và các công thức nhiều dòng phức tạp. Tôi sẽ chỉ hiển thị một ví dụ rất đơn giản của một công thức mảng Excel thể hiện những gì nó có thể làm.

Giả sử bạn có 2 cột số, cột A và B. Và bạn muốn biết có bao nhiêu lần cột B lớn hơn hoặc bằng cột A khi giá trị trong cột B lớn hơn 0. Nhiệm vụ này đòi hỏi phải so sánh hai dãy và bạn có thể làm điều này bằng cách sử dụng công thức mảng sau:

= SUM ((B2: B10> = A2: A10) * (B2: B10> 0))

Chú thích. Để nhập công thức mảng Excel một cách chính xác, bạn phải nhấn Ctrl + Shift + Enter thay vì gõ Enter thông thường.

Mặc dù Microsoft Excel có hàng trăm hàm được xây dựng, bạn vẫn có thể thấy mình phải đối mặt với một thách thức mà không có hàm Excel phù hợp. Trong trường hợp này, bạn có thể tạo ra hàm đó … hoặc có ai đó tạo ra nó cho bạn