1.8. Name

Range: có thể chỉ là 1 cell, có thể là 1 dãy các cell liền nhau theo hàng hoặc theo cột, có thể một vùng chọn hình chữ nhật(hình vuông). Ngoài ra Range lại có thể bao gồm các cell, các dãy cell không liên tiếp hoặc Range 3D nằm rải rác ở các sheet khác nhau. Trong bài này tôi dùng từ “vùng chọn” để thay thế cho range.

Tọa độ của 1 vùng chọn (range): là địa chỉ từ ô đầu tiên đến ô cuối cùng của 1 vùng chọn. Ví dụ: A1:C10, F1:F10…

1. Giới thiệu về Name

-Mặc dù các vùng chọn vẫn cho phép bạn làm việc một cách có hiệu quả với các nhóm ô lớn, nhưng có một số khuyết điểm khi sử dụng các tọa độ của một vùng chọn:

Bạn không thể làm việc với nhiều hơn một tập hợp các dãy ô trong một lần. Mỗi lần bạn muốn sử dụng một vùng chọn, bạn phải định nghĩa lại tọa độ của nó.

Để biết một công thức chẳng hạn như =SUM(E6:E10) đang cộng những giá trị gì, bạn phải xem chính vùng chọn đó mới biết.

Một sai sót nhỏ trong việc định nghĩa các tọa độ của vùng chọn có thể dẫn đến kết quả tệ hại, nhất là khi bạn xóa một vùng chọn.

-Bạn có thể khắc phục những vấn đề trên bằng cách sử dụng tên cho vùng chọn (range name), là các tên được gán cho một ô hoặc một dãy ô. Với một tên đã được định nghĩa, bạn có thể sử dụng nó thay thế cho các tọa độ của vùng chọn.

Ví dụ: để đưa vùng chọn vào công thức, bạn sử dụng tên của vùng chọn đó thay vì chọn vùng chọn hoặc nhập tọa độ của nó. Bạn có thể tạo bao nhiều tên cho vùng chọn tùy thích và thậm chí bạn có thể gán nhiều tên cho cùng một vùng chọn.

-Tên của vùng chọn làm cho các công thức của bạn trở nên trong sáng, dễ nhìn, dễ đọc và dễ hiểu hơn.

Ví dụ: việc gán tên DSo_8(Doanh số tháng 8) vào một vùng chọn, chẳng hạn như vùng chọn E6:E10, sẽ làm rõ ngay mục đích của một công thức, chằng hạn như =SUM(DSo_8) nghĩa là tổng doanh số của tháng 8. Các tên của vùng chọn cũng làm tăng tính chính xác của các phép tính với vùng chọn bởi vì bạn không cần phải xác định tọa độ của vùng chọn nữa.

-Ngoài ra, sử dụng tên cho vùng chọn còn có nhiều ưu điểm sau đây:

Các tên dễ nhớ hơn các tọa độ vùng chọn.

Các tên không thay đổi khi bạn di chuyển một vùng chọn sang một chỗ khác trong bảng tính.

Các vùng chọn được đặt tên tự động điều chỉnh bất cứ khi nào bạn chèn hoặc xóa các hàng hoặc các cột trong vùng chọn.

Các tên làm cho bạn dễ định hướng một bảng tính hơn. Bạn có thể sử dụng lệnh GoTo để nhảy nhanh đến một vùng chọn đã được đặt tên.

Top

2. Nguyên tắc đặt tên cho vùng chọn

Các tên của vùng chọn có thể hoàn toàn linh hoạt, nhưng bạn cần tuân theo một vài giới hạn và quy tắc sau đây:

Tên chỉ có thể có tối đa 255 ký tự.

Tên phải bắt đầu với một chữ cái hoặc ký tự gạch dưới (_). Đối với phần còn lại của tên, bạn có thể sử dụng bất kỳ ký tự nào (chữ, số, symbol) ngoại trừ khoảng trắng. Đối với các tên có nhiều từ, bạn nên tách các từ bằng dấu gạch dưới (ví dụ: Số_Tài_Khoản) hoặc bằng cách dùng chữ hoa chữ thường (ví dụ: SốTàiKhoản), vì Excel không phân biệt chữ thường và chữ hoa khi bạn sử dụng tên vùng chọn trong bảng tính.

Không sử dụng các địa chỉ ô (chẳng hạn như Q1) hay bất kỳ ký hiệu toán tử nào (+, -, *, /, <, >, và &) bởi vì những ký hiệu này có thể gây rắc rối cho bạn, nhất là khi sử dụng tên của vùng chọn trong công thức.

Để việc nhập tên của vùng chọn dễ dàng hơn và nhanh hơn, bạn nên cố gắng đặt tên cho vùng chọn càng ngắn càng tốt nhưng vẫn giữ được ý nghĩa của chúng. Ví dụ: TSoLN_07 dễ nhập hơn Tổng_Số_Lợi_Nhuận_Năm_2007, và dễ hiểu hơn TSLN07.

Không sử dụng các tên có sẵn của Excel. Những tên đó là: Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Area, Print_Titles, Recorder, và Sheet_Title.

Bạn nên ghi nhớ những quy tắc trên, trước khi học tiếp cách đặt tên cho một vùng chọn.

Top

3. Sử dụng Name Box (hộp Name) để đặt tên

Name Box nằm ở phía ngoài cùng bên trái của thanh công thức, trong trường hợp bạn không thấy nó, bạn chọn menu View và đánh dấu chọn Formula Bar. Thường thì Name Box hiển thị địa chỉ của ô hiện hành, tuy nhiên nó cũng cung cấp thêm một vài tính năng nhằm giúp bạn làm việc với các tên của vùng chọn một cách dễ dàng hơn:

Sau khi bạn đã đặt tên cho một vùng chọn, tên này sẽ xuất hiện trong Name Box bất cứ khi nào bạn chọn vùng chọn đó (xem minh họa ở hình dưới)

Name Box là một drop-down list (nghĩa là một cái danh sách xổ xuống). Để chọn nhanh một vùng chọn đã được đặt tên, bạn mở danh sách xổ xuống này và chọn tên của vùng chọn mà bạn muốn, Excel sẽ di chuyển ngay đến vùng chọn đó và chọn nó.

