Các công thức cơ bản để sử dụng bảng tính trong Excel Lưu ý: Sau đây mình sẽ chỉ liệt kê những hàm mình thấy nó được sử dụng nhiều. Trước khi tới với công thức chúng ta cũng nên nhìn qua một số lỗi có thể gặp khi sử dụng các công thức cơ bản đó. I. Các lỗi cơ bản trong công thức: - #####: Không đủ độ rộng của ô để hiển thị. Khi này các bạn chỉ cần mở rộng độ ngang hoặc dọc của ô. - #VALUE! : Dữ liệu không đúng với yêu cầu của công thức. - #DIV/0! : Chia cho giá trị không. - #NAME? : Không xác định được kí tự trong công thức. - #NUM! : Dữ liệu không đúng kiểu số. II. Các nhóm hàm cơ bản: 1. Nhóm hàm cơ bản: - Hàm SUM: Tính tổng giá trị của các đối số kiểu số Cú pháp: =SUM (Number1, Number2) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. - Hàm AVERAGE: Tính tổng trung bình cộng của các đối số kiểu số. Cú pháp: =AVERAGE (Number1, Number2) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. - Hàm MIN: Trả về giá trị nhỏ nhất của các đối số kiểu số. Cú pháp: =MIN (Number1, Number2) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. - Hàm MAX: Trả về giá trị lớn nhất của các đối số kiểu số. Cú pháp: =MAX (Number1, Number2) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. - Hàm COUNT: Đếm có bao nhiêu kiểu số trong đối số. Cú pháp: =COUNT (Value1, [Value2] ) Value: Chứa các đối số gồm nhiều kiểu dữ liệu khác nhau, nhưng chỉ có kiểu số mới đếm được. - Round: Làm tròn số Cú pháp: =ROUND (number, num_digits) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. Num_digits: Là số nguyên chỉ vị trí làm tròn với: 1, 2.. >0: Làm tròn đến số thập phân được chỉ định. 0: Làm tròn đến số nguyên hàng đơn vị. 1, -2.. <0: Làm tròn đến phần nguyên. - Hàm INT: Hàm trả về số nguyên nhỏ hơn và gần đối số nhất. Cú pháp: = INT (Number) Number: Là các hằng số hoặc các ô có chứa giá trị hằng số, hoặc một vùng ô có giá trị hằng số. - Hàm MOD: Hàm trả về giá trị phần dư của một phép chia Cú pháp: = MOD (Number, Devisor) Number: Là số bị chia. Devisor: Là số chia - Hàm RANK: Hàm trả về thứ hạng của các đối số kiểu số trong một vùng ô. Cú pháp: = RANK (number, ref, [order]) Number: Là giá trị cần xếp hạng. Ref: Các giá trị tham chiếu hoặc danh sách các số so sánh. Order: Là 1 giá trị 0 (Sắp xếp giảm dần) hoặc 1 (Sắp xếp tăng dần) cho biết cách thức sắp xếp. - Hàm IF: Dùng để sử dụng khi có nhiều trường hợp "nếu" xảy ra trong một ô và sẽ trả về một kết quả cuối cùng nếu đúng hoặc sai với điều kiện trước đó đã đặt ra. Cú pháp: =IF (logical_test, value_if_true, value_if_false) Hàm trả về kết quả là value_if_true nếu logical_test có giá trị TRUE Hàm trả về kết quả là value_if_false nếu logical_test có giá tri FALSE Logical_test: Điều kiện để hàm xét true hay false và trả kết quả về. Value_if_true/ value_if_false: Có thể là đối số (chữ, số, ngày tháng) Hoặc hàm số hay một hàm if khác. 2. Nhóm hàm Logic: - Hàm OR: Hàm sẽ hiện TRUE nếu có ít nhất 1 đối số đúng và sẽ hiện FALSE nếu tất cả đối số đều sai. Cú pháp: =OR (logical 1, logical 2) Logical là các biểu thức điều kiện. - Hàm AND: Hàm sẽ hiện TRUE nếu tất cả đối số đều đúng và sẽ hiện FALSE nếu có ít nhất một đối số sai. Cú pháp: =AND (logical 1, logical 2) Logical là các biểu thức điều kiện 3. Nhóm hàm xử lý chuỗi: - Hàm LEN: Hàm trả về số ký tự trong một chuỗi văn bản. Cú pháp: =LEN (text) Text: Chuỗi văn bản - Hàm LEFT: Hàm trích xuất phần bên trái một chuỗi. Cú pháp: =LEFT (text, [num-chars]) Text: Chuỗi văn bản cần trích xuất ký tự. Num-chars: Số ký tự cần trích xuất. - Hàm RIGHT: Hàm trích xuất phần bên phải một chuỗi. Cú pháp: =RIGHT (text, [num-chars]) Text: Chuỗi văn bản cần trích xuất ký tự. Num-chars: Số ký tự cần trích xuất. - Hàm MID: Hàm trích xuất chuỗi con từ 1 chuỗi lớn với vị trí bất kì. Cú pháp: =MID (text, start-num, num-chars) Text: Chuỗi văn bản cần trích xuất ký tự. Start: Vị trí bắt đầu trích xuất từ bên trái. Num-chars: Số ký tự cần trích xuất. - Hàm FIND: Hàm trả về một vị trí của ký tự đầu tiên của chuỗi con đầu tiên Được tìm thấy nằm trong một chuỗi văn bản. Cú pháp: =FIND (find-text, within-text, [start-num]) Find-text: Chuỗi cần tìm. Within-text: Chuỗi văn bản chứa chuỗi cần tìm. Start-num: Vị trí bắt đầu tìm - Hàm VALUE: Hàm chuyển dữ liệu kiểu chuỗi text có dạng số thành dữ liệu Kiểu số. Cú pháp: =VALUE (text) Text: Chuỗi văn bản 4. Nhóm hàm ngày và giờ: - Hàm NOW: Hàm trả về ngày giờ theo hệ thống, hiển thị tùy theo kiểu định dạng đang sử dụng. Cú pháp: =NOW () - Hàm TODAY: Hàm trả về ngày theo hệ thống, hiển thị tùy theo kiểu định dạng đang sử dụng Cú pháp: =TODAY () - Hàm DAY: Hàm trả về chỉ số ngày trong biểu thức ngày tháng. Cú pháp: =DAY (Serial-number) Serial-number: Biểu thức ngày tháng - Hàm MONTH: Hàm trả về chỉ số tháng trong biểu thức ngày tháng. Cú pháp: =MONTH (Serial-number) Serial-number: Biểu thức ngày tháng - Hàm YEAR: Hàm trả về chỉ số năm trong biểu thức ngày tháng. Cú pháp: =YEAR (Serial-number) Serial-number: Biểu thức ngày tháng - Hàm DATE: Hàm trả về ngày tháng được nhập. Cú pháp: =DATE (year, month, day) Year: Năm. Month: Tháng. Day: Ngày. - Hàm HOUR: Hàm trả về giá trị là số giờ lẻ chưa tròn 1 ngày. Cú pháp: =HOUR (Serial-number) Serial-number: Là biểu thức thời gian hoặc 1 số dương nhỏ hơn 1 nào đó. - Hàm MINUTE: Hàm trả về giá trị là số phút lẻ chưa tròn 1 giờ. Cú pháp: =MINUTE (Serial-number) Serial-number: Là biểu thức thời gian hoặc 1 số dương nhỏ hơn 1 nào đó. - Hàm SECOND: Hàm trả về giá trị là số giây lẻ chưa tròn 1 phút. Cú pháp: =SECOND (Serial-number) Serial-number: Là biểu thức thời gian hoặc 1 số dương nhỏ hơn 1 nào đó. - Hàm TIMEVALUE: Hàm trả về giá trị số của chuỗi thời gian nhập vào. Cú pháp: =TIMEVALUE (Time-text) Time-text: Chuỗi thời gian 5. Nhóm hàm dò tìm: - Hàm HLOOKUP: Dò tìm theo hàng ngang, hàm trả về giá trị của ô nằm trên dòng nào đó thỏa mãn điều kiện dò tìm Cú pháp: =HLOOKUP (lookup_value, table_array, row_index_num, Option_lookup) Lookup_value: Giá trị dò tìm. Table_array: Là bảng chứa giá trị cần để dò tìm. Row_index: Số thứ tự cột từ trên xuống dưới trong bảng. Option_lookup: Xác định kiểu dò tìm là giá trị 0 hoặc FALSE (dò tìm tuyệt đối, lấy giá trị đầu tiên tìm được) ; 1 hoặc TRUE (kiểu dò tìm tương đối, nếu không tìm thấy trả về giá trị nhỏ hơn gần nhất với giá trị cần dò tìm) - Hàm VLOOKUP: Dò tìm theo hàng dọc, hàm trả về giá trị của ô nằm trên dòng nào đó thỏa mãn điều kiện dò tìm Cú pháp: =VLOOKUP (lookup_value, table_array, row_index_num, Option_lookup) Lookup_value: Giá trị dò tìm. Table_array: Là bảng chứa giá trị cần để dò tìm. Row_index: Số thứ tự cột từ trên xuống dưới trong bảng. Option_lookup: Xác định kiểu dò tìm là giá trị 0 hoặc FALSE (dò tìm tuyệt đối, lấy giá trị đầu tiên tìm được) ; 1 hoặc TRUE (kiểu dò tìm tương đối, nếu không tìm thấy trả về giá trị nhỏ hơn gần nhất với giá trị cần dò tìm) 6. Nhóm hàm thống kê có điều kiện: - Hàm SUMIF: Hàm trả về tổng của các ô trong một vùng thỏa mãn 1 hoặc Nhiều điều kiện cho trước. Cú pháp: = SUMIFS (sum-range, criteria-range1, criteria1[..] ) Sum-range: Vùng chứa các ô cần tính tổng. Criteria-range1: Vùng chứa các ô để so sánh với điều kiện 1. Criteria1: Điều kiện thứ 1 để tính tổng. - Hàm SUMIFS: Hàm trả về tổng số ô trong một vùng thỏa mãn 1 điều kiện cho trước. Cú pháp: =COUNTIF (range, criteria) Range: Vùng chứa các ô để so sánh với điều kiện. Criteria: Điều kiện để tính tổng. - Hàm COUNTIF: Hàm trả về tổng số ô trong một vùng thỏa mãn 1 điều kiện cho trước. Cú pháp: = COUNIF (range, criteria) Range: Vùng chứa các ô để so sánh với điều kiện. Criteria: Điều kiện để tính tổng. - Hàm COUNIFS: Hàm trả về tổng số ô trong một vùng thỏa mãn 1 nhóm điều kiện cho trước Cú pháp: = COUNTIFS (criteria-range1, criteria1[..] ) Criteria-range1: Vùng chứa các ô để so sánh với điều kiện 1. Criteria1: Điều kiện 1 để tính tổng. - Hàm AVERAGEIF: Hàm trả về tổng số ô trong một vùng thỏa mãn 1 điền kiện cho trước. Cú pháp: =AVERAGEIF (range, criteria, average-range) Range: Vùng chứa các ô để so sánh với điều kiện. Criteria: Điều kiện để tính tổng. Average-range: Vùng chứa các ô cần tính tổng - Hàm AVERAGEIFS: Hàm trả về trung bình cộng của các ô trong một vùng thỏa mãn 1 hoặc nhiều điều kiện cho trước. Cú pháp: = AVERAGEIFS (average-range, criteria-range1, criteria1[..] ) Average-range: Vùng chứa các ô cần tính tổng. Criteria-range1: Vùng chứa các ô để so sánh với điều kiện 1. Criteria1: Điều kiện thứ 1 để tính tổng
