Ngừng đặt tên phạm vi thủ công trong Excel: Có một cách nhanh hơn nhiều

Các phạm vi được đặt tên là bí quyết để sổ làm việc Excel tự ghi chú, nhưng việc đặt tên cho các ô thủ công là một công việc tẻ nhạt. Bằng cách sử dụng một thủ thuật chọn đơn giản, bạn có thể ngay lập tức ánh xạ các tiêu đề của mình với dữ liệu, làm cho công thức của bạn bền hơn, dễ kiểm tra hơn và chuyên nghiệp hơn nhiều.

Các tham chiếu ô thủ công là một gánh nặng

Các công thức Excel tiêu chuẩn như:

=B2*C15

là cơn ác mộng về kiểm toán. Khi bạn hoặc đồng nghiệp xem lại một bảng tính sau sáu tháng, bạn không nên phải lùng sục các tọa độ ô chỉ để hiểu phép tính của chính mình. Các phạm vi được đặt tên giải quyết vấn đề này bằng cách biến các tham chiếu khó hiểu thành ngôn ngữ con người, chẳng hạn như:

=Unit_Price*Tax_Rate

Vấn đề? Hầu hết mọi người bỏ qua điều này vì họ nghĩ rằng họ phải định nghĩa từng tên một cách thủ công trong Trình quản lý Tên hoặc hộp tên. Sự mệt mỏi khi đặt tên này thường dẫn mọi người trực tiếp trở lại với các tham chiếu ô lộn xộn.

Tuy nhiên, bạn không cần phải chọn giữa lao động thủ công và toán học lộn xộn. Công cụ Tạo từ Lựa chọn của Excel hoạt động như một chế độ tự động cho kiến trúc bảng tính của bạn, cho phép bạn định nghĩa hàng trăm tên chức năng chỉ trong vài cú nhấp chuột.

Bạn có thể tạo hàng trăm tên trong vài giây

Để sử dụng công cụ Tạo từ Chọn một cách hiệu quả, bạn phải đảm bảo rằng nhãn của bạn nằm trực tiếp sát với dữ liệu. Excel rất thông minh, nhưng nó không thể nhảy qua các hàng hoặc cột trống để tìm tiêu đề. Phép màu xảy ra khi bạn chọn toàn bộ phạm vi bao gồm cả tiêu đề và giá trị.

Một tình huống thực tế

Hãy tưởng tượng bạn có một bảng dữ liệu doanh số. Các ô B1:E1 chứa các vùng, các ô A2:A13 chứa các tháng, và dữ liệu tài chính điền vào các ô ở giữa.

Suy nghĩ trước, bạn biết rằng bạn sẽ cần tạo các báo cáo tóm tắt với tổng doanh số theo khu vực và theo tháng. Không sử dụng phạm vi được đặt tên, để tóm tắt doanh số cho tháng Một, bạn sẽ cần gõ:

=SUM(B2:E2)

Tương tự, đối với khu vực phía Bắc, bạn sẽ nhập:

=SUM(B2:B13)

Mặc dù các công thức này hoạt động, nhưng chúng dễ mắc lỗi và khó kiểm tra chỉ bằng cái nhìn thoáng qua. Thay vào đó, bằng cách đặt tên cho các phạm vi này, bạn có thể sử dụng ngôn ngữ tự nhiên.

Quy trình từng bước

Để sử dụng công cụ Tạo từ lựa chọn, trước tiên, hãy chọn toàn bộ phạm vi dữ liệu của bạn (A1:E13), bao gồm cả tiêu đề hàng và cột. Sau đó, trong tab Công thức, nhấp vào "Tạo từ lựa chọn" (hoặc nhấn Ctrl+Shift+F3).

Nếu, như trong ví dụ của tôi, các tiêu đề cột của bạn nằm ở hàng trên cùng và tiêu đề hàng nằm ở cột ngoài cùng bên trái, hãy đánh dấu vào "Hàng trên cùng" và "Cột bên trái", sau đó nhấp vào "OK."

