Bài viết: Chiêu thứ 46: Sử dụng tên để tạo hàm tự tạo

[kyo]

Chiêu thứ 46: Sử dụng tên để tạo hàm tự tạo

Mặc dù tham chiếu dữ liệu bằng tên thì rất tiện lợi, thỉnh thoảng nó cũng hữu ích với cả việc dùng tên để lưu trữ giá trị hằng số hay một công thức nào đó, đặc biệt kể cả nếu bạn dùng để tạo một hàm VBA tự tạo nào đó.

Giả sử bạn có tỷ suất thuế là 10% và bạn cần nó trong suốt quá trình tính toán của mình. Thay vì bạn cứ phải gõ 10% hoặc 0.1 vào từng công thức, bạn có thể dùng tên TaxRate và Excel sẽ hiểu nó là 0.1, bằng cách:

1. Chọn thẻ Formulas -> chọn Defined Names -> Define Name (với Excel 2003 thì là Insert -> Name -> Define).
2. Ở dòng "Names:", gõ TaxRate.
3. Ở dòng "Refers To:", gõ =0.1 rồi nhấn Add.

Một trong những ưu điểm lớn nhất khi sử dụng cách này, đó là, chẳng hạn như tỷ suất thuế có thay đổi thì bạn chỉ cần vào lại thẻ Formulas -> Defined Names -> Define Name (Excel 2003: Insert -> Name -> Define) rồi chọn tên TaxRate và sau đó chỉnh lại cho phù hợp mà không cần phải cất công rà từng công thức một để thay đổi.

Bên cạnh việc dùng giá trị hằng số như trên, bạn cũng có thể sử dụng công thức ở dòng Refers To trong bảng Name. Giả sử như bạn muốn tạo lập một công thức mà khi bạn gõ vào một ô, nó sẽ tự động lấy tổng (SUM) của 10 ô phía trên ô bạn vừa gõ. Bạn sẽ làm như sau:

1. Chọn A11 -> Click chuột phải -> chọn Name a Range (với Excel 2003, Insert -> Name -> Define).
2. Tại dòng "Name:", gõ TongCong và tại dòng "Refers To:", gõ =SUM(A1:A10). Sau đó chọn OK.
3. Gõ 10 con số bất kỳ trong 10 ô phía dưới liên tiếp mà con số bắt đầu có thể là ở bất kỳ hàng nào, cột nào. Giả sử ở đây tôi gõ từ 1 đến 10 lần lượt vào C2:C11.
4. Sau đó, tại hàng thứ 11 mà ở ví dụ trên, tôi gõ tại ô C12: =TongCong.

Kết quả mà bạn thấy, đó chính là, nó sẽ tự động cộng liên tục 10 ô phía trên ô mà bạn gõ =TongCong.

​

Nếu bạn muốn tạo một tên chứa công thức mà nó không bị giới hạn bởi 10 ô, nhưng nó sẽ lấy toàn bộ tất cả các ô phía trên ô chứa công thức =TongCong. Bạn làm như sau:

1. Chọn B11 -> Chọn thẻ Formulas -> Ở Defined Names, chọn Name Manager (với Excel 2003, chọn Insert -> Name -> Define) -> Chọn tên TongCong mà bạn đã tạo từ ví dụ trước.
2. Ở dòng "Refers To:", bạn sẽ thấy nó ghi là =SUM(Sheet1!B1:B10), bạn hãy sửa lại là =SUM(B$1:B10).

Bây giờ bạn hãy thử lại đi, hãy tạo một bảng dữ liệu bất kỳ theo một cột nào đó, và ở ô tiếp theo trong cột đó, bạn hãy dùng =TongCong, lập tức bạn sẽ thấy rằng, nó sẽ cộng toàn bộ các ô phía trên ô bạn nhập =TongCong lại bắt đầu từ ô số 1, bất kể là nó có bao nhiêu ô đi chăng nữa. Đó là bởi vì bạn đã đặt tham chiếu tuyệt đối vào hàng 1.

