Tài liệu MS EXCEL NÂNG CAO ppt

LINK DOWNLOAD MIỄN PHÍ TÀI LIỆU "Tài liệu MS EXCEL NÂNG CAO ppt": http://123doc.vn/document/1039822-tai-lieu-ms-excel-nang-cao-ppt.htm

Trung tâm ngoại ngữ - tin học Úc Anh - APT 5
 Một sai sót nhỏ trong việc định nghĩa các tọa độ của vùng chọn có thể dẫn đến kết quả tệ hại,
nhất là khi bạn xóa một vùng chọn.
-Bạn có thể khắc phục những vấn đề trên bằng cách sử dụng tên cho vùng chọn (range name), là các
tên được gán cho một ô hoặc một dãy ô. Với một tên đã được định nghĩa, bạn có thể sử dụng nó thay
thế cho các tọa độ của vùng chọn.
Ví dụ: để đưa vùng chọn vào công thức, bạn sử dụng tên của vùng chọn đó thay vì chọn vùng chọn
hoặc nhập tọa độ của nó. Bạn có thể tạo bao nhiều tên cho vùng chọn tùy thích và thậm chí bạn có thể
gán nhiều tên cho cùng một vùng chọn.

-Tên của vùng chọn làm cho các công thức của bạn trở nên trong sáng, dễ nhìn, dễ đọc và dễ hiểu hơn.
Ví dụ: việc gán tên DSo_8(Doanh số tháng 8) vào một vùng chọn, chẳng hạn như vùng chọn
E6:E10, sẽ làm rõ ngay mục đích của một công thức, chằng hạn như =SUM(DSo_8) nghĩa là tổng
doanh số của tháng 8. Các tên của vùng chọn cũng làm tăng tính chính xác của các phép tính với
vùng chọn bởi vì bạn không cần phải xác định tọa độ của vùng chọn nữa.
-Ngoài ra, sử dụng tên cho vùng chọn còn có nhiều ưu điểm sau đây:
 Các tên dễ nhớ hơn các tọa độ vùng chọn.
 Các tên không thay đổi khi bạn di chuyển một vùng chọn sang một chỗ khác trong bảng tính.
 Các vùng chọn được đặt tên tự động điều chỉnh bất cứ khi nào bạn chèn hoặc xóa các hàng
hoặc các cột trong vùng chọn.
 Các tên làm cho bạn dễ định hướng một bảng tính hơn. Bạn có thể sử dụng lệnh GoTo để
nhảy nhanh đến một vùng chọn đã được đặt tên.
b. Nguyên tắc đặt tên cho vùng chọn
Các tên của vùng chọn có thể hoàn toàn linh hoạt, nhưng bạn cần tuân theo một vài giới hạn và quy tắc
sau đây:
 Tên chỉ có thể có tối đa 255 ký tự.
Trung tâm ngoại ngữ - tin học Úc Anh - APT 6
 Tên phải bắt đầu với một chữ cái hoặc ký tự gạch dưới (_). Đối với phần còn lại của tên, bạn có
thể sử dụng bất kỳ ký tự nào (chữ, số, symbol) ngoại trừ khoảng trắng. Đối với các tên có nhiều
từ, bạn nên tách các từ bằng dấu gạch dưới (ví dụ: Số_Tài_Khoản) hoặc bằng cách dùng chữ
hoa chữ thường (ví dụ: SốTàiKhoản), vì Excel không phân biệt chữ thường và chữ hoa khi bạn
sử dụng tên vùng chọn trong bảng tính.
 Không sử dụng các địa chỉ ô (chẳng hạn như Q1) hay bất kỳ ký hiệu toán tử nào (+, -, *, /, <, >,
và &) bởi vì những ký hiệu này có thể gây rắc rối cho bạn, nhất là khi sử dụng tên của vùng
chọn trong công thức.
 Để việc nhập tên của vùng chọn dễ dàng hơn và nhanh hơn, bạn nên cố gắng đặt tên cho vùng
chọn càng ngắn càng tốt nhưng vẫn giữ được ý nghĩa của chúng. Ví dụ: TSoLN_07 dễ nhập hơn
Tổng_Số_Lợi_Nhuận_Năm_2007, và dễ hiểu hơn TSLN07.
 Không sử dụng các tên có sẵn của Excel. Những tên đó là: Auto_Activate, Auto_Close,
Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract,
FilterDatabase, Print_Area, Print_Titles, Recorder, và Sheet_Title.
Bạn nên ghi nhớ những quy tắc trên, trước khi học tiếp cách đặt tên cho một vùng chọn.

