Bài giảng Excel CCA

MICROSOFT EXCEL 2003
2
KHÁI QUÁT VỀ MICROSOFT EXCEL
I GIỚI THIỆU:
- MS_Excel là chương trình giúp ta tính toán trên bảng tính với tốc độ cực nhanh do hãng Microsoft sản xuất.
- Hiện tại Excel được sử dụng rộng rãi trong môi trường doanh nghiệp, nhằm phục vụ các công việc tính toán thông dụng, bằng các công thức tính toán mà người sử dụng (NSD) không cần phải xây dựng các chương trình.
3
II.KHỞI ĐỘNG VÀ THOÁT
1/ Khởi động:
+ C1: StartProgramsMicrosoft Office Microsoft Office Excel 2003
+ C2: Kích đúp chuột vào biểu tượng (Microsoft Office Excel 2003) trên mà hình Desktop.
KHÁI QUÁT VỀ MICROSOFT EXCEL
4
KHÁI QUÁT VỀ MICROSOFT EXCEL
2/Thoát:
Cách 1: Chọn lệnh File  Exit
Cách 2: Kích chuột vào nút (Close) trên thanh tiêu đề của cửa sổ Excel
Cách 3: Nhấn tổ hợp phím Alt + F4
- Nếu chọn:
+Yes: thoát và lưu tập tin
+ No: thoát và không lưu tập tin.
+ Cancel: huỷ bỏ thao tác vừa thực hiện.
5
KHÁI QUÁT VỀ MICROSOFT EXCEL
III. MÀN HÌNH GIAO DIỆN:
6
-  Thanh tiêu đề (Title bar)
-  Thanh thực đơn (Menu bar)
-  Thanh công cụ chuẩn (Standard)
-  Thanh công cụ định dạng (Formatting)
- Thanh địa chỉ (Address)
-  Thanh trạng thái (Status).
KHÁI QUÁT VỀ MICROSOFT EXCEL
III. MÀN HÌNH GIAO DIỆN:
7
KHÁI QUÁT VỀ MICROSOFT EXCEL
III. MÀN HÌNH GIAO DIỆN(tt)
* Hiện / ẩn các thanh công cụ: chọn View  Toolbars (thường hay sử dụng ba thanh công cụ Standard, Formatting, Drawing…)
+ Thanh công thức( Formula ): bên trái thể hiện địa chỉ ô, bên phải hiển thị dữ liệu trong ô. Dấu = được chọn trước khi sử dụng công thức.

(Hiện hoặc ẩn thanh công thức:View Formula Bar
8
KHÁI QUÁT VỀ MICROSOFT EXCEL
IV. Cấu trúc bảng tính Excel
Mỗi tập tin Excel có thể chứa nhiều bảng tính được gọi là một Sheet (gồm 256 cột x 65536 dòng = 16777216 ô). Mỗi tập tin (book) có thể chứa tối đa 255 Sheet. Khi mở mới một tập tin, Excel sẽ có tên tập tin là Book1.xls và con trỏ sẽ nằm ở bảng tính Sheet1
* Để quy định số Sheet hiển thị trong một bảng tính: ToolsOptionsGeneral nhập số sheet cần hiển thị trong khung Sheet in new Workbook.
9
KHÁI QUÁT VỀ MICROSOFT EXCEL
IV. Cấu trúc bảng tính Excel (tt)
- Dòng (Row): Tiêu đề dòng được ký hiệu theo số thứ tự 1, 2, 3, …đến 65536
- Cột (Column): Tiêu đề cột được ký hiệu theo các chữ cái: A, B, C... đến IV (tối đa 256 cột trên một bảng tính)
- Ô (Cell): Giao của hàng và cột. Ô được xác định bằng địa chỉ ô, ký hiệu
Ví dụ: Ô C5 là ô tại vị trí cột C giao với dòng 5
- Khối ô: D5:D55
10
KHÁI QUÁT VỀ MICROSOFT EXCEL
IV. Cấu trúc bảng tính Excel (tt)
*Đổi tên Sheet, chèn - xóa một Sheet: chọn Sheet, kích chuột phải:
+ Đổi tên Sheet: Chọn Rename đặt tên mới Enter.
+ Xoá Sheet: Chọn Delete.
+ Chèn thêm Sheet: Chọn Insert Worksheet OK.
11
MỘT SỐ KHÁI NIỆM
a. Tên ô: là tên dòng và tên cột (A1)
b.Kiểu dữ liệu trong ô: trong một ô có thể có các kiểu dữ liệu sau:
+ Kiểu chuỗi (Text): là các chữ cái,chữ số, dấu chính tả,…
Ví dụ: Hoa Mai, 12A1, ‘002
+ Kiểu số (Number): là các số nguyên hay số thực (khi nhập số phải liền nhau)
Ví dụ: 120, 12, 5000,…
+ Kiểu ngày (Date), giờ (Time): là các giá trị ngày, giờ
Ví dụ:20/11/2011; 12:30:PM; 20-12-2000
Lưu ý: cần điều chỉnh lại ngày hệ thống theo các chuẩn bằng lệnh StartSetting—Control PanelRegional and Language
KHÁI QUÁT VỀ MICROSOFT EXCEL
12
- Muốn thay đổi thông số ngày tháng ta làm như sau:
B1: Vào Start/Setting/Control Panel/Regional Options/ Chọn Date/ chọn dạng thức theo ý muốn
B2: Chọn OK
B3: Chọn vùng nhập dữ liệu,
Mở Format/Cells/ Number/ date/Custom
và chọn kiểu ngày tháng và nhập liệu.
- Có thể nhập hàm = Date(YY,MM,DD)
- Nhập ngày tháng hệ thống: Ctrl + ;
- Nhập giờ hệ thống: Ctrl + Shift + ;
- Dữ liệu nhập dồn sang phải ô
13
MỘT SỐ KHÁI NIỆM (tt)
b.Kiểu dữ liệu trong ô:
+ Kiểu công thức: giá trị nhập vào ô là một biểu thức (sô, chuỗi, ngày, địa chỉ ô, …) và được viết sau dấu =
Cú pháp: =
Ví dụ: =B2+C2

KHÁI QUÁT VỀ MICROSOFT EXCEL
14
Bảng mô tả lỗi khi dữ liệu nhập công thức không hợp lệ
15
MỘT SỐ KHÁI NIỆM (tt)
c. Các phép toán:
+Phép toán số: gồm các phép toán số học: +, -,*, /, ^,%
Có độ ưu tiên: ( ), ^,*, / %, +, -
Ví dụ: =(10-4)^2+10%*10)/236.5
+ Phép toán chuỗi: có phép toán & (và) để nối 2 chuỗi với nhau. Các chuỗi khi thực hiện trong công thức phải đặt trong cặp nháy kép “Chuỗi”
Vídụ: =“Tỉnh”& “ ” &“Cà Mau”Tỉnh Cà Mau
+ Phép toán ngày: gồm các phép toán trên ngày như cộng ngày với số; trừ ngày với số.
+ Các phép toán so sánh: <, >, <=, >=, =, <>
Ví dụ: 1<2True; 3>=5False


