Bài viết này rất có thể xem là bài viết tổng hòa hợp nhiều tác dụ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ê những hàm vào excel, các văn bản, phép toán, thống kê, xúc tích và ngắn gọn và các tính năng khác cần thiết nhất cùng với các ví dụ về công thức và link tới những tài nguyên có liên quan.
Bạn đang xem: Hướng dẫn sử dụng các hàm trong excel 2010
Excel hỗ trợ một con số lớn các công dụng để phân tích, truy thuế kiểm toán và thống kê giám sát 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 những những chức năng này được áp dụng bởi nhiều người tiêu dùng Excel từng ngày và cũng có một số tín đồ chỉ áp dụng những phép toán này vào trường hợp nuốm thể.
Trong gợi ý này, chúng ta sẽ không tò mò từng công dụng trong Microsoft Excel nhưng mà chỉ chuyển ra cho mình cái chú ý tổng quan liêu về những tính năng hữu ích trong Excel nhất là đưa ra tầm nhìn tổng quan lại về biện pháp dùng hàm vào excel được thực hiện nhiều nhất.
Hầu hết các công dụng được liệt kê dưới đó là các hàm bảng tính Excel được thực hiện như một phần của công thức trong một ô. Những mục cuối cùng bao hàm các công dụ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 để không ngừng mở rộng những điều cơ phiên bản của Excel với công dụng tuỳ chỉnh. Lựa chọn 1 danh mục nhằm vào list các công dụng với các mô tả ngắn và link đến các ví dụ sử dụng.
Xem nhanh
Chức năng văn bản trong Excel (các tác dụng chuỗi)Chức năng TEXTHàm SUBSTITUTEHàm VALUECác công thức toán học trong ExcelTìm tổng của những ôCác hàm những thống kê trong ExcelHàm lookup trong Excel và các hàm liên quanCác hàm tài chính trong Excel
Chức năng văn bản trong Excel (các công dụng chuỗi)
Có khôn cùng 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 dạng 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 để biến hóa một số hoặc một ngày thành một chuỗi soát sổ ở format được chỉ định, trong đó:
value là 1 trong 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 dưới đây thể hiện hoạt động vui chơi của chức năng Excel TEXT:
=TEXT(A1,”€#,##0.00″) – chuyển một trong những trong A1 thành một chuỗi cam kết tự chi phí tệ như “€3.00”.
Ví dụ cho cách làm TEXT:Hàm TEXT sử dụng để biến hóa một ngày sang format văn bạn dạng Công TEXT dùng làm chuyển một vài thành dạng văn bản
Hàm CONCATENATE
CONCATENATE(text1,
Hàm TRIM
TRIM(text) được sử dụng để thải trừ hàng đầu, vết cách cũng giống như các không gian dư thừa giữa những từ. Trường thích hợp văn bản hoặc một chuỗi văn phiên bản hay tham chiếu cho ô gồm chứa văn bạn dạng mà bạn có nhu cầu loại vứt 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 sơ hoặc tham chiếu cho ô mà bạn có nhu cầu thay thế một số trong những ký tự duy nhất định. Old_text – các ký tự bạn muốn thay thế. New_text – các ký tự mà chúng ta mới bạn muốn dùng để cố kỉnh chỗ văn bản cũ. Nth_appearance – một tham số tùy chọn mang đến old_text mà bạn muốn thay thế bởi new_text. Sau đó, từng lần xuất hiện của văn bạn dạng cũ sẽ được sửa chữa bằng văn bản mới. Ví dụ: bí quyết SUBSTITUTE sau sửa chữa thay thế tất cả những dấu phẩy trong ô A1 bằng dấu chấm phẩy:
=SUBSTITUTE(A2, “,”, “;”)
Công thức SUBSTITUTE:– đào thải ngắt loại trong một ô – đổi khác chuỗi văn bạn dạng với lốt phân cách cấu hình thiết lập thành ngày
Hàm VALUE
VALUE(text) – thay đổ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 biến đổi các quý hiếm đị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 những công thức Excel khác với tính toán.
Ví dụ đến hàm VALUE:Chuyển đổi chữ số format văn bản thành số Hàm VALUE sử dụng để biến đổi văn bạn dạng thành ngày Hàm EXACT EXACT(text1, text2) đối chiếu hai chuỗi văn phiên bản và trả về giá trị TRUE trường hợp cả hai giá trị mọi giống nhau, với trả về FALSE nếu không giống nhau.
Ví dụ: nếu như A2 là “apples” cùng B2 là “Apples”, bí quyết =EXACT(A2, B2) đã trả về FALSE vày chúng không khớp chính xác.
Hàm EXACT hi hữu khi được thực hiện riêng, nhưng mà lại hữu dụng khi kết hợp với những hàm khác để làm cho tác vụ phức tạp hơn, ví như kết hợp với Vlookup dùng thao tác với văn bạn dạng trong Excel.
Các hàm thực hiện để biến đổi văn phiên bản (UPPER, LOWER, PROPER)
Microsoft Excel hỗ trợ 3 hàm văn bản để đổi khác giữa UPPER, LOWER, PROPER.
UPPER(text) – biến đổi tất cả những ký tự trong một chuỗi cam kết tự được hướng dẫn và chỉ định sang chữ hoa.
LOWER(text) – chuyển đổi tất cả những chữ in hoa vào một chuỗi văn bản thành chữ thường.
PROPER(text) – viết hoa chữ cái trước tiên của từng từ và thay đổi tất cả những chữ chiếc khác sang chữ thường.
Trong cả tía 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 cho một ô tất cả chứa văn phiên bản hoặc một cách làm trả về văn bản.
Trích xuất các ký từ văn phiên bản (LEFT, RIGHT, MID)
Nếu bạn cần một phương pháp để trả về một vài ký tự độc nhất vô nhị định xuất phát từ một chuỗi văn bản, sử dụng một trong những hàm Excel sau.
LEFT(text,
RIGHT(text,
MID(text, start_num, num_chars) – trả về một số trong những ký tự độc nhất định xuất phát từ một chuỗi văn bản, bắt đầu ở ngẫu nhiên vị trí nào bạn chỉ định.
Trong những hàm này, bạn cung cấp các đối số sau:
– Text – một chuỗi văn bạn dạng hoặc một tham chiếu đến một ô chứa những ký trường đoản cú mà bạn muốn trích xuất. – Start_num – chỉ ra nơi ban đầu (tức là địa điểm của ký tự đầu tiên bạn muốn trích xuất). – Num_chars – số ký kết tự bạn có nhu cầu trích xuất.
Các hàm súc tích trong Excel
Microsoft Excel cung cấp một số không nhiều các tác dụng logic đánh giá một điều kiện xác định và trả về quý giá tương ứng.
AND, OR, XOR tác dụng AND(logical1,
OR(logical1,
XOR(logical1,
Hàm NOT
NOT(logical) – hòn đảo ngược quý hiếm đối số của nó, nghĩa là nếu review lôgic thành FALSE, hàm NOT sẽ trả về TRUE cùng ngược lại.
Ví dụ, cả nhì công thức tiếp sau đây sẽ trả về FALSE:
=NOT(TRUE)
=NOT(2*2=4)
Hàm IF
Hàm IF vào Excel đôi khi được gọi là “hàm điều kiện” vì nó trả về một quý hiếm dựa trên đk mà các bạn chỉ định. Cú pháp của hàm IF như sau:
IF(logical_test,
Ví dụ, cách làm = IF =IF(A1””, “good”, “bad”) trả về “good” trường hợp có ngẫu nhiên giá trị làm sao trong ô A1, “bad” trường hợp không.
Và đấy là một lấy ví dụ như về phương pháp hàm IF phối hợp thể hiện tại điểm thi trong ô A2:
=IF(A2>80, “Brilliant”, IF(A2>50, “Good”, IF(A2>30, “Fair”, “Poor”)))
Hàm IFERROR với IFNA
Cả nhì hàm những được sử dụng để bình chọn lỗi vào một công thức tính tuyệt nhất định, cùng nếu có lỗi xảy ra, những hàm MS Excel trả lại một cực hiếm được chỉ định núm thế.
IFERROR(value, value_if_error) – chất vấn lỗi của cách làm hoặc biểu thức đánh giá. Nếu có lỗi, bí quyết trả về cực hiếm được cung cấp trong đối số value_if_error, trường hợp không, tác dụng 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à gần như lỗi khác. Nó bao gồm sẵn trong Excel 2007 và phần nhiều phiên phiên bản cao hơn.
IFNA(value, value_if_na) – được trình làng trong Excel 2013, nó vận động tương từ như IFERROR, nhưng chỉ xử trí lỗi #N/A.
Các ví dụ sau minh họa cách làm IFERROR dễ dàng và đơn giản nhất:
Các phương pháp toán học tập trong Excel
Excel tất cả vô vàn công dụng cơ phiên bản và tiên tiến và phát triển để thực hiện các phép toán, giám sát hàm mũ, các đối số, nguyên tố và các dạng tương tự. Chúng ta hẳn sẽ mất cho tới vài trang chỉ nhằm xuất bạn dạng danh sách những chức năng. Vị vậy, họ hãy thảo luận về một vài công dụng toán học cơ bạn dạng có thể hữu dụng cho việc giải quyết các quá trình 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 những ô trong một phạm vi xác định theo dưới đây.
Hàm SUMSUM(number1,
Ví dụ, cách làm toán học dễ dàng và đơn giản =SUM(A1:A3, 1) cộng những giá trị vào ô A1, A2 với A3, và thêm một vào kết quả. Những hàm SUMIF cùng SUMIFS (tổng điều kiện) Cả hai hàm này dùng làm cộng các ô vào 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á duy nhất tiêu chuẩn, trong những lúc SUMIFS, được reviews trong Excel 2007, có thể chấp nhận được sử dụng những tiêu chí. Hãy để ý rằng sản phẩm công nghệ tự của những đối số là khác nhau trong mỗi chức năng:
SUMIF(range, criteria,
SUMPRODUCT(array1,array2, …) là 1 trong những trong số ít các tính năng của Microsoft Excel dùng làm xử lý mảng. Nó kết phù hợp với các yếu tố mảng được cung ứng và trả về tổng sản phẩm.
Bản chất của hàm SUMPRODUCT có thể khó thâu tóm vì vậy các chúng ta có thể theo dõi các ví dụ rõ ràng của hàm này trong nội dung bài viết dành riêng mang lại hàm SUMPRODUCT để sở hữu được đọc biết đầy đủ.
Tạo những số bất chợt (RAND cùng RANDBETWEEN) Microsoft Excel cung ứng 2 tính năng để tạo nên các số ngẫu nhiên. Cả hai phần đông là những hàm bất ổn định, tức là một số bắt đầu được trả về mỗi lúc bảng tính tiến hành tính toán.
RAND() – trả về một số thực (số thập phân) bỗng dưng giữa 0 cùng 1.
RANDBETWEEN(bottom, top) – trả về một số trong những nguyên tự dưng giữa số dưới cùng và số trên cùng mà chúng ta chỉ định.
Chức năng làm tròn
Có một số công dụng để có tác dụng tròn số trong Excel, và chỉ dẫn làm tròn của Excel của chúng tôi đã góp bạn phân tích và lý giải cách áp dụng những tính năng đó dựa trên tiêu chí của bạn. Vui mắt nhấp vào tên của công dụng để mày mò cú pháp và ví dụ về sử dụng.
ROUND – làm cho tròn số mang đến số chữ số được chỉ định.
ROUNDUP – có tác dụng tròn lên, mang lại số ký kết tự được chỉ định.
ROUNDDOWN – làm tròn xuống, cho số cam kết tự được chỉ định.
MROUND – làm cho tròn số cho một bội số được chỉ định.
FLOOR – có tác dụng tròn số xuống, mang đến bội số được chỉ định.
CEILING – tròn số lên, mang đến bội số được chỉ định.
INT – làm tròn số xuống số nguyên sát nhất.
TRUNC – cắt bớt số số cho số nguyên gần nhất.
EVEN – làm tròn số mang lại số nguyên chẵn gần nhất.
ODD – làm cho tròn số lên tới mức số nguyên lẻ sát nhất.
Trả về số dư sau thời điểm chia (hàm MOD)
MOD(number, divisor) trả về số sư sau thời điểm chia.
Hàm này thực sự khôn cùng hữu ích trong nhiều trường phù hợp khác nhau, ví dụ như:
Tính tổng những giá trị trong những hàng, hoặc một hàng được chỉ định thay đổi màu sắc của sản phẩm trong Excel
Các hàm thống kê trong Excel
Trong số các hàm thống kê của Excel, có một trong những hàm rất có thể áp dụng để thực hiện cho những công việc mang tính bài bản cao.
Tìm những giá trị bự nhất, bé dại nhất với trung bình
MIN(number1,
MAX(number1,
AVERAGE(number1,
SMALL(array, k) – trả về giá trị nhỏ nhất lắp thêm k vào mảng.
LARGE (mảng, k) – trả về giá bán trị lớn số 1 thứ k vào mảng.
Ảnh chụp screen sau minh họa các hàm thống kê lại cơ bản.
Đếm ô
Dưới đấy là danh sách những hàm Excel cho phép bạn đếm những ô tất cả chứa một kiểu tài liệu nhất định hoặc dựa trên các điều khiếu nại mà chúng ta chỉ định.
COUNT(value1,
COUNTA(value1,
COUNTBLANK(range) – đếm số ô trống vào một phạm vi xác định. Những ô tất cả chuỗi văn phiên bản trống (“”) cũng rất được tính là các ô trống.
COUNTIF(range, criteria) – tính số ô vào 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 buổi giao lưu của các hàm đếm ô: Chú thích. Lúc ngày tháng được lưu trữ như số trong Excel, chúng cũng rất được đếm bởi các công dụng thống kê của Excel. Ví dụ: phương pháp =COUNTIF(A2:A9, “>5”) đếm ngày trong ô A8 trong hình ảnh chụp màn hình ở trên vày nó được lưu trữ dưới dạng 42005 trong hệ thống Excel.
Hàm lookup vào Excel và các hàm liên quan
Các hàm dạng này của MS Excel hữu dụng khi bạn phải tìm tin tức nhất định vào một bảng dựa trên một quý giá trong một cột hoặc trả về một tham chiếu mang lại một ô tốt nhất định.
Hàm VLOOKUP
Hàm VLOOKUP kiếm tìm một quý giá được chỉ định và hướng dẫn trong cột thứ nhất và kéo dữ liệu tương xứng từ và một hàng vào 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 trong công thức INDEX 1-1 giản: =INDEX(A1:C10, 3 ,4) hàm này search trong ô A1 mang lại C10 cùng trả về giá trị tại giao điểm của hàng đồ vật 3 cùng cột 4, chính là ô D3.
Hàm MATCH
MATCH(lookup_value, lookup_array,
Sự phối hợp giữa những hàm MATCH với INDEX có thể được áp dụng như là 1 thay thế mạnh bạo và linh hoạt hơn mang đến Vlookup của Excel.
Hàm INDIRECT
INDIRECT(ref_text,
Dưới đấy là một ví dụ về công thức dễ dàng và đơ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ày nó là một trong hàng xuống cùng 2 cột phía bên trái tính từ bỏ A1.
Hàm TRANSPOSE
TRANSPOSE(array) – thay đổi một khoảng nằm ngang của những ô thành một dải đứng với ngược lại, tức là chuyển hàng thành các cột cùng 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 tính năng để đơn giản dễ dàng hóa các bước của các nhà cai quản kế toán, các nhà so với tài thiết yếu và các chuyên viên 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 hay xuyên, những hàm ngày tháng ngày càng trở nên rất gần gũi hơn cùng được thực hiện trong rất nhiều trường phù hợp khác nhau.
Tạo lập ngày tháng
DATE – trả về một ngày được hướng dẫn và chỉ định dưới dạng số sê ri. DATEVALUE – thay đổi một chuỗi văn bạn dạng đại diện cho 1 ngày để format ngày.
Ngày với giờ hiện tại thời
TODAY – trả về ngày hiện tại 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 vào 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 tuyệt nhất định. EOMONTH – trả về ngày cuối cùng của tháng. WEEKDAY – trả về ngày vào 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ự biệt lập giữa nhì ngày. EDATE – trả về một ngày nằm trong thời điểm tháng đã định trước, rất 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 hạn trong một năm.
Tính các ngày có tác dụng việc
WORKDAY – Trả về một trong những tuần tự diễn tả số ngày làm việc, rất có thể là trước giỏi sau ngày bước đầu làm câu hỏi và trừ đi hầu như ngày vào cuối tuần và ngày nghỉ (nếu có) vào khoảng thời gian đó. WORKDAY.INTL – tính tháng ngày là số ngày vào tuần được hướng dẫn và chỉ định trước hoặc sau ngày bắt đầu, cùng với thông số vào buổi tối cuối tuần tùy chỉnh. NETWORKDAYS – trả về số ngày làm việc giữa nhì ngày được chỉ định. NETWORKDAYS.INTL – trả về số ngày làm việc giữa nhì ngày được chỉ định và hướng dẫn với ngày vào buổi tối cuối tuần tùy chỉnh.
Các hàm thời hạn trong Excel
Dưới đây là danh sách những hàm Excel chủ yếu dùng làm việc với thời gian.
TIME(hour, minute, second) – trả về thời hạn dưới dạng số sê ri.
TIMEVALUE(time_text) – chuyển đổi một thời hạn nhập dưới dạng một chuỗi văn bản thành một vài thể hiện thời hạn dưới dạng số sê ri.
NOW() – trả về số khớp ứng với ngày và thời hạn hiện trên dạng số sê ri.
HOUR(serial_number) – đổi khác một số thành một giờ đồng hồ dạng số sê ri.
MINUTE(serial_number) – đổi khác một số thành phút bên dưới dạng số sê ri.
SECOND(serial_number) – chuyển đổi một số thành giây bên dưới dạng số sê ri.
Đếm và tính tổng những ô theo màu sắc (chức năng do người dùng xác định)
Microsoft Excel không có ngẫu nhiên chức năng tích thích hợp nào để đếm và tính tổng những ô theo màu sắc, vày vậy học tập Excel Online đã chế tạo ra ra một số tùy chỉnh. Các tính năng này được viết bằng VBA, và bạn có thể thêm nó 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 những hàm của Excel:
GetCellColor(cell) – trả về mã màu sắc của màu nền trực thuộc một ô xác định. GetCellFontColor(cell) – trả về mã màu của màu fonts chữ ở trong một ô xác định. CountCellsByColor(range, màu sắc code) – đếm các ô bao gồm màu nền được chỉ định. CountCellsByFontColor(range, color code) – đếm các ô gồm màu phông chữ được chỉ định. SumCellsByColor(range, màu sắc code) – tính tổng của những ô gồm màu nền tuyệt nhất định. SumCellsByFontColor(range, màu sắc code)) – trả về tổng của các ô với một màu sắc chữ nhất định. WbkCountCellsByColor(cell) – tính các ô bao gồm màu nền được chỉ định và hướng dẫn trong toàn cục bảng tính. WbkSumCellsByColor(cell) – tính tổng các ô với màu nền được hướng dẫn và chỉ định trong toàn thể bảng tính.
Tất nhiên, Microsoft Excel có không ít hàm hơn phần lớn hàm được liệt kê tại chỗ này rất nhiều, tuy nhiên đó là những hàm vô cùng rất cần thiết và ship hàng nhiều cho công việc của bạn.
Để hoàn toàn có thể ứng dụng giỏi Excel vào vào công việc, họ không chỉ nắm rõ được các hàm cơ mà còn phải sử dụng tốt cả những công thế của Excel. Mọi hàm nâng cấp giúp áp dụng tốt vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những lý lẽ thường áp dụng là Data validation, Conditional formatting, Pivot table…
Toàn cỗ những kiến thức này chúng ta đều rất có thể học được trong khóa huấn luyện và đào tạo dưới đây: