TẬP HUẤN TIN HỌC giáo trình Excel

Chu Quốc Tài
Văn Quan 11-6-2009
CHƯƠNG TRÌNH BẢNG TÍNH MICROSOFT EXCEL
Chu Quốc Tài
§1. CÁC KHÁI NIỆM CƠ BẢN VÀ THAO TÁC BAN ĐẦU VỚI BẢNG TÍNH EXCEL
I. KHỞI ĐỘNG – THOÁT KHỎI EXCEL
Chương trình Excel được khởi động và thoát khỏi như mọi phần mềm trong Window.
Chu Quốc Tài
Cửa sổ làm việc của Excel
Chu Quốc Tài
II. KHÁI NIỆM CƠ BẢN:
1. Work Book, Sheet, Cell
- Word book (hay book): Là một file chứa các bảng tính (Sheets), trong một book gồm có nhiều Sheets.
- Mỗi Sheet: là một bảng tính rất lớn bao gồm 256 cột được đánh thứ tự theo chữ cái (A,B,C,...) và có 65.536 hàng đánh thứ tự theo số (1,2,3,...).
- Một Cell (hay còn gọi là một ô): Được ứng với một cột và một hàng. Mỗi Cell đều có toạ độ (địa chỉ) tương ứng là tên cột, tên hàng. VD: D5, E10 ...
2. Cách nhập dữ liệu vào bảng tính.
- Đặt con trỏ vào từng ô và nhập dữ liệu, khi kết thúc nhập cho ô nào đó thì ấn phím Enter để dứt lệnh.
Chu Quốc Tài
II. CÁC THAO TÁC CƠ BẢN:
1. Tạo một tệp mới:
C1: Kích chuột vào biểu tượng New trên thanh công cụ chuẩn
C2: Ấn tổ hợp phím Ctrl+N
C3: Vào menu File/New…/Workbook
2. Mở một tệp đã ghi trên ổ đĩa:
C1: Kích chuột vào biểu tượng Open trên thanh công cụ chuẩn
C2: Ấn tổ hợp phím Ctrl+O
C3: Vào menu File/Open…
Chu Quốc Tài

1. Chọn nơi chứa tệp
2. Chọn tệp cần mở
3. Bấm nút Open để mở tệp
Bấm nút Cancel để hủy lệnh mở tệp
Hộp thoại Open
Chu Quốc Tài
3. Ghi tệp vào ổ đĩa (Save)
C1: Kích chuột vào biểu tượng Save trên Toolbar.
C2: Ấn tổ hợp phím Ctrl+S.
C3: Vào menu File/Save.
Nếu tệp đã được ghi trước từ trước thì lần ghi tệp hiện tại sẽ ghi lại sự thay đổi kể từ lần ghi trước (có cảm giác là Excel không thực hiện việc gì).
Nếu tệp chưa được ghi lần nào sẽ xuất hiện hộp thoại Save As, chọn nơi ghi tệp trong khung Save in, gõ tên tệp cần ghi vào khung File name, ấn nút Save.
Chu Quốc Tài
1. Chọn nơi ghi tệp
2. Gõ tên mới cho tệp
3. Bấm nút Save để ghi tệp
Bấm nút Cancel để hủy lệnh ghi tệp

Hộp thoại Save As
Chu Quốc Tài
Chu Quốc Tài
§2. CĂN CHỈNH - KẺ BẢNG - LẬP CÔNG THỨC
a) Trộn ô:
Bước 1: Bôi đen số ô cần trộn
Bước 2: C1: Nháy chuột vào biểu tượng Merger and center trên thanh công cụ
C2: Vào Format  Cell  Aligmant  Merger cell  OK
 Bỏ trộn ô bằng cách thực hiện lại thao tác trên
b) Chọn Font chữ: Bôi đen các ô cần chọn  vào Format  Cell  Font... Rồi chọn Font chữ như Word
1. Căn chỉnh (trộn ô, chọn font chữ, điền số thứ tự)
Chu Quốc Tài
c. Điền số thứ tự (Seres):
Nhập một số đầu tiên rồi đặt con trỏ chuột vào điểm chấm đen ở góc dưới bên phải của ô, ấn phím Ctrl rồi kéo thả chuột qua các ô cần đánh thứ tự
2. Lập công thức.
- Các phép toán trong Excel gồm: Cộng (+), trừ (-), nhân (*), chia (/), luỹ thừa (^).
- Trước khi nhập công thức bao giờ cũng phải nhập giấu ‘=‘
- Trên một hàng dữ liệu ta chỉ việc nhập công thức tại một ô, sau đó copy công thức cho các hàng còn lại.
Chu Quốc Tài
3. Các kiểu dữ liệu của Excel:
+ Kiểu chữ (text)
+ Kiểu số (Number)
+ Kiểu ngày (date): khi nhập dữ liệu phải chú ý là máy của bạn đang đặt hệ ngày là mm/dd/yy (tháng/ngày/năm) hay là dd/mm/yy (ngày/tháng/năm)
 Cách thức đặt ngày hệ thống cho máy tính: Nháy chuột vào nút Start  Settings  Control panel  Regional setting  Date  Sort date style: ta đặt là mm/dd/yy theo yêu cầu
Chu Quốc Tài
4. Kẻ bảng.
Bôi đen vùng dữ liệu cần kẻ Format Cells Bordes
- Chọn none là không có đường kể
- Chọn Outline (kẻ khung cho bảng)
- Chọn Inside (kẻ đường dòng và cột cho bảng)
Khung Style ta chọn các kiểu đường nét cho bảng
Chọn OK
Chu Quốc Tài
5. Điều chỉnh chữ ở trong mỗi ô:
Bôi đen số ô cần chỉnh, vào Format Cell Aligment:
- Dòng Horizontal: Chọn căn trái, phải...
- Dòng Vertical: Chọn căn trên, căn dưới...
- Chọn  Wrap text (chữ tự động xuống dòng khi ô nhỏ không đủ chữa)
Chu Quốc Tài
Chu Quốc Tài
§3. THAO TÁC CƠ BẢN VỚI SHEET - ĐỊA CHỈ, CHÈN HÀNG, CỘT, Ô
1. Thao tác cơ bản với Sheet:
a) Copy sheet: Chọn Sheet cần copy, nháy chuột phải chọn move of copy hiển thị hộp thoại
b) Đổi tên sheet:
Chọn sheet cần đổi tên nháy chuột phải Rename rồi gõ tên cần đổi
Chọn sheet cần copy
Chọn  để copy OK
c) Xoá sheet: Chọn sheet cần đổi tên nháy chuột phải chọn Delete
d) Chèn sheet: Chọn sheet cần đổi tên nháy chuột phải chọn Insert
Chu Quốc Tài
2. Địa chỉ:
Địa chỉ ô: Là nơi giao giữa cột và dòng. Địa chỉ ô được xác định: VD: A1, B2, C10...
Địa chỉ vùng: Vùng là tập hợp các ô liên kề nhau trên một bảng tính. Địa chỉ vùng được xác định: [Địa chỉ ô đầu góc bên trái]:[Địa chỉ ô cuối góc bên phải] VD: A1:B10, C1:C2,…
- Địa chỉ tương đối: Là địa chỉ mà công thức nó được cập nhật khi sao chép đến địa chỉ mới. - Cách viết: VD: ở ô C1 ta có công thức =A1+B1 thì khi sao chép công thức này đến ô C2 ta có =A2+B2
Chu Quốc Tài
Địa chỉ tuyệt đối: Là địa chỉ mà công thức của nó không thay đổi khi sao chép đến địa chỉ mới. Muốn lấy cố định toạ độ trong công thức thì ngay sau khi chọn toạ độ ta phải nhấn phím F4 Kí hiệu: $ + $$ : Tuyệt đối cột, tuyệt đối dòng + $ : Tuyệt đối cột, tương đối dòng + $ : Tương đối cột, tuyệt đối dòng. VD: Gõ A1, ấn F4  $A$1 (tuyệt đối cột, tuyệt đối dòng)
 Lưu ý: Khi thực hiện phép toán (cộng, trừ, nhân, chia) của một dãy số với một ô địa chỉ, thì ô địa chỉ đó ta phải đặt về giá trị tuyệt đối để khi sao chép công thức địa chỉ của ô không bị thay đổi
Chu Quốc Tài
3. Chèn xoá, ẩn hàng, cột. Bôi đen cột hoặc hàng cần chèn (bằng cách nháy chuột vào tên cột hoặc hàng) Nháy phải chuột:  Insert Column (hoặc Row): Để chèn cột (hoặc hàng)  Delete Column (hoặc Row): Để xoá cột (hoặc hàng)  Hide: Để ẩn  UnHide: Bỏ ẩn.
4. Thay đổi độ rộng cho các hàng (cột): Cách 1: Bôi đen hàng hoặc cột cần giãn, đặt trỏ chuột vào đường biên của hoàng (hay cột) xuất hiện hình hai múi tên ngược chiều thì kéo thả chuột theo ý muốn
Cách 2: Bôi đen hàng hoặc cột cần giãn Format
Chu Quốc Tài
Cách 2: Bôi đen hàng hoặc cột cần giãn
Format
Row Height Nhập khoảng cách giãn hàng vào ô Row height OK
Column Width Nhập khoảng cách giãn cột vào ô Column Width OK
Chu Quốc Tài
4. Sử dụng một số biểu tượng chuyên dùng của Exel:
- Nếu muốn có biểu tượng tiền tệ ở phía trước của số ta nháy chuột vào biểu tượng $ - Nếu muốn có tỷ lệ phần trăm ta nháy chuột vào biểu tượng % - Nếu muốn có dấu (.) hoặc dấu (,) để ngăn cách hàng nghìn và hàng thập phân ta nháy chuột vào biểu tượng ,
- Nếu muốn giảm, tăng phần thập phân ở phía sau ta nháy chuột và biểu tượng
Chu Quốc Tài
Chu Quốc Tài
§4. ĐỊNH DẠNG DỮ LIỆU SỬ DỤNG MỘT SỐ HÀM ĐƠN GIẢN
I. ĐỊNH DẠNG DỮ LIỆU
1. Định dạng dữ liệu số (Numbers) cho máy tính: (Đặt dấu (.) hoặc dấu (,) ở hàng nghìn hoặc hàng thập phân) Vào Start Settings  Control panel Regional settings Number chọn các mục: - Decimal symbol (dấu hàng thập phân): ‘,’ hoặc ‘.’ - Digit grouping symbol (dấu hàng nghìn): ‘.’ hoặc ‘,’
 Apply OK
Chu Quốc Tài
2. Định dạng dữ liệu cho các ô (cells): Bôi đen các ô cần lấy định dạng  Format Cells Number... Trong đó: + Number: Kiểu số. + Currency: Kiểu tiền tệ. + Accounting: Kiểu kế toán. + Date: Kiểu ngày tháng. + Time: Kiểu giờ. + Percentage: Kiểu phần trăm. + Fraction: Kiểu phân số. + Scientific: Kiểu khoa học. +Text: Kiểu văn bản, chữ. + Special: Kiểu đặc biệt. + Custom: Lựa chọn kiểu.
Chu Quốc Tài
II. SỬ DỤNG HÀM ĐƠN GIẢN TRONG EXCEL
1. Các hàm thống kê – hàm số học
Hàm tính tổng – Sum. = Sum(các giá trị) VD: = Sum(A3:D3) (A3:D3) là (Địa chỉ ô đầu:địa chỉ ô cuối)
- Hàm tính trung bình cộng – Aveerage. = Average(Dãy số cần tính)
- Hàm tính giá trị lớn nhất, nhỏ nhất – Max, Min = Max(Dãy số) = Min(Dãy số)
a) Nhóm hàm thống kê:
Chu Quốc Tài
b) Nhóm hàm số học:
Hàm xếp hạng – Rank. = Rank(số cần xếp hạng, dãy số cần so sánh, tiêu chuẩn) + Số cần xếp hạng: là toạ độ, địa chỉ của một ô nào đó. (ô đầu tiên trong dãy số) + Dãy số cần so sánh: Là toạ độ của một dãy các số so sánh để xếp hạng (sau khi chọn toạ độ của dãy này cần ấn F4 để lấy toạ độ tuyệt đối). + Tiêu chuẩn nhận hai giá trị: 0 - Xếp hạng giảm dần (số lớn nhất xếp thứ nhất). 1 - Xếp hạng tăng dần (số nhỏ nhất xếp thứ nhất)
Ví dụ:
Chu Quốc Tài
Ví dụ
Chu Quốc Tài
Hàm lấy số nguyên của phép chia – Int. = Int(Biểu thức số) VD: A2=9, B2=4  =Int(A2/B2)  kết quả là 2
Hàm lấy số dư của phép chia – Mod. = Mod(số chia, Số bị chia) VD: A2=7, B2=3;  = Mod(A2,B2)  kết quả là 1
Hàm tích luỹ thừa – Power. = Power(Cơ số, Số mũ) VD: A2=2;  = Power(A2,3)  kết quả là 8
Chu Quốc Tài
Hàm làm tròn số – Round. = Round(Biểu thức số, Số các chữ số cần làm tròn)
Lưu ý: Nếu chữ số cần lấy làm tròn mà > 0, thì làm tròn số ở phần thập phân của biểu thức số. Còn nếu số cần lấy làm tròn mà < 0 (tức số âm), thì làm tròn số ở phần nguyên của biểu thức số.
VD: Địa chỉ A1=2,154658; = Roud(A1,1) = 2,2 = Roud(A1,2) = 2,15
Chu Quốc Tài
§5. HỆ THỐG CÁC HÀM (TIẾP)
I. HÀM LOGICAL.
Hàm Và – AND.
Chức năng: Hội các biểu thức Logic (ĐK) cho kết quả True (Đúng) khi tất cả các biểu thức Logic đều bằng True Cú pháp: =AND(,,…) VD: = And(A2D2,E2=F2)
A2=3, B2=4, C2=2, D2=1, E2=5, F2=5 kết quả là True
A2=3, B2=4, C2=2, D2=1, E2=5, F2=6 Kết quả là False
Chu Quốc Tài
Hàm hoặc – OR.
Chức năng: Tuyển các biểu thức Logic (ĐK) cho kết quả True (Đúng) khi một trong các biểu thức Logic bằng True Cú pháp: =OR(,,…) VD: =OR (A2D2)
A2=3, B2=4, C2=2, D2=2, kết quả là True
A2=5, B2=4, C2=2, D2=2, Kết quả là False
Chu Quốc Tài
Hàm phủ định – NOT.
Chức năng: Phủ định giá trị của biểu thức True=False, False=True Cú pháp: =NOT() VD: = NOT(A2Chu Quốc Tài
III. CÁC HÀM VỀ CHUỖI KÝ TỰ.
Hàm LEET().
Chức năng: Lấy ra n ký tự ở phía bên trái của chuỗi ký tự cần lấy Cú pháp: =LEET(,n) VD: =LEET(“Vn007”,2) kết quả là Vn
2. Hàm RIGHT().
Chức năng: Lấy ra n ký tự ở phía bên phải của chuỗi ký tự cần lấy Cú pháp: =RIGHT(,n) VD: =RIGHT(“Vn007”,2) kết quả là 07
Chu Quốc Tài
2. Hàm MID().
Chức năng: Lấy ra n ký tự từ vị trí m của chuỗi ký tự cần lấy Cú pháp: =MID(,m,n) VD: =MID(“Vn007”,2,2) kết quả là n0
Chu Quốc Tài
§6. HÀM TÌM KIẾM VÀ THAM CHIẾU
I. HÀM TÌM KIẾM VÀ THAM CHIẾU THEO HÀNG (HLOOKUP)
Hlookup sẽ tìm kiếm dữ liệu so sánh trên hàng đầu tiên của bảng tham chiếu (hay còn gọi là bảng mã) và trả về giá trị tương ứng với mã đó.
=HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_Lookup)
 =HLOOKUP(Giá trị tìm,Vùng đối chiếu,Hàng cần lấy,Giá trị trả về)
