Học các hàm Excel cơ bản, hàm excel được sử dụng phổ biến nhất hiện nay. Ngay sau đây Vietgiatrang sẽ liệt kê ra các hàm văn bản, toán học, logic và các hàm chức năng cần thiết của Excel. Kèm theo ví dụ minh họa chi tiết cho các hàm giúp bạn đọc hiểu rõ nhất!
Hầu hết các hàm dưới đây là các hàm trang tính Excel được sử dụng làm công thức trong ô. Cuối danh sách là các hàm người dùng xác định (UDF).
1. Các hàm excel toán học cơ bản
Microsoft Excel có cả tấn các hàm để tính toán như tính số mũ, logarit,… Và nếu liệt kê ra hết thì chúng ta sẽ mất vài trang giấy! Vậy nên trong giới hạn bài viết này, chúng ta sẽ chỉ tìm hiểu những hàm tính toán thông dụng trong công việc hàng ngày.
1.1 Hàm tính tổng các ô
Có 4 hàm để bạn tính tổng các ô trong một phạm vi cho phép như sau:
+ Hàm SUM
Cú pháp: SUM([number1];[number2];…)
Chức năng: Trả về kết quả là tổng các đối số trong nó. Đối số có thể là các số, các giá trị chỉ định ô hoặc giá trị số theo hướng công thức.
Ví dụ một công thức toán học đơn giản nhất như:
=SUM(A1:A13;1) tức là ta sẽ công các số trong ô từ A1 đến A13 và cộng thêm 1 nữa.
+ Hàm SUMIF và SUMIFS (tính tổng có điều kiện)
Cả hai hàm đều dùng để tính tổng các ô trong một phạm vi nhất định đáp ứng đúng điều kiện. Điểm khác biệt duy nhất là SUMIF chỉ đánh giá được một tiêu chí duy nhất. Còn SUMIFS có thể đánh giá được nhiều tiêu chí khác nhau.
Cú pháp:
- =SUMIF(range; criteria;[sum_rage])
- =SUMIFS(sum_range;criteria_range;criterial;[criteria_range2,criteria2],…)
Trong đó:
- range, criteria_range: là phạm vi các ô được đánh giá theo tiêu trí tương ứng
- criteria: là điều kiện- tiêu chí phải đáp ứng.
- sum_range: phạm vi các ô được tính tổng nếu thỏa mãn điều kiện.
Ví dụ: Hình ảnh dưới đây mô phỏng cho sự khác nhau của SUMIF và SUMIFS trong đời thực:
Với lệnh =SUMIF(A2:A6,”apples”,B2:B6): tức là:
- Tìm sản phẩm táo- apples trong các ô từ A2 đến A6,
- nếu đúng thì cộng các giá trị của ô tương ứng từ B2 đến B6 để cho kết quả số tiền bán táo- Apples sales.
Với lệnh =SUMIFS(B2:B6,A2:A6,”apples”,C2:C6,”seller 1”) tức là:
- Tìm sản phẩm táo- apples (từ ô A2 đến A6) do người bán 1- seller 1 (từ C2 đến C6)
- Cho ra tổng số tiền 1 bán táo 1 (Apples- Seller 1 sales).
+ Hàm SUMPRODUCT
Trong Excel không có nhiều hàm xử lý mảng. Trong đó SUMPRODUCT là một hàm trong đó.
Cú pháp: =SUMPRODUCT(array1,array2,…)
Chức năng: Nhân các thành phần của các mảng- array được cung cấp và trả về tổng của các tích. Bản chất của SUMPRODUCT có thể khó hiểu nhưng qua ví dụ sau bạn sẽ sáng tỏ được nó.
Ví dụ:
Phép SUMPRODUCT sẽ nhân các thành phần của mảng này với thành phần tương ứng của mảng kia và trả về kết quả là tổng của các tích đó.
Ví dụ: Đếm số lần bán táo có doanh số thực tế lớn hơn doanh số dự kiến:
Hàm -(B3:B10<C3:C10) trả về giá trị 1 nếu đúng và trả về giá trị 0 nếu sai.
3.2 Hàm tạo số ngẫu nhiên (RAND và RANDBETWEEN)
Microsoft Excel cung cấp 2 hàm tạo số ngẫu nhiên. Nó là hàm biến động có nghĩa là kết quả trả về là một số mới trong mỗi giây bảng tin hoạt động.
Cú pháp:
- =RAND(): Trả về giá trị ngẫu nhiên giữa hai con số 0 và 1 (số thực)
- =RANDBETWEEN(bottom,top): Trả về giá trị số nguyên ngẫu nhiên trong đoạn từ bottom đến top nhất.
Hãy xem ví dụ sau để biết cách sử dụng hai hàm này nhé:
3.3 Hàm làm tròn
Microsoft Excel cung cấp nhiều hàm để bạn làm tròn cho một số.
+ Hàm ROUND
Cú pháp: =ROUND (number, num_digits)
Chức năng: Là hàm làm tròn chính, dùng làm tròn một số theo chỉ định.
Trong đó:
- number: là số cần làm tròn
- number_digits: chỉ số làm tròn thành, có thể là số âm hoặc dương.
+ Hàm ROUNDUP, ROUNDDOWN
Cú pháp :
- =ROUNDUP (number, num_digits),
- =ROUNDUP (number, num_digits)
Chức năng: Dùng để làm tròn lên và xuống đến một số theo chỉ định.
Trong đó:
- number: là số được chọn để làm tròn
- number_digits: số chữ số bạn muốn được làm tròn thành. Nó hoạt động giống như hàm =ROUND() nhưng khác nhau ở chỗ nó luôn làm tròn lên hoặc xuống.
Ví dụ: làm tròn lên
Ví dụ làm tròn xuống:
+ Hàm MROUND
Cú pháp: =MROUND(number;multiples)
Chức năng: Làm tròn chữ số lên hoặc xuống thành một bội số đã chỉ định.
Trong đó:
- number: là số cần làm tròn
- multiples: Bội số của số mà bạn muốn làm tròn.
Nếu giá trị của number lớn hơn hoặc bằng một nửa còn lại thì hàm sẽ làm tròn lên. Ngược lại sẽ làm tròn xuống. MROUND rất thích hợp để làm tròn tiền đô la:
Và cũng có thể làm tròn giờ:
+ Hàm FLOOR
Cú pháp: =FLOOR(number,significance)
Chức năng: Làm tròn xuống một số đến bội số gần nhất của significance.
Trong đó:
- number: là số cần làm tròn xuống
- Significance: là số bội số làm tròn xuống.
Ví dụ: =FLOOR(2,5;2) : là làm tròn số 2,5 đến bội số gần nhất của 2>> kết quả là 2.
Lưu ý: nếu number là một số dương thì significance là một số dương và tương tự nếu number là một số âm thì significance là một số âm. Nếu không tuân thủ thì kết quả trả về sẽ là #NUM.
Ví dụ:
+ Hàm CEILING
Cú pháp: =CEILING(number,significance)
Chức năng: Làm tròn lên một số đến bội số gần nhất của significance.
Trong đó:
- number: là số cần làm tròn xuống
- significance: là số bội số làm tròn xuống.
Ví dụ: =CEILING(2,5;2) : là làm tròn số 2,5 đến bội số gần nhất của 2>> kết quả là 4.
Lưu ý: nếu number là một số dương thì significance là một số dương và tương tự nếu number là một số âm thì significance là một số âm. Nếu không tuân thủ thì kết quả trả về sẽ là #NUM.
Ví dụ minh họa:
+ Hàm INT
Cú pháp: =INT(number)
Chức năng: làm tròn xuống một số nguyên gần nhất.
Ví dụ: nếu bạn muốn làm tròn xuống số -2,5 thì hãy dùng: =INT(-2,5). Kết quả trả về là -3.
Và một số ví dụ khác trong hình minh họa
+ Hàm TRUNC
Cú pháp: =TRUNC (number, [num_digits])
Chức năng: Cắt bớt một giá trị số nhất định thành một số có vị trí thập phân được chỉ định.
Ví dụ:
+ Hàm EVEN và ODD
Đây là 2 hàm được cung cấp để làm tròn một số thành số nguyên.
Cú pháp:
- =ODD(number)
- =EVEN(number)
Chức năng: ODD là làm tròn đến số nguyên lẻ gần nhất trong khi EVEN là làm tròn đến số nguyên chẵn gần nhất. Chúng có thể làm tròn mọi số thực. Nếu khác định dạng số sẽ trả về giá trị #VALUE.
Ví dụ:
- =ODD(2,4) trả về 3
- =EVEN(5,2) trả về 6
- =ODD(6,6) trả về 7
- =EVEN(-6.7) trả về -6
- =ODD(9.1) trả về 9
- =EVEN(-3.3) trả về -4
3.4 Hàm chia lấy dư (MOD)
Cú pháp: =MOD(number;divisor)
Chức năng: Trả về phần dư của phép chia number cho divisor
Ví dụ như:
2. Hàm văn bản/chuỗi
Có rất nhiều hàm trong Microsoft Excel thực hiện trên chuỗi. Dưới đây là một số hàm Excel cơ bản với chuỗi bao gồm:
2.1 Hàm TEXT chuyển đổi ngày tháng
Cú pháp: =TEXT(value,format_text)
Chức năng: Sử dụng để chuyển đổi một số hay một ngày tháng sang một chuỗi ký tự tương ứng theo định dạng định sẵn. Trong đó:
- Value: Là giá trị số bạn muốn chuyển đổi thành dạng văn bản.
- Formal_text: Là định dạng văn bản mong muốn.
Ví dụ sau đây sẽ cho bạn thấy hàm TEXT hoạt động như thế nào.
- “=TEXT(A2,”mm/dd/yyyy”)“ : Chuyển đổi một dạng ngày tháng trong ô A2, thành dạng văn bản: “07/01/2015” (tháng/ ngày/ năm).
- “=TEXT(A4,”€#,##0.00”)”: Chuyển đổi một số trong ô A4 thành chuỗi có dạng “€3.00”.
2.2 Hàm CONCATENATE
Cú pháp: =CONCATENATE(text1,[text2],…): Là hàm có chức năng kết nối nhiều văn bản với nhau. Hoặc kết hợp giá trị của một số ô vào một ô duy nhất.
Cách khác để dùng hàm excel cơ bản này là sử dụng toán từ & trên bàn phím. Hãy quan sát ví dụ sau:
“=CONCATENATE(A2,” “,B2)” cũng như là”=A2&” “&B2” là ta ghép chuỗi trong ô A2 với dấu cách ở giữa và số trong ô B2 thành chuỗi duy nhất: “Project 1”.
2.3 Hàm TRIM
Cú pháp: =TRIM(text).
Chức năng: giúp bạn loại bỏ các khoảng trắng thừa ở đầu, cuối và giữa các từ trong chuỗi ký tự.
Trong đó text là một chuỗi văn bản hay một ô chứa văn bản mà bạn muốn xóa khoảng trắng. Hình ví dụ dưới đây sẽ giúp bạn hình dung ra cách hàm này hoạt động.
Chúng ta thấy tình huống là khoảng trắng ở đầu, và giữa 2 từ “Project” và “1” bị thừa trong ô A2. Sử dụng hàm TRIM để loại bỏ các khoảng trắng thừa ta được kết quả như ô B2.
2.4 Hàm SUBSTITUTE
Cú pháp: =SUBSTITUTE(text,old_text,new_text,[instance_num])
Chức năng: Thay thế một ký tự cũ bằng một ký tự mới trong một ô hay một chuỗi văn bản được chỉ định. Các thành phần trong cú pháp của hàm SUBSTITUTE là:
- Text:: Chuỗi văn bản gốc hoặc một ô bạn tham chiếu đến để thay đổi một ký tự trong đó.
- Old_text: Ký tự cũ bạn muốn thay thế.
- New_text: Ký tự mới.
- [instance_num]: Tham số chỉ định sự xuất hiện của old_text mà bạn muốn thay thế bằng new_text. Nếu bỏ qua phần này thì tất cả ký tự cũ của bạn sẽ được thay thế bằng ký tự mới.
Ví dụ:
Lệnh “=SUBSTITUTE(A2, “,”, “;”)”: đã thay thế toàn bộ dấu “,” thành dấu “;” trong ô A2.
2.5 Hàm VALUE
Cú pháp: VALUE(text)
Chức năng: Sử dụng để chuyển đổi một chuỗi văn bản thành một số. Chức năng này rất hữu ích khi chuyển đổi các giá trị dạng văn bản thể hiện cho các số thành các số. Điều này có thể được sử dụng trong các công thức Excel và các phép tính toán.
Ví dụ minh họa dưới đây sẽ cho bạn thấy sự hoạt động của hàm excel cơ bản này:
- Chuyển đổi từ dạng văn bản thành số.
- Chuyển đổi từ dạng văn bản thành ngày tháng: Hàm VALUE có thể chuyển đổi bất kỳ dạng văn bản nào giống ngày tháng hoặc dạng số thành định dạng ngày tháng mà bạn chọn.
- Text là giá trị tham chiếu đến một ô hoặc một chuỗi văn bản bạn muốn chuyển.
- Hàm VALUE có thể xử lý cả ngày, tháng, năm và giờ. Trong đó giờ sẽ được chuyển về giá trị thập phân như dòng 6 trong hình.
2.6 Hàm EXACT
Cú pháp: =EXACT(text1,text2)
Chức năng: So sánh hai chuỗi văn bản text1 và text2 và trả về kết quả TRUE nếu các ký tự trong chuỗi hoàn toàn giống nhau. Xét cả về chữ hoa, chữ thường. Nếu khác nhau thì sẽ trả về FALSE
Ví dụ:
Chúng ta thấy ví dụ trên so sánh hai chuỗi trong ô A1 và A2.
Hàm viết ra là: =EXACT(A1;A2). Kết quả trả về là FALSE vì “Táo” dùng chữ hoa còn “táo” dùng chữ thường.
Hàm EXACT hiếm khi được sử dụng riêng lẻ thông thường. Nhưng nó lại hữu ích trong các công việc phức tạp hơn. Như thực hiện Vlook phân biệt chữ hoa và chữ thường trong Excel.
2.7 Các hàm excel cơ bản thay đổi kiểu chữ của văn bản (UPPER, LOWER, PROPER)
Microsoft Excel cung cấp 3 chức năng để chuyển đổi giữa UPPER, LOWER, PROPER.
Cú pháp:
- =UPPER(text): Chuyển đổi tất cả các ký tự trong chuỗi văn bản được chỉ định thành chữ hoa.
- =LOWER(text): Chuyển đổi tất cả các ký tự trong chuỗi văn bản được chỉnh định thành chữ thường.
- =PROPER(text): Viết hoa chữ cái đầu tiên của một từ và các chữ cái còn lại viết thường.
2.8 Hàm trích xuất ký tự văn bản (LEFT, RIGHT, MID)
Nếu bạn muốn kết quả trả về là một số ký tự nhất định trong chuỗi văn bản. Hãy sử dụng một trong số các hàm có cú pháp như sau:
- =LEFT(text;[num_char]): Trả về một số ký tự trong một văn bản được chỉ định kể từ đầu văn bản đó.
- =RIGHT(text;[num_char]): Trả về một số ký tự trong một văn bản được chỉ định kể từ cuối văn bản đó.
- =MID(text;start_num;num_chars): Trả về một số ký tự trong một văn bản được chỉ định kể từ vị trí Start_num đến vị trí num_chars.
Trong cú pháp gồm:
- Text: Một chuỗi văn bản hoặc giá trị tham chiếu đến một ô chứa các ký tự bạn muốn trích xuất.
- Start_num: Chỉ ra nơi bắt đầu
- Num_chars: Chỉ ra số ký tự bạn muốn trích xuất.
3. Các hàm excel logic cơ bản
Microsoft Excel cung cấp một số đánh giá các điều kiện và trả về kết quả tương ứng.
3.1. Hàm AND, OR, XOR
Cú pháp:
- =AND(logical1;logical2;…): Trả về TRUE nếu tất cả các đối số đánh giá là TRUE và ngược lại trả về FALSE.
- =OR(logical1;logical2;…): Trả về TRUE nếu có chỉ ít nhất 1 đối số đánh giá là TRUE.
- =XOR(logical1;logical2;…): Trả về kiểu logic Exclusive Or của tất cả các đối số.
Ví dụ dưới đây sẽ cho bạn thấy rõ hơn về chức năng của các hàm trên:
3.2. Hàm NOT
Cú pháp: =NOT(logical)
Chức năng: Đảo ngược giá trị của một đối số. Tức là nếu logic là FALSE thì hàm NOT sẽ trả về là TRUE và ngược lại.
Ví dụ: Cả hai công thức sau đều trả về FALSE:
- =NOT(TRUE)
- =NOT(2*2=4)
Ngoài ra, hàm NOT còn nhiều chức năng khác bạn nên khám phá.
3.3. Hàm IF
Hàm IF trong Excel đôi khi gọi là hàm có điều kiện vì nó trả về một giá trị dựa trên điều kiện.
Cú pháp: =IF(logical_test;[value true];[value_false]).
Chức năng: Công thức áp dụng kiểm tra các điều kiện được thể hiện trong đối tượng logical_test và trả về một giá trị value_true nếu đáp ứng được điều kiện. Và ngược lại sẽ trả về value_false.
Ví dụ:
- Để kiểm tra ô C2, C3 xem có đúng là “Nho” hay không!
Ta thấy rằng C2 chứa “Táo” nên sẽ trả về giá trị “Sai”. Còn C3 chứa “Nho” nên trả về “Đúng”.
2. Ví dụ hàm IF lồng “xếp học lực” cho từng điểm thi trong ô A2:
Cú pháp: “IF(A2>80;”Brilliant”;IF(A2>50;”Good”;IF(A2>30;”Fair”;”Poor”)))”.
3.4. Hàm IFEROR và IFNA
Cả hai hàm này đều được sử dụng để đánh giá một công thức có bị lỗi hay không. Nếu có thì Excel sẽ trả về một giá trị được chỉ định thay thế.
Cú pháp: =IFERROR(value;value_if_error)
Chức năng: Dùng để kiểm tra xem công thức có lỗi hay không. Nếu có thì sẽ trả về giá trị trong value_if_error, nếu không sẽ giữ nguyên giá trị. Hàm này xử lý được hầu hết các lỗi trong Excel bao gồm: VALUE, N/A, REF, NAME, NUM…
Cú pháp: =IFNA(value;value_if_na)
Chức năng: Nó cũng hoạt động tương tự như IFERROR nhưng chỉ xử lý lỗi # N/A.
Ví dụ sau đây là minh họa đơn giản nhất cho IFERROR.
Phép chia không thể thực hiện nếu mẫu bằng 0 nên khi đó kết quả trả về là: “You cannot divide by 0!”.
4. Các hàm Excel cơ bản chức năng thống kê
Trong excel có các hàm thống kê rất đặc biệt, một trong số đó có thể sử dụng cho những công việc chuyên nghiệp.
4.1. Tìm chức năng lớn nhất, nhỏ nhất, giá trị trung bình
- =MIN (number1, [number2],…): trả về giá trị nhỏ nhất từ danh sách các đối số.
- =MAX (number1, [number2],…): trả về giá trị lớn nhất từ danh sách các đối số
- =AVERAGE (number1, [number2],…): trả về giá trị trung bình của các đối số.
- =SMALL (mảng, 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.
Ví dụ:
4.2. Hàm đếm ô
Dưới đây là danh sách các hàm excel cơ bản cho phép đếm ô theo một định dạng nhất định hoặc dựa trên điều kiện bạn đặt ra.
| Cú pháp | Chức năng |
| =COUNT (value1, [value2],…) | Trả về giá trị số (số và ngày) trong danh sách các đối số. |
| =COUNTA (value1, [value2],…) | Trả về số ô không trống trong danh sách các đối số. Các giá trị lỗi hay trống sẽ được báo bởi hàm khác. |
| =COUNTBLANK(range) | Đếm số ô trống trong phạm vi được chọn. Các chuỗi có văn bản trống “” cũng được đếm. |
| =COUNTIF (range, criteria) | Đếm số ô thỏa mãn tiêu chí bạn đặt ra. |
| =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) | Đếm các ô thỏa mãn tất cả tiêu chí mà bạn đặt ra. |
Ví dụ:
5. Hàm tra cứu và tham chiếu
Hàm tra cứu và tham chiếu này rất hữu ích khi bạn muốn tìm kiếm một thông tin trong bảng dựa trên giá trị trên một cột hoặc trả về một ô cần tìm kiếm.
5.1. Hàm VLOOKUP
Hàm VLOOKUP dùng để tìm kiế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. Nó yêu cầu các đối số sau:
Cú pháp: =VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])
Trong đó:
- lookup_value: giá trị bạn cần tìm
- table_array: 2 hoặc nhiều cột của dữ liệu
- col_index_num: Số cột lấy dữ liệu về.
- range_lookup: Xác định xem nên tìm kiếm đối sánh chính xác hay đối sánh gần đúng.
- Đối sánh gần đúng (TRUE). Nếu không tìm thấy kết quả phù hợp chính xác, công thức sẽ tìm kiếm kết quả phù hợp gần nhất.
- Đối sánh chính xác(FALSE). Công thức tìm kiếm một giá trị chính xác bằng giá trị tra cứu. Nếu không tìm thấy kết quả khớp chính xác, giá trị # N / A được trả về.
Ví dụ: Đối sánh chính xác:
Hàm: =VLOOKUP(E1;A2:B6;2;FALSE)
Chức năng: Tìm kiếm tốc độ di chuyển của động vật “Người” trong phạm vi từ A2 đến B6 và dữ liệu lấy từ cột 2.
5.2. Hàm INDEX
Cú pháp: INDEX(array;row_num;column_num)
Chức năng: Trả về một giá trị tham chiếu đến một ô trong mảng đã lựa chọn dựa trên số hàng và số cột bạn chỉ định.
Trong đó:
- array: là một dải ô, một dải ô được đặt tên hoặc một bảng.
- row_num: Là số hàng trong mảng mà bạn lấy dữ liệu từ đó để trả về kết quả.
- column_num: Là số cột trong mảng mà bạn lấy dữ liệu từ đó để trả về kết quả. Nếu phần này bị bỏ qua thì cần có row_num.
Ví dụ: INDEX(B2:D6;G2;G3)
Ta chọn mảng từ ô B2 đến ô D6 chứa số lượng. Ta lấy giao của hàng 3 và cột 3 vậy ta được số sản phẩm 3 trong tuần 3 là 53.
5.3. Hàm MATCH
Cú pháp: =MATCH(lookup_value, lookup_array, [match_type])
Chức năng: Tìm kiếm lookup_value ( giá trị tra cứu) trong lookup_array (mảng tra cứu) và trả về [match_type]- kiểu giá trị: chính xác hoặc gần đúng.
Trong đó: [match_type] có 3 kiểu
- 1 ( hoặc bỏ trống): Tìm giá trị lớn nhấn <= lookup_value.
- 0: Tìm giá trị chính xác = lookup_value
- -1: Tìm giá trị nhỏ nhất >= lookup_value.
Ví dụ: Tìm kiếm học sinh tên “Hoàng” xem ở vị trí thứ mấy trong danh sách.
Sự kết hợp của hàm VLOOKUP và INDEX là một giải pháp mạnh mẽ hơn. Điều này được minh họa qua ví dụ sau:
Ví dụ: Tìm điểm của bạn “Nam” trong danh sách: =INDEX(C2:C5;MATCH(G2;B2:B5;0))
5.4. Hàm INDIRECT
Cú pháp: =INDIRECT (ref_text, [a1])
Chức năng: Trả về tham chiếu ô hoặc dải được chỉ định bởi một chuỗi văn bản.
Trong đó:
- ref_text: là một tham chiếu ô hay một tham chiếu đến một ô ở dạng văn bản hoặc một phạm vi được đặt tên
- [a1]: Kiểu tham chiếu, có 2 loại là TRUE ( hoặc để trống): gần đúng và FALSE: chính xác.
Ví dụ: Trả về giá trị trong ô A2
- Ô D1 có lệnh: =INDIRECT(C1)
- Đây là tham chiếu đến ô- C2 dạng văn bản : “A2” và trả về giá trị của ô A2 là 222.
- Kiểu tham chiếu gần đúng
- Ô D3 có lệnh: =INDIRECT(C3;FALSE)
- Tham chiếu đến ô C3 có kiểu phạm vi R2C1 tức là hàng 2, cột 1. Đó chính là ô A2 nên trả về giá trị 222.
- Kiểu tham chiếu chính xác- FALSE
- Ô D5 có lệnh: =INDIRECT(C5;FALSE)
- Tham chiếu đến ô C5 có kiểu R3C tức là hàng 3 và cùng cột C chính là C3 và cũng là R2C1 và cũng là A2. Vậy nên cũng trả về 222.
- Kiểu tham chiếu chính xác.
5.5. Hàm OFFSET
Cú pháp: =OFFSET (reference, rows, cols, [height], [width])
Chức năng: Hàm OFFSET trong Excel trả về một giá trị trong một ô hoặc dải ô là một số
Với 3 đối tượng đầu tiên là bắt buộc:
- reference: Tham chiếu- là một ô hay một loạt các ô liền kề nhau mà từ đó bạn đặt giá trị Offset. Bạn có thể coi đó là điểm khởi đầu.
- rows: Số hàng di chuyển từ đầu lên hoặc xuống. Nếu các hàng là một số dương, công thức sẽ di chuyển bên dưới tham chiếu bắt đầu. Trong trường hợp là số âm, công thức sẽ di chuyển vượt lên trên tham chiếu bắt đầu.
- cols: Số cột- là số cột bạn muốn công thức di chuyển kể từ điểm bắt đầu. Cols có thể dương (ở bên phải tham chiếu bắt đầu) và âm (ở bên trái của tham chiếu bắt đầu).
2 đối số tùy chọn:
- height: Là chiều cao tính theo số hàng từ tham chiếu trả về.
- width: Chiều rộng tính theo số cột của tham chiếu trả về.
Cả tham số chiều cao và chiều rộng đều là dương. Nếu bỏ qua hai tham số này, chiều rộng hoặc chiều cao của tham chiếu bắt đầu sẽ được sử dụng.
Ví dụ : Minh họa công thức OFFSET theo cách đơn giản nhất:
- Offset cho một ô
Điểm tham chiếu bắt đầu là A1, sau đó số hàng là 3 ta được ô A4, số cột là 1 ô ta sẽ di chuyển sang ô B4.
Ta có hàm: =OFFSET(A1;E1;1) trả về kết quả là $20.
- Offset cho một dải ô kết hợp với SUM:
Hàm trả về là dải ô kích thước 1 hàng 3 cột: ta được tổng: 15 + 45 + 20 = $80.
5.6. Hàm TRANSPOSE
Cú pháp: =TRANSPOSE(array)
Chức năng: Chuyển một dải ô theo chiều ngang thành một dải ô theo chiều dọc và ngược lại. Tức là chuyển hàng thành cột hoặc cột thành hàng.
Ví dụ: Chúng ta sẽ chuyển đổi bảng liệt kê các tiểu bang của Hoa Kỳ theo dân số:
Chúng ta dùng công thức: “=TRANSPOSE(A1:G6)”
Bảng gốc có 7 cột và 6 hàng, vì thế ta bôi đen 7 hàng 6 cột để tạo bảng mới. Sau đó ta nhấn tổ hợp phím Ctrl + Shift + Enter để nó biến thành một bảng mới.
Và đây là kết quả đạt được:
5.7. Hàm HYPERLINK
Cú pháp: =HYPERLINK(link_location;[friendly_name])
Chức năng: Tạo liên kết đến tài liệu trên mạng cục bộ hoặc Internet.
Trong đó:
- link_location: là liên kết đến trang web hoặc tài liệu đã mở sẵn
- friendly_name: là tên bạn đặt cho liên kết của mình phần này không bắt buộc
Ví dụ: bạn sẽ hiểu thêm về hàm HYPERLINK qua hình ảnh sau
- Hàm trong ô C2 là =HYPERLINK(B2;A2) là hàm tạo liên kết đến trang web “https://www.ablebits.com/”. Và tên liên kết đến giá trị trong ô A2 là: “Ablebits.com”.
- Hàm trong ô C3 là =HYPERLINK(B3;A3) là hàm tạo liên kết đến tài liệu “Sheet2!A1”- tức là giá trị của ô A1 trong sheet 2. Và tên liên kết được đặt là: “Sheet2”.
- Hàm trong ô C4 là =HYPERLINK(B4;A4) là hàm tạo liên kết đến tài liệu theo địa chỉ: “C:\User\sveta\Documents\Price list.docx”. Và tên liên kết là “Word doc”.
6. Hàm excel cơ bản chuyên trách về tài chính
Excel cung cấp rất nhiều hàm để thực hiện các công việc của người kế toán, nhà phân tích tài chính và nhân viên ngân hàng. Trong bài viết này Vietgiatrang sẽ thảo luận về một hàm tài chính có thể sử dụng để tính lãi kép.
6.1. Hàm FV
Cú pháp: =FV(rate, nper, pmt, [pv], [type])
Chức năng: dùng để giá trị của khoản đầu tư của bạn dựa trên tính lãi suất không đổi. Cú pháp của nó hơi khó một chút.
Trong đó:
- -) rate: là lãi suất mỗi kỳ.
- nper: số kỳ thanh toán.
- pmt: một khoản thanh toán bổ sung được thực hiện mỗi kỳ, được biểu diễn ở dạng số âm.
- [pv]: Giá tri hiện tại của khoản đầu tư (đầu tư nguyên tắc). Cũng là một số âm. Nếu bị bỏ qua thì tham số này mặc định bằng 0.
- [type]: chỉ hạn của các thanh toán bổ sung
- 0 hoặc bỏ trống: thì hạn thanh toán là cuối kỳ
- 1: thì hạn thanh toán là cuối kỳ.
Ví dụ: Tính lãi kép sau đây sẽ giúp bạn hiểu rõ hơn về hàm FV.
7. Hàm ngày tháng
Đối với những người thường xuyên sử dụng ngày tháng trong excel. Vietgiatrang sẽ cung cấp toàn diện về các hàm excel cơ bản, hàm ngày tháng ngay dưới đây.
7.1. Tạo ngày tháng
| Cấu trúc | Chức năng | Trong đó |
| =DATE(year;month;day) | Trả về một dãy số gồm ngày, tháng, năm mà bạn nhập vào | Có 3 biến số: year- năm, month- tháng, day- ngày. |
| =DATEVALUE(data_text) | Chuyển đổi ngày tháng năm ở định dạng văn bản sang số seri gồm: năm/ tháng/ ngày | data_text có thể là: 20- may- 2016. |
Ví dụ:
- Cho hàm DATE:
- Hàm DATEVALUE
7.2. Tạo ngày và giờ hiện tại
| Cấu trúc | Chức năng | Trong đó |
| =TODAY() | Trả về giá trị là ngày hôm nay | Không có biến |
| =NOW() | Trả về giá trị là ngày và giờ hiện tại | Không có biến |
Ví dụ:
7.3. Trích xuất ngày tháng và các thành phần ngày tháng
| Công thức | Chức năng | Trong đó |
| =DAY(seri_number) | Trả về ngày trong một tháng (từ 1 đến 31) theo seri | seri_number là một dãy số gồm: ngày, tháng, năm |
| =MONTH(seri_number) | Trả về tháng trong một năm (từ 1 đến 12) theo seri | seri_number là một dãy số gồm: ngày, tháng, năm |
| =YEAR(seri_number) | Trả về năm theo seri | seri_number là một dãy số gồm: ngày, tháng, năm |
| =EOMONTH(start_date;months) | Trả về ngày cuối cùng của một tháng | Start_date: ngày/ tháng/ năm bắt đầu. Months: số cộng vào tháng của ngày bắt đầu |
| =WEEKDAY(seri_number;[return_type]) | Trả về thứ trong một tuần theo số seri bạn nhập ở dạng 1 (CN),… 7 (thứ 7) | seri_number là một dãy số gồm: ngày, tháng, nămReturn_type: là một số được coi là ngày đầu tiên của tuần. |
Ví dụ:
7.4. Tính toán chênh lệch ngày
| Công thức | Chức năng | Trong đó |
| =DATEIF(start_date;end_date;unit) | Đánh giá sự chênh lệch của hai ngày theo ngày, tháng hoặc năm | -) start_date: là ngày bắt đầu-) end_date: ngày kết thúc-) unit: theo “ngày”, “tháng” hoặc “năm”. |
| =YEARFRAC(start_date;end_date;[basis] | Dùng để xác định khoảng cách giữa hai ngày chiếm bao nhiêu phần của một năm. | -) start_date: ngày bắt đầu-) end_date: ngày kết thúc-) basis: cơ sở xác định cách đếm ngày. |
Ví dụ:
7.5. Tính toán ngày công làm việc
+ Hàm WORKDAY
Cú pháp: =WORKDAY(start_date, days, [holidays])
Chức năng: Trả về ngày đầy đủ khi hoàn thành công việc. Nó sẽ được tính bao gồm ngày bắt đầu, số ngày công và ngày nghỉ (đầy đủ). Cuối tuần là thứ 7 chủ nhật được mặc định là cuối tuần trong hàm này.
Hàm excel cơ bản trả về ngày hoàn thành dự án này rất hữu ích cho việc tính toán các mốc và các sự kiện quan trọng khác dựa trên lịch làm việc tiêu chuẩn.
+ Hàm WORKDAY.INTL
Cú pháp: =WORKDAY.INTL(start_date, days,[weekend]; [holidays])
Chức năng: là một biến thể mạnh mẽ hơn của WORKDAY.INTL.
Trong đó:
- start_date: ngày bắt đầu
- days: Số ngày làm việc.
- weekend: Ngày cuối tuần (theo quy ước của Excel)- sự nâng cấp.
- holidays: ngày nghỉ ( bạn quy định)
Ví dụ:
+ Hàm NETWORKDAYS, NETWORKDAYS.INTL
Cú pháp:
- =NETWORKDAYS(start_date, end_date, [holidays])
- =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Chức năng: Ngược lại với WORKDAY, NETWORKDAY cũng như NETWORKDAY.INTL là tính tổng số ngày làm việc từ 2 ngày (đầy đủ) cho trước bao gồm cả ngày cuối nghỉ tuần và nghỉ lễ.
Điểm khác biệt của NETWORKDAY.INTL bạn có thể chọn ngày cuối tuần của mình, còn NETWORKDAY sẽ mặc định cuối tuần là thứ 7, chủ nhật.
Ví dụ cho hàm NETWORKDAY:
Đây là các định dạng của tham số weekend trong NETWORKDAY.INTL:
Ví dụ:
Sau cùng, Vietgiatrang đã cung cấp cho bạn những thông tin hữu ích về các hàm excel cơ bản. Hãy thao tác ngay và để lại bình luận bên dưới nếu bạn có bất kỳ thắc mắc nào nhé!
Xem thêm: Cách đổi số thành chữ trong excel 2016 64bit tại đây!
