2 kỹ thuật đặt tên vùng dữ liệu trong Excel cực hay

Bài viết này sẽ hướng dẫn các bạn 2 kỹ thuật đặt tên vùng dữ liệu trong Excel cực hay là đặt tên nhanh với tiêu đề cột và đặt tên vùng tự động co dãn theo nội dung. Hãy cùng chúng mình tìm hiểu trong bài viết dưới đây ngay nhé.

Tuyệt đỉnh Excel: Trở thành bậc thầy Excel sau 16 giờ

2 kỹ thuật đặt tên vùng dữ liệu trong Excel

Kỹ thuật đặt tên nhanh với tên tại dòng tiêu đề

Trong thẻ Formulas, các bạn tìm đến nhóm công cụ Defined Names sẽ thấy được mục Create form Selection như trong hình ảnh dưới đây:

Đây chính là công cụ mà chúng ta sử dụng để đặt tên vùng dữ liệu nhanh dựa theo dòng tiêu đề của bảng. Cách này áp dụng trong các trường hợp chúng ta muốn đặt tên nhanh thay vì phải đặt tên cho từng cột trong bảng dữ liệu. Khi đó chúng ta có thể làm việc với cả một bảng dữ liệu chỉ dựa trên tên tiêu đề của bảng.

Ví dụ: Cho bảng dữ liệu như sau:

Để đặt tên nhanh theo tiêu đề của các cột thì đầu tiên các bạn chọn toàn bộ vùng bảng. Sau đó các bạn vào thẻ Formulas > chọn mục Create form Selection. Tổ hợp phím tắt Excel dể thực hiện thao tác này là Ctrl + Shift + F3.

Sau đó sẽ có hộp thoại Create form Selection hiện lên với 4 tùy chọn là:

• Top row: Dòng đầu tiên bên trên
• Left row: Cột đầu tiên bên trái
• Bottom row: Dòng dưới cùng của bảng
• Right column: Cột cuối cùng bên phải của bảng

Ở đây chúng ta sẽ chọn mục Top Row vì tiêu đề của bảng nằm ở dòng đầu tiên bên trên:

Sau khi chọn xong thì bấm OK để lưu lại thiết lập.

Để kiểm tra lại xem tên được đặt theo cột này đã đúng hay chưa thì các bạn có thể vào thẻ Formulas > chọn mục Name Manager là sẽ thấy tên của các vùng.

Tại đây các bạn có thể đổi tên, chỉnh sửa vùng tham chiếu hoặc xóa 

Khi một cột đã có tên thì bạn có thể nhập tên đó vào công thức mà không cần viết lại vùng tham chiếu. Ví dụ chúng mình muốn tính tổng cột Amount thì công thức sẽ là: =SUM(Amount).

Cách làm rất tiện lợi nếu bạn có một bảng dữ liệu lớn mà không muốn phải thao tác để đặt tên cho từng cột. Tuy nhiên, cách này có một nhược điểm là tên của cột không được viết tiếng Việt có dấu và không được chứa dấu cách vì theo quy tắc đặt tên vùng dữ liệu trong Excel thì viết tên theo cách này là không hợp lệ. Do đó các bạn mà muốn đặt tên nhanh cho vùng dữ liệu theo cách này thì phần tiêu đề nên được viết bằng tiếng Anh hoặc tiếng Việt không dấu và sử dụng dấu gạch dưới để thay thế cho dấu cách.

Kỹ thuật đặt tên vùng tự động co dãn theo nội dung

Khi chúng ta đặt tên cho một vùng trong Excel thì tên đó được cố định cho vùng tham chiếu ban đầu. Thông thường, khi muốn mở rộng vùng tham chiếu thì các bạn phải thao tác thủ công.

Ví dụ: Ban đầu bạn đặt tên cho cột A có dữ liệu trong vùng A1 đến A16. Sau đó bạn thêm dữ liệu vào cột từ ô A17 trở đi thì các ô này sẽ không nằm trong vùng dữ liệu đã được đặt tên của cột A.

Nếu muốn tọa độ vùng tham chiếu tự động mở rộng khi chúng ta thêm nội dung thì các bạn sử dụng hàm để xác định vùng tham chiếu khi đặt tên vùng trong Excel.

Ví dụ:

Cho bảng dữ liệu ban đầu với cột Amount đã được đặt tên theo tiêu đề và vùng tham chiếu là từ E11 đến E16:

Bây giờ, để các dữ liệu được điền vào từ dòng 17 trở đi của cột E sẽ được cập nhật tự động vào trong vùng tham chiếu của tên vùng dữ liệu Amount thì chúng ta sẽ dùng hàm OFFSET theo công thức như sau:

= OFFSET($E$11,0,0,COUNTA($E$11:$E$200))

Ý nghĩa của công thức này là: Lấy tọa độ điểm bắt đầu là E11 rồi đếm xem vùng E11:E200 có độ rộng là bao nhiêu ô bằng hàm COUNTA. 

Sau khi viết công thức bên ngoài rồi thì các bạn copy công thức hàm rồi vào thẻ Fomulas > chọn mục Name Manager > chọn vào tên vùng dữ liệu Amount > xóa vùng tham chiếu ban đầu ở mục Refers to rồi điền công thức hàm đã copy vào ô đó.

Để kiểm tra xem vùng tham chiếu của vùng dữ liệu đang được đặt tên là Amount dài đến đâu thì các bạn bấm vào nút mũi tên nằm bên phải ô điền công thức của mục Refers to. Lúc này Excel sẽ hiển thị đường nét đứt bao quanh vùng dữ liệu đang được tham chiếu như trong hình ảnh sau:

Nếu các bạn viết thêm các dòng dưới nữa thì vùng tham chiếu vẫn được tự động mở rộng như sau:

Nếu các bạn đang thao tác trên một bảng lớn hơn nữa thì trong công thức hàm OFFSET các bạn có thể cho đếm tới con số hàng nghìn cũng được nhé.

Xem thêm: Cách sử dụng Name Range trong Excel để quản lý dữ liệu theo tên

Kết luận

Hy vọng với 2 kỹ thuật đặt tên vùng dữ liệu trong Excel mà chúng mình giới thiệu trong bài viết này thì các bạn có thể làm việc nhanh chóng và hiệu quả hơn trên Excel. 

Chúc bạn luôn học tập hiệu quả!