Using A Range Name - Sử dụng tên cho dãy

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part I: MASTERING EXCEL RANGES AND FORMULAS - Nắm vững các dãy và công thức trong Excel

• Chapter 1: Getting The Most Out Of Range - Tận dụng tối đa các dãy

• Chapter 2: Using a Range Name - Sử dụng tên cho dãy

• Chapter 3: Building Basic Formulas - Thiết lập các công thức (cơ bản)

• Chapter 4: Creating Advanced Formulas - Thiết lập các công thức (nâng cao)

• Chapter 5: Troubleshooting Formulas - Xử lý lỗi công thức

Chương 2, vì có rất ít thời gian, tôi sẽ dịch thẳng mà không trình bày kiểu song ngữ. Mong các bạn thích xem kiểu song ngữ thông cảm.


Chapter 2 - USING A RANGE NAME
Sử dụng tên cho dãy

Mặc dù các dãy (range) 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 dãy:

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

• Ký hiệu dãy không trực giác. Để 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 dãy đó mới biết.

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

Bạn có thể khắc phục những vấn đề trên bằng cách sử dụng tên cho dãy (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 độ dãy. Ví dụ, để đưa dãy vào công thức, bạn sử dụng tên của dãy đó thay vì chọn dãy hoặc nhập tọa độ của nó. Bạn có thể tạo bao nhiêu tên dãy tùy thích và thậm chí bạn có thể gán nhiều tên cho cùng một dãy.

Các tên dãy làm cho các công thức trực giác và dễ đọc. Ví dụ, việc gán tên DSo_8 vào một dãy, chẳng hạn như 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 dãy cũng tăng tính chính xác của các phép tính với dãy bởi vì bạn không cần phải xác định tọa độ của dãy nữa.

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

• Các tên dễ nhớ hơn các tọa độ dãy.

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

• Các dãy đượ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 dãy.

• Các tên làm cho 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 dãy đã được đặt tên.

Trong những bài sau đây, tôi sẽ trình bày cách đặt tên cho dãy và làm việc với các tên dãy, với hy vọng bạn sẽ thấy được sức mạnh cũng như sự linh hoạt mà các tên dãy mang lại khi bạn làm việc với bảng tính của mình.
​

 

[BNTT]

Defining a Range Name - Đặt tên cho một dãy


Đặt tên cho một dãy

Các tên dãy 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 dãy 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 dãy trong công thức.

• Để làm cho việc nhập tên dãy dễ dàng hơn, và nhanh hơn, bạn nên cố gắng đặt tên cho dãy 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à (có lẽ) 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, Autpen, 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 dãy.
​

Sử dụng Name Box (hộp Name) để đặt tên cho dãy

Name Box (hộp Name) 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 dãy cách dễ dàng hơn:

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

• 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 dãy đã được đặt tên, bạn mở danh sách xổ xuống này và chọn tên của dãy mà bạn muốn, Excel sẽ di chuyển ngay đến dãy đó 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 dãy hiện hành (do nó dài quá), bạn di chuyển curson (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), curson 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.

Hình 2.1.
Khi bạn chọn một dãy 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 dãy C6:F6)​

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

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

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

3. Nhập tên mà bạn muốn đặt cho dãy và nhấn Enter. Excel tự động định nghĩa một tên mới.​

Để tự thực hành, bạn có thể download file ví dụ tại đây: Expenses.xlsx
​

 

[BNTT]

Defining a Range Name - Đặt tên cho một dãy


Sử dụng hộp thoại New Name để đặt tên cho dãy

Sử dụng hộp Name để đặt tên cho dãy thì nhanh và trực giác. Tuy nhiên, nó có hai khuyết điểm nhỏ (nhưng gây không ít phiền toái) 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 dãy đã mang cái tên kia (là 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 dãy và nhập vào một tên, dĩ nhiên là tên khác.

• Nếu bạn chọn dãy, mà vô ý chọn không chính xác, và đã đặt tên cho nó, 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 dãy 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 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 dãy nếu như bạn vô ý chọn không đúng.

• Bạn có thể xóa một tên dãy đã có.

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

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

2. 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... Hộp thoại New Name xuất hiện như ở hình 2.2.

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

4. 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). Tôi sẽ nói về những lợi ích của việc giới hạn phạm vi ảnh hưởng của tên dãy trong bảng tính ở phần cuối bài này.

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

