Hướng dẫn giải thích tên Excel là gì và chỉ ra cách xác định tên cho ô, phạm vi, không đổi hoặc công thức. Bạn cũng sẽ học cách chỉnh sửa, lọc và xóa các tên được xác định trong Excel.
Tên trong Excel là một điều nghịch lý: là một trong những tính năng hữu ích nhất, chúng thường được coi là vô nghĩa hoặc nerdy. Lý do là rất ít người dùng hiểu bản chất của tên Excel. Hướng dẫn này sẽ không chỉ dạy bạn cách tạo ra một phạm vi được đặt tên trong Excel, mà còn cho thấy cách tận dụng tính năng này để giúp các công thức của bạn dễ dàng viết, đọc và sử dụng lại.
Tên có nghĩa là gì trong Excel?
Trong các tên cuộc sống hàng ngày được sử dụng rộng rãi để đề cập đến con người, đối tượng và địa điểm địa lý. Ví dụ, thay vì nói "Thành phố nằm ở vĩ độ 40.7128 ° N và kinh độ 74,0059 ° W, bạn chỉ cần nói" Thành phố New York ".
Tương tự, trong Microsoft Excel, bạn có thể đặt tên người có thể đọc được cho một ô hoặc một loạt các ô và tham khảo các ô đó bằng tên chứ không phải bằng tham chiếu.
Chẳng hạn, để tìm tổng số doanh số [B2: B10] cho một mặt hàng cụ thể [E1], bạn có thể sử dụng công thức sau:
=SUMIF[$A$2:$A$10, $E$1, $B$2:$B$10]
Hoặc, bạn có thể đưa ra những cái tên có ý nghĩa cho các phạm vi và các ô riêng lẻ và cung cấp các tên đó cho công thức:
=SUMIF[items_list, item, sales]
Nhìn vào ảnh chụp màn hình bên dưới, công thức nào trong hai công thức dễ hiểu hơn?
Các loại tên Excel
Trong Microsoft Excel, bạn có thể tạo và sử dụng hai loại tên:
Tên được xác định - một tên đề cập đến một ô, phạm vi của ô, giá trị không đổi hoặc công thức. Ví dụ: khi bạn xác định tên cho một loạt các ô, nó được gọi là phạm vi được đặt tên hoặc phạm vi được xác định. Những cái tên này là chủ đề của hướng dẫn ngày nay. - a name that refers to a single cell, range of cells, constant value, or formula. For example, when you define a name for a range of cells, it's called a named range, or defined range. These names are subject of today's tutorial.
Tên bảng - Tên của bảng Excel được tạo tự động khi bạn chèn một bảng vào bảng tính [Ctrl + T]. Để biết thêm thông tin về bảng Excel, vui lòng xem cách thực hiện và sử dụng bảng trong Excel. - a name of an Excel table that is created automatically when you insert a table in a worksheet [Ctrl + T]. For more information about Excel tables, please see How to make and use a table in Excel.
Cách tạo phạm vi xuất sắc có tên
Nhìn chung, có 3 cách để xác định tên trong hộp Excel: Tên, Xác định nút Tên và Trình quản lý tên Excel.
Nhập tên vào hộp tên
Hộp tên trong Excel là cách nhanh nhất để tạo phạm vi được đặt tên:
- Chọn một ô hoặc một loạt các ô mà bạn muốn đặt tên.
- Nhập tên vào hộp tên.Name Box.
- Nhấn phím Enter.
Voila, một phạm vi xuất sắc mới được tạo ra!
Tạo tên bằng cách sử dụng tùy chọn Xác định tên
Một cách khác để tạo ra một phạm vi được đặt tên trong Excel là:
- Chọn [các] ô.
- Trên tab Công thức, trong nhóm Xác định tên, nhấp vào nút Xác định tên.Define Name button.
- Trong hộp thoại tên mới, chỉ định ba điều:
- Trong hộp tên, nhập tên phạm vi.
- Trong thả xuống phạm vi, đặt phạm vi tên [sổ làm việc theo mặc định].
- Trong hộp tham khảo, kiểm tra tham chiếu và sửa nó nếu cần.
- Nhấp vào OK để lưu các thay đổi và đóng hộp thoại.
Ghi chú. Theo mặc định, Excel tạo ra một tên với các tài liệu tham khảo tuyệt đối. Nếu bạn muốn có một phạm vi tương đối được đặt tên, hãy xóa dấu $ khỏi tài liệu tham khảo [trước khi bạn làm điều này, hãy đảm bảo bạn hoàn toàn hiểu cách các tên tương đối hoạt động trong bảng tính]. By default, Excel creates a name with absolute references. If you'd rather have a relative named range, remove the $ sign from the reference [before you do this, make sure you fully understand how relative names behave in worksheets].
So với phương thức trước đó, sử dụng tên xác định trong Excel sẽ có thêm một vài lần nhấp, nhưng nó cũng cung cấp thêm một vài tùy chọn như đặt phạm vi của tên và thêm một nhận xét giải thích điều gì đó về tên. Ngoài ra, tính năng Xác định của Excel cho phép bạn tạo tên cho một công thức không đổi hoặc công thức.
Tạo một phạm vi được đặt tên bằng cách sử dụng Trình quản lý tên Excel
Thông thường, trình quản lý tên trong Excel được sử dụng để làm việc với các tên hiện có. Tuy nhiên, nó cũng có thể giúp bạn xây dựng một cái tên mới. Đây là cách:
- Chuyển đến tab Công thức> Nhóm Tên được xác định, Nhấp vào Tên Trình quản lý. Hoặc, chỉ cần nhấn Ctrl + F3 [cách ưa thích của tôi].Name Manager. Or, just press Ctrl + F3 [my preferred way].
- Ở góc trên cùng bên trái của cửa sổ hộp thoại Trình quản lý tên, nhấp vào nút Mới mới:New… button:
- Điều này sẽ mở hộp thoại Tên mới trong đó bạn định cấu hình một tên như đã trình bày trong phần trước.
Mẹo. Để nhanh chóng kiểm tra tên mới được tạo, chọn nó trong danh sách thả xuống hộp tên. Ngay khi bạn phát hành chuột, phạm vi trên bảng tính sẽ được chọn. To quickly test the newly created name, select it in the Name Box dropdown list. As soon as you release the mouse, the range on the worksheet will be selected.
Cách tạo tên Excel cho một hằng số
Ngoài các phạm vi được đặt tên, Microsoft Excel cho phép bạn xác định tên không có tham chiếu ô sẽ hoạt động như một hằng số được đặt tên. Để tạo tên như vậy, hãy sử dụng tính năng tên Excel DEFINE hoặc Trình quản lý tên như được giải thích ở trên.named constant. To create such a name, use either the Excel Define Name feature or Name Manager as explained above.
Chẳng hạn, bạn có thể tạo một tên như USD_EUR [tỷ lệ chuyển đổi USD - EUR] và gán một giá trị cố định cho nó. Đối với điều này, hãy nhập giá trị trước một dấu bằng [=] trong trường tham chiếu, ví dụ: = 0,93:
Và bây giờ, bạn có thể sử dụng tên này ở bất cứ đâu trong các công thức của mình để chuyển đổi USD thành EUR:
Ngay khi tỷ giá hối đoái thay đổi, bạn chỉ cập nhật giá trị ở một vị trí trung tâm và tất cả các công thức của bạn sẽ được tính toán lại trong một bước duy nhất!
Cách xác định tên cho một công thức
Theo cách tương tự, bạn có thể đặt tên cho một công thức Excel, ví dụ, cái trả về số lượng các ô không trống trong cột A, không bao gồm hàng tiêu đề [-1]:
=COUNTA[Sheet5!$A:$A]-1
Ghi chú. Nếu công thức của bạn đề cập đến bất kỳ ô nào trên bảng hiện tại, bạn không cần bao gồm tên trang tính trong các tài liệu tham khảo, Excel sẽ tự động làm điều đó cho bạn. Nếu bạn đang tham khảo một ô hoặc phạm vi trên một bảng tính khác, hãy thêm tên của trang tính theo sau là điểm nhấn dấu trước tham chiếu ô/phạm vi [như trong ví dụ công thức ở trên]. If your formula refers to any cells on the current sheet, you do not need to include the sheet name in the references, Excel will do it for you automatically. If you are referencing a cell or range on another worksheet, add the sheet's name followed by the exclamation point before the cell/range reference [like in the formula example above].
Bây giờ, bất cứ khi nào bạn muốn biết có bao nhiêu mục trong cột A trên Sheet5, không bao gồm tiêu đề cột, chỉ cần nhập dấu hiệu bình đẳng theo sau là tên của công thức của bạn trong bất kỳ ô nào, như thế này: = items_count
Cách đặt tên các cột trong Excel [tên từ lựa chọn]
Nếu dữ liệu của bạn được sắp xếp theo dạng bảng, bạn có thể nhanh chóng tạo tên cho từng cột và/hoặc hàng dựa trên nhãn của chúng:each column and/or row based on their labels:
- Chọn toàn bộ bảng bao gồm các tiêu đề cột và hàng.
- Chuyển đến tab Công thức> Xác định nhóm tên và nhấp vào nút Tạo từ lựa chọn. Hoặc, nhấn phím tắt Ctrl + Shift + F3.Create from Selection button. Or, press the keyboard shortcut Ctrl + Shift + F3.
- Dù bằng cách nào, tên tạo từ hộp thoại lựa chọn sẽ mở. Bạn chọn cột hoặc hàng có tiêu đề, hoặc cả hai, và nhấp vào OK.
Trong ví dụ này, chúng tôi có các tiêu đề ở hàng trên cùng và cột bên trái, vì vậy chúng tôi chọn hai tùy chọn này:
Kết quả là, Excel sẽ tạo ra 7 phạm vi được đặt tên, tự động chọn tên từ các tiêu đề:
- Táo, chuối, chanh và cam cho các hàng, và
- Jan, tháng 2 và MAR cho các cột.
Ghi chú. Nếu có bất kỳ khoảng trống nào giữa các từ trong nhãn tiêu đề, các không gian sẽ được thay thế bằng dấu gạch dưới [_]. If there are any spaces between words in the header labels, the spaces will be replaced with underscores [_].
Phạm vi được đặt tên động của Excel Dynamic
Trong tất cả các ví dụ trước, chúng tôi đã xử lý các phạm vi tĩnh có tên luôn đề cập đến cùng một ô, nghĩa là bạn sẽ phải cập nhật phạm vi tham chiếu theo cách thủ công bất cứ khi nào bạn muốn thêm dữ liệu mới vào phạm vi được đặt tên.
Nếu bạn đang làm việc với các bộ dữ liệu có thể mở rộng, có lý do để tạo ra một phạm vi động được đặt tên tự động cung cấp dữ liệu mới được thêm vào.
Hướng dẫn chi tiết về cách tạo ra một phạm vi động được đặt tên trong Excel có thể được tìm thấy ở đây:
- Công thức bù Excel để tạo phạm vi động
- Công thức chỉ mục để tạo một dải động
Quy tắc đặt tên Excel
Khi tạo tên trong Excel, có một vài quy tắc cần nhớ:
- Một tên Excel phải dài dưới 255 ký tự.
- Tên Excel không thể chứa khoảng trắng và hầu hết các ký tự dấu câu.
- Một tên phải bắt đầu bằng một chữ cái, dấu gạch dưới [_] hoặc dấu gạch chéo ngược [\]. Nếu một tên bắt đầu bằng bất cứ điều gì khác, Excel sẽ gây ra lỗi.
- Tên Excel không nhạy cảm trường hợp. Ví dụ: "táo", "táo" và "táo" sẽ được coi là cùng tên.
- Bạn không thể đặt tên phạm vi như tham chiếu ô. Đó là, bạn không thể đặt tên "A1" hoặc "AA1" cho một phạm vi.
- Bạn có thể sử dụng một chữ cái để đặt tên cho một phạm vi như "A", "B", "D", v.v. Các phím tắt để chọn một hàng hoặc cột cho ô hiện được chọn khi bạn nhập chúng vào tên tên].
Phạm vi tên Excel
Về tên Excel, phạm vi là vị trí hoặc cấp độ, trong đó tên được công nhận. Nó có thể là một trong hai:
- Bảng tính cụ thể - Cấp độ bảng tính địa phương
- Sổ làm việc - Cấp sách bài tập toàn cầu
Tên cấp bảng tính
Một tên cấp bảng tính được công nhận trong bảng tính nơi nó được đặt. Ví dụ: nếu bạn tạo một phạm vi được đặt tên và đặt phạm vi của nó thành Sheet1, nó sẽ chỉ được nhận ra trong Sheet1.
Để có thể sử dụng tên cấp bảng tính trong một bảng tính khác, bạn phải đặt trước tên của bảng tính theo sau là dấu chấm than [!], Như thế này:another worksheet, you must prefix the worksheet's name followed by the exclamation point [!], like this:
Sheet1!items_list
Để tham khảo tên cấp bảng tính trong một sổ làm việc khác, bạn cũng nên bao gồm tên sổ làm việc được đặt trong khung vuông:another workbook, you should also include the workbook name enclosed in square brackets:
[Sales.xlsx]Sheet1!items_list
Nếu tên trang tính hoặc tên sổ làm việc chứa khoảng trắng, chúng nên được đặt trong các dấu ngoặc kép đơn:spaces, they should be enclosed in single quotation marks:
'[Bán hàng 2017.xlsx] sheet1'! Items_list
Tên cấp sách bài tập
Một tên cấp sách bài tập được công nhận trong toàn bộ sổ làm việc và bạn có thể đề cập đến nó chỉ bằng tên từ bất kỳ trang tính nào trong cùng một sổ làm việc.
Sử dụng cho một tên cấp sách bài tập trong một sổ làm việc khác, đi trước tên với tên sổ làm việc [bao gồm cả phần mở rộng] theo sau là dấu chấm than:another workbook, precede the name with the workbook name [including the extension] followed by the exclamation point:
Book1.xlsx!items_list
Phạm vi ưu tiên
Một tên xác định phải là duy nhất trong phạm vi của nó. Bạn có thể sử dụng cùng tên trong các phạm vi khác nhau, nhưng điều này có thể tạo ra một xung đột tên. Để ngăn chặn điều này xảy ra, theo mặc định, cấp độ bảng tính được ưu tiên hơn mức sách bài tập.unique within its scope. You can use the same name in different scopes, but this might create a name conflict. To prevent this from happening, by default, the worksheet level takes precedence over the workbook level.
Nếu có một vài phạm vi có tên giống hệt với các phạm vi khác nhau và bạn muốn sử dụng tên cấp sách bài tập, tiền tố tên có tên sổ làm việc như thể bạn đang đề cập đến một tên trong một sổ làm việc khác, ví dụ: Book1.xlsx! Data. Bằng cách này, xung đột tên có thể được ghi đè cho tất cả các bảng tính ngoại trừ tờ đầu tiên, luôn sử dụng tên cấp bảng tính địa phương.
Trình quản lý tên Excel - cách nhanh chóng để chỉnh sửa, xóa và lọc tên
Như tên của nó cho thấy, trình quản lý tên Excel được thiết kế đặc biệt để quản lý tên: thay đổi, lọc hoặc xóa tên hiện có cũng như tạo tên mới.
Có hai cách để truy cập trình quản lý tên trong Excel:
- Trên tab Công thức, trong nhóm Xác định tên, nhấp vào Trình quản lý tênName Manager
- Nhấn phím tắt Ctrl + F3.Ctrl + F3 shortcut.
Dù bằng cách nào, cửa sổ hộp thoại Trình quản lý tên sẽ mở, cho phép bạn xem tất cả các tên trong sổ làm việc hiện tại. Bây giờ, bạn có thể chọn tên bạn muốn làm việc và nhấp vào một trong 3 nút ở đầu cửa sổ để thực hiện hành động tương ứng: chỉnh sửa, xóa hoặc lọc.Name Manager dialog window will open, letting you see all names in the current workbook at a glance. Now, you can select the name you want to work with, and click one of the 3 buttons at the
top of the window to perform the corresponding action: edit, delete or filter.
Cách chỉnh sửa phạm vi được đặt tên trong Excel
Để thay đổi tên Excel hiện có, hãy mở Trình quản lý tên, chọn tên và nhấp vào nút Chỉnh sửa. Điều này sẽ mở hộp thoại Chỉnh sửa tên nơi bạn có thể thay đổi tên và tham chiếu. Phạm vi của tên không thể thay đổi.Edit… button. This will open the Edit Name dialog box where you can change the name and reference. The scope of the name cannot be
changed.
Để chỉnh sửa tham chiếu tên, bạn không cần phải mở hộp thoại Chỉnh sửa tên. Chỉ cần chọn tên quan tâm trong Trình quản lý tên Excel và nhập tham chiếu mới trực tiếp vào hộp tham chiếu hoặc nhấp vào nút ở bên phải và chọn phạm vi mong muốn trên trang tính. Sau khi bạn nhấp vào nút Đóng, Excel sẽ hỏi bạn có muốn lưu các thay đổi không và bạn nhấp vào Có.edit a name reference, you do not need to open the Edit Name dialog box. Just select the name of interest in the Excel Name Manager, and type a new reference directly in the Refers to box, or click the button at the right and
select the desired range on the sheet. After you click the Close button, Excel will ask if you want to save the changes, and you click Yes.
Mẹo. Một nỗ lực để điều hướng thông qua một tham chiếu dài hoặc công thức trong đề cập đến trường với các phím mũi tên rất có thể sẽ dẫn đến một hành vi rất khó chịu. Để di chuyển trong trường này mà không làm gián đoạn tham chiếu, nhấn phím F2 để chuyển từ ENTER sang chế độ chỉnh sửa. An attempt to navigate through a long reference or formula in the Refers to field with the arrow keys will most likely result in a very frustrating behavior. To move within this field without disrupting the reference, press the F2 key to switch from Enter to Edit mode.
Cách lọc tên trong Excel
Nếu bạn có nhiều tên trong một sổ làm việc nhất định, hãy nhấp vào nút Bộ lọc ở góc trên cùng bên phải của cửa sổ Trình quản lý tên Excel để chỉ xem các tên có liên quan tại một thời điểm nhất định. Các bộ lọc sau đây có sẵn:Filter button at the top right corner of the Excel Name Manager window to view only the names relevant at a given time. The following filters are available:
- Tên phạm vi đến bảng tính hoặc sổ làm việc
- Tên có hoặc không có lỗi
- Tên hoặc tên bảng được xác định
Cách xóa phạm vi được đặt tên trong Excel
Để xóa một phạm vi được đặt tên, chọn nó trong trình quản lý tên và nhấp vào nút Xóa ở trên cùng.delete a named range, select it in the Name Manager and click the Delete button at the top.
Để xóa một số tên, nhấp vào tên đầu tiên, sau đó nhấn phím CTRL và giữ nó trong khi nhấp vào các tên khác mà bạn muốn xóa. Sau đó nhấp vào nút Xóa và tất cả các tên được chọn sẽ bị xóa trong một lần.delete several names, click the first name, then press the Ctrl key and hold it while clicking other names you want to remove. Then click the Delete button, and all selected names will be deleted in one go.
Để xóa tất cả các tên được xác định trong sổ làm việc, chọn tên đầu tiên trong danh sách, nhấn và giữ phím Shift, sau đó nhấp vào họ. Phát hành phím Shift và nhấp vào Xóa.delete all defined names in a workbook, select the first name in the list, press and hold the Shift key, and then click the last name. Release the Shift key and click Delete.
Cách xóa các tên đã xác định với lỗi
Nếu bạn có một số tên không hợp lệ có lỗi tham chiếu, hãy nhấp vào nút Filter> Tên có lỗi để lọc chúng:Filter button > Names with Errors to filter them:
Sau đó, chọn tất cả các tên được lọc như được giải thích ở trên [bằng cách sử dụng phím Shift] và nhấp vào nút Xóa.Delete button.
Ghi chú. Nếu bất kỳ tên Excel nào của bạn được sử dụng trong các công thức, hãy chắc chắn cập nhật các công thức trước khi xóa tên, nếu không các công thức của bạn sẽ trả về #Name? lỗi. If any of your Excel names are used in formulas, be sure to update the formulas before deleting names, otherwise your formulas will return #NAME? errors.
5 lợi ích hàng đầu của việc sử dụng tên trong Excel
Cho đến nay trong hướng dẫn này, chúng tôi đã tập trung chủ yếu vào những thứ bao gồm các khía cạnh khác nhau của việc tạo và sử dụng các phạm vi được đặt tên trong Excel. Nhưng bạn có thể tò mò muốn biết điều gì đặc biệt về những cái tên Excel khiến chúng đáng để nỗ lực? Năm lợi thế hàng đầu của việc sử dụng tên được xác định trong Excel theo sau.
1. Tên Excel làm cho các công thức dễ dàng thực hiện và đọc
Bạn không phải nhập các tài liệu tham khảo phức tạp hoặc đi lại và chọn các phạm vi trên trang tính. Chỉ cần bắt đầu gõ tên bạn muốn sử dụng trong công thức và Excel sẽ hiển thị danh sách các tên phù hợp cho bạn để chọn. Nhấp đúp vào tên mong muốn và Excel sẽ chèn nó vào công thức ngay lập tức:
2. Tên Excel cho phép tạo các công thức có thể mở rộng
Bằng cách sử dụng các phạm vi được đặt tên động, bạn có thể tạo công thức "động" tự động bao gồm dữ liệu mới trong tính toán mà không phải cập nhật mọi tham chiếu theo cách thủ công.
3. Tên Excel giúp các công thức dễ dàng sử dụng lại
Tên Excel giúp việc sao chép một công thức vào một tờ khác hoặc chuyển một công thức vào một sổ làm việc khác. Tất cả những gì bạn phải làm là tạo cùng tên trong sổ làm việc đích, sao chép/dán công thức như hiện tại, và bạn sẽ giúp nó hoạt động ngay lập tức.
Mẹo. Để ngăn chặn hình thức Excel tạo tên mới một cách nhanh chóng, hãy sao chép công thức dưới dạng văn bản trong thanh công thức thay vì sao chép ô công thức. To prevent Excel form creating new names on the fly, copy the formula as text in the formula bar instead of copying the formula cell.
4. Phạm vi được đặt tên đơn giản hóa điều hướng
Để nhanh chóng đến một phạm vi cụ thể được đặt tên, chỉ cần nhấp vào tên của nó trong hộp tên. Nếu một phạm vi được đặt tên nằm trên một tờ khác, Excel sẽ tự động đưa bạn đến bảng đó.
Ghi chú. Các phạm vi có tên động không hiển thị trong hộp tên trong Excel. Để xem các phạm vi động, hãy mở Trình quản lý tên Excel [CTRL + F3] hiển thị chi tiết đầy đủ về tất cả các tên trong sổ làm việc, bao gồm phạm vi và tài liệu tham khảo của chúng. Dynamic named ranges do not show up in the Name box in Excel. To see dynamic ranges, open the Excel Name Manager [Ctrl + F3] that shows full details about all names in the workbook, including their scope and references.
5. Phạm vi được đặt tên cho phép tạo danh sách thả xuống động
Để xây dựng một danh sách thả xuống có thể mở rộng và có thể cập nhật, hãy tạo một phạm vi được đặt tên động trước và sau đó tạo một danh sách xác thực dữ liệu dựa trên phạm vi đó. Các hướng dẫn từng bước chi tiết có thể được tìm thấy ở đây: Cách tạo ra một thả xuống động trong Excel.
Excel có tên phạm vi - Mẹo và thủ thuật
Bây giờ bạn đã biết những điều cơ bản của việc tạo và sử dụng tên trong Excel, hãy để tôi chia sẻ thêm một vài mẹo có thể chứng minh hữu ích trong công việc của bạn.
Cách lấy danh sách tất cả các tên trong sổ làm việc
Để có được một danh sách hữu hình hơn của tất cả các tên trong sổ làm việc hiện tại, hãy làm như sau:
- Chọn ô trên cùng của phạm vi nơi bạn muốn tên xuất hiện.
- Chuyển đến tab Công thức> Xác định nhóm Tên, nhấp vào Sử dụng trong các công thức, sau đó nhấp vào Dán tên, hoặc, chỉ cần nhấn phím F3.Paste Names… Or, simply press the F3 key.
- Trong hộp thoại Tên dán, nhấp vào danh sách dán.Paste List.
Điều này sẽ chèn tất cả các tên Excel cùng với các tham chiếu của chúng trong bảng tính hiện tại, bắt đầu trong ô đã chọn.
Tên Excel tuyệt đối so với Tên Excel tương đối
Theo mặc định, tên Excel hoạt động như các tài liệu tham khảo tuyệt đối - bị khóa vào các ô cụ thể. Tuy nhiên, có thể tạo một phạm vi được đặt tên so với vị trí của ô hoạt động tại thời điểm tên được xác định. Tên tương đối hoạt động giống như các tài liệu tham khảo tương đối - được thay đổi khi công thức được di chuyển hoặc sao chép sang một ô khác.relative to the position of the active cell at the time the name is defined. Relative names behave like relative references - get changed when the formula is moved or copied to another cell.
Trên thực tế, tôi không thể nghĩ ra bất kỳ lý do gì tại sao người ta muốn tạo ra một phạm vi tương đối được đặt tên, ngoại trừ có thể khi một phạm vi bao gồm một ô duy nhất. Ví dụ, chúng ta hãy tạo một tên tương đối đề cập đến một ô một cột ở bên trái của ô hiện tại, trong cùng một hàng:
- Chọn ô B1.
- Nhấn Ctrl + F3 để mở Trình quản lý tên Excel và nhấp vào mớiCtrl + F3 to open Excel Name Manager, and click the New…
- Trong hộp tên, nhập tên mong muốn, giả sử, item_left.
- Trong đề cập đến hộp, gõ
=A1. - Bấm OK.
Bây giờ, hãy xem điều gì xảy ra khi chúng ta sử dụng tên item_left trong công thức, ví dụ:
= Sumif [item_list, item_left, sales]
Trong đó các mục_list đề cập đến $ 2 $ 2: $ A $ 10 và bán hàng đề cập đến $ B $ 2: $ B $ 10 trong bảng bên dưới.
Khi bạn nhập công thức trong ô E2, sau đó sao chép nó xuống cột, nó sẽ tính tổng doanh số cho từng sản phẩm vì item_left là tên tương đối và tham chiếu của nó dựa trên vị trí tương đối của cột và hàng trong đó công thức được sao chép:relative name and its reference adjusts based on the relative position of the column and row where the formula is copied:
Cách áp dụng tên Excel cho các công thức hiện có
Nếu bạn đã xác định các phạm vi đã được sử dụng trong các công thức của bạn, Excel sẽ không thay đổi các tham chiếu thành các tên thích hợp tự động. Mặc dù, thay vì thay thế các tài liệu tham khảo bằng tên bằng tay, bạn có thể có Excel thực hiện công việc cho bạn. Đây là cách:
- Chọn một hoặc nhiều ô công thức mà bạn muốn cập nhật.
- Chuyển đến tab Công thức> Xác định nhóm tên và nhấp vào Xác định tên> Áp dụng tênDefine Name > Apply Names…
- Trong hộp thoại Áp dụng Tên, nhấp vào tên bạn muốn áp dụng, sau đó nhấp vào OK. Nếu Excel có thể khớp với bất kỳ tên nào hiện có với các tham chiếu được sử dụng trong các công thức của bạn, các tên sẽ được chọn tự động cho bạn:
Ngoài ra, hai tùy chọn khác có sẵn [được chọn theo mặc định]:
- Bỏ qua tương đối/Tuyệt đối - Giữ hộp này được kiểm tra nếu bạn muốn Excel chỉ áp dụng các tên có cùng loại tham chiếu: Thay thế các tài liệu tham khảo tương đối bằng tên tương đối và tài liệu tham khảo tuyệt đối bằng tên tuyệt đối.
- Sử dụng tên hàng và cột - Nếu được chọn, Excel sẽ đổi tên tất cả các tham chiếu ô có thể được xác định là giao điểm của một hàng có tên và cột được đặt tên. Để biết thêm lựa chọn, nhấp vào các tùy chọn
Phím tắt tên Excel
Như thường lệ trong Excel, các tính năng phổ biến nhất có thể được truy cập theo nhiều cách: thông qua ruy băng, menu nhấp chuột phải và phím tắt. Các phạm vi xuất sắc có tên không phải là ngoại lệ. Dưới đây là ba phím tắt hữu ích để làm việc với tên trong Excel:
- Ctrl + F3 để mở trình quản lý tên Excel. to open the Excel Name Manager.
- Ctrl + Shift + F3 để tạo phạm vi được đặt tên từ lựa chọn. to create named ranges from selection.
- F3 để có được một danh sách tất cả các tên Excel trong sổ làm việc. to get a list of all Excel names in a workbook.
Lỗi tên Excel [#REF và #NAME]
Theo mặc định, Microsoft Excel cố gắng hết sức để giữ cho các tên được xác định của bạn nhất quán và hợp lệ bằng cách điều chỉnh phạm vi tham chiếu tự động khi bạn chèn hoặc xóa các ô trong phạm vi có tên hiện có. Ví dụ: nếu bạn đã tạo một phạm vi được đặt tên cho các ô A1: A10, và sau đó bạn chèn một hàng mới ở bất cứ đâu giữa các hàng 1 và 10, tham chiếu phạm vi sẽ thay đổi thành A1: A11. Tương tự, nếu bạn xóa bất kỳ ô nào giữa A1 và A10, phạm vi được đặt tên của bạn sẽ ký hợp đồng phù hợp.
Tuy nhiên, nếu bạn xóa tất cả các ô tạo nên phạm vi xuất sắc có tên, tên sẽ không hợp lệ và hiển thị #REF! Lỗi trong trình quản lý tên. Lỗi tương tự sẽ hiển thị trong một công thức tham khảo tên đó:delete all cells that make up an Excel named range, the name becomes
invalid and displays a #REF! error in the Name Manager. The same error will show up in a formula referencing that name:
Nếu một công thức đề cập đến một tên không tồn tại [bị nhầm hoặc xóa], #Name? Lỗi sẽ hiển thị. Trong cả hai trường hợp, hãy mở trình quản lý tên Excel và kiểm tra tính hợp lệ của các tên đã xác định của bạn [cách nhanh nhất là lọc tên với lỗi].non-existing name [mistyped or deleted], the #NAME? error will show up. In either case, open the Excel Name Manager and check the validity of your defined names [the fastest way is to filter names with errors].
Đây là cách bạn tạo và sử dụng tên trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!