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.
Bạn đang xem: Các hàm trong excel 2010 và ví dụ
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”.
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, 
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,
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, “,”, “;”)
– 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,
RIGHT(text,
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,
OR(logical1,
XOR(logical1, 
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,
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 SUMSUM(number1,
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,
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,
MAX(number1,
AVERAGE(number1,
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,
COUNTA(value1,
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,
Ả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,
Hàm INDEX
INDEX(array, row_num,
Đâ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,
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,
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,
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,
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 ,
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.
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:
Bài viết này mình muốn chia sẻ cho những bạn muốn học về excel từ căn bản. Công ty dịch vụ kế toán Đông Nam Á sẽ viết lại tất cả các hàm trong excel và ví dụ minh họa cho từng hàm trong phạm vi lấy bằng tin học B. Bài viết còn là một hướng dẫn để những bạn chưa biết gì về Excel có thể hiểu và thực hiện nó một cách tốt hơn. Cùng tham khảo tổng hợp các hàm trong excel từ cơ bản đến nâng cao nhé!
Cách dùng các hàm trong Excel cơ bản và ví dụ dùng để tính toán và đo đếm số
Mình đã tìm hiểu và tổng hợp tất cả các hàm trong excel để có thể trình bày lại sau đây những gì dễ hiểu và cần thiết nhất cho các bạn, đặc biệt là dân văn phòng. Vậy sau đây mình xin đi vào nội dung chính các hàm trong excel và ví dụ luôn nhé!
a/ Các hàm excel cơ bản và ví dụ thông dụng trong kế toán
Hàm sumHàm sum (hàm tính tổng) là một hàm mà bất kì ai cũng sẽ biết khi mới nhập môn excel. Đây là hàm tính tổng các số lại với nhau. Hàm có cú pháp là “=sum(giá trị 1, giá trị 2,…)”. Khi bắt đầu 1 hàm hay 1 phép tính bất kì nào, bạn phải để dấu bằng (=) phía trước thì phép tính.
Ví dụ như muốn tính tổng từ A1 tới B5 ta làm như sau: =sum(A1:B5) là ra kết quả.
Bạn cũng có thể làm từng số kiểu như vầy: =sum (A1,A2,A3,A4,A5,B2,B3) cũng ra kết quả tương tự.
Những hàm cơ bản kiểu giống hàm sum này thì bạn có thể áp dụng cả 2 cách trên. Nhưng để thuận tiện trong việc tính toán, cải thiện tốc độ nên nhiều người chọn làm cách 1. Vậy mình sẽ giới thiệu cách 1 cho bạn!
Hàm CountHàm Count là hàm đếm kí tự trong một đống những ô trống và ô chứa chữ số. Nếu bạn muốn biết trong vùng nào đó có bao nhiêu chữ số thì thay vì đếm từng ô bạn có thể dùng hàm này để tính rất nhanh. Hãy nhớ rằng hàm này là hàm đếm và đếm gì thì hãy dùng hàm này nhé!
Cú pháp hàm như sau: = Count(vùng giá trị cần đếm). Ví dụ mình muốn đếm từ vùng A1 đến B6 có bao nhiêu ô có chứa chữ số thì dùng công thức =Count(A1:B6).
Kết quả như trong hình. Bạn thấy đó, dù trong vùng giá trị có kí tự “a” nhưng vẫn không được tính. Vì hàm Count chỉ tính giá trị chữ số mà thôi.
Hàm CountaHàm Counta cũng là hàm đếm nhưng hàm này đếm cả số lẫn chữ la tinh. Cấu trúc nó cũng như hàm Count thôi nhé!
Hàm Countblank
Hàm Countblank cũng là hàm đếm nhưng hàm này đếm ô trống. Nó chỉ đếm mỗi ô trống thôi. Cấu trúc nó cũng như hàm Count.
Hàm Average là hàm tính trung bình các số. Đây là hàm này được dùng rất nhiều trong việc tính trung bình điểm số học sinh trong các trường học. Cú pháp của hàm này là =Avarage(giá trị 1, giá trị 2,….).
Ví dụ nếu bạn muốn tính trung bình cho một học sinh với số điểm các môn thì từ B2 tới E2. Thì bạn làm như sau: =Average(B2:E2).
Bạn cần lưu ý vấn đề sau đây trong tính hàm này. Trong trường hợp yêu cầu là Toán hệ số 2 thì bạn phải làm cú pháp khác cụ thể như thế này: =Avegare(B2:E2,B2). Lúc này mới tính Toán là hệ số 2 vì như vậy hàm sẽ nhận thêm 1 giá trị nữa ở ô B2 nhé. Nếu hệ số 3 thì bạn lại thêm một giá trị B2 nữa nhé!
Hàm MinHàm Min cũng là một trong các hàm cơ bản trong Excel vì hàm này rất đơn giản. Nhiệm vụ của nó là lấy giá trị nhỏ nhất trong vùng giá trị mà bạn chọn. Nếu có yêu cầu tìm giá trị nhỏ nhất, chắc chắn hàm này chính là sự lựa chọn tốt nhất cho bạn.
Cú pháp của hàm là: =Min(vùng giá trị). Ví dụ nếu như bạn muốn tìm giá trị nhỏ nhất từ vùng B1 đến B7 thì cú pháp sẽ là =Min(B2:B7).
Bạn có thể thấy trong các giá trị trong vùng giá trị trên, kết quả sẽ cho ra con số nhỏ nhất.
Hàm MaxHàm Max là hàm đi đôi với hàm Min. Ngược lại với hàm Min, hàm Max có nhiệm vụ tìm giá trị lớn nhất trong vùng dữ liệu. Nếu có yêu cầu tìm giá trị lớn nhất trong vùng dữ liệu thì bạn hãy dùng tới hàm này nhé.
Cú pháp của hàm là: =Max(vùng giá trị). Ví dụ như nếu bạn muốn tìm giá trị lớn nhất từ vùng B2 đến B7 thì cú pháp sẽ là =Max(B2:B7).
Kết quả cho ra giá trị lớn nhất trong vùng giá trị như trong hình trên.
Lưu ý rằng cả 2 hàm Min và Max chỉ nhận diện giá trị là các con số thôi nhé. Sẽ không thể tìm giá trị lớn nhất hay nhỏ nhất nếu là nó chữ La tinh được nhé bạn.
5 hàm trên là 5 hàm tính toán dính đến những con số. Các hàm này rất cơ bản và bạn hãy nắm rõ nó để sắp tới chúng ta đi sâu vào những hàm nâng cao của các hàm đó nhé.
Hàm SubtotalHàm Subtotal là hàm tổng quát của các hàm trên. Nhưng có một vấn đề là phải chọn số cấu trúc nên ít khi được nhiều người dạy. Nhưng hàm Subtotal này lại được ứng dụng nhiều trong văn phòng vì tính tiện lợi của nó. Trong kế toán, hàm này dùng để tính tổng phát sinh trong kì hay tính tổng tiền tồn cuối ngày.
Trước tiên là cấu trúc của hàm: =Subtotal(số cấu trúc tính toán, vùng tính toán).
Với số cấu trúc tính toán, bạn có thể tham khảo bảng sau để rõ về nó:
Số cấu trúc tính toán | Hàm tính toán thực sự | |
| Tính cả các giá trị gồm cả giá trị ẩn | Tính cả các giá trị nhưng không tính giá trị ẩn | |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Việc dùng hàm Subtotal có thể dùng cả hàm Sum lẫn hàm Count, Count
A, Average,… tức là nó bao gồm nhiều hàm chứ không như hàm Sum chỉ dùng để tính tổng. Bảng trên đã cho bạn thấy rõ sự khác nhau của 2 hàm này.
Để hiểu rõ hàm hơn, các bạn có thể xem ví dụ sau đây là rõ. Ví dụ về mèo gà heo chó và thức ăn của nó, bạn có thể tính thành tiền bằng công thức sau: =SUBTOTAL(9,C2:C8). Trong đó 9 là hàm Sum như trong bảng trên nhé! Mình dùng số 9 này để tính tổng. Và nếu bạn cần đếm thì có thể dùng số 2 hoặc 3 tùy thích nhé!
b/ Tổng hợp các hàm excel nâng cao và ví dụ
Hàm SumifHàm Sumif là hàm tính tổng nhưng là tính tổng có điều kiện kèm theo. Nếu có yêu cầu tính tổng mà đi kèm với một điều kiện nào đó. Nhưng chỉ có một điều kiện thôi thì bạn sử dụng hàm sumif này. Đây là một trong các hàm excel nâng cao được dùng nhiều nhất
Cú pháp của hàm này như sau: =Sumif(phạm vi điều kiện, điều kiện, vùng tính tổng). Trong trường hợp phạm vi điều kiện và vùng tính tổng trùng nhau thì cú pháp nó sẽ đơn giản hơn là =Sumif(phạm vi điều kiện, điều kiện).
Mình sẽ lấy ví dụ đầu tiên đó là khi phạm vi điều kiện và vùng tính tổng trùng nhau. Nếu bạn có một vùng điều kiện để tính từ B2 đến B7 và điều kiện tính của bạn là các giá trị đó phải >20. Thì cú pháp của hàm này sẽ là =Sumif(B2:B7,”>20”). Và kết quả như hình dưới.
Còn nếu vùng điều kiện của bạn không trùng với vùng tính thì mình làm như sau. Ví dụ bạn có một hóa đơn tiền thức ăn cho chó mèo gà vịt và bạn muốn tính hóa đơn thức ăn của chó thì bạn sẽ làm thế này. Phạm vi điều kiện chính là vùng chó mèo gà vịt từ cột A2 đến A7, điều kiện mình muốn tính đó là chỉ tính thức ăn của chó thôi và vùng tính tổng là lượng tiền mà các động vật đã ăn từ cột B2 đến B7. Vậy cú pháp sẽ là =Sumif(A2:A7,”chó”,B2:B7).
Một lưu ý quan trọng là phần điều kiện bạn phải luôn để nó trong ngoặc kép (“ điều kiện“) nhé! Nếu không Excel sẽ báo lỗi và bạn không thể nào thực hiện được phép tính đó.
Hàm SumifsHàm Sumifs cũng gần giống như hàm Sumif là một hàm tính tổng có điều kiện. Nhưng Sumifs là làm tính tổng nhiều điều kiện, khác với sumif chỉ tính tổng một điều kiện mà thôi. Đây cũng là một trong các hàm nâng cao trong excel được dùng khá nhiều.
Cú pháp của hàm này như sau: =Sumifs(vùng cần tính tổng, vùng điều kiện 1, điều kiện 1, vùng điều kiện 2, điều kiện 2, vùng điều kiện 3, điều kiện 3….). Vì đây là hàm tính tổng nhiều điều kiện nên bạn có thể thêm vùng điều kiện và điều kiện vào hàm để phù hợp với yêu cầu nhé!
Sau đây mình sẽ lấy ví dụ về 2 điều kiện thôi. Vẫn lấy ví dụ về thức ăn cho chó mèo lợn gà những có thêm cột số lượng. Nếu yêu cầu đề là tính thành tiền của thức ăn chó với số lượng là >3 thì bài này có 2 điều kiện là phải là “chó” và số lượng phải “>3”. Cú pháp sẽ là như sau: =SUMIFS(C2:C8,A2:A8,”chó”,B2:B8,”>3″).
Như các bạn thấy kết quả sẽ ra như trong hình trên.
Hàm SumproductHàm Sumproduct là hàm đơn giản. Nó chỉ là hàm tính tổng các sản phẩm của bạn mà thôi. Cú pháp của hàm là =Sumproduct(Vùng muốn nhân và tính tổng, vùng đối số muốn nhân và tính tổng 1, vùng đối số muốn nhân và tính tổng 2,….). Để hiểu hàm này hơn mình nghĩ nên lấy ví dụ để nói cho bạn mới rõ được.
Như trong hình cho ra kết quả là 1020. Có kết quả này là vì hàm này tính bằng cách lấy từ số ở cột số lượng x cột đơn giá đối ứng rồi cộng lại. Tức là 3×15 + 4×30 + 6×50 + 6×25 + 5×45 + 2×70 + 2×20 =1020. Và hàm này tính tổng theo cách đó. Nó được dùng nhiều trong kế toán hơn là trong học tập bạn nhé!
Hàm CountifHàm Countif cũng giống như hàm Sumif và hàm Count là vẫn là đếm giá trị nhưng đếm này có điều kiện (1 điều kiện). Ở phần Sumif và Sumifs mình đã chia sẻ rất kỹ rồi nên sang phần này và phần hàm Countifs tiếp theo mình sẽ nói sơ qua thôi vì nó tương tự nhau bạn nhé!
Cú pháp hàm Countif như sau: =Countif(vùng đếm, điều kiện). Ví dụ như để đếm từ ô C2 đến ô C8 với điều kiện là >30 thì ta có cú pháp =COUNTIF(C2:C8,”>30″).
Hàm Countifs là hàm đếm với nhiều điều kiện. Cũng giống như sumifs, hàm này cũng có thể đếm với nhiều điều kiện khác nhau. Cú pháp của hàm là =Countifs(Vùng cần đếm 1, điều kiện 1, vùng cần đếm 2, điều kiện 2,…).
Ví dụ nếu mình muốn đếm cột thành tiền có điều kiện là >25 và điều kiện kèm là cột số lượng đó phải >3 thì mới đếm thì ta có cú pháp như sau: =COUNTIFS(C2:C8,”>25″,B2:B8,”>3″).
Một lưu ý mà bạn cần quan tâm đó là số ô ở ô mà bạn muốn đếm (ở ví dụ này là ô Thành Tiền) phải trùng với số ô ở điều kiện kèm theo ( mà ở đây là ô Số lượng), tức là trùng 7 ô. Là các điều kiện không nhất thiết phải trùng kế bên nhau như mình đã cho ví dụ nha.
Cấu trúc các hàm trong Excel (2010) về ngày tháng năm giờ phút giây
Cấu trúc các hàm trong Excel dạng này khá đơn giản nhưng lại dễ sai. Trước khi làm phần này các bạn cần chú ý những điều sau:
Nếu điền ngày tháng, các bạn hãy dùng dấu “/” hoặc dấu “-“ để phân biệt ngày tháng năm.Nếu điền thời gian thì bạn hãy sử dụng dấu “:”.Để chỉnh sửa các dấu này hoặc bạn muốn chỉnh lại theo ý mình thì mình sẽ hướng dẫn bạn luôn và chính bạn tự thống nhất kí hiệu cho mình để tránh bị sai về vấn đề đồng nhất trong quá trình làm Excel nhé!Vào Start chọn Control Panel. Chổ “View by” chọn lại “Large icon” (Nếu bạn đã chọn rồi thì thôi không cần làm nhé). Sau đó bạn vào phần “Region and Language”.
Ở phần Short day, Long day, short time và long time bạn có thể chỉnh lại theo ý mình. Riêng mình thì mình thích chọn kiểu dd/mm/yyyy hoặc chọn mặc định như trong hình cũng được. Vì như vậy sẽ thuận tay hơn.
Sau khi chỉnh xong ta bước vào phần chính là các hàm và ví dụ nhé!
Hàm Year, Month và Day
Ba hàm này đều có chung một cú pháp là =Year(ngày tháng năm), =Month(ngày tháng năm) hay =Day(ngày tháng năm). Những hàm nay cơ bản là dùng để tách lấy ngày, tháng hoặc năm ra từ trong một chuỗi ngày tháng năm mà thôi.
Thực đơn giản phải không nào!
Hàm Date
Hàm Date là hàm mà giúp bạn có thể thêm số ngày, tháng hoặc là năm. Theo mình thấy hàm này cũng ít dùng nhưng nếu dùng thì nó hay dùng trong các nghiệp vụ phát sinh gia hạn ngày nọ trong báo cáo. Cơ bản tác dụng của nó là thêm ngày tháng năm thôi. Cú pháp của hàm là =date(năm, tháng,ngày). Xem ví dụ minh họa cho dể hiểu nhé!
Bạn xem kết quả ở hình trên nhé!
Hàm Days360
Hàm Days360 là hàm lấy số ngày giữa 2 mốc thời gian với nhau. Cấu trúc của hàm cũng đơn giản =Days360(mốc thời gian 1, mốc thời gian 2).
Và kết quả như hình trên. Nếu không tin các bạn có thể tự nhẩm xem đúng hay không nhé!
Hàm Hour, Minute và Second
Để có kết quả trả về là giờ, phút hoặc giây, bạn có thể dùng hàm Hour, Minute hoặc Second. Cấu trúc 3 hàm này tương tự như nhau là =Hour(thời gian), Minute(thời gian) và Second(thời gian).
1 ví dụ minh họa là bạn sẽ thấy nó dễ dàng như thế nào nhé. Mình lấy 1 mốc thời gian là 5:15:59 tức là 5 giờ 15 phút 59 giây thì dùng 3 hàm trên có thể tách ra kết quả như sau.
Hàm Time
Hàm Time nó tựa tựa như hàm Date vậy. Dùng để thêm giờ phút giây cho mốc thời gian ban đầu. Cú pháp của nó là =Time(giờ,phút,giây).
Ví dụ như vẫn mốc thời gian là 5 giờ 15 phút 59 giây ta muốn thêm vào đó 2 giờ 30 phút và 21 giây thì dùng cú pháp sau: =TIME(HOUR(B2)+2,MINUTE(B2)+30,SECOND(B2)+21).
Và kết quả sẽ là như trong hình. Lưu ý rằng nếu đủ 60s thì sẽ tính 1p vào số phút và tương tự nhé.
Tổng hợp tất cả các hàm trong Excel mang tính logic và ví dụ
Phần tổng hợp tất cả các hàm trong excel mà mang tính logic này không có phần nâng cao. Nhưng đây là những hàm mà bạn dùng rất nhiều trong khi làm Excel nên các bạn hãy chú ý nhé.
Hàm If
Nếu mình có 1 con số là 70 là số kí của một người cao 1m7 thì nếu ai 70 kí hoặc lớn hơn thì là người mập, còn thấp hơn 70 kí thì là người ốm thì mình sẽ dùng cú pháp sau để tính: =IF(A1>=70,”Mập”,”Ốm”).
Các bạn thấy đó, nếu mình để 65 thì theo cú pháp này kết quả sẽ đưa ra là “Ốm” vì nó nhỏ hơn 70 kí.
Hàm If lồng nhau
Hàm IF tuy được dùng nhiều nhưng ít khi nó đi một mình như ví dụ trên cả mà thường là sẽ những chuỗi các hàm IF lồng nhau tạo nên một sự logic nhất định.
Thường thì các bạn sẽ gặp các bài toán kiểu như cho điểm TB học kì của các học sinh và bạn hãy phân loại nếu >=9 thì là loại Giỏi, nếu >=7.5 thì là loại Khá còn lại là loại Yếu chẳng hạn. Gặp trường hợp này bạn phải làm như sau: =IF(B2>=9,”Giỏi”,IF(B2>=7.5,”Khá”,”TB”)).
Với bài trên mình đã dùng 2 hàm IF lồng nhau để tìm ra kết quả. Các bạn có thể áp dụng nhiều hàm IF lồng nhau nữa để phù hợp với bài toán nhé!
Hàm Or
Hàm Or là hàm trả về kết quả đúng hoặc sai (TRUE hoặc FALSE). Cú pháp của hàm là =OR(điều kiện 1, điều kiện 2). Một lưu ý khi dùng hàm này là nếu cả 2 điều kiện này có một cái đúng thì OR vẫn trả về kết quả đúng (TRUE). Chỉ khi nào cả 2 điều kiện đều sai mới trả về kết quả FALSE thôi nhé!
Cũng lấy ví dụ về điểm trung bình lúc nãy như dùng làm OR để lấy giá trị. Ta sẽ cho điều kiện TB của học sinh A >10 và TB của học sinh B 10,B3Hàm Value
Như đã nói tác dụng của hàm là chuyển chữ thành số nên cú pháp nó cũng đơn giản. Cú pháp là =Value(kí tự). Như thế là chuyển chữ thành số rồi nhé! Bây giờ quay lại ví dụ về hàm lồng nhau Index và Match thôi nào.
Ví dụCác bạn xem qua hình nhé!
Bây giờ chúng ta thấy ta có cột mã hàng từ A2 đến A5. Bảng màu đỏ là bảng tham chiếu mã hàng với đơn hàng. Các số 0,1,2 là những số thứ 3 ở cột mã hàng màu cam. Và bây giờ chúng ta phải lấy đơn giá từ bảng màu đỏ tương ứng với bảng màu cam.
Với hàm Index(bảng tham chiếu, dòng, cột) thì bảng tham chiếu ta lấy ô màu nâu. Dòng và cột thì trích từ bảng màu cam đưa xuống bằng cú pháp Match.
Sử dụng các cú phápĐể trích dẫn dòng ta phải dùng hàm Match như sau: =MATCH(LEFT(A2,2),$C$9:$C$11,0).
Để trích dẫn cột ta phải dùng hàm Match như sau: = MATCH(VALUE(MID(A2,3,1)),$D$8:$F$8,0). Như các bạn thấy, mình phải dùng hàm Value để chuyển kí tự thứ 3 trong bảng màu cam mà cụ thể là ô A2 từ kí tự “1” sang số “1”.
Xem thêm: Ong gia may man va co y ta tre full? ? thần long ở rể
Sau khi đã biết cách lấy dòng và cột ta được cú pháp lồng nhau của 2 hàm: =INDEX($D$9:$F$11,MATCH(LEFT(A2,2),$C$9:$C$11,0),MATCH(VALUE(MID(A2,3,1)),$D$8:$F$8,0)).
Và kết quả ra 40. Bạn nhìn vào cũng thấy đúng phải không nè!
Trên đây là tổng hợp tất cả các hàm trong Excel và ví dụ cụ thể mà có thể giúp cho những bạn chưa biết về Excel hay những bạn học kế toán, làm kế toán tham khảo. Còn nhiều hàm nữa và nếu rảnh mình sẽ tiếp tục tổng hợp các hàm trong excel để các bạn tham khảo nhé! Hãy bookmark bài viết để tiện theo dõi bạn nhé!