Talent Network là gì?

Gia nhập Talent Network của chúng tôi sẽ giúp bạn nâng cao khả năng tìm kiếm việc làm. Cho dù bạn ứng tuyển một công việc nào đó hoặc đơn giản là cập nhật thông tin của mình, chúng tôi cũng luôn mong muốn được kết nối cùng bạn.

Bạn đang xem: Các hàm thông dụng trong excel

Vì sao bạn nên gia nhập Talent Network? Nhận thông báo việc làm mới phù hợp với sự quan tâm của bạn Cập nhật các thông tin mới nhất về công ty Chia sẻ cơ hội việc làm với gia đình, bạn bè thông qua mạng xã hội hoặc email

Hãy gia nhập Talent Network của chúng tôi ngay hôm nay!


Tiếp Tục
Ngành nghề
Bán hàng / Kinh doanh
Bán lẻ / Bán sỉ
Dịch vụ khách hàng
Kế toán / Kiểm toán
Mới tốt nghiệp / Thực tập
Xuất nhập khẩu
Nơi làm việc
Hồ Chí Minh
Hà Nội
Bà Rịa - Vũng Tàu
Bình Phước
Đắk Lắk
Đắk Nông
Gia Lai
Hà Giang
Hải Dương
Hải Phòng
Hưng Yên
Khánh Hòa
Kiên Giang
Nam Định
Quảng Ninh
Tây Ninh
Thái Bình
Tuyên Quang
Vĩnh Phúc Tìm kiếm

TOP 20 hàm Excel thông dụng mà dân văn phòng nhất định phải biết


Excel đóng vai trò quan trọng trong đời sống công việc hàng ngày. Cùng điểm danh 20 hàm Excel phổ biến giúp bạn tiết kiệm thời gian khi làm việc trên Excel.

Bạn đã nắm được các hàm cơ bản Excel?

1. Hàm tính tổng SUM

Hàm SUM được coi là hàm cơ bản nhất trong danh sách những hàm cơ bản trong Excel dùng để tính tổng tất cả các dãy số trong ô. Hàm SUM có công thức như sau:

Để tính tổng của 2 hoặc nhiều giá trị khác nhau:

=SUM (Number1,Number2,Number3…)Trong đó Number1, Number2, Number3 là giá trị của các số hạng cần tính tổng. Kếtquả của hàm SUM sẽ bằng tổng giá trị được chọn.

Ví dụ: =SUM(10,30)

*

– Công thức Excel cơ bản

Để tính tổng của 2 hoặc nhiều ô khác nhau:

Để tính tổng của tất cả các ô nằm trong trường dữ liệu bạn áp dụng công thức:

=SUM(tham số 1:tham số 2)Trường dữ liệu sẽ được quy định với tham số 1 là ô tính bắt đầu và tham số 2 là ô tính kết thúc.

Ví dụ: =SUM(B2:B6)

Bạn cũng có thể sử dụng hàm SUM kèm một thao tác tính toán bổ sung, ví dụ như cộng tổng giá trị của 2 ô A1 và A2 rồi chia 2 theo công thức sau:

=SUM(A1,A2)/2

Bạn cũng có thể sử dụng nút Auto
Sum trong tab Home để tính tổng của nhiều ô trong cùng một hàng hoặc cột chỉ bằng vài thao tác chuột.

2. HÀMSUMIF (Hàm điều kiện)

Cũng là hàm tính tổng nhưng mà nó là hàm tính tổng có điều kiện.

Cú pháp:SUMIF(Range,Criteria,Sum_range). Trong đó:

Range: Làhàng hoặc cột mà bạn đã chọn

Criteria: Đặt điều kiện, điều kiệnnày bạn có thể đặt là số, là biểu thức hoặc là chuỗi đều được.

Sum_range: Là các ô mà bạn thực sự cần tính tổng.

