Điện thoại: 0977170452 - 0789 706 683
Trong bài viết này, vitinhhuynhlong.com sẽ tổng hợp những hàm cơ bản trong Excel mà bạn không nên bỏ qua!
Hàng trăm, hàng nghìn dữ liệu với đầy ắp những con số sẽ không thể đếm theo cách thông thường được. Đừng lo lắng bởi đã có các hàm sau:
1.1. Hàm đếm COUNT
Chức năng: Thường được dùng trong các trường hợp cần đếm số ô trong 1 vùng dữ liệu hoặc toàn bộ dữ liệu trong file. COUNT sẽ trả kết quả và các giá trị số ( như số âm, phần trăm, số ngày tháng năm, phân thức…) còn các ký tự văn bản sẽ không được đếm.
Cú pháp: =COUNT(value1, [value2],…)
Trong đó:
Value 1, value 2,… là những giá trị trong vùng tuỳ chọn để đếm các ô giá trị
Bạn có thể đếm được 255 đối số trong 1 lần đến trong các phiên bản Excel 2007 – 2016
Ví dụ:
1.2. Hàm đếm có điều kiện COUNTIF
Chức năng: Thường được dùng để đếm số ô giá trị thoả mãn điều kiện cụ thể mà người dùng đặt ra.
Cú pháp: =COUNTIF(range; criteria)
Trong đó:
Range là vùng dữ liệu cần đếm
Criteria là điều kiện để đếm
Ví dụ ở bảng này, bạn muốn đếm số lần Cam bán ra, bạn nhập =COUNTIF(A2:A7,”CAM”)
1.3. Hàm đếm có nhiều điều kiện COUNTIFS
Chức năng: Dùng để tìm số lượng ô có giá trị thoả mãn từ 2 điều kiện trở lên.
Cú pháp: =COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2] …)
Trong đó:
criteria_range1: Vùng dữ liệu đầu tiên, bắt buộc cần điền
criteria1: Điều kiện của vùng criteria_range1.
[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung.
Hàm này cho phép bạn thêm tối đa 127 cặp điều kiện.
Lưu ý: Khi chọn vùng và điều kiện bổ sung cần phải có cùng số hàng, cột với vùng 1 trước đó, không nhất thiết phải nằm liền kề nhau.
Ví dụ: Bạn có danh hàng trái cây như bảng sau. Muốn xem thử còn bao nhiêu hàng tồn kho chưa bán được. Lúc này, điều kiện sẽ là sản phẩm còn phải lớn hơn 0 và không hết hàng. Bạn chỉ cần nhập cú pháp: =COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)
1.4. Hàm đến ô trống COUNTBLANK
Chức năng: Được dùng để đếm các ô trống không có dữ liệu trong vùng được chọn
Cú pháp: =COUNTBLANK(range)
Trong đó:
Range là vùng dữ liệu cần đếm
Ví dụ: Bạn muốn đếm số buổi có sinh viên vắng. Hãy nhập công thức: =COUNBLANK(C3:F7)
1.5. Hàm đếm ô không trống COUNTA
Chức năng: Dùng để các ô có chứa dữ liệu ( số, ký tự, biểu tượng..) trong vùng dữ liệu tuỳ chọn
Cú pháp: =COUNTA(range)
Trong đó:
Range là vùng dữ liệu cần đếm
Ví dụ: Muốn số buổi sinh viên có mặt, bạn chỉ cần nhập =COUNTA(C3:F7) như bảng sau
2.1. Hàm SUM
Chức năng: dùng để cộng các ô có giá trị số trong một vùng phạm vi. Công thức sẽ tự động cập nhật khi người dùng thay đổi hoặc chèn thêm giá trị. Hàm sẽ tự bỏ qua các ô trống và các ô chứa văn bản không phải số.
Cú pháp: =SUM(number1,[number2],…)
Trong đó:
Number1, number2 là các giá trị số được thêm vào.
Ví dụ: Ở bảng hàng sau, bạn muốn tính tổng số lượng hàng còn, nhập =SUM(B2:B7)
2.2. Hàm tính tổng có điều kiện SUMIF
Chức năng: dùng để tính tổng có điều kiện của các ô dữ liệu
Cú pháp: =SUMIF(range,criteria,[sum_range])
Trong đó:
Range: vùng chứa các ô dữ liệu cần tính tổng
Criteria: Điều kiện/tiêu chí
Sum_range: vùng dữ liệu cần tính tổng
Ví dụ: để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng từ bảng dưới đây. Nhập công thức: =SUMIF(B2:B7,"<50,000,000",D2:D7)
2.3. Hàm tính tổng nhiều điều kiện SUMIFS
Chức năng: dùng để tính tổng các ô dữ liệu theo nhiều điều kiện
Cú pháp: = SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Trong đó:
Sum_range: Là phạm vi ô cần tính tổng (số hoặc tên), đây là phần bắt buộc.
Criteria_range1: phạm vi cần đánh giá theo điều kiện
Criteria1: Điều kiện/tiêu chí
Criteria_range2, criteria2,…: các cặp phạm vi, điều kiện bổ sung
Ví dụ: Muốn tính số nhân viên bán hàng có lương trên 15 triệu.
Bạn nhập =SUMIFS(B2:B7,A2:A7,">15000000",F2:F7,"bán hàng")
2.4. Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
Chức năng: tính tổng từ các tích. Hay nói dễ hiểu hơn, hàm sẽ được sử dụng để tính tổng tiền các sản phẩm bằng cách nhân đơn giá x số lượng và cộng tất cả lại với nhau.
Cú pháp: =SUMPRODUCT(array1; array2; array3;...)
Trong đó:
Array1: là ô dữ liệu bắt buộc đầu tiên của thành phần mà bạn muốn nhân ( ví dụ đơn giá/ số lượng)
Array2, array3: những ô dữ liệu tiếp theo, lưu ý phải chọn cùng số hàng và cột để không lỗi #VALUE!
Ví dụ: Tính tổng tiền của các đơn hàng sau. Bạn nhập: =SUMPRODUCT(B2:B7,C2:C7)
Chức năng: cho ra con số trung bình của tổng các ô dữ liệu được chọn
Cú pháp: =AVERAGE(range)
Trong đó:
Range: là vùng dữ liệu cần tính giá trị trung bình
Ví dụ: Để tính điểm trung bình ba môn cho Nguyễn Văn B, ta nhập : =AVERAGE(B2:D2)
Chức năng: đúng như tên gọi, hàm này dùng để tìm ra giá trị lớn nhất, giá trị nhỏ nhất của 1 vùng dữ liệu
Cú pháp:
MIN(range)
MAX(range)
Trong đó:
Range: là vùng dữ liệu bạn cần tìm số nhỏ nhất (lớn nhất)
Ví dụ: Muốn tìm điểm cao nhất lớp, nhập =MAX(B2:D6)
5.1. Hàm điều kiện IF
Chức năng: dùng để kiểm tra xem ô dữ liệu có thoả điều kiện được đặt ra hay không, nếu đúng sẽ giả về giá trị đúng, sai sẽ trả giá trị sai.
Cú pháp: =IF(Logical_test;Value_if_true;Value_if_false)
Trong đó:
Logical_test: Điều kiện đặt ra cho dữ liệu
Value_if_true: giá trị trả về nếu thoả mãn điều kiện
Value_if_false: giá trị trả về nếu không thoả mãn điều kiện
Ví dụ: Muốn xem học sinh có đạt hay không dựa trên điều kiện điểm lớn hơn 8. Nhập =IF(B2>=8,"ĐẠT","KHÔNG ĐẠT")
5.2. Hàm AND
Chức năng: kiểm tra các ô dữ liệu bằng cách sẽ trả về giá trị đúng nếu tất cả các điều kiện đều được thoả mãn, nếu có bất kì điều kiện nào sai, kết quả sẽ trả về sai
Cú pháp: =AND(Điều kiện 1,Điều kiện 2,…)
Thường hàm AND được sử dụng kết hợp với hàm IF để xét nhiều mệnh đề điều kiện cùng lúc với cú pháp: =IF(AND(điều kiện 1,Điều kiện 2,…),“Giá trị 1”,“Giá trị 2”)
Ví dụ: Nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau:
5.3. Hàm OR
Chức năng: khác với hàm AND, hàm OR kiểm tra các ô dữ liệu nhưng sẽ trả về giá trị đúng nếu có bất kì điều kiện thoả mãn nhưng sẽ trả về giá trị sai nếu tất cả điều kiện đều không thoả mãn.
Cú pháp: =OR(logical 1, [logical 2], …)
Thường hàm OR cũng được kết hợp với hàm IF để kiểm tra các giá trị trong bảng dữ liệu
Ví dụ: Cũng tương tự như hàm IF, nếu bạn muốn tìm kết quả đạt hay không đạt dựa trên điều kiện điểm thực hành trên 7, lý thuyết trên 8. Cú pháp sẽ như sau:
6.1. Hàm VLOOKUP
Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang
Cú pháp: =VLOOKUP (Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
Lookup_value: là giá trị cần dò tìm
Table_array: bảng để dò tìm.
Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng, đếm từ trái qua phải.
Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.
Ví dụ: Để tìm định mức dựa vào bảng loại hàng, ta cần nhập cú pháp sau: =VLOOKUP(C7,$B$16:$D$20,2,0)
6.2. Hàm HLOOKUP
Chức năng: dò tìm dữ liệu ở cột ngoài cùng bên trái của bảng theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc
Cú pháp: =HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup)
Trong đó:
Lookup_value: Giá trị cần tìm
Table_array: Bảng để dò tìm. ( Sau khi kéo bảng, nhấn F4)
Row_index_num: Số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, đếm từ trên xuống dưới.
Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, thường mặc định là 1.
Ví dụ: Nếu bạn muốn tìm ngành học dựa trên mã số ngành và mã ưu tiên.
Ta nhập: =HLOOKUP(LEFT(D7,1),$B$13:$F$14,2,0)
6.3. Hàm INDEX
Chức năng: trả về giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi
Cú pháp: INDEX(array, row_num, [column_num])
Trong đó:
array: Phạm vi ô hoặc một hằng số mảng.
row_num: Thứ tự của hàng trong mảng chứa giá trị trả về
column_num: Thứ tự của cột trong mảng chứa giá trị trả về.
Ví dụ: Muốn tìm giá trị ở ô thứ 4 cột thứ 1 trong bảng sau, ta nhập cú pháp: =INDEX(B4:E13,4,1)
6.4. Hàm MATCH
Chức năng: tìm kiếm, xác định vị trí tương đối của một giá trị trong một vùng dữ liệu
Cú pháp: =Match(Lookup_value, Lookup_array, Match_type)
Trong đó:
Lookup_value: Giá trị cần tìm
Lookup_array: Vùng bạn muốn xác định vị trí của giá trị. Lưu ý phải chọn cùng cột hoặc hàng
Match_type: Xác định kiểu khớp
0: kết quả là vị trí tương đối của vùng dữ liệu chưa được sắp xếp
1: kết quả là vị trí của giá trị lớn nhất nhưng nhỏ hơn hoặc gần bằng giá trị cần tìm
-1: kết quả là vị trí của giá trị nhỏ nhất nhưng lớn hơn hoặc gần bằng giá trị cần tìm
Ví dụ: Để tìm vị trí của Lê Văn Kiên để tra dữ liệu, bạn nhập: =MATCH(F2;A1:A8;0)
7.1. Hàm YEAR, MONTH, DAY
Chức năng: Hàm về thời gian thường được dùng để xử lý các dữ liệu có liên quan đến thời hạn, ngày tháng. Cụ thể như:
Hàm Day: để xác định ngày trong 1 vùng dữ liệu ngày/tháng/năm
Hàm Month: xác định tháng trong 1 vùng dữ liệu ngày/tháng/ năm
Hàm Year: xác định năm trong 1 vùng dữ liệu ngày/tháng/ năm
Cú pháp: =DAY(serial_number).
Thực hiện tương tự ở các hàm Month và Year.
Trong đó:
Serial_number là ngày mà bạn muốn tìm
Ví dụ: Muốn tìm ngày, tháng trong bản dữ liệu sau, ta chỉ cần nhập:
7.2. Hàm DATE
Chức năng: tạo các ngày hợp lệ từ ô dữ liệu ngày tháng năm
Cú pháp: =DATE(year, month, day)
Ví dụ:
8.1. Hàm LEFT/RIGHT
Chức năng: cắt chuỗi kí tự từ bên trái/ bên phải văn bản ở ô được chọn
Cú pháp:
=LEFT(text,[num_chars])
=RIGHT(text,[num_chars])
Trong đó:
Text: là ô văn bản chứa kí tự muốn cắt
Num_chars: số lượng kí tự đếm từ trái/phải qua mà bạn muốn lấy
Ví dụ: Muốn tìm tên lớp của học sinh, biết rằng số lớp là số kí tự đầu của số báo danh, ta nhập như hình sau:
8.2. Hàm MID
Chức năng: cắt số kí tự từ ô văn bản được chọn, tính từ vị trí con trỏ chuột của bạn
Cú pháp: =MID(text, start_num, num_chars)
Trong đó:
Text: ô văn bản muốn chứa kí tự muốn cắt
start_num: vị trí của kí tự đầu tiên mà bạn muốn lấy
num_chars: số lượng kí tự bạn muốn lấy
Ví dụ: Muốn tìm họ của tên học sinh dưới đây, chỉ cần nhập:
8.3. Hàm LEN
Chức năng: đếm ký tự ở ô văn bản bao gồm cả khoảng trắng
Cú pháp: =LEN(ô chứa chuỗi ký tự)
Ví dụ: Muốn đếm ô của học sinh Phạm Minh Nhật, ta nhập:
8.4. Hàm CONCATENATE (CONCAT)
Chức năng: nối hai hoặc nhiều ô văn bản thành một
Cú pháp: =CONCATENATE(text1, text2,…)
Trong đó:
Text 1,text2: là ô chứa văn bản
Ví dụ: Để gộp họ và tên của học sinh, ta nhập:
8.5. Hàm SUBSTITUTE
Chức năng: thay thế văn bản trong ô văn bản được chọn
Cú pháp: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Trong đó:
Text: ô văn bản muốn thay thế các ký tự trong đó.
Old_text: ô chứa văn bản cũ bạn muốn được thay thế.
New_text: văn bản mới mà bạn muốn thay thế cho văn bản cũ.
Instance_num: vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới.
Ví dụ:
8.6. Hàm cắt khoảng trống TRIM
Chức năng: Cắt các ô khoảng trắng thừa khi dán văn bản khác vào bảng tính
Cú pháp: TRIM(text)
Trong đó:
Text là ô văn bản muốn loại bỏ các khoảng trắng
Ví dụ:
8.7. Hàm đổi chữ hoa, chữ thường LOWER, UPPER, PROPER
Chức năng: chuyển đổi các phần văn bản in thường thành chữ hoa và ngược lại.
Cú pháp:
=LOWER(text) để chuyển chữ hoa thành chữ thường
=UPPER(text) để chuyển chữ thường thành chữ hoa
=PROPER(text) để viết hoa các chữ cái đầu của văn bản
Ví dụ:
Excel là trợ thủ tính toán đắc lực vừa giúp tiết kiệm thời gian vừa cho ra kết quả chính xác. Nắm được các hàm cơ bản excel sẽ giúp bạn xử lý mọi công việc thêm nhanh chóng, hiệu quả hơn.
Trên đây là các hàm excel cơ bản mà chúng tôi đã tổng hợp. Hi vọng những thông tin này sẽ hữu ích với bạn!
Đánh giá
Dịch vụ soạn thảo văn bản, đánh máy vi tính.
Hướng dẫn thực hành Tin học, luyện thi chứng chỉ CNTT
Cài đặt, sửa chữa máy tính
Cập nhật, chia sẽ kiến thức công nghệ, đánh giá sản phẩm
Hướng dẫn khảo sát Online, kiếm tiền, thẻ cào điện thoại
***************************************************************
Điện thoại: 0977 170452 - 0789 706 683
Zalo: 0845 190 476
Email: pclongcom@gmail.com
Địa chỉ: Thạnh Đức - Gò Dầu - Tây Ninh