Bang tinh excel

11/2/2009
Chương 6 - Phần mềm bảng tính
1
PHẦN MỀM BẢNG TÍNH
CHƯƠNG 6
Giảng viên : Đào Quốc Thắng
Khoa Công nghệ thông tin
Đại học Ngaân hàng Tp Hồ Chí Minh
11/2/2009
Chương 6 - Phần mềm bảng tính
2
Nội dung
Tổng quát về MS Excel .3
Nhập và hiệu chỉnh nội dung các ô trong bảng tính .12
Định dạng bảng tính .20
Sử dụng hàm trong Exel .36
Quản trị cơ sở dữ liệu .49
Đồ thị - Biểu đồ .58
In bảng tính .78
11/2/2009
Chương 6 - Phần mềm bảng tính
3
Tổng quan về MS Excel
Phần mềm bảng tính (Spreadsheet software), cho phép người sử dụng tạo ra các bảng tính một cách trực quan, không cần lập trình.
Thành phần của bộ Microsoft Office.
Khởi động : từ Start menu/Programs/ Microsoft Office/Microsoft Office Excel.
11/2/2009
Chương 6 - Phần mềm bảng tính
4
Giao diện MS Excel 2003
Ô hiện hành
Task Pane
Thanh công thức
11/2/2009
Chương 6 - Phần mềm bảng tính
5
Lưu và mở bảng tính
Lưu bảng tính : File/ Save (Ctrl S) hoặc File/ Save As .
Mở bảng tính : File/ Open.
11/2/2009
Chương 6 - Phần mềm bảng tính
6
11/2/2009
Chương 6 - Phần mềm bảng tính
7
Sổ bảng tính (Workbook)
Tập hợp các bảng tính đặt trong một tập tin bảng tính Excel Worksheet (kiểu XLS.).
Gồm nhiều trang bảng tính (Sheet/ Worksheet). Với tên mặc định Sheet1, Sheet2, Sheet3 …Có thể dùng lệnh Format/Sheet/ Rename để đổi tên sheet.
11/2/2009
Chương 6 - Phần mềm bảng tính
8
Hàng, cột, ô (Row, column, cell)
Được chia thành 256 cột (cột A -> IV), 65,536 hàng (đánh số từ 1 tới 65536).
Ô : giao điểm của hàng và cột.
Địa chỉ ô : [Sheetr>
Ví dụ : B5, C6, [Sheet1]D7…
11/2/2009
Chương 6 - Phần mềm bảng tính
9
Vùng (Region)
Tập hợp ô liên tiếp tạo thành một vùng hình chữ nhật trên bảng tính.
Xác định vùng : đặt tên vùng, hoặc chỉ ô đầu, ô cuối.
Ví dụ : vùng [Đơn giá], vùng D5:D12
11/2/2009
Chương 6 - Phần mềm bảng tính
10
Nội dung ô
Chuỗi ký tự .
Số .
Ngày tháng/ Thời gian.
Giá trị logic.
Công thức.
11/2/2009
Chương 6 - Phần mềm bảng tính
11
Các thao tác cơ bản trên Excel
Lập bảng tính.
Định dạng bảng tính.
Quản trị cơ sỡ dữ liệu.
Tạo – hiệu chỉnh biểu đồ.
In ấn.
Lưu trữ.
11/2/2009
Chương 6 - Phần mềm bảng tính
12
2. Nhập và hiệu chỉnh nội dung các ô trong bảng tính
Nhập nội dung ô từ bàn phím. Excel tự động phân tích và xác định kiểu nội dung ô.
Trường hợp nhập nhằng : sử dụng quy tắc nhập (slide kế tiếp).
Hiệu chỉnh nội dung ô : nhấn F2.
Sao chép, cắt, dán, xóa nội dung khỏi ô : Copy, Cut, Paste, Clear.
11/2/2009
Chương 6 - Phần mềm bảng tính
13
Quy tắc nhập nội dung ô
Chuỗi ký tự : có thể thêm dấu nháy đơn ( ‘ ) vào đầu chuỗi.
Số : bắt đầu bằng các dấu =, +, - hoặc số không dấu.
Giá trị logic (TRUE, FALSE).
Ngày tháng : nhập theo dạng hệ thống (qui định trong bảng ký hiệu Regional).
11/2/2009
Chương 6 - Phần mềm bảng tính
14
Nhập công thức cho bảng tính
Bắt đầu bởi các dấu bằng (=), cộng (+) hoặc trừ (-).
Tham chiếu tới dữ liệu trong các ô thông qua địa chỉ ô. Có thể nhấp chuột vào ô cần tham chiếu để lấy địa chỉ.
11/2/2009
Chương 6 - Phần mềm bảng tính
15
Các toán tử cơ bản
Toán tử số : cộng (+), trừ (-), nhân (*), chia (/), phần trăm (%), lũy thừa (^).
Toán tử so sánh : =, >, >=. <. <=, <>
Toán tử nối chuỗi : &
Toán tử ngày giờ : +. -
11/2/2009
Chương 6 - Phần mềm bảng tính
16
Ví dụ
[F9] = D9*E9
11/2/2009
Chương 6 - Phần mềm bảng tính
17
Sao chép – Di chuyển các ô công thức
Khi sao chép – di chuyển ô công thứ, địa chỉ các ô dữ liệu trong công thức cũng thay đổi tương ứng theo.
Ví dụ : Chép công thức trong ô F9 xuống F10
[F9] = D9*E9
=> [F10] = D10*E10

11/2/2009
Chương 6 - Phần mềm bảng tính
18
Sử dụng địa chỉ tuyệt đối
Là các địa chỉ không bị thay đổi khi sao chép – di chuyển ô công thức
Biểu diễn : thêm dấu ‘$ ‘ vào trước hàng và cột cần cố định.
Ví dụ :
$C$4 : cố định cả hàng và cột.
$C4 : cố định hàng.
C$4 : cố định cột.
11/2/2009
Chương 6 - Phần mềm bảng tính
19
Vú dụ : Bảng dự tóan vật tư
Tham khảo
11/2/2009
Chương 6 - Phần mềm bảng tính
20
3. Định dạng bảng tính
Làm việc với trang bảng tính.
Định dạng hàng, cột.
Định dạng ô, vùng.
11/2/2009
Chương 6 - Phần mềm bảng tính
21
Làm việc với trang bảng tính
Đổi tên trang : Format/Sheet/Rename.
Ẩn trang : Format/Sheet/Hide.
Hiện lại trang ẩn : Format/Sheet/Unhide..
Tạo nền trang : Format/Sheet/Background.
Thêm trang mới : Insert/Worksheet..
Loại bỏ trang : Edit/ Delete sheet..
Sao chép – Di chuyển trang : Move or Copy
sheet.
11/2/2009
Chương 6 - Phần mềm bảng tính
22
Làm việc với hàng và cột
Thay đổi chiều rộng cột.
Thay đổi chiềi cao hàng.
Thêm hàng, cột.
Loại bỏ hàng, cột.
Ẩn/hiện hàng, cột.
11/2/2009
Chương 6 - Phần mềm bảng tính
23
Làm việc với ô / vùng
Chèn ô .
Loại bỏ ô.
Hiệu chỉnh nội dung ô (nhấn F2).
Định dạng ô.
11/2/2009
Chương 6 - Phần mềm bảng tính
24
Địng dạng ô/ vùng
Format  Cells
Number : định dạng số.
Alignment : Canh ô.
Font : Font chữ.
Border : Kẻ khung.
Patterns : Màu nền.
Protectiom : Bảo vệ.
11/2/2009
Chương 6 - Phần mềm bảng tính
25
Định dạng Number
Ví dụ :
11/2/2009
Chương 6 - Phần mềm bảng tính
26
11/2/2009
Chương 6 - Phần mềm bảng tính
27
Định dạng Font
Font Name, Font Style, Font Size : tên font, kiểu dáng, kích cỡ.
Underline : kiểu đường gạch dưới.
Color : màu chữ.
Effects : hiệu ứng
Strikethrough : gạch ngang.
Superscript : chỉ số trên (x2).
Subscript : chỉ số dưới (x2).
11/2/2009
Chương 6 - Phần mềm bảng tính
28
11/2/2009
Chương 6 - Phần mềm bảng tính
29
Canh ô (Aligment)
Text Aligment Horizontal : canh văn bản theo chiều ngang.
Text Aligment Vertical : canh văn bản theo chiều đứng (trên, dưới, giữa).
Wrap text : xuống dòng trong ô.
Shink to fit : thay đổi kích thước chữ cho chuỗi text vừa với ô.
11/2/2009
Chương 6 - Phần mềm bảng tính
30
Canh ô (tt)
Merge cells : trộn ô.
Indent : độ thụt (số ký tự).
Orientation : chiều in (độ nghiêng) của chuỗi ký tự.
11/2/2009
Chương 6 - Phần mềm bảng tính
31
11/2/2009
Chương 6 - Phần mềm bảng tính
32
Xóa ô/ vùng
Edit -> Clear
All : xóa toàn bộ (nội dung, định dạng )
Formats : Xóa các định dạng.
Content : Xóa nội dung.
Comments : Xóa các ghi chú.
11/2/2009
Chương 6 - Phần mềm bảng tính
33
Dán đặc biệt ( Paste Special)
Đối tượng dán
án
Phép toán
Chuyển đổi hàng - cột
11/2/2009
Chương 6 - Phần mềm bảng tính
34
Định dạng có điều kiện (Conditional Formatting)
Format  Conditional formatting
Định dạng được thể hiện khi thỏa mãn một điều kiện nào đó.
Tham khảo
11/2/2009
Chương 6 - Phần mềm bảng tính
35
11/2/2009
Chương 6 - Phần mềm bảng tính
36
4. Sử dụng hàm trong Excel
Cú pháp chung của các hàm :
Tên_hàm(ds tham số)
Đưa hàm vào công thức
Nhập trực tiếp từ bàn phím
Sử dụng công cụ Insert Function
11/2/2009
Chương 6 - Phần mềm bảng tính
37
Click
11/2/2009
Chương 6 - Phần mềm bảng tính
38
Hàm tính t oán
ABS(number)
PI()
ROUND(số, độ chính xác)
SUM(ds giá trị số/vùng ô)
PRODUCT(ds giá trị số/vùng ô)
Ví dụ :
11/2/2009
Chương 6 - Phần mềm bảng tính
39
Hàm chuỗi ký tự
LEFT(Chuỗi, số ký tự)
RIGHT(Chuỗi, số ký tự)
MID(Chuỗi vị trí, số ký tự)
TRIM(chuỗi)
UPPER(chuỗi)
LOWER(chuỗi)
PROPER(chuỗi)
LEN(chuỗi)
11/2/2009
Chương 6 - Phần mềm bảng tính
40
Ví dụ về hàm chuỗi
ký tự
Xem
11/2/2009
Chương 6 - Phần mềm bảng tính
41
Hàm ngày tháng
NOW()
TODAY()
DAY(date)
MONTH(date)
YEAR(date)
Xem:
11/2/2009
Chương 6 - Phần mềm bảng tính
42
Hàm thống kê
MAX(vùng)
MIN(vùng)
AVERAGE(vùng)
AVERAGEA(vùng)
COUNT(vùng)
COUNTA(vùng)
RANK(giá trị, vùng)
COUNTIF(vùng, criteria)
SUMIF(vùng, criteria, [vùng giá trị])
11/2/2009
Chương 6 - Phần mềm bảng tính
43
Hàm logic
NOT(đk)
AND(đk1, đk2,…)
OR(đk1, đk2,…)
11/2/2009
Chương 6 - Phần mềm bảng tính
44
Hàm IF
IF(đk, gt1, gt2)
Trả về gt1 nếu đk thỏa mãn, gt2 trong trường hợp ngược lại.
Ví dụ : ô F5 chứa điểm tổng kết, ô G5 chứa kết quả xét lên lớp của một học sinh.
Công thức trong G5 :
[G5] = IF(F5>=5,”Lên lớp”,”Ở lại”)
11/2/2009
Chương 6 - Phần mềm bảng tính
45
Hàm COUNTIF và SUMIF
COUNTIF(vùng, điều kiện)
Ví du :
COUNTIF(B4:B20, “A”)
CONTIF(C6:C25, “>5”)
SUMIF(vùng, đk, [vùng tổng])
Ví dụ :
SUMIF(D4:D30,”>=5”)
SUMIF(K5:K50, “A”, M5:M50)
11/2/2009
Chương 6 - Phần mềm bảng tính
46
Hàm dò tìm
VLOOKUP(giá trị, vùng, cột, kiểu)
HLOOKUP(giá trị,vùng, hàng, kiểu)
Dò tìm trong (cột đầu hoặc hàng đầu). Trả về giá trị ô tương ứng trong cột hoặc hàng được nêu.
Kiểu dò tìm :
TRUE (hoặc bỏ qua) : dò tìm xấp xỉ.
FALSE : dò tìm chính xác.
11/2/2009
Chương 6 - Phần mềm bảng tính
47
Ví dụ về hàm dò tìm
Dò tìm chính xác

Dò tìm xấp xỉ

11/2/2009
Chương 6 - Phần mềm bảng tính
48
11/2/2009
Chương 6 - Phần mềm bảng tính
49
5. Quản trị cơ sở dữ liệu
Cơ sở dữ liệu Excel : bảng liên tục (không chứa dòng trống)
Cột : trường (field)
Dòng 1 : dòng tên trường (không trùng lặp)
Các dòng còn lại : bản ghi (record)
11/2/2009
Chương 6 - Phần mềm bảng tính
50
Field
Record
Field name
11/2/2009
Chương 6 - Phần mềm bảng tính
51
Một số thao tác cơ bản trên CSDL
Sắp xếp (Sort)
Lọc (Filter)
AutoFilter
Advanced Filter
Subtotal
Consolidate
Pivot table/ Pivot chart

11/2/2009
Chương 6 - Phần mềm bảng tính
52
Tạo vùng chỉ tiêu lọc (Criteria range)
Dòng 1 : tên trường.
Các dòng tiếp theo : điều kiện lọc.
Điều kiện trên cùng 1 hàng : kết hợp bởi toán tử And.
Điều kiện trên các hàng kế tiếp : kết hợp bởi toán tử Or.
11/2/2009
Chương 6 - Phần mềm bảng tính
53
Chi tiết loại hàng A B bán trong ngày 1/6/2006
, sheet [Thong ke]
11/2/2009
Chương 6 - Phần mềm bảng tính
54
Pivot Table và Pivot Chart
Cho phép tạo một bản báo cáo tổng hợp từ một CSDL dưới dạng bảng, hoặc biểu đồ.
Xem
11/2/2009
Chương 6 - Phần mềm bảng tính
55
Các bước thực hiện
Chọn CSDL.
Vào Data/Pivot Table and Pivot Chart Report.
Step 1 : Chọn kiểu pivot Report (Pivot Table/ Pivot Chart).
Step 2 : Chọn vùng CSDL.
Step 3 : Tạo Layout và chỉ định vị trí xuất báo cáo.
Nhấp Finish.
11/2/2009
Chương 6 - Phần mềm bảng tính
56
Hàm cơ sở dữ liệu
Cú pháp chung
Dfunction(database, field, criteria)
Với:
Database : vùng cơ sở dữ liệu
Field : trường (chuỗi text, ô , stt trường trong CSDL)
Criteria : vùng chứa điều kiện
11/2/2009
Chương 6 - Phần mềm bảng tính
57
Một số hàm CSDL quan trọng
DCOUNT
DCOUNTA
DAVERAGE
DSUM
DMAX
DMIN
11/2/2009
Chương 6 - Phần mềm bảng tính
58
6. Đồ thị - Biểu đồ
Dữ liệu được sử dụng để tạo biểu đồ
Các loại biểu đồ
Các thành phần cơ bản của biểu đồ
Tạo biểu đồ
Hiệu chỉnh biểu đồ
11/2/2009
Chương 6 - Phần mềm bảng tính
59
Dữ liệu được sử dụng để tạo biểu đồ
Bảng dữ liệu
Category.
Data Series Name.
Data Series.
Data Points
11/2/2009
Chương 6 - Phần mềm bảng tính
60
Bảng dữ liệu tạo biểu đồ
Category
Data Series
D.S. Name
Data Point
11/2/2009
Chương 6 - Phần mềm bảng tính
61
11/2/2009
Chương 6 - Phần mềm bảng tính
62
Tạo biểu đồ
Đưa con trỏ tới CSDL.
Vào Insert/ Chart.
Step 1 : Chọn kiểu biểu đồ.
Step 2 : Xác định dữ liệu nguồn.
Step 3 : Xác định các tùy chọn biểu đồ (Chart Options).
Step 4 : Chỉ định vị trí lưu biểu đồ.
11/2/2009
Chương 6 - Phần mềm bảng tính
63
Hiệu chỉnh biểu đồ
Nhấp chuột phải trên biểu đồ, chọn tác vụ hiệu chỉnh (Thay đổi kiểu biểu đồ, dữ liệu nguồn, các tùy chọn biểu đồ …).
Double click vào thành phần muốn hiệu chỉnh để thay đổi định dạng.
11/2/2009
Chương 6 - Phần mềm bảng tính
64
11/2/2009
Chương 6 - Phần mềm bảng tính
65
Kiểu biểu đồ (Chart Type)
Column
Bar
Pie
Line
XY
Area
V.v.
11/2/2009
Chương 6 - Phần mềm bảng tính
66
11/2/2009
Chương 6 - Phần mềm bảng tính
67
Dữ liệu nguồn (Source Data)
Tab Data Range : xác định vùng dữ liệu
Data Range : Vùng dữ liệu.
Series in : chuỗi dữ liệu nằm theo hàng/cột (rows/colomns)
Tab Series : xác định chuỗi dữ loệu
Series : Ds chuỗi.
Name (tên), Values (giá trị), Category axis labels (vùng nhãn cho trục X).
11/2/2009
Chương 6 - Phần mềm bảng tính
68
11/2/2009
Chương 6 - Phần mềm bảng tính
69
11/2/2009
Chương 6 - Phần mềm bảng tính
70
Tùy biến biểu đồ (Chart Option)
Title : tiêu đề (biểu đồ, trục X, trục Y).
Axis : trục (X, Y).
Gridlines : đường lưới (X, Y).
Legend : chú thích.
Data Labels : nhãn dữ liệu.
Data Table : hiển thị bảng dữ liệu (có/ không).
11/2/2009
Chương 6 - Phần mềm bảng tính
71
Titles
11/2/2009
Chương 6 - Phần mềm bảng tính
72
Axis
Hiển thị
các trục
11/2/2009
Chương 6 - Phần mềm bảng tính
73
Gridlines
Hiển thị
đưởng lưới
11/2/2009
Chương 6 - Phần mềm bảng tính
74
Legend
Vị trí
chú thích
11/2/2009
Chương 6 - Phần mềm bảng tính
75
Data Labels
11/2/2009
Chương 6 - Phần mềm bảng tính
76
Data Table
Hiển thị bảng
dữ liệu
11/2/2009
Chương 6 - Phần mềm bảng tính
77
Format Axis - >Scale
Thay đổi tỷ lệ
trên trục
11/2/2009
Chương 6 - Phần mềm bảng tính
78
7. In bảng tính
Thiết lập trang in
File/Page Setup
Xem trước trang in
File/ Print Previww
Thực hiện in ấn
File/ Print (Ctrl+ P)
11/2/2009
Chương 6 - Phần mềm bảng tính
79
Thẻ Page
Chiều in
Cỡ giấy
11/2/2009
Chương 6 - Phần mềm bảng tính
80
Thẻ Margons
Canh giữa trang in
Đặt lề
trang in
11/2/2009
Chương 6 - Phần mềm bảng tính
81
Header
Footer
Thẻ Header/Footer
11/2/2009
Chương 6 - Phần mềm bảng tính
82
Phạm vi in
Chọn máy in
Số bản
11/2/2009
Chương 6 - Phần mềm bảng tính
83
HẾT CHƯƠNG 2