Giáo trình Hướng dẫn sử dụng Excel 2010

Mục lục Excel 2010

I. Những điểm mới trong Microsoft Excel 2010 . 7

Thêm tính năng Sparkline . 8

Tính năng Slicers. 9

Định dạng dữ liệu có điều kiện . 10

PivotTables và PivotCharts . 11

Share Workbook . 12

I. Nội dung chính . 12

Chương 1: Làm Quen Với Microsoft Excel . 12

1.1 Giới thiệu Excel . 12

Excel là gì: . 12

Ribbon là gì? . 16

Sử dụng thực đơn ngữ cảnh (shortcut menu) . 17

1.2. Mở Excel, đóng Excel, phóng to, thu nhỏ cửa sổ . 18

Phóng to, thu nhỏ cửa sổ Excel . 18

Thu nhỏ cửa sổ Excel . 19

Phóng to cửa sổ Excel . 19

Thoát khỏi Excel . 19

1.3. Thao tác với ô và vùng. 19

Nhận dạng ô và vùng (cells, range) . 19

Chọn vùng. 20

Sao chép và di chuyển vùng . 20

Dán đặc biệt (Paste Special) . 21

Đặt tên vùng . 23

Thêm chú thích cho ô . 23

Chèn, xóa ô, dòng và cột. 24

Thay đổi độ rộng cột và chiều cao dòng . 26

Nối (Merge) và bỏ nối các ô (Split) . 28

Chuyển một ô đã nối về lại nhiều ô . 28

1.4. Di chuyển trong bảng tính và sử dụng phím tắt . 28

Thanh cuốn dọc, thanh cuốn ngang . 28

Thanh Sheet tab . 29

Sử dụng các tổ hợp phím tắt để di chuyển . 29

1.5 Thao tác với workbook . 31

Tạo mới workbook . 31

Lưu workbook . 32

Đóng workbook . 34

Sắp xếp workbook. 34

1.6. Thao tác với worksheet . 35

Chèn thêm worksheet mới vào workbook . 35

Đổi tên worksheet . 36

Xóa worksheet . 36

Sắp xếp thứ tự các worksheet . 36

Sao chép worksheet . 37

Chọn màu cho sheet tab . 37

Ẩn/ Hiện worksheet . 38

1.7 Sử dụng các chế độ hiển thị trong quá trình thao tác . 38

Sử dụng thanh Zoom . 39

Xem và so sánh worksheet trong nhiều cửa sổ . 39

Chia khung bảng tính làm nhiều phần và cố định vùng tiêu đề . 39

Sử dụng Watch Window . 41

Chương 2: Làm việc với dữ liệu trong Excel . 41

2.1. Nhập liệu, hiệu chỉnh . 41

Nhập li ệu . 41

Nhập các ký tự đặc biệt . 42

Hiệu chỉ nh nội dung . 43

Nhập đè lên ô có sẵn nội dung . 44

Sử dụng các kỹ thuật khi nhập liệu . 44

2.2. Định dạng . 48

Định dạng chung . 48

Tự động định dạng có điều kiện . 56

Bảng và định dạng bảng (table) . 57

Sử dụng mẫu định dạng tài liệu (Document Themes) . 59

2.3 Tìm và thay thế dữ liệu. 60

2.4 Sắp xếp và lọc dữ liệu . 61

Chương 3: Giới Thiệu Và Sử Dụng Các Hàm . 65

3.1 Giới thiệu công thức và hàm: . 65

Giới thiệu công thức (Formula) . 65

Giới thiệu hàm (Function) . 67

Nhập công thức và hàm . 68

Tham chiếu trong công thức . 70

Các lỗi thông dụng (Formulas errors) . 72

3.2 Các hàm trong excel . 73

a. Nhóm hàm về thống kê . 73

b. Nhóm hàm về phân phối xác suất. 75

c. Nhóm hàm về tương quan và hồi quy tuyến tính . 77

d. Các hàm tài chính - financian functions . 78

