Tên và nhãn trong công thức

[handung107]

Phần 1 : Sử dụng tên - Đặt tên

Những qui định về việc đặt tên cho các ô, các công thức, các hằng :
1. Các ký tự được phép : Ký tự đầu tiên của 1 tên phải là mẫu tự, hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là các mẫu tự, các số, các dấu chấm, và các ký tự gạch dưới. Td : _01_01_04 hay HH_01
2. Các tên không thể là một tham chiếu ô . Td : R1C1 hay A$1
3. Có thể dùng nhiều từ, nhưng không có khoảng trống. Các ký tự gạch dưới hay dấu chấm có thể được dùng để nối từ. Td : Product_01 hay Product.01
4. Số ký tự : Một tên có thể lên đến 255 ký tự
5. Loại kiểu chữ dùng trong tên : Excel không phân biệt ký tự in hoa và ký tự thường trong các tên

Sử dụng các nhãn và tên :

Các sheet thường có các nhãn ở đỉnh của mỗi cột và phía bên trái của mỗi hàng nhằm mô tả dữ liệu ở bên trong của nó. Bạn có thể sử dụng các nhãn này trong các công thức lúc bạn muốn tham chiếu đến các dữ liệu liên quan. Bạn cũng có thể đặt các tên mô tả để dễ gợi nhớ cho các Cell, dãy các Cell, các công thức hay các hằng.
Lưu ý : Excel không nhận ra các nhãn trong công thức nên bạn hãy vào Tools/Options/Calculation. Bên trong Workbook options bạn hãy chọn hộp kiểm Accept labels in formulas

Đặt tên các Cell trong một bảng tính :
Chọn ô, dãy các ô mà bạn muốn đặt tên.
- Đặt tên một ô hoặc một dãy các ô.
Nhấp hộp Name ở bên trái ngoài cùng của thanh công thức rồi gõ nhập tên muốn đặt. Nhấn Enter
- Đặt tên các ô bằng cách sử dụng các nhãn hàng và cột hiện có : Nếu dãy ô của bạn có sẵn tiêu đề cột hay hàng ở ngay bên trên, dưới, trái, phải bạn có thể vào Menu Insert/Name/Create và chọn dấu kiểm Top row, Bottom row, Left Column, Right Column.
Tên được tạo ra bằng thủ tục này chỉ tham chiếu đến các ô có chứa giá trị, không chứa các nhãn hàng và cột đang có
- Đặt tên thông qua Insert/Name/Define hay dùng tổ hợp phím tắt Ctrl+F3, hôp thoại Name Define hiện ra, Trong Names in workbook bạn gõ tên muốn đặt, trong Refers to : bạn gõ vùng tham chiếu. Nếu đặt tên cho một hằng hay một công thức thì hộp thoại Refers to bạn sẽ gõ hằng hay công thức vào đây.
-Đặt nhãn thông qua hộp thoại Insert/Name/Label. Thường được sử dụng để đặt nhãn cho một cột có tiêu đề, chọn từng cột luôn cả tiêu đề, add vào hộp thoại Add Label Range/Add/Ok. Nhãn của vùng sẽ lấy theo tiêu đề cột.
Tên và nhãn sẽ được sử dụng rất nhiều và hiệu quả mà chúng ta sẽ tuần tự đề cập đến trong những phần sau - Sức mạnh của tên

 

[handung107]

Phần 2 : So sánh Label và Name

- Nhãn (Label) được tạo ra khi bạn sử dụng hộp thoại Insert/Name/Label.
- Tên (Name) : đã nói ở phần trên.
So sánh :
- Khi bạn đã đặt tên cho 1 ô, dãy ô thì trong hộp thoại Name Define sẽ hiển thị. Nhưng nhãn không hiển thị tại đây.
- Để nhãn có thể áp dụng được trong công thức, bạn phải đặt dấu kiểm ở Tool/Option/Calculation/Accept Labels in Formula.
Bây giờ tôi giả sử có 1 mảng gồm 4 cột và 3 hàng (A1 : D3). Từ A1 đến D1 là tiêu đề cột gồm (B1 : Product 1; C1 : Product 2; D1 : Product 3). Từ A2 đến A3 là tiêu đề hàng gồm (A2 : East; A3 : West). Các ô còn lại bạn lần lượt nhập số lượng cho từng sản phẩm. Tôi giả sử các số hạng trong dãy B2 : D3 lần lượt từ 10,20,30,40,50,60.
Bạn hãy chọn mảng B1 : D3 rồi vào Insert/Name/Create, chọn Top Row và mảng A2 : D3 chọn Left Column. Vậy là bạn đã đặt các tên cho từng dãy B2 : B3 (ProDuct_1), C2 : C3(Product_2), D23(Product_3), B2 : D2(East), B3 : D3(West). Nếu bạn mở hộp Name, bạn sẽ thấy đủ 5 tên này.
Nhưng nếu đặt nhãn, bạn phải chọn từng cột hay từng hàng một. Td B1 : B3 (nhãn sẽ là Product 1 cho dãy B2 : B3), C1 : C3, D1 : D3. Và tất cả các nhãn này không có mặt trong hộp tên.
Nếu bạn thu bảng tính lại còn 39% hay nhỏ hơn, Excel sẽ bổ sung một đường biên xung quanh nhãn mà bạn đã chỉ định với lệnh Label Ranges cùng tên nhãn của từng dãy. Đường biên này không được in ra và nó cũng không hiển thị khi phóng bảng tính lớn hơn 39%
Khi bạn sử dụng công thức SUM(Product_1) để tính tổng là bạn sử dụng tên của dãy B2 : B3. Nhưng nếu bạn nhập SUM(Product 1) để tính tổng là bạn sử dụng nhãn, cả 2 đều cho ra kết quả như nhau.
Nếu bạn muốn xem số lượng Product 3 ứng với vùng East nghĩa là dữ liệu ở ô
D2, bạn chỉ cần nhập công thức sau : = Product_3 East (S/d tên) hay =Product 3 East (s/d nhãn) bạn sẽ được kết quả giống như nhập các hàm tham chiếu dò tìm sau :
=INDEX(B2 : D3,1,3)
=INDIRECT(ADDRESS(2,4))
Hẹn các bạn lần sau, chúng ta sẽ nói thêm về Sức mạnh của tên và nhãn

 

[handung107]

Phần 3 : Đặt tên cho vùng dữ liệu động (Dynamic Ranges)