Excel ngay lập tức ánh xạ các nhãn đó tới các ô tương ứng. Bạn có thể xác minh kết quả bằng cách mở rộng menu thả xuống của hộp tên hoặc mở "Trình quản lý Tên" trong tab Công thức để xem danh sách nhãn chức năng, dễ đọc mới của bạn.

Bây giờ, để sử dụng tên của bạn, hãy gõ một công thức sử dụng nhãn mới của bạn thay vì tọa độ. Ví dụ:

=SUM(North)

Excel ngay lập tức chọn phạm vi tương ứng, xác nhận rằng sổ làm việc của bạn hiện đang sử dụng ngôn ngữ của bạn.

Còn tốt hơn, nếu bạn gõ =SUM( và bắt đầu gõ một tên như No..., IntelliSense của Excel sẽ tự động hoàn thành "North," giúp viết công thức nhanh hơn.

Khi bạn nhấn Enter, tất cả các giá trị trong phạm vi được đặt tên đó sẽ được tính tổng.

Làm sạch nhãn của bạn và cập nhật các công thức cũ

Excel khá kén về quy tắc đặt tên. Tên không được chứa khoảng trắng và thường không được bắt đầu bằng số. May mắn thay, Create from Selection thực hiện phần việc nặng nề: nó tự động thay thế khoảng trắng trong tiêu đề của bạn bằng dấu gạch dưới. Ví dụ, "Retail Price" tự động được chuyển thành "Retail_Price" trong nền.

Vấn đề lớn nhất đối với nhiều người sử dụng công cụ này là việc tạo tên không tự động cập nhật các công thức mà bạn đã viết. Ví dụ, nếu bạn gõ:

=SUM(B2:E2)*12

Trước khi đặt tên cho phạm vi B2:E2 là "North," công thức vẫn bị giữ với tham chiếu ô cũ. Tuy nhiên, bạn không cần phải viết lại chúng. Thay vào đó, sau khi bạn đã đặt tên cho phạm vi, trong tab Formulas, nhấp vào mũi tên xuống bên cạnh "Define Name," và chọn "Apply Names."

Sau đó, trong hộp thoại Áp dụng Tên, tất cả các tên được chọn tự động theo mặc định, vì vậy khi bạn nhấp vào "OK," Excel sẽ quét sổ làm việc của bạn và thay thế các tọa độ thông thường bằng các nhãn mới sáng bóng của bạn.

Cuối cùng, để tận dụng tối đa công cụ này, hãy chuyển phạm vi dữ liệu của bạn thành một bảng Excel chính thức (Ctrl+T) trước khi sử dụng. Khi dữ liệu của bạn được định dạng dưới dạng bảng, các tên sẽ tham chiếu đến các cột dữ liệu của bảng, các cột này sẽ tự động mở rộng khi bảng phát triển. Nói cách khác, nếu bạn thêm một hàng mới cho tháng Một năm tới, phạm vi có tên "North" của bạn sẽ tự động mở rộng để bao gồm điểm dữ liệu mới, giữ cho các báo cáo tổng hợp của bạn hoàn toàn chính xác mà không cần bất kỳ điều chỉnh thủ công nào.

Khắc phục những sự cố phổ biến

Ngay cả những công cụ tự động tốt nhất cũng có thể gặp sự cố. Nếu Excel hiển thị cảnh báo hoặc tên của bạn không xuất hiện như mong đợi, hãy kiểm tra bảng này để có cách sửa nhanh.

Khi các công thức của bạn đọc giống như các câu tiếng Anh thay vì một bản đồ tọa độ, bạn gần như loại bỏ hoàn toàn rủi ro 'gõ nhầm' một tham chiếu. Cách tiếp cận này là bước đầu tiên hướng tới một quy trình làm việc chuyên nghiệp, cho phép bạn kiểm tra đúng cách bảng tính của mình để tìm lỗi bằng các công cụ tích hợp sẵn mà phát huy hiệu quả trên kiến trúc sạch sẽ mà bạn vừa xây dựng.