KHÁI QUÁT VỀ MICROSOFT EXCEL
16
KHÁI QUÁT VỀ MICROSOFT EXCEL
MỘT SỐ KHÁI NIỆM (tt)
d. Địa chỉ ô:
Địa chỉ tương đối: là tên ô đó, nếu ô có chứa công thức, khi sao chép tới vị trí khác thì công thức sẻ thay đổi theo.
Ví dụ: ô D3 có công thức: =A3+C3+B3 khi sao chép công thức sang ô D4 thì công thức: =A4+C4+B4
Địa chỉ tuyệt đối: là địa chỉ khi sao chép công thức tới vị trí mới thì công thức không đổi (phía trước địa chỉ ô phải có dấu $)
Ví dụ: ô D3 có công thức: =$A$3+$C$3+$B$3 khi sao chép công thức sang ô D4 thì công thức là =$A$3+$C$3+$B$3
17
KHÁI QUÁT VỀ MICROSOFT EXCEL
MỘT SỐ KHÁI NIỆM (tt)
d. Địa chỉ ô:
Địa chỉ hỗn hợp: là địa chỉ có cả tương đối và tuyệt đối, khi sao chép công thức tới vị trí khác thì công thức thay đổi tương ứng nhưng cố định phần tuyệt đối
Tương đối về cột, tuyệt đối về hàng: C$3
Ví dụ: D3 =C$3*3E4 = D$3*3
Tương đối về hàng, tuyệt đối về cột: $C3
Ví dụ: D3 =$C3*3D4 = $C4*3


18
KHÁI QUÁT VỀ MICROSOFT EXCEL
e. Nhập dữ liệu vào ô:
- Đặt con trỏ vào ô cần nhập dữ liệuNhập dữ liệu vào Nhấn Enter (hoặc dùng các phím mũi tên di chuyển tới ô khác)
19
KHÁI QUÁT VỀ MICROSOFT EXCEL
Di chuyển trong cửa sổ bảng tính
Cách 1: Dùng chuột:
- Click chuột tại ô cần làm việc.
Cách 2: Sử dụng bàn phím.
Phím Di chuyển
/ qua phải / trái một ô.
/ lên / xuống một dòng.
Pgup / Pgdn lên / xuống một trang.
Ctrl /Ctrl  đến ô đầu/cuối của hàng hiện tại.
Ctrl /Ctrl  đến ô đầu/cuối của cột hiện tại.
Ctrl+Home/Ctrl+End lên đầu cuối bảng tính (vùng dữ liệu).
Ctrl+PageUp/PageDown chuyển đến Sheet kế trước /sau.
20
Di chuyển trong cửa sổ bảng tính (tt)
Cách 3: Dùng lệnh goto:
- Chọn lệnh Edit Goto (hoặc nhấn F5)
- Nhập địa chỉ ô muốn chuyển đến vào mục Reference (hoặc chọn từ danh sách)
- Chọn nút lệnh OK.
Cách 4: Sử dụng hộp tên (Name box):
- Di chuyển con trỏ vào hộp tên
- Gõ địa chỉ ô cần chuyển đến
- Nhấn phím Enter.
KHÁI QUÁT VỀ MICROSOFT EXCEL
21
Chọn (đánh dấu) các ô, vùng trong bảng tính.
Chọn 1 ô: Kích chuột vào ô cần chọn.
Chọn cột, dòng: kích chuột vào tên cột hay dòng cần chọn.
Chọn vùng:
+ Cách 1: Kích chuột ô đầu, kéo đến ô cuối của vùng cần chọn.
+ Cách 2: Chọn ô đầu, giữ Shift và kích chuột vào ô cuối của vùng cần chọn
Chọn nhiều vùng rời rạc.
B1: Chọn vùng 1
B2: Nhấn và giữ Ctrl khi chọn các vùng khác.
- Chọn bằng phím: ấn Shift đồng thời, ấn phím mũi tên
- Dùng chuột: Kích chuột vào ô cần mang trỏ tới
- Dùng Menu
B1: Mở EditGoto (Ctrl+G)
B2: Nhập địa chỉ ô cần đến vào mục ReferenceOK
KHÁI QUÁT VỀ MICROSOFT EXCEL
22
Sửa dữ liệu trong ô:
C1: Chọn ô cần sửa Nhấn F2Sửa dữ liệu
C2: Chọn ô cần sửa Nháy chuột vào thanh nội dungSửa dữ liệu.
KHÁI QUÁT VỀ MICROSOFT EXCEL
23
KHÁI QUÁT VỀ MICROSOFT EXCEL
Sao chép dữ liệu thông thường:
- Chọn ô hoặc nhóm ô cần sao chép
- Chọn lệnh EditCopy (Ctrl+C)
- Chọn ô (hoặc ô đầu của nhóm) cần chép đến
Chọn lệnh EditPaste (Ctrl+V)

Sao chép dữ liệu với các thông số ấn định:
- Chọn ô hoặc nhóm ô cần sao chép
- Chọn lệnh EditCopy (Ctrl+C)
- Chọn ô (hoặc ô đầu của nhóm) cần chép đến
- Chọn lệnh EditPaste Special
- Thực hiện các tuỳ chọn
- Nhấn nút OK.