c. Sử dụng Name Box (hộp Name) để đặt tên
Name Box nằm ở phía ngoài cùng bên trái của thanh công thức, trong trường hợp bạn không thấy nó,
bạn chọn menu View và đánh dấu chọn Formula Bar. Thường thì Name Box hiển thị địa chỉ của ô
hiện hành, tuy nhiên nó cũng cung cấp thêm một vài tính năng nhằm giúp bạn làm việc với các tên của
vùng chọn một cách dễ dàng hơn:
 Sau khi bạn đã đặt tên cho một vùng chọn, tên này sẽ xuất hiện trong Name Box bất cứ khi nào
bạn chọn vùng chọn đó (xem minh họa ở hình dưới)
 Name Box là một drop-down list (nghĩa là một cái danh sách xổ xuống). Để chọn nhanh một
vùng chọn đã được đặt tên, bạn mở danh sách xổ xuống này và chọn tên của vùng chọn mà bạn
muốn, Excel sẽ di chuyển ngay đến vùng chọn đó và chọn nó.
Trung tâm ngoại ngữ - tin học Úc Anh - APT 7
 Phiên bản nào của Excel cũng có Name Box, nhưng Excel 2007 có một tính năng mới rất tiện
lợi, là bạn có thể thay đổi kích cỡ của Name Box. Nếu bạn không thể thấy hết tên của vùng
chọn hiện hành (do nó dài quá), bạn di chuyển cursor (con trỏ chuột) sang mép phải của Name
Box (ngay chỗ có một cái núm tròn lõm xuống), cursor sẽ biến thành một mũi tên hai đầu nằm
ngang, bạn chỉ việc nhấp và rê chuột qua lại để định lại kích cỡ cho Name Box.

Khi bạn chọn một vùng chọn có tên đã được định nghĩa tên sẽ xuất hiện trong Name Box(cụ thể ở đây,
Advertising là tên của vùng chọn C4:F4)
Name Box cũng ngẫu nhiên là cách dễ nhất để đặt tên cho một vùng chọn. Bạn làm theo các bước sau:
B1: Chọn vùng chọn mà bạn muốn đặt tên.
B2: Nhấp chuột vào bên trong Name Box để hiển thị một điểm chèn.
B3: Nhập tên mà bạn muốn đặt cho vùng chọn và nhấn Enter. Excel sẽ tự động định nghĩa một tên
mới.
d. Sử dụng hộp thoại Define Name để đặt tên
B1: Chọn dãy mà bạn muốn đặt tên.

B2: Chọn Formulas, Define Name. Hoặc nhanh hơn: nhấp phải (right-click) vào vùng chọn và sau
đó chọn Name a Range
Trung tâm ngoại ngữ - tin học Úc Anh - APT 8

Khi bạn mở hộp thoại New Name để đặt tên cho một dãy, tọa độ của dãy tự động xuất thiện trong
khung Refers to
B3: Nhập tên mà bạn muốn đặt cho dãy vào khung Name.

B4: Sử dụng danh sách Scope để chọn phạm vi mà tên dãy này có ảnh hưởng đến. Mặc định, nó sẽ
là Workbook (toàn bảng tính)
B5: Nhập một vài mô tả hoặc ghi chú về dãy này trong khung Comment. Những mô tả hoặc ghi
chú này sẽ xuất hiện khi bạn sử dụng tên dãy trong công thức.
Quản lý các tên vùng vào Formulas/ Name Manager

- New: thêm mới
- Edit: chỉnh sửa
Trung tâm ngoại ngữ - tin học Úc Anh - APT 9
- Delete: xóa
BÀI 2: CÁC CÔNG CỤ TÙY CHỌN TRONG EXCEL
1.Conditional Formatting (Định dạng màu sắc theo điều kiện)
Giới thiệu
Định dạng theo điều kiện là công cụ cho phép bạn áp dụng định dạng cho một ô (cell) hay nhiều
ô (range of cells) trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công
thức.
Ví dụ như bạn có thể tạo cho định dạng của ô đó là chữ in đậm màu xanh khi giá trị của nó lớn
hơn 100. Khi giá trị của ô thoả điều kiện thì các định dạng bạn tạo ra ứng với điều kiện đó sẽ được áp
dụng cho ô đó. Nếu giá trị của ô không thoả điều kiện bạn tạo ra thì định dạng của ô đó sẽ áp dụng
định dạng mặc định (default formatting) .
Quy tắc chung trong tô màu:
B1: Chọn vùng cần định dạng tô màu (không chọn tiêu đề)
B2: Menu Home/ Conditional Formatting
- New rule : tạo mới 1 định dạng
- Clear rule: xóa các định dạng
- Manage Rules: quản lý các định dạng
B3: Chọn New Rule để tạo mới -> xuất hiện hộp thoại New Formatting Rule

