Bài giảng Nhập môn Công nghệ thông tin 1 - Chương 9: 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 của bảng tính
8/20/2019 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 4
8/20/2019 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 đồ
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 6
8/20/2019 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 8
8/20/2019 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ờ.
8/20/2019 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.
8/20/2019 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 12
• Phần mềm bảng tính có thể được phân loại ở dạng giao diện
 trực quan GUI (WYSIWYG) và ở 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). Tuy nhiên việc thực
 hiện tính toán sẽ dễ dàng với giao diện trực quan hơn là lập
 trình tuần tự từng dòng vì:
 – Các mối quan hệ và các phụ thuộc thể hiện trực quan sẽ dễ
 dàng xem và hiểu hơn là sử dụng các dòng lệnh.
 – Một hay nhiều phần có thể làm việc đúng thậm chí các phần còn
 lại chưa hoàn thành hay bị sai. Việc lập trình đòi hỏi từng dòng
 lệnh phải đúng để có thể chạy.
 – Bảng tính GUI cho phép đánh dấu bằng màu sắc, hình ảnh, kí
 hiệu cho ý nghĩa của các phần tử.
• Tuy nhiên do việ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).
8/20/2019 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.
8/20/2019 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
 Openoffice.org Excel OOXML 
 XML (sxc) CSV (xls) HTML LaTeX ODF (ods) (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
 Openoffice.org Excel ODF OOXML 
 XML (sxc) CSV (xls) HTML PDF LaTeX (ods) (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ó
 8/20/2019 Khả năngKhoa kế CNTTt xu -ấĐHt các Khoa đ họcịnh Tự d ạnhiênng của phần mềm 16
• Bảng tính trên Web: Google docs, Office
 Web Apps, Zoho Office Suite, 
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 17
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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 19
• 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 ô.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 20
• 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
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 21
• 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 =.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 22
• Đị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, 
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 23
• Đị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ó.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 24
• 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 25
• 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, 
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 26
• 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,
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 27
• 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 28
• 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 29
• 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)
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 30
• 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
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 31
• 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)
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 32
• 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).
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 33
• 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).
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 34
• Nhóm hàm về chuỗi: phục vụ cho quá 
 trình xử lý chuỗi.
• 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).
8/20/2019 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).
8/20/2019 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ô.
8/20/2019 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):
 – i một cột): biểu thị một thuộc 
 tính a ng và có kiểu dữ liệu nhất định.
 – i một dòng): biểu thị một dòng 
 dữ liệu.
• C ý: Dòng 
 a các tên trường, những dòng a 
 các mẩu tin.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 38
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 39
• Vùng điều kiện: là a điều kiện để 
 tìm kiếm, gồm ít nhất hai dòng:
 – Dòng a tiêu đề.
 – Các dòng còn a điều kiện.
• Ví dụ:
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 40
• Vùng trích dữ liệu: 
 a vùng cơ sở dữ liệu 
 a vùng điều kiện. Vùng trích dữ liệu có 
 dòng a các tiêu đề a vùng 
 cơ sở dữ liệu.
• Ví dụ:
8/20/2019 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)
8/20/2019 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ẽ.
8/20/2019 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.
8/20/2019 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.
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 45
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 47
 BIỂU ĐỒ GIÁ TRỊ XUẤT KHẨU THEO QUÝ
 45000 Khoáng sản
 40000 Nông sản
 Hải sản
 35000
 Dược phẩm
 30000
 25000
 20000
 15000
 10000
 Dược phẩm
 5000 Hải sản
 0 Nông sản
 QUÝ 1 Khoáng sản
 QUÝ 2
 QUÝ 3
 QUÝ 4
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 48
 BIỂU ĐỒ GIÁ TRỊ XUẤT KHẨU THEO MẶT HÀNG
 50000
 45000
 40000
 35000
 QUÝ 1
 30000
 QUÝ 2
 25000
 QUÝ 3
 20000
 QUÝ 4
 15000
 10000
 5000
 0
 Khoáng sản Nông sản Hải sản Dược phẩm
8/20/2019 Khoa CNTT - ĐH Khoa học Tự nhiên 49