6. Nếu tọa độ của dãy (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 cái nút nhỏ ở ngoài cùng bên phải khung Refers to (có mũi tên nhỏ màu đỏ) để quay ra bảng tính và chọn lại dãy cho chính xác; sau khi chọn xong thì nhấp vào cái nút nhỏ (cũng ở ngoài cùng bên phải) để 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).

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

Hình 2.2.
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​

Thay đổi phạm vi ảnh hưởng của tên dãy trong bảng tính

Excel 2007 cho phép bạn định nghĩa phạm vi ảnh hưởng (Scope) của một tên dãy. Phạm vi này cho biết mức độ mà tên dãy 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 dãy 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 dãy 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 dãy 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 dãy 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 dãy 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 dãy 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.​

​

 

[BNTT]

Defining a Range Name - Đặt tên cho một dãy


Sử dụng tiêu đề của hàng hay cột để đặt tên cho dãy

Khi bạn sử dụng hộp thoại New Name, bạn sẽ thấy đôi khi Excel đã gợi ý sẵn cho bạn một cái tên cho dãy được chọn. Ví dụ, hình 2.3 cho thấy Excel đã gợi ý sẵn tên Salaries cho dãy C9:F9. Trong thực tế bảng tính, bạn thấy Salaries là tiêu đề hàng của dãy đượ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 dãy.

Hình 2.3
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ự đoán, bạn có thể yêu cầu nó sử dụng một tiêu đề có sẵn để làm tên dãy, theo những bước sau đây:

1. Chọn dãy 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 dãy (xem hình 2.4)

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

2. Chọn Formulas, Create from Selection, hoặc nhấn Ctrl+Shift+F3.Excel sẽ hiển thị hộp thoại Create Names from Selection như minh họa ở hình 2.5.

Hình 2.5
Dùng hộp thoại Create Names from Selection để xác định vị trí của tiêu đề muốn dùng làm tên cho dãy​

3. Excel sẽ đoán ô chứa tên tiêu đề mà bạn muốn dùng làm tên cho dãy nằm ở vị trí nào (Top row: ô nằm trong hàng trên cùng, Left Column: ô nằm ở cột ngoài cùng bên trái, Bottom row: ô nằm ở hàng dưới cùng, Right Column: ô nằm ở cột ngoài cùng bên phải) 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.

4. Nhấn OK.

Ghi chú: Nếu tiêu đề mà bạn muốn chọn làm tên cho dãy 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 dãy 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 dãy 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 dãy. Ví dụ, trong hình 2.6, sau khi bạn chọn dãy B5:F10 và gọi hộp thoại Create Names from Selection, 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 dãy, 4 cái theo cột (January, February, March, Total) và 5 cái theo hàng (Advertising, Rent, Supplies, Salaries, Utilities).

Hình 2.6
Excel có thể tạo nhiều tên dãy cùng một lúc​

Ở trên, tôi nói rằng tạo được 10 tên dãy, 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 dãy được chọn, và nó sẽ sử dụng text (nếu có) trong ô này làm tên cho cả dãy. Bạn xem lại hình 2.6, ô trên cùng bên trái của dãy B5:F10 là ô B5, đang chứa chữ EXPENSES. Sau khi bạn nhấn OK ở ví dụ trên, dãy thứ 10 chính là dãy Expenses, có tọa độ là C6:F10 (xem hình 2.7, để ý cái gì đang ở trong Name Box).

Hình 2.7
Khi tạo nhiều tên dãy 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 dãy chọn để làm tên cho cả dãy​

Để tự thực hành, bạn có thể download file ví dụ tại đây: Expenses.xlsx
​

 

[BNTT]

Defining a Range Name - Đặt tên cho một dãy


Đặ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 để làm cho chúng 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 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.
  
  
  

  
  Hình 2.8
  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 dãy A5:B7 và chọn Chọn Formulas, Create from Selection, rồi nhấn OK. (Xem lại bài Sử dụng tiêu đề của hàng hay cột để đặt tên cho dãy)

