Bài giảng Nhập môn Công nghệ thông tin 1 - Chương 6: Xử lý bảng tính (Phần 1) - Ngô Chánh Đức

Xử lý bảng tính
Nhập môn Công nghệ Thông tin 1
Tổng quan Bảng tính
Phần mềm bảng tính
Cấu trúc cơ bản
Ứng dụng bảng tính
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 2
• Bảng tính (spreadsheet) là một khổ giấy lớn có các
hàng và các cột nhằm để tổ chức dữ liệu về các giao tác
phục vụ cho việc kiểm tra phân tích của một nhà kinh
doanh. Nó thể hiện tất cả chi phí, thu nhập, thuế và
những dữ liệu liên quan vào trong một khổ giấy để một
nhà quản lí kiểm tra phân tích khi muốn đưa ra một
quyết định.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 4
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 5
• Bảng tính tin học là một chương trình máy tính giả lập
lại một bảng tính trên giấy. 
• Chương trình bảng tính sẽ tổng hợp thông tin từ nhiều
nguồn tài liệu giấy và thể hiện thông tin ở dạng có thể hỗ
trợ người ra quyết định nhìn thấy một bức tranh tài chính
lớn của công ty.
• Các chức năng chính bao gồm:
– Lưu trữ và thể hiện dữ liệu
– Tính toán
– Lọc và thống kê dữ liệu
– Tạo biểu đồ
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 6
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 7
• Năm 1978, hai sinh viên trường Harvard, Daniel Bricklin
và Bob Frankston đã tạo ra ra phần mềm bảng tính
VisiCalc chạy trên máy tính Apple II. Và họ được xem
như cha đẻ của bảng tính.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 8
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 9
• Lotus 1-2-3 được phát triển bởi Mitch Kapor năm 1982 
trên máy IBM PC với những tính năng phức tạp hơn đã
đánh bật VisiCalc ra khỏi thị trường.
• Lotus 1-2-3 được xem như là chương trình tiêu biểu cho
các bảng tính phát triển trên nền Dos lúc bấy giờ.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 10
• Điểm mốc tiếp theo là sự ra đời của bảng tính
Microsoft Excel được viết cho máy Apple Macintosh 
512K vào năm 1984 – 1985. Excel là một trong những
bảng tính đầu tiên sử dụng giao diện đồ họa với các
thao tác kéo thả bằng chuột do đó nó nhanh chóng thu
hút thị trường.
• Excel được nhúng vào hệ điều hành Windows 2.0 vào
năm 1987 nên được xem như bảng tính đầu tiên chạy
trên nền hệ điều hành Windows. Sau đó nó được tích
hợp vào trong
bộ Office. Từ đó cho đến nay,
Microsoft Excel đã chiếm lĩnh
thị trường bảng tính.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 11
• Cùng với sự phát triển của Microsoft Excel, các phần
mềm bảng tính khác như OpenOffice.org Calc (2001), 
Gnumeric (2001), Numbers (Apple, 2007), Google 
Spreadsheets (2007) chạy trên các hệ điều hành khác
nhau, trên nền tảng khác nhau với các tính năng thời
gian thực và làm việc cộng tác đã và đang bắt đầu chia 
sẻ thị phần bảng tính.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 12
• dạng giao diện trực quan GUI (WYSIWYG): dễ dàng
cho việc thực hiện tính toán
• dạng lập trình biên dịch (phát sinh ra bảng tính dưới
dạng in còn các xử lý bảng tính được thực hiện ngay
trong thao tác lập trình): giúp bảng tính ít bị giới hạn tính
năng từ phía nhà cung cấp phần mềm nên nó có thể
được bổ sung ở tính năng viết script (VBA, Javascript,), 
hay chuyển sang hệ thống cơ sở dữ liệu (database).
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 14
• Phần mềm bảng tính có thể được phân loại theo
nền tảng ứng dụng: ứng dụng trên desktop và ứng
dụng trên Web. Lợi thế của ứng dụng Web là có thể chạy
mà không phụ thuộc vào hệ điều hành.
• Ngoài tính năng được đáp ứng như desktop, ứng dụng
web còn bổ sung hai tính năng quan trọng: làm việc cộng
tác giữa nhiều người và lấy dữ liệu từ bảng tính của
người khác thậm chí họ đang không làm việc.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 15
• Bảng tính trên desktop: Microsoft Excel, IBM Lotus 
Symphony, Numbes, OpenOffice.org Calc, Gnumeric
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 16
Openoffice.org 
XML (sxc) CSV
Excel 
(xls) HTML LaTeX ODF (ods)
OOXML 
(xlsx)
Gnumeric Có Có Có Có Có Có Có
IBM Lotus Symphony Có Có Có Không Không Có Có
Microsoft Excel Không Có Có Có Không Có Có
Numbers Không Có Có Không Không Có Có
Openoffice.org Calc Có Có Có Có Không Có Có
Khả năng mở các định dạng của phần mềm
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 17
Openoffice.org 
XML (sxc) CSV
Excel 
(xls) HTML PDF LaTeX
ODF 
(ods)
OOXML 
(xlsx)
Gnumeric Không Có Có Có Có Có Có Có
IBM Lotus Symphony Có Có Có Có Có Không Có Không
Microsoft Excel Không Có Có Một phần Có Có Có
Numbers Không Có Có Không Có Không
Openoffice.org Calc Có Có Có Có Có Không Có Có
Khả năng kết xuất các định dạng của phần mềm
• Bảng tính trên Web: Google SpreadSheets, Office Web 
Apps, Zoho Office Suite, 
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 18
• Bảng tính bao gồm nhiều thành phần và có thể được chia 
thành các nhóm sau:
– Lưu trữ và thể hiện dữ liệu
– Biểu thức, hàm xử lý
– Cơ sở dữ liệu
– Biểu đồ
– Làm việc cộng tác
– Ngoài ra còn có các thao tác để thực hiện các chức năng trên. 
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 20
• Bảng tính được thể hiện dưới dạng lưới bao gồm các cột 
và các dòng giao nhau tạo thành các ô (cell). Trong mỗi 
file có thể bao gồm nhiều bảng tính được thể hiện dưới 
các trang bảng tính(sheet).
• Số lượng dòng, cột phụ thuộc
vào từng phần mềm. Ví dụ
Ms Excel 2003 có 65536 dòng
và 256 cột. Như vậy có tất cả
16,777,216 ô.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 21
• Các dòng được đánh thứ tự bằng số nguyên từ 1, 2, 
• Các cột được đánh thứ tự bằng ký tự chữ cái từ A, B, ,Z, AA, 
BB, 
• Mỗi ô được thể hiện bằng một địa chỉ duy nhất có dạng 
. 
– Ví dụ ô A1 là ô nằm ở cột A, dòng 1. 
• Có 3 loại địa chỉ ô nhằm phục vụ cho quá trình tham chiếu tự 
động:
– Tương đối: cột và dòng thay đổi khi tham chiếu tự động. VD: A1
– Tuyệt đối: cột và dòng không thay đổi khi tham chiếu tự động. VD: 
$A$1
– Hỗn hợp: cột hoặc dòng không thay đổi khi tham chiếu tự động. VD: 
$A1, A$1
• Ngoài ra địa chỉ của ô được gắn thêm tên của trang bảng tính 
mà nó được thể hiện trong trường hợp được tham chiếu ngoài 
trang đó. Ví dụ: Trang1!A1
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 22
• Mỗi ô có thể chứa một giá trị hay một hàm hay có thể bỏ 
trống. 
– Giá trị có thể được nhập vào trực tiếp hay từ kết quả trả về 
của các hàm, từ thể hiện ngày tháng, từ dữ liệu nhận về từ 
bên ngoài.
– Ô chứa hàm thông thường bắt đầu với dấu =.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 23
• Định dạng ô: bao gồm định dạng màu sắc, kích thước, 
in nghiêng, in đậm, gạch chân, font chữ, đường viền, 
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 24
• Định dạng kiểu giá trị trong ô: mỗi ô có thể được định ra 
cách thể hiện kiểu giá trị. Ví dụ giá trị kiểu ngày tháng, 
giá trị kiểu tiền tệ, giá trị kiểu số, giá trị kiểu chuỗi, 
• Lưu ý: nội dung của ô
không thay đổi,
chỉ thay đổi cách
thể hiện nó.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 25
• Vùng (range) là một ô được xác đinh bởi địa chỉ của ô 
hay tập hợp các ô liên tiếp nhau được xác định bởi:
– : 
• Vùng được sử dụng để cho phần mềm biết phạm vi giá
trị được chọn, phục vụ cho quá trình di chuyển, sao chép
và làm dữ liệu tham chiếu cho các hàm. Ví dụ: 
Sum(B2:E5) nghĩa là tính tổng các số trong tất cả ô của
vùng.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 26
• Thẻ quản ô/vùng: khi một ô hay một vùng được chọn
thì góc phải dưới cùng được gọi là thẻ quản ô (vùng).
• Thẻ quản ô (vùng) phục vụ các thao tác kéo thả, sao
chép, điền dữ liệu, 
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 27
• Biểu thức: thực hiện các công việc hay tính toán được
đặt trong ô có chứa nó, kết quả trả về ngay tại ô này.
• Biểu thức có dạng sau:
– = biểu_thức
• Biểu thức bao gồm:
– Giá trị thực như =“ĐHKHTN”, =2, =3.15,
– Tham chiếu đến ô khác như =A1
– Biểu thức đại số với +,-,*,/,
– Biểu thức quan hệ với >=, <,
– Các hàm như sum(), if(), các hàm tự định nghĩa,
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 28
• Do đặc thù của bảng tính nên các hàm được sử dụng khá
nhiều. Hàm có thể do chương trình đã định nghĩa trước
hoặc do người dùng tự định nghĩa. Ví dụ trong Microsoft 
Excel 2003 có khoảng 300 hàm đã được định nghĩa.
• Cú pháp: Tên_hàm(các tham số)
– Ví dụ: Sum(A1,15, Sum(B2:D7))
• Các tham số cách nhau bằng dấu phẩy.
• Tham số hàm có thể là:
– Hằng số, hằng chuỗi.
– Địa chỉ ô, vùng.
– Các hàm khác.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 29
• Các hàm có thể được phân thành các nhóm:
– Nhóm hàm về số.
– Nhóm hàm thời gian.
– Nhóm hàm thống kê.
– Nhóm hàm chuỗi.
– Nhóm hàm luận lý.
– Nhóm hàm điều khiển.
• Trong mỗi chương trình bảng tính đều có hỗ trợ định 
nghĩa các hàm, cách dùng.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 30
• Nhóm hàm về số: xử lý kiểu dữ liệu số hoặc thời gian. 
• Ví dụ trong Excel 2003: 
– abs (hàm lấy giá trị tuyệt đối).
– int (hàm lấy phần nguyên)
– mod (hàm lấy phần dư)
– round (hàm làm tròn)
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 31
• Nhóm hàm về thời gian: dùng để trích các thành phần
thời gian, lấy thời gian hiện tại, chuyển đổi dữ liệu thời
gian.
• Một giá trí thời gian bao gồm 6 thành phần:
– Tháng/ngày/năm giờ:phút:giây
– giá trị ngày giá trị giờ
• Ví dụ trong Excel 2003: 
– Hàm trích thành phần thời gian: Day, Month, Year, Hour, 
Minute, Second.
– Hàm lấy thời gian hiện tại: Now.
– Hàm chuyển đổi dữ liệu thời gian: Date, Time
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 32
• Nhóm hàm về thống kê: phục vụ cho quá trình tính
toán thống kê.
• Ví dụ trong Excel 2003: 
– Avarage (hàm tính trung bình cộng).
– Count (hàm đếm giá trị số).
– CountA (hàm đếm các giá trị khác rỗng).
– Max, min (hàm lấy giá trị lớn nhất, nhỏ nhất).
– Sum (hàm tính tổng)
– Rank (hàm xếp hạng)
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 33
• Nhóm hàm về chuỗi: các hàm xử lý chuỗi trong bảng
tính.
• Ví dụ trong Excel 2003: 
– Left (hàm lấy chuỗi bên trái).
– Right (hàm lấy chuỗi bên phải).
– Mid (hàm lấy chuỗi ở giữa).
– Value (hàm đổi số thành chuỗi).
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 34
• Nhóm hàm về luận lý: trả về kết quả true hoặc false.
• Ví dụ trong Excel 2003: 
– And(hàm hội).
– Or (hàm tuyển).
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 35
• Nhóm hàm điều khiển: bao gồm các hàm rẻ nhánh và
thực hiện tìm kiếm.
• Ví dụ trong Excel 2003: 
– If (hàm rẻ nhánh điều kiện).
– CountIf(hàm đếm có điều kiện).
– SumIf(hàm tính tổng có điều kiện).
– VLookup(hàm tìm kiếm theo cột).
– HLookup(hàm tìm kiếm theo dòng).
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 36
• Hàm do người dùng định nghĩa bằng các đoạn mã. 
Ví dụ trong Microsoft Excel sử dụng VBA (Visual Basic for 
Applications).
• Macro: là một chuỗi các hành động được thực hiện và
có thể áp lên các ô trong bảng tính mà không cần lời gọi
hàm tại ô đó. Trong Microsoft Excel cũng sử dụng VBA để
viết macrô.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 37
• Vùng cơ sở dữ liệu: là vùng bảng tính được xác định
bởi địa chỉ của ô trái trên và ô phải dưới.
• Bao gồm các trường (field) và mẫu tin (record):
– Trường (tương ứng với một cột): biểu thị một thuộc tính của 
đối tượng và có kiểu dữ liệu nhất định.
– Mẩu tin (tương ứng với một dòng): biểu thị một dòng dữ liệu.
• Chú ý: Dòng đầu của vùng cơ sở dữ liệu chứa các
tên trường, những dòng tiếp theo chứa các mẩu tin.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 38
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 39
• Vùng điều kiện: là vùng chứa điều kiện để tìm kiếm, gồm 
ít nhất hai dòng:
– Dòng chứa tiêu đề.
– Các dòng còn lại chứa điều kiện.
• Ví dụ:
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 40
• Vùng trích dữ liệu: Là vùng chứa các mẩu tin của vùng 
cơ sở dữ liệu thỏa yêu cầu của vùng điều kiện. Vùng trích 
dữ liệu có dòng đầu tiên chứa các tiêu đề của vùng cơ sở 
dữ liệu.
• Ví dụ:
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 41
• Các thao tác quan trọng trong cơ sở dữ liệu: 
– Sắp xếp dữ liệu theo nội dung một cột và nhiều cột.
– Lọc và tìm kiếm trên cơ sở dữ liệu.
• Các hàm thống kê trên cơ sở dữ liệu.
• Ví dụ trong Excel 2003:
– DSum(tính tổng có điều kiện)
– DCount (hàm đếm có điều kiện)
– DCountA (hàm đếm có điều kiện)
– DAverage (hàm tính trung bình có điều kiện)
– DMax, DMin (hàm tìm số lớn nhất nhỏ nhất có điều kiện)
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 42
• Biểu đồ tổng hợp dữ liệu dưới dạng cột, đường, mặt 
nhằm mục đích để thấy tổng quan về dữ liệu từ đó đưa 
ra các nhận xét và đánh giá.
• Các bước chính của tạo biểu đồ:
– Chọn vùng dữ liệu cần vẽ trong đó hàng đầu và cột đầu là giá 
trị thước đo.
– Chọn biểu đồ thích hợp để vẽ.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 43
• Làm việc cộng tác là cho phép nhiều người cùng chỉnh 
sửa trên bảng tính sao cho đảm báo tính thống nhất, an 
toàn cho dữ liệu.
• Các chức năng của làm việc cộng tác:
– Import và Export dữ liệu trong excel.
– Cài các quyền cho bảng tính.
– Thêm và chỉnh sửa các chú thích, lời khuyên (comment).
– Theo vết các thay đổi.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 44
• Các thao tác thêm, xóa, thay đổi kích thước cột và dòng.
• Các thao tác ẩn, hiện dòng cột, gom nhóm dòng cột.
• Thay đổi kích thước khung nhìn.
• Vẽ hình trong bảng tính.
• Các thao tác định dạng trang in.
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 45
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 47
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 48
QUÝ 1
QUÝ 2
QUÝ 3
QUÝ 4
Khoáng sản
Nông sản
Hải sản
Dược phẩm
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
BIỂU ĐỒ GIÁ TRỊ XUẤT KHẨU THEO QUÝ
Khoáng sản
Nông sản
Hải sản
Dược phẩm
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 49
BIỂU ĐỒ GIÁ TRỊ XUẤT KHẨU THEO MẶT HÀNG
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
Khoáng sản Nông sản Hải sản Dược phẩm
QUÝ 1
QUÝ 2
QUÝ 3
QUÝ 4
• Mỗi nhóm chuẩn bị slide thuyết trình trong 10 phút về
chủ đề được giao.
– Định dạng và thể hiện dữ liệu: 5, 9, 15
– Các hàm về thống kê dữ liệu: 4, 8, 12
– Lập biểu đồ: 3, 7, 11
– Phân tích dữ liệu: 2, 6, 10, 16
• Sắp xếp, Lọc, Bảng
• Pivot Table
– VBA – Macro: 1, 13, 17, 14
– Mail Merge: 18, 19
11/26/2015 Khoa CNTT - ĐH Khoa học Tự nhiên 50