Xem mẫu
- 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
nguon tai.lieu . vn
