Bài giảng Tin học kế toán - Trường CĐ Công nghệ và Nông Lâm Nam Bộ

Chia sẻ: _ _ | Ngày: | Loại File: PDF | Số trang:162

Thêm vào BST

Báo xấu

48
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng Tin học kế toán được kết cấu thành 7 chương, cụ thể như sau: Bài 1: Các hàm excel thông dụng; Bài 2: Lập cơ sở dữ liệu; Bài 3: Sử dụng excel giải quyết bài toán về tính hiệu quả vốn đầu tư; Bài 4: Phân tích và dự báo kinh tế trong excel; Bài 5: Ứng dụng các hình thức kế toán trên excel. Mời các bạn cùng tham khảo!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Tin học kế toán - Trường CĐ Công nghệ và Nông Lâm Nam Bộ

BỘ NÔNG NGHIỆP VÀ PHÁT TRIỂN NÔNG THÔN TRƢỜNG CAO ĐẲNG CÔNG NGHỆ VÀ NÔNG LÂM NAM BỘ -------  ------- BÀI GIẢNG TIN HỌC KẾ TOÁN Mã số: MĐ33 NGHỀ: CÔNG NGHỆ THÔNG TIN KHOA CÔNG NGHỆ THÔNG TIN Địa chỉ: QL 1K, Phƣờng Bình An, TX. Dĩ An, Tỉnh Bình Dƣơng Email: it.svoctaf@gmail.com/ cn.cnnlnb@gmail.com. [Lƣu hành nội bộ] -2018-
GIỚI THIỆU. Chƣơng trình “Tin học kế toán trên excel” cung cấp giải pháp kỹ thuật ứng dụng microsoft excel trong công tác kế toán để liên kết các bảng tính theo mẫu sổ sách kế toán của Bộ Tài chính. Sau khi tốt nghiệp học viên có thể sử dụng thành thạo các công cụ excel phục vụ cho công tác kế toán tại các hộ kinh doanh gia đình, các doanh nghiệp nhỏ mà không cần sử dụng các chƣơng trình phần mềm phức tạp. Nhờ ứng dụng chƣơng trình này các kế toán viên giảm bớt đƣợc các khâu tính toán cộng, trừ, nhân, chia và bảo đảm tính chính xác của các số liệu. Vì vậy, tính ứng dụng của chƣơng trình này rất cao bởi nó đáp ứng và phù hợp với nhu cầu của hàng chục vạn doanh nghiệp nhỏ và hộ gia đình đang kinh doanh trên khắp mọi miền đất nƣớc. Tài liệu Tin học kế toán trên excel đƣợc biên soạn nhằm hỗ trợ cho việc giảng dạy và học tập, nghiên cứu cho các Học sinh – Sinh viên (HS-SV) ngành Công nghệ thông tin, trang bị cho HS-SV những kiến thức, hệ thống lý luận cần thiết, tổng quan về giải pháp ứng dụng microsoft excel trong công tác kế toán. Nội dung chƣơng trình đƣợc kết cấu thành 7 chƣơng, cụ thể nhƣ sau: - Bài 1: Các hàm excel thông dụng. - Bài 2: Lập cơ sở dữ liệu. - Bài 3: Sử dụng excel giải quyết bài toán về tính hiệu quả vốn đầu tƣ. - Bài 4: Phân tích và dự báo kinh tế trong excel. - Bài 5: Ứng dụng các hình thức kế toán trên excel. Tài liệu đƣợc biên soạn có tham khảo từ các tài liệu, bài giảng và kinh nghiệm giảng dạy của tập thể giáo viên, nên không thể tránh khỏi các thiếu soát rất mong nhận đƣợc ý kiến góp ý để tài liệu hoàn thiện hơn. Mọi ý kiến đóng góp xin gửi về Khoa Công nghệ thông tin, Trƣờng Cao đẳng Công nghệ và Nông Lâm Nam Bộ. Điện thoại: 0274 3772 899; Email: cn.cnnlnb@gmail.com. Chân thành cảm ơn ! Bình Dương, ngày 01 tháng 01 năm 2018 Nhóm biên soạn
2
MỤC LỤC GIỚI THIỆU. .............................................................................................................................. 1 MỤC LỤC ................................................................................................................................... i Bài 1. MỘT SỐ HÀM TRONG EXCEL.................................................................................... 2 1.1. CÚ PHÁP CHUNG VÀ CÁCH SỬ DỤNG. .................................................................. 2 1.1.1. Cú pháp. .................................................................................................................. 2 1.1.2. Cách sử dụng. .......................................................................................................... 2 1.2. CÁC HÀM THÔNG DỤNG ........................................................................................... 4 1.2.1. Các hàm toán học (Math). ..................................................................................... 4 1.2.2. Các hàm thống kê (Statistical). ............................................................................. 4 1.2.3. Các hàm Logic. ...................................................................................................... 5 1.2.4. Các hàm xử lý chuỗi (Text).................................................................................... 6 1.2.5. Các hàm ngày và giờ (Date &Time). .................................................................... 7 1.2.6. Các hàm tìm kiếm (Lookup & Reference) ........................................................... 8 Bài 2. THAO TÁC TRÊN CƠ SỞ DỮ LIỆU .......................................................................... 11 2.1. KHÁI NIỆM VỀ CƠ SỞ DỮ LIỆU. ............................................................................ 11 2.2. CÁC HÀM CƠ SỞ DỮ LIỆU ....................................................................................... 11 Bài 3. SỬ DỤNG EXCEL GIẢI QUYẾT BÀI TOÁN VỀ TÍNH HIỆU QUẢ VỐN ĐẦU TƢ .................................................................................................................................................. 22 3.1. CÁC HÀM TÍNH KHẤU HAO TÀI SẢN CỐ ĐỊNH. ................................................. 22 3.1.1. Một số khái niệm liên quan. ................................................................................. 22 3.1.2. Các phƣơng pháp tính khấu hao TSCĐ và các hàm Excel tƣơng ứng. ............. 23 3. 2. CÁC HÀM TÍNH HIỆU QUẢ VỐN ĐẦU TƢ. .......................................................... 30 3.2.1. Một số kiến thức liên quan................................................................................... 30 3.2.2. Các hàm tính hiệu quả vốn đầu từ trong Excel. ................................................ 31 Bài 4. PHÂN TÍCH VÀ DỰ BÁO KINH TẾ TRONG EXCEL .............................................. 41 4.1. GIỚI THIỆU CHUNG................................................................................................... 41 4.2. TÍNH TOÁN VỚI CÁC CHỈ TIÊU THỐNG KÊ MÔ TẢ. .......................................... 44 4.2.1. Giới thiệu ............................................................................................................... 44 4.2.2. Một số hàm Excel có chức năng tính các chỉ tiêu thống kê đơn lẻ. ................. 48 4.3. BẢNG TẦN SUẤT VÀ BẢNG XẾP HẠNG. .............................................................. 50 4.3.1. Lập bảng tần suất. ................................................................................................ 50 4.4. PHÂN TÍCH KINH TẾ BẰNG PHƢƠNG TRÌNH TƢƠNG QUAN. ......................... 55 4.4.1. Xác đinh hệ số tƣơng quan giữa các yếu tố kinh tế. ........................................... 55 4.4.2. Xây dựng phƣơng trình tƣơng quan giữa các yếu tố kinh tế. ............................ 57 4.5. DỰ BÁO KINH TẾ TRONG EXCEL. ......................................................................... 62 4.5.1. Giới thiệu. .............................................................................................................. 62
4.5.2. Dự báo kinh tế sử dụng phƣơng trình đƣờng xu thế .......................................... 62 4.5.3. Dự báo sử dụng hàm FORECAST. .................................................................... 65 4.5.4. Dự báo sử dụng phƣơng trình tƣơng quan. ........................................................ 65 Bài 5. ỨNG DỤNG CÁC HÌNH THỨC KẾ TOÁN TRÊN EXCEL...................................... 69 5.1. THIẾT LẬP THÔNG TIN DOANH NGHIỆP VÀ SỐ DƢ ĐẦU KỲ CÁC TÀI KHOẢN. .............................................................................................................................. 69 5.1.1. Thiết lập thông tin ban đầu và xây dựng hệ thống danh mục....................... 69 5.2. CẬP NHẬT NGHIỆP VỤ KINH TẾ PHÁT SINH TRONG KỲ. .............................. 96 5.2.1. Trình tự cập nhật các nghiệp vụ kinh tế phát sinh. ..................................... 96 5.2.2. Tìm hiểu bản chất của công thức. ................................................................... 98 5.2.3. Nghiệp vụ thu, chi tiền. ..................................................................................... 100 5.2.4. Nghiệp vụ mua hàng/ bán hàng. ..................................................................... 103 5.2.5. Nghiệp vụ nhập kho hàng hóa (làm tƣơng tự với nhập kho vật tƣ). ........ 107 5.2.6. Lập phiếu nhập xuất kho. ............................................................................... 113 5.3. LÀM CÁC BÚT TOÁN CUỐI KỲ. ........................................................................... 125 5.3.1. Tính và trích khấu hao tài sản cố định. ........................................................... 125 5.3.2. Phân bổ chi phí trả trƣớc. ................................................................................. 126 5.3.3. Xác định giá vốn xuất trong kỳ. ...................................................................... 127 5.3.4. Xác định kết quả kinh doanh, lãi/lỗ. ................................................................ 129 5.4. LÀM BÁO TÀI CHÍNH VÀ IN SỔ KẾ TOÁN........................................................ 132 5.4.1. Cập nhập bảng cân đối tài khoản. .................................................................... 132 5.4.2. Thuyết minh báo cáo tài chính. ........................................................................ 145 BÀI TẬP ................................................................................................................................ 149 ii
NỘI DUNG TỔNG QUÁT VÀ PHÂN PHỐI THỜI GIAN Thời gian (h) Số Tên Bài trong mô đun Lý Thực Kiểm TT Tổng số thuyết hành tra 1 Các hàm excel thông dụng 12 3 8 1 2 Tổ chức dữ liệu kế toán trong điều kiện ứng dụng 8 2 6 excel 3 Sử dụng excel giải quyết bài toán về tính hiệu quả 8 2 6 vốn đầu tƣ 4 Phân tích và dự báo kinh tế trong excel 16 4 11 1 5 Ứng dụng các hình thức kế toán trên excel 16 4 10 2 Cộng 60 15 41 4 DỤNG CỤ - THIẾT BỊ - VẬT LIỆU. 1
Bài 1. CÁC HÀM EXCEL THÔNG DỤNG MỤC TIÊU. - Trình bày đƣợc cú pháp chung, cách sử dụng các hàm excel. - Vận dụng đƣợc các kiến thức trên vào các bài tập. - Rèn luyện ý thức lao động, tác phong công nghiệp, có trách nhiệm và sáng tạo. DỤNG CỤ - THIẾT BỊ - VẬT LIỆU. NỘI DUNG. 1.1. CÚ PHÁP CHUNG VÀ CÁCH SỬ DỤNG. Hàm dùng để tính toán và trả về một giá trị, trong ô chứa hàm sẽ trả về một giá trị, một chuỗi ký tự hoặc một thông báo lỗi, … Excel có một tập hợp các hàm rất phong phú và đƣợc phân loại theo từng nhóm phục vụ cho việc tính toán trên nhiều kiểu dữ liệu và nhiều mục đích khác nhau. 1.1.1. Cú pháp. = TÊN HÀM ([Danh sách đối số]) Đa số các hàm của Excel đều có đối số nhƣng cũng có những hàm không có đối số. Nếu hàm có nhiều đối số thì giữa các đối số phải đƣợc phân cách bằng ký hiệu phân cách, các ký hiệu phân cách đƣợc quy định trong Control Panel… với mặc định là dấu phẩy. 1.1.2. Cách sử dụng. Nếu công thức bắt đầu là một hàm thì phải có dấu = hoặc dấu + ở phía trƣớc. Nếu hàm là đối số của một hàm khác thì không cần nhập các dấu trên. Có 2 cách nhập hàm: - Cách 1: Nhập trực tiếp từ bàn phím + Đặt trỏ chuột tại ô muốn nhập hàm. + Nhập dấu = (hoặc dấu +). + Nhập tên hàm cùng các đối số theo đúng cú pháp. + Nhấn Enter để kết thúc. - Cách 2: Thông qua hộp thoại Insert Function: + Đặt trỏ tại ô muốn nhập hàm. 2
+ Click chọn Insert Function hoặc Hình 1.1: Hộp thoại Insert Function. + Chọn Group hàm trong danh sách Function category. + Chọn hàm cần sử dụng trong danh sách Function name. + Click OK để chọn hàm. + Tùy theo hàm đƣợc chọn, Excel sẽ mở hộp thoại kế tiếp cho phép nhập các đối số (nhập hoặc quét chọn). Tiến hành nhập các đối số. Ví dụ danh sách các đối số cần nhập của hàm IF Hình 1.2: Hộp thoại Function Arguments. (*) Chú ý về an toàn. - Tuân thủ nội quy phòng máy. - Dữ liệu lƣu trữ trên máy tính gọn gàng, khoa học TIÊU CHÍ ĐÁNH GIÁ. TT Nội dung Điểm chuẩn Điểm đánh giá I Điểm thao tác 10 1 Cú pháp chung. 4 2 Cách sử dụng. 6 II Điểm cộng sáng tạo 0.5 Sáng tạo trong các bài tập ứng dụng. 0.5 III Điểm cộng hoàn thành trƣớc thời 0.5 gian qui định 3
1 Hoàn thành đúng thời gian qui định. 0 2 Hoàn thành trƣớc thời gian qui định 0.25 từ 10-15 phút. 3 Hoàn thành trƣớc thời gian qui định 0.5 từ 16 phút trở lên. Tổng điểm 10 1.2. CÁC HÀM THÔNG DỤNG. 1.2.1. Các hàm toán học (Math). Các hàm thông dụng toán học đƣợc thống kê theo bảng sau: 1.2.2. Các hàm thống kê (Statistical). Các hàm thống kê thông dụng đƣợc trình bày bên dƣới: CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ MAX(number1, number2, Trả về giá trị lớn nhất của các giá trị số trong danh ...) sách tham số =MAX(1, 2, 3, 5) = 5 MIN(number1, number2, ...) Trả về giá trị nhỏ nhất của các giá trị số trong danh sách tham số. =MIN(1, 2, 3, 5) = 1 AVERAGE(number1, Trả về giá trị trung bình cộng của các số trong danh umber2, ...) sách tham số. =AVERAGE(1, 2, 3, 5) -> 2.75 COUNT(value1, value2, ...) Đếm số các giá trị số trong danh sách tham số. =COUNT(2, “hai”, 4, -6) = 3 COUNTA(value1, value2, Đếm số các ô không rỗng trong danh sách tham số. ...) =COUNTA(2, “hai”, 4, -6) -> 4 COUNTBLANK(range) Đếm số các ô rỗng trong vùng range. =COUNTBLANK(B4:B12) COUNTIF(range, criteria) Đếm các ô thỏa mãn điều kiện criteria trong vùng range. range: là vùng mà điều kiện sẽ đƣợc so sánh. criteria: là chuỗi mô tả điều kiện. Ví dụ: "10", ">15", "
1.2.3. Các hàm Logic. Các hàm logic thông dụng đƣợc trình bày bên dƣới: CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ AND(logical1, logical2, …) Trả về giá trị TRUE nếu tất cả các điều kiện đều là TRUE. =AND(3>2, 5-12) –TRUE OR(logical1, logical2, …) Trả về giá trị FALSE nếu tất cả điều kiện là FALSE. =OR(2>3, 123) -TRUE =OR(2>3, 123) -FALSE NOT(logical) Lấy phủ định của giá trị logical. =NOT(2>3) -TRUE IF(logical_test,alue_if_true Trả về giá trị value_if_true nếu điều kiện , value_if_false) logical_test là TRUE, ngƣợc lại sẽ trả về giá trị value_if_false. =IF(A1 >=5, “Trƣợt”,”Đỗ”) ABS(number) Trả về giá trị tuyệt đối của một số thực. =ABS(12 - 20) 8 Trả về số nguyên lớn nhất không vƣợt quá number. INT(number) =INT(5.6)  5 =INT(-5.6)  -6 Trả về số dƣ của phép chia nguyên number cho divisor (number, divisor là các số nguyên). MOD(number, divisor) =MOD(5, 3)  2 Làm tròn lên tới một số nguyên lẻ gần nhất. =ODD(3.6)  5 ODD(number) =ODD(-2.2)  -3 PRODUCT(number1, Tính tích của các giá trị trong danh sách tham number2, ...) số. =PRODUCT(2, -6, 3, 4) =-144 5
CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ Làm tròn số number với độ chính xác đến num_digits chữ số thập ROUND(number, num_digits) phân (với qui cƣớc 0 là làm tròn tới hàng đơn vị, 1 là lấy 1 chữ số thập phân, -1 là làm tròn tới hàng chục, ...). =ROUND(5.13687, 2)= 5.14 =ROUND(145.13687, -2) = 100 SQRT(number) Tính căn bậc 2 của một số dƣơng number. =SQRT(36) =6 SUM(number1, number2,...) Tính tổng của các giá trị trong danh sách tham số. =SUM(2, -6, 8, 4)= 8 Tính tổng các ô thỏa mãn điều kiện. range: vùng mà điều kiện sẽ đƣợc so sánh. SUMIF(range, criteria [, criteria: chuỗi mô tả điều kiện. sum_range]) Ví dụ: "10", ">15", "
CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ UPPER(text) Chuyển chuỗi text thành chữ in hoa. = UPPER(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”) = CAO ĐẲNG CÔNG NGHỆ NÔNG LÂM NAM BỘ PROPER(text) Đổi các ký tự đầu của mỗi từ trong chuỗi text thành chữ in hoa, còn lại đều là chữ thƣờng. =PROPER(“Cao đẳng công nghệ nông lâm nam bộ”) TRIM(text) Cắt bỏĐẳng - Cao các ký tự trống Công NghệvôNông ích trong Lâm chuỗi text. Nam Bộ =TRIM(“Cao đẳng công nghệ nông lâm nam bộ”) - Cao đẳng công nghệ nông lâm nam bộ LEN(text) Trả về độ dài của chuỗi text. =LEN(“Cao đẳng công nghệ nông lâm nam bộ”) = 34 LEFT(text, num_chars) Trả về num_char ký tự bên trái chuỗi text. =LEFT(“Cao đẳng công nghệ nông lâm nam bộ”, 8) = Cao đẳng RIGHT(text, Trả về num_char ký tự bên phải chuỗi text. num_chars) =RIGHT(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”,15) MID(text, start_num, = Nông Trả Lâm Nam về chuỗi ký tựBộ có độ dài num_chars bắt đầu từ vị trí num_chars) start_num của chuỗi text. =MID(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”, 10, 9) VALUE(text) Chuyển chuỗi có dạng số thành trị số. = Công Nghệ = VALUE("123") + 2 -125 1.2.5. Các hàm ngày và giờ (Date &Time). Các hàm ngày giờ thông dụng đƣợc trình bày bên dƣới: CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ TODAY( ) Trả về ngày hiện hành của hệ thống. =TODAY( ) NOW( ) Trả về ngày và giờ hiện hành của hệ thống. =NOW( ) DAY(date) Trả về giá trị ngày trong tháng của biểu thức ngày date. =DAY(A1)-14 MONTH(date) Trả về giá trị tháng trong năm của biểu thức ngày date. =MONTH(A1)- 8 7
YEAR(date) Trả về giá trị năm của biểu thức ngày date. =YEAR(A1)-2010 WEEKDAY(date) Trả về số thứ tự ngày trong tuần của biểu thức date. Giá trị 1: Sunday, 2:Monday, ..., 7: Saturday. =WEEKDAY(A1)-3 DATE(year, month, day) Trả về giá trị dạng Date theo quy định của hệ thống. =DATE(2010,08,14) -14/08/2010 =DATE(10,8,14) -14/08/2010 TIME(hour, minute, Trả về giá trị dạng Time. second) =TIME(8,25,28) -8:25:28 AM =TIME(17,2,46) -5:2:46 PM 1.2.6. Các hàm tìm kiếm (Lookup & Reference) 1.2.6.1. Hàm VLOOKUP. Chức năng: Tìm giá trị lookup_value trong cột trái nhất của bảng table_array theo chuẩn dò tìm range_lookup, trả về trị tƣơng ứng trong cột thứ col_index_num (nếu tìm thấy). Cú pháp:=VLOOKUP(lookup_value, Table_array, col_index_num, range_ lookup) + range_lookup = 1: Tìm tƣơng đối, danh sách các giá trị dò tìm của bảng Table_array phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả về giá trị lớn nhất nhƣng nhỏ hơn lookup_value. + range_lookup = 0: Tìm chính xác, danh sách các giá trị dò tìm của bảng Table_array không cần sắp xếp thứ tự. Nếu tìm không thấy sẽ trả về lỗi #N/A. Ví dụ: Hình 1.3: Ví dụ sử dụng hàm Vlookup. 8
1.2.6.2. Hàm HLOOKUP. Chức năng: Tìm giá trị lookup_value trong dòng trên cùng của bảng table_array theo chuẩn dò tìm range_lookup, trả về trị tƣơng ứng trong dòng thứ row_index_num (nếu tìm thấy). Cú pháp: = HLOOKUP(lookup_value, Table_array, row_index_num, range_lookup) Ý nghĩa của các đối số của hàm Hlookup tƣơng tự nhƣ hàm Vlookup. Ví dụ: Hình 1.4: Ví dụ sử dụng hàm Hlookup. 1.2.6.3. Hàm MATCH. Chức năng: Hàm trả về vị trí của lookup_value trong mảng lookup_array theo cách tìm match_type Cú pháp: = MATCH(lookup_value, lookup_array, match_type) + Match_type = 1: Tìm tƣơng đối, danh sách các giá trị dò tìm của bảng Table_array phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả về vị trí của giá trị lớn nhất nhƣng nhỏ hơn lookup_value. + Match_type = 0: Tìm chính xác, danh sách các giá trị dò tìm của bảng Table_array không cần sắp xếp thứ tự. Nếu tìm không thấy sẽ trả về lỗi #N/A. + Match_type = -1: Tìm tƣơng đối, danh sách phải sắp xếp các giá trị dò tìm của bảng Table_array theo thứ tự giảm dần. Nếu tìm không thấy sẽ trả về vị trí của giá trị nhỏ nhất nhƣng lớn hơn lookup_value. Ví dụ: Hình 1.5: Ví dụ sử dụng hàm Match. 9
1.2.6.4. Hàm INDEX Chức năng: Trả về giá trị trong ô ở hàng thứ row_num, cột thứ column_num trong mảng array. Cú pháp: = INDEX(array, row_num, column_num) Ví dụ: Hình 1.6: Ví dụ sử dụng hàm Index. (*) Chú ý về an toàn. - Tuân thủ nội quy phòng máy. - Dữ liệu lƣu trữ trên máy tính gọn gàng, khoa học TIÊU CHÍ ĐÁNH GIÁ. Điểm Điểm TT Nội dung chuẩn đánh giá I Điểm thao tác 10 1 Các hàm toán học. 2 2 Các hàm thống kê. 2 3 Các hàm logic. 1 4 Các hàm xử lý chuỗi. 2 5 Các hàm ngày và giờ. 1 6 Các hàm tìm kiếm. 2 II Điểm cộng sáng tạo 0.5 1 Sáng tạo trong các bài tập ứng dụng. 0.5 III Điểm cộng hoàn thành trƣớc thời gian qui định 0.5 1 Hoàn thành đúng thời gian qui định. 0 2 Hoàn thành trƣớc thời gian qui định từ 10-15 phút. 0.25 3 Hoàn thành trƣớc thời gian qui định từ 16 phút trở lên. 0.5 Tổng điểm 10 10
Bài 2. TỔ CHỨC DỮ LIỆU KẾ TOÁN TRONG ĐIỀU KIỆN ỨNG DỤNG EXCEL MỤC TIÊU. - Trình bày đƣợc các hàm cơ sở dữ liệu, các lệnh xử lý dữ liệu, subtotals, Pivot Table, consolidate. - Vận dụng đƣợc các kiến thức trên vào các bài tập. - Rèn luyện ý thức lao động, tác phong công nghiệp, có trách nhiệm và sáng tạo. DỤNG CỤ - THIẾT BỊ - VẬT LIỆU. NỘI DUNG. 2.1. KHÁI NIỆM VỀ CƠ SỞ DỮ LIỆU. Khi quản lý thông tin về một đối tƣợng, ta phải quản lý các thuộc tính liên quan đến đối tƣợng đó. Ví dụ, quản lý nhân viên thì cần quản lý thông tin của nhân viên nhƣ họ tên, mã nhân viên, phái, năm sinh, nơi sinh, địa chỉ, mã ngạch, bậc, hệ số, lƣơng, phụ cấp, chức vụ,... Đó là các thuộc tính phản ánh nội dung của một đối tƣợng cần quản lý. Các thuộc tính đó thƣờng đƣợc biểu diễn dƣới dạng các kiểu dữ liệu khác nhau (là chuỗi, số, ngày tháng, …) và đƣợc hợp nhất thành một đơn vị thông tin duy nhất gọi là mẫu tin (record). Các mẫu tin cùng “dạng” (cùng cấu trúc) hợp lại thành một cơ sở dữ liệu. Trong Excel, cơ sở dữ liệu có dạng nhƣ một danh sách, ví dụ nhƣ danh sách nhân viên, danh sách hàng hóa,... Mỗi danh sách có thể gồm có một hay nhiều cột, mỗi cột đƣợc gọi là một trƣờng (field) của cơ sở dữ liệu, tên của cột sẽ đƣợc gọi là tên trƣờng. Hàng đầu tiên trong danh sách (cơ sở dữ liệu) chứa các tên trƣờng đƣợc gọi là hàng tiêu đề (Header row), các hàng tiếp theo mỗi hàng là một mẫu tin (record) cho biết thông tin về đối tƣợng mà ta quản lý. 2.2. CÁC HÀM CƠ SỞ DỮ LIỆU Các hàm cơ sở dữ liệu mang tính chất thống kê những mẫu tin trong CSDL có trƣờng thỏa điều kiện của vùng tiêu chuẩn đã đƣợc thiết lập trƣớc. 2.2.1. Cú pháp chung. =Tên hàm(database, field, criteri) 11
- database: Địa chỉ vùng CSDL (Chọn địa chỉ tuyệt đối để sao chép). - field: Cột cần tính toán, field có thể là tên trƣờng, địa chỉ của ô tên field hoặc số thứ tự của trƣờng đó (cột thứ nhất của vùng CSDL đã chọn tính là 1 và tăng dần sang trái). - criteria: Địa chỉ vùng điều kiện Ví dụ : Ta có một cơ sở dữ liệu nhƣ sau: Hình 1.7: Ví dụ sử dụng hàm cơ sở dữ liệu. 2.2.2. Các hàm thao tác cơ sở dữ liệu. Tên hàm Ý nghĩa và ví dụ DAVERAGE(database, field, Tính trung bình cộng các giá trị trong cột field của các criteria) mẫu tin thỏa điều kiện criteria. =DAVERAGE($A$1:$D$8,D1,F1:F2) DMAX(database, field, criteria) Tìm trị lớn nhất trong cột field của các mẫu tin thỏa điều kiện criteria. =DMAX($A$1:$D$8,D1,F1:F2) DMIN(database, field, criteria) Tìm trị nhỏ nhất trong cột field của các mẫu tin thỏa điều kiện criteria. =DMIN($A$1:$D$8,D1,F1:F2) DCOUNT(database, field, Đếm các ô kiểu số trong cột field của các mẫu tin thỏa criteria) điều kiện criteria. =DCOUNT($A$1:$D$8,D1,F1:F2) DCOUNTA(database, field, Đếm các ô khác rỗng trong cột field của các mẫu tin criteria) thỏa điều kiện criteria. =DCOUNTA($A$1:$D$8,D1,F1:F2) 2.2.3. Các lệnh xử lý dữ liệu. 2.2.3.1. Trích lọc dữ liệu. 2.2.3.1.1. Lọc dữ liệu tự động (AutoFilter). 12
Chức năng: Lệnh Data\(Group Sort & Filter)\Filter dùng để lọc các mẩu tin thỏa mãn những tiêu chuẩn nào đó từ cơ sở dữ liệu ban đầu. Kết quả chỉ hiển thị những mẫu tin thỏa điều kiện còn những mẫu tin khác sẽ tạm thời bị che Thực hiện: + Chọn vùng CSDL với tiêu đề. + Chọn Tab Data\(Group Sort & Filter)\Filter, Excel sẽ tự động xuất hiện các nút thả cạnh tên field cho phép chọn điều kiện lọc tƣơng ứng với các field đó. + Chọn điều kiện lọc trong hộp liệt kê của từng field tƣơng ứng. + Chọn Text Fillter để thực hiện chức năng lọc nâng cao theo yêu cầu của ngƣời dùng: Hình 1.8: Hộp thoại Custom AutoFilter. Show rows where: Cho phép ngƣời dùng chọn điều kiện và nhập giá trị so sánh ở combobox kế bên. Ngƣời dùng có thể kết hợp với điều kiện “và”, “hoặc” phía dƣới. 2.2.3.1.2. Lọc dữ liệu nâng cao (Advanced Filter). Chức năng: Lệnh Data\(Group Sort & Filter)\ Advanced dùng để trích ra các mẩu tin theo các điều kiện chỉ định trong vùng điều kiện đƣợc tạo trƣớc. Thực hiện: Bƣớc 1: Tạo vùng điều kiện lọc. Sử dụng một trong hai cách sau: Cách 1: Sử dụng tên trƣờng để tạo vùng điều kiện: Vùng điều kiện sẽ có ít nhất hai hàng, hàng đầu chứa các tên field điều kiện, các hàng khác dùng để mô tả điều kiện. + Chọn các ô trống trong bảng tính để làm vùng điều kiện. + Sao chép tên field điều kiện làm tiêu đề của vùng điều kiện. 13
+ Nhập trực tiếp các điều kiện vào ô dƣới tên trƣờng tƣơng ứng. Các điều kiện ghi trên cùng một hàng là các điều kiện thỏa mãn đồng thời (AND), những điều kiện ghi trên các hàng khác nhau là những điều kiện thỏa mãn không đồng thời (OR). Ví dụ: Đối tƣợng Điểm A >5 Cách 2: Sử dụng công thức để tạo vùng điều kiện: Vùng điều kiện sẽ có hai ô, ô trên chứa tiêu đề nhƣ: “điều kiện”, …hoặc bỏ trống nhƣng phải khác với tên trƣờng, ô dƣới là công thức mô tả điều kiện. + Chọn hai ô trống trong bảng tính để làm vùng tiêu chuẩn. + Nhập tiêu đề ở ô trên của vùng tiêu chuẩn. + Nhập công thức vào ô bên dƣới mô tả điều kiện, dùng mẫu tin đầu tiên trong cơ sở dữ liệu để đặt điều kiện so sánh, hàm AND dùng để lập các điều kiện thỏa mãn đồng thời, hàm OR dùng để lập các điều kiện thỏa mãn không đồng thời. Ví dụ: Bƣớc 2: Vào Data\(Group Sort & Filter)\ Advanced, xuất hiện hộp thoại có các tùy chọn sau Action: + Filter the list, inplace: kết quả hiển thị trực tiếp trên vùng CSDL. + Copy to another location: kết quả đƣợc đặt tại một vị trí khác. + List range: Chọn địa chỉ vùng CSDL. + Criteria range: Chọn địa chỉ vùng tiêu chuẩn. + Copy to: Chọn địa chỉ của ô đầu tiên trong vùng kết quả (phải chọn mục Copy to another location). + Unique records only: Nếu có nhiều mẫu tin giống nhau thì chỉ lấy duy nhất một mẫu tin đại diện, ngƣợc lại thì lấy hết các mẫu tin thỏa điều kiện của vùng tiêu chuẩn (dù giống nhau). 14