Phiên bản nào của Excel cũng có Name Box, nhưng Excel 2007 có một tính năng mới rất tiện lợi, là bạn có thể thay đổi kích cỡ của Name Box. Nếu bạn không thể thấy hết tên của vùng chọn hiện hành (do nó dài quá), bạn di chuyển cursor (con trỏ chuột) sang mép phải của Name Box (ngay chỗ có một cái núm tròn lõm xuống), cursor sẽ biến thành một mũi tên hai đầu nằm ngang, bạn chỉ việc nhấp và rê chuột qua lại để định lại kích cỡ cho Name Box.

Khi bạn chọn một vùng chọn có tên đã được định nghĩa tên sẽ xuất hiện trong Name Box(cụ thể ở đây, Advertising là tên của vùng chọn C4:F4)

Name Box cũng ngẫu nhiên là cách dễ nhất để đặt tên cho một vùng chọn. Bạn làm theo các bước sau:

§ B1: Chọn vùng chọn mà bạn muốn đặt tên.

§ B2: Nhấp chuột vào bên trong Name Box để hiển thị một điểm chèn.

§ B3: Nhập tên mà bạn muốn đặt cho vùng chọn và nhấn Enter. Excel sẽ tự động định nghĩa một tên mới.

Top

4. Sử dụng hộp thoại Define Name (Excel 2007: New Name) để đặt tên

Sử dụng hộp thoại Name Box để đặt tên cho vùng chọn thì nhanh và dễ thấy kết quả. Tuy nhiên, nó có hai khuyết điểm nhỏ sau đây:

Nếu bạn cố đặt một tên trùng với tên đã có, Excel sẽ lanh chanh chọn ngay vùng chọn mang cái đó (tên đã có). Điều này có nghĩa là bạn phải làm lại từ đầu, chọn lại vùng chọn và nhập vào một tên, dĩ nhiên là tên khác.

Nếu bạn chọn vùng chọn đã được đặt tên, mà vô ý chọn không chính xác Excel sẽ không cho bạn bất kỳ cách thức trực tiếp nào để sửa lại tọa độ của vùng chọn hoặc xóa nó để làm lại.

Để giải quyết hai vấn đề trên, bạn cần sử dụng hộp thoại Define Name(New Name). Hộp thoại này có những ưu điểm sau đây:

Nó hiển thị một danh sách đầy đủ các tên đã có (đã được định nghĩa), do dó khả năng nhập trùng tên sẽ giảm đi.

Dễ dàng sửa lại tọa độ của vùng chọn nếu như bạn vô ý chọn không đúng.

Bạn có thể xóa một tên vùng chọn đã có.

Các bước sau đây hướng dẫn cách đặt tên cho một vùng chọn bằng cách sử dụng hộp thoại Define Name:

B1: Chọn vùng chọn mà bạn muốn đặt tên.

B2: Từ thanh Menu bar bạn chọn tab Insert/Define Name.

B3: Nhập tên mà bạn muốn đặt cho vùng chọn vào khung Name.

B4: Nếu tọa độ của vùng chọn (tự động nằm trong ô Refers to) không chính xác, bạn có thể sử dụng một trong hai cách sau để sửa nó:

· Nhập lại tọa độ đúng (nhớ bắt đầu bằng một dấu =).

· Nhấp nút ở ngoài cùng bên phải khung Refers to - để quay ra bảng tính và chọn lại vùng chọn cho chính xác. Sau khi chọn xong thì nhấp lại vào cái nút để quay về hộp thoại New Name.

Lưu ý: Nếu bạn cần di chuyển bên trong khung Refers to bằng các phím mũi tên, trước tiên hãy nhấn phím F2 để Excel biết là bạn muốn sửa trực tiếp. Nếu không, Excel sẽ nghĩ rằng bạn đang muốn dùng phím mũi tên để chọn một ô nào đó ngoài bảng tính (bạn sẽ thấy nội dung trong khung Refers to tự động hay đổi, mà thường là không phải là cái bạn muốn).

B5:Nhấn OK để quay về bảng tính.

Đối với Excel 2007:

B1: Chọn dãy mà bạn muốn đặt tên.

B2: Chọn Formulas, Define Name. Hoặc nhanh hơn: nhấp phải (right-click) vào vùng chọn và sau đó chọn Name a Range...

Khi bạn mở hộp thoại New Name để đặt tên cho một dãy, tọa độ của dãy tự động xuất thiện trong khung Refers to

B3: Nhập tên mà bạn muốn đặt cho dãy vào khung Name.

B4: Sử dụng danh sách Scope để chọn phạm vi mà tên dãy này có ảnh hưởng đến. Mặc định, nó sẽ là Workbook (toàn bảng tính)

B5: Nhập một vài mô tả hoặc ghi chú về dãy này trong khung Comment. Những mô tả hoặc ghi chú này sẽ xuất hiện khi bạn sử dụng tên dãy trong công thức. Bạn có thể xem thêm bài "Làm việc với Name AutoComplete", cũng trong topic này.

Các bước tiếp theo giống Excel 2003..

Top

5. Định nghĩa tên toàn cục (Global Name), tên cục bộ (Local Name), vùng tham chiếu ngoại (External Reference) và thứ tự tìm kiếm các tên trong excel

Tên toàn cục (Global Name): 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 nhảy đế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 trong 1 Workbook.

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 A10 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,tại Cell bất kỳ bạn gõ =TC , 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ùng chọn toàn cục), 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

