Transcript PHẦN IV LẬP BẢNG TÍNH TRÊN MICROSOFT EXCEL CHƯƠNG 1: CÁC KHÁI NIỆM CƠ BẢN 1.

PHẦN IV
LẬP BẢNG TÍNH TRÊN MICROSOFT EXCEL
CHƯƠNG 1: CÁC KHÁI NIỆM CƠ BẢN
1. Khởi động Excel
Sử dụng một trong các cách sau
1
Start  Programs  Microsoft Office  Microsoft Excel
2
Nháy đúp chuột vào biểu tượng
trên màn hình nền
2. Thoát khỏi Excel: Sử dụng một trong các cách sau
1
File  Exit
2
Kích nút Close
Chän Yes khi gÆp th«ng b¸o sau:
3
Sử dụng phím tắt : ALT + F4
Màn hình làm việc của Excel
Nút thu nhỏ,
mở rộng,
đóng cửa sổ
Thanh
tiêu đề
Thanh bảng
chọn
Thanh
công cụ
định dạng
Thanh công
cụ chuẩn
Thanh trạng
thái
Thanh
cuộn dọc
Thanh cuộn
ngang
Minh hoạ
Màn hình làm việc của Excel
Thanh
công thức
Hộp tên: Hiển thị địa chỉ ô hoặc khối ô đang được kích hoạt
Hộp công thức: Hiển thị công thức hoặc thông tin trong ô hiện hành
Bảng tính
Con trỏ
chuột
Ô
hoạt
động
Sơ lược về bảng tính
Bảng tính Excel được chia thành nhiều
dòng theo hàng ngang và nhiều cột
theo hàng đứng
Sơ lược về bảng tính
Cột (Column): Tính theo hàng đứng, có 256 cột được đặt
tên bằng các chữ cái tiếng Anh theo thứ tự:
Cột C
A,B,C,…AB,AC,…
Sơ lược về bảng tính
Dòng(Row): Tính theo hàng ngang, có 65.536 dòng được
đánh theo số thứ tự từ trên xuống là 1,2,3,…65.536
Dòng 2
Sơ lược về bảng tính
Ô (Cell): Là giao giữa 1 cột và 1 dòng. Tên ô được xác
định bởi tên cột trước dòng sau: <Cột> <Dòng>
E8
Sơ lược về bảng tính
Vùng bảng tính: Được xác định bởi nhiều ô đứng liền nhau,
tên của vùng được xác định bởi tên của ô góc trên bên trái và
tên của ô góc dưới phải của vùng, phân cách nhau bởi dấu (:)
Vùng B3:D8
Sơ lược về bảng tính
Bảng tính (Sheet): Bao gồm tất cả các cột và các dòng. Tập
tin Excel thường có 3 bảng tính mặc định là Sheet1, Sheet 2,
Sheet 3 nhưng ta có thể tạo thêm nhiều bảng tính và có thể
đổi tên các Sheet
Sheet1: Bao gồm 256 cột và 65.536 dòng
Minh hoạ
Di chuyển ô hiện hành trong bảng tính
a. Sử dụng bàn phím
b. Sử dụng chuột
Di chuyển chuột đến ô chọn và nháy chuột vào đó
Di chuyển ô hiện hành trong bảng tính
c. Sử dụng lệnh Goto của Excel
Mở bảng chọn Edit, chọn lệnh Goto… hoặc ấn tổ
hợp phím Ctrl + G (Ấn phím F5)
Gõ địa chỉ ô cần di chuyển tới, Ấn Enter hoặc OK
2. Các kiểu dữ liệu và cách nhập
a. Nguyên tắc chung khi nhập dữ liệu:
- Chọn ô cần nhập dữ liệu: Kích chuột vào ô đó
- Gõ dữ liệu cần nhập vào ô (dữ liệu sẽ hiện ở trong ô trên
thanh công thức)
- Kết thúc nhập bằng 1 trong 3 cách:
+ Ấn Enter
+ Sử dụng phím mũi tên di chuyển sang ô khác
+ Nháy chuột lên ô bất kỳ
2. Các kiểu dữ liệu và cách nhập
b. Các loại dữ liệu:
Dữ liệu: những gì ta đưa vào trong các ô của bảng tính,
nó có thể là: Số, chữ, ngày ,…
- Dữ liệu nhập vào bảng tính Excel bắt buộc phải là 1 trong
2 dạng: Hằng hoặc công thức
- Giá trị hằng: Có giá trị không thay đổi, trừ khi ta chỉnh
sửa hoặc thay đổi định dạng của dữ liệu, hằng gồm: Trị số,
trị chuỗi, trị ngày, giờ.
- Công thức (Fomula): Là tập hợp các giá trị số, toạ độ ô,
tên khối, toán tử, hàm...và được bắt đầu bằng dấu (=) hoặc
dấu (+).
+ Giá trị của công thức sẽ phụ thuộc vào sự thay đổi nội
dung của những dữ liệu trong công thức đó
2. Các kiểu dữ liệu và cách nhập
Dữ liệu dạng số: Dữ liệu số dùng để tính toán nên chỉ gồm
các con số: 0,1,2,..9. hoặc số và phân số đi kèm vd 5 1/4
- Dấu âm có dấu trừ (-) đứng trước hoặc để dấu âm trong
ngoặc đơn.
2. Các kiểu dữ liệu và cách nhập
Dữ liệu dạng ngày\giờ :
- Ngày trước tháng sau hay tháng trước ngày sau do Control Panel
quy định.
- Ngày, tháng, năm phân cách nhau bởi dấu (-) hoặc gạch chéo (/).
- Phần tháng có thể là số hoặc chữ tiếng Anh
- Nếu không nhập ngày, năm thì mặc nhiên đựoc coi là ngày, năm
hiện tại
2. Các kiểu dữ liệu và cách nhập
Dữ liệu dạng giờ
- Có dạng hh:mm:ss (giờ:phút:giây) hoặc hh:mm(Giờ:phút )
hoặc hh: (giờ).
- AM hoặc PM để chỉ sáng hoặc chiều.
- Có thể kết hợp ngày và giờ
Vd: 11:55AM 30/4/75; 7:15PM
2. Các kiểu dữ liệu và cách nhập
Nhập dữ liệu dạng chuỗi: Dữ liệu kiểu chuỗi thường là các
tiêu đề, diễn giải trong bảng tính. Dữ liệu kiểu chuỗi không
tính toán được.
- Nếu muốn các số hiển thị theo kiểu chuỗi thì phải để trong
cặp dấu nháy đơn (' ')
2. Các kiểu dữ liệu và cách nhập
Nhập dữ liệu kiểu công thức:
- Bắt đầu bằng dấu (=) hoặc dấu (+), tiếp theo là các toán
hạng và toán tử,..
= D3+E3+F3
Các toán tử sử dụng trong công thức
Toán tử số học:
Các toán tử sử dụng trong công thức
Toán tử so sánh: Dùng để so sánh hai giá trị hoặc biểu
thức và khi đó kết quả sẽ chỉ là Đúng (TRUE) hoặc Sai
(FALSE).
Các toán tử sử dụng trong công thức
Toán tử nối chuỗi : Toán tử nối chuỗi & dùng khi cần nối
(ghép) hai hoặc nhiều chuồi
Toán tử tham chiếu
Chú ý: Độ ưu tiên của các toán tử trong công thức :
- Các nhóm biểu thức trong (…).
- Luỹ thừa
- Nhân, chia, cộng, trừ
3. Tạo chú thích
Chú thích là lời giải thích cho dữ liệu trong ô
Để nhập chú thích cho ô:
- Chọn ô cần tạo chú thích
- Insert/Coment
- Gõ chú thích rồi ấn Enter
4. Các thao tác trên vùng
- Chọn ô: Kích chuột lên ô muốn chọn
- Chọn cột: Kích chuột vào tiêu đề cột cần chọn
- Chọn dòng: Kích chuột vào tiêu đề dòng cần chọn
- Chọn Vùng kề nhau: Bấm và rê chuột từ ô đầu vùng đến
cuối vùng hoặc ấn giữ phím Shift và 1 trong 4 phím mũi tên
để mở rộng vùng chọn.
- Chọn Vùng không kề nhau: Ấn và giữ phím Ctrl trong suốt
quá trình rê chuột để chọn
- Chọn toàn bộ bảng: Ctrl + A hoặc nháy chuột vào ô góc
trên cùng bên trái bảng tính
Minh hoạ
8. Các thao tác trên dòng, trên cột dữ liệu
- Chèn cột:
- Xoá cột:
- Định dạng cột: Chọn cột hoặc các cột cần định dạng
Vào Format/ Columns:
+ With: Thay đổi độ rộng cột theo giá trị nhập từ
bàn phím.
+ Autofit Selection: Tự động thay đổi độ rộng của
cột theo dữ liệu được nhập vào.
+ Hide: Che dấu cột
+ Unhide: Hiện lại các cột che dấu.
+ StandardWith: Trả lại ô của dòng theo đúng giá trị
nhập vào.
+ Autofit: Tự động thay đổi chiều cao của dòng theo
dữ liệu được nhập vào.
Sao chép, di chuyển dữ liệu
Sao chép thường: Tương tự như soạn thảo văn bản
Sao chép đặc biệt
- Bước 4: Mở bảng chọn Edit, chọn Paste Special. Hộp thoại Paste
Special xuất hiện
- Paste:
All: Sao chép toàn bộ
- Fomulas: Chỉ sao chép công thức
Values: Chỉ sao chép giá trị
Comments: Chỉ sao chép các giá trị ghi
chú trong ô
Validation: Chỉ sao chép giá trị hợp lệ
Column Widths: Chỉ sao chép độ rộng
cột
Sao chép, di chuyển dữ liệu
- Operation:
None: Sao chép bình thường
Add: Sao chép đồng thời thêm
giá trị nguồn
Subtract: Sao chép đồng thời trừ
giá trị nguồn.
Multiply: Sao chép đồng thời
nhân giá trị nguồn.
Divide: Sao chép đồng thời chia
cho giá trị nguồn.
- Transpose: Sao chép và hoán chuyển cột thành dòng và
dòng thành cột
9. Các loại địa chỉ trong Excel
Tất cả các ô trong vùng đều có tên, tên của ô hoặc vùng
chính là địa chỉ của ô hoặc vùng đó. Nhờ các địa chỉ này mà ta
dễ dàng thành lập các công thức tính toán
vd: A4: Là tên của ô A4 và là địa chỉ của ô đó
9. Các loại địa chỉ trong Excel
a. Địa chỉ tương đối: Các địa chỉ ghi đúng theo của ô được
gọi là địa chỉ tương đối của ô hay vùng đó. Nếu trong
công thức có sử dụng địa chỉ này thì khi sao chép công
thức sang ô khác, các địa chỉ cũng thay đổi theo
Quy cách: <Cột> <dòng>
* Cách tạo địa chỉ tương đối: Nhập trực tiếp từ bàn phím
hoặc sử dụng chuột kích vào ô hay chọn vùng muốn tạo
địa chỉ
9. Các loại địa chỉ trong Excel
b. Địa chỉ tuyệt đối: Cũng giống như địa chỉ tương đối
nhưng khi viết sẽ chèn thêm các dấu cố định($) và khi sao
chép công thức sang vị trí mới sẽ không bị thay đổi
Quy cách: $<Cột>$<Dòng>
9. Các loại địa chỉ trong Excel
+ Địa chỉ hỗn hợp: Là địa chỉ mà dấu cố định chỉ đặt
trước cột hoặc dòng (Cố định cột hoặc cố định dòng)
* Cách tạo địa chỉ tuyệt đối, hỗn hợp:
- Nhập trực tiếp từ bàn phím dấu $ trước tên cột hay tên
hàng của địa chỉ ô cần thực hiện
- Nhập địa chỉ tương đối sau đó ấn F4 (F4 vừa bật vừa tắt).
7. Các thao tác với tệp
• Tệp Excel có phần mở rộng là “xls”.
• Các thao tác chính với tệp
– Tạo tệp mới
– Mở tệp đã có
– Ghi tệp
– Ghi tệp với tên khác
– Ghi tệp với kiểu khác
– Đóng tệp
7.1. Tạo tệp mới
1. Chọn menu File New
2. Kích chuột vào nút New
trên thanh công cụ
chuẩn
3. Nhấn tổ hợp phím
Ctrl+N
7.2. Mở tệp đã có trên đĩa
1. File  Open.
2. Kích chuột vào nút Open trên thanh công
cụ chuẩn.
3. Ctrl + O.
• Sau đó:
– Chọn thư mục.
– Nhấn chuột chọn tệp.
– Nhấn Open.
7.3. Ghi tệp
• File  Save (Ctrl +
S, biểu tượng đĩa
mềm trên thanh
công cụ).
• Sau đó:
– Chọn thư mục.
– Gõ tên tệp.
– Gõ Enter hoặc nhấn
Save.
7.4. Ghi tệp với tên khác
• File  Save As
• Sau đó:
– Gõ tên khác cho tệp.
– Gõ Enter hoặc nhấn Save.
• Kết quả:
– Excel ghi tệp hiện hành sang tệp mới.
– Tệp mới được mở thay thế tệp cũ.
• Sử dụng khi muốn tạo phiên bản mới để
sửa chữa mà không ảnh hưởng đến tệp
cũ
7.4. Ghi tệp với kiểu khác
• File  Save As, sau đó:
– Gõ tên tệp.
– Chọn kiểu tệp ở mục Save as type
• *.HTML: trang web
• *.DBF: cơ sở dữ liệu dBASE,
•…
• Kết quả:
– Excel ghi tệp hiện hành sang tệp mới có kiểu
đã chọn.
– Tệp mới được mở thay thế tệp cũ.
• Sử dụng khi muốn xuất dữ liệu sang dạng
khác.
7.5. Đóng tệp
• File  Close, hoặc
• Kích chuột vào nút đóng tại cửa sổ
tệp, hoặc:
• Gõ Ctrl + F4
• Kết quả:
– Tệp đang soạn thảo được đóng
– Excel và các tệp khác vẫn được mở
8. Một số hàm đơn giản.
• SUM (miền)
– Tính tổng các số trong miền
– Ví dụ: SUM(A1:A5); SUM(số_ngày_thuê)
• AVERAGE (miền)
– Tính trung bình các số trong miền
– Ví dụ: AVERAGE(B2:E10)
•
•
•
•
MAX(miền): Giá trị lớn nhất trong miền
MIN(miền): Giá trị nhỏ nhất trong miền
COUNT(miền): Số ô chứa số trong miền
IF(điều_kiện, giá_trị_1, giá_trị_2):
– Nếu điều_kiện đúng, trả về giá_trị_1.
CHƯƠNG II
MỘT SỐ HÀM CƠ BẢN TRONG EXCEL
1. Hàm tổng quát
= <Tên hàm> ( Danh sách đối số )
Trong đó:
- <Tên hàm>: Là tên được đặt theo quy ước của Excel
- Đối số:
+ Tham biến là 1 ký tự hoặc 1 chuỗi ký tự thì phải đặt trong
cặp dấu nháy kép (“…”).
2. Nhóm hàm toán học
- Hàm SQRT (X): Cho giá trị căn bậc hai của X
Vd = sqrt(25) Cho kết quả là 5
- Hàm ABS (X): Cho giá trị tuyệt đối của X
Vd: =ABS (-25) Cho kết quả là 25
- Hàm INT (X): Cho kết quả phần nguyên của biểu
thức số X
Vd: = INT(235.25) Cho kết quả là 235
- Hàm PI (): Cho kết quả số Pi (3.14593)
2. Nhóm hàm toán học
Hàm ROUND (Giá trị,n): Làm tròn giá trị đến n số lẻ
- Nếu n>0: Làm tròn đến số lẻ thứ n (Phần sau dấu phảy).
Vd: = Round (12345.54321,2): Cho kết quả là 12345.54
- Nếu n<0:
+ n=(-1): Làm tròn đến hàng đơn vị
+ n=(-2): Làm tròn đến hàng chục
+ n=(-3): Làm tròn đến hàng trăm
Vd= Round(12345.543216,-2): Cho kết quả là 12300
Hàm MOD (X,Y): Cho kết quả là phần dư của X cho Y
Vd: = Mod (25,8): Cho kết quả là 1
3. Nhóm hàm Thống kê
- Hàm SUM (Danh sách trị số)
Tính tổng của các giá trị có mặt trong danh sách
- Hàm AVERAGE (Danh sách trị số)
Tính giá trị bình quân của các giá trị trong ngoặc
- Hàm MAX (Danh sách trị số)
Tìm giá trị số lớn nhất
- Hàm MIN (Danh sách trị số)
Tìm giá trị số nhỏ nhất của các trị số trong danh sách
3. Nhóm hàm Thống kê
- Hàm COUNT (vùng hoặc giá trị 1, giá trị 2)
Đếm các ô có giá trị là số
Vd; =count(A3:C8) Đếm các ô có dữ liệu trong vùng A3:C8
- Hàm COUNTA (Vùng hoặc giá trị 1, giá trị 2)
Đếm các ô có dữ liệu trong vùng cần xác định. Không
phân biệt các kiểu dữ liệu
Vd: = counta(C4:C8) Đếm các ô có dữ liệu từ C4 đến C8
4. Nhóm hàm chuỗi
- Hàm LEFT (Dữ liệu chuỗi, số ký tự muốn lấy)
Lấy các ký tự bên trái
-Hàm RIGHT (Dữ liệu chuỗi, số ký tự muốn lấy)
Lấy các ký tự bên phải
-Hàm MID (Dữ liệu chuỗi, Số bắt đầu, số ký tự muốn lấy)
Lấy các ký tự trong chuỗi, tính từ bên trái và lấy X ký tự
-
Hàm UPPER (Dữ liệu chuỗi)
Hàm chuyển đổi Text từ chữ thường thành chữ hoa
- Hàm
LOWER (Text)
Hàm chuyển các ký tự đầu chuỗi thành chữ hoa
5. Nhóm hàm ngày tháng
- Hàm TODAY ()
Cho biết giá trị ngày, tháng, năm hiện hành
- Hàm NOW ()
Cho biết giá trị ngày, giờ hiện hành
- Hàm DAY (x)
Cho biết giá trị ngày của tham biến X
-
Hàm MONTH (x)
Cho biết giá trị tháng của tham biến X
- Hàm YEAR
(x)
Cho biết giá trị năm của tham biến X
6. Nhóm hàm Logic
- Hàm AND (Điều kiện 1, điều kiện 2,…điều kiện n)
Cho giá trị đúng khi mọi điều kiện trong danh sách đều đúng
- Hàm OR (Điều kiện 1, điều kiện 2,…điều kiện n)
Cho giá trị đúng khi có 1 điều kiện trong danh sách là đúng
-
Hàm NOT (Điều kiện)
Cho giá trị đúng khi điều kiện sai và ngược lại
7. Nhóm hàm điều kiện
- Hàm IF (Điều kiện, giá trị đúng, giá trị sai)
- Nếu điều kiện đúng, trả về giá trị 1. Nếu sai, trả về
giá trị 2.
Các lệnh IF có thể lồng nhau.
Bài tập các hàm
Hàm if
Đề Ktra D6
NỘI DUNG
1. Tìm kiếm theo dòng
2. Tìm kiếm theo cột
3. Trích lọc dữ liệu
4. Bài tập về hàm: Dsum, Dcount, Countif, Sumif,
Tìm kiếm
- Hàm SUMIF : TÍNH TỔNG CÓ ĐIỀU KIỆN
= SUMIF(Vùng điều kiện, điều kiện, vùng tính tổng)
8. Nhóm hàm tìm kiếm
- Hàm HLOOKUP: Tìm và lấy giá trị theo dòng
(Dòng đầu tiên của khối tìm kiếm)
=HLOOKUP (Giá trị tìm, vùng tham chiếu, n, 0)
Tìm giá trị cần thiết trong dòng thứ nhất của vùng tham
chiếu và lấy giá trị tương ứng ở dòng thứ n trong vùng này
Vd: = Hlookup (B3, $B$7: $D$8,2,0)
Tìm giá trị giống như giá trị của ô B3 trong vùng
B7: D8. Nếu giống thì lấy giá trị tương ứng ở dòng thứ 2
trong vùng này
8. Nhóm hàm tìm kiếm
- Hàm VLOOKUP: Tìm và lấy giá trị theo ột
(cột đầu tiên của khối tìm kiếm)
c
=VLOOKUP (Giá trị tìm, vùng tham chiếu, n, 0)
Tìm giá trị cần thiết trong cột thứ nhất của vùng tham chiếu
và lấy giá trị tương ứng ở cột thứ n trong vùng này
Vd: = Vlookup (B3, $B$7: $D$8,2,0)
Tìm giá trị giống như giá trị của ô B3 trong vùng
B7: D8. Nếu giống thì lấy giá trị tương ứng ở Cột thứ 2
trong vùng này
TRÍCH LỌC DỮ LIỆU
Trích lọc dữ liệu là có thể lấy ra, loại bỏ hoặc dấu đi những
dòng dữ liệu không cần thiết. Chỉ ghi lại những dòng dữ liệu
thoả mãn 1 điều kiện nào đó
1. Trích lọc tự động
- Đặt con trỏ chuột vào bất kỳ ô nào trong vùng dữ liệu (bảng)
- Data\Filter\Auto Fileteẩn
- Kích vào các nút để mở bảng chọn tiêu chuẩn, các dòng có cùng
điều kiện đã chọn sẽ được giữ lại, các dòng khác sẽ ẩn đi
1. Trích lọc dữ liệu
2. in ấn dữ liệu
3. Bài tập về trích lọc,bài tập tổng hợp kiểm tra lấy điểm
HS1
TRÍCH LỌC DỮ LIỆU
Thiết lập các vùng tiêu chuẩn
Tên trường dùng làm điều kiện
Ghi những điều kiện
- Di chuyển ô hiện hành vào trong phạm vi vùng CSDL cần thực hiện
- Mở bảng chọn Data, Chọn Filter , chọn Advanced Filter
Nhập vùng khai thác
Nhập vùng điều kiện
Nhập vùng copy tới
Filter the list, in_place: Hiển bản ghi chọn lọc ngay trên vùng
CSDL khai thác (tại chỗ)
Copy to another location: Sao chép những bản ghi chọn lọc vào
vùng khác.
3. Bài tập về trích lọc,bài tập tổng hợp kiểm tra lấy điểm
HS1
1. Tạo vùng điều kiện (Criteria range) theo cấu trúc:
- Dòng 1: Chứa tên trường dùng làm điều kiện (phải chính xác với tên
trường của vùng khai thác, nên sao chép xuống cho đỡ sai sót)
- Dòng 2 (hoặc dòng kế tiếp): Dùng để ghi những điều kiện theo yêu
cầu
2. Nhập những điều kiện ở ngay phía dưới tên trường trong dòng 2
(hoặc 3 ) của vùng điều kiện tuỳ theo yêu cầu cụ thể.
3. Di chuyển ô hiện hành vào trong phạm vi vùng CSDL cần thực hiện
4. Mở bảng chọn Data, Chọn Filter , chọn Advanced Filter