​

Sử dụng tên với Intersect (tạm dịch: phép giao)

Bằng việc kết hợp dùng tên với toán tử giao (một trong những đặc trưng cơ bản của Excel mà rất ít người biết đến), bạn có thể dễ dàng tạo nên được một hàm dò tìm phức tạp. Nếu bạn vẫn còn chưa rành về phương thức Intersect hoạt động ra sao, chúng ta hãy cùng thực hành một ví dụ nho nhỏ:

1. Tại A1, gõ tiêu đề là "Họ Tên", tương tự với B1 là "Lương (đơn vị: triệu đồng)", C1 là "Nghề nghiệp".
2. Bạn hãy đặt hai cái tên lần lượt ở A2 và A3, chẳng hạn như tôi đặt là "Nguyễn Văn A" và "Trần Thị B".
3. Hãy cho hai mức lương lần lượt vào B2 và B3, ở đây tôi đặt là "10" và "20".
4. Hai người này làm nghề gì? Tôi đặt lần lượt là "Giáo viên" và "Bác sĩ".
5. Sau đó, bạn hãy quét chọn A1:C3 -> vào thẻ Formulas -> ở Defined Names, chọn Create from Selection (với Excel 2003, chọn Insert -> Name -> Create). Hãy bảo đảm rằng lựa chọn "Top row" và "Left column" đã được đánh dấu check -> Nhấn OK.

​

Kể từ đây, khi bạn gõ công thức theo cú pháp =Tên Tiêu_đề (lưu ý là nếu tên có khoảng trắng hoặc các dấu như "(", ":" thì bạn phải thay là dấu gạch dưới _), bạn sẽ nhận được thông tin như bạn mong muốn.

​

Cũng xin lưu ý khác là, khoảng trắng giữa Tên và Tiêu đề là rất quan trọng, vì nó thể hiện cho Excel biết rằng, đây là toán tử giao.

Dựa trên những lý thuyết trên, bạn có thể kết hợp nó với tên để làm cho công việc đơn giản đi cũng như giúp bạn dễ sử dụng, dễ đọc và dễ nhớ hơn. Bạn hãy thử làm như sau:

Đầu tiên, bạn hãy tạo một bảng dữ liệu như hình dưới đây và bạn hãy dùng cách Create from Selection như bước 5 ở phía trên:

​

Sau đó, bạn hãy làm các thao tác như sau:

1. Bấm chuột phải vào một ô bất kỳ, chọn Name a Range (với Excel 2003, chọn Insert -> Name -> Define).
2. Gõ một cái tên vào "Name:", ở đây tôi gõ là DuyAnhJob
3. Tại "Refers To:", gõ =Thái_Duy_Anh Nghề_nghiệp rồi chọn OK.

Từ đây, bạn có thể chỉ cần dùng =DuyAnhJob là bạn sẽ có ngay thông tin nghề nghiệp của Thái Duy Anh. Bạn có thể linh hoạt biến hóa hơn để phục vụ nhu cầu của bạn, chắc chắn, nó sẽ giúp cho công việc của bạn được dễ dàng hơn.

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu thứ 45: Sử dụng cùng một cái tên cho dãy ở những worksheet khác nhau
2/ Chiêu thứ 44: Thay địa chỉ dữ liệu bằng tên
3/ Chiêu 43: Thêm các danh sách có sẵn và cả danh sách tự tạo vào menu chuột phải
4/ Chiêu 42: Giảm danh sách xổ xuống của Validation, sau khi chọn 1
5/ Chiêu 41: Bẫy lỗi để trống dữ liệu
6/ Chiêu 40: Cho phép sử dụng tính năng Group and Outline trên bảng tính bị khoá
7/ Chiêu thứ 39: Ngày tháng trong Excel
8/ Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động
9/ Chiêu 37: Chuyển đổi các hàm và công thức trong Excel thành giá trị
10/ Chiêu thứ 36: Làm nổi các Subtotal của Excel