Phạm vi có thể được đặt tên trong excel?
|
Một bảng tính điển hình có thể có nhiều công thức khác nhau và có thể khó theo dõi tất cả các phạm vi ô được sử dụng bởi các công thức đó. Để làm cho mọi thứ dễ dàng hơn, bạn có thể thêm một tên mô tả, trực quan hơn vào bất kỳ phạm vi ô nào, rồi sử dụng các phạm vi đã đặt tên đó trong công thức của bạn Show Để tạo một phạm vi được đặt tên, chỉ cần chọn một ô hoặc phạm vi ô, sau đó nhập tên mong muốn vào hộp Tên (ở bên trái của thanh công thức). Sau đó, bạn có thể sử dụng tên mới bất cứ khi nào bạn muốn tham chiếu đến phạm vi ô đó Trình quản lý tên có thể làm cho bảng tính dễ theo dõi hơn nhưng người lập mô hình Excel phải biết cách — và quan trọng hơn là khi nào — để sử dụng công cụ đúng cách. Bởi Liam Bastick, FCMA, CGMA
Nếu bạn hỏi các chuyên gia lập mô hình về giá trị của việc sử dụng tên dãy, bạn sẽ thấy rằng ý kiến đó rất khác nhau. Trong bảng tính, nếu được sử dụng một cách hợp lý và tiết kiệm, tên dải ô có thể mang lại giá trị lớn vì chúng có thể giúp công thức dễ đọc hơn. Trong các macro (không được thảo luận ở đây), chúng rất quan trọng. Mặt khác, việc sử dụng quá mức có thể dẫn đến sự nhầm lẫn của người dùng cuối Tên phạm vi là tên được sử dụng để chỉ tham chiếu ô, kết quả công thức hoặc giá trị. Chúng thường được sử dụng để tránh các giá trị được mã hóa cứng xuất hiện trong công thức và để làm cho công thức nói chung rõ ràng hơn. Chúng được lưu trữ trong cái được gọi là Trình quản lý tên trong Excel
Lưu ý phần thứ hai (Phạm vi). Tất cả các tên đều có phạm vi, đối với một trang tính cụ thể (còn được gọi là cấp trang tính cục bộ) hoặc toàn bộ sổ làm việc (còn được gọi là cấp sổ làm việc chung). Phạm vi của tên là vị trí trong đó tên được công nhận mà không cần trình độ Ví dụ: nếu bạn đã xác định tên dải ô là “Lợi nhuận” với phạm vi là Trang tính 1, thay vì Sổ làm việc, thì nó sẽ chỉ được công nhận là “Lợi nhuận” trong Trang tính 1 (nghĩa là không có tiêu chuẩn) Để sử dụng tên cục bộ này trong một trang tính khác, bạn phải đủ điều kiện cho nó bằng cách thêm tên trang tính đã bản địa hóa vào trước nó =Tờ1. Lợi nhuận Nếu bạn đã xác định một tên, chẳng hạn như “Dòng tiền”, và phạm vi của nó là sổ làm việc, thì tên đó được nhận dạng cho tất cả các trang tính trong sổ làm việc đó (nhưng không phải cho bất kỳ sổ làm việc nào khác). Nếu phạm vi là một trang tính (giả sử, Trang tính 1), thì tên phạm vi sẽ là “Trang tính 1. Dòng tiền” thay vào đó. Do đó, phạm vi sổ làm việc giúp đặt tên phạm vi rõ ràng hơn và tránh nhầm lẫn Tên phải luôn là duy nhất trong phạm vi của nó. Excel ngăn bạn xác định tên không phải là duy nhất trong phạm vi của nó. Tuy nhiên, bạn có thể sử dụng cùng một tên với các phạm vi khác nhau. Ví dụ: bạn có thể xác định tên, chẳng hạn như “Lợi nhuận”, nằm trong phạm vi Trang tính 1, Trang tính 2 và Trang tính 3 trong cùng một sổ làm việc. Mặc dù mỗi tên đều giống nhau, nhưng mỗi tên là duy nhất trong phạm vi của nó. Bạn có thể làm điều này để đảm bảo rằng một công thức sử dụng tên “Lợi nhuận gộp”, chẳng hạn, luôn tham chiếu đến cùng các ô ở cấp trang tính cục bộ Bạn thậm chí có thể xác định cùng một tên, chẳng hạn như “Lợi nhuận”, cho cấp sổ làm việc toàn cầu, nhưng một lần nữa phạm vi này là duy nhất. Trong trường hợp này, có thể có xung đột tên. Để giải quyết xung đột này, Excel sử dụng tên được xác định cho trang tính theo mặc định. Cấp độ trang tính cục bộ được ưu tiên hơn cấp độ sổ làm việc toàn cầu. Điều này có thể tránh được bằng cách thêm tiền tố vào tên, ví dụ: đổi tên thành “WorkbookFile_Profit” thay vào đó Có thể ghi đè mức trang tính cục bộ cho tất cả các trang tính trong sổ làm việc, ngoại trừ trang tính đầu tiên. Điều này sẽ luôn sử dụng tên cục bộ nếu có xung đột tên và không thể ghi đè Chúng tôi thực sự khuyên bạn nên luôn cố gắng chỉ tạo tên dải ô ở cấp độ phạm vi sổ làm việc. Ngoài ra, nếu có thể, hãy tránh sử dụng tên dải ô trong (các phần của) trang tính sẽ được sao chép sang các trang tính hoặc sổ làm việc khác. Bạn sẽ nhầm lẫn Excel, người dùng cuối và quan trọng nhất là chính bạn Chăm sóc với tên Chuỗi tên phải bắt đầu bằng một chữ cái hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là chữ cái, số, dấu chấm và ký tự gạch dưới. Khoảng cách không được phép, nhưng hai từ có thể được nối với nhau bằng dấu gạch dưới (_) hoặc dấu chấm (. ). Ví dụ: để nhập tên “Dòng tiền”, bạn nên nhập “Cash_Flow” hoặc “Cash. Lưu lượng" Bạn không thể sử dụng tên có thể bị nhầm lẫn làm tham chiếu ô; . ) Dường như không có giới hạn về số lượng tên mà bạn có thể xác định, nhưng một tên có thể chứa không quá 255 ký tự. Tên có thể chứa chữ hoa và chữ thường, nhưng Excel không phân biệt chữ hoa và chữ thường trong tên. Ví dụ: nếu bạn đã tạo tên chung là “Profit” và sau đó tạo một tên chung khác là “PROFIT” trong cùng một sổ làm việc, thì tên thứ hai sẽ bị từ chối vì tên cần phải là duy nhất, bất kể cách viết hoa Đây không phải là vấn đề về cú pháp, nhưng tôi thực sự khuyên bạn nên nghĩ đến việc thêm tiền tố vào tên dải ô. Những độc giả thông thường sẽ lưu ý rằng tên phạm vi danh sách của tôi luôn bắt đầu bằng “LU_” trong đó “LU” là viết tắt của “Tra cứu”. Tương tự, tôi sử dụng “BC_” cho “Ô cơ sở” khi làm việc với hàm OFFSET Bằng cách sử dụng các tiền tố này, tôi hiểu mục đích của tên dãy và các tên có mục đích chung được nhóm lại với nhau trong một danh sách. Điều này không có nghĩa là tất cả các tên phạm vi phải chứa tiền tố. Ví dụ: “Tax_Rate” tự nó có ý nghĩa và việc thêm tiền tố sẽ chỉ làm mất giá trị của tên đã cho, có khả năng gây nhầm lẫn cho người dùng cuối Tạo tên dãy nhanh chóng Có một lối tắt tiện lợi để tạo tên dãy bằng tên hiện có. Hãy xem xét danh sách sau đây
Lý do hộp thoại này sử dụng các hộp kiểm (chứ không phải các nút tùy chọn) là để cho phép người dùng chọn nhiều hộp cùng một lúc. Ví dụ
Ví dụ này cũng minh họa tại sao dấu cách là ký tự không hợp lệ trong tên dải ô (và cũng không được thêm vào công thức). Space là toán tử giao nhau trong Excel. Nếu bạn gõ công thức sau =Gross_Margin Tháng 2, Excel sẽ trả về giá trị trong ô P34 (giao điểm của hai phạm vi, ở trên), tức là $4,183. Đây có thể là một công cụ phân tích mạnh mẽ nhưng nhanh chóng và đơn giản cho các đầu ra chính — nhanh hơn VLOOKUP hoặc INDEX MATCH Sử dụng tên dãy một cách nhanh chóng Một trong những lý do tôi thích sử dụng phím tắt Ctrl+F3 là vì nó là một phần của nhóm phím tắt F3 Names. Chúng ta vừa thấy Ctrl+Shift+F3 có thể hữu ích như thế nào — và F3 cũng vậy Có lẽ thay thế bởi thực tế là trong Excel 2007 và các phiên bản mới hơn, Excel hiện sẽ nhắc khi bạn nhập công thức, F3 trước đây rất hữu ích dưới dạng phím tắt Dán Tên. Ví dụ: khi bạn nhập công thức, bạn có thể tham khảo tên dải ô bằng cách chỉ cần nhập F3 để nhận hộp thoại Dán tên, như minh họa trong ảnh chụp màn hình bên dưới
Tuy nhiên, nhìn kỹ hơn vào hộp thoại. Nút Dán danh sách ở góc dưới cùng bên trái, nếu được nhấn xuống, sẽ dán danh sách và định nghĩa của chúng vào một dải ô được chọn trước (nghĩa là trước khi bạn sử dụng phím tắt F3) trong trang tính Excel, điều này có thể hữu ích cho việc kiểm tra mô hình Đôi khi, các công thức đã được viết trước khi tên dải ô được tạo. Trong một số trường hợp, có thể áp dụng hồi tố các tên này bằng cách sử dụng Áp dụng tên trong nhóm Xác định tên của tab Công thức (xem ảnh chụp màn hình bên dưới)
Nếu tôi chỉ được trả 1 đô la cho mỗi lần tôi được hỏi cách xóa tên dải ô, có lẽ tôi đã nghỉ hưu ngay bây giờ. Điều này chủ yếu là do menu phản trực giác trong Excel 2003 và các phiên bản trước đó
Tham chiếu tương đối Theo mặc định, tên dãy được tham chiếu tuyệt đối (nghĩa là chứa dấu $ để các tham chiếu tĩnh). Tuy nhiên, hãy tưởng tượng một kịch bản trong đó bạn đang lập mô hình doanh thu và bạn muốn tăng giá trị của giai đoạn trước theo lạm phát (đã được đặt tên dải ô, chẳng hạn như ô C3 trên Trang tính 1). Đơn giản chỉ cần nhấp vào bất kỳ ô nào (ví dụ: tôi sẽ sử dụng D17 tùy ý), sau đó xác định tên phạm vi mới như sau
=Prior_Period*(1+Lạm phát) trên hàng Các loại tên khác Như tôi đã nói trước đó, tên cũng có thể đề cập đến hàm, ngày tháng và hằng số — những cái sau có thể hữu ích (ví dụ: “Months_in_Year” được định nghĩa là 12) để tránh chèn mã cứng vào công thức Nói theo cách khôn ngoan Bài viết này chỉ thảo luận về phần nổi của tảng băng tên. Thử nghiệm có thể trả cổ tức lớn. Tuy nhiên, mục đích không phải là đi quá đà, vì quá nhiều tên trong sổ làm việc thực sự có thể khiến các công thức — và do đó, mô hình của bạn — trở nên khó theo dõi hơn Hơn nữa, hãy cẩn thận nếu bạn đặt tên cho các phạm vi mà sau đó sẽ bị xóa. Tên phạm vi sẽ không bị xóa (mặc dù chúng sẽ không còn xuất hiện trong Hộp tên). Chúng sẽ cần được xóa như mô tả ở trên để tránh các lỗi tiềm ẩn trong công thức, v.v. Điều quan trọng cần nhớ là điều này thường là nguyên nhân khiến tệp bị hỏng hoặc lỗi trong Excel — Liam Bastick, FCMA, CGMA, FCA, là giám đốc của SumProduct, một công ty tư vấn toàn cầu chuyên về đào tạo Excel. Ông cũng là MVP của Excel (do Microsoft bổ nhiệm) và là tác giả của cuốn sách Giới thiệu về lập mô hình tài chính. Gửi ý tưởng cho các bài viết liên quan đến Excel trong tương lai cho anh ấy tại Liam. bastick@sumproduct. com. Để nhận xét về bài viết này hoặc đề xuất ý tưởng cho một bài báo khác, hãy liên hệ với Jeff Drew, biên tập viên cấp cao của tạp chí FM, tại Jeff. Drew@aicpa-cima. com Một phạm vi có thể được đặt tên trong hộp tên?Nếu tên đề cập đến một phạm vi, bạn có thể chọn tên đó trong danh sách thả xuống Hộp tên, để chọn phạm vi đã đặt tên trên trang tính
Điều gì không được phép trong tên của phạm vi trong Excel?Bạn sẽ nhầm lẫn Excel, người dùng cuối và quan trọng nhất là chính bạn. Chuỗi tên phải bắt đầu bằng một chữ cái hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là chữ cái, số, dấu chấm và ký tự gạch dưới. Không được phép sử dụng dấu cách nhưng hai từ có thể được nối với nhau bằng dấu gạch dưới (_) hoặc dấu chấm (. ).
Những gì không được phép trong việc đặt tên cho phạm vi?Tên không được giống với tham chiếu ô, chẳng hạn như Z$100, BIN9 hoặc R1C1. Tên có thể chứa tối đa 255 ký tự. Nếu tên được xác định cho một phạm vi chứa hơn 253 ký tự, bạn không thể chọn tên đó từ hộp Tên nhưng bạn vẫn có thể sử dụng tên đó trong công thức
Phạm vi được đặt tên ở đâu trong Excel?Bạn có thể tìm một dải ô đã đặt tên bằng cách sử dụng tính năng Đi tới—tính năng này điều hướng đến bất kỳ dải ô đã đặt tên nào trong toàn bộ sổ làm việc. Bạn có thể tìm một dải ô đã đặt tên bằng cách đi tới tab Trang chủ, nhấp vào Tìm & Chọn, rồi nhấp vào Đi tới . Hoặc, nhấn Ctrl + G trên bàn phím của bạn. Trong hộp Đi tới, bấm đúp vào phạm vi đã đặt tên mà bạn muốn tìm. |
