20 Biểu thức cần biết trong Excel cho bất kỳ ai, bao gồm ví dụ và bí quyết

Khái niệm cơ bản về biểu thức trong Excel

Trong MS Excel, biểu thức trong Excel được xác định như các công thức toán học giúp thực hiện các phép toán trên bảng tính. Cho dù Microsoft đã cập nhật nhiều hàm phức tạp qua thời gian, nhưng nguyên tắc cơ bản của biểu thức Excel giữ nguyên không đổi cho tất cả các phiên bản từ Excel 2016, Excel 2013, Excel 2010, đến Excel 2007 và các phiên bản trước đó.

  • Chúng ta thấy mỗi biểu thức Excel đều khởi đầu bằng dấu bằng (=).
  • Điền vào sau dấu bằng là các phép tính hoặc hàm. Chẳng hạn, bạn muốn cộng giá trị trong các ô từ B1 đến B5, bạn có thể làm như sau:
    • Nhập thủ công công thức: = B1 + B2 + B3 + B4 + B5
    • Dùng hàm SUM: = SUM (B1: B5)
  • Press Enter để áp dụng biểu thức. Và bạn đã hoàn thành!
Microsoft Excel formulas
Biểu thức trong Microsoft Excel – Khái niệm cơ bản

Cấu thành biểu thức trong Microsoft Excel

Để xây dựng một biểu thức trong Excel, bạn có thể kết hợp nhiều thành phần cung cấp nguồn dữ liệu cho biểu thức và chỉ rõ phép toán nào cần được thực hiện trên dữ liệu đó. Tùy thuộc vào loại biểu thức, nó có thể bao hàm một hay tất cả những phần tử sau: ·

  • Hằng số – đây là các con số hoặc dữ liệu văn bản mà bạn gõ trực tiếp vào biểu thức, như = 2 * 3. ·
  • Tham chiếu ô – tham chiếu đến một ô chứa giá trị bạn muốn dùng trong biểu thức Excel, ví dụ: = SUM (A1, A2, B5). Đối với việc tham chiếu tới dữ liệu nằm trong khoảng ô kề nhau, dùng tham chiếu phạm vi như A1: A5. Với cách này, để tính tổng giá trị trong khoảng từ A1 đến A5, hãy dùng biểu thức: = SUM (A1: A5).·
  • Tên – xác định cho một phạm vi ô, hằng số, bảng, hoặc hàm, ví dụ = SUM (my_name). ·
  • Các hàm – đây là những biểu thức đã được xác định trước trong Excel để thực hiện phép tung dùng các giá trị do đối số cung cấp. ·
  • Toán tử – biểu tượng đặc biệt dùng để chỉ định loại hoạt động tính toán có thể thực hiện.
Basic elements of Excel formulas
Phần thành biểu thức Microsoft Excel

Toán tử trong bảng tính Excel

Để báo cho Microsoft Excel bạn muốn làm tính toán nào trong một biểu thức, hãy sử dụng toán tử. Có bốn loại toán tử chính trong Excel:

  • Toán học – để thực hiện những phép toán cơ bản.
  • So sánh (logic) – để kiểm tra sự tương quan giữa các giá trị.
  • Kết hợp – để ghép các chuỗi văn bản thành một chuỗi liền mạch.
  • Tham chiếu – để tạo ra phạm vi và các đối số riêng biệt cho hàm trong Excel.

Áp dụng toán tử số học trong biểu thức Excel

Toán tử này dùng để tiến hành các phép toán cơ bản: phép cộng, trừ, nhân và chia.

Toán tử Chức năng Biểu thức mẫu
+ Phép cộng =A2+B2
Phép trừ
Dấu âm
=A2-B2
=-A2 (chuyển giá trị ô A2 thành âm)
* Phép nhân =A2*B2
/ Phép chia =A2/B2
% Phần tram
trăm =A2*10%
(Tính 10% giá trị nằm trong ô A2)
^ Lũy thừa =A2^3
(Tính lũy thừa bậc 3 cho số trong ô A2)

Ví dụ, nếu giá tiền được ghi trong ô A2 và thuế VAT trong ô B2, bạn có thể xác định được lượng thuế VAT cần nộp bằng công thức phần trăm: =A2*B2

Using mathematical operators in Excel formulas
Sử dụng toán tử số học trong công thức Excel

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

Các toán tử so sánh hay toán tử logic trong công thức Microsoft Excel cho phép so sánh hai giá trị với nhau. Mỗi so sánh sẽ cho kết quả TRUE hoặc FALSE. Dưới đây là các toán tử logic dùng trong Excel:

Toán tử so sánh Ý nghĩa Công thức ví dụ
= Bằng =A2=B2
<> Không bằng =A2<>B2
> Lớn hơn =A2>B2
< Nhỏ hơn =A2<B2
>= Lớn hơn hoặc bằng =A2>=B2
<= Nhỏ hơn hoặc bằng =A2<=B2

