Cập nhật thông tin chi tiết về Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010 mới nhất trên website Uplusgold.com. 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.
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ẻ
Like this:
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 dongnguyenthe
Luô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.
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ải
Vớ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 Formulas
Vớ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 Sử Dụng Tính Năng Group Trong Excel
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,…
Bạn đang xem bài viết Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010 trên website Uplusgold.com. 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. Chúc bạn một ngày tốt lành!