Làm việc với tên phạm vi

Trong khi thực hiện Phân tích dữ liệu, việc tham chiếu đến các dữ liệu khác nhau sẽ có ý nghĩa và dễ dàng hơn nếu tham chiếu là theo Tên thay vì tham chiếu ô - một ô hoặc một dải ô. Ví dụ: nếu bạn đang tính Giá trị hiện tại ròng dựa trên Tỷ lệ chiết khấu và một loạt Dòng tiền, công thức

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

có ý nghĩa hơn

C10 = NPV (C2, C6: C8)

Với Excel, bạn có thể tạo và sử dụng các tên có ý nghĩa cho các phần khác nhau của dữ liệu. Những lợi thế của việc sử dụng tên phạm vi bao gồm:

• Tên Phạm vi có ý nghĩa (chẳng hạn như Cash_Flows) dễ nhớ hơn nhiều so với địa chỉ Phạm vi (chẳng hạn như C6: C8).

• Nhập tên ít bị lỗi hơn nhập địa chỉ ô hoặc dải ô.

• Nếu bạn nhập sai tên trong công thức, Excel sẽ hiển thị #NAME? lỗi.

• Bạn có thể nhanh chóng di chuyển đến các vùng trong trang tính của mình bằng cách sử dụng các tên đã xác định.

• Với Tên, công thức của bạn sẽ dễ hiểu hơn và dễ sử dụng hơn. Ví dụ: công thức Net_Income = Gross_Income - Các khoản khấu trừ trực quan hơn C40 = C20 - B18.

• Tạo công thức với tên phạm vi dễ dàng hơn so với địa chỉ ô hoặc phạm vi. Bạn có thể sao chép tên ô hoặc dải ô vào công thức bằng cách sử dụng tính năng Tự động điền công thức.

Trong chương này, bạn sẽ học -

• Quy tắc cú pháp cho tên.
• Tạo tên cho các tham chiếu ô.
• Tạo tên cho hằng số.
• Quản lý tên.
• Phạm vi tên được xác định của bạn.
• Chỉnh sửa tên.
• Lọc tên.
• Đang xóa tên.
• Áp dụng tên.
• Sử dụng tên trong công thức.
• Xem tên trong sổ làm việc.
• Sử dụng dán tên và dán danh sách.
• Sử dụng tên cho các giao lộ trong phạm vi.
• Sao chép công thức với tên.

Sao chép Tên bằng Tự động Hoàn thành Công thức

Nhập chữ cái đầu tiên của tên vào công thức. Một hộp thả xuống xuất hiện với tên hàm và tên phạm vi. Chọn tên cần thiết. Nó được sao chép vào công thức của bạn.

Quy tắc cú pháp tên phạm vi

Excel có các quy tắc cú pháp sau cho tên:

• Bạn có thể sử dụng bất kỳ sự kết hợp nào của các chữ cái, số và các ký hiệu - dấu gạch dưới, dấu gạch chéo ngược và dấu chấm. Các ký hiệu khác không được phép.

• Tên có thể bắt đầu bằng một ký tự, dấu gạch dưới hoặc dấu gạch chéo ngược.

• Tên không được bắt đầu bằng một số (ví dụ - 1stQuarter) hoặc giống với địa chỉ ô (ví dụ - QTR1).

• Nếu bạn thích sử dụng những tên như vậy, hãy đặt trước tên bằng dấu gạch dưới hoặc dấu gạch chéo ngược (ví dụ - \ 1stQuarter, _QTR1).

• Tên không được chứa khoảng trắng. Nếu bạn muốn phân biệt hai từ trong tên, bạn có thể sử dụng dấu gạch dưới (ví dụ- Dòng tiền thay vì Dòng tiền)

• Các tên đã xác định của bạn không được xung đột với các tên được xác định nội bộ của Excel, chẳng hạn như Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. Nếu bạn xác định các tên giống nhau, chúng sẽ ghi đè các tên nội bộ của Excel và bạn sẽ không nhận được bất kỳ thông báo lỗi nào. Tuy nhiên, người ta khuyên không nên làm như vậy.

• Giữ tên ngắn gọn nhưng dễ hiểu, mặc dù bạn có thể sử dụng tối đa 255 ký tự

Tạo tên dải ô

Bạn có thể tạo Tên phạm vi theo hai cách:

• Sử dụng Name box.

• Sử dụng New Name hộp thoại.

• Sử dụng Selection hộp thoại.

Tạo Tên Phạm vi bằng Hộp Tên

Để tạo tên Phạm vi, sử dụng Namehộp ở bên trái của thanh công thức là cách nhanh nhất. Làm theo các bước dưới đây -

Step 1 - Chọn phạm vi mà bạn muốn xác định Tên.

Step 2 - Bấm vào ô Tên.

Step 3 - Nhập tên và nhấn Enter để tạo Tên.

Tạo tên phạm vi bằng cách sử dụng hộp thoại Tên mới

Bạn cũng có thể tạo Tên Phạm vi bằng cách sử dụng hộp thoại Tên Mới từ tab Công thức.

Step 1 - Chọn phạm vi mà bạn muốn xác định tên.

Step 2 - Nhấp vào tab Công thức.

Step 3- Bấm Xác định Tên trong nhóm Tên đã Xác định. CácNew Name hộp thoại xuất hiện.

Step 4 - Nhập tên vào ô bên cạnh Tên

Step 5- Kiểm tra xem phạm vi được chọn và hiển thị trong hộp Tham khảo có chính xác không. Bấm OK.

Tạo tên phạm vi bằng cách sử dụng hộp thoại Tạo tên từ lựa chọn

Bạn cũng có thể tạo tên Phạm vi bằng cách sử dụng Create Names từ hộp thoại Lựa chọn từ tab Công thức, khi bạn có các giá trị Văn bản liền kề với phạm vi của bạn.

Step 1 - Chọn phạm vi mà bạn muốn xác định tên cùng với hàng / cột chứa tên.

Step 2 - Nhấp vào tab Công thức.

Step 3 - Bấm Create from Selectiontrong nhóm Tên được xác định. CácCreate Names from Selection hộp thoại xuất hiện.

Step 4 - Chọn hàng trên cùng khi Văn bản xuất hiện ở hàng trên cùng của vùng chọn.

Step 5- Kiểm tra phạm vi đã được chọn và hiển thị trong hộp bên cạnh Tham chiếu là chính xác. Bấm OK.

Bây giờ, bạn có thể tìm giá trị lớn nhất trong phạm vi với =Sum(Tên học sinh), như hình dưới đây -

Bạn cũng có thể tạo tên với nhiều lựa chọn. Trong ví dụ dưới đây, bạn có thể đặt tên hàng dấu của mỗi học sinh bằng tên của học sinh.

Bây giờ, bạn có thể tìm tổng điểm cho mỗi học sinh với =Sum (tên sinh viên), như hình dưới đây.

Tạo tên cho hằng số

Giả sử bạn có một hằng số sẽ được sử dụng trong sổ làm việc của bạn. Bạn có thể chỉ định tên cho nó trực tiếp mà không cần đặt nó vào một ô.

Trong ví dụ dưới đây, Lãi suất Ngân hàng Tiết kiệm được đặt thành 5%.

• Nhấp vào Xác định Tên.
• Trong hộp thoại Tên mới, nhập Savings_Bank_Interest_Rate vào hộp Name.
• Trong Phạm vi, chọn Sổ làm việc.
• Trong hộp Tham chiếu, xóa nội dung và nhập 5%.
• Bấm OK.

Tên Savings_Bank_Interest_Rateđược đặt thành không đổi 5%. Bạn có thể xác minh điều này trong Trình quản lý tên. Bạn có thể thấy rằng giá trị được đặt thành 0,05 và trongRefers to = 0,05 được đặt.

Quản lý tên

Sổ làm việc Excel có thể có bất kỳ số ô và phạm vi được đặt tên nào. Bạn có thể quản lý những tên này bằng Trình quản lý tên.

• Bấm vào tab Công thức.

• Nhấp chuột Name Manager bên trong Defined Namesnhóm. CácName Managerhộp thoại xuất hiện. Tất cả các tên được xác định trong sổ làm việc hiện tại được hiển thị.

Danh sách của Names được hiển thị với Values, Cell Reference (bao gồm Tên trang tính), Scope và Comment.

Trình quản lý Tên có các tùy chọn để -

• Xác định một New Đặt tên với New Cái nút.

• Edit một Tên xác định.

• Delete một Tên xác định.

• Filter tên được xác định theo danh mục.

• Sửa đổi phạm vi của một tên được xác định mà nó Refers to.

Phạm vi của một tên

Các Scopecủa một tên theo mặc định là sổ làm việc. Bạn có thể tìm thấyScope của một tên đã xác định từ danh sách các tên trong Scope cột trong Name Manager.

Bạn có thể xác định Scope của một New Name khi bạn xác định tên bằng cách sử dụng New Namehộp thoại. Ví dụ, bạn đang xác định tên Lãi suất. Sau đó, bạn có thể thấy rằngScope sau đó New Name Lãi suất_Rate là Workbook.

Giả sử bạn muốn Scope lãi suất này bị hạn chế cho điều này Worksheet chỉ có.

Step 1- Nhấp vào mũi tên xuống trong Hộp Phạm vi. Các tùy chọn Phạm vi có sẵn xuất hiện trong danh sách thả xuống.

Các tùy chọn Phạm vi bao gồm Workbookvà tên trang tính trong sổ làm việc.

Step 2- Bấm vào tên trang tính hiện tại, trong trường hợp này là NPV và bấm OK. Bạn có thể xác định / tìm tên trang tính trong tab trang tính.

Step 3 - Để xác minh rằng Phạm vi là trang tính, hãy nhấp vào Name Manager. Trong cột Phạm vi, bạn sẽ tìm thấy NPV cho Lãi suất_Rate. Điều này có nghĩa là bạn chỉ có thể sử dụng Name Interest_Rate trong NPV của Worksheet, chứ không phải trong các Worksheet khác.

Note - Khi bạn xác định Phạm vi của Tên, nó không thể được sửa đổi sau này.

Xóa tên có giá trị lỗi

Đôi khi, có thể xảy ra trường hợp định nghĩa Tên có thể có lỗi vì nhiều lý do khác nhau. Bạn có thể xóa các tên như sau:

Step 1 - Bấm Filter bên trong Name Manager hộp thoại.

Các tùy chọn lọc sau xuất hiện:

• LÀm sạch bộ lọc
• Tên trong phạm vi trang tính
• Các tên trong phạm vi Workbook
• Tên có lỗi
• Tên không có lỗi
• Tên được xác định
• Tên bảng

Bạn có thể áp dụng Filter đến defined Names bằng cách chọn một hoặc nhiều tùy chọn này.

Step 2 - Chọn Names with Errors. Tên có chứa giá trị lỗi sẽ được hiển thị.

Step 3 - Từ danh sách thu được của Names, chọn những cái bạn muốn xóa và nhấp vào Delete.

Bạn sẽ nhận được một tin nhắn, xác nhận xóa. Bấm OK.

Chỉnh sửa tên

Bạn có thể dùng Edit tùy chọn trong Name Manager hộp thoại đến -

• Thay đổi Name.

• Sửa đổi Refers to phạm vi

• Chỉnh sửa Comment trong một Name.

Thay đổi tên

Step 1 - Bấm vào ô chứa hàm Large.

Bạn có thể thấy, hai giá trị khác được thêm vào mảng, nhưng không được bao gồm trong hàm vì chúng không phải là một phần của Array1.

Step 2 - Nhấp vào Name bạn muốn chỉnh sửa trong Name Managerhộp thoại. Trong trường hợp này,Array1.

Step 3 - Bấm Edit. CácEdit Name hộp thoại xuất hiện.

Step 4 - Thay đổi Name bằng cách nhập tên mới mà bạn muốn vào Name Box.

Step 5 - Nhấp vào Range nút ở bên phải của Refers to Hộp và bao gồm các tham chiếu ô mới.

Step 6 - Thêm một Comment (Không bắt buộc)

Thông báo rằng Scope không hoạt động và do đó không thể thay đổi.

Bấm OK. Bạn sẽ quan sát những thay đổi được thực hiện.

Áp dụng tên

Hãy xem xét ví dụ sau:

Như bạn quan sát, tên không được định nghĩa và sử dụng trong hàm PMT. Nếu bạn đặt hàm này ở một nơi khác trong trang tính, bạn cũng cần nhớ chính xác vị trí của các giá trị tham số. Bạn biết rằng sử dụng tên là một lựa chọn tốt hơn.

Trong trường hợp này, hàm đã được xác định với các tham chiếu ô không có tên. Bạn vẫn có thể xác định tên và áp dụng chúng.

Step 1 - Sử dụng Create from Selection, xác định tên.

Step 2- Chọn ô chứa công thức. Nhấp vào

bên cạnhDefine Name bên trong Defined Names nhóm trên Formulaschuyển hướng. Từ danh sách thả xuống, hãy nhấp vàoApply Names.

Step 3 - Sự Apply Nameshộp thoại xuất hiện. ChọnNames mà bạn muốn Apply và bấm OK.

Các tên đã chọn sẽ được áp dụng cho các ô đã chọn.

Bạn cũng có thể Apply Names vào toàn bộ trang tính, bằng cách chọn trang tính và lặp lại các bước trên.

Sử dụng tên trong một công thức

Bạn có thể sử dụng một Name trong một Formula theo những cách sau -

• Gõ Name nếu bạn nhớ nó, hoặc

• Nhập một hoặc hai chữ cái đầu tiên và sử dụng Excel Formula Autocomplete đặc tính.

• Nhấp vào Sử dụng trong Công thức trong nhóm Tên được Xác định trên tab Công thức.

  • Chọn Tên được yêu cầu từ danh sách thả xuống gồm các tên đã xác định.

  • Bấm đúp vào tên đó.

• Sử dụng Paste Name hộp thoại.

  • Chọn tùy chọn Dán tên từ danh sách thả xuống gồm các tên đã xác định. Hộp thoại Dán Tên xuất hiện.

  • Chọn Name bên trong Paste Names hộp thoại và bấm đúp vào nó.

Xem tên trong sổ làm việc

Bạn có thể nhận được tất cả Names trong sổ làm việc của bạn cùng với References và Save họ hoặc Print chúng.

• Bấm vào ô trống nơi bạn muốn sao chép Names trong sổ làm việc của bạn.

• Nhấp chuột Use in Formula bên trong Defined Names nhóm.

• Nhấp chuột Paste Names từ danh sách thả xuống.

• Nhấp chuột Paste List bên trong Paste Name hộp thoại xuất hiện.

Danh sách tên và các tham chiếu tương ứng của chúng được sao chép tại vị trí được chỉ định trên trang tính của bạn như thể hiện trong ảnh chụp màn hình dưới đây -

Sử dụng tên cho các giao lộ phạm vi

Range Intersections là những ô riêng lẻ có hai Dãy chung.

Ví dụ, trong dữ liệu được đưa ra bên dưới, Dãy B6: F6 và Dãy C3: C8 có chung Ô C6, thực sự đại diện cho điểm của sinh viên Kodeda, Adam trong Bài kiểm tra 1.

Bạn có thể làm cho điều này có ý nghĩa hơn với Range Names.

• Tạo nên Names với Create from Selection cho cả Sinh viên và Thi.

• Của bạn Names sẽ như sau:

• Gõ =Kodeda_Adam Exam_1 trong B11.

Ở đây, bạn đang sử dụng phép toán Giao nhau dãy, khoảng cách giữa hai dãy.

Điều này sẽ hiển thị điểm của Kodeda, Adam trong Bài kiểm tra 1, được đưa ra trong Ô C6.

Sao chép công thức có tên

Bạn có thể sao chép công thức có tên bằng Copyvà Paste trong cùng một trang tính.

Bạn cũng có thể sao chép một công thức có tên vào một trang tính khác bằng cách copy và paste, cung cấp tất cả names bên trong formula có workbook như Scope. Nếu không, bạn sẽ nhận được một#VALUE lỗi.