Chọn miền, cột, dòng, bảng
Chọn miền (range)
Bấm và rê chuột từ ô trên
cùng bên trái tới ô dưới cùng
bên phải của vùng (hoặc theo
các chiều khác).
Chọn cả dòng/cột
Kích chuột trái tại tên dòng
(cột) để lựa chọn cả dòng
(cột).
Nhấn thêm phím Ctrl khi
chọn để chọn các miền rời
nhau.
Chọn cả bảng tính
Kích chuột vào giao giữa hàng
tên cột và cột tên dòng
Nhập dữ liệu
Sử dụng bàn phím để nhập dữ liệu cho ô tại vị
trí con trỏ. Gõ Enter để hoàn tất
Nhập số:
Nhập bình thường
Sử dụng dấu chấm “.” làm dấu phân cách thập
phân (Regional setting là US).
Sử dụng dấu gạch chéo “/” để nhập phân số.
Viết cách phần nguyên và phần thập phân để
nhập hỗn số.
54 trang |
Chia sẻ: thanhle95 | Lượt xem: 581 | Lượt tải: 1
Bạn đang xem trước 20 trang tài liệu Bài giảng Tin học đại cương - Phần 4 - Trần Thị Bích Phương, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
BÀI GIẢNG
TIN HỌC ĐẠI CƯƠNG
GV:TRẦN THỊ BÍCH PHƯƠNG
BỘ MÔN: TIN HỌC
MAIL: ttbphuong@ctump.edu.vn
12/04/2020
1
TRƯỜNG ĐẠI HỌC Y DƯỢC CẦN THƠ
KHOA KHOA HỌC CƠ BẢN
Soạn thảo bảng tính với Microsoft Excel
NỘI DUNG
1. Khởi động Excel.
2. Màn hình làm việc.
3. Thoát khỏi Excel.
4. Khởi tạo một bảng tính.
5. Địa chỉ, miền và công thức.
6. Các thao tác soạn thảo.
7. Các thao tác với tệp.
Hệ soạn thảo bảng tính - Microsoft Excel 212/04/2020
8. Định dạng dữ liệu
9. Các hàm thông dụng
10. Cơ sở dữ liệu
Các thành phần của màn hình làm việc
Thanh tiêu đề (Title Bar).
Thanh menu (Menu Bar).
Thanh công cụ chuẩn (Standard Toolbar).
Thanh công cụ định dạng (Formating Toolbar).
Thanh công thức (Formula Bar).
Trang tính (Sheet).
256 cột (A..Z, AA,ABIV).
65536 dòng.
Một tệp (workbook) gồm nhiều trang tính (sheet).
Các thanh cuộn (scroll bars).
Thanh trạng thái (status bar).
Hàng tên cột (column heading).
Cột tên hàng (row heading).
Hệ soạn thảo bảng tính - Microsoft Excel 312/04/2020
Một số thao tác trên bảng tính
Đặt tên cho trang tính:
Kích chuột phải vào tên
trang, chọn Rename
Gõ tên rồi gõ Enter.
Bố trí trật tự các sheets:
Dùng chuột bấm và rê
tên sheet tới vị trí mong
muốn.
Hệ soạn thảo bảng tính - Microsoft Excel 412/04/2020
BẤM
RÊ
THẢ
Dịch chuyển con trỏ
Con trỏ ô (Cell poiter) là hình chữ nhật bao
quanh đường viền ô
Di chuyển: Sử dụng các phím mũi tên, Page
Up, Page Down, Home.
Nhấn Ctrl + mũi tên để di chuyển đến ô cuối
cùng (đầu tiên) của bảng tính theo chiều mũi
tên.
Kích chuột vào ô nào đó
Hệ soạn thảo bảng tính - Microsoft Excel 512/04/2020
Chọn miền, cột, dòng, bảng
Chọn miền (range)
Bấm và rê chuột từ ô trên
cùng bên trái tới ô dưới cùng
bên phải của vùng (hoặc theo
các chiều khác).
Chọn cả dòng/cột
Kích chuột trái tại tên dòng
(cột) để lựa chọn cả dòng
(cột).
Nhấn thêm phím Ctrl khi
chọn để chọn các miền rời
nhau.
Chọn cả bảng tính
Kích chuột vào giao giữa hàng
tên cột và cột tên dòng
Hệ soạn thảo bảng tính - Microsoft Excel 612/04/2020
Nhập dữ liệu
Sử dụng bàn phím để nhập dữ liệu cho ô tại vị
trí con trỏ. Gõ Enter để hoàn tất
Nhập số:
Nhập bình thường
Sử dụng dấu chấm “.” làm dấu phân cách thập
phân (Regional setting là US).
Sử dụng dấu gạch chéo “/” để nhập phân số.
Viết cách phần nguyên và phần thập phân để
nhập hỗn số.
Hệ soạn thảo bảng tính - Microsoft Excel 712/04/2020
Nhập dữ liệu
Nhập số:
123.456
-123.456 hoặc (123.456)
3/2
1 1/2 (hỗn số).
3E+7 (dạng kỹ thuật).
Nhập xâu văn bản (text):
Các xâu có chứa chữ, nhập
bình thường
Các xâu dạng số: “1234”, có
hai cách nhập:
=“1234”
‘1234
Nhập ngày tháng
Khuôn dạng: mm/dd/yyyy
(Mỹ)
Ví dụ:
02/09/2018
12/31/2018
Nhập thời gian
Khuôn dạng: hh:mm:ss
Ví dụ:
13:30:55
Hệ soạn thảo bảng tính - Microsoft Excel 812/04/2020
Địa chỉ, miền, công thức
Địa chỉ ô
Địa chỉ tương đối
Địa chỉ tuyệt đối
Miền
Khái niệm miền
Đặt tên miền
Công thức
Khái niệm
Hàm
Hệ soạn thảo bảng tính - Microsoft Excel 912/04/2020
Địa chỉ ô
Mỗi ô được xác định bởi hàng và cột chứa nó.
Địa chỉ ô = tên cột + tên hàng
Ví dụ:
Ô trong hình có địa chỉ B2 (B là cột B, 2 là hàng 2)
Địa chỉ chia làm 2 loại: tương đối và tuyệt đối
Hệ soạn thảo bảng tính - Microsoft Excel 1012/04/2020
Tên cột
Tên hàng
B2
Địa chỉ tương đối
Chỉ bao gồm tên cột viết liền tên hàng.
Ví dụ:
A1
F9
Công thức bị thay đổi theo vị trí tương đối của ô chép tới so
với ô gốc khi sao chép công thức.
Hệ soạn thảo bảng tính - Microsoft Excel 1112/04/2020
Kéo xuống Thả Kích chọn Nhấn F2
Địa chỉ tuyệt đối
Thêm dấu $ vào trước tên cột (hàng) không muốn
thay đổi khi sao chép công thức.
Ví dụ:
$A1: Luôn ở cột A
$F$9: Luôn là ô F9
Không bị thay đổi khi sao chép công thức
Hệ soạn thảo bảng tính - Microsoft Excel 1212/04/2020
Công thức sử dụng
địa chỉ tuyệt đối Sao chép công chức
Miền (range)
Tập hợp các ô
Cách viết
Đ/c ô trái trên:Đ/c ô phải dưới
B2:D5
Đ/c ô trái trên..Đ/c ô phải dưới
B2..D5
Dùng “;” phân cách các miền
rời nhau:
A2..A5 ; E2:E5
Hệ soạn thảo bảng tính - Microsoft Excel 1312/04/2020
Công thức (formula)
Sử dụng để tính toán, thống kê trên dữ liệu.
Bắt đầu bằng dấu bằng ( = )
Tiếp theo là:
Các hằng, địa chỉ,
Phép toán số học: + , - , * , / , ^
Hàm
Ví dụ:
=A1/B1+COS(A1)*SIN(B1)
Hệ soạn thảo bảng tính - Microsoft Excel 1412/04/2020
Formula bar
Có thể sử dụng thanh công thức để nhập công
thức cho tiện.
Hệ soạn thảo bảng tính - Microsoft Excel 1512/04/2020
Thanh công thức Nhập công thức
Định dạng dữ liệu
a. Thay đổi kích thước của ô.
b. Định dạng ô.
c. Sử dụng thanh công cụ định dạng.
Hệ soạn thảo bảng tính - Microsoft Excel 1612/04/2020
Number
General: Theo dữ liệu nhập.
Number: Con số.
Currency: Tiền tệ.
Accounting: Tài chính.
Date: Ngày tháng.
Time: Thời gian.
Percentage: Phần trăm.
Text: Chữ
Custom: Tuỳ biến.
Nhập xâu định dạng
Hệ soạn thảo bảng tính - Microsoft Excel 1712/04/2020
Alignment
Hệ soạn thảo bảng tính - Microsoft Excel 1812/04/2020
Trình bày dữ liệu theo chiều dọc
(horizontal) và ngang (Vertical)
General – theo dữ liệu
Left – căn lề trái
Right – căn lề phải
Top – căn lề ở đỉnh ô
Center – căn lề giữa
Bottom – căn lề ở đáy ô
Wrap text - Gói gọn dữ liệu trong ô
Shrink to fit – Tự động thay
đổi kích cỡ chữ cho vừa ô
Merge Cells – Hoà nhập các
ô đã chọn thành 1 ô
Orientation - Hướng viết chữ (tính theo độ)Text direction - chiều viết chữ
Font
Hệ soạn thảo bảng tính - Microsoft Excel 1912/04/2020
Font – Phông chữ
Font Style – Kiểu font Size – Kích thước
Underline – Gạch chân
Color – Màu sắc
Effects – Các hiệu ứng khác
Preview – Xem trước
Border
Hệ soạn thảo bảng tính - Microsoft Excel 2012/04/2020
Line Style – Kiểu đường viền
Line Color – Màu sắc đường viền
Border – Đường viền hiện, nút chìm
Patterns
Hệ soạn thảo bảng tính - Microsoft Excel 2112/04/2020
Color – Màu tô
Patterns – Mẫu tô
Các hàm thông dụng
a. Nhắc lại khái niệm công thức
b. Khái niệm hàm
c. Thiết lập dấu phân cách
d. Nhập công thức và hàm
e. Một số hàm thông dụng
Hệ soạn thảo bảng tính - Microsoft Excel 2212/04/2020
Công thức (nhắc lại)
Công thức:
Bắt đầu bởi dấu “=“
Gồm:
Địa chỉ, hằng, miền,
Toán tử
Hàm
Ví Dụ:
= B2+C2+D2
= SIN(A1) + COS(B2)
= LN(A5)
Hệ soạn thảo bảng tính - Microsoft Excel 2312/04/2020
Khái niệm hàm
Các công thức tính toán được xây dựng trước.
Cú pháp:
Tên hàm (danh sách đối số)
Đối số được phân cách bởi dấu phẩy “,” (hoặc
dấu chấm phẩy “;”)
Ví dụ: = Rank(x , range , order)
Đối số có thể là giá trị, địa chỉ, hằng,
Ví dụ: = Vlookup(Left(B1,2) , $A$13:$D$16 , 3 , 0 )
Hệ soạn thảo bảng tính - Microsoft Excel 2412/04/2020
Miền địa chỉHàm Hằng
Nhập công thức và hàm
Nhập trực tiếp vào ô
Sử dụng thanh công thức:
Kích chuột vào biểu tượng fx
hoặc shift + f3 để mở hộp
thoại chọn hàm.
Select a category: Chọn
loại hàm.
Select a function: Chọn
hàm.
Hệ soạn thảo bảng tính - Microsoft Excel 2512/04/2020
Một số hàm thông dụng
Một số hàm toán học và lượng giác (Math & Trig)
Một số hàm thống kê (Statistical)
Một số hàm xử lý văn bản (Text)
Một số hàm xử lý thời gian (Date & Time)
Một số hàm tra cứu và tham chiếu (Lookup & Reference)
Một số hàm Logic (Logical)
Hệ soạn thảo bảng tính - Microsoft Excel 2612/04/2020
Một số hàm toán học và lượng giác {1}
ABS(X) : Giá trị tuyệt đối của X
ABS(4) = ABS(-4) = 4
ABS(-4.5) = 4.5
INT(X) : Làm tròn “dưới” tới một số nguyên gần nhất
INT(-4.45) = -5
INT( 4.55) = 4
Hệ soạn thảo bảng tính - Microsoft Excel 2712/04/2020
Một số hàm toán học và lượng giác {3}
ROUND(X,N) : Làm tròn X
N - số chữ số sau dấu phân cách phần thập phân
ROUND(4.27, 1) = 4.3
ROUND(-4.27, 0) = - 4
ROUND(16.27, -1) = 20
Hệ soạn thảo bảng tính - Microsoft Excel 2812/04/2020
Một số hàm toán học và lượng giác {6}
SUM(X1,X2,)
Tổng dãy số X1,X2,
Ví dụ:
SUM(B2,C2,D2)
SUM(miền)
Tổng các số trong miền
Ví dụ:
SUM(B2:D2)
SUM(B2..D2)
Hệ soạn thảo bảng tính - Microsoft Excel 2912/04/2020
Một số hàm toán học và lượng giác {7}
SUMIF(,,)
Tính tổng các phần tử trong với điều kiện phần tử
tương ứng trong thoả mãn
Ví dụ:
F2: Tổng số lượng trái cây mà cửa hàng A bán được
Tại ô F2 ta gõ công thức: =SUMIF(A2:A5, “A”, C2:C5)
Hệ soạn thảo bảng tính - Microsoft Excel 3012/04/2020
Một số hàm thống kê {1}
COUNT(X1,X2,) hay COUNT (miền)
Đếm số ô có giá trị số trong dãy, miền
Hệ soạn thảo bảng tính - Microsoft Excel 3112/04/2020
Đếm số ô có giá trị số trong dãy
Đếm số ô có giá trị số trong miền
Một số hàm thống kê {2}
COUNTA (X1,X2,) hay COUNTA(miền)
Đếm số ô có chứa dữ liệu trong dãy, miền
Hệ soạn thảo bảng tính - Microsoft Excel 3212/04/2020
Đếm số ô có chứa giá trị số trong miền
Đếm số ô có chứa giá trị số trong dãy
Một số hàm thống kê {3}
COUNTIF(, )
Đếm số ô trong có giá trị thoả mãn .
COUNTIF(C3:C11,">=5"): Số ô có giá trị ≥ 5 trong miền C3:C11
Hệ soạn thảo bảng tính - Microsoft Excel 3312/04/2020
1
Một số hàm thống kê {4}
AVERAGE(X1,X2,) hay AVERAGE()
Trung bình cộng của dãy (miền) các ô có chứa giá trị số.
Hệ soạn thảo bảng tính - Microsoft Excel 3412/04/2020
Trung bình cộng của dãy các ô
Trung bình cộng của miền các ô
Một số hàm thống kê {5}
MAX(X1,X2,) hay
MAX()
Giá trị lớn nhất
MIN(X1,X2,) hay
MIN()
Giá trị nhỏ nhất
Hệ soạn thảo bảng tính - Microsoft Excel 3512/04/2020
Giá trị lớn nhất
Giá trị nhỏ nhất
Một số hàm thống kê {6}
RANK(X,,)
Cho thứ hạng của X trong miền
= 0 hoặc khuyết thì
sắp xếp theo chiều giảm dần,
khác 0 thì sắp xếp tăng dần.
Hệ soạn thảo bảng tính - Microsoft Excel 3612/04/2020
Thứ tự tăng dần Thứ tự giảm dần
Một số hàm xử lý văn bản {1}
LEFT(s,n) : n ký tự trái của
xâu s.
RIGHT(s,n) : n ký tự phải của
xâu s.
MID(s,m,n) : n ký tự, từ vị trí m
của xâu s.
TRIM(s) : Bỏ dấu cách thừa
trong xâu s.
LEN(s) : Độ dài xâu s.
Hệ soạn thảo bảng tính - Microsoft Excel 3712/04/2020
= TRIM(A2)B2
Một số hàm xử lý văn bản {2}
VALUE(s) : Chuyển xâu s thành số.
TEXT(value, định_dạng) : Chuyển thành xâu theo định dạng.
Ví dụ:
TEXT(“01/01/2018”, ”mmm”) = “Jan”
TEXT(1/3,"0.00") = 0.33.
LOWER(s) : Đổi xâu s thành chữ thường.
UPPER(s) : Đổi xâu s thành chữ hoa.
Hệ soạn thảo bảng tính - Microsoft Excel 3812/04/2020
A2 = ‘1234.56
B2 = VALUE(A2)
B2 = LOWER(A2)
C2 = UPPER(A2)
Một số hàm thời gian {1}
NOW() : Thời điểm hiện tại
TODAY() : Ngày hôm nay
DATE(năm, tháng, ngày) : Giá trị ngày tháng năm
Năm = 1900-9999
Hệ soạn thảo bảng tính - Microsoft Excel 3912/04/2020
A1 = NOW() B1 = TODAY() C1 = DATE(2008,11,20)
Một số hàm thời gian {2}
DAY(xâu ngày tháng) : Lấy giá trị ngày
MONTH(xâu ngày tháng) : Lấy giá trị tháng
YEAR(xâu ngày tháng) : Lấy giá trị năm
DATEVALUE(xâu ngày) : Chuyển xâu sang dữ liệu số biểu
diễn ngày tháng
Hệ soạn thảo bảng tính - Microsoft Excel 4012/04/2020
B2 = DAY(A2) C2 = MONTH(A2) D2 = YEAR(A2)
B2 = DATEVALUE(B1) I2 = DATEVALUE(I1)
Một số hàm Logic {1}
NOT(X) : Phủ định của X
AND(X1,X2,) :
- TRUE : nếu X1, X2, đều nhận giá trị TRUE
- FALSE : nếu một trong các X1, X2, nhận giá trị FALSE
OR(X1,X2,) :
- TRUE : nếu một trong các X1, X2, nhận giá trị TRUE
- FALSE : nếu X1, X2, đều nhận giá trị FALSE
Hệ soạn thảo bảng tính - Microsoft Excel 4112/04/2020
Một số hàm Logic {2}
IF( , , )
Nếu đúng, trả về
Nếu sai, trả về
Hệ soạn thảo bảng tính - Microsoft Excel 4212/04/2020
C3 = IF(B3>=14 , ”ĐẬU” , ”RỚT”)
Một số hàm Logic {3}
IF( , , )
có thể là một hàm IF khác
Hệ soạn thảo bảng tính - Microsoft Excel 4312/04/2020
C2 = IF(B2<6.5 , "TRUNG BÌNH“ , IF(B2<8 , "KHÁ“ , "GIỎI"))
Một số hàm tra cứu và tham chiếu {1}
VLOOKUP(Trị_tra_cứu, Bảng_tra_cứu, Cột_lấy_dữ_liệu, Kiểu_tra_cứu)
Lấy giá trị của ô thuộc Cột_lấy_dữ_liệu mà giá trị của ô thuộc cột đầu
tiên trong Bảng_tra_cứu có giá trị bằng Trị_tra_cứu.
Hệ soạn thảo bảng tính - Microsoft Excel 4412/04/2020
F2 = SUM(C2:E2) + VLOOKUP(B2,$A$8:$B$10,2,0)
Tổng điểm 3 môn Điểm cộng theo khu vực
Một số hàm tra cứu và tham chiếu {2}
Điểm cộng theo khu vực: VLOOKUP(B2,$A$8:$B$10,2,0)
Hệ soạn thảo bảng tính - Microsoft Excel 4512/04/2020
F2 = SUM(C2:E2) + VLOOKUP(B2,$A$8:$B$10,2,0)Trị_tra_cứu : B2
Bảng_tra_cứu : $A$8:$B$10
Cột_tra_cứu : 2
Kiểu_tra_cứu :
0 (false):
- So khớp
- Vùng tra cứu không cần
sắp xếp
1 (true):
- So gần khớp (tìm giá trị
“gần nhất”)
- Vùng tra cứu phải được
sắp xếp sẵn
Một số hàm tra cứu và tham chiếu {3}
HLOOKUP(Trị_tra_cứu, Bảng_tra_cứu, Hàng_lấy_dữ_liệu, Kiểu_tra_cứu)
Giống hàm VLOOKUP nhưng dữ liệu được xử lý theo hàng
Hệ soạn thảo bảng tính - Microsoft Excel 4612/04/2020
F2 = SUM(C2:E2) + HLOOKUP(B2,$A$8:$B$10,2,0)
Tổng điểm 3 môn Điểm cộng theo khu vực
Một số hàm tra cứu và tham chiếu {4}
Điểm cộng theo khu vực: HLOOKUP(B2,$A$8:$B$10,2,0)
Hệ soạn thảo bảng tính - Microsoft Excel 4712/04/2020
F2 = SUM(C2:E2) + VLOOKUP(B2,$A$8:$B$10,2,0)Trị_tra_cứu : B2
Bảng_tra_cứu : $A$8:$B$10
Hàng_tra_cứu : 2
Cơ sở dữ liệu
a. Khái niệm về CSDL
b. Sắp xếp
c. Lọc dữ liệu
d. Các hàm thông dụng trong CSDL
Hệ soạn thảo bảng tính - Microsoft Excel 4812/04/2020
Lọc dữ liệu
Mục đích: Lấy ra những bản ghi thoả mãn
điều kiện nhất định.
Có thể lọc theo 2 cách:
AutoFilter: Excel hỗ trợ điều kiện lọc
Advanced Filter: người sử dụng tự định điều
kiện lọc.
Hệ soạn thảo bảng tính - Microsoft Excel 4912/04/2020
Lọc dữ liệu dùng AutoFilter
Chọn miền CSDL gồm
cả dòng tên trường
Hệ soạn thảo bảng tính - Microsoft Excel 5012/04/2020
Click Menu Data – Filter
– AutoFilter
Click chuột vào mũi tên:
All: hiện lại mọi bản ghi
Top 10: các giá trị lớn
nhất
Custom: tự định điều
kiện lọc
Các giá trị của cột
Lọc dữ liệu dùng AutoFilter (tiếp)
Nếu chọn Custom sẽ hiện hộp thoại Custom
AutoFilter để người sử dụng tự định điều kiện lọc:
Hệ soạn thảo bảng tính - Microsoft Excel 5112/04/2020
Ví dụ: Lọc những bản ghi thoả mãn năm sinh < 1989 hoặc ≥ 1990
Lọc dữ liệu dùng Advanced Filter
Bước 1: Xác định miền điều kiện
Dòng đầu ghi tên trường để định điều kiện, chú ý phải giống hệt tên
trường của miền CSDL, tốt nhất là copy từ tên trường CSDL.
Các dòng tiếp dưới ghi điều kiện: các điều kiện cùng dòng là phép
AND, các điều kiện khác dòng là phép OR.
Hệ soạn thảo bảng tính - Microsoft Excel 5212/04/2020
Hệ soạn thảo bảng tính - Microsoft Excel 5312/04/2020
Lọc dữ liệu dùng Advanced Filter – Ví dụ
Lọc ra những bản ghi thỏa điều kiện:
Năm sinh < 1989 Năm sinh = 1990 1988 < Năm sinh <= 1990
Năm sinh < 1989
hoặc
Năm sinh >=1990
Năm sinh > 1989
và
Quê quán = “Nghệ An”
Năm sinh > 1989
hoặc
Quê quán = “Quảng Trị”
Lọc dữ liệu dùng Advanced Filter (tiếp)
Bước 2: Thực hiện lọc
Vào menu Data – Filter – Advanced Filter
Hệ soạn thảo bảng tính - Microsoft Excel 5412/04/2020
Hiện kết quả lọc ngay tại miền
dữ liệu
Hiện kết quả lọc ra nơi khác
Chọn miền CSDL
Chọn miền điều kiện
Chọn miền hiện kết quả
Chỉ hiện 1 bản ghi trong số những
kết quả trùng lặp