Tài liệu nâng cao Excel

Microsoft Excel
Microsoft Excel
Định dạng dữ liệu bảng tính
Tổng quan về Excel
Cơ sở dữ liệu trong bảng tính
Các hàm thường dùng và nâng cao trong Excel
Biểu đồ và In ấn (TLTK)
Microsoft Excel
TỔNG QUAN VỀ EXCEL
Tổng quan về Excel
Workbook
Sheet 255
Sheet 1
Rows
Sheet 2
.
.
.
Columns
Cells
Bật/ tắt lưới ô bảng tính
Tools – Options
Chọn Tab View: bật/ tắt Gridlines
Chọn OK
Địa chỉ tuyệt đối và Địa chỉ tương đối
Địa chỉ tương đối:
+ Cấu trúc: [Tên cột] [Tên dòng]
+ Đặc điểm: Khi sao chép công thức thì địa chỉ của nó thay đổi theo phương, chiều và khoảng cách
+ Ví dụ: B7
Địa chỉ tuyệt đối:
+ Cấu trúc: $ [Tên cột] $ [Tên dòng]
+ Đặc điểm: Khi sao chép công thức thì địa chỉ của nó không thay đổi theo phương, chiều và khoảng cách
+ Ví dụ: $B$7
Địa chỉ khác:
+ Cấu trúc:
$ [Tên cột] [Tên dòng]
[Tên cột] $ [Tên dòng]
Kiểu dữ liệu
Định dạng kiểu dữ liệu cho hệ thống
Tạo Split cho bảng tính
Microsoft Excel
ĐỊNH DẠNG DỮ LIỆU BẢNG TÍNH
Định dạng dữ liệu bảng tính
Format – Cells (Ctrl + 1)
Định dạng số (Number)
Format – Cells (Ctrl + 1) Number
Định dạng vị trí dữ liệu trong ô (Alignment)
- Horizontal: dữ liệu đặt theo hướng ngang
- Vertical: dữ liệu đặc theo hướng dọc
- Orientation: chọn hướng trãi dữ liệu
- Wrap text: trãi văn bản
- Merge cells: nối các ô được chọn
Format – Cells (Ctrl + 1) Alignment
Định dạng ký tự (Font)
Format – Cells (Ctrl + 1) Font
Định dạng đường biên (Border)
Format – Cells (Ctrl + 1) Border
Định dạng nền dữ liệu (Pattern)
Format – Cells (Ctrl + 1) Patterns
Microsoft Excel
CÁC HÀM TÍNH TOÁN VÀ THỐNG KÊ
Các hàm tính toán và thống kê
Hàm Sum
Cấu trúc: Sum(danh sách tham số)
Chức năng: cho kết quả là tổng của danh sách tham số
Ví dụ:
= Sum(2;4) = 6
= Sum(A2:A4): tính tổng các ô từ A2 đến A4
Hàm Average
Cấu trúc: Average(danh sách tham số)
Chức năng: cho kết quả là giá trị trung bình của danh sách tham số
Ví dụ:
= Average(2;4) = 3
= Average(A2:A4): cho kết quả là giá trị trung bình các ô từ A2 đến A4
Các hàm tính toán và thống kê
Hàm Max
Cấu trúc: Max(danh sách tham số)
Chức năng: cho kết quả là giá trị lớn nhất của danh sách tham số
Ví dụ:
= Max(2,4) = 4
= Max(A2:A4): cho kết quả là giá trị lớn nhất các ô từ A2 đến A4
Hàm Min
Cấu trúc: Min(danh sách tham số)
Chức năng: cho kết quả là giá trị nhỏ nhất của danh sách tham số
Ví dụ:
= Min(2,4) = 2
= Min(A2:A4): cho kết quả là giá trị nhỏ nhất các ô từ A2 đến A4
Các hàm tính toán và thống kê
Hàm Round
Cấu trúc: Round(số, n)
Chức năng: cho kết quả là giá trị số đã làm tròn theo n
Ví dụ:
= Round(254,187; -1) = 250
= Round(254,187; -2) = 300
= Round(254,187; -3) = 0
= Round(255,187; -1) = 260
Hàm Int
Cấu trúc: Int(n)
Chức năng: cho kết quả là giá trị phần nguyên của n
Ví dụ:
= Int(254,187) = 254
= Int(2,54) = 2
Các hàm tính toán và thống kê
Hàm Count
Cấu trúc: Count(danh sách tham số)
Chức năng: cho kết quả là số các giá trị thuộc kiểu số trong danh
sách tham số
Ví dụ:
= Count(14;”tinhoc”;2) = 2
= Count(“14”;”tinhoc”;2) = 1
Hàm CountA
Cấu trúc: CountA(danh sách tham số)
Chức năng: cho kết quả là số các giá trị trong danh sách tham số, không đếm các tham số chứa giá trị rỗng
Ví dụ:
= CountA(14;”tinhoc”;2) = 3
Các hàm tính toán và thống kê
Hàm Mod
Cấu trúc: Mod(số bị chia; số chia)
Chức năng: cho kết quả là giá trị phần dư của phép chia
Ví dụ:
= Mod(14,2) = 0
= Mod(14,3) = 2
Các hàm tính toán và thống kê
Hàm Rank
Cấu trúc: Rank(giá trị cần xếp hạng, vùng xếp hạng, phương
pháp xếp hạng)
Chức năng: cho kết quả là thứ hạng của giá trị cần xếp hạng
trong vùng xếp hạng.
Chú ý:
- Nếu vùng xếp hạng là địa chỉ thì ta lấy địa chỉ tuyệt đối
- Phương pháp bằng 1: Xếp hạng theo thứ tự tăng dần
- Phương pháp bằng 0: Xếp hạng theo thứ tự giảm dần
Ví dụ:
+ C2 = Rank(B2; $B$2:$B$5;1) = 2
+ C2 = Rank(B2; $B$2:$B$5;0) = 3
Các hàm tính toán và thống kê
Hàm Sumif
Cấu trúc: Sumif(vùng 1; điều kiện; vùng 2)
Chức năng: nếu vùng 1 thoả điều kiện thì cho kết quả là tổng
giá trị tương ứng trong vùng 2.
Ví dụ:
= Sumif(A2:A5; “A”; B2:B5) = 60
= Sumif(A2:A5; “B”; B2:B5) = 210
Các hàm tính toán và thống kê
Hàm Countif
Cấu trúc: Countif(vùng; điều kiện)
Chức năng: cho kết quả là số các ô trong vùng thoả điều kiện.
Ví dụ:
= Countif(A2:A5; “A”) = 2
= Countif(A2:A5; “B”) = 1
Microsoft Excel
CÁC HÀM LOGIC
Các hàm Logic
Các hàm Logic
Hàm If
Cấu trúc: If(biểu thức logic; giá trị 1; giá trị 2)
Chức năng: cho kết qủa là giá trị 1 nếu biểu thức logic đúng, ngược lại cho kết qủa giá trị 2 nếu biểu thức logic sai.
Ví dụ:
= If(mod(4,3)=0; “chia hết cho 3”; “không chia hết cho 3”)
= “không chia hết cho 3”
Các hàm Logic
C2 = If (B2 >=9, “Xuất sắc”, If(B2 >=8, “Giỏi”, If(B2 >=6.5, “Khá”, If(B2>=5, “Trung bình”, “Rớt”))))
Nếu ĐTB >=9: Xuất sắc
Nếu 8<= ĐTB < 9: Giỏi
Nếu 6.5 <= ĐTB < 8: Khá
Nếu 5<= ĐTB < 6.5: TBình
Nếu ĐTB <5: rớt
Microsoft Excel
CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU
Các hàm tìm kiếm và tham chiếu
Hàm Vlookup
Cấu trúc: Vlookup(x; S; n; phương thức)
Chức năng: Tìm giá trị x trên vùng tìm kiếm S, nếu tìm thấy thì
trả về giá trị cột thứ n (tính từ trái sang phải).
Chú ý:
S: nhận giá trị địa chỉ tuyệt đối;
Phương thức nhận giá trị 0 hoặc 1.
Ví dụ:
= Vlookup(“C”;$A$2:$C$5;2;0) = 6,7
{Tìm Điểm trung bình của học sinh “A”}
Các hàm tìm kiếm và tham chiếu
Hàm Hlookup
Cấu trúc: Hlookup(x; S; n; phương thức)
Chức năng: Tìm giá trị x trên vùng tìm kiếm S, nếu tìm thấy thì
trả về giá trị dòng thứ n (tính từ trên xuống dưới).
Chú ý:
S: nhận giá trị địa chỉ tuyệt đối;
Phương thức nhận giá trị 0 hoặc 1.
Ví dụ:
= Hlookup(“C”;$A$1:$E$3;2;0) = 6,7
{Tìm Điểm trung bình của học sinh “C”}
Microsoft Excel
CÁC HÀM TÌM CHUỖI
Các hàm chuỗi
Hàm Len
Cấu trúc: Len(S)
Chức năng: cho kết quả là độ dài của chuỗi S, trong đó ký tự trống cũng
được đếm như những ký tự khác.
Ví dụ:
= Len(“Tin hoc”) = 7
Hàm Left
Cấu trúc: Left(S,n)
Chức năng: cho kết quả là một chuỗi gồm n ký tự tính từ bên trái của chuỗi S.
Ví dụ:
= Left(“Tin hoc”;5) = “Tin h”
Hàm Right
Cấu trúc: Right(S,n)
Chức năng: cho kết quả là một chuỗi gồm n ký tự tính từ bên phải của chuỗi S.
Ví dụ:
= Right(“Tin hoc”;5) = “n hoc”
Các hàm chuỗi
Hàm nối hai chuỗi
Cấu trúc: S1 & S2
Chức năng: cho kết quả là một chuỗi được nối từ chuỗi S1 và chuỗi S2.
Ví dụ:
= “Tin” & “ hoc” = “Tin hoc”
Hàm Mid
Cấu trúc: Mid(S,m,n)
Chức năng: cho kết quả là một chuỗi gồm n ký tự tính từ ký tự thứ m của chuỗi S.
Ví dụ:
= Mid(“Tin hoc”;5;3) = “hoc”
Hàm Value
Cấu trúc: Value(S)
Chức năng: cho kết quả là giá trị số của chuỗi S.
Ví dụ:
= Mid(“2009”) = 2009
Microsoft Excel
CÁC HÀM THỜI GIAN
Hàm thời gian
Hàm Day
Cấu trúc: Day(dãy số kiểu ngày)
Chức năng: cho kết quả là ngày của dãy số kiểu ngày.
Ví dụ:
= Day(15/07/1979) = 15
Hàm Month
Cấu trúc: Month(dãy số kiểu ngày)
Chức năng: cho kết quả là tháng của dãy số kiểu ngày.
Ví dụ:
= Month(15/07/1979) = 07
Hàm thời gian
Hàm Year
Cấu trúc: Year(dãy số kiểu ngày)
Chức năng: cho kết quả là năm của dãy số kiểu ngày.
Ví dụ:
= Year(15/07/1979) = 1979
Hàm Now
Cấu trúc: Now()
Chức năng: cho kết quả là thời điểm hiện tại tính theo thời gian hệ thống. Hàm Now không có tham số.
Ví dụ:
= Now() = 10/08/2009 20:30
Microsoft Excel
CƠ SỞ DỮ LIỆU TRÊN BẢNG TÍNH
Sắp xếp bảng dữ liệu
B1. Chọn vùng CSDL cần sắp xếp
B2. Vào Data Sort…
Lọc dữ liệu bởi AutoFilter
B1. Chọn vùng CSDL
B2. Vào Data Filter AutoFilter
Lọc dữ liệu bởi Advanced Filter
B1. Chọn vùng CSDL
B2. Vào Data Filter Advanced Filter
Các hàm cơ sở dữ liệu
Hàm Dsum
Cấu trúc: Dsum(vùng 1, n, vùng 2)
Chức năng: cho kết quả là tổng giá trị cột thứ n của những dữ liệu
trên vùng 1 thoả điều kiện ở vùng 2.
Ví dụ:
Các hàm cơ sở dữ liệu
Các hàm cơ sở dữ liệu
Hàm Dmax
Cấu trúc: Dmin(vùng 1, n, vùng 2)
Chức năng: cho kết quả là giá trị nhỏ nhất cột thứ n của những dữ liệu trên vùng 1 thoả điều kiện ở vùng 2.
Ví dụ:
Các hàm cơ sở dữ liệu
Hàm Daverage
Cấu trúc: Daverage(vùng 1, n, vùng 2)
Chức năng: cho kết quả là giá trị trung bình cột thứ n của những dữ liệu trên vùng 1 thoả điều kiện ở vùng 2.
Ví dụ:
Các hàm cơ sở dữ liệu
Hàm Dcount
Cấu trúc: Dcount(vùng 1, n, vùng 2)
Chức năng: cho kết quả là số phần tử cột thứ n của những dữ liệu trên vùng 1 thoả điều kiện ở vùng 2.
Ví dụ:
Tổng số liệu theo nhóm
B1. Sắp xếp dữ liệu theo khoá cần thực hiện tổng hợp
+ Ví dụ: Tìm tổng lương theo đơn vị công tác
B2. Đặt con trỏ vào vùng dữ liệu và thực hiện lệnh: Data Subtotals
Tổng số liệu theo nhóm