e. Danh mục các Các Hàm Quản l{ Cơ sở dữ liệu và Danh sách . 82

f. HÀM TOÁN HỌC VÀ LƯỢNG GIÁC . 99

g. HÀM XỬ LÝ VĂN BẢN VÀ DỮ LIỆU . 113

Chương 4: Khai Thác Cơ Sở Dữ Liệu . 130

4.1. Sort (sắp xếp) và Filter (lọc) . 130

Sắp xếp . 130

Lọc dữ liệu . 131

4.2 PivotTable và PivotChart . 132

4.2.1 Giới thiệu PivotTable và PivotChart . 133

Tạo một báo cáo PivotTable đơn giản . 133

4.2.2 Tìm hiểu dữ liệu nguồn của PivotTable . 147

4.2.3 Sử dụng các công cụ của PivotTable và định dạng PivotTable . 160

Chương 5: Đồ Thị Trong Excel . 172

5.1 Giới thiệu đồ thị . 172

5.2. Vẽ đồ thị . 173

5.3. Các thao tác trên đồ thị . 178

a. Nhận biết các thành phần trên đồ thị . 178

Các thành phần thông ụng . 178

 ột số thành phần chỉ có trong đồ thị 3-D . 178

b. Các thao tác với đồ thị . 179

Chọn thành phần trên đồ thị . 179

Di chuyển đồ thị . 179

Sao ch p đồ thị . 180

 óa đồ thị . 180

Thêm các thành phần của đồ thị . 180

Sắp xếp và xóa các thành phần của đồ thị . 180

 n đồ thị . 180

c. Hiệu chỉnh và định dạng đồ thị . 181

Hiệu chỉnh Chart Area . 181

Hiệu chỉnh Flot Area . 182

Hiệu chỉnh tiêu đề đồ thị, ch thích, tiêu đề trục hoành và trục tung, . 182

Hiệu chỉnh đường lưới ngang và ọc . 184

d. Các thao tác với chuỗi số liệu trong đồ thị . 188

 óa bớt một chuỗi số liệu khỏi đồ thị . 188

Thêm chuỗi mới vào đồ thị . 188

Thay đổi chuỗi số liệu . 189

Thêm đường xu hướng vào đồ thị . 191

Chương 6: Định Dạng Trang Và In Bảng Tính . 192

6.1. Các chế độ hiển thị trang trong Excel. 192

6.2. Thiết lập thông số cho trang in . 193

6.3. Thiết lập thông số hộp thoại Print . 198

6.4. Các lưu { khác . 200

Chương 7: Làm Việc Với Macro, Templates. 200

7.1 Macro . 200

Ghi một Macro . 200

Thực thi Macro . 202

Chương 8: Phím Tắt Và Thủ Thuật . 203

8.1 Phím Tắt . 203

a. Truy cập Ribbon bằng bàn phím . 203

b. Phím tắt . 203

8.2 Thủ thuật . 206

1. Dịch số tiền về chữ (Chuyển số thành chữ) . 206

2. Giấu bảng tính Excel chuyên nghiệp . 212

3. Khóa và bảo vệ những ô có chứa công thức . 212

4. Sử dụng Data-Validation khi danh sách nguồn nằm trong một Sheet khác . 219

5. Điều khiển Conditional Formating bằng checkbox. . 221

6. Đánh ấu những ô chứa công thức bằng Conditional Formatting . 228

7. Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn. . 229

8. Chuyển đổi con số dạng văn bản sang số thực . 230

9. Tăng thêm số lần Undo cho Excel . 232

10. Tách họ và tên trong cùng một cột trên bảng tính Excel . 236

11. Tạo mục lục trong Excel . 237

pdf240 trang | Chuyên mục: Excel | Chia sẻ: dkS00TYs | Ngày: 01/10/2014 | Lượt xem: 1232 | Lượt tải: 24download
Tóm tắt nội dung Giáo trình Hướng dẫn sử dụng Excel 2010, để xem tài liệu hoàn chỉnh bạn click vào nút "TẢI VỀ" ở trên
g số thực 
Các giá trị số (number) trong Excel được mặc định canh lề phải và văn bản (text) thì canh lế trái. Do 
vậy, cách đơn giản để nhận biết các giá trị số và văn bản trong một cột trên bảng tính là bạn thiết lập 
chế độ canh lề mặc định cho cột đó. Bạn vào Home ➝ nhóm Alignment ➝ ch n Format Cells ➝ vào 
Tab Alignment ➝ chọn General tại hộp Horizontal để thiết lập việc canh lề mặc định cho cột đang 
chọn ➝ nhấn OK để đóng hộp thoại Format Cells lại. 
Bạn kéo cột rộng ra một ít để đễ phân biệt việc canh lề, khi đó bạn sẽ thấy các giá trị số, ngày tháng 
sẽ được canh lề phải và văn bản sẽ được canh lề trái. 
Dùng Paste Special để chuyển giá trị số dạng văn bản sang số thực 
Đây là cách nhanh và ễ dàng nhất để chuyển các giá trị số ang văn bản sang số thực. Các bước 
thực hiện như sau: 
Chọn một ô trống nào đó và nhấn lệnh Copy (Ctrl + C)➝ quét ch n vùng s liệu dạng văn bản định 
chuyển đổi (ví dụ như vùng A1:A9 ở hình trên) ➝ nhấp phải chuột và chọn Paste Special ➝ ch n 
Add tại nhóm Operation ➝ nhấn OK để hoàn tất. 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
231 
Việc làm trên sẽ giúp chuyển toàn số các con số dạng văn bản sang số thực, vì ô rỗng có giá trị là 0 và 
khi bạn cộng bất kz số nào vào một con số lưu ưới dạng văn bản trong Excel thì bạn đã làm cho con 
số dạng văn bản chuyển thành số thực. 
Dùng các hàm TEXT để chuyển đổi 
Bạn có thể áp dụng nguyên tắc như cách trên vào một số hàm có sẵn của Excel để thực hiện việc 
chuyển đổi. Thông thường, khi bạn dùng một hàm thuộc nhóm TEXT và kết quả trả về ưới dạng con 
số thì Excel vẫn xem con số đó là giá trị dạng văn bản. 
Giả sử bạn có một vùng dữ liệu A1:A7 như hình sau: 
Bạn ùng hai hàm trong nhóm TE T là LEFT và F ND để tách các giá trị ra khỏi các chuỗi văn bản như 
sau: 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
232 
=LEFT(A1,FIND(" ",A1)-1) 
Sau khi ùng hàm để tách phần giá trị ra thì các kết quả trả về vẫn được Excel xem như là văn bản vì 
ch ng được canh lề trái như hình sau: 
Do vậy, bạn cần phải hiệu chỉnh công thức tác chuỗi trên một ít để kết quả trả về là các con số thực 
sự bằng cách cộng thêm số 0 vào sau công thức trên: 
=LEFT(A1,FIND(" ",A1)-1) + 0 
9. Tăng thêm số lần Undo cho Excel 
Tất cả chúng ta đều đã quen thuộc với chức năng Undo của Excel, cho phép chúng ta làm lại 
những sai lầm của mình. Tuy nhiên, mặc định, chúng ta chỉ có thể Undo được 16 lần. Chiêu 
này giúp bạn có thể tăng s lần Undo lên nhiều hơn, có thể đ n 100 lần. 
Khi bạn sử dụng chức năng Undo của Excel, và bạn đã th c hiện Undo 16 lần, thì nút Undo 
bị mờ đi, không thể Undo được nữa. Ngoài ra, khi bạn nhấn nút Save để lưu bảng tính, thì nút 
Undo cũng bị mờ đi, và danh sách những tác vụ (những hành động đã th c hiện trên bảng 
tính) mà bạn đã th c hiện bị mất sạch. Đó là do khi bạn lưu bảng tính, Excel cho rằng bạn đã 
hài lòng với bảng tính này (thì mới nhấn Save), và nó thấy rằng không cần thi t phải giữ lại 
danh sách những tác vụ trước khi lưu nữa. 
Bạn có thể thấy rằng, quay lui lại 16 lần là không đủ, nhưng làm cách nào để tăng s lần này 
lên? Nghĩa là làm cách nào để tăng danh sách các tác vụ của bạn lên? Thưa rằng, có cách, và 
bạn có thể tăng con s này lên đ n 100 lần. 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
233 
Để làm điều này, trước h t, bạn hãy thoát khỏi Excel. Sau đó bạn chạy lệnh Run của 
Windows (Start | Run, hoặc nhấn phím Window + R), gõ vào đó Regedit.exe và nhấn OK. 
Khi bạn đã mở được chương trình Regedit, hãy tìm đ n khóa: 
HKEY_CURRENT_USER \ Software \ Microsoft \ Office \ 11.0 \ Excel \ Options 
Con s 11.0 ở trên đây có thể khác, ví dụ với Excel2000 thì nó là 10.0, với Excel 2010 thì nó 
là 12.0 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
234 
Nhấn Edit | New | DWORD Value (xem hình). Một mục mới New Value #1 sẽ được tạo ra 
trong khung bên phải, bạn nhập vào đó chữ UndoHistory, và nhấn Enter. 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
235 
Nhấn đúp (double-click) vào mục UndoHistory mới tạo, rồi gõ vào hộp thoại mở ra một con 
s bất kỳ, miễn là lớn hơn 16 và nhỏ hơn 100. Nhấn OK, rồi thoát Regedit. 
Bạn hãy khởi động lại Excel và thử làm gì trong đó, rồi nhấn vào nút Undo xem bạn có thể 
Undo được bao nhiêu lần nhé. Hy v ng rằng Undo 100 lần, đã là quá đủ cho bạn. Tuy nhiên, 
n u bạn nhấn lưu bảng tính, thì danh sách các tác vụ có thể Undo 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
236 
10. Tách họ và tên trong cùng một cột trên bảng tính Excel 
Bước 1: Khi nhập liệu vào bảng tính Excel, thông thường ta nhập cả h và tên vào cùng một 
ô. Tuy nhiên, có khi sau đó ta cần phải tách riêng tên ra khỏi h và tên đệm, n u làm thủ công 
bằng thao tác sao chép rồi dán qua ô khác thì sẽ rất bất tiện. N u sử dụng hàm Right k t hợp 
với một vài hàm khác thì cũng có thể cho ra k t quả như bạn mu n, nhưng trong một s 
trường hợp sẽ cho k t quả không chính xác. Có một phương pháp t i ưu hơn sẽ giúp bạn 
hoàn thành t t công việc này, cách th c hiện cũng khá đơn giản như sau: 
Bước 2: - Trên bảng tính chứa cột h tên cần tách, nhấn Alt+F11 
Bước 3: - Ti p theo, bạn nhấn menu Insert và ch n Module sẽ xuất hiện một cửa sổ soạn 
thảo. 
Bước 4: Bạn nhập đoạn mã lệnh sau vào cửa sổ soạn thảo: 
Private Function Tachten(ten As String, lg As Integer) 
Dim j As Integer 
Name = Trim(ten) 
For j = Len(Name) To 1 Step -1 
If Mid(Name, j, 1) = " " Then 
If lg = "1" Then 
Tachten = Right(Name, Len(Name) - j) 
Else 
Tachten = Left(Name, j) 
End If 
Exit For 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
237 
End If 
Next 
End Function 
Bước 5:- Bạn nhấn Ctrl+S để lưu lại và quay trở về bảng tính Excel. 
Bước 6:- Bây giờ để ti n hành tách riêng h và tên đệm vào một ô mới, bạn đặt con trỏ tại ô 
mới chứa h và tên đệm rồi nhập vào công thức =TachTen(B3,0) và nhấn Enter sẽ thấy ngay 
k t quả. 
- Để tách riêng tên vào một ô mới, bạn đặt con trỏ tại ô sẽ chứa tên cần tách và nhập vào công 
thức =TachTen(B3,1) rồi nhấn Enter là xong. 
Ghi chú: B3 chính là địa chỉ của ô ban đầu chứa cả h , tên đệm và tên. 
11. Tạo mục lục trong Excel 
Nếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất nhiều worksheet 
(trang tính), bạn sẽ cảm thông được sự khó khăn trong việc tìm kiếm một trang tính nào đó. 
Khi đó, có lẽ ta nên lập một chỉ mục các trang tính đang có để thuận tiện cho việc điều 
hướng trong bảng tính. 
Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng và dễ dàng 
điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưa bạn đến chính xác nơi bạn 
muốn đến mà không lo bị nhầm lẫn. Bạn có thể tạo một chỉ mục trong một vài cách: bằng 
tay, tự động tạo ra bởi mã VBA, hoặc là sử dụng trình đơn tùy chọn theo ngữ cảnh (thậm 
chí có thể dùng các hàm Macro4 – không trình bày ở đây). 
Tạo chỉ mục thủ công 
Cách này rất dễ làm, bạn chỉ cần chèn mới một worksheet và đăt cho nó một cái tên, ví dụ 
như tên là Index. Sau đó, bạn nhận vào tên của các worksheet có trong workbook và tạo 
các siêu liên kết (hyperlink) đến các worksheet tương ứng với tên mà bạn nhập. 
Để tạo hyperlink bạn chọn tên sheet, sau đó vào Insert | chọn Hyperlink tại nhóm Links | 
Insert | Hyperlinks] hoặc nhấn phím tắt là Ctrl+K để mở hộp thoại Insert Hyperlink. 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
238 
Chọn Place in This Document và chọn tên Sheet muốn kết nối tới. Nhấn nút OK để hoàn 
tất. 
Phương pháp này phù hợp khi bảng tính không có quá nhiều worksheet và tên worksheet 
không có sự thay đổi thường xuyên, nếu không sẽ gây ra nhiều khó khăn cho công tác bảo 
trì trang chỉ mục. 
Tạo chỉ mục tự động bằng cách sử dụng VBA 
Một cách thay thế khác là sử dụng VBA để tự động tạo ra các chỉ mục. Đoạn mã sau đây sẽ 
tự động tạo ra một bảng chỉ mục liên kết đến tất cả các worksheet mà bạn có trong 
workbook. Bảng chỉ mục này sẽ được tạo lại mỗi khi worksheet lưu chỉ mục được chọn. 
Đoạn mã này phải được đặt trong private module của Sheet chứa chỉ. Chèn một worksheet 
mới vào workbook và đặt tên cho nó là Index chẳng hạn. Nhấp chuột phải vào tên 
worksheet vừa tạo và chọn ViewCode từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phím 
Alt+F11. 
Nhập đoạn mã VBA sau vào cửa sổ Code: 
Private Sub Worksheet_Activate() 
 Dim wSheet As Worksheet 
 Dim lCount As Long 
 lCount = 1 
Phiên bản thử nghiệm – Lưu hành nội bộ - Microsoft Vietnam 
239 
With Me 
 .Columns(1).ClearContents 
 .Cells(1, 1) = "INDEX" 
End With 
For Each wSheet In Worksheets 
 If wSheet.Name Me.Name Then 
 lCount = lCount + 1 
 With wSheet 
 .Range("A1").Name = "Start" & wSheet.Index 
 .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _ 
 "Index", TextToDisplay:="Back to Index" 
 End With 
 Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), Address:="", SubAddress:= _ 
 "Start" & wSheet.Index, TextToDisplay:=wSheet.Name 
 End If 
