Top 7 # Cách Đặt Tên Vùng Trong Excel Xem Nhiều Nhất, Mới Nhất 2/2023 # Top Trend

Bạn đã bao giờ nghe về việc đặt tên cho ô hoặc 1 vùng dữ liệu trong Excel chưa. Trong Excel, nhà phát hành đã tích hợp tính năng này. Đối với những bảng tính dài, phức tạp, quy mô lớn thì nó rất tiện lợi trong việc bạn cần ghi nhớ hoặc truy cập nhanh đến 01 ô hoặc vùng dữ liệu nào đó. Và nó cũng rất tiện lợi khi bạn cần tham chiếu để tính toán nhiều lần đối với 01 vùng dữ liệu.

Để bạn dễ hiểu hơn thì mình sẽ thực hành với ví dụ sau. Giả sử mình có 1 bảng lương như hình dưới. Hiện tại là có 3 cột chưa được tính toán là BHXH, BHYT và Thực lĩnh. 03 cột này đều phải lấy dữ liệu từ cột Tổng lương để tính toán. 

Cách đặt tên cho 01 cột

Bước 1: Bôi đen cột cần đặt tên, chọn tab Formulas rồi chọn Define Name

Bước 2: 

– Tại ô Name: Bạn đặt tên cho vùng dữ liệu.

– Scope: Chọn phạm vi để thao tác. Ví dụ mình chỉ cần ứng dụng đối với Sheet1.

Bạn cũng có thể gõ = rồi gõ tên vùng dữ liệu cũng được. 

Ví dụ =tongluong rồi gõ công thức tính toán như thông thường.

– Sau khi chọn thì cột Tổng lương sẽ được tham chiếu để tính toán giống như bạn thao tác bằng chuột vậy. 

– Thao tác tương tự với cột BHYT và Thực lĩnh ta được kết quả như sau:

Cách xóa tên cho vùng dữ liệu

Bước 1: Trong tab Formulas, bạn chọn Name Manager

Cách đặt tên cho nhiều cột, nhiều hàng

Bước 1: Bôi đen vùng dữ liệu, chọn tab Formulas rồi chọn Create from Selection.

Bước 2: Chon dòng hoặc cột làm tiêu đề.

– Top row: Hàng trên cùng

– Left column: Cột bên trái ngoài cùng

– Bottom row: Hàng dưới cùng

– Right column: Cột bên phải ngoài cùng

– Trong phần Use in Formula, ban sẽ thấy các cột đã được tự động đặt tên.

Hy vọng thủ thuật này hữu ích đối với các bạn.

SHARE THIS

Xin chào các bạn,

Khi muốn tham chiếu đến một vùng dữ liệu trong Excel bạn thường đặt tên cho nó.

Ví dụ:

Tôi có một danh sách tên alias của các thành viên trong team như sau:

Danh sách này hiện nay cố định trong vùng $A$5: $A$19

Để đặt tên cho vùng này tôi chọn vùng rồi nhấn chọn vào vùng đặt tên gõ tên vùng là “Danhsach”.

Công việc này đã giúp tôi định nghĩa nhanh vùng dữ liệu $A$5: $A$19 có tên gọi là Danhsach.

Sau này khi cần tham chiếu đến vùng dữ liệu $A$5: $A$19 thì tôi chỉ việc gõ chữ “Danhsach” là xong.

 

 

 

 

 

 

 

 

Để kiểm tra lại tôi vào thanh tab Formulas chọn chức năng Name Manager

Lưu ý là tên vùng Danhsach được gán với vùng =Sheet1!$A$5:$A$19 cố định

Điều này có nghĩa là khi bạn thêm một tên mới trong vùng dữ liệu này thì Danhsach sẽ không được cập nhật.

Cách giải quyết:

Thay vì đặt vùng tham chiếu là cố định như trên bạn thay vào đó là hàm OFFSET, hàm này có cấu trúc như sau:

=OFFSET(Reference, rows, cols, [height], [width])

Reference: là vị trí tham chiếu. Trong trường hợp này ta chọn cell A5 (vì danh sách sẽ bắt đầu từ đây)

Rows: vị trí dòng bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo dòng thì bỏ trống giá trị này, nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển xuống 2 dòng nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển lên 2 dòng nữa)

Cols: vị trí của cột bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo cột thì bỏ trống giá trị này. Nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển sang phải 2 cột nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển sang trái 2 cột nữa)

Height: chiều cao của vùng dữ liệu sẽ lấy kể từ vị trí tham chiếu.

Width: chiều rộng của vùng dữ liệu kể từ vị trí tham chiếu

Như vậy trong hàm này, 2 đối số quan trọng nhất là Height và Width. Tôi sẽ làm như sau để các bạn tham khảo

Height: tôi sẽ đưa công thức này vào COUNTA(A:A). hàm COUNTA cho phép đếm tất cả các cell trong cột A có chứa dữ liệu và sẽ trả về giá trị số nguyên.

Width: giá trị số nguyên biểu diễn số lượng cột cần lấy. Nếu theo trường hợp của bài này thì bạn có thể bỏ qua hoặc gõ số 1

Vậy công thức cuối cùng cho hàm OFFSET sẽ như sau:

=OFFSET(Sheet1!$A$5,,,COUNTA(Sheet1!$A:$A))

Trong trường hợp bạn muốn mở rộng vùng tham chiếu cho cột thì cũng chỉ cần bổ sung vào hàm OFFSET thông số cuối cùng cho width như sau:

=OFFSET($A$4,,,COUNTA(Sheet1!$A:$A),COUNTA($4:$4))

Hy vọng thủ thuật nhỏ này sẽ giúp ích nhiều cho các bạn trong công việc

Nguyễn Thế Đông

Share this:

Chia sẻ

Facebook

Like this:

Số lượt thích

Đang tải…

Hôm nay tôi hướng dẫn bạn cách đặt tên cho vùng dữ liệu trong excel- Define Name

Đối với excel 2007 hoặc cao hơn. chúng ta vào tab fomulas trên thanh ribbon.

Cách 2: Là nhấp chuột vào nút define name, hộp thoại New Name sẽ xuất hiện như sau:

Tips: Tốt nhất là chọn workbook để sử dụng globally luôn.

►Trường Comment: Là thông tin ghi chú thêm cho tên bạn đặt, cái này có thể bỏ qua►Trường Refers to: Đây là vùng dữ liệu nguồn cho tên. Bạn nhấp chuột vào mũi tên màu đỏ để chọn vùng dữ liệu nguồn. ở đây tôi chọn Source là Sheet1!$M$4

Sau khi đặt tên xong thì trong hộp thoại Name Manager sẽ hiện ra tên bạn vừa đặt. Đây là nơi quản lý các tên mà đa phần người dùng đặt trong workbook đó, do đó nếu tên bị lỗi, hay muốn sửa lại dữ liệu nguồn (Data source) thì nhấp chuột vào tên bạn muốn chỉnh sửa rồi xuống ô Refers To để chọn lại.

Lưu ý: Bạn có thể chọn data source trước khi đặt tên cũng được bằng cách bôi đen vùng dữ liệu làm data source sau đó thực hiện lại các bước bên trên

Ứng dụng của Define Name:

Với các tên đã đặt trong workbook bạn có thể dùng chúng để lồng vào các tham số Reference của các hàm tham chiếu như Hàm Index, Hàm Vlookup, Hàm Hlookup, Thậm chí cả trong chức năng Data Validation chúng ta cũng dùng Define Name để điền vào trong trường Data Source. Bạn có thể tham khảo cách dùng của nó trong bài viết Data Validation Nâng cao

Ví dụ: ta có hàm Index như sau:

=INDEX((A1:C6,A8:C11),2,2,2)Nếu ta đặt tên cho vùng A1:C6 trong sheet1 là Vungthamchieu1 (Vungthamchieu1=Sheet1!$A$1:$C$6)đặt tên cho vùng A8:C11 trong sheet1 là Vungthamchieu2 (Vungthamchieu2=Sheet1!$A$8:$C$11)

Thì ta có thể lồng vào tham số reference của hàm Index như sau:=INDEX((Vungthamchieu1,Vungthamchieu2),2,2,2)

Do đó chúng ta phải sửa chữa lại thì mới có thể dùng chúng trong các công thức của chúng ta.

Nếu chúng ta có vài ba cái tên bị lỗi, thì việc sửa lỗi cũng không mất nhiều thời gian. Tuy nhiên nếu có quá nhiều tên bị lỗi, lên đến hàng 100 hoặc hàng ngàn tên bị lỗi thì việc sửa chửa là không thể.

Lúc này ta phải xóa nó đi.

Cũng giống như cách sửa lỗi, để xóa các tên bị lỗi thì ta cũng phải gọi hộp thoại quản lý tên (Name Manager) để chọn lấy tên cần xóa và nhấn De-lete. Tuy nhiên trong trường hợp có quá nhiều tên bị lỗi, thì ta không thể xóa từng cái được. Đặc biệt các tên bị lỗi nằm xen kẻ với các tên không bị lỗi. Vậy làm sao đây?

Để thực hiện xóa đồng loạt các tên lỗi ta làm như sau:

Group là một trong những tính năng hữu ích của Excel để hợp nhất các sheet riêng lẻ, thực hiện chỉ một lệnh thay đổi cho hàng loạt các sheet khác nhau. Nhờ đó tiết kiệm rất nhiều thời gian và công sức.

Các thay đổi được thực hiện trong một sheet của bảng tính Exel sẽ cùng thay đổi trong các sheet khác của bảng tính.

Group giúp tiết kiệm rất nhiều thời gian cho người dùng Excel trong việc thực hiện tính toán hoặc tạo các bảng tính phức tạp với dữ liệu lớn cũng như chỉnh sửa và định dạng nhiều sheet.

Group sẽ cho kết quả tốt nhất cho người dùng khi cùng loại dữ liệu được trình bày trong các ô có cùng địa chỉ trong các sheet khác nhau.

Group cũng cải thiện độ chính xác của dữ liệu và loại bỏ lỗi do người dùng gây ra khi thực hiện các phép tính giống nhau trên nhiều sheet khác nhau.

Cách để nhóm (Group) và hủy nhóm (Ungroup) trong Excel

Để nhóm (Group) các sheet tính riêng lẻ, bạn cần nhấn và giữ phím đồng thời phím CTRL trên bàn phím và sử dụng chuột để kích chọn tên các sheet riêng.

Khi không cần nhóm các sheet đã chọn lại với nhau trong Excel, bạn có thể hủy Group bằng cách bấm giữ phím CTRL, hoặc kích chuột vào tên các sheet trong file.

Trong trường hợp bạn muốn nhóm (Group) tất cả các sheet thay vì chọn từng sheet riêng lẻ như ví dụ 1. Cách làm cũng khá đơn giản chỉ với 2 bước sau:

Bước 1: Đầu tiên, bạn kích chọn bất kỳ sheet nào trong số các sheet cần nhóm.

Cách Group tất cả các sheet trong Excel đơn giản thì việc Ungroup các sheet này cũng tương đối dễ dàng và được thực hiện như sau.

Kích chuột phải vào sheet bất kỳ đang được chọn, trong menu hiển thị chọn tùy chọn ‘Ungroup worksheets’.

Để nhóm các sheet liên tiếp với nhau trong Excel, đầu tiên bạn cần kích chọn tên sheet đầu tiên.

Để hủy nhóm các sheet liên tiếp nhau trong Excel, bạn chỉ cần kích chuột phải vào sheet đầu tiên hoặc cuối cùng và chọn tùy chọn ‘Ungroup worksheets’

Cách để sử dụng tính năng Group để tính toán trong Excel

Người dùng chỉ cần chọn một sheet để thêm công thức và áp dụng các thay đổi định dạng cho tất cả các sheet còn lại của bảng tính khi sử dụng tính năng Group.

Nên xác nhận xem Group có được chọn hay không bằng cách kiểm tra tên của file trong thanh tiêu đề.

Để tạo một nhóm các sheet với nhau trong Excel, ít nhất phải có hai sheet trong một file Excel.

Không duyệt qua các sheet khác không có trong group ngay sau khi đã nhóm các sheet cần thực hiện. Vì thao tác này sẽ lập tức hủy lệnh nhóm các sheet riêng.

Sử dụng tính năng Group để áp dụng các hàm tính toán trong Excel chỉ chính xác khi mà các sheet có cùng cấu trúc hàng, cột giống hệt nhau.

Ngoài việc sử dụng tính năng Group để tính toán trong Excel, người dùng cũng có thể cài đặt lệnh in, trang in, căn lề trái, phải, trên dưới ….của tất cả các sheet được chọn trong cửa sổ in. Điều này sẽ giảm tối đa thời gian nếu bạn cần phải in rất nhiều sheet giống nhau cần sử dụng chung một định dạng in trong các file Excel.

Tài liệu kèm theo bài viết

Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…