Phần mềm bảng tính Excel: [Mô tả/Hướng dẫn/Thủ thuật] tốt nhất

PHẦN MỀM BẢNG TÍNH EXCEL

Giảng viên : Ths.Nguyễn Thị Khiêm Hoà

Khoa Công nghệ thông tin Đại học Ngân hàng 1

Liên hệ

 Website: sites.google.com/site/khiemhoa  Email: khiemhoa@buh.edu.vn

Nội dung

1. Tổng quát về MS Excel

2. Nhập và hiệu chỉnh nội dung các ô

trong bảng tính

3. Định dạng bảng tính

4. Sử dụng hàm trong Excel

5. Quản trị cơ sở dữ liệu

6. Đồ thị Biểu đồ

In bảng tính

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 4

Giao diện MS Excel 2003

Thanh công thức

Ô hiện hành

Task Pane

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 6

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 8

Hàng, cột, ô (Row, column, cell)

 Được chia thành 256 cột (cột A > IV) và 65.536 hàng (đánh số từ 1 tới 65536).

 Ô (cell): Giao điểm của hàng và cột.

 Địa chỉ ô: [

Ví dụ : B5, C6, [Sheet] D7…

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 10

Nội dung ô

 Chuỗi ký tự .  Số .  Ngày tháng/ Thời gian.  Giá trị logic.  Công thức.

Khoa Công nghệ thông tin Đại học Ngân hàng 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ữ.

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 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).

Khoa Công nghệ thông tin Đại học Ngân hàng 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ỉ.

Khoa Công nghệ thông tin Đại học Ngân hàng 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ờ : +,

Khoa Công nghệ thông tin Đại học Ngân hàng 16

Ví dụ

[F9] = D9* E9

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 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ài 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 cột.

C$4

: cố định hàng.

Khoa Công nghệ thông tin Đại học Ngân hàng 19

Ví dụ : Bảng dự toán vật tư

 Tham khảo

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 21

Làm việc với trang bảng tính

: Format/Sheet/Rename.

: Format/Sheet/Hide.

: Format/Sheet/Background.

: Edit/ Delete sheet..

 Đổi tên trang  Ẩn trang  Hiện lại trang ẩn : Format/Sheet/Unhide..  Tạo nền trang  Thêm trang mới : Insert/Worksheet..  Loại bỏ trang  Sao chép – Di chuyển trang : Move or Copy

sheet.

Khoa Công nghệ thông tin Đại học Ngân hàng 22

Làm việc với hàng và cột

 Thay đổi chiều rộng cột.  Thay đổi chiều cao hàng.  Thêm hàng, cột.  Loại bỏ hàng, cột.  Ẩn/hiện hàng, cột.

Khoa Công nghệ thông tin Đại học Ngân hàng 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 ô.

Khoa Công nghệ thông tin Đại học Ngân hàng 24

Địng dạng ô/ vùng

: định dạng số.

: Canh ô.

: Font chữ.

: Kẻ khung.

: Màu nền.

 Format  Cells  Number  Alignment  Font  Border  Patterns  Protection

: Bảo vệ.

Khoa Công nghệ thông tin Đại học Ngân hàng 25

Định dạng Number

 Ví dụ :

Khoa Công nghệ thông tin Đại học Ngân hàng 26

Khoa Công nghệ thông tin Đại học Ngân hàng 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).

Khoa Công nghệ thông tin Đại học Ngân hàng 28

Khoa Công nghệ thông tin Đại học Ngân hàng 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 ô.

Khoa Công nghệ thông tin Đại học Ngân hàng 30

Canh ô (tt)

 Merge cells : trộn ô.  Indent : độ lùi (số ký tự).  Orientation : chiều in (độ nghiêng) của

chuỗi ký tự.

Khoa Công nghệ thông tin Đại học Ngân hàng 31

Khoa Công nghệ thông tin Đại học Ngân hàng 32

Xóa ô/ vùng

 Edit > Clear

 All

: xóa toàn bộ (nội

dung, định dạng )

: Xóa các định dạng.

: Xóa nội dung.

 Formats  Content  Comments

: Xóa các ghi chú.

Khoa Công nghệ thông tin Đại học Ngân hàng 33