Ví dụ: =SUMIF(B3:B8,”

Tính tổng của các giá trị trong vùng chọn từ B2 đến B5 và với điều kiện là các giá trị nhỏ hơn hoặc bằng8.

3. Hàm tính tổng giá tiền sản phẩm SUMPRODUCT

Để tính tổng sản phẩm của các số tương ứng (kiểu tính tổng tiền của các sản phẩm dựa trên số lượng và giá tương ứng của từng sản phẩm) trong một hoặc nhiều dãy, bạn hãy sử dụng hàm SUMPRODUCT mạnh mẽ của Excel.

Ví dụ dưới đây dùnghàm SUMPRODUCTđể tính tổng số tiền đã tiêu.

Cụ thể ở trên, hàm SUMPRODUCT đã thực hiện phép tính: (2*1000) + (4*250) + (4*100) + (2*50) = 3500.

Phạm vi tính tổng phải có cùng kích thước, nếu không Excel sẽ hiển thị lỗi #VALUE!

Nếu giá trị trong ô không phải dạng số thì SUMPRODUCT sẽ mặc định giá trị của chúng là 0.

4. Hàm Product

Hàm Product dùng để tính tích (nhân) các con số. Lương thực lãnh của nhân viên bằng tích của số ngày làm việc và lương ngày cho sẵn.

Công thức =Product(C7,D7).5. Hàm Count

Hàm Count để đếm số ô chứa dữ liệu. Bạn có thể dùng hàm này để đếm số ngày làm việc của nhân viên trong một tháng.

Công thức =Count (Value 1,Value 2,...).

Hàm đếm ô trống COUNTBLANK

Bạn sử dụng hàm này khi cần đếm các ô trống.

Cú pháp =COUNTBLANK(phạm vi bảng tính cần đếm).

Hàm đếm ô không trống COUNTA

Hàm COUNTA có chức năng dùng để đếm số ô có chứa nội dung bất kỳ bao gồm chữ số, chữ cái hay biểu tượng, hay nói cách khác nó dùng để đếm các ô không trống.

Cú pháp =COUNTA(phạm vi bảng tính cần đếm).6. Hàm Rank

Hàm Rank dùng để xếp thứ hạng theo dữ liệu tăng dần hoặc giảm dần nhanh chóng.

Công thức =Rank(Number,Ref,Order).Number là số cần xếp hạng trong danh sách. Ref dãy số so sánh có chứa số cần xếp hạng. Order kiểu xếp hạng, có hai cách xếp tăng dần số 0 (hoặc mặc định nếu không nhập), số 1 giảm dần.

7. Hàm If

Hàm điều kiện If được dân văn phòng thường xuyên sử dụng để so sánh giữa một giá trị với giá trị mong muốn.

Công thức của hàm If (điều kiện,giá trị 1,giá trị 2). Nếu như điều kiện đúng thì kết quả hàm trả về là giá trị 1, ngược lại trả về giá trị 2.

Ví dụ: bạn có bảng điểm của nhân viên tham gia kỳ thi thăng chức và cần xét kết quả để biết nhân viên đó đạt hay không đạt. Nếu điểm trung bình lớn hơn 5 là đạt, còn lại là không đạt.

=If (điểm trung bình >=5,"đạt","không đạt").

8. Hàm Vlookup, Hlookup

Tìm kiếm theo cột dùng Vlookup, theo hàng dùng Hlookup. Để tìm kiếm phụ cấp của từng nhân viên dựa vào chức vụ của người đó và bảng phụ cấp, bạn dùng hàm Vlookup hoặc Hlookup.

Công thức Vlookup hoặc Hlookup (Giá trị dò tìm trong bảng chính, Chọn khối phạm vi bảng dò tìm trong bảng phụ, Chỉ định cột dò tìm trong bảng phụ, Cách dò tìm 0 (chính xác) hoặc 1 (tương đối)).9. Hàm lọc số lớn nhất và nhỏ nhất MAX & MIN

Hàm MAX và hàm MIN sẽ giúp bạn lọc giá trị lớn nhất và nhỏ nhất trong một trường dữ liệu hoặc cả bảng tính. Công thức của hai hàm MAX và MIN như sau:

MAX(Number1:Number2)MIN(Number1:Number2)Trong đó: Number1, Number2 là vùng giá trị cần so sánh

Ví dụ: Để tìm giá trị lớn nhất hoặc nhỏ nhất từ ô A1 đến ô A5, bạn nhập công thức =MAX(A1:A5) hoặc =MIN(A1:A5).

10. Hàm tính trung bình AVERAGE

Hàm tính trung bình cũng được coi là một trong các hàm cơ bản trong Excel, sử dụng để tính trung bình cộng của một trường dữ liệu. Công thức của hàm AVERAGE như sau:

AVERAGE(Number1,Number2…)Trong đó:

Number1: là số thứ nhất, tham chiếu ô hoặc phạm vi dữ liệu mà bạn muốn tính trung bình,

Number 2: Các số, tham chiếu ô hoặc phạm vi bổ sung mà bạn muốn tinh trung bình. Number2 là tùy chọn, không bắt buộc.

Ví dụ: Để tính trung bình cộng giá trị từ ô A1 đến A5, bạn nhập công thức =AVERAGE(A1:A5)

11. Percentage

Đây là công thức tính tỷ lệ phần trăm trong Excel, bạn hãy nhập các ô tìm tỷ lệ phần trăm, để chuyển đổi giá trị thập phân thành tỷ lệ phần trăm, chọn ô và nhấp vào tab trang chủ chọn Percentage.

Không có công thức Excel để tính phần trăm cụ thể, nhưng chúng ta có thể quy đổi và dạng tỉ lệ phần trăm để các bạn không bị vướng mắc.

Công thức áp dụng như sau:Tỷ lệ phần trăm (%)=1 phần/tổng số12. Hàm
PRODUCT (Hàm nhân)

Sử dụng hàm nhân rất đơn giản và cũng tương tự như hàm SUM tính tổng ở bên trên. Ví dụ ta muốn nhân tất cả các ô tính từ A3 cho đến F3 thì ta sẽ có công thức như sau:

=PRODUCT(A3:F3) và nhấn Enter để thực hiện.Một cách khác nếu như bạn không nhớ hàm Product và các này có thểáp dụng cho tất cả cácphép tính Cộng(+), Trừ(-), Nhân(*)và Chia(/) nhé.

Ví dụ: Để nhân tất cả các số từ A3 cho đến F3 thì bạn hãy thực hiện công thức như sau:=A3*B3*C3*D3*E3và nhấn Enter để thực hiện.

13. Nối chuỗi văn bản

Để nối chuỗi văn bản bạn sử dụng ký tự &, muốn chèn thêm dấu cách thì sử dụng " " (mở ngoặc kép, cách, đóng ngoặc kép).

14. Hàm LEFT

Để lấy những ký tự bên trái của một chuỗi sử dụng hàm LEFT.

Công thức =LEFT(ô chứa chuỗi cần lấy, số ký tự muốn lấy)15. Hàm RIGHT

Dùng hàm RIGHT khi cần lấy những ký tự từ phía bên phải của chuỗi.

Công thức =RIGHT(ô chứa chuỗi cần lấy, số ký tự muốn lấy)

16. Hàm MID

Dùng để lấy các ký tự bắt đầu từ đâu (tính từ trái sang).

Công thức =MID(ô chứ chuỗi cần lấy, vị trí lấy, số ký tự cần lấy). Trong ví dụ này ta lấy 3 ký tự, bắt đầu từ ký tự thứ 5.

17. Hàm LEN

Nếu bạn muốn đếm tổng số ký tự hay còn gọi là chiều dài chuỗi ký tự trong một ô bao gồm cả chữ cái, chữ số và khoảng trắng, hãy nghĩ đến hàm =LEN.

Công thức =LEN(ô chứa chuỗi cần đếm).

Ví dụ: để biết được có bao nhiêu ký tự trong ô B4, chỉ cần di chuyển đến một ô khác bất kỳ và gõ vào cú pháp=LEN(B4). Ngay lập tức, Excel sẽ trả về một con số giá trị tổng các ký tự trong ô B4.

18. Hàm FIND

Dùng để tìm vị trí của một chuỗi con trong chuỗi.

Công thức =FIND("chuỗi con", ô chứa chuỗi). Trong ví dụ này ta tìm vị trí của chuỗi con "am" trong chuỗi "example text", và nó ở vị trí thứ 3.

19. Hàm YEAR, MONTH, DAY

Để điền ngày vào Excel, bạn sử dụng "/" hoặc "-". Để điền thời gian, sử dụng ":" (dấu hai chấm). Bạn cũng có thể điền ngày và thời gian trong cùng một cột.

Để lấy năm trong ngày tháng năm bạn sử dụng hàm YEAR. Tương tự để lấy tháng dùng hàm MONTH và lấy ngày dùng hàm DATE.

Công thức =YEAR(cột chứa năm cần lấy).

20. Hàm DATE

Để thêm số ngày vào ngày tháng chỉ cần sử dụng công thức đơn giản là lấy ô chứa ngày tháng + với số ngày bạn muốn thêm, như ví dụ dưới đây:

Để thêm số năm, tháng và ngày vào ngày tháng nào đó, bạn sẽ phải sử dụng đến hàm DATE.

Công thức =DATE(YEAR(ô chứa ngày tháng gốc)+số năm cần thêm,MONTH(ô chứa ngày tháng gốc)+số tháng cần thêm,DAY(ô chứa ngày tháng gốc)+số ngày cần thêm).

Chú ý: Hàm DATE chỉ có 3 đối số là năm, tháng và ngày. Excel biết rằng 6+2=8= Tháng 8, tháng 8 chỉ có 31 ngày, nên nó sẽ tự động tính sang tháng tiếp theo (23 tháng 8 + 9 ngày thành 1 tháng 9).

Bài viết này có thể xem là bài viết tổng hợp nhiều chức năng của Microsoft Excel, rất cần thiết đối với người mới bắt đầu học về Excel. Trong bài viết này, Học Excel Online sẽ liệt kê các hàm trong excel, các văn bản, phép toán, thống kê, logic và các chức năng khác cần thiết nhất cùng với các ví dụ về công thức và liên kết tới các tài nguyên có liên quan.

Excel cung cấp một số lượng lớn các chức năng để phân tích, kiểm toán và tính toán dữ liệu bằng cách tận dụng tối đa các hàm trong excel cung cấp. Một trong số những chức năng này được sử dụng bởi nhiều người sử dụng Excel hàng ngày và cũng có một số người chỉ sử dụng những phép toán này trong trường hợp cụ thể.

Trong hướng dẫn này, chúng ta sẽ không khám phá từng chức năng trong Microsoft Excel mà chỉ đưa ra cho bạn cái nhìn tổng quan về những chức năng hữu ích trong Excel đặc biệt là đưa ra cái nhìn tổng quan về cách dùng hàm trong excel được sử dụng nhiều nhất.

Hầu hết các chức năng được liệt kê dưới đây là các hàm bảng tính Excel được sử dụng như một phần của công thức trong một ô. Các mục cuối cùng bao gồm các chức năng Excel tùy chỉnh, chức năng người dùng định nghĩa (UDF). Đây là những chức năng dựa trên VBA để mở rộng những điều cơ bản của Excel với chức năng tuỳ chỉnh. Chọn một danh mục để vào danh sách các chức năng với các mô tả ngắn và liên kết đến các ví dụ sử dụng.


Xem nhanh


Chức năng văn bản trong Excel (các chức năng chuỗi)Chức năng TEXTHàm SUBSTITUTEHàm VALUECác công thức toán học trong Excel
Tìm tổng của các ô
Các hàm thống kê trong Excel
Hàm lookup trong Excel và các hàm liên quan
Các hàm tài chính trong Excel

Chức năng văn bản trong Excel (các chức năng chuỗi)

Có rất nhiều chức năng của Microsoft Excel để áp dụng cho chuỗi văn bản. Dưới đây là những điều cơ bản nhất về các hàm cơ bản trong excel:

Chức năng TEXT

TEXT(value, format_text) được sử dụng để chuyển đổi một số hoặc một ngày thành một chuỗi kiểm tra ở định dạng được chỉ định, trong đó:

value là một giá trị số bạn muốn chuyển đổi sang văn bản. Format_text là định dạng mong muốn. Các công thức sau đây thể hiện hoạt động của chức năng Excel TEXT:



=TEXT(A1,”€#,##0.00″) – chuyển một số trong A1 thành một chuỗi ký tự tiền tệ như “€3.00”.

*

Ví dụ cho công thức TEXT:

Hàm TEXT dùng để chuyển đổi một ngày sang định dạng văn bản Công TEXT dùng để chuyển một số thành dạng văn bản

Hàm CONCATENATE

CONCATENATE(text1, , …) được thiết kế để nối nhiều đoạn văn bản lại với nhau hoặc kết hợp các giá trị từ một vài ô vào một ô duy nhất. Một kết quả tương tự có thể đạt được bằng cách sử dụng Excel & operator, như thể hiện trong ảnh chụp màn hình sau.

*

Hàm TRIM

TRIM(text) được dùng để loại bỏ hàng đầu, dấu cách cũng như các khoảng trống dư thừa giữa các từ. Trường hợp văn bản hoặc một chuỗi văn bản hay tham chiếu đến ô có chứa văn bản mà bạn muốn loại bỏ không gian. Ảnh chụp màn hình sau minh họa một ví dụ về cách sử dụng:

*

Hàm SUBSTITUTE

SUBSTITUTE(text, old_text, new_text, ) sẽ thay thế một bộ ký tự bằng một ký tự khác trong một ô được chỉ định hoặc một chuỗi văn bản. Cú pháp của hàm SUBSTITUTE như sau:

Text – chuỗi văn bản ban đầu hoặc tham chiếu đến ô mà bạn muốn thay thế một số ký tự nhất định. Old_text – các ký tự bạn muốn thay thế. New_text – các ký tự mà bạn mới bạn muốn dùng để thế chỗ văn bản cũ. Nth_appearance – một tham số tùy chọn cho old_text mà bạn muốn thay thế bằng new_text. Sau đó, mỗi lần xuất hiện của văn bản cũ sẽ được thay thế bằng văn bản mới. Ví dụ: công thức SUBSTITUTE sau thay thế tất cả các dấu phẩy trong ô A1 bằng dấu chấm phẩy:

=SUBSTITUTE(A2, “,”, “;”)

*

Công thức SUBSTITUTE:

– Loại bỏ ngắt dòng trong một ô – Chuyển đổi chuỗi văn bản với dấu phân cách tùy chỉnh thành ngày

Hàm VALUE

VALUE(text) – chuyển đổi một chuỗi văn bản thành một số.

Hàm này thực sự hữu ích khi chuyển đổi các giá trị định dạng văn bản đại diện cho các con số thành các con số có thể được sử dụng trong các công thức Excel khác và tính toán.

Ví dụ cho hàm VALUE:

Chuyển đổi chữ số định dạng văn bản thành số Hàm VALUE dùng để chuyển đổi văn bản thành ngày Hàm EXACT EXACT(text1, text2) so sánh hai chuỗi văn bản và trả về giá trị TRUE nếu cả hai giá trị đều giống nhau, và trả về FALSE nếu không giống nhau.

Ví dụ: nếu A2 là “apples” và B2 là “Apples”, công thức =EXACT(A2, B2) sẽ trả về FALSE vì chúng không khớp chính xác.

Hàm EXACT hiếm khi được sử dụng riêng, nhưng lại hữu ích khi kết hợp với những hàm khác để tạo nên tác vụ phức tạp hơn, ví dụ như kết hợp với Vlookup dùng thao tác với văn bản trong Excel.

Các hàm sử dụng để chuyển đổi văn bản (UPPER, LOWER, PROPER)

Microsoft Excel cung cấp 3 hàm văn bản để chuyển đổi giữa UPPER, LOWER, PROPER.

UPPER(text) – chuyển đổi tất cả các ký tự trong một chuỗi ký tự được chỉ định sang chữ hoa.

LOWER(text) – thay đổi tất cả các chữ in hoa trong một chuỗi văn bản thành chữ thường.

PROPER(text) – viết hoa chữ cái đầu tiên của mỗi từ và chuyển đổi tất cả các chữ cái khác sang chữ thường.

Trong cả ba chức năng, đối số văn bản có thể là một chuỗi văn bản kèm theo dấu ngoặc kép, một tham chiếu đến một ô có chứa văn bản hoặc một công thức trả về văn bản.

*

Trích xuất các ký tự văn bản (LEFT, RIGHT, MID)

Nếu bạn cần một công thức để trả về một số ký tự nhất định từ một chuỗi văn bản, sử dụng một trong các hàm Excel sau.

LEFT(text, ) – trả về một số ký tự nhất định bắt đầu từ đầu chuỗi văn bản.

RIGHT(text,) – trả về một số ký tự được chỉ định bắt đầu từ cuối chuỗi văn bản.

MID(text, start_num, num_chars) – trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu ở bất kỳ vị trí nào bạn chỉ định.

Trong các hàm này, bạn cung cấp các đối số sau:

– Text – một chuỗi văn bản hoặc một tham chiếu đến một ô chứa các ký tự mà bạn muốn trích xuất. – Start_num – chỉ ra nơi bắt đầu (tức là vị trí của ký tự đầu tiên bạn muốn trích xuất). – Num_chars – số ký tự bạn muốn trích xuất.

*

Các hàm logic trong Excel

Microsoft Excel cung cấp một số ít các chức năng logic đánh giá một điều kiện xác định và trả về giá trị tương ứng.

AND, OR, XOR chức năng AND(logical1, , …) – trả về TRUE nếu tất cả các đối số đánh giá TRUE, FALSE nếu ngược lại.

OR(logical1, , …) – trả về TRUE nếu ít nhất một trong các đối số là TRUE.

XOR(logical1, ,…) – trả về một giá trị hợp lệ duy nhất trong tất cả các đối số. Chức năng này đã được giới thiệu trong Excel 2013 và không có sẵn trong các phiên bản trước đó.

*

Hàm NOT

NOT(logical) – đảo ngược giá trị đối số của nó, nghĩa là nếu đánh giá lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại.

Ví dụ, cả hai công thức sau đây sẽ trả về FALSE:

=NOT(TRUE)

=NOT(2*2=4)

Hàm IF

Hàm IF trong Excel đôi khi được gọi là “hàm điều kiện” vì nó trả về một giá trị dựa trên điều kiện mà bạn chỉ định. Cú pháp của hàm IF như sau:

IF(logical_test, , ) Công thức IF kiểm tra các điều kiện được biểu diễn trong đối số logical_test và trả về một giá trị (value_if_true) nếu điều kiện được đáp ứng và một giá trị khác (value_if_false) nếu điều kiện không được đáp ứng.

Ví dụ, công thức = IF =IF(A1””, “good”, “bad”) trả về “good” nếu có bất kỳ giá trị nào trong ô A1, “bad” nếu không.

Và đây là một ví dụ về công thức hàm IF kết hợp thể hiện điểm thi trong ô A2:

=IF(A2>80, “Brilliant”, IF(A2>50, “Good”, IF(A2>30, “Fair”, “Poor”)))

*

Hàm IFERROR và IFNA

Cả hai hàm đều được sử dụng để kiểm tra lỗi trong một công thức tính nhất định, và nếu có lỗi xảy ra, các hàm MS Excel trả lại một giá trị được chỉ định thay thế.

IFERROR(value, value_if_error) – kiểm tra lỗi của công thức hoặc biểu thức đánh giá. Nếu có lỗi, công thức trả về giá trị được cung cấp trong đối số value_if_error, nếu không, kết quả của công thức sẽ được trả về. Hàm này xử lý tất cả các lỗi Excel có thể xảy ra, bao gồm VALUE, N/A, NAME, REF, NUM và những lỗi khác. Nó có sẵn trong Excel 2007 và những phiên bản cao hơn.

IFNA(value, value_if_na) – được giới thiệu trong Excel 2013, nó hoạt động tương tự như IFERROR, nhưng chỉ xử lý lỗi #N/A.

Các ví dụ sau minh họa công thức IFERROR đơn giản nhất:

*

Các công thức toán học trong Excel

Excel có vô vàn chức năng cơ bản và tiên tiến để thực hiện các phép toán, tính toán hàm mũ, các đối số, yếu tố và các dạng tương tự. Chúng ta hẳn sẽ mất tới vài trang chỉ để xuất bản danh sách các chức năng. Vì vậy, chúng ta hãy thảo luận về một vài chức năng toán học cơ bản có thể hữu ích cho việc giải quyết các công việc hàng ngày của bạn.

Tìm tổng của các ô

Bốn hàm trong Excel cần thiết để thêm giá trị của các ô trong một phạm vi xác định theo dưới đây.

Hàm SUM

SUM(number1,,…) trả về tổng các đối số của nó. Các đối số có thể là các số, các tham chiếu ô hoặc các giá trị số bằng công thức.

Ví dụ, công thức toán học đơn giản =SUM(A1:A3, 1) cộng các giá trị trong ô A1, A2 và A3, và thêm 1 vào kết quả. Các hàm SUMIF và SUMIFS (tổng điều kiện) Cả hai hàm này dùng để cộng các ô trong một phạm vi xác định đáp ứng một điều kiện nhất định. Sự khác biệt là SUMIF có thể đánh giá chỉ một tiêu chuẩn, trong khi SUMIFS, được giới thiệu trong Excel 2007, cho phép sử dụng nhiều tiêu chí. Hãy lưu ý rằng thứ tự của các đối số là khác nhau trong mỗi chức năng:

SUMIF(range, criteria, ) SUMIFS(sum_range, criteria_range1, criteria1, , …) Range / criteria_range – phạm vi của các ô được đánh giá bởi các tiêu chí tương ứng. criteria – điều kiện cần phải đáp ứng. Sum_range – các ô tổng hợp nếu điều kiện được đáp ứng. Ảnh chụp màn hình sau đây cho biết ý nghĩa của các chức năng SUMIF và SUMIFS trên dữ liệu thực tế:

Hàm SUMPRODUCT

SUMPRODUCT(array1,array2, …) là một trong số ít các chức năng của Microsoft Excel dùng để xử lý mảng. Nó kết hợp với các thành phần mảng được cung cấp và trả về tổng sản phẩm.

Bản chất của hàm SUMPRODUCT có thể khó nắm bắt vì vậy các bạn có thể theo dõi các ví dụ cụ thể của hàm này trong bài viết dành riêng cho hàm SUMPRODUCT để có được hiểu biết đầy đủ.

Tạo các số ngẫu nhiên (RAND và RANDBETWEEN) Microsoft Excel cung cấp 2 chức năng để tạo ra các số ngẫu nhiên. Cả hai đều là các hàm không ổn định, có nghĩa là một số mới được trả về mỗi khi bảng tính thực hiện tính toán.

RAND() – trả về một số thực (số thập phân) ngẫu nhiên giữa 0 và 1.

RANDBETWEEN(bottom, top) – trả về một số nguyên ngẫu nhiên giữa số dưới cùng và số trên cùng mà bạn chỉ định.

Chức năng làm tròn

Có một số chức năng để làm tròn số trong Excel, và Hướng dẫn làm tròn của Excel của chúng tôi đã giúp bạn giải thích cách sử dụng những chức năng đó dựa trên tiêu chí của bạn. Vui lòng nhấp vào tên của chức năng để tìm hiểu cú pháp và ví dụ về sử dụng.

ROUND – làm tròn số đến số chữ số được chỉ định.

ROUNDUP – làm tròn lên, đến số ký tự được chỉ định.

ROUNDDOWN – làm tròn xuống, đến số ký tự được chỉ định.

MROUND – làm tròn số đến một bội số được chỉ định.

FLOOR – làm tròn số xuống, đến bội số được chỉ định.

CEILING – tròn số lên, đến bội số được chỉ định.

INT – làm tròn số xuống số nguyên gần nhất.

TRUNC – cắt bớt số số đến số nguyên gần nhất.

EVEN – làm tròn số đến số nguyên chẵn gần nhất.

ODD – làm tròn số lên đến số nguyên lẻ gần nhất.

Trả về số dư sau khi chia (hàm MOD)

MOD(number, divisor) trả về số sư sau khi chia.

Hàm này thực sự rất hữu ích trong nhiều trường hợp khác nhau, ví dụ như:

Tính tổng các giá trị trong mỗi hàng, hoặc một hàng được chỉ định Thay đổi màu sắc của hàng trong Excel

Các hàm thống kê trong Excel

Trong số nhiều hàm thống kê của Excel, có một số hàm có thể áp dụng để sử dụng cho những công việc mang tính chuyên nghiệp cao.

Tìm các giá trị lớn nhất, nhỏ nhất và trung bình

MIN(number1, , …) – trả về giá trị nhỏ nhất từ danh sách các đối số.

MAX(number1, , …) – trả về giá trị lớn nhất từ danh sách các đối số

AVERAGE(number1, , …) – trả giá trị trung bình của các đối số.

SMALL(array, k) – trả về giá trị nhỏ nhất thứ k trong mảng.

LARGE (mảng, k) – trả về giá trị lớn nhất thứ k trong mảng.

Ảnh chụp màn hình sau minh họa các hàm thống kê cơ bản.

Đếm ô

Dưới đây là danh sách các hàm Excel cho phép bạn đếm các ô có chứa một kiểu dữ liệu nhất định hoặc dựa trên các điều kiện mà bạn chỉ định.

COUNT(value1, , …) – trả về số lượng các giá trị số (số và ngày) trong danh sách các đối số.

COUNTA(value1, , …) – trả về số ô có chứa dữ liệu trong danh sách các đối số. Nó đếm các ô có chứa bất kỳ thông tin, bao gồm các giá trị lỗi và các chuỗi văn bản rỗng (“”) được trả về bởi các công thức khác.

COUNTBLANK(range) – đếm số ô trống trong một phạm vi xác định. Các ô có chuỗi văn bản trống (“”) cũng được tính là các ô trống.

COUNTIF(range, criteria) – tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định.

COUNTIFS(criteria_range1, criteria1, …) – đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định.

Ảnh chụp màn hình sau minh họa cách hoạt động của các hàm đếm ô: Chú thích. Khi ngày tháng được lưu trữ như số trong Excel, chúng cũng được đếm bởi các chức năng thống kê của Excel. Ví dụ: công thức =COUNTIF(A2:A9, “>5”) đếm ngày trong ô A8 trong ảnh chụp màn hình ở trên vì nó được lưu trữ dưới dạng 42005 trong hệ thống Excel.

Hàm lookup trong Excel và các hàm liên quan

Các hàm dạng này của MS Excel có ích khi bạn cần tìm thông tin nhất định trong một bảng dựa trên một giá trị trong một cột hoặc trả về một tham chiếu đến một ô nhất định.

Hàm VLOOKUP

Hàm VLOOKUP tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác.

VLOOKUP (lookup_value, table_array, col_index_num, ) Lookup_value – giá trị cần tìm kiếm. Table_array – hai hoặc nhiều cột dữ liệu. Col_index_num – số cột để kéo dữ liệu. Range_lookup – xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua). Ví dụ: công thức =VLOOKUP(“apples”, A2:C10, 3) tìm kiếm “apples” trong ô A2 đến A10 và trả về giá trị phù hợp từ cột C:

Hàm INDEX

INDEX(array, row_num, ) – trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.

Đây là một công thức INDEX đơn giản: =INDEX(A1:C10, 3 ,4) hàm này tìm trong ô A1 đến C10 và trả về giá trị tại giao điểm của hàng thứ 3 và cột 4, đó là ô D3.

Hàm MATCH

MATCH(lookup_value, lookup_array, ) – tìm kiếm lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.

Sự kết hợp giữa các hàm MATCH và INDEX có thể được sử dụng như là một thay thế mạnh mẽ và linh hoạt hơn cho Vlookup của Excel.

Hàm INDIRECT

INDIRECT(ref_text, ) – trả về một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản.

Dưới đây là một ví dụ về công thức đơn giản nhất:

Hàm OFFSET

OFFSET(reference, rows, cols, , ) – trả về một tham chiếu đến một dãy ô được tính từ một ô bắt đầu hoặc một dãy các ô theo số hàng và cột được chỉ định.

Ví dụ, =OFFSET(A1, 1, 2) trả về giá trị trong ô C2 vì nó là 1 hàng xuống và 2 cột bên trái tính từ A1.

Hàm TRANSPOSE

TRANSPOSE(array) – biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng.

Hàm HYPERLINK

HYPERLINK(link_location, ) – Tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet.

Các hàm tài chính trong Excel

Microsoft Excel cung cấp một loạt các chức năng để đơn giản hóa công việc của các nhà quản lý kế toán, các nhà phân tích tài chính và các chuyên gia về ngân hàng. Dưới đây là một trong số những hàm chính có thể được sử dụng để tính lãi suất.

Hàm FV

FV(rate, nper ,pmt ,, ) – tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định.

Hàm ngày tháng trong Excel

Đối với những người sử dụng Excel thường xuyên, các hàm ngày tháng ngày càng trở nên quen thuộc hơn và được sử dụng trong rất nhiều trường hợp khác nhau.

Tạo lập ngày tháng

DATE – trả về một ngày được chỉ định dưới dạng số sê ri. DATEVALUE – chuyển đổi một chuỗi văn bản đại diện cho ngày để định dạng ngày.

Ngày và giờ hiện thời

TODAY – trả về ngày hiện tại. NOW – trả về ngày và thời gian hiện tại.

Trích ra ngày tháng và các thành phần ngày tháng

DAY – trả về ngày trong tháng. MONTH – trả về tháng của một ngày được chỉ định. YEAR – trả về năm của một ngày nhất định. EOMONTH – trả về ngày cuối cùng của tháng. WEEKDAY – trả về ngày trong tuần. WEEKNUM – trả về số tuần của một ngày.

Tính chênh lệch ngày

DATEDIF – trả về sự khác biệt giữa hai ngày. EDATE – trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu. YEARFRAC – trả về tỷ lệ của một khoảng thời gian trong một năm.

Tính các ngày làm việc

WORKDAY – Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó. WORKDAY.INTL – tính ngày tháng là số ngày trong tuần được chỉ định trước hoặc sau ngày bắt đầu, với thông số cuối tuần tùy chỉnh. NETWORKDAYS – trả về số ngày làm việc giữa hai ngày được chỉ định. NETWORKDAYS.INTL – trả về số ngày làm việc giữa hai ngày được chỉ định với ngày cuối tuần tùy chỉnh.

Các hàm thời gian trong Excel

Dưới đây là danh sách các hàm Excel chính dùng làm việc với thời gian.

TIME(hour, minute, second) – trả về thời gian dưới dạng số sê ri.

TIMEVALUE(time_text) – chuyển đổi một thời gian nhập dưới dạng một chuỗi văn bản thành một số thể hiện thời gian dưới dạng số sê ri.

NOW() – trả về số tương ứng với ngày và thời gian hiện tại dạng số sê ri.

HOUR(serial_number) – chuyển đổi một số thành một giờ dạng số sê ri.

MINUTE(serial_number) – chuyển đổi một số thành phút dưới dạng số sê ri.

SECOND(serial_number) – chuyển đổi một số thành giây dưới dạng số sê ri.

Đếm và tính tổng các ô theo màu sắc (chức năng do người dùng xác định)

Microsoft Excel không có bất kỳ chức năng tích hợp nào để đếm và tính tổng các ô theo màu sắc, vì vậy Học Excel Online đã tạo ra một số tùy chỉnh. Các chức năng này được viết bằng VBA, và bạn có thể thêm chúng vào các bảng tính trong môi trường lập trình của Excel – Visual Basic for Applications. Dưới đây là danh sách các hàm của Excel:

Get
Cell
Color(cell) – trả về mã màu của màu nền thuộc một ô xác định. Get
Cell
Font
Color(cell) – trả về mã màu của màu phông chữ thuộc một ô xác định. Count
Cells
By
Color(range, color code) – đếm các ô có màu nền được chỉ định. Count
Cells
By
Font
Color(range, color code) – đếm các ô có màu phông chữ được chỉ định. Sum
Cells
By
Color(range, color code) – tính tổng của các ô có màu nền nhất định. Sum
Cells
By
Font
Color(range, color code)) – trả về tổng của các ô với một màu chữ nhất định. Wbk
Count
Cells
By
Color(cell) – tính các ô có màu nền được chỉ định trong toàn bộ bảng tính. Wbk
Sum
Cells
By
Color(cell) – tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.

Xem thêm: Lý Lịch Tư Pháp Là Gì? Thủ Tục Làm Lý Lịch Tư Pháp Cần Giấy Tờ Gì Theo Qđ 2022?

Tất nhiên, Microsoft Excel có nhiều hàm hơn những hàm được liệt kê ở đây rất nhiều, tuy nhiên đây là những hàm vô cùng thiết yếu và phục vụ nhiều cho công việc của bạn.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học dưới đây: