Bạn đang xem bài viết Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010 được cập nhật mới nhất tháng 9 năm 2023 trên website Eduviet.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
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ẻ
Số lượt thích
Đang tải…
Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010
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.
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.
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:
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
About dongnguyentheLuôn tìm cách cân bằng giữa cuộc sống và công việc. Ngoài thời gian làm việc bận rộn, tôi thích dành nhiều thời gian cho bọn trẻ con, giúp chúng học và giải trí, tập luyện thể thao. Về cá nhân, tôi rất coi trọng việc rèn luyện và phát triển các kỹ năng cá nhân, thích đọc sách, viết lách, du lịch mạo hiểm và chụp ảnh.
Bài này đã được đăng trong 09-Office 14. Đánh dấu đường dẫn tĩnh.
Đặt Tên Vùng, Chèn, Xóa Ô, Dòng Và Cột Trong Microsoft Excel 2010
Minh họa cho hiện các chú thích
Minh họa chèn các dòng trống lên trên các dòng đang chọn
Ta có thể qui định chiều cao dòng và độ rộng cột cho cả worksheet hay cả workbook bằng cách chọn cả worksheet hay cả workbook trước khi thực hiện lệnh. Ngoài cách thay đổi chiều cao dòng và độ rộng cột như trên, ta còn có thể dùng chuột để thao tác nhanh hơn. Muốn thay đổi độ rộng cột nào hãy rê chuột đến phía bên phải tiêu đề cột đó cho xuất hiện ký hiệu
và kéo chuột về bên phải để tăng hoặc kéo về bên trái để giảm độ rộng cột. Tương tự, muốn thay đổi chiều cao dòng nào hãy rê chuột đến bên dưới số thứ tự dòng cho xuất hiện ký hiệu
và kéo chuột lên trên để giảm hoặc kéo xuống dưới để tăng chiều cao dòng.
Di chuyển trong bảng tính và sử dụng phím tắt Bạn có thể dùng chuột, các phím mũi tên lên, xuống, trái, phải , thanh cuốn dọc, thanh cuốn ngang, sheet tab, các tổ hợp phím tắt đề di chuyển qua lại giữa các sheet hay đi đến các nơi bất kỳ trong bảng tính. Thanh cuốn dọc, thanh cuốn ngang Thanh cuốn dọc và thanh cuốn ngang được gọi chung là các thanh cuốn (scroll bars). Bởi vì màn hình Excel chỉ có thể hiển thị (cho xem) một phần của bảng tính đang thao tác, nên ta phải dùng thanh cuốn dọc để xem phần bên trên hay bên dưới bảng tính và dùng thanh cuốn ngang để xem phần bên trái hay bên phải của bảng tính.
* Thanh cuốn dọc là thanh dài nhất, nằm dọc ở bên lề phải; hai đầu có hai mũi tên lên và mũi tên xuống; ở giữa có thanh trượt để cuốn màn hình lên xuống. * Thanh cuốn ngang là thanh nằm ngang, thứ hai từ dưới màn hình đếm lên. Có hình dạng giống thanh cuốn dọc, chỉ khác là nằm ngang.
Thao tác: * Nhấp chuột vào mũi tên sang trái để cuốn màn hình đi sang trái. * Nhấp chuột vào mũi tên sang phải để cuốn màn hình sang phải.
Sử dụng các tổ hợp phím tắt để di chuyển Để đến được nơi cần thiết trong bảng tính ngoài việc dùng chuột cuốn các thanh cuốn ngang và dọc, các phím mũi tên thì bạn nên nhớ các tổ hợp phím ở bảng bên dưới để giúp di chuyển được nhanh hơn.
Nhấn phímDi chuyển → hoặc Tab Sang ô bên phải ← hoặc Shift + Tab Sang ô bên trái ↑ Lên dòng ↓ Xuống dòng Home Đến ô ở cột A của dòng hiện hành Ctrl + Home Đến địa chỉ ô A1 trong worksheet Ctrl + End Đến địa chỉ ô có chứa dữ liệu sau cùng trong worksheet Alt + Page Up Di chuyển ô hiện hành qua trái một màn hình Alt + Page Down Di chuyển ô hiện hành qua phải một mành hình Page Up Di chuyển ô hiện hành lên trên một màn hình Page Down Di chuyển ô hiện hành xuống dưới một màn hình F5 Mở hộp thoại Go To End + → hoặc Ctrl + →Đến ô bên phải đầu tiên mà trước hoặc sau nó là ô trống End + ← hoặc Ctrl + ←Đến ô bên trái đầu tiên mà trước hoặc sau nó là ô trống End + ↑ hoặc Ctrl + ↑ Lên ô phía trên đầu tiên mà trên hoặc dưới nó là ô trống End + ↓ hoặc Ctrl + ↓ Xuống ô phía dưới đầu tiên mà trên hoặc dưới nó là ô trống Ctrl + Page Up Di chuyển đến sheet phía trước sheet hiện hành Ctrl + Page Down Di chuyển đến sheet phía sau sheet hiện hành
Cách Đặt Tên Cho Ô Hoặc Vùng Dữ Liệu Trong Excel
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
Hướng Dẫn Các Cách Để Bạn Đặt Tên Cho Vùng Trong Excel
Trong bài viết này, chúng tôi sẽ hướng dẫn các bạn một số cách để bạn có thể đặt tên cho vùng trong Excel một cách nhanh chóng và góp phần giúp ta làm việc với Excel hiệu quả hơn.
Trong Excel có một tính năng rất hay là đặt tên cho vùng dữ liệu (Name Range). Với việc đặt tên này chúng ta có thể sử dụng tên khi viết công thức, hàm, hoặc thậm chí có thể sử dụng cả trong VBA.
Sử dụng Name BoxĐây có lẽ là cách đơn giản nhất để bạn có thể đặt tên cho 1 vùng, cụ thể bạn thực hiện như sau:
Đầu tiên, bạn chọn 1 vùng dữ liệu mà bạn muốn đặt tên, sau đó di chuyển con trỏ lên ô Name Box (như hình) và thực hiện gõ tên, sau đó ấn Enter để xác nhận tên cho vùng.
Sử dụng chuột phảiVới cách này, trước tiên bạn cũng chọn vùng dữ liệu muốn đặt tên, sau đó chuột phải lên vùng chọn đó, tìm tới mục có tên là Define Name…
Vào Tab FormulasVới cách này bạn vào Tab Formulas trên thanh Ribbon của Excel, trong nhóm Defined Names bạn chọn Define Name để đặt tên cho vùng dữ liệu của bạn.
Ngoài ra, trong nhóm này bạn cũng có thể đặt tên cho vùng dữ liệu bằng cách vào Name Manager (Quản lý tên), một hộp thoại Name Manager hiện lên và bạn chọn New để đặt một tên mới. Trong hộp thoại quản lý tên này bạn sẽ có nhiều tuỳ chọn hơn như thêm mới, chỉnh sửa, xoá tên,…
Với các cách sử dụng chuột phải hay vào Tab Formulas để đặt tên thì bạn sẽ nhận được 1 hộp thoại New Name như sau:
Trong hộp thoại này:
Name: Đây là tên mà bạn muốn đặt cho vùng dữ liệu. (Lưu ý: Tên này cần viết liền nhau, không có dấu, không được chứa các ký tự đặc biejt và không được trùng với các tên đã đặt trước đó)
Scope: Đây là phạm vi hoạt dộng của tên mà bạn đặt, bạn có thể chọn phạm vi là chỉ trong 1 Sheet cụ thể nào đó, hoặc trong toàn bộ File (thường dùng)
Comment: Đây là miêu tả thêm cho tên vùng của bạn
Refers to: Đây là vùng dữ liệu tham chiếu mà bạn muốn đặt tên cho nó.
Ngoài các cách đặt tên trên, bạn còn có tuỳ chọn đặt tên nữa là sử dụng VBA.
Bạn cũng hoàn toàn có thể đặt tên cho vùng dữ liệu bằng việc sử dụng Code VBA, cụ thể ta sẽ có dòng Code VBA đơn giản như sau:
Với thuộc tính .Name cho vùng B2:C5 trong Sheet hiện tại bạn đặt tên cho nó là Name_04
Hướng dẫn cách tạo name và sử dụng name trong vba excel
Gitiho.com hy vọng qua bài viết này, bạn đã biết được các cách khác nhau để đặt tên (Define Name) cho một vùng dữ liệu. Để làm việc được hiệu quả hơn với các Name này thì bạn cũng cần kiểm soát được các Name của mình một cách khoa học và hợp lý.
Hướng dẫn cách bật, tắt đường kẻ mờ trong Excel
Thủ thuật kiểm tra sai lệch dữ liệu trong bảng dữ liệu lớn của excel
Các thủ thuật chèn nhanh dòng trong excel, nhiều cách lạ và hay
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,…
Cách Dùng Tên (Define Name) Cho Vùng Dữ Liệu Trong Excel
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:
Cập nhật thông tin chi tiết về Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010 trên website Eduviet.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!