Từ bài này trở đi, tôi không đề cập đến nhãn nữa, vì tên thường được sử dụng nhiều hơn. Khi xử lý dữ liệu liên quan đến nhiều vùng, ô, ta thường viết theo quy tắc Góc trái trên : Góc phải dưới.
Có 2 loại vùng : Vùng cố định và vùng di động.

- Đặt tên vùng cố định :

Đánh vào hộp Refer to công thức xác định vùng
Td : 'Sheet1'!$A$1:$H$300, rồi Add. (Góc trái trên và góc phải dưới của vùng cố định không nhất thiết phải là địa chỉ tuyệt đối, tuỳ thuộc bạn áp dụng công thức đối với tên vùng)
Nếu đặt tên vùng cố định bằng địa chỉ tương đối, bạn phải làm chủ được sự sai lệch địa chỉ khi copy công thức có tên vùng từ ô này sang ô khác.
Thông thường, ta hay sử dụng vùng di động, nghĩa là khi ta bổ sung thêm dữ liệu vào vùng thì tên vùng sẽ tự cập nhật theo.

- Đặt tên vùng di động :

Trong hộp Refer To ta nhập công thức sau :
=OFFSET('Tên Sheet'!Góc trái trên,0,0,COUNTA($Cột:$Cột), Độ rộng vùng)
Trong đó :
*Tên Sheet chứa vùng
*Địa chỉ cố định góc trái trên của vùng
*Độ lệch ban đầu của góc trái trên theo hàng
*Độ lệch ban đầu của góc trái trên theo cột
*Chiều cao của vùng được tính bằng cách đếm cột căn cứ
*Độ rộng của vùng bằng cách đánh vào một số cụ thể
Nhập công thức xong, bạn cũng Add để đóng hộp thoại.
Bây giờ, tôi giả sử vùng dữ liệu của tôi gồm : Cột A (Họ và Tên), Cột B (Địa chỉ), Cột C (Mã số thuế) trên Sheet tên DMKH và tôi bắt đầu nhập liệu từ hàng thứ 2 trở đi
Đặt tên :
HOTEN : =OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),1)
DIACHI : =OFFSET('DMKH'!$B$2,0,0,COUNTA($A:$A),1)
MST : =OFFSET('DMKH'!$C$2,0,0,COUNTA($A:$A),1)
Và tên CSDL là :
DL==OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A),3) (CSDL có 3 cột)

Nếu tôi muốn vùng DL này được mở rộng cả về phía bên phải của cột C thì :

DL : =OFFSET('DMKH'!$A$2,0,0,COUNTA('DMKH'!$A:$A),COUNT A('DMKH'!$1:$1)).
Chúng ta đã đặt tên xong cho vùng cố định và di động, tên này được sử dụng thoải mái cho bất kỳ Sheet nào trong Workbook. Excel còn có một chức năng rất hay mà chúng ta lại sử dụng nó rất mờ nhạt : đó là đặt tên cho các công thức. Khi bạn đã làm chủ được việc đặt tên này, bạn sẽ thấy độ dài của một công thức không còn phải đánh dài vô tội vạ nữa, và thuật toán sẽ trở nên thật gọn gàng và dễ hiểu. Bạn sẽ thấy Excel cho bạn những kết quả hơn cả bạn mong đợi nữa. Hẹn các bạn lần sau nhé.

 

[handung107]

Phần 4 : Dynamic Ranges (tt)

Tôi giả sử cột dữ liệu của tôi là cột A và nhập liệu từ hàng 1 gồm những dạng Format khác nhau vừa Text, vừa Number. Để đặt tên cho cột a, tôi dùng hộp thoại Insert/Name/Define như trên.

Trong hộp Refers to nhập :

1/ Vùng dữ liệu mở rông về phía dưới đến hàng có số dưới cùng :

=OFFSET($A$1,0,0,MATCH(1E+300,$A:$A,1),1) (1E+300 : số 1 và 300 số 0)

(Nếu bạn nhập 1 số nhỏ, td :20, vùng mở rộng sẽ kéo xuống hàng dưới cùng có số 20)

2/Vùng dữ liệu mở rộng về phía dưới đến hàng có dạng Text ở dưới cùng :

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

3/Vùng dữ liệu mở rông về phía dưới dựa trên giá trị của 1 Cell Td : Ở Cell B1 ta nhập số 40

=OFFSET($A$1,0,0,$B$1,1) Vùng dữ liệu sẽ là : A1:A40

4/ Vùng dữ liệu động nằm trong một vùng dữ liệu khác : Giả sử tại cột A tôi có một danh sách được sắp xếp theo thứ tự tăng dần (A:Z). Bây giờ, chúng ta sẽ đặt tên cho một vùng động gồm những tên bắt đầu bằng chữ "H", nghĩa là vùng bắt dầu từ tên đầu tiên và kết thúc bằng tên cuối cùng có chữ "H". Để thực hiện, chúng ta đặt vào hộp Refers to công thức :