24
Di chuyển dữ liệu trong ô:
- Chọn ô hoặc nhóm ô cần chuyển đi
- Chọn lệnh EditCut (Ctrl+X)
- Chọn ô (hoặc ô đầu của nhóm) cần đặt dữ liệu.
Chọn lệnh EditPaste (Ctrl+V)
Xóa dữ liệu:
- Chọn các ô cần xóa dữ liệu.
- Nhấn phím Delete hoặc EditDelete hoặc EditClearAll (contents Del).
KHÁI QUÁT VỀ MICROSOFT EXCEL
25
Phục hồi dữ liệu:
Chọn một trong các cách sau:
- Chọn lệnh Edit  Undo clear
- Nhấn Ctrl+Z
- Nhấp trên biểu tượng undo ở thanh công cụ chuẩn.
KHÁI QUÁT VỀ MICROSOFT EXCEL
26
Tìm kiếm và thay thế dữ liệu trong phạm vi bảng tính
Chọn phạm vi cần tìm kiếm
- Chọn lệnh EditFind (Ctrl+F)
- Chọn các thông số
- Thực hiện tìm kiếm
+ Find next: Tìm xuôi
+ Shift + Find Next: Tìm ngược.
KHÁI QUÁT VỀ MICROSOFT EXCEL
27
Chọn phạm vi cần tìm và thay
- Chọn lệnh EditReplace (Ctrl+H)
- Chọn các thông số
- Thực hiện tìm và thay
+ Find next: Tìm tiếp , bỏ qua ô hiện tại
+ Replace: Thay thế ô hiện tại và tìm ô tiếp theo
+ Replace all: Thay thế tất cả.
* Lưu ý: Nếu không nhập dữ liệu vào mục Replace with, Excel sẽ thay thế chuỗi rỗng.
KHÁI QUÁT VỀ MICROSOFT EXCEL
28
THIẾT LẬP BẢNG TÍNH
a. Mở tập tin mới: Có 3 cách
Cách 1: Chọn File  New
Cách 2: Nhấn tổ hợp phím Ctrl + N
Cách 3: Nhấp chuột trên biểu tượng New của thanh công cụ.
29
THIẾT LẬP BẢNG TÍNH
b. Lưu bảng tính chưa có tên
B1: Có 3 cách: Cách 1: Chọn lệnh File  Save
Cách 2: Ctrl + S
Cách 3: Kích chuột vào nút Save trên thanh công cụ.
B2: + Tại mục Save in: Chọn ổ đĩa hoặc thư mục chứa tệp tin
+ Tại mục File Name: Gõ tên cho tệp tin
B3: Chọn Save (hoặc nhấn Enter)
Lưu một bảng tính đang làm việc với tên khác
-   Chọn lệnh FileSave As
-   Chọn ổ đĩa, thư mục cần lưu tập tin
-   Gõ tên mới vào ô File name
-   Nhấn nút Save.
30
THIẾT LẬP BẢNG TÍNH
Đóng một bảng tính đang mở
- Chọn cửa sổ tài liệu chứa tập tin cần đóng
- Chọn lệnh FileClose, hoặc nhấn nút Close.
Mở một bảng tính đang có trên đĩa
- Chọn lệnh FileOpen (Hoặc nhấn phím Ctrl+O, )
- Chọn ổ đĩa, thư mục chứa tập tin bảng tính cần mở
- Chọn hoặc gõ tên tập tin cần mở vào ô File name
- Nhấn nút Open.

31
THIẾT LẬP BẢNG TÍNH
Tiêu đề đầu trang tính: là phần tiêu đề xác định nội dung trang tính.
Tạo tiêu đề chính của trang tính: xác định bảng tính có bao nhiêu cột và bao nhiêu hàng (có gộp cột, hàng lại không)
a. Đặt tên khối.
Tên khối ô bao gồm các ô kề nhau, kí hiệu: :
Ví dụ: A3:G5
b.Thay đổi kích thước cột, dòng.
Chọn cột/hàng, FormatColumns/Row Widht../Height nhập chỉ sốOk
32
THIẾT LẬP BẢNG TÍNH
c. Xóa dữ liệu ô, cột, dòng :
+ Xóa dữ liệu : phím Delete.
+ Xóa dữ liệu và cả định dạng của ô: Edit Delete.
+ Xóa hẳn ô, cột, dòng : Edit Delete.
Trường hợp xóa hẳn ô thì xuất hiện thêm các mục chọn :
- Shift Cells Left : Dồn các ô sang trái lấp vào các ô bị xóa
- Shift Cells Up : Dồn các ô lên trên khi xóa
- Entire Row : Xóa nguyên dòng có chứa ô đang bị đánh dấu.
- Entire Column : Xóa nguyên cột có chứa ô đang bị đánh dấu.
d. Chèn ô, cột, dòng :
Chọn vị trí cần chèn, vào Insert Cells Hoặc Insert Columns Hoặc Insert Rows
33
THIẾT LẬP BẢNG TÍNH
Kẻ khung cho vùng tính:
- Chọn vùng các ô cần kẻ đuờng viền
- Chọn lệnh FormatCells (hoặc ấn phải chuột chọn FormatCell)  Xuất hiện hộp thoại Format Cells Border gồm có :
+ Line Style: Chọn kiểu dáng đường viền (đường viền mản, đậm, đường viền đôi, đứt quãng…)
+ Color : Chọn màu đường viền.
+ Trong mục Presets: có 03 phần
None : Xóa bỏ nhanh đường viền.
Outline : Kẻ nhanh đường viền ngoài.
Inside : Kẻ nhanh đường viền trong vùng ô.
- Chọn OK
34
Nhập và định dạng dữ liệu
a. Nhập dữ liệu
Chọn ô cần nhập và nhập dữ liệu vào (số hoặc chữ, cồn thức,…)
b. Định dạng dữ liệu
Định dạng kí tự: Chọn dữ liệu cần định dạngFormatCell (Ctrl+1)Font
35
b. Định dạng dữ liệu

Định dạng hiển thị: Chọn dữ liệu cần định dạngFormatCell (Ctrl+1)Number



Định dạng hướng của dữ liệu: Chọn dữ liệu cần định dạngFormatCell (Ctrl+1)Alignment
36
b. Định dạng dữ liệu (tt)
Định dạng nền dữ liệu: Chọn dữ liệu cần định dạngFormatCell (Ctrl+1)Patterns

37
Thao tác trên khối dữ liệu
Tính toán trên cột hay dòng
Chọn cột hay dòng
Điền dữ liệu tự động:
Chọn vị trí cần tạo dữ liệu tự động: số dạng chuỗi ta gõ ’01 hoặc gõ 1, 2bôi den và đưa chuột tại góc phải phía dưới kéo rê.
Chọn EditFillSeria
38
Thao tác trên khối dữ liệu
Sắp xếp dữ liệu:
- Chọn dữ liệu cần sắp xếpDataSort





- Chọn dữ liệu cần sắp xếp
39
Vẽ và chèn đối tượng trong bảng tính
Chọn ViewToolbarsDrawing
40
CÁC HÀM THÔNG DỤNG
Hàm là công cụ nhằm giải quyết một công việc nhất định. Hàm gồm 2 thành phần là tên hàm và các tham biến (tham biến nằm trong cặp dấu ngoặc ( ) ). Hàm cho kết quả là một giá trị hay một thông báo lỗi
Các nhóm hàm của Excel
+ Hàm toán học
+ Hàm xử lý chuỗi ký tự (Text)
+ Hàm về ngày và giờ (Date & Time)
+ Hàm luận lý (Logic)
+ Hàm thống kê (Statistical)
+ Hàm dò tìm và tham chiếu (Lookup & Reference)
+ Hàm thông tin (Information)
+ Hàm tài chính (Financial)
+ Hàm cơ sở dữ liệu (Database)
41
Hàm có cú pháp
= (đối số 1, đối số 2, ..., đối số n)
Ví dụ: =SUM(A1,A2,B1,B2); =sum(1,2,3,4)10
Cú pháp hàm gồm ba thành phần:
+ Dấu = để Excel biết theo sau là một hàm hay công thức
+ Tên hàm: theo quy ước của Excel.
+ Đối số: là các giá trị, chuỗi, tọa độ ô, tên vùng, công thức, hoặc một hàm khác.
Các điểm cần lưu ý đối với cú pháp của hàm
Phía trước hàm phải có dấu = hoặc @
Trong hàm không được chứa khoảng trống
Có thể chứa tối đa 30 đối số hoặc không quá 255 ký tự
Nếu dùng 1 hàm làm đối số cho 1 hàm khác thì hàm làm đối số không cần phải có dấu = ở đằng trước
Các đối số phải được đặt trong cặp dấu ( ) và giữa các đối số phải được phân cách bằng dấu phẩy (,) hoặc dấu chấm phẩy (;).
42
Các hàm Toán học (Math)
- Hàm PRODUCT():
+ Công dụng: Tính tích của các đối số.
+ Cú pháp: =PRODUCT(number1,number2,...)
+ Ví dụ: =PRODUCT(3,5,20)  300
- Hàm PI():
+ Công dụng: Tính trị số của số PI.
+ Cú pháp: =PI()
+ Ví dụ: =PI() x 2 x 2  12,56637
CÁC HÀM THÔNG DỤNG
43
Các hàm Toán học (Math)
- Hàm ROUND():
+ Công dụng: Làm tròn số number đến numberdigits chữ số thập phân.
+ Cú pháp: =ROUND(number, numberdigits)
+ Đối số:
 Number: Giá trị số, địa chỉ ô chứa số.
 Numberdigits:
>=0: Làm tròn phần số thập phân.
< 0: Làm tròn phần số nguyên.
+ Ví dụ: =ROUND(12345.54321, 0) = 12346
=ROUND(12345.54321, 2) = 12345,54
=ROUND(12345.54321, -2) = 12300
44
Các hàm Toán học (Math)
- Hàm ABS():
+ Công dụng: Tính trị tuyệt đối của số number
+ Cú pháp: =ABS(number)
+ Đối số:  Number: Giá trị số, địa chỉ ô chứa số
+ Ví dụ: =ABS(-2) = 2
=ABS(12.54) = 12.54
- Hàm INT():
+ Công dụng: Tính phần nguyên của số number thập phân.
+ Cú pháp: =INT(number)
+ Đối số:  Number: Giá trị số, địa chỉ ô chứa số
+ Ví dụ: = INT(7,6) = 7
= INT(C4) = Giá trị nguyên của số ở ô C4.
45
Các hàm Toán học (Math)
- Hàm MOD( ):
+ Công dụng: Lấy phần dư của phép chia Number cho Divisor
+ Cú pháp: =MOD(number,divisor)
+ Đối số:
 Number: Giá trị số, địa chỉ của ô chứa số bị chia
 Divisor: Số chia
+ Ví dụ:
=MOD(9,2) (Cho kết quả là 1) = 1
=MOD(15,3) = 0
46
- Hàm SQRT():
+ Công dụng: Lấy căn bậc 2 của số number
+ Cú pháp: = SQRT(number)
+ Đối số:
Number: Giá trị số, địa chỉ của một ô số lấy căn bậc 2
+ Ví dụ:
=SQRT(9) -> Cho kết quả là 3

Các hàm Toán học (Math)
47
1- Hàm SUM():
+ Công dụng: Tính tổng số number1, number2, .. Number N.
+ Cú pháp: =SUM(number1, number2, ..., numberN)
+ Các đối số:
 Num: Có thể là số, tọa độ ô hoặc nhóm ô.
+ Ví dụ: =SUM(3,4,5)
=SUM(A1:A5)
=SUM(A3, B3:B6, C5:C9)
2- Hàm AVERAGE():
+ Công dụng: Tính trung bình cộng số từ number1, number2, .. Number N.
+ Cú pháp: =AVERAGE(number1, number2 ..., number N)
+ Các đối số:
 Number : Có thể là số, tọa độ ô hoặc nhóm ô
+ Ví dụ: = AVERAGE(3,4,5)
=AVERAGE(B4:B9)
=AVERAGE(C5:C9, D7:D12)
Các hàm thống kê
48
3- Hàm MAX(), MIN():
+ Công dụng:
 MAX: Tính giá trị lớn nhất trong các đối số
 MIN: Tính giá trị nhỏ nhất trong các đối số
+ Cú pháp:
=MAX(num1, num2 ..., num)
=MIN(num1, num2 ..., num)
+ Các đối số
 Numb: Có thể là số, tọa độ ô hoặc nhóm ô
+ Ví dụ: = MAX(3,4,5)
=MAX(B3:B5)
=MIN(C6:C10)
Các hàm thống kê
49
4- Hàm COUNT():
+ Công dụng: Đếm số ô chứa gía trị là số trong các đối
số Value1, Value2,…Value n.
+ Cú pháp: =COUNT(Value1, Value2,…Value n)
+ Đối số: Value: Có thể là số, tọa độ ô hoặc nhóm ô
+ Ví dụ:





=COUNT(B1:B5) = 3
=COUNT(C5) = 0
Các hàm thống kê
50
5- Hàm COUNTA():
+ Công dụng: Đếm tất cả các giá trị khác rổng là số hoặc chuỗi trong các đối số Value1, Value2,…Value n
+ Cú pháp: =COUNTA(Value1, Value2,…Value n)


+ Ví dụ:



=COUNTA(B1:B5) = 4
=COUNTA(C5) = 0
=COUNTA(C1:C5, D2:D5) = 7
Các hàm thống kê
51
Các hàm thống kê
6- Hàm COUNTIF():
+ Công dụng: Đếm số ô trong có giá trị thỏa <Điều kiện>
+ Cú pháp:=COUNTIF(,<“Điều kiện”>)
+ Ví dụ: =COUNTIF(B2:B5,”<500”)

Lưu ý : Điều kiện nằm trong cặp dấu nháy “ ”
52
7- Hàm SUMIF():
+ Công dụng: Tính tổng các gía trị ở thỏa mãn <ĐK> ở
+Cú pháp: =SUMIF(,<“ĐK”>,)
+ Ví dụ: =SUMIF(B2:B5,”<500”,D2:D5)
=SUMIF(C2:C5,”<500”,C2:C5)
Các hàm thống kê
53
Các hàm thống kê
8- Hàm RANK( ):
+ Công dụng: trả về thứ bậc cuả một số trong một
mảng Reference
+ Cú pháp: =RANK(Number,Reference,Order)
+ Đối số :
 Number: Số cần xác định thứ bậc.
 Reference: mảng các số.
 Order:
* Bằng 0 hoặc không có: Trả về thứ hạng của số number trong dãy số Reference khi xếp theo thứ tự tăng dần.
* Khác 0: Trả về thứ hạng của số number trong dãy số Reference khi xếp theo thứ tự giảm dần.
54
9. Hàm Countblank()

Công dụng : Đếm các ô là rỗng
Cú pháp: =COUNTBLANK(range)
55
Các hàm luận lý (Logic)
1 - Hàm IF():
+ Công dụng: Xác định giá trị đúng, sai của một biểu thức luận lý để thực hiện các biểu thức tương ứng
+ Cú pháp: =IF(<Điều kiện>, <Đúng>, )
+ Các đối số:
 Điều kiện: Biểu thức logic để xác định
 Đúng: Biểu thức được thực hiện khi điều kiện đúng
 Sai: Biểu thức được thực hiện khi điều kiện sai
+ Ví dụ:
=IF(15 < 24, “YES”, “NO”) = YES
56
Các hàm luận lý (Logic)
2 - Hàm AND():
+ Công dụng: Cho trị TRUE nếu mọi đối số đều TRUE
+ Cú pháp: =AND(logical1,logical2,...)
+ Ví dụ: =AND(5>3,4<8)  TRUE
- Hàm OR():
+ Công dụng: Cho trị TRUE nếu có một đối số đều TRUE
+ Cú pháp: =OR(logical1,logical2,...)
+ Ví dụ: =OR(7>8,6<3,5>1)  TRUE
- Hàm NOT():
+ Công dụng: Cho trị ngược lại với trị logic cuả đối số
+ Cú pháp: =NOT(logical)
+ Ví dụ: =NOT(5>3)  FALSE
57
Các hàm xử lý chuỗi ký tự (Text)
- Hàm LEFT() :
+ Công dụng: Trích một số number ký tự ở bên trái của chuỗi ký tự (text)
+ Cú pháp: =LEFT(text, number)
+ Các đối số:
 Text: chuỗi ký tự hoặc ô chứa chuỗi ký tự cần trích
 Number: Số ký tự cần trích trên chuỗi (>0)
+ Ví dụ:
=LEFT(“February”, 3)  Feb

58
Các hàm xử lý chuỗi ký tự (Text)

- Hàm RIGHT():
+ Công dụng: Trích một số number ký tự ở bên phải của chuỗi ký tự (text)
+ Cú pháp: =RIGHT(text,number)
+ Các đối số:
 Text: chuỗi ký tự hoặc ô chứa chuỗi ký tự cần trích
 Number: Số ký tự cần trích trên chuỗi (>0)
+ Ví dụ:
=RIGHT(“January”, 3)  ary
59
Các hàm xử lý chuỗi ký tự (Text)
- Hàm MID():
+ Công dụng: Cắt n kí tự trong chuỗi kí tự TEXT tính từ kí tự thứ m.
+ Cú pháp: =MID(TEXT,m,n)
Trong đó: m là số kí tự bắt đầu cắt.
n là số kí tự cần cắt
+ Ví dụ: =MID(“ABCDEF”,3,2)  “CD”
- Hàm TRIM():
+ Công dụng: bỏ kí tự trắng vô ích cuả chuỗi kí tự TEXT.
+ Cú pháp: =TRIM(TEXT)
+ Ví dụ:
=TRIM(“ MICROSOFT EXCEL “)  “MICROSOFT EXCEL”
60

- Hàm LEN():
+ Công dụng: Trả về độ dài của chuổi (tính cả khoảng trắng).
+ Cú pháp: =LEN(text)
+ Các đối số:  Text: Chuỗi ký tự hoặc tọa độ ô.
+ Ví dụ: =LEN(“Good, Morning!”) = 14
=LEN(3456028) = 7
Các hàm xử lý chuỗi ký tự (Text)
61
Các hàm xử lý chuỗi ký tự (Text)
Hàm UPPER()
+ Công dụng: Chuyển các ký tự trong chuỗi sang chữ hoa
+ Cú pháp: =UPPER(text)
+ Các đối số: Text: Chuỗi ký tự hoặc tọa độ ô chứa ký tự
+ Ví dụ: =UPPER(“Hello!”) = HELLO!