Chu Quốc Tài
Trong đó - Lookup_value: Là giá trị tìm kiếm, giá trị so sánh (còn gọi là mã). Nhìn vào ô đầu tiên của bảng phụ nhưng được lấy ở trên bảng chính
Table_array: Là toạ độ của bảng tham chiếu (còn gọi là là bảng mã, bảng phụ).
Lưu ý: Khi lấy xong toạ độ của bảng tham chiếu ta phải ấn F4 để trả về giá trị tuyệt đối.
Row_index_num: Là số thứ tự của hàng được lấy ở bảng phụ, nơi lấy giá trị được trả về bảng chính.
Range_Lookup: nhận một trong hai giá trị: 0 : Là tìm kiếm chính xác 1 : Là tìm kiếm tương đối
Chu Quốc Tài
VD: Dựa vào bảng tham chiếu để điền dữ liệu cho cột tên hàng
Vì bảng tham chiếu là hàng nên phải dùng hàm Hlookup.
Công thức được lập như sau: =HLOOKUP(G144,$B$144:$E$145,2,0)
Chu Quốc Tài
I. HÀM TÌM KIẾM VÀ THAM CHIẾU THEO CỘT (VLOOKUP)
Vlookup sẽ tìm kiếm dữ liệu so sánh trên cột đầu tiên của bảng tham chiếu (hay còn gọi là bảng mã) và trả về giá trị tương ứng với mã đó.
=VLOOKUP(Lookup_value,Table_array,Clumn_index_num,Range_Lookup)
 =VLOOKUP(Giá trị tìm,Vùng đối chiếu,Cột cần lấy,Giá trị trả về)
Chu Quốc Tài
§7. LẬP BIỂU ĐỒ, ĐỒ THỊ
1. Biểu đồ, đồ thị
a) Lập biểu đồ hình cột:
Bước 1: Bôi đen vùng dữ liệu cần lập biểu đồ (Nếu các cột dữ liệu không liên tiếp thì ấn Ctrl khi bôi đen)  Chọn bảng chọn Insert Chart…
(Hoặc nháy chuột vào biểu tương trên thanh công cụ
Bước 2: Chọn kiểu biểu đồ hình cột (Clumn) Next
Next
Chu Quốc Tài
Bước 3:
Chọn mục Title
Gõ tiêu đề cho biểu đồ
Gõ tiêu đề cho trục x
Gõ tiêu đề cho trục y
Chu Quốc Tài
Bước 4: Chọn Finish để kết thúc
Chu Quốc Tài
§8. SẮP XẾP VÀ LỌC DỮ LIỆU
1. Sắp xếp dữ liệu
Bôi đen vùng dữ liệu cần Sắp xếp Data Sort Xuất hiện hộp thoại: Sort
Chọn cột khoá chính cần sắp
Chọn cột khoá phụ cần sắp
Sắp xếp theo thứ tự tăng dần
Sắp xếp theo thứ tự giảm dần
Sắp xếp cả hàng đầu tiên
Sắp xếp và trừ hàng đầu tiên (tên cột)
Chu Quốc Tài
2. Lọc dữ liệu AutoFilter
Bôi đen vùng dữ liệu cần lọc Data Filer AutoFilter Xuất hiện ở các cột
Nháy chuột vào chọn Custom để lọc theo điều kiện. (Nếu không có điều kiện ta có thể chọn các giá trị có sẵn của cột cần lọc)
Sau khi lọc ra kết quả ta có thể copy ra vùng khác và in riêng.
Muốn hiện lại toàn bộ dữ liệu để lọc tiếp ta chọn: Data Filer Show All.
Muốn bỏ chế độ lọc ta chọn: Data Filer Bỏ dấu  AutoFiler.
Chu Quốc Tài
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:
Chu Quốc Tài
b) Lọc dữ liệu dùng Advanced Filter
B1: Đị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.
VD với miền CSDL như trên:
Chu Quốc Tài
B2: Thực hiện lọc
Vào menu Data/Filter/Advanced Filter…
Hiện KQ lọc ngay tại miền dữ liệu
Hiện KQ 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 KQ
Chỉ hiện 1 bản ghi trong số những KQ trùng lặp