Next wSheet 
End Sub 
Nhấn tổ hợp phím Alt+Q để thoát VBE và trở về cửa sổ bảng tính, sau đó lưu bảng tính lại. 
Để chạy đoạn mã vừa tạo, bạn dùng chuột chọn sang worksheet khác trong workbook và 
sau đó chọn lại worksheet Index để kích hoạt sự kiện Worksheet_Activate. 
Lưu ý rằng, đoạn mã sẽ các đặt tên (Name) cho các ô A1 ở mỗi worksheet kèm theo số chỉ 
mục của worksheet trong bảng tính (worksheet đầu tiên có chỉ mục là 1, kế đó là 2, 3…. n). 
Điều này bảo đảm rằng ô A1 trên mỗi trang tính có một tên khác nhau. Nếu ô A1 trên 
worksheet của bạn đã được đặt tên, bạn nên cân nhắc đến việc thay đổi ô A1 trong đoạn 
mã sang một địa chỉ khác phù hợp hơn. 
Lưu ý, nếu bạn có thiết lập Hyperlink base (siêu liên kết cơ sở) trong workbook thì các 
hyperlink được tạo ra từ đoạn mã trên sẽ không thể hoạt động được, do chúng đã liên kết 
đến các Name trong workbook hiện hành. Khi thuộc tính hyperlink base được thiết lập thì 
các siêu liên kết sẽ trỏ đến hyperlink base kết hợp với các Name. 

File đính kèm:

  • pdfexcel_2010_1677.pdf
Tài liệu liên quan