Vận Dụng Đặt Tên Trong Excel

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi workman, 15 Tháng mười 2005.

17,064 lượt xem

  1. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Hôm trước tôi có gửi một file đính kèm về template tính Loan Amortisation của Microsoft. Khi đọc kỹ lại 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_Start>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.

    .....

    Mệt quá, để mai làm phần tiếp theo. Phần tiếp theo tôi sẽ nói về cách áp dụng các hàm tài chính và các "mánh" khác được sử dụng trong Worksheet này.
     

    Các file đính kèm:

    #1
  2. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    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.
     
    #2
  3. workman

    workman Thành viên hoạt động

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Tính ra cái ... mai của tôi nó kéo dài ra phết, tôi gọi đó là phép tương đối của Albert Einstein. Mà xem ra cũng rất có ít người theo dõi bài này, nên tôi cũng không áy náy lắm (cái này để tự bào chữa thôi).

    Thôi thời gian là vàng bạc, tôi đi tiếp luôn nhé.

    Vẫn tiếp tục phần 3: Tên dưới dạng một vùng

    - Sched_Pay ($D$18:$D$377): đây là phần bạn phải trả hàng tháng theo lịch. Khoản này sẽ cố định hàng tháng, nên công thức từ trên xuống dưới là như nhau, chính bằng với khoản tiền được tính ở ô Scheduled Payment (ô H6, phần 1). Các bạn lưu ý là cái Sched_Pay chỉ có ý nghĩa tham khảo, tức là không phải là số tiền mà khách hàng phải trả cho ngân hàng đâu. Ở dòng cuối cùng, bạn sẽ thấy Sched_Pay lắm khi còn to hơn cả số dư đầu kỳ nữa

    - Extra_Pay: cái này cần nói nhiều đây. Đây là một vùng với công thức phức tạp. Tôi đi dần từng phần nhé.

    Công thức của vùng này như sau: =IF(AND(Pay_Num<>"",Sched_Pay+Scheduled_Extra_Payments<Beg_Bal),Scheduled_Extra_Payments,IF(AND(Pay_Num<>"",Beg_Bal-Sched_Pay>0),Beg_Bal-Sched_Pay,IF(Pay_Num<>"",0,""))).

    Tạm diễn nôm như thế này: Nếu Số thứ tự <> "" (cái này có mánh liên quan đến LastRow, tôi sẽ nói kỹ hơn ở phần "mánh") VÀ Số tiền ScheduledPayment + Extra Payment nhỏ hơn Số Dư Nợ đầu kỳ, thì cho phép trả Extra Payment, nếu không thì

    Nếu IF(AND(Pay_Num<>"",Beg_Bal-Sched_Pay>0),Beg_Bal-Sched_Pay (lại một cái nếu nữa) Số Dư Nợ đầu kỳ - ScheduledPayment > 0 (tức là chưa trả hết nợ) thì cho phép trả phần còn nợ còn thiếu, nếu không thì

    Nếu IF(Pay_Num<>"",0,"") (cái hàm IF cuối cùng, còn có duy nhất 1 trường hợp là số Dư Nợ Đầu kỳ = ScheduledPayment) - cái này dễ, tôi không dám nói nhiều

    Total Payment: Đơn giản, tôi xin lướt. Chỉ lưu ý các bạn ở dòng cuối cùng, Total Payment sẽ có giá trị bằng với Beginning Balance (số Dư Nợ đầu kỳ).
    Đến đây thì tôi phát hiện ra cái bảng tính của Microsoft bị sai, nhưng thôi kệ. Tôi đang nói về Excel, không phải nói về tài chính, nên cũng không sao. Nếu bạn muốn có một sheet hoàn chỉnh thì chỉ cần điểu chỉnh công thức lại một chút như sau: =IF(AND(Pay_Num<>"",Sched_Pay+Extra_Pay<Beg_Bal),Sched_Pay+Extra_Pay,IF(Pay_Num<>"",Beg_Bal+Int,""))

    Vùng Int: Đơn giản, đi trực tiếp từ công thức tính lãi

    Vùng Princ: cũng đơn giản, tương tự như Vùng Int

    Vùng End_Bal: cũng đơn giản: tính bằng Số Dư Đầu kỳ - Số Princ (số vốn) đã trả trong kỳ. Nếu trả hết rồi thì bằng 0.

    4. Last_Row

    Đây là phần tôi thích nhất khi xem worksheet này.

    Ngoài phần trigger, tác giả đưa ra khá nhiều mánh khóe khác khi sử dụng Name.

    Đầu tiên: Header_Row: được định nghĩa là dòng 17 (cái này là số chết). Header_Row có giá trị 17

    Number_of_Payments: Một cái tên động, hoặc tên sống (khác với các tên tĩnh mà chúng ta thường hay sử dụng). Tên này có giá trị biến đổi, tùy thuộc vào số lượng Payment mà chúng ta tính toán. Công thức như sau =MATCH(0.01,End_Bal,-1)+1.

    Tiếp tục diễn nôm tí nhé: tìm trong cột End_Bal, lấy ra dòng nhỏ nhất lớn hơn 0, rồi cộng thêm 1 vào (tức là đến dòng có số 0 đầu tiên)

    Last_Row. Đây là một tên động, hoặc tên sống ). Last_Row được tính như sau: =IF(Values_Entered,Header_Row+Number_of_Payments,Header_Row). Đơn giản, tôi làm mất thời giờ các bạn.

    Bây giờ mới phân tích tác dụng của Last_Row

    - Last_Row có tác dụng làm trigger cho PrintArea (sẽ nói ở phần sau)
    - Last_Row có tác dụng làm trigger Condition Format

    Nếu bạn vào một ô bất kỳ ở Name Pay_Num, chọn Format/Condition Format bạn sẽ thấy họ làm như sau: Nếu dòng của ô hiện hành nhỏ hơn Last_Row thì Format để chúng ta nhìn thấy, bằng không thì set format của font chữ thành trắng (chữ trắng, nền trắng) nên chúng ta không thấy gì cả.

    5. Print_Area

    Đây là một Excel "Built-In" name (tên được sử dụng bởi Excel). Trong excel ta có các tên được mặc định như sau:

    Consolidate_Area
    Auto_Open
    Auto_Close
    Extract
    Database
    Criteria
    Print_Area
    Print_Titles
    Recorder
    Data_Form
    Auto_Activate
    Auto_Deactivate
    Sheet_Title

    (cái này tôi sưu tầm thôi, không biết có còn work không)

    Ở bài này tác giả sử dụng Print_Tittles và Print_Area cho việc định nghĩa vùng được in.

    Tác giả cũng sử dụng Last_Row để định nghĩa vùng cần được in.

    Print_Area được định nghĩa là =OFFSET(Full_Print,0,0,Last_Row)

    Print_Tittles thì đơn giản hơn nhiều, ='Amortization Table'!$15:$17

    Thế là hoàn thành nhiệm vụ nhé. Hi vọng các bạn tìm được một vài điều hay ho từ worksheet mẫu của Microsoft.

    Chúc các bạn vui
     
    #3

Chia sẻ trang này