62
Các hàm xử lý chuỗi ký tự (Text)

Hàm LOWER()
+ Công dụng: Chuyển các ký tự trong chuỗi sang chữ thường
+ Cú pháp: =LOWER(text)
+ Các đối số: Text: Chuỗi ký tự hoặc tọa độ ô chứa ký tự.
+ Ví dụ: =LOWER(“EXCEL”) = excel

63
Các hàm xử lý chuỗi ký tự (Text)

- Hàm PROPER():
+ Công dụng: Chuyển ký tự đầu trong chuỗi sang dạng chữ hoa
+ Cú pháp: =PROPER(text)
+ Các đối số: Text: Chuỗi ký tự hoặc tọa độ ô chứa ký tự
+ Ví dụ: =PROPER(“WINWORD”) = Winword
64
Hàm TEXT():
+ Công dụng: Chuyển đổi giá trị Value thành chuổi theo dạng Format_text.
+ Cú pháp: =TEXT(Value, Format_text)
+ Ví dụ: =TEXT(3.456,”$0.00) là “$3.45”
Các hàm chuyển đổi kiểu

65
Hàm VALUE():
+ Công dụng: Chuyển đổi chuổi số Text thành số.
+ Cú pháp: =VALUE(text)
+ Ví dụ: =VALUE(“3456”) là 3456
Các hàm xử lý chuỗi ký tự (Text)

66
- Hàm NOW():
+ Công dụng: Trả về ngày giờ hiện hành trong máy tính.
+ Cú pháp: =NOW()
+ Ví dụ: Hiện giờ ở trong máy là 11 giờ 30 ngày 20 tháng 11 năm 2005. Nếu ta gõ vào hàm
NOW()  ta sẽ nhận được giá trị 20/11/2005 11:30
- Hàm TODAY():
+ Công dụng: Trả về ngày hiện hành trong máy tính.
+ Cú pháp: =TODAY()
+ Ví dụ: Hiện giờ ở trong máy là ngày 20 tháng 11 năm 2005. Nếu ta gõ vào hàm
TODAY()  ta sẽ nhận được giá trị 20/11/2005

Các hàm ngày, giờ (Date & Time)

67
Các hàm ngày, giờ (Date & Time)
- Hàm DATE():
+ Công dụng: Đổi 3 giá trị năm, tháng, ngày thành một biểu thức ngày.
+ Cú pháp: =DATE(year,month,day)
+ Ví dụ: DATE(96,12,19) --> 19/12/96
- Hàm TIME():
+ Công dụng: Đổi 3 giá trị giờ, phút, giây thành một biểu thức giờ.
+ Cú pháp: =TIME(hour,minute,second)
+ Ví dụ: TIME(10,30,45) --> 10:30:45 AM
68
Các hàm ngày, giờ (Date & Time)
- Hàm DATEVALUE():
+ Công dụng: Đổi một chuổi dạng ngày thành ngày theo dạng mã ngày của Excel
+ Cú pháp: =DATEVALUE(“date_text”)
+ Ví dụ: =DATEVALUE(“07/15/2004”)  38183
- Hàm TIMEVALUE():
+ Công dụng: Đổi một chuổi dạng giờ thành dạng mã giờ của Excel
+ Cú pháp: =TIMEVALUE(“time_text”)
+ Ví dụ: =TIMEVALUE(“10:15:30”) 0.42743

69
Các hàm ngày, giờ (Date & Time)
- Hàm DAY():
+ Công dụng: Trả về ngày trong tháng của biểu thức BtD
+ Cú pháp: = DAY(BtD)
+ Ví dụ: Hiện giờ ở trong máy là ngày 20 tháng 11 năm 2005. Nếu ta gõ vào hàm
=DAY(“20/11/2005”)  20
=DAY(TODAY())  20
- Hàm MONTH():
+ Công dụng: Trả về số chỉ tháng của biểu thức BtD
+ Cú pháp: = MONTH(BtD)
+ Ví dụ: Hiện giờ ở trong máy là ngày 20 tháng 11 năm 2005. Nếu ta gõ vào hàm
=MONTH(“20/11/2005”)  11
=MONTH(TODAY())  11
70
- Hàm YEAR():
+ Công dụng: Trả về số chỉ năm của biểu thức BtD
+ Cú pháp: = YEAR(BtD)
+ Ví dụ: Hiện giờ ở trong máy là ngày 20 tháng 11 năm 2005. Nếu ta gõ vào hàm
=YEAR(“20/11/2005”)  2005
=YEAR(TODAY())  2005
- Hàm WEEKDAY(BtD):
+ Công dụng: Trả về số chỉ thứ trong tuần của BtD
+ Cú pháp: = WEEKDAY(BtD)
+ Ví dụ: Hiện giờ ở trong máy là ngày 21 tháng 11 năm 2005. Nếu ta gõ vào hàm
= WEEKDAY(“21/11/2005”)  2
= WEEKDAY(TODAY())  2

Các hàm ngày, giờ (Date & Time)
71
Các hàm ngày, giờ (Date & Time)
- Hàm HOUR ():
+ Công dụng: Tính phần giờ cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =HOUR(Serial_number)
+ Ví dụ: =HOUR(0.42743) 10
- Hàm MINUTE():
+ Công dụng: Tính phần Phut cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =MINUTE(Serial_number)
+ Ví dụ: =MINUTE(0.42743) 15
72
Các hàm ngày, giờ (Date & Time)
- Hàm SECOND():
+ Công dụng: Tính phần giây cuả một biểu thức giờ (24 hours/day).
+ Cú pháp: =SECOND(Serial_number)
+ Ví dụ: =SECOND(0.42743)  30
73
Các hàm tìm kiếm và tham chiếu (Lookup)
1 - Hàm VLOOKUP():
+ Công dụng: Tìm kiếm giá trị Lookup_value trong cột đầu tiên của bảng Table_array và trả về giá trị tại cột col_index_num trên dòng tìm thấy.
+ Cú pháp:
=VLOOKUP(Lookup_value,table_array, col_index_num, range_lookup)
+ Đối số:
Lookup_value : Giá trị cần tìm trong cột của bảng Table array.
- Table_array: Là bảng dữ liệu ta cần tham chiếu (dưới dạng cột).
Col_index_num : là cột chứa dữ liệu trong bảng cần tham chiếu, được tính theo thứ tự từ trái sang phải và bắt đầu từ số 1.
74
- Hàm VLOOKUP(): tiếp theo
- range_lookup : gồm các trường hợp sau
* =0: khi cột đầu tiên của table_array không sắp thứ tự, khi đó VLOOKUP() sẽ tìm trị chính xác nếu không sẽ trả về trị #N/A.
* =1 hoặc không ghi : Cột đầu tiên của table_array phải được săp thứ tự tăng dần, khi đó nếu VLOOKUP() không tìm thấy trị chính xác thì trả về trị lớn nhất trong các số <= Lookup_value
75
- Hàm VLOOKUP(): tiếp theo
+ Ví dụ:





