Các công thức và hàm Excel cơ bản với các ví dụ

Các công thức và hàm Excel cơ bản với các ví dụ

Hướng dẫn này cung cấp danh sách các công thức và hàm cơ bản của Excel với các ví dụ và liên kết đến các hướng dẫn có liên quan chuyên sâu.

 

Được thiết kế chủ yếu như một chương trình bảng tính, Microsoft Excel là cực kỳ mạnh mẽ và linh hoạt khi nói đến tính toán số hoặc giải quyết các vấn đề toán học và kỹ thuật. Nó cho phép bạn tổng số hoặc trung bình một cột số trong chớp mắt. Ngoài ra, bạn có thể tính lãi kép và trọng số trung bình, nhận ngân sách tối ưu cho chiến dịch quảng cáo của mình, giảm thiểu chi phí giao hàng hoặc lập lịch làm việc tối ưu cho nhân viên của bạn. Tất cả điều này được thực hiện bằng cách nhập công thức vào các ô.

 

Hướng dẫn này nhằm mục đích dạy cho bạn các yếu tố cần thiết của các hàm Excel và hiển thị cách sử dụng các công thức cơ bản trong Excel.

Khái niệm cơ bản về công thức Excel

Trước khi cung cấp danh sách công thức Excel cơ bản, hãy xác định các thuật ngữ chính chỉ để đảm bảo chúng ta đang ở trên cùng một trang. Vậy, chúng ta gọi công thức Excel và hàm Excel là gì?

 

Công thức là biểu thức tính toán giá trị của ô.

 

Ví dụ, =A2+A2+A3+A4là một công thức cho biết thêm các giá trị trong các ô từ A2 đến A4.

 

Hàm là một công thức được xác định trước đã có sẵn trong Excel. Các hàm thực hiện các phép tính cụ thể theo một thứ tự cụ thể dựa trên các giá trị được chỉ định, được gọi là các đối số hoặc các tham số.

 

Ví dụ: thay vì chỉ định từng giá trị được tổng hợp như trong công thức trên, bạn có thể sử dụng hàm SUM để thêm một dải ô: =SUM(A2:A4)

This Post: Các công thức và hàm Excel cơ bản với các ví dụ

 

Bạn có thể tìm tất cả các hàm Excel có sẵn trong Thư viện hàm trên tab Công thức 

 

Có tồn tại hơn 400 hàm trong Excel và số đang tăng theo phiên bản lên phiên bản. Tất nhiên, đó là bên cạnh không thể ghi nhớ tất cả chúng, và bạn thực sự không cần phải. Trình hướng dẫn chức năng sẽ giúp bạn tìm thấy hàm phù hợp nhất cho một nhiệm vụ cụ thể, trong khi Công thức Excel Intellisense sẽ nhắc cú pháp và đối số của hàm ngay sau khi bạn nhập tên của hàm trước bởi một dấu bằng trong ô:

 

Kích vào tên của hàm sẽ biến nó thành một siêu liên kết màu xanh, nó sẽ mở ra chủ đề trợ giúp cho hàm đó.

 

Tiền boa. Bạn không nhất thiết phải gõ tên hàm trong tất cả các mũ, Microsoft Excel sẽ tự động viết hoa nó sau khi bạn nhập công thức và nhấn phím Enter để hoàn thành nó.

 

10 chức năng cơ bản của Excel bạn chắc chắn phải biết

 

Dưới đây là danh sách 10 chức năng đơn giản nhưng thực sự hữu ích là một kỹ năng cần thiết cho tất cả những ai muốn chuyển từ người mới sử dụng Excel sang chuyên gia Excel.

SUM

Hàm Excel đầu tiên bạn cần làm quen thuộc là hàm thực hiện phép tính số học cơ bản của phép cộng:

 

SUM ( number1 , [number2], …)

 

Trong cú pháp của tất cả các hàm Excel, một đối số được đính kèm trong [dấu ngoặc vuông] là tùy chọn, các đối số khác được yêu cầu. Có nghĩa là, công thức Tổng của bạn nên bao gồm ít nhất 1 số, tham chiếu đến một ô hoặc một dải ô. Ví dụ:

 

=SUM(A2:A6) – thêm các giá trị trong các ô từ A2 đến A6.

 

=SUM(A2, A6) – thêm các giá trị vào các ô A2 và A6.

 

=SUM(A2:A6)/5 – thêm các giá trị trong các ô từ A2 đến A6, sau đó chia tổng cho 5.

Tiền boa. Cách nhanh nhất để tổng hợp một cột hoặc hàng số là chọn một ô bên cạnh các số bạn muốn tính tổng (ô ngay bên dưới giá trị cuối cùng trong cột hoặc bên phải của số cuối cùng trong hàng) và bấm các AutoSum nút trên Trang chủ tab, trong Biên tập nhóm. Excel sẽ tự động chèn một công thức SUM cho bạn.

 

Tài nguyên hữu ích:

 

Ví dụ về công thức Excel Sum – công thức tính tổng cột, hàng, chỉ các ô được lọc (hiển thị) hoặc tổng trên các trang tính.

Excel AutoSum – cách nhanh nhất để tổng hợp một cột hoặc một dãy số.

 

SUMIF trong Excel – ví dụ công thức để tổng hợp các ô có điều kiện.

 

Hàm SUMIFS trong Excel – ví dụ công thức để tổng hợp các ô dựa trên nhiều tiêu chí.

TRUNG BÌNH CỘNG

Hàm AVERAGE của Excel thực hiện chính xác tên của nó, nghĩa là tìm số trung bình hoặc số học của các số. Cú pháp của nó tương tự như cú pháp của SUM:

 

AVERAGE (số 1, [số2],…)

 

Xem xét kỹ hơn công thức cuối cùng từ phần trước ( =SUM(A2:A6)/5), nó thực sự làm gì? Tổng các giá trị trong các ô từ A2 đến A6 và sau đó chia kết quả cho 5. Và bạn gọi thêm một nhóm các số và sau đó chia tổng số cho các số đó? Yep, trung bình!

 

Vì vậy, thay vì nhập =SUM(A2:A6)/5, bạn có thể chỉ cần đặt=AVERAGE(A2:A6)

LỚN NHẤT NHỎ NHẤT

Công thức MAX và MIN trong Excel nhận được giá trị lớn nhất và nhỏ nhất trong một tập các số, tương ứng. Đối với tập dữ liệu mẫu của chúng tôi, các công thức sẽ đơn giản như sau:

 

=MAX(A2:A6)

 

COUNT & COUNTA

 

Nếu bạn tò mò muốn biết có bao nhiêu ô trong một phạm vi nhất định chứa các giá trị số (số hoặc ngày), đừng lãng phí thời gian của bạn đếm chúng bằng tay. Hàm COUNT của Excel sẽ mang lại cho bạn số đếm nhịp tim:

 

COUNT (giá trị 1, [giá trị2], …)

Mặc dù hàm COUNT chỉ giao dịch với các ô chứa số, hàm COUNTA của Excel tính tất cả các ô không trống , cho dù chúng chứa số, ngày, thời gian, văn bản, giá trị lô-gic của TRUE và FALSE, lỗi hoặc chuỗi văn bản trống (” “):

 

COUNTA (giá trị 1, [giá trị2], …)

 

RED : 4 Georgetown Restaurants Serving up Delicious Mexican Food

Ví dụ: để tìm hiểu số lượng ô trong cột A chứa số, hãy sử dụng công thức này:

 

=COUNT(A:A)

 

Để đếm tất cả các ô không trống trong cột A, hãy đi đến ô này:

 

=COUNTA(A:A)

 

Trong cả hai công thức, bạn sử dụng cái gọi là “tham chiếu toàn bộ cột” (A: A) đề cập đến tất cả các ô trong cột A.

 

Ảnh chụp màn hình sau đây cho thấy sự khác biệt:

 

=MIN(A2:A6)

 

if

Đánh giá bởi số lượng nhận xét liên quan đến IF trên blog của chúng tôi, đó là chức năng phổ biến nhất trong Excel. Theo thuật ngữ đơn giản, bạn sử dụng công thức IF để yêu cầu Excel kiểm tra một điều kiện nhất định và trả lại một giá trị hoặc thực hiện một phép tính nếu điều kiện được đáp ứng và một giá trị hoặc phép tính khác nếu điều kiện không được đáp ứng:

 

IF (logical_test, [value_if_true], [value_if_false])

 

Ví dụ, câu lệnh IF sau đây chỉ thị cho Excel kiểm tra giá trị trong A2 và trả về “OK” nếu nó lớn hơn hoặc bằng 3, “Không OK” nếu nó nhỏ hơn 3:

 

=IF(A2>=3, “OK”, “Not OK”)

TRIM

Nếu công thức Excel chính xác của bạn chỉ trả về một loạt lỗi, một trong những điều đầu tiên cần kiểm tra là khoảng trống trong ô được tham chiếu trong công thức của bạn (Bạn có thể ngạc nhiên khi biết có bao nhiêu hàng đầu, dấu và ở giữa không được chú ý trong tờ của bạn chỉ cho đến khi có điều gì đó sai!).

 

Có một số cách để loại bỏ không gian không mong muốn trong Excel, với hàm TRIM là hàm dễ nhất:

 

TRIM (văn bản)

 

Ví dụ: để cắt thêm khoảng trắng trong cột A, hãy nhập công thức sau vào ô A1 và sau đó sao chép nó xuống cột:

 

=TRIM(A1)

 

Nó sẽ loại bỏ tất cả các khoảng trống thừa trong các ô nhưng một ký tự khoảng trắng giữa các từ:

LEN

Bất cứ khi nào bạn muốn biết số ký tự trong một ô nào đó, LEN là hàm để sử dụng:

 

LEN (văn bản)

 

Cần tìm ra số lượng ký tự trong ô A2? Chỉ cần gõ =LEN(A2)vào ô khác.

 

Xin lưu ý rằng hàm Excel LEN đếm hoàn toàn tất cả các ký tự bao gồm cả dấu cách :

 

Bạn muốn nhận tổng số ký tự trong một phạm vi hoặc ô hoặc chỉ đếm các ký tự cụ thể? Vui lòng xem các tài nguyên sau.

VÀ VÀ HOẶC

Đây là hai hàm logic phổ biến nhất để kiểm tra nhiều tiêu chí. Sự khác biệt là cách họ làm điều này:

 

VÀ trả về TRUE nếu tất cả các điều kiện được đáp ứng, FALSE nếu không.

 

HOẶC trả về TRUE nếu có bất kỳ điều kiện nào được đáp ứng, FALSE nếu không.

 

Trong khi hiếm khi được sử dụng trên riêng của họ, các chức năng này rất tiện dụng như là một phần của công thức lớn hơn.

 

Ví dụ: để kiểm tra số lượng trong 2 cột và trả về “Tốt” nếu cả hai giá trị lớn hơn 0, bạn sử dụng công thức IF sau đây với câu lệnh AND được nhúng:

 

=IF(AND(A2>0, B2>0), “Good”, “”)

 

Nếu bạn hài lòng với chỉ một giá trị lớn hơn 0 (A2 hoặc B2), thì hãy sử dụng câu lệnh OR:

 

=IF(OR(A2>0, B2>0), “Good”, “”)

CONCATENATE

Trong trường hợp bạn muốn lấy giá trị từ hai hoặc nhiều ô và kết hợp chúng thành một ô, hãy sử dụng toán tử nối (&) hoặc hàm CONCATENATE:

 

CONCATENATE (text1, [text2],…)

 

Ví dụ: để kết hợp các giá trị từ các ô A2 và B2, chỉ cần nhập công thức sau vào ô khác:

 

=CONCATENATE(A2, B2)

 

Để tách các giá trị được kết hợp với một dấu cách, hãy nhập ký tự dấu cách (“”) vào danh sách đối số:

 

=CONCATENATE(A2, ” “, B2)

 

TODAY & NGAY

Để xem ngày và giờ hiện tại bất cứ khi nào bạn mở trang tính của mình mà không phải cập nhật hàng ngày theo cách thủ công, hãy sử dụng:

 

=TODAY() để chèn ngày hôm nay vào ô.

 

=NOW() để chèn ngày giờ hiện tại vào ô.

 

Vẻ đẹp của các chức năng này là chúng không yêu cầu bất kỳ đối số nào cả, bạn nhập các công thức chính xác như được viết ở trên.

Sử dụng các hàm TODAY và NOW trong Excel

 

Tài nguyên hữu ích:

 

Cách chèn ngày hôm nay trong Excel – các cách khác nhau để nhập ngày giờ hiện tại trong Excel: dưới dạng dấu thời gian không thể thay đổi hoặc ngày và thời gian có thể cập nhật tự động.

 

Các hàm ngày tháng Excel với các ví dụ công thức – các công thức để chuyển đổi ngày thành văn bản và ngược lại, trích xuất một ngày, tháng hoặc năm từ một ngày, tính toán sự khác biệt giữa hai ngày và nhiều hơn nữa.

Mẹo công thức Excel và cách thực hiện

Bây giờ bạn đã quen thuộc với các công thức Excel cơ bản, các mẹo này sẽ cung cấp cho bạn một số hướng dẫn về cách sử dụng chúng hiệu quả nhất và tránh các lỗi công thức phổ biến.

 

Sao chép cùng một công thức vào các ô khác thay vì nhập lại nó

 

Một khi bạn đã gõ một công thức vào một ô, không cần phải gõ lại nó nhiều lần. Đơn giản chỉ cần sao chép công thức vào các ô liền kề bằng cách kéo tay cầm điền (một hình vuông nhỏ ở góc dưới bên phải của ô). Để sao chép công thức vào toàn bộ cột, hãy đặt con trỏ chuột vào thanh công cụ điền và nhấp đúp vào dấu cộng.

 

Chú thích. Sau khi sao chép công thức, hãy đảm bảo rằng tất cả các tham chiếu ô đều chính xác. Tham chiếu ô có thể thay đổi tùy thuộc vào việc chúng là tuyệt đối (không thay đổi) hoặc tương đối (thay đổi).

 

Để biết hướng dẫn từng bước chi tiết, vui lòng xem Cách sao chép công thức trong Excel .

 

Cách xóa công thức, nhưng giữ giá trị được tính

 

Khi bạn xóa công thức bằng cách nhấn phím Delete, giá trị được tính cũng sẽ bị xóa. Tuy nhiên, bạn chỉ có thể xóa công thức và giữ giá trị kết quả trong ô. Dưới đây là cách thực hiện:

 

Chọn tất cả các ô có công thức của bạn.

 

Nhấn Ctrl + C để sao chép các ô đã chọn.

 

Nhấp chuột phải vào lựa chọn, và sau đó nhấp vào Dán giá trị > Giá trị để dán các giá trị được tính toán trở lại các ô đã chọn. Hoặc, bấm Paste shortcut đặc biệt: Shift + F10 và sau đó V .

 

Để biết các bước chi tiết với ảnh chụp màn hình, vui lòng xem Cách thay thế công thức bằng các giá trị của chúng trong Excel .

 

Bao gồm các giá trị văn bản trong dấu ngoặc kép, nhưng không phải là số

Bất kỳ văn bản nào có trong công thức Excel của bạn đều phải được đính kèm trong “dấu ngoặc kép”. Tuy nhiên, bạn không bao giờ nên làm điều đó với các con số, trừ khi bạn muốn Excel coi chúng là các giá trị văn bản.

 

Ví dụ: để kiểm tra giá trị trong ô B2 và trả về 1 cho “Đã qua”, 0 nếu không, bạn đặt công thức sau, như trong C2:

 

=IF(B2=”pass”, 1, 0)

 

Sao chép công thức xuống các ô khác và bạn sẽ có cột 1 và 0 có thể được tính mà không bị cản trở.

 

Bây giờ, hãy xem điều gì sẽ xảy ra nếu bạn báo giá gấp đôi các con số:

 

=IF(B2=”pass”, “1”, “0”)

 

Ngay từ cái nhìn đầu tiên, đầu ra là bình thường – cùng một cột của 1 và 0. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ nhận thấy rằng các giá trị kết quả được căn chỉnh trái trong các ô theo mặc định, có nghĩa là các giá trị này là các chuỗi văn bản, chứ không phải các số! Nếu sau này ai đó sẽ cố gắng tính toán số 1 và 0, họ có thể sẽ kéo tóc ra để cố gắng tìm ra lý do tại sao công thức Sum hoặc Count chính xác 100% không trả về gì ngoài 0.

Không định dạng số trong công thức Excel

Hãy nhớ quy tắc đơn giản này: các số được cung cấp cho công thức Excel của bạn phải được nhập mà không có bất kỳ định dạng nào như dấu tách thập phân hoặc ký hiệu đô la. Ở Bắc Mỹ và một số quốc gia khác, dấu phẩy là dấu tách đối số mặc định và ký hiệu đô la ($) được sử dụng để tạo tham chiếu ô tuyệt đối. Sử dụng các ký tự đó trong số có thể chỉ làm cho Excel của bạn bị điên 🙂 Vì vậy, thay vì gõ $ 2,000, chỉ cần gõ 2000, và sau đó định dạng giá trị đầu ra theo ý thích của bạn bằng cách thiết lập một định dạng số Excel tùy chỉnh .

Khớp tất cả các dấu ngoặc mở và đóng trong công thức của bạn

Khi kết hợp một công thức Excel phức tạp với một hoặc nhiều hàm lồng nhau, bạn sẽ phải sử dụng nhiều hơn một bộ ngoặc đơn để xác định thứ tự các phép tính. Trong các công thức như vậy, hãy chắc chắn ghép các dấu ngoặc đơn đúng cách để có một dấu đóng ngoặc cho mỗi dấu ngoặc đơn mở. Để làm cho công việc dễ dàng hơn cho bạn, Excel làm tròn cặp ngoặc đơn bằng các màu khác nhau khi bạn nhập hoặc chỉnh sửa công thức.

 

Đảm bảo Tùy chọn tính toán được đặt thành Tự động

 

Nếu đột nhiên công thức Excel của bạn đã ngừng tính toán lại tự động, rất có thể là Tùy chọn Tính toán bằng cách nào đó đã chuyển sang Thủ công . Để khắc phục điều này, hãy chuyển đến tab Công thức > Nhóm tính toán , nhấp vào nút Tùy chọn tính toán và chọn Tự động .

 

Nếu điều này không giúp được bạn, hãy xem các bước khắc phục sự cố sau: Công thức Excel không hoạt động: các bản sửa lỗi và giải pháp .

 

Đây là cách bạn tạo và quản lý các công thức cơ bản trong Excel. Tôi làm cách nào bạn sẽ thấy thông tin này hữu ích. Dù sao, tôi cảm ơn bạn đã đọc và hy vọng sẽ nhìn thấy bạn trên blog của chúng tôi vào tuần tới.

 

 

Mời bạn xem thêm:

 Tìm sự xuất hiện thứ N của một ký tự && Hàm FIND và SEARCH của Excel

 

Bạn có thắc mắc gì muốn được giải đáp hãy để lại ý kiến ( Họ tên, Số điện thoại hoặc Email, câu hỏi) của mình dưới dưới phần bình luận chúng tôi sẽ giải đáp cho bạn !

CÁC BÀI VIẾT CÙNG CHỦ ĐỀ LIÊN QUAN

CÁC BÀI VIẾT MỚI NHẤT