Bài giảng Tin học kế toán (Phần 1)
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
[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
..................................................................................................................................................22
3.1.1. Một số khái niệm liên quan..................................................................................22
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
4.1. GIỚI THIỆU CHUNG...................................................................................................41
4.2.1. Giới thiệu...............................................................................................................44
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.1. Xác đinh hệ số tƣơng quan giữa các yếu tố kinh tế............................................55
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
KHOẢN. .............................................................................................................................. 69
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.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.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ố
TT
Tên Bài trong mô đun
Lý
Thực
Kiểm
tra
1
Tổng số
thuyết
hành
1
2
Các hàm excel thông dụng
Tổ chức dữ liệu kế toán trong điều kiện ứng dụng
excel
12
8
3
2
8
6
3
Sử dụng excel giải quyết bài toán về tính hiệu quả
vốn đầu tƣ
8
2
6
4
5
Phân tích và dự báo kinh tế trong excel
Ứng dụng các hình thức kế toán trên excel
Cộng
16
16
60
4
4
15
11
10
41
1
2
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ànphím
+ Đặt trỏ chuột tại ô muốn nhậphà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
I
Nội dung
Điểm thao tác
1 Cú pháp chung.
2 Cách sử dụng.
Điểm cộng sáng tạo
Điểm chuẩn
10
4
6
0.5
Điểm đánh giá
II
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
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
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
Trả về giá trị trung bình cộng của các số trong danh
sách tham số.
...)
MIN(number1, number2, ...)
AVERAGE(number1,
umber2, ...)
=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",
"<20".
=COUNTIF(B4:B12, “>=6”)
4
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<8, 9>-12) –TRUE
Trả về giá trị FALSE nếu tất cả điều kiện là
FALSE.
OR(logical1, logical2, …)
=OR(2>3, 12<8, 9>3) -TRUE
=OR(2>3, 12<8, -9>3) -FALSE
Lấy phủ định của giá trị logical.
=NOT(2>3) -TRUE
NOT(logical)
IF(logical_test,alue_if_true
, value_if_false)
Trả về giá trị value_if_true nếu điều kiện
logical_test là TRUE, ngƣợc lại sẽ trả về giá trị
value_if_false.
=IF(A1 >=5, “Trƣợt”,”Đỗ”)
Trả về giá trị tuyệt đối của một số thực.
=ABS(12 - 20) 8
ABS(number)
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(5, 3) 2
MOD(number, divisor)
Làm tròn lên tới một số nguyên lẻ gần nhất.
=ODD(3.6) 5
ODD(number)
PRODUCT(number1,
number2, ...)
=ODD(-2.2) -3
Tính tích của các giá trị trong danh sách tham
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
Tính căn bậc 2 của một số dƣơng number.
=SQRT(36) =6
SQRT(number)
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.
criteria: chuỗi mô tả điều kiện.
Ví dụ: "10",">15", "<20"
SUMIF(range,
sum_range])
criteria
[,
sum_range: vùng đƣợc tính tổng. Các ô trong
vùng này sẽ đƣợc
tính tổng nếu các ô tƣơng ứng trong vùng range
thỏa điều kiện.
Nếu không có sum_range thì vùng range sẽ
đƣợctính
=SUMIF(C4:C12, “>=6”, F4:F12)
1.2.4. Các hàm xử lý chuỗi (Text).
Các hàm xử lý chuỗi thông dụng đƣợc trình bày bên dƣới:
CÚ PHÁP Ý NGHĨA VÀ VÍ DỤ
LOWER(text)
Chuyển chuỗi text thành chữ thƣờng.
= LOWER(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”)
6
CÚ PHÁP
UPPER(text)
Ý NGHĨA VÀ VÍ DỤ
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)
TRIM(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ộ”)
Cắt bỏ các ký tự trống vô ích trong chuỗi text.
=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,
num_chars)
Trả về num_char ký tự bên phải chuỗi text.
=RIGHT(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”,15)
MID(text,
start_num, Trả về chuỗi ký tự có độ dài num_chars bắt đầu từ vị trí
start_num của chuỗi text.
num_chars)
=MID(“Cao Đẳng Công Nghệ Nông Lâm Nam Bộ”, 10, 9)
Chuyển chuỗi có dạng số thành trị số.
VALUE(text)
= 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( )
NOW( )
Trả về ngày hiện hành của hệ thống.
=TODAY( )
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,
second)
Trả về giá trị dạng Time.
=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).
=VLOOKUP(lookup_value, Table_array,
Cú pháp:
lookup)
col_index_num, range_
+ 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
chuẩn
Điểm
đánh
giá
TT
Nội dung
I
1
Điểm thao tác
Các hàm toán học.
10
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
1
III
1
2
3
Điểm cộng sáng tạo
0.5
0.5
0.5
0
0.25
0.5
10
Sáng tạo trong các bài tập ứng dụng.
Điểm cộng hoàn thành trƣớc thời gian qui định
Hoàn thành đúng thời gian qui định.
Hoàn thành trƣớc thời gian qui định từ 10-15 phút.
Hoàn thành trƣớc thời gian qui định từ 16 phút trở lên.
Tổng điểm
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,
criteria)
Đếm các ô kiểu số trong cột field của các mẫu tin thỏa
điều kiện criteria.
=DCOUNT($A$1:$D$8,D1,F1:F2)
DCOUNTA(database, field,
criteria)
Đếm các ô khác rỗng trong cột field của các mẫu tin
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
Tải về để xem bản đầy đủ
46 trang
11/05/2022
2160
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học kế toán (Phần 1)", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_tin_hoc_ke_toan_phan_1.pdf
