Bài giảng Tin học đại cương - Chương 4: Phần mềm bảng tính điện tử MS Excel 2010 - Huỳnh Văn Kha

REF!Sai vùng tham chiếu Xóa những ô đang được tham chiếu bởi công thức. Dán những giá trị tạo ra từ công thức lên chính vùng tham chiếu đó.- Tham chiếu đến 1 ứng dụng không thể chạy được.#NUM!Lỗi dữ liệu kiểu số Dùng một đối số không phù hợp trong công thức. Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. Dùng hàm trả về 1số quá lớn/nhỏ so với khả năng tính toán của Excel#NULL!Lỗi dữ liệu rỗng Dùng một dãy toán tử không phù hợp Một số thông báo lỗi thường gặpNhóm hàm ngày (Date & Time)HÀMNOW()Cho biết ngày, giờ hiện thờiTODAY()Cho biết ngày hiện thờiDAY(serial_number)Cho biết ngày của serial_numberMONTH(serial_number)Cho biết tháng của serial_numberYEAR(serial_number)Cho biết năm của serial_numberChương 4 - PM bảng tính điện tử MS EXCEL2010908/13/2021Nhóm hàm toán học (Math & trig)HÀMVÍ DỤFACT(number)Tính giai thừa của number= FACT(4) Trả về kết quả 24INT(number)Lấy phần nguyên của number= INT(123.7) Trả về kết quả 123MOD(number,divisor)Phần dư của phép chia number cho divisor= MOD(23,5)Trả về kết quả 3SQRT(number)Căn bậc hai của number=SQRT(144)Trả về kết quả 12PRODUCT(number1, number2,...)Tích các số number1, number2,...=PRODUCT(9,8,7)Trả về kết quả 504SUM(number1, number2,...)Tổng các số number1, number2,...=SUM(15.8,35.6) Trả về kết quả 51.4ROUND(number, num_digits)Làm tròn số number với num_digits chữ số phần thập phân= ROUND(35.67, 1)Trả về kết quả 35.7LN(number)Tính ln=LN(1)Trả về kết quả 1Chương 4 - PM bảng tính điện tử MS EXCEL2010918/13/2021Nhóm hàm luận lý (Logical)HÀMVÍ DỤAND(logical1,logical2,..) Cho trị TRUE nếu mọi đối số là TRUE, ngược lại cho trị là FALSE=AND(3=0) Trả về kết quả TRUE=AND(3>7,2>=0) Trả về kết quả FALSEOR(logical1,logical2,..) Cho trị TRUE nếu bất kỳ đối số nào là TRUE, cho trị là FALSE nếu tất cả đối số là FALSE=OR(3>7, 2>=0)Trả về kết quả TRUENOT(logical) Cho trị logic ngược lại với trị logic của đối số=NOT(6=5, "Trung bình”, "Kém”)Trả về kết quả "Trung bình”.Chương 4 - PM bảng tính điện tử MS EXCEL2010928/13/2021Nhóm hàm xử lý chuỗi (Text)HÀMVÍ DỤLEFT(text, num_chars) Giữ lại num_chars ký tự bên trái chuỗi text= LEFT(“Tin học ứng dụng”, 7)Trả về kết quả “Tin học”RIGHT(text, num_chars)Giữ lại num_chars ký tự bên phải chuỗi text=RIGHT(“Tin học ứng dụng”,8)Trả về kết quả “ứng dụng”MID(text, start_num,num_chars) Trả về num_chars ký tự kể từ ký tự thứ start_num của chuỗi=MID(“Tin học ứng dụng”,4,3)Trả về kết quả “học”LEN(text)Độ dài chuỗi= LEN(“Tin học ứng dụng”)Trả về kết quả 16PROPER(text)Trả về chuỗi có chữ in hoa ở đầu mỗi từ=PROPER(“Tin học ứng dụng”)Trả về kết quả “Tin Học Ứng Dụng”UPPER(text) Đổi thành chuỗi in hoa=UPPER(“Tin học”)Trả về kết quả “TIN HỌC”LOWER(text)Đổi thành chuỗi in thườngLOWER(“HA NOI”) = “ha noi”VALUE(text) Đổi chuỗi các chữ số thành dãy các con sốVALUE(“0834”) = 834Chương 4 - PM bảng tính điện tử MS EXCEL2010938/13/2021Nhóm hàm thống kê (Statistical)HÀMÝ NGHĨAMIN(number1,[number2],)Trả về giá trị nhỏ nhất trong các đối sốMAX(number1,[number2],) Trả về giá trị lớn nhất trong các đối sốAVERAGE(number1,[number2],)Trả về giá trị trung bình của các đối sốCOUNT(value1,[value2],)Đếm số lượng các ô kiểu số trong miền ôCOUNTA(value1,[value2],)Đếm số lượng các ô khác rỗng có trong miền ôCOUNTBLANK(range)Đếm số lượng các ô trống trong miền ôCOUNTIF(range,criteria) Đếm số lượng các ô thoả điều kiện criteria đưa ra có trong miền ô rangeSUM(number1,[number2],) Tính tổng các giá trị số trong miền ôSUMIF(range,criteria,[sum_range]) Tính tổng các giá trị số trong sum_range thoả điều kiện criteria có trong miền ô chứa điều kiện rangeChương 4 - PM bảng tính điện tử MS EXCEL2010948/13/2021Nhóm hàm dò tìm và tham chiếu (Lookup and reference)HÀMÝ NGHĨAINDEX(Array,Row_num,Column_num)Cho nội dung ô tính trong miền ô dò tìm Array, ô tính này có chỉ số dòng là Row_num và chỉ số cột là Column_numMATCH(lookup_value,lookup_array, match_type)Cho vị trí tương đối của lookup_value trong dãy dò tìm lookup_array (dãy chỉ là dòng hay cột) tương ứng với kiểu tìm match_type. LOOKUP(lookup_value,lookup_vector, result_vector)Tìm kiếm và trả về giá trị có trong dãy result_vector ở vị trí tương ứng với lookup_value trong dãy dò tìm lookup_vector.HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Tìm giá trị lookup_value ở dòng đầu tiên trong miền ô dữ liệu table_array có chứa lookup_value. Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở dòng thứ row_index_num. Nếu không tìm thấy sẽ đưa ra thông báo #N/A (Not Available). Range_lookup là tham số tuỳ chọn.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Tìm giá trị lookup_value ở cột đầu tiên trong miền ô dữ liệu table_array có chứa lookup_value. Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở cột thứ col_index_num. Nếu không tìm thấy sẽ đưa ra thông báo #N/A (Not Available). Range_lookup là tham số tuỳ chọn.Chương 4 - PM bảng tính điện tử MS EXCEL2010958/13/2021Nhóm hàm cơ sở dữ liệu (Database)TÊN_HÀM_CSDL (database,field,criteria)Database: Vùng CSDL là toàn bộ hoặc một phần CSDL ban đầu.Field: Tên trường (thông qua địa chỉ ô chứa tên trường) hoặc tương ứng là cột thuộc tính thứ n trong vùng CSDL đã chọn.Criteria: Vùng tiêu chuẩn là miền ô chứa tên trường và biểu thức chứa điều kiện của các bản ghi chịu tác động của hàm.Chương 4 - PM bảng tính điện tử MS EXCEL2010988/13/2021Một số hàm CSDLTên hàmÝ nghĩaDAVERAGE(database,field,criteria)Tính trung bình các giá trị thuộc tên trường/cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn. DMAX/DMIN(database,field,criteria)Lấy ra giá trị lớn nhất/nhỏ nhất thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DGET(database,field,criteria)Lấy ra giá trị thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DCOUNT(database,field,criteria)Đếm số lượng các giá trị kiểu số thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DCOUNTA(database,field,criteria)Đếm số lượng ô không trống thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DSUM(database,field,criteria)Tính tổng cộng các giá trị thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.Chương 4 - PM bảng tính điện tử MS EXCEL2010998/13/2021Ví dụChương 4 - PM bảng tính điện tử MS EXCEL20101008/13/2021Phân nhóm dữ liệu (SUBTOTAL)Bước 1: tiến hành sắp xếp dữ liệu (tăng dần hoặc giảm dần) trên trường cần phân nhóm dữ liệu. Bước 2: lựa chọn vùng CSDL.Bước 3: Data – Subtotal. Chương 4 - PM bảng tính điện tử MS EXCEL20101018/13/2021Ví dụTính tổng thu nhập theo từng nhóm giới tínhChương 4 - PM bảng tính điện tử MS EXCEL20101028/13/2021Liên kết dữ liệu (tính năng GROUP and OUTLINE)Tình huống 1: Người dùng chủ động thực hiện liên kết các dòng/cột liền kề vào thành từng nhómBước 1: (chỉ thực hiện khi muốn liên kết các dòng dữ liệu vào từng nhóm): sắp xếp dữ liệu trên trường sẽ phân nhóm dữ liệu. Bước 2: chọn các dòng/ cột dữ liệu cần liên kết trong một nhóm. Bước 3: chọn Data – Group – Group Chương 4 - PM bảng tính điện tử MS EXCEL20101038/13/2021Ví dụChương 4 - PM bảng tính điện tử MS EXCEL20101048/13/2021Liên kết dữ liệu (tính năng GROUP and OUTLINE)Tình huống 2: Excel sẽ tự động thực hiện liên kết cột khi phát hiện thấy dữ liệu trên các cột liền kề có liên quan lẫn nhau qua công thức/hàmBước 1: Đưa chuột đến ô bất kì trong vùng dữ liệu.Bước 2: Nhấn chọn Data - Group - Auto OutLine => sẽ tự động xuất hiện các liên kết dòng/cột theo các cấp có thể được. Chú ý: nếu trong CSDL không có mối liên kết giữa các cột liền kề thì Excel sẽ từ chối thực hiện tính năng Auto OutLine.Chương 4 - PM bảng tính điện tử MS EXCEL20101058/13/2021Bảng tham chiếu chéo - Pivot TableLựa chọn CSDLInsert - PivotTable/ PivotChart Chương 4 - PM bảng tính điện tử MS EXCEL20101068/13/2021Cấu trúc bảng PivotTableChương 4 - PM bảng tính điện tử MS EXCEL20101078/13/2021Mức tổng hợp dữ liệu cao nhất Thống kê/tính toán trên dữ liệu Phân tích dữ liệu Report FilterRowLabelsColumn Labels∑ ValuesMức tổng hợp dữ liệu cao thứ nhì Ví dụChương 4 - PM bảng tính điện tử MS EXCEL20101088/13/2021Với mỗi Tên đơn vị vay, cho biết Ngày vay và Thời hạn vay tương ứng cùng với Tổng số tiền phải trả của đơn vị vay đóVí dụChương 4 - PM bảng tính điện tử MS EXCEL20101098/13/2021Biểu diễn dữ liệu bằng biểu đồChương 4 - PM bảng tính điện tử MS EXCEL20101108/13/2021Phân tích xu hướng dữ liệu SparklineTrendlineChương 4 - PM bảng tính điện tử MS EXCEL20101118/13/2021