- Format all cells based on their values : định dạng cho tất cả các ô
Trung tâm ngoại ngữ - tin học Úc Anh - APT 10
- Format only cells that contain: định dạng cho từng ô theo điều kiện có sẵn
- Format only top or bottom ranked value: định dạng cho dữ liệu cao nhất hoặc thấp
nhất
- Format only values that are above or below average: định dạng dữ liệu so sánh với
trung bình
- Format only unique or duplicate values: định dạng cho các dự liệu duy nhất hoặc
trùng nhau
- Use a formula to determine which cell to format: sử dụng công thức hàm để định
dạng.
1.1.Định dạng cho ô dữ liệu theo các điều kiện có sẵn
B1: Vào CF / New Rule -> Format only cells that contain

B2: Chỉnh các tùy chọn
- Tùy chọn đầu tiên : chọn kiểu dữ liệu cần định dạng
+ Cell value: ô chứa giá trị
+ Specific text: ô về chuỗi
+ Date Occurring: ô về ngày tháng
+ Blanks: ô trống
+ No Blank: ô không trống
+ Errors: ô bị lỗi
+ No Errors: ô không bị lỗi
- Tùy chọn thứ 2: chọn toán tử so sánh
+ Between: trong khoảng
Trung tâm ngoại ngữ - tin học Úc Anh - APT 11
+ Not between: ngoài khoảng
+ Equal to: bằng
+ Not equal to: không bằng
+ Greater than: lớn hơn
+ Less than: nhỏ hơn
+ Greater than or equal: lớn hơn hoặc bằng
+ Less than or equal to: nhỏ hơn hoặc bằng
- Tùy chọn thứ 3: nhập giá trị cần so sánh
- Format : định dạng màu sắc, font chữ, …
VD: Định dạng cho những ô có giá trị >= 100 nền xanh chữ đen

B3: OK
1.2.Định dạng cho các ô có giá trị cao nhất hoặc nhỏ nhất
B1: CF/ New Rule -> Format only top or bottom ranked values
Trung tâm ngoại ngữ - tin học Úc Anh - APT 12

B2: Chọn kiểu định dạng: Top (cao nhất) or Bottom(thấp nhất) , nhập số ô cần lấy
B3: Nhấn Format để định dạng -> OK
1.3.Định dạng cho những ô có giá trị trên hoặc dưới trung bình
B1: CF/ New Rule -> Format only values that are above or below average

B2: Mục Format values that are: chọn kiểu so sánh
B3: Nhấn Format để định dạng -> OK
1.4.Định dạng cho những ô có giá trị duy nhất hoặc trùng lặp
B1: CF/ New Rule -> Format only unique or duplicate values
Trung tâm ngoại ngữ - tin học Úc Anh - APT 13

B2: Mục Format all: chọn kiểu so sánh
-Duplicate: là trùng nhau
-Unique: là duy nhất
B3: Nhấn Format để định dạng -> OK
1.5. Sử dùng hàm để định dạng có điều kiện
B1: CF/ New Rule -> Use a formula to determine which cells to format

B2: Mục Format values where this formula is true: nhập biểu thức hàm điều kiện cần định dạng
a) Định dạng dòng chẵn: =Mod(row(),2)=0
Trung tâm ngoại ngữ - tin học Úc Anh - APT 14
b) Định dạng dòng lẻ: = Mod(row(),2)>0
c) Định dạng cột chẵn: = Mod(column(),2)=0
d) Định dạng cột lẻ: = Mod(column(),2)>0
e) Định dạng cho N mẫu tin lớn nhất: VD định dạng 2 mẫu tin có đơn giá lớn nhất
= ô đầu tiên của cột cần so sánh>=LARGE(vùng chứa ô so sánh, số mẫu cần lấy)
= $G3>=LARGE($G$3:$G$6,2)

f) Định dạng cho N mẫu tin nhỏ nhất: VD định dạng 2 mẫu tin có đơn giá thấp nhất
= ô đầu tiên của cột cần so sánh<=SMALL(vùng chứa ô so sánh, số mẫu cần lấy)
= $G3>=SMALL($G$3:$G$6,2)

g) Định dạng theo điều kiện bất kỳ:
Có thể sử dụng hàm để đặc điều kiện cho vùng dữ liệu
VD: = C2>=100 ( đinh dạng các ô lớn hơn 100)