Bảng Tính Cơ Bản Với Microsoft Excel | Môn Lập trình vba excel - Trường Cao đẳng Công nghiệp quốc phòng

lOMoAR cPSD| 58605085
CHƯƠNG IV. SỬ DỤNG BẢNG TÍNH CƠ BẢN Thời gian: 29 giờ I. MỤC TIÊU
Học xong chương này, người học có khả năng: -
Trình bày ược một số kiến thức cơ bản về bảng tính, trang tính; về sử dụng phần mềm Microsoft Excel; -
Sử dụng ược phần mềm xử lý bảng tính Microsoft Excel ể tạo bảng tính, trang tính; nhập và ịnh
dạng dữ liệu; sử dụng các biểu thức toán học, các hàm cơ bản ể tính toán các bài toán thực tế.
II. NỘI DUNG 1. Kiến thức cơ bản về bảng tính (Workbook)
1.1. Khái niệm bảng tính
1.2. Các bước xây dựng bảng tính thông thường
2. Sử dụng Microsoft Excel
2.1. Làm việc với phần mềm Microsoft Excel
- Excel là phần mềm bảng tính (còn gọi là bảng tính iện tử) thuộc bộ phần mềm văn phòng
Office, do hãng phần mềm Microsoft (Mỹ) sản xuất.
- Đặc trưng (nổi bật) của Excel: tự ộng tính toán lại kết quả khi có dự thay ổi dữ liệu nguồn.
2.1.1. Mở, óng phần mềm - Giống Word.
2.1.2. Giao diện Microsoft Excel
- Tương tự Word, khác là cửa sổ soạn thảo ược chia sẵn thành các cột và các dòng. * Tệp tin excel:
- Có kiểu là .xls hoặc .xlsx
- Một tập ược gọi là một Workbook hoặc là Book
- Mỗi Book có một hoặc nhiều trang tính gọi là Worksheet hoặc Sheet
- Mỗi Sheet luôn chia sẵn các cột và các dòng; số lượng theo phiên bản XLS/ XLSX như sau:
256/ 16384 cột và 65.536/ 1.048.576 dòng. Các cột có tên cột là A, B, C,... ến IV/ XFD. Các dòng có tên
là 1, 2, 3, .... ến 65.536/ 1.048.576.
2.2. Thao tác trên tập tin bảng tính
Gồm 06 thao tác: Mở mới, Đặt tên; Ghi lưu; Đóng tệp; Mở tệp ã có; Xóa tệp: Được thực hiện tương tự như Word.
2.2.1. Mở tập tin bảng tính 2.2.2. Lưu bảng tính 2.2.3. Đóng bảng tính
3. Thao tác với ô
3.1. Các kiểu dữ liệu
- Data types, Input and Modify data in Excel
- Data types: có 05 kiểu dữ liệu
+ Text: Văn bản (chữ): là tất cả các ký tự.
+ Number (numberic): Số, thập phân.
+ Logic (logical): Lô-gíc, chỉ nhận giá trị là úng (True) hoặc sai (False)
+ Date: Ngày tháng, dữ liệu nhận kiểu dd/MM/yyyy hoặc mmm-yy
+ Kiểu công thức (Formular): Là các công thức tính toán do người dùng tạo lập dựa trên ịa chỉ (ô) và các
toán tử (phép toán: +; -; *; /; %; & - nối chuỗi) hoặc là các hàm (Function) của Excel. 3.2. Cách nhập dữ liệu
* Nhập: Chuyển trỏ viết ến ô cần nhập dữ liệu và tiến hành nhập dữ liệu; nhập xong gõ phím enter. Lưu ý:
- Với dữ liệu chữ ở dạng số (ví dụ dữ liệu iện thoại di ộng), trước khi nhập phải nhập dấu nháy ơn (').
- Với kiể công thức: Luôn bắt ầu là dấu bằng (=).
* Chỉnh sửa dữ liệu: Chuyển trỏ viết ến ô cần sửa; bấm F2 ể sửa trực tiếp trong ô hoặc nhấp chuột vào vị
trí cần sửa trên thanh công thức (fx); sửa xong bấm phím enter. lOMoAR cPSD| 58605085 2
3.3. Một số thao tác khác
- Chọn ô, vùng dữ liệu: Kéo chuột hoặc Shift + Arrows - Chọn cột, dòng: Bấm chuột vào tên cột, dòng.
- Thêm/ Xóa cột, dòng; vùng ô. Chọn vị trí cần làm việc; sau ó nhấp chuột phải và chọn lệnh tương ứng.
- Điều chỉnh ộ lớn của Cột/ Dòng: Kéo chuột tại vụ trí giới hạn phải của cột hoặc giới hạn dưới của dòng.
- Đổi tên Sheet: Nhấp chuột phải và tên Sheet; chọn Rename; gõ têm mới; bấm enter.
- Xóa Sheet: Nhấp chuột phải và tên Sheet; chọn Delete; chọn Yes.
3.3. Chỉnh sửa dữ liệu 3.3.1. Xóa dữ liệu
3.3.2. Khôi phục dữ liệu
4. Làm việc với trang tính (Worksheet) 4.1. Dòng và cột 4.1.1. Thêm dòng và cột 4.1.2. Xoá dòng và cột
4.1.3. Hiệu chỉnh kích thước ô, dòng, cột
4.1.4. Ẩn/hiện, cố ịnh (freeze)/ thôi cố ịnh (unfreeze) tiêu ề dòng, cột 4.2. Trang tính
4.2.1. Tạo, xóa, di chuyển, sao chép các trang tính
4.2.2. Thay ổi tên trang tính
4.2.3. Mở nhiều trang tính
4.2.4. Tính toán trên nhiều trang tính
5. Định dạng ô, dãy ô
5.1. Định dạng kiểu số, ngày, tiền tệ
5.2. Định dạng văn bản
5.3. Căn chỉnh, hiệu ứng viền
6. Biểu thức và hàm
6.1. Biểu thức số học
6.1.1. Khái niệm biểu thức số học
Tạo lập do người sử dụng bằng cách sử dụng ịa chỉ và tác toán tử.
6.1.2. Tạo biểu thức số học ơn giản
6.1.3. Các lỗi thường gặp 6.2. Hàm
6.2.1. Khái niệm hàm, cú pháp hàm, cách nhập hàm
- Có thể coi là các lệnh của excel, do excel ịnh nghĩa và ặt tên
- Thường hàm có TÊN_HÀM(tham_số1, tham_số2,....) 6.2.2. Toán tử so sánh =, < ,
> Cho kết quả là giá trị logic: =: Bằng >: Lớn hơn < nhỏ hơn < >: Khác
>= Lớn hơn hoặc bằng
<= Nhỏ hơn hoặc bằng % tính phần trăm & nối chuỗi. Cách ánh số thứ tự:
Cách 1:dùng IF lồng SUBTOTAL lOMoAR cPSD| 58605085 3
-Như bảng dưới là cách ánh số thứ tự lồng IF và SUBTOTAL nghĩa là ô Họ tên nếu có dự liệu ô STT sẽ ược tự
ộng ánh nếu không có sẽ ể trống. Cách 2:sử dụng hàm ROW Ví dụ:=ROW()-4
Giải thích: () không dữ liệu và -4 là trừ i hàng số 4 và bắt ầu từ số 1. Cách 3:
6.3. Các hàm cơ bản a) SUM
- Syntax: =SUM(a1, a2, ..., an)
- Ý nghĩa: Cho kết quả là tổng a1, a2, ..., an.
- Ví dụ: =sum(7,5) cho kết quả là 12
- Hình ảnh mình hoạ:=SUM(F5:H5) sẽ cho kết quả là 27 từ F5 ến H5. lOMoAR cPSD| 58605085 4 b) AVERAGE
- Syntax: =AVERAGE(a1, a2, ..., an)
- Ý nghĩa: Cho kết quả là trung bình cộng a1, a2, ..., an.
- Ví dụ: =AVERAGE(7,5) cho kết quả là 6
- Hình ảnh minh hoạ: =AVERAGE(D5:G5) (D5:G5 là từ ô D5 ến G5 gồm 7,8,1,9 sẽ cho ra kết quả trung bình là 6,3. c) MIN
- Syntax: =MIN(a1, a2, ..., an)
- Ý nghĩa: Cho kết quả là số nhỏ nhất trong các số a1, a2, ..., an.
- Ví dụ: =MIN(7,5) cho kết quả là 5
- Hình ảnh minh hoạ:=MIN(D5:D9) sẽ cho ra kết quả của ô nhỏ nhất là 1. lOMoAR cPSD| 58605085 5 d) MAX
- Syntax: =MAX(a1, a2, ..., an)
- Ý nghĩa: Cho kết quả là số lớn nhất trong các số a1, a2, ..., an.
- Ví dụ: =MAX(7,5) cho kết quả là 7
- Hình ảnh minh hoạ:=MAX(D5:D9) sẽ cho ra kết quả số lớn nhất là 9. ) COUNT
- Syntax: =COUNT(a1, a2, ..., an) hoặc =COUNT(vùng) -
Ý nghĩa: Cho kết quả là số ô chứa dữ liệu số trong vùng e) COUNTA
- Syntax: =COUNTA(a1, a2, ..., an)
- Ý nghĩa: Cho kết quả là số ô chứa dữ liệu trong vùng g) ROUND - Syntax: =Round(a, n)
- Ý nghĩa: Làm tròn số thập phân a với n chữ số thập phân - Ví dụ: =round(6.6666, 2)
cho kết quả là 6.67 h) INT - Syntax: =Int(a)
- Ý nghĩa: Cho phần nguyên của số thập phân a. - Ví dụ: =Int(6.6666) cho kết quả là 6 i) MOD - Syntax: =Mod(a, n) lOMoAR cPSD| 58605085 6
- Ý nghĩa: Cho kết quả là tổng phần dư phép chia a cho n. - Ví dụ: =Mod(7, 4) cho kết quả là 3 k) RANK - Syntax: =Rank(a, Vùng)
- Ý nghĩa: Cho biết thứ hạng của số a trong Vùng.
ĐỊA CHỈ Ô, VÙNG DỮ LIỆU
* Địa chỉ là gì? Là tọa ộ giao giữa cột và dòng.
- Địa chỉ ô: Giao một cột với một dòng. Ví dụ D5, H9,..
- Địa chỉ vùng ược xác ịnh là Địa chỉ ô ầu : ịa chỉ ô cuối. Ví dụ A5:G8 - Địa chỉ tương ối: Cách xác ịnh và viết
như trên là ịa chỉ tương ối.
- Địa chỉ tuyệt ối: Xác ịnh như ịachỉ tương ối nhưng khi viết thêm dấu $ ở phía trước tên cột, tên dòng. Ví dụ: + $A$5 tuyệt ối ô A5 + $A5 tuyệt ối cột A + A$5 tuyệt ối dòng 5 6.4. Hàm iều kiện IF
- Syntax: =If(Điều_kiện, Giá_trị_a_nếu_ĐK_ úng, Giá_trị_b_nếu_ iều_kiện_sai) -
Ví dụ: Nếu ô D3 chứa dữ liệu số thì hàm
=If(D3< 15, "Nhi ồng", "Không phải nhi ồng").
6.5. Các hàm logic (AND, OR) Gi á trị Kết quả X Y And(X,Y) Or(X,Y) True True True True True False False True False True False True False False False False
6.6. Các hàm ngày (DAY, MONTH, YEAR, NOW) a) Today()
- Cú pháp (Syntax): =today()
- Cho ngày, tháng, năm hiện tại.
- Thí dụ: =today() cho kết quả là 09/11/2022.(vì Today là hôm nay nên trong () không có dữ liệu). b) Day()
- Cú pháp (Syntax): =day(BT_ngày)
- Cho NGÀY của biểu thức ngày (BT_ngày).
- Thí dụ: =day("09/11/2022") cho kết quả là 09 (C1 là ô chứa ngày tháng năm và trong ví dụ là 09/11/2022) c) Month()
- Cú pháp (Syntax): =month(BT_ngày) - Cho THÁNG của biểu thức ngày (BT_ngày).
- Thí dụ: =month("09/11/2022") cho kết quả là 11(C1 là ô chứa ngày tháng
năm và trong ví dụ là 09/11/2022) lOMoAR cPSD| 58605085 7 d) Year()
- Cú pháp (Syntax): =year(BT_ngày) - Cho NĂM của biểu thức ngày (BT_ngày).
- Thí dụ: =year("09/11/2022") cho kết quả là 2022 (C1 là ô chứa ngày tháng
năm và trong ví dụ là 09/11/2022) ) Now()
- Cú pháp (Syntax): =now()
- Cho ngày/tháng/năm giờ:phút:giây hiện tại ( ồng hồ máy tính).
- Thí dụ: =now() cho kết quả là 09/11/2022 20:05 (NOW nghĩa là ngay bây giờ và hiện tại sẽ cho ra kết quả
vào úng thời iểm mà bạn nhập hàm NOW vào ô tính).
6.7. Các hàm chuỗi (LEFT, RIGHT, MID, LEN, UPPER, PROPER, LOWER, VALUE) a) Left()
- Cú pháp (Syntax): =left(chuoi,n) - Cho n ký tự phía trái của chuoi.
- Thí dụ: =left("Nguyễn Xuân Lộc",6) cho kết quả là "Nguyễn".(nghĩa là sẽ nhặt chữ Nguyễn trong chuỗi
Nguyễn Xuân Lộc gồm 6 chữ ó là Nguyễn).(trong ó ô A1 là ô mà chứa vùng dữ liệu mà bạn muốn nhặt chữ ra
ngoài và 6 là chuỗi chữ mà bạn ếm ược ở trong chuỗi ó)(nếu bạn muốn lấy sang chữ tiếp có dấu cách thì phải
tính và ếm cả dấu cách). b) Right()
- Cú pháp (Syntax): =Right(chuoi,n) - Cho n ký tự phía phải của chuoi.
- Thí dụ: =right("Nguyễn Xuân Lộc ",5) cho kết quả là "n Lộc".(giải thích:giống với hàm left nhặt ra chữ n
Lộc trong chuỗi Nguyễn Xuân Lộc gồm 4 chữ có dấu cách tổng là 5 và cho ra ược kết quả là n Lộc). c) Mid()
- Cú pháp (Syntax): =mid(chuoi,k,n)
- Cho n ký tự của chuoi, bắt ầu từ vị trí thứ k.
- Thí dụ: =mid("Nguyễn Xuân Lộc",8,4) cho kết quả là "Xuân"(Giải thích: giống với hàm Right và Left tính
cả dấu cách và số 8 là chuỗi bắt ầu ếm trên ví dụ trên ếm bắt ầu từ chữ X và 4 là số ký tự mà bạn muốn lấy trên
ví dụ là lấy 4 chữ Xuân và ưa ra kết quả là Xuân). lOMoAR cPSD| 58605085 8 d) Len()
- Cú pháp (Syntax): =len(chuoi) - Cho biết ộ dài của chuoi.
- Thí dụ: =len("Nguyễn Xuân Lộc") cho kết quả là 15.(A1 là ô mà chứa dữ liệu mà bạn muốn ếm chuỗi). ) Upper()
- Cú pháp (Syntax): =upper(chuoi) - Chuyển chuoi thành CHỮ HOA.
- Thí dụ: =upper("Nguyễn Xuân Lộc") cho kết quả là "PHAM HUU HAO".(A1 là ô chứa dữ liệu mà bạn
muốn chuyển từ viết thường sang viết in HOA toàn bộ). e) Lower()
- Cú pháp (Syntax): =lower(chuoi) - Chuyển chuoi thành chữ thường.
- Thí dụ: =lower("Nguyễn Xuân Lộc") cho kết quả là "nguyễn xuân lộc".(A1 là ô chứa dữ liệu mà bạn muốn
chuyền sang viết thường toàn bộ cho dù nó là viết hoa hoặc thường ở tất cả vị trí nào trong ó) g) Proper()
- Cú pháp (Syntax): =proper(chuoi)
- Viết Hoa ký tự ầu tiên của mỗi từ trong chuoi.
- Thí dụ: =lower("nGuyỄn XuÂn Lộc") cho kết quả là "Nguyễn Xuân Lộc".(A1 là ô chứa dữ liệu mà bạn
muốn chuyển sang chữ viết HOA toàn bộ như ở ví dụ là chuỗi chữ “nGuyỄn XuÂn Lộc” có cả viết thường viết
hoa thì vẫn ưa ra kết quả là Nguyễn Xuân Lộc ) tương tự như trên các hàm UPPER,LOWER cũng như vậy h) Trim()
- Cú pháp (Syntax): =Trim(chuoi)
- Chuẩn dữ liệu chuoi (cắt hết khoảng cách 2 ầu, loại bỏ khoảng cách thừa giữa các từ trong chuoi. - Thí dụ:
=trim(" Nguyễn Xuân Lộc ") cho kết quả là "Nguyễn Xuân Lộc". i) Value() lOMoAR cPSD| 58605085 9
- Cú pháp (Syntax): =Value(chuoi_so) - Đổi chuoi_so thành Số.
- Thí dụ: =value("0123456") cho kết quả là 123456. 6.8.
Các hàm có iều kiện (COUNTIF, SUMIF) a) Countif() -
Cú pháp (Syntax): =countif(vung, dieu_kien) - Đếm số ô trong vung
chứa giá trị theo dieu_kien. -
Ví dụ:=COUNTIF($E$4:$E$10;D18) ô E4 ến E10 ể ở giá trị tuyệt
ối(F4) cho ra kết quả ếm số lượng loại A là 2. b) Countifs() -
Cú pháp (Syntax): =countifs(vung_1, dieu_kien_1,...,vung_n,
dieu_kien_n) - Đếm số ô thỏa mãn các iều kiện: vung_1, dieu_kien_1,...,vung_n,
dieu_kien_n. c) Sumif() -
Cú pháp (Syntax): =sumtif(vung, dieu_kien, [,vung_gia_tri]) - Tính
tổng trong vung_gia_tri nếu trong vung thoả mãn dieu_kien. -
Ví dụ: =SUMIF($E$4:$E$10;D18;$H$4:$H$10) dấu $ là ở giá trị tuyệt
ối và ưa ra kết quả loại của loại A tổng lương nhận của loại A là 5205. lOMoAR cPSD| 58605085 10
6.9. Các hàm tìm kiếm (VLOOKUP, HLOOKUP) a) Vlookup()
- Cú pháp (Syntax): =vlookup(gia_tri, vung, cot, dieu_kien)
- Cho kết quả ở cột cot của vung nếu tìm thấy gia_tri trong vung; dieu_kien là 0 (hoặc false)
nếu yêu cầu tìm chính xác, ngược lại là 1 hoặc (true) nếu tìm tương ối. Gia_tri tìm trong cột
ầu tiên của vung. b) Hookup()
- Cú pháp (Syntax): =hlookup(gia_tri, vung, dong, dieu_kien)
- Cho kết quả ở cột dong của vung nếu tìm thấy gia_tri trong vung; dieu_kien là 0 (hoặc
false) nếu yêu cầu tìm chính xác, ngược lại là 1 hoặc (true) nếu tìm tương ối. Gia_tri tìm
trong dòng ầu tiên của vung. 7. Biểu ồ 7.1. Tạo biểu ồ
- Đồ họa thể hiện dữ liệu bằng hình ảnh sinh ộng theo các hình cột (columns), ường thẳng (lines), hình bánh (Pie),...
- Các bước tạo biểu ồ:
+ Tạo bảng dữ liệu (2 chiều - 2D)
+ Chọn bảng (vùng dữ liệu) + Chọn kiểu biểu ồ + Chỉnh sửa biểu ồ.
7.2. Chỉnh sửa, cắt dán, di chuyển, xóa biểu ồ
8. Kết xuất và phân phối trang tính, bảng tính 8.1. Trình bày trang tính ể in - Page layout/ Page setup.
- Lựa chọn như word ( ơn giản hơn word). Trong excel ơn vị o lường tính là Inch, không khải cm (1 inch = 2.54 cm).
- Thường phông chữ trong bảng nhỏ hơn văn bản word (cỡ chữ 8 - 12) 8.2. Kiểm tra và in 8.3. Phân phối trang tính