76
+ Ghi chú:
 Số cột tham chiếu phải là trị số >= 1 và không được lớn hơn số cột có trong cột tham chiếu.
 Nếu Lookup_value là chuỗi thì phải là chuỗi chính xác với dữ kiện có trong cột đầu tiên của khối tham chiếu.
 Nếu Lookup_value là số thì phải là số lớn hơn hoặc bằng với trị số nhỏ nhất trong cột đầu tiên của khối tham chiếu.
- Hàm VLOOKUP(): tiếp theo
77
Các hàm tìm kiếm và tham chiếu (Lookup)
2- Hàm HLOOKUP():
+ Công dụng: Tìm kiếm giá trị Lookup_value trong dòng đầu tiên của bảng Table_array và trả về giá trị tại dòng Row_index_num trên dòng tìm thấy
+ Cú pháp:
=HLOOKUP(Lookup_value,table_array,Row_index_num, range_lookup)
78
Hàm HLOOKUP(): tiếp theo
+ Đối số:
Lookup_value : Giá trị cần tìm trong dòng đầu tiên của bảng Table array.

Table_array: Là bảng dữ liệu ta cần tham chiếu.

Row_index_num : là dòng chứa dữ liệu trong bảng cần tham chiếu, được tính theo thứ tự từ trên xuống dưới và bắt đầu từ số 1.
- range_lookup : gồm các trường hợp sau
* =0: khi dòng đầu tiên của table_array không sắp thứ tự, khi đó HLOOKUP() sẽ tìm trị chính xác nếu không sẽ trả về trị #N/A.
* =1 hoặc không ghi : Dòng đầu tiên của table_array phải được săp thứ tự tăng dần, khi đó nếu HLOOKUP() không tìm thấy trị chính xác thì trả về trị lớn nhất trong các số <= Lookup_value

79
Ví dụ hàm HLOOKUP()
=HLOOKUP("K01";D4:F8;3;0) là 6000

80
Các hàm tìm kiếm và tham chiếu (Lookup)
- Hàm MATCH( ):
+ Cú pháp:
=MATCH(Lookup_value, table_array, Match_type)
+ Công dụng: Trả về vị trí tương đối của Lookup_value trong dãy table_array, trong đó Match_type quyết định kiểu tìm kiếm như sau :
 1: array phải được sắp tăng dần, sẽ cho kết quả là vị trí cuả ô có gía trị lớn nhất và <= Lookup_value.
 0: array sắp xếp bất kì, sẽ cho kết quả là vị trí ô có gía trị chính xác bằng Lookup_value.
 -1: array phải được sắp giảm dần, sẽ cho kết quả là vị trí ô có gía trị nhỏ nhất và >= Lookup_value .
81
VÍ DỤ về hàm Match()

=MATCH(500000;D5:D9;0) Kết quả là 4
=MATCH("TB khá";C5:C9;0) kết quả là 3
82
Các hàm tài chính
Hàm tính PV()
- cụng d?ng : Tính số tiền phải đầu tư với các định kỳ thanh toán
- Cú pháp :
= PV(Lãi suất %, định kỳ T, Tiền nhận theo kỳ, Tiền tương lai, kiểu TT)
Ví dụ 1: 1 công ty gọi đầu tư vào công ty 270 triệu đồng, trong 3 năm. Cuối năm thứ 3 bạn nhận 300 triệu đồng, lãi suất là 10%/ 1 năm. Chu?i đầu tư này có lợi hay không?
=PV(10%,3,0,300,0) -> 225.39
83
- Hàm tính tiền trả cho một khoản vay trả góp PMT.
- Chức năng : Tính khoản trả cho một khoản vay, trên cơ sở các khoản trả hàng kỳ không đổi và lãi suất không thay đổi. Khoản trả do hàm này tìm ra bao gồm cả phần trả vốn và phần trả lãi.
- Cú pháp :
= PMT (Lãi suất %, Tổng số lần thanh toán, Vốn ban đầu, giá trị tương lai, kiểu thanh toán)
Ví dụ 1: Mua 1 xe máy theo hình thức trả góp với giá 27 triệu đồng hiện tại, lãi suất là 6%/năm. Trả trong 18 tháng cả gốc và lãi, trả tiền vào cuối mỗi tháng. Tính số tiền mà người mua phải trả hàng tháng
= PMT(6%/12,18,27000000,0,0)
84
VẼ ĐỒ THỊ
+Chọn bảng số liệu.
+ Chọn Insert  Chart hoặc kích biểu tượng Chart Wizard
Thao tác qua 4 bước sau:
+Bước 1 : (Chart Type) chọn loại đồ thị, kích nút
85
+Bước 2: (Chart Suoree Data) : chọn khu vưcï chứa dữ liệu để vẽ đồ thị (Data Range) (đã chọn sẵn),chọn dãy số liệu theo hàng (Rows) hoặc theo cột (Columns), kích nút
Nháy next
86
+Bước 3 : (Chart Options) Các lựa chọn cho đồ thị :
Tại Titles
+ Chart Tilles: Đặt tiêu đề cho đồ thị
+ Category(x) Axis: Tiêu đề cho trục ngang X
+ Value(z) Axis: Tiêu đề cho trục đứng Z
- Tại Legend
+  Show Legend: Ẩn/ hiện hộp chú giải.
+ Chọn vị trí hiển thị hộp chú giải: *Bottom-dưới,
*Top-trên.
*Corner: góc.
*Right : phải
* Left – trái)
- Kích nút
87
88
+Bước 4: (Chart Location) : chọn vị trí đặt đồ thị.
 As New Sheet : Đặt đồ thị trong một Sheet mới
 As Objeet In : Đặt đồ thị trong Sheet đang soạn thảo. Kích nút Finish
