Hướng dẫn sử dụng các hàm trong excel là một trong những từ khóa được gg search nhiều nhất về chủ đề hướng dẫn sử dụng các hàm trong excel. Trong bài viết này, timviecgap.vn sẽ viết bài viết hướng dẫn sử dụng các hàm trong excel mới nhất 2020.
Hướng dẫn sử dụng các hàm trong excel mới nhất 2020
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 để vận dụng cho chuỗi văn bản. Dưới đây là những điều cơ bản nhất:
CHỨC NẲNG TEXT
TEXT(value, format_text) được sử dụng để chuyển biến một số hoặc một ngày thành một chuỗi rà soát ở định hình đượ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 hình mong muốn.
Các cách thức sau đây thể hiện hoạt động của tính năng Excel TEXT:
=TEXT(A1,”mm/dd/yyyy”) – chuyển ngày tháng trong ô A1 sang một chuỗi văn bản theo định dạng ngày truyền thống của Hoa Kỳ, chẳng hạn như “01/01/2015” (tháng / ngày / năm).
=TEXT(A1,”€#,##0.00″) – chuyển một số trong A1 thành một chuỗi ký tự tiền tệ giống như “€3.00”.
Gợi ý CHO bí quyết TEXT:
Hàm TEXT sử dụng để biến đổi một ngày sang định hướ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, [text2], …) được thiết kế để nối nhiều đoạn văn bản lại với nhau hoặc hòa 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ự đủ sức đạt được bằng cách dùng Excel & operator, giống như thể hiện trong ảnh chụp màn hình sau.
HÀM TRIM
TRIM(text) được sử dụng để loại bỏ hàng đầu, dấu phương pháp cũng giố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 mong muốn loại bỏ chân trời. ảnh chụp màn ảnh sau minh họa một gợi ý về mẹo sử dụng:
HÀM SUBSTITUTE
SUBSTITUTE(text, old_text, new_text, [instance_num]) sẽ thay thế một bộ ký tự bằng một ký tự không giống 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 mong muốn thay thế.
New_text – các ký tự mà bạn mới bạn mong muốn dùng để thế chỗ văn bản cũ.
Nth_appearance – một tham số tùy lựa chọn cho old_text mà bạn mong 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ụ: bí quyết 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ách thức SUBSTITUTE:
– Loại bỏ ngắt thể loại trong một ô
– biến động chuỗi văn bản với dấu phân phương pháp tùy chỉnh thành ngày
HÀM VALUE
VALUE(text) – biến động một chuỗi văn bản thành một số.
Hàm này thực sự hữu dụng khi biến đổi các trị giá định dạng văn bản đại diện cho chiếc sim thành số sim đủ sức được dùng trong các cách thức Excel khác và tính toán.
ví dụ CHO HÀM VALUE:
biến đổi chữ số định hướng văn bản thành số
Hàm VALUE sử dụng để biế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ác nhau.
Ví dụ: nếu A2 là “apples” và B2 là “Apples”, phương pháp =EXACT(A2, B2) sẽ trả về FALSE vì chúng k khớp chuẩn xác.
Hàm EXACT hiếm có khi được dùng riêng, nhưng lại bổ ích khi kết hợp với những hàm khác để tạo nên tác vụ khó khăn hơn, gợi ý giống như hòa hợp với Vlookup dùng thao tác với văn bản trong Excel.
Lớp học word excel ở Hà Nội
CÁC HÀM sử dụng ĐỂ chuyển đổi VẲN BẢN (UPPER, LOWER, PROPER)
Microsoft Excel phân phối 3 hàm văn bản để biế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à biến động all các chữ cái khác sang chữ thường.
Trong cả ba chức năng, đối số văn bản đủ nội lực 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 bí quyết để trả về một số ký tự nhất định từ một chuỗi văn bản, dùng một trong các hàm Excel sau.
LEFT(text, [num_chars]) – trả về một số ký tự nhất định diễn ra từ đầu chuỗi văn bản.
RIGHT(text,[num_chars]) – trả về một số ký tự được chỉ định kể 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, khởi đầu ở bất kỳ vị trí nào bạn chỉ định.
Trong các hàm này, bạn phân phối 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 mong muốn trích xuất.
– Start_num – chỉ ra ngành bắt đầu (tức là vị trí của ký tự trước hết bạn mong muốn trích xuất).
– Num_chars – số ký tự bạn mong muốn trích xuất.
CÁC HÀM LOGIC TRONG EXCEL
Microsoft Excel phân phối một số ít các chức năng logic phân tích một điều kiện định hình và trả về trị giá tương ứng.
AND, OR, XOR chức năng
AND(logical1, [logical2], …) – trả về TRUE nếu all các đối số đánh giá TRUE, FALSE nếu ngược lại.
OR(logical1, [logical2], …) – trả về TRUE nếu ít nhất một trong các đối số là TRUE.
XOR(logical1, [logical2],…) – trả về một trị giá hợp lệ duy nhất trong all các đối số. tính năng này đã được giới thiệu trong Excel 2013 và k có sẵn trong các phiên bản trước đó.
HÀM NOT
NOT(logical) – đảo ngược trị giá đối số của nó, nghĩa là nếu nghiên cứu lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại.
ví dụ, cả hai bí quyết 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, [value_if_true], [value_if_false])
công thức IF tra cứu 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 trị giá không giống (value_if_false) nếu điều kiện không được cung cấp.
gợi ý, mẹo = IF =IF(A1<>””, “good”, “bad”) trả về “good” nếu có bất kỳ trị giá nào trong ô A1, “bad” nếu k.
Và đây là một ví dụ về phương thức hàm IF phối 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 dùng để check lỗi trong một bí quyết 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) – test lỗi của bí quyết hoặc biểu thức nghiên cứu. Nếu có lỗi, cách 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 mẹo sẽ được trả về. Hàm này xử lý toàn bộ các lỗi Excel đủ sức xảy ra, gồm có 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 mẹo IFERROR đơn giản nhất:
CÁC công thức TOÁN HỌC TRONG EXCEL
Excel có vô vàn tính 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. thành ra, chúng ta hãy trao đổi về một vài chức năng toán học cơ bản đủ sức 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 cần thiết của Excel để thêm trị giá của các ô trong một phạm vi dựng lại theo dưới đây.
HÀM SUM
SUM(number1,[number2],…) 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 trị giá số bằng công thức.
ví dụ, phương thức toán học không khó khăn =SUM(A1:A3, 1) cộng các trị giá 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 sử dụng để cộng các ô trong một phạm vi dựng lại cung cấp một điều kiện nhất định. Sự khác biệt là SUMIF có thể phân tích chỉ một tiêu phù hợp, trong khi SUMIFS, được mô tả trong Excel 2007, cho phép dùng nhiều tiêu chí. Hãy note rằng thứ tự của các đối số là không giống nhau trong mỗi chức năng:
SUMIF(range, criteria, [sum_range]) SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
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 ô thống kê nếu điều kiện được cung cấp.
hình chụp màn ảnh sau đây giải thích ý 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 tính năng của Microsoft Excel sử dụng để giải quyết mảng. Nó kết hợp với các yếu tố mảng được phân phối và trả về tổng hàng hóa.
Bản chất của hàm SUMPRODUCT có thể khó nắm bắt do đó các bạn đủ sức theo dõi các ví dụ cụ thể của hàm này trong post dành riêng cho hàm SUMPRODUCT để có được hiểu biết đa số.
Tạo các số ngẫu nhiên (RAND và RANDBETWEEN)
Microsoft Excel cung cấp 2 chức năng để xây dựng các số ngẫu nhiên. Cả hai đều là các hàm k ổ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 sử dụng TRÒN
Có một số chức năng để sử dụng tròn số trong Excel, và hướng dẫn làm tròn của Excel của chúng tôi vừa mới giúp bạn lý giải mẹo sử dụng những tính 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 để nghiên cứ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 – sử dụng tròn lên, đến số ký tự được chỉ định.
ROUNDDOWN – sử dụng 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 – sử dụng 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 trị giá trong mỗi hàng, hoặc một hàng được chỉ định
cải thiện màu sắc của hàng trong Excel
CÁC HÀM đo đạt 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 để dùng cho những công việc đưa tính chuyên nghiệp cao.
TÌM CÁC giá trị to NHẤT, NHỎ NHẤT VÀ TRUNG BÌNH
MIN(number1, [number2], …) – trả về trị giá nhỏ nhất từ mục lục các đối số.
MAX(number1, [number2], …) – trả về giá trị lớn nhất từ danh mục các đối số
AVERAGE(number1, [number2], …) – trả giá trị trung bình của các đối số.
SMALL(array, k) – trả về trị giá nhỏ nhất thứ không trong mảng.
LARGE (mảng, k) – trả về trị giá to nhất thứ không trong mảng.
hì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à mục lục 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, [value2], …) – trả về số lượng các giá trị số (số và ngày) trong mục lục các đối số.
COUNTA(value1, [value2], …) – 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 trị giá lỗi và các chuỗi văn bản rỗng (“”) được trả về bởi các bí quyết không giống.
COUNTBLANK(range) – đếm số ô trống trong một phạm vi định hì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, [criteria_range2, criteria2]…) – đếm số ô đáp ứng được toàn bộ các tiêu chí được chỉ định.
ảnh chụp màn hình sau minh họa hướng dẫn hoạt động của các hàm đếm ô:
Chú like. Khi ngày tháng được lưu trữ như số trong Excel, chúng cũng được đếm bởi các tính năng đo đạt của Excel. Ví dụ: công thức =COUNTIF(A2:A9, “>5”) đếm ngày trong ô A8 trong hình chụp màn hình ở trên vì nó được lưu trữ dưới dạng 42005 trong nền tả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 bổ í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 trị giá 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 trước hết và kéo dữ liệu thích hợp từ cùng một hàng trong một cột khác.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Lookup_value – giá trị cần kiếm tì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 – định hình tìm kiếm là hoàn toàn chuẩn xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).
Ví dụ: cách 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, [column_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, [match_type]) – search lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.
Sự phối hợp giữa các hàm MATCH và INDEX đủ nội lực đượ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, [a1]) – 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 gợi ý về cách thức dễ dàng nhất:
HÀM OFFSET
OFFSET(reference, rows, cols, [height], [width]) – trả về một tham chiếu đến một dãy ô được tính từ một ô khởi đầ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ề trị giá 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à trái 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, [friendly_name]) – Tạo một siêu liên kết đến một ebook đượ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 tính năng để không khó khăn hóa công việc của các nhà cai quản kế toán, các nhà nghiên cứu tài chính và các chuyên gia về bank. Dưới đây là một trong số những hàm chính đủ nội lực được dùng để tính lãi suất.
HÀM FV
FV(rate, nper ,pmt ,[pv], [type]) – tính trị giá 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
so với những người dùng Excel liên tục, các hàm ngày tháng ngày càng trở nên thân thuộc hơn và được sử dụng trong rất nhiều trường hợp không giống 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 biến một chuỗi văn bản đại diện cho ngày để định hướ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 yếu tố 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ông giống 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 khởi đầu.
YEARFRAC – trả về phần trăm của một khoảng thời gian trong một năm.
TÍNH CÁC NGÀY sử dụng VIỆC
WORKDAY – Trả về một số tuần tự thể hiện số ngày làm việc, đủ nội lực 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 sử dụng 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 sử 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) – biế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 bây giờ dạng số sê ri.
HOUR(serial_number) – chuyển biến 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 biến 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 user XÁC ĐỊNH)
Microsoft Excel k có bất kỳ tính 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 Trực tuyến đã 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 đủ nội lực thêm chúng vào các bảng tính trong nơi lập trình của Excel – Visual Basic for Applications. Dưới đây là danh mục các hàm của Excel:
GetCellColor(cell) – trả về mã màu của màu nền thuộc một ô dựng lại.
GetCellFontColor(cell) – trả về mã màu của màu phông chữ thuộc một ô xác định.
CountCellsByColor(range, color code) – đếm các ô có màu nền được chỉ định.
CountCellsByFontColor(range, color code) – đếm các ô có màu phông chữ được chỉ định.
SumCellsByColor(range, color code) – tính tổng của các ô có màu nền nhất định.
SumCellsByFontColor(range, color code)) – trả về tổng của các ô với một màu chữ nhất định.
WbkCountCellsByColor(cell) – tính các ô có màu nền được chỉ định trong toàn bộ bảng tính.
WbkSumCellsByColor(cell) – tính tổng các ô với màu nền được chỉ định trong all bảng tính.
dĩ nhiên, Microsoft Excel có nhiều hàm hơn những hàm được liệt kê ở đây rất nhiều, ngoài ra đâ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.
Để đủ nội lực vận dụng tốt Excel vào trong công việc, chúng ta k chỉ nắm vững được các hàm mà còn phải dùng tốt cả các công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào công việc giống như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường dùng là Data validation, Conditional formatting, Pivot table…
tất cả những văn hóa này các bạn đều đủ nội lực học được trong khóa học dưới đây
Nguồn: https://blog.hocexcel.online/