Dán đặc biệt ( Pasle Special)

Đối tượng dán

Chuyển đổi hàng cột

Phép toán

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 35

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 37

Clic k

Khoa Công nghệ thông tin Đại học Ngân hàng 38

Hàm tính toá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ụ :

Khoa Công nghệ thông tin Đại học Ngân hàng 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)

Khoa Công nghệ thông tin Đại học Ngân hàng 40

Ví dụ về hàm chuỗi ký tự

 Xem

Khoa Công nghệ thông tin Đại học Ngân hàng 41

Hàm ngày tháng

 NOW()  TODAY()  DAY(date)  MONTH(date)  YEAR(date)

Xem:

Khoa Công nghệ thông tin Đại học Ngân hàng 42

Hàm thống kê

 MAX(vùng)  MIN(vùng)  AVERAGE(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ị])

Khoa Công nghệ thông tin Đại học Ngân hàng 43

Hàm logic

 NOT(điều kiện)  AND(điều kiện 1, điều kiện 2,…)  OR( điều kiện 1, điều kiện 2,…)

Khoa Công nghệ thông tin Đại học Ngân hàng 44

Hàm IF

• IF(điều kiện, giá trị 1, giá trị 2)

Trả về giá trị 1 nếu điều kiện thỏa mãn, giá trị

2 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”)

Khoa Công nghệ thông tin Đại học Ngân hàng 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)

Khoa Công nghệ thông tin Đại học Ngân hàng 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 vùng (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.

Khoa Công nghệ thông tin Đại học Ngân hàng 47

Ví dụ về hàm dò tìm

 Dò tìm chính xác

 Dò tìm xấp xỉ

Khoa Công nghệ thông tin Đại học Ngân hàng 48

Khoa Công nghệ thông tin Đại học Ngân hàng 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)

Khoa Công nghệ thông tin Đại học Ngân hàng 50

Field

Field name

Record

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 53

Chi tiết loại hàng A B bán trong ngày 1/6/2006

, sheet [Thong ke]

Khoa Công nghệ thông tin Đại học Ngân hàng 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

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 56

Hàm cơ sở dữ liệu

Cú pháp chung

Dfunction(database, field, criteria)

Với

o Database : vùng cơ sở dữ liệu

o Field : trường (chuỗi text, ô , stt trường

trong CSDL)

o Criteria : vùng chứa điều kiện

Khoa Công nghệ thông tin Đại học Ngân hàng 57

Một số hàm CSDL quan trọng

 DCOUNT  DCOUNTA  DAVERAGE  DSUM  DMAX  DMIN

Khoa Công nghệ thông tin Đại học Ngân hàng 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 đồ

Khoa Công nghệ thông tin Đại học Ngân hàng 59

Dữ liệu được sử dụng để tạo biểu đồ

 Bảng dữ liệu

o Category.

o Data Series Name.

o Data Series.

o Data Points

Khoa Công nghệ thông tin Đại học Ngân hàng 60

Bảng dữ liệu tạo biểu đồ

Categor y

D.S. Name

Data Seriries

Data Point

Khoa Công nghệ thông tin Đại học Ngân hàng 61

Khoa Công nghệ thông tin Đại học Ngân hàng 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 đồ.

Khoa Công nghệ thông tin Đại học Ngân hàng 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.

Khoa Công nghệ thông tin Đại học Ngân hàng 64

Khoa Công nghệ thông tin Đại học Ngân hàng 65

Kiểu biểu đồ (Chart Type)

 XY  Area  v.v.

 Column  Bar  Pie  Line

Khoa Công nghệ thông tin Đại học Ngân hàng 66

Khoa Công nghệ thông tin Đại học Ngân hàng 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áx định chuỗi dữ liệu

 Series : Ds chuỗi.  Name (tên), Values (giá trị), Category axis

labels (vùnh nhãn cgo trục X).

Khoa Công nghệ thông tin Đại học Ngân hàng 68

Khoa Công nghệ thông tin Đại học Ngân hàng 69

Khoa Công nghệ thông tin Đại học Ngân hàng 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ị nảng dữ liệu (có/ không).

Khoa Công nghệ thông tin Đại học Ngân hàng 71