* Chỉnh sửa đồ thị :
- Kích chọn đồ thị
- Tại Chart:
+ Chart Type: Chọn loại đồ thị
+ Chart Options: Điều chỉnh các lựa chọn đồ thị
+ Location: Chọn lại vị trí cho đồ thị
+ Add Data: Bổ sung vùng dữ lệu cho đồ thị
* Có thể kích chọn từng phần trong đồ thị, rồi ta kích đôi chuột tại biểuo1, xuất hiện hộp thoại, chọn màu cần thay đổi cho đối tượng.
89
Cơ sở dữ liệu
I.KHÁI NIỆM:
1.Cơ sở dữ liệu
Là một tập hợp các mẫu tin được tổ chức theo cấu trúc cột/dòng. Trong CSDL có thể liệy kê, sắp xếp hoặc tìm nhanh chóng các thông tin.
- Mỗi cột gọi là một vùng tin ( Field). Tên vùng tin nằm ở dòng đầu tiên trong CSDL.
- Các dòng còn lại gọi là mẫu tin ( Record)
90
2 . Vùng điều kiện :
+ Vùng điều kiện là khu vực khác CSDL, cùng thuộc chung trong một bảng tính, ở đó có nêu ra các điều kiện giúp ta xử lý, lựa chọn trên CSDL.
+ Trong vùng điều kiện phải gõ lại dúng tên các vùng tin trong CSDL ( trừ điều kiện là công thức), rồi nêu điều kiện ở ngay phía dưới.
3. Các loại tiêu chuẩn điều kiện
a. Tiêu chuẩn so sánh :
- Ô điều kiện chứa các toán tử so sánh kèm với các giá trị so sánh.
- Các phép so sánh : >,<,>=,<=,<>,=
91
b. Tiêu chuẩn công thức:
- Ô điều kiện có chứa các công thức (tiêu đề cuả tiêu chuẩn phải khác với tiêu đề vùng dữ liệu)
- Trong ô điều kiện cần phải lấy giá trị sau tiêu đề dữ liệu
C. Tiêu chuẩn kết hợp: And (và), Or (hoặc)
- Sử dụng phép toán And nếu các ô điều kiện khác cột
Ví dụ: Lọc ra danh sách các học sinh có điểm Văn>5 và Toán>5




Ví dụ : Lọc ra các người có lương từ 1.500.000đ đến 5.000.000đ
92
II- SẮP XẾP DỮ LIỆU
+ Chọn CSDL cần sắp xếp vào Data / Sort.
-Mục Sort by: chọn vùng ưu tiên sắp xếp chính
-Mục Then by: chọn được ưư tiên sắp xếp phụ
(.) Ascensding : sắp xếp tăng dần
(.) Descending : Sắp xếp giảm dần
(.) Header row : Sắp xếp chừa hàng tiêu đề
(.) No header row : Sắp xếp luôn cả hàng tiêu đề
- OK
Lưu ý: Muốn sắp xếp dữ liệu cho riêng một cột, các cột khác không thay đổi, ta đánh dấu chọn riêng cột này, chọn Data/ Sort: Chọn  Continue with the current selection.
93
III/ LỌC DỮ LIỆU :
1. Lọc tự động: (Auto Filter)
Đặt con trỏ trong CSDL .Chọn menu Data/Filter/Auto Filter.
Lúc này bên phải các tiêu đề cộtà xuất hiện các dấu
. Muốn lọc dữ liệu trên cột nào, ta kích dấu của cột đó để hiện các giá trị dữ liệu của cột này. Kích chọn giá trị cần lọc.
All : Hiện lại toàn bộ
Custom : Chọn.
(*) Equal : Bằng
(*) Does not Equals : Khác nhau
(*) Is greate than : Lớn hơn
(*) Is greate or Equals : Lớn hơn hoặc bằng
(*) Is less than : Nhỏ hơn …
Có thể kết hợp hai điều kiện : Add hoặc Or
* Lưư ý: Bỏ lọc tự động, chọn Data/Filter/Auto Filter.
94
IV. RÚT TRÍCH DỮ LIÊU
- Tạo tiêu chuẩn cho dữ liệu cần rút trích từ bảng dữ liệu (Tiêu chuẩn phải có tiêu đề và điều kiện giống như bảng chính)
- Sao chép tiêu đề bảng nguồn dữ liệu cần rút đến.
- Thực hiện thao tác rút trích:
+ Chọn DataFilterAdvanced filter xuất hiện hộp thoại:
95
* Cần đánh dấu teck vào mục Copy to another location
+ Trong ô List range chọn địa chỉ vùng dữ liệu cần rút trích.
+ Trong ô Criteria range chọn vùng tiêu chuẩn rút trích.
+ Trong ô Copy to chọn vùng chứa dữ liệu rút trích tới
96
V. CÁC HÀM TRÊN CƠ SỞ DỮ LIỆU:
1. Hàm DSUM()
+ Cú pháp : = Dsum(,,)
+ Công dụng : Tính tổng các số trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
2. Hàm DAVERAGE()
+ Cú pháp : = DAVERAGE(,,)
+ Công dụng : Tính trung bình cộng các số trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
97
Các hàm cơ sở dữ liệu:
3. Hàm DMAX()
+ Công dụng : Tìm giá trị lớn nhất của các số trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
+ Cú pháp : = Dmax(,,)
4. Hàm DMIN()
+ Công dụng : Tìm giá trị nhỏ nhất của các số trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
+ Cú pháp : = Dmin(,,)

98
Các hàm cơ sở dữ liệu:
5. Hàm DCOUNT()
+ Công dụng : Đếm các giá trị là số trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
+ Cú pháp = DCOUNT(, , )
6. Hàm DCOUNTA()
+ Công dụng : Đếm các giá trị khác rổng trong một cột của vùng dữ liệu thoả mãn điều kiện ở vùng tiêu chuẩn.
+ Cú pháp: = DCOUNTA(, , )