• 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 Formulas, Define Name để mở hộp thoại 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 2.9 minh họa cách đặt tên cho hằng số Rate theo cách này.
  
  

  
  Hình 2.9
  Đặt tên cho một hằng số trực tiếp trong hộp thoại New Name​
  
  Ghi chú: Ngoài việc nhập giá trị trực tiếp, Excel còn cho phép bạn gán một công thức cho một tên. Ví dụ, bạn có thể nhập = YEAR(NOW()) trong khung Refers to để tạo một cái tên sẽ luôn luôn trả về giá trị là con số của năm hiện hành. Tính năng này rất thích hợp trong việc gán tên cho một công thức dài và phức tạp.

Để tự thực hành, bạn có thể download file ví dụ tại đây: Amortization Schedule.xlsx
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Làm việc với các tên dãy

Sau khi đã định nghĩa tên cho một dãy, 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.


Tham chiếu đến một tên dãy

Sử dụng một tên dãy 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 dãy bằng tên của dãy đó. 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 dãy 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. Các bước thực hiện điều này như sau:

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 dãy 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 2.10.

Hình 2.10
Chọn lệnh Use in Formula để chèn một tên dãy 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 dãy 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 dãy 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 dãy 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 dãy đó. Ví dụ, nếu bạn muốn sử dụng tên dãy Rate nằm trong bảng tính Mortgage Amortization, bạn phải nhập như sau:

'Mortgage Amortization.xls'!Rate​

​

Để tự thực hành, bạn có thể download file ví dụ tại đây: Amortization Schedule.xlsx
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Sử dụng AutoComplete để chèn tên dãy

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 dãy cũng vậy. Khi bạn nhập vài chữ đầu tiên của một tên dãy 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 dãy. Hình 2.11 minh họa việc chèn tên dãy 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 dãy cũng sẽ hiện ra khi bạn chọn tên dãy 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 dãy trong danh sách và nhấn phím Tab để chèn nó vào công thức.

Hình 2.11
Chèn tên dãy bằng AutoComplete​

Để tự thực hành, bạn có thể download file ví dụ tại đây: Expenses.xlsx
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Chọn một dãy bằng cách sử dụng tên của nó

Một dãy, 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 dãy bằng tên của nó:

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

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

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Chèn bảng Danh sách các tên dãy đã đượ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 dãy trong bảng tính của bạn đại diện cho dãy ô nào. Để thuận tiện cho việc tra cứu các tên dãy, bạn nên chèn một bảng danh sách các tên dãy 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 dãy, và cột kia là tọa độ tuyệt đối của dãy có tên đó. Bạn theo các bước sau đây:

1. Chọn một vùng đủ 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 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 dãy vào ô hiện hành.
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Sử dụng Name Manager

Name Manager là một công cụ quản lý các tên dãy. 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 dãy, nhưng với Name Manager của Excel 2007, bạn có thể xóa hàng loạt tên dãy chỉ với một cú nhấn chuột. Để hiển thị Name Manager, bạn chọn Formulas, Name Manager. Hình 2.12 sau đây minh họa một Name Manager.

Hình 2.12
Sử dụng Name Manager để chỉnh sửa, lọc và xóa các tên dãy​

Bạn cũng có thể tạo mới một tên dãy 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 ở bài Sử dụng hộp thoại New Name để đặt tên cho dãy.
​

Lọc các tên dãy

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 dãy đã được định nghĩa, danh sách tên dãy 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, Excel 2007 cho phép bạn lọc (filter) các tên dãy, 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 dãy, kể cả các tên dãy 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 dãy 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 dãy có phạm vị ảnh hưởng trong Sheet hiện hành.

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

Names with Errors : Xem các tên dãy đang chứa các giá trị lỗi (chứ không phải là tên dãy 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 dãy không chứa các giá trị lỗi.

Defined Names : Chỉ xem các tên dãy 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 dãy ô (hay còn gọi là một vùng chọn) là một Table.
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Biên tập (sửa chữa, thay đổi) tọa độ của một tên dãy

Nếu bạn muốn tên dãy 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 dãy đến nơi khác. Excel sẽ tự động cập nhật tọa độ mới cho tên dãy.

• 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 dãy khác, trong cửa sổ Name Manager, bạn chọn tên dãy 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).

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


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 dãy

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 dãy hiện có, Excel sẽ tự động điều chỉnh tọa độ của dãy mới (trong tham chiếu của tên dãy). Tuy nhiên, sẽ là vấn đề nếu như bạn thêm dữ liệu vào cuối dãy. 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 dãy bằng tay để thêm các dữ liệu mới vào trong tên dãy 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 dãy:

• Cách 1: Chọn thêm một hàng (cột) trống vào cuối dãy
  Khi chọn một dãy để đị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 cột, hoặc cả hai) trống ở cuối dãy (thường thì tôi hay thêm một hàng trống ở dưới cùng của dãy). Hình 2.13 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 (dãy C4:C11), nhưng khi chọn dãy để đặt tên, tôi chọn thêm ô C12, là một ô trống.
  
  
  

  

  
  Hình 2.13
  Để làm cho Excel tự động điều chỉnh tọa độ của một tên dãy, đưa thêm một ô trống vào cuối dãy 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 dãy. Khi bạn thêm dữ liệu vào hàng cuối (có thể thấy được) của dãy, 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 dãy đã được đặt tên, và điều đó làm cho tọa độ của tên dãy sẽ tự động điều chỉnh. Ví dụ, trong bảng tính ở hình 2.13, sau khi chèn thêm dữ liệu ở hàng 12, thì dãy có tên là Amount tự động sửa tọa độ là C4:C13. Xem minh họa ở hình 2.14.
  
  
  

  

  
  Hình 2.14
  Tọa độ mới của dãy 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 dãy.

• Còn một cách nữa, là dùng tên động cho dãy, mà thường là sử dụng hàm OFFSET() trong khung Refers to khi định nghĩa tên cho dãy. Tôi sẽ nói về vấn đề này ở một bài khác.

Để tự thực hành, bạn có thể download file ví dụ tại đây: Penske.xlsx
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


Đổi tên một tên dãy

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

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

• Trong cửa sổ Name Manager, bạn chọn tên dãy 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.

Xóa một tên dãy

Khi thấy rằng không còn cần đến một tên dãy 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 dãy. Bạn theo các bước sau đây:

1. Mở cửa sổ Name Manager.

2. Chọn tên dãy mà bạn muốn xóa. Bạn có thể phối hợp với phím Shift (để chọn nhiều tên dãy liên tục trong danh sách) hoặc phím Ctrl (để chọn những tên dãy 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.
​

 

[BNTT]

Working with Range Names - Làm việc với các tên dãy


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

Nếu trong bảng tính của bạn có những dãy 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 giao nhau này. Ví dụ, hình 2.15 minh họa hai dãy C4:E9 và D8:G11 giao nhau ở vùng D8:E9. Để tạo một tham chiếu đến vùng D8:E9 này, bạn dùng cách viết như sau: C4:E9 D8:G11

Hình 2.15​

Nếu bạn đã đặt tên cho các dãy 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 2.16, dãy C6:C10 có tên là January và dãy C7:F7 có tên là Rent. Để tham chiếu đến giá trị của ô C7 (là ô giao nhau của hay dãy trên), bạn chỉ cần gõ (tại ô I6): = January Rent.

Có lẽ đây là một vấn đề khá mới (ngay cả với tôi, khi dịch tới đây, tôi mới biết điều này!). Nếu không biết cách này, để tham chiếu đến giá trị của C7, chúng ta phải dùng những hàm như MATCH(), INDEX()... vừa dài dòng, vừa khó.

Hình 2.16
Công thức tại ô I6 (xem trên Formular Bar) là tham chiếu đến ô giao nhau giữa hai dãy January và Rent. ​

Trong trường hợp kết quả bạn nhận được là #NULL!, thì có nghĩa là hai dãy 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 dãy mà bạn muốn tham chiếu đến giá trị của ô giao nhau giữa chúng.​

Để tự thực hành, bạn có thể download file ví dụ tại đây: Expenses.xlsx
​

-------------------- HẾT CHƯƠNG 2 --------------------

Mời xem tiếp chương 3: Building Basic Formulas - Thiết lập những công thức cơ bảnhttp://www.giaiphapexcel.com/forum/showthread.php?t=9546​