Ngày nay Microsoft Excel đóng vai trò quan trọng trong cuộc sống, đặc biệt đối với các doanh nghiệp nó chính là công cụ hỗ trợ đắc lực trong vấn đề quản lý thông tin nhân viên, tính lương…. Việc áp dụng Excel hay các công thức trong Excel giúp cho việc tính toán trở nên nhanh chóng và dễ dàng hơn. Trong bài viết dưới đây cùng Box.edu tìm hiểu các công thức trong Excel được sử dụng nhiều nhất cũng như cách học excel online sao cho hiệu quả nhất.
Xem thêm: Tổng hợp các phím tắt trong Excel phổ biến bạn nên biết
Xem thêm: Phân loại và cách sử dụng Conditional Formatting trong Excel
Mục lục bài viết
Các thành phần cơ bản trong công thức của Excel
Trong quá trình tạo ra các công thức trong Excel, người dùng 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ử sẽ được sử dụng trong công thức đó. Các thành phần cơ bản trong công thức Excel bao gồm:
- Hằng số: là số hoặc các giá trị văn bản mà bạn nhập trực tiếp như =5*4.
- Tên: được xác định cho một phạm vi ô, hằng, bảng hoặc hàm. Ví dụ như: =SUM(my name).
- Tham chiếu ô: chính là tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel.
- Các hàm: là 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ố.
- 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.
Tổng hợp các công thức trong Excel
Bạn có biết tất cả các công thức trong Excel được thực hiện bằng dấu + theo sau đó là các văn bản cụ thể biểu thị công thức Excel.
Hàm SUM
Hàm SUM trong Excel cho phép người dùng tính tổng các giá trị bạn chỉ cần làm theo công thức sau
=SUM(giá trị 1, giá trị 2,…)
Các giá trị ở đây có thể là số thực hoặc bằng số trong một ô cụ thể của bảng tính mà bạn đang thực hiện.
Ví dụ về hàm SUM trong Exel.
Tính tổng bằng hàm SUM: =SUM(A1:A2) để tính tổng cho các số trong dãy số (A1:A2) thay cho các tham số và cho ra được kết quả cuối cùng là 120
Hàm sum trong Excel
Phép trừ
Thực hiện phép trừ trong Excel thì người dùng cần nhập ô cần thực hiện phép trừ và sử dụng công thức hàm Sum hoặc sử dụng định dạng.
Sử dụng phép trừ bằng hàm Sum:
Cho bảng dữ liệu dưới đây, yêu cầu sử dụng hàm SUM để thực hiện phép trừ với các số liệu cụ thể trong bảng tính.
Bước 1: Tại ô A3 nếu bạn muốn hiển thị kết quả, bạn cần nhập theo công thức sau: =SUM(A1;-B1;C1;-D1;-E1)
Phép trừ
Bước 2: Nhấn Enter để hiện thị kết quả phép tính.
Phép trừ trong Excel
Phép nhân
Thực hiện phép nhân trong Excel, bạn cần thực hiện nhập công thức theo định dạng như sau =(giá trị 1 * giá trị 2)
Giá trị ở đây là giá trị ở 2 cột khác nhau trong Excel
Ví dụ về phép nhân trong Excel
Để thực hiện nhân 2 giá trị ở hai cột khác nhau bạn thực hiện =(B2*C2) >> Sau đó nhấn click tại vị trí như hình để áp dụng cho những số còn lại.
Phép nhân trong Excel
Phép chia
Để thực hiện được phép chia trong Excel rất đơn giản bạn hãy nhấn trò chuột vào một ô trống nhập “=” và theo dõi giá trị bạn muốn chi với một hoặc nhiều giá trị mà bạn muốn chia bằng dấu gạch chép về pheis trước “/” ở giữa 2 giá trị đó là được.
Phép chia trong Excel
Công thức tính tỷ lệ phần trăm (Percentage)
Công thức tính tỷ lệ phần trăm trong Excel như sau:
Tỷ lệ phần trăm (%) = (Giá trị mới – Giá trị cũ )/ Giá trị cũ.
Bước 1: Bạn tiến hành mở bảng tính mà bạn muốn tính tỷ lệ phần trăm trong Excel >> Nhập công thức tính phần trăm.
Ví dụ: Tính tăng/giảm số lượng Cherries của tháng này so với tháng trước bạn sẽ nhập công thức là =(C2-B2)/C2.
Trong đó C2 là số liệu tháng trước, B2 là số liệu tháng hiện tại.
Tính tỷ lệ phần trăm trong Excel
Bước 2: Nhấn chọn Tab Home >> Nhấn Number >> Chọn Percentage để các phân số thập phân hiển thị theo tỷ lệ phần trăm.
Tính tỷ lệ phần trăm trong Excel 1
Bạn có thể tăng chữ số thập phân bằng cách tuỳ chọn trong More Number Formats.
Để áp dụng công thức cho các dòng dưới bạn nhấp chuột vào góc dưới của cột vừa tìm được cho đến khi thấy dấu + và kéo xuống sẽ áp dụng tính công thức cho những hàng phía dưới.
Tính tỷ lệ phần trăm trong Excel 2
Hàm DATE
Hàm DATE trong Excel được tính theo công thức như sau: =DATE (year,moth,date)
Khi bạn nhập công thức này nó sẽ trả về một ngày tượng ứng với các giá trị được nhập trong ngoặc đơn – kể cả những giá trị được gọi từ các ô khác.
– Tạo ngày từ các giá trị ô.
Hàm DATE trong Excel
– Tự động thiết lập ngày hôm nay.
Hàm DATE trong Excel 1
Trong hai cách sử dụng công thức ngày của Excel, ngày trả về của bạn phải ở dạng “mm/ dd/ yy” – trừ khi chương trình Excel của bạn được định dạng khác nhau.
Hàm VLOOKUP trong Excel
Sử dụng hàm VLOOKUP với mục đích tìm kiếm một giá trị trong cột đầu tiên của bảng và trả về giá trị trong cùng một hàng ở vị trí index number.
Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
– Lookup_value: là giá trị cần dò tìm
– table_array: là bảng cần dò tìm
– col_index_num: là vị trí cột cần lấy giá trị
– range_lookup: nhập vào số 0 để tìm chính xác và nhập vào số 1 để tìm gần đúng
Ví dụ hàm VLOOKUP
Bạn quan sát ví dụ dưới đây: yêu cầu xếp lại học sinh theo thứ hạng yếu, trung bình, khá, giỏi.
Bước 1: Trong ô cần tìm kiếm kết quả, bạn nhập =VLOOKUP(B2,$E$2:$G$9,2,0).
Trong đó:
– B2: là giá trị cần dò tìm giá trị.
– $E$2:$G$9: Bảng đối chiếu giá trị (đã khóa).
– 2 là giá trị cột cần dò tìm.
– 0: Chọn giá trị tuyệt đối để dò tìm.
Hàm VLOOKUP
Bước 2: Kết quả sẽ hiện ra như sau.
Hàm VLOOKUP trong Excel
Bạn quan sát bảng dữ liệu vừa rồi sẽ hiển thị ra lỗi #N/A, nguyên nhân chính là di giá trị cần tìm phải là số nguyên dương từ 1 – 10 vì vậy lỗi xảy ra do không chấp nhận số trong khoảng hoặc chứ dấu phẩy.
Vậy nên trong quá trình sử dụng hàm VLOOKUP bạn cần lưu ý lúc lựa chọn điều kiện cho giá trị hiển thị tuyệt đối hoặc tượng đối để đảm bảo kết quả hiển thị đúng với mục đích của mình.
Sau khi sửa lại công thức bạn sẽ được kết quả như hình dưới đây.
Kết quả Hàm VLOOKUP
Hàm COUNT
Sử dụng hàm COUNT trong Excel với mục đích hàm này sẽ trả về một giá trị băng với số lượng mục được tìm thấy trong phạm vi ô mà bạn mong muốn.
Ví dụ: nếu có 8 ô có giá trị được nhập giữa A1 và A10, = COUNT (A1: A10) sẽ trả về giá trị 8.
Công thức COUNT trong Excel
=COUNT(Value1, Value2,….)
Trong đó:
– Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
– Value 2: Là tham số không bắt buộc.
Ví dụ về cách sử dụng hàm COUNT:
Cho bảng dữ liếu sau, yêu cầu sử dụng công thức hàm COUNT trong Excel để đếm dữ liệu trong cột.
Bước 1: Nhấp chuột vào ô tham chiếu mà bạn muốn hiển thị kế quả nhập công thức: =COUNT(E4:E7)
Sử dụng hàm COUNT trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả.
Hàm COUNT trong Excel
Hàm SUMIF
Cú pháp hàm SUMIF trong Excei:
=SUMIF(range, criteria, [sum_range]).
Trong đó:
– Range: Là vùng được chọn có chứa các ô điều kiện.
– Criteria: Là điều kiện để thực hiện hàm này.
– Sum_range: Vùng dữ liệu cần tính tổng.
Ví dụ hàm SUMIF trong Excel.
Cho bảng dữ liệu dưới đây, yêu cầu tính tổng thành tiền các hoá chất có số lượng >200 bằng cách sử dụng hàm SUMIF.
Bước 1: Nhấn chuột vào ô bạn muốn hiển thị kết quả >> tiến hành nhập công thức =SUMIF(D2:D10;”>200″; E2:E10).
Trong đó:
– D2:D10: Là vùng dữ liệu Số lượng theo yêu cầu của ví dụ.
– “>200”: Là điều kiện theo yêu cầu của ví dụ (có số lượng > 200).
– E2:E10: Là vùng dữ liệu Thành Tiền để lấy kết quả.
Hàm SUMIF trong Excel
Bước 2: Nhấn Enter hiển thị kết quả.
Kết quả sử dụng hàm SUMIF trong Excel
Hàm AVERAGE
Để thực hiện phép tính trung bình trong Excel bạn sử dụng cú pháp =AVERAGE(number1,number2,…) hoặc =AVERAGE(giá trị đầu:giá trị cuối)
Hai cách tính này sẽ giúp cho bạn tính được trung bình tất cả các giá trị hoặc phạm vi của các ô có trong ngoặc.
Áp dụng tính trung bình cua rmootj phạm vi ô trong Excel giúp bạn không phải tìm các tổng riêng lẻ và thực hiện phương trình phân chia riêng trên tổng số của bạn.
Ví dụ hàm AVERAGE trong Excel:
Cho ví dụ dưới đây, yêu cầu tính lương trung bình các tháng dựa tren các số liệu trong bảng.
Bước 1: Để hiện thị kết quả tại ô C9 bạn nhập công thức: AVERAGE(C3:C8).
Hàm tính trung bình trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả.
Kết quả hàm tính trung bình trong Excel
MIN và MAX
Mục đích việc sử dụng hàm MIN là tìm ra số nhỏ nhất trong một phạm vi giá trị và MAX là tìm số lớn nhất trong một phạm vi giá trị nào đó.
- Hàm Max
Cú pháp hàm MAX: =MAX(number1, number2,…)
Trong đó: Number1, Number 2 là giá trị bạn cần so sánh.
Ví dụ hàm MAX
Dựa vào bảng điểm số của các bạn học sinh, yêu cầu tìm ra điểm cao nhất trong lớp.
Bước 1: Nhấn chuột vào ô bạn muốn hiển thị kết quả >> Nhập công thức =MAX(C2:E6)
Hàm Max trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả.
Hiển thị kết quả khi sử dụng hàm Max trong Excel
- Hàm MIN
Cú pháp hàm MIN trong Excel: =MIN(number1, number2,…)
Trong đó: number1, number 2, là giá trị cần so sánh.
Ví dụ về hàm MIN
Dựa vào bảng điểm số, bạn cần tìm ra điểm số thấp nhất (MIN) trong lớp.
Bước 1: Tại ô muốn hiển thị kết quả bạn nhập công thức: =MIN(E2:E6).
Sử dụng hàm MIN trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả.
Kết quả khi sử dụng hàm MIN trong Excel
Hàm TRIM trong Excel
Hàm TRIM trong Excel sử dụng để loại bỏ các khoảng trắng trước và sau văn bản được nhập vào ô, thay vì việc bạn thao tác xoá hoặc thêm từng khoảng trắng thì bạn có thể sử dụng hàm TRIM để loại bỏ hoặc thêm cùng một lúc.
Cú pháp: =TRIM(text)
Trong đó:
– TRIM: Lệnh thực hiện thao tác
– Text: Ô dữ liệu có đoạn văn bản chứa khoảng trắng cần loại bỏ.
Ví dụ về cách sử dụng hàm TRIM trong Excel
Cho ví dụ, yêu cầu loại bỏ các khoảng trống dữ liệu bằng hàm TRIM.
Bước 1: Tại ô cần hiển thị kết quả, bạn nhập công thức: =TRIM(B2) ở đây B2 là ô tham chiếu.
Hàm TRIM trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả. Tiếp đó bạn giữ chuột kéo xuống hết các ô Excel còn lại để đảm bảo kết quả được hiển thị hết.
Kết quả sử dụng hàm TRIM trong Excel
Công thức PRODUCT
Sử dụng hàm PRODUCT mục đích là ghép các ô lại với nhau
Cú pháp: =PRODUCT(X:X)
Ví dụ: = PRODUCT(A1: A30) sẽ ghép nhiều ô lại với nhau, vì vậy A1 * A2 * A3, v.v.
Hàm PRODUCT trong Excel
AutoSum
Hàm SUM là hàm được sử dụng nhiều nhất trong Excel, trường hợp nếu bạn thực hiện phép cộng với số lượng ít thì có thể thực hiện thử công. Nhưng một số trường hợp bạn cần phải cộng hàng trăm phép cộng thì điều này không thực sự là tối ưu. Thay vào đó người dùng có thể sử dụng hàm AutoSum ó sẵn trên Formulas trên thanh menu hoặc sử dụng đơn giản phím tắt Alt + =
Ví dụ sử dụng hàm AutoSum
Bước 1: Mở file Excel mà bạn cần tính tổng >> Chọn AutoSum để màn hình tự động nhập hàm tính tổng.
Sử dụng AutoSum tính tổng
Bước 2: Nhấn Enter để hiển thị kết quả.
Kết quả khi sử dụng hàm Autosum
CONCAT / CONCATENATE
Mục đích việc sử dụng các hàm CONCAT / CONCATENATE để kết hợp nhiêu ô, phạm vi hoặc chuỗi dữ liệu vào một ô, sử dụng phổ biến chức năng này để kết hợp tên và họ vào một ô.
Cú pháp: CONCATENATE: =CONCATENATE (text1, [text2], …)
Trong đó:
– Text 1: Là chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.
– Text 2: Chuỗi có thể có hoặc không.
Ví dụ sử dụng hàm CONCATENATE trong Excel
Cho bảng dữ liệu sao, yêu cầu nối Họ và Tên bằng cách sử dụng hàm CONCATENATE.
Bước 1: Ở ô muốn hiển thị kế quả bạn nhập công thức: =CONCATENATE(B4,C4).
Sử dụng hàm Concatenate trong Excel
Bước 2: Nhấn Enter để nhận kết quả.
Hàm Concatenate trong Excel
Hàm NOW
Hàm NOW được sử dụng để giúp người dùng xem được ngày giờ hiện tại ở một bảng tính cụ thể. Bạn nhập NOW() tại ô bạn muốn xem.
Nếu bạn muốn xem một ngày trong tương lai từ bây giờ, bạn sử dụng công thức =NOW()+32
Chú ý hàm NOW không nhận bất kỳ đối số nào vì vậy bạn không được đặt bất cứ ký tự gì vào dấu ngoặc.
Ví dụ cách sử dụng hàm NOW trong Excel.
Cho bảng dữ liệu, yêu cầu dùng hàm NOW để trả về ngày, thời gian hiện tại.
Bước 1: Tại ô cần hiển thị kết quả, bạn nhập công thức =NOW()
Sử dụng hàm NOW trong Excel
Bước 2: Nhấn Enter để hiển thị kết quả.
Kết quả sử dụng hàm NOW trong Excel
Hàm CONDITIONAL FORMATTING
Xét về mặt kỹ thuật thì đây không được coi là một công thức, nó chính là một công cụ hữu ích được tích hợp trong Excel.
Bạn nhấn chọn Home >> Chọn Styles >> Nhấn Conditional formatting, để bạn có thể chọn nhiều tuỳ chọn sẽ đưa ra kết quả nếu số điều là đúng.
Ví dụ cách sử dụng Conditional formatting
Cho bảng dữ liệu, yêu cầu làm nổi bật cách ô có chứ chữ Việt Nam trong cột Quốc Tịch.
Bước 1: Sử dụng chuột bôi đen vùng dữ liệu cần làm nổi bật theo mong muốn của bạn. Tiếp đó nhấn chọn Home >> Conditional formatting >> Highlight Cells Rules >> Text that Contains…
Sử dụng hàm Conditional formatting
Bước 2: Chờ hộp thoại Text that Contains hiện lên >> Bạn nhập chữ Việt Nam vào ô Format Cells that contain the next >> Nhấn OK để hoàn tất.
Kết quả sử dụng hàm Conditional formatting
Kết hợp hàm INDEX + MATCH
Việc kết hợp 2 hàm này với mục đích để truy xuất dữ liệu đa chiều, điều này sẽ giúp bạn khắc phục những hạnc hế gây ra những khó khăn của hàm VLOOKUP.
Cú pháp: = INDEX (danh sách các giá trị, MATCH (những gì bạn muốn tra cứu, cột tra cứu, định danh sắp xếp))
Bạn có thể tìm kiếm toàn bộ bảng tính cho các giá trị thay vì chỉ có thể tìm kiếm cột ngoài cùng bên trái.
Ví dụ sử dụng hàm INDEX và hàm MATCH trong Excel
Cho bảng dữ liệu dưới đây, dựa vào Sản phẩm và hãng sản xuất, yêu cầu điền đơn giá vào cột D trong bảng 1.
Bước 1: Trong ô D4 để hiển thị kết quả bạn nhập công thức:
=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))
Trong đó, kết quả trả về giá trị tương ứng của sản phẩm Mouse là hàng 4 trong bảng 2, cột 2 (Cột Samsung) trong bảng 2 là 5.
Sử dụng hàm INDEX bà hàm MATCH trong Excel
Bước 2: Copy công thức để hiển thị kết quả ở những ô tiếp theo.
Kết quả sử dụng hàm INDEX và hàm MATCH trong Excel
Hàm PROPER trong Excel
Đưa hàm PROPER vào sử dụng trong Excel nhằm mục đích thao tác chuyển chữ thường thành chữ in hoa một cách nhanh chóng.
Cú pháp: =PROPER(text)
Trong đó:
– Text: Dữ liệu chứa đoạn văn bản muốn chuyển đổi.
Ví dụ sử dụng hàm PROPER trong Excel.
Cho bảng dữ liệu, yêu cầu chuyển đổi chữ bằng cách sử dụng hàm PROPER.
Bước 1: Tại ô bạn muốn hiên thị kế quả bạn nhập công thức: =PROPER(B14).
Sử dụng hàm Proper trong Excel
Bước 2: Nhấn Enter để nhận kết quả, sau đó dùng trỏ chuột kéo xuống hết các ô trong Excel để hiển thị tất cả những kết quả bên dưới.
Kết quả khi sử dụng hàm Proper trong Excel
Hàm AND
Hàm AND được sử dụng để kiểm tra xem kết quả đó cho ra đó là đúng hay sai.
Ví dụ: =AND(A1= “Tốt”, B2>10) lcus này sẽ xuất hiên kết quả TRUE nếu A1 là Tốt và giá trị của B2 >10. Bạn cũng có thể kiểm tra nhiều giá trị hơn hai, chỉ cần nhập thêm vào dấu phẩy.
Cú pháp: AND: =AND(Điều kiện 1, Điều kiện 2,…)
Trong đó: Điều kiện 1, Điều kiện 2 là mệnh đồ logic.
Ví dụ sử dụng hàm AND trong Excel.
Bước 1: Tại ô E3 nơi cần hiển thị kết quả bạn nhập công thức =AND(C3>=5, D3>=5)
Trong đó:
– AND: Lệnh hàm bắt buộc phải có
– C3>=5 và D3>=5: Điều kiện để xét học sinh giỏi là điểm môn toàn và văn lớn hơn hoặc bằng 5.
Sử dụng hàm AND trong Excel
Bước 2: Nhấn Enter để nhận kết quả, sau đó bạn nhấn trò cuột kéo xuống kết các ô Excel bên dưới để hiển thị kết quả.
Kết quả sử dụng hàm AND trong Excel
Với kết quả như bảng, thì những bạn hiển thị kết quả FLASE là LÊ B và Phạm D không đáp ứng được tiêu chí của đề bài là do 2 môn toán văn lớn hơn hoặc bằng 5 nên 2 bạn này không được học sinh giỏi.
Tổng kết
Trên đây Box.edu đã tổng hợp các công thức trong Excel thường được sử dụng nhiều nhất. Hi vọng bài viết mang đến cho bạn đọc nhiều hữu ích.