Tham chiếu ngoại (External reference):

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õ (ví dụ) : =[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

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

Giả sử bạn có một Workbook(thí dụ tên của workbook này là Book1) 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 toàn cục 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.

Đối với Exel 2007

Excel 2007 cho phép bạn định nghĩa phạm vi ảnh hưởng (Scope) của một tên vùng chọn. Phạm vi này cho biết mức độ mà tên vùng chọn sẽ được nhận biết trong các công thức. Trong hộp thoại New Name nếu bạn chọn Workbook trong danh sách Scope (hoặc nếu bạn tạo trực tiếp bằng cách sử dụng hộp Name), tên vùng chọn sẽ có ảnh hưởng trong toàn bộ các Sheet. Điều này có nghĩa là một công thức trong Sheet1 có thể tham chiếu đến một tên của một vùng chọn nằm trong Sheet3. Tuy nhiên, đây có thể là một vấn đề nếu bạn sử dùng cùng một tên vùng chọn trong các Sheet khác nhau. Ví dụ, bạn có bốn Sheet: First Quarter, Second Quarter, Third Quarter, và Fourth Quarter, và trong mỗi Sheet như vậy, bạn đều có những vùng chọn cùng mang tên là Expenses.

Nếu bạn cần sử dụng cùng một tên trong nhiều Sheet khác nhau, bạn có thể chỉ định phạm vi mà tên đó có ảnh hưởng trong từng Sheet riêng biệt. Điều này có nghĩa là tên đó sẽ chỉ tham chiếu đến vùng chọn mà nó đã được định nghĩa trong riêng Sheet đó mà thôi.

Ban chỉ định phạm vi ảnh hưởng của tên vùng chọn bằng cách chọn danh sách xổ xuống của khung Scope trong hộp thoại New Name. Danh sách này, ngoài chữ Workbook là mặc định, còn có thêm tên của các Sheet trong bảng tính. Bạn chỉ việc chọn Sheet mà bạn muốn sử dụng.

Top

6. Sử dụng tiêu đề của hàng hay cột để đặt tên cho vùng chọn

Khi bạn sử dụng hộp thoại Define Name (Excel2007: New Name), bạn sẽ thấy đôi khi Excel đã gợi ý sẵn cho bạn một cái tên cho vùng chọn được chọn. Ví dụ, hình bên dưới cho thấy Excel đã gợi ý sẵn tên January cho vùng chọn C4:C8. Trong thực tế bảng tính, bạn thấy January là tiêu đề cột của vùng được chọn, và Excel tự nó tiên đoán rằng bạn muốn sử dụng cái tên này làm tên của vùng chọn.

Excel sử dụng text trong ô gần kề để đoán tên mà bạn muốn sử dụng

Thay vì để Excel tự đặt tên cho vùng chọn, bạn có thể yêu cầu nó sử dụng một tiêu đề có sẵn để làm tên vùng chọn, theo những bước sau đây:

B1: Chọn vùng chọn mà bạn muốn đặt tên, bao gồm cả ô chứa tiêu đề mà bạn muốn sử dụng làm tên cho vùng chọn (xem hình dưới)

Khi muốn dùng một tiêu đề để đặt tên cho vùng chọn, nhớ chọn cả tiêu đề này

B2: Chọn Insert/Name/Create (Excel2007: Formulas/Create from Selection)hoặc nhấn Ctrl+Shift+F3.Excel sẽ hiển thị hộp thoại Create Names (Excel2007: Create Names from Selection) như minh họa ở hình dưới.

Dùng hộp thoại Create Names (Excel 2007: Create Names from Selection) để xác định vị trí của tiêu đề muốn dùng làm tên cho vùng chọn

B3: Excel sẽ đoán ô chứa tên tiêu đề mà bạn muốn dùng làm tên cho vùng chọn nằm ở vị trí nào:

§ Top row: dòng trên cùng của vùng chọn.

§ Left Column: cột ngoài cùng bên trái của vùng chọn.

§ Bottom row: dòng cuối cùng của vùng chọn.

§ Right Column: cột ngoài cùng bên phải của vùng chọn.

Và tự động đánh sẵn dấu chọn vào mục tương ứng (trong ví dụ trên, Excel chọn sẵn Left Column). Nếu đây không phải là vị trí mà bạn muốn chọn, bạn chỉ việc hủy chọn nó và đánh dấu vào mục nào thích hợp.

B4: Nhấn OK.

Ghi chú: Nếu tiêu đề mà bạn muốn chọn làm tên cho vùng chọn chứa những ký tự không thích hợp, mà thường là khoảng trắng, Excel sẽ tự động thay thế khoảng trắng này bằng ký tự gạch dưới (_).

Khi đặt tên cho vùng chọn bằng cách này, bạn đừng lo là bị giới hạn, chỉ được chọn một cột hoặc một hàng. Bạn có thể chọn các vùng chọn chứa nhiều tiêu đề hàng lẫn tiêu đề cột, và Excel sẽ sẵn lòng gán tên vào mỗi hàng và mỗi cột, có nghĩa là chỉ với một lần chọn, bạn có thể đặt tên cho một loạt vùng chọn.

Ví dụ: trong hình dưới, sau khi bạn chọn vùng chọn B5:F10 và gọi hộp thoại Create Names , nó sẽ xuất hiện với các đánh dấu có sẵn ở Top Row và Left Column. Và khi bạn nhấn OK mà không sửa gì cả trong trường hợp này, bạn sẽ tạo được môt lúc 10 tên vùng chọn, 4 cái theo cột (January, February, March, Total) và 5 cái theo hàng (Advertising, Rent, Supplies, Salaries, Utilities).

Excel có thể tạo nhiều tên vùng chọn cùng một lúc

Ở trên, tôi nói rằng tạo được 10 tên vùng chọn, mà chỉ mới liệt kê có 9, vậy cái thứ 10 ở đâu? Vâng, khi bạn sử dụng phương pháp này để tạo tự động các tên, Excel sẽ xử lý đặc biệt ô trên cùng bên trái của vùng chọn được chọn, và nó sẽ sử dụng text (nếu có) trong ô này làm tên cho cả vùng chọn. Bạn xem lại hình trên, ô trên cùng bên trái của vùng chọn B3:F8 là ô B3, đang chứa chữ EXPENSES. Sau khi bạn nhấn OK ở ví dụ trên, vùng chọn thứ 10 chính là vùng chọn Expenses, có tọa độ là C3:F8 (xem hình kế tiếp, để ý cái gì đang ở trong Name Box).

Khi tạo nhiều tên vùng chọn cùng một lúc, Excel sẽ sử dụng nội dung trong ô góc trên bên trái của vùng chọn để làm tên cho cả vùng chọn

Top

7. Đặt tên cho các hằng số

Một trong những cách tốt nhất để làm cho các bảng tính dễ hiểu hơn, là định nghĩa tên cho các giá trị hằng số.

Ví dụ: nếu bảng tính của bạn sử dụng một biến đổi lãi suất trong một số công thức nào đó, bạn có thể định nghĩa một hằng số có tên là Rate (lãi suất), và sử dụng tên này trong công thức để dễ đọc hơn. Bạn có thể làm theo một trong hai cách sau:

Dành riêng một vùng chọn trong bảng tính làm nơi chứa các hằng số và đặt tên cho từng ô riêng lẻ. Ví dụ, hình 2.8 minh họa một bảng tính có ba hằng số được đặt tên: Rate (ô B5), Term (ô B6) và Amount (ô B7). Bạn chú ý công thức ở ô E5 tham chiếu đến mỗi hằng số theo tên như thế nào.

Tập trung các hằng số vào một chỗ riêng và đặt tên cho chúng

Mẹo: Để đặt tên nhanh cho cả ba hằng số này, bạn chọn vùng chọn A5:B7 và chọn Insert/Name/Create (Excel 2007: Formulas, Create from Selection) rồi nhấn OK. (Xem lại phần 6).

Nếu bạn không muốn làm bề bộn bảng tính của mình, bạn có thể đặt tên cho các hằng số mà không nhập tọa độ ô chứa chúng trong bảng tính. Chọn Inseret/Name/Define (Excel 2007:Formulas, Define Name) để mở hộp thoại Define Name (Excel 2007: New Name). Nhập một cái tên cho hằng số muốn tạo trong khung Names, rồi tại khung Refers to, nhập dấu bằng (=) và giá trị của hằng số đó. Hình dưới minh họa cách đặt tên cho hằng số Rate theo cách này.

Đặt tên cho một hằng số trực tiếp trong hộp thoại Define Name(New Name)

Top

8. Làm việc với các tên của vùng chọn

Sau khi đã định nghĩa tên cho một vùng chọn, bạn có thể sử dụng nó trong các công thức, dùng làm tham số trong hàm, định hướng với nó, sửa chữa, thay đổi hoặc xóa nó... Các bài tiếp theo đây sẽ hướng dẫn bạn những kỹ thuật này, và nhiều hơn nữa.

8.1.Tham chiếu đến một tên vùng chọn

Sử dụng tên một vùng chọn trong công thức hoặc dùng nó làm đối số trong hàm thì khá đơn giản: Bạn chỉ việc thay thế tọa độ của một vùng chọn bằng tên của vùng chọn đó.

Ví dụ: giả sử bạn có một ô có công thức như sau: = G1

Công thức này xác lập giá trị của ô chứa công thức sang giá trị hiện hành của ô G1. Nếu như ô G1 đã được đặt tên là TotalExpenses (tổng chi phí), thì công thức trên tương đương với:

= TotalExpenses

Tương tự, bạn xem hàm sau đây:

= SUM(E3:E10)

Nếu vùng chọn E3:E10 được đặt tên là Sales, công thức này sẽ tương đương với:

= SUM(Sales)

Nếu bạn không chắc chắn về một tên nào đó, bạn có thể yêu cầu Excel dán (paste) nó vào trong bảng tính dùm bạn.

Trường hợp: Các tên đã được đặt trước khi bạn lập công thức thì bạn dùng chức năng Paste Name

1. Bắt đầu nhập công thức hoặc hàm, cho tới khi bạn cần chèn một tên vùng chọn vào, thì dừng lại.

2. Chọn menu Insert/Name/Paste --> hộp thoại Paste Name gồm danh sách tất cả các tên có trong sheet hiện hành mà bạn đã đặt, như minh họa ở hình sau đây

Chọn Insert/Name/Paste để chèn một tên vùng chọn từ danh sách các tên đã được định nghĩa

3. Chọn tên mà bạn muốn dùng. Excel sẽ chèn tên đó vào trong công thức hoặc hàm ngay lập tức.

Trường hợp bạn lập công thức trước khi đặt tên:

Cách 1: sử dụng chức năng Apply Name

-Cũng với ví dụ trên, ở ô G5 bạn đặt công thức: =PPMT(0.5/12,D5,B6*12,B7)

-Sau khi lập công thức xong bạn mới đặt tên: Rate=0.5, Months=Amortization!$B$6, Principal==Amortization!$B$7

-Tiếp theo bạn vào Insert/Name/Apply, bạn chọn các tên Rate,Months,Principal (nhấn giữ phím Shift để việc chọn tên được liên tục) và nhấn OK.

-Giờ bạn quay lại ô G5, nhấn phím F2, bạn sẽ thấy công thức đã được đổi thành: =PPMT(Rate/12,D5,Months*12,Principal)

Cách 2: sử dụng chức năng Find and Replace

Giống như trên, thay vì dùng chức năng Apply Name thì bạn gõ Ctrl+ F (Edit/Find) để mở hộp thoại Find and Replace, chọn tab Replace.

-Ở khung Find what bạn gõ tên vùng muốn thay thế bằng tên. Cụ thể ở đây là bạn gõ 0.5 (lần lượt với các vùng: Amortization!$B$6 , Amortization!$B$7)

-Ở khung Replace with bạn gõ tên name thay thế cho vùng chọn đó. Cụ thể ở đây, tương ứng với 0.5 là Rate.(lần lượt với các tên Months, Principal)

- Nếu trong sheet hiện hành của bạn có nhiều công thức cần thay thế thì bạn nhấn nút Replace all, nếu chỉ có một công thức thì bạn nhấn Replace.

Chú ý: khi sử dụng chức năng này, tên vùng bạn gõ vào khung Find what phải chính xác. Bạn nên copy vùng chọn ngay tại công thức bằng cách nhấn phím F2 và copy vùng chọn cần được thay thế.

Đối với Excel 2007:

1. Bắt đầu nhập công thức hoặc hàm, cho tới khi bạn cần chèn một tên vùng chọn vào, thì dừng lại.

2. Chọn Formulas, Use in Formula, Excel sẽ hiển thị một danh sách các tên có phạm vi ảnh hưởng đến bảng tính hiện hành, như minh họa ở hình sau

Chọn lệnh Use in Formula để chèn một tên vùng chọn từ danh sách các tên đã được định nghĩa

3. Chọn tên mà bạn muốn dùng. Excel sẽ chèn tên đó vào trong công thức hoặc hàm ngay lập tức.

Khi làm việc với các tên vùng chọn chỉ có phạm vi ảnh hưởng trong từng Sheet, bạn sử dụng tên như thế nào phụ thuộc vào nơi bạn sử dụng nó:

Nếu bạn sử dụng tên này trong Sheet mà nó đã được định nghĩa, bạn cứ việc sử dụng bình thường như đã nói ở trên.

Nếu bạn sử dụng một tên vùng chọn có phạm vi ảnh hưởng trong từng Sheet mà lại được tạo ở một Sheet khác với Sheet hiện hành, bạn phải sử dụng tên đầy đủ của nó (SheetName!RangeName).

Nếu tên vùng chọn mà bạn muốn sử dụng nằm trong một bảng tính khác, trước hết bạn phải đặt tên của file trong một cặp dấu nháy đơn (‘), rồi đến một dấu chấm than (!), rồi mới đến tên vùng chọn đó. Ví dụ, nếu bạn muốn sử dụng tên vùng chọn Rate nằm trong bảng tính Mortgage Amortization, bạn phải nhập như sau:

‘Mortgage Amortization.xls’!Rate

8.2. Sử dụng phím tắt (Excel 2007: AutoComplete) để chèn tên vùng chọn

Bạn nhấn phím F3 để hiện thị danh sách các Name có trong sheet hiện hành.

Sau đó bạn chỉ cần chọn tên thích hợp để điền vào hàm của bạn. (Để chọn hàm bạn dùng phím tắt Shift + F3).

Đối với excel 2007:

Bạn đã biết rằng tính năng AutoComplete trong Excel 2007 tự động hiển thị một danh sách tên hàm (dựa theo những ký tự bạn gõ vào, sau dấu =), nếu thấy hàm bạn muốn, bạn có thể chọn nó từ danh sách (bằng cách dùng phím mũi tên hoặc chuột chọn và nhấn phím Tab) thay vì gõ tiếp phần còn lại của tên hàm, điều này thường nhanh hơn và chính xác hơn.

Với các tên vùng chọn cũng vậy. Khi bạn nhập vài chữ đầu tiên của một tên vùng chọn trong công thức, Excel sẽ hiển thị danh sách AutoComplete, trong đó bao gồm cả tên hàm và tên vùng chọn.

Hình bên dưới minh họa việc chèn tên vùng chọn bằng chức năng này. Ngoài ra, nếu bạn có nhập phần Comment khi tạo tên, thì những mô tả hoặc chú thích về tên vùng chọn cũng sẽ hiện ra khi bạn chọn tên vùng chọn từ danh sách AutoComplete. Và cũng như cách chọn tên hàm, bạn dùng phím mũi tên (hoặc chuột) để chọn tên vùng chọn trong danh sách và nhấn phím Tab để chèn nó vào công thức.

Chèn tên vùng chọn bằng AutoComplete

8.3. Chọn một vùng chọn bằng cách sử dụng tên của nó

Một vùng chọn, nếu đã được đặt tên thì rất dễ chọn. Excel cung cấp cho bạn hai phương pháp sau để chọn một vùng chọn bằng tên của nó:

Dùng Name Box: để chọn nhanh một vùng chọn, bạn nhấn vào Name Box và chọn tên vùng chọn trong danh sách xổ xuống.

Dùng GoTo: Bạn chọn Edit/Go To (Excel 2007: Home, Find & Select, Go To) hoặc nhấn phím F5 để mở hộp thoại GoTo, chọn tên vùng chọn trong danh sách và nhấn OK.

8.4. Chèn bảng Danh sách các tên vùng chọn đã được định nghĩa vào trong bảng tính

Nếu bạn đưa bảng tính của mình cho người khác xem, hoặc bạn phải làm việc với một bảng tính đã có từ vài tháng trước, có thể bạn (hoặc người được nhờ xem) sẽ không biết các tên vùng chọn trong bảng tính của bạn đại diện cho vùng chọn ô nào. Để thuận tiện cho việc tra cứu các tên vùng chọn, bạn nên chèn một bảng danh sách các tên vùng chọn vào bảng tính. Danh sách này sẽ gồm có hai cột, một cột là tên vùng chọn, và cột kia là tọa độ tuyệt đối của vùng chọn có tên đó. Bạn theo các bước sau đây:

1. Chọn một vùng chọn đủ lớn để chứa bảng danh sách này mà không ghi đè lên dữ liệu đang có (cách tốt nhất là bạn chọn luôn một Sheet mới).

2. Chọn Insert/Name/Paste (Excel 2007: Formulas, Use In Formula, Paste Names) hoặc nhấn phím F3, Excel sẽ hiển thị hộp thoại Paste Name.

3. Nhấn nút Paste List. Excel sẽ chèn bảng danh sách các tên vùng chọn vào ô hiện hành.

8.5. Xem các tên đã đặt bằng cách sử dụng Name Manager

Để quản lý các tên vùng chọn bạn vào Insert/Name/Define, để thêm hoặc xóa. Tuy nhiên với Excel 2003 bạn chỉ có thể xóa từng tên.(Bạn xem lại phần 4).

Để tiện lợi hơn, bạn dowload Add-in Name Manager của anh Maika theo đường link sau:

http://www.giaiphapexcel.com/forum/showthread.php?t=1108

Hoặc add-in ASP Utilities theo đường link sau:

http://www.asap-utilities.com/downlo...-utilities.php

Giới thiệu add-in Name Manager:

Sau khi download về bạn giải nén và mở excel ra, vào tools/add-ins chọn browse đi đến nơi bạn vừa down load file này về và nhấn OK.

Khi add-in này được cài đặt nó sẽ hiện lên ở khung add-in với tên Accounting Assitant Add-in/Version 1.01 và ở tools sẽ có thêm một nút mới là Name Manager.

Khi muốn sử dụng add-in này, bạn vào tools/Name Manager, sẽ hiện ra khung Thao Tác Với Names

Lúc này bạn có xem các tên có trong workbook, sheets,…Điểm đặc biệt của add-in này là bạn có thể xóa tất cả các tên chỉ với một cái click chuột hoặc kiểm tra các tên bị ẩn,…

Bạn xem hướng dẫn chi tiết tại mục 8.6 ở phần tiếp theo sau đây.

Giới thiệu add-in ASAP Utilities:

Các bạn cài đặt add-in này như add-in Name Manager ở phần trên. Sau khi cài đặt add-in này xong thì trên thanh Menu Bar sẽ xuất hiện một menu có tên ASAP Utilities

Với add-in này thì bạn có thể liệt kê các tên có trong 1 workbook

và xóa tất cả các tên trong 1 workbook hoặc xóa các tên ở một vùng chọn:

Ví dụ: bạn đang đứng ở ô C7, ô này lại thuộc vùng A1:C15 đã được đặt tên. Giờ bạn muốn xóa tên của vùng A1:C15, bạn chỉ cần vào menu ASAP Utilities chọn Range/Delete all range names in selection.

Ngoài ra add-in này còn có những ứng dụng khác. Bạn vào trang www.giaiphapexcel.com box English - Vietnamese Excel translation để đọc thêm các bài hướng dẫn sử dụng add-in này.

Đối với excel 2007:

Name Manager là một công cụ quản lý các tên vùng chọn. Trong Excel 2007, công cụ này hoàn toàn mới so với những phiên bản cũ, hay hơn và linh hoạt hơn rất nhiều. Ví dụ, với những phiên bản cũ, bạn chỉ có thể xóa mỗi lần một tên vùng chọn, nhưng với Name Manager của Excel 2007, bạn có thể xóa hàng loạt tên vùng chọn chỉ với một cú nhấn chuột. Để hiển thị Name Manager, bạn chọn Formulas, Name Manager. Hình sau đây minh họa một Name Manager.

Sử dụng Name Manager để chỉnh sửa, lọc và xóa các tên vùng chọn

Bạn cũng có thể tạo mới một tên vùng chọn ngay trong cửa sổ Name Manager này, bằng cách nhấn nút New..., hộp thoại New Name sẽ mở ra, như đã nói ở phần 4.

8.6. Lọc các tên vùng chọn

Nếu như bạn có một bảng tính mà trong đó có hàng chục, thậm chí hàng trăm tên vùng chọn đã được định nghĩa, danh sách tên vùng chọn trong Name Manager sẽ rất cồng kềnh. Để giảm kích cỡ nó, và cũng để dễ dàng tìm một tên, Add-in này cho bạn các options để lựa chọn.

Option “Tất cả các tên”: Hủy tất cả các cách lọc nếu như nó đang được chọn. Nghĩa là bạn sẽ thấy lại toàn bộ các tên vùng chọn, kể cả các tên vùng chọn nằm trong một bảng tính (workbook) khác mà có liên kết với bảng tính hiện hành và các tên vùng chọn do Excel tự tạo ra (ví dụ như tên của các Table, tên do bộ lọc Advanced Filter tạo ra...)

Option “Các tên ở mức workbook”: Chỉ xem các tên vùng chọn có phạm vị ảnh hưởng trong bảng tính hiện hành.

Option “Các tên ở mức sheet”: Chỉ xem các tên vùng chọn có phạm vị ảnh hưởng trong Sheet hiện hành

Option “Các tên ẩn”: Những tên này thường không thể hiện ở Define Name

Option “Các tên linked”: Xem các tên có link đến một workbook khác hoặc liên kết đến một file dạng HTML (thường xuất hiện khi bạn dùng import external data hoặc import XML)

Option “Các tên không đúng”: Xem các tên vùng chọn đang chứa các giá trị lỗi (chứ không phải là tên vùng chọn này có lỗi, như một vài người lầm tưởng), chẳng hạn như #NAME, #REF, #VALUE, v.v...

Option “Theo sheet”: xem các tên theo sheet.

Đối với excel 2007:

Excel 2007 cho phép bạn lọc (filter) các tên vùng chọn, với nhiều cách khác nhau, mỗi cách sẽ có một kiểu hiển thị riêng. Để lọc, bạn nhấn nút Filter trong cửa sổ Name Manager sau đó chọn một trong các bộ lọc sau đây:

Clear Filter : Hủy tất cả các cách lọc nếu như nó đang được chọn. Nghĩa là bạn sẽ thấy lại toàn bộ các tên vùng chọn, kể cả các tên vùng chọn nằm trong một bảng tính (workbook) khác mà có liên kết với bảng tính hiện hành và các tên vùng chọn do Excel tự tạo ra (ví dụ như tên của các Table, tên do bộ lọc Advanced Filter tạo ra...)

Names Scoped to Worksheet : Chỉ xem các tên vùng chọn có phạm vị ảnh hưởng trong Sheet hiện hành.

Names Scoped to Workbook : Chỉ xem các tên vùng chọn có phạm vị ảnh hưởng trong bảng tính hiện hành.

Names with Errors : Xem các tên vùng chọn đang chứa các giá trị lỗi (chứ không phải là tên vùng chọn này có lỗi, như một vài người lầm tưởng), chẳng hạn như #NAME, #REF, #VALUE, v.v...

Names without Errors : Xem các tên vùng chọn không chứa các giá trị lỗi.

Defined Names : Chỉ xem các tên vùng chọn do bạn tạo ra, mà không thấy các tên do Excel tạo ra tự động (như tên các Table chẳng hạn).

Table Names : Chỉ xem các tên của các Table. Những tên này được Excel tự động tạo ra khi bạn định nghĩa một vùng chọn ô (hay còn gọi là một vùng chọn chọn) là một Table.

8.7. Biên tập (sửa chữa, thay đổi) tọa độ của một tên vùng chọn

Nếu bạn muốn tên vùng chọn hiện có tham chiếu đến một tập hợp tọa độ khác, bạn có thể dùng một trong hai cách sau đây:

Chọn và sau đó di chuyển vùng chọn đến nơi khác. Excel sẽ tự động cập nhật tọa độ mới cho tên vùng chọn.
Nếu bạn muốn tự tay điều chỉnh tọa độ hiện có hoặc kết hợp chúng với một vùng chọn khác, trong cửa sổ Define Name (Excel 2007: Name Manager), bạn chọn tên vùng chọn mà bạn muốn thay đổi, sau đó sửa trực tiếp tọa độ của nó trong khung Refers to (nhớ nhấn F2 trước khi sử dụng các phím mũi tên để di chuyển con nháy trong khung này).

8.8. Cách làm cho Excel luôn luôn tự động cập nhật tọa độ mới của một tên vùng chọn

Khi thao tác với bảng tính, chuyện thêm vào một hàng hay một cột là chuyện thường tình. Sẽ không có vấn đề gì nếu như bạn thêm hàng hoặc cột vào giữa vùng chọn hiện có, Excel sẽ tự động điều chỉnh tọa độ của vùng chọn mới (trong tham chiếu của tên vùng chọn). Tuy nhiên, sẽ là vấn đề nếu như bạn thêm dữ liệu vào cuối vùng chọn. Trong trường hợp này, bạn sẽ phải tự điều chỉnh tham chiếu tọa độ của tên vùng chọn bằng tay để thêm các dữ liệu mới vào trong tên vùng chọn hiện có. Bạn càng thêm nhiều dữ liệu, thì sự phiền toái này càng lớn. Để tránh công việc cực nhọc rất mất thời gian này, bạn có thể dùng hai cách sau để Excel luôn luôn tự cập nhật tọa độ cho tên vùng chọn:

· Cách 1: Chọn thêm một hàng (cột) trống vào cuối vùng chọn:

Khi chọn một vùng chọn để định nghĩa tên cho nó, bạn hãy chọn thêm một vài hàng (hoặc một vài cột, hoặc cả hai) trống ở cuối vùng chọn (thường thì tôi hay thêm một hàng trống ở dưới cùng của vùng chọn).

Hình sau minh họa cho điều này: tôi sẽ đặt tên Amount cho dữ liệu nơi cột có tiêu đề là Amount (vùng chọn C4:C11), nhưng khi chọn vùng chọn để đặt tên, tôi chọn thêm ô C12, là một ô trống.

Để làm cho Excel tự động điều chỉnh tọa độ của một tên vùng chọn, đưa thêm một ô trống vào cuối vùng chọn nếu có thể được

Lợi ích của việc này là nó có thể khiến cho Excel tự động điều chỉnh tọa độ của vùng chọn. Khi bạn thêm dữ liệu vào hàng cuối (có thể thấy được) của vùng chọn, thì thật ra là bạn chèn thêm một hàng vào trước hàng cuối cùng (thực tế) của vùng chọn đã được đặt tên, và điều đó làm cho tọa độ của tên vùng chọn sẽ tự động điều chỉnh. Ví dụ, trong bảng tính ở hình trên, sau khi chèn thêm một ô (dòng) bên trên ô C12 , thì vùng chọn có tên là Amount tự động sửa tọa độ là C4:C13. Xem minh họa ở hình tiếp theo đây.

Tọa độ mới của vùng chọn Amount khi chèn thêm dữ liệu vào hàng 12

· Cách 2: Đặt tên cho toàn bộ cột hoặc hàng

Một cách nữa, dễ hơn, là bạn đặt tên cho toàn bộ hàng hoặc cột mà bạn sẽ thêm dữ liệu mới vào sau này. Với cách này, bất kỳ dữ liệu mà bạn thêm vào hàng hoặc cột sẽ mặc nhiên là thành phần của vùng chọn.

· Cách 3: đặt tên cho vùng dữ liệu động (Dynamic Ranges)

Còn một cách nữa, là dùng tạo vùng dữ liệu động, mà thường là sử dụng các hàm dò tìm và tham chiếu (Lookup and reference) như: OFFSET(), INDEX(),INDIRECT(), MATCH()… trong khung Refers to khi định nghĩa tên cho vùng chọn.Nhập công thức xong bạn nhấn Add để đóng hộp thoại.

Dùng hàm Offset:

Công thức tổng quát: =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: là tên của sheet chứa vùng chọn.
*Địa chỉ cố định góc trái trên của vùng chọn.
*Độ 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ể

(Các bạn tham khảo thêm bài viết về hàm OFFSET ở link này: http://www.giaiphapexcel.com/forum/showthread.php?t=7188 )

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,1)
DIACHI : =OFFSET('DMKH'!$B$2,0,0,COUNTA($A:$A)-1,1)
MST : =OFFSET('DMKH'!$C$2,0,0,COUNTA($A:$A)-1,1)

Và tên CSDL là :

DL: =OFFSET('DMKH'!$A$2,0,0,COUNTA($A:$A)-1,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 (Xem hình sau) thì :

DL : =OFFSET('DMKH'!$A$2,0,0,COUNTA('DMKH'!$A:$A)-1,COUNT A('DMKH'!$1:$1)).

Vùng DL sẽ mở rộng sang một cột nữa nếu cột đó có dữ liệu. Trong hình vùng DL đã được mở rộng thêm một cột là cột D.

Kết hợp các hàm dò tìm và tham chiếu (lookup and reference) với nhau:

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 :

+ Vùng dữ liệu mở rông về phía dưới đến hàng có số dưới cùng: bạn dùng một trong 2 công thức sau đây

=INDIRECT("A1:A"&MATCH(9.99999999999999E+307,A:A))

=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)

+ Vùng dữ liệu mở rộng về phía dưới đến hàng có dạng Text ở dưới cùng: bạn dùng một trong 2 công thức sau đây

=INDIRECT("A1:A"&MATCH(REPT("Z",255),A:A))

(để hiểu thêm về hàm REPT bạn tham khảo tại link sau: http://www.giaiphapexcel.com/forum/showpost.php?p=46987&postcount=13)

Hàm REPT("Z",255) dùng để lập lại 255 lần chữ Z (chữ Z là ký tự cuối cùng trong bảng ABC).. vì thế mà chắc chắn rằng giá trì cần tim này là "to" nhất, ko còn em nào "to" hơn nữa... Và khi tìm trong vùng chọn, nó sẽ tìm gần đúng: giá trị lớn nhất nhưng nhỏ hơn giá trị tìm... tức là cuối danh sách.

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

+ Nếu vùng dữ liệu của bạn bao gồm cả chữ và số thì bạn dùng công thức như sau: (xem hình sau)

=INDIRECT("A1:A"&MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A)))

Vùng A:A chứa các giá trị số và chữ, để kiểm tra công thức trong refer to, bạn dùng hàm count như hình

+ Vùng dữ liệu mở rông về phía dưới dựa trên giá trị của 1 Cell:

=OFFSET($A$1,0,0,$B$1,1)

Td : Ở Cell B1 ta nhập số 40 thì vùng dữ liệu sẽ là : A1:A40

+ 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ữ “H” trong công thức trên chỉ là ví dụ, bạn có thể thay bất cứ chữ gì tùy theo nhu cầu của bạn. Chúng ta bắt đầu bằng Cell A2 để có tiêu đề cột (A1 là 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 (bạn vào data/sort chọn option ascending), 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 và đặt tên cho dãy này là MyRange. Bạn 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 MyRange. Click OK, để xem kết quả. 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ả.

Kiểm tra công thức của Name bằng cách dùng lệnh GoTo

Giờ bạn đã hiểu thế nào là Dyanmic Range. Dynamic Range được ứng dụng khi bạn làm việc với Validation, Combo Box, Chart, Pivot Table….Bạn tìm hiểu điều này trong các bài hướng dẫn cụ thể về Validation, Chart, Pivot Table ở diễn đàn giaiphapexcel nhé.

8.9. Đổi tên một tên vùng chọn

Nếu bạn cần đổi tên của một tên vùng chọn hiện có, bạn có thể dùng một trong hai cách sau đây:

· Khi tên vùng chọn được tạo theo cách dùng tên tiêu đề của cột (hoặc hàng ở mục 5), nếu bạn đã thay đổi tiêu đề này, bạn định nghĩa lại tên vùng chọn dựa theo tên tiêu đề mới và xóa tên cũ đi.

· Trong cửa sổ Define Name, bạn chọn tên vùng chọn mà bạn cần thay đổi, xóa tên đó đi và đổi tên mới, nhấn add là bạn đã đổi được tên. (Excel 2007: Name Manager bạn chọn tên vùng chọn mà bạn muốn thay đổi, sau đó nhấn nút Edit... để mở hộp thoại Edit Name. Ban thực hiện các thay đổi trong hộp thoại này và nhấn OK sau khi làm xong.)

8.10. Xóa một tên vùng chọn

Khi thấy rằng không còn cần đến một tên vùng chọn nữa, bạn nên xóa chúng ra khỏi bảng tính để tránh việc làm bề bộn danh sách các tên vùng chọn. Bạn theo các bước sau đây:

1. Mở cửa sổ Define Name(Exel 2007: Name Manager.)

2. Chọn tên vùng chọn mà bạn muốn xóa.

Tip: Để xóa toàn bộ tên đã được đặt bạn vào tools/Name Manager (với điều kiện bạn đã cài đặt add-in Name Manager), nhấn nút “xóa hết”.

(Đối với excel 207, bạn có thể phối hợp với phím Shift (để chọn nhiều tên vùng chọn liên tục trong danh sách) hoặc phím Ctrl (để chọn những tên vùng chọn không liên tục trong danh sách)).

3. Nhấn nút Delete. Excel sẽ đề nghị bạn xác nhận việc xóa này.

4. Nếu đã chắc chắn muốn xóa, bạn nhấn OK hai lần.

8.11. Cách sử dụng tên ở vùng chọn giao nhau của hai vùng chọn

Nếu trong bảng tính của bạn có những vùng chọn phủ chồng lên nhau (giao nhau), bạn có thể sử dụng toán tử giao nhau (là một khoảng trắng) để tham chiếu đến những ô thuộc vùng chọn giao nhau này. Ví dụ, hình sau minh họa hai vùng chọn C4:E9 và D8:G11 giao nhau ở vùng chọn D8:E9. Để tạo một tham chiếu đến vùng chọn D8:E9 này, bạn dùng cách viết như sau: C4:E9 D8:G11

Nếu bạn đã đặt tên cho các vùng chọn giao nhau, việc sử dụng toán tử giao nhau sẽ làm cho mọi thứ trở nên dễ đọc hơn nhiều, vì bạn có thể tham chiếu đến một ô riêng lẻ bằng cách sử dụng tiêu đề của hàng và cột của ô đó. Ví dụ, bạn xem hình sau, vùng chọn C6:C10 có tên là January và vùng chọn C7:F7 có tên là Rent.Để tham chiếu đến giá trị của ô C7 (là ô giao nhau của hay vùng chọn trên), tại ô I6 bạn chỉ cần gõ = January Rent.

Công thức tại ô I6 (xem trên Formular Bar) là tham chiếu đến ô giao nhau giữa hai vùng chọn January và Rent.

Trong trường hợp kết quả bạn nhận được là #NULL!, thì có nghĩa là hai vùng chọn mà bạn nhập trong công thức không có ô nào giao nhau hết, bạn cần xác định lại chính xác hai vùng chọn mà bạn muốn tham chiếu đến giá trị của ô giao nhau giữa chúng.

Top

9. So sánh tên và nhãn trong công thức

- 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.

Ví dụ: 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.

Sau đó bạn bôi đen vùng B1:B3, vào Insert/Name/Label chọn Option Colum Labels và nhấn nút add. Như vậy bạn đã tạo nhãn cho vùng B1:B3, tương tự như vậy, bạn tạo nhãn cho các vùng C1:C3, D1:D3. Xem hình sau

Nếu bạn mở hộp Name, bạn sẽ thấy đủ 5 tên này. Nhưng các nhãn này lại không có mặt trong hộp tên.

Các nhãn không có trong hộp Name

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) (không có gạch nối “_” giữa “Product” và “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ụng tên) hay =Product 3 East (sử dụng 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)

Top

10. Tên của Name cũng chính là tên 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.

Ví dụ : 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à vùng 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.

+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.

Ví dụ: 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)+CO UNTA(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)
* DIEM = 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 trung bình của mỗi học sinh.

+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.

Ví dụ : 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.

+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.

Ví dụ: Ta muốn kết quả :
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".

Bạn lập công thức : =IF(B1="","",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",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)))))))))))))

Bảo đảm rằng khi bạn gõ công thức này vào thì excel sẽ báo lỗi. Vì vậy để thực hiện được công thức này, bạn đặt tên như sau:

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))))))

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)

Top