=OFFSET(INDIRECT(ADDRESS(MATCH("H*",Sheet1!$A$2:$A $1000,0)+1,1)),0,0,COUNTIF(Sheet1!$A$2:$A$1000,"H* ",1)
Chúng ta bắt đầu bằng Cell A2 để có tiêu đề cột

Để thấy rõ vấn đề này, chúng ta hãy nhập một số tên trong cột A và sắp xếp tăng dần, và chắc chắn rằng trong số những tên này có một số tên bắt đầu bằng chữ "H". Ta add công thức trên vào hộp Refer to xong, gọi tên dãy bằng cách nhấn F5 và trong hộp thoại Go To / Reference nhập tên dãy. Click OK, sau đó, nhập thêm một số tên và sắp xếp lại, gọi lại hộp thoại Go To / Reference để xem kết quả.

 

[handung107]

Phần 5 : Tên của dãy chính là tên của một công thức

Bạn có nhận thấy điều này không ? Khi bạn tạo ra một cái tên, cũng là bạn tạo ra một công thức, công thức này không ở trên Cell đó, nó tồn tại trong bộ nhớ của Excel.
Khi bạn làm việc với hộp thoại Define Name Dialog Box, tại Refers to sẽ chứa công thức và tại Name in Workbook chứa tên của công thức. Khi bắt đầu nhập vào hộp Refers to bạn luôn luôn phải đặt dấu = trước.
Td : Tại Name in Workbook đặt tên là TS và Refers To : =Sheet1!$B$3 (nghĩa là Cell B3 có tên là TS)
hay Name in workbook là TG và Refes to : =Sheet1!$B$3:$B$12 (nghĩa là dãy này có tên là TG
Còn trong Name in Workbook bạn đặt : TC
Và Refers to : = A4 + A5 + A6 (nghĩa là tên của công thức trên là TC).
Nếu bạn chấp nhận được điều này, chúng ta sẽ dễ dàng tạo ra những công thức phức tạp hơn và đặt tên cho chúng, công thức của chúng ta sẽ trở nên gọn nhẹ, và khi sửa chữa, chúng ta cũng dễ dàng thao tác hơn.
Chúng ta sẽ bàn về Sức mạnh của tên :
1/ Tên của Cell, Dãy các Cell, công thức sẽ được sử dụng tại mọi sheet trong Workbook. Việc này giúp bạn thuận lợi trong việc đặt các DS (Validation), viết Code trong VBA, làm công thức trong sáng, dễ hiểu, dễ thực hiện.
Td : tính điểm trung bình của môn học. CSDL gồm cột A là cột STT, cột B là cột Họ và Tên, 3 cột (C5:Ex ) điểm kiểm tra miệng, 3 cột (F5:Hx) kiểm tra 15 phút (hệ số 1), 4 cột (I5:Lx) kiểm tra 1 tiết (hệ số 2), 1 cột (M5:Mx) kiểm tra học kỳ (hệ số 3). Cột N là cột tính điểm TB môn.
N5=SUM(C5:M5)+SUM(I5:M5)+SUM(M5)/(COUNTA(C5:M5)+COUNTA(I5:M5)+COUNTA(M5))
Công thức trên thì quá khó hiểu, và chúng ta sẽ đặt tên cho chúng như sau :
Đặt con trỏ tại hàng thứ 5, và đặt :
*TONG = SUM($C5:$M5)+SUM($I5:$M5)+SUM($M5)
* DEM = COUNTA($C5:$M5)+COUNTA($I5:$M5)+COUNTA($M5)
* DTB = TONG/DEM
Bây giờ, ở bất kỳ hàng nào ta chỉ việc đánh =DTB sẽ ra điểm trun gbình của mỗi học sinh.
2/ Tên của công thức cũng giúp bạn tránh được việc nhập Ctrl+Shift+Enter khi nhập công thức mảng.
Td : Công thức mảng sau đây sẽ trả về TRUE nếu từ A1:A11 sắp xếp theo thứ tự tăng và trả về FALSE nếu ngược lại
{=AND(A2:A11>A1:A10)}
Nếu ta đặt tên cho công thức là Thutu =AND(A2:A11>A1:A10) thì sau khi đặt tên cho công thức xong, bạn sử dụng như bình thường, nghĩa là chỉ cần nhập =Thutu, không cần nhập Ctrl+Shift+Enter nữa.
3/Bạn cũng biết trong công thức IF(dk,True,False), bạn chỉ được quyền sử dụng tối đa 7 vòng lặp.
= IF(dk1,gt1,IF(dk2,gt2,IF(dk3,gt3,IF(dk4,gt4,IF(dk5 ,gt5,IF(dk6,gt6,IF(dk7,gt7,"")))))))
Có nhiều cách khác nhau để sử dụng được nhiều hơn 7 vòng lặp đó, chúng ta sẽ bàn vào lúc khác. Nhưng một trong những cách đó là sử dụng tên cho công thức.
Td : Ta muốn đặt công thức sau :
Nếu B1=1 kết quả là "Một", B1=2 là "Hai", B1=3 là "Ba", B1=4 là "Bốn"...đến B1=12 là "Mười hai". Ta đặt tên như sau :
Đặt con trỏ ở hàng 1, vào hôp thoại ta gõ công thức :
MotDenSau = IF(B1=1,"Một",IF(B1=2,"Hai",IF(B1=3,"Ba",IF(B1=4 ,"Bốn",IF(B1=5,"Năm",IF(B1=6,"Sáu","Không thấy))))))
Sau đó, đặt tên tiếp cho công thức thứ hai là MotDenMuoiHai
MotDenMuoiHai = IF(B1=7,"Bảy",IF(B1=8,"Tám",IF(B1=9,"Chín",IF( B1=10,"Mười",IF(B1=11,"Mười Một",IF(B1=12,"Mười Hai",MotDenSau))))))
Cuối cùng, công thức của bạn sẽ thật gọn đến không ngờ :
=IF(B1="","",MotDenMuoiHai)
4/Ở phần đầu của loạt bài này, tôi có đề cập đến mảng gồm 4 cột và 3 hàng (A1 : D3). Từ A1 đến D1 là tiêu đề cột gồm (B1 : Product 1; C1 : Product 2; D1 : Product 3). Từ A2 đến A3 là tiêu đề hàng gồm (A2 : East; A3 : West). Các ô còn lại bạn lần lượt nhập số lượng cho từng sản phẩm.
Nếu bạn muốn xem số lượng Product 3 ứng với vùng East nghĩa là dữ liệu ở ô
D2, bạn chỉ cần nhập công thức sau : = Product_3 East.
Đó cũng là một trong những ưu điểm của việc đặt các tên trong công thức và chúng ta cần khai thác hết những sức mạnh này. Hẹn các bạn dịp sau

 

[handung107]

Còn một chức năng khác của menu Insert/Name mà không phải ai cũng để ý là Insert/Name/Apply
Chức năng này nhằm apply các name ( đối với name của vùng cố định thôi nhé) đã được định nghĩa cho các vùng Refers To tương ứng,
VD:
- Trên Sheet2 bạn đã có một công thức như sau: = Vlookup(sheet1!$B$2:$E$9;$D2;Columns($B$2:$C$9);0)
- Sau đó bạn mới định nghĩa vùng Sheet1!$B$2:$E$9 trong hộp menu Insert/Name/Define là VungChon chẳng hạn.
- Bây giờ bạn muốn apply tên VungChon cho vùng Sheet1!$B$2:$e$9;$B2 trong các công thức bạn đã tạo mà không muốn sửa trực tiếp trên công thức đó. Bạn vào menu Insert/Name/Apply trong danh sách hộp name bạn chọn tên VungChon và ấn Apply.
- Quay lại sheet2 của bạn và kiểm tra công thức của bạn lúc này sẽ là
= Vlookup(VungChon;$D2;Columns($B$2:$C$9);0)
- Ngoài ra trong Insert/Name Còn 1 chức năng nữa là Paste thì đơn giản là paste tên đã được định nghĩa vào công thức thôi

 

[handung107]

Giới thiệu Add-in Name Lister

--------------------------------------------------------------------------------

Name Lister, tên đầy đủ là J-Walk Name Lister, có trong PUP 6. Với Add-in này, bạn có thể liệt kê tất cả các tên trong Workbook. Ngoài ra, bạn có thể chọn theo từng Option để được DS liệt kê theo từng loại như sau :
1/ Những tên ở cấp độ Workbook
2/ Những tên ở cấp độ Sheet (dành cho những tên đặt riêng cho những Sheet đặc hiệu)
3/ Những tên ẩn (hidden) : Những tên này thưỡng không thể hiện ở hộp Name của Insert / Name /Defeine
4/Những tên Link (Linked Names) tham chiếu đến những Workbook khác
5/ Những tên không hợp lệ : (Bad names), những tên này cần được xoá bỏ
Add -In này sẽ giúp bạn :
- Xoá các tên không hợp lệ, cũng như những tên nào bạn muốn xoá
-Khi bạn chọn tên trong hộp tên và Click nút Go To, bạn sẽ đến ngay vùng dãy có tên đó, mà không cần đóng hộp thoại Name Lister lại
Bạn nào đã có PUP (Power Utility Pak) thì sẽ có add-in này, nếu chưa có, bạn có thể Down riêng lẻ add-in này để sử dụng cho chức năng này tuỳ ý

 

[handung107]

Code của Add-in Name Lister do bạn LVD cung cấp

Name Lister
Phần I
Đây là đọan mã trong module của Name Lister
Ngòai ra còn có UserForm1 (bài sau)
Option Explicit
Public Const PUPNAME As String = "Power Utility Pak"
Public Const APPNAME As String = "Name Lister"
'Các bạn chú ý khai báo kiểu biến này rất hay và tiện trong lập trình
Type NameType
TheName As String
TheIndex As Long
RefersTo As String
SheetLevel As Boolean
DefinedIn As String
Hidden As Boolean
Linked As Boolean
Bad As Boolean
BySheet As String
End Type

Public AllNames() As NameType
Public NameCount As Long

Sub NameLister()
Dim Msg As String, Ans As Long
CheckAddIn
If ActiveSheet Is Nothing Then Exit Sub
If ActiveWorkbook.Names.Count = 0 Then
MsgBox "The active workbook contains no names.", vbInformation, APPNAME
Exit Sub
End If
If ActiveWorkbook.ProtectStructure Then
Msg = "The active workbook's structure is protected." & vbCrLf & "You can view names, but you will not be able to delete any names." & vbCrLf & vbCrLf & "Continue?"
Ans = MsgBox(Msg, vbInformation + vbYesNo, APPNAME)
If Ans = vbNo Then
Exit Sub
Else
UserForm1.DeleteButton.Enabled = False
UserForm1.DeleteAll.Enabled = False
End If
End If
UserForm1.Show
End Sub

Sub GetAllNames()
' Add names to the AllNames array
Dim n As Name
Dim s As String
Dim wks As Worksheet
Dim pn As String
ReDim Preserve AllNames(1 To ActiveWorkbook.Names.Count)
NameCount = 0

For Each n In ActiveWorkbook.Names
NameCount = NameCount + 1
AllNames(NameCount).TheName = n.Name
AllNames(NameCount).TheIndex = n.Index
AllNames(NameCount).RefersTo = n.RefersTo
If TypeName(n.Parent) = "Workbook" Then
AllNames(NameCount).SheetLevel = False
Else
AllNames(NameCount).SheetLevel = True
End If
AllNames(NameCount).DefinedIn = n.Parent.Name
AllNames(NameCount).Hidden = Not n.Visible
AllNames(NameCount).Linked = (n.RefersTo Like "*[[]*") Or (n.RefersTo Like "*\*")
AllNames(NameCount).Bad = n.RefersTo Like "*REF!*"
On Error Resume Next
If AllNames(NameCount).Bad Then
AllNames(NameCount).BySheet = ""
Else
pn = n.RefersToRange.Parent.Name
If Err = 0 Then AllNames(NameCount).BySheet = pn Else AllNames(NameCount).BySheet = "(none)"
End If
On Error GoTo 0
Next n
On Error GoTo 0
End Sub
Private Sub CheckAddIn()
On Error GoTo CloseMe
If Workbooks("pup6.xla").Sheets(1).Range("A1") = "Utility" Then Exit Sub
CloseMe:
ThisWorkbook.Close
End Sub
Private Function StripSheetName(s)
' Removes the sheet name from a reference
StripSheetName = Right(s, Len(s) - InStr(s, "!"))
End Function


Chúc các bạn thành công.
Lê Văn Duyệt

 

[handung107]

Name Lister
Phần II
Đây là đọan mã của UserForm1

Option Explicit
Dim WarningWasShown As Boolean
Dim Output As Worksheet
Dim EventsEnabled As Boolean

Private Sub UserForm_Activate()
Dim i As Long
DoEvents
GetAllNames
EventsEnabled = True
obAll_Click
LabelRefersTo.Font.Bold = False
End Sub

Private Sub UserForm_Initialize()
Dim sht As Object
EventsEnabled = False
Me.Caption = APPNAME
Label1.Caption = "Names in " & ActiveWorkbook.Name
For Each sht In ActiveWorkbook.Sheets
ComboSheetList.AddItem sht.Name
Next sht
With ComboSheetList
.AddItem "(no sheet)"
.Value = ActiveSheet.Name
.Enabled = False
End With
With LabelRefersTo
.Caption = "Retrieving names..."
.Font.Bold = True
End With
DoEvents
End Sub

Private Sub ListBox1_Click()
Dim i As Long
If Not EventsEnabled Then Exit Sub
On Error Resume Next
For i = 1 To NameCount
If AllNames(i).TheName = ListBox1.Value Then
With LabelRefersTo
.Caption = AllNames(i).RefersTo
.ControlTipText = .Caption
End With
ListBox1.ControlTipText = ListBox1.Value
If AllNames(i).Hidden Then UnhideButton.Enabled = True Else UnhideButton.Enabled = False
If AllNames(i).Bad Then GotoButton.Enabled = False Else GotoButton.Enabled = True
Exit For
End If
Next i
If Err <> 0 Then
With LabelRefersTo
.Caption = ""
.ControlTipText = .Caption
End With
End If
If ListBox1.Value = "(none)" Then
GotoButton.Enabled = False
DeleteButton.Enabled = False
DeleteAll.Enabled = False
Else
GotoButton.Enabled = True
If Not ActiveWorkbook.ProtectStructure Then
DeleteButton.Enabled = True
DeleteAll.Enabled = True
End If
End If
On Error GoTo 0
End Sub

Private Sub obAll_Click()
' Show all names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub

Private Sub obWorkbook_Click()
' Show workbook level names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
If Not AllNames(i).SheetLevel Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub

Private Sub obSheetLevel_Click()
' Show sheet level names
Dim i As Long
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
ListBox1.Clear
DoEvents
For i = 1 To NameCount
If AllNames(i).SheetLevel Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub

Private Sub ComboSheetList_Change()
Dim i As Long
Dim ShtName As String
If Not EventsEnabled Then Exit Sub
ListBox1.Clear
DoEvents
ShtName = ComboSheetList.Value
ComboSheetList.ControlTipText = ComboSheetList.Value
If ShtName = "(no sheet)" Then ShtName = "(none)"
For i = 1 To NameCount
If AllNames(i).BySheet = ShtName Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub

Private Sub obBySheet_Click()
Call ComboSheetList_Change
With ComboSheetList
.Enabled = True
.SelStart = 0
.SelLength = 100
.SetFocus
End With
End Sub

Private Sub obHidden_Click()
' Show hidden names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = True
For i = 1 To NameCount
If AllNames(i).Hidden Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then
ListBox1.AddItem "(none)"
UnhideButton.Enabled = False
End If
ListBox1.ListIndex = 0
End Sub

Private Sub obLinked_Click()
' Show linked names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
If AllNames(i).Linked Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub


Chúc các bạn thành công
Lê Văn Duyệt

 

[handung107]

Name Lister
Phần III
Private Sub obBad_Click()
' Show erroneous names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
If AllNames(i).Bad Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub

Private Sub DeleteButton_Click()
Dim ListPlace As Long
Dim DisplayedSheet As Long
Dim Ans As Integer
Dim NameIndex As Long
' Deletes the selected name

' Show the warning only one time
If Not WarningWasShown Then
WarningWasShown = True
Ans = MsgBox("Caution!" & vbCrLf & vbCrLf & "Deleting a name that is used in a formula will cause the formula to return an error.", vbCritical + vbOKCancel, APPNAME)
If Ans = vbCancel Then Exit Sub
End If

ListPlace = ListBox1.ListIndex 'remember list item
DisplayedSheet = ActiveSheet.Index 'remember active sheet number

' add a new blank worksheet at the beginning, so that there is no
' possibility that there is a similar name on the active sheet
On Error Resume Next
Application.ScreenUpdating = False
Worksheets.Add before:=Sheets(1)
NameIndex = ActiveWorkbook.Names(ListBox1.Value).Index
With ActiveWorkbook.Names(NameIndex)
.Visible = True ' this handles cases of "bad" hidden names that can't be deleted
.Delete ' delete the name
End With
Application.DisplayAlerts = False
Worksheets(1).Delete 'delete the new sheet
Application.DisplayAlerts = True
Sheets(DisplayedSheet).Select 're-activate the active sheet
Application.ScreenUpdating = True

If Err = 0 Then
If NameCount = 1 Then ' last name was deleted
With ListBox1
.Clear
.AddItem "(none)"
.ListIndex = 0
End With
MsgBox "The active workbook contains no more names.", vbInformation, APPNAME
Unload Me: Exit Sub
Else
' adjust the array
Call GetAllNames

If obAll Then Call obAll_Click
If obWorkbook Then Call obWorkbook_Click
If obSheetLevel Then Call obSheetLevel_Click
If obHidden Then Call obHidden_Click
If obLinked Then Call obLinked_Click
If obBad Then Call obBad_Click
If obBySheet Then Call obBySheet_Click
End If
Else
MsgBox "Cannot delete that name." & vbCrLf & vbCrLf & "It may be an invalid name. If the name is visible you may be able to delete it using Excel's Define Name dialog box.", vbInformation, APPNAME
End If
If NameCount = 0 Then ReportButton.Enabled = False

If ListPlace = ListBox1.ListCount Then ListPlace = ListPlace - 1
ListBox1.ListIndex = ListPlace
ListBox1.SetFocus
On Error GoTo 0
End Sub

Private Sub DeleteButton_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' This handles fast button clicks, which are otherwise ignored
DeleteButton_Click
End Sub

Private Sub DeleteAll_Click()
Dim Ans As String
Dim Msg As String
Dim NameCount As Long
Dim NameIndex As Long
Dim i As Long, DisplayedSheet As Long

NameCount = ListBox1.ListCount
DisplayedSheet = ActiveSheet.Index 'remember active sheet number

Msg = ""
If Not WarningWasShown Then
WarningWasShown = True
Msg = Msg & "Deleting a name that is used in a formula will cause your formula to return an error." & vbCrLf
If NameCount = 1 Then
Msg = Msg & "Deleting this name cannot be undone." & vbCrLf & vbCrLf
Else
Msg = Msg & "Deleting these names cannot be undone." & vbCrLf & vbCrLf
End If
Else
If NameCount = 1 Then
Msg = Msg & "OK to delete this name?"
Else
Msg = Msg & "OK to delete these " & NameCount & " names?"
End If
End If

Ans = MsgBox(Msg, vbInformation + vbYesNo, APPNAME)
If Ans <> vbYes Then Exit Sub

' add a new blank worksheet at the beginning, so that there is no
' possibility that there is a similar name on the active sheet
On Error Resume Next
Application.ScreenUpdating = False
Worksheets.Add before:=Sheets(1)
For i = 0 To ListBox1.ListCount - 1
NameIndex = ActiveWorkbook.Names(ListBox1.List(i)).Index
With ActiveWorkbook.Names(NameIndex)
.Visible = True
.Delete ' delete the name
End With
Next i

Application.DisplayAlerts = False
Worksheets(1).Delete 'delete the new sheet
Application.DisplayAlerts = True

Sheets(DisplayedSheet).Select 're-activate the active sheet
Application.ScreenUpdating = True

If ActiveWorkbook.Names.Count = 0 Then
ListBox1.Clear
MsgBox "The active workbook contains no more names.", vbInformation, APPNAME
Unload Me
Exit Sub
Else
' adjust the array
Call GetAllNames
If obAll Then Call obAll_Click
If obWorkbook Then Call obWorkbook_Click
If obSheetLevel Then Call obSheetLevel_Click
If obHidden Then Call obHidden_Click
If obLinked Then Call obLinked_Click
If obBad Then Call obBad_Click
If obBySheet Then Call obBySheet_Click
End If
If NameCount = 0 Then
ReportButton.Enabled = False
Else
MsgBox "One or more names could not be deleted." & vbCrLf & vbCrLf & "Try using Excel's Define Name dialog box to delete the name(s).", vbInformation, APPNAME
End If
ListBox1.SetFocus
On Error GoTo 0
End Sub


Private Sub GotoButton_Click()
' Activates a name, if possible
Dim i As Long
Dim GoToRef As String

On Error Resume Next
For i = 1 To NameCount
If AllNames(i).TheName = ListBox1.Value Then
GoToRef = AllNames(i).RefersTo
GoToRef = Right(GoToRef, Len(GoToRef) - 1)
Exit For
End If
Next i
Application.GoTo Range(GoToRef)
If Err <> 0 Then
MsgBox ListBox1.Value & vbCrLf & ActiveWorkbook.Names(ListBox1.Value) & vbCrLf & vbCrLf & "Cannot go there.", vbInformation, APPNAME
End If
ListBox1.SetFocus
On Error GoTo 0
End Sub

Private Sub UnhideButton_Click()
' unhides a hidden name
Dim i As Long
On Error Resume Next
For i = 1 To NameCount
If AllNames(i).TheName = ListBox1.Value Then
ActiveWorkbook.Names(ListBox1.Value).Visible = True
AllNames(i).Hidden = False
UnhideButton.Enabled = False
Exit For
End If
Next i
If obHidden Then Call obHidden_Click
On Error Resume Next
ListBox1.SetFocus
End Sub

Private Sub ReportButton_Click()
Dim UserSheets As Long
Dim PasteRange As Range
Dim NameBook As Workbook
Dim UserBook As Workbook
Dim r As Long

Set UserBook = ActiveWorkbook
CloseButton.Caption = "Cancel"
UserSheets = Application.SheetsInNewWorkbook
Application.ScreenUpdating = False
Set NameBook = Workbooks.Add
Set Output = NameBook.Sheets(1)
Application.SheetsInNewWorkbook = UserSheets
With Output
.Cells(1, 1) = "Name report for " & UserBook.FullName
.Cells(1, 1).Font.Size = 14
.Cells(1, 1).Font.Bold = True
.Cells(1, 1).Font.Italic = True
.Cells(2, 1) = "Report generated " & Now
.Cells(2, 1).Font.Bold = True
.Cells(2, 1).Font.Italic = True
End With

With Output.Range("A4:H4")
.Font.Bold = True
.Interior.ColorIndex = 36
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
End With

Set PasteRange = Output.Cells(4, 1)
With PasteRange
.Offset(0, 0) = "Name"
.Offset(0, 1) = "Refers to"
.Offset(0, 2) = "On Sheet"
.Offset(0, 3) = "Sheet level"
.Offset(0, 4) = "Defined in"
.Offset(0, 5) = "Hidden"
.Offset(0, 6) = "Linked"
.Offset(0, 7) = "Erroneous"

For r = 1 To NameCount
If r Mod 10 = 0 Then
LabelRefersTo.Caption = "Generating Report: " & r & " of " & NameCount & " names"
DoEvents
End If
.Offset(r, 0) = AllNames(r).TheName
.Offset(r, 1) = "'" & AllNames(r).RefersTo
.Offset(r, 2) = AllNames(r).BySheet
.Offset(r, 3) = AllNames(r).SheetLevel
.Offset(r, 4) = AllNames(r).DefinedIn
.Offset(r, 5) = AllNames(r).Hidden
.Offset(r, 6) = AllNames(r).Linked
.Offset(r, 7) = AllNames(r).Bad
Next r
End With
Output.Range("A4:G500").Columns.AutoFit
If Output.Columns(2).ColumnWidth > 74 Then Output.Columns(2).ColumnWidth = 74
Application.ScreenUpdating = True
Unload Me
End Sub

Private Sub CloseButton_Click()
On Error Resume Next
Output.Parent.Close False
On Error GoTo 0
End
Unload Me
End Sub

Private Sub PUPHelpButton_Click()
Application.Run "pup6.xla!ShowPUPHelp", 660
End Sub
Chúc các bạn thành công
Lê Văn Duyệt

 

[handung107]

So sánh tên toàn cục và tên cục bộ

--------------------------------------------------------------------------------

Khi bạn xác định một tên mới bằng hộp Name Box hay Insert / Name /Define, chúng tự động được xem là áp dụng cho toàn tập bảng tính.
Nếu bạn chọn tên từ hộp Name, Excel sẽ kích hoạt đúng trang bảng tính rồi chọn dãy ô mang tên này.
Nếu bạn sử dụng tên cho công thức, tên sẽ tham chiếu đúng ô, dãy đó, cho dù chúng có ở trên một trang bảng tính riêng biệt
Vì những lý do trên, tên này được gọi là tên toàn cục (global name), sử dụng cho toàn bộ các Sheet cho Workbook
Tuy nhiên, có những trường hợp, bạn muốn dùng tên cụ thể trên nhiều Sheet.
TD : Bạn muốn tham chiếu đến hàng Tổng Cộng ở tất cả các Sheet với cùng một cái tên giống nhau :TC, sao cho bạn có thể sao chép công thức ngang qua các Sheet.
Trường hợp này, bạn phải sử dụng tên cục bộ (local name). Để định rõ tên này, bạn chọn Sheet sẽ áp dụng tên này.
Td : Sheet1. Vào Insert / Name / Define, bạn gõ vào hộp tên TC.
Hộp Refers to : Sheet1!$A$150 (A150 là ô chứa giá trị Tổng cộng chẳng hạn).
Bạn cũng làm như vậy cho các Sheet khác, nếu bạn muốn đặt tên là TC TD : Sheet2!$A$150, Sheet3!$A$200...
Bây giờ, bạn trở lại hộp Name, bạn sẽ nhận thấy tên cục bộ dành cho Sheet đã chọn xuất hiện (Sheet làm việc), tên Sheet hiển thị ở bên phải DS hộp tên. Các tên cục bộ dành cho những trang không được chọn sẽ không xuất hiện
Nếu bạn dùng hộp Name ở góc bên trái thanh Formula, bạn định tên cục bộ bằng cách gõ trực tiếp tên Sheet trước tên chọn. Td : Sheet1!TC

 

[handung107]

Tên tham chiếu đến một Workbook khác

--------------------------------------------------------------------------------

Nếu bạn muốn xác định một tên để tham chiếu đến một ô, dãy ô trong một Workbook khác, bạn có thể tạo một liên kết được gọi là tham chiếu ngoại (external reference). Khi thực hiện điều này, bạn phải chắc chắn rằng Workbook mà bạn muốn tham chiếu đến phải được mở và đã được lưu.
1/Tại Workbook làm việc mà bạn muốn đặt tên, bạn vào Insert / Name / Define.
2/Gõ vào tên bạn muốn đặt ở hộp Name in Workbook
3/Ở Refers To : Bạn gõ TD : =[Data.xls!]DanhMuc!$B$2:$F200
Trong đó : Data.xls là tên Workbook có chứa vùng dữ liệu mà bạn muốn đặt tên
DanhMuc là tên của Sheet có dữ liệu đó
$B$2:$F$200 : Vùng dữ liệu bạn đặt tên
Lưu ý : Bạn phải đứng tại WorkbooK làm việc để đặt tên. Data.xls là Workbook có chứa dữ liệu để đặt tên

 

[handung107]

Global Range và Local Name

--------------------------------------------------------------------------------

Tên cục bộ (Local Name), được định nghĩa cho một vùng của Sheet đó. Nếu ta muốn có cùng một tên (Td : TC) cho tất cả các Sheet trong cùng một Workbook tại Cell A100 chẳng hạn, ta có thể thực hiện như sau :
-Vào Insert / Name / Define, tại hộp Name in Workbook ta gõ : TC
- Hộp Refers To : =OFFSET(!$A$100,,,,)
(Áp dụng cho vùng sẽ là : Td :=OFFSET(!$A$1:$A$100,,,,))

Như vậy, tên TC xem như tên cục bộ, vì khi bạn đứng tại Sheet nào, bạn gõ =TC tại Cell bất kỳ, Cell này sẽ có giá trị của Cell A100 của Sheet đó
Ngược lại, Cell A100 với tên TC lại được gọi là Global Range, vì có cùng một địa chỉ tại tất cả các Sheet trong cùng một Workbook

Đặt tên như vậy, dù là tên cục bộ, nhưng nó không thể hiện trong hộp Insert/Name/Define như những tên cục bộ đặt theo cách bình thường (Gõ Sheet!Ten) , ta nhận biết khi gõ vào hộp Name Box ở phía bên trái thanh Formula tên của vùng đó, vùng sẽ được chọn

 

[handung107]

Thứ tự tìm kiếm các tên trong Excel

--------------------------------------------------------------------------------

Giả sử bạn có một Workbook và có 2 Sheet, Sheet 1 có vùng mang tên cục bộ là Sheet1!TriGia, Sheet 2 có vùng mang tên tổng quát là TriGia. Khi Sheet 1 hoạt động, bạn muốn thiết lập công thức : =SUM(Book1!TriGia) để tính tổng các giá trị trong vùng mang tên TriGia ở Sheet 2.
Nhưng Excel sẽ ngay lập tức biến công thức này thành =SUM(Sheet1!TriGia), đó chính là thứ tự tìm kiếm các tên miền giống nhau của Excel.
Tại Sheet 1, Excel gặp tên TriGia trong công thức SUM, nó trả về tên cục bộ đến Sheet 1.
Bây giờ, tại Sheet 2, bạn nhập =SUM(Sheet2!TriGia) thay vì =SUM(Book1!TriGia), do Sheet 2 không có tên cục bộ, nên Excel đi tìm tiếp tên tổng quát. Và vì chỉ có một tên tổng quát là TriGia, nên Excel sẽ thay đổi Sheet2!TriGia thành Book1!TriGia.
Ngược lại, tại Sheet 3 trong Book 1, không chứa tên cục bộ, hoặc tên tổng quát TriGia, nên trên Sheet 3, bạn có thể nhập : =SUM(Book1!TriGia), và Excel sẽ không sửa đổi những gì bạn nhập.
Trong cả 2 trường hợp, Excel sẽ trả về tổng của tên tổng quát TriGia trên Sheet 2

 

[handung107]

Workman Post :

Vận Dụng Đặt Tên Trong Excel trong File Loan Amortisation của Microsoft.

--------------------------------------------------------------------------------

Thấy cách sử dụng tên của họ rất sáng tạo và đáng học tập nên tôi tranh thủ bình luận và post lên đây để các bạn tham khảo.

Chắc chắn đây không phải toàn bộ kiến thức về Name, nhưng hi vọng phần nào giúp các bạn thấy được cách vận dụng Name một cách sáng tạo vào công việc hàng ngày của mình.

Đầu tiên bạn mở attached file ra nhé.

Do sheet này bị protect, bạn vào Tools-Protection-Unprotectsheet để theo dõi các bước tiếp sau.

Một số lưu ý:

1. Bất cứ lúc nào bạn cũng có thể sử dụng tổ hợp Ctrl+F3 để thấy tên và cách sử dụng Name.
2. Lưu ý là Name không chỉ được sử dụng thuần túy chỉ một địa chỉ cụ thể, mà còn sử dụng như một biến hoặc hằng số
3. Trong quá trình phân tích tác giả có sử dụng những công thức / hàm tài chính (chủ yếu liên quan đến đầu tư và dòng tiền tệ)

Tôi đi luôn vào chi tiết phân tích nhé.

1. Tên dưới dạng một hằng số: Cái này ai cũng biết, tôi không dám nói nhiều.

Nhóm tên này có mục đích làm các tham số trong các công thức phía dưới. Thay vì ta dùng các địa chỉ như $D$6, $D$7..., người ta sử dụng những tên này cho dễ theo dõi và mang tính gợi nhớ hơn. Ví dụ: Interest = Principle * Interest_Rate

Nhóm này bao gồm:
- Loan_Amount : Là khoản tiền vay ban đầu.
- Interest_Rate: Là lãi suất tính theo năm.
- Loan_Years: Số NĂM bạn vay tiền
- Number_of_Payments_Per_Year: Số lần thanh toán một năm. Ví dụ bạn có thể qui định một tháng thanh toán 1 lần, tức là bạn thanh toán 12 lần một năm (theo hình thức trừ lương chẳng hạn).
- Loan_Start: ngày khoản vay có hiệu lực. Nó chỉ có ý nghĩa tính toán ra "Payment Date" sẽ được trình bày ở dưới.
- Optional_Extra_Payments: Cho phép ta chọn phương pháp trả nợ nhanh. Tức là ta có thể trả nhiều hơn số qui định.

Tất cả những Name nêu trên đều có ý nghĩa nền tảng cho các bước tiếp theo. Những ô đề cập đều có thuộc tính UNLOCKED, tức là cho phép người sử dụng được nhập liệu và sửa chữa thoải mái.

- Scheduled_Monthly_Payment(H6): Như sẽ đề cập ở phần 2, Name này chỉ có giá trị khi trigger được thiết lập. Hàm để tính tiền phải trả hàng tháng như sau: PMT(Lãi suất,Số lần thanh toán nợ,Số tiền vay). Bạn nào gỏi về về công thức tài chính (công thức toán học thứ thiệt, không phải hàm excel) thì chia sẻ cho anh em với nhé.

- Total_Interest (H10): Hàm sumif đơn giản, ai cũng biết rồi.

2. Tên để làm một trigger (Khai hỏa):

Tôi cũng không biết dịch chữ trigger là gì nữa. Nói túm lại nó là một biến để kích hoạt mọi hoạt động của một chương trình.

Trong ví dụ trên, trigger chính là Name "Values_Entered".

Nếu bạn chịu khó dạo vòng quanh worksheet một lần, bạn sẽ thấy phần lớn các ô đều bắt đầu bằng hàm =If(Values_Entered,...). Nôm na có nghĩa là "nếu đã nhập dữ liệu rồi thì làm cái này, nếu chưa nhập dữ liệu thì để trống.

Bạn nhấn Ctrl+F3, bạn sẽ thấy Values_Entered được định nghĩa là

=IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Star t>0,1,0)

Theo như công thức trên, tác giả cho rằng người dùng phải nhập đủ bốn giá trị: Loan_Amount,Interest_Rate,Loan_Years và Loan_Start. Nếu thiếu một trong những số trên thì worksheet sẽ không hoạt động.

3. Tên dưới dạng một vùng: Loại này y chang như phần 1, đây loại tên thể hiện một vùng cố định làm mục đích cho các công thức tính toán. Nó chỉ khác là thay vì chỉ đến một hằng số (một ô cụ thể) nó tham chiếu đến một vùng (range).

Loại Name này bao gồm:

- Pay_Num (A18:A377): chỉ là cột số thứ tự của các khoản thanh toán nợ vay. Trong vùng này, các công thức được thể hiện dưới dạng "sảnh" hoặc "chơi bài tiến lên" (tức là số chạy liên tục). Số này "chơi tiến lên" cho đến ô 377.

Có bạn sẽ thắc mắc tại sao số chạy liên tục mà khi ta nhìn vào worksheet lại chỉ thấy những số thể hiện theo ô H8-Actual Number of Payments. Phần lớn các bạn đã biết đáp án. Những bạn nào chưa biết, xin đọc kỹ phần sau, tôi sẽ nói kỹ hơn về thủ thuật này.

- Pay_Date (B18:B377): Trong vùng này, tác giả vận dụng công thức khá lạ. Thông thường khi ta nói đến hàm Date() (ở đây là hàm Date của excel nha, các bác nghiền VBA đừng lẫn lộn qua hàm Date của VBA tội nghiệp tôi), người ta thường có khuynh hướng phải tính toàn tỉ mỉ số năm và tháng tương ứng. Trong ví dụ này, tác giả tận dụng tối đa khả năng của hàm Date để đơn giản hóa vấn đề bằng cách:
+ Số năm: chính là năm BẮT ĐẦU khoản vay
+ Ngày: là ngày bắt đầu khoản vay
+ Tháng: Đây chính là tuyệt chiêu của tác giả. Hàm Date() thực ra chấp nhận sô tháng lớn hơn 12. Nếu ta input số tháng lớn hơn 12 thì hàm Date() sẽ cộng thêm 1 năm nữa vào giá trị của mình. Đây là điều mà có thể nhiều người biết nhưng rất ít ai áp dụng.

Tôi lấy ví dụ: nếu tôi gõ vào hàm Date(2005,25,12) thì hàm này sẽ cho ra ngày 12 tháng 1 năm 2007.

- Beg_Bal (C18:C377): Vùng này cực kỳ dễ hiểu, bình loạn nhiều các bạn cười chết.

Download:clickhere

 

[handung107]

Adam_tran Post :

Cách dùng trigger rất hay, thay vì phải dùng 1 vùng đệm để kiểm tra đủ dữ liệu cần thiết, tác giả lại dùng Name để thay thế. Tuy ứng dụng của trong bài ít, nhưng nó mở ra một cách vận dụng Name rất lạ để đơn giản bớt công thức và trình bày.

Có 1 thủ thuật của VBA có thể giúp bạn dấu đi những name trong Name list (nhìn rối mắt) là thuộc tính visible của name.
Sub ToggleNameVisible()
Dim nName As Name
For Each nName In ActiveWorkbook.Names
nName.Visible = Not nName.Visible
Next nName
End Sub
Vận dụng cách đặt tên Name và thuộc tính Visible của Name, bạn có thể che bớt các Name để người dùng đỡ nhìn rối mắt hoặc tránh co người dùng thay đổi các Name này.
Bạn vận dụng thuộc tính này cùng Add-In Name Manager của MaiKa (Forest) chắc chắn sẽ rất hữu ích.

 

[handung107]

Maika đã chỉnh sửa, kết hợp giữa Add-in Name Lister của PUP, Add-in của bạn Lê văn Duyệt viết để quản lý hộp Name, Add-in các hàm đọc số thành chữ đủ 3 loại Font : VNi, Unicode, TCVN...Nhình chung, đây là một Add-in nhiều tiện ích hơn cả đang có trên Thư viện của Webketoan.
Và hôm nay, khi chúng ta mở Excel ra sử dụng, chúng ta sẽ mãi mãi nhớ Maika qua món quà bạn đã gởi tặng cho chúng ta, các bạn nhé

File Name Manager (Đọc số thành chữ)

của MaiKa.