Bài giảng EXCEL toàn tập đã chỉnh sửa

Pres Name:
Trung Tâm Giáo Dục Thường Xuyên Nam Sách
GIÁO TRÌNH MICROSOFT EXCEL
Nguyễn Bá Tài
Giới Thiệu Về Microsoft Excel
1.Định Nghĩa
Excel là một chương trình trong bộ Microsoft Office. Nó giúp ích cho việc tổ chức, sắp xếp, trình bày dữ liệu trong công việc hàng ngày thành dữ liệu (Data) dưới dạng số hoặc chữ trong một bảng tính gồm nhiều hàng và nhiều cột. Có ứng dụng nhiều trong công việc quản lý, kế toán..
2.Khởi động và thoát khỏi Excel
2.1 Khởi động

C2 :KÝch ®óp vµo biÓu t­îng trªn mµn h×nh nÒn
C3 :Vào Task Manager New Task..
C4 :Vào My Computer C:Program File Microsoft Office ...
Ngoài ra còn nhiều cách khác như :vào Windows Explorer,mở 1 file có phần mở rộng là .ppt,...
C1:Kích chuột vào nút StartProgams Microsoft Office Microsoft Excel
3.Cách Thoát
C1: Vào File Exit
C2 :Nhấn đồng thời Alt +F4
C3 : Vào Task Manager chọn chương trình Excel rồi chọn End Task
C4 :Nhấn vào biểu tượng trên thanh Tiêu đề
Chương 1:Giới thiệu màn hình làm việc
Thanh tiêu đề
(Title Bar )
Menu Bar
Formalar Bar
Các Sheet
Thanh Cuộn
Vùng soạn thảo bảng tính
Standard
Formatting
Sau khi khởi động, màn hình Excel xuất hiện như sau
Thanh trạng thái (Status bar)
I.GIỚI THIỆU MÀN HÌNH LÀM VIỆC
1,Thanh tiêu đề :
2,Thanh Thực đơn (Menu bar)
Chứa các tên lệnh đơn tương ứng với các nhóm chức năng xử lý dữ liệu
3,Thanh công cụ (Toolbars)
Hiện thị tên của cửa sổ ứng dụng
Là các thanh công cụ , trên thanh này có nhiều nút công cụ lệnh,giúp thực hiện nhanh các chức năng lệnh thay vì thực hiện bằng thanh thực đơn
Trong Excel ngầm định hiển thị sẵn 2 thanh công cụ :Standard và Formatting vì chúng thường được sử dụng.
Ngoài ra còn rất nhiều nút công cụ khác trong quá trình làm việc,nếu bạn muốn dùng nút nào thì cho hiển thị lên, nếu không thì giấu nó đI.
Để hiện thị hoặc giấu thanh công cụ thì vào View Toolbars hoặc vào Tools Customize. Toolbars
Để hiện thị hoặc giấu nút công cụ nào thì vào Tools Customize. Commands rồi nhấn chuột trái vào nút muốn hiển thị rê ra thanh công cụ, còn muốn giấu nút nào thì làm ngược lại
4,Thanh công thức (Formula bar)
Địa chỉ của ô đang làm việc
Hiển thị nội dung của ô. Cũng là nơI dùng để sửa công thức, dữ liệu
5,Thanh trạng thái (Status bar)
Nằm ở cuối cửa sổ ứng dụng, cho biết trạng thái làm việc của Workbook
Ready :trạng thái sẵn sàng chờ nhập dữ liệu cho ô
Enter :Trạng thái đang nhập dữ liệu
Edit : Trạng thái đang sửa dữ liệu
6,Phần Bảng Tính :
Được tổ chức thành các trang bảng tính, mỗi trang bảng tính tương ứng với một Sheet, mỗi trang bảng tính có 256 cột được đánh thứ tự A, B,.AA,.IVvà 65536 dòng được đánh thứ tự từ 1, 2,3,..65536.
Trong một tệp của Excel mặc định là 3 Sheet
Ta có thể thay đ?i s? lượng Sheet mặc định bằng cách vào Tools Option General , nhập số lượng Sheet mặc định vào hộp Sheet in new workbook rồi OK
Các loại địa chỉ trong Excel
1.Địa chỉ của một ô (Cell)
2.Địa chỉ của vùng
7. ĐỊA CHỈ CỦA MỘT Ô (CELL)
7.1 Khái niệm
Địa chỉ của ô hay một khối ô trong Excel chính là tên của ô hay khối ô đó. Excel phân ra làm 2 loại cơ bản :
+ Địa chỉ tương đối
+ Địa chỉ tuyệt đối
Là loại địa chỉ thay đổi được khi sao chép dữ liệu hay công thức. Địa chỉ bình thường nhập vào trong công thức là địa chỉ tương đối
Là loại địa chỉ không thay đổi được khi sao chép dữ liệu hay công thức
8. ĐỊA CHỈ CỦA VÙNG
Là địa chỉ của một khối ô liên tiếp (2 ô trở lên).Nó được xác định bằng cách lấy địa chỉ của ô đầu tiên và cuối cùng trong vùng nhưng được đặt cách nhau bởi dấu hai chấm
Cũng có hai loại địa chỉ là :
+ Địa chỉ tương đối
+ Địa chỉ tuyệt đối
Ví dụ :
A2 :C10 là địa chỉ tương đối của khối ô từ ô A2 cho đến ô C10
$A2 :$C10 là địa chỉ tuyệt đối của khối ô từ ô A2 cho đến ô B10
- Địa chỉ tương đối được xác định dưới dạng
Cách xác định địa chỉ của ô
Ví dụ :
B5 : là địa chỉ của ô B5 (trong đó A là tên cột, 5 là số thứ tự của dòng
- Địa chỉ tuyệt đối của một ô được xác định dưới dạng $$
Ví dụ :
$C$6 : là địa chỉ tuyệt đối của ô C6
- Tương tự cho địa chỉ của vùng
Chú ý :
Để chuyển địa chỉ tương đối sang địa chỉ tuyệt đối ta dùng phím F4
Ví dụ:
Ví dụ :Ta có địa chỉ tương đối là A1 thì sau khi ta nhấn F4 sẽ được kết quả sau:
Giới Thiệu Về Hàm
Hàm là những công thức được xác định sẵn trong máy tính cho phép tính toán với những phép tính xác định, kết quả sau phép tính không phải là hàm mà là giá trị được trả về từ hàm đó
Tất cả các hàm trong Excel đều có dạng:
= Tên hàm(DSĐS)
Trong đó:
Tên hàm do Excel quy định, được viết bằng tiếng anh và không phân biệt chữ hoa hay chữ thường
DSĐS là danh sách các đối số, có thể nhận các giá trị như : Giá trị số, địa chỉ ô, tên vùng, công thức hoặc hàm khác..
SUM
AVERAGE
MIN
COUNT
AND
VLOOKUP
SUMIF
COUNTIF
OR
RANK
HLOOKUP
IF
MAX
II,Các Hàm Cơ Bản
Nhóm hàm với số
Nhóm Hàm Chuỗi
*.Hàm Sum
=Sum(DSĐS)
Trong đó :
Sum :là tên hàm
DSĐS : là các số, địa chỉ của ô chứa số hoặc địa chỉ của dãy ô liên tiếp
Hàm cho kết quả là tổng các giá trị trong danh sách đối số
=Sum(A2:H2) tức tính tổng các số liệu từ ô A2 đến ô H2
=Sum(1,3,5) cho ra kết quả là 9

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Chức năng :
Ví Dụ :
*Hàm Max
Cú Pháp:
=Max(DSĐS)
Hàm cho kết quả là giá trị lớn nhất trong các giá trị trong danh sách đối số
Chức năng :
Ví Dụ :
=Max(1,5,8,4) trả về giá trị lớn nhất trong 4 số trên là 9
=Max(A3:C5) trả về giá trị lớn nhất trong các giá trị trong vùng từ A3 đến C5

Các Hàm Cơ Bản Trong Excel
* Hàm Average
=Average(DSĐS)
Hàm cho kết quả là giá trị trung bình của các giá trị trong danh sách đối số
=Average(2,6,3,7) cho kết quả là 9
=Average(A2:A6) cho kết quả là giá trị trung bình của các giá trị từ ô A2 đến A6
=Average(B2:D5) cho kết quả là giá trị trung bình trong vùng từ ô B2 đến ô D5

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Chức năng :
Ví Dụ :
* Hàm Min
=Min(DSĐS)
Hàm cho kết quả là giá trị nhỏ nhất trong các giá trị trong danh sách đối số
=Min(1,5,8,4) trả về giá trị nhỏ nhất trong 4 số trên là 1
=Min(A3:C5) trả về giá trị lớn nhất trong các giá trị trong vùng từ A3 đến C5

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Chức năng :
Ví Dụ :
*Hàm Count
=Count(DSĐS)
Hàm cho kết quả là số giá trị kiểu số và các thành phần có tính chất kiểu số trong danh sách đối số
=Count(1,6,"tn",t2",5) ? 3
Chú ý: Hàm chỉ đếm khi đối số của hàm thỏa mãn là kiểu số
COUNT(C1:C5)=5
COUNT(A1:A5)=2

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Chức năng :
Ví Dụ :
*.Hàm Vlookup
=Vlookup(x,bảng dò tim,n,m)
Tham số :
X :là giá trị mang dò tìm
Bảng dò tìm : là địa chỉ tuyệt đối của bảng tham chiếu không bao gồm phần tiêu đề.Trong Bảng dò tìm cột đầu tiên thường là cột dò tìm
n : là số nguyên dương chỉ số thứ tự của cột tham chiếu trong bảng dò tìm
m : Kiểu dò tìm
+Nếu m =0 thì giá trị trên cột dò tìm không phải sắp xếp

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
+ Nếu m =1 thì trong bảng dò tìm, cột dò tìm phải được sắp xếp theo thứ tự tăng dần
Hàm lấy giá trị x trong cột dò tìm của bảng tham chiếu, nếu tìm được thì dóng sang cột thứ n của bảng dò tìm để lấy giá trị

Các Hàm Cơ Bản Trong Excel
Chức năng :
Ví Dụ :
Để tính ô B2 ta gõ công thức =Vlookup(A2,$A$9:$C$10,2,0) ? Enter ? Máy tính
Để tính ô D2 gõ công thức :=Vlookup(A2,$A$9:$C$10,3,0)
?Enter ? 300

Các Hàm Cơ Bản Trong Excel
*.Hàm Hlookup
=Hlookup(x,bảng dò tim,n,m)
Cú Pháp:
Tham số : giống như hàm Vlookup
Hàm lấy giá trị x trong dòng dò tìm của bảng tham chiếu, nếu tìm được thì dóng sang dòng thứ n của bảng dò tìm để lấy giá trị
Chức năng :
Ví Dụ :
Để tính ô E2 ta gõ công thức =Hlookup(A2,$A$8:$G$9,2,0) ? Enter ? 10%
*.Hàm If :

Các Hàm Cơ Bản Trong Excel
Cú Pháp: =If(btđk,Giá trị 1,Giá trị 2)
Trong đó :
Chức năng :
Btđk là biểu thức có dạng logic
Giá trị 1, Giá trị 2 : có thể là giá trị số hoặc "chuỗi", hoặc công thức..
Cho kết quả là Giá trị 1 nếu btđk đúng,ra Giá trị 2 nếu btđk sai
Ví Dụ :
Để tính các ô C2 đến C5 với đk học bổng khi ĐTB lớn hơn hoặc bằng 8
*.Hàm And

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Ví Dụ :
Chức năng :
=And(Btđk1,Btđk2,..,Btđkn)
Hàm cho kết quả là đúng nếu tất cả các Btđk đúng(True),kết quả là sai nếu 1 trong các Btđk là sai(False)
=And(9>4,5>2,6<8) ? True
=And(1>5,5<9,4>3) ? False
=And(2<6,7<4,5<1) ? False
*.Hàm Or

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
=OR(Btđk1,Btđk2,..,Btđkn)
Chức năng :
Hàm cho kết quả là đúng nếu 1 trong các Btđk đúng(True),kết quả là sai nếu tất các Btđk là sai(False)
Ví Dụ :
=Or(9>4,5>2,6<8) ? True
=Or(1>5,5<9,4>3) ? True
=Or(2<6,7<4,5<1) ? False
*.Hàm Rank

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Ví Dụ :
=Rank(x,DS,m)
Tham số :
x : là giá trị cần sắp xếp
DS : là địa chỉ( thường lấy địa chỉ tuyệt đối) vùng làm cơ sở để xếp thứ bậc
m : cách sắp xếp thứ bậc
Nếu m =0 :Giá trị lớn được xếp thứ trước
Nếu m =1 :Giá trị nhỏ được xếp thứ trước
*.Hàm Sumif :

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
=Sumif(VCĐK,"n",VCTT)
Trong đó :
VCĐK : là địa chỉ của vùng làm cơ sở để xét điều kiện
n : là điều kiện có thể gõ trực tiếp vào hoặc là địa chỉ của ô chứa điều kiện
VCTT : là địa chỉ của vùng cần tính tổng
Chức năng :
Hàm cho kết quả là tổng của các giá trị trên VCTT tương ứng với các giá trị thỏa mãn đk ở VCĐT
Ví Dụ :
*.Hàm Countif :

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
=Countif(VCĐK,"n")
Trong đó :
VCĐK : là địa chỉ của vùng làm cơ sở để xét điều kiện
n : là điều kiện có thể gõ trực tiếp vào hoặc là địa chỉ của ô chứa điều kiện
Chức năng :
Hàm cho kết quả là số các ô trong vùng VCĐK thỏa mãn điều kiện n
Ví Dụ :
Ví Dụ :
Để tính các ô từ E2 đến E6 ta dùng hàm Rank gõ công thức vào ô E2
=rank(B2,$B$2:$B$6,0) ?Enter rồi sao
chép công thức cho đến ô C6
Để tính ô D7 ta dùng hàm Sumif, gõ công thức vào ô D7 =sumif(C2:C6."1990",B2,B6) ?Enter ?24.8
Để tính ô D8 ta dùng hàm Countif, gõ công thức vào ô D8 =Countif(D2:D6,"Hà Tây") ?Enter ? 3
*.Nhóm Hàm với số :

Các Hàm Cơ Bản Trong Excel
Quy Ước chung :
n,m : Có thể là một số
Hay một địa chỉ ô chứa giá trị số
Hoặc một phép toán cho kết quả là một giá
trị số
Hàm lấy giá trị tuyệt đối :
Cú Pháp:
=Abs(n)
Ví Dụ :
Ô B2 = -6
=Abs(-21) ? 21
=Abs(B2) ? 6
=Abs(5+B2) ? 1
Hàm lấy phần nguyên
*.Nhóm Hàm với số :

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Ví Dụ :
=Int(n)
Ô B2 =21,69
=Int(18,568) ? 18
=Int(B2) ? 21
=Int(5,46+B2) ? 27
Hàm lấy phần dư của phép chia n cho m
Cú Pháp:
=Mod(n,m)
Ví Dụ :
Ô B2=22,C2=4
=Mod(B2,3) ? 1
=Mod(B2,C2) ? 2

Các Hàm Cơ Bản Trong Excel
Cú Pháp:
Ví Dụ :
*.Nhóm Hàm với số :

Hàm lấy Căn bậc hai
=Sqrt(n)
Ô B2=16,C2=81
=Sqrt(B2) ? 4
=Sqrt(64) ? 8
=Sqrt(Sqrt(C2)) ? 3
Hàm làm tròn số liệu
Cú Pháp:
=Round(n,m)
Ví Dụ :
Ô B2 = 21,4745
=Round(B2,1) ? 21,5 ;=Round(B2,0) ? 21
=Round(B2,2) ? 21,47
*.Nhóm Hàm Chuỗi :

Các Hàm Cơ Bản Trong Excel
Hàm Cắt Trái
Cú Pháp:
Ví Dụ :
=Left(st,n)
Trong đó : st có thể là chuỗi kí tự được đặt trong dấu nháy kép hay là địa chỉ ô chứa giá tri kiểu Text
B2 = Tin học Trẻ
=Left(B2,3) ? Tin
=Left("PhanTuan",4) ? Phan
Hàm Cắt Phải
=Right(st,n)
Cú Pháp:
Ví Dụ :
=Right(B2,3) ? Trẻ
=Right("PhanTuan",4) ? Tuan
Hàm cắt giữa

Cú Pháp:
=Mid(st,n,m)
Trong đó : st giống như hàm Left, Right
n : là vị trí bắt đầu cắt
m : là số ký tự cần lấy
Ví Dụ :
B2 = Tin học Trẻ
=Mid(B2,5,3) ? "học"
=Left("PhanTuan",2,5) ? "han T"
Chú ý :
Dấu cách ( Space ) cũng được coi là một ký tự
Ngoài ra còn rất nhiều hàm khác
Day : hàm lấy ngày
Month : hàm lấy tháng
Year : hàm lấy năm
Bài Giảng Đến Đây Kết Thúc
Chúc các bạn và cô giáo mạnh khỏe
Người thực hện :Phan Quang Tuấn
Hà Nội, ngày 08/12/2008