Tài liệu bài giảng Excel nâng cao

End If 
Max2So = max 
 End Function 
Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn 
Nguyễn Thanh Long - Excel Nâng cao Trang 34 
* Tạo Add-in cho Excel: 
Cách tạo add-in hàm tính thế thu nhập cá nhân (Hàm này ta có thể tự viết hoặc sưu tầm trên mạng về để 
dùng cho mục đích riêng). 
Để các hàm VB (user-defined) có thể dùng chung cho nhiều tập tin Excel, bạn chỉ cần gom chúng lại 
thành tập tin .XLA (Microsoft Excel Add-In). Cách làm như sau: 
1. Khởi động Excel, tạo một workbook mới. Chọn Tools > Macro > Visual Basic Editor (Alt+F11) để mở 
cửa sổ soạn thảo VBA. Trong cửa sổ Project Explorer (nếu không thấy thì chọn View > Project Explorer), 
chọn VBAProject (Book1). Chọn Insert > Module để thêm một module vào workbook. 
2. Gõ nội dung hàm CodeUni của bạn vào module mới tạo. 
Function CodeUni(text As String) As Integer 
CodeUni = AscW(text) 
End Function 
3. Chọn Tools > Macro > Macros để hiển thị hộp thoại Macro. Gõ tên hàm CodeUni vào ô Macro name, 
bấm Options để mở tiếp hộp thoại Macro Options, gõ nội dung mô tả hàm vào ô Description, bấm OK để 
đóng hộp thoại Macro Options, bấm Cancel để đóng hộp thoại Macro. 
4. Ghi workbook lên đĩa thành tập tin CodeUni.xla. Lưu ý: bạn phải đặt tập tin này đúng chỗ để Excel tự 
động nạp nó khi khởi động. Ví dụ, đối với Excel XP trên Windows XP, chọn thư mục C:\Documents 
and Settings\UserName\Application Data\Microsoft\AddIns; Đối với Vista thì: 
UserName\AppData\Roaming\ Microsoft\AddIns. Đóng Excel mà không cần ghi lại các thay đổi trên 
Book1. 
Trong đó: Username là tên người sử dụng máy tính. 
Ghi chú: Có thể copy file CodeUni.xla vào một thư mục bất kỳ rồi khi browse thì chỉ đường dẫn đến 
vị trí của file CodeUni.xla 
5. Khởi động lại Excel, cài đặt add-in vừa tạo cho Excel bằng cách chọn Tools > Add-Ins, chọn tên add-in 
cần cài đặt trong danh sách Add-Ins available, rồi bấm OK. 
Từ đây trở về sau, mỗi lần mở Excel, bạn đương nhiên sử dụng được hàm CodeUni() trong add-in 
CodeUni.xla. 
Buổi 10: Thi cuối khóa 
Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn 
Nguyễn Thanh Long - Excel Nâng cao Trang 35 
Một số thủ thuật hay với Excel: 
Hiển thị đối số của các hàm: 
Để xem đối số trong một công thức, hãy nhấn Ctrl- Shift- A. 
 Ví dụ: Nếu bạn gõ =RATE và nhấn Ctrl- Shift -A, bạn có thể nhìn thấy tất cả các đối số cho hàm này 
(ví dụ =RATE (nper, pmt, pv, fv, type, guess)). 
Nếu bạn muốn biết chi tiết hơn, gõ =RATE. Và nhấn Ctrl+A để hiển thị theo Function Wizard. 
Tham chiếu các hàm: 
Nếu bạn ko nhớ hết các hàm thì có thể mở Insert Funtion để xem trước hướng dẫn các hàm. 
Nhìn trên thanh công cụ có ký hiệu fx hoặc dùng phím tắt Shift-F3, Excel sẽ đưa ra hộp thoại chứa tất 
cả các hàm ứng dụng và hướng dẫn các cú pháp cụ thể cho từng hàm khi bạn di chuyển con trỏ điểm 
sáng đến hàm muốn tham chiếu. Đây cũng là cách nhập công thức nhanh mà không cần gõ toàn bộ cú 
pháp. 
Sử dụng ô tham chiếu và nhãn text trong các công thức: 
Để sử dụng các tham chiếu ô cùng với nhăn text trong một công thức, bạn chỉ việc gõ một ký hiệu (&) 
ở giữa tham chiếu và text. Ví dụ, để hiển thị dòng dữ liệu là "25 Departments", gõ (=A1 & 
"Departments"), trong đó A1 chứa số 25. 
Làm thế nào để ấn định một macro tới một nút? 
Bạn muốn chạy macro hay hàm chỉ bằng một thao tác nhắp vào một nút? Bằng cách gán một hàm hay 
một macro tới một nút tuỳ biến bạn có thể thực hiện rất nhanh chóng mà không phải chọn 
Tools/Macro. Để ấn định một macro tới một nút, chọn View/Toolbars/Customize. Nhắp vào tab 
Commands và chọn Macros trong điều khiển Categories. Nếu đã có sẵn ở ngoài thì bạn chọn 
View/Toolbars/Control Toolbox hoặc Click phải chuột lên thanh công cụ check vào Control 
Toolbox 
Tách Họ và Tên thành từng cột riêng biệt: 
Chọn vùng cần tách vào Menu Data/Text to Columns/chọn Delimited/chọn Space/Mục Destination 
chọn vùng cần tách/Chọn Finish. 
Hiển thị công thức một cách an toàn 
Bạn nhớ phải luôn luôn ghi bảng tính trước khi chuyển tới công thức hiển thị bằng cách ấn Ctrl - ~. 
Khi bạn quay lại bảng tính sau khi hiển thị các công thức, bạn có thể tìm thấy một vài định dạng khác 
nhau. Ví dụ, Excel có thể đã thay đổi độ rộng cột cùng một vài cột khác của bạn. Nếu điều này xảy ra, 
đơn giản mở lại bảng tính để quay lại định dạng đã mất. Phương pháp khác là chuyển bảng tính theo 
cách ở trên và tắt bằng cách vào menu Tools/Options chọn Views và đánh dấu chọn vào ô Formulas. 
Nhắp và OK để kết thúc. 
Sắp xếp các ký tự in hoa và in thường 
Excel không quan tâm đến trường hợp các ký tự chữ cái khi sắp xếp, trừ khi bạn thông báo tới nó. 
Nếu bạn muốn phân biệt giữa chữ in hoa và in thường trong quá trình sắp xếp, chọn dữ liệu bạn muốn 
sắp xếp, chọn Data/Sort. Nhắp vào Options trong hộp thoại Sort và chọn lựa chọn Case Sensitive. 
Nhắp vào OK hai lần. Bạn sẽ phải nhớ để chọn lựa chọn này mỗi lần vì Excel sẽ không giữ lại thiết 
đặt này. 
Chuyển đổi các nhăn tới các số 
Text dạng số là một kiểu dữ liệu hợp lệ. Ví dụ một số phone không hoàn toàn là một số. Khi lưu trữ 
kiểu dữ liệu này, bạn có thể thêm vào dữ liệu nhập vào bằng ký tự ('). Tuy nhiên, thỉnh thoảng các giá 
trị số được nhập vào được nhập như là text số và điều này có thể xảy ra rắc rối. May thay, có thể dễ 
dàng chuyển đổi text số tới dạng số. Bạn theo các bước sau: chọn các ô dữ liệu bạn muốn chuyển đổi. 
Chọn Data\ Text To Column. Nhắp vào Next hai lần. Chọn General format và nhắp vào Finish. Excel 
sẽ chuyển đổi text số thành dữ liệu số. 
Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn 
Nguyễn Thanh Long - Excel Nâng cao Trang 36 
 Xóa mật khẩu Protect Sheet của Excel 
Như đã biết Excel có 1 chức năng khóa 1 sheet bất kỳ bằng passwords( menu Tool/ Option/ 
Protection/ Protect sheet) 
Khi 1 sheet đã bị khóa, bạn ko thể modify( chèn thêm nội dung, chèn cột, địnnh dạng ô...). Thế 
nhưng bạn quên passwords thì sao? Đây là giải pháp: 
1. Nhấn chuột phải vào tên sheet cần gỡ bỏ passwords, chọn View Code.....sẽ hiện ra 1 của sổ 
nhập liệu. 
2. Copy đoan mã bên dưới và paste vào ô nhập liệu trên. 
Sub PasswordBreaker() 
 Dim i As Integer, j As Integer, k As Integer 
 Dim l As Integer, m As Integer, n As Integer 
 Dim i1 As Integer, i2 As Integer, i3 As Integer 
 Dim i4 As Integer, i5 As Integer, i6 As Integer 
 On Error Resume Next 
 For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 
 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 
 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 
 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 
 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ 
 Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ 
 Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) 
 If ActiveSheet.ProtectContents = False Then 
 ActiveWorkbook.Sheets(1).Select 
 Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _ 
 Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ 
 Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) 
 Exit Sub 
 End If 
 Next: Next: Next: Next: Next: Next 
 Next: Next: Next: Next: Next: Next 
 End Sub 
3. Vào menu Run> Run sub/User form và chờ giây lát. 
4. Vào menu File> Close and return. 
Bây giờ bạn có thể modify thoải mái, passwords đã bị gỡ bỏ. 
Tạo siêu liên kết các Sheet tự động 
Khi làm việc với một tập tin Excel có nhiều Sheet, các bạn sẽ gặp nhiều khó khăn khi di chuyển giữa các 
bảng tính này. 
Giải pháp: 
Chúng ta sẽ tạo ra một Sheet mới với tên là MụcLục. Sau đó thêm một đoạn mã vào Sheet này để tạo mục 
lục tự động. 
Các bước thực hiện: 
1. Trở về Sheet đầu tiên. Vào Insert > Worksheet. Đặt tên của Sheet này lại thành MụcLục hay để nguyên 
tùy thích. 
2. Right-click lên thẻ của Sheet này chọn View Code. 
3. Copy tất cả đoạn mã này vào: 
Cty CP ĐT & PT Viễn Thông Miền Tây – www.hi-phone.com.vn 
Nguyễn Thanh Long - Excel Nâng cao Trang 37 
Private Sub Worksheet_Activate() 
Dim wSheet As Worksheet 
Dim M As Long 
M = 1 
 With Me 
 .Columns(1).ClearContents 
 .Cells(1, 1) = "INDEX" 
 .Cells(1, 1).Name = "Index" 
 End With 
 For Each wSheet In Worksheets 
 If wSheet.Name Me.Name Then 
 M = M + 1 
 With wSheet 
 .Range("H1").Name = "Start" & wSheet.Index 
 .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Ind
ex", TextToDisplay:="Back to Index" 
 End With 
 Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:=
"Start" & wSheet.Index, TextToDisplay:=wSheet.Name 
 End If 
 Next wSheet 
End Sub 
4. Nhấn Alt + Q và Save lại. 
5. Từ Excel, vào Tool > Macro > Security. Trong hộp thoại này, chọn mức Low. Vì trong file Excel của 
bạn có dùng Macro, nên đôi khi mức Macro security ở mức high, đoạn lệnh của bạn sẽ không có tác 
dụng. Nếu bạn chọn Medium, bạn sẽ được nhắc là chạy macro không. Trong trường hợp này, bạn chọn 
Enable Macro để thấy tác dụng. 
5. Nhấn Ctrl + W để đóng lại Workbook và mở lại Workbook này. 
Đến đây, khi nhấp vào Sheet có tên là MụcLục, bạn sẽ thấy danh sách các bảng tính hiện ra. Nhấp vào 
Sheet nào mà bạn muốn di chuyển nhanh đến để làm việc. 
Cách tính thâm niên làm việc của nhân viên 
Để tính chính xác số tháng và năm làm việc thì ta phải sử dụng hàm sau: 
= DATEDIF (start_date, end_date [, unit]) 
Ví dụ 1: công thức sau đây tính số ngày từ hôm nay đến lễ Giáng Sinh: 
= DATEDIF(TODAY(), DATE(YEAR(TODAY()), 12, 25), "d") 
Hàm DATEDIF() có thể đơn giản hóa đáng kể công thức tính tuổi của một người. Nếu ngày tháng năm 
sinh của một người nằm trong một ô có tên là Birthday, công thức sau đây tính tuổi chính xác của người 
đó: 
= DATEDIF(Birthdate, TODAY(), "y") 
Ví dụ 2: Tính thâm niên của một người bắt đầu làm việc từ 27/07/2006 đến hiện tại 
Cách 1: = YEAR(TODAY()) – YEAR(“27/07/2006”) = 3 (Tính bằng cách này thì nó tự làm tròn là 3 
năm nhưng thực tế còn thiếu 4 tháng mới đủ 3 năm) 
Cách 2: Chính xác số tháng và năm làm việc là 2 năm 8 tháng 
 = DATEDIF(“27/07/2006”,TODAY(),”y”) = 2 năm 
 = DATEDIF(“27/07/2006”,TODAY(),”ym”) = 8 tháng