Bài giảng Tin học đại cương - Chương 5: Soạn thảo bảng tính với Microsoft Excel - Trần Thị Bích Phương

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).
 
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 3
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ẤM
 Bố trí trật tự các sheets: 
 Dùng chuột bấm và rê RÊ
 tên sheet tới vị trí mong 
 muốn. THẢ
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 4
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 đó
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 5
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 6
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ố.
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 7
Nhập dữ liệu
 Nhập số:  Nhập ngày tháng
  123.456
  Khuôn dạng: mm/dd/yyyy 
  -123.456 hoặc (123.456) (Mỹ)
  3/2
  Ví dụ:
  1 1/2 (hỗn số).
  02/09/2018
  3E+7 (dạng kỹ thuật).
  12/31/2018
 Nhập xâu văn bản (text):
  Các xâu có chứa chữ, nhập  Nhập thời gian
 bình thường  Khuôn dạng: hh:mm:ss
  Các xâu dạng số: “1234”, có  Ví dụ:
 hai cách nhập:
  13:30:55
  =“1234”
  ‘1234
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 8
Đị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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 9
Đị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ụ: Tên cột
 B2
 Tên hàng
 Ô 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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 10
 Đị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.
 Kéo xuống Thả Kích chọn Nhấn F2
 12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 11
Đị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
 Công thức sử dụng 
 địa chỉ tuyệt đối Sao chép công chức
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 12
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 13
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)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 14
Formula bar
 Có thể sử dụng thanh công thức để nhập công 
 thức cho tiện.
 Thanh công thức Nhập công thức
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 15
Đị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.
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 16
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 17
Alignment
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 ô
Text direction - chiều viết chữ Orientation - Hướng viết chữ (tính theo độ)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 18
Font
 Font Style – Kiểu font Size – Kích thước
Font – Phông chữ
Color – Màu sắc
Underline – Gạch chân
Effects – Các hiệu ứng khác
Preview – Xem trước
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 19
Border
 Border – Đường viền hiện, nút chìm
Line Style – Kiểu đường viền
Line Color – Màu sắc đường viền
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 20
Patterns
 Color – Màu tô
 Patterns – Mẫu tô
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 21
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 22
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)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 23
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àm Miền địa chỉ Hằng
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 24
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.
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 25
 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)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 26
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 27
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 28
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)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 29
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)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 30
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
 Đếm số ô có giá trị số trong dãy
 Đếm số ô có giá trị số trong miền
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 31
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
 Đếm số ô có chứa giá trị số trong dãy
 Đếm số ô có chứa giá trị số trong miền
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 32
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
 1
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 33
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ố.
 Trung bình cộng của dãy các ô
 Trung bình cộng của miền các ô
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 34
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
 Giá trị lớn nhất
 Giá trị nhỏ nhất
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 35
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.
 Thứ tự tăng dần Thứ tự giảm dần
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 36
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.
 B2 = TRIM(A2)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 37
 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. C2 = UPPER(A2)
 B2 = LOWER(A2)
 B2 = VALUE(A2)
 A2 = ‘1234.56
 12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 38
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
 A1 = NOW() B1 = TODAY() C1 = DATE(2008,11,20)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 39
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
 B2 = DAY(A2) C2 = MONTH(A2) D2 = YEAR(A2)
 B2 = DATEVALUE(B1) I2 = DATEVALUE(I1)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 40
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 41
Một số hàm Logic {2}
 IF( , , )
  Nếu đúng, trả về 
  Nếu sai, trả về 
 C3 = IF(B3>=14 , ”ĐẬU” , ”RỚT”)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 42
Một số hàm Logic {3}
 IF( , , )
  có thể là một hàm IF khác
 C2 = IF(B2<6.5 , "TRUNG BÌNH“ , IF(B2<8 , "KHÁ“ , "GIỎI"))
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 43
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.
 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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 44
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)
 Trị_tra_cứu : B2 F2 = SUM(C2:E2) + VLOOKUP(B2,$A$8:$B$10,2,0)
 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ị 
 Cột_tra_cứu : 2 “gần nhất”)
 - Vùng tra cứu phải được 
 sắp xếp sẵn
 Bảng_tra_cứu : $A$8:$B$10
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 45
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
 Tổng điểm 3 môn Điểm cộng theo khu vực
 F2 = SUM(C2:E2) + HLOOKUP(B2,$A$8:$B$10,2,0)
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 46
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)
 Trị_tra_cứu : B2 F2 = SUM(C2:E2) + VLOOKUP(B2,$A$8:$B$10,2,0)
 Bảng_tra_cứu : $A$8:$B$10
 Hàng_tra_cứu : 2
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 47
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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 48
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.
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 49
 Lọc dữ liệu dùng AutoFilter
  Chọn miền CSDL gồm 
 cả dòng tên trường
 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
 12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 50
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:
 Ví dụ: Lọc những bản ghi thoả mãn năm sinh < 1989 hoặc ≥ 1990
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 51
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.
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 52
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 Năm sinh > 1989
 hoặc và hoặc
 Năm sinh >=1990 Quê quán = “Nghệ An” Quê quán = “Quảng Trị”
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 53
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
 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
12/04/2020 Hệ soạn thảo bảng tính - Microsoft Excel 54