Ví dụ: công thức =A1=B1 sẽ đưa ra TRUE nếu ô A1 và B1 giống nhau (dù là số, chữ, hay ngày), và FALSE nếu chúng khác nhau.

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

Toán tử nối dùng trong Excel là ký tự ampersand (&). Công cụ này giúp kết hợp hai hoặc nhiều chuỗi văn bản thành một.

Ví dụ: nếu mã quốc gia đặt tại cột A và số điện thoại tại cột B, bạn có thể dùng công thức sau để có được số điện thoại đầy đủ với mã quốc gia:

=A1 & “ ” & B1

Trong công thức trên, khoảng trắng đã được thêm vào giữa để số dễ đọc hơn.

Using the text concatenation operator in Excel formulas
Toán tử nối văn bản

Kết quả này cũng có thể đạt được khi sử dụng hàm CONCATENATE.

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

Để chỉ định một phạm vi dữ liệu trong công thức Excel và hàm, có thể dùng các toán tử sau.

Dấu hai chấm (:) – toán tử này giúp tạo tham chiếu đến một loạt ô nằm giữa hai ô đã chỉ rõ.

Chẳng hạn, phạm vi A1:A100 bao gồm tất cả 100 ô từ A1 tới A100. Bạn có thể tìm giá trị trung bình của chúng với công thức: =AVERAGE(A1:A100)

Còn bạn muốn tham chiếu đến toàn bộ một cột (A:A) hoặc hàng (1:1), ví dụ công thức dưới đây tính tổng của tất cả các số trong cột A: =SUM(A:A).

Dấu phẩy (,) – toán tử này phân tách các đối số trong công thức bảng tính Excel. Công thức =IF(A1>0, “tốt”, “xấu”) có nghĩa: nếu A1 lớn hơn 0, trả về “tốt”, còn không thì “xấu”.

Lưu ý: Dấu phẩy hiện diện trong bảng tính của những quốc gia như Bắc Mỹ và một số nước khác như là dấu phân cách đối số. Ở các nước Châu Âu, dấu phẩy lại là dấu thập phân và dấu phân cách đối số thường là dấu chấm phẩy (;). Trong trường hợp này, để phân tách đối số bạn cần dùng đến dấu chấm phẩy, ví dụ: =IF(A1>0, “tốt”, “xấu”).

Do đó, nếu bạn đang cố định nghĩa công thức trong bảng tính mà Excel không chấp nhận, hiển thị thông báo lỗi “formula invalid formula”, bạn nên kiểm tra Cài đặt Khu vực (Control

Hãy vào cài đặt (Panel> Region and Language> Additional Settings) và xác định ký tự phân cách được sử dụng ở đó. Đây chính là ký hiệu cần thiết để phân cách các đối số trong công thức của Excel mà bạn đang dùng.

Khoảng trắng – Đây là một toán tử gặp nhau, nó cho phép bạn lấy giá trị của các ô nằm ở điểm giao nhau giữa hai tham chiếu đã được chỉ ra. Chẳng hạn, với một danh sách mục tiêu được liệt kê dọc theo cột A và một số thông tin liên quan trải dọc các cột khác, bạn có thể tìm giá trị nằm tại giao điểm giữa một cột và dòng cụ thể thông qua công thức như sau: = B3: D3 C2: C4

Sử dụng toán tử giao lộ để lấy được ô chung từ hai tham chiếu mà bạn chỉ định.
Toán tử tham chiếu trong công thức và hàm Excel

Các dạng công thức Excel

Công thức bạn tạo ra trong bảng tính Excel có thẻ là loại đơn giản hoặc phức tạp:

  • Công thức Excel cơ bản chỉ triển khai một phép toán cụ thể, như = 10 * 5 hoặc = SUM (A1: A10)
  • Công thức Excel nâng cao bao gồm nhiều phép tính, như = 10 * 5 + 20 hoặc = SUM (A1: A10) / 2

Xuyên suốt hướng dẫn này, chúng ta sẽ đi qua từng bước chi tiết để xây dựng cả hai kiểu công thức cho bảng tính Excel.

Cập nhập công thức trong Excel

Như đã đề cập, tất cả công thức Excel đều bắt đầu với dấu bằng (=). Do đó, bất kỳ công thức nào bạn cần viết, hãy mở đầu bằng dấu = trong ô đã chọn hoặc trên thanh công thức của Excel. Giờ đây, chúng ta sẽ xem xét cách bạn có thể tạo các loại công thức khác biệt trong Excel.

Cách xây dựng công thức đơn giản trong Excel

Mặc dù công thức Excel cơ bản chỉ triển khai một phép toán duy nhất, có nhiều cách để thực hiện. Dữ liệu nguồn có thể được nhập dưới dạng giá trị cố định, tham chiếu đến ô, hoặc dùng các tên đã định sẵn, và thực hiện phép tính bằng cách sử dụng toán tử toán học hoặc các hàm của Excel.

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

Khi bạn đã quen với việc sử dụng công thức Excel cơ bản, bạn có thể mong muốn tổ hợp nhiều phép toán trong một công thức duy nhất. Các ví dụ dưới đây sẽ minh họa cách để thực hiện.

Tạo công thức cấp độ cao với giá trị cố định và toán tử toán học

Để một công thức phức tạp hoạt động chính xác, cần tuân theo một số nguyên tắc thực hiện cụ thể. Thứ tự hoạt động mặc định của công thức Excel như sau:

  • Các phép tính bên trong dấu ngoặc
  • Phép nâng lũy thừa
  • Phép nhân và phép chia, tùy vào thứ tự xuất hiện trong công thức
  • Phép cộng và phép trừ, tùy vào thứ tự xuất hiện trong công thức

Chẳng hạn: bạn có thể sử dụng công thức sau cho việc tính toán tổng doanh số cũng như hoa hồng:

Công thức Excel phức tạp với các hằng số và toán tử toán học
Cách lập công thức cấp độ cao trong Excel

Và bây giờ, chúng ta hãy phân tích các công thức đó để xem Excel thực hiện tính toán chúng ra sao:

Công thức tổng doanh số: = $B2*$D2 + $B2 * $D2 * $C2

  • Lần nhân đầu tiên: $B2 * $D2 (giá * số lượng = doanh số)
  • Lần nhân thứ hai và thứ ba: $B2*$D2*$C2 (giá * số lượng * phần trăm VAT = tiền VAT)
  • Phép cộng: Doanh số + Tiền VAT = Tổng cộng

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

Để tính hoa hồng là 10%, bạn cần phải nhân tổng số với 10%, vì vậy bạn sử dụng dấu ngoặc để bao phép toán trước đó và có được kết quả mong đợi.

Bạn hoàn toàn có thể nhân số lượng đã tính toán sẵn trong cột E

Với tỷ lệ là 10%, công thức sẽ đơn giản hóa thành = E2 * 10%. Tuy nhiên, với những bảng tính phức tạp, việc xây dựng các công thức độc lập trong cách tính toán là thích hợp.

Hàm Excel Lồng Nhau trong Công Thức

Các công thức trong Microsoft Excel có thể lồng các hàm: một hàm có thể trở thành đối số cho hàm khác. Phiên bản Excel 2016, 2013, và 2010 cho phép sử dụng đến 64 hàm lồng nhau, trong khi phiên bản Excel 2003 chỉ cho phép tối đa 7 hàm.

Ví dụ về công thức Excel lồng nhau đơn giản dưới đây sử dụng hàm SUM để cộng dãy số, và hàm ROUND để làm tròn số đó: = ROUND(SUM(B2:B6), 0)

Ví dụ về công thức Excel với hàm lồng nhau
Công thức Excel với các hàm lồng nhau

Hàm IF là hàm thường được lồng trong Excel. Hàm IF kiểm tra điều kiện nhất định và trả về giá trị tương ứng khi thỏa mãn hoặc không thỏa mãn điều kiện đó. Đối mặt với các tình huống có nhiều hơn hai kết quả có thể, ta có thể lồng nhiều hàm IF vào nhau:

Cách sử dụng hàm IF lồng nhau trong Excel
Công thức Excel với hàm IF lồng nhau

Các Công Thức Mảng trong Excel

Công thức mảng trong Excel là một nâng cao, có khả năng thay thế hàng trăm công thức thông thường bằng một công thức đơn. Tuy việc tìm hiểu công thức mảng đòi hỏi đầu tư thời gian và nỗ lực, nhưng nó mang lại hiệu quả đáng kể.

Dành cho người mới, tôi sẽ không trình bày các kiến thức về các hằng số mảng hoặc công thức nhiều dòng, nhưng sẽ giới thiệu một ví dụ đơn giản để thấy công dụng của nó. Giả sử có 2 cột số A và B, bạn muốn biết số lần giá trị cột B lớn hơn hoặc bằng cột A khi cột B có giá trị lớn hơn 0, yêu cầu so sánh hai dãy. Công thức mảng sau sẽ thực hiện điều đó:

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

Ví dụ về công thức mảng trong Excel
Công thức mảng trong Excel

Lưu ý. Để nhập chính xác công thức mảng Excel, bạn cần nhấn Ctrl + Shift + Enter thay vì Enter thông thường.

Hàm Do Người Dùng Tự Định Nghĩa

Dù có hàng trăm hàm sẵn có trong Microsoft Excel, đôi khi bạn cần một hàm chuyên biệt không có sẵn. Bạn có thể tự mình tạo hàm đó hoặc nhờ người khác hỗ trợ. Những hàm như vậy được gọi là Hàm Do Người Dùng Tạo (UDF) và chúng thường được sử dụng cho các toán tính kỹ thuật phức tạp. VBA (Visual Basic for Applications) là ngôn ngữ lập trình sử dụng để viết UDFs.

Về tham chiếu ô trong Excel, có 3 loại: tham chiếu tuyệt đối ($A$1), tương đối (A1) và hỗn hợp ($A1 hoặc A$1). Ba loại này đều ám chỉ đến cùng một ô, và dấu đô la ($) chỉ được sử dụng nhằm một mục đích –

Điều này cho phép Microsoft Excel hiểu rằng liệu các tham chiếu ô có cần được điều chỉnh khi chúng ta chuyển hay sao chép công thức sang các ô khác trong bảng tính.

Tham chiếu ô kiểu cố định ($A$1) – việc sử dụng dấu $ trước cả hàng và cột đảm bảo rằng tham chiếu luôn giữ nguyên không đổi mỗi lần công thức được sao chép đi nơi khác.

Tham chiếu ô theo vị trí (A1) – loại tham chiếu này không có sự kiểm soát bởi dấu đô la nào cả, nó thay đổi dựa trên vị trí hàng và cột mà công thức được chép tới.

Tham chiếu ô dạng kết hợp – bao gồm 2 kiểu sau:

  • Cố định cột, tương đối hàng ($A1) – dấu $ trước tên cột giữ cho cột đó không thay đổi khi công thức được di chuyển, trong khi tham chiếu đến hàng lại thay đổi theo hàng được sao chép tới.
  • Tương đối cột, cố định hàng (A$1) – tham chiếu cột linh hoạt với hàng cố định nhờ ký hiệu $, cho phép hàng không bị thay đổi khi di chuyển công thức.

Biểu đồ dưới đây minh họa cụ thể sự khác biệt giữa các loại tham chiếu khi được áp dụng thực tế.

Absolute, relative and mixed cell references in Excel formulas
Hàm người dùng tự tạo

Một số mẹo và phím tắt giúp tiết kiệm thời gian khi sử dụng công thức trong Excel

Công thức Excel là công cụ đắc lực và linh hoạt, có khả năng xử lý đa dạng tác vụ trên bảng tính của bạn. Việc nắm vững hàm và công thức Excel đương nhiên mất nhiều thời gian, đôi khi bạn cảm thấy không đủ giờ để học hết chúng. Một mẹo hay là tiết kiệm thời gian bằng mọi cách để tìm thêm thời gian học hỏi .

  • Phím F4 giúp bạn dễ dàng chuyển đổi giữa các tham chiếu cố định, tương đối và kết hợp trong công thức.
  • Để xem tất cả công thức có trong trang tính, hãy chọn Show Formulas trong tab Formulas > Nhóm Formulas Auditing hoặc sử dụng phím tắt Ctrl + ~.
  • Nếu muốn chỉnh sửa công thức, bạn có thể ấn F2, hoặc double-click vào ô, hay sử dụng thanh công thức.
  • Để phân tích công thức trong Excel, chọn phần của công thức và ấn F9 để kiểm tra giá trị thực của các tham chiếu ô.
  • Để nhân bản công thức xuống toàn cột, hãy nhập công thức vào ô đầu tiên, chọn ô đó và di chuyển con trỏ tới góc vuông nhỏ dưới bên phải (còn được gọi là Fill handle) cho đến khi biểu tượng chuyển thành hình chữ thập màu đỏ đen. Nhấp đôi vào đó và bạn sẽ thấy công thức được sao chép xuống toàn bộ cột.
  • Để chuyển các công thức thành giá trị, chọn tất cả các ô cần chuyển đổi, ấn Ctrl + C để sao chép, sau đó ấn Shift + F10, tiếp theo là nhấn V và cuối cùng Enter. Shift + F10 + V là tổ hợp phím tắt cho chức năng dán kiểu giá trị chuyên biệt của Excel. Nếu bạn không tự tin ghi nhớ phím tắt này, chỉ việc sử dụng tổ hợp dán thông thường Ctrl + V, click vào mũi tên bên phải nút Paste và chọn Paste Values từ danh sách xổ xuống.

Để sử dụng thành thạo Excel trong công việc của mình, chúng ta cần không chỉ thông thạo các hàm mà còn phải hiểu cách sử dụng các công cụ của Excel nữa. Một số hàm tiên tiến như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH,… và các công cụ thường dùng như Data validation, Conditional formatting, Pivot table,… đều có thể học được trong các khoá học Excel từ cơ bản đến nâng cao do giảng viên Nguyễn Đức Thanh hoặc DT